MySQL ODBC 8 Error: the number of attributes is larger than the number of attribute values provide

[dylim]

Hi VFP Experts,

I have recently installed MySQL 8.0.28, in the hope of migrating to it from MySQL 5.7.27.

I tried running my apps with the new version, but a lot of times I encounter this error caught via AERROR():

laError[ 1, 1 ] = 1526
laError[ 1, 2 ] = "Connectivity error: CCMD"
laError[ 1, 3 ] = "[MySQL][ODBC 8.0(w) Driver][mysqld-8.0.28]The number of attributes is larger than the number of attribute values provided"
laError[ 1, 4 ] = "01000"
laError[ 1, 5 ] = 500
laError[ 1, 6 ] = 1
laError[ 1, 7 ] = ''

Any adjustments or stuff that cannot be used anymore in MySQL 8.0.28?

Any leads, comments, help.. anything..

So desperate!

TIA

 

[dylim]

Chris,

I have used the same code for decades. Only time this didn't work when using it against this AUTOINC table created with UTF4MB4 encoding in a MySQL 8 server instance.

The TableID is always unique.

To be sure, I also revised just now to LIMIT 0,1 with same unwanted result.

I also did an ODBC trace, comparing them with using it for three other AUTOINC tables in UTF8MB3 in different databases vs this solitary AUTOINC in the new database with UTF8MB4 encoding. The latter is the only one failing. All the logs are identical. Same logs. No red flags.

 

[Chris Miller]

LIMMT 0,1 wasn't meant as a solution, how do you get this idea, it was just an observation on the side.

I have used the same code for decades

This argument gets old. Stop using it. All you do is insisting on downward compatibility with it, you can't expect downward compatibility to hold forever, especially in a major version upgrade.

Again asked, why do a SELECT FOR UPDATE into a VFP cursor, then update instead of doing an update and then read in the new id value? If doing that within a transaction you ensure no dirty reads of other users concurrently working on the table, their transaction must be queued and wait for yours to finish. And it'll only take a short time, so that's fine:

START TRANSACTION;
UPDATE autoinc SET id=id+1 For tableid=?tableid;
Select id From autoinc Where tableid=?tableid;
COMMIT;

You get the new value.

You can also get the old value, for example when needing 4 IDs:

START TRANSACTION;
UPDATE autoinc SET @oldid=id=id+4 For tableid=?tableid;
Select @oldid;
COMMIT;

Then you get the base id for your new ids, which are the returned old id +1 to +4, respectively.

If you want, you can still combine this with a select for update, but I don't see an advantage over a simpler UPDATE, an UPDATE does ensure the necessary locking and/or queueing of concurrent updates. This just does one simple thing: Make use of having a server that cares for this. No need to handle locking issues from the client side, you DO have a MySQL SERVER. Don't handle things on the client side the server should handle in itself on the server side. I guess when this all had DBFs as backend you did the same thing in VFP, but you had no other chance.

You repeat the problem about utf8mb4. In which state are your tables and indexes by now? If you recreate the tables in a completely new database making utf8mb4 the default codepage for everything and/or explicitly using utf8mb4 in any place of the definitions, then there can't be errors due to that mix of encoding, can there?

Many of my suggestions are not just about MySQL 8 but more general improvements, do things in a more proper way without overcomplicated roundtrips of data and you have a smoother outcome. Don't you agree?

Chriss

 

[dylim]

Chris,

Thank you for your being patient with me.

You repeat the problem about utf8mb4. In which state are your tables and indexes by now? If you recreate the tables in a completely new database making utf8mb4 the default codepage for everything and/or explicitly using utf8mb4 in any place of the definitions, then there can't be errors due to that mix of encoding, can there?

If the database is in UTF8MB3, it works all the time.

When I use the same class libraries, with this new database created in utb4mb4, it does not work.

 

[Chris Miller]

Which driver are you using? With VFP, no matter if your database is UTF8 or not, I'd always use the ANSI drivers.
And when we're at that which driver did you use for MySQL 5.x?

Besides that, what is the profit of using UTF8MB4 to you? What characters do you need that have a 4 byte encoding? If you don't really need UTF8MB4, then why not stick to 3?
I guess, though, using a driver for the MySQL version will also fix your problem.

Chriss

 

[Chris Miller]

Sorry, by the way. I don't bring direct solutions to your problem and it surely nags on you that things broke that worked before.

I'm not saying you wouldn't have got into any trouble if you would have done things differently already in the past.

But confronted with problems due to a new version, I personally like to combine solving a problem with better solutions I learned in the meantime or learn for solving the current problem. I know it's often not easy, as it possibly goes through all levels of your previous code and all you'd like to get done is things working as they did before.

