Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Some query records lost when use prepare #3712

Closed
yaoguais opened this issue Jul 11, 2017 · 1 comment
Closed

Some query records lost when use prepare #3712

yaoguais opened this issue Jul 11, 2017 · 1 comment

Comments

@yaoguais
Copy link

yaoguais commented Jul 11, 2017 •
edited

Please answer these questions before submitting your issue. Thanks!

  1. What did you do?
    If possible, provide a recipe for reproducing the error.

when use prepare mode and some records lost.

  1. What did you expect to see?

would display 6 rows.

mysql> PREPARE prod FROM "select `media_id`, `is_view` from `test_views` where `media_id` in (?, ?, ?, ?, ?, ?) and `to_uid` = ?";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a1 = "874";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a2 = "875";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a3 = "876";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a4 = "1858";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a5 = "1787";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a6 = "1783";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a7 = 353;
Query OK, 0 rows affected (0.01 sec)

mysql> EXECUTE prod USING @a1, @a2,  @a3, @a4,  @a5, @a6,  @a7;
+----------+---------+
| media_id | is_view |
+----------+---------+
|      874 |       0 |
|      875 |       0 |
|      876 |       0 |
|     1858 |       0 |
|     1787 |       0 |
|     1783 |       0 |
+----------+---------+
6 rows in set (0.00 sec)

mysql> DEALLOCATE PREPARE prod;
Query OK, 0 rows affected (0.00 sec)
  1. What did you see instead?

only 3 rows.

mysql> PREPARE prod FROM "select `media_id`, `is_view` from `test_views` where `media_id` in (?, ?, ?, ?, ?, ?) and `to_uid` = ?";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a1 = "874";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a2 = "875";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a3 = "876";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a4 = "1858";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a5 = "1787";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a6 = "1783";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a7 = 353;
Query OK, 0 rows affected (0.01 sec)

mysql> EXECUTE prod USING @a1, @a2,  @a3, @a4,  @a5, @a6,  @a7;
+----------+---------+
| media_id | is_view |
+----------+---------+
|      874 |       0 |
|      875 |       0 |
|      876 |       0 |
+----------+---------+
3 rows in set (0.00 sec)

mysql> DEALLOCATE PREPARE prod;
Query OK, 0 rows affected (0.00 sec)
  1. What version of TiDB are you using (tidb-server -V)?

5.7.1-TiDB-1.0

  1. How to repeat:
mysql> create table test_views (`id` bigint(20) UNSIGNED NOT NULL, `media_id` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
    -> `to_uid` bigint(20) UNSIGNED NOT NULL DEFAULT '0', `is_view` tinyint(3) UNSIGNED NOT NULL DEFAULT '0')ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.47 sec)
mysql> insert into test_views(id, media_id, to_uid) values(1, 874, 353), (2, 875, 353), (3, 876, 353), (4, 1858, 353), (5, 1787, 353), (6, 1783, 353);
Query OK, 6 rows affected (0.01 sec)
mysql> PREPARE prod FROM "select `media_id`, `is_view` from `test_views` where `media_id` in (?, ?, ?, ?, ?, ?) and `to_uid` = ?";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a1 = "874";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a2 = "875";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a3 = "876";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a4 = "1858";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a5 = "1787";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a6 = "1783";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a7 = 353;
Query OK, 0 rows affected (0.01 sec)

mysql> EXECUTE prod USING @a1, @a2,  @a3, @a4,  @a5, @a6,  @a7;
+----------+---------+
| media_id | is_view |
+----------+---------+
|      874 |       0 |
|      875 |       0 |
|      876 |       0 |
+----------+---------+
3 rows in set (0.00 sec)

mysql> DEALLOCATE PREPARE prod;
Query OK, 0 rows affected (0.00 sec)

mysql> DEALLOCATE PREPARE prod;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_views where media_id in(874, 875, 876, 1858, 1787, 1783) and to_uid = 353;
+----+----------+--------+---------+
| id | media_id | to_uid | is_view |
+----+----------+--------+---------+
|  1 |      874 |    353 |       0 |
|  2 |      875 |    353 |       0 |
|  3 |      876 |    353 |       0 |
|  4 |     1858 |    353 |       0 |
|  5 |     1787 |    353 |       0 |
|  6 |     1783 |    353 |       0 |
+----+----------+--------+---------+
6 rows in set (0.00 sec)

