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

 

[Mike Lewis]

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 here on Tek tips seems to be quite active.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[dylim]

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.

 

[Nigel Gomm]

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

odbc options

hth

n

 

[Chris Miller]

MySQL ODBC documentation isn't very specific on Error 500:

https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-reference-errorcodes.html

"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

 

[dylim]

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

 

[dylim]

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

 

[atlopes]

Dylim,

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

 

[Chris Miller]

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

 

[dylim]

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.

 

[Chris Miller]

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

 

[dylim]

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?

 

[Mike Lewis]

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

 

[dylim]

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...

 

[Mike Lewis]

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

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

And to answer a couple of your other questions:

1. Could user be a reserved word?

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

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

 

[Chris Miller]

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

 

[dylim]

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?

 

[Chris Miller]

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

 

[dylim]

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?