Re: BUG: Cannot DELETE all records with NULL entries in UNIQUE KEYfields
On Mon, 9 Apr 2001, Thalis A. Kalfigopoulos wrote: This is perfectly normal from Mysql's part. What you call a uniq_id in the table where you don't declare it as a unique key is merely another int field and a table scan will be performed to actually find ALL the occurences of the value you are deleting. On the other hand in the case where you actually declare the uniq_id to be unique by building a UNIQUE index on it, Mysql will of course use the asserted uniqueness and stop processing as soon as the first occurence of the value you want to delete is found. I.e. it assumes that as a unique field there will not be another occurence of this value. Of course on the other hand it allows you to insert multiple cases of NULL even though it is a unique field, because NULL is just SO DAMN NULL that it is NOT EVEN EQUAL TO ITSELF :-) So when you have a NULL in a unique field and add another NULL Mysql will not complain about it cause Null!=Null whereas it would complain if you had inserted a '1' and tried to insert another '1'. Clear as mud? regards, thalis Okay..? Your explanation makes sense, but it seems a bit counter-intuitive. In my mind, if you insert identical values (no matter what they are) into a field that is declared to be a unique key, the database should complain like crazy, if not completely disallow the action. In any case, thanks for the explanation. - Matt -- Matt Loschert | email: [EMAIL PROTECTED]| Software Engineer | web: http://www.servint.net/ | ServInt Internet Services | phone: (703) 847-1381 | - 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 output file not valid as input file for mysql client
On Thu, 22 Feb 2001, Sinisa Milivojevic wrote:
[EMAIL PROTECTED] writes:
Description:
As of mysql 3.23.33, a dumpfile generated with mysqldump (using the flags:
--opt --all-databases) creates a file which will not be uploaded correctly
using the mysql client (as in: 'mysql -u root -pXX -q -s
dumpfile.sql').
Specifically, the problem relates to mysqldump's outputting of table keys.
See the actual dump of mysql.db below:
DROP TABLE IF EXISTS db;
CREATE TABLE db (
Host char(60) binary NOT NULL default '',
Db char(64) binary NOT NULL default '',
User char(16) binary NOT NULL default '',
Select_priv enum('N','Y') NOT NULL default 'N',
Insert_priv enum('N','Y') NOT NULL default 'N',
Update_priv enum('N','Y') NOT NULL default 'N',
Delete_priv enum('N','Y') NOT NULL default 'N',
Create_priv enum('N','Y') NOT NULL default 'N',
Drop_priv enum('N','Y') NOT NULL default 'N',
Grant_priv enum('N','Y') NOT NULL default 'N',
References_priv enum('N','Y') NOT NULL default 'N',
Index_priv enum('N','Y') NOT NULL default 'N',
Alter_priv enum('N','Y') NOT NULL default 'N',
PRIMARY KEY (Host,Db,User),
KEY User(User) pukes on this line
) TYPE=MyISAM COMMENT='Database privileges';
If a space is added (as in: "KEY User (User)"), the file will import correctly.
I looked through the source file mysqldump.c and it appears that there always
should be a space between the key name and the key columns, but my personal
dumpfiles show that all non-primary keys have no space between the key name
and the key column names. Go figure. :) Needless to say I don't get it.
How-To-Repeat:
See above description.
Fix:
See above description.
Submitter-Id: submitter ID
Originator: Matt Loschert
Organization:
Matt Loschert| email: [EMAIL PROTECTED]|
Software Engineer| web: http://www.servint.net/ |
ServInt Internet Services| phone: (703) 847-1381 |
MySQL support: none
Synopsis: mysqldump output file not valid as input file for mysql client
Severity: non-critical
Priority: medium
Category: mysql
Class:sw-bug
Release: mysql-3.23.33 (Source distribution)
Environment:
System: FreeBSD delft.servint.com 4.2-STABLE FreeBSD 4.2-STABLE #0: Tue Feb 6
11:43:35 EST 2001
[EMAIL PROTECTED]:/disk2/cvsup/branches/releng_4/obj/disk2/cvsup/branches/releng_4/src/sys/QUICKIE
i386
Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Using builtin specs.
gcc version 2.95.2 19991024 (release)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
LIBC:
-r--r--r-- 1 root wheel 1169450 Feb 6 12:05 /usr/lib/libc.a
lrwxr-xr-x 1 root wheel 9 Feb 6 12:05 /usr/lib/libc.so - libc.so.4
-r--r--r-- 1 root wheel 559516 Feb 6 12:05 /usr/lib/libc.so.4
Configure command: ./configure --prefix=/usr/local/mysql-23.33 --with-low-memory
Perl: This is perl, version 5.005_03 built for i386-freebsd
Hi!
The above happens as USER is a keyword in MySQL.
Regards,
Sinisa
Sure, but this is a standard dump of the whole database, and the mysql
client is having problems reloading the **mysql** database. The mysql.db
table defines this key, not one of my tables. If you cannot use the
--all-databases flag to mysqldump to make a valid backup file, what is the
flag useful for? :)
By the way, this used to work. I am not sure exactly what release it
stopped working in.
Thanks for the help.
- Matt
--
Matt Loschert | email: [EMAIL PROTECTED]|
Software Engineer | web: http://www.servint.net/ |
ServInt Internet Services | phone: (703) 847-1381 |
-
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 output file not valid as input file for mysql client
On Thu, 22 Feb 2001, Sinisa Milivojevic wrote:
Matt Loschert writes:
On Thu, 22 Feb 2001, Sinisa Milivojevic wrote:
[EMAIL PROTECTED] writes:
Description:
As of mysql 3.23.33, a dumpfile generated with mysqldump (using the
flags:
--opt --all-databases) creates a file which will not be uploaded
correctly
using the mysql client (as in: 'mysql -u root -pXX -q -s
dumpfile.sql').
Specifically, the problem relates to mysqldump's outputting of table
keys.
See the actual dump of mysql.db below:
DROP TABLE IF EXISTS db;
CREATE TABLE db (
Host char(60) binary NOT NULL default '',
Db char(64) binary NOT NULL default '',
User char(16) binary NOT NULL default '',
Select_priv enum('N','Y') NOT NULL default 'N',
Insert_priv enum('N','Y') NOT NULL default 'N',
Update_priv enum('N','Y') NOT NULL default 'N',
Delete_priv enum('N','Y') NOT NULL default 'N',
Create_priv enum('N','Y') NOT NULL default 'N',
Drop_priv enum('N','Y') NOT NULL default 'N',
Grant_priv enum('N','Y') NOT NULL default 'N',
References_priv enum('N','Y') NOT NULL default 'N',
Index_priv enum('N','Y') NOT NULL default 'N',
Alter_priv enum('N','Y') NOT NULL default 'N',
PRIMARY KEY (Host,Db,User),
KEY User(User) pukes on this line
) TYPE=MyISAM COMMENT='Database privileges';
If a space is added (as in: "KEY User (User)"), the file will import
correctly.
I looked through the source file mysqldump.c and it appears that there
always
should be a space between the key name and the key columns, but my
personal
dumpfiles show that all non-primary keys have no space between the key
name
and the key column names. Go figure. :) Needless to say I don't get
it.
How-To-Repeat:
See above description.
Fix:
See above description.
Submitter-Id:submitter ID
Originator: Matt Loschert
Organization:
Matt Loschert | email: [EMAIL PROTECTED]
|
Software Engineer | web: http://www.servint.net/ |
ServInt Internet Services | phone: (703) 847-1381 |
MySQL support: none
Synopsis:mysqldump output file not valid as input file for mysql client
Severity:non-critical
Priority:medium
Category:mysql
Class: sw-bug
Release: mysql-3.23.33 (Source distribution)
Environment:
System: FreeBSD delft.servint.com 4.2-STABLE FreeBSD 4.2-STABLE #0: Tue Feb
6 11:43:35 EST 2001
[EMAIL PROTECTED]:/disk2/cvsup/branches/releng_4/obj/disk2/cvsup/branches/releng_4/src/sys/QUICKIE
i386
Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Using builtin specs.
gcc version 2.95.2 19991024 (release)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
LIBC:
-r--r--r-- 1 root wheel 1169450 Feb 6 12:05 /usr/lib/libc.a
lrwxr-xr-x 1 root wheel 9 Feb 6 12:05 /usr/lib/libc.so - libc.so.4
-r--r--r-- 1 root wheel 559516 Feb 6 12:05 /usr/lib/libc.so.4
Configure command: ./configure --prefix=/usr/local/mysql-23.33
--with-low-memory
Perl: This is perl, version 5.005_03 built for i386-freebsd
Hi!
The above happens as USER is a keyword in MySQL.
Regards,
Sinisa
Sure, but this is a standard dump of the whole database, and the mysql
client is having problems reloading the **mysql** database. The mysql.db
table defines this key, not one of my tables. If you cannot use the
--all-databases flag to mysqldump to make a valid backup file, what is the
flag useful for? :)
By the way, this used to work. I am not sure exactly what release it
stopped working in.
Thanks for the help.
- Matt
--
Matt Loschert | email: [EMAIL PROTECTED]|
Software Engineer | web: http://www.servint.net/ |
ServInt Internet Services | phone: (703) 847-1381 |
It is not client bug, but a server bug.
We shall put on our TODO to fix that.
Regards,
Sinisa
Thanks, I appreciate the help.
- Matt
--
Matt Loschert | email: [EMAIL PROTECTED]|
Software Engineer | web: http://www.servint.net/ |
ServInt Internet Services | phone: (703) 847-1381 |
-
Before posting, please check:
http://www.mysql.com/manual.php (the manua
