mysql 内存耗尽bug_MySQL内存耗尽错误

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 tried 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.