Does InnoDB use any of these variables?
Hi, Does InnoDB use any of these variables: bulk_insert_buffer_size join_buffer_size key_buffer_size read_buffer_size read_rnd_buffer_size sort_buffer_size table_cache thread_concurrency Thanks in advance, Mikhail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT only unique records
Sorry for hijacking this question, but while we are on this topic: How can one select unique rows based on a set of fields select distinct state_ID from financial_master where category_id='1' only returns rows based on the uniqueness of one field. what if there is another field that COMBINED with state_ID forms a unique row; how can one search for those? On Fri, 2003-09-12 at 20:13, Paul DuBois wrote: At 2:07 PM -0400 9/12/03, Dan Greene wrote: SELECT state_ID FROM financial_master WHERE category_ID = '1' becomes SELECT unique state_ID FROM financial_master WHERE category_ID = '1' -or- SELECT distinct state_ID FROM financial_master WHERE category_ID = '1' DISTINCT will work, but UNIQUE will not. DISTINCTROW is a synonym for DISTINCT. 2 points ! -Original Message- From: Comcast [mailto:[EMAIL PROTECTED] Sent: Friday, September 12, 2003 2:03 PM To: [EMAIL PROTECTED] Subject: SELECT only unique records I am sure this is a slam-dunk, but I am new to this and stumped ... thanks. I have the following statement, but I need it to pull only unique listings - I get repeated items. SELECT state_ID FROM financial_master WHERE category_ID = '1' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- 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]
Truncate do not reset auto increment counter
hello I once red that using truncate table xx instead of delete from xx should reset autoincrement , but it seem not to happend. but when I try to insert data with insert or load data in it remember the old autoincrement value. from manual : Truncate operations drop and re-create the table, which is much faster than deleting rows one by one. seems not , when I drop table and the create table again it works perfect. Mysql Linux 4.014 regards Kim G. Pedersen macaos/elprint Development +45 35373808 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Blasted #$%$^$^ host has v3.23 when i need UNION
Hi guys, First of all let me warn you, i am very much a newbie to mysql, i am pretty good with the basics like selecting,deleting,updateing etc but after that i get a dazed look in my eyes with complex sql :-D Now that you have been warned let me explain, on my local machine (win2k) i have php and mysql 4.0 installed for testing purposes, I have created a site locally and then uploaded it only to find out that because of UNION it wont run as my host is on version 3.23. I then searched google as i was pretty sure that i am not the only person who ran into this problem and found 2 places with some kind of explanation of which this seems to be the best: (the other one is the online manual) http://jinxidoru.com/tutorials/union.html but being a newbie and never having used join in my life, (at least not knowingly) this is @$#$%^$ confusing. Below is my union select statement, can somebody please show me how to convert it so it will work on 3x please? (This one is the actual php code i am using but if you dont understand it there is the normal sql below this one) $tt = SELECT COUNT(*), 'C1' FROM shared WHERE user ='.$mmmy_user.' and ccno=.$mmmy_ccno. UNION SELECT COUNT(*), 'C2' FROM dedicated where user='.$mmmy_user.' and ccno=.$mmmy_ccno. UNION SELECT COUNT(*), 'C3' FROM reseller WHERE user ='.$mmmy_user.' and ccno=.$mmmy_ccno. UNION SELECT COUNT(*), 'C4' FROM colocated WHERE user ='.$mmmy_user.' and ccno=.$mmmy_ccno. UNION SELECT COUNT(*), 'C5' FROM freehosting WHERE user ='.$mmmy_user.' and ccno=.$mmmy_ccno; // Normal version SELECT COUNT(*), 'C1' FROM shared WHERE user ='testing' and ccno=1 UNION SELECT COUNT(*), 'C2' FROM dedicated where user='testing' and ccno=1 UNION SELECT COUNT(*), 'C3' FROM reseller WHERE user ='testing' and ccno=1 UNION SELECT COUNT(*), 'C4' FROM colocated WHERE user ='testing' and ccno=1 UNION SELECT COUNT(*), 'C5' FROM freehosting WHERE user ='testing' and ccno=1; Thanks in advance and have a fantastic day, even though its monday... :-D Cheers, -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replace text in a field
Hi All MySQL experts. A while ago I asked how replace a bit of text in a field. The answer someone gave worked very well and quickly too. The field is ItemDescription with the properties set as mediumtext There is quite a bit of Text in each field (nearly 40,000 records) and the text is the same in each one. All I want to do is change one expression in every record? Can anyone remember this questionand know the answer? I lost my emails :( Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Blasted #$%$^$^ host has v3.23 when i need UNION
Hey, Thanks for replying. Nope, the whole reason for selecting the data is the count, i need to display to the client how many records of each category he has...if there is any other way to do that (me being a newbie to mysql) I would happy to know of it. Cheers, -Ryan Without 'count' function, you could have: SELECT user.C1, dedicated.C2, reseller.C3, colocated.C4, freehosting.C5 FROM user, dedicated, reseller, colocated , freehosting WHERE (user.user ='testing' and user.ccno=1) OR (dedicated.user ='testing' and dedicated.ccno=1) OR (reseller.user ='testing' and reseller.ccno=1) OR (colocated.user ='testing' and ccolocated.cno=1) OR (freehosting.user ='testing' and freehosting.ccno=1) OR Ryan A wrote: Hi guys, First of all let me warn you, i am very much a newbie to mysql, i am pretty good with the basics like selecting,deleting,updateing etc but after that i get a dazed look in my eyes with complex sql :-D Now that you have been warned let me explain, on my local machine (win2k) i have php and mysql 4.0 installed for testing purposes, I have created a site locally and then uploaded it only to find out that because of UNION it wont run as my host is on version 3.23. I then searched google as i was pretty sure that i am not the only person who ran into this problem and found 2 places with some kind of explanation of which this seems to be the best: (the other one is the online manual) http://jinxidoru.com/tutorials/union.html but being a newbie and never having used join in my life, (at least not knowingly) this is @$#$%^$ confusing. Below is my union select statement, can somebody please show me how to convert it so it will work on 3x please? (This one is the actual php code i am using but if you dont understand it there is the normal sql below this one) $tt = SELECT COUNT(*), 'C1' FROM shared WHERE user ='.$mmmy_user.' and ccno=.$mmmy_ccno. UNION SELECT COUNT(*), 'C2' FROM dedicated where user='.$mmmy_user.' and ccno=.$mmmy_ccno. UNION SELECT COUNT(*), 'C3' FROM reseller WHERE user ='.$mmmy_user.' and ccno=.$mmmy_ccno. UNION SELECT COUNT(*), 'C4' FROM colocated WHERE user ='.$mmmy_user.' and ccno=.$mmmy_ccno. UNION SELECT COUNT(*), 'C5' FROM freehosting WHERE user ='.$mmmy_user.' and ccno=.$mmmy_ccno; // Normal version SELECT COUNT(*), 'C1' FROM shared WHERE user ='testing' and ccno=1 UNION SELECT COUNT(*), 'C2' FROM dedicated where user='testing' and ccno=1 UNION SELECT COUNT(*), 'C3' FROM reseller WHERE user ='testing' and ccno=1 UNION SELECT COUNT(*), 'C4' FROM colocated WHERE user ='testing' and ccno=1 UNION SELECT COUNT(*), 'C5' FROM freehosting WHERE user ='testing' and ccno=1; Thanks in advance and have a fantastic day, even though its monday... :-D Cheers, -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Blasted #$%$^$^ host has v3.23 when i need UNION
Ryan, As you probably found out, union is only available in version 4 of mysql. As you're using PHP anyway, why don't you just break it up into 5 separate selects and combine the results in PHP? Andy -Original Message- From: Ryan A [mailto:[EMAIL PROTECTED] Sent: 15 September 2003 10:41 To: Subject: Blasted #$%$^$^ host has v3.23 when i need UNION Hi guys, First of all let me warn you, i am very much a newbie to mysql, i am pretty good with the basics like selecting,deleting,updateing etc but after that i get a dazed look in my eyes with complex sql :-D Now that you have been warned let me explain, on my local machine (win2k) i have php and mysql 4.0 installed for testing purposes, I have created a site locally and then uploaded it only to find out that because of UNION it wont run as my host is on version 3.23. I then searched google as i was pretty sure that i am not the only person who ran into this problem and found 2 places with some kind of explanation of which this seems to be the best: (the other one is the online manual) http://jinxidoru.com/tutorials/union.html but being a newbie and never having used join in my life, (at least not knowingly) this is @$#$%^$ confusing. Below is my union select statement, can somebody please show me how to convert it so it will work on 3x please? (This one is the actual php code i am using but if you dont understand it there is the normal sql below this one) $tt = SELECT COUNT(*), 'C1' FROM shared WHERE user ='.$mmmy_user.' and ccno=.$mmmy_ccno. UNION SELECT COUNT(*), 'C2' FROM dedicated where user='.$mmmy_user.' and ccno=.$mmmy_ccno. UNION SELECT COUNT(*), 'C3' FROM reseller WHERE user ='.$mmmy_user.' and ccno=.$mmmy_ccno. UNION SELECT COUNT(*), 'C4' FROM colocated WHERE user ='.$mmmy_user.' and ccno=.$mmmy_ccno. UNION SELECT COUNT(*), 'C5' FROM freehosting WHERE user ='.$mmmy_user.' and ccno=.$mmmy_ccno; // Normal version SELECT COUNT(*), 'C1' FROM shared WHERE user ='testing' and ccno=1 UNION SELECT COUNT(*), 'C2' FROM dedicated where user='testing' and ccno=1 UNION SELECT COUNT(*), 'C3' FROM reseller WHERE user ='testing' and ccno=1 UNION SELECT COUNT(*), 'C4' FROM colocated WHERE user ='testing' and ccno=1 UNION SELECT COUNT(*), 'C5' FROM freehosting WHERE user ='testing' and ccno=1; Thanks in advance and have a fantastic day, even though its monday... :-D Cheers, -Ryan -- 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: mysql 4.0.15: configure fails
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Fri, 12 Sep 2003, Michael Stassen wrote: When I downloaded it on August 30, it was called Dec2002gccUpdater.dmg, perhaps because it was an update to the Dec 2002 DevTools. That has disappeared and the August 2003 gcc Updater now sits in its place. Looking at the About file, they look mostly the same, except the August 2003 version has added the -fast flag to gcc for G4 and G5 optimization. OK, thanks for the info. Thanks for the hint - we did not test the Mac OS X build with gcc-3.3 yet. Your fix looks correct - we will update our configure script accordingly. Not sure if -traditional-cpp is required for Mac OS X at all - maybe I can simply remove it for darwin6 in general. You're welcome. My man gcc says: -traditional -traditional-cpp Formerly, these options caused GCC to attempt to emu- late a pre-standard C compiler. They are now only supported with the -E switch. The preprocessor con- tinues to support a pre-standard mode. See the GNU CPP manual for details. This isn't my area of expertise, but that doesn't look to me like something Mac OS X needs. I should point out, however, that I have not tried this with any previous version of OS X / DevTools, only with 10.2.6 and gcc 3.3. I tested it with gcc 2.95 and 3.1 - none of them required the flag. I now removed it from configure.in for future releases. Thanks for the hint! Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany For technical support contracts, visit https://order.mysql.com/?ref=mlgr -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE/ZZcVSVDhKrJykfIRAseAAJ4qWDuGC7T7l63VIS0GBOkVuHuIpwCfapnj Gz7ewA5SPhwCnZ3i8XZanA8= =1PkI -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Blasted #$%$^$^ host has v3.23 when i need UNION
Ryan, If this query worked, it would return you 5 rows, one for each separate count. If you execute 5 separate counts in PHP, you'll get 5 separate values with the same numbers as above. Not radically different? Andy -Original Message- From: Ryan A [mailto:[EMAIL PROTECTED] Sent: 15 September 2003 11:25 To: [EMAIL PROTECTED] Cc: Subject: Re: Blasted #$%$^$^ host has v3.23 when i need UNION Hey, Thanks for replying. Nope, the whole reason for selecting the data is the count, i need to display to the client how many records of each category he has...if there is any other way to do that (me being a newbie to mysql) I would happy to know of it. Cheers, -Ryan Without 'count' function, you could have: SELECT user.C1, dedicated.C2, reseller.C3, colocated.C4, freehosting.C5 FROM user, dedicated, reseller, colocated , freehosting WHERE (user.user ='testing' and user.ccno=1) OR (dedicated.user ='testing' and dedicated.ccno=1) OR (reseller.user ='testing' and reseller.ccno=1) OR (colocated.user ='testing' and ccolocated.cno=1) OR (freehosting.user ='testing' and freehosting.ccno=1) OR Ryan A wrote: Hi guys, First of all let me warn you, i am very much a newbie to mysql, i am pretty good with the basics like selecting,deleting,updateing etc but after that i get a dazed look in my eyes with complex sql :-D Now that you have been warned let me explain, on my local machine (win2k) i have php and mysql 4.0 installed for testing purposes, I have created a site locally and then uploaded it only to find out that because of UNION it wont run as my host is on version 3.23. I then searched google as i was pretty sure that i am not the only person who ran into this problem and found 2 places with some kind of explanation of which this seems to be the best: (the other one is the online manual) http://jinxidoru.com/tutorials/union.html but being a newbie and never having used join in my life, (at least not knowingly) this is @$#$%^$ confusing. Below is my union select statement, can somebody please show me how to convert it so it will work on 3x please? (This one is the actual php code i am using but if you dont understand it there is the normal sql below this one) $tt = SELECT COUNT(*), 'C1' FROM shared WHERE user ='.$mmmy_user.' and ccno=.$mmmy_ccno. UNION SELECT COUNT(*), 'C2' FROM dedicated where user='.$mmmy_user.' and ccno=.$mmmy_ccno. UNION SELECT COUNT(*), 'C3' FROM reseller WHERE user ='.$mmmy_user.' and ccno=.$mmmy_ccno. UNION SELECT COUNT(*), 'C4' FROM colocated WHERE user ='.$mmmy_user.' and ccno=.$mmmy_ccno. UNION SELECT COUNT(*), 'C5' FROM freehosting WHERE user ='.$mmmy_user.' and ccno=.$mmmy_ccno; // Normal version SELECT COUNT(*), 'C1' FROM shared WHERE user ='testing' and ccno=1 UNION SELECT COUNT(*), 'C2' FROM dedicated where user='testing' and ccno=1 UNION SELECT COUNT(*), 'C3' FROM reseller WHERE user ='testing' and ccno=1 UNION SELECT COUNT(*), 'C4' FROM colocated WHERE user ='testing' and ccno=1 UNION SELECT COUNT(*), 'C5' FROM freehosting WHERE user ='testing' and ccno=1; Thanks in advance and have a fantastic day, even though its monday... :-D Cheers, -Ryan -- 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: Truncate do not reset auto increment counter
Kim G. Pedersen [EMAIL PROTECTED] wrote: I once red that using truncate table xx instead of delete from xx should reset autoincrement , but it seem not to happend. but when I try to insert data with insert or load data in it remember the old autoincrement value. from manual : Truncate operations drop and re-create the table, which is much faster than deleting rows one by one. seems not , when I drop table and the create table again it works perfect. What table type do you use? TRUNCATE TABLE reset auto_increment value for MyISAM table, but not for InnoDB. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: mysqld not reading my.cnf
Randall Perry [EMAIL PROTECTED] wrote: Using the OS X binary dist of mysql 4.0.14. I put my.cnf in both the data dir and /etc, restarted the server, but it doesn't seem to be reading the config. I added these options to the my-large.cnf under [mysqld]: safe-show-database safe-user-create And tested with a user who had no privelages to the mysql db, but the db shows up with the SHOW DATABASES command. Does user have SHOW DATABASES privilege? Does mysqladmin variables show these options? If not, how can I tell what options are active? -- 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: REPLICATE_IGNORE_DB don't work :(
Lorenzo Sicilia [EMAIL PROTECTED] wrote: I am new member here. I have a question about REPLICATE_IGNORE_DB, I have two mysql server (4.0.15) with replication A-B-A All work fine Now, I want to ignore one database. I have try this command: CHANGE MASTER TO REPLICATE_IGNORE_DB = 'PMA'; ... but it don't work. 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 'REPLICATE_IGNORE_DB = ' At the following section you can find which variables you can use in the CHANGE MASTER TO command: http://www.mysql.com/doc/en/CHANGE_MASTER_TO.html I can excute this sql No. or I need set this param only in my.cnf? Yes. -- 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: joining 4 tables
Thank you all for replying. Brent your code is nice but I'ts not what I want. In where clause the equality is not true because its table has diferent cod fields anyway. Can you help me with that? - Original Message - From: Brent Baisley [EMAIL PROTECTED] To: $B-)*/2 (B $B#!32'2(B [EMAIL PROTECTED]) Cc: [EMAIL PROTECTED] Sent: Friday, September 12, 2003 3:43 PM Subject: Re: joining 4 tables SELECT table4.cod, table1.cod, table2.cod, table3.cod FROM table4 LEFT JOIN table1 ON table4.cod=table1.cod LEFT JOIN table1 ON table4.cod=table2.cod LEFT JOIN table1 ON table4.cod=table3.cod WHERE table1.cod IS NULL AND table2.cod IS NULL AND table3.cod IS NULL I think that should do it, I didn't test it. What you are doing is a left join for all the tables on table4. The left join will retain all the records of table4, and then you filter out any records that have no value in the fields for the other tables. On Friday, September 12, 2003, at 04:37 AM, $B-)*/2(B $B#!32'2(B wrote: Hello list I have a problem. I have 4 tables (in Mysql 3.23). All of them have a filed named cod. I want a query to find which cod of table1, table2, table3 is/are not in table4. cod values in table1, table2, table3 are diferent. Thank you Nikos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
Re: joining 4 tables
Hi, You need to use UNION, but it's not possible in version 3.23.* So it's better to upgrade MySQL server first. SELECT table1.cod FROM table1 LEFT JOIN table4 ON table1.cod=table4.cod WHERE table4.cod IS NULL UNION SELECT table2.cod FROM table2 LEFT JOIN table4 ON table2.cod=table4.cod WHERE table4.cod IS NULL UNION SELECT table3.cod FROM table3 LEFT JOIN table4 ON table3.cod=table4.cod WHERE table4.cod IS NULL Best regards, Mikhail. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Brent Baisley [EMAIL PROTECTED] Sent: Monday, September 15, 2003 1:15 PM Subject: Re: joining 4 tables Thank you all for replying. Brent your code is nice but I'ts not what I want. In where clause the equality is not true because its table has diferent cod fields anyway. Can you help me with that? - Original Message - From: Brent Baisley [EMAIL PROTECTED] To: $B-)*/2 (B $B#!32'2(B [EMAIL PROTECTED]) Cc: [EMAIL PROTECTED] Sent: Friday, September 12, 2003 3:43 PM Subject: Re: joining 4 tables SELECT table4.cod, table1.cod, table2.cod, table3.cod FROM table4 LEFT JOIN table1 ON table4.cod=table1.cod LEFT JOIN table1 ON table4.cod=table2.cod LEFT JOIN table1 ON table4.cod=table3.cod WHERE table1.cod IS NULL AND table2.cod IS NULL AND table3.cod IS NULL I think that should do it, I didn't test it. What you are doing is a left join for all the tables on table4. The left join will retain all the records of table4, and then you filter out any records that have no value in the fields for the other tables. On Friday, September 12, 2003, at 04:37 AM, $B-)*/2(B $B#!32'2(B wrote: Hello list I have a problem. I have 4 tables (in Mysql 3.23). All of them have a filed named cod. I want a query to find which cod of table1, table2, table3 is/are not in table4. cod values in table1, table2, table3 are diferent. Thank you Nikos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Numeric Types and display attribute
Dwight Trumbower [EMAIL PROTECTED] wrote: I'm sure this has been discussed before, I just haven't found it. When creating numeric data types you have the option of putting a display attribute. After reading the docs, I'm not really sure why you would want to do this. Display size doesn't affect on the numeric types, only on the string types. From a DBA point of view, I don't care how the number is displayed. That is up to the ui designer. Is there any performance gain to using the display attribute? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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]
random record
hi, why its mysql database over random record use?
random record
hi, why its mysql database over random record use?
Re: Blasted #$%$^$^ host has v3.23 when i need UNION
You may also want to look at http://www.php.net/manual/en/function.mysql-num-rows.php Ryan A wrote: Hey, Thanks for replying. Nope, the whole reason for selecting the data is the count, i need to display to the client how many records of each category he has...if there is any other way to do that (me being a newbie to mysql) I would happy to know of it. Cheers, -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Blasted #$%$^$^ host has v3.23 when i need UNION
Hey Andy, True, but thats using 5 selects instead of just one, and since we are expecting quite a bit of traffic to the site that can add up pretty fast, expecially since we cant afford to have a dedicated server but are on a shared hosting package. If we have no other alternative we will be going with the 5 selects but since there seems to be a join alternative was hoping someone could help me out. Thanks anyway. Cheers, -Ryan Ryan, If this query worked, it would return you 5 rows, one for each separate count. If you execute 5 separate counts in PHP, you'll get 5 separate values with the same numbers as above. Not radically different? Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with mysql.sock
Hi I need to know how and where to tell mysql to look for mysql.sock. I am using mysql version 3.23.55 and SUSE Linux 8.2. My problem is that i have a mysql daemon working very well and using /var/lib/mysql/mysql.sock as the correct socket. No problems. My problems come when I try and install the perl msql-mysql modules. It asks for my mysql directory, which is /usr/local/mysql/. I can only assume that it then tries to execute /usr/local/mysql/bin/mysql, and guess what? For some reason it tries to connect through the default /tmp/mysql.sock, which doesn't exist and everything fails. SO whats going on? Why, when i execute /usr/bin/mysql does mysql look to /var/lib/mysql/mysql.sock and when I execute /usr/local/mysql/bin/mysql, does it look to /tmp/mysql.sock? How can I set BOTH to look at /var/lib/mysql/mysql.sock? I tried mysql_config --socket /var/lib/mysql/mysql.sock, but all that does is repeat the usage message for mysql_config. Help me, I am confused! Thanks Mick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Blasted #$%$^$^ host has v3.23 when i need UNION
Ryan, You might well find that the 5 separate counts are quicker than the join approach. Mysql is pretty efficient at counts on indexed columns from a single table. My instincts suggest that the four table join you are proposing could be slower than the 5 separate counts, especially if the tables have thousands of rows. As long as you use the same database connection, there's shouldn't be much extra network overhead either. I may be wrong, but I suspect you're worrying unnecessarily, unless the database server is connected to the web server via a particularly slow network. Make sure you've got the right indexes on all of the tables though (ie put an index on each table that matches the where clause against that table). All the best, Andy -Original Message- From: Ryan A [mailto:[EMAIL PROTECTED] Sent: 15 September 2003 13:31 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Blasted #$%$^$^ host has v3.23 when i need UNION Hey Andy, True, but thats using 5 selects instead of just one, and since we are expecting quite a bit of traffic to the site that can add up pretty fast, expecially since we cant afford to have a dedicated server but are on a shared hosting package. If we have no other alternative we will be going with the 5 selects but since there seems to be a join alternative was hoping someone could help me out. Thanks anyway. Cheers, -Ryan Ryan, If this query worked, it would return you 5 rows, one for each separate count. If you execute 5 separate counts in PHP, you'll get 5 separate values with the same numbers as above. Not radically different? Andy -- 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]
Joining tables from two different databases
I have an existing database with a lot of information, I need to create a new database to record inventory information that pertains to records in the first database. I'd like to keep these two database's separate. Is it possible to relate a record in one database to a record in another and do queries that pull from both databases? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: random record
39? -Original Message- From: tuncay bas [mailto:[EMAIL PROTECTED] Sent: 15 September 2003 13:32 To: mysql Subject: random record hi, why its mysql database over random record use? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: {Scanned} RE: random record
pls unsubscribe me from this maillist. thanks.
- Original Message -
From: Andy Eastham [EMAIL PROTECTED]
To: Mysql List [EMAIL PROTECTED]
Sent: Monday, September 15, 2003 8:54 PM
Subject: {Scanned} RE: random record
39?
-Original Message-
From: tuncay bas [mailto:[EMAIL PROTECTED]
Sent: 15 September 2003 13:32
To: mysql
Subject: random record
hi,
why its mysql database over random record use?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
random data
excuse me have can I get from mysql database random 6 data item?
Re: Joining tables from two different databases
Jeff McKeon wrote: I have an existing database with a lot of information, I need to create a new database to record inventory information that pertains to records in the first database. I'd like to keep these two database's separate. Is it possible to relate a record in one database to a record in another and do queries that pull from both databases? Thanks, Jeff Hi, Yes you can do that. Just prefix table name with database name in your query: select * from table1,database2.table2 where table1.field1=database2.table2.field2 Hope this helps, Joseph Bueno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: more on don't work.
Is the server running? SWIT wrote: badboy# ./mysql -u root -p Enter password: ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' ( 2) that file is not there. should I touch it ? argggh ! and ya say windows sucks. (ok the beer is talking now) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Q: 3rd party ADO access to Mysql?
Has anyone ever used a non-ODBC, 3rd party ADO connector to MySQL? Any suggestions would be greatly appreciated. My client does not wish to use ODBC. /tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joining tables from two different databases
I do it everyday, they are on the same machine and installation, but most of my queries span 2 to 4 databases. I can't get it it work on ODBC queries though, but PHP does it fine and so does the mysql command line. But I would be interested to know if there is any problems or reason not to do this. It seems to be the way to bring more organization to the db layout from my perspective. KL Jeff McKeon wrote: I have an existing database with a lot of information, I need to create a new database to record inventory information that pertains to records in the first database. I'd like to keep these two database's separate. Is it possible to relate a record in one database to a record in another and do queries that pull from both databases? Thanks, Jeff -- 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: Joining tables from two different databases
In a message dated 9/15/03 9:50:22 AM Eastern Daylight Time, [EMAIL PROTECTED] writes: Jeff McKeon wrote: Is it possible to relate a record in one database to a record in another and do queries that pull from both databases? Programmatically yes. Using referential integrity (foreign keys), I doubt it. :-( /tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT only unique records
Petre Agenbag wrote: How can one select unique rows based on a set of fields select distinct state_ID from financial_master where category_id='1' only returns rows based on the uniqueness of one field. what if there is another field that COMBINED with state_ID forms a unique row; how can one search for those? Select distinct on both: select distinct stateID, cityID ... Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Joining tables from two different databases
That's what I thought. Thanks for the advise!! :o) Jeff -Original Message- From: Joseph Bueno [mailto:[EMAIL PROTECTED] Sent: Monday, September 15, 2003 9:26 AM To: Jeff McKeon Cc: [EMAIL PROTECTED] Subject: Re: Joining tables from two different databases Jeff McKeon wrote: I have an existing database with a lot of information, I need to create a new database to record inventory information that pertains to records in the first database. I'd like to keep these two database's separate. Is it possible to relate a record in one database to a record in another and do queries that pull from both databases? Thanks, Jeff Hi, Yes you can do that. Just prefix table name with database name in your query: select * from table1,database2.table2 where table1.field1=database2.table2.field2 Hope this helps, Joseph Bueno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OS X Installation and Setup
Are you trying to run mysqld or mysqld_safe (or safe_mysqld)? If you are going to start mysql manually, you should be in the /usr/local/mysql directory and then type the following: sudo ./bin/mysqld_safe mysqld_safe is actually just a shell script that will launch and monitor mysqld to make sure it stays running. mysqld needs to run under the mysql account (or whatever account you created for it to run under). The script handles launching mysqld under the correct account. That's why you need to use sudo, so it can switch accounts from root to run the process. The at the end just tells the scripts to run in the background so that you get control of the terminal back and can even close your terminal session without causing the script to exit. The only time I ever try to run mysqld directly is if I'm having trouble getting things to launch, because then it will show the errors in the terminal session instead of logging them. But you do need to be one level up other mysqld won't be able to figure out the paths to all the supporting files. sudo ./bin/mysqld Should tell you what the problem is. There is a lot of great stuff on OS X on Marc Liyanage's website. He even has instructions on how to configure you computer to launch MySQL at startup. http://www.entropy.ch/software/macosx/ On Saturday, September 13, 2003, at 02:54 AM, Andy Callan wrote: Does the installation of the PKG itself finish successful? No problems installing the files, the PKG works fine and everything is as it should be in /usr/local/ It's essential to start up mysqld before you continue with using mysqladmin. When i try to startup the mysqld it works but then I get msqld ended immediately afterwards, I tried to follow the two posts about that with the online documentation with no luck. If you need anymore info just let me know, thanks a lot for your response. -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OS X Installation and Setup
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Mon, 15 Sep 2003, Brent Baisley wrote: Are you trying to run mysqld or mysqld_safe (or safe_mysqld)? If you are going to start mysql manually, you should be in the /usr/local/mysql directory and then type the following: sudo ./bin/mysqld_safe Correct. There is a lot of great stuff on OS X on Marc Liyanage's website. He even has instructions on how to configure you computer to launch MySQL at startup. http://www.entropy.ch/software/macosx/ Actually, starting with MySQL 4.0.15 you can simply use the StartupItem that is bundled with the MySQL PKG in the Disk Image! Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany For technical support contracts, visit https://order.mysql.com/?ref=mlgr -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE/Zc85SVDhKrJykfIRAqQDAJ9P1JE6+FUjS2B6jPXUstgUYwjb2wCdHAuq uk6SrLqUhm1fqTxk++eR3gg= =gPqq -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Does NULL == ?
The MySQL documentation confuses me a bit. If I create a table with property NOT NULL and default , does that mean that a record with a column so defined will have a zero-length string in that column if I don't provide a value? I guess what I'm asking is whether there's a difference between a field with NULL in it and a field with a zero-length () string in it. Thanks. Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does NULL == ?
No, NULL is not the same as the empty string. NULL, for any class of field, means that no data has ever been written there. Tests involving NULL other than IS NULL and IS NOT NULL will return NULL. Thus a returns 1 (true) whereas NULL a returns NULL, which will always be regarded as a miss in any select. |-+ | | Randy Chrismon | | | [EMAIL PROTECTED]| | | ia.net | | || | | 15/09/2003 16:00 | | || |-+ --| | | | To: [EMAIL PROTECTED] | | cc: | | Subject: Does NULL == ? | --| The MySQL documentation confuses me a bit. If I create a table with property NOT NULL and default , does that mean that a record with a column so defined will have a zero-length string in that column if I don't provide a value? I guess what I'm asking is whether there's a difference between a field with NULL in it and a field with a zero-length () string in it. Thanks. Randy -- 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]
NULL
Randy- NULL means No Data So in the case of a MySQL Column defined as Type String NULL is equivalent to Best Regards, Marty Gainty
Re: Does NULL == ?
Randy Chrismon wrote: what I'm asking is whether there's a difference between a field with NULL in it and a field with a zero-length () string in it. Yes, there is. NULL is the absence of a value; an empty string is a valid value. NULL by definition isn't equal to anything, even to itself, while = . Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with CHARACTER SET xxx COLLATE yyy
I'm trying to create a table in my database with CHARACTER SET utf8 COLLATE COLLATE utf8_general_ci Answer is ERROR 1115: Unknown character set: 'utf8_general_ci' What am I doing wrong, if anything? Please find details on my server and the table I'm trying to create below: mysql select version(); +-+ | version() | +-+ | 4.1.0-alpha-Max-log | +-+ The SQL statement I'm trying to execute is: CREATE TABLE blahagent ( id VARCHAR(100) CHARACTER SET latin1 DEFAULT ''NOT NULL, last_name VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' NOT NULL, first_name VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY(id) ); Yours Sigfrid -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Does NULL == ?
Null is a special value and cannot be tested in the same manner as a string
or other value. Yes, there is a difference. If the column is Null, a
comparison operation such as a.field == or a.field == something will
both return Null.
Maybe this document will help:
A.5.3 Problems with NULL Values
The concept of the NULL value is a common source of confusion for newcomers
to SQL, who often think that NULL is the same thing as an empty string .
This is not the case! For example, the following statements are completely
different:
mysql INSERT INTO my_table (phone) VALUES (NULL);
mysql INSERT INTO my_table (phone) VALUES ();
Both statements insert a value into the phone column, but the first inserts
a NULL value and the second inserts an empty string. The meaning of the
first can be regarded as ``phone number is not known'' and the meaning of
the second can be regarded as ``she has no phone''.
In SQL, the NULL value is always false in comparison to any other value,
even NULL. An expression that contains NULL always produces a NULL value
unless otherwise indicated in the documentation for the operators and
functions involved in the expression. All columns in the following example
return NULL:
mysql SELECT NULL,1+NULL,CONCAT('Invisible',NULL);
If you want to search for column values that are NULL, you cannot use the
=NULL test. The following statement returns no rows, because expr = NULL is
FALSE, for any expression:
mysql SELECT * FROM my_table WHERE phone = NULL;
To look for NULL values, you must use the IS NULL test. The following shows
how to find the NULL phone number and the empty phone number:
mysql SELECT * FROM my_table WHERE phone IS NULL;
mysql SELECT * FROM my_table WHERE phone = ;
Note that you can only add an index on a column that can have NULL values if
you are using MySQL Version 3.23.2 or newer and are using the MyISAM or
InnoDB table type. In earlier versions and with other table types, you must
declare such columns NOT NULL. This also means you cannot then insert NULL
into an indexed column.
When reading data with LOAD DATA INFILE, empty columns are updated with ''.
If you want a NULL value in a column, you should use \N in the text file.
The literal word 'NULL' may also be used under some circumstances. See
section 6.4.9 LOAD DATA INFILE Syntax.
When using ORDER BY, NULL values are presented first. If you sort in
descending order using DESC, NULL values are presented last. When using
GROUP BY, all NULL values are regarded as equal.
To help with NULL handling, you can use the IS NULL and IS NOT NULL
operators and the IFNULL() function.
For some column types, NULL values are handled specially. If you insert NULL
into the first TIMESTAMP column of a table, the current date and time is
inserted. If you insert NULL into an AUTO_INCREMENT column, the next number
in the sequence is inserted.
-Original Message-
From: Randy Chrismon [mailto:[EMAIL PROTECTED]
Sent: Monday, September 15, 2003 11:00
To: [EMAIL PROTECTED]
Subject: Does NULL == ?
The MySQL documentation confuses me a bit. If I create a table with
property NOT NULL and default , does that mean that a record with a
column so defined will have a zero-length string in that column if I
don't provide a value? I guess what I'm asking is whether there's a
difference between a field with NULL in it and a field with a
zero-length () string in it.
Thanks.
Randy
--
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: Does NULL == ?
I ran into the issue where data had been written, and then a Null was written. The presence of Null in a field may not in fact guarantee that data has never been written. -m- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, September 15, 2003 11:12 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Does NULL == ? No, NULL is not the same as the empty string. NULL, for any class of field, means that no data has ever been written there. Tests involving NULL other than IS NULL and IS NOT NULL will return NULL. Thus a returns 1 (true) whereas NULL a returns NULL, which will always be regarded as a miss in any select. |-+ | | Randy Chrismon | | | [EMAIL PROTECTED]| | | ia.net | | || | | 15/09/2003 16:00 | | || |-+ --- ---| | | | To: [EMAIL PROTECTED] | | cc: | | Subject: Does NULL == ? | --- ---| The MySQL documentation confuses me a bit. If I create a table with property NOT NULL and default , does that mean that a record with a column so defined will have a zero-length string in that column if I don't provide a value? I guess what I'm asking is whether there's a difference between a field with NULL in it and a field with a zero-length () string in it. Thanks. Randy -- 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does NULL == ?
This is sometime a tough concept to get through. For example, there is more than two answers to a yes and no question. There is yes, no, I don't know (the empty set) and the No answer at all (null). It's almost useless to specify a property as NOT NULL and also set a default value. The only way it would ever be NULL is if you specifically set it to NULL. If you see an empty field in a database, you have to wonder if someone forgot to enter a value or if there was no value to enter. The difference between NULL (no value entered) and (no value to enter). On Monday, September 15, 2003, at 11:00 AM, Randy Chrismon wrote: The MySQL documentation confuses me a bit. If I create a table with property NOT NULL and default , does that mean that a record with a column so defined will have a zero-length string in that column if I don't provide a value? I guess what I'm asking is whether there's a difference between a field with NULL in it and a field with a zero-length () string in it. -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Querys Dropped/Connections Lost
I don't seem to have a my.cnf file. Maybe I should create one. Dan Can you send you're my.cnf options in /etc? You might have wait-timeout set. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Daniel Baughman [mailto:[EMAIL PROTECTED] --Sent: Friday, September 12, 2003 4:21 PM --To: [EMAIL PROTECTED] --Subject: Querys Dropped/Connections Lost -- --I have a mysql server running on a dual processor pentium 233 (a real --power --house :) ). And I have serveral php web sites that access it locally. --One --of about 15 reads results in the following error being generated: -- -- --Error -- --MySQL said: -- --Lost connection to MySQL server during query -- -- --Anyone got a fix or have heard of that before? -- -- --Daniel Baughman -- -- --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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does NULL == ?
At 11:00 AM -0400 9/15/03, Randy Chrismon wrote: The MySQL documentation confuses me a bit. If I create a table with property NOT NULL and default , does that mean that a record with a column so defined will have a zero-length string in that column if I don't provide a value? I guess what I'm asking is whether there's a difference between a field with NULL in it and a field with a zero-length () string in it. Other responses have given a lot of informatin that I'll not repeat here, but you can test whether the two values are equal like this: mysql SELECT IS NULL; ++ | IS NULL | ++ | 0 | ++ Guess not. :-) Thanks. Randy -- 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]
Re: NULL
At 11:12 AM -0400 9/15/03, Martin Gainty wrote: Randy- NULL means No Data So in the case of a MySQL Column defined as Type String NULL is equivalent to Actually, it's not. isn't No Data, it's Data with a length of zero. -- 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]
Re: Problem with CHARACTER SET xxx COLLATE yyy
At 5:23 PM +0200 9/15/03, Sigfrid Lundberg - LUB NetLab wrote: I'm trying to create a table in my database with CHARACTER SET utf8 COLLATE COLLATE utf8_general_ci Answer is ERROR 1115: Unknown character set: 'utf8_general_ci' Try it again when 4.1.1 comes out. There is a bunch of character set renaming that went on between 4.1.0 and 4.1.1. Also, COLLATE need be specified only once. :-) I assume the double COLLATE above is just a typo? What you're seeing is one of the things referred to by: http://www.mysql.com/doc/en/Charset.html which says: The features described here are as implemented in MySQL 4.1.1. (MySQL 4.1.0 has some but not all of these features, and some of them are implemented differently.) What am I doing wrong, if anything? Please find details on my server and the table I'm trying to create below: mysql select version(); +-+ | version() | +-+ | 4.1.0-alpha-Max-log | +-+ The SQL statement I'm trying to execute is: CREATE TABLE blahagent ( id VARCHAR(100) CHARACTER SET latin1 DEFAULT '' NOT NULL, last_name VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' NOT NULL, first_name VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY(id) ); Yours Sigfrid -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- 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]
Select distinct speed on an indexed column
Hey everyone, I have a question about the speed of selecting distinct values on an indexed column. I have a table with a five column primary key and 3,215,540 records. I want to select all of the distinct values of the first column in the primary key. This column only has 549 distinct values. To execute this query takes about a minute and a half on a P4 2.4G. I assume that mysql is doing a complete table scan thus making the query slow. My question follows. Shouldn't the distinct values of the first column in an index be pulled from the index itself instead from the actual data? Thanks for your insights! Nathan show table status; ---+-+++ ---+ | Name | Type | Row_format | Rows| Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | ++++--++ -+-+--+---++ --- | PostedZpdi | InnoDB | Dynamic| 3215540 |678 | 2180988928 |NULL |530350080 | 0 | NULL | NULL | NULL | NULL || InnoDB free: 142368768 kB | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FOREIGN KEY() REFERENCES ON UPDATE CASCADE ON DELETE RESTRICT
Dear Programmers, At the end of this query, I make a select * from the table product_order, Which happens to be empty, Why ? Is something wrong with my insert statements please ? I inserted something into the tables CUSTOMER and PRODUCT, and I expected it to appear into the table PRODUCT_ORDER which is some kind of relationship between the two entity types CUSTOMER and PRODUCT. What do I please have to insert in order to achieve some evidence for the existence of referential integrity ? please ? ON UPDATE CASCADE I feel means something like that the actual data is propagated due to the references ? Yours Sincerely Morten Gulbrandsen USE test; DROP TABLE IF EXISTS PRODUCT_ORDER, CUSTOMER, PRODUCT; CREATE TABLE PRODUCT ( category INT NOT NULL, id INT NOT NULL, priceDECIMAL(1,2), PRIMARY KEY(category, id) )TYPE=INNODB; CREATE TABLE CUSTOMER ( id INT NOT NULL, PRIMARY KEY (id) )TYPE=INNODB; CREATE TABLE PRODUCT_ORDER ( noINT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_idINT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customer(id) )TYPE=INNODB; INSERT INTOPRODUCT(category, id, price) VALUES(1, 1, 0.1 ); INSERT INTOCUSTOMER(id) VALUES (2); SELECT * FROM PRODUCT; SELECT * FROM CUSTOMER; SELECT * FROM PRODUCT_ORDER; C:\mysql\binmysql -u sampadm -psecret -vvv Foreign_Key_02.sql out.txt -- DROP TABLE IF EXISTS PRODUCT_ORDER, CUSTOMER, PRODUCT -- Query OK, 0 rows affected (0.02 sec) -- CREATE TABLE PRODUCT ( category INT NOT NULL, id INT NOT NULL, priceDECIMAL(1,2), PRIMARY KEY(category, id) ) TYPE=INNODB -- Query OK, 0 rows affected (0.00 sec) -- CREATE TABLE CUSTOMER ( id INT NOT NULL, PRIMARY KEY (id) ) TYPE=INNODB -- Query OK, 0 rows affected (0.00 sec) -- CREATE TABLE PRODUCT_ORDER ( noINT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_idINT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customer(id) ) TYPE=INNODB -- Query OK, 0 rows affected (0.02 sec) -- INSERT INTOPRODUCT(category, id, price) VALUES(1, 1, 0.1 ) -- Query OK, 1 row affected (0.00 sec) -- INSERT INTOCUSTOMER(id) VALUES (2) -- Query OK, 1 row affected (0.00 sec) -- SELECT * FROM PRODUCT -- +--++---+ | category | id | price | +--++---+ |1 | 1 | 0.10 | +--++---+ 1 row in set (0.00 sec) -- SELECT * FROM CUSTOMER -- ++ | id | ++ | 2 | ++ 1 row in set (0.00 sec) -- SELECT * FROM PRODUCT_ORDER -- Empty set (0.00 sec) Bye -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select distinct speed on an indexed column
Nathan Cassano wrote: Hey everyone, I have a question about the speed of selecting distinct values on an indexed column. I have a table with a five column primary key and 3,215,540 records. I want to select all of the distinct values of the first column in the primary key. This column only has 549 distinct values. To execute this query takes about a minute and a half on a P4 2.4G. I assume that mysql is doing a complete table scan thus making the query slow. My question follows. Shouldn't the distinct values of the first column in an index be pulled from the index itself instead from the actual data? Thanks for your insights! Nathan show table status; ---+-+++ ---+ | Name | Type | Row_format | Rows| Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | ++++--++ -+-+--+---++ --- | PostedZpdi | InnoDB | Dynamic| 3215540 |678 | 2180988928 |NULL |530350080 | 0 | NULL | NULL | NULL | NULL || InnoDB free: 142368768 kB | Hi, What do you get when you do an EXPLAIN of your query ? -- Joseph Bueno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select distinct speed on an indexed column
Its a compound key, they are always slow. I would imagin you will need to seriously redesign your database to speed that up. I'm not 100% sure how the index is stored, but it would be some what pointless if it was individual field values. Its like haveing field1field2feild3field4field5 so that a single comparison of the values tells you if its unique. You might be able to speed it up by putting a secondary index on the first field I'm curious as to why any one would design a database with that many feilds in the primary key? Maybe its me but that would just be wrong. I'm not to happy when I have 2 fields in the primary key - Original Message - From: Nathan Cassano [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 15, 2003 5:07 PM Subject: Select distinct speed on an indexed column : : Hey everyone, : I have a question about the speed of selecting distinct values on an : indexed column. I have a table with a five column primary key and 3,215,540 : records. I want to select all of the distinct values of the first column in : the primary key. This column only has 549 distinct values. To execute this : query takes about a minute and a half on a P4 2.4G. I assume that mysql is : doing a complete table scan thus making the query slow. : My question follows. Shouldn't the distinct values of the first : column in an index be pulled from the index itself instead from the actual : data? Thanks for your insights! : : Nathan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select distinct speed on an indexed column
mysql explain select distinct AccountLevelId from PostedZpdi; ++---+---+-+-+--+-+- + | table | type | possible_keys | key | key_len | ref | rows| Extra | ++---+---+-+-+--+-+- + | PostedZpdi | index | NULL | PRIMARY | 60 | NULL | 3447290 | Using index | ++---+---+-+-+--+-+- + 1 row in set (0.01 sec) Hi, What do you get when you do an EXPLAIN of your query ? -- Joseph Bueno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NULL
Ye, NULL = Undefined in every database I've ever used. Null and are not equal, NULL and NULL are not equal eigther. Infact absolutly nothing is ever equal to NULL. Haydies. Database/PHP Developer - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Martin Gainty [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, September 15, 2003 5:02 PM Subject: Re: NULL : At 11:12 AM -0400 9/15/03, Martin Gainty wrote: : Randy- : : NULL means No Data : So in the case of a MySQL Column defined as Type String : NULL is equivalent to : : Actually, it's not. : : isn't No Data, it's Data with a length of zero. : : -- : Paul DuBois, Senior Technical Writer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does NULL == ?
On 15 Sep 2003 at 11:47, Brent Baisley wrote: It's almost useless to specify a property as NOT NULL and also set a default value. The only way it would ever be NULL is if you specifically set it to NULL. I think you meant NULL rather than NOT NULL there. -- 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: NULL
At 5:21 PM +0100 9/15/03, Haydies wrote: Ye, NULL = Undefined in every database I've ever used. Null and are not equal, NULL and NULL are not equal eigther. Infact absolutly nothing is ever equal to NULL. One slight exception is that for purposes of ORDER BY, GROUP BY, and DISTINCT, NULL values are considered the same in the sense that they group together. (It's difficult to see what other way of handling them would make sense for such operations.) Haydies. Database/PHP Developer - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Martin Gainty [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, September 15, 2003 5:02 PM Subject: Re: NULL : At 11:12 AM -0400 9/15/03, Martin Gainty wrote: : Randy- : : NULL means No Data : So in the case of a MySQL Column defined as Type String : NULL is equivalent to : : Actually, it's not. : : isn't No Data, it's Data with a length of zero. : : -- : Paul DuBois, Senior Technical Writer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- 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]
Re: Select distinct speed on an indexed column
Since your primary key is used, maybe you should consider adding an index on PostedZpdi field only. You may also check 'key_buffer_size' value (show variables like 'key%'). Since your primary key is more than 200 MB big, allocating a big key buffer ( 256MB) may help. Joseph Bueno Nathan Cassano wrote: mysql explain select distinct AccountLevelId from PostedZpdi; ++---+---+-+-+--+-+- + | table | type | possible_keys | key | key_len | ref | rows| Extra | ++---+---+-+-+--+-+- + | PostedZpdi | index | NULL | PRIMARY | 60 | NULL | 3447290 | Using index | ++---+---+-+-+--+-+- + 1 row in set (0.01 sec) Hi, What do you get when you do an EXPLAIN of your query ? -- Joseph Bueno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does Null == ?
Null is a special value and cannot be tested in the same manner as a string or other value. Yes, there is a difference. If the column is Null, a comparison operation such as a.field == or a.field == something will both return Null. Maybe this document will help: A.5.3 Problems with NULL Values --SNIP lots of good info-- Thanks for the info. Actually, I hadn't gotten as far as thinking about testing. I am at the point of designing some tables to receive an exort from a Lotus Notes database. At some point, the MySQL documentation says that a table with no nullable columns is better/faster than one with. The Lotus Notes database I'm migrating, however, has many fields with no values. I infer from the MySQL documentation that I'm better off doing: create table my_table(a_field varchar(16) NOT NULL default , ...) and exporting my values from Notes as rather than using NULL. Thanks. Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 3.23.58 has been released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL 3.23.58, a new version of the popular Open Source/Free Software Database, has been released. It is now available in source and binary form for a number of platforms from our download pages at http://www.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is a bugfix release for the recent production version. It includes a fix for a potential local security vulnerability which has already been applied to MySQL 4.0.15 as well. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. News from the ChangeLog: * Fixed buffer overflow in password handling which could potentially be exploited by MySQL users with `ALTER' privilege on the `mysql.user' table to execute random code or to gain shell access with the UID of the mysqld process (thanks to Jedi/Sector One for spotting and reporting this bug). * `mysqldump' now correctly quotes all identifiers when communicating with the server. This assures that during the dump process, `mysqldump' will never send queries to the server that result in a syntax error. This problem is *not* related to the `mysqldump' program's output, which was not changed. (Bug #1148) * Fixed table/column grant handling - proper sort order (from most specific to less specific, *note Request access::) was not honored. (Bug #928) * Fixed overflow bug in `MyISAM' and `ISAM' when a row is updated in a table with a large number of columns and at least one `BLOB/TEXT' column. * Fixed MySQL so that field length (in C API) for the second column in `SHOW CREATE TABLE' is always larger than the data length. The only known application that was affected by the old behaviour was Borland dbExpress, which truncated the output from the command. (Bug #1064) * Fixed `ISAM' bug in `MAX()' optimisation. * Fixed `Unknown error' when doing `ORDER BY' on reference table which was used with `NULL' value on `NOT NULL' column. (Bug #479) Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany For technical support contracts, visit https://order.mysql.com/?ref=mlgr -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE/Ze5NSVDhKrJykfIRAjdyAJ9eg1CTafcFv+U8W9GwcCPpU7m1XQCeMSCp xo6EMYY1Ixk81fveHOC+OQc= =UuFh -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select distinct speed on an indexed column
From: Haydies [mailto:[EMAIL PROTECTED] Sent: Monday, September 15, 2003 11:19 AM To: [EMAIL PROTECTED] Subject: Re: Select distinct speed on an indexed column Its a compound key, they are always slow. I would imagin you will need to seriously redesign your database to speed that up. I'm not 100% sure how the index is stored, but it would be some what pointless if it was individual field values. Its like haveing field1field2feild3field4field5 so that a single comparison of the values tells you if its unique. You might be able to speed it up by putting a secondary index on the first field Hmm... Well I tried adding and an additional index and that did not change the query speed. I'm curious as to why any one would design a database with that many feilds in the primary key? Maybe its me but that would just be wrong. I'm not to happy when I have 2 fields in the primary key I don't know. It's really out of my control. Maybe it's a Telecom thing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select distinct speed on an indexed column
Well, it's an InnoDb database and has some decent memory pools. | innodb_additional_mem_pool_size | 33554432 | innodb_buffer_pool_size | 536870912 -Original Message- From: Joseph Bueno [mailto:[EMAIL PROTECTED] Sent: Monday, September 15, 2003 11:47 AM To: Nathan Cassano Cc: '[EMAIL PROTECTED]' Subject: Re: Select distinct speed on an indexed column Since your primary key is used, maybe you should consider adding an index on PostedZpdi field only. You may also check 'key_buffer_size' value (show variables like 'key%'). Since your primary key is more than 200 MB big, allocating a big key buffer ( 256MB) may help. Joseph Bueno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: random data
tuncay bas [EMAIL PROTECTED] wrote: excuse me have can I get from mysql database random 6 data item? Take a look at the RAND() function: http://www.mysql.com/doc/en/Mathematical_functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: MySQL 3.23.58 has been released
Wasn't there just an announcement that 4.0.something was released? -Dan On Mon, 2003-09-15 at 12:52, Lenz Grimmer wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL 3.23.58, a new version of the popular Open Source/Free Software Database, has been released. It is now available in source and binary form for a number of platforms from our download pages at http://www.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is a bugfix release for the recent production version. It includes a fix for a potential local security vulnerability which has already been applied to MySQL 4.0.15 as well. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. News from the ChangeLog: * Fixed buffer overflow in password handling which could potentially be exploited by MySQL users with `ALTER' privilege on the `mysql.user' table to execute random code or to gain shell access with the UID of the mysqld process (thanks to Jedi/Sector One for spotting and reporting this bug). * `mysqldump' now correctly quotes all identifiers when communicating with the server. This assures that during the dump process, `mysqldump' will never send queries to the server that result in a syntax error. This problem is *not* related to the `mysqldump' program's output, which was not changed. (Bug #1148) * Fixed table/column grant handling - proper sort order (from most specific to less specific, *note Request access::) was not honored. (Bug #928) * Fixed overflow bug in `MyISAM' and `ISAM' when a row is updated in a table with a large number of columns and at least one `BLOB/TEXT' column. * Fixed MySQL so that field length (in C API) for the second column in `SHOW CREATE TABLE' is always larger than the data length. The only known application that was affected by the old behaviour was Borland dbExpress, which truncated the output from the command. (Bug #1064) * Fixed `ISAM' bug in `MAX()' optimisation. * Fixed `Unknown error' when doing `ORDER BY' on reference table which was used with `NULL' value on `NOT NULL' column. (Bug #479) Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany For technical support contracts, visit https://order.mysql.com/?ref=mlgr -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE/Ze5NSVDhKrJykfIRAjdyAJ9eg1CTafcFv+U8W9GwcCPpU7m1XQCeMSCp xo6EMYY1Ixk81fveHOC+OQc= =UuFh -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mixed Left and Inner Join
I am needing help with a mixed left and inner join. I guess my point of confusion is that if I do a join like table_1 left join table_2 inner join table_3 (assuming the clauses only reference each of the 2 tables in the order written), i expect to get all of the results from table_1 - where am I thinking incorrectly and how do I accomplish the following? Here are the specifics of the situation: 3 Tables (2 main and 1 associative) *event table* id name datetime_start datetime_end *resource table* id name *event_resources table* event_id resource_id I need to list ALL of the resources from the resource table and show if there is a conflict with any existing events by showing the name of the event, otherwise, the name of the event should be NULL. There will be a given datetime_start and a datetime_end to show conflicts against. The result set I need would look like this: ++++ | res_id | res_name | event_name | ++++ | 1 | TV/VCR | Event One | | 2 | Overhead Projector | Event Two | | 3 | Table and Chairs | NULL | | 4 | Artboard | NULL | ++++ event table data +---+-+-+ | name | datetime_start | datetime_end| +---+-+-+ | Event One | 2003-09-30 19:00:00 | 2003-09-30 21:00:00 | | Event Two | 2003-09-30 19:30:00 | 2003-09-30 21:30:00 | +---+-+-+ event_resources table data +--+-+ | event_id | resource_id | +--+-+ | 248 | 1 | | 250 | 2 | +--+-+ Given start time of '2003-09-30 20:00:00' and end time of '2003-09-30 22:00:00'. Here is the (incorrect) SQL statement as I am trying it right now: select resource.name as res_name, event.name as event_name from resource left join event_resources on resource.id = event_resources.resource_id inner join event on event_resources.event_id = event.id and ((event.datetime_start '2003-09-30 20:00:00' and event.datetime_start '2003-09-30 22:00:00') OR (event.datetime_end '2003-09-30 20:00:00' and event.datetime_end '2003-09-30 22:00:00')); I get the following result: ++++ | res_id | res_name | event_name | ++++ | 1 | TV/VCR | Event One | | 2 | Overhead Projector | Event Two | ++++ I WANT to get the following result: ++++ | res_id | res_name | event_name | ++++ | 1 | TV/VCR | Event One | | 2 | Overhead Projector | Event Two | | 3 | Table and Chairs | NULL | | 4 | Artboard | NULL | ++++ Any help would be greatly appreciated! Thanks. Chris Fowler [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 3.23.58 has been released
At 1:31 PM -0400 9/15/03, Dan Anderson wrote: Wasn't there just an announcement that 4.0.something was released? Yes, 4.0.15. There are 3.23, 4.0, 4.1, and 5.0 development trees, each at different stages of their lifetime. -- 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]
Stop a User warning message
I am doing a mysql_num_rows after a SELECT statement and am getting
the following warning message:
Warning: Supplied argument is not a valid MySQL result resource in {pathname to
program} on line 40
Line 40 - $result = mysql_num_rows($res);
The SELECT statement:
$res = mysql_query(SELECT * FROM company WHERE $category='yes',$db);
($category is passed to this program from an input form.)
Is there a way to stop this warning to the user when $category = 'no' for all records?
-tom
Re: MySQL 3.23.58 has been released
On Mon, Sep 15, 2003 at 01:48:36PM -0400, Dan Anderson wrote: There are 3.23, 4.0, 4.1, and 5.0 development trees, each at different stages of their lifetime. Is there any reason not to use 4.0.15 and instead use 3.23 in a production environment? I know MAX is unstable but I have 4.0.15 installed. Well, some organizations are more cautious approach to upgrades. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 1 days, processed 58,542,256 queries (388/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stop a User warning message
At 11:06 AM -0700 9/15/03, Tom Sparks wrote:
I am doing a mysql_num_rows after a SELECT statement and am getting
the following warning message:
Warning: Supplied argument is not a valid MySQL result resource in
{pathname to program} on line 40
Line 40 - $result = mysql_num_rows($res);
The SELECT statement:
$res = mysql_query(SELECT * FROM company WHERE $category='yes',$db);
($category is passed to this program from an input form.)
Is there a way to stop this warning to the user when $category =
'no' for all records?
It indicates an error in your program. You have no error checking
on the result of the mysql_query() call, I take it?
-tom
--
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]
Re: Stop a User warning message
Paul, It is not an error for $category to be 'no' in all records. The warning is just telling me that it didn't find any 'yes' records. I need to read up and find out how to error check the SELECT statement, I guess... -tom
Re: Stop a User warning message
Paul,
It is not an error for $category to be 'no' in all records. The warning
is just telling me that it didn't find any 'yes' records.
Well, no, it is not. The error you showed was:
Warning: Supplied argument is not a valid MySQL result resource in
{pathname to program} on line 40
PHP is telling you that $res does not refer to a valid result set.
That means your query *failed with an error*, not that
it executed properly and returned no records.
I need to read up and find out how to error check the SELECT statement,
I guess...
Yes. Try this, for example:
if (!$res)
die (query failed, error message is: . mysql_error ());
-tom
--
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]
IGNORE THIS MESSAGE
didn't ignore, huh? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld_multi - Cant find mysqld in my.cnf
Hi, I have an odd problem with mysqld_multi. I am trying to set up several MySQL servers on my new development server, for testing purposes. I currenty have only one, 4.0.9, just want to get this one going first. However, when I try to start mysqld_multi, it seems it does not recognise the mysqld=xxx option in the [mysqld40009] section of my my.cnf file. It simply complains it can't find a mysqld option! [bin] ./mysqld_multi --no-log start 40009 MySQL 4.0.9 is installed in: /usr/local/mysql/40009 Here is /etc/my.cnf: [mysqld40009] datadir=/usr/local/mysql/40009/data socket=/usr/local/mysql/40009/mysql.sock user=mysql basedir=/usr/local/mysql/40009 port=3306 log=log log-bin=binlog mysqld=/usr/local/mysql/40009/bin/mysqld_safe [mysqld_multi] I can only get it to work when the mysqld= part is in a [mysqld_multi] section, or on the command line. This is of course pointless, as I want each server to run its own version. My mysqld_multi is version 2.5, which according to the docs should be able to do this. This does work for example: [bin]./mysqld_multi --no-log --mysqld=/usr/bin/mysql/40009/bin/mysqld_safe - -mysqladmin=/usr/bin/mysql/40009/bin/mysqladmin start 40009 Any ideas? Mant thanks, Mark - Windows, Linux and Internet Development Consultant Email: [EMAIL PROTECTED] Web: http://www.scriptsmiths.co.za - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL ssl Q
Hey, Does anyone have any suggestions? Is this the correct list to post MySQL ssl questions? I didn't get any responses so far. Thanks. Sherrill On Wed, 10 Sep 2003, Sherrill (Pei-chih) Verbrugge wrote: Hey, I am trying to test mysql moniter ssl connection. I've compiled ssl support into MySQL server and client, and I am using version 4.0.14. In my.cnf global file, I have --ssl-cert, --ssl-ca, --ssl-key set for the server and the client. I grant some users to use require ssl, but I am getting access denied errors when I try to connect to mysql moniter even though I use correct password. Do you know what I am doing wrong? I am in the dark. I am able to connect to MySQL using DBI ssl correctly in a perl script with the same spvtest MySQL user. I just can't get the mysql client moniter to work. If you could help me, it will be wonderful. Thank you very much. [EMAIL PROTECTED] spv]$ mysql --ssl --ssl-ca=/openssl/cacert.pem --ssl-cert=/openssl/client-cert.pem --ssl-key=/openssl/client-key.pem --ssl-capath=/openssl -u spvtest -p Sherrill ~~ Sherrill (Pei-chih) Verbrugge -- ~~ Sherrill (Pei-chih) Verbrugge -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select distinct speed on an indexed column
Yeah, I have a similar box like yours. I copied the first column to a new table with an index. I ran select distinct and the query took 6 seconds to execute. This must have to do with the record length, because when I indexed the origional table's first column the query was 1 minute 30 seconds to select distinct. Do you think MySQL is scanning the entire index? Mike are you using ISAM or InnoDb? My tests were with InnoDb. I don't know why your Distincts are so slow. When I do a Select Distinct on 2 columns from my 2.7 million row table, I get back 256 distinct values in 5 seconds. The string columns are around 10 characters each. The column is indexed. I'm also using 2.4 g machine but wiht 1gbyte ram and 7200RPM 8m cache hard drives (8mb cache drives are faster than the conventional 2mb cache drives). Maybe it has to do with your record length? Try copying the column to a temporary table and index the column, then try your Select Distinct on that. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mixed Inner and Left Join SQL Statement
I am needing help with a mixed left and inner join SQL statement in MySQL 4.0 (ie, can't use subselects yet like 4.1). I guess my point of confusion is that if I do a join like table_1 left join table_2 inner join table_3 (assuming the clauses only reference each of the 2 tables in the order written), i expect to get all of the results from table_1 - where am I thinking incorrectly and how do I accomplish the following? Here are the specifics of the situation: 3 Tables (2 main and 1 associative) *event table* id name datetime_start datetime_end *resource table* id name *event_resources table* event_id resource_id I need to list ALL of the resources from the resource table and show if there is a conflict with any existing events by showing the name of the event, otherwise, the name of the event should be NULL. There will be a given datetime_start and a datetime_end to show conflicts against. The result set I need would look like this: ++++ | res_id | res_name | event_name | ++++ | 1 | TV/VCR | Event One | | 2 | Overhead Projector | Event Two | | 3 | Table and Chairs | NULL | | 4 | Artboard | NULL | ++++ event table data +---+-+-+ | name | datetime_start | datetime_end| +---+-+-+ | Event One | 2003-09-30 19:00:00 | 2003-09-30 21:00:00 | | Event Two | 2003-09-30 19:30:00 | 2003-09-30 21:30:00 | +---+-+-+ event_resources table data +--+-+ | event_id | resource_id | +--+-+ | 248 | 1 | | 250 | 2 | +--+-+ Given start time of '2003-09-30 20:00:00' and end time of '2003-09-30 22:00:00'. Here is the (incorrect) SQL statement as I am trying it right now: select resource.name as res_name, event.name as event_name from resource left join event_resources on resource.id = event_resources.resource_id inner join event on event_resources.event_id = event.id and ((event.datetime_start '2003-09-30 20:00:00' and event.datetime_start '2003-09-30 22:00:00') OR (event.datetime_end '2003-09-30 20:00:00' and event.datetime_end '2003-09-30 22:00:00')); I get the following result: ++++ | res_id | res_name | event_name | ++++ | 1 | TV/VCR | Event One | | 2 | Overhead Projector | Event Two | ++++ I WANT to get the following result: ++++ | res_id | res_name | event_name | ++++ | 1 | TV/VCR | Event One | | 2 | Overhead Projector | Event Two | | 3 | Table and Chairs | NULL | | 4 | Artboard | NULL | ++++ Any help would be greatly appreciated! Thanks. Chris Fowler [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: random record
I just checked... 83 On Mon, 15 Sep 2003 13:54:53 +0100, Andy Eastham [EMAIL PROTECTED] wrote: | 39? | | -Original Message- | From: tuncay bas [mailto:[EMAIL PROTECTED] | Sent: 15 September 2003 13:32 | To: mysql | Subject: random record | | | hi, | | why its mysql database over random record use? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stop a User warning message
Yep, you're right Paul, it is a bad query, one thing I've been burnt on a lot
in the past, is using the variables inside those double quoted lines. I've
started expanding everything, mostly objects and arrays won't get interpreted
correctly, try building the query in a string, and then printing out the
string to the screen and see if it looks right, and then execute that mysql
command from the mysql command line, and see if it flies, bet it won't.
KL
PS: Sorry about sending you that last message Paul, I'm still not used to the
reply all, and I just use that all too easy reply button :(
Paul DuBois wrote:
Paul,
It is not an error for $category to be 'no' in all records. The warning
is just telling me that it didn't find any 'yes' records.
Well, no, it is not. The error you showed was:
Warning: Supplied argument is not a valid MySQL result resource in
{pathname to program} on line 40
PHP is telling you that $res does not refer to a valid result set.
That means your query *failed with an error*, not that
it executed properly and returned no records.
I need to read up and find out how to error check the SELECT statement,
I guess...
Yes. Try this, for example:
if (!$res)
die (query failed, error message is: . mysql_error ());
-tom
--
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL access issue
Hello everyone, Kind of an oddball question but I'll try to make it as clear as possible. We have a Solaris server, that we have root access to. It houses mysql db's and information. I was not the admin for the db's and frankly I'm not an MySQL buff to be honest. Our DB Admin is gone now, I need access to mysql db's, but I don't know what the username/password was for them. What are my options here to be able to not lose this info and get root access into the db's in mysql? Thank you, ~Phil
MySQL/InnoDB-3.23.58 is released
Hi! InnoDB is a MySQL table type which provides transactions, foreign key constraints, and a non-free hot backup tool. InnoDB is included in MySQL-Max-3.23 downloads, and in all downloads of MySQL-4.0 and MySQL-4.1. Release 3.23.58 is a bugfix release of the 'old' stable 3.23 branch. For production use, MySQL-4.0 is now the recommended version. The full InnoDB changelog of 3.23.58: * Fixed a bug: InnoDB could make the index page directory corrupt in the first B-tree page splits after mysqld startup. A symptom would be an assertion in page0page.c, in function page_dir_find_slot(). * Fixed a bug: InnoDB could in rare cases return an extraneous row if a rollback, purge, and a SELECT coincided. * Fixed a possible hang over the btr0sea.c latch if SELECT was used inside LOCK TABLES. * Fixed a bug: if a single DELETE statement first managed to delete some rows and then failed in a FOREIGN KEY error or a 'Table is full error', MySQL did not roll back the whole SQL statement as it should. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does InnoDB use any of these variables?
Mikhail, I am not absolutely sure of the answers below. We should ask Monty. - Original Message - From: Mikhail Entaltsev [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, September 15, 2003 9:30 AM Subject: Does InnoDB use any of these variables? Hi, Does InnoDB use any of these variables: bulk_insert_buffer_size no join_buffer_size yes key_buffer_size no, except if temp tables are used in sorts, for example read_buffer_size yes read_rnd_buffer_size yes sort_buffer_size yes table_cache yes thread_concurrency yes Thanks in advance, Mikhail. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lotus Notes/Script...
Has anyone every connected Lotus Notes/Script to MySQL? Someone asked me about it and I can't seem to find any information on Google... and I don't know much of MySQL... Would I have to have built MySQL with ODBC support? I installed the binary so wouldn;t this already be available... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump and mysqlimport
I've been trying this for a few hours now and I'm not sure what's going on. mysqldump --host=localhost --user=root --password=mypass dbname dbname.dump This works fine, it creates a text file with CREATE TABLE blocks and INSERT statements for each table in the database. When I try to recreate this db on another server using mysqlimport: mysqlimport -u root -p dbname 'dbname.dump' I keep getting the error: Error: Table 'dbname.dbname' doesn't exist, when using table: dbname My question: Why is mysqlimport interpreting the dbname argument as a table name? From the documentation for mysql 4.8.8: mysqlimport is invoked like this: shell mysqlimport [options] database textfile1 [textfile2 ...] It's not doing that. It's taking the argument database and using it as the name of a table instead. I checked to make sure I didn't have any CREATE TABLE dbname statements to make sure it wasn't the dump file trying to do this, but it's not. The command line for some reason is trying to make a table called dbname.dbname. I assumed that the mysqlimport would just create the necessary tables using the dumpfile. Anyone have a similar problem? Peter. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with CHARACTER SET xxx COLLATE yyy
On Mon, 15 Sep 2003, Paul DuBois wrote: At 5:23 PM +0200 9/15/03, Sigfrid Lundberg - LUB NetLab wrote: I'm trying to create a table in my database with CHARACTER SET utf8 COLLATE COLLATE utf8_general_ci Answer is ERROR 1115: Unknown character set: 'utf8_general_ci' Try it again when 4.1.1 comes out. There is a bunch of character set renaming that went on between 4.1.0 and 4.1.1. Also, COLLATE need be specified only once. :-) I assume the double COLLATE above is just a typo? Yeah.. What you're seeing is one of the things referred to by: http://www.mysql.com/doc/en/Charset.html which says: The features described here are as implemented in MySQL 4.1.1. (MySQL 4.1.0 has some but not all of these features, and some of them are implemented differently.) Ah, I have to get a new one... Sigh, I installed this just last week. Thanks a lot! Sigfrid -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 3.23.58 has been released
It is a good idea to stick with the MySQL branch that you currently use in production. The only reasons I can see to do otherwise are 1) if you need a feature introduced in one of the newer development trees or 2) if your project is in its early stages and you want to avoid the hassle of upgrading later. There are several changes between 3.23.x and 4.0.x that could require you to modify/upgrade your MySQL-enabled apps. You can avoid most of them by keeping up with the change-log for the dev tree branch that comes after yours (4.0.x if you use 3.23.x, 4.1.x if you're using 4.0.x, etc.)and writing your apps with those changes in mind. Personally, I have some production servers running 3.23.x and some running 4.0.x versions of MySQL. Neither version has given me a problem. There's my two cents. ;) -Rob -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Monday, September 15, 2003 2:08 PM To: Dan Anderson Cc: Paul DuBois; [EMAIL PROTECTED] Subject: Re: MySQL 3.23.58 has been released On Mon, Sep 15, 2003 at 01:48:36PM -0400, Dan Anderson wrote: There are 3.23, 4.0, 4.1, and 5.0 development trees, each at different stages of their lifetime. Is there any reason not to use 4.0.15 and instead use 3.23 in a production environment? I know MAX is unstable but I have 4.0.15 installed. Well, some organizations are more cautious approach to upgrades. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 1 days, processed 58,542,256 queries (388/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and mysqlimport
At 4:47 PM -0400 9/15/03, Peter Koutsoulias wrote: I've been trying this for a few hours now and I'm not sure what's going on. mysqldump --host=localhost --user=root --password=mypass dbname dbname.dump This works fine, it creates a text file with CREATE TABLE blocks and INSERT statements for each table in the database. When I try to recreate this db on another server using mysqlimport: mysqlimport -u root -p dbname 'dbname.dump' mysqlimport is not the complement of mysqldump, it is a command-line interface to the LOAD DATA INFILE statement. To import the dump file, use mysql: mysql -u root -p dbname dbname.dump I keep getting the error: Error: Table 'dbname.dbname' doesn't exist, when using table: dbname My question: Why is mysqlimport interpreting the dbname argument as a table name? From the documentation for mysql 4.8.8: mysqlimport is invoked like this: shell mysqlimport [options] database textfile1 [textfile2 ...] It's not doing that. It's taking the argument database and using it as the name of a table instead. I checked to make sure I didn't have any CREATE TABLE dbname statements to make sure it wasn't the dump file trying to do this, but it's not. The command line for some reason is trying to make a table called dbname.dbname. I assumed that the mysqlimport would just create the necessary tables using the dumpfile. Anyone have a similar problem? Peter. -- 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]
Re: MySQL access issue
this link from the mysql docs should help http://www.mysql.com/doc/en/Resetting_permissions.html - hcir Kind of an oddball question but I'll try to make it as clear as possible. We have a Solaris server, that we have root access to. It houses mysql db's and information. I was not the admin for the db's and frankly I'm not an MySQL buff to be honest. Our DB Admin is gone now, I need access to mysql db's, but I don't know what the username/password was for them. What are my options here to be able to not lose this info and get root access into the db's in mysql? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Convert with Unions
Hello all, I've tried searching through the lists but haven't been able to find an answer to my problem. If any one can help I would be very grateful, Thanks in advance. Anyway, on to the problem. I have two tables each with field that contains a date in string format 'YYMMDDhhmmss' I want to get the contents of those fields back as DateTime types. for example SELECT CONVERT(PatientDateTimeOfBirth, DATETIME) FROM orders returns the record set with the field as type DateTime. That works great, however when I try to union the results of two selects with the converts in them I get back the original string data not the date type. (simplified example) (SELECT CONVERT(PatientDateTimeOfBirth, DATETIME) FROM orders) UNION (SELECT CONVERT(PatientDateTimeOfBirth, DATETIME) FROM patients) This gives me two rows with the one field formatted like 'YYMMDDhhmmss' instead of the datetime type. Does anyone have any suggestions on what to try. Once again, Thanks in advance Andrew Taft -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lotus Notes/Script...
Has anyone every connected Lotus Notes/Script to MySQL? I'm not sure what you mean. Just five minutes ago, I finished writing a LotusScript agent that exports Notes data to a text file. I then used LOAD to bring that data into a MySQL table. Both Lotus/Notes and MySQL have obdc drivers but I haven't had time to figure out how to get one system to talk to the other. HTH. Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lotus Notes/Script...
Jonathon We are using odbc and jdbc to link up from notes to mysql. David -Original Message- From: Jonathan Villa [mailto:[EMAIL PROTECTED] Sent: Monday, September 15, 2003 1:43 PM To: [EMAIL PROTECTED] Subject: Lotus Notes/Script... Has anyone every connected Lotus Notes/Script to MySQL? Someone asked me about it and I can't seem to find any information on Google... and I don't know much of MySQL... Would I have to have built MySQL with ODBC support? I installed the binary so wouldn;t this already be available... -- 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]
Performance of using 2 columns as row ID
Hello, I am currently creating a multi-user system which has approximately 15 tables. Each table has a primaryID as well as the userID of who this record belongs to. Therefore, when I perform a SELECT I am checking (potentially) both the primaryID column as well as the userID. I'm using MyISAM tables. Assuming I have a valid index on each table of (primaryColumn, userID) is there much of a performance loss doing this? While storing the userID is not technically necessary (since each primary column has the UNIQUE attribute) I find it much simpler to know who a row belongs to just by checking the ID rather than tracing back the various relations that will link said row/table back to a user. Any advice would be appreciated, Russ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD Fails on Lost Connection
I've tried this several times: mysql source c:/mysql/import_cash.sql with this error: ERROR 2013: Lost connection to MySQL server during query from the commandline, using: C:\mysql -u myname -pmy_password my_database c:/mysql/import_cash.sql I get the same error. This happens immediately, so I'm having a hard time believing it's a connection time-out. Any thoughts? Thanks. Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Order By question
I have a query:- SELECT recTran.TransactionID,tr.* FROM Transaction tr LEFT OUTER JOIN ReconciledTransactions recTran ON recTran.TransactionID = tr.TransactionID WHERE tr.ReconciliationID = '8' HAVING recTran.TransactionID IS NULL ORDER BY 'tr.Amount' DESC; The problem I have is that the Order By doesn't seem to be returning the results in the order I expect. Can anybody shed any light on this? I am expecting to see the data returned ordered overall by the order_by clause, however it doesn't do this. Regards Marty --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.516 / Virus Database: 313 - Release Date: 01/09/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order By question
Martin Moss [EMAIL PROTECTED] wrote: SELECT recTran.TransactionID,tr.* FROM Transaction tr LEFT OUTER JOIN ReconciledTransactions recTran ON recTran.TransactionID = tr.TransactionID WHERE tr.ReconciliationID = '8' HAVING recTran.TransactionID IS NULL ORDER BY 'tr.Amount' DESC; You are ordering by a constant: the string tr.Amount. Since the string is the same for every row, you're not actually getting any ordering. What are you trying to accomplish with those quotes? Get rid of them, and you may get the results you want. -- 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]
Problem with stability
Hello, I have problem with stability of MySQL. I got this messages: 030915 11:30:52 mysqld restarted /usr/local/libexec/mysqld: ready for connections mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail key_buffer_size=4190208 record_buffer=131072 sort_buffer=524280 max_used_connections=15 max_connections=50 threads_connected=6 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 36091 K bytes of memory Hope that's ok, if not, decrease some variables in the equation 030915 12:01:37 mysqld restarted /usr/local/libexec/mysqld: ready for connections I decreased values, but situation is still the same. My version is 3.23.55 and I have OpenBSD. This is my /etc/my.cnf: # The following options will be passed to all MySQL clients [client] #password = your_password #port = 3306 #socket = /var/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] #port = 3306 #socket = /var/mysql/mysql.sock skip-locking set-variable= key_buffer=4M set-variable= max_allowed_packet=1M set-variable= table_cache=64 set-variable= sort_buffer=512K set-variable= net_buffer_length=8K set-variable= myisam_sort_buffer_size=8M set-variable= max_connections=50 server-id = 1 skip-innodb default-character-set=latin2 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=20M set-variable= sort_buffer=20M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=20M set-variable= sort_buffer=20M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout I use MySQL with Apache and PHP, now I have 256 MB RAM. Is some solution? -- S pozdravem, Bc. Radek Kreja Starnet, s. r. o. [EMAIL PROTECTED] http://www.ceskedomeny.cz http://www.skdomeny.com http://www.starnet.cz ICQ: 65895541 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order By question - solved
sorry, I had some extraneous quotes in my perl code:-) zzz - Original Message - From: Martin Moss [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 15, 2003 11:17 PM Subject: Order By question I have a query:- SELECT recTran.TransactionID,tr.* FROM Transaction tr LEFT OUTER JOIN ReconciledTransactions recTran ON recTran.TransactionID = tr.TransactionID WHERE tr.ReconciliationID = '8' HAVING recTran.TransactionID IS NULL ORDER BY 'tr.Amount' DESC; The problem I have is that the Order By doesn't seem to be returning the results in the order I expect. Can anybody shed any light on this? I am expecting to see the data returned ordered overall by the order_by clause, however it doesn't do this. Regards Marty --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.516 / Virus Database: 313 - Release Date: 01/09/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.516 / Virus Database: 313 - Release Date: 02/09/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lotus Notes/Script...
In a message dated 9/15/03 4:53:18 PM Eastern Daylight Time, [EMAIL PROTECTED] writes: Would I have to have built MySQL with ODBC support? I installed the binary so wouldn;t this already be available... Download and install the MyODBC driver for your client application machine (surprisingly, it may be the Lotus Notes server in this case...but it's been a while since I've used Notes). If you are running MySQL on a *nix/Linux box, you'll need ODBCunix installed there too. Otherwise, if its on a Microsoft platform, it's likely already there. Links: http://www.odbcunix.org (probably on RedHat's site too) http://www.mysql.com/downloads/api-myodbc.html Hope this helps. /Tony ps. database, SQL, queries, sort, select, alter, table and other words for the list man to read. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD Fails on Lost Connection
Hi Randy, See here: http://www.mysql.com/doc/en/Gone_away.html Maybe one of the queries in import_cash.sql is longer than max_allowed_packet? - Original Message - From: Randy Chrismon Sent: Monday, September 15, 2003 4:50 PM Subject: LOAD Fails on Lost Connection I've tried this several times: mysql source c:/mysql/import_cash.sql with this error: ERROR 2013: Lost connection to MySQL server during query from the commandline, using: C:\mysql -u myname -pmy_password my_database c:/mysql/import_cash.sql I get the same error. This happens immediately, so I'm having a hard time believing it's a connection time-out. Any thoughts? Thanks. Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does Null == ?
On Mon, Sep 15, 2003 at 12:46:50PM -0400, Randy Chrismon wrote: an exort from a Lotus Notes database. At some point, the MySQL documentation says that a table with no nullable columns is better/faster than one with. The Lotus Notes database I'm migrating, however, has many fields with no values. I infer from the MySQL documentation that I'm better off doing: create table my_table(a_field varchar(16) NOT NULL default , ...) and exporting my values from Notes as rather than using NULL. No. The meaning of NULL is defined in the SQL specification; it means not known or not applicable. If you have data that is missing because it is not known or not applicable, then use NULL. A zero-length string has no defined meaning. I've done maintenance on databases that contained zero-length strings, and they were nightmares. I can't think of any reason why you would use a zero-length string in a database. Bob Hall -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FOREIGN KEY() REFERENCES ON UPDATE CASCADE ON DELETE RESTRICT
I believe that your ON UPDATE CASCADE clause should be in the definition for the PRODUCT and CUSTOMER table rather than the PRODUCT_ORDER table. However, I don't think that it will work how you expect. ON UPDATE CASCADE means that everytime you update a row in this table then all rows in other tables that reference this table (via a foreign key) will be updated also. So if there are no rows in PRODUCT_ORDER then ON UPDATE CASCADE will not insert new rows when you add rows to the other tables. What will happen instead is that any row (that already exists) in your PRODUCT_ORDER table will be updated with the new data that has been updated in one of the other tables. This is my understanding of how it works anyway. For further information go to http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html Hope this helps. Toro Dear Programmers, At the end of this query, I make a select * from the table product_order, Which happens to be empty, Why ? Is something wrong with my insert statements please ? I inserted something into the tables CUSTOMER and PRODUCT, and I expected it to appear into the table PRODUCT_ORDER which is some kind of relationship between the two entity types CUSTOMER and PRODUCT. What do I please have to insert in order to achieve some evidence for the existence of referential integrity ? please ? ON UPDATE CASCADE I feel means something like that the actual data is propagated due to the references ? Yours Sincerely Morten Gulbrandsen USE test; DROP TABLE IF EXISTS PRODUCT_ORDER, CUSTOMER, PRODUCT; CREATE TABLE PRODUCT ( category INT NOT NULL, id INT NOT NULL, priceDECIMAL(1,2), PRIMARY KEY(category, id) )TYPE=INNODB; CREATE TABLE CUSTOMER ( id INT NOT NULL, PRIMARY KEY (id) )TYPE=INNODB; CREATE TABLE PRODUCT_ORDER ( noINT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_idINT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customer(id) )TYPE=INNODB; INSERT INTOPRODUCT(category, id, price) VALUES(1, 1, 0.1 ); INSERT INTOCUSTOMER(id) VALUES (2); SELECT * FROM PRODUCT; SELECT * FROM CUSTOMER; SELECT * FROM PRODUCT_ORDER; C:\mysql\binmysql -u sampadm -psecret -vvv Foreign_Key_02.sql out.txt -- DROP TABLE IF EXISTS PRODUCT_ORDER, CUSTOMER, PRODUCT -- Query OK, 0 rows affected (0.02 sec) -- CREATE TABLE PRODUCT ( category INT NOT NULL, id INT NOT NULL, priceDECIMAL(1,2), PRIMARY KEY(category, id) ) TYPE=INNODB -- Query OK, 0 rows affected (0.00 sec) -- CREATE TABLE CUSTOMER ( id INT NOT NULL, PRIMARY KEY (id) ) TYPE=INNODB -- Query OK, 0 rows affected (0.00 sec) -- CREATE TABLE PRODUCT_ORDER ( noINT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_idINT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customer(id) ) TYPE=INNODB -- Query OK, 0 rows affected (0.02 sec) -- INSERT INTOPRODUCT(category, id, price) VALUES(1, 1, 0.1 ) -- Query OK, 1 row affected (0.00 sec) -- INSERT INTOCUSTOMER(id) VALUES (2) -- Query OK, 1 row affected (0.00 sec) -- SELECT * FROM PRODUCT -- +--++---+ | category | id | price | +--++---+ |1 | 1 | 0.10 | +--++---+ 1 row in set (0.00 sec) -- SELECT * FROM CUSTOMER -- ++ | id | ++ | 2 | ++ 1 row in set (0.00 sec) -- SELECT * FROM PRODUCT_ORDER -- Empty set (0.00 sec) Bye -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does Null == ?
Bob Hall wrote: The meaning of NULL is defined in the SQL specification; it means not known or not applicable. Which is just about as useful as not defining it, actually. The vagueness is the cause of a great many program bugs when database designers don't specify what NULL means for a given field. To give a hypothetical example: The application is payroll/personnel. A programmer is tasked with creating forms for data entry on new employees, including supervisor. If the user doesn't enter a new employee's supervisor, the application accepts it, figuring that it is not yet known, and stores NULL for the field (not known use of NULL). Meanwhile, a payroll programmer has been tasked with writing an application to give the CEO a huge bonus and stock options. To figure out which employee is the CEO, the application looks for the employee with NULL for supervisor (not applicable use of NULL). Suddenly, a large number of new hires are fabulously wealthy. Who screwed up? Answer: the DB designer who didn't specify what NULL meant. A zero-length string has no defined meaning. I've done maintenance on databases that contained zero-length strings, and they were nightmares. I can't think of any reason why you would use a zero-length string in a database. Because you know that a given person has no middle name? To represent no value, as differentiated from not known? Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does Null == ?
In the last episode (Sep 15), Bruce Feist said: Bob Hall wrote: The meaning of NULL is defined in the SQL specification; it means not known or not applicable. Which is just about as useful as not defining it, actually. The vagueness is the cause of a great many program bugs when database designers don't specify what NULL means for a given field. To give a hypothetical example: The application is payroll/personnel. A programmer is tasked with creating forms for data entry on new employees, including supervisor. If the user doesn't enter a new employee's supervisor, the application accepts it, figuring that it is not yet known, and stores NULL for the field (not known use of NULL). Meanwhile, a payroll programmer has been tasked with writing an application to give the CEO a huge bonus and stock options. To figure out which employee is the CEO, the application looks for the employee with NULL for supervisor (not applicable use of NULL). Suddenly, a large number of new hires are fabulously wealthy. Who screwed up? Answer: the DB designer who didn't specify what NULL meant. Your example has nothing to do with the vagueness of NULL though. Replace NULL with 0 and you get the same result. Of course, if I were the payroll programmer, I would simply select all employees WHERE emp.titleid = titles.id AND titles.name=CEO. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Does Null == ?
The application is payroll/personnel. A programmer is tasked with creating forms for data entry on new employees, including supervisor. If the user doesn't enter a new employee's supervisor, the application accepts it, figuring that it is not yet known, and stores NULL for the field (not known use of NULL). Meanwhile, a payroll programmer has been tasked with writing an application to give the CEO a huge bonus and stock options. To figure out which employee is the CEO, the application looks for the employee with NULL for supervisor (not applicable use of NULL). Suddenly, a large number of new hires are fabulously wealthy. Who screwed up? Answer: the DB designer who didn't specify what NULL meant. I would disagree here. I'd place the blame squarely with the programmer who made an *assumption* about the meaning of NULL in the absence of documentation. It's the responsibility of the programmer to understand the system he or she is writing code for, before running that code. If documentation can't be found, the programmer should have asked the DB designer. If the DB designer was unavailable the programmer should have at LAST tested the assumption (SELECT COUNT(*) FROM employee WHERE supervisor_id IS NULL -- there's only one CEO, so if it returns a value 1 the assumption is definitely false, if it returned 0, the assumption is definitely false, and if it returned exactly 1, the assumption MAY be true) before mucking with data. Granted that the DB designer had the opportunity to prevent this particular misunderstanding by designing a schema that distinguishes between A-mark (absence of information -- not known) and I-mark (inapplicability of information), but it's impossible to completely idiot-proof any system. At some point, the users of the system -- in this case the programmer -- have a responsibility to achieve a certain minimum level of understanding before using that system. Because you know that a given person has no middle name? To represent no value, as differentiated from not known? That's an ugly way to make the distinction between A-mark and I-mark. In most situations, I'd move the relevant column(s) to a separate table, with a NULL-allowed column in that table and a FK reference back to the original table. The absence of a row in this child table indicates I-mark, and the presence of a row with a NULL in the column indicates an A-mark. Or alternatively you could just get a database that has two kinds of NULLs, specifically defined to represent the distinction you bring up. ( http://www.firstsql.com ) -JF -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
