Re: Searching on Two Keys with OR?
Thanks for the suggestion. This is not ideal for a couple of reasons: I'm using an object-relational layer that would have to be hacked up something fierce, and my actual query would be pretty hairy: (select * from t1 where a=x union select t1.* from t1,t2 where t1.b=t2.b and t2.c=y union select t1.* from t1,t2 where t1.b=t2.b and t2.d=z ) order by e I guess this is what a good optimizer would generate behind the scenes, but it'd be nice not to have to know about it. On Tue, 5 Aug 2003, Rudi Benkovic wrote: Have you tried using the UNION statement? That worked great for me. So, something like: (select * from sometable where f1 = 123) UNION (select * from sometable where f2 = 123) ? -- Rudi Benkovic [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with spatial extensions
Hi, I would like to have a column of a table of the type GeometryCollection, but when I try to type a simple example like the ones in the manual, I get this: mysql CREATE TABLE geom (g GEOMETRY); ERROR 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 'GEOMETRY)' at line 1 I'm running server mysqld-nt version 4.0.13 on a windows NT, the mysql client is the same version. Have I to install some plug-in or change some option in the configuration file? I need help. Thanks and excuse me by the english (I'm spanish). Enrique ___ Yahoo! Messenger - Nueva versión GRATIS Super Webcam, voz, caritas animadas, y más... http://messenger.yahoo.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
data type matching / composite key / index name
I have a problem that has raised a couple of questions. SITUATION: I have a table called pr_persona that has a composite key comprising pr_persona_db CHAR(2) pr_persona_id INT(11) auto increment On a separate table, rv_reservation, I have a foreign key that links to pr_persona as follows rv_reservation.rv_agent_id VARCHAR(13) I'm running MySQL version 3.23.40 PROBLEM: When I run a SELECT statement of the following nature SELECT rv_reservation.*, pr_persona.pr_name FROM rv_reservation LEFT JOIN pr_persona ON rv_reservation.rv_agent_id = concat(pr_persona.pr_persona_db, pr_persona.pr_persona_id) the optimiser does not join on the index - it performs a cross join. Attempted solution 1: Created a new field on pr_persona called pr_persona_ix VARCHAR(13) and populated it with the concatenation of pr_persona_db, pr_persona.pr_persona_id and defined the field as a UNIQUE index. I then changed the query to join ON rv_reservation.rv_agent_id = pr_persona_ix Success! - the optimiser uses the new index. However, this is not an optimal solution because I have to populate the redundant pr_persona_ix field. Attempted solution 2: Instead of creating the index described above, I changed the PRIMARY index to UNIQUE and gave it an index name of pr_persona_ix. When I run the same query MySQL doesn't recognise the index name of pr_persona_ix. PROBLEM SUMMARY: 1. Why does the optimiser not use the PRIMARY index in the original case? 2. Why is the index name of my composite UNIQUE index not recognised? Regards, Mike Coppinger -- Resrequest Online Reservation Systems Tel: +27-11-476 4740 Fax: +27-11-476 7235 Cell: +27-82-774 0820 Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems with spatial extensions
Enrique, Your English is great so don't apologise! Looking on the web site at http://www.mysql.com/doc/en/Spatial_extensions_in_MySQL.html , it appears that this feature was introduced in server version 4.1, so I'm afraid you'll have to upgrade your server. Best regards, Andy -Original Message- From: Enrique Andreu [mailto:[EMAIL PROTECTED] Sent: 05 August 2003 08:08 To: [EMAIL PROTECTED] Subject: Problems with spatial extensions Hi, I would like to have a column of a table of the type GeometryCollection, but when I try to type a simple example like the ones in the manual, I get this: mysql CREATE TABLE geom (g GEOMETRY); ERROR 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 'GEOMETRY)' at line 1 I'm running server mysqld-nt version 4.0.13 on a windows NT, the mysql client is the same version. Have I to install some plug-in or change some option in the configuration file? I need help. Thanks and excuse me by the english (I'm spanish). Enrique ___ Yahoo! Messenger - Nueva versión GRATIS Super Webcam, voz, caritas animadas, y más... http://messenger.yahoo.es -- 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]
mysql 4.0.1-alpha
7.5.5.2 Foreign Key Constraints Hi programmers, I try to code this CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN CHAR(9) NOT NULL, BDATE DATE, ADDRESS VARCHAR(30), SEX CHAR, SALARY DECIMAL(10,2), SUPERSSNCHAR(9), DNO INT NOT NULL, PRIMARY KEY (SSN), # INDEX (SUPERSSN, DNO), FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN), FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER) )TYPE = INNODB; And mysql complains with ERROR 1005 at line 9: Can't create table '.\company\employee.frm' (errno: 150) Bye Can someone please help me to fix this code? I think something is wrong with the index declaration, or with the foreign key constraint. But what can I do ? Yours sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication question
I have connect my web server (master) to my local server (slave) through dial-up and replicate 2 databases. a) Is there a log file describing the replications that happend (if any)? b) how do I know when to stop the dial-up so not to stop any data transfer through repliication? Thanx Nikos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 4.0.1-alpha
Morten Gulbrandsen [EMAIL PROTECTED] wrote: 7.5.5.2 Foreign Key Constraints Hi programmers, I try to code this CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN CHAR(9) NOT NULL, BDATE DATE, ADDRESS VARCHAR(30), SEX CHAR, SALARY DECIMAL(10,2), SUPERSSNCHAR(9), DNO INT NOT NULL, PRIMARY KEY (SSN), # INDEX (SUPERSSN, DNO), FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN), FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER) )TYPE = INNODB; And mysql complains with ERROR 1005 at line 9: Can't create table '.\company\employee.frm' (errno: 150) I think something is wrong with the index declaration, or with the foreign key constraint. Uncomment index declaration on SUPERSSN, DNO columns and create an index on DNO column. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with OR?
You are ORing on two different fields. The index cannot be used to check the value of z for an OR. Why are you cross posting? Joshua Spoerri wrote: On Tue, 5 Aug 2003, Alexander Keremidarski wrote: MySQL will never use any index for small tables. With just few rows using index adds overhead only. Table scan is faster in such cases. This is docummented behaviour. is 100,000 rows small? my simple OR queries take longer than a second. mysql create temporary table x (y int, z int, index (y, z)); insert into x select f1,f2 from myrealtable; alter table x add q int; explain select * from x where y = 1 or z = 1; Query OK, 0 rows affected (0.00 sec) Query OK, 101200 rows affected (1.95 sec) Records: 101200 Duplicates: 0 Warnings: 0 Query OK, 101200 rows affected (1.61 sec) Records: 101200 Duplicates: 0 Warnings: 0 +---+--+---+--+-+--++-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--++-+ | x | ALL | y | NULL |NULL | NULL | 101200 | Using where | +---+--+---+--+-+--++-+ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: storing large integers properly
How about BIGINT(10) ZEROFILL ? Eben Goodman wrote: I am storing book isbn numbers in a table. isbn numbers are 10 digit numbers and many start with 0. The data type of the field I am storing this info in is a bigint(16) unsigned. It appears that isbns that start with 0 are going in as 9 digit numbers, the 0 is being ignored or stripped. I have experienced this before with integer data types ignoring leading 0s. I'm wondering how to address this? Should I change the field to a varchar or char data type? Any advice is appreciated, thanks, Eben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data infile problem
Montagna, Dan wrote:
Hello,
I'm a very new mysql/php user and am trying to use the load data infile
command without luck. I'd like to replace a table using a text file but
can't get it to work. I set up a test table with no records using a telnet
console. I'm trying to write a php page that takes the file on the server
and adds the info into the table. Here's my code:
?php
# Connect to the database
$db = mysql_connect(server.net, username,password);
mysql_select_db(elasticnature,$db);
# Delete the current content of the table
$result = mysql_db_query('$sql_id',DELETE FROM tablename) or die (Invalid
DELETE query);
do you have a table called 'tablename' ? i guess not!
# Optimize the current table (recover empty space)
$result = mysql_db_query('$sql_id',OPTIMIZE TABLE tablename) or die
(Invalid OPTIMIZE query);
# Load local comma separated, fields enclosed by quotes text database - File
has to be in the same directory of this file
$result = mysql_db_query('$sql_id',LOAD DATA INFILE 'file.txt' INTO TABLE
tablename FIELDS TERMINATED BY ',' ENCLOSED BY '\') or die (Invalid DATA
LOAD query);
# Get how many records are present in the table now
$result = mysql_db_query('$sql_id',SELECT * from uploadtable) or die
(Invalid SELECT query);
$rows_count = mysql_num_rows($result);
echo Records: $rows_count; mysql_free_result($result);
?
I got this from an example given online and the output is the invalid
delete query. What's wrong with this code?? Thanks for any help...
--
Sebastian Mendel
www.sebastianmendel.de
www.tekkno4u.de
www.nofetish.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with OR?
On Tue, 5 Aug 2003, Alexander Keremidarski wrote: MySQL will never use any index for small tables. With just few rows using index adds overhead only. Table scan is faster in such cases. This is docummented behaviour. is 100,000 rows small? my simple OR queries take longer than a second. mysql create temporary table x (y int, z int, index (y, z)); insert into x select f1,f2 from myrealtable; alter table x add q int; explain select * from x where y = 1 or z = 1; Query OK, 0 rows affected (0.00 sec) Query OK, 101200 rows affected (1.95 sec) Records: 101200 Duplicates: 0 Warnings: 0 Query OK, 101200 rows affected (1.61 sec) Records: 101200 Duplicates: 0 Warnings: 0 +---+--+---+--+-+--++-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--++-+ | x | ALL | y | NULL |NULL | NULL | 101200 | Using where | +---+--+---+--+-+--++-+ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alter date
On Tue, 5 Aug 2003 11:41:21 -0300 Fabio Bernardo [EMAIL PROTECTED] wrote: Hi there, Do you know a sql command which I can write to obtain the last date that I updated a table SHOW TABLE STATUS LIKE 'yourtable'; fabrizio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table size in mysql
* NEWMEDIAPLAN how many records can i put in a mysql table. As many as you like, pretty much. The total file size could be delimited by your OS, but this can be dealt with using MERGE tables (splitting a single table in multiple files) or InnoDB tables (with multiple table spaces). i need a table with more than 50,000,000 records (just 4 short fields, it's basically a log with id,action,date,result). What do you think? Should not be a problem. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table size in mysql
hello. how many records can i put in a mysql table. i need a table with more than 50,000,000 records (just 4 short fields, it's basically a log with id,action,date,result). What do you think? thanks in advance!!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data infile problem
I'm using some default table names and such here...my actual code has the appropriate table, server, un, pw etc...
Re: replication w/o stopping the master
See the command LOAD DATA FROM MASTER ( http://www.mysql.com/doc/en/LOAD_DATA_FROM_MASTER.html ). This should do what you want - it does it for me. As I understand it, this puts a read lock on each table as it copies it. This provides some interruption to service, but nothing like a stop/start. |-+ | | Bogdan TARU | | | [EMAIL PROTECTED] | | || | | 05/08/2003 13:07 | | || |-+ --| | | | To: [EMAIL PROTECTED] | | cc: | | Subject: replication w/o stopping the master | --| Hi guys gals, Tried to get an answer through the manual, but couldn't find one. So, is there a way to do replication w/o stopping the master. The only way I found up until now was to stop the master (in order to insure there are no changes to the database), copy the database directory, start the master again with logging enabled (and delete the old logging files, if any), and copy the database directory on the slave. Of course, stopping the master isn't that much fun, esspecially when the databases are large and copying them takes some time. Is there a way to avoid it? I'm using 4.0.13. Thanks, bogdan -- 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]
Multiple table joins in a select
Ver. 3.23 How do I write a select query to join more than two tables? Table A relates to table B and table B relates to Table C. I need to return fields from Table A and C that are related.. Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load data infile issue
On 5 Aug 2003 at 9:37, Montagna, Dan wrote:
$result = mysql_db_query('$sql_id',DELETE FROM tablename) or die
(Invalid DELETE query);
It's helpful to print mysql_error() when you have an error, so you
get a message more specific than Invalid DELETE query. In this
case, you're trying to delete from a database called '$sql_id' --
that is, a dollar sign followed by 'sql_id'. Presumably that's not
your actual database name. Remove the single quotes (so that you're
using the *value* of $sql_id) and try again.
--
Keith C. Ivey [EMAIL PROTECTED]
Tobacco Documents Online
http://tobaccodocuments.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: win200 - lost root privilege - cannot shutdown - need help
thanks. I wanted a confirmation. I did it and moving on installing v4.0.14 -Original Message- From: Frank Tanner III [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 05, 2003 1:19 PM To: [EMAIL PROTECTED] Subject: Re: win200 - lost root privilege - cannot shutdown - need help Right click on the process for MySQL in the processes tab of Task Manager and select End Process. --- Christophe Poirier [EMAIL PROTECTED] wrote: I am working on a Windows 2000 server. The version 4.0.13 was installed at the end of June because we are looking at using MySQL. Me and the other guy are novice with MySQL. I read the different emails about access and granting and password and flushing privilege from other people who couldn't get access. To get to the point, I know that the guy has altered the privileges and lost the password he would have set for root. mysqld was started from DOS window using mysqld and the window was closed. When I go to Task Manager, I see the mysqld process running. When I use now mysqld, if I try to specify root, because of not knowing the root password, I can't get correct privilege to run the shutdown command. If I just type mysql, I am user [EMAIL PROTECTED] and the only database I see is test. Since mysql database is not visible, I know that I cannot set privilege from this user. So my goal is to desinstall v4.0.13, and re-install v4.0.14. But my current issue is how do I stop the mysqld process? Even if stopping from Task Manager is not the best approach, do I have another option? Please help. Christophe Poirier -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lowering the ft_min_word_len
At 10:30 -0700 8/5/03, Justin Hopper wrote:
Hello,
I have a table with a FULLTEXT index on a column of type 'text'.
Searches on this table using MATCH() AGAINST() work fine for most
words. However, I needed to match against a 3 letter word. So I
lowered the ft_min_word_len to 3 in /etc/my.cnf. I then restarted
MySQL. I checked that the variable was set to 3 in the running mysqld.
I don't see that you rebuilt your FULLTEXT indexes after restarting
the server. Did you?
But for some reason, I cannot fetch any results:
mysql select title_id from support_doc_articles where match(article)
against ('dns');
Empty set (0.00 sec)
It does not work IN BOOLEAN MODE either:
mysql select title_id from support_doc_articles where match(article)
against ('dns' IN BOOLEAN MODE);
Empty set (0.00 sec)
Actually, I just tried it again, searching for the 3 letter word 'key',
and it brought back results. Is 'dns' in the stopwords list? Is there
any way I can see what words are in there? Can I exclude words from the
stopword list without recompiling MySQL?
They're in the file myisam/ft_static.c in the source distribution. dns
is not one of them.
I don't believe you can exclude words from the list without recompiling.
Thanks for any help.
--
Justin Hopper
UNIX Systems Engineer
Spry Hosting
http://www.spry.com
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Library database help
Hi all. I am trying to build a small simple database for a lending library. Basically, I have a table of resources to be lended out, and a table that will store a user's name and what they have borrowed. My problem is that we may have more than one copy of a given resource say a book. So I thought I would just create each row in the resource table as an auto_increment integer and assign that the primary key... How can I make that auto_incrementing field the foreign key and a compiste primary key in the user table? Thanks, Taylor Please Note The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. Thank you *
load data infile problem
Hello,
I'm a very new mysql/php user and am trying to use the load data infile
command without luck. I'd like to replace a table using a text file but
can't get it to work. I set up a test table with no records using a telnet
console. I'm trying to write a php page that takes the file on the server
and adds the info into the table. Here's my code:
?php
# Connect to the database
$db = mysql_connect(server.net, username,password);
mysql_select_db(elasticnature,$db);
# Delete the current content of the table
$result = mysql_db_query('$sql_id',DELETE FROM tablename) or die (Invalid
DELETE query);
# Optimize the current table (recover empty space)
$result = mysql_db_query('$sql_id',OPTIMIZE TABLE tablename) or die
(Invalid OPTIMIZE query);
# Load local comma separated, fields enclosed by quotes text database - File
has to be in the same directory of this file
$result = mysql_db_query('$sql_id',LOAD DATA INFILE 'file.txt' INTO TABLE
tablename FIELDS TERMINATED BY ',' ENCLOSED BY '\') or die (Invalid DATA
LOAD query);
# Get how many records are present in the table now
$result = mysql_db_query('$sql_id',SELECT * from uploadtable) or die
(Invalid SELECT query);
$rows_count = mysql_num_rows($result);
echo Records: $rows_count; mysql_free_result($result);
?
I got this from an example given online and the output is the invalid
delete query. What's wrong with this code?? Thanks for any help...
Dan
column privilege problem--Solved
I solved the problem--I needed ( ) around the column list. I think the manual should make that more clear. Original Message Subject: column privilege problem Date: Tue, 05 Aug 2003 15:39:54 -0700 From: Tiffany Wilkes [EMAIL PROTECTED] To: [EMAIL PROTECTED] Hi, I am having problems granting column privileges. I want to grant the update privilege (only) to a column (called pass) in a table (called Acct). Here's what I get: mysql grant update pass on practicedb.Acct to [EMAIL PROTECTED]; ERROR 1064: You have an error in your SQL syntax near 'pass on practicedb.Acct to [EMAIL PROTECTED]' at line 1 I don't understand how the syntax is wrong. I think I followed the instructions in the manual. I don't have any problems granting table privileges--just column. Tiffany Landry -- 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: Load data infile issue
On 5 Aug 2003 at 11:16, Montagna,Dan [EMAIL PROTECTED] wrote: Thanks Keith, that got me past the first two queries but I'm now getting an Invalid DATA LOAD query . Someone told me that the load data infile option is not always on by default. Is there a piece of code I need to insert earlier in the file to turn on the load local file option? Invalid DATA LOAD query is the message you're printing in your code. You need to print mysql_error() to see the error message MySQL is giving you, which should help in figuring out what's going wrong. If I had to guess, I'd say the problem was with the location of the file (are you giving MySQL the full path?) or with permissions. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
storing large integers properly
I am storing book isbn numbers in a table. isbn numbers are 10 digit numbers and many start with 0. The data type of the field I am storing this info in is a bigint(16) unsigned. It appears that isbns that start with 0 are going in as 9 digit numbers, the 0 is being ignored or stripped. I have experienced this before with integer data types ignoring leading 0s. I'm wondering how to address this? Should I change the field to a varchar or char data type? Any advice is appreciated, thanks, Eben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: storing large integers properly
* Eben Goodman I am storing book isbn numbers in a table. isbn numbers are 10 digit numbers and many start with 0. The data type of the field I am storing this info in is a bigint(16) unsigned. Why not use BIGINT(10) UNSIGNED ZEROFILL? It appears that isbns that start with 0 are going in as 9 digit numbers, the 0 is being ignored or stripped. I have experienced this before with integer data types ignoring leading 0s. I'm wondering how to address this? Should I change the field to a varchar or char data type? Integers are numeric values, they don't have leading zeroes, but a _presentation_ of a number might have leading zeroes. Defining the field with ZEROFILL tells the mysql server to allways use leading zeroes on when _presenting_ this column. The actual integer is stored in a binary format, the same way as if you did not use ZEROFILL. You could of course change your column type to a string type, but you don't need to, unless 0123123123 and 123123123 are two different, valid ISBN numbers. If you can accept that 123123123 _is_ 0123123123 without the leading zero, go for ZEROFILL. It is faster to search on an indexed BIGINT compared to an indexed VARCHAR(10). URL: http://www.mysql.com/doc/en/Column_types.html ...and don't worry about leading zeroes in your SQL queries, input the ISBN number as a number, without quotes or leading zeroes. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: basic SQL question
How I can get the right one? Depending on your goal, which was not given here. I recall in my earlier career, in the computing center there was a banner saying: I wish they could sell this computer: It never does what I want it to do, only what I tell it to do. The result you got was intrinsic to your data. I'd suggest you take a closer look at your data, do a hand calculation for each step in your where-clause. Then you'd see the reason of the returned results, and perhaps find a way to achieve what you want. Best regards, Lin -Original Message- From: Gomez Fabre, Pedro Manuel [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 05, 2003 5:19 AM To: [EMAIL PROTECTED] Subject: basic SQL question Dear all, I have the following problem, I am trying to select records from two tables. the tables are constructed like: block block_id sequence_id snp_required first_polymorphism_index last_polymorphism_index first_reference_positio last_reference_position start_pos end_pos tiled_bp polymorphism_block_map block_id polymorphism_index polymorphism_id When I try to do: mysql select first_polymorphism_index,last_polymorphism_index from block where first_polymorphism_index like 14163 OR first_polymorphism_index like 14750 OR first_polymorphism_index like 14932; +--+-+ | first_polymorphism_index | last_polymorphism_index | +--+-+ |14163 | 14235 | |14750 | 14802 | |14932 | 14980 | +--+-+ 3 rows in set (0.02 sec) everything works fine, easy ;) but if I try to link these two tables I got: +--++-+- ++-+ | first_polymorphism_index | polymorphism_index | polymorphism_id | last_polymorphism_index | polymorphism_index | polymorphism_id | +--++-+- ++-+ |14163 | 14163 | P0216196 | 14235 | 14235 | P0216288| |14750 | 14750 | P0217007 | 14235 | 14235 | P0216288| |14932 | 14932 | P0217251 | 14235 | 14235 | P0216288| +--++-+- ++-+ 3 rows in set (1 min 27.48 sec) ## # this does not return the right values on the las polymorphism index ## SELECT b1.first_polymorphism_index, p1.polymorphism_index, p1.polymorphism_id, b2.last_polymorphism_index, p2.polymorphism_index, p2.polymorphism_id FROM block as b1, polymorphism_block_map as p1, block as b2, polymorphism_block_map as p2 WHERE b1.first_polymorphism_index = p1.polymorphism_index AND b2.last_polymorphism_index = p2.polymorphism_index LIMIT 5; But the second polymorphism_index is always the same, How I can get the right one? Thanks in advance P -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ON UPDATE CASCADE fails
Hello,
I have a table which has a foreign key relationship with itself. I
want and expect my updates to cascade (deletes definitely cascade as
expected) but instead I just get error 1217: foriegn key error.
I have written example code to use in reproducing the problem:
# Create the table
drop TABLE IF EXISTS person;
create table person (
name VARCHAR(50) NOT NULL,
parent VARCHAR(50) NOT NULL,
INDEX parent_ind(parent),
FOREIGN KEY(parent)
REFERENCES person(name) # this makes it so that when a page is deleted/updated, so
are all it's sub-pages
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (name)
) TYPE=INNODB;
# Insert some example data
INSERT INTO person (name,parent) VALUES ('Grandpa', 'Grandpa');
INSERT INTO person (name,parent) VALUES ('John', 'Grandpa');
INSERT INTO person (name,parent) VALUES ('Baby Bob', 'John');
mysql select * from person;
+--+-+
| name | parent |
+--+-+
| Grandpa | Grandpa |
| John | Grandpa |
| Baby Bob | John|
+--+-+
# Attempt the update (this fails with error 1217)
UPDATE person SET name='Mary' where name='John';
# I expect to end up with these values:
mysql select * from person;
+--+-+
| name | parent |
+--+-+
| Grandpa | Grandpa |
| Mary | Grandpa |
| Baby Bob | Mary|
+--+-+
I have verified that deletes cascade as expected.
After attempting the update query, SHOW INNODB STATUS reports the following:
LATEST FOREIGN KEY ERROR
030805 19:28:57 Transaction:
TRANSACTION 0 13261, ACTIVE 0 sec, OS thread id 664 updating or deleting, thread
declared inside InnoDB 499
3 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 307, query id 2804 localhost 127.0.0.1 root Updating
UPDATE person SET name='Mary' where name='John'
Foreign key constraint fails for table test/person: ,
CONSTRAINT `0_568` FOREIGN KEY (`parent`) REFERENCES `person` (`name`) ON DELETE
CASCADE ON UPDATE CASCADE
Trying an update, possibly causing a cyclic cascaded update
in the child table, in parent table, in index PRIMARY tuple:
0: len 4; hex 4a6f686e; asc John;; 1: len 6; hex 33cd; asc 3.;; 2:
len 7; hex 3700f4; asc 7.¶;; 3: len 7; hex 4772616e647061; asc Grandpa;;
But in child table test/person, in index parent_ind, there is a record:
RECORD: info bits 0 0: len 4; hex 4a6f686e; asc John;; 1: len 8; hex 4261627920426f62;
asc Baby Bob;;
I have verified that the problem is not related to the self-referential row at
the start by turning off foreign key checks, deleting that row, turning
foreign_key_checks back on and then attempting the update again. This still
produces error 1217.
Is this an official mysql bug, or am I missing something?
Any help is greatly appreciated,
--Fraser Hanson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql won't install (crashes) on Red Hat Enterprise Linux 3 Beta 1 (Taroon)
When installing the rpms (or the tar file) onto Red Hat Enteprise Linux AS Beta 1 (Taroon), we get the follwing error: Installing all prepared tables /usr/bin/mysql_install_db: line 1: 7690 Segmentation fault /usr/sbin/mysqld --bootstrap --skip-grant-tables --basedir=/ --datadir=/var/lib/mysql --skip-innodb --skip-bdb Installation of grant tables failed The log file simply shows a start and a stop of the server. How to repeat: 1) Load RH AS Beta 1: Installable binary ISO images, RPM packages, and source RPMs are available at: ftp://ftp.redhat.com/pub/redhat/linux/beta/taroon Or the following mirrors: North America: United States: ftp://ftp.cse.buffalo.edu/pub/RedHat/redhat/linux/beta/taroon/ ftp://kickstart.linux.ncsu.edu/pub/redhat/linux/beta ftp://ftp.oregonstate.edu/pub/ftp.redhat.com/beta/taroon Canada: ftp://ftp.nrc.ca/pub/systems/linux/redhat/ftp.redhat.com/linux/beta/taroon Europe: Czech Republic: ftp://sunsite.mff.cuni.cz/MIRRORS/ftp.redhat.com/redhat/linux/beta/taroon ftp://ultra.linux.cz/MIRRORS/ftp.redhat.com/redhat/linux/beta/taroon Germany: ftp://sunsite.informatik.rwth-aachen.de/pub/Linux/redhat/linux/beta/taroon http://sunsite.informatik.rwth-aachen.de/ftp/pub/Linux/redhat/linux/beta/taroon ftp://ftp.tu-chemnitz.de/pub/linux/redhat-ftp/redhat/linux/beta/taroon/ http://wftp.tu-chemnitz.de/pub/linux/redhat-ftp/redhat/linux/beta/taroon/ Netherlands: ftp://ftp.nluug.nl/pub/os/Linux/distr/RedHat/ftp/redhat/linux/beta/taroon/ ftp://ftp.surfnet.nl/pub/os/Linux/distr/RedHat/ftp/redhat/linux/beta/taroon/ Romania: ftp://ftp.iasi.roedu.net/pub/mirrors/ftp.redhat.com/pub/redhat/linux/beta/taroon / Russia: ftp://ftp.chg.ru/pub/Linux/redhat/linux/beta/taroon/ http://ftp.chg.ru/pub/Linux/redhat/linux/beta/taroon/ Switzerland: ftp://sunsite.cnlab-switch.ch/mirror/redhat/linux/beta/taroon/ Asia/Pacific: Australia: http://planetmirror.com/pub/redhat/linux/beta/taroon/ ftp://ftp.planetmirror.com/pub/redhat/linux/beta/taroon/ 2) rpm -Uvh MySQL-server-4.0.14-0.i386.rpm MySQL-client-4.0.14-0.i386.rpm 3) Boom! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
reindexing
I just did a major insert of new data and now all my selects have slowed down. The table is innodb. Is there a way to reindex everything without having to drop anything. Otherwise, I suppose I will have to drop the indexes and remake them. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ON UPDATE CASCADE fails
I forgot to mention that I am using mysql 4.0.14-nt on Windows 2000. --Fraser -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
