Merge 2 Tables from different databases under the same user

[oltr3oceano]

Hi guys, I need some help here, please...

I dont know why I am getting this error, I think this morning i ran this query without errors and it worked.. or maybe I tweaked it a little and now I can't remember ((((

I am trying to merge two different tables from two different databases under the same user into one table, the tables have the same fields, but different values, and I want a big table with the fields from the 2 merged tables.

Lets call the 2 databases db1 and db1

I am using:

CREATE TABLE jos_camp_merged engine = MERGE UNION = (db1.jos_camp_contacts,db2.jos_camp_contacts);
And i get

"#1113 - A table must have at least 1 column "

Do you know what I am doing wrong please??? Thanks in advance.

[eval(BadCode)]

From dev.mysql.com:

The MERGE storage engine, also known as the MRG_MyISAM engine, is a collection of identical MyISAM tables that can be used as one. “Identical” means that all tables have identical column and index information. You cannot merge MyISAM tables in which the columns are listed in a different order, do not have exactly the same columns, or have the indexes in different order.

example:

mysql> CREATE TABLE t1 (
    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    message CHAR(20)) ENGINE=MyISAM;
mysql> CREATE TABLE t2 (
    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    message CHAR(20)) ENGINE=MyISAM;
mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
mysql> CREATE TABLE total (
    ->    a INT NOT NULL AUTO_INCREMENT,
    ->    message CHAR(20), INDEX(a))
    ->    ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

also, this might get you (i would have hoped innoDB could be merged anyways):

if you create a MERGE table from non-MyISAM tables, or if you drop an underlying table or alter it to be a non-MyISAM table, no error for the MERGE table occurs until later when you attempt to use it.

basically... you need to have two identical MyISAM tables AND cross database permissions (maybe you ran it as root, and later used a different account?)