Re: upgrade from mysql 3.23 to 4.1
On Tue, 2004-11-30 at 11:50 -0600, Jeff Smelser wrote: On Monday 29 November 2004 11:35 pm, Greg Macek wrote: I was thinking about this as well, but was hoping to minimize the amount of work I would have to do. However, if this makes the most sense for upgrading, perhaps this is what I'll do. Actually, I think you will be minimizing going the 4.0 route. These major upgrades are no small tasks anyway. Jeff OK, sounds like what I should do is the following: * Upgrade current mysql install (3.23.49) to latest stable 4.0 series (4.0.22 according to the website) * Test out all applications and make sure everything is working as expected. * Test new features in 4.0.x vs. 3.23.x * After sufficient time to test, upgrade to latest stable 4.1.x version. I think I can make this scenario work. :) Thanks all for the direction. Otherwise, I may have run into issues doing the huge jump. Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrade from mysql 3.23 to 4.1
On Wed, 2004-11-24 at 09:15 -0600, Jeff Smelser wrote: On Wednesday 24 November 2004 07:32 am, Hristo Chernev wrote: How to upgrade from 3.23.58 to 4.1.7? Which is the easiest way with minimal risk and downtime? Yeah right.. ;) Background: Heavy loaded mysql server, only one database but it is huge - 5GB.The database is replicated to another server.Mysql 3.23.58.Linux OS. Here is the my plan, please correct me if there is a better way or if I am doing unnecessary actions: 1. Stop the site and mysql servers and backup database. 2. Upgrade mysql versions on the two machines. 3. Compiling apache and php with new mysql 4.1 client lib. 4. Recreate users and rights ( the provided script fix_privilege_table will not work correctly form 3.23. to 4.1. will it?). 5. Start main mysql server without replication. 6. Rename database to olddb. 7. Create new database named db. Do one of 8a or 8b: 8a.Dump olddb with mysqldump, then run the result file to fill the new db (in order to have native 4.1 database ). 8b.Using SQL queries (or phpmyadmin) copy all tables from olddb to new db (in order to have native 4.1 database ). 9. Assure that all is correct then delete olddb, copy database to the slave and start mysql servers. 10. Confirm replication is ok, then start the site. This is a good list.. My suggestion would be to do this on some sort of back/dev machine.. php doesnt just work with 4.1 automatically (depending on version). Not sure which distro u use, that could be a driving factor of problems you may see to. Jeff I am actually in the process of planning a similar upgrade for our database server. None of our databases are that big, but was wondering about what gotcha's I should be on the lookout for. I planned on walking through the upgrade pages from mysql.com for going from 3.23 to 4.0 and 4.0 to 4.1. I was hoping not to have to re-create the databases as the original poster mentioned, but I don't have a slave DB setup either. Will this be an issue? Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrade from mysql 3.23 to 4.1
On Mon, 2004-11-29 at 13:11 -0600, Paul DuBois wrote: At 12:57 -0600 11/29/04, Jeff Smelser wrote: On Monday 29 November 2004 09:01 am, Greg Macek wrote: I am actually in the process of planning a similar upgrade for our database server. None of our databases are that big, but was wondering about what gotcha's I should be on the lookout for. I planned on walking through the upgrade pages from mysql.com for going from 3.23 to 4.0 and 4.0 to 4.1. I was hoping not to have to re-create the databases as the original poster mentioned, but I don't have a slave DB setup either. Will this be an issue? Well, in 4.0.22, you can do a new=1.. (something like that). and yer suppose to be able to test these new features in 4.1.. Might want to look for that in 3.23.. No, there is no such option in 3.23. In general, it's better to upgrade 3.23 - 4.0 - 4.1 so that you can get used to differences from one version series to the next, rather than skipping version series. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com I was thinking about this as well, but was hoping to minimize the amount of work I would have to do. However, if this makes the most sense for upgrading, perhaps this is what I'll do. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Converting table types
Hello, I was wondering what happens when you change table types. Does MySQL automatically get rid of the old table files? I'm looking into changing out some tables from MyISAM to InnoDB. I'm not tight on space, but I wasn't sure how it handled the conversion, since the storage location changes with this. -- Greg Macek | Senior IT Manager Marketing Resources, Inc. Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Require pair of fields to be unique?
Hello, I have a strange question. I'm in the design phase for a table and was wondering if it's possible in MySQL to force a pair of fields in a record to be unique within the table. I would like it to look like this: user_id job_id What I'd like to do is never have the same user_id and job_id paired up paired together more than once. Maybe I'm missing a better way to setup this table. If I am, please let me know. Thanks. -- Greg Macek | Senior IT Manager Marketing Resources, Inc. [EMAIL PROTECTED] | http://www.mrichi.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Disappearing records?
Hello, I have a problem that I could use some help with. We're running a mysql/php intranet site for time sheets (home grown solution). However from time to time a user will tell me hours from the previous week are just gone. I go to look and sure enough, no hours have been entered. I have not been bitten by this bug (yet), but I have never encountered this problem with any other databases that exist on this very same box. I have mysql logging enabled and cannot even find existence of these records being inserted into the database at all. Where should I start looking for clues? I am at a loss. Any direction is appreciated. Running Mysql 3.23.49-log on a SuSE 7.3 box running on Dell PowerEdge 2550. Lots of RAM and hard drive space. It's a MyISAM table at the moment. The machine has been running since late July w/o reboot (server and mysql itself). - Greg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Disappearing records?
That's the weird part of it. Last week in reviewing the system, making sure reports were working I can personally verify his stuff was in there (not even logged on as him). This week: nothing. Is there any record of 3.23.49 ever having random data loss issues? Related to something else on the system? On Mon, 2002-11-18 at 10:37, Dan Nelson wrote: In the last episode (Nov 18), Greg Macek said: Hello, I have a problem that I could use some help with. We're running a mysql/php intranet site for time sheets (home grown solution). However from time to time a user will tell me hours from the previous week are just gone. I go to look and sure enough, no hours have been entered. I have not been bitten by this bug (yet), but I have never encountered this problem with any other databases that exist on this very same box. I have mysql logging enabled and cannot even find existence of these records being inserted into the database at all. If you have no record of the records having been entered, maybe the user never entered them? Or could there have been a problem with the frontend program that kept it from submitting the data? -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Disappearing records?
I'll look into the code on the PHP side (since I wrote that too). Maybe it is something I did. :) I may avoid repairing the tables for now, since the missing records have already been re-entered and I don't want to risk anything being duplicated at this point. Perhaps I'll drop all the indexes for now and keep a watch on things. The table is still small ( 5000 records) so reporting speed isn't too much of a problem right now. On Mon, 2002-11-18 at 10:52, Dan Nelson wrote: In the last episode (Nov 18), Greg Macek said: That's the weird part of it. Last week in reviewing the system, making sure reports were working I can personally verify his stuff was in there (not even logged on as him). This week: nothing. Is there any record of 3.23.49 ever having random data loss issues? Related to something else on the system? Even if there was dataloss, how could you explain not seeing the entries in the mysql log? I think in this case the query never made it to mysql. You can always try doing a repair table in case the index got damaged. Also check your log to see if the record was manually deleted. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Upgrading mysql with mysql binary vs. compiling
Is there any reason I should use my MySQL supplies binaries to upgrade my currently installed version? I compile the running version (3.23.49). Is there any difficulty in doing this? Any pitfalls to watch out for, or should I just continue to compile my own and upgrade via that path? - Greg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
INSERT INTO ... SELECT question
Hello, I recently stumbled upon the INSERT INTO..SELECT abilities. Basically what I'm doing is archiving records into another table before deleting them (inventory information). However, I'd like to have the archive table to have one more field than the original table: a date_archived function. So, for example if the tables looked like this: (quite simplified) Original: carton_id item_id qty status date_recd Archive carton_id item_id qty status date_recd date_archived *(new field) Can I have the SQL query have a NOW() in there to insert today's date when running this ? INSERT INTO archive (carton_id,item_id,qty,status,date_recd,date_archived) SELECT carton_id,item_id,qty,status,date_recd, NOW() FROM original Would this work? I'd like to know upfront before basing my code around this or whether or not I'll have to track how many records are going into the new table and manually updating the field. Thanks. - Greg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: INSERT INTO ... SELECT question
Well, amazingly enough, it works great! I found a test box to try it on first before implementing this on the production box. This will definitely make life easier... On Thu, 2002-11-14 at 10:14, gerald_clark wrote: Did you try it? Did it work? Greg Macek wrote: Hello, I recently stumbled upon the INSERT INTO..SELECT abilities. Basically what I'm doing is archiving records into another table before deleting them (inventory information). However, I'd like to have the archive table to have one more field than the original table: a date_archived function. So, for example if the tables looked like this: (quite simplified) Original: carton_id item_id qty status date_recd Archive carton_id item_id qty status date_recd date_archived *(new field) Can I have the SQL query have a NOW() in there to insert today's date when running this ? INSERT INTO archive (carton_id,item_id,qty,status,date_recd,date_archived) SELECT carton_id,item_id,qty,status,date_recd, NOW() FROM original Would this work? I'd like to know upfront before basing my code around this or whether or not I'll have to track how many records are going into the new table and manually updating the field. Thanks. - Greg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: INSERT INTO ... SELECT question
Thanks for the tip! Looks like I can change my date_archived field to timestamp(8), since all I care about for this is the date information (actual time is useless to me). My sql query all of a sudden got a lot simpler. Thanks again for the help! On Thu, 2002-11-14 at 11:07, Matthew Baranowski wrote: Hey Greg: A slightly easier way to do this is to use a timestamp field. Timestamp is just a standard mysql data type. When a record is added, it records the current time. When a record is updated, the timestamp field will be set to the time of the update. http://www.mysql.com/doc/en/DATETIME.html The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically. Thanks, Matt Matthew P Baranowski Data Manager, Office of Educational Assessment University of Washington - Original Message - From: Greg Macek [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, November 14, 2002 8:06 AM Subject: INSERT INTO ... SELECT question Hello, I recently stumbled upon the INSERT INTO..SELECT abilities. Basically what I'm doing is archiving records into another table before deleting them (inventory information). However, I'd like to have the archive table to have one more field than the original table: a date_archived function. So, for example if the tables looked like this: (quite simplified) Original: carton_id item_id qty status date_recd Archive carton_id item_id qty status date_recd date_archived *(new field) Can I have the SQL query have a NOW() in there to insert today's date when running this ? INSERT INTO archive (carton_id,item_id,qty,status,date_recd,date_archived) SELECT carton_id,item_id,qty,status,date_recd, NOW() FROM original Would this work? I'd like to know upfront before basing my code around this or whether or not I'll have to track how many records are going into the new table and manually updating the field. Thanks. - Greg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
