MYSQL :: Cannot Restore 3.23.58 Db To MySQL 4.0.22-debug

MySQL Compiletime Errors With --with-debug=full
I am trying to report a bug, and I would like to compile mysql with =
debugging enabled to provide further information, but I receive compile =
errors. I would like to resolve the compile errors before I report the =
possible bug I have discovered.

I am running FreeBSD 4.8, and I attempt to compile mysql-4.0.13 with the =
following configure command: ./configure --with-vio --with-openssl =
--with-debug=3Dfull (the initial bug I wanted to report is related to =
openssl so compiling with the ssl and vio options is essential).

I then try running 'make' but I get a lot of undefined reference errors =
- so I have piped the STDOUT and STDERR to a file available for download =
at: http://www.no1.com.au/~adamc/makeoutput.txt hoping that someone with =
more C/C++ knowledge than myself can offer some insight.

Debug Sql
I'm trying to create an sql statement.

here's my statement that works in mssql ---

select count(customerid) as countcustomer from customer where customerid in(SELECT customerid from customerofferlink where offerid=1) and mytimestamp BETWEEN '2005-01-04' AND '2005-08-2005').

How To Debug
The following exception occurs at a sporadic point in my Java web app. What is the best way to diagnose and fix?

Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1125)
at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:677)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1357)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1274)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1259)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2299)
... 63 more

Debug Query
I am trying to run an sql as follows:

SELECT * from customer WHERE mytimestamp BETWEEN '2005-01-04' AND '2005-08-01' order by name

I am getting no records:

