CODEWITHSUNDEEP
pythonmysqlcsv
Learning
Learning

Reputation: 89

MySQL crashes during data transfer from large csv using LOAD DATA from python

I have a large csv file of 30 million rows(1.6 gb) and I am using pymysql to load the data from csv to mysql tables. I have removed all constraints in table schema to make load faster and have also set timeout values to large values.

def setTimeOutLimit(connection):
try:
    with connection.cursor() as cursor:
        query = "SET GLOBAL innodb_lock_wait_timeout = 28800"
        cursor.execute(query)

        query2 = "SET innodb_lock_wait_timeout = 28800"
        cursor.execute(query2)

        query3 = "SET GLOBAL connect_timeout = 28800"
        cursor.execute(query3)

        query4 = "SET GLOBAL wait_timeout = 28800"
        cursor.execute(query4)

        query5 = "SET GLOBAL interactive_timeout = 28800"
        cursor.execute(query5)

        query6 = "SET GLOBAL max_allowed_packet = 1073741824"
        cursor.execute(query6)

except:
    conn.close()
    sys.exit(" Could not set timeout limit ")

The data gets inserted into the table but I need to make one of the column as Primary Key and so I am creating another table that makes that column primary index by ignoring duplicate values. (tableName_1 is old table tableName is new table)

def createNewTableFromOld(connection, tableName):

try:
    pprint( " Creating new table from old table  with constraints" )

    with connection.cursor() as cursor:

        query = (" CREATE TABLE " + tableName + 
                 " Like " + tableName + "_1")

        cursor.execute(query)

        query2 = (" ALTER TABLE " + tableName +
                  " ADD PRIMARY KEY(TimeStamp) ")

        cursor.execute(query2)

        query3 = (" INSERT IGNORE INTO " + tableName + 
                  " SELECT * FROM " + tableName + "_1")

        cursor.execute(query3)

        query4 = ("DROP TABLE " + tableName + "_1")

        cursor.execute(query4)

        connection.commit()

except:
    conn.close()
    sys.exit(" Could not create table with Primary Key ")

During this method execution, somewhere after 5-6 minutes I get this error, pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query ([WinError 10054] An existing connection was forcibly closed by the remote host)')

And when I check services, MYSQL80 automatically crashed and stopped. I have also set max_allowed_packet_size to 1 gb in my.ini file and all timeouts are manually set to 8 hours. What could be the issue?

The original table schema is:

query = ("CREATE TABLE IF NOT EXISTS " + table + " ("
                  " TimeStamp  DECIMAL(15, 3), " + 
                  " Value      DECIMAL(30, 11), " +
                  " Quality    INT, " +
                  " TagName    varchar(30) )"
                  )

Upvotes: 0

Views: 407

Answers (1)

Learning
Learning

Reputation: 89

I finally solved the issue by setting the innodb_buffer_pool_size in my.ini file to 2GB which was earlier only 4M.

Upvotes: 1

Related Questions