0

I´m trying to import an SQL file into MySQL Server 5.5, but I keep getting this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''a:10:{i:0'

and this is the query:

insert into `preferences` values ('system','active_plugins','a:10:{i:0;s:16:\"voting/index.php\";i:1;s:26:\"social_bookmarks/index.php\";i:2;s:25:\"profile_picture/index.php\";i:3;s:26:\"lz_theme_options/index.php\";i:4;s:21:\"google_maps/index.php\";i:5;s:17:\"youtube/index.php\";i:6;s:18:\"facebook/index.php\";i:7;s:23:\"lz_demo_theme/index.php\";i:8;s:25:\"cars_attributes/index.php\";i:9;s:20:\"lz_firephp/index.php\";}','STRING'),

So I'm thinking it's some encoding problem, because it is throwing an error right on the semicolon, so I checked, everything is utf-8. The strangest thing is that if I run this same query through phpmyadmin or navycat or even mysqlworkbench no error is thrown, only when it is processed by the server.

JMilanes
  • 23
  • 6
  • 1
    Nope, not the semicolon. The start of the string `'a:10` is wrong. Looks like you're actually sending two single quotes. – deceze Mar 18 '14 at 13:02
  • @deceze I've been looking and I do not see two single quotes. Are you suggesting OP did not copy+paste the SQL properly? – MonkeyZeus Mar 18 '14 at 13:05
  • @Monkey That's what I'm insinuating. – deceze Mar 18 '14 at 13:06
  • 1
    @deceze I think I see the two single-quotes which you are talking about and it refers to `right syntax to use near ''a:10:{i:0'`. I am 99% sure that MySQL errors encapsulate the suspected problem area in single-quotes before outputting the error to the user. – MonkeyZeus Mar 18 '14 at 13:07
  • @user3433109 you copied the SQL directly from the SQL file correct? – MonkeyZeus Mar 18 '14 at 13:08
  • @Monkey Yes, but the syntax as shown is correct. The error would make perfect sense if the actual query contained two quotes. – deceze Mar 18 '14 at 13:08
  • check here http://stackoverflow.com/questions/2853454/php-unserialize-fails-with-non-encoded-characters – krishna Mar 18 '14 at 13:10
  • I copyed the query from the sql file and the error from the error description in my log, but the 2 quotes you see is from the error description like @MonkeyZeus mentioned, it is not in the query. – JMilanes Mar 18 '14 at 13:11
  • You might be running into an issue with invisible characters which your text editor does not render but MySQL picks up on. http://stackoverflow.com/questions/1067742/clean-source-code-files-of-invisible-characters – MonkeyZeus Mar 18 '14 at 13:20
  • @user3433109 If there is a hidden single-quote somewhere, could you try manually removing the visible single-quote and try the SQL import from the file again? – MonkeyZeus Mar 18 '14 at 13:27
  • Ok so i tryed the link @MonkeyZeus suggested, ended up converting the file to iso 8859-1 and back to utf-8, but the problem persists. – JMilanes Mar 18 '14 at 13:32
  • As for removing the sigle quotes, theres is no extra single quote on the query, if i run the same exact query through navcat, mysqlworkbench it runs ok, no errors, so i realy think it is not a query problem, is something else. – JMilanes Mar 18 '14 at 13:35
  • I understand your frustration but did you try my suggestion? I made it a priority to distinguish hidden vs. visible single-quotes. – MonkeyZeus Mar 18 '14 at 13:43
  • ok no problem will do, but tell me this "try manually removing the visible single-quote", all of them? or a especific one? @MonkeyZeus – JMilanes Mar 18 '14 at 13:48
  • Just one, the one before `a:10`. See if the SQL import process will get stuck on that same spot or move on to the next INSERT – MonkeyZeus Mar 18 '14 at 13:49
  • @MonkeyZeus not sure if i got it right, removing any of the single quotes on the sql file before the line of the query above trows a error before, but if i remove it after the above query, the error still stops at the sabe point. Sorry if i did not understand exatly what you ment. – JMilanes Mar 18 '14 at 13:55
  • It sounds like you got it right, this is definitely strange. What MySQL version created the SQL file in the first place? Are you aware of the DB migration tools available within MySQL Workbench? – MonkeyZeus Mar 18 '14 at 14:00
  • Got it, strangely enough it stops at the same point but with a slightly diferent error: **MySQL server version for the right syntax to use near ':10:{i:0' at line 14** – JMilanes Mar 18 '14 at 14:04

1 Answers1

0

OK, so i found what the problem was, was not the mysql, was not php, was no hidden chars, as i´m dealing with a open source cms´s, and this code is for a plugin for the cms, i was using a existing method that the cms itself uses to import the same file, i have seen the method meny times, and knew that it had to break the big sql file in separate querys to than run each of those querys, and it was breaking the sql on every ";", so it was breaking the serialized string in parts too, that is why i was getting this:

''a:10:{i:0'

as a error, the query ended there, when it should not.

Thanks a lots for all the help, hope my lack of atention servers others well! rsrs

JMilanes
  • 23
  • 6