There are clearly records for example on 2005-11-16 21:21:33 and in the format I just posted (that's how i see it in phpmyadmin.)

Ahat am i doing wrong?

Need Help To Debug Table Creation Error
I'm trying to install a software package on my server and when I try to import the .sql file I get an error on the following table creation statement:

DROP TABLE IF EXISTS general;
CREATE TABLE general (
id bigint(20) DEFAULT Ɔ' NOT NULL auto_increment,
last_name varchar(40) NOT NULL,
middle_initial char(1) DEFAULT ' ' NOT NULL,
first_name varchar(40) NOT NULL,
work_phone varchar(20),
extension varchar(4),
cell_phone varchar(20),
job_desc varchar(80),
department varchar(30),
site varchar(30),
pager_pin_number varchar(20),
pager_company varchar(20),
pager_phone varchar(20),
email varchar(160),
web varchar(160),
PRIMARY KEY (id),
UNIQUE id (id),
UNIQUE last_name (last_name, middle_initial, first_name)
);
The error I get is:

#1067 - Invalid default value for 'id'

I'm using Mysql version 4.1.20

Any ideas?

Mysql Restore
I have been running a mysql backend to phpBB on my home Gentoo based linux server. I recently switched the mysql and phpBB to run on a dedicated web server on my home network. I was using mysql version 4.1.14 and then 4.1.14-r1 on my new web server. The problem is that the character set seems to have gotten corrupted during the backup and restore process. The forum is based in French and hence a lot of the special characters have gotten messed up. I have even tried copying the my.cfg file from the old server to the new one but with no effect.

Mysql Backup And Restore For 4.1.x
I have mysql 4.1.11 on my current server, i need my database restore another
server.. (another server mysql version is 4.1.11 same.)

And now... I just created new my database to new server following command:

CREATE DATABASE dbname DEFAULT CHARACTER SET latin5 COLLATE
latin5_turkish_ci;

because our database and tables uses latin5 and turkish_ci collation.

How can i get correct backup and restore for this character sets? example;

mysqldump -uUSER -pDBNAME > /path/backup.sql

and restore;

mysql -uUSER -pDBNAME < /path/backup.sql

is this correct for me?

OR

mysqldump --default-character-set=latin5 -uUSER -pDBNAME > /path/backup.sql

and restore;

mysql --default-character-set=latin5 -uUSER -pDBNAME < /path/backup.sql

some peoples suggest me --default-character-set= variable for mysql 4.1.x
backups and restore, is this correct? also if we backup database
--default-character-set option, restore command is same option will need to
use?

How To Restore To MySQL? I Have Phpmyadmin.
How can I restore to mySQL using phpmyadmin? I created a database and now I want to restore from another db to a newly created database. I have it save on my local drive.

Cannot Restore Mysql Database
My host suddenly went out of business, and deleted all my files without a notice. The only thing I have now is a week old back up
Now everytime I'm with a new host, and created a new database, to restore the old one over it, but everytime I try to restore, using ssh, I get that dreadful error message:
" ERROR 1062 at line 238892: Duplicate entry '189864' for key 1"

Added Ã‚ Character After Mysql Db Restore
We had to restore a mysql database from backup. Now it contains the character Ã‚ all over the place, and it's annoying. Is there anything we can do to remove it?

How Do You Backup/Restore Big MySQL DB's?
i found a few hints with the search function, but would love to know your ways to backup and restore your "big" mysql databases?

Backup/Restore MySQL Databases.
Is it safe to say that restoring the [/usr/local/myql/data] directory from a
backup would that restore all database data and structure? All usr/pwd and
GRANT info?

How To Restore Default Settings Of Mysql
i have dump all things from A computer database to B computer database; but it replace all settings on B computer database, i want to restore all the setting of B like a default settings after installaitonl.

Looking For A Backup/restore MySQL DB Application
I'm looking for a very simple application to backup/restore a mySQL database.

Actually, I would need to be able to launch the application from a command line so that I dont have to select a profile or anything like that...just put the command line in a bat file, run the bat file and everything is done.

I've already tried :

- Backup Watcher for Mysql
- MyWitch
- DumpTimer
- SQLyog
- MySQL Snap

None of them seems to work with a command line.

MySQL Database Restore Error
I decided to download a db and upload it back to the server just out of curiosity. My site is now missing the top menu and components menu in the backend of Mambo. This site is for a client and I'm in big trouble here. Code:

MySQL Administrator Backup/Restore
I use Backup option in MySQL Administrator to backup Database & Data. When go to Restore Backup file, restoring process (showing on progress bar) is allways finished before reaching full size of Backup file. Is that mean that NOT all data are restored? (I can't check whole database because of large amount of data).

Restore Old Mysql Data To A New Schema
I have new version of paryoll application and hence now I have to restore my old backed up mysql data (old mysql data like *.frm,*.myd,*.myi has been zipped).Unfortunately this is not easy.

The new version has new schema (e.g emp_desc has been added in the new EMPLOYEE table).
Wondering if their is a good way for me to do restoration. Is their any mysql tool that does this automatically or I have to programatically find out of a schema change.

Restore From Backup With MySQL Admin
I am trying to restore a mysqldump txt file to another cpu using MySQL Admin. I receive an error:

The selected file was generated by mysqldump and cannot be restored by this application.

I understand there are other ways of transfering a database to MySQL on a different cpu.
ie. mysql -u Username -p DBname < Filename.sql

When I try this it doesn't work I'm doing something wrong. Does the "filename" need to be an exact path to the file or just the file name ie. C:folderfolderdatabase.sql

Also does it matter if the file is .txt or .sql?
Does it matter where I have the database.sql file on my computer?
Do I need to have a new empty database created?

How To Restore A Mysql Database From A .bck File
i am trying to move my site to a new host. the mysql database was backed up to a .bck file nightly and i would be most appreciative if one of you could inform me of how to restore it?

Restore Root Access To Mysql
I'm another linux noob running redhat 9 and the included mysql.

I had it working using phpmyadmin to configure it until I follishly removed my
permisions for root trying to secure my server in phpmyadmin. Is there
anyway to get this back? I've tried the grant options but I can't even
logon to mysql as root@localhost. Thanks for any tips.
jedihermit@jedihermit.com

Database Restore / MySQL 4.1 -- InnoDB
I have got a MySQL 4.1 that we use wit vBulletin.... We had a crash
last week and I've been able to restore the data up to September 2005
as it stands.

Now, I'm under the impression I could rebuild the data from the logs
but I cannot seem to figure out how to do that based on the tools and
instructions posted.

I've got my ib_log files (ib_log0 and ib_log1) also my ibdata file,
which is full of all the data i'm trying to get restored. Opening it up
in hex editor, I can see posts and information right up to the moment
the database crashed.

What steps should I take to restore the data from the log/data files?

I've tried several different steps, but I'm goofing it up somewhere I
guess. Are there some other tools available to help with this?

This site was a 'non-revenue' site, so I didn't exactly make all of the
redundancy arrangements that I would normally do for an Oracle/MSSQL or
any other live business database. I'm ateast going to enable mirroring
this time around and move the backups to another location.

A Cron Job To Backup And Restore Mysql Db
my server won't give me shell so i'm stuck with phpmyadmin, its stupid, i can't even restore a 20mb backup, whether gzipped or text, it just times out..
so i want to do it via a cron job, what commands are they

Can You Retrieve/restore A MySQL User Password ?
Subject: Can you retrieve/restore a MySQL user Password?

I'm working on my own, local WXP, MySQL, version: 3.23.38-nt and I have the "root" password, and normally use the root account.

I have forgotten one of the Passwords for a user account which I have set up. Is there any way to UnEncrypt the users password, so I can use that account again? Any handy function that the "root" account can use, to determine a user's password?

OR

Should I delete the account and start from scratch?

OR

Should I re-apply GRANT statements so as to reset user/pass info?

MySQL Cannot Backup And Restore UCS2 Data
Did a mysqldump and the UCS2 fields where dumped with insert statements with standard 8 bit ascii character strings, not UCS2.

Restore Mysql Databases From /var/lib/mysql/user_database/ Files Only?
I recently had a harddrive failure and luckliy I can still access certain directories on this failed drive. I can still access the /var/lib/mysql/ directory which holds all the users databases and have backed all these up separately using tar.

Now what I need to know is how do you restore these database files to another server? I tried simply untar'ing one of these to the new servers /var/lib/mysql/ direcotry and it stuffed Mysql up - it went offline. I had to get a cpanel tech to bring Mysql back online.

ERROR:Using The Mysql Client On Freebsd To Restore A Mysqldump??
I cannot figure out how to resolve this error, can anyone point me in
the right direction?

mysql: Out of memory (Needed 182246856 bytes)
mysql: Out of memory (Needed 182242760 bytes)

User time 66.66, System time 36.05
Maximum resident set size 526660, Integral resident set size
-2117839848
Non-physical pagefaults 2585767, Physical pagefaults 1, Swaps 0
Blocks in 11195 out 0, Messages in 2628 out 2694, Signals 0
Voluntary context switches 176407, Involuntary context switches 11071

Backup/Restore Mysql Database From Remote Server To Local Pc
Im trying to download mysql database "dbproducts1" which is residing in my webhost's server (Linux). From telnet i backed up using "mysqldump -u -p dbproducts1 > dbproducts1.sql"

Then i downloaded this file via ftp to my local pc(WinXP). Now on my local pc to restore database i went to command prompt and i did following:

mysql -u -p dbproducts1 < dbproducts1.sql

I got following error:

ERROR 1064 (42000) at line 380: You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to use

near '-workshop (
title varchar(250) NOT NULL default '',
decription longtext NOT ' at line 1

I opened the phpmyadmin and found that dbproducts1 database was restored but with only few tables i.e. not all tables in the db was restored.

Can somebody please help me to backup/restore this database. I checked the mysql server version and found following:

server version: 5.0.24a-community in my local winxp pc
server version: 3.23.36 in my webhost's server (Linux)

How To Backup/restore To Move Databases From One Server To Another (Win2k3 Enterprise, Mysql)
I'm trying to move a "schema" (I guess thats the term) aka database from our webserver2 to our webserver3. I backup the .sql file, and move it over to webserver3. I tell it to restore that file and create the database if it does not exist. Part way thru i get all these errors like: Code:

MYSQL Database Alteration, Repair And Restore Takes Huge Time
I am trying to run alteration queries on one of my mysql tables which has more then 22,00,0000 lakh records. Its been 23 hours and the process is still running (I have to close all the sites running on server due to same).

My server specifications:
Red Hat 9.0
Pentium 4 3.0 GHz
2 GB Ram, Burst RAM 5 GB
Running webserver, mail server as well.

Is there a way I can view the minute process details, as what table record is being updated ?

Restore Db From 4.1.12 To 3.23
I've a backup from my database running on mysql 4.1.12

Now i want to restore this to an old machine running 3.23.

How to do this. I get several errors.

DB Restore
I appear to be getting the following error message everytime I want to restore this database. The message is as follows:

Unable to restore database web1_wordp : ERROR 1064 (42000) at line 754: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '22:17:15)' at line 1

I'm not quite sure what this means :confused: - How would I correct it?

Restore
A customer passed me her backup, for me to open in my MySql. They are 50Mb of backup. MySQLAdministrator is being long a lot, very even to make the restoration in my machine (1kbps!!!!)

Restore
I've got a table with over 400,000 records. I did a mysqldump to ensure I could restore it in case of problems. Sure enough, problems arose and I needed to do a restore. The mysqldump created the usual create database, create table and one gigantic insert statement, where all 400,000+ records are inserted. I started my restore around 10pm or so yesterday and was surprised to wake up this morning and find it still running. Considering the dump itself took a couple of seconds, why is the restore taking so long? I'm building a website that's going to have tons of traffic. A database restore that's going to take multiple hours seems to be unacceptable. One way to speed up the process would have been to simply copy the actual database files and just stick them back in the data directory.

How To Restore Database
I think i will have to da a full resortation of a database.

lets assume old database is deleted.

So I go to my hosting control panel and create a new mysql database
Use same username and password as used in original

open phpmyadmin and do to the new database and select import-then import the file that I have as a backup--i have a file that I exported as a text file-a full databse export. it is about 4 mb so I hope can do it at one time

Is that basically how I can do it??

Database Restore Help
im trying to construct a database using a backup file i made aout a year ago when i use phpmyadmin to upload the file i get an error on the first table it trys to create

CREATE TABLE `cal` (

`id` bigint( 20 ) NOT NULL AUTO_INCREMENT ,
`author` varchar( 32 ) NOT NULL default '',
`subj` varchar( 64 ) NOT NULL default '',
`txt` text NOT NULL ,
`typ` tinyint( 4 ) NOT NULL default Ɔ',
`dmy` varchar( 8 ) NOT NULL default '',
`hm` varchar( 4 ) NOT NULL default '',
`wday` tinyint( 4 ) NOT NULL default Ɔ',
`location` varchar( 64 ) NOT NULL default '',
`approved` char( 1 ) NOT NULL default '',
`tz` int( 11 ) NOT NULL default Ɔ',
`dat` bigint( 20 ) NOT NULL default Ɔ',
`mkt` bigint( 20 ) NOT NULL default Ɔ',
PRIMARY KEY ( `id` ) ,
KEY `typ` ( `typ` ) ,
KEY `wday` ( `wday` ) ,
KEY `dmy` ( `dmy` ) ,
KEY `mkt` ( `mkt` )

) ENGINE = MYISAM DEFAULT CHARSET = latin1;

With this msg

MySQL said:

#1064 - You have an error in your SQL syntax near ') ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 20

this was a backup straight from phpmyadmin some time ago, can someone tell me why it wont restore?

Stalled Restore
I Exported my MySQL database (60KB) (MySQL 4.0.24-standard) from iPowerweb hosting site.

I know want to restore it to my MySQL (mysql-4.1.13a-win32.zip) on my home server.

I loged on with MySQL Administrator, went to Restore, Opened .sql file, hit Start Restore, and it gets stuck at 9598 number of bytes each time I attempt it. Total is 60812 Bytes.

How To Restore A Database (.bak)
Recently I downloaded a ASP script, so I can use on my website, however in the instructions, it tells me the following : -

Restore the bbdf.bak SQL database with SQL 2005 (any edition) Management Tools

Now I have the file bbdf.bak, but I am not sure what I am suppose to do.

I have downloaded and installed Microsofts SQL 2005, but am totally lost as to what I am suppose to open and type .... ? ?

I would of thought it would be easier for them to just give me a .mdb file or something which I could use...

Restore Of Database
How do I go about restoring multiple databases at the same time. I know if I needed to restore 1 database I can do something like.

mysql db_name < backup-file.sql

but what if backup-file.sql consists of multiple databases (ie it was created using mysqldump-all-databases) , which database would I specify in the db_name section?

How To Restore .tgz Files?
how do I restore .tgz backup files using the mysql panel? I tried restoring it as a gunzipped file but it gave me errors.

SQL Database Restore
I upgraded to Tiger and it damaged my SQL database.

I have a backup file that is about 80 mb. The backup file was created
with PHPmyAdmin and is in .SQL format.

When I try to use PHPmyAdmin to load this .SQL file and restore my database, I get an error message that says NO SQL QUERY.

The file stops executing and NO data is restored.

Is this because the file is too big ??

Is there an application that will break the .SQL file down into smaller
parts ??

Is there a way to restore the data using the .SQL file and a direct SQL command line entry ??

Restore Problem
I am trying to do a Restore on a dump file (see below) but I get the "failed
copying a .frm file" error.

RESTORE TABLE ListMembers FROM
'/home/virtual/thefrugallife.com/var/www/html/12all/backups/1089048410_Mon05
-07-2004.sql';
+-------------+---------+----------+--------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------+---------+----------+--------------------------+
| ListMembers | restore | error | Failed copying .frm file |
+-------------+---------+----------+--------------------------+
1 row in set (0.77 sec)

Backup / Restore
I have this one FreeBSD box (nice Dell with one 40GB HDD). I have made a replica of the drive and put it on the secondary IDE channel. I want to do a restore of all the MySQL db's to the second drive right after the backup takes place from the primary drive.

The reasoning behind this is that this is the only system running this particular app and the company that wrote it went belly so, I NEED to have the ability to just swap drives bring it back up and be done.

Backup/Restore
I'm migrating a web site from my testing system on my local machine to the web host upon which it will live. My testing server is running MySQL v. 5.0.22, and the remote host is running 4.0.24. I am using MySQL Administrator v. 1.0.10 to run a backup and export my tables to an SQL file, and PHPMyAdmin to import the SQL file and run it on the server. At first, I was getting a 1065 - Query is Empty error. After reading through some of the postings here, I realized that some of the commented strings at the top of the file were unnecessary, and probably the cause of my error, so I removed them. I re-ran a backup that would only backup the appropriate tables and write the appropriate data (the database schema on my server is named differently, so I don't need to back up the full path). After editing the file to remove the uneeded stuff, I recieve a different error message, regarding an error in the SQL syntax of the dump file. The error in question occurs on a line that reads as follows:

)ENGINE = MYIASM DEFAULT CHARSET = latin1;

