MySQL Forums - Merge Storage Engine

New merge table issue with underlying table (no replies)

Rob Brown — Tue, 09 Apr 2013 11:16:50 +0000

I have inherited a database running on 5.0.96 with some merge tables that are showing errors when trying to upgrade to 5.1.67

Even when trying to drop the existing merge table and recreate it, I still get the same errors with the underlying tables, which appear to be fine...

mysql> repair table foo_20070101;
+------------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------------+--------+----------+----------+
| s123.r2_click_20070101 | repair | status | OK |
+------------------------+--------+----------+----------+
1 row in set (40.35 sec)

Hence, this table is healthy.

CREATE TABLE foo_20070131 LIKE foo_20070101;

ALTER TABLE foo_20070131 DROP PRIMARY KEY, ADD KEY (click_id);

ALTER TABLE foo_20070131 ENGINE=MERGE;

ALTER TABLE foo_20070131 UNION=(foo_20070101);

ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist.

Is there anything more I can do to investigate the issue, or to try and "fix" the apparently-fine underlying table?

Thanks,
Rob

Identifying underlying table in merge-table (no replies)

Markus Tervooren — Sun, 11 Dec 2011 16:14:29 +0000

Is it possible to add an extra column to the merge table that has a certain value depending on the underlying table this row belongs to? I could add this extra column to every underlying table and initialitze it for every table, but that would be a waste of disk space.

Retreivcing data that was not not saved into table due to varchar limit (no replies)

Anjum Dhamial — Wed, 21 Sep 2011 15:44:42 +0000

Hi,

Can any one please help me on this ?

I developed a website for my client where users were entering into the competition. While filling one form, user were suppose to enter description in text area. The field against that text area in Database table is varchar (512), but the users entered description longer than varchar 512, so we lost some par of their description.
We really need to see what was the description entered by the user.
Well i am thinking of any log file that will have log of all queries that were requested, i may able to see what user actually entered and was queried to mysql.

Can any please tell me if there is any other way of retrieving the description or if there is only log file that can help me, where to get that log file ?

Any help will be highly appreciated.

Thanks
Anjum Dhamial

Merge vs. UNION ALL (no replies)

James McKelvey — Tue, 05 Apr 2011 16:33:12 +0000

Suppose that instead of using the MERGE engine I instead modified my code to UNION ALL the shards.

Would I get worse performance? In other words, besides the convenience, does the MERGE engine have specific performance optimizations that make it perform better?

This is of interest for several reasons. First, my code could determine in some cases that only a few (or a single) shards were needed. Second, I could apply the shard technique to InnoDB tables. Third, I could apply the shard technique to databases that were not identical.

Index Caching (no replies)

James McKelvey — Tue, 05 Apr 2011 14:33:10 +0000

We are currently evaluating the merge engine. Right now we create and preload several index caches.

But what is the best way to approach this with a merged table? Do I create a single index cache and assign all of the shards to it?

Or do I create a separate index cache for each shard?

Cardinality is NULL on Merge Table's indexes (no replies)

Matt Leech — Wed, 26 Jan 2011 10:45:11 +0000

I have one merge table comprised of two child tables. The index cardinality on the child tables is correct but the cardinality for most of the indexes on the merge table report NULL.

