mariadb import bug: ERROR 1064 (42000) at line xxxxx

[kaibosh]

A recent issue with mariadb import throws this error:

Code: [Select]

ERROR 1064 (42000) at line 12182: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '*/' at line 1
Until the bug is fixed upstream, one work-around is to delete the line (in this case 12182) from the dump file, import the database, then manually insert the trigger with:

Code: [Select]

CREATE TRIGGER insert_MD5 BEFORE INSERT ON release_comments FOR EACH ROW SET NEW.text_hash = MD5(NEW.text);
Depending on available RAM, you'll probably need to use a "low-level" editor like vim if the SQL dump file is larger than say 10G. This bug has been seen on Ubuntu 14.04.3 LTS and Fedora 23.

[drakejones]

I am seeing the same error using Percona.

<<addendum>> Did you upgrade from Server version 5.5 to 5.6 before the error occurred?

[kaibosh]

This occurred with mariadb-10.0.21-1.fc23.x86_64

The export/import where done on the same db version.

[drakejones]

I solved this for my case. It's a "bad" trigger. You can identify the offending trigger by opening up your mysqldump with Logexpert or other big file viewer. If it's a trigger problem, the trigger should be within a line or two of the reported error.  The fix: I used phpmyadmin. Backup your data just in case... Open phpmysql, click on nzedb db. At the top of the page are tabs, click on "Triggers", delete the offending trigger. Do a mysqldump (I always do a force recovery=1 or 2) and re-import your data. The problem should be gone.

[av98]

Ah! I just had this today myself

Code: [Select]

[ERROR 1064 (42000) at line 47107: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '*/' at line 1
Line 47000 was at about the 20GB mark of a 27GB database. It was easiest to just split the file at the 4700 line mark like so:

split -l 47100 nzedb-compact.sql

Offending code:

Code: [Select]

/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`nzedb`@`localhost`*/ /*!50003 TRIGGER insert_MD5 BEFORE INSERT ON release_comments FOR EACH ROW
  SET
    NEW.text_hash = MD5(NEW.text); */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!40101 SET @saved_cs_client     = @@character_set_client */;\
/*!40101 SET character_set_client = utf8 */;
The funny thing is, the trigger did actually get stored before the import crapped out. I tried it in a number of different database servers and all the same problem.

Restarting the import at the following point successfully imported the database

Code: [Select]

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `release_comments` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `releases_id` int(11) unsigned NOT NULL COMMENT 'FK to releases.id',
  `text` varchar(2000) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `text_hash` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `user_id` int(11) unsigned NOT NULL,
  `createddate` datetime DEFAULT NULL,
  `host` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `shared` tinyint(1) NOT NULL DEFAULT '0',
  `shareid` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `siteid` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `nzb_guid` binary(16) NOT NULL DEFAULT '0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix_release_comments_hash_releases_id` (`text_hash`,`releases_id`),
  KEY `ix_releasecomment_releases_id` (`releases_id`),
  KEY `ix_releasecomment_userid` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
using VI to trim off the lines that were bad I found was the best way as the whole file doesn't try to load itself into memory