The error in question is as follows:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT CHARSET=latin1' at line 21

I'm having a hard time finding the issue, and would really appreciate if somebody might point me in the right direction to help me create a backup file that my server will interpret charitably and restore the data I need. I'm getting to the point where I may have to re-enter it all by hand, and that thought depresses me quite thoroughly.

Slow Restore
Mysql 4.1.15 on Win2k. Using InnoDB.

Using the mysql administrator gui to create a backup, everything goes
fine, and restores quickly.

Using the command line:

mysqldump %dbname% --single-transaction > %dbname%.sql

creates a file about 15% smaller than the gui produces, and is
EXTREMELY slow to restore. I have tried adding locks, skip opt,
everything. What does the gui use for a command to create this dump?

Backup/restore
Working on a single machine (win2k) needing to do backups. Trying Backup
Table with the following syntax:

BACKUP TABLE tbldealers TO 'c:mysqlackups'
BACKUP TABLE tbldealers TO /mysql/backups

I've tried with/without quotes, ticks, nothing except the path. I either
get error in syntax, or I do get 1 row in set, but there are no files in
that directory.

Any help would be appreciated.

I've read the manual as extensively as I know to and can't find anything on
backup table syntax.

I'd prefer to stay away from mysqldump and mysqlhotcopy and control the
backups myself.