×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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

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

Very difficult to diagnose this without seeing the code that is firing the error. But my guess is that it is an INSERT statement, and the number of values that you are listing in the VALUES clause is more than the number of fields listed. Perhaps the ealier version wasn't fussy about that error, with the new version being more strict.

But in any case, this is clearly a MySQL issue, not a VFP issue. You might get a faster answer by posting the question in a MySQL-related forum. But if you do, be sure to post the actual line of code that is causing the error.

Update: forum436: MySQL here on Tek tips seems to be quite active.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

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

(OP)
Hi Mike!

Thanks for such swift response!

First off, here is what I usually do. I have a Connection Manager class which I instantiated, set the connection info such as server, user, password such as the ff:

driver=MySQL ODBC 8.0 Unicode Driver
server=localhost
port=3306
uid=root
pwd=12345
database=mydb

Upon getting a connected return value, I now instantiate my business object which is actually a dataenvironment class with all the cursoradapter objects in it. Upon init, my business object would then loop thru each cursoradapter object and create the cursors.

There are instances wherein it is successful, but there are certain business objects that simple won't, and generate the ODBC error I mentioned.

BTW, getting that ODBC error also renders you disconnnected as well.

So far, I don't see any patterns, but, if this business object won't, it really won't. Therefore, it is not a 'random' behaviour.

Thanks.

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



try bigint to int (16384) and big result set (8) options in your connection string

odbc options

hth

n

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

MySQL ODBC documentation isn't very specific on Error 500:
https://dev.mysql.com/doc/connector-odbc/en/connec...

"General Warning"

From the text message, well what does that mean? Attributes could be parameters you pass in to a stored proc that is used by one of the CAs. Or you pass in too few values into an insert.

If I was in your shoes, I'd already be in the debugger and checking CA one by one where that error occurs, then look into its SelectCmd.

Chriss

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

(OP)
Hi Nigel,

This was already included in my connection string.. OPTION=16384

I was really hoping that will solve it.. but unfortunately it did not..

Thanks

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

(OP)
Hi Chris,

I already checked every CA I have. What is weird is, there is no pattern.

I have parent-child CAs that work; there are some that generates that error.

Googling for "The number of attributes is larger than the number of attribute values provided" does not yield any meaningful results as well.

Jeeezzz...

Thanks

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

Dylim,

Can you post the underlying statements that the CAs use to fetch or update data?

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

I second atlopes,

more important would be to have a reproducable situation.

