#StackBounty: #mysql #utf-8 Is it possible for a properly set up MySQL to return invalid UTF-8?

Bounty: 50

I am wondering: assuming everything is set up properly, meaning a column with charset utf8mb4 and a client connecting to the database setting the connection encoding to utf8mb4, is there any way in which said client could SELECT data which contains invalid UTF-8 byte sequences? Is there any way to insert data into a utf8mb4 column which is not in fact valid UTF-8?

Put another way: is it reasonable to assume that SELECTing any non-binary column over a client connection set to utf8mb4 should always return valid UTF-8 byte sequences?

(Let’s assume plain SELECT * statements without any CAST shenanigans or such.)


Get this bounty!!!

#StackBounty: #mysql #mysqldump #windows mysqldump slow on Windows unless piped to file

Bounty: 100

Running mysqldump without piping to file such as:

mysqldump -u root -pPASS db 

is really slow on windows. If I do

mysqldump -u root -pPASS db > db.sql

it runs fast.

This might not seem like a big deal as I can always pipe to a file. BUT I am using mysqldump from a script and I just want the raw output so I don’t have to load the file in memory or save a temp file.

Linux and Mac there is NOT a difference if I pipe it to a file or just let it output.

Is there a way to fix this? This is for mysql 5.6.x

The full command I run is:

mysqldump --user=$db_username --password=$db_password --tz-utc=false --single-transaction --routines --triggers db

but I have tried a lot of combos and nothing makes it fast.


Get this bounty!!!

#StackBounty: #mysql #query-performance #innodb #join #update How to speed up update join query between 2 mysql tables?

Bounty: 50

alright long story short.,
i have powerful dedicated server.

Intel  I7-6700K -
64GB DDR4 2400 MHz
1x480GB   SSD

running mysql server along with nginx,php

innodb-ft-min-token-size = 1
innodb-ft-enable-stopword = 0

innodb_buffer_pool_size = 40G
max_connections = 2000

[deploy@ns540545 ~]$ free -h
              total        used        free      shared  buff/cache   available
Mem:            62G         45G         11G        107M        6.4G         16G
Swap:          2.0G        1.4G        640M

it was expensive so i got another dedicated server for cost cutting lets call it
not-so powerful dedicated server

Intel  i3-2130  
8GB DDR3 1333 MHz   
2TB 

running mysql server along with nginx,php

innodb-ft-min-token-size = 1
innodb-ft-enable-stopword = 0

innodb_buffer_pool_size = 4G
max_connections = 2000

[root@privateserver deploy]# free -h
              total        used        free      shared  buff/cache   available
Mem:           7.7G        7.5G         73M         24M        150M         79M
Swap:           39G        7.8G         32G

i moved database from powerful server to not-so powerful server.

I can feel slight performance degradation while running simple queries which is fine, but this one query which used to take 2 minutes on powerful server now it takes around 26.6525 hours and counting on not-so powerful server.

UPDATE content a JOIN peers_data b ON a.hash = b.hash SET a.seeders = b.seeders, a.leechers = b.leechers, a.is_updated = b.is_updated

More info about tables which are exactly same on both the dedicated server

