Re: UPDATE with WHERE+ORDER+LIMIT error(?) - test shell script (fwd)
Hello Serge, Serge E. Yakubovich wrote: Description: First of all I want to thank you for your excelent bug report and apologize for delayed responce. The same test script I've submitted today to [EMAIL PROTECTED], which demonstrates improper(?) behaviour of UPDATE statement on 4.0.12 server under FreeBSD, on 4.0.15 server under Alt Linux causes segmentation fault. It is quite difficult to judje what is proper/improper behaviour of such UPDATE statement. From User Variables section in manual: Note: in a SELECT statement, each expression is evaluated only when it's sent to the client ... The general rule is to never assign and use the same variable in the same statement. end of quote Furthermore Relational Model requires that no operation depends on internal order of rows in table or order in which rows are processed. (Tables are Sets - they have no order, Operations on Sets happen at once) However no statement should crash mysqld so I am entering your bug report into Bugs Database with comment that this is improper usage of User Variables. I was able to narrow down the problem to simple test case and entered it as #1945 in our Bugs Database http://bugs.mysql.com/1945 Feel free to follow up discussion there. --- SAMPLE 1 (BAD): inproper row is updated This is questionable. My feeling is that this should be discussed as separate issue once crashing bug is fixed. Comments are welcome. Best regards -- Are you MySQL certified? - http://www.mysql.com/certification For technical support contracts, visit https://order.mysql.com/?ref=msal __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RADIUS is a Wh*%@
Jeff,
I am affraid your question has nothing to do with MySQL internals. It is all about
MySQL usage.
Stout, Jeff wrote:
I am using MySQL 4.0.15 on Win2K (not by choice, vendor chose this platform)
I user the Database to store user information to Allow RADIUS to authenticate
users against the tables. I have to encrypt the password fields, If I use
MD5 it is a one way algorithm, enable for me to user MD5 I have to compare
the hashes if hashes match then I'm golden and user is granted access,
however the flavor of RADIUS the Vendor has chosen to use can not by it's self
do a hash comparison thus all Hash encrypted passwords can't be read by
Radius and users are denied access.
Doesn't matter. You only need to define what RADIUS has to match and do it within
SQL query.
For an example if you store MD5 hashes of passwords into table you can just use:
SELECT MD5(plaintextpasswd) = password WHERE userid=userid;
In case hashes match above query will return 1 (true)
Please don't crospost such kind of questions in internals@ It is dedicated to
MySQL source, algorithms etc.
Use general list [EMAIL PROTECTED] or consider purchasing Support. You can
read more about Support MySQL AB provides at http://www.mysql.com/support/
Has any one had experience with this and know of a way to fix this
My other thought was to use:
INSERT INTO user_profile (userid, password)
VALUES ('sam', AES_ENCRYPT(sam,password));
this will allow me to encrypt the password field, but I still need to get RADIUS to
do the AES_DECRYPT any thoughts.
ugh
Jeff Stout
CSG Systems, Inc.
303-200-3204
Best regards
--
Are you MySQL certified? -- http://www.mysql.com/certification
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with OR?
Joshua, Joshua Spoerri wrote: Forgive me, that example is no good. Oddly, it works, but the following does not: mysql create temporary table x (y int, z int, q int, index (y, z)); insert into x values (1,2,3), (3,4,5), (5,6,7); explain select * from x where y = 1 or z = 1; MySQL will never use any index for small tables. With just few rows using index adds overhead only. Table scan is faster in such cases. This is docummented behaviour. Best regards -- Are you MySQL certified? - http://www.mysql.com/certification For technical support contracts, visit https://order.mysql.com/?ref=msal __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with OR?
Joshua, Joshua Spoerri wrote: On Tue, 5 Aug 2003, Alexander Keremidarski wrote: MySQL will never use any index for small tables. With just few rows using index adds overhead only. Table scan is faster in such cases. This is docummented behaviour. is 100,000 rows small? my simple OR queries take longer than a second. No. It is not! I referred to your test case in your previous email: Oddly, it works, but the following does not: mysql create temporary table x (y int, z int, q int, index (y, z)); insert into x values (1,2,3), (3,4,5), (5,6,7); explain select * from x where y = 1 or z = 1; mysql create temporary table x (y int, z int, index (y, z)); insert into x select f1,f2 from myrealtable; alter table x add q int; explain select * from x where y = 1 or z = 1; Query OK, 0 rows affected (0.00 sec) Query OK, 101200 rows affected (1.95 sec) Records: 101200 Duplicates: 0 Warnings: 0 Query OK, 101200 rows affected (1.61 sec) Records: 101200 Duplicates: 0 Warnings: 0 +---+--+---+--+-+--++-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--++-+ | x | ALL | y | NULL |NULL | NULL | 101200 | Using where | +---+--+---+--+-+--++-+ 1 row in set (0.00 sec) Same table: mysql explain select * from x where y = 1 or z = 1; +---+---+---+--+-+--+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+---+--+-+--+---+-+ | x | index | y | y| 10 | NULL | 85971 | where used; Using index | +---+---+---+--+-+--+---+-+ Note that I am using MySQL 3.23.57, 4.0.14 and 4.1.0 Best regards -- Are you MySQL certified? - http://www.mysql.com/certification For technical support contracts, visit https://order.mysql.com/?ref=msal __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with OR?
Joshua, Joshua Spoerri wrote: Which version is targetted for optimization of OR searching on two keys, that is, select * from sometable where f1 = 123 or f2 = 123, as described in http://www.mysql.com/doc/en/Searching_on_two_keys.html ? As described there MySQL can use only one index per table. It will work same way untill next major Optimizer update which is scheduled for 5.1 Meanwhile your query can be optimized with using of composite index over both columns: mysql explain select * from t where f1 = 10 or f2 = 10; +---+---+---+--+-+--+---+--+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+---+--+-+--+---+--+ | t | index | f1| f1 | 10 | NULL | 16384 | Using where; Using index | +---+---+---+--+-+--+---+--+ Thanks -- Are you MySQL certified? - http://www.mysql.com/certification For technical support contracts, visit https://order.mysql.com/?ref=msal __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: optimizer bug in the index used by mysql/Innodb in the search
Hello, [EMAIL PROTECTED] wrote: Description: Hello, I am working with mysql/InnoDb 4.0.11 under Windows 2000. I have the following table: cut Time: 0.20 secs. Explain: table type possible_keys keykey_len ref rows Extra GIROS range PRIMARY,TipoFeVCod TipoFeVCod1 NULL 9417 Using where; Using index; Using filesort I changed the table type to MyIsam (alter table giros type=MyIsam), and I ran the above query: Time:0.02 secs Explain: table type possible_keys keykey_len ref rows Extra GIROS range PRIMARY,TipoFeVCod PRIMARY 16NULL 19472 Using where; Using index So, MySQL/MyIsam uses the right index (the primary index) while MySQL/InnoDB uses a wrong index (TipoFeVCod). From EXPLAIN result output you can do only opposite conclusion: With InnoDB MySQL chooses to use Index TipoFeVCod for which it expects to match 9417 rows. This is about twice less rows than expected with PRIMARY key in second explain 19472 cut How-To-Repeat: Select ... from giros ... Fix: Sorry but this is not Repeatable Test Case. Optimizer issues highly depend on your real data. -- MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html For technical support contracts, visit https://order.mysql.com/?ref=msal __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL 4.0.5(a) is released
Dear Stefan,
Stefan Hinz, iConnect (Berlin) wrote:
Dear Lenz,
Removed variable `safe_show_database' as it was not used anymore.
What will ISPs say about this one? They use 'safe_show_database' for their
MySQL setups, so their customers on virtual MySQL hosts cannot see other
customers' databases. (It's more likely that you won't attack something
which you cannot see.)
Or am I missing out on something?
Functionality is still there, but implemented at the place it belongs to.
Pay attention on Privlieges tables 4.0.5 uses. You will see there is:
`Show_db_priv` enum('N','Y') NOT NULL default 'N'
i.e. User must be granted explicitly this privilege in order to be able to use:
SHOW DATABASES;
Suggested way for setting this Ptivilege is ofcourse command:
GRANT SHOW DATABASES;
I hope you will agree that this approach provides much better flexibility and is
more natural than mysqld starting option.
If you still have concerns, please don't hesitate to share them with us.
--
For technical support contracts, visit https://order.mysql.com/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
___/ www.mysql.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Locking TABLES for myisamchk, please help!!
Mark wrote: Wednesday, May 22, 2002, 9:23:02 AM, louie wrote: cut Personally, I find the behavior of myisasmchk to be a bit beta when it comes live tables. It would be real easy for myisasmchk to refuse to run when mysqld is running. That should be a built-in precaution. I can understand possible objections to myisasmchk globally locking tables out of the blue (it might disrupt what some clients are doing), but if myisamchk cannot run on a live table, it should not run on a live table. As simple as that. Hi, It is not that simple. You can run several mysqld simultaneously. So checking if mysqld is running is not enough. Or you might wish to use myisamchk knowing in advance that mysqld will not write to table. Typical case is to check 'offline' database or table. Backup copy for example. Another case: You want to check table which is read-only for all mysql users and for some reason (different priority, buffers used etc.) you don't want to do it with REPAIR TABLE. myisamchk uses tables as files so it is admin responcibility to avoid problems. It is dangerous to use it but it is possible. You can compare it to filesystem checks. It is admin to decide if he can handle it with live server or not. -- Best regards -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com M: +359 88 231668 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [BUG] CONCAT_WS + GROUP BY display problem
Terra wrote:
Greetings,
cut
mysql select *,concat_ws('.',oct1,oct2,oct3,oct4)
ip,concat(oct1,'.',oct2,'.',oct3,'.',oct4) test,count(*) c from ips group by
oct1,oct2,oct3,oct4 having c1;
+--+--+--+--+--+-+---+--++---+
| ID | OCT1 | OCT2 | OCT3 | OCT4 | SID | AVAIL | ip | test | c
|
+--+--+--+--+--+-+---+--++---+
| 3424 | 63 | 151 | 144 | 84 | 0 | 1 | 63.151.144.8 | 63.151.144.84 | 2
|
| 3425 | 63 | 151 | 144 | 85 | 0 | 1 | 63.151.144.8 | 63.151.144.85 | 2
|
| 3426 | 63 | 151 | 144 | 86 | 0 | 1 | 63.151.144.8 | 63.151.144.86 | 2
|
| 3427 | 63 | 151 | 144 | 87 | 0 | 1 | 63.151.144.8 | 63.151.144.87 | 2
|
Notice the difference between the result columns of 'ip' and 'test'...
It is normal to have a difference there. It is not beacuse of how concat() and()
concat_ws() work, but how GROUP BY works.
In order to get predictable result you need to use:
select concat_ws('.',oct1,oct2,oct3,oct4) as IP, count(*) as c from ips group by
IP [having c 1];
Your table is:
mysql desc ips;
+---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-++
| ID| int(11) | | PRI | NULL| auto_increment |
| OCT1 | int(3) | | | 0 ||
| OCT2 | int(3) | | | 0 ||
| OCT3 | int(3) | | | 0 ||
| OCT4 | int(3) | | MUL | 0 ||
| SID | int(11) | | MUL | 0 ||
| AVAIL | int(1) | | | 0 ||
+---+-+--+-+-++
This is huge waste of space
To store OCT1..4 you use INT. INT always occupies 4 bytes no matter how it is
declared (3) is display size only in your case.
AVAIL also uses 4 bytes consider using ENUM() type
Step1. alter all OCT to TINYNT UNSIGNED - 1 byte range 0..255. Exactly what
you need.
This way you will save 4*3 = 12 bytes per row
Step2. Depending on your setup you might consider using single column: ip INT
UNSIGNED - 4 bytes.
Read about INET_NTOA() INET_ATON() functions and bitwise operators.
cut
--
Terra
sysAdmin
FutureQuest, Inc.
http://www.FutureQuest.net
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--
Best regards
--
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
___/ www.mysql.com M: +359 88 231668
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: funny truncate problem
Hi Mihail Manolov wrote: Hey guys, did you spot this problem? mysql select truncate(199.20,2); ++ | truncate(199.20,2) | ++ | 199.19 | ++ 1 row in set (0.00 sec) Bug? No. Feature :) This is described in manual at: http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#IDX1285 This is common problem with foalting point arithmetics.Try following and you will understand the problem better: mysql select truncate(199.20,10); +-+ | truncate(199.20,10) | +-+ | 199.19 | +-+ -- Best regards -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com M: +359 88 231668 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL load problems - gcc-2.96?
Hello, Mike Robinson wrote: Hello to all, I've had some severe issues on redhat-7.x boxes and mysql compiled from source with gcc-2.96 much like the warning states on the download page. On these boxes, more than say 20-25 concurrent or near-concurrent connects produced unexpected results with PHP4. cut There are known problems with gcc-2.96 which comes with RH 7.2 distribution. There is disclaimer about it at www.mysql.com/downloads/mysql-3.23.html as you noticed. Similar problems occur with different applications, but it seems that updated gcc-2.96 available from update.redhat.com are more stable. Unfortunaely these problems are very hard to diagnose :( They are not easily repeatable. Recently RedHat released 7.3 which comes with gcc-2.96-110 Note that 110! It is just couple of days around so nothing can be said about it yet, but let's hope RedHat team fixed these problems. Can I ask you to provide more info about your setup? Especially if problem persists. [EMAIL PROTECTED] is better place for such issues. -- Best regards -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com M: +359 88 231668 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: NOW()
Hi, Dave wrote: cut Dim intWeekID objRSWeekID.open SELECT WeekID FROM WeekControl _ WHERE WeekStart NOW() AND WeekEnd NOW(), objConn intWeekID = objRSWeekID(WeekID) This fails to bring back a value for WeekID. Just want to mention couple of things: 1. storing both WeekStart and WeekEnd is redundant unless you want Weeks != 7 days long MySQL will accept following where clause: ... WHERE NOW() BETWEEN WeekStart AND WeekStart + INTERVAL 7 DAY; Data exists as : WeekStart 2002-05-13 00:00:01 WeekEnd 2002-05-19 23:59:59 2. This is wrong assumption because when you issue NOW() at midnight it will contain 00:00:00 and will fall out of any of Your weeks :) In mine above example WeekStart can be DATE type and it will still give you correct result. Read more about MySQL Date/Time functions at http://www.mysql.com/doc/D/a/Date_and_time_functions.html Having functions like WEEK(date) and TO_DAYS() you might find you don't need to store even WeekStart. Questions like Given NOW() find WeekStart and WeekEnd can be answered in single statement in MySQL :) -- Best regards -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com M: +359 88 231668 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: moving databases question...
Hi, Richard Idalski wrote: I have before me what seems to be a very daunting task to someone with my limited SQL knowledge. I'm upgrading our ad server which runs on MySQL 3.22.32 to a newer machine and MySQL 3.23.39. Right now there are 16GB worth of databases that need to be transferred over, and the old server lacks enough hard disks space to even do a dump on one of those tables. So is there a way I can do a mysqldump directly from the old machine to the new one over the network? And if I can or if I'm offered a better way to do this, will the difference in MySQL version affect anything? Like all commandline tools mysqldump also has option -h host mysqldump -h oldhost mysqldump --help to see all it's options http://www.mysql.com/doc/m/y/mysqldump.html -- Best regards -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com M: +359 88 231668 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Bug or error in SQL syntax - Specifying and using a user variableinside a single select query
Hi, Gregg Graubins wrote: Gregg, Monday, May 06, 2002, 6:57:55 PM, you wrote: GG Upon reading into the user variables section of the mysql manual, I cut cut According to the manual from the above link, where it states: cut mysql SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3; +--+--+--+--+ | @t1:=(@t2:=1)+@t3:=4 | @t1 | @t2 | @t3 | +--+--+--+--+ |5 |5 |1 |4 | +--+--+--+--+ == mysql should be SET'ing the user variables in my query above. If mysql is processing the WHERE first and then the SELECT, shouldn't the user variables be set anyhow (even if the query returns an empty set)? For example: == mysql SELECT -annual_filing.*, -companies.name AS company_name, -YEAR(filing_date) AS filing_year, -@predictedYear := 1999 AS predictedYear, -@earliestYear := 1996 AS earliestYear, -@learnYear := 1998 AS learnYear - FROM -annual_filing, -companies - WHERE - annual_filing.company_id = companies.ds_id -AND - YEAR(filing_date) = @predictedYear -AND - YEAR(filing_date) = @earliestYear; Empty set (25.89 sec) mysql SELECT @predictedYear, @earliestYear, @learnYear; ++---++ | @predictedYear | @earliestYear | @learnYear | ++---++ | NULL | NULL | NULL | ++---++ 1 row in set (0.00 sec) == If I execute a separate SELECT statement beforehand (Or SET for that matter) setting the user variables then it works. However, I'm trying to use MyODBC to connect into the server with my data mining software and I can't perform multiple queries in this fashion (it expects me to only send one query). :( Perhaps there's a workaround? That is correct result. Imagine you have to process this statement manually. Think about order of execution. At 1st step WHERE clause must be processed to find matching rows At 2nd step evrything in SELECT part nust be processed Do you agree with above? If so it must be clear that in you case order in time will be: 1. WHERE ,.. AND YEAR(filing_date) = @predictedYear 2. SELECT (@predictedYear := 1999) That is why in Where clause your variable is undefined. The same reason prevents you from using grouping functions in WHERE clause like SELECT col ... WHERE col = MAX(col); So as Victoria already suggested you must initialize User variables Before select statement. Or just use common API techniques to generate SQL statement on the fly. I don't see any benefits of using User Variables in your case. Ofcourse you can 'initialize' each variable twice in every statement like: SELECT (@var1 := 1999) as ... WHERE YEAR(filing_date) = (@var1 := 1999) but don't believe it will save you any effort. -- Best regards -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com M: +359 88 231668 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LOAD DATA LOCAL error
Hi, Neil Bobstone wrote: Hi, cut I Have compile Mysql 3.23.49 with : --enable-local-infile But when I want to use LOAD DATA LOCAL its says : The used command is not allowed with this MySQL version cut --enable-local-infile affects client library libmysqlclient Can you check if client library you are using is installed from same source? - the one you compiled. It often happens that you have different files from different releases installed. Proper way to upgrade if you use source is: 1. cd new_src; ./configure ... your opts; make; 2. cd old_src; make uninstall 3. stop mysqld 4. cd new_src; make install 5. start mysqld make uninstall will clean all libraries, headers and binaries, but will not touch your datadir nor config files. (/etc/my.cnf /etc/rc.d if any) -- Best regards -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com M: +359 88 231668 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: display days between two dates?
Hello, Tim Carlson wrote: Hello, Newbie SQL person here. I am hoping to be able to do the following. Given two dates, I would like to display all of the days between them. So if I had the dates 2002-02-08 and 2002-02-12, I would like to have MySQL spit back 2002-02-08 2002-02-09 2002-02-10 2002-02-11 2002-02-12 Any way I can do this directly in MySQL without constructing a table? I've looked through the date maniplulation routines in the manual, but nothing jumps out at me. Any pointers would be appreciated. Suppose you have some table with column called date_col DATE. i.e. containing dates as above Now if your question is: How to select all rows where is true that date_col is between dates 2002-02-08 and 2002-02-12? You can use almost same spelling. (SQL is written to be close to natural english) mysql SELECT date_col FROM your_table WHERE date_col BETWEEN '2002-02-08' AND '2002-02-12'; will be valid statement :) -- Best regards -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com M: +359 88 231668 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: quota on mysql
HI, Praful Saijare wrote: Hi all, How do I go about applying user quota on mysql databases, so that I = can keep track of the disk space used by all the databases users. I am using RH linux 7.0 =20 Mysql 3.23.40 installed on /var/lib/mysql/ quota-2.00pre3-7 applied on /home partation Thanks in advance Praful Place user databases in their home dirs with some conventional names and make symlinks into mysql datadirs. Example: mkdir /home/user1/mysql_db chmod 700 /home/user1/mysql_db chown mysql home/user1/mysql_db ln -s /home/user1/mysql_db /var/lib/mysql/user1_db -- Best regards -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com M: +359 88 231668 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL to Excel ?
| 0 | 0 | 0 | 1 | | 2002-05-01 | 4| 0 | 0 | 0 | 1 | 0 | 1 | | 2002-05-01 | 5| 0 | 0 | 0 | 0 | 2 | 2 | | 2002-05-02 | 1| 1 | 0 | 0 | 0 | 0 | 1 | | 2002-05-02 | 2| 0 | 1 | 0 | 0 | 0 | 1 | ++--+---+---+---+---+---+-+ 10 rows in set (0.00 sec) Well w have redundant rows now - r_id is presented both as a column and a row - let's get rid of it remove from group by. I left it just to see result is correct Here is your final 'Magic query' mysql select d, sum(if(r_id = 1, 1, 0)) as q_id1, sum(if(r_id = 2, 1, 0)) as q_id2, sum(if(r_id = 3, 1, 0)) as q_id3, sum(if(r_id = 4, 1, 0)) as q_id4, sum(if(r_id = 5, 1, 0)) as q_id5, count(*) as q_total from foo group by d; ++---+---+---+---+---+-+ | d | q_id1 | q_id2 | q_id3 | q_id4 | q_id5 | q_total | ++---+---+---+---+---+-+ | 2002-04-30 | 1 | 3 | 1 | 1 | 0 | 6 | | 2002-05-01 | 1 | 1 | 0 | 1 | 2 | 5 | | 2002-05-02 | 1 | 1 | 0 | 0 | 0 | 2 | ++---+---+---+---+---+-+ 3 rows in set (0.00 sec) Are you satisfied? :) Detailed analisys of this technique can be found in a great (MUST read) article at: http://www.mysql.com/articles/wizard/index.html http://www.devshed.com/Server_Side/MySQL/MySQLWiz/page1.html -- Best regards -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com M: +359 88 231668 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: finding values in TableA not in TableB
Hi, Salada, Duncan wrote: cut select events_terms.* from events_terms left join events on events_terms.events_ID=events.events_ID where isnull(events.events_ID); Can anyone tell me if that looks like it should have the desired effect? It seems to, but I am going to eventually be using this to delete rows - not find them - so I need to be sure. Thanks, Duncan select events_terms.* from events_terms left join events on events_terms.events_ID=events.events_ID where events.events_ID IS NULL; ISNULL(test) is function equivalent to IF(test IS NULL, 'Some new value', test); Where test can be any valid expression -- Best regards -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com M: +359 88 231668 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: User Variables and Previous Row Question
Hi, Jasmin Bertovic wrote: cut For example; SELECT number as current_day, (number - prevnumber) as change_from_prev_day FROM TABLE ORDER BY DATE prevnumber is the reference that I need from the previous row. Am I missing something simple or do I have to do this outside of MYSQL? 1. Yes you are missing something very basic (not simple). Tables in Relational Database Model are defined as Sets of Items. I.e. there is no Internal order of table. Server is free to store and retrieve rows in any order. ORDER BY clause is applied After rows are retrieved and processed (row by row) If you try to tranlsate your Query into Unordered Set terms it will sounds like: For each member of set do something with it and After that sort the result according to ... But since Set has no order you can not say For each member use Prev member 2. In your case there might be solution :) But it is very specific. Assuming date column is Primary Key you can just join table to it self SELECT t1.date_col, t1.num, t2.date_col, t2.num) FROM yourtable AS t1, yourtable AS t2 WHERE t1.date_col = t2.date_col + INTERVAL 1 DAY; Above is just for your info - to see what is happening. Having all these columns you can: SELECT t1.num as current, (t1.num - t2.num) as diff FROM yourtable AS t1, yourtable AS t2 WHERE t1.date_col = t2.date_col + INTERVAL 1 DAY; The only trick is to deal with 1st date because there is no previous, but I will let this excercise to you :) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How get the columns that are between an interval?
Hi, Dra. Silvia Andreasi wrote: Hi, How can I get the columns between a given time interval? Something like: The patients admitted between january 25, 2000 and january 30, 2001 ?? I'm new to MySql and would like a suggestion from you... Best Regards Silvia SQL is close to natural english so let's rewrote your sentence a little. Assuming you have table patients and collumn addmitted - WHERE clause you need will be: select ... from patients WHERE admitted BETWEEN '2000-01-25' AND '2000-01-30'; -- Best regards -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com M: +359 88 231668 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SQL optimizer and OR - bug or misconcept?
Description: When WHERE clause contains OR mysql don't any keys and don't perorm validity check even. USE KEY directive does not change this. How-To-Repeat: create table t ( a int unsigned, b int unsigned, key(a), key(b)); insert about 2000 random rows select count(*) from t; 2048 1. Check for exisiting values: mysql explain select a,b from t where b = 9258279; +---+--+---+--+-+---+--++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+---+--++ | t | ref | b | b| 5 | const | 48 | where used | mysql explain select a,b from t where b = 7973452; | t | ref | b | b| 5 | const | 69 | where used | mysql explain select a,b from t where a = 9258279 or b = 7973452; | t | ALL | a,b | NULL |NULL | NULL | 2048 | where used | mysql explain select a,b from t use key (a) where a = 9258279 or b = 7973452; | t | ALL | a,b | NULL |NULL | NULL | 2048 | where used | 2. Check for impossible (negative) values: mysql explain select a,b from t where a = -20; | t | ref | a | a| 5 | const |1 | where used | mysql explain select a,b from t where a = -20 or b = -10; | t | ALL | a,b | NULL |NULL | NULL | 2048 | where used | mysql explain select a,b from t use key (a) where a = -20 or b = -10; | t | ALL | a,b | NULL |NULL | NULL | 2048 | where used | Regardless of index schema - i used simplest example. For 1. I can agree that optimizer can decide to scan all table under certain conditions, but for 2. I think range checks must always apply. Negative values for unsigned, IS NULL for column defined not null and so on. mysql alter table t modify a int unsigned not null; mysql explain select a,b from t where a is null or b = -10; | t | ALL | a,b | NULL |NULL | NULL | 2048 | where used | In this case optimizer must notice primary key can't be null so (a is null) can be skipped from where clause Tested with several 3.23.x versions, 4.00, 4.0.1 Fix: Workaround in 4.x - Use UNION select ... from t where a = 'x' union select ... where b = x; mysql explain select a,b from t where a = -20 union select a,b from t where b = -10; | t | ref | a | a| 5 | const |1 | where used | | t | ref | b | b| 5 | const |1 | where used | Workaround in 3.23.x - Use temporary table mysql create temporary table tmp select a,b from t where a = -20; mysql insert into tmp select a,b from t where b = -10; mysql select * from tmp; 0 rows mysql drop table tmp; lock/unlock table may be added. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
