MySQL memory exhausted error

Today I was using a simple Java application to load a large size data into MySQL DB, and got a error below:

java.sql.SQLException: Syntax error or access violation message from server: "memory exhausted near ''Q1',2.34652631E10,'000','000',5.0519608E9,5.8128358E9,'000','000',8.2756818E9,2' at line 5332"

I've t开发者_运维技巧ried to modified the my.ini file to increase some point, however it doesn't work at all and actually the size of file is not so large, it's just a 14mb xls file, almost running out of idea, awaiting for any suggestion. Appreciate your help!

(Without the relevant parts of your code I can only guess, but here we go...)

From the error message, I will take a shot in the dark and guess that you are trying to load all of 300,000 rows in a single query, which is probably produced by concatenating a whole bunch of INSERT statements in a single string. A 14MB XLS file can become a lot bigger when translated into SQL statements and your server runs out of memory trying to parse the query.

To resolve this (in order of preference):

• Convert your file to CSV and use mysqlimport.

• Convert your file to CSV and use LOAD DATA INFILE.

• Use multiple transactions of moderate size with only a few thousand INSERT statements each. This is the recommended option if you cannot simply import the file.

• Use a single transaction - InnoDB MySQL databases should handle transaction sizes in this size range.