Bug #120484 The number of attributes is larger than the number of attribute values provided

• Submitted: 16 May 8:00
• Modified: 16 May 10:01
• Reporter: Hans-Joachim Ballin
• Status: Open
• Category: Connector / ODBC
• Severity: S3 (Non-critical)
• Version: 9.7
• OS: Windows (Pro)
• CPU Architecture: x86
• Tags: ODBC

[16 May 8:00] Hans-Joachim Ballin

Description:
I'm using AIVEN.com managed service with MySQL 8.0.45
By accessing from frontend (MS Access database) to the backend side all works fine so far, except the deletion of data records ends in an error page.
The error message is as follows: "The number of attributes is larger than the number of attribute values provided"
By performing the DELETE statement (grabbed from ODBC log file) via "MySQL Workbench 8.0 CE" the data record was successfully deleted.

I found this incient, but it was just closed without a reoution:
Bug #108027; The number of attributes is larger than the number of attribute values provided.

The SQL string from log file is:(replace holder were changed from "?" to real value to perform the test):
DELETE FROM DLDB.`tbl_Personal` WHERE `ID` = 1265 AND `Anrede` IS NULL AND `Vorname` IS NULL AND `HausNr` IS NULL AND `PLZ` IS NULL AND `Ort` IS NULL AND `Vorort` IS NULL AND `Telefon` IS NULL AND `Mobiltelefon` IS NULL AND `Email` IS NULL AND `Anmeldung per Mail bestätigt` IS NULL AND `Teilnahme per Mail bestätigt` IS NULL AND `Newsletter` IS NULL AND `Uebungsstunde` IS NULL AND `Warteliste` IS NULL AND `Auswärtige` IS NULL AND `Anmeldetag` IS NULL AND `Sprechstunde` IS NULL AND `Kursdatum` IS NULL AND `Von` IS NULL AND `Bis` IS NULL AND `ErstesOderZweitesHalbjahr` IS NULL AND `ErstelltAm` IS NULL AND `ErstelltDurch` IS NULL AND `LetzteAenderungAm` IS NULL AND `LetzteAenderungDurch` IS NULL AND `GeloeschtAm` IS NULL AND `GeloeschtDurch` IS NULL AND `Archivieren` IS NULL AND `OutStrasse` IS NULL AND `OutHausnummer` IS NULL AND `outPLZ` IS NULL AND `OutOrt` IS NULL AND `GesendetVon` IS NULL AND `MailAnfrageGesendet` IS NULL AND `MailTeilnahmeGesendet` IS NULL AND `Berater` IS NULL AND `Raum` IS NULL AND `Archivierung` IS NULL AND `DSVGOMuendlich` IS NULL AND `DSVGOSchriftlich` IS NULL AND `Geraete` IS NULL AND `Anmeldefrist` IS NULL AND `BestaetigtAm` IS NULL AND `AngemeldetAm` IS NULL AND `TDBMeldung` IS NULL AND `FindetKursStatt` IS NULL;

How to repeat:
MySQL DB and table with approx. 60 Columns.
The same on MS Access side acting as Front end.
For the connectivity task use an ODBC driver.
The backend db contains in this stage just approx. 70 data records.

The below original LOG snippet is...
1778912161:SELECT `ID`,`Anrede`,`Vorname`,`Nachname`,`Strasse`,`HausNr`,`PLZ`,`Ort`,`Vorort`,`Telefon`,`Mobiltelefon`,`Email`,`Notizen`,`Anmeldung per Mail bestätigt`,`Teilnahme per Mail bestätigt`,`Newsletter`,`Uebungsstunde`,`Warteliste`,`Auswärtige`,`Anmeldetag`,`Sprechstunde`,`Kursdatum`,`Von`,`Bis`,`ErstesOderZweitesHalbjahr`,`ErstelltAm`,`ErstelltDurch`,`LetzteAenderungAm`,`LetzteAenderungDurch`,`GeloeschtAm`,`GeloeschtDurch`,`Archivieren`,`OutStrasse`,`OutHausnummer`,`outPLZ`,`OutOrt`,`GesendetVon`,`MailAnfrageGesendet`,`MailTeilnahmeGesendet`,`Berater`,`Raum`,`Archivierung`,`DSVGOMuendlich`,`DSVGOSchriftlich`,`Geraete`,`Anmeldefrist`,`BestaetigtAm`,`AngemeldetAm`,`TDBMeldung`,`FindetKursStatt`  FROM `tbl_Personal`  WHERE `ID` = ?;
1778912161:ssps has been executed;
1778912161:query has been executed;
1778912161:Using prepared statement;
1778912161:Using prepared statement;
1778912161:DELETE FROM `tbl_Personal` WHERE `ID` = ? AND `Anrede` IS NULL AND `Vorname` IS NULL AND `HausNr` IS NULL AND `PLZ` = ? AND `Ort` IS NULL AND `Vorort` IS NULL AND `Telefon` IS NULL AND `Mobiltelefon` IS NULL AND `Email` IS NULL AND `Anmeldung per Mail bestätigt` = ? AND `Teilnahme per Mail bestätigt` = ? AND `Newsletter` IS NULL AND `Uebungsstunde` IS NULL AND `Warteliste` IS NULL AND `Auswärtige` IS NULL AND `Anmeldetag` IS NULL AND `Sprechstunde` IS NULL AND `Kursdatum` IS NULL AND `Von` IS NULL AND `Bis` IS NULL AND `ErstesOderZweitesHalbjahr` IS NULL AND `ErstelltAm` IS NULL AND `ErstelltDurch` IS NULL AND `LetzteAenderungAm` = ? AND `LetzteAenderungDurch` = ? AND `GeloeschtAm` IS NULL AND `GeloeschtDurch` IS NULL AND `Archivieren` IS NULL AND `OutStrasse` IS NULL AND `OutHausnummer` IS NULL AND `outPLZ` IS NULL AND `OutOrt` IS NULL AND `GesendetVon` IS NULL AND `MailAnfrageGesendet` = ? AND `MailTeilnahmeGesendet` = ? AND `Berater` IS NULL AND `Raum` IS NULL AND `Archivierung` IS NULL AND `DSVGOMuendlich` IS NULL AND `DSVGOSchriftlich` IS NULL AND `Geraete` IS NULL AND `Anmeldefrist` IS NULL AND `BestaetigtAm` IS NULL AND `AngemeldetAm` IS NULL AND `TDBMeldung` IS NULL AND `FindetKursStatt` IS NULL;
1778912214:COMMIT;

[16 May 10:01] Hans-Joachim Ballin

I found a workaround (but is is just a workaround)

I used a new event with pure VBA code instead of the common event handling in my FE MS Access database.
I added the following code:

Private Sub Befehl856_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim sql As String
    Dim criteriaValue As Long 
    
On Error GoTo ErrHandler
    
    criteriaValue = Me.ID
    
    sql = "DELETE FROM tbl_Personal WHERE ID = [pID];"
     
    Set db = CurrentDb

    Set qdf = db.CreateQueryDef("", sql)
    qdf.Parameters("[pID]") = criteriaValue

    qdf.Execute dbFailOnError
    
    If Not qdf Is Nothing Then Set qdf = Nothing
    If Not db Is Nothing Then Set db = Nothing
    
    Exit Sub
    
ErrHandler:
    MsgBox ("Shit happens!")
    
End Sub

As outlined in the code snippet the SQL string is much shorter than automatically generated SQL string from MS Access event handler.