Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
None
-
Linux
Description
Hello,
Error can be seen in the image below. Effectively running the exact same sql query, will return two different responses here.
mysql> select * from BTC_USDT_binance where id >= 56057162 and id < 56057791 and bidAsk = 1 order by id asc limit 2000; |
Empty set (0.00 sec) |
mysql> select * from BTC_USDT_binance where id >= 56057162 and id < 56057791 and bidAsk = 1 order by id asc limit 2000; |
+----------+------------------------+---------+----------+--------+-------+ |
| id | date | price | volume | bidAsk | reset | |
+----------+------------------------+---------+----------+--------+-------+ |
| 56057163 | 2018-11-27 00:49:46.63 | 3848.26 | 0.201554 | | |
|
| 56057164 | 2018-11-27 00:49:46.63 | 3835.31 | 0 | | |
|
| 56057168 | 2018-11-27 00:49:47.64 | 3848.27 | 2.1 | | |
|
| 56057175 | 2018-11-27 00:49:48.64 | 3848.3 | 0.086733 | | |
|
| 56057176 | 2018-11-27 00:49:48.64 | 3848.27 | 0 | | |
|
| 56057177 | 2018-11-27 00:49:48.64 | 3848.31 | 0.085672 | | |
|
Second Image to showcase it:
mysql> select COUNT(*) from BTC_USDT_binance where id >= '56057162' and id < '56057791' and bidAsk = 0; |
+----------+ |
| COUNT(*) | |
+----------+ |
| 316 |
|
+----------+ |
1 row in set (0.00 sec) |
|
mysql> select COUNT(*) from BTC_USDT_binance where id >= '56057162' and id < '56057791' and bidAsk = 0; |
+----------+ |
| COUNT(*) | |
+----------+ |
| 1 |
|
+----------+ |
1 row in set (0.00 sec) |
|
mysql> select COUNT(*) from BTC_USDT_binance where id >= '56057162' and id < '56057791' and bidAsk = 0; |
+----------+ |
| COUNT(*) | |
+----------+ |
| 316 |
|
+----------+ |
1 row in set (0.00 sec) |
|
mysql> select COUNT(*) from BTC_USDT_binance where id >= '56057162' and id < '56057791' and bidAsk = 0; |
+----------+ |
| COUNT(*) | |
+----------+ |
| 1 |
|
+----------+ |
1 row in set (0.01 sec) |
|
mysql> select COUNT(*) from BTC_USDT_binance where id >= '56057162' and id < '56057791' and bidAsk = 0; |
+----------+ |
| COUNT(*) | |
+----------+ |
| 316 |
|
+----------+ |
1 row in set (0.00 sec) |
Notes/Observations on this:
I have turned off other connections, am not storing to the table at all. It happens consistently every other - meaning that if I run it once, it will return one result (typically a large query/result of a couple thousand responses), and if I query it again, it will return the other (a single record).
-
-
- Note this is not related to space constraints on the primary key or anything related to this. The database schema is as follows here.
-
mysql> describe BTC_USDT_binance;
|
+--------+---------------------+------+-----+---------+----------------+ |
| Field | Type | Null | Key | Default | Extra | |
+--------+---------------------+------+-----+---------+----------------+ |
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment | |
| date | timestamp(2) | YES | | NULL | | |
| price | float | YES | | NULL | | |
| volume | float | YES | | NULL | | |
| bidAsk | bit(1) | YES | | NULL | | |
| reset | bit(1) | YES | | NULL | | |
+--------+---------------------+------+-----+---------+----------------+ |
-
-
- Hacks Around
-
If you limit the number of records drawn (i.e. only pull 100 records at once), the problem goes away. This happens until around 1,000 records. This can be easily seen/reproduced by running (varying the value of N):
select * from BTC_USDT_binance where id >= 56057162 and id < 56057791 and bidAsk = 1 order by id asc limit N; |
If you instead only use a single-sided greater than, and use a limit, the problem goes away as well. This is seen in:
select * from BTC_USDT_binance where id >= 56057162 and bidAsk = 1 order by id asc limit 500; |
Other Details and Notes