I have run analyze table on the child tables (can't run analyze on the merge table, "The storage engine for the table doesn't support analyze") and dropped/recreated all the indexes on the merge and child tables, but the cardinality on the merge table stubbornly remains NULL.

MySQL version 5.1.48.

These are my tables:
(I can't find a quick and easy way to format the data below so that it's easy to read, so here it is in an HTML table)
http://mattleech.com/bkwk.html

Merge table

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Index_type
Merge_Table 0 idx_Delivery_Item_Plnt 1 Delivery A NULL BTREE
Merge_Table 0 idx_Delivery_Item_Plnt 2 Item A NULL BTREE
Merge_Table 0 idx_Delivery_Item_Plnt 3 Plnt A NULL BTREE
Merge_Table 1 idx_SalesDoc 1 SalesDoc A 21367368 BTREE
Merge_Table 1 idx_Createdon 1 Createdon A 5486 BTREE
Merge_Table 1 idx_Route_Createdon_DlvTy 1 Route A 947 BTREE
Merge_Table 1 idx_Route_Createdon_DlvTy 2 Createdon A 161874 BTREE
Merge_Table 1 idx_Route_Createdon_DlvTy 3 DlvTy A NULL BTREE
Merge_Table 1 idx_Soldtopt_Createdon_ShipToPt 1 Soldtopt A NULL BTREE
Merge_Table 1 idx_Soldtopt_Createdon_ShipToPt 2 Createdon A NULL BTREE
Merge_Table 1 idx_Soldtopt_Createdon_ShipToPt 3 ShipToPt A NULL BTREE
Merge_Table 1 idx_Material 1 Material A NULL BTREE

Child table 1

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Index_type
Child_Table_1 0 idx_Delivery_Item_Plnt 1 Delivery A 9432348 BTREE
Child_Table_1 0 idx_Delivery_Item_Plnt 2 Item A 9432348 BTREE
Child_Table_1 0 idx_Delivery_Item_Plnt 3 Plnt A 9432348 BTREE
Child_Table_1 1 idx_SalesDoc 1 SalesDoc A 4716174 BTREE
Child_Table_1 1 idx_Createdon 1 Createdon A 53899 BTREE
Child_Table_1 1 idx_Route_Createdon_DlvTy 1 Route A 53899 BTREE
Child_Table_1 1 idx_Route_Createdon_DlvTy 2 Createdon A 65960 BTREE
Child_Table_1 1 idx_Route_Createdon_DlvTy 3 DlvTy A 66896 BTREE
Child_Table_1 1 idx_Soldtopt_Createdon_ShipToPt 1 Soldtopt A 6642 BTREE
Child_Table_1 1 idx_Soldtopt_Createdon_ShipToPt 2 Createdon A 314411 BTREE
Child_Table_1 1 idx_Soldtopt_Createdon_ShipToPt 3 ShipToPt A 377293 BTREE
Child_Table_1 1 idx_Material 1 Material A 68350 BTREE

Child table 2

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Index_type
Child_Table_2 0 idx_Delivery_Item_Plnt 1 Delivery A NULL BTREE
Child_Table_2 0 idx_Delivery_Item_Plnt 2 Item A NULL BTREE
Child_Table_2 0 idx_Delivery_Item_Plnt 3 Plnt A NULL BTREE
Child_Table_2 1 idx_SalesDoc 1 SalesDoc A 16651194 BTREE
Child_Table_2 1 idx_Createdon 1 Createdon A 2161 BTREE
Child_Table_2 1 idx_Route_Createdon_DlvTy 1 Route A 370 BTREE
Child_Table_2 1 idx_Route_Createdon_DlvTy 2 Createdon A 86275 BTREE
Child_Table_2 1 idx_Route_Createdon_DlvTy 3 DlvTy A 92250 BTREE
Child_Table_2 1 idx_Soldtopt_Createdon_ShipToPt 1 Soldtopt A 15980 BTREE
Child_Table_2 1 idx_Soldtopt_Createdon_ShipToPt 2 Createdon A 1585828 BTREE
Child_Table_2 1 idx_Soldtopt_Createdon_ShipToPt 3 ShipToPt A 1850132 BTREE
Child_Table_2 1 idx_Material 1 Material A 42101 BTREE

Design Advice for archiving (no replies)

F Simons — Thu, 20 Jan 2011 03:52:50 +0000

Hi guys, can you please advise with some design advice with the following situation: We have a 1 - 6 million record table where only 5000 records are actively updates. The rest of the records are read only.

We're thinking of archiving these records to another table and using merge tables. ie to take advantage of faster optimizes on the active table, and possible target indexes.

I'm also concerned about performing the archive. ie 1. insert/select from active table to archive table and then delete the copied records from the active table. I've done some profiling and the delete is a bottleneck when the volume is high. I've extended my design (which I explain below) to manage active 2 tables which I flip so I can truncate an entire table when archiving.

Is the following a good solution for archiving records and should I be using merge tables?

Tables:
- active1
- active2
- archive
- mergetable (merge table union=(archive, active1, active2), insert_method=last

Archive Process (assuming target table is active2):
1. lock tables
2. Copy records to archive from active1 to archive
3. flush tables
4. Truncate table active1
5. flush tables
6. set union=(archive, active2, active1). ie flip active1 and active2 so active1 is now the active table

When the archive process is run the next time, the logic is flipped to consider active 1 as the target table. ie data is copied from active2 and active2 is truncated.

Am I on the right track with this guys?

Using MySQL Partitioning Instead of MERGE Tables (no replies)

Edwin DeSouza — Sat, 24 Jul 2010 21:33:23 +0000

Using MySQL Partitioning Instead of MERGE Tables
http://www.pythian.com/news/14971/using-mysql-partitioning-instead-of-merge-tables/

The partition helper - Improving usability with MySQL 5.1 partitioning
http://datacharmer.blogspot.com/2008/12/partition-helper-improving-usability.html

Low cost price table (no replies)

St Artchibald — Sat, 10 Apr 2010 16:06:11 +0000

I try to add one Index on a merge table

Adding index on a merged table "breaks" merge functionality (no replies)

Orna Weisman — Thu, 17 Dec 2009 20:04:45 +0000

Hi

I created two base myIsam tables with two columns and no indexes and a merge table for these two as :

create table test_merge...
...
ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST
UNION=(`test1`,`test2`)

All works fine until I added an index on test1.
Once I did that all inserts into the merge table were not added to test1, but were added to the test_merge table only - like it is a regular table and not a merge table.

However - if I dropped the merge table and recreated it, then all subsequent inserts were inserted correctly to the base table ( even though the index was still there on test1 ).

Is this an expected behavior ?
mysql version is 5.0.27

Recommended backup for MyISAM (no replies)

Krzysztof Retel — Fri, 27 Nov 2009 11:19:56 +0000

Hi All,

I was looking around the forum to find out which backup method MySQL Community recommends for the MyISAM DB.

We have two DBs (A and B), where one (A) is design to have heavy inserts where each insert fires a trigger to populate data across to some tables in other DB.
The second DB (B) has a heavy usage for CRUD operations.

Requirements:
1. Do not lock neither of these two DBs.
2. Minimise the impact of lossing data.
3. Perform backup every night.
4. Truncate the CRUD related DB (B) say every 7 or 31 days.

My thoughts are really to have a two instances of MySQL server on same machine. Do a replication from Master to Slave, when doing backup put down slave, do backup, restore Slave. However, I am not sure about truncating the slave and master DBs.

I would appreciated any responses recommendations.

Thanks,
Krzysztof

add Index on a merge table (no replies)

Laurent DANIELE — Fri, 06 Nov 2009 09:59:46 +0000

I try to add one Index on a merge table
ALTER TABLE `TABLE_MERGE` ADD INDEX `Index_1`(`CODE`);
I try after create the Index on the merged table (with same name or différent name)
ALTER TABLE `TABLE1` ADD INDEX `Index_1`(`CODE`);
ALTER TABLE `TABLE2` ADD INDEX `Index_1`(`CODE`);
or and without this index

and I have always the error 'Mysql Error number 1168'.

could you help me...

Consolidating servers (no replies)

Steve Steve — Mon, 19 Oct 2009 18:41:15 +0000

We're consolidating mysql servers and I need a quick way to move the contents of server A to server B. Server B has it's own databases and tables and is running but can be stopped. I've done backups before using mysqldump, but never anything like this. Can I just copy the files from the old server to the mysql data directory on the new server? Do I need to create db's and tables first? I'm looking for quick and easy...

Backup Help (1 reply)

Paul Washbrook — Wed, 30 Sep 2009 08:16:59 +0000

Hi,

We are currently undergoing a system redevelopment during which time we will be converting our main tables from MyISAM to INNODB. Part of our current backup strategy for MySQL includes copying the .frm, .myi and .myd files to a backup location, having used all of the relevent flush / lock commands recommended by MySQL. This method allows us to perform individual table restores, however it is my understanding (wrong as it might be!) that this method isn't possible for INNODB tables. So, what would be the best approach for giving me the ability to perform individual table restores, given that the tables will be INNODB and the backup could be performed from either the Master server or a replication slave.

Hope this is reasonably clear as to what i want to achieve.

Many Thanks

Paul

ERROR 2013 (HY000): Lost connection to MySQL server during query (1 reply)

Helen Yelluas — Fri, 03 Jul 2009 19:41:08 +0000

Hello everyone,

I have a problem running a simple query from merge table
The table has 22 columns , 15 keys, 3 members with exact set of indecies
Merge table has the same set.

select from each member work fine,

select count(1) from ProfilerEventMin_1441909 where DestReportObjectID=5 and SrcUserGroupID=2 and DestPort=80;
0

select count(1) from ProfilerEvent where DestReportObjectID=5 and SrcUserGroupID=2 and DestPort=80;
ERROR 2013 (HY000): Lost connection to MySQL server during query

select count(1) from ProfilerEvent where DestReportObjectID=5 and SrcUserGroupID=2 and DestPort=8;
+----------+
| count(1) |
+----------+
| 0 |
+----------+

any idea why merge table crashes on the indecies?
any setting should I check?

thank you
helen

ERROR 1168 when adding a table (1 reply)

Ricky Smart — Tue, 02 Jun 2009 18:41:37 +0000

I have 8 tables sized between 10-30GB and with around 0.5*10^9 rows. I am trying to search all of themvia a merge table. When I go up to 7 tables things are fine but when I add the eighth I get a ERROR 1168. The total number of rows is around 4.5*10^9 for all 8 tables.

Below is an example of what happens if I merge with DBS2, then DBS3, then both, I show just the error from count but it is the same error for all commands:

mysql> alter table DB2 union=(DBN0,DBN1,DBN2,DBN3,DBS0,DBS1,DBS2) ; select count(*) from DB2 ;
Query OK, 0 rows affected (0.32 sec)
Records: 0 Duplicates: 0 Warnings: 0

+------------+
| count(*) |
+------------+
| 3747089710 |
+------------+
1 row in set (0.00 sec)

mysql> alter table DB2 union=(DBN0,DBN1,DBN2,DBN3,DBS0,DBS1,DBS3) ; select count(*) from DB2 ;
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0

+------------+
| count(*) |
+------------+
| 3520529145 |
+------------+
1 row in set (0.00 sec)

mysql> alter table DB2 union=(DBN0,DBN1,DBN2,DBN3,DBS0,DBS1,DBS2,DBS3) ; select count(*) from DB2 ;
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0

ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
mysql>

merge table crash (no replies)

li kong — Sat, 21 Mar 2009 06:40:38 +0000

I am using merge tables select rows, there are 6 tables under this merge table.
after insert or delete some data in sub tables, the merge table will crash on query.

I try to use the same sql in mysql client, always crashed.
after repair the sub tables, still crashed.

But if I use the subtables in sql, it did not crash.

May some one help me find out how to fix this?

Is this a bug?

Thanks

here is the log:
[0x697c6e]
[0x4bbdb2]
[0x62e670]
[0x64d1e2]
[0x676097]
[0x5a827a]
[0x591f67]
[0x59143d]
[0x5913b7]
[0x51dc50]
[0x525e59]
[0x52c09d]
[0x53cc27]
[0x53e548]
[0x53ee79]
[0x4c643c]
[0x4cb90e]
[0x4d1b58]
[0x4d1f43]
[0x4d30f4]
[0x4c3dd7]
[0x62afe7]
[0x721db9]
090321 12:05:45 - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.
key_buffer_size=4294967296
read_buffer_size=33554432
max_used_connections=3
max_threads=151
threads_connected=3
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 14091772 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x197f0040
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x4164ef60 thread_stack 0x40000
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x197fb0c0 = SELECT count(*) FROM zfa_ic_inventory A WHERE A.CORP_ID=1 AND A.TABLE_TYPE=1
thd->thread_id=2
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
090321 12:05:45 mysqld_safe Number of processes running now: 0
090321 12:05:45 mysqld_safe mysqld restarted
090321 12:05:46 [Warning] Storage engine 'SPHINX' has conflicting typecode. Assigning value 42.
090321 12:05:46 [Note] Event Scheduler: Loaded 0 events
090321 12:05:46 [Note] /data/mysql_new/libexec/mysqld: ready for connections.
Version: '5.1.32' socket: '/tmp/mysql.sock' port: 33066 Source distribution

Physcial Backup newbie (no replies)

Ganesh Tadimalla — Fri, 30 Jan 2009 15:54:47 +0000

Hi all:
I am new to mysql...could some one clarify the below.
1.) Does OS backup tools backup MYSQL database with MYSQL running.
Or only way to backup MYSQL is to use MYSQL utilites as MYSQL dump or MYSQL hot backup.
2.)Is the MYSQL architecture simialar to sql/oracle where we have a translog file/redo log file .
So once restored from a backup do we need to apply additional logfiles to bring it point in time .

