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

5 次查看（过去 30 天）
Simon
Simon 2023-3-28
编辑： Simon 2023-5-13
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!

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

采纳的回答

Keshav
Keshav 2023-4-12
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
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
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?

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Database Toolbox 的更多信息

标签

产品


版本

R2023a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by