How I INSERT to MySQL data about 10000 lines using php

Question

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

If you're using a .csv to import, I highly recommend using LOAD DATA LOCAL INFILE .... Have a look here for a proper example. — Andrei
– Andrei, Commented Jun 8, 2016 at 14:01
Make sure you're not somehow executing an insert query per line of code, that'sbadman — Glubus
– Glubus, Commented Jun 8, 2016 at 14:02
Isn't it more likely that your PHP script takes too long and times out than a bug in MySQL Server? — Álvaro González
– Álvaro González, Commented Jun 8, 2016 at 14:06
possible duplicate of MYSQL import data from csv using LOAD DATA INFILE — Funk Forty Niner
– Funk Forty Niner, Commented Jun 8, 2016 at 14:07
I bet if you look at the PHP ERROR LOG you will see a Time Limit Exceeded message — RiggsFolly
– RiggsFolly, Commented Jun 8, 2016 at 14:09

Ghassan Zein · Accepted Answer · 2016-06-08 14:04:10Z

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.

answered Jun 8, 2016 at 14:04

Ghassan Zein

4,1844 gold badges21 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hidden Skills · Accepted Answer · 2016-06-08 14:12:20Z

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.

edited Jun 8, 2016 at 14:12

answered Jun 8, 2016 at 14:06

Hidden Skills

12 bronze badges

1 Comment

Hidden Skills Over a year ago

Yes, LOAD DATA statement consider as SQL statement and use this statement as query in mysqli_query function.

Collectives™ on Stack Overflow

How I INSERT to MySQL data about 10000 lines using php

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related