mysql> select @@version;
+----------------+
| @@version      |
+----------------+
| 5.7.1-TiDB-1.0 |
+----------------+
1 row in set (0.00 sec)

mysql> PREPARE prod FROM "select `media_id`, `is_view` from `test_views` where `media_id` in (874, 875, 876, 1858, 1787, 1783) and `to_uid` = ?";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a1 = 353;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE prod USING @a1;
+----------+---------+
| media_id | is_view |
+----------+---------+
|      874 |       0 |
|      875 |       0 |
|      876 |       0 |
|     1858 |       0 |
|     1787 |       0 |
|     1783 |       0 |
+----------+---------+
6 rows in set (0.00 sec)

mysql> DEALLOCATE PREPARE prod;
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE prod FROM "select `media_id`, `is_view` from `test_views` where `media_id` in (?, ?, ?, ?, ?, ?) and `to_uid` = 353";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a1 = "874";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a2 = "875";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a3 = "876";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a4 = "1858";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a5 = "1787";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a6 = "1783";
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE prod USING @a1, @a2,  @a3, @a4,  @a5, @a6;
+----------+---------+
| media_id | is_view |
+----------+---------+
|      874 |       0 |
|      875 |       0 |
|      876 |       0 |
+----------+---------+
3 rows in set (0.01 sec)

mysql> DEALLOCATE PREPARE prod;
Query OK, 0 rows affected (0.00 sec)
@yaoguais yaoguais changed the title Some data records lost when use prepare mode Some query records lost when use prepare Jul 14, 2017
@morgo
Copy link
Contributor

morgo commented Oct 28, 2018

Thank you for the bug report!

This appears to be fixed in TiDB 2.1. Pastable testcase:

create table test_views (
`id` bigint(20) UNSIGNED NOT NULL, 
`media_id` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
`to_uid` bigint(20) UNSIGNED NOT NULL DEFAULT '0', 
`is_view` tinyint(3) UNSIGNED NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
insert into test_views(id, media_id, to_uid) values(1, 874, 353), (2, 875, 353), (3, 876, 353), (4, 1858, 353), (5, 1787, 353), (6, 1783, 353);

PREPARE prod FROM "select `media_id`, `is_view` from `test_views` where `media_id` in (?, ?, ?, ?, ?, ?) and `to_uid` = ?";
SET @a1 = "874";
SET @a2 = "875";
SET @a3 = "876";
SET @a4 = "1858";
SET @a5 = "1787";
SET @a6 = "1783";
SET @a7 = 353;
EXECUTE prod USING @a1, @a2,  @a3, @a4,  @a5, @a6,  @a7;
DEALLOCATE PREPARE prod;

In TiDB 2.1:

MySQL [test]> PREPARE prod FROM "select `media_id`, `is_view` from `test_views` where `media_id` in (?, ?, ?, ?, ?, ?) and `to_uid` = ?";
Query OK, 0 rows affected (0.01 sec)

MySQL [test]> SET @a1 = "874";
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> SET @a2 = "875";
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> SET @a3 = "876";
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> SET @a4 = "1858";
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> SET @a5 = "1787";
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> SET @a6 = "1783";
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> SET @a7 = 353;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> EXECUTE prod USING @a1, @a2,  @a3, @a4,  @a5, @a6,  @a7;
+----------+---------+
| media_id | is_view |
+----------+---------+
|      874 |       0 |
|      875 |       0 |
|      876 |       0 |
|     1858 |       0 |
|     1787 |       0 |
|     1783 |       0 |
+----------+---------+
6 rows in set (0.00 sec)

MySQL [test]> DEALLOCATE PREPARE prod;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v2.1.0-rc.3-98-gd65ce86
Git Commit Hash: d65ce8655cd26bb5ec44f9239562013af692d2fa
Git Branch: master
UTC Build Time: 2018-10-24 08:22:37
GoVersion: go version go1.11 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false
1 row in set (0.00 sec)

I will close this issue now, but please feel free to re-open it if you have further questions.

@morgo morgo closed this as completed Oct 28, 2018
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@morgo @yaoguais