Edit: sqlwrite( ) fails when integer variable's value range is set too small.

Edit:

previous wrong undestanding: "sqlwrite( ) fails to save a tall table to MySQL (native connection mode), when the number of rows of the table is equal to or greater than 19324."

Thanks for the answer below, I realized I had a range value problem.

% finb is my original table
tic
qb = finb(1:19324,:);
sqlwrite(conn, 'qb', qb)
toc

%---- Error message----------
Error using database.relational.connection/execute
Out of range value for column 'value' at row 19324.

Error in database.mysql.connection/sqlwriteHook (line 56)
    execute(conn,insertquery);

Error in database.relational.connection/sqlwrite (line 880)

But when the table is shortened to 19323 rows, the code works fine.

qb = finb(1:19323,:);
tic
sqlwrite(conn, 'qb', qb)
toc

Elapsed time is 84.472844 seconds.

I found an explanation in a post from StackOverflow. It said MySQL imposes a limit on the number of rows for insertion. Is that the reason sqlwrite( ) fails? What does this magic number '19324' mean?

I am thinking about saving to MySQL chunk-wise through a for-loop. Is there other cleaner way to do it? Any tips you have are welcomed. Thank you!

Answer 1

[Keshav]

Hi Simon,

I understand that you are trying to use “sqlwrite” function to save the table to MySql and you are getting an error “Out of range value for column 'value' at row 19324”. It might be possible that at row 19324 the column ‘value’ has a value that is greater than the range for the column ‘value’ datatype.

you can refer the below mysql documentation to check the range of differents datatypes:

https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

[Simon]

My data include more than a thousand tables. When I sqlwrite( ) a single table one at a time, there is no error message. But if I lump them up as one long table and sqlwrite( ) it, sqlwrite( ) hanged without termination. I don't think the particular 19324th row caused the problem. I had tried to sqlwrite( ) partial rows of the long table, and sqlwrite( ) failed to work if the number of rows in a table exceeding 19324. When the number of rows, which may include the 19324th row, is less than 19324, sqlwrite( ) works fine. So I guess MySQL puts limitation on the number of rows on a table that can be written with one shot to the database. At leaset, that was one explanationI saw in StackOverflow.

[Simon]

@Keshav

Hi Keshav, thanks for your explanatoin. You're right. After I change data type from INT to BIGINT in MySQL for 'value', that error is gone. However, when I re-run sqlwrite(conn, 'tablename', LongTable), it takes more than three hours and doesn't finish. The LongTable has about 150,000 rows and 7 columns. It shouldn't be an awfully large table. If I save it to local drive as .mat file, the saving takes less than 10 minutes. Why would sqlwriting to MySQL stall?