problem to restore mysql database

kirukan · 12-01-2010, 08:58 PM

Quote:

# mysql -u root -prex@12Eg mtdb < wp1db
ERROR 1071 (42000) at line 535: Specified key was too long; max key length is 1000 bytes

Quote:

# mysql -u root -prex@12Eg -f mtdb < wp1db
ERROR 1146 (42S02) at line 568: Table 'mtdb.streaming_transaction' doesn't exist
ERROR 1146 (42S02) at line 569: Table 'mtdb.streaming_transaction' doesn't exist
ERROR 1146 (42S02) at line 815: Table 'mtdb.streaming_transaction_backup' doesn't exist
ERROR 1146 (42S02) at line 816: Table 'mtdb.streaming_transaction_backup' doesn't exist

There are some other tables in this database and they all are successfully restored but i am facing issue with only these two tables(mtdb.streaming_transaction, mtdb.streaming_transaction_backup). please any body guide me to solve this problem.

TB0ne · 12-02-2010, 08:19 AM

Quote:

Originally Posted by kirukan

There are some other tables in this database and they all are successfully restored but i am facing issue with only these two tables(mtdb.streaming_transaction, mtdb.streaming_transaction_backup). please any body guide me to solve this problem.

Did you try to look this up? Some research on the MySQL forums indicates that the maximum key size in MySQL is 1000 bytes. What is your encoding/character set for your DB??

Given that latin1 uses one (1) byte per character and UTF-8 uses three (3) bytes per character some indexes will exceed the 1000 byte limit. This accounts for the generated error when converting tables with large or compound indexes from latin1 to UTF8 encoding. According to the MySQL bug tracker this issue has yet to be resolved. Currently the only solution is to limit indexing on UTF-8 encoded tables or use latin1 instead.

May want to modify your SQL input file, to add something like this on the table:

Code:

ALTER TABLE `TableName`  CONVERT TO CHARACTER SET latin1 COLLATE latin1;

Or, you can modify the "CREATE TABLE IF NOT EXISTS" statement for that one table, and change the character set there. You don't say what version of MySQL, or version/distro of Linux, so this is all guessing.

kirukan · 12-06-2010, 03:45 AM

Thanks TBOne to response my question. Yes, i already referred mysql forum but still no luck to resolve my problem. herewith i attach some more outputs.

1. Using Centos 5.4
2. Mysql version (mysql Ver 14.14 Distrib 5.1.49, for pc-linux-gnu (i686) using readline 5.1)
3. Mysql status

Code:

mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.1.49, for pc-linux-gnu (i686) using readline 5.1

Connection id:		8
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.1.49-community MySQL Community Server (GPL)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	latin1
Conn.  characterset:	latin1
UNIX socket:		/var/lib/mysql/mysql.sock
Uptime:			57 min 57 sec

Threads: 1  Questions: 1349  Slow queries: 0  Opens: 303  Flush tables: 1  Open tables: 29  Queries per second avg: 0.387

4. Database status

Code:

mysql> use mtdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.1.49, for pc-linux-gnu (i686) using readline 5.1

Connection id:		8
Current database:	mtdb
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.1.49-community MySQL Community Server (GPL)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	latin1
Conn.  characterset:	latin1
UNIX socket:		/var/lib/mysql/mysql.sock
Uptime:			58 min 16 sec

Threads: 1  Questions: 1376  Slow queries: 0  Opens: 303  Flush tables: 1  Open tables: 29  Queries per second avg: 0.393
--------------

5. Table status

Code:

mysql> show table status;
| streaming_transaction         | MyISAM | 10 | Dynamic    |  0 |  0 |0 | 281474976710655 | 1024 |0 | 1 | 2010-12-07 01:01:01 | 2010-12-07 01:01:01 | NULL | latin1_swedish_ci | NULL | ||
| streaming_transaction_backup  | MyISAM | 10 | Dynamic    |  0 |  0 |0 | 281474976710655 | 1024 |0 | 1 | 2010-12-07 01:00:55 | 2010-12-07 01:00:55 | NULL | latin1_swedish_ci | NULL | ||

