Error 1168 Hy000 Unable To Open Underlying Table
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the mariadb merge statement workings and policies of this site About Us Learn more about
Unable To Open Underlying Table Which Is Differently Defined
Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions mariadb upsert Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up ERROR 1168 (HY000) Mysql Merge does not work up vote 0 down vote favorite When I was creating merging table I have got this error. ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist my query is CREATE TABLE IF NOT EXISTS `users_001`( `sequence_id` BIGINT PRIMARY KEY NOT NULL UNIQUE, `wb_id` BIGINT NOT NULL UNIQUE, `containerid` BIGINT NOT NULL UNIQUE, `screen_name` varchar(255) NOT NULL, `verified` TINYINT NOT NULL, `v_reason` varchar(255) NOT NULL, `v_type` TINYINT NOT NULL, `is_member` TINYINT NOT NULL, `gender` varchar(1) NOT NULL, `statuses_count` SMALLINT NOT NULL, `description` varchar(255) NOT NULL, `description_length` SMALLINT NOT NULL, `fans_num` int NOT NULL, `follow_num` int NOT NULL, `last_update` INT NOT NULL default 0, `last_check` INT NOT NULL default 0, `is_crawled` TINYINT NOT NULL default 0, `info` varchar(512) NOT NULL default "", INDEX (wb_id), INDEX (containerid), INDEX (statuses_count), INDEX (last_update), INDEX (last_check) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE users_002 LIKE users_001; CREATE TABLE users_003 LIKE users_001; CREATE TABLE users_004 LIKE users_001; CREATE TABLE users_005 LIKE users_001; CREATE TABLE users_006 LIKE users_001; Then Merge: CREATE TABLE `users` ( `sequence_id` BIGINT NOT NULL UNIQUE, `wb_id` BIGINT NOT NULL UNIQUE, `containerid` BIGINT NOT NULL UNIQUE, `screen_name` varchar(255) NOT NULL, `verified` TINYINT NOT NULL, `v_reason` varchar(255) NOT NULL, `v_type` TINYINT NOT NULL, `is_member` TINYINT NOT NULL, `gender` varchar(1) NOT NULL, `statuses_count` SMALLIN
Email Updates: Status: Not a Bug Impact on me: None Category:MySQL Server Severity:S1 (Critical) Version:4.1.23-pro OS:Linux Assigned to: Tags: merge, regression View Add Comment Files Developer Edit Submission View Progress Log Contributions [28 Jan 2008 12:49] Radek Kavan Description: In bug number #26881 (http://bugs.mysql.com/bug.php?id=26881) is mentioned, that this bug it was repair in version 4.1.23, but i have still this problem. we have mysql version 4.1.23-pro and still have problem with MERGE tables. mysql> status -------------- mysql Ver 14.7 Distrib 4.1.23, for pc-linux-gnu (i686) using EditLine http://stackoverflow.com/questions/30879033/error-1168-hy000-mysql-merge-does-not-work wrapper Connection id: 2 Current database: XY Current user: XY@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 4.1.23-pro Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: cp1250 Db characterset: cp1250 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /tmp/mysql.sock and we have still problem with Merge tables. mysql> select * from http://bugs.mysql.com/34101 m1; ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist Is it possible to fix it in our licenced version? How to repeat: flush tables; drop table t1; mysql> create table t1 ( -> i1 datetime, -> v1 varchar(30), -> m1 mediumint(8), -> s1 smallint(5), -> t1 tinyint(3), -> id INT AUTO_INCREMENT, -> PRIMARY KEY (id) -> )engine=myisam; Query OK, 0 rows affected (0.04 sec) drop table m1; mysql> create table m1( -> i1 datetime, -> v1 varchar(30), -> m1 mediumint(8), -> s1 smallint(5), -> t1 tinyint(3) -> ) engine=merge union(t1); Query OK, 0 rows affected (0.01 sec) select * from t1; ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist [28 Jan 2008 15:17] Valerii Kravchuk Have you read the last comment in http://bugs.mysql.com/bug.php?id=26881, in reply to your comment? You have additinal column, id INT AUTO_INCREMENT, in your t1 table that is not present in MERGE (m1) table, hence the error message. Legal Policies Your Privacy Rights Terms of Use Contact
2007 0:52 Reporter: Arjen Lentz Email Updates: Status: Closed Impact on me: None Category:MySQL Server: Merge storage engine Severity:S2 https://bugs.mysql.com/bug.php?id=26976 (Serious) Version:5.1.14/5.0 OS:Any (any) Assigned to: Sergey Vojtovich Tags: bfsm_2007_03_15, merge, myisam https://www.experts-exchange.com/questions/28376291/to-better-understand-MERGE-STORAGE-ENGINE-in-Ver-5-6-MySQL-but-can-not-even-desc-table-see-errors.html View Add Comment Files Developer Edit Submission View Progress Log Contributions [8 Mar 2007 23:54] Arjen Lentz Description: When a MERGE table is opened, server checks for existence of underlying tables. The resulting error message is useless, since it does not specify *which* table is missing. In unable to addition, SHOW CREATE TABLE and SHOW TABLE STATUS on the MERGE table also fail, so you can't even look back at the definition to see where the problem might be. 5.0 in some cases reports the error differently (see http://bugs.mysql.com/22716) however the testcase below was performed on 5.1.14 How to repeat: mysql> use test Database changed mysql> create unable to open table fm1 (i int) engine=merge union(nmt1); Query OK, 0 rows affected (0.34 sec) mysql> select * from fm1; ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist mysql> show create table fm1; ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist mysql> show table status like "fm1"\G *************************** 1. row *************************** Name: fm1 Engine: NULL Version: NULL Row_format: NULL Rows: NULL Avg_row_length: NULL Data_length: NULL Max_data_length: NULL Index_length: NULL Data_free: NULL Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: NULL Checksum: NULL Create_options: NULL Comment: Unable to open underlying table which is differently defined or of non-MyISAM ty (the comment was truncated by the server, not me) Suggested fix: This bug is a side-effect of not fixing http://bugs.mysql.com/22716 While I can agree with Monty that it can -in theory- be useful to be able to define a merge table and then later create the underlying tables, the above behaviour makes it very difficult
for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Ask a Question Ask for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Expand Search Submit Close Search Login Join Today Products BackProducts Gigs Live Careers Vendor Services Groups Website Testing Store Headlines Experts Exchange > Questions > to better understand MERGE STORAGE ENGINE in Ver. 5.6 MySQL but can not even desc table see errors Want to Advertise Here? Solved to better understand MERGE STORAGE ENGINE in Ver. 5.6 MySQL but can not even desc table see errors Posted on 2014-02-27 MySQL Server 1 Verified Solution 4 Comments 603 Views Last Modified: 2014-03-01 CREATE TABLE NACountry SELECT Code, Name FROM Country WHERE Continent = 'North alter nacountry engine=MyIsam charset=latin1; CREATE TABLE SACountry SELECT Code, Name FROM Country WHERE Continent = 'South alter nacountry engine=MyIsam charset=latin1; America'; CREATE TABLE NorthAndSouth (Code CHAR( 3) NOT NULL, Name CHAR( 52) NOT NULL) ENGINE = MERGE UNION = (NACountry, SACountry) charset=latin1; select count(1) from northandsouth; ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist desc northandsouth; ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist So far no idea why this simple example from sample world world db does not work Any ideas? 0 Question by:Robert Silver Facebook Twitter LinkedIn Google LVL 34 Active today Best Solution byDan Craciun Repeated on XAMPP, with phpmyadmin. (CODE) (CODE) (CODE) (CODE) HTH, Dan Go to Solution 4 Comments LVL 34 Overall: Level 34 MySQL Server 10 Message Active today Accepted Solution by:Dan Craciun2014-02-27 Repeated on XAMPP, with phpmyadmin. CREATE TABLE IF NOT EXISTS `country` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `code` char(3) NOT NULL, `name` char(52) NOT NULL, `continent` char(50) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `code` (`code`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `country` -- INSERT INTO `country` (`id`, `code`, `name`, `continent`) VALUES (1, 'USA', 'United States', 'North America'), (2, 'CAN', 'Canada', 'North America'), (3, 'BRA', 'Brasil', 'South America'), (4, 'VEN', 'Venezuela', 'S