3 messages in com.mysql.lists.bugsRe: Merge Table always "IN USE" after...| From | Sent On | Attachments |
|---|---|---|
| Sebastian Bergmann | 21 Apr 2003 02:16 |  .txt |
| Benjamin Pflugmann | 22 Apr 2003 11:01 | |
| Sebastian Bergmann | 22 Apr 2003 14:14 |
| Subject: | Re: Merge Table always "IN USE" after creation |
|---|---|
| From: | Benjamin Pflugmann (benj...@pflugmann.de) |
| Date: | 04/22/2003 11:01:13 AM |
| List: | com.mysql.lists.bugs |
Hi.
On Mon 2003-04-21 at 11:16:49 +0200, lis...@sebastian-bergmann.de wrote:
After executing the attached series of CREATE TABLE queries phpMyAdmin always shows "IN USE" as status for the pot_accesslog table.
For instance a
SHOW KEYS FROM `pot_accesslog`
query results in a
Can't open file: 'pot_accesslog.MRG'. (errno: 143)
$ perror 143 Error code 143: Unknown error 143 143 = Conflicting table definition between MERGE and mapped table
[...]
CREATE TABLE IF NOT EXISTS pot_accesslog_200312 ( accesslog_id INT(11) NOT NULL, timestamp INT(10) UNSIGNED NOT NULL, document_id INT(11) NOT NULL, exit_target_id INT(11) DEFAULT '0' NOT NULL, entry_document TINYINT(3) UNSIGNED NOT NULL,
KEY accesslog_id (accesslog_id), KEY timestamp (timestamp), KEY document_id (document_id), KEY exit_target_id (exit_target_id) ) DELAY_KEY_WRITE=1
CREATE TABLE IF NOT EXISTS pot_accesslog ( accesslog_id INT(11) NOT NULL, visitor_id INT(11) NOT NULL, client_id INT(10) UNSIGNED NOT NULL, operating_system_id INT(11) NOT NULL, user_agent_id INT(11) NOT NULL, host_id INT(11) NOT NULL, referer_id INT(11) NOT NULL, timestamp INT(10) UNSIGNED NOT NULL, returning_visitor TINYINT(3) UNSIGNED NOT NULL,
Here, the merge table definition has serveral fields (e.g. visitor_id) which are not part of the underlying tables. So error 143 is to be expected. Fix the table defintion for the merge table to fit the underlying tables and everything should work.
PRIMARY KEY (accesslog_id),
KEY client_time (client_id, timestamp)
) TYPE=MRG_MyISAM
UNION=(pot_accesslog_200304,pot_accesslog_200305,pot_accesslog_200306,pot_accesslog_200307,pot_accesslog_200308,pot_accesslog_200309,pot_accesslog_200310,pot_accesslog_200311,pot_accesslog_200312)
[...]
HTH,
Benjamin.
-- benj...@pflugmann.de