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

Question

0 个投票

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!

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Follow Question

Answer 1

Keshav 2023-4-12

0 个投票

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

2 个评论
显示无隐藏无

Simon 2023-4-13

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 2023-4-18

@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?

请先登录，再进行评论。

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

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

2 个评论
显示无隐藏无

更多回答（0 个）

类别

产品

版本

标签

Community Treasure Hunt

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

0 个评论 显示 -2更早的评论 隐藏 -2更早的评论

采纳的回答

2 个评论 显示 无 隐藏 无

更多回答（0 个）

类别

产品

版本

标签

另请参阅

Community Treasure Hunt

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

2 个评论
显示无隐藏无