[MDEV-24806] Inconsistent records after upgrade to MariaDB 10.3.23 Created: 2021-02-08  Updated: 2021-04-25  Resolved: 2021-04-25

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 10.3.23
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Agus Syafaat Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: need_feedback
Environment:

AWS RDS for MariaDB


 Description   

We have some records issue after upgrade to MariaDB 10.3.23 (AWS RDS). The column type for the filter is an integer for the table. If we check the records without trim, the records only 14 rows:

MariaDB [db_talenta]> select count(1) from table_a where company_id=3805;
 
+----------+
 
| count(1) |
 
+----------+
 
|       14 |
 
+----------+
 
1 row in set (0.01 sec)

While if we trim the records, the records are 15 rows.

MariaDB [db_talenta]> select count(1) from table_a where trim(company_id)=3805;
 
+----------+
 
| count(1) |
 
+----------+
 
|       15 |
 
+----------+
 
1 row in set (0.02 sec)

There seems any character on the company_id columns, while it's an integer. If I check the length of the columns, look normal as shown below:

MariaDB [db_talenta]> select length(company_id) from table_a where trim(company_id)=3805;
 
+--------------------+
 
| length(company_id) |
 
+--------------------+
 
|                  4 |
 
|                  4 |
 
|                  4 |
 
|                  4 |
 
|                  4 |
 
|                  4 |
 
|                  4 |
 
|                  4 |
 
|                  4 |
 
|                  4 |
 
|                  4 |
 
|                  4 |
 
|                  4 |
 
|                  4 |
 
|                  4 |
 
+--------------------+
 
15 rows in set (0.02 sec)
 
 
 
MariaDB [db_talenta]> select length(company_id) from table_a where company_id=3805;
 
+--------------------+
 
| length(company_id) |
 
+--------------------+
 
|                  4 |
 
|                  4 |
 
|                  4 |
 
|                  4 |
 
|                  4 |
 
|                  4 |
 
|                  4 |
 
|                  4 |
 
|                  4 |
 
|                  4 |
 
|                  4 |
 
|                  4 |
 
|                  4 |
 
|                  4 |
 
+--------------------+
 
14 rows in set (0.01 sec)

Default sql_mode is NO_ENGINE_SUBSTITUTION while on the old RDS database (MariaDB 10.0), default sql_mode is empty. I can't change the sql_mode to be empty in upgraded RDS for MariaDB (which is expected in >= 10.2).

 Comments   
Comment by Elena Stepanova [ 2021-03-28 ]

Did you try to run CHECK TABLE?

check table table_a extended;

What does this return?

select hex(company_id), hex(trim(company_id)) from table_a where trim(company_id)=3805;

Is it reproducible if you create a new table and run the same there? E.g. 

create table table_b like table_a;
 
insert into table_b select * from table_a;
 
select count(1) from table_b where company_id=3805;
 
select count(1) from table_b where trim(company_id)=3805;

or even

create table table_b as select company_id from table_a where trim(company_id)=3805;
 
select count(1) from table_b where company_id=3805;
 
select count(1) from table_b where trim(company_id)=3805;

Generated at Thu Feb 08 09:32:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.