Please advise.

Import error (4 replies)

anjani sinha — Tue, 20 Jan 2009 19:36:22 +0000

Hi,

I have exported a table called "session" from MySQL 4.0 on Linux using mysqldump and trying to import in MySQL 5.0 on windows. I am getting the following errors:

E:\export>mysql -u root -pbiccadmin rtlatin < E:\export\sessions.sql
ERROR 1366 (HY000) at line 24: Incorrect string value: '\x81\x00\x00\x00\x07c...
' for column 'a_session' at row 1

Please advise.

Thanks,

Anjani

MERGE table using MARIA tables (no replies)

Hariharan Ananthakrishnan — Mon, 29 Dec 2008 19:00:33 +0000

Hi,

Since MARIA is a crash safe version of MYISAM, is it possible to create a MERGE table which is a collection of tables created with MARIA Storage Engine.

Thanks,
Hari

determine UNION=(list-of-tables) (2 replies)

r dinoff — Fri, 10 Oct 2008 07:38:05 +0000

After a merge table has been created how do you find out what the tables are in the union? "Describe table" and "show table status" both do not display the list of tables in the union.

thanks,

r dinoff

Get error 1168 on merge tables after upgrade (no replies)

Riana Rossouw — Wed, 13 Aug 2008 10:07:09 +0000

