MySQL Mrg_myisam Engine Error Resolution

Error: Unable to open underlying table which is differently defined or of Non-myisam type or doesn ' t exist

So when encountering unable to open underlying table which is differently defined or of Non-myisam type or doesn ' t exist error, you need to start with the following: 1 , to see if there are tables that are not MyISAM engines, because the merge engine applies only to MyISAM table 2, to see if there are tables that do not exist in the Union table. 3. See if the merge refers to a table that is not in the same library, and the table does not specify a database name. 4. Compare the structure of each table (index, engine, column, character set, etc.) to see if it is consistent. Mrg_myisam Engine: CREATE TABLE ' pvlogs_merge_test ' (' id ' varchar) NOT NULL, ' member_id ' int (one) DEFAULT NULL, ' jsession ' in T (one) default null, ' IP ' bigint (() default null, ' search_id ' bigint () default null, ' info_id ' bigint (all) default null, ' LA Stmodify ' timestamp null default NULL, ' disc ' int (one) not null, ' status ' int (one) not null default ' 0 ' COMMENT ' when the page (HTML) is open, this attribute would set 1 ', PRIMARY key (' ID '), key ' info_id ' (' info_id '), key ' member_id ' (' member_id '), key ' IP ' (' IP ')) Engine=mrg_myisam DEFAULT Charset=utf8 union= (' pvlogs_20170313 ', ' pvlogs_20170312 ', ' pvlogs_20170311 ', ' pvlogs_20170310 ', ' pvlogs_20170309 ', ' pvlogs_20170308 ', ' pvlogs_20170307 ', ' pvlogs_20170306 ', ' pvlogs_20170305 ', ' pvlogs_20170304 ', ' pvlogs_20170303', ' pvlogs_20170302 ', ' pvlogs_20170301 ', ' pvlogs_20170228 ', ' pvlogs_20170227 ', ' pvlogs_20170226 ', ' pvlogs_20170225 ', ' pvlogs_20170224 ', ' pvlogs_20170223 ', ' pvlogs_20170222 ', ' pvlogs_20170221 ', ' pvlogs_20170220 ', ' pvlogs_20170219 ', ' pvlogs_20170218 ', ' pvlogs_20170217 ', ' pvlogs_20170216 ', ' pvlogs_20170215 ', ' pvlogs_20170214 ', ' pvlogs_20170213 ', ' pvlogs_20170212 ', ' pvlogs_20170211 ', ' pvlogs_20170210 ', ' pvlogs_20170209 ', ' pvlogs_20170208 ', ' pvlogs_20170207 ', ' pvlogs_20170206 ', ' pvlogs_20170205 ', ' pvlogs_20170204 ', ' pvlogs_20170203 ', ' pvlogs_20170202 ', ' pvlogs_20170201 ', ' pvlogs_20170131 ', ' pvlogs_20170130 ', ' pvlogs_20170129 ', ' pvlogs_20170128 ', ' pvlogs_20170127 ', ' pvlogs_20170126 ', ' pvlogs_20170125 ', ' pvlogs_20170124 ', ' pvlogs_20170123 ', ' pvlogs_20170122 ', ' pvlogs_20170121 ', ' pvlogs_20170120 ', ' pvlogs_20170119 ', ' pvlogs_20170118 ', ' pvlogs_20170117 ', ' pvlogs_20170116 ', ' pvlogs_20170115 ', ' pvlogs_20170114 ', ' pvlogs_20170113 ');  because the day of the pvlogs_20170310 table was not created successfully. Cause the problem of this pvlogs_merge_test ...   about Mrg_myisam do partition table .... Organized from the network .... Generally, when the data in our database exceeds100w Records should consider the table or partition, this time I would like to elaborate on some of the methods of the table. The methods I know at the moment are MyISAM, innodb How to do the tables and keep the transactions and foreign keys, I don't quite know.   First of all, we need to figure out how many tables to divide, but the premise is to satisfy the application. Here I used a relatively simple table method, that is, according to the mantissa of the self-increment ID points, that is, 0-9 total 10 tables, the value is also very good to do, that is, 10 to take the mold. In addition, can be based on the MD5 value of a field to take a few of them to the table, so that can be divided into a lot of tables. OK, let's create the table, the code is as follows  createtable ' test '. ' Article_0 ' (' ID ' BIGINT () notnull, ' Subject ' VARCHAR (+) Notnull, ' Conte NT ' Textnotnull, PRIMARYKEY (' id ')) ENGINE = MYISAM CHARACTERSET UTF8 COLLATE utf8_general_ci createtable ' test '. ' Arti Cle_1 ' (' ID ' BIGINT () notnull, ' Subject ' VARCHAR (notnull), ' content ' Textnotnull, PRIMARYKEY (' id ')) ENGI NE = MYISAM CHARACTERSET UTF8 COLLATE utf8_general_ci createtable ' test '. ' Article_2 ' (' ID ' BIGINT () notnull, ' Subjec T ' VARCHAR (notnull), ' content ' Textnotnull, PRIMARYKEY (' id ')) ENGINE = MYISAM CHARACTERSET UTF8 COLLATE utf8_g Eneral_ci createtable ' test '. ' Article_3 ' (' ID ' BIGINT () notnull, ' Subject ' VARCHAR () notnull, ' content ' Textno Tnull, PRIMARYKEY (' id ')) ENGINE = MYISAM CHARACTERSET UTF8 COLLATE utf8_general_ci createtable ' test '. ' Article_4 ' (' ID ' BIGINT () notnull, ' subject ' Varc HAR (notnull), ' content ' Textnotnull, PRIMARYKEY (' id ')) ENGINE = MYISAM CHARACTERSET UTF8 COLLATE utf8_general_ CI createtable ' test '. ' Article_5 ' (' ID ' BIGINT () notnull, ' Subject ' VARCHAR () notnull, ' content ' textnotnull, PRIMARYKEY (' id ')) ENGINE = MYISAM CHARACTERSET UTF8 COLLATE utf8_general_ci createtable ' test '. ' Article_6 ' (' ID ' BIG INT (Notnull), ' Subject ' VARCHAR (notnull), ' content ' Textnotnull, PRIMARYKEY (' id ')) ENGINE = MYISAM CHARA Cterset UTF8 COLLATE utf8_general_ci createtable ' test '. ' Article_7 ' (' ID ' BIGINT () notnull, ' Subject ' VARCHAR (200) Notnull, ' content ' Textnotnull, PRIMARYKEY (' id ')) ENGINE = MYISAM CHARACTERSET UTF8 COLLATE utf8_general_ci createt ABLE ' test '. ' Article_8 ' (' ID ' BIGINT () notnull, ' Subject ' VARCHAR () notnull, ' content ' Textnotnull, Primaryke Y (' id ')) ENGINE = MYISAM CHARACTERSET UTF8 COLLATE utf8_general_ci createtable ' test '. ' Article_9 ' (' ID ' BIGINT () notnull, ' subject ' Varc HAR (notnull), ' content ' Textnotnull, PRIMARYKEY (' id ')) ENGINE = MYISAM CHARACTERSET UTF8 COLLATE utf8_general_ ci   okay. 10 tables created, it should be noted that the ID can not be set to self-increment, and all the table structure must be consistent, including structure, type, length, the order of the fields must be consistent then how to get this ID? I'll explain in detail later. Now, we need a merged table for querying, and the code to create the merged table is as follows CreateTable ' test '. ' article ' (' ID ' BIGINT () notnull, ' Subject ' VARCHAR (+) Notnull, ' Content ' Textnotnull, PRIMARYKEY (' id ')) engine=mrg_myisam DEFAULT Charset=utf8 insert_method=0union= (' article_0 ', ' a Rticle_1 ', ' article_2 ', ' article_3 ', ' article_4 ', ' article_5 ', ' article_6 ', ' article_7 ', ' article_8 ', ' article_9 ');     Here insert_method=0 in some versions may not work, need to change to Insert_method=no note that the merged table must also have the same structure as the previous table, type, length, including the order of the fields must be consistent here Insert_ Method=0 indicates that insert operations on this table are not allowed. Well, when we need to query, we can only operate on the article table, that is, the table can only be a select operation   then for the insert that is how the insert operation should be, the first is to obtain a unique ID, Here you will also need a table to create the ID specifically, the code below createtable ' test '. ' create_id ' (' ID ' biginT () notnull auto_increment PRIMARYKEY) ENGINE = myisam  also   that is, when we need to insert the data, we must generate the ID value from this table, My PHP code method is as follows function get_ai_id () {$sql = "insert into create_id (ID) VALUES (") "; $this->db->query ($sql); return$t His->db->insertid (); }   OK, now suppose we want to insert a piece of data, what should we do? Go ahead and look at the code. Function New_article () {$id = $this->get_ai_id (); $table _name = $this->get_table_name ($id); $sql = "Insert into {$table _name} (id,subject,content) VALUES (' {$id} ', ' Test title ', ' Test content '); $this->db->query ($sql); } /** * is used to get the table name by ID */function get_table_name ($id) {return ' Article_ '. Intval ($id)%10;} In fact, it's very simple, right, is to get the ID, and then according to the ID to get into which table, then it is very simple. For update operation I guess I don't need to say it anymore, just have the ID, then get the table name, and then do the update operation.

more content:http://www.wangzhanjianshegs.com/website Construction

MySQL Mrg_myisam Engine Error Resolution