CODEWITHSUNDEEP
mysql
Brian L. Clark
Brian L. Clark

Reputation: 628

Mysql select always returns empty set

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 |

Upvotes: 2

Views: 11431

Answers (3)

Brian L. Clark
Brian L. Clark

Reputation: 628

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)

Upvotes: 1

Aman Aggarwal
Aman Aggarwal

Reputation: 18459

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;

Upvotes: 0

user2276553
user2276553

Reputation:

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

Upvotes: 0

Related Questions