Re: Backup problem from 5.0 to mysql 4.1
Amir Bukhari wrote: -Original Message- From: Addison, Mark [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 18, 2006 1:22 PM To: Amir Bukhari; mysql@lists.mysql.com Subject: RE: Backup problem from 5.0 to mysql 4.1 From: Amir Bukhari Sent: 18 July 2006 09:23 I have local mysql 5.0 and I have developed an arabic site. The database encoding is utf8-bin. Localy everything work fine, all arabic text are displayed OK. Now I want to move it to a server in internet. The server has mysql 4.1 and as I restored the database there, some special arabic character are not displayed correctly. I don't know why only some character are inserted differently from others. Localy I have also tried to have both mysql server 4.1 5.0 to play with the backup and store, but without success, always the same result. Moving the binary database files (*.frm ...) from 5.0 to 4.1 doesn't work, it seem they are not compatible. Moving binary files between versions is generally a bad idea, use mysqldump instead. It creates a sql file you can run against the 4.1 server and should move all the chars over ok. E.g. if your database was called foodb then locally run: $ mysqldump -u amir -p foodb foodb.sql Then load this file into the server: $ mysql --host=server.name -u amir -p foodb foodb.sql I have tried this also, but this produce those bad character. I have used exactly what you have done above. When restoring the database which was backuped using mysqldump into mysql 5.0 everything is OK, but when restoring it to mysql 4.1 then there is those bad characters. I have also tried to backup and restore with both version of mysql mysqldump included in 5.0 and 4.1, but without success. There is a special commandline option for the mysqldump included in 5.0 to produce dumps suitable for 4.1. Try that. Bye Racke -- LinuXia Systems = http://www.linuxia.de/ Expert Interchange Consulting and System Administration ICDEVGROUP = http://www.icdevgroup.org/ Interchange Development Team -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Speedup of database changes
Hello, one of my customers has a database with about 1.7 million records (52 fields, almost 1GB on amd64, MyISAM). This database is running on Debian sarge with MySQL 4.0.24. Changing the database structures and also large set of inserts/deletes take too long to be acceptable on a productions system. What can I do to speedup these operations ? Bye Racke -- LinuXia Systems = http://www.linuxia.de/ Expert Interchange Consulting and System Administration ICDEVGROUP = http://www.icdevgroup.org/ Interchange Development Team -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speedup of database changes
Brent Baisley wrote: Changes to the database structure should be few a far between. If you are finding you need to change the structure regularly, you should probably look at a redesign. This customer regularly request new things, so I cannot avoid changes to the database structure. MyISAM locks the table when an insert/delete is occurring, during which time other requests are queue up. You can switch it to InnoDB, which does not use table locking. This can be done easy enough with an Alter table command, but make sure you read up on what InnoDB doesn't support (full text searching). InnoDB does take up a lot more space than MyISAM, so make sure your file system supports files over 2GB. I found in the MySQL documentation that the row-level locking of InnoDB is slower if you need to do frequent full table scans. Unfortunately, I can not avoid them (3rd party application running there). If you need to stick with MyISAM, you may want to change the way you do inserts/deletes, breaking them up into chunks. Then you'll be interleaving you insert/deletes with other requests. OK, thanks. How about DELAY_KEY_WRITE=1 ? Does this speed up things substantially ? Bye Racke -- LinuXia Systems = http://www.linuxia.de/ Expert Interchange Consulting and System Administration ICDEVGROUP = http://www.icdevgroup.org/ Interchange Development Team -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speedup of database changes
Brent Baisley wrote: There will always be new requests, but many times you can store data vertically in rows instead of horizontally in columns. You would need a label column to indicate what type of data the row contains. It's more work on the front end, but a lot more flexible. It's a technique I use often, but may not be applicable/possible in your situation. That is right, but wouldn't make the large table even larger ? You can't create indexes to avoid or reduce full table scans? The major problem is that the application needs fast selects (for displaying information) _and_ fast inserts/updates/deletes (data is updated regularly with some large upload sets). So at the moment it isn't possible to use more indices. InnoDB is slower in certain respects, but you need to keep in mind that InnoDB will allow multiple things to happen at once. Massive deletes can be a problem in InnoDB, taking a lng time. Depending on how your data is structured, you may want to look into merge tables (MyISAM). I've got a table with almost 250 million records in it, which is a rolling 6 months worth of data. Once a month we need to delete a months worth of data (millions of records). We make sure that each of the underlying tables of the merge table are about 1 months worth of data. To delete we drop the table and alter the UNION on the merge table. Takes less than a second. Delay key write won't help much if you are not using indexes. If will try that first and see if it speeds up the writing operations. Thanks for your valuable input, I'll report back what happened :-). Bye Racke -- LinuXia Systems = http://www.linuxia.de/ Expert Interchange Consulting and System Administration ICDEVGROUP = http://www.icdevgroup.org/ Interchange Development Team -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with INSERT
Duane Hill wrote:
Can someone either answer or point me somewhere for the answer?
I am attempting to store text lines from an e-mail message into a MySQL
table through the use of Perl and DBI. Periodically I get the error that
states there was an error in the statement and to check the syntax. I
know it has something to do with how the statement is being interpreted.
Here is the Perl that is in question:
$sql = INSERT INTO msg_line_hash ;
$sql .= (line_text, line_hash, total_count) ;
$sql .= VALUES ;
$sql .= ('$msgline', '$digest', 1) ;
$sql .= ON DUPLICATE KEY UPDATE total_count = total_count + 1;
$sth = $dbh-prepare_cached($sql);
$sth-execute();
I'm preparing the statement as cached based on the fact it will be the
same statement that will execute several times. Therefore, eliminating
having to contact the server on every perpare.
I know it's the $msgline that is causing issues in that if I pack
$msgline into a readable hex text line, it stores without any errors.
That would mean the SQL statement is getting messed up because of some
of the characters within $msgline.
Use DBI quote method on $msgline.
Racke
--
LinuXia Systems = http://www.linuxia.de/
Expert Interchange Consulting and System Administration
ICDEVGROUP = http://www.icdevgroup.org/
Interchange Development Team
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
