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

 

[atlopes]

Dylim,

Can you try to change [highlight #FCE94F][tt]?user.id[/tt][/highlight] into [highlight #FCE94F][tt]?(user.id)[/tt][/highlight]? Does it make a difference?

 

[dylim]

Hi ATLOPES,

Can you try to change ?user.id into ?(user.id)? Does it make a difference?

Is that how you do it ever since?

 

[dylim]

Hi ATLOPES,

Can you try to change ?user.id into ?(user.id)? Does it make a difference?

Same result. Same error. If I do not get any breakthrough. I guess I have no choice but to revert back to 5.7.27.

BTW, I also upgraded to the latest (8.0.29 from 8.0.28) in the hope this might be resolved.

#DamnYouMySQL80

 

[Chris Miller]

Brackets aren't necessary to use an alias.field OR object.proeprty parameter.

If you assume it's not the value but the syntax of parameterization, then copy user.id into a variable and do the parameterzation that can't fail for syntax reasons ?m.variable

I still think it's the value that causes the error. Did you get any closer to finding out the value when the query fails? Debugging, Errorlogging. I got no reply to this, still.

Chriss

 

[atlopes]

When the error pops up, what is the result of

? TYPE("user.id")
? VARTYPE(user.id)
? ISNULL(user.id)
? EMPTY(user.id)
? EOF("user")

 

[Chris Miller]

It could also be scope or the order of CA execution. Since you say your CAs are in their own DE, well, is user availble in there? And is CAUser executed before CAUserRights? And does the CAUser cursoradapter use the alias USER at all?

Chriss

 

[dylim]

Hi Chris and atlopes,

I hope this is finally the cure!

I dropped all the INDEXES and recreated them. Done! It is now working like before!

I think it is because the existing INDEXES were built on deprecated UTF8MB3, which is one byte shorter than MySQL 8 default UTF8MB4.

@Chris, I didn't bother to do Error Logging as my gut has been on this UTF issue. Yesterday, I converted the tables concerned to the new UTF8MB4, not knowing that the INDEXES should also be dropped and created.

Many thanks to all of you, most specially @Chris and @atlopes!

I pray there won't be anymore future gotchas.

Long Live the Fox!

 

[Chris Miller]

I don't get how that would cause that error, I would expect the index to fail finding a record, perhaps, but why would where field=value then error as if you call a function with too few parameters?

I'd care more for error handling and debugging.

Chriss

 

[dylim]

Chris,

Each and everytime, the error always occurs when the caUserRights CURSORFILL() is invoked.
When I removed the WHERE USERID=?USER.ID, it works fine, although it fetches all the rights records of all users.
Which got me to think:

Having this error:
"[MySQL][ODBC 8.0(w) Driver][mysqld-8.0.28]The number of attributes is larger than the number of attribute values provided"

and knowing that there is a one byte difference in length between UTF8MB3 and UTF8MB4, maybe that is what the message meant?

 

[Chris Miller]

That's not called attribute, that's the byte length or size of a codepoint of a codepage/encoding. And uTF8 has varying size codepoints, both in utf8mb3 and utf8mb4. The difference is support for 4 byte codepoints, which uft8mb3 didn't include, but the UTF definition in itself is straight forward about what encodes the length of a codepoint in the codepoint bytes themselves. So actually the characters you had in utf8b3 don't change at all when you switch to utf8mb4.

The sentence talks about number of attributes and number of attribute VALUEs.
So something has 3 attributes, but the command only provides 2 attribute values where 3 are needed. That's not talking about a missing byte. There only would be a byte missing, if you had a 4 byte character, but when all users only needed utf8mb3 before, how would a 4-byte codepoint come up?

So in short I don't see how that would have anything to do with string comparisons and codepages.

In MySQL the term attributes appears in table definitions. Columns can have attributes..

You have a point, of course: Your change made it work. I think it's one of those error messages that come from so deep, that you actually can't relate them to the command on the surface level, the query in this case.

I searched for the error number and message in conjunction with utf8mb3 utf8mb4 and uft8 and didn't find anything but this tek-tips thread, besides documentation about codepages lacking the mention of this error. No idea. But I will remember when upgrading a database to remember recreating the indexes.

Well, I usually do create new databases and import data, I don't try to upgrade an existing database to a new level or address it with a newer MySQL or MSSQL version. Too many bad experiences overall with all kind of things breaking when you don't migrate the data but hope everything is downward compatible.

Think about the product developers, they surely do intensive tests on their new product version, but can't check every combination with any older version. Especially with major version changes, see the MAJOR new version as a big improvement making bigger steps in the codebase and also storage of data etc. Especially when the version even skips 6 and 7. So use it from ground up, export/import your data, don't use any old files in the new environment.

Chriss

 

[dylim]

Chris,

To be honest, I must just be lucky, to even try dropping each index and recreating them and made it work. Thanks to that error message about attributes and attribute values, which as you said, has even no direct relation or logic to what I just did to the INDEXES.

You're right. I have yet to find a link that discusses this particular error.

Now, my next quest is.. to look for some app or routine to automate conversion of UTF8MB3 to UTF8MB4, and dropping and recreating INDEXES.

Thanks Chris.

 

[Chris Miller]

to look for some app or routine to automate conversion of UTF8MB3 to UTF8MB4, and dropping and recreating INDEXES

In my opinion the simplest would be export all data into UTF8 CSV, for example, and reimport into a newly created database with UTF8MB4..

But good luck with finding a simpler solution. I do think UTF8MB3 is a complete subset of UTF8MB4 and doesn't require changes, UTF8MB4 just supports more UTF8 codepoints, including the ones using 4 bytes. But your index experience show there is something going on. Perhaps it has nothing to do with encoding changes but with the way indexes are stored.

Again, if you just add everything to a new empty database that might be best for its storage including the indexes. The usual tools already support that, i.e. MySQL Dump. Even if it would use utf8mb only and not 3 or 4, you could change that code, as it literally runs on a new empty database server and creates everything from scratch.



Chriss

 

[dylim]

Chris,

In my opinion the simplest would be export all data into UTF8 CSV, for example, and reimport into a newly created database with UTF8MB4..

Yeah, that is the simplest so far. Good thing though, I am onto a new project now, so it's really entirely from scratch.

As for my existing client installs, is it a good idea to offer this kind of upgrade (5 to 8) service? As if they would appreciate it enough to pay for it..

 

[Chris Miller]

My experience with upgrades is, the stakeholder can't raise funds to spend on version upgrades just for the version upgrade with no obvious other benefits. I mean, on the other side it's the reason there is still Foxpro software running at all.

Chriss

 

[dylim]

Chris,

I guess I spoke too soon!

I have a simple table AUTOINC, with columns TABLEID and LASTNUMBER. I use it, as the name implies, for my own autonumbering functionality.

This table is present in all my databases. I now have a CA class AUTOINC with a single method:

[pre]
GetNumber( tcTableID )

IF This.LockRecord( tcTableID )
lnLastNumber = This.GetFieldValue( "LastNumber" )
lnLastNumber = lnLastNumber + 1
This.SetFieldValue( "LastNumber", lnLastNumber )
llOk = TABLEUPDATE( 1, .T., This.Alias ) && if we use Save() it will commit
ELSE
llOk = .F.
ENDIF

IF NOT llOk
lnLastNumber = -1
ENDIF

RETURN lnLastNumber[/pre]


On the new database I created for the new app I told you about, IT DOES NOT SAVE!

It does on the old AUTOINC tables with UTF8MB3!

WTF! Agony does not seem to end!

 

[Chris Miller]

What method has that code?

One of the hook methods, BefoeInsert, for example? Or what else?

There are no CA methods LockRecord or SetFieldValue. So, I guess this is the only method with code in that CA class, but it's based on another CA class, you have to provide code from the class hiararchy, there might even be several levels. And I guess in these parent classes, you need some fixes to work with MySQL8. But without seeing that, you can't expect advice.

I can only point out a generally different solution:
I solved all pk issues using GUIDS longer ago, see

https://dev.mysql.com/blog-archive/mysql-8-0-uuid-support/

on how to work with this type of Pk in MySQL 8.

Chriss

 

[dylim]

Chris,

LockRecord() is simply a MySQL statement issued:

[pre]LPARAMETERS tcTableID

SELECT * FROM autoinc WHERE tableid = tcTableID LIMIT 0, 20 FOR UPDATE[/pre]


SetFieldValue() is just my 'fancy' way of REPLACING a field:

[pre]LPARAMETERS tcField, tuValue

LOCAL lcAalis

lcAlias = ALIAS()

SELECT ( This.Alias )

REPLACE ( tcField ) WITH tuValue

IF NOT EMPTY( lcAlias )
SELECT ( lcAlias )
ENDIF[/pre]

 

[Chris Miller]

Btw, I know the issue with CAs and also views even with VFP native DBF autinc, so that's a problem limited to MySQL.

There are ways to solve this with the help of AfterInsert. In the native DBF case you simply cann access table.idfield after inserts, but with remote tables you need to fetch it actively from the remote db. And you can prevent this need, as you did, by generating the id with a sequence and thus knowing it from there. You shouldn't need to lock a sequence though. I see a simpler way to do this defining a MYSQL pseudo sequence in your db with the help of LAST_INSERT_ID():

For initialization:

CREATE TABLE sequence (id INT NOT NULL);
INSERT INTO sequence VALUES (0);

For usage:

UPDATE sequence SET id=LAST_INSERT_ID(id+1);
SELECT LAST_INSERT_ID();

That is recommended in version 5.x and 8.0 (current).

This, of course is a table with only one sequence not a sequence per table. You might use it to generate ids for all tables, though, so ids are unique within a database, not just within a table.

Chriss

 

[dylim]

Chris,

As an addendum, the same exact code is used with all my database projects.

It just does not work only with this new database (MySQL 8) that I have create with UTF8MB4.

It has exactly the same name and structure. It is just two columns - TABLEID CHAR(20) and LASTNUMBER INT... only the difference is UTF8MB3 VS UTF8MB4..

Crazy right?!

 

[Chris Miller]

LPARAMETERS tcTableID

SELECT * FROM autoinc WHERE tableid = tcTableID LIMIT 0, 20 FOR UPDATE

1. Is that the literal code of the LockRecord method? Or did you just strip off the code to use the CA connection to execute this query in MySQL?
2. Why LIMIT 0,20, there should only be 1 record for each tableiid, so LIMIT 0,1 (it's not explaining why the tableupdate fails).

If you do this with SQLEXEC I guess you can't expect MYSQL to identify an update coming in via TABLEUPDATE to be the valid owner of the locked record. This has to come via the same connection, at least, if not even from a single call, so you'd rather not load the value into a VFP alias and then update it from there, you do it fully in MySQL and then fetch the new value for the table. So instead of SELECT FOR UPDATE, just do an UPDATE, it seems overcomplicated to do what you do regardless of MySQL version.

Chriss