Or wait for the next error, but with specific error handling in place. In that situation, when the error occurs in the loop iterating the CAs, do the cursorfill (I assume that's what you call per CA) in a TRY..CATCH block and in the CATCH block then log the CAs SelectCmd and all memory variables or properties that go in the query as parameters, even better set a breakpoint there and then have a live look into it.

Chriss

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

(OP)
Hi atlopes and Chris,

I have two cursoradapters: caUser and caUserRights.

caUser
SelectCmd: select * from user
CursorSchema: CODE C(20), DESCRIPTOR C(50), PASSWORD C(20), ADMIN I, ID C(32)

caUserRights
SelectCmd: select a.userid, a.moduleid, b.descriptor as modulename, b.menugrpid, b.ordinal, b.type, a.data, a.id from userright a left join module b on a.moduleid=b.id where a.userid=?user.id order by
menugrpid, ordinal
CursorSchema: USERID C(32), MODULEID C(20), MODULENAME C(50), MENUGRPID C(15), ORDINAL I, TYPE I, DATA C(10), ID C(32)

Both are inside a DataEnvironment class.

Upon Init() of this DE class, it calls an OpenTables() method, which basically does CursorFill() of each cursoradapter.

Curiously, if I omit the underlined part of caUserRights SelectCmd, it opens normally.

If I put it back, the same error repeats, and kicks me out of the connection.


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

What about corner cases?

What if user.id is null or a wrong type?

Did this change from DBF to remote backend? Because if you do a CA with Select * From User (caUser), just like a view, it does open User and query its result into anpother workarea.

The second ca Seems to make use of user.id, the field from user. But change that to a remote backend the user table isn't open. This is simple undefined, then. Or gets to an object named user or any workarea by chance having tha alias user.

Again, if you'd set a breakpoint at the cursorfill you can inspect the values that are used as parameters and get to know why they break the query.

Chriss

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

(OP)
Chris,

What about corner cases? -- What is a corner case?

What if user.id is null or a wrong type? -- both User.ID and UserRight.UserID are of Char(32) and have an empty string as default. The module table has no records (this worked all the time though).

Here are my thoughts/questions:

1. Could user be a reserved word?
2. Is the use of "?" in ?user.id no longer available? If I try other CAs like an Invoice Header and Invoice Details, it works using the "?".
3. I ran the same exact User and UserRight DE in MySQL 5.7.25 and it runs all the time. I have used my class for 15 years already. It is just now moving up to MySQL 8.0.28 that I experienced this.
4. I also have converted the User and UserRight tables to utf8mb4 encoding, thinking it could matter because of the difference in bytes (3 for utf8 vs 4 for utf8mb4). Same crappy result.

P.S. What do you use as data backend with VFP?

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

Quote:

What about corner cases? -- What is a corner case?

It's what most of us call a boundary case. For example, if a permitted value is in the range zero to 255, then 0 would be a boundary case, as would 255 - because they are on the boundary of the range. If a piece of text has a maximum size of 1024 bytes, then text with exactly that length would be a boundary case. Other examples might include a customer with no orders, or an invoice with a net value of zero, and so on.

The point is that you should always allow for this type of case when designing and testing your program.

Does that make sense?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

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

(OP)
Hi Mike,

It's what most of us call a boundary case. For example, if a permitted value is in the range zero to 255, then 0 would be a boundary case, as would 255 - because they are on the boundary of the range. If a piece of text has a maximum size of 1024 bytes, then text with exactly that length would be a boundary case. Other examples might include a customer with no orders, or an invoice with a net value of zero, and so on.

The point is that you should always allow for this type of case when designing and testing your program.

Does that make sense?


Got it sir! That is what I term as limit testing... bigsmile

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

Yes, "limit testing" is another good term for it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

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

And to answer a couple of your other questions:

Quote:

1. Could user be a reserved word?

No (at least, not in VFP). USE and USED are reserved words, but not USER.

Quote:

2. Is the use of "?" in ?user.id no longer available?

The ? command is still available, and works in the same way that it has always done. So if you have a table named User and if that has an column named ID, the command will correctly display the ID on screen or in print.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

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

Well, my idea of corner cases are those that are not in the middle of usual usage. As I gave the example a null value could be a corner case the query fails at.

In unit testing when you do coverage of all branches a tested method could go into, you also have to think about corner cases of parameterization, including unallowed parameters. So it could be that corner of your code, an else branch of an else branch, that was never actually tested.

I actually think user is a system table of MySQL, but I'm saying that from the top of my head without looking it up.

I use MSSQL and MySQL as backends. Also already used MySQL 8 and MariaDB. And some other databases like PostgreSQL.

The ? syntax works with VFP, there's no dependency with the backend. It has to be in scope for the CA and there more possible than some think, like with reports. I had worked for a company that explicityl made parameters private vars or object properties of the CA object to ensure it sees them. You can use vars in local scope of the code that does the CursorFill. One thing about CAs in a DE is that they work automatic, too. Just like you don't need to put USE viewname into the OpenTables method of the DE, Well, that's the major job of the D, to process all the objects in it automatically.

So you might fail at a stage the parameters differ or are not yet initialized. Do you have a general error handler that could break when error 1526 happens, that would be helpful. You then can do RETRY and get to the point that fails, can inspect variables and objects and see how that influences the query and renders it unusable.

Chriss

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

(OP)
Hi Chris,

When you used MySQL 8, you didn't encounter any issues at all with your VFP apps? Kindly share.. or was it seamless from the get go?

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

It was quite seamless, yes. Knowing you need a 32bit ODBC driver, but that's surely not the problem, as you wouldn't get to a connection in the first place.

I have seen some people struggle with driver versions, but I guess I was just lucky not working at the wrong time. I already used older MySQL versions and also used CA.

Chriss

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

(OP)
Chris,

I have been using MySQL 5.7.x with no major issues. There are a few glitches which were quickly resolved, unlike this 8.0.

What version of MySQL are you using?

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

Dylim,

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

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

(OP)
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?

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

(OP)
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

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

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

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

When the error pops up, what is the result of

CODE --> VFP

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

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

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

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

(OP)
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!

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

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

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

(OP)
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?

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

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

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

(OP)
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.

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

Quote (dylim)

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

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

(OP)
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.. ponder

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

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

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

(OP)
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:

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 


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!

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

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-... on how to work with this type of Pk in MySQL 8.

Chriss

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

(OP)
Chris,

LockRecord() is simply a MySQL statement issued:

LPARAMETERS tcTableID

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


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

LPARAMETERS tcField, tuValue

LOCAL lcAalis

lcAlias = ALIAS()

SELECT ( This.Alias )

REPLACE ( tcField ) WITH tuValue

IF NOT EMPTY( lcAlias )
   SELECT ( lcAlias )
ENDIF 

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

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:

CODE

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

For usage:

CODE

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

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

(OP)
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?!

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

CODE --> Lockrecord

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

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

(OP)
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.



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

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

Quote (dylim)

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:

CODE --> MySQL

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:

CODE --> MySQL

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

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

(OP)
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.

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

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

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

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/informatio...)

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

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

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

https://dev.mysql.com/doc/refman/8.0/en/storage-re...

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

Quote (MySQL)

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

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

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

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

(OP)
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.

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

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

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

(OP)
Anyways, the migration journey continues!

Thanks as always Chris!

Will keep you posted! glasses

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close