row_count() blues

MySQL’s row_count(), I think, should be in every MySQL developer’s toolbox. I’ve been happily using it with MySQL 5.1. Then some odd things started happening with my newly upgraded CentOS 7 box, and I investigated, of course with MySQL Workbench. But I got totally confused along the way. After many WTF’s, I finally found out what the problem was. Well, it was my fault, to be exact, for not reading the docs very carefully.

My project uses C# and for MySQL Connector/Net. Somehow the same insert statement seemed to give different results for my C# program and for MySQL Workbench test. Why? Let’s read the docs carefully.

Originally I read 5.1’s row_count(). Then for 5.5, the behavior changed drastically for certain cases:

If you specify the CLIENT_FOUND_ROWS flag, the affected-rows value is 1 (not 0) if an existing row is set to its current values” Which means that CLINET_FOUND_ROWS is not default. Indeed that was the case when I experimented with MySQL Workbench.

Well, only if you specify the flag, which for backward compatibility should be off by default.

Now I searched for an equivalent option for MySQL Connector/net. Of course it should be in the connection string, and there it was

Use Affected Rows, UseAffectedRows
default: false
When true, the connection reports changed rows instead of found rows. This option was added in Connector/Net version 5.2.6.

I first glanced over it, and repeated my WTF’s for about ten minutes. Wait, what? The default is false? The default is as if the flag CLIENT_FOUND_ROWS is given? The default is NOT compatible with 5.1’s behavior?

Why?

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.