| Bug #114470 | returned data that does not match expected data length for column | ||
|---|---|---|---|
| Submitted: | 25 Mar 5:48 | Modified: | 19 Nov 10:59 |
| Reporter: | Bruce Young | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 8.3.0 | OS: | Windows (Windows Server 2022 Datacenter) |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[25 Mar 5:48]
Bruce Young
[11 Apr 4:12]
Bruce Young
This may be useful: mysql> desc cqu_application_cstm; +--------------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+--------------+------+-----+---------+-------+ | id_c | char(36) | NO | PRI | NULL | | | area_of_study_c | varchar(100) | YES | | NULL | | | lifecycle_c | varchar(100) | YES | | NULL | | | product_c | varchar(100) | YES | | NULL | | | school_graduation_year_c | varchar(100) | YES | | NULL | | | success_flag_c | tinyint(1) | YES | | 0 | | | accepted_as_of_date_c | datetime | YES | | NULL | | | admitted_as_of_date_c | datetime | YES | | NULL | | | applicant_as_of_date_c | datetime | YES | | NULL | | | converted_as_of_date_c | datetime | YES | | NULL | | | likely_intake_c | varchar(100) | YES | | NULL | | | offerred_as_of_date_c | datetime | YES | | NULL | | | prospect_as_of_date_c | datetime | YES | | NULL | | | cancelled_as_of_date_c | datetime | YES | | NULL | | | cqu_event_id_c | char(36) | YES | | NULL | | | inactive_as_of_date_c | datetime | YES | | NULL | | | lead_phase_c | varchar(100) | YES | | NULL | | | lead_step_c | varchar(100) | YES | | NULL | | | source_c | varchar(100) | YES | | NULL | | | prospect_status_c | varchar(100) | YES | | NULL | | | status_reason_c | varchar(100) | YES | | NULL | | | prospect_status_date_c | datetime | YES | | NULL | | | marketo_check_status_c | varchar(100) | YES | | NULL | | | marketo_id_c | varchar(255) | YES | | NULL | | | ln_testing_date_c | datetime | YES | | NULL | | | ln_result_1_c | varchar(255) | YES | | NULL | | | ln_result_2_c | varchar(255) | YES | | NULL | | | send_to_bksb_c | tinyint(1) | YES | | 0 | | | bksb_complete_c | tinyint(1) | YES | | 0 | | | steps_testing_sent_c | date | YES | | NULL | | | steps_testing_return_c | date | YES | | NULL | | | steps_interview_c | date | YES | | NULL | | | steps_study_plan_c | tinyint(1) | YES | | 0 | | | steps_fast_track_c | varchar(100) | YES | | NULL | | | steps_computer_type_c | varchar(100) | YES | | NULL | | | send_to_bksb_date_c | datetime | YES | | NULL | | | utm_source_c | varchar(255) | YES | | | | | utm_medium_c | varchar(255) | YES | | | | | utm_campaign_c | varchar(255) | YES | | | | | utm_content_c | varchar(255) | YES | | | | +--------------------------+--------------+------+-----+---------+-------+ 40 rows in set (0.01 sec)
[22 Apr 12:20]
MySQL Verification Team
Hello Bruce Young, Thank you for the bug report. Discussed your issue with ODBC developer and concluded that length of the character is probably 2 bytes, thus the max length is 72 = 36*2 The column in question is this: cqu_event_id_c | char(36) It cannot be a variable length because char is not varchar. Hence, the char(N) needs to be padded with the spaces. The data is evidently shorter, so it does not match the exact expected length of 72. There is an option to do the padding for situations like this: 1. Via driver GUI dialog the following option has to be checked: Details >> Cursors/Results >> [x] Pad CHAR to full length with space 2. Add PAD_SPACE=1 directly to the ODBC connection string Let us know if it resolved the problem. Regards, Ashwini Patil
[29 Apr 6:15]
Bruce Young
Thanks for the update, but the variability issue of this data has been present for years, but the failure to extract only started this year. Further to this I altered the table on the SQL server side to be varchar(255) and the error occurs unchanged. Details of the variable data in the table: mysql> select distinct char_length(cqu_event_id_c), count(char_length(cqu_event_id_c)) from cqu_application_cstm group by char_length(cqu_event_id_c); +-----------------------------+------------------------------------+ | char_length(cqu_event_id_c) | count(char_length(cqu_event_id_c)) | +-----------------------------+------------------------------------+ | NULL | 0 | | 36 | 475014 | | 0 | 10088 | | 23 | 59 | | 13 | 108 | | 35 | 8 | | 5 | 446 | | 27 | 67 | | 1 | 9 | | 3 | 98 | | 15 | 35 | | 33 | 15 | | 20 | 133 | | 12 | 17 | | 19 | 29 | | 9 | 1 | +-----------------------------+------------------------------------+ 16 rows in set (0.76 sec) I don't doubt there is a data issue here, but don't know how to find it with the provided error.
[23 May 6:52]
MySQL Verification Team
Hello Bruce Young, Thank you for the details. However this is not enough to reproduce the issue at our end. Can you please let us know if simply running this query causes the error (without INSERT INTO sugar.CQU_APPLICATION_CSTM_TEMP ...)? SELECT * from openquery(SUGARCRM,'select * from cqu_application_cstm'); Also, please provide CREATE TABLE statement for `cqu_application_cstm`. Does it fails with the empty table or a particular record should be present? Regards, Ashwini Patil
[24 Jun 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[25 Jul 4:27]
Bruce Young
Apologies for the delay. For the select statement without the insert it starts to work and displays rows. After about 55 seconds it fails with this error: Msg 7347, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' for linked server 'SUGARCRM' returned data that does not match expected data length for column '[MSDASQL].cqu_event_id_c'. The (maximum) expected data length is 72, while the returned data length is 68. Completion time: 2024-07-25T14:23:27.9333404+10:00 ---------------------------------- It creates the table with correct structure without error. The table creation runs with zero rows returned without issue. This still does not help me find the row or rows where the data is triggering this.
[25 Jul 4:31]
Bruce Young
The select returns over 70000 rows before the error occurs. The source table in the Sugar MySQL database has 1422768 rows at this time.
[26 Jul 12:38]
MySQL Verification Team
Hello Bruce Young, Thank you for the details. Could you please provide create table statement? In order to continue we need the DDL for the table from MySQL Server (not for the linked table in SQL Server) Regards, Ashwini Patil
[30 Jul 1:05]
Bruce Young
from show create table cqu_application_cstm; CREATE TABLE `cqu_application_cstm` ( `id_c` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `area_of_study_c` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `lifecycle_c` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `product_c` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `school_graduation_year_c` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `success_flag_c` tinyint(1) DEFAULT '0', `accepted_as_of_date_c` datetime DEFAULT NULL, `admitted_as_of_date_c` datetime DEFAULT NULL, `applicant_as_of_date_c` datetime DEFAULT NULL, `converted_as_of_date_c` datetime DEFAULT NULL, `likely_intake_c` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `offerred_as_of_date_c` datetime DEFAULT NULL, `prospect_as_of_date_c` datetime DEFAULT NULL, `cancelled_as_of_date_c` datetime DEFAULT NULL, `cqu_event_id_c` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `inactive_as_of_date_c` datetime DEFAULT NULL, `lead_phase_c` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `lead_step_c` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `source_c` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `prospect_status_c` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `status_reason_c` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `prospect_status_date_c` datetime DEFAULT NULL, `marketo_check_status_c` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `marketo_id_c` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `ln_testing_date_c` datetime DEFAULT NULL, `ln_result_1_c` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `ln_result_2_c` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `send_to_bksb_c` tinyint(1) DEFAULT '0', `bksb_complete_c` tinyint(1) DEFAULT '0', `steps_testing_sent_c` date DEFAULT NULL, `steps_testing_return_c` date DEFAULT NULL, `steps_interview_c` date DEFAULT NULL, `steps_study_plan_c` tinyint(1) DEFAULT '0', `steps_fast_track_c` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `steps_computer_type_c` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `send_to_bksb_date_c` datetime DEFAULT NULL, `utm_source_c` varchar(255) DEFAULT '', `utm_medium_c` varchar(255) DEFAULT '', `utm_campaign_c` varchar(255) DEFAULT '', `utm_content_c` varchar(255) DEFAULT '', PRIMARY KEY (`id_c`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED
[5 Sep 0:46]
Bruce Young
Is there anything else I can provide to be of assistance here?
[10 Oct 1:24]
Bruce Young
I provided the additional required data at the end of July. It is now October. Almost 2.5 months without any update. If I can be of more assistance, happy to do that. I just need a way forward to extract this data.
[12 Nov 0:26]
Bruce Young
I provided the last update requested at the end of July. It is now 3 and a half months since then. No updates, no requests for further details from me. Status is still "Analyzing". I need a solution. I am happy to provide any additional support to solving this issue. What can I do to move this forward?
[12 Nov 2:10]
Bogdan Degtyariov
This is not a trivial issue to repeat. We tried generating random 100,000 rows for the table cqu_application_cstm, but the queries worked: (0 rows affected) (104001 rows affected) Completion time: 2024-11-02T13:04:35.2484591+11:00 Does the issue happen on the same row? Can this row be identified and isolated? If we know the exact data, which causes the issue it will help to reproduce and fix the problem.
[19 Nov 1:28]
Bruce Young
Does the issue happen on the same row? I do not know. Probably but can't guarantee. Can this row be identified and isolated? Probably, but not a trivial task. Is there any available "RowID" type selecting that could help me find it? If we know the exact data, which causes the issue it will help to reproduce and fix the problem. Totally agree with this one, but finding it is the hard bit. Any assistance you could provide would be appreciated.
[19 Nov 4:34]
Bruce Young
ok, found a row that is causing the issue. Have created a table with just that row and it also fails to select across the link. I've exported that table and will attach the file next. Please try with that.
[19 Nov 10:59]
Bogdan Degtyariov
Hi Bruce, Thank you for providing the row data. I managed to repeat the problem with the Unicode version of the driver. With ANSI it worked, which could be a temporary workaround for you: drop table if exists testing..CQU_APPLICATION_CSTM_TEMP ; drop table if exists testing..CQU_APPLICATION_CSTM_OLD ; SELECT * INTO testing..CQU_APPLICATION_CSTM_TEMP from openquery(MYSQL_ODBC_9_1_0_ANSI,'select * from bruce_testing_two limit 0') ; INSERT INTO testing..CQU_APPLICATION_CSTM_TEMP SELECT * from openquery(MYSQL_ODBC_9_1_0_ANSI,'select * from bruce_testing_two') ; (0 rows affected) (1 row affected) Completion time: 2024-11-19T21:50:56.0358517+11:00 This bug is verified.
