ERROR 1064 (42000) at broken character
I tried to convert database latin1 to utf8 as the under-mentioned procedure.
mysqldump -uroot -p --default-character-set=latin1 --extended-insert=false db-org > dump_data.sql
Converting dumpdata from latin1 to utf8 using notepad++.
Changing "latin1" to "utf8".
mysql -uroot -p db-upgrade < dump_data.sql
Then I got this error message.
ERROR 1064 (42000) at line 26225: 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 'Mozilla/4.0 (compatible' at line 1
I found so many records which contains broken characters as quote:
INSERT INTO g4_visit VALUES (333464,'202.30.xxx.xxx','2010-03-05','14:08:52','http://search.naver.com/search.naver?query=?ㅻ━肄섑샇?ㅻ깂??,'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 1.1.4322; InfoPath.2; .NET CLR 2.0.50727; .NET CLR 3.0.04506.648; .NET CLR 3.5.21022; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729)');
INSERT INTO g4_visit VALUES (335632,'115.143.xxx.xxx','2010-03-11','20:45:41','http://search.daum.net/search?w=tot&f=&q=?€紐낆꽦??,'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.0; Trident/4.0; GTB6; Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) ; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.5.30729; .NET CLR 3.0.30618; .NET CLR 1.1.4322)');
these are two of no probelm records:
INSERT INTO g4_visit VALUES (333461,'121.182.xxx.xxx','2010-03-05','13:58:21','','Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.0; Trident/4.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; InfoPath.1; .NET CLR 3.5.30729; .NET CLR 1.1.4322; OfficeLiveConnector.1.4; OfficeLivePatch.1.3; .NET CLR 3.0.30729; AskTB5.3)');
INSERT INTO g4_visit VALUES (333463,'95.27.xxx.xxx','2010-03-05','14:02:36','http://www.dathdfic.com/bbs/board.php?bo_table=dmb_free&wr_id=265098&sfl=&stx=&sst=wr_hit&sod=desc&sop=and&page=26+%28200+ok%29+ACCEPTED','Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 4.0; .NET CLR 1.0.2914)');
Is ther another method to convert latin1 to utf8 without broken character?
Or is there a method to search broken characters and replace them?
Any comment would be much appreciated.