1

I have a table like 'my table' it has only one column 'mycolumn' varchar(10). I have two records in that table. while i am trying to delete that table, the error is coming like

'data too long for column for 'x' at row 1'

how to resolve this. any idea, thanks in advance.

ENGINE is InnoDB and CHARSET is latin1.

the column varchar limit is 10, and i am not trying to insert any data, i am just deleting the data which is already exists in that table.

while deleting it showing the above mentioned error.

Community
  • 1
  • 1
Aashick
  • 91
  • 2
  • 15

3 Answers3

1

The problem is found. I forget that we are create a trigger for that table (the my table is associated with that trigger). while we delete the data from my table, that automatically fires the trigger.

in trigger we are inserting the

deletion string in temp table, that table column value is (varchar(5)), but the deletion string length is (8). so it showing error, data too long for column for 'x' at row 1.

thank you all for your help.

Aashick
  • 91
  • 2
  • 15
0

The syntax is 

DELETE FROM mytable

not:

DELETE * FROM mytable

N.B.

If you are deliberately emptying a table I prefer TRUNCATE as it is more obvious what your intentions are:

TRUNCATE mytable

If you actually want to remove the table:

DROP TABLE mytable
Arth
  • 12,789
  • 5
  • 37
  • 69
0

MySQL will truncate any insert value that exceeds the specified column width.

to make this without error try Switch your MySQL mode to not use STRICT.

To change the mode

This can be done in two ways:

Open your "my.ini" file within the MySQL installation directory, and look for the text "sql-mode". Find:

Code:

Set the SQL mode to strict

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" Replace with:

Code:

Set the SQL mode to strict

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" Or

You can run an SQL query within your database management tool, such as phpMyAdmin: Code:

SET @@global.sql_mode= '';

Shikhar Srivastava
  • 151
  • 4
  • 15