How to fix "Variable 'sql_mode' can't be set to the value of 'NULL'" error

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 this troubles? I'm using HeidiSql 6 for export that table...

In fact the table and the data is added on my db, Just I don't understand why that message...

Answer 1

If the code you posted is the entire code you're running, then the cause would be:

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

Normally you'd also have this at the top of a SQL dump to preserve the original value before the dump:

/*!40101 SET OLD_SQL_MODE=@SQL_MODE */;

In what you posted this is omitted, so @OLD_SQL_MODE is NULL, so at the end you'd be setting it to NULL.

Answer 2

I've not used phpMyAdmin but perhaps it has something to do with actually declaring the sql mode for the database?

The SQL modes define what syntax the database should support and what validation checks it should do.

In Mysql from the command line you can check what the sql mode is declared as by:

mysql> SELECT @@sql_mode;

Overview of sql modes

List of modes