#StackBounty: #php #mysql #sql #ajax #recursive-query Order hierarchical query for comments with parent-child relation?

Bounty: 50

I have a comments system that uses the adjacency list to store hierarchical data (i believe), for example: MySql table has the columns id, parent_id, date, … , where a comment without a parent_id is a main comment and a comment with a parent_id is naturally a reply.

On initial page load i make an Ajax call that loads all the comments without a parent_id, so all the main comments.

SELECT * FROM comms WHERE parent_id IS NULL

Now if any of the comments have replies a button like “load replies” appears for that comment and on click another call is made that loads all the comments that have as parent_id that comment id, then the recursive query loads the replies of replies and so on. And that works pretty well the problem is that from the order they are loaded you can’t really tell what is a replay to what.

So what i want is to order them so that a replay is under the comment that it belongs.

Now is this possible only from sql doing something like ORDER BY id = parent_id, ordering them so that they somewhat make sense or should i handle this from php? Or should i just start over and store them in a different way?

Edit: part of the second query (example taken from this answer i found a while back)

SELECT date_p, parent_id, id
   FROM (SELECT * FROM comms) rec,
   (SELECT @pv := 14) initialisation
   WHERE find_in_set(parent_id, @pv) > 0 
   AND @pv := concat(@pv, ',', id) ORDER BY ?

If i would use the “Alternative 1” provided in the answer i liked for, would the method for ordering be different or better?

This is what I am trying to achive:

<p>Main comm 1</p>
  <p>reply to main comm 1</p>
  <p>another reply to main comm 1</p>
    <p> replay to reply of main comm 1</p>
  <p> yet another reply to main comm 1</p>
<p>Main comm 2</p>
<p>Main comm 3</p>


Get this bounty!!!

#StackBounty: #mysql #innodb #mysql-5.6 #deadlock MySql Gap Lock Deadlock on Inserts

Bounty: 50

I’m getting Deadlocks from gap locks on a table when inserting into it frequently from multiple sources. Here is an overview of my processes.

START TRANSACTION  
UPDATE vehicle_image SET active = 0 WHERE vehicleID = SOMEID and active = 1  
Loop:
  INSERT INTO vehicle_image (vehicleID, vehicleImageFilePath, vehicleImageSplashFilePath, vehicleImageThumbnailFilePath, vehicleImageMiniFilePath, mainVehicleImage, active) VALUES (%s, %s, %s, %s, %s, %s, 1);  
END TRANSACTION

The output of SHOW Create table vehicle_image;

CREATE TABLE `vehicle_image` (
  `vehicleImageID` int(11) NOT NULL AUTO_INCREMENT,
  `vehicleID` int(11) DEFAULT NULL,
  `vehicleImageFilePath` varchar(200) DEFAULT NULL,
  `vehicleImageSplashFilePath` varchar(200) DEFAULT NULL,
  `vehicleImageThumbnailFilePath` varchar(200) DEFAULT NULL,
  `vehicleImageMiniFilePath` varchar(200) DEFAULT NULL,
  `mainVehicleImage` bit(1) DEFAULT NULL,
  `active` bit(1) DEFAULT b'1',
  `userCreated` int(11) DEFAULT NULL,
  `dateCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `userModified` int(11) DEFAULT NULL,
  `dateModified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`vehicleImageID`),
  KEY `active` (`active`),
  KEY `mainvehicleimage` (`mainVehicleImage`),
  KEY `vehicleid` (`vehicleID`)
) ENGINE=InnoDB AUTO_INCREMENT=22878102 DEFAULT CHARSET=latin1

And the last Deadlock given by show engine innodb status:

LATEST DETECTED DEADLOCK
------------------------
2018-03-27 12:31:15 11a58
*** (1) TRANSACTION:
TRANSACTION 5897678083, ACTIVE 2 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 873570, OS thread handle 0x124bc, query id 198983754 ec2-34-239-240-179.compute-1.amazonaws.com 34.239.240.179 image_processor update
INSERT INTO vehicle_image (vehicleID, vehicleImageFilePath, vehicleImageSplashFilePath, vehicleImageThumbnailFilePath, vehicleImageMiniFilePath, mainVehicleImage, active) VALUES (70006176, 'f180928(1)1522168276.230837full.jpg', 'f180928(1)1522168276.230837splash.jpg', 'f180928(1)1522168276.230837thumb.jpg', 'f180928(1)1522168276.230837mini.jpg', 1, 1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 875 page no 238326 n bits 472 index `vehicleid` of table `ipacket`.`vehicle_image` trx id 5897678083 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 378 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 842c365a; asc  ,6Z;;
 1: len 4; hex 815d03bc; asc  ]  ;;

*** (2) TRANSACTION:
TRANSACTION 5897678270, ACTIVE 1 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 873571, OS thread handle 0x11a58, query id 198983849 ec2-35-171-169-21.compute-1.amazonaws.com 35.171.169.21 image_processor update
INSERT INTO vehicle_image (vehicleID, vehicleImageFilePath, vehicleImageSplashFilePath, vehicleImageThumbnailFilePath, vehicleImageMiniFilePath, mainVehicleImage, active) VALUES (70006326, '29709(1)1522168277.4443843full.jpg', '29709(1)1522168277.4443843splash.jpg', '29709(1)1522168277.4443843thumb.jpg', '29709(1)1522168277.4443843mini.jpg', 1, 1)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 875 page no 238326 n bits 464 index `vehicleid` of table `ipacket`.`vehicle_image` trx id 5897678270 lock_mode X locks gap before rec
Record lock, heap no 378 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 842c365a; asc  ,6Z;;
 1: len 4; hex 815d03bc; asc  ]  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 875 page no 238326 n bits 472 index `vehicleid` of table `ipacket`.`vehicle_image` trx id 5897678270 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 378 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 842c365a; asc  ,6Z;;
 1: len 4; hex 815d03bc; asc  ]  ;;

*** WE ROLL BACK TRANSACTION (2)

I am running many of these processes simultaneously but never running two process that are using the same VehicleID. I’m really confused as to why I’m getting Deadlocks. I have temporarily solved the problem by utilizing the Isolation level READ COMMITTED but I have read that this requires changed to replication in that you must do row level replication. I have read other questions on here that are similar to mine, but I am somewhat new to SQL and still cannot understand Why this is occurring.
Similar Questions:
Deadlock on MySQL insert statments
MySQL InnoDB Deadlock For 2 simple insert queries

UPDATE:

I found out that using “READ COMMITED” has not actually solved the issue. I still haven’t figured out why the deadlocks are occurring and I really don’t know how to diagnose any further than I currently have. I’m continuing to get Deadlocks in my production system. Any help would be appreciated.


Get this bounty!!!

#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!!!