0

I am writing table to mysql from python using pymysql to_sql function. I am having 1000 rows with 200 columns.

Query to connect to mysql is below:

engine = create_engine("mysql://hostname:password#@localhostname/dbname")
conn = engine.connect()
writing query: df.to_sql('data'.lower(),schema=schema,conn,'replace',index=False)

I am getting below error:

OperationalError: (pymysql.err.OperationalError) (1118, 'Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.')

I have changed column dtypes to string still am getting above error. Please, help me to solve this error.

I am trying to save the table like below. Here, I am providing few columns with create table query.I am getting error while creating the table while saving. CREATE TABLE dbname.table name( 08:00:00 TEXT, 08:08:00 TEXT, 08:16:00 TEXT, 08:24:00 TEXT, 08:32:00 TEXT, 08:40:00 TEXT, 08:48:00 TEXT, 08:56:00 TEXT, 09:04:00 TEXT, 09:12:00 TEXT, 09:20:00 TEXT, 09:28:00 TEXT)

Navya
  • 307
  • 3
  • 15
  • Give miniminally viable code please. Reduce your problem to show an example that still causes the error. Show the pandas dataframe `df` you are trying to store and the `schema`, at least of the table `data`. – Bennett Brown Mar 19 '21 at 05:54
  • 1
    The `to_sql` method is not from `pymysql`. It is a method of a dataframe object from pandas. https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_sql.html – Bennett Brown Mar 19 '21 at 05:57
  • @Bennett Brown ,Ok I will post the data – Navya Mar 19 '21 at 06:00
  • https://www.percona.com/blog/2011/04/07/innodb-row-size-limitation/. This contains a number of solutions for your problem. Specially the compression one is worth looking at. It is a bit difficult to reproduce this at my end due to the large nature of data otherwise I would have tried providing the code solution. – Farhan Ahmed Mar 19 '21 at 06:04
  • @BennettBrown, I have provided the sample columns – Navya Mar 19 '21 at 06:12

2 Answers2

0

The error is not caused by your code. It is caused by your Mysql server

To solve the problem change the innodb_log_file_size in the my.ini text file of your mysql server.

Then I beleive the error will be fixed.

innodb_log_file_size = 500M 
innodb_log_buffer_size = 800M
innodb_strict_mode = 0
Balaj Narasimhan
  • 198
  • 9
  • ,I have searched for innodb_log_file_size in my.ini but I didn't find that option in Innodb tab. Is innodb_log_file_size and innodb_buffer_file_size same? – Navya Mar 19 '21 at 06:11
  • No they are not same, Can you add innodb_log_file_size = 500M and innodb_log_buffer_size = 800M to your file.. Then try it – Balaj Narasimhan Mar 19 '21 at 06:13
  • I am getting error while setting those values.```SET GLOBAL innodb_log_file_size = 500M;``` error : ``` variable innodb_log_file_size is a read only variable```. Error code 1238 in mysql – Navya Mar 19 '21 at 07:25
  • I beleive you have changed the values in my.ini file and restarted the mysql srvr. Is that what you did or you tried to set in mysql cmd – Balaj Narasimhan Mar 19 '21 at 07:38
  • I have changed innodb_log_buffer_size to 800M in server>Optionsfile>Innodbtab and I have marked checkbox in Server>Optionsfile>Innodb tab. I didn't find innodb_log_file_size in Innodb tab so I have used mysql query in query tab. I have restarted the mysql using task manager. – Navya Mar 19 '21 at 07:48
  • Ok no problem, Can you check the C:\Program Files\MySQL\MySQL Server 5.6\ location for my.ini file.. If it is present can you open it and check the details. Or whereever the mysql is installed – Balaj Narasimhan Mar 19 '21 at 08:12
  • Currently i am using mysql workbench 8.0 version but my.ini file contains mysql server 5.5 version .Why? I am not able open that my.ini file. – Navya Mar 19 '21 at 08:15
  • First thing to understand is Mysql workbench is just a UI tool for Mysql Server(Running in background). Stop the mysql server from command line with "net stop MySQL" command. Then open the my.ini to edit the file and save it. Now start the mysql server using "net start MySQL" . https://stackoverflow.com/questions/10885038/stop-mysql-service-windows – Balaj Narasimhan Mar 19 '21 at 08:43
  • I have included the those parameters in my.ini file then restarted the MySQL but it did not work for me. Is there any other method to solve that problem? – Navya Mar 20 '21 at 07:26
  • Can you please help me to solve that error – Navya Mar 25 '21 at 09:22
  • Did you restart the MySql server ? or you restarted the workbench – Balaj Narasimhan Apr 08 '21 at 10:14
0

Try below values setting in my.cnf and restart mysqld. I do hope this might help you. Refer below link for more details.

 [mysqld]
 max_allowed_packet = 1G
 innodb_log_file_size = 2G
 innodb_log_buffer_size = 512M

Helpful link

Chinmay T
  • 745
  • 1
  • 9
  • 17