dump time progressively increasing with Innodb

Hi, I noticed that over the months the dump of my databases (very subject to modifications, but not subject to increase significantly in size) gets progressively slower: from ~8 minutes to almost 15 in 6 months. How can I avoid this degeneration? Thanks, Nico -- MySQL General

Re: dump time progressively increasing with Innodb

OPTIMIZE TABLE sometimes helps, ymmv. http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html / Carsten Nico Sabbi skrev: Hi, I noticed that over the months the dump of my databases (very subject to modifications, but not subject to increase significantly in size) gets progressively

Re: Index not being used

Hi all! Ananda Kumar wrote: you should change the column order for the index sitefieldsort. It should be (sortorder,siteid`,`fieldid`). NO! NEVER! (sorry for shouting) For any DBMS (this is not specific to MySQL), it is not possible to use a multi-column index unless values are given for

RE: Master/Slave - trucate master table

I don't have the error anymore, but the slave will start, but when you show slave status, it shows that there is a failure, and the failure was, a duplicate primary key. What i did to fix/bandaid it, was i truncated the table on the slave, and restarted the slave again... then i did a delete on

RE: Displaying date/time

Here is a MySQL stored procedure that I have used to format the difference between two dates: CREATE definer=`ro...@`127.0.0.1` FUNCTION `sp_maint_PeriodLength`(dt1 DATETIME, dt2 DATETIME) RETURNS char(128) CHARSET latin1 BEGIN DECLARE yy, mm, d0, dd, hh, mi, ss, t1 BIGINT;

Re: mysqldump hex-blob option

On 3/4/10 7:21 PM, peng yao xwei...@gmail.com wrote: hello erveryone, I have a question about mysqldump.I have some blob data, someone tell me mysqldump the data must use hex-blob options, why? mysqldump just creates a file containing insert statements that when executed rebuild your

Re: Master/Slave - trucate master table

did u stop the slave process before stopping mysql on slave. Did u do the below before stopping the mysql on slave? slave stop; show slave status\G; regards anandkl On Mon, Mar 8, 2010 at 6:46 PM, Steven Staples sstap...@mnsi.net wrote: I don't have the error anymore, but the slave will start,

Re: Displaying date/time

Hi Thanks for the positive reply. Your stored procedure kind of works but doesn't give me the desired results. However I will base my requirements on this. Thanks again Neil On Mon, Mar 8, 2010 at 2:42 PM, Price, Randall randall.pr...@vt.edu wrote: Here is a MySQL stored procedure that I

RE: Master/Slave - trucate master table

Technically, shouldn't the slave be able to shutdown and then catch back up? What if the slave lost power and shut down... and when the power came back, shouldn't the slave restart, get the bin logs, and catchup? From now on, I wont truncate any tables, i will just delete from the table where the

possible bug in mysql_tzinfo_to_sql

i used mysql_tzinfo_to_sql utility to create timezone_* database after upgrading zoneinfo in my host. after that i am seeing this mismatch. 'America/Sao_Paulo' (http://www.timeanddate.com/worldclock/city.html?n=233) and 'America/Buenos_Aires'

UDF - Sequence Numbers

I have two servers, both running 5.0.77-log, one is setup as a master, the other as a replication slave. The database contains a table that holds records of loans for financial lending stores. This table has an ID column this is defined as auto increment. There is another column called

RE: UDF - Sequence Numbers

Others may correct me if I'm wrong, but if you want to maintain your own auto increment without duplicates like that, then you need to serialize inserts to your table. Which means either doing an explicit table lock by the process that's inserting, or using innodb with transactions in the

Merging deltas from one table to another?

OK, a Drupal site I deal with has two copies... a production site and a test site. After new changes are developed, they're put on the test site. Once the test site is deemed to be OK, that entire site and database will be copied over to the production site. The issue is, while that work is

Application lost database connection

Hi All, I am really not sure whether this question should be asked here or not but I am hoping that someone must have faced this problem. My Servers are losing connections with database because of inactivity and on next activity I am getting following error :- "The last packet successfully

Re: Application lost database connection

Manasi Save wrote: Hi All, I am really not sure whether this question should be asked here or not but I am hoping that someone must have faced this problem. My Servers are losing connections with database because of inactivity and on next activity I am getting following error :- The