Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.5.10, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
-
None
-
CentOS 7.9, Docker
Description
I am using MariaDB for several products.
When I inserted data in a table with indexes pre-added, I sometimes did not get the expected search results.
I tried this with Mariadb 10.5.10 on Docker container.
First, start the MariaDB container on Linux (CentOS 7.9).
docker run -p 3306:3306 --name test-mariadb -e MARIADB_ROOT_PASSWORD=12345678 -d mariadb:10.5.10 |
Next, I ran the following on my MacBook.
lobin@Nagisa relief-map % mysql -h192.168.59.109 -uroot -p12345678 < setup.sql
|
mysql: [Warning] Using a password on the command line interface can be insecure. |
lobin@Nagisa relief-map % mysql -h192.168.59.109 -uroot -p12345678 test1 < insert_data.sql
|
mysql: [Warning] Using a password on the command line interface can be insecure. |
lobin@Nagisa relief-map % mysql -h192.168.59.109 -uroot -p12345678 --table test1 < select.sql |
mysql: [Warning] Using a password on the command line interface can be insecure. |
+----+----------+---------------+------+-------------+-------------+---------------------+---------------------+---------------------+
|
| ID | Facility | prefecture_id | ID | Facility_ID | damage_type | comment | regist_date | update_date |
|
+----+----------+---------------+------+-------------+-------------+---------------------+---------------------+---------------------+
|
| 1 | having0 | 27 | NULL | NULL | NULL | NULL | NULL | NULL |
|
| 2 | having1 | 27 | 1 | 2 | 4 | this is latest. | 2019-03-05 00:00:00 | 2019-03-06 00:00:00 |
|
| 3 | having2 | 27 | 2 | 3 | 4 | this is not latest. | 2019-03-05 00:00:00 | 2019-03-06 00:00:00 |
|
+----+----------+---------------+------+-------------+-------------+---------------------+---------------------+---------------------+
|
lobin@Nagisa relief-map %
|
This is not the result I was expecting.
Next, I removed the index created at the end of `setup.sql` and ran `select.sql` again, and got the expected results.
lobin@Nagisa relief-map % mysql -h192.168.59.109 -uroot -p12345678 --table test1
|
mysql: [Warning] Using a password on the command line interface can be insecure. |
Reading table information for completion of table and column names |
You can turn off this feature to get a quicker startup with -A
|
|
|
Welcome to the MySQL monitor. Commands end with ; or \g.
|
Your MySQL connection id is 42 |
Server version: 5.5.5-10.5.10-MariaDB-1:10.5.10+maria~focal mariadb.org binary distribution
|
|
|
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. |
|
|
Oracle is a registered trademark of Oracle Corporation and/or its |
affiliates. Other names may be trademarks of their respective
|
owners.
|
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. |
|
|
mysql> DROP INDEX `for_latest_sort` ON `facility2_damages`
|
-> ;
|
Query OK, 0 rows affected (0.01 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
mysql> ^DBye
|
lobin@Nagisa relief-map % mysql -h192.168.59.109 -uroot -p12345678 --table test1 < select.sql |
mysql: [Warning] Using a password on the command line interface can be insecure. |
+----+----------+---------------+------+-------------+-------------+------------------+---------------------+---------------------+
|
| ID | Facility | prefecture_id | ID | Facility_ID | damage_type | comment | regist_date | update_date |
|
+----+----------+---------------+------+-------------+-------------+------------------+---------------------+---------------------+
|
| 1 | having0 | 27 | NULL | NULL | NULL | NULL | NULL | NULL |
|
| 2 | having1 | 27 | 1 | 2 | 4 | this is latest. | 2019-03-05 00:00:00 | 2019-03-06 00:00:00 |
|
| 3 | having2 | 27 | 3 | 3 | 4 | this is latest2. | 2019-03-06 00:00:00 | 2019-03-05 00:00:00 |
|
+----+----------+---------------+------+-------------+-------------+------------------+---------------------+---------------------+
|
lobin@Nagisa relief-map %
|
After that, I re-added the same index, and the correct search results remained.
It seems that adding the index first and then inserting the data will result in incorrect search results.
I also tried with MariaDB 10.2.38 and the results were the same.
I think It might be related to the fact that I'm trying to reference the index in the correlation subquery.
I tried the same with MySQL 5.7.34 and got the correct results without reindexing.
# start a MySQL container On CentOS
|
docker run -p 3307:3306 --name test-mysql -e MYSQL_ROOT_PASSWORD=12345678 -d mysql:5.7.34 |
lobin@Nagisa relief-map % mysql -h192.168.59.109 -P3307 -uroot -p12345678 < setup.sql
|
mysql: [Warning] Using a password on the command line interface can be insecure. |
lobin@Nagisa relief-map % mysql -h192.168.59.109 -P3307 -uroot -p12345678 test1 < insert_data.sql
|
mysql: [Warning] Using a password on the command line interface can be insecure. |
lobin@Nagisa relief-map % mysql -h192.168.59.109 -P3307 -uroot -p12345678 --table test1 < select.sql |
mysql: [Warning] Using a password on the command line interface can be insecure. |
+----+----------+---------------+------+-------------+-------------+------------------+---------------------+---------------------+
|
| ID | Facility | prefecture_id | ID | Facility_ID | damage_type | comment | regist_date | update_date |
|
+----+----------+---------------+------+-------------+-------------+------------------+---------------------+---------------------+
|
| 2 | having1 | 27 | 1 | 2 | 4 | this is latest. | 2019-03-05 00:00:00 | 2019-03-06 00:00:00 |
|
| 3 | having2 | 27 | 3 | 3 | 4 | this is latest2. | 2019-03-06 00:00:00 | 2019-03-05 00:00:00 |
|
| 1 | having0 | 27 | NULL | NULL | NULL | NULL | NULL | NULL |
|
+----+----------+---------------+------+-------------+-------------+------------------+---------------------+---------------------+
|
lobin@Nagisa relief-map %
|
thanx.
Attachments
Issue Links
- relates to
-
-
- Closed
-