0

I have a exported sql backup file, and when I want to import into the mariaDB(mysql), some insert sentences trigger the syntax error 1064 when I import it from command.

how could I to modify these cases to be correct, waht is the problem(I susspect it is have some html and mathml tags in some field values)

for a original text, I put a long string with no newline, so the format will be not so good for reading,forgive me for I want to keep the original text content

and the sql file is large( about 3GB) if I want to correct this, is there a good utlity or script. should I write a script to pattern this.

there is a bad case(a little messy because the html tag)

 INSERT INTO `scimag` (`ID`, `DOI`, `DOI2`, `Title`, `Author`, `Year`, `Month`, `Day`,
 `Volume`, `Issue`, `First_page`, `Last_page`, `Journal`, `ISBN`, `ISSNP`, `ISSNE`, 
`MD5`, `Filesize`, `TimeAdded`, `JOURNALID`, `AbstractURL`, `Attribute1`, `Attribute2`,
 `Attribute3`, `Attribute4`, `Attribute5`, `Attribute6`, `Attribute7`, `Attribute8`, 
`Attribute9`, `Attribute10`) VALUES (25524454,'10.1088/1751-8113/44/31/315202','',
'Some exact results for the face-centred cubic lattice Green function','G. S. 
Joyce','2011','','','','','','','','','','','789a1f1ccfbb103645bd23dc3e0c0768',128882,
'2014-06-01 19:58:00','','http://stacks.iop.org/1751-8121/44/i=31/a=315202?
key=crossref.dbc1c6f74b26849de6c4e0d6e5cd7c26','','','','','','','','','',''),
(25524455,'10.1103/PhysRevD.89.094027','','Connection of the virtual 
<span class=\"aps-inline-formula\"><math xmlns=\"http://www.w3.org/1998/Math/MathML\"
 display=\"inline\"><mrow><msup><mrow><mi>γ</mi></mrow><mrow><mo>*</mo></mrow></msup>
<mi>p</mi></mrow></math></span> cross section of <span class=\"aps-inline-formula\">
<math xmlns=\"http://www.w3.org/1998/Math/MathML\" display=\"inline\"><mi>e</mi>
<mi>p</mi></math></span> deep inelastic scattering to real <span class=\"aps-inline-
formula\"><math xmlns=\"http://www.w3.org/1998/Math/MathML\" display=\"inline\">
<mi>γ</mi>
<mi>p</mi></math></span> scattering, and the implications for <span class=\"aps-inline-
formula\"><math xmlns=\"http://www.w3.org/1998/Math/MathML\" display=\"inline\">
<mi>ν</mi><mi>N</mi></math></span> and <span class=\"aps-inline-formula\"><math 
xmlns=\"http://www.w3.org/1998/Math/MathML\" display=\"inline\"><mi>e</mi><mi>p</mi>
</math></span> total cross sections','M. M. Block; L. Durand; P. 
Ha','2014','','','','','','','','','','','801bff06c8d77d0aff88f8f5f725553d',729649,'2014-
06-01 19:58:10','','http://link.aps.org/doi/10.1103/PhysRevD.89.094027','','','','','','','','','
','')

and a good example:

INSERT INTO `scimag` (`ID`, `DOI`, `DOI2`, `Title`, `Author`, `Year`, `Month`, `Day`,
 `Volume`, `Issue`, `First_page`, `Last_page`, `Journal`, `ISBN`, `ISSNP`, `ISSNE`, 
`MD5`, `Filesize`, `TimeAdded`, `JOURNALID`, `AbstractURL`, `Attribute1`, `Attribute2`,
 `Attribute3`, `Attribute4`, `Attribute5`, `Attribute6`, `Attribute7`, `Attribute8`, 
`Attribute9`, `Attribute10`) VALUES (24776787,'10.1016/S0891-5520(05)70009-
X','','','','','','','','','','','','','','','88c7b98c0f7dc213b477b01b7c001c92',882298
,'2014-06-01 
14:43:35','','','','','','','','SD/e96c58addabf82dc2c6a4e50ff7c3115.pdf','','','','')

you can create the table with:

CREATE TABLE `scimag` (
    `ID` INT(15) UNSIGNED NOT NULL AUTO_INCREMENT,
    `DOI` VARCHAR(100) NOT NULL DEFAULT '',
    `DOI2` VARCHAR(100) NOT NULL DEFAULT '',
    `Title` VARCHAR(2000) NOT NULL DEFAULT '',
    `Author` VARCHAR(2000) NOT NULL DEFAULT '',
    `Year` VARCHAR(10) NOT NULL DEFAULT '',
    `Month` VARCHAR(10) NOT NULL DEFAULT '',
    `Day` VARCHAR(10) NOT NULL DEFAULT '',
    `Volume` VARCHAR(45) NOT NULL DEFAULT '',
    `Issue` VARCHAR(95) NOT NULL DEFAULT '',
    `First_page` VARCHAR(45) NOT NULL DEFAULT '',
    `Last_page` VARCHAR(45) NOT NULL DEFAULT '',
    `Journal` VARCHAR(500) NOT NULL DEFAULT '',
    `ISBN` VARCHAR(500) NOT NULL DEFAULT '',
    `ISSNP` VARCHAR(11) NOT NULL DEFAULT '',
    `ISSNE` VARCHAR(10) NOT NULL DEFAULT '',
    `MD5` VARCHAR(32) NOT NULL DEFAULT '',
    `Filesize` INT(11) UNSIGNED NOT NULL,
    `TimeAdded` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `JOURNALID` VARCHAR(45) NOT NULL DEFAULT '',
    `AbstractURL` VARCHAR(500) NOT NULL DEFAULT '',
    `Attribute1` VARCHAR(500) NOT NULL DEFAULT '',
    `Attribute2` VARCHAR(1000) NOT NULL DEFAULT '',
    `Attribute3` VARCHAR(2000) NOT NULL DEFAULT '',
    `Attribute4` VARCHAR(10000) NOT NULL DEFAULT '',
    `Attribute5` VARCHAR(256) NOT NULL DEFAULT '',
    `Attribute6` VARCHAR(45) NOT NULL DEFAULT '',
    `Attribute7` VARCHAR(500) NOT NULL DEFAULT '',
    `Attribute8` VARCHAR(500) NOT NULL DEFAULT '',
    `Attribute9` VARCHAR(500) NOT NULL DEFAULT '',
    `Attribute10` VARCHAR(200) NOT NULL DEFAULT '',
    PRIMARY KEY (`ID`) USING BTREE,
    UNIQUE INDEX `DOIUNIQUE` (`DOI`) USING BTREE,
    INDEX `VOLUMEINDEX` (`Volume`) USING BTREE,
    INDEX `ISSUEINDEX` (`Issue`) USING BTREE,
    INDEX `ISSNPINDEX` (`ISSNP`) USING BTREE,
    INDEX `YEARINDEX` (`Year`) USING BTREE,
    INDEX `ISSNEINDEX` (`ISSNE`),
    INDEX `DOIINDEX` (`DOI`) USING BTREE,
    INDEX `JOURNALID` (`JOURNALID`) USING BTREE,
    FULLTEXT INDEX `MD5FULLTEXT` (`MD5`),
    FULLTEXT INDEX `FULLTEXT` (`Title`, `Author`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
ROW_FORMAT=DYNAMIC
AUTO_INCREMENT=28696436;
alljoyland
  • 101
  • 1
  • 4
  • Why did you not properly escaped the values **BEFORE** inserting them into database? – Bud Damyanov Aug 22 '14 at 12:03
  • What do you mean escaped the values, how to do this I'm not good at the sql syntax, especially import, backup for big database, I just use query in everyday life. could you be kindly in more details or give me some reference, I will to study how to escape the value. – alljoyland Aug 22 '14 at 12:18
  • http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php/60442#60442 – Bud Damyanov Aug 22 '14 at 12:20
  • Thank you very much , I will study this method – alljoyland Aug 22 '14 at 12:21
  • the reference is for single statement, I think, I want to use mysql ...< importfile.sql, which is big, maybe I should to write a text filter to filter the file, then import. I still stick to the real problem, why the exported data was not correct, and how could I to correct this in a right format – alljoyland Aug 22 '14 at 12:30
  • I read the error report carefully, and search the big sql file, found that my sql file have many tex language and a little mathml tags in the field, and when I import this sql file, at the strings like \',\",\\, etc, which is a "\" with not a [a-zA-Z_] character, mysql will think this a command, but this is not so trigger unknow command '\'' etc also '\0' I think for none, how could I solve this, what I now can think is to replace the '\\\W' to a blank, but this is information loss in the text. could there be an another solution – alljoyland Aug 22 '14 at 15:13
  • there is a similar question, but there is no a proper solution http://stackoverflow.com/questions/14167227/on-mysql-import-error-at-line-32769-unknown-command – alljoyland Aug 22 '14 at 15:16

0 Answers0