How to fix "sql_mode" variable cannot be set to "NULL"

Question

I have this table:

# Dumping structure for table editz.to_import CREATE TABLE IF NOT EXISTS `to_import` ( `id` int(11) unsigned NOT NULL auto_increment, `reference` int(11) unsigned NOT NULL, `trackid` int(11) unsigned NOT NULL, `side_pos1` char(2) NOT NULL, `side1` varchar(255) NOT NULL, `pos1` char(2) NOT NULL, `hh1` char(2) NOT NULL, `mm1` char(2) NOT NULL, `ss1` char(2) NOT NULL, `atl1` varchar(255) NOT NULL, `side_pos2` char(2) NOT NULL, `side2` varchar(255) NOT NULL, `pos2` char(2) NOT NULL, `hh2` char(2) NOT NULL, `mm2` char(2) NOT NULL, `ss2` char(2) NOT NULL, `atl2` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1311 DEFAULT CHARSET=utf8; # Dumping data for table editz.to_import: ~1.025 rows (approximately) DELETE FROM `to_import`; /*!40000 ALTER TABLE `to_import` DISABLE KEYS */; INSERT INTO `to_import` (`id`, `reference`, `trackid`, `side_pos1`, `side1`, `pos1`, `hh1`, `mm1`, `ss1`, `atl1`, `side_pos2`, `side2`, `pos2`, `hh2`, `mm2`, `ss2`, `atl2`) VALUES (1, 205, 550, '0', 'Single Side', '0', '??', '??', '??', 'Noizefucker - Tons Of Bluesteel - Special Forces', '0', 'Single Side', '0', '??', '??', '??', 'Noizefucker - Tons Of Bluesteel - Special Forces'), ... some lines, approx 1300)... (1310, 268, 463, '#', '', '20', '00', '41', '00', 'Ingler - Trek', '#', '', '20', '00', '41', '00', 'Ingler - Trek'); /*!40000 ALTER TABLE `to_import` ENABLE KEYS */; /*!40101 SET SQL_MODE=@OLD _SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD _FOREIGN_KEY_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD _CHARACTER_SET_CLIENT */; 

but when I try to import using phpMyAdmin, I get this message:

 #1231 - Variable 'sql_mode' can't be set to the value of 'NULL' 

Why? And how can I fix these problems? I am using HeidiSql 6 to export this table ...

Actually the table and data are added to my db, I just don’t understand why this message ...

Answer 1

I have not used phpMyAdmin, but maybe it has something to do with the actual sql mode declaration for the database?

SQL modes determine which syntax a database should support and what validation checks it should do.

In Mysql, from the command line, you can verify that sql mode is declared:

 mysql> SELECT @@sql_mode; 

Overview of sql modes

Mode list

Answer 2

If the code you sent is all the code you use, then the reason will be:

 /*!40101 SET SQL_MODE=@OLD _SQL_MODE */; 

Usually, you should also have this at the top of the SQL dump to keep the original value before the dump:

 /*!40101 SET OLD_SQ L_MODE=@SQL _MODE */; 

This is omitted in what you posted, so @OLD_SQL_MODE is NULL, so in the end you should set it to NULL.

Answer 3

Had a similar problem after restarting importing a large file in MySQL that was disabled. The explanation in this link seems appropriate. In my case, everything looks fine according to the answer I found. Notice I did not use Drupal.

http://drupal.org/node/703764

Answer 4

None of the above solutions fixed this for me. In case the above (starts with CREATE TABLE) is your dump file, you need to add the following CREATE TABLE command:

 /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 

PS: A good way to debug is to create a new dump of the existing database and see what is different between the two files. Try adding these statements, and he should fix it.