6. Table structure

Code:

mysql> describe streaming_transaction;
+--------------------------+------------------+------+-----+---------+----------------+
| Field                    | Type             | Null | Key | Default | Extra          |
+--------------------------+------------------+------+-----+---------+----------------+
| id                       | int(11)          | NO   | PRI | NULL    | auto_increment |
| session_id               | varchar(128)     | YES  |     | NULL    |                |
| bundle_id                | varchar(50)      | YES  |     | NULL    |                |
| requested_url            | varchar(256)     | YES  |     | NULL    |                |
| session_start_time       | decimal(10,0)    | YES  |     | NULL    |                |
| session_end_time         | decimal(10,0)    | YES  |     | NULL    |                |
| status                   | char(1)          | YES  |     | NULL    |                |
| retry_count              | int(10) unsigned | YES  |     | NULL    |                |
| product_code             | varchar(50)      | YES  |     | NULL    |                |
| user_agent               | varchar(256)     | YES  |     | NULL    |                |
| profile_id               | varchar(50)      | YES  |     | NULL    |                |
+--------------------------+------------------+------+-----+---------+----------------+

Even after change CHARACTER SET as latin1, i receive same error when restore the database.

TB0ne · 12-06-2010, 07:32 AM

As I said, unless you change it in the MySQLdump file, it'll try to do it as UTF-8. You have to change it there.

kirukan · 12-06-2010, 08:46 AM

Is there any possibility to change the encoding type in dump file or it should done in database and then have to dump the database? because i changed the key length from 512 to 256 and UTF-8 too in the restored server still no luck, I cant do such modification in the production environment.

TB0ne · 12-06-2010, 08:58 AM

Quote:

Originally Posted by kirukan

Is there any possibility to change the encoding type in dump file or it should done in database and then have to dump the database? because i changed the key length from 512 to 256 and UTF-8 too in the restored server still no luck, I cant do such modification in the production environment.

Yes, and I told you how in my first post...did you not read it???

Quote:

Originally Posted by TB0ne

May want to modify your SQL input file, to add something like this on the table:

ALTER TABLE `TableName` CONVERT TO CHARACTER SET latin1 COLLATE latin1;

Or, you can modify the "CREATE TABLE IF NOT EXISTS" statement for that one table, and change the character set there. You don't say what version of MySQL, or version/distro of Linux, so this is all guessing.

kirukan · 12-06-2010, 09:12 AM

In normal procedure to restore database
#mysql -u root -ppassword restoreddb < dbdump, before this
Do you ask me to create table on restored server and set the COLLATE as latin1?

TB0ne · 12-06-2010, 10:27 AM

Quote:

Originally Posted by kirukan

In normal procedure to restore database
#mysql -u root -ppassword restoreddb < dbdump, before this
Do you ask me to create table on restored server and set the COLLATE as latin1?

No. READ what I posted, twice now.

In your other question, you specifically asked:

Quote:

Originally Posted by kirukan

Is there any possibility to change the encoding type in dump file

Again, YES...again READ WHAT I FIRST POSTED. Open the dump file, edit it accordingly. I even told you what to look for in the dump file, and where to edit it. Not sure how much more I can tell you.

kirukan · 12-07-2010, 09:22 AM

TBOne, Thanks a lot. Very first time i didn't get the meaning of edit the dumped database, however i successfully completed the task. One more doubt if the dumped file is huge size, is it possible to open in Linux editors?

TB0ne · 12-07-2010, 09:29 AM

Quote:

Originally Posted by kirukan

TBOne, Thanks a lot. Very first time i didn't get the meaning of edit the dumped database, however i successfully completed the task. One more doubt if the dumped file is huge size, is it possible to open in Linux editors?

Yes, but it'll take a while, depending on the editor.

You have two options there. You can either use a sed statement to replace the text in the file, and redirect it to a second file (your first dump is then unchanged, in case there are problems), or you can do two dumps. One of the whole database, EXCEPT that one table, and the other of JUST that table, which will make it easier to edit.