Mysql select always returns empty set

Question

So for some reason my Mysql table always returns an empty set

mysql> show table status like 'test_table';
+-----------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name            | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free  | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+-----------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| test_table      | InnoDB |      10 | Compact    | 1625218 |            749 |  1218363392 |               0 |            0 | 1234173952 |           NULL | 2015-07-25 12:03:40 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
+-----------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> select * from test_table;
Empty set (0.00 sec)

mysql>

Any advice on how I can debug this?

Here's the create table

| test_table | CREATE TABLE `test_table` (
  `export_date` bigint(20) DEFAULT NULL,
  `id` int(11) NOT NULL DEFAULT '0',
  `title` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `recommended_age` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `artist_name` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `seller_name` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `company_url` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `support_url` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `view_url` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `artwork_url_large` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `artwork_url_small` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `release_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

have you tried putting the the table name into quotes? maybe look here: http://stackoverflow.com/a/2901502/5127499 — Carsten, Jul 26 '15 at 02:02

score 1 · Accepted Answer · answered Jul 27 '15 at 01:36

1

Turns out it was an import problem. I imported my data with a Python script and didn't have autocommit set to true (the script was written for an older version of MYSQL)

answered Jul 27 '15 at 01:36

Brian L. Clark

588
5
19

I was helping a friend fix his code and he didn't tell me that either – Brian L. Clark Jul 29 '15 at 14:02

score 0 · Answer 2 · answered Jul 26 '15 at 02:03

0

Its possibly the mySQL server does not know which database you are searching and is defaulting to the default schema where the table doesnt exist?

Try using the USE verb with your database name. eg: if your database was named db1 and your table was mytable.

USE db1;

SELECT * FROM mytable;

# selects from db1.mytable

answered Jul 26 '15 at 02:03

Tried this as well and didn't make a difference :( – Brian L. Clark Jul 26 '15 at 04:55
Long shot guess.. select * from test_table where 1; – Jul 26 '15 at 05:04

score 0 · Answer 3 · answered Jul 26 '15 at 02:30

0

It is possible that your data is not flush properly in to the table, run flush table by the following command and then check again.

FLUSH TABLES;

answered Jul 26 '15 at 02:30

Aman Aggarwal

17,619
9
53
81

how you insert the data ? – Aman Aggarwal Jul 26 '15 at 07:50

Mysql select always returns empty set

3 Answers3

Linked