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

Had a similar issue after restarting the import of a large file to MySQL that had timed out. The explanation in this link seems to fit. In my case, all seems okay as per the answer that I found. Note, I was not using Drupal.

http://drupal.org/node/703764

Answer 3

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

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

PS: A good way to debug it is create a fresh dump of your existing DB and see what's different between the two files. Try adding those statements and it should fix it.

Answer 4

This solution works for me. To fix this issue set the SQL modes like this:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

I would recommend check the the docs for detailed description. Ref: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-setting

Answer 5

You can try the below configuration in "\mysql\bin\my.ini" and it will fix the issue.

[mysqld]
max_allowed_packet = 64M
default_time_zone="+00:00"