0

I am inserting data from a csv file to mysql datebase using PHP but I can't INSERT more then 922 lines . Every time the number of the lines is different, usually it is 841 or 837 but after restart my mysql-server PC it has the maximum number - 922 rows. I am runing Ubuntu server 16.04

vi /var/log/mysql/error.log is :

0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 223238094ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)

Lelio Faieta
  • 5,913
  • 6
  • 34
  • 57
Andrey
  • 134
  • 1
  • 1
    If you're using a `.csv` to import, I highly recommend using [`LOAD DATA LOCAL INFILE ...`](http://dev.mysql.com/doc/refman/5.7/en/load-data.html). Have a look [here](http://stackoverflow.com/questions/14127529/mysql-import-data-from-csv-using-load-data-infile) for a proper example. – Andrei Jun 08 '16 at 14:01
  • Make sure you're not somehow executing an insert query per line of code, that'sbadman – Glubus Jun 08 '16 at 14:02
  • 1
    Isn't it more likely that your PHP script takes too long and times out than a bug in MySQL Server? – Álvaro González Jun 08 '16 at 14:06
  • possible duplicate of [MYSQL import data from csv using LOAD DATA INFILE](http://stackoverflow.com/questions/14127529/mysql-import-data-from-csv-using-load-data-infile) – Funk Forty Niner Jun 08 '16 at 14:07
  • 1
    I bet if you look at the PHP ERROR LOG you will see a Time Limit Exceeded message – RiggsFolly Jun 08 '16 at 14:09

2 Answers2

1

The innodb_page_cleaners default value was changed from 1 to 4 in MySQL 5.7.8. If the number of page cleaner threads exceeds the number of buffer pool instances, innodb_page_cleaners is automatically set to the same value as innodb_buffer_pool_instances.

Check innodb_buffer_pool_instances with: mysql> SHOW GLOBAL VARIABLES LIKE innodb_buffer_pool_instances:

You can only set innodb_page_cleaners as high as innodb_buffer_pool_instances. If you want innodb_page_cleaners=4 then you also need innodb_buffer_pool_instances=4.

Ghassan Zein
  • 3,289
  • 2
  • 15
  • 27
0

You can try MySQL - LOAD DATA... in PHP with mysql_query function.

LOAD DATA LOCAL INFILE 'file_name_with_full_path'
            INTO TABLE table_name
            FIELDS TERMINATED BY ',' 
            OPTIONALLY ENCLOSED BY '"' 
            LINES TERMINATED BY '\n'
            (TABLE_FIELDS)

For more details - http://dev.mysql.com/doc/refman/5.7/en/load-data.html

Thanks.

Hidden Skills
  • 1
  • 2