CREATE TABLE `peers_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hash` char(40) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `seeders` int(11) NOT NULL DEFAULT '0',
  `leechers` int(11) NOT NULL DEFAULT '0',
  `is_updated` int(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`hash`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `content` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content_id` int(11) unsigned NOT NULL DEFAULT '0',
  `hash` char(40) CHARACTER SET ascii NOT NULL DEFAULT '',
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `tags` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `category` smallint(3) unsigned NOT NULL DEFAULT '0',
  `category_name` varchar(50) CHARACTER SET ascii COLLATE ascii_bin DEFAULT '',
  `sub_category` smallint(3) unsigned NOT NULL DEFAULT '0',
  `sub_category_name` varchar(50) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `size` bigint(20) unsigned NOT NULL DEFAULT '0',
  `seeders` int(11) unsigned NOT NULL DEFAULT '0',
  `leechers` int(11) unsigned NOT NULL DEFAULT '0',
  `upload_date` datetime DEFAULT NULL,
  `uploader` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
  `uploader_level` varchar(10) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '',
  `comments_count` int(11) unsigned NOT NULL DEFAULT '0',
  `is_updated` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`content_id`),
  UNIQUE KEY `unique` (`id`) USING BTREE,
  KEY `hash` (`hash`),
  KEY `uploader` (`uploader`),
  KEY `sub_category` (`sub_category`),
  KEY `category` (`category`),
  KEY `title_index` (`title`),
  KEY `category_sub_category` (`category`,`sub_category`),
  KEY `seeders` (`seeders`),
  KEY `uploader_sub_category` (`uploader`,`sub_category`),
  KEY `upload_date` (`upload_date`),
  KEY `uploader_upload_date` (`uploader`,`upload_date`),
  KEY `leechers` (`leechers`),
  KEY `size` (`size`),
  KEY `uploader_seeders` (`uploader`,`seeders`),
  KEY `uploader_size` (`uploader`,`size`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `tags` (`tags`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


mysql> explain UPDATE content a JOIN peers_data b ON a.hash = b.hash SET a.seeders = b.seeders, a.leechers = b.leechers, a.is_updated = b.is_updated ;
+----+-------------+-------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | UPDATE      | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 4236260 |   100.00 | NULL        |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 160     | func |       1 |   100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
2 rows in set (0.00 sec)

records in peers_data 6,367,417
records in content 4,236,268

How can i speed up the above update join query ?
i was expecting about 1 hour on not-so powerful server, but 26 hours+ is too much.

what i am doing wrong ? or missing here ?

i have tried to compensate for RAM on not-so powerful server by setting 32 GB + swap space.
is innodb buffer pool 4 gb too much ?


so far i have tried.

  1. fixing both charset type same
  2. setting innodb buffer pool size to default 128M

and now the task completed in 19.93 Hours.


Get this bounty!!!

#StackBounty: #mysql #query-performance #join How to speed up update join query between 2 mysql tables?

Bounty: 50

alright long story short.,
i have powerful dedicated server.

Intel  I7-6700K -
64GB DDR4 2400 MHz
1x480GB   SSD

running mysql server along with nginx,php

innodb-ft-min-token-size = 1
innodb-ft-enable-stopword = 0

innodb_buffer_pool_size = 40G
max_connections = 2000

[deploy@ns540545 ~]$ free -h
              total        used        free      shared  buff/cache   available
Mem:            62G         45G         11G        107M        6.4G         16G
Swap:          2.0G        1.4G        640M

it was expensive so i got another dedicated server for cost cutting lets call it
not-so powerful dedicated server

Intel  i3-2130  
8GB DDR3 1333 MHz   
2TB 

running mysql server along with nginx,php

innodb-ft-min-token-size = 1
innodb-ft-enable-stopword = 0

innodb_buffer_pool_size = 4G
max_connections = 2000

[root@privateserver deploy]# free -h
              total        used        free      shared  buff/cache   available
Mem:           7.7G        7.5G         73M         24M        150M         79M
Swap:           39G        7.8G         32G

i moved database from powerful server to not-so powerful server.

I can feel slight performance degradation while running simple queries which is fine, but this one query which used to take 2 minutes on powerful server now it takes around 26.6525 hours and counting on not-so powerful server.

UPDATE content a JOIN peers_data b ON a.hash = b.hash SET a.seeders = b.seeders, a.leechers = b.leechers, a.is_updated = b.is_updated

More info about tables which are exactly same on both the dedicated server

CREATE TABLE `peers_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hash` char(40) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `seeders` int(11) NOT NULL DEFAULT '0',
  `leechers` int(11) NOT NULL DEFAULT '0',
  `is_updated` int(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`hash`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `content` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content_id` int(11) unsigned NOT NULL DEFAULT '0',
  `hash` char(40) CHARACTER SET ascii NOT NULL DEFAULT '',
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `tags` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `category` smallint(3) unsigned NOT NULL DEFAULT '0',
  `category_name` varchar(50) CHARACTER SET ascii COLLATE ascii_bin DEFAULT '',
  `sub_category` smallint(3) unsigned NOT NULL DEFAULT '0',
  `sub_category_name` varchar(50) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `size` bigint(20) unsigned NOT NULL DEFAULT '0',
  `seeders` int(11) unsigned NOT NULL DEFAULT '0',
  `leechers` int(11) unsigned NOT NULL DEFAULT '0',
  `upload_date` datetime DEFAULT NULL,
  `uploader` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
  `uploader_level` varchar(10) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '',
  `comments_count` int(11) unsigned NOT NULL DEFAULT '0',
  `is_updated` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`content_id`),
  UNIQUE KEY `unique` (`id`) USING BTREE,
  KEY `hash` (`hash`),
  KEY `uploader` (`uploader`),
  KEY `sub_category` (`sub_category`),
  KEY `category` (`category`),
  KEY `title_index` (`title`),
  KEY `category_sub_category` (`category`,`sub_category`),
  KEY `seeders` (`seeders`),
  KEY `uploader_sub_category` (`uploader`,`sub_category`),
  KEY `upload_date` (`upload_date`),
  KEY `uploader_upload_date` (`uploader`,`upload_date`),
  KEY `leechers` (`leechers`),
  KEY `size` (`size`),
  KEY `uploader_seeders` (`uploader`,`seeders`),
  KEY `uploader_size` (`uploader`,`size`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `tags` (`tags`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


mysql> explain UPDATE content a JOIN peers_data b ON a.hash = b.hash SET a.seeders = b.seeders, a.leechers = b.leechers, a.is_updated = b.is_updated ;
+----+-------------+-------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | UPDATE      | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 4236260 |   100.00 | NULL        |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 160     | func |       1 |   100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
2 rows in set (0.00 sec)

records in peers_data 6,367,417
records in content 4,236,268

How can i speed up the above update join query ?
i was expecting about 1 hour on not-so powerful server, but 26 hours+ is too much.

what i am doing wrong ? or missing here ?

i have tried to compensate for RAM on not-so powerful server by setting 32 GB + swap space.
is innodb buffer pool 4 gb too much ?


so far i have tried.

  1. fixing both charset type same
  2. setting innodb buffer pool size to default 128M

and now the task completed in 19.93 Hours.


Get this bounty!!!

#StackBounty: #mysql Table doesn't exist in engine

Bounty: 50

I am getting a weird error in my mysql database:

SELECT * FROM mytable

returns:

ERROR 1932 (42S02): Table 'mydb.mytable' doesn't exist in engine

I can confirm that the tables do indeed exist on local file system and show tables; does indeed return the tables I am interested in.

show tables;

+-----------------+
| Tables_in_mydb  |
+-----------------+
| presets         |
| mytable         |
+-----------------+

And the select:

select * from mytable;

Table 'mydb.mytable' doesn't exist in engine. 

Error makes absolute no sense and I’ve been working with this tables a couple of days ago without any problems. I haven’t moved the tables. What should I do?

Before that error above showed up I was getting an error:

Access denied for user ‘root’@’localhost’ (using password: YES),
but I successfully updated the password. I have no idea what triggered this error because I haven’t played with passwords.

However, when I tried to fix this error I have set permissions on /Applications/XAMPP/xamppfiles/var/mysql to read/write to everyone.

I have physical access to .frm and .ibd files of the database. I believe I use InnoDB storage engine.

UPDATE:

show table status gives back a really weird outout:

+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------------------------------------+
| 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                                              |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------------------------------------+
| preset          | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL                | NULL        | NULL       | NULL              |     NULL | NULL           | Table 'mydb.preset' doesn't exist in engine          |
| mytable         | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL                | NULL        | NULL       | NULL              |     NULL | NULL           | Table 'mydb.mytable' doesn't exist in engine         |

UPDATE 2
ls -lh /Applications/XAMPP/xamppfiles/var/mysql/ returns

-rwxrwxr--   1 _mysql  706   135K Jan  4  2017 My-MBP.err
-rwxrwxr--   1 _mysql  706   897K Jan 27 00:46 My-MacBook-Pro.local.err
drwxrwxr-x  33 _mysql  706   1.1K Feb 20  2017 abraham
drwxrwxr-x   7 _mysql  706   238B May 27  2016 accounts
-rwxrwxr--   1 _mysql  706    16K Jan 27 00:46 aria_log.00000001
-rwxrwxr--   1 _mysql  706    52B Jan 27 00:46 aria_log_control
drwxrwxr-x  23 _mysql  706   782B Jun 11  2017 ch7
drwxrwxr-x  31 _mysql  706   1.0K Jun 19  2017 coronel_2
drwxrwxr-x   5 _mysql  706   170B May 29  2017 emails
-rwxrwxr--   1 _mysql  706   5.0M Jan 27 00:46 ib_logfile0
-rwxrwxr--   1 _mysql  706   5.0M Nov 24  2015 ib_logfile1
-rwxrwxr--   1 _mysql  706    74M Jan 27 00:46 ibdata1
drwxrwxr-x   5 _mysql  706   170B Aug 28 17:44 jsonTest
-rwxrwxr--   1 _mysql  706     0B Nov 24  2015 multi-master.info
drwxrwxrwx   9 _mysql  706   306B Jan 26 11:40 mydb
drwxrwxr-x  89 _mysql  706   3.0K Jan 15 11:29 mysql
drwxrwxr-x  13 _mysql  706   442B May 30  2017 payments_ipndb
drwxrwxr-x   3 _mysql  706   102B Jan 15 11:29 performance_schema
drwxrwxr-x  41 _mysql  706   1.4K Jan 15 11:29 phpmyadmin
drwxrwxr-x   2 _mysql  706    68B Jan 15 11:28 test
drwxrwxr-x   5 _mysql  706   170B Jun 11  2017 tracking
drwxrwxr-x   9 _mysql  706   306B May 17  2017 tutorial
drwxrwxr-x   9 _mysql  706   306B Feb 21  2017 users

and ls -lh /Applications/XAMPP/xamppfiles/var/mysql/mydb/ returns

-rw-rw-rw-  1 _mysql  706    65B Sep  4 17:57 db.opt
-rw-rw-rw-  1 _mysql  706   1.8K Sep  8 18:20 preset.frm
-rw-rw-rw-  1 _mysql  706    96K Sep  9 20:53 preset.ibd
-rw-rw-rw-  1 _mysql  706   1.0K Jan 11 16:15 mytable.frm
-rw-rw-rw-  1 _mysql  706   128K Jan 11 21:02 mytable.ibd


Get this bounty!!!

#StackBounty: #mysql Table doesn't exist in engine

Bounty: 50

I am getting a weird error in my mysql database:

SELECT * FROM mytable

returns:

ERROR 1932 (42S02): Table 'mydb.mytable' doesn't exist in engine

I can confirm that the tables do indeed exist on local file system and show tables; does indeed return the tables I am interested in.

show tables;

+-----------------+
| Tables_in_mydb  |
+-----------------+
| presets         |
| mytable         |
+-----------------+

And the select:

select * from mytable;

Table 'mydb.mytable' doesn't exist in engine. 

Error makes absolute no sense and I’ve been working with this tables a couple of days ago without any problems. I haven’t moved the tables. What should I do?

Before that error above showed up I was getting an error:

Access denied for user ‘root’@’localhost’ (using password: YES),
but I successfully updated the password. I have no idea what triggered this error because I haven’t played with passwords.

However, when I tried to fix this error I have set permissions on /Applications/XAMPP/xamppfiles/var/mysql to read/write to everyone.

I have physical access to .frm and .ibd files of the database. I believe I use InnoDB storage engine.

UPDATE:

show table status gives back a really weird outout:

+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------------------------------------+
| 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                                              |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------------------------------------+
| preset          | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL                | NULL        | NULL       | NULL              |     NULL | NULL           | Table 'mydb.preset' doesn't exist in engine          |
| mytable         | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL                | NULL        | NULL       | NULL              |     NULL | NULL           | Table 'mydb.mytable' doesn't exist in engine         |

UPDATE 2
ls -lh /Applications/XAMPP/xamppfiles/var/mysql/ returns

-rwxrwxr--   1 _mysql  706   135K Jan  4  2017 My-MBP.err
-rwxrwxr--   1 _mysql  706   897K Jan 27 00:46 My-MacBook-Pro.local.err
drwxrwxr-x  33 _mysql  706   1.1K Feb 20  2017 abraham
drwxrwxr-x   7 _mysql  706   238B May 27  2016 accounts
-rwxrwxr--   1 _mysql  706    16K Jan 27 00:46 aria_log.00000001
-rwxrwxr--   1 _mysql  706    52B Jan 27 00:46 aria_log_control
drwxrwxr-x  23 _mysql  706   782B Jun 11  2017 ch7
drwxrwxr-x  31 _mysql  706   1.0K Jun 19  2017 coronel_2
drwxrwxr-x   5 _mysql  706   170B May 29  2017 emails
-rwxrwxr--   1 _mysql  706   5.0M Jan 27 00:46 ib_logfile0
-rwxrwxr--   1 _mysql  706   5.0M Nov 24  2015 ib_logfile1
-rwxrwxr--   1 _mysql  706    74M Jan 27 00:46 ibdata1
drwxrwxr-x   5 _mysql  706   170B Aug 28 17:44 jsonTest
-rwxrwxr--   1 _mysql  706     0B Nov 24  2015 multi-master.info
drwxrwxrwx   9 _mysql  706   306B Jan 26 11:40 mydb
drwxrwxr-x  89 _mysql  706   3.0K Jan 15 11:29 mysql
drwxrwxr-x  13 _mysql  706   442B May 30  2017 payments_ipndb
drwxrwxr-x   3 _mysql  706   102B Jan 15 11:29 performance_schema
drwxrwxr-x  41 _mysql  706   1.4K Jan 15 11:29 phpmyadmin
drwxrwxr-x   2 _mysql  706    68B Jan 15 11:28 test
drwxrwxr-x   5 _mysql  706   170B Jun 11  2017 tracking
drwxrwxr-x   9 _mysql  706   306B May 17  2017 tutorial
drwxrwxr-x   9 _mysql  706   306B Feb 21  2017 users

and ls -lh /Applications/XAMPP/xamppfiles/var/mysql/mydb/ returns

-rw-rw-rw-  1 _mysql  706    65B Sep  4 17:57 db.opt
-rw-rw-rw-  1 _mysql  706   1.8K Sep  8 18:20 preset.frm
-rw-rw-rw-  1 _mysql  706    96K Sep  9 20:53 preset.ibd
-rw-rw-rw-  1 _mysql  706   1.0K Jan 11 16:15 mytable.frm
-rw-rw-rw-  1 _mysql  706   128K Jan 11 21:02 mytable.ibd


Get this bounty!!!

#StackBounty: #mysql Table doesn't exist in engine

Bounty: 50

I am getting a weird error in my mysql database:

SELECT * FROM mytable

returns:

ERROR 1932 (42S02): Table 'mydb.mytable' doesn't exist in engine

I can confirm that the tables do indeed exist on local file system and show tables; does indeed return the tables I am interested in.

show tables;

+-----------------+
| Tables_in_mydb  |
+-----------------+
| presets         |
| mytable         |
+-----------------+

And the select:

select * from mytable;

Table 'mydb.mytable' doesn't exist in engine. 

Error makes absolute no sense and I’ve been working with this tables a couple of days ago without any problems. I haven’t moved the tables. What should I do?

Before that error above showed up I was getting an error:

Access denied for user ‘root’@’localhost’ (using password: YES),
but I successfully updated the password. I have no idea what triggered this error because I haven’t played with passwords.

However, when I tried to fix this error I have set permissions on /Applications/XAMPP/xamppfiles/var/mysql to read/write to everyone.

I have physical access to .frm and .ibd files of the database. I believe I use InnoDB storage engine.

UPDATE:

show table status gives back a really weird outout:

+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------------------------------------+
| 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                                              |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------------------------------------+
| preset          | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL                | NULL        | NULL       | NULL              |     NULL | NULL           | Table 'mydb.preset' doesn't exist in engine          |
| mytable         | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL                | NULL        | NULL       | NULL              |     NULL | NULL           | Table 'mydb.mytable' doesn't exist in engine         |

UPDATE 2
ls -lh /Applications/XAMPP/xamppfiles/var/mysql/ returns

-rwxrwxr--   1 _mysql  706   135K Jan  4  2017 My-MBP.err
-rwxrwxr--   1 _mysql  706   897K Jan 27 00:46 My-MacBook-Pro.local.err
drwxrwxr-x  33 _mysql  706   1.1K Feb 20  2017 abraham
drwxrwxr-x   7 _mysql  706   238B May 27  2016 accounts
-rwxrwxr--   1 _mysql  706    16K Jan 27 00:46 aria_log.00000001
-rwxrwxr--   1 _mysql  706    52B Jan 27 00:46 aria_log_control
drwxrwxr-x  23 _mysql  706   782B Jun 11  2017 ch7
drwxrwxr-x  31 _mysql  706   1.0K Jun 19  2017 coronel_2
drwxrwxr-x   5 _mysql  706   170B May 29  2017 emails
-rwxrwxr--   1 _mysql  706   5.0M Jan 27 00:46 ib_logfile0
-rwxrwxr--   1 _mysql  706   5.0M Nov 24  2015 ib_logfile1
-rwxrwxr--   1 _mysql  706    74M Jan 27 00:46 ibdata1
drwxrwxr-x   5 _mysql  706   170B Aug 28 17:44 jsonTest
-rwxrwxr--   1 _mysql  706     0B Nov 24  2015 multi-master.info
drwxrwxrwx   9 _mysql  706   306B Jan 26 11:40 mydb
drwxrwxr-x  89 _mysql  706   3.0K Jan 15 11:29 mysql
drwxrwxr-x  13 _mysql  706   442B May 30  2017 payments_ipndb
drwxrwxr-x   3 _mysql  706   102B Jan 15 11:29 performance_schema
drwxrwxr-x  41 _mysql  706   1.4K Jan 15 11:29 phpmyadmin
drwxrwxr-x   2 _mysql  706    68B Jan 15 11:28 test
drwxrwxr-x   5 _mysql  706   170B Jun 11  2017 tracking
drwxrwxr-x   9 _mysql  706   306B May 17  2017 tutorial
drwxrwxr-x   9 _mysql  706   306B Feb 21  2017 users

and ls -lh /Applications/XAMPP/xamppfiles/var/mysql/mydb/ returns

-rw-rw-rw-  1 _mysql  706    65B Sep  4 17:57 db.opt
-rw-rw-rw-  1 _mysql  706   1.8K Sep  8 18:20 preset.frm
-rw-rw-rw-  1 _mysql  706    96K Sep  9 20:53 preset.ibd
-rw-rw-rw-  1 _mysql  706   1.0K Jan 11 16:15 mytable.frm
-rw-rw-rw-  1 _mysql  706   128K Jan 11 21:02 mytable.ibd


Get this bounty!!!

#StackBounty: #mysql Table doesn't exist in engine

Bounty: 50

I am getting a weird error in my mysql database:

SELECT * FROM mytable

returns:

ERROR 1932 (42S02): Table 'mydb.mytable' doesn't exist in engine

I can confirm that the tables do indeed exist on local file system and show tables; does indeed return the tables I am interested in.

show tables;

+-----------------+
| Tables_in_mydb  |
+-----------------+
| presets         |
| mytable         |
+-----------------+

And the select:

select * from mytable;

Table 'mydb.mytable' doesn't exist in engine. 

Error makes absolute no sense and I’ve been working with this tables a couple of days ago without any problems. I haven’t moved the tables. What should I do?

Before that error above showed up I was getting an error:

Access denied for user ‘root’@’localhost’ (using password: YES),
but I successfully updated the password. I have no idea what triggered this error because I haven’t played with passwords.

However, when I tried to fix this error I have set permissions on /Applications/XAMPP/xamppfiles/var/mysql to read/write to everyone.

I have physical access to .frm and .ibd files of the database. I believe I use InnoDB storage engine.

UPDATE:

show table status gives back a really weird outout:

+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------------------------------------+
| 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                                              |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------------------------------------+
| preset          | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL                | NULL        | NULL       | NULL              |     NULL | NULL           | Table 'mydb.preset' doesn't exist in engine          |
| mytable         | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL                | NULL        | NULL       | NULL              |     NULL | NULL           | Table 'mydb.mytable' doesn't exist in engine         |

UPDATE 2
ls -lh /Applications/XAMPP/xamppfiles/var/mysql/ returns

-rwxrwxr--   1 _mysql  706   135K Jan  4  2017 My-MBP.err
-rwxrwxr--   1 _mysql  706   897K Jan 27 00:46 My-MacBook-Pro.local.err
drwxrwxr-x  33 _mysql  706   1.1K Feb 20  2017 abraham
drwxrwxr-x   7 _mysql  706   238B May 27  2016 accounts
-rwxrwxr--   1 _mysql  706    16K Jan 27 00:46 aria_log.00000001
-rwxrwxr--   1 _mysql  706    52B Jan 27 00:46 aria_log_control
drwxrwxr-x  23 _mysql  706   782B Jun 11  2017 ch7
drwxrwxr-x  31 _mysql  706   1.0K Jun 19  2017 coronel_2
drwxrwxr-x   5 _mysql  706   170B May 29  2017 emails
-rwxrwxr--   1 _mysql  706   5.0M Jan 27 00:46 ib_logfile0
-rwxrwxr--   1 _mysql  706   5.0M Nov 24  2015 ib_logfile1
-rwxrwxr--   1 _mysql  706    74M Jan 27 00:46 ibdata1
drwxrwxr-x   5 _mysql  706   170B Aug 28 17:44 jsonTest
-rwxrwxr--   1 _mysql  706     0B Nov 24  2015 multi-master.info
drwxrwxrwx   9 _mysql  706   306B Jan 26 11:40 mydb
drwxrwxr-x  89 _mysql  706   3.0K Jan 15 11:29 mysql
drwxrwxr-x  13 _mysql  706   442B May 30  2017 payments_ipndb
drwxrwxr-x   3 _mysql  706   102B Jan 15 11:29 performance_schema
drwxrwxr-x  41 _mysql  706   1.4K Jan 15 11:29 phpmyadmin
drwxrwxr-x   2 _mysql  706    68B Jan 15 11:28 test
drwxrwxr-x   5 _mysql  706   170B Jun 11  2017 tracking
drwxrwxr-x   9 _mysql  706   306B May 17  2017 tutorial
drwxrwxr-x   9 _mysql  706   306B Feb 21  2017 users

and ls -lh /Applications/XAMPP/xamppfiles/var/mysql/mydb/ returns

-rw-rw-rw-  1 _mysql  706    65B Sep  4 17:57 db.opt
-rw-rw-rw-  1 _mysql  706   1.8K Sep  8 18:20 preset.frm
-rw-rw-rw-  1 _mysql  706    96K Sep  9 20:53 preset.ibd
-rw-rw-rw-  1 _mysql  706   1.0K Jan 11 16:15 mytable.frm
-rw-rw-rw-  1 _mysql  706   128K Jan 11 21:02 mytable.ibd


Get this bounty!!!

#StackBounty: #mysql Table doesn't exist in engine

Bounty: 50

I am getting a weird error in my mysql database:

SELECT * FROM mytable

returns:

ERROR 1932 (42S02): Table 'mydb.mytable' doesn't exist in engine

I can confirm that the tables do indeed exist on local file system and show tables; does indeed return the tables I am interested in.

show tables;

+-----------------+
| Tables_in_mydb  |
+-----------------+
| presets         |
| mytable         |
+-----------------+

And the select:

select * from mytable;

Table 'mydb.mytable' doesn't exist in engine. 

Error makes absolute no sense and I’ve been working with this tables a couple of days ago without any problems. I haven’t moved the tables. What should I do?

Before that error above showed up I was getting an error:

Access denied for user ‘root’@’localhost’ (using password: YES),
but I successfully updated the password. I have no idea what triggered this error because I haven’t played with passwords.

However, when I tried to fix this error I have set permissions on /Applications/XAMPP/xamppfiles/var/mysql to read/write to everyone.

I have physical access to .frm and .ibd files of the database. I believe I use InnoDB storage engine.

UPDATE:

show table status gives back a really weird outout:

+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------------------------------------+
| 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                                              |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------------------------------------+
| preset          | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL                | NULL        | NULL       | NULL              |     NULL | NULL           | Table 'mydb.preset' doesn't exist in engine          |
| mytable         | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL                | NULL        | NULL       | NULL              |     NULL | NULL           | Table 'mydb.mytable' doesn't exist in engine         |

UPDATE 2
ls -lh /Applications/XAMPP/xamppfiles/var/mysql/ returns

-rwxrwxr--   1 _mysql  706   135K Jan  4  2017 My-MBP.err
-rwxrwxr--   1 _mysql  706   897K Jan 27 00:46 My-MacBook-Pro.local.err
drwxrwxr-x  33 _mysql  706   1.1K Feb 20  2017 abraham
drwxrwxr-x   7 _mysql  706   238B May 27  2016 accounts
-rwxrwxr--   1 _mysql  706    16K Jan 27 00:46 aria_log.00000001
-rwxrwxr--   1 _mysql  706    52B Jan 27 00:46 aria_log_control
drwxrwxr-x  23 _mysql  706   782B Jun 11  2017 ch7
drwxrwxr-x  31 _mysql  706   1.0K Jun 19  2017 coronel_2
drwxrwxr-x   5 _mysql  706   170B May 29  2017 emails
-rwxrwxr--   1 _mysql  706   5.0M Jan 27 00:46 ib_logfile0
-rwxrwxr--   1 _mysql  706   5.0M Nov 24  2015 ib_logfile1
-rwxrwxr--   1 _mysql  706    74M Jan 27 00:46 ibdata1
drwxrwxr-x   5 _mysql  706   170B Aug 28 17:44 jsonTest
-rwxrwxr--   1 _mysql  706     0B Nov 24  2015 multi-master.info
drwxrwxrwx   9 _mysql  706   306B Jan 26 11:40 mydb
drwxrwxr-x  89 _mysql  706   3.0K Jan 15 11:29 mysql
drwxrwxr-x  13 _mysql  706   442B May 30  2017 payments_ipndb
drwxrwxr-x   3 _mysql  706   102B Jan 15 11:29 performance_schema
drwxrwxr-x  41 _mysql  706   1.4K Jan 15 11:29 phpmyadmin
drwxrwxr-x   2 _mysql  706    68B Jan 15 11:28 test
drwxrwxr-x   5 _mysql  706   170B Jun 11  2017 tracking
drwxrwxr-x   9 _mysql  706   306B May 17  2017 tutorial
drwxrwxr-x   9 _mysql  706   306B Feb 21  2017 users

and ls -lh /Applications/XAMPP/xamppfiles/var/mysql/mydb/ returns

-rw-rw-rw-  1 _mysql  706    65B Sep  4 17:57 db.opt
-rw-rw-rw-  1 _mysql  706   1.8K Sep  8 18:20 preset.frm
-rw-rw-rw-  1 _mysql  706    96K Sep  9 20:53 preset.ibd
-rw-rw-rw-  1 _mysql  706   1.0K Jan 11 16:15 mytable.frm
-rw-rw-rw-  1 _mysql  706   128K Jan 11 21:02 mytable.ibd


Get this bounty!!!

#StackBounty: #mysql Table doesn't exist in engine

Bounty: 50

I am getting a weird error in my mysql database:

SELECT * FROM mytable

returns:

ERROR 1932 (42S02): Table 'mydb.mytable' doesn't exist in engine

I can confirm that the tables do indeed exist on local file system and show tables; does indeed return the tables I am interested in.

show tables;

+-----------------+
| Tables_in_mydb  |
+-----------------+
| presets         |
| mytable         |
+-----------------+

And the select:

select * from mytable;

Table 'mydb.mytable' doesn't exist in engine. 

Error makes absolute no sense and I’ve been working with this tables a couple of days ago without any problems. I haven’t moved the tables. What should I do?

Before that error above showed up I was getting an error:

Access denied for user ‘root’@’localhost’ (using password: YES),
but I successfully updated the password. I have no idea what triggered this error because I haven’t played with passwords.

However, when I tried to fix this error I have set permissions on /Applications/XAMPP/xamppfiles/var/mysql to read/write to everyone.

I have physical access to .frm and .ibd files of the database. I believe I use InnoDB storage engine.

UPDATE:

show table status gives back a really weird outout:

+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------------------------------------+
| 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                                              |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------------------------------------+
| preset          | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL                | NULL        | NULL       | NULL              |     NULL | NULL           | Table 'mydb.preset' doesn't exist in engine          |
| mytable         | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL                | NULL        | NULL       | NULL              |     NULL | NULL           | Table 'mydb.mytable' doesn't exist in engine         |

UPDATE 2
ls -lh /Applications/XAMPP/xamppfiles/var/mysql/ returns

-rwxrwxr--   1 _mysql  706   135K Jan  4  2017 My-MBP.err
-rwxrwxr--   1 _mysql  706   897K Jan 27 00:46 My-MacBook-Pro.local.err
drwxrwxr-x  33 _mysql  706   1.1K Feb 20  2017 abraham
drwxrwxr-x   7 _mysql  706   238B May 27  2016 accounts
-rwxrwxr--   1 _mysql  706    16K Jan 27 00:46 aria_log.00000001
-rwxrwxr--   1 _mysql  706    52B Jan 27 00:46 aria_log_control
drwxrwxr-x  23 _mysql  706   782B Jun 11  2017 ch7
drwxrwxr-x  31 _mysql  706   1.0K Jun 19  2017 coronel_2
drwxrwxr-x   5 _mysql  706   170B May 29  2017 emails
-rwxrwxr--   1 _mysql  706   5.0M Jan 27 00:46 ib_logfile0
-rwxrwxr--   1 _mysql  706   5.0M Nov 24  2015 ib_logfile1
-rwxrwxr--   1 _mysql  706    74M Jan 27 00:46 ibdata1
drwxrwxr-x   5 _mysql  706   170B Aug 28 17:44 jsonTest
-rwxrwxr--   1 _mysql  706     0B Nov 24  2015 multi-master.info
drwxrwxrwx   9 _mysql  706   306B Jan 26 11:40 mydb
drwxrwxr-x  89 _mysql  706   3.0K Jan 15 11:29 mysql
drwxrwxr-x  13 _mysql  706   442B May 30  2017 payments_ipndb
drwxrwxr-x   3 _mysql  706   102B Jan 15 11:29 performance_schema
drwxrwxr-x  41 _mysql  706   1.4K Jan 15 11:29 phpmyadmin
drwxrwxr-x   2 _mysql  706    68B Jan 15 11:28 test
drwxrwxr-x   5 _mysql  706   170B Jun 11  2017 tracking
drwxrwxr-x   9 _mysql  706   306B May 17  2017 tutorial
drwxrwxr-x   9 _mysql  706   306B Feb 21  2017 users

and ls -lh /Applications/XAMPP/xamppfiles/var/mysql/mydb/ returns

-rw-rw-rw-  1 _mysql  706    65B Sep  4 17:57 db.opt
-rw-rw-rw-  1 _mysql  706   1.8K Sep  8 18:20 preset.frm
-rw-rw-rw-  1 _mysql  706    96K Sep  9 20:53 preset.ibd
-rw-rw-rw-  1 _mysql  706   1.0K Jan 11 16:15 mytable.frm
-rw-rw-rw-  1 _mysql  706   128K Jan 11 21:02 mytable.ibd


Get this bounty!!!