The following query imports 100,000 perfectly, but larger files nothing happens. No errors, no rows loaded.
Any clues you can offer are appreciated. I spent hours looking into max file and memory limits and need help. Thanks!
LOAD DATA LOCAL INFILE 'C:\\temp\\masterplay\\bigone.csv' INTO TABLE mytable FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (`MARK`,`SERIAL NUMBER`, ...thirty some columns...) 0 rows inserted. (Query took 2.2385 sec)
The above performs perfectly and FAST on 100,000 records, does nothing on 350,000 records. When the CSV is broken down to 100,000+ records, the import works.
Example CSV file size is 314,946 records. Average 551 characters per row. Max row length is 575. Versions: MYSQL 5.6.17, PHP 5.5.12 WAMPSERVER 2.5, Windows 10 Pro 16 GB RAM. Unable to determine failure mode, so no reference to cutoff on file.
Per suggestions below, tried same SQL on other versions and Linux, same problem.
Ideas below focused me on the source CSV files. Thus far, I discovered resaving the CSV (even though it checks good using CSVed application and appears identical in name and content) somehow fixes the file so that it will import. I have hundreds of large files to process and yet do not understand what the difference is.
The files are comma separated with no text delimiters. After I re-save the file, it still appears to be identical in content but roughly 300K larger file size. Does a CSV file type have a header or meta data that makes a difference??