mysqldump bug ?
Hello, I'm using mysql-max 3.23.49 binary distribution. I use mysqldump utility to dump tables from my DB. for example: mysqldump --opt -u root -p EFDB users users.sql users.sql file contains: -- -- MySQL dump 8.21 -- -- Host: localhostDatabase: EFDB - -- Server version 3.23.49-max-log -- -- Table structure for table 'users' -- DROP TABLE IF EXISTS users; CREATE TABLE users ( user_id int(10) unsigned NOT NULL auto_increment, name varchar(50) NOT NULL default '', login varchar(20) NOT NULL default '', password varchar(16) NOT NULL default '', email varchar(50) NOT NULL default '', org varchar(128) NOT NULL default '', country char(3) NOT NULL default '', description varchar(128) NOT NULL default '', PRIMARY KEY (user_id) ) TYPE=InnoDB; /*!4 ALTER TABLE users DISABLE KEYS */; . . . INSERT follows. - Now take a look at the line saying: /*!4 ALTER TABLE users DISABLE KEYS */; This means that alter table query will be executed only if MySQL version is 4.00.00 or higher. When i try to use mysql utility to import this dumped file (mysql -u root -p EFDB users.sql) I get the NO SQL QUERY ERROR. This is probably caused by misplaced semicolon ; at the end of the line. In my case, alter table query is ignored because my MySQL version is 3.23.49. Then mysql utility tries to interpret ; as the end of empty SQL query. I think, this semicolon should be placed within the /* */ comments. What do you think? - 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: mysqldump bug during regenerate enum field.
It would seem that there is a bug, and it's that create table accepts an 'illegal' definition. It should either convert the integer to a literal (making the definition legal and dumpable), or it should barf on it. IMO, of course. --Pete [Charset iso-8859-1 unsupported, filtering to ASCII...] But my problem is perhaps simply in the concept: When MySQL accept a create table in entry (as bad as it is like this one from a guy in my team), mysqldump should regenerate from this table a create table statement accepted by MySQL too. no? it's not logic? ;-) Regards, Le Mon, Jun 18, 2001 at 09:34:27AM -0400, John Cichy a _crit: Sinisa, A good way to prove that mysqldump is doing what it should, might be to do a describe TESTBADDUMP; I think this will prove that mysqldump is just dumping what is in the table def, which is all you can expect. Have a great day... John On Monday 18 June 2001 09:46, Sinisa Milivojevic wrote: Guillaume Fougnies writes: ok so it's a failure from mysqldump. I know this declaration is not really correct but mysqldump must regenerate a correct sql statement (good when pipe in mysql client i mean at least ;) ). It's not the case. Regards, -- Guillaume FOUGNIES Research Development Profile For You Ltd. No, it is not mysqldump failure. If you specify integers where literals are expected, then it is your fault. Use quotes and a problem will dissapear. Regards, -- For technical support contracts, go to https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - 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 -- Have a great day... John -- Guillaume FOUGNIES Research Development Profile For You Ltd. - 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
mysqldump bug during regenerate enum field.
mysqldump regenerates a bad sql statement from a bad initial create table.
When you create this table for example (ok it's not a really nice definition):
CREATE TABLE TESTBADDUMP (
e_test enum('0','1') NOT NULL default 0
);
desc TESTBADDUMP;
++---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++---+--+-+-+---+
| e_test | enum('0','1') | | | | |
++---+--+-+-+---+
and use mysqldump to regenerate the sql statement, you obtain:
CREATE TABLE TESTBADDUMP (
e_test enum('0','1') NOT NULL default ''
);
this new declaration is not correct and refused by MySQL.
MySQL accept the value '' for insert but not for create table.
I'm not sure if it's normal to authorize empty values in enum.
If this is normal, the bug is in mysqldump.
Originator:Guillaume Fougnies
Organization: Profile For You Ltd.
MySQL support: licence
Severity: non-critical
Priority: low
Category: mysql
Class: sw-bug
Release: mysql-3.23.39 (Source distribution)
System: Linux db-am-001 2.2.19 #3 SMP lun jun 18 10:53:11 CEST 2001 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i686-pc-linux-gnu/egcs-2.91.66/specs
gcc version egcs-2.91.66 19990314 (egcs-1.1.2 release)
Compilation info: CC='gcc' CFLAGS='-O6 -mpentiumpro -s' CXX='c++' CXXFLAGS='-O6
-mpentiumpro -s' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Jun 12 11:15 /lib/libc.so.6 - libc-2.1.3.so
-rwxr-xr-x 1 root root 4101005 Sep 3 2000 /lib/libc-2.1.3.so
-rw-r--r-- 1 root root 20268874 Sep 3 2000 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Sep 3 2000 /usr/lib/libc.so
Configure command: ./configure --with-libwrap --enable-thread-safe-client
--enable-assembler --disable-large-files --without-debug --with-mysqld-user=db
--without-raid --disable-large-files --prefix=/usr/local/mysql-3.23.39
--localstatedir=/usr/local/mysql_var
-
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: mysqldump bug during regenerate enum field.
[EMAIL PROTECTED] writes:
mysqldump regenerates a bad sql statement from a bad initial create table.
When you create this table for example (ok it's not a really nice definition):
CREATE TABLE TESTBADDUMP (
e_test enum('0','1') NOT NULL default 0
);
desc TESTBADDUMP;
++---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++---+--+-+-+---+
| e_test | enum('0','1') | | | | |
++---+--+-+-+---+
and use mysqldump to regenerate the sql statement, you obtain:
CREATE TABLE TESTBADDUMP (
e_test enum('0','1') NOT NULL default ''
);
this new declaration is not correct and refused by MySQL.
MySQL accept the value '' for insert but not for create table.
I'm not sure if it's normal to authorize empty values in enum.
If this is normal, the bug is in mysqldump.
Hi!
The above is expected behaviour.
ENUM and SET types can take values only in the form of literals. If
you supply non-literal, like integer 0, MySQL tries it's best to find
the most approximate value.
Internally, MySQL does store both types as integers, but can insert /
update / display only it's representations of literals or set of
literals.
--
Regards,
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
___/ www.mysql.com
-
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: mysqldump bug during regenerate enum field.
Hi,
mysqldump regenerates a bad sql statement from a bad initial create table.
When you create this table for example (ok it's not a really nice definition):
CREATE TABLE TESTBADDUMP (
e_test enum('0','1') NOT NULL default 0
);
desc TESTBADDUMP;
++---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++---+--+-+-+---+
| e_test | enum('0','1') | | | | |
++---+--+-+-+---+
and use mysqldump to regenerate the sql statement, you obtain:
CREATE TABLE TESTBADDUMP (
e_test enum('0','1') NOT NULL default ''
);
If you use default 0, 0 is an integer. But ENUM only accept strings,
so try to use default '0' when you create the table.
Have a nice day,
Thomas Karcher
-
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: mysqldump bug during regenerate enum field.
Try quoting (' ') the 0 in your create:
CREATE TABLE TESTBADDUMP (
e_test enum('0','1') NOT NULL default '0' );
-^
You should then get the result you want.
Have a great day...
John
On Monday 18 June 2001 08:54, [EMAIL PROTECTED] wrote:
mysqldump regenerates a bad sql statement from a bad initial create table.
When you create this table for example (ok it's not a really nice
definition):
CREATE TABLE TESTBADDUMP (
e_test enum('0','1') NOT NULL default 0
);
desc TESTBADDUMP;
++---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++---+--+-+-+---+
| e_test | enum('0','1') | | | | |
++---+--+-+-+---+
and use mysqldump to regenerate the sql statement, you obtain:
CREATE TABLE TESTBADDUMP (
e_test enum('0','1') NOT NULL default ''
);
this new declaration is not correct and refused by MySQL.
MySQL accept the value '' for insert but not for create table.
I'm not sure if it's normal to authorize empty values in enum.
If this is normal, the bug is in mysqldump.
Originator: Guillaume Fougnies
Organization: Profile For You Ltd.
MySQL support: licence
Severity:non-critical
Priority:low
Category:mysql
Class: sw-bug
Release: mysql-3.23.39 (Source distribution)
System: Linux db-am-001 2.2.19 #3 SMP lun jun 18 10:53:11 CEST 2001 i686
unknown Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc GCC: Reading specs from
/usr/lib/gcc-lib/i686-pc-linux-gnu/egcs-2.91.66/specs gcc version
egcs-2.91.66 19990314 (egcs-1.1.2 release)
Compilation info: CC='gcc' CFLAGS='-O6 -mpentiumpro -s' CXX='c++'
CXXFLAGS='-O6 -mpentiumpro -s' LDFLAGS='' LIBC:
lrwxrwxrwx 1 root root 13 Jun 12 11:15 /lib/libc.so.6 -
libc-2.1.3.so -rwxr-xr-x 1 root root 4101005 Sep 3 2000
/lib/libc-2.1.3.so -rw-r--r-- 1 root root 20268874 Sep 3 2000
/usr/lib/libc.a -rw-r--r-- 1 root root 178 Sep 3 2000
/usr/lib/libc.so Configure command: ./configure --with-libwrap
--enable-thread-safe-client --enable-assembler --disable-large-files
--without-debug --with-mysqld-user=db --without-raid --disable-large-files
--prefix=/usr/local/mysql-3.23.39 --localstatedir=/usr/local/mysql_var
-
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
--
Have a great day...
John
-
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: mysqldump bug during regenerate enum field.
Le Mon, Jun 18, 2001 at 04:04:52PM +0300, Sinisa Milivojevic a écrit:
[EMAIL PROTECTED] writes:
mysqldump regenerates a bad sql statement from a bad initial create table.
When you create this table for example (ok it's not a really nice definition):
CREATE TABLE TESTBADDUMP (
e_test enum('0','1') NOT NULL default 0
);
desc TESTBADDUMP;
++---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++---+--+-+-+---+
| e_test | enum('0','1') | | | | |
++---+--+-+-+---+
and use mysqldump to regenerate the sql statement, you obtain:
CREATE TABLE TESTBADDUMP (
e_test enum('0','1') NOT NULL default ''
);
this new declaration is not correct and refused by MySQL.
MySQL accept the value '' for insert but not for create table.
I'm not sure if it's normal to authorize empty values in enum.
If this is normal, the bug is in mysqldump.
Hi!
The above is expected behaviour.
ENUM and SET types can take values only in the form of literals. If
you supply non-literal, like integer 0, MySQL tries it's best to find
the most approximate value.
Internally, MySQL does store both types as integers, but can insert /
update / display only it's representations of literals or set of
literals.
ok so it's a failure from mysqldump.
I know this declaration is not really correct but mysqldump must regenerate a correct
sql statement (good when pipe in mysql client i mean at least ;) ).
It's not the case.
Regards,
--
Guillaume FOUGNIES
Research Development Profile For You Ltd.
-
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: mysqldump bug during regenerate enum field.
but mysqldump generates an invalid statement.
mysql CREATE TABLE TESTBADDUMP (
- e_test enum('0','1') NOT NULL default ''
- );
ERROR 1067: Invalid default value for 'e_test'
mysql
I think that's what the orignator is trying to show. That mysqldump is
generating an invalid statement.
thanks,
-- Andrew
- Original Message -
From: Thomas Karcher [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, June 18, 2001 9:08 AM
Subject: Re: mysqldump bug during regenerate enum field.
Hi,
mysqldump regenerates a bad sql statement from a bad initial create
table.
When you create this table for example (ok it's not a really nice
definition):
CREATE TABLE TESTBADDUMP (
e_test enum('0','1') NOT NULL default 0
);
desc TESTBADDUMP;
++---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++---+--+-+-+---+
| e_test | enum('0','1') | | | | |
++---+--+-+-+---+
and use mysqldump to regenerate the sql statement, you obtain:
CREATE TABLE TESTBADDUMP (
e_test enum('0','1') NOT NULL default ''
);
If you use default 0, 0 is an integer. But ENUM only accept strings,
so try to use default '0' when you create the table.
Have a nice day,
Thomas Karcher
-
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: mysqldump bug during regenerate enum field.
Guillaume Fougnies writes: ok so it's a failure from mysqldump. I know this declaration is not really correct but mysqldump must regenerate a correct sql statement (good when pipe in mysql client i mean at least ;) ). It's not the case. Regards, -- Guillaume FOUGNIES Research Development Profile For You Ltd. No, it is not mysqldump failure. If you specify integers where literals are expected, then it is your fault. Use quotes and a problem will dissapear. Regards, -- For technical support contracts, go to https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - 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: mysqldump bug during regenerate enum field.
Sinisa, A good way to prove that mysqldump is doing what it should, might be to do a describe TESTBADDUMP; I think this will prove that mysqldump is just dumping what is in the table def, which is all you can expect. Have a great day... John On Monday 18 June 2001 09:46, Sinisa Milivojevic wrote: Guillaume Fougnies writes: ok so it's a failure from mysqldump. I know this declaration is not really correct but mysqldump must regenerate a correct sql statement (good when pipe in mysql client i mean at least ;) ). It's not the case. Regards, -- Guillaume FOUGNIES Research Development Profile For You Ltd. No, it is not mysqldump failure. If you specify integers where literals are expected, then it is your fault. Use quotes and a problem will dissapear. Regards, -- For technical support contracts, go to https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - 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 -- Have a great day... John - 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
mysqldump bug?
Hi all, I don't know if this is a bug in mysqldump but it behaves in a strange way. I tried to use mysqldump to dump out the data and structure of a small database in order to populate another database but not all records are dumped. I tried to flush the tables from mysql server using FLUSH TABLES and also use mysqlamdin to refresh the database(by flushing tables the log files) but records are still missing from the dumped file. I have tried to shutdown and restart the database server but that didn't help. Is this a bug in mysqldump or there is some other things I have to do to get mysqldump dumps out the entire database with DDL DML statements? Thank you very much for your help. --Regards, Huy. __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ - 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
