Re: q re transferring mysql db from redhat to debian
On Tue, 27 Jul 2004 17:56:18 +0200, Henrik Heil <[EMAIL PROTECTED]> wrote: > My bad -- this is indeed a problem using mysqldump. I just checked the > manpage and it seems that you cannot tell mysqldump to add > AUTO_INCREMENT=... to the CREATE TABLE statement (please correct me if > you know a way). > I tried mysqldump and it actually recorded the original AUTO_INCREMENTed id's. PhpMyAdmin also records this data whether you check the "Add AUTO_INCREMENT value" checkbox or not. It seems that they just forgot to mention this in the docs, but is already being done. Chad -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: q re transferring mysql db from redhat to debian
Chad Adlawan wrote: I tried mysqldump and it actually recorded the original AUTO_INCREMENTed id's. PhpMyAdmin also records this data whether you check the "Add AUTO_INCREMENT value" checkbox or not. I don't mean the inserted values but the next id that will be inserted. This is what phpmayadmin does with checked "Add AUTO_INCREMENT value" CREATE TABLE `testtab` ( `id` int(11) NOT NULL auto_increment, PRIMARY KEY (`id`) ) TYPE=MyISAM AUTO_INCREMENT=8 ; I don't know how get this with mysqldump. Best regards, Henrik -- Henrik Heil, zweipol Coy & Heil GbR http://www.zweipol.net/ -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: q re transferring mysql db from redhat to debian
On Wed, Jul 28, 2004 at 11:39:40AM +1000, Kevin Littlejohn wrote: > >that's bizarreand could easily lead to a hopelessly corrupted database > >when other tables refer to that id field. > > > >how are you supposed to restore a mysql db from backup then? > > Two answers: > > 1) Why are you relying on the auto_increment field to increment from highest > point each time? So long as it gives you a unique value (and it should > always do that), it shouldn't matter if it's re-using an old value (if it > does, you shouldn't have deleted the old value...). i'm not. i was just curious. btw, sometimes it does matter if record ids are re-used. e.g. one reason not to re-use id numbers is if it's a search field on a web database. if someone bookmarks a particular search (e.g. for id=99) then returning to that bookmark should either return the same record or it should return "no such record" if it has been deleted. it should never return a completely different record. actually, this is true for any kind of app, not only for web databases. e.g. if your sales staff are used to entering product ids from memory, or if your customers quote their customer ID, this can lead to serious confusion or problems. at best, some time will be wasted sorting out the mess. at worst, the wrong product may be shipped or the wrong customer may be billedor the wrong medical records may be referred to when consulting with a patient. in short, unique IDs need to be unique forever(*), not just unique for the present moment. (*) or at least a reasonable facsimile of "forever" :) > Certinaly, if you're referring to those IDs elsewhere, and you've > deleted the record it was referring to, good database design would be to > not leave the references lying around, imnsho. true enough. more to the point, good database design wouldn't LET you leave them lying around. note: i mean database design here, not application design or schema design. i mean the database engine itself should not allow this to happen, it is not something that can or should be left up to the application to enforce, it has to be enforced by the database engine itself. > 2) You can set the point to increment from, in a fairly hackish way, by > doing a "alter table tbl_name auto_increment = x" where x is the highest > number in use. Requires scripting around your backup/restore process, > unfortunately. no big deal. some scripting is almost inevitable in database backup and restore. > With regard 1, the actual definition of auto_increment doesn't preclude > re-use of numbers as far as I know, so if you're relying on it not to, you've > got broken code anyway. That means the mysqldump is doing the correct thing, > according to spec for auto_increment - there's no requirement in there to > retain the highest number. The name of auto_increment is misleading, > obviously ;) ok. "works as designed" - it's not an implementation bug it's a design bug :) > With regard Craig's comment, if your database leaves hanging references to > non-existant data around, you've got a broken database, whether you've > realised it yet or not. true, i didn't think about that at the time. it was just my initial reaction to the idea that there was weirdness with restoring a mysql dump. since dumping to text (or other re-importable format) is the only good way of backing up a database, it seems like a major problembeing able to *reliably* backup and restore a database is, IMO, an essential feature of any database. you need to be certain that what you will restore is *identical* to what you backed up. whether it actually is a major problem or not, i don't know. that's why i was asking. the alter table workaround you mentioned seems reasonable. OTOH, since mysql doesn't actually do transactions(*) or check referential integrity, it's quite possible to have such references in the db. and in this case, an import like this will convert dangling references which point to non-existent records into references that point to records that actually exist (but aren't the right ones). (*) yes, i know about innodbbut hardly anyone actually uses it because that means giving up the only feature that mysql users (mistakenly) care about - raw speed. not that mysql is actually any faster in the real world with multiple simultaneous readers and writers, but that's the mythology. > General note: We make a policy of using auto_increment _only_ to create > sequence tables, which we manage ourselves. This is in line with postgres > and oracle's use of sequence tables, and makes porting easier. We don't > bother with ensuring that the next ID is higher than all previous ones - as > long as they're unique, that's sufficient, any references to a defunct entry > are removed when the entry is removed. postgres sequences (and serial fields) are what i'm used to. craig -- craig sanders <[EMAIL PROTECTED]> The next time you vote, remember that "Regime change begins at ho
Re: q re transferring mysql db from redhat to debian
Craig Sanders wrote: On Tue, Jul 27, 2004 at 09:00:58AM -0400, Fraser Campbell wrote: On July 27, 2004 03:58 am, Henrik Heil wrote: The record_ids will stay the same with mysqldump. What makes you think they will not? I have seen problems with this. The existing auto-incremented fields were just fine but new ones were a little bit off. In a normal mysqldb if you have a single record with id 1 and delete it then add another record the new record will get id 2 (not filling in the missing 1). I've seen a case that after a mysqldump and restore the new records did not honour have that behaviour, "missing" ids were reused. I'm sure that I did something wrong with the dump but in that case it was not important so I didn't research it further. that's bizarreand could easily lead to a hopelessly corrupted database when other tables refer to that id field. how are you supposed to restore a mysql db from backup then? Two answers: 1) Why are you relying on the auto_increment field to increment from highest point each time? So long as it gives you a unique value (and it should always do that), it shouldn't matter if it's re-using an old value (if it does, you shouldn't have deleted the old value...). Certinaly, if you're referring to those IDs elsewhere, and you've deleted the record it was referring to, good database design would be to not leave the references lying around, imnsho. 2) You can set the point to increment from, in a fairly hackish way, by doing a "alter table tbl_name auto_increment = x" where x is the highest number in use. Requires scripting around your backup/restore process, unfortunately. With regard 1, the actual definition of auto_increment doesn't preclude re-use of numbers as far as I know, so if you're relying on it not to, you've got broken code anyway. That means the mysqldump is doing the correct thing, according to spec for auto_increment - there's no requirement in there to retain the highest number. The name of auto_increment is misleading, obviously ;) With regard Craig's comment, if your database leaves hanging references to non-existant data around, you've got a broken database, whether you've realised it yet or not. General note: We make a policy of using auto_increment _only_ to create sequence tables, which we manage ourselves. This is in line with postgres and oracle's use of sequence tables, and makes porting easier. We don't bother with ensuring that the next ID is higher than all previous ones - as long as they're unique, that's sufficient, any references to a defunct entry are removed when the entry is removed. KJL -- Kevin Littlejohn Obsidian Consulting Group phone: +613 9355 7844 skype: callto://silarsis -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: q re transferring mysql db from redhat to debian
On Tue, Jul 27, 2004 at 09:00:58AM -0400, Fraser Campbell wrote: > On July 27, 2004 03:58 am, Henrik Heil wrote: > > The record_ids will stay the same with mysqldump. > > What makes you think they will not? > > I have seen problems with this. The existing auto-incremented fields were > just fine but new ones were a little bit off. In a normal mysqldb if you > have a single record with id 1 and delete it then add another record the new > record will get id 2 (not filling in the missing 1). I've seen a case that > after a mysqldump and restore the new records did not honour have that > behaviour, "missing" ids were reused. I'm sure that I did something wrong > with the dump but in that case it was not important so I didn't research it > further. that's bizarreand could easily lead to a hopelessly corrupted database when other tables refer to that id field. how are you supposed to restore a mysql db from backup then? craig -- craig sanders <[EMAIL PROTECTED]> The next time you vote, remember that "Regime change begins at home" -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: q re transferring mysql db from redhat to debian
the reason why i don't want to do the database transfer using data generated by mysqldump is because i want all the auto-generated record_ids to stay the same in the new system. The record_ids will stay the same with mysqldump. What makes you think they will not? I have seen problems with this. The existing auto-incremented fields were just fine but new ones were a little bit off. In a normal mysqldb if you have a single record with id 1 and delete it then add another record the new record will get id 2 (not filling in the missing 1). I've seen a case that after a mysqldump and restore the new records did not honour have that behaviour, "missing" ids were reused. I'm sure that I did something wrong with the dump but in that case it was not important so I didn't research it further. My bad -- this is indeed a problem using mysqldump. I just checked the manpage and it seems that you cannot tell mysqldump to add AUTO_INCREMENT=... to the CREATE TABLE statement (please correct me if you know a way). phpmyadmin creates dumps with AUTO_INCREMENT information -- i thought mysqldump would do the same -- but it does not. Best regards, Henrik -- Henrik Heil, zweipol Coy & Heil GbR http://www.zweipol.net/ -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: q re transferring mysql db from redhat to debian
On July 27, 2004 03:58 am, Henrik Heil wrote: > > the reason why i don't want to do the database transfer using data > > generated by mysqldump is because i want all the auto-generated > > record_ids to stay the same in the new system. > > The record_ids will stay the same with mysqldump. > What makes you think they will not? I have seen problems with this. The existing auto-incremented fields were just fine but new ones were a little bit off. In a normal mysqldb if you have a single record with id 1 and delete it then add another record the new record will get id 2 (not filling in the missing 1). I've seen a case that after a mysqldump and restore the new records did not honour have that behaviour, "missing" ids were reused. I'm sure that I did something wrong with the dump but in that case it was not important so I didn't research it further. -- Fraser Campbell <[EMAIL PROTECTED]> http://www.wehave.net/ Georgetown, Ontario, Canada Debian GNU/Linux -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: q re transferring mysql db from redhat to debian
--- Henrik Heil <[EMAIL PROTECTED]> wrote: > > The record_ids will stay the same with mysqldump. > What makes you think they will not? Something must have made me think the auto-incremented ids will be re-generated. My bad. > > you could set up replication to keep the databases > in sync until you switch to the new server This sounds really good. I wonder why I never thought of it. (maybe because i've never tried mysql replication) I'm going to try this on a test machine now and see how stable it is. Thanks for the idea! Shannon __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: q re transferring mysql db from redhat to debian
Shannon R. wrote: the reason why i don't want to do the database transfer using data generated by mysqldump is because i want all the auto-generated record_ids to stay the same in the new system. The record_ids will stay the same with mysqldump. What makes you think they will not? If you cannot have a downtime or disable write access while migrating you could set up replication to keep the databases in sync until you switch to the new server (only with mysql version >= 3.23.15). Best regards, Henrik -- Henrik Heil, zweipol Coy & Heil GbR http://www.zweipol.net/ -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: q re transferring mysql db from redhat to debian
On Jul 26, 2004, at 7:56 PM, Brian Nelson wrote: Jeremy Zawodny <[EMAIL PROTECTED]> writes: On Jul 26, 2004, at 6:21 PM, Shannon R. wrote: hello list! i'll be moving from a RedHat server to a Debian server very soon. despite the different linux distributions, is it ok to transfer my entire mysql database by just copying everything in /var/lib/mysql of the RedHat system to the Debian system? Assuming the directory structures are similar and you're using only MyISAM tables, yes. With InnoDB, you'd have to deal with the logs as well. Surely using mysqldump(1) would be safer, especially if the MySQL versions are different. Well, either way you're copying a bunch of bits from server A to server B. I'm not sure how the actual data format matters. Copying the raw data will be more efficient. Jeremy -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: q re transferring mysql db from redhat to debian
Jeremy Zawodny <[EMAIL PROTECTED]> writes: > On Jul 26, 2004, at 6:21 PM, Shannon R. wrote: > >> hello list! >> >> >> i'll be moving from a RedHat server to a Debian server very soon. >> despite the different linux distributions, is it ok to transfer my >> entire >> mysql database by just copying everything in /var/lib/mysql of the >> RedHat >> system to the Debian system? > > Assuming the directory structures are similar and you're using only > MyISAM tables, yes. With InnoDB, you'd have to deal with the logs as > well. Surely using mysqldump(1) would be safer, especially if the MySQL versions are different. -- You win again, gravity! -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: q re transferring mysql db from redhat to debian
On Jul 26, 2004, at 6:21 PM, Shannon R. wrote: hello list! i'll be moving from a RedHat server to a Debian server very soon. despite the different linux distributions, is it ok to transfer my entire mysql database by just copying everything in /var/lib/mysql of the RedHat system to the Debian system? Assuming the directory structures are similar and you're using only MyISAM tables, yes. With InnoDB, you'd have to deal with the logs as well. Jeremy -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
