RE: urgent : PLEASE HELP - problems with back up and restore
Hi, Thanks Again. You are really helping me Well it get a lot of query ok, 0 rows affected 0.00 sec And one like this : query ok, 2 rows affected 0.01 sec Records: 2 Duplicates: 0 Warnings: 0 But it look like everything is in the Database now ? What is this Source Command ? Another thing I noticed is that My Original databes my Sp's Got Delimiter $$ But on the backup file it replaced with ;; Barak _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, November 24, 2005 7:21 AM To: Barak Mery Cc: mysql@lists.mysql.com Subject: RE: urgent : PLEASE HELP - problems with back up and restore I saw both views and stored procedures in the dump file. What error do you get if you process the script with the source command within the MySQL CLI? mysql -uroot -pmypass (login welcome) mysql CREATE DATABASE IF NOT EXISTS bcm; mysql USE bcm; mysql source myback1.sql There is another option to drop/create the database. Your dump didn't have that so you need to do it manually in order to restore it. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 07:18:02 PM: Wel I read the manual but didn't find something that I really need to add I suspect it is something with the script itself. Now I'm using mysqldump -uroot -pmypass --routines -q -x bcmmyback1.sql but after i run mysqldump -uroot -pmypass --routines bcmmyback1.sql it now restore the tables but not the views or sp's here is the backup file thanks Barak snipped out backup file From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 23, 2005 9:30 PM To: Barak Mery Cc: mysql@lists.mysql.com Subject: RE: urgent : PLEASE HELP - problems with back up and restore There's a new option added in 5.0.13: -R (--routines) It's designed designed specifically to dump SPROCs and FUNCTIONs. I thought it would have been turned on by default but I guess it wasn't. Add that to the list of options and check your dump results again. I refer you again to the manual for additional warnings and cautions. http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 02:23:42 PM: Well Shawn, First of thanks for the quick result. I tried your suggestion but it didn't help no sp and no restore. It only shrinked the backup file (by deleting the cr). Is there any log file where I can trace that kind of erros ? Barak From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 23, 2005 9:07 PM To: Barak Mery Cc: mysql@lists.mysql.com Subject: Re: urgent : PLEASE HELP - problems with back up and restore Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 01:51:37 PM: Hi, I'm really desperate on this on. After struggling with some really weird bugs I finally finished my project. Buut I can't perform a good backup and restore. I'm using : MySql 5.0.16-nt (essentials version) Windows xp The DB contains tables, vw's and sp's. It is a very small one and at the moment I don't have any data inside. The whole backup file size is 100Kb. 1. I backed up my db using mysql administrator. It created a back up file with all tables, views and sp's, but when I tried a restore it got errors like. Could not handle this statement etc. 2. I tried : mysqldump -uroot -pmypassword dbname backup.sql And then mysqldump -uroot -pmypassword newDbName backup.sql I didn't get any error. The shell printed to the screen the backup file completely only without the table script part. It Created a backup file only for the tables (why ? a minute agoI did the backup with the same tool). But after restoring, the new db was still empty. 3. I tried to restore with mysql -uroot -pmypassword newDbName backup.sql But I got the same results. It should be a very simple and basic issue. Why everything is so hard with mysql ? Is there any really good and quick forum for mySql ? I posted some new thread in mysql.com at the past few days but never got answered. Is it just me or that's the life on the mysql planet ? I now try this mailing-list. I hope you can help me. Thanks in advance Barak You are in luck as the mailing list is quite active. Look at your actual dump file. It is simply a SQL script that will create all of the elements of the database and populate them with data (if you had any). My suspicion is that you have something that isn't quoted that needed
Re: urgent : PLEASE HELP - problems with back up and restore
Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 01:51:37 PM: Hi, I'm really desperate on this on. After struggling with some really weird bugs I finally finished my project. Buut I can't perform a good backup and restore. I'm using : MySql 5.0.16-nt (essentials version) Windows xp The DB contains tables, vw's and sp's. It is a very small one and at the moment I don't have any data inside. The whole backup file size is 100Kb. 1. I backed up my db using mysql administrator. It created a back up file with all tables, views and sp's, but when I tried a restore it got errors like. Could not handle this statement etc. 2. I tried : mysqldump -uroot -pmypassword dbname backup.sql And then mysqldump -uroot -pmypassword newDbName backup.sql I didn't get any error. The shell printed to the screen the backup file completely only without the table script part. It Created a backup file only for the tables (why ? a minute ago I did the backup with the same tool). But after restoring, the new db was still empty. 3. I tried to restore with mysql -uroot -pmypassword newDbName backup.sql But I got the same results. It should be a very simple and basic issue. Why everything is so hard with mysql ? Is there any really good and quick forum for mySql ? I posted some new thread in mysql.com at the past few days but never got answered. Is it just me or that's the life on the mysql planet ? I now try this mailing-list. I hope you can help me. Thanks in advance Barak You are in luck as the mailing list is quite active. Look at your actual dump file. It is simply a SQL script that will create all of the elements of the database and populate them with data (if you had any). My suspicion is that you have something that isn't quoted that needed to be. mysqldump has several options. You can see them with the command: mysqldump --help -or- refer to the manual http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html try dumping your database again, this time use the -r= and -Q options. That does two things: a) it avoids adding CR characters at the end of every line b) it puts backticks around EVERYTHING that needs them (table names, column names, etc) mysqldump -uroot -pmypassword -r backup.sql -Q dbname Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: urgent : PLEASE HELP - problems with back up and restore
Well Shawn, First of thanks for the quick result. I tried your suggestion but it didn't help no sp and no restore. It only shrinked the backup file (by deleting the cr). Is there any log file where I can trace that kind of erros ? Barak _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 23, 2005 9:07 PM To: Barak Mery Cc: mysql@lists.mysql.com Subject: Re: urgent : PLEASE HELP - problems with back up and restore Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 01:51:37 PM: Hi, I'm really desperate on this on. After struggling with some really weird bugs I finally finished my project. Buut I can't perform a good backup and restore. I'm using : MySql 5.0.16-nt (essentials version) Windows xp The DB contains tables, vw's and sp's. It is a very small one and at the moment I don't have any data inside. The whole backup file size is 100Kb. 1. I backed up my db using mysql administrator. It created a back up file with all tables, views and sp's, but when I tried a restore it got errors like. Could not handle this statement etc. 2. I tried : mysqldump -uroot -pmypassword dbname backup.sql And then mysqldump -uroot -pmypassword newDbName backup.sql I didn't get any error. The shell printed to the screen the backup file completely only without the table script part. It Created a backup file only for the tables (why ? a minute ago I did the backup with the same tool). But after restoring, the new db was still empty. 3. I tried to restore with mysql -uroot -pmypassword newDbName backup.sql But I got the same results. It should be a very simple and basic issue. Why everything is so hard with mysql ? Is there any really good and quick forum for mySql ? I posted some new thread in mysql.com at the past few days but never got answered. Is it just me or that's the life on the mysql planet ? I now try this mailing-list. I hope you can help me. Thanks in advance Barak You are in luck as the mailing list is quite active. Look at your actual dump file. It is simply a SQL script that will create all of the elements of the database and populate them with data (if you had any). My suspicion is that you have something that isn't quoted that needed to be. mysqldump has several options. You can see them with the command: mysqldump --help -or- refer to the manual http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html try dumping your database again, this time use the -r= and -Q options. That does two things: a) it avoids adding CR characters at the end of every line b) it puts backticks around EVERYTHING that needs them (table names, column names, etc) mysqldump -uroot -pmypassword -r backup.sql -Q dbname Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: urgent : PLEASE HELP - problems with back up and restore
There's a new option added in 5.0.13: -R (--routines) It's designed designed specifically to dump SPROCs and FUNCTIONs. I thought it would have been turned on by default but I guess it wasn't. Add that to the list of options and check your dump results again. I refer you again to the manual for additional warnings and cautions. http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 02:23:42 PM: Well Shawn, First of thanks for the quick result. I tried your suggestion but it didn’t help no sp and no restore. It only shrinked the backup file (by deleting the cr). Is there any log file where I can trace that kind of erros ? Barak From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 23, 2005 9:07 PM To: Barak Mery Cc: mysql@lists.mysql.com Subject: Re: urgent : PLEASE HELP - problems with back up and restore Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 01:51:37 PM: Hi, I'm really desperate on this on. After struggling with some really weird bugs I finally finished my project. Buut I can't perform a good backup and restore. I'm using : MySql 5.0.16-nt (essentials version) Windows xp The DB contains tables, vw's and sp's. It is a very small one and at the moment I don't have any data inside. The whole backup file size is 100Kb. 1. I backed up my db using mysql administrator. It created a back up file with all tables, views and sp's, but when I tried a restore it got errors like. Could not handle this statement etc. 2. I tried : mysqldump -uroot -pmypassword dbname backup.sql And then mysqldump -uroot -pmypassword newDbName backup.sql I didn't get any error. The shell printed to the screen the backup file completely only without the table script part. It Created a backup file only for the tables (why ? a minute ago I did the backup with the same tool). But after restoring, the new db was still empty. 3. I tried to restore with mysql -uroot -pmypassword newDbName backup.sql But I got the same results. It should be a very simple and basic issue. Why everything is so hard with mysql ? Is there any really good and quick forum for mySql ? I posted some new thread in mysql.com at the past few days but never got answered. Is it just me or that's the life on the mysql planet ? I now try this mailing-list. I hope you can help me. Thanks in advance Barak You are in luck as the mailing list is quite active. Look at your actual dump file. It is simply a SQL script that will create all of the elements of the database and populate them with data (if you had any). My suspicion is that you have something that isn't quoted that needed to be. mysqldump has several options. You can see them with the command: mysqldump --help -or- refer to the manual http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html try dumping your database again, this time use the -r= and -Q options. That does two things: a) it avoids adding CR characters at the end of every line b) it puts backticks around EVERYTHING that needs them (table names, column names, etc) mysqldump -uroot -pmypassword -r backup.sql -Q dbname Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: urgent : PLEASE HELP - problems with back up and restore
Thanks again, I will try this and read the manual again. _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 23, 2005 9:30 PM To: Barak Mery Cc: mysql@lists.mysql.com Subject: RE: urgent : PLEASE HELP - problems with back up and restore There's a new option added in 5.0.13: -R (--routines) It's designed designed specifically to dump SPROCs and FUNCTIONs. I thought it would have been turned on by default but I guess it wasn't. Add that to the list of options and check your dump results again. I refer you again to the manual for additional warnings and cautions. http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 02:23:42 PM: Well Shawn, First of thanks for the quick result. I tried your suggestion but it didn't help no sp and no restore. It only shrinked the backup file (by deleting the cr). Is there any log file where I can trace that kind of erros ? Barak From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 23, 2005 9:07 PM To: Barak Mery Cc: mysql@lists.mysql.com Subject: Re: urgent : PLEASE HELP - problems with back up and restore Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 01:51:37 PM: Hi, I'm really desperate on this on. After struggling with some really weird bugs I finally finished my project. Buut I can't perform a good backup and restore. I'm using : MySql 5.0.16-nt (essentials version) Windows xp The DB contains tables, vw's and sp's. It is a very small one and at the moment I don't have any data inside. The whole backup file size is 100Kb. 1. I backed up my db using mysql administrator. It created a back up file with all tables, views and sp's, but when I tried a restore it got errors like. Could not handle this statement etc. 2. I tried : mysqldump -uroot -pmypassword dbname backup.sql And then mysqldump -uroot -pmypassword newDbName backup.sql I didn't get any error. The shell printed to the screen the backup file completely only without the table script part. It Created a backup file only for the tables (why ? a minute ago I did the backup with the same tool). But after restoring, the new db was still empty. 3. I tried to restore with mysql -uroot -pmypassword newDbName backup.sql But I got the same results. It should be a very simple and basic issue. Why everything is so hard with mysql ? Is there any really good and quick forum for mySql ? I posted some new thread in mysql.com at the past few days but never got answered. Is it just me or that's the life on the mysql planet ? I now try this mailing-list. I hope you can help me. Thanks in advance Barak You are in luck as the mailing list is quite active. Look at your actual dump file. It is simply a SQL script that will create all of the elements of the database and populate them with data (if you had any). My suspicion is that you have something that isn't quoted that needed to be. mysqldump has several options. You can see them with the command: mysqldump --help -or- refer to the manual http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html try dumping your database again, this time use the -r= and -Q options. That does two things: a) it avoids adding CR characters at the end of every line b) it puts backticks around EVERYTHING that needs them (table names, column names, etc) mysqldump -uroot -pmypassword -r backup.sql -Q dbname Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: urgent : PLEASE HELP - problems with back up and restore
Thanks Marko :) -Original Message- From: Marko Knezevic [mailto:[EMAIL PROTECTED] Sent: Thursday, November 24, 2005 12:29 AM To: MySQL list Subject: Re: urgent : PLEASE HELP - problems with back up and restore Dear Barak, Save yourself year or two of your life and try using MySQL Front not that command line tools. Its really nice tool for handling MySQL databases Regards Marko -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: urgent : PLEASE HELP - problems with back up and restore
I saw both views and stored procedures in the dump file. What error do you get if you process the script with the source command within the MySQL CLI? mysql -uroot -pmypass (login welcome) mysql CREATE DATABASE IF NOT EXISTS bcm; mysql USE bcm; mysql source myback1.sql There is another option to drop/create the database. Your dump didn't have that so you need to do it manually in order to restore it. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 07:18:02 PM: Wel I read the manual but didn’t find something that I really need to add I suspect it is something with the script itself. Now I’m using mysqldump -uroot -pmypass --routines -q -x bcmmyback1.sql but after i run mysqldump -uroot -pmypass --routines bcmmyback1.sql it now restore the tables but not the views or sp’s here is the backup file thanks Barak snipped out backup file From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 23, 2005 9:30 PM To: Barak Mery Cc: mysql@lists.mysql.com Subject: RE: urgent : PLEASE HELP - problems with back up and restore There's a new option added in 5.0.13: -R (--routines) It's designed designed specifically to dump SPROCs and FUNCTIONs. I thought it would have been turned on by default but I guess it wasn't. Add that to the list of options and check your dump results again. I refer you again to the manual for additional warnings and cautions. http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 02:23:42 PM: Well Shawn, First of thanks for the quick result. I tried your suggestion but it didn’t help no sp and no restore. It only shrinked the backup file (by deleting the cr). Is there any log file where I can trace that kind of erros ? Barak From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 23, 2005 9:07 PM To: Barak Mery Cc: mysql@lists.mysql.com Subject: Re: urgent : PLEASE HELP - problems with back up and restore Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 01:51:37 PM: Hi, I'm really desperate on this on. After struggling with some really weird bugs I finally finished my project. Buut I can't perform a good backup and restore. I'm using : MySql 5.0.16-nt (essentials version) Windows xp The DB contains tables, vw's and sp's. It is a very small one and at the moment I don't have any data inside. The whole backup file size is 100Kb. 1. I backed up my db using mysql administrator. It created a back up file with all tables, views and sp's, but when I tried a restore it got errors like. Could not handle this statement etc. 2. I tried : mysqldump -uroot -pmypassword dbname backup.sql And then mysqldump -uroot -pmypassword newDbName backup.sql I didn't get any error. The shell printed to the screen the backup file completely only without the table script part. It Created a backup file only for the tables (why ? a minute agoI did the backup with the same tool). But after restoring, the new db was still empty. 3. I tried to restore with mysql -uroot -pmypassword newDbName backup.sql But I got the same results. It should be a very simple and basic issue. Why everything is so hard with mysql ? Is there any really good and quick forum for mySql ? I posted some new thread in mysql.com at the past few days but never got answered. Is it just me or that's the life on the mysql planet ? I now try this mailing-list. I hope you can help me. Thanks in advance Barak You are in luck as the mailing list is quite active. Look at your actual dump file. It is simply a SQL script that will create all of the elements of the database and populate them with data (if you had any). My suspicion is that you have something that isn't quoted that needed to be. mysqldump has several options. You can see them with the command: mysqldump --help -or- refer to the manual http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html try dumping your database again, this time use the -r= and -Q options. That does two things: a) it avoids adding CR characters at the end of every line b) it puts backticks around EVERYTHING that needs them (table names, column names, etc) mysqldump -uroot -pmypassword -r backup.sql -Q dbname Shawn Green Database Administrator Unimin Corporation - Spruce Pine