Good day

We run mysql on a RedHat Enterprise 5 IA64 server and use the mysql that comes with the RedHat Release.

We have recently upgraded from Enterprise 5.1 to Enterprise 5.2 which also upgraded our MySQL from version 5.0.22-standard-log to 5.0.45-log.

This resulted in all our Merge tables giving error 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist.

What can I do to fix this?
I have already run myisamchk -r on all the tables and have also checked the underlying tables' version (all are version 10).

Should I have performed some converions before the upgrade, or is there a utility that can be run now?

Any help will be appreciated.
Thanks
Riana

Query performance on merge tables after upgrade (1 reply)

valerie jones — Mon, 21 Jul 2008 11:32:36 +0000

Our department is primarily using Sybase, and we are all fairly new MySQL users, so we're dealing with a learning curve. We recently migrated our Data Warehouse data from Sybase to MySQL, with very good results, until we decided to upgrade. We are using the merge storage engine, as we have a large amount of data that we're dealing with.

We recently upgraded our test server from version 4.1.22 to 5.0.51, and our query performance has suffered greatly. I've already double-checked that all tables used in the merge are defined identically to the merge table. The query plans are very different (post-upgrade) when we have queries with joins. These tables are pretty large, each fs_ledgerdetail_YYYY table has between 2 & 4 million rows, and each fs_account_hier_YYYY table has over 100,000 rows.