Some of the solutions, like using UUIDs would obviously be a big hit of refactoring all code, but creating a pseudo sequence as the MySQL documenta tell it will leave you with numeric IDs just like the other solution to that. (for reference:

https://dev.mysql.com/doc/refman/5.6/en/information-functions.html#idm46323739416752)

You don't have to do everything the way I propose, of course. But you should really stop thinking the VFP way of locking rows to establish a semaphore to only update the autoinc value from one client that successfully got the lock on it.

I understand that TABLEUPDATE not working is an issue also outside of generating IDs, so it's something you'd like to fix not by preventing usage of Tableupdate, that's not solving it for general use of MySQL 8 with cursoradapters. But I can assure you VFP and MySQL can work together, provided also in cases you do query data into a CA cursor and finally update it by TABLEUPDATEs. That's not the genral problem. The special case is SELET.. FOR UPDATE, and you don't use that all the time for any other CA.

You surely have overused the VFP way of rlock, then update/replace, then unlock to MySQL with read locks. It's over the top to do that. And so I bet you can overall forget about the option to do SELECT ... FOR UPDATE. I didn't need it in my whole VFP+MySQL usage in any project.

My guess is, as I already said above, this feature will only work if you use the FOR UPDATE option and then do the update in the same call, in the same SQLExec or CA.Cursorfill or whatever other CA event or method, so it can't be split as reading data into a VFP workarea and then release the read lock by the update sent to MySQL via TableUpdate(...,CA.Alias). Even though this worked before. I think MySQL 8 has become more restricted with what it sees as single sessions. Coming in from the same connection isn't sufficient anymore. If MySQL allows START TRANSACTION and COMMIT in two separate calls, that would allow using read locks within a transaction that spans two calls, i.e. the CA SelectCmd and Tableupdate().

But even then, you don't use long lasting transactions, you want to get something updated which you read from a MySQL table with a read lock, then for gods sake, do it fully in MySQL why does it have to come over to VFP and go back? You want to have a short timespan of that transaction and therefore ideally want that to be in the same call anyway, not with any roundtrip from MySQL to VFP and back.

Chriss

 

[Chris Miller]

And one more thing I came across regarding storage of utf8mb4:

https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html#data-types-storage-reqs-strings

This tells that about a char(M) field of an InnoDB table:

M × w bytes, <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set.

The utf8mb4 character set allows 4 byte characters, so it reserves M*4 bytes instead of M*3 bytes previously. I can see how that can cause flaws, even if you mostly use the lower 128 characters only using 1 byte per character.

Chriss

 

[Chris Miller]

One more weak point in using utf8mb4 with VFP:

VFP does only have minor UTF8 support. In itself that won't matter, to VFP any string is just an array of bytes, no matter if UTF8, UCS, UTF16. The most sensible thing VFP can do with STRCONV() from UTF8 to ansi is turn a character not available in the Ansi codepage to a questionmark. If you do that with 𐍈 (just one example I found, which is 0hF0908D88 in binary form), Strcconv turns that single UTF8MB4 character into two questionmarks. That indicates it doesn't interpret UTF8 correct.

The same then is likely true in the other direction. So even something as simple as where charfield=?m.vfpstring can lead to conversion errors. I don't see a case where any existing Ansi characters fail to translate into UTF8, VFP would just never reach any 4 byte codepoint in UTF8 as no Ansi character converts to such a UTF8 codepoint, I think.

But there's something in the air that stinks. You could make MySQL deal with byte combinations not allowed in UTF8.

You better use binary types where possible, that'll enable transfers without any conversions. As I said earlier, the driver to use in VFP is Ansi drivers, as it only depends on the general nature of the client, and VFP is no Unicode client. If you use that, any UTF8 string still comes in as garbage in parts that are not in the lower 128 characters where UTF8 and Latin-1 or codepage 1252 are the same, but can still be passed on to UTF8 capable controls or the Webbrowser to be seen as the same UTF8 characters showing up in MySQL Workbench, for example.

So gain, use ANSI drivers. I guess that solves several problems, if not all.

Chriss

 

[dylim]

Hi Chris,

1. I didn't know that we do not need to do explicit locking in MySQL anymore. Thanks.

2. Guess what? I changed:

CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci -->>> which is the MySQL 8 default
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci -->>> to this


It is now working. Very strange.

 

[Chris Miller]

I don't see a big difference in both. ai means accent insensitivity, ci is case insensitivity and both play a role in comparisons and sorting. Ai means e, è, é, ê are all sorted together whereas the accents come after z in ASCII encoding, for example. But unicode also is about such sorting rules.

I have still the strongest feeling you have some unallowed byte combinations in your utf8 data which cause problems in some cases. And for anything you want to be equal bit by bit you bettter use binary columns.

On the other side: congratulations to have got it going again.

Chriss

 

[dylim]

Anyways, the migration journey continues!

Thanks as always Chris!

Will keep you posted!