I have a feeling that we just need to make some changes to our indexes, but I don't know where to start with this problem. Any suggestions would be greatly appreciated. Thanks!

Here is one example of a query:

SELECT dwhm_fs.fs_ledgerdetail.monthend_date AS 'Monthend Date' ,
dwhm_fs.fs_ledgerdetail.account_code AS 'Account Code' ,
dwhm_fs.fs_ledgerdetail.fund_code AS 'Fund Code' ,
dwhm_fs.fs_ledgerdetail.sub_code AS 'Sub Code' ,
dwhm_fs.fs_ledgerdetail.object_code AS 'Object Code' ,
dwhm_fs.fs_ledgerdetail.trans_description AS 'Transaction Description' ,
dwhm_fs.fs_ledgerdetail.trans_amount AS 'Transaction Amount'
FROM dwhm_fs.fs_ledgerdetail, dwhm_fs.fs_account_hier
WHERE (dwhm_fs.fs_ledgerdetail.location_code ='9')
AND (dwhm_fs.fs_account_hier.department_code = '0302')
AND dwhm_fs.fs_ledgerdetail.monthend_date between '2007-1-31' and '2008-1-31'
AND dwhm_fs.fs_ledgerdetail.monthend_date=dwhm_fs.fs_account_hier.monthend_date
AND dwhm_fs.fs_ledgerdetail.location_code=dwhm_fs.fs_account_hier.location_code
AND dwhm_fs.fs_ledgerdetail.account_code=dwhm_fs.fs_account_hier.account_code
ORDER BY dwhm_fs.fs_ledgerdetail.monthend_date

The explain for this SQL in version 4.1.22:
1 SIMPLE fs_account_hier ref PRIMARY,XPKlocation_account,XAKaccount,XAKdepartment,XAKhi XAKdepartment 4 const 848 Using where; Using temporary; Using filesort
1 SIMPLE fs_ledgerdetail ref PRIMARY,XAKfs_detail_indx,XPKfs_ledgerdetail,XAKaccount XAKaccount 9 dwhm_fs.fs_account_hier.account_code,dwhm_fs.fs_account_hier.monthend_date 54 Using where

The explain in version 5.0.51:
SIMPLE fs_ledgerdetail range PRIMARY,XAKfs_detail_indx,XPKfs_ledgerdetail,XAKaccount XPKfs_ledgerdetail 3 4355427 Using where
SIMPLE fs_account_hier ref PRIMARY,XPKlocation_account,XAKaccount,XAKdepartment,XAKhi XAKaccount 6 dwhm_fs.fs_ledgerdetail.account_code 16 Using where

Each merge table has 14 underlying tables, here are examples of those:

CREATE TABLE `fs_ledgerdetail_9899` (
`monthend_date` date NOT NULL default '0000-00-00',
`dataclass_code` char(1) NOT NULL default '',
`location_code` char(1) NOT NULL default '',
`linear_month` char(3) NOT NULL default '',
`typeentry_code` char(2) NOT NULL default '',
`point_entry` char(4) NOT NULL default '',
`trans_id` char(6) NOT NULL default '',
`sequence` int(11) NOT NULL default '0',
`account_code` char(6) NOT NULL default '',
`cost_center` char(2) NOT NULL default '',
`fund_code` char(5) NOT NULL default '',
`project_code` char(6) NOT NULL default '',
`sub_code` char(2) NOT NULL default '',
`object_code` char(4) NOT NULL default '',
`source_code` char(6) NOT NULL default '',
`doc_date` date NOT NULL default '0000-00-00',
`trans_description` char(20) NOT NULL default '',
`trans_reference` char(10) NOT NULL default '',
`partial_full_flag` char(1) NOT NULL default '',
`trans_amount` decimal(10,2) NOT NULL default '0.00',
`subsystem_id` char(1) NOT NULL default '',
`period_close_flag` char(1) NOT NULL default '',
`trans_close_flag` char(1) NOT NULL default '',
`post_date` date NOT NULL default '0000-00-00',
`final_flag` char(1) NOT NULL default '',
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`monthend_date`,`final_flag`,`location_code`,`typeentry_code`,`point_entry`,`trans_id`,`dataclass_code`,`sequence`),
UNIQUE KEY `XAKfs_detail_indx` (`monthend_date`,`final_flag`,`location_code`,`typeentry_code`,`point_entry`,`trans_id`,`dataclass_code`,`sequence`),
KEY `XPKfs_ledgerdetail_9899` (`monthend_date`,`final_flag`,`account_code`,`fund_code`,`location_code`),
KEY `XAKaccount` (`account_code`,`monthend_date`,`final_flag`,`location_code`),
KEY `XAKfund` (`fund_code`,`monthend_date`,`final_flag`,`location_code`),
KEY `XAKproject` (`project_code`,`monthend_date`,`final_flag`),
KEY `XAKsub` (`sub_code`,`monthend_date`,`final_flag`,`location_code`),
KEY `XAKobject` (`object_code`,`monthend_date`,`final_flag`,`location_code`),
KEY `XAKtypeentry` (`typeentry_code`,`monthend_date`,`final_flag`,`location_code`),
KEY `XAKaccount_dataclass` (`dataclass_code`,`account_code`,`monthend_date`,`final_flag`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `fs_account_hier_9899` (
`monthend_date` date NOT NULL default '0000-00-00',
`location_code` char(1) NOT NULL default '',
`account_code` char(6) NOT NULL default '',
`cost_center` char(2) NOT NULL default '',
`account_title` char(40) NOT NULL default '',
`sub_ledger_flag` char(1) NOT NULL default '',
`sub_ledger_history_flag` char(1) NOT NULL default '',
`sub_ledger_reference_code` char(1) NOT NULL default '',
`dummy_flag` char(1) NOT NULL default '',
`account_group_title1` char(18) NOT NULL default '',
`annual_report_code` char(6) NOT NULL default '',
`uas_code` char(6) NOT NULL default '',
`discipline_code` char(3) NOT NULL default '',
`jag_code` char(6) NOT NULL default '',
`account_group_code` char(6) NOT NULL default '',
`account_group_title2` char(35) NOT NULL default '',
`nsf_code` char(3) NOT NULL default '',
`sau_code` char(1) NOT NULL default '',
`program_code` char(2) NOT NULL default '',
`function_code` char(2) NOT NULL default '',
`account_responsibility` char(20) NOT NULL default '',
`home_department_code` char(6) NOT NULL default '',
`capitalization_code` char(1) NOT NULL default '',
`sub_department` char(8) NOT NULL default '',
`low_level1` char(8) NOT NULL default '',
`low_level2` char(8) NOT NULL default '',
`low_level3` char(8) NOT NULL default '',
`low_level4` char(8) NOT NULL default '',
`bal_sheet_account_closed_flag` char(1) NOT NULL default '',
`asset_number` char(5) default NULL,
`organization_code` char(4) NOT NULL default '',
`organization_title` char(40) default NULL,
`division_code` char(4) NOT NULL default '',
`division_title` char(40) default NULL,
`subdivision_code` char(4) NOT NULL default '',
`subdivision_title` char(40) default NULL,
`department_code` char(4) NOT NULL default '',
`department_title` char(40) default NULL,
PRIMARY KEY (`account_code`,`monthend_date`,`location_code`,`cost_center`),
UNIQUE KEY `XPKlocation_account` (`monthend_date`,`location_code`,`account_code`,`cost_center`),
KEY `XAKaccount` (`account_code`),
KEY `XAKdepartment` (`department_code`),
KEY `XAKfunction` (`function_code`),
KEY `XAKhi` (`department_code`),
KEY `XAKorganization` (`organization_code`),
KEY `XAKdivision` (`division_code`),
KEY `XAKsubdivision` (`subdivision_code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

The merge table definitions are:

CREATE TABLE `fs_ledgerdetail` (
`monthend_date` date NOT NULL default '0000-00-00',
`dataclass_code` char(1) NOT NULL default '',
`location_code` char(1) NOT NULL default '',
`linear_month` char(3) NOT NULL default '',
`typeentry_code` char(2) NOT NULL default '',
`point_entry` char(4) NOT NULL default '',
`trans_id` char(6) NOT NULL default '',
`sequence` int(11) NOT NULL default '0',
`account_code` char(6) NOT NULL default '',
`cost_center` char(2) NOT NULL default '',
`fund_code` char(5) NOT NULL default '',
`project_code` char(6) NOT NULL default '',
`sub_code` char(2) NOT NULL default '',
`object_code` char(4) NOT NULL default '',
`source_code` char(6) NOT NULL default '',
`doc_date` date NOT NULL default '0000-00-00',
`trans_description` char(20) NOT NULL default '',
`trans_reference` char(10) NOT NULL default '',
`partial_full_flag` char(1) NOT NULL default '',
`trans_amount` decimal(10,2) NOT NULL default '0.00',
`subsystem_id` char(1) NOT NULL default '',
`period_close_flag` char(1) NOT NULL default '',
`trans_close_flag` char(1) NOT NULL default '',
`post_date` date NOT NULL default '0000-00-00',
`final_flag` char(1) NOT NULL default '',
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`monthend_date`,`final_flag`,`location_code`,`typeentry_code`,`point_entry`,`trans_id`,`dataclass_code`,`sequence`),
UNIQUE KEY `XAKfs_detail_indx` (`monthend_date`,`final_flag`,`location_code`,`typeentry_code`,`point_entry`,`trans_id`,`dataclass_code`,`sequence`),
KEY `XPKfs_ledgerdetail` (`monthend_date`,`final_flag`,`account_code`,`fund_code`,`location_code`),
KEY `XAKaccount` (`account_code`,`monthend_date`,`final_flag`,`location_code`),
KEY `XAKfund` (`fund_code`,`monthend_date`,`final_flag`,`location_code`),
KEY `XAKproject` (`project_code`,`monthend_date`,`final_flag`),
KEY `XAKsub` (`sub_code`,`monthend_date`,`final_flag`,`location_code`),
KEY `XAKobject` (`object_code`,`monthend_date`,`final_flag`,`location_code`),
KEY `XAKtypeentry` (`typeentry_code`,`monthend_date`,`final_flag`,`location_code`),
KEY `XAKaccount_dataclass` (`dataclass_code`,`account_code`,`monthend_date`,`final_flag`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST

CREATE TABLE `fs_account_hier` (
`monthend_date` date NOT NULL default '0000-00-00',
`location_code` char(1) NOT NULL default '',
`account_code` char(6) NOT NULL default '',
`cost_center` char(2) NOT NULL default '',
`account_title` char(40) NOT NULL default '',
`sub_ledger_flag` char(1) NOT NULL default '',
`sub_ledger_history_flag` char(1) NOT NULL default '',
`sub_ledger_reference_code` char(1) NOT NULL default '',
`dummy_flag` char(1) NOT NULL default '',
`account_group_title1` char(18) NOT NULL default '',
`annual_report_code` char(6) NOT NULL default '',
`uas_code` char(6) NOT NULL default '',
`discipline_code` char(3) NOT NULL default '',
`jag_code` char(6) NOT NULL default '',
`account_group_code` char(6) NOT NULL default '',
`account_group_title2` char(35) NOT NULL default '',
`nsf_code` char(3) NOT NULL default '',
`sau_code` char(1) NOT NULL default '',
`program_code` char(2) NOT NULL default '',
`function_code` char(2) NOT NULL default '',
`account_responsibility` char(20) NOT NULL default '',
`home_department_code` char(6) NOT NULL default '',
`capitalization_code` char(1) NOT NULL default '',
`sub_department` char(8) NOT NULL default '',
`low_level1` char(8) NOT NULL default '',
`low_level2` char(8) NOT NULL default '',
`low_level3` char(8) NOT NULL default '',
`low_level4` char(8) NOT NULL default '',
`bal_sheet_account_closed_flag` char(1) NOT NULL default '',
`asset_number` char(5) default NULL,
`organization_code` char(4) NOT NULL default '',
`organization_title` char(40) default NULL,
`division_code` char(4) NOT NULL default '',
`division_title` char(40) default NULL,
`subdivision_code` char(4) NOT NULL default '',
`subdivision_title` char(40) default NULL,
`department_code` char(4) NOT NULL default '',
`department_title` char(40) default NULL,
PRIMARY KEY (`account_code`,`monthend_date`,`location_code`,`cost_center`),
UNIQUE KEY `XPKlocation_account` (`monthend_date`,`location_code`,`account_code`,`cost_center`),
KEY `XAKaccount` (`account_code`),
KEY `XAKdepartment` (`department_code`),
KEY `XAKfunction` (`function_code`),
KEY `XAKhi` (`department_code`),
KEY `XAKorganization` (`organization_code`),
KEY `XAKdivision` (`division_code`),
KEY `XAKsubdivision` (`subdivision_code`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`fs_account_hier_0708`,`fs_account_hier_0607`,`fs_account_hier_0506`,`fs_account_hier_0405`,`fs_account_hier_0304`,`fs_account_hier_0203`,`fs_account_hier_0102`,`fs_account_hier_0001`,`fs_account_hier_9900`,`fs_account_hier_9899`,`fs_account_hier_9798`,`fs_account_hier_9697`,`fs_account_hier_9596`,`fs_account_hier_9495`)

Sorry about the length of this message, I want to make sure I provide enough information. Thanks!

Nagios configuration to monitor MySQL (1 reply)

nani pandu — Tue, 29 Jul 2008 08:00:09 +0000

Hello

Can any one tell how to configure nagios plugins to monitor MySQL Server.

Thanks

How to check backup Consistency (no replies)

nani pandu — Fri, 04 Jul 2008 08:30:06 +0000

Hello,

How do we know back up is taken correctly.And if at all need to be reload how do we know it is being loaded corectly.

I Need Serious Help! (no replies)

L Man — Wed, 18 Jun 2008 22:53:30 +0000

Hello all, I'm new to MySQL and I am searching for "relevant" information to help me backup my MySQL database.

I have read that there are different ways and types of backups. I am NOT so much interested in backing up user data because in my database there is ONLY test accounts which I have set up. I guess I should be interested in that when I have the site up and running and there are members registering.

My MAIN concern right now is COPYING the database exactly and keeping the tables and parameters intact.

In other words, I want to make an EXACT copy of my database to be used on another hosted account. I paid a company to do some programming for my site and they set up the database for me. Now that I have paid, they said that it will cost me extra if they set up another one for me. I know it's not difficult.

And beside what good would it be if I were to pay someone to do it...? What if I need to do it again and again...it's better that I learn myself.

Again, I know that there are many ways to do it...however, I am ONLY interested in learning how to make an exact duplicate of my current database with all tables and parameters intact.

Thank you so much in advance.

L-Man

Getting the contained tables in a merged table (no replies)

Ido Blutman — Thu, 05 Jun 2008 10:32:06 +0000

Hello everyone,

Is there any way I can get the information which tables are united in a merge table from a query which is not the 'show create table'?

something like "select union from my_table"?

thanks,

Ido

Manual backup of files fails (1 reply)

Michał Głębowski — Tue, 06 May 2008 08:01:59 +0000

Dear Friends.

I've got problem with manual backup of merge table.

1. I create MERGE table which consists of some MyISAM tables.
2. I copy database folder from my MySQL..\Data\ somewhere else.
3. I DROP DATABASE.
4. I manually copy back folder to MySQL..\Data\ folder
5. All partial tables are here back but when I try to access MERGE TABLE I get an error: Can't find file 'here my merge table name'.

I found that file responsible for keeping table structure (mymergetable.frm) is there.
What shall I copy more to be able to restore MERGE TABLE that way (manually)?
Michal.

Types of Backup (1 reply)

alim arzed — Wed, 19 Mar 2008 22:43:56 +0000

Hi
Is it possible to take transactional log backup in MySql. If So what is the procedure to take a transactional log backup.

Thanks in advance
Alim

privileges problem (2 replies)

Marco Milon — Wed, 25 Jun 2008 08:03:07 +0000

Hello,

i have a problem with merge tables and privileges.

i want to setup something like this:
i need two users admin and user_2, two tables news_1 and news_2
plus one merge table 'news' with news_1 and news_2.

admin has select, create, update and delete privileges on news_1 and news_2
user_2 has select privileges on news_1 and select, create, update and delete on news_2

my problem is that no matter how i setup privileges for user_2, he can delete news from table news_1 if using the merge table 'news'.

the point is that i want user_2 to have 'read-only' access to table news_1 even if he uses the merge table 'news'.

i hope you understand the problem.

thank you very much.