SJIS character including 7 bit ASCII code
When using SJIS charset, there is a problem when the second byte of a character matches with 7bit ASCII code. The character codes including \(\x5C) in their second byte need caution. The range of SJIS code is [\x81-\x9F\xE0-\xFC][\x40-\x7E\x80-\xFC] Please see my test result at http://epx.timedia.co.jp/mysql/sjis-2ndbyte_html The problem was solved when I set sjis to launch mysql client command mysql --default-character-set=sjis or in my.cnf [mysql] default-character-set = sjis Without above setting, MySQL didn't handle the character correctly even if I typed the following settings: set names sjis set character set sjis This is the explanation in the English manual: --- http://dev.mysql.com/doc/mysql/en/Character_sets.html 5.7.1 The Character Set Used for Data and Sorting You can force the client to use specific character set as follows: [client] default-character-set=character-set-name This is normally unnecessary, however. --- But it seems that I have to set this option to use SJIS character set. Hirofumi Fujiwara (Tokyo JAPAN) enjoy JAVA and Puzzle World [EMAIL PROTECTED]http://www.pro.or.jp/~fuji/index-eng.html [EMAIL PROTECTED] Puzzle Japanhttp://www.puzzle.jp/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL and Unicode
Hopefully this will be the last snag...least till the next one :) I'm having a problem setting the charset to unicode (utf-8). If I understood what I read I can set each table to support a charset. But what I want to do is set the default charset for a database to be utf-8. Is this possible? If so how? In any case, I need to be able to store unicode data and retrieve it. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANN: Advanced Data Generator 1.0.4 released
Dear ladies and gentlemen, Upscene Productions is happy to announce a new version of the database developer tool: Advanced Data Generator (version 1.0.4) A fast test-data generator tool that comes with a library of real-life data, presets and much more. This new release consists of four versions: - Pro: ADO and ODBC connectivity - InterBase Edition - Firebird Edition - MySQL Edition More info and a 30-day trial version on www.upscene.com Pricing information available on www.upscene.com/purchase.htm#adg Recent changes: V1.0.4 - - New: street names for the US, UK, Australia, Germany and The Netherlands - Fixed: Save to File dialog in project screen didn't work - Fixed: database list didn't refresh on db registration or project changes - Fixed: canvas does not allow drawing threading error - Fixed: Abort is no longer an error in the log, but a warning - Fixed: (Pro) delimited identifier problem with, for example, MSSQL - Fixed: (MySQL) added ` to object names to avoid errors With regards, Martijn Tonies Upscene Productions - Database Tools for Developers http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Gripe with MySQL
Stormblade said: Ok. Love MySQL and I will be using it for my personal use and recommending it to clients as a lower cost alternative. I've only been using it for a very short time but there one major gripe I have with it and I believe it's just a design thing. MySQL seems to put the burden of error checking and such on the client. - All fields have a default value even when I don't tell it to? - Allow Null will only result in an error if I explicitly try to set the field to null. Date fields allow invalid dates: -00-00 Trailing spaces are silently discarded (fixed in 5) Decimal fields are silently truncated if the inserted value is too long etc. http://sql-info.de/mysql/gotchas.html has a nice list. These are the two things that I really dislike. I think it's a poor design to rely on clients only for error checking. MySQL supports foreign keys. To some extend. It is not enforced that the parent record is unique, which is a requirement with foreign keys according to the SQL spec. I've only been working with MySQL for a little bit so this is just my first impressions. I'll be very happy to be told I'm wrong or that future updates (5.0 perhaps) will change some of the things I've mentioned. They will eventually (not in 5). After all, if you read the todo list carefully you will see that it says they strive to fully supporting the SQL spec. Naturally this will break backwards compatibility in many areas, so I am very curious how this will be implemented. Relying on clients for database integrity is a bad idea in my experience. -- Then you choose the wrong database. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlclient missing
Hello. I have installed binary version for aix 5.2 from www.mysql.net and when i try to compile cyrus-sasl with mysql support it don't find mysqlclient libraries. I use --with-mysql=/usr/local/mysql/ to build but i get: checking for mysql_select_db in -lmysqlclient... no configure: WARNING: MySQL library mysqlclient does not work Doesn't it include client libraries ? Thanks.
Re: MySQL and Unicode
Stormblade [EMAIL PROTECTED] wrote: Hopefully this will be the last snag...least till the next one :) I'm having a problem setting the charset to unicode (utf-8). If I understood what I read I can set each table to support a charset. But what I want to do is set the default charset for a database to be utf-8. Is this possible? If so how? Yes, it's possible from version 4.1.0. CREATE DATABASE db_name DEFAULT CHARACTER SET utf8; http://dev.mysql.com/doc/mysql/en/CREATE_DATABASE.html In any case, I need to be able to store unicode data and retrieve it. -- 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]
Mysqlclient is missing from binary version Aix 5.2
Hello. I have installed binary version 4.0 for aix 5.2 from www.mysql.com and when i try to compile cyrus-sasl with mysql support it don't find mysqlclient libraries. I use --with-mysql=/usr/local/mysql/ to build but i get: checking for mysql_select_db in -lmysqlclient... no configure: WARNING: MySQL library mysqlclient does not work Doesn't it include client libraries ? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error importing date
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I encounter this error when importing database from one mysql server into another: the date imported is wrong. The type is timestamp. In the dump file, the date is : 0404121921, but when imported, it becomes: 0004041219. Could anybody pls give me direction why is that and how to correct it? Thanks. The old mysql server is v. 4.0.13, and the new is 4.0.15. One more info, I notice that the timezone is different, in the old: JAVT, and in the new: WIT. - -- Fajar Priyanto | Reg'd Linux User #327841 | http://linux.arinet.org 15:47:50 up 7:38, Mandrake Linux release 9.2 (FiveStar) for i586 public key: https://www.arinet.org/fajar-pub.key -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFAhOR7kp5CsIXuxqURAqwWAJ9+rrT8JtL5418kjNfFfb92lPU5PACghTvC yirB75rqkc1vrPMtD0pos9U= =eosZ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB Load Problem
Hi, Thanks Dathan for your response. So far I have upgraded the mysql to 4.0.18, this supports O_DIRECT as my 4.0.13 did not. I increased my buffer pool by another 256 Meg and so far I have not seen any change in performance. I've looked at the SHOW INNODB STATUS (pasted further down), my buffer pool hit rate is constantly at 1000 / 1000, what does this tell you ? Regarding your other points, a slow query is this, only 1 second though but you mentioned count(*) as bad. # Time: 040420 11:10:09 # [EMAIL PROTECTED]: web[web] @ [10.168.78.207] # Query_time: 1 Lock_time: 0 Rows_sent: 3310 Rows_examined: 185723 select locale,ggd,from,to,supplier,date,count(*) as count from cache group by locale,ggd,from,to,supplier,date having count 1; This query is to see if there are duplicate records, I'm not sure how else I could do this and it only runs once every 10 minutes. The indexes look fine on all other queries. The disk layout is probably not good, unfortunately these are standard built single drive machines by our tech services department and it too bigger hassle for them to do something different for me :( The disk layout is Filesystem 1k-blocks Used Available Use% Mounted on /dev/hda2 34218636 22979948 9500456 71% / /dev/hda1 101089 19894 75976 21% /boot none 2005632 0 2005632 0% /dev/shm The data being on hda2. How can I determine if the problem is disk bound ? If I can get some hard evidence of this then I can go to our tech department and get them to build me a decent box. Many Thanks. Marvin. = 040420 11:15:32 INNODB MONITOR OUTPUT = Per second averages calculated from the last 15 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 245688, signal count 208343 Mutex spin waits 10498150, rounds 63549544, OS waits 69764 RW-shared spins 113294, OS waits 50819; RW-excl spins 83135, OS waits 26235 TRANSACTIONS Trx id counter 0 464704697 Purge done for trx's n:o 0 464704447 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 22455, OS thread id 19320851 MySQL thread id 38508, query id 2467002 localhost root SHOW INNODB STATUS ---TRANSACTION 0 0, not started, process no 17652, OS thread id 36874 MySQL thread id 1, query id 2431662 192.168.35.181 web ---TRANSACTION 0 464704694, ACTIVE 0 sec, process no 27189, OS thread id 38580247 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 3 lock struct(s), heap size 320, undo log entries 39 MySQL thread id 73569, query id 2467003 10.168.77.231 web update insert into negotiated_classes_cache set id=108245613219642041, route_id=3, segment_id=1, class='C', num='4' FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 4831 OS file reads, 41891 OS file writes, 40144 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 20.53 writes/s, 18.13 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 0, seg size 2, 364 inserts, 364 merged recs, 5 merges Hash table size 5312557, used cells 469072, node heap has 483 buffer(s) 43.86 hash searches/s, 3380.24 non-hash searches/s --- LOG --- Log sequence number 295 378143163 Log flushed up to 295 378138460 Last checkpoint at 295 42216323 0 pending log writes, 0 pending chkp writes 39690 log i/o's done, 17.27 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 1469851048; in additional pool allocated 4584832 Buffer pool size 81920 Free buffers 56299 Database pages 25138 Modified db pages 18737 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 5113, created 20025, written 7489 0.00 reads/s, 9.27 creates/s, 22.80 writes/s Buffer pool hit rate 1000 / 1000 -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue Main thread process no. 17648, id 28680, state: sleeping Number of rows inserted 2183633, updated 1997, deleted 945139, read 2006850 1482.37 inserts/s, 1.20 updates/s, 0.00 deletes/s, 653.56 reads/s END OF INNODB MONITOR OUTPUT -Original Message- From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED] Sent: 19 April 2004 23:35 To: 'Marvin Wright'; [EMAIL PROTECTED] Subject: RE: InnoDB Load Problem -Original Message- From: Marvin Wright
RE: InnoDB Load Problem
This query is to see if there are duplicate records, I'm not sure how else I could do this and it only runs once every 10 minutes. Why don't you put a unique index on locale,ggd,from,to,supplier,date so you won't have to play that request any more ... How can I determine if the problem is disk bound ? iostat -x 1 The right column of the display gives you the busy state (in percent) of the disk. If I can get some hard evidence of this ... Try to set innodb_flush_log_at_trx_commit to 0 (the default value is 1) Marc. -Message d'origine- De : Marvin Wright [mailto:[EMAIL PROTECTED] Envoyé : mardi 20 avril 2004 12:45 À : Dathan Vance Pattishall; Marvin Wright; [EMAIL PROTECTED] Objet : RE: InnoDB Load Problem Hi, Thanks Dathan for your response. So far I have upgraded the mysql to 4.0.18, this supports O_DIRECT as my 4.0.13 did not. I increased my buffer pool by another 256 Meg and so far I have not seen any change in performance. I've looked at the SHOW INNODB STATUS (pasted further down), my buffer pool hit rate is constantly at 1000 / 1000, what does this tell you ? Regarding your other points, a slow query is this, only 1 second though but you mentioned count(*) as bad. # Time: 040420 11:10:09 # [EMAIL PROTECTED]: web[web] @ [10.168.78.207] # Query_time: 1 Lock_time: 0 Rows_sent: 3310 Rows_examined: 185723 select locale,ggd,from,to,supplier,date,count(*) as count from cache group by locale,ggd,from,to,supplier,date having count 1; This query is to see if there are duplicate records, I'm not sure how else I could do this and it only runs once every 10 minutes. The indexes look fine on all other queries. The disk layout is probably not good, unfortunately these are standard built single drive machines by our tech services department and it too bigger hassle for them to do something different for me :( The disk layout is Filesystem 1k-blocks Used Available Use% Mounted on /dev/hda2 34218636 22979948 9500456 71% / /dev/hda1 101089 19894 75976 21% /boot none 2005632 0 2005632 0% /dev/shm The data being on hda2. How can I determine if the problem is disk bound ? If I can get some hard evidence of this then I can go to our tech department and get them to build me a decent box. Many Thanks. Marvin. = 040420 11:15:32 INNODB MONITOR OUTPUT = Per second averages calculated from the last 15 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 245688, signal count 208343 Mutex spin waits 10498150, rounds 63549544, OS waits 69764 RW-shared spins 113294, OS waits 50819; RW-excl spins 83135, OS waits 26235 TRANSACTIONS Trx id counter 0 464704697 Purge done for trx's n:o 0 464704447 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 22455, OS thread id 19320851 MySQL thread id 38508, query id 2467002 localhost root SHOW INNODB STATUS ---TRANSACTION 0 0, not started, process no 17652, OS thread id 36874 MySQL thread id 1, query id 2431662 192.168.35.181 web ---TRANSACTION 0 464704694, ACTIVE 0 sec, process no 27189, OS thread id 38580247 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 3 lock struct(s), heap size 320, undo log entries 39 MySQL thread id 73569, query id 2467003 10.168.77.231 web update insert into negotiated_classes_cache set id=108245613219642041, route_id=3, segment_id=1, class='C', num='4' FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 4831 OS file reads, 41891 OS file writes, 40144 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 20.53 writes/s, 18.13 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 0, seg size 2, 364 inserts, 364 merged recs, 5 merges Hash table size 5312557, used cells 469072, node heap has 483 buffer(s) 43.86 hash searches/s, 3380.24 non-hash searches/s --- LOG --- Log sequence number 295 378143163 Log flushed up to 295 378138460 Last checkpoint at 295 42216323 0 pending log writes, 0 pending chkp writes 39690 log i/o's done, 17.27 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 1469851048; in additional pool allocated 4584832 Buffer pool size 81920 Free buffers 56299 Database pages 25138 Modified db pages 18737 Pending reads 0 Pending writes: LRU 0, flush list 0, single page
RE: when 4.1.2 release
Is there a planned release date for the production version of 4.1 x? I'd love to be able to use sub-queries but I don't want to use a non-production version on a production database. Jack -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Monday, April 19, 2004 6:30 PM To: electroteque; Victoria Reznichenko; [EMAIL PROTECTED] Subject: RE: when 4.1.2 release At 7:11 +1000 4/20/04, electroteque wrote: Huh as in production ? Surely you jest. 4.1.1 was an alpha release. It's unlikely that 4.1.x would go straight to production-level in a single step. I believe that 4.1.2 will also be an alpha release. That said, I encourage you to go ahead and try it out. It has lots of bugfixes and new stuff: http://dev.mysql.com/doc/mysql/en/News-4.1.2.html -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 5:52 AM To: [EMAIL PROTECTED] Subject: Re: when 4.1.2 release Marek Lewczuk [EMAIL PROTECTED] wrote: Hello, when do you plan to release 4.1.2 version ? It will be released in several weeks. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: BETWEEN
Hello: I am trying to measure the difference between a single insert statement of 10,000 rows and 10,000 insert statements. It is easy for me to see the single statement takes about 2 seconds. However I can come up with no good way to get the total time for individual statements. Can anyone provide a suggestion? Thanks in advance. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Apologies: double message
Sory for the double postings, I was getting an error from the MySQL list manager and didn't think they had been posted. I also found the solution to my problem... Regards, Dan Cumpian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: BETWEEN
Hello: I am trying to measure the difference between a single insert statement of 10,000 rows and 10,000 insert statements. It is easy for me to see the single statement takes about 2 seconds. However I can come up with no good way to get the total time for individual statements. Can anyone provide a suggestion? Thanks in advance. Try super-smack. It works great for this type of testing. http://jeremy.zawodny.com/mysql/super-smack/ Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -- 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: InnoDB Load Problem
Hi, To put the unique index on like you suggest is fine for this table but this table is just the top level of a hierarchy. table a has 1 record table b has 100's of records linked to 1 table a record table c has 100's of records linked to 1 table b record All the records in table b and c would need to be updated/deleted for a new record. It think this would be very time consuming, and the clients that are inserting are public internet users therefore I'd rather not slow these down. under load iostat -x 1 gives me this avg-cpu: %user %nice%sys %idle 38.500.00 18.00 43.50 Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/s avgrq-sz avgqu-sz await svctm %util /dev/hda 104.00 552.00 31.00 39.00 1088.00 4728.00 544.00 2364.00 83.0962.20 1174.29 141.43 99.00 /dev/hda10.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda2 104.00 552.00 31.00 39.00 1088.00 4728.00 544.00 2364.00 83.0982.20 1174.29 75.71 53.00 /dev/hda30.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda50.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice%sys %idle 44.500.00 16.50 39.00 Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/s avgrq-sz avgqu-sz await svctm %util /dev/hda 6.00 838.00 1.00 58.00 64.00 7168.0032.00 3584.00 122.58 3.30 393.22 169.49 100.00 /dev/hda10.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda26.00 838.00 1.00 58.00 64.00 7168.0032.00 3584.00 122.5823.30 393.22 23.73 14.00 /dev/hda30.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda50.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice%sys %idle 2.000.000.00 98.00 Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/s avgrq-sz avgqu-sz await svctm %util /dev/hda 195.00 162.00 58.00 8.00 2080.00 1392.00 1040.00 696.00 52.6144.40 740.91 128.79 85.00 /dev/hda10.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda2 195.00 162.00 58.00 8.00 2080.00 1392.00 1040.00 696.00 52.6164.40 740.91 151.52 100.00 /dev/hda30.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda50.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice%sys %idle 8.000.003.00 89.00 Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/s avgrq-sz avgqu-sz await svctm %util /dev/hda 174.00 0.00 60.00 5.00 1856.008.00 928.00 4.00 28.6850.00 1235.38 147.69 96.00 /dev/hda10.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda2 174.00 0.00 60.00 5.00 1856.008.00 928.00 4.00 28.6870.00 1235.38 153.85 100.00 /dev/hda30.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda50.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice%sys %idle 29.500.00 16.50 54.00 Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/s avgrq-sz avgqu-sz await svctm %util /dev/hda 102.00 71.00 40.00 6.00 1088.00 616.00 544.00 308.00 37.04 5.60 671.74 193.48 89.00 /dev/hda10.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda2 102.00 71.00 40.00 6.00 1088.00 616.00 544.00 308.00 37.0425.60 671.74 163.04 75.00 /dev/hda30.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda50.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice%sys %idle 57.500.00 20.00 22.50 Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/s avgrq-sz avgqu-sz await svctm %util /dev/hda 0.00 398.00 0.00 28.000.00 3408.00 0.00 1704.00 121.71 42949657.76 171.43 357.14 100.00 /dev/hda10.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda20.00 398.00 0.00 28.000.00 3408.00 0.00 1704.00 121.71 4.80 171.43 14.29 4.00 /dev/hda30.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda50.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice%sys %idle 39.000.009.50 51.50
Query that crashes MySQL
I'm using mysql-standard-4.1.0-alpha and I've found a query that consistently crashes the server. The form of the query is this: (SELECT DISTINCT g.id FROM T1 b, T2 g WHERE g.id IN (0) ) UNION ( SELECT DISTINCT b.id FROM T1 b, T2 g WHERE g.id IN (0) ) LIMIT 1; If I change the IN to '=' or remove the DISTINCTs then it no longer crashes. I've run this query on Intel and Power PC architectures and the result is the same. If I run in safe mode, the query no longer causes a crash. Unfortunately other parts of my application then slow down unacceptably. In order to post a bug report I need to winnow down my tables and data to the minimum, which will take some effort. So before I do this, I'd like to ask if anyone else has seen this problem and if there is a fix. Jim -- James Fryer / [EMAIL PROTECTED] / [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: BETWEEN
Max: Thanks for the tip. Unfortunately I am not using a FreeBSD environment. My options are to either run a WinXP client remotely or to run something Linux based in a terminal emulator (Putty). Any suggestions would be appreciated. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Max Michaels [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 7:07 AM To: 'Boyd E. Hemphill'; 'mysql' Subject: RE: BETWEEN Hello: I am trying to measure the difference between a single insert statement of 10,000 rows and 10,000 insert statements. It is easy for me to see the single statement takes about 2 seconds. However I can come up with no good way to get the total time for individual statements. Can anyone provide a suggestion? Thanks in advance. Try super-smack. It works great for this type of testing. http://jeremy.zawodny.com/mysql/super-smack/ Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -- 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: Gripe with MySQL
On Tue, 20 Apr 2004 09:48:11 +0200 (CEST), Jochem van Dieten wrote: Stormblade said: Ok. Love MySQL and I will be using it for my personal use and recommending it to clients as a lower cost alternative. I've only been using it for a very short time but there one major gripe I have with it and I believe it's just a design thing. MySQL seems to put the burden of error checking and such on the client. - All fields have a default value even when I don't tell it to? - Allow Null will only result in an error if I explicitly try to set the field to null. Date fields allow invalid dates: -00-00 Trailing spaces are silently discarded (fixed in 5) Decimal fields are silently truncated if the inserted value is too long etc. http://sql-info.de/mysql/gotchas.html has a nice list. Thanks for the link. That's very informative. These are the two things that I really dislike. I think it's a poor design to rely on clients only for error checking. MySQL supports foreign keys. To some extend. It is not enforced that the parent record is unique, which is a requirement with foreign keys according to the SQL spec. Aye but at least it doesn't allow me to add/delete if it'll create an orphan. Relying on clients for database integrity is a bad idea in my experience. -- Then you choose the wrong database. Jochem Well, choosing a database involves more than just not liking a particular approach. Their approach in this case only means that I would not recommend using MySQL for any large scale applications. Like the situation I described earlier. If you are going to have a database that will be centralized for the company and will have many different clients accessing it, I would tell them not to use MySQL. It would be a bad idea IMO. Not that it couldn't work of course. But for small projects it is find. It's cost effective and it does the job well. Like the project I'm currently working on. Just a small web site. Only one web app will be accessing the database and I wrote it. And I have another project coming up, similar in size that I will be using MySQL for as well. So it's not that I chose the wrong database. Just that I believe their design choices make the database the wrong choice for certain situations and applications. I would love to see MySQL add more features that allowed the database to do more of the work when it comes to integrity but until it does I simply will not use it for situations where that could be a problem. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and Unicode
On Tue, 20 Apr 2004 11:32:22 +0300, Victoria Reznichenko wrote: Stormblade [EMAIL PROTECTED] wrote: Hopefully this will be the last snag...least till the next one :) I'm having a problem setting the charset to unicode (utf-8). If I understood what I read I can set each table to support a charset. But what I want to do is set the default charset for a database to be utf-8. Is this possible? If so how? Yes, it's possible from version 4.1.0. CREATE DATABASE db_name DEFAULT CHARACTER SET utf8; http://dev.mysql.com/doc/mysql/en/CREATE_DATABASE.html Oh boy, that will be a pain. I use a GUI (Navicat and EMS MySQL Manager) and so far have not seen a way for me to make it create a table with a default character set. Nor even change it. Well at least I know it's possible. Thanks. In any case, I need to be able to store unicode data and retrieve it. -- 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 -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LIST function?
Hi, When using aggregate functions, I know you can retrieve the MAX, MIN, SUM, etc from all the values in your specific group from the GROUP BY. Is there any function to simply return a list of the values in the group? e.g. SELECT id, LIST(buddy_id) FROM buddies GROUP BY id which would return: idbuddy_id 1 1,3,5 2 2,3 I cant see why this wouldnt be possible but I havent found anything yet. Thanks for any help Andy Hall.
RE: Gripe with MySQL
Hi, ENUM columns can also trip you up if you insert a value outside of the ENUM; an empty string is inserted instead. This is documented behaviour (mysql.com seems to be going slowly though, so can't dig a reference right now), even if it is undesired in some cases. Thanks, Mike -Original Message- From: news [mailto:[EMAIL PROTECTED] Behalf Of Stormblade Sent: 20 April 2004 01:42 To: [EMAIL PROTECTED] Subject: Gripe with MySQL Ok. Love MySQL and I will be using it for my personal use and recommending it to clients as a lower cost alternative. I've only been using it for a very short time but there one major gripe I have with it and I believe it's just a design thing. MySQL seems to put the burden of error checking and such on the client. - All fields have a default value even when I don't tell it to? - Allow Null will only result in an error if I explicitly try to set the field to null. These are the two things that I really dislike. I think it's a poor design to rely on clients only for error checking. MySQL supports foreign keys. This is good because the database handles referential integrity. But it shouldn't stop there. I should also be able to tell the database not to allow a field to be empty/null and it should not put anything in there that I have not told it to. One scenario I can think of is this. My company uses MySQL as it's database. Different departments implement various interfaces to this database. All it would take is ONE client to have ONE bad SQL and although the insert works (Thanks to default values being put in) the data is not valid. I've only been working with MySQL for a little bit so this is just my first impressions. I'll be very happy to be told I'm wrong or that future updates (5.0 perhaps) will change some of the things I've mentioned. Relying on clients for database integrity is a bad idea in my experience. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- 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]
Error when dumping DBs.
I am getting the following error when trying to dump my DBs. /usr/bin/mysqldump: Got error: 1103: Incorrect table name '/home/jr/backups/20040420/wcp.sql' when doing LOCK TABLES Version is: 4.0.18-standard These DBs where on another box running the same version. MySQL was shutdown on both the old and new box, the DB directory structure was copied to a new server. MySQL was restarted and everything works fine as far as I can tell except I can't dump the DBs. Any help would be appreciated. Thanks in advance! JR -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIST function
Never mind, I found the GROUP_CONCAT function but I am still using v. 3.23.37 so thats that. Looks like I'll be using PHP, unless anyone has a workaround of some kind? Thanks
Re: Query that crashes MySQL
James Fryer [EMAIL PROTECTED] wrote: I'm using mysql-standard-4.1.0-alpha and I've found a query that consistently crashes the server. The form of the query is this: (SELECT DISTINCT g.id FROM T1 b, T2 g WHERE g.id IN (0) ) UNION ( SELECT DISTINCT b.id FROM T1 b, T2 g WHERE g.id IN (0) ) LIMIT 1; If I change the IN to '=' or remove the DISTINCTs then it no longer crashes. I've run this query on Intel and Power PC architectures and the result is the same. If I run in safe mode, the query no longer causes a crash. Unfortunately other parts of my application then slow down unacceptably. In order to post a bug report I need to winnow down my tables and data to the minimum, which will take some effort. So before I do this, I'd like to ask if anyone else has seen this problem and if there is a fix. Since 4.1.0 many bugs was fixed. Test your query on version 4.1.1 and if you get the same result, provide structure of tables and some data. I tested your query on version 4.1.2 with my test data and all worked like a charm. -- 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: Query that crashes MySQL
What does the explain look like for this query? -Original Message- From: James Fryer To: [EMAIL PROTECTED] Sent: 4/20/04 8:08 AM Subject: Query that crashes MySQL I'm using mysql-standard-4.1.0-alpha and I've found a query that consistently crashes the server. The form of the query is this: (SELECT DISTINCT g.id FROM T1 b, T2 g WHERE g.id IN (0) ) UNION ( SELECT DISTINCT b.id FROM T1 b, T2 g WHERE g.id IN (0) ) LIMIT 1; If I change the IN to '=' or remove the DISTINCTs then it no longer crashes. I've run this query on Intel and Power PC architectures and the result is the same. If I run in safe mode, the query no longer causes a crash. Unfortunately other parts of my application then slow down unacceptably. In order to post a bug report I need to winnow down my tables and data to the minimum, which will take some effort. So before I do this, I'd like to ask if anyone else has seen this problem and if there is a fix. Jim -- James Fryer / [EMAIL PROTECTED] / [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]
Multi-user / transactions question..
Hi everyone, Some quick background before I get to my question: I have developed a fantasy football game that is based on an auction style format. In any given game there are a consistant number of players, usually 10 or 12. There can be more than one game taking place at the same time on the site. All 12 players are trying to put together the best team they can with a limited amount of fictional money, in this case $200. A typical auction lasts around 90 minutes, and consists of a whole lot of furious refreshing of the auction board by the participants since each player only has a 2 minute clock. It might be easiest to demonstrate what this looks like by copying some data from an actual auction board: *at the top there is a refresh button $1 Bid On: L Tomlinson, RB SDC High Bid: You! Timer: 0m 25s $2 Bid On: P Holmes, RB KCC High Bid: You! Timer: 0m 35s $2 Bid On: C Portis, RB WAS High Bid: You! Timer: 0m 38s $2 Bid On: A Green, RB GBP High Bid: You! Timer: 0m 42s $2 Bid On: D McAllister, RB NOS High Bid: You! Timer: 0m 45s . . . When the player at the top goes to Timer: 0m 0s whoever has the high bid is awarded that player. Then a player is pulled off of a list that the auctioner who nominated that player created, and that player has a full clock and goes to the bottom of the list. The question: Since there is so much clicking going on, and my php code and mysql calls (including the transactions that decide which new player should be nominated to the board) obviously happen with each user click, is there any way to hide the guts of the work so that when they click refresh all they are doing is viewing the state of the respective tables at that time. In other words, I don't need 4 people simultaneously calling the functions that decide which player should be nominated next, awarded the player that was won to the necessary roster, deducting money, etc. With four people calling the function at once, I am forced to lock up the rows and rollback 3 of the transactions. Is there any way to have them all call the same function that only gets executed once? Another related question would be, would it make sense to create a cronjob that would go through the behind the scenes functions every one or two seconds, thereby spreading the load and sparing the end user from being involved in activating the use of the functions? Or is the natural burstable patterns that human users create actually easier on the server and a more natural way of doing things. I know I've thrown alot of stuff out there for you guys, but I'm just wondering if there is a better way from how I am doing things at present. Any help or advice would be greatly appreciated. Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: error importing date
How are you exporting/importing the data? -Original Message- From: Fajar Priyanto To: [EMAIL PROTECTED] Sent: 4/20/04 3:51 AM Subject: error importing date -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I encounter this error when importing database from one mysql server into another: the date imported is wrong. The type is timestamp. In the dump file, the date is : 0404121921, but when imported, it becomes: 0004041219. Could anybody pls give me direction why is that and how to correct it? Thanks. The old mysql server is v. 4.0.13, and the new is 4.0.15. One more info, I notice that the timezone is different, in the old: JAVT, and in the new: WIT. - -- Fajar Priyanto | Reg'd Linux User #327841 | http://linux.arinet.org 15:47:50 up 7:38, Mandrake Linux release 9.2 (FiveStar) for i586 public key: https://www.arinet.org/fajar-pub.key -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFAhOR7kp5CsIXuxqURAqwWAJ9+rrT8JtL5418kjNfFfb92lPU5PACghTvC yirB75rqkc1vrPMtD0pos9U= =eosZ -END PGP SIGNATURE- -- 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: LIST function?
At 14:44 +0100 4/20/04, Andy Hall wrote: Hi, When using aggregate functions, I know you can retrieve the MAX, MIN, SUM, etc from all the values in your specific group from the GROUP BY. Is there any function to simply return a list of the values in the group? GROUP_CONCAT()? Supported in MySQL 4.1 and up. e.g. SELECT id, LIST(buddy_id) FROM buddies GROUP BY id which would return: idbuddy_id 1 1,3,5 2 2,3 I cant see why this wouldnt be possible but I havent found anything yet. Thanks for any help Andy Hall. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to speed up a simple query? can anyone help with an ideea?
Hello! I have a simple query on a table of about 1,000,000 records... The table is optimized and the query is pretty simple at this moment... something like this select id,name,desc,cat,date from table where cat='12' however I need to order the results by date desc... I have indexes on both the cat and date (of type timestamp) fields however this operation is much more slowly when I used the order So the result is something like this select id,name,desc,cat,date from table where cat='12' takes 0.7 seconds select id,name,desc,cat,date from table where cat='12' order by date desc takes 2.4 seconds any ideea what I can do to help speeding up things? I was thinking that I should recreate the table daily (something like insert into temp select * from table order by date desc delete from table insert into table select * from temp ) and add the last records at the begining so the mysql database can retreieve the first records the ones added last because this is my purpose in fact But I was wondering if you have any other ideas of how I could do this because I am not to happy with such operations with such large tables... Thanks, Arthur -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and Unicode
At 9:38 -0400 4/20/04, Stormblade wrote: On Tue, 20 Apr 2004 11:32:22 +0300, Victoria Reznichenko wrote: Stormblade [EMAIL PROTECTED] wrote: Hopefully this will be the last snag...least till the next one :) I'm having a problem setting the charset to unicode (utf-8). If I understood what I read I can set each table to support a charset. But what I want to do is set the default charset for a database to be utf-8. Is this possible? If so how? Yes, it's possible from version 4.1.0. CREATE DATABASE db_name DEFAULT CHARACTER SET utf8; http://dev.mysql.com/doc/mysql/en/CREATE_DATABASE.html Oh boy, that will be a pain. I use a GUI (Navicat and EMS MySQL Manager) and so far have not seen a way for me to make it create a table with a default character set. Nor even change it. Um, what? If you specify a default character set when you create a database, any table created in that database will have the same character set by default. Is that not what you are asking? Well at least I know it's possible. Thanks. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to speed up a simple query?
Arthur Radulescu [EMAIL PROTECTED] wrote: I have a simple query on a table of about 1,000,000 records... The table is optimized and the query is pretty simple at this moment... something like this select id,name,desc,cat,date from table where cat='12' however I need to order the results by date desc... I have indexes on both the cat and date (of type timestamp) fields however this operation is much more slowly when I used the order So the result is something like this select id,name,desc,cat,date from table where cat='12' takes 0.7 seconds select id,name,desc,cat,date from table where cat='12' order by date desc takes 2.4 seconds any ideea what I can do to help speeding up things? I was thinking that I should recreate the table daily (something like insert into temp select * from table order by date desc delete from table insert into table select * from temp ) and add the last records at the begining so the mysql database can retreieve the first records the ones added last because this is my purpose in fact But I was wondering if you have any other ideas of how I could do this because I am not to happy with such operations with such large tables... Create composite index on (cat, date). Use EXPLAIN to see if MySQL uses index: http://dev.mysql.com/doc/mysql/en/EXPLAIN.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]
sql syntax error
I'm using amavisd-new -20030616p9, RH 3.0 ES and mysql 3.23.58-1 trying to do sql lookups for user prefs. I've done this before and have compared my sql statements and can't figure out the problem. When i start amavisd-new with the debug switch, here's what i get: # /usr/local/sbin/amavisd debug Error in config file /etc/amavisd.conf: syntax error at /etc/amavisd.conf line 829, near ' ORDER BY users.priority DESC '; Here are the lines from my /etc/amavisd.conf file: $sql_select_policy = 'SELECT *,users.vuid FROM users,policy_names'. ' WHERE (users.policy=policy_names.id) AND (users.username IN (%k))'. ' ORDER BY users.priority DESC '; Please help! Thanks in advance, Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LIST function?
This will do it... SELECT id, group_concat(distinct buddy_id) FROM buddies GROUP BY id Ed -Original Message- Hi, When using aggregate functions, I know you can retrieve the MAX, MIN, SUM, etc from all the values in your specific group from the GROUP BY. Is there any function to simply return a list of the values in the group? e.g. SELECT id, LIST(buddy_id) FROM buddies GROUP BY id which would return: idbuddy_id 1 1,3,5 2 2,3 I cant see why this wouldnt be possible but I havent found anything yet. Thanks for any help Andy Hall. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error importing date
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Victor, thanks for responding. I really need help here. I export/import the database using mysqldump (for exporting), and the mysql command (for importing). One thing to be noted thought, the exported sql has correct timestamps (they're exactly the same when in still the database). Also, I found another interesting thing: When I modified the timestamps in the dumpfile, adding '00' at the end, example: from 0404121921 into 040412912100, it got imported correctly. What gives? I'm thinking of hacking the dumpfile adding '00' at the end of every timestamp. On Tuesday 20 April 2004 08:49 pm, Victor Pendleton wrote: How are you exporting/importing the data? -Original Message- From: Fajar Priyanto To: [EMAIL PROTECTED] Sent: 4/20/04 3:51 AM Subject: error importing date Hi all, I encounter this error when importing database from one mysql server into another: the date imported is wrong. The type is timestamp. In the dump file, the date is : 0404121921, but when imported, it becomes: 0004041219. Could anybody pls give me direction why is that and how to correct it? Thanks. The old mysql server is v. 4.0.13, and the new is 4.0.15. One more info, I notice that the timezone is different, in the old: JAVT, and in the new: WIT. -- Fajar Priyanto | Reg'd Linux User #327841 | http://linux.arinet.org 15:47:50 up 7:38, Mandrake Linux release 9.2 (FiveStar) for i586 public key: https://www.arinet.org/fajar-pub.key - -- Fajar Priyanto | Reg'd Linux User #327841 | http://linux.arinet.org 21:11:56 up 45 min, Mandrake Linux release 9.2 (FiveStar) for i586 public key: https://www.arinet.org/fajar-pub.key -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFAhTCjkp5CsIXuxqURAhVGAJ90w9pULCucBiJ6Eay6avWqBteWPQCfQarM BpxA5MMayy8zdApxCfXDhec= =CUBy -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlclient missing
At 9:51 +0200 4/20/04, erricharl wrote: Hello. I have installed binary version for aix 5.2 from www.mysql.net and when i try to compile cyrus-sasl with mysql support it don't find mysqlclient libraries. I use --with-mysql=/usr/local/mysql/ to build but i get: checking for mysql_select_db in -lmysqlclient... no configure: WARNING: MySQL library mysqlclient does not work Doesn't it include client libraries ? Thanks. If you look under /usr/local/mysql, do you find any client libraries? If so, then it's a problem of the cyrus-sasl configure script not finding them. Sometimes configure scripts allow more specific options such as --with-mysql-libs. If the client libraries are present, you might check whether such an option is allowed. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error importing date
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Yes, I use the mysqldump command without any arguments. But, by the way, the timestamp is timestamp(10), is it correct? On Tuesday 20 April 2004 09:38 pm, Victor Pendleton wrote: Are you exporting/importing the DDL as well? Is the column type the same for both tables? What is that column type? TIMESTAMP(14)? -Original Message- From: Fajar Priyanto To: Victor Pendleton; '[EMAIL PROTECTED] ' Sent: 4/20/04 9:16 AM Subject: Re: error importing date Hi Victor, thanks for responding. I really need help here. I export/import the database using mysqldump (for exporting), and the mysql command (for importing). One thing to be noted thought, the exported sql has correct timestamps (they're exactly the same when in still the database). Also, I found another interesting thing: When I modified the timestamps in the dumpfile, adding '00' at the end, example: from 0404121921 into 040412912100, it got imported correctly. What gives? I'm thinking of hacking the dumpfile adding '00' at the end of every timestamp. On Tuesday 20 April 2004 08:49 pm, Victor Pendleton wrote: How are you exporting/importing the data? -Original Message- From: Fajar Priyanto To: [EMAIL PROTECTED] Sent: 4/20/04 3:51 AM Subject: error importing date Hi all, I encounter this error when importing database from one mysql server into another: the date imported is wrong. The type is timestamp. In the dump file, the date is : 0404121921, but when imported, it becomes: 0004041219. Could anybody pls give me direction why is that and how to correct it? Thanks. The old mysql server is v. 4.0.13, and the new is 4.0.15. One more info, I notice that the timezone is different, in the old: JAVT, and in the new: WIT. -- Fajar Priyanto | Reg'd Linux User #327841 | http://linux.arinet.org 15:47:50 up 7:38, Mandrake Linux release 9.2 (FiveStar) for i586 public key: https://www.arinet.org/fajar-pub.key - -- Fajar Priyanto | Reg'd Linux User #327841 | http://linux.arinet.org 22:17:51 up 1:51, Mandrake Linux release 9.2 (FiveStar) for i586 public key: https://www.arinet.org/fajar-pub.key -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFAhT9tkp5CsIXuxqURAowrAJ9+abLiuBXM0hiQ46Ms+RCJlaxtCACgkNNV 3ETlTD8S84OauheNRYAPuKI= =A44K -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error importing date
Fajar Priyanto [EMAIL PROTECTED] wrote: Hi all, I encounter this error when importing database from one mysql server into=20 another: the date imported is wrong. The type is timestamp. In the dump file, the date is : 0404121921, but when imported, it becomes:= 0004041219. Could anybody pls give me direction why is that and how to correct it? Thanks. The old mysql server is v. 4.0.13, and the new is 4.0.15. One more info, I notice that the timezone is different, in the old: JAVT, and in the new: WIT. What method do you use for import? -- 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 and Unicode
At 11:02 -0400 4/20/04, Stormblade wrote: On Tue, 20 Apr 2004 09:21:00 -0500, Paul DuBois wrote: At 9:38 -0400 4/20/04, Stormblade wrote: On Tue, 20 Apr 2004 11:32:22 +0300, Victoria Reznichenko wrote: Stormblade [EMAIL PROTECTED] wrote: Hopefully this will be the last snag...least till the next one :) I'm having a problem setting the charset to unicode (utf-8). If I understood what I read I can set each table to support a charset. But what I want to do is set the default charset for a database to be utf-8. Is this possible? If so how? Yes, it's possible from version 4.1.0. CREATE DATABASE db_name DEFAULT CHARACTER SET utf8; http://dev.mysql.com/doc/mysql/en/CREATE_DATABASE.html Oh boy, that will be a pain. I use a GUI (Navicat and EMS MySQL Manager) and so far have not seen a way for me to make it create a table with a default character set. Nor even change it. Um, what? If you specify a default character set when you create a database, any table created in that database will have the same character set by default. Is that not what you are asking? Yes and no. Whenever I go looking for MySQL help I generally see the command-line or manual way of doing things. This lets me know that at least it's possible. I wasn't even sure it was at the time I asked. So seeing the DDL for database creation answered that. However, I am not manually creating my databases. I have two GUI programs that I am using. Since that is outside the scope of this list I was merely commenting on the fact that I use these and that I hadn't seen a way to create a database or table with a specified char set. Okay. So that is an issue with those tools, not with MySQL. I've already sent some messages to their technical support. But from this list I really just wanted to know if it were possible and how to do it. I was hoping that I could change a setting somewhere to make utf-8 the default char set used for databases. Then I could see if Navicat or EMS MySQL Manager picked that up. Well, that isn't quite what you asked above, where you asked if you could specify the default character set for a database. For what you want, it might be better to change the server's default character set with the --default-character-set=utf8 option. For example, you can put this in an option file: [mysqld] default-character-set=utf8 That will cause utf8 to be the default character set for each database (and table, column, string) to be utf8. I don't know if you really want that, but it's at least possible to do this. It would not require the GUI tools to be aware of the character set setting. At this point though looks like I'll end up doing something else like creating all my tables and such then somehow exporting or copy and pasting the DLL for them all, modifying them so that it uses utf-8 and re-creating everything. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Gripe with MySQL
If you are going to have a database that will be centralized for the company and will have many different clients accessing it, I would tell them not to use MySQL. OK, though 5.0 makes it possible to write stored procs that protect clients from MySQL's gotchas, and that keep database-protecting validation where it belongs--on the server. PB - Original Message - From: Stormblade To: [EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 8:35 AM Subject: Re: Gripe with MySQL On Tue, 20 Apr 2004 09:48:11 +0200 (CEST), Jochem van Dieten wrote: Stormblade said: Ok. Love MySQL and I will be using it for my personal use and recommending it to clients as a lower cost alternative. I've only been using it for a very short time but there one major gripe I have with it and I believe it's just a design thing. MySQL seems to put the burden of error checking and such on the client. - All fields have a default value even when I don't tell it to? - Allow Null will only result in an error if I explicitly try to set the field to null. Date fields allow invalid dates: -00-00 Trailing spaces are silently discarded (fixed in 5) Decimal fields are silently truncated if the inserted value is too long etc. http://sql-info.de/mysql/gotchas.html has a nice list. Thanks for the link. That's very informative. These are the two things that I really dislike. I think it's a poor design to rely on clients only for error checking. MySQL supports foreign keys. To some extend. It is not enforced that the parent record is unique, which is a requirement with foreign keys according to the SQL spec. Aye but at least it doesn't allow me to add/delete if it'll create an orphan. Relying on clients for database integrity is a bad idea in my experience. -- Then you choose the wrong database. Jochem Well, choosing a database involves more than just not liking a particular approach. Their approach in this case only means that I would not recommend using MySQL for any large scale applications. Like the situation I described earlier. If you are going to have a database that will be centralized for the company and will have many different clients accessing it, I would tell them not to use MySQL. It would be a bad idea IMO. Not that it couldn't work of course. But for small projects it is find. It's cost effective and it does the job well. Like the project I'm currently working on. Just a small web site. Only one web app will be accessing the database and I wrote it. And I have another project coming up, similar in size that I will be using MySQL for as well. So it's not that I chose the wrong database. Just that I believe their design choices make the database the wrong choice for certain situations and applications. I would love to see MySQL add more features that allowed the database to do more of the work when it comes to integrity but until it does I simply will not use it for situations where that could be a problem. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: error importing date
I am not certain but it appears that you date values are shifting to the right. Can you create a test case using timestamp(14) and see if the error occurs? -Original Message- From: Fajar Priyanto To: Victor Pendleton; ''[EMAIL PROTECTED] ' ' Sent: 4/20/04 10:19 AM Subject: Re: error importing date -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Yes, I use the mysqldump command without any arguments. But, by the way, the timestamp is timestamp(10), is it correct? On Tuesday 20 April 2004 09:38 pm, Victor Pendleton wrote: Are you exporting/importing the DDL as well? Is the column type the same for both tables? What is that column type? TIMESTAMP(14)? -Original Message- From: Fajar Priyanto To: Victor Pendleton; '[EMAIL PROTECTED] ' Sent: 4/20/04 9:16 AM Subject: Re: error importing date Hi Victor, thanks for responding. I really need help here. I export/import the database using mysqldump (for exporting), and the mysql command (for importing). One thing to be noted thought, the exported sql has correct timestamps (they're exactly the same when in still the database). Also, I found another interesting thing: When I modified the timestamps in the dumpfile, adding '00' at the end, example: from 0404121921 into 040412912100, it got imported correctly. What gives? I'm thinking of hacking the dumpfile adding '00' at the end of every timestamp. On Tuesday 20 April 2004 08:49 pm, Victor Pendleton wrote: How are you exporting/importing the data? -Original Message- From: Fajar Priyanto To: [EMAIL PROTECTED] Sent: 4/20/04 3:51 AM Subject: error importing date Hi all, I encounter this error when importing database from one mysql server into another: the date imported is wrong. The type is timestamp. In the dump file, the date is : 0404121921, but when imported, it becomes: 0004041219. Could anybody pls give me direction why is that and how to correct it? Thanks. The old mysql server is v. 4.0.13, and the new is 4.0.15. One more info, I notice that the timezone is different, in the old: JAVT, and in the new: WIT. -- Fajar Priyanto | Reg'd Linux User #327841 | http://linux.arinet.org 15:47:50 up 7:38, Mandrake Linux release 9.2 (FiveStar) for i586 public key: https://www.arinet.org/fajar-pub.key - -- Fajar Priyanto | Reg'd Linux User #327841 | http://linux.arinet.org 22:17:51 up 1:51, Mandrake Linux release 9.2 (FiveStar) for i586 public key: https://www.arinet.org/fajar-pub.key -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFAhT9tkp5CsIXuxqURAowrAJ9+abLiuBXM0hiQ46Ms+RCJlaxtCACgkNNV 3ETlTD8S84OauheNRYAPuKI= =A44K -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: error importing date
Are you exporting/importing the DDL as well? Is the column type the same for both tables? What is that column type? TIMESTAMP(14)? -Original Message- From: Fajar Priyanto To: Victor Pendleton; '[EMAIL PROTECTED] ' Sent: 4/20/04 9:16 AM Subject: Re: error importing date -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Victor, thanks for responding. I really need help here. I export/import the database using mysqldump (for exporting), and the mysql command (for importing). One thing to be noted thought, the exported sql has correct timestamps (they're exactly the same when in still the database). Also, I found another interesting thing: When I modified the timestamps in the dumpfile, adding '00' at the end, example: from 0404121921 into 040412912100, it got imported correctly. What gives? I'm thinking of hacking the dumpfile adding '00' at the end of every timestamp. On Tuesday 20 April 2004 08:49 pm, Victor Pendleton wrote: How are you exporting/importing the data? -Original Message- From: Fajar Priyanto To: [EMAIL PROTECTED] Sent: 4/20/04 3:51 AM Subject: error importing date Hi all, I encounter this error when importing database from one mysql server into another: the date imported is wrong. The type is timestamp. In the dump file, the date is : 0404121921, but when imported, it becomes: 0004041219. Could anybody pls give me direction why is that and how to correct it? Thanks. The old mysql server is v. 4.0.13, and the new is 4.0.15. One more info, I notice that the timezone is different, in the old: JAVT, and in the new: WIT. -- Fajar Priyanto | Reg'd Linux User #327841 | http://linux.arinet.org 15:47:50 up 7:38, Mandrake Linux release 9.2 (FiveStar) for i586 public key: https://www.arinet.org/fajar-pub.key - -- Fajar Priyanto | Reg'd Linux User #327841 | http://linux.arinet.org 21:11:56 up 45 min, Mandrake Linux release 9.2 (FiveStar) for i586 public key: https://www.arinet.org/fajar-pub.key -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFAhTCjkp5CsIXuxqURAhVGAJ90w9pULCucBiJ6Eay6avWqBteWPQCfQarM BpxA5MMayy8zdApxCfXDhec= =CUBy -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to change a Bit of a SET Value
Hi all,
I use a SET Value to keep track of my Replication Process on the
Main-Daemon.
So for every Daemon, how collects Data from a Branch, a have a Flags
as a SET ('daemon_on', 'slave_working', ...)
Now I wont to toggle only one Bit and lave the rest at it is.
Example:
Now I would like to toggle Bit 1 (slave_working) without to check first
if Bit 0 (daemon_on)
is set or not.
(I know the Slave can't work if the daemon is off, but there are more
Options)
Is there a way to set/reset only one Bit in a SET Value.
I couldn't find something useful in the Docs.
Maybe I can link the Update Values with an XOR and a Mask, but how must
I do it in MySQL?
Up to now a havent figured it out how to UPDATE a SET Value with a
binary Value.
I use only the Strings or the Decimal Value.
Thanks if someone is a little bit more familiar with SET Values and
posts a short comment.
MfG,
Tobias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Website
Does anyone know what is going on with the MySQL website? Jason Lehman Webmaster, Registrar's Office (813)974-4157 Phone 574-4157 Suncom (813)974-5271 FAX [EMAIL PROTECTED] Email -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to speed up a simple query? can anyone help with an ideea?
select id,name,desc,cat,date from table where cat='12' however I need to order the results by date desc... I have indexes on both the cat and date (of type timestamp) fields however this operation is much more slowly when I used the order So the result is something like this select id,name,desc,cat,date from table where cat='12' takes 0.7 seconds select id,name,desc,cat,date from table where cat='12' order by date desc takes 2.4 seconds 1. MySQL only uses one index for each table in a JOIN; this query only uses one table, so only one index is used. 2. DESC is slower than ASC 3. Try creating an index on two columns; try cat and date, and try date and cat. 4. Check EXPLAIN SELECT id,name.. to see whether the right index is used. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multi-user / transactions question..
Hi everyone, Some quick background before I get to my question: I have developed a fantasy football game that is based on an auction style format. In any given game there are a consistant number of players, usually 10 or 12. There can be more than one game taking place at the same time on the site. All 12 players are trying to put together the best team they can with a limited amount of fictional money, in this case $200. A typical auction lasts around 90 minutes, and consists of a whole lot of furious refreshing of the auction board by the participants since each player only has a 2 minute clock. It might be easiest to demonstrate what this looks like by copying some data from an actual auction board: *at the top there is a refresh button $1 Bid On: L Tomlinson, RB SDC High Bid: You! Timer: 0m 25s $2 Bid On: P Holmes, RB KCC High Bid: You! Timer: 0m 35s $2 Bid On: C Portis, RB WAS High Bid: You! Timer: 0m 38s $2 Bid On: A Green, RB GBP High Bid: You! Timer: 0m 42s $2 Bid On: D McAllister, RB NOS High Bid: You! Timer: 0m 45s . . . When the player at the top goes to Timer: 0m 0s whoever has the high bid is awarded that player. Then a player is pulled off of a list that the auctioner who nominated that player created, and that player has a full clock and goes to the bottom of the list. The question: Since there is so much clicking going on, and my php code and mysql calls (including the transactions that decide which new player should be nominated to the board) obviously happen with each user click, is there any way to hide the guts of the work so that when they click refresh all they are doing is viewing the state of the respective tables at that time. In other words, I don't need 4 people simultaneously calling the functions that decide which player should be nominated next, awarded the player that was won to the necessary roster, deducting money, etc. With four people calling the function at once, I am forced to lock up the rows and rollback 3 of the transactions. Is there any way to have them all call the same function that only gets executed once? Another related question would be, would it make sense to create a cronjob that would go through the behind the scenes functions every one or two seconds, thereby spreading the load and sparing the end user from being involved in activating the use of the functions? Or is the natural burstable patterns that human users create actually easier on the server and a more natural way of doing things. I know I've thrown alot of stuff out there for you guys, but I'm just wondering if there is a better way from how I am doing things at present. Any help or advice would be greatly appreciated. Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to speed up a simple query? can anyone help with an ideea?
1. MySQL only uses one index for each table in a JOIN; this query only uses one table, so only one index is used. 2. DESC is slower than ASC 3. Try creating an index on two columns; try cat and date, and try date and cat. 4. Check EXPLAIN SELECT id,name.. to see whether the right index is used. Thanks for yoiur answers. I only need to order by date to return the latest results and this is why I am using an index for date and one for cat since I am using it in the where condition... Do you think that combining those indexes would make it faster because I am not so sure about that... I know that desc is slower than asc but I also know that SQL server has a specific index for this so I was wondering if MySQL has something similar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: BETWEEN
Max, You can measure the elapsed time by writing a linux shell script to do the inserts, then use the linux time command to run it. However, the user and system times displayed will not include the amount of cpu time used by the db server. Do it a few times and vary the number of inserts to build an accurate picture. Alternatively, you may be able to do this easier in version 4.1, where you can use %f in time_format to get milliseconds, so hopefully now() retrieves milliseconds too(?): select time_format(now(), '%H:%i:%s.%f'); to get timestamps to the nearest millisecond. Of course, getting the timestamp takes a finite amount of time, which you may want to measure. Andy -Original Message- From: Boyd E. Hemphill [mailto:[EMAIL PROTECTED] Sent: 20 April 2004 14:29 To: 'Max Michaels'; 'mysql' Subject: RE: BETWEEN Max: Thanks for the tip. Unfortunately I am not using a FreeBSD environment. My options are to either run a WinXP client remotely or to run something Linux based in a terminal emulator (Putty). Any suggestions would be appreciated. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Max Michaels [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 7:07 AM To: 'Boyd E. Hemphill'; 'mysql' Subject: RE: BETWEEN Hello: I am trying to measure the difference between a single insert statement of 10,000 rows and 10,000 insert statements. It is easy for me to see the single statement takes about 2 seconds. However I can come up with no good way to get the total time for individual statements. Can anyone provide a suggestion? Thanks in advance. Try super-smack. It works great for this type of testing. http://jeremy.zawodny.com/mysql/super-smack/ Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlclient missing
I find this: /mysql-standard-4.0.18-ibm-aix5.2.0.0-powerpc/lib/libmysqlclient.a /mysql-standard-4.0.18-ibm-aix5.2.0.0-powerpc/lib/libmysqlclient_r.a Is this mysqlclient ? At 9:51 +0200 4/20/04, erricharl wrote: Hello. I have installed binary version for aix 5.2 from www.mysql.net and when i try to compile cyrus-sasl with mysql support it don't find mysqlclient libraries. I use --with-mysql=/usr/local/mysql/ to build but i get: checking for mysql_select_db in -lmysqlclient... no configure: WARNING: MySQL library mysqlclient does not work Doesn't it include client libraries ? Thanks. If you look under /usr/local/mysql, do you find any client libraries? If so, then it's a problem of the cyrus-sasl configure script not finding them. Sometimes configure scripts allow more specific options such as --with-mysql-libs. If the client libraries are present, you might check whether such an option is allowed. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Setting Dynamic Variables
I've found two methods that both seem to work fine (on 4.0.18) for setting dynamic variables: mysql set @@session.autocommit=0; mysql set session autocommit=0; Which is the preferred (latest) method? Is one eventually going to be deprecated? Thanks, Lou
RE: Gripe with MySQL
Everybody should remember as well, if you run rm -rf /*.* on your server you will delete everything from your server, but linux will stay running. Even though that's not documented either. If you use a client like PHPMyadmin or one of the other 80 million that are around you won't have to worry about error checking because they have already done it for you. Now as far as your clients/customers, if you don't have error checking in yourself, that's your problem not mysql's problem. Donny -Original Message- From: Michael McTernan [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 9:04 AM To: Stormblade Cc: [EMAIL PROTECTED] Subject: RE: Gripe with MySQL Hi, ENUM columns can also trip you up if you insert a value outside of the ENUM; an empty string is inserted instead. This is documented behaviour (mysql.com seems to be going slowly though, so can't dig a reference right now), even if it is undesired in some cases. Thanks, Mike -Original Message- From: news [mailto:[EMAIL PROTECTED] Behalf Of Stormblade Sent: 20 April 2004 01:42 To: [EMAIL PROTECTED] Subject: Gripe with MySQL Ok. Love MySQL and I will be using it for my personal use and recommending it to clients as a lower cost alternative. I've only been using it for a very short time but there one major gripe I have with it and I believe it's just a design thing. MySQL seems to put the burden of error checking and such on the client. - All fields have a default value even when I don't tell it to? - Allow Null will only result in an error if I explicitly try to set the field to null. These are the two things that I really dislike. I think it's a poor design to rely on clients only for error checking. MySQL supports foreign keys. This is good because the database handles referential integrity. But it shouldn't stop there. I should also be able to tell the database not to allow a field to be empty/null and it should not put anything in there that I have not told it to. One scenario I can think of is this. My company uses MySQL as it's database. Different departments implement various interfaces to this database. All it would take is ONE client to have ONE bad SQL and although the insert works (Thanks to default values being put in) the data is not valid. I've only been working with MySQL for a little bit so this is just my first impressions. I'll be very happy to be told I'm wrong or that future updates (5.0 perhaps) will change some of the things I've mentioned. Relying on clients for database integrity is a bad idea in my experience. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- 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: when 4.1.2 release
4.1.2 will probably not be beta or gamma. Not sure why. I've been using 4.1.1 in a production environment since it was released. I love it! We still use 4.0.x or 3.23.x on some of our older stuff, and everytime I have to use it I get aggravated. Once you use it and you find all of the differences in speed and functionality, you don't want to go back. Donny -Original Message- From: electroteque [mailto:[EMAIL PROTECTED] Sent: Monday, April 19, 2004 4:12 PM To: Victoria Reznichenko; [EMAIL PROTECTED] Subject: RE: when 4.1.2 release Huh as in production ? -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 5:52 AM To: [EMAIL PROTECTED] Subject: Re: when 4.1.2 release Marek Lewczuk [EMAIL PROTECTED] wrote: Hello, when do you plan to release 4.1.2 version ? It will be released in several weeks. -- 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] -- 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: Gripe with MySQL
On Tue, 20 Apr 2004 09:25:58 -0500, Peter Brawley wrote: If you are going to have a database that will be centralized for the company and will have many different clients accessing it, I would tell them not to use MySQL. OK, though 5.0 makes it possible to write stored procs that protect clients from MySQL's gotchas, and that keep database-protecting validation where it belongs--on the server. PB I can't speak on 5.0 since I'm not using it. If 5.0 allows the database-protecting validation on the server then my main gripe with it would be null and void and then I could recommend it for the situations I mentioned. But not any of the current versions. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query that crashes MySQL
At 02:48 pm 20/04/04, Victor Pendleton wrote: What does the explain look like for this query? Like this: mysql explain (SELECT DISTINCT g.id - FROM Broadcast b, Genre g - WHERE g.id IN (0) - - ) UNION ( - SELECT DISTINCT b.id - FROM Broadcast b, Genre g - WHERE g.id IN (0) - - ) LIMIT 1 \G *** 1. row *** id: 1 select_type: PRIMARY table: b type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 12428 Extra: Using index; Using temporary *** 2. row *** id: 1 select_type: PRIMARY table: g type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 1 Extra: Using where; Using index *** 3. row *** id: 2 select_type: UNION table: b type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 12428 Extra: Using index; Using temporary *** 4. row *** id: 2 select_type: UNION table: g type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 1 Extra: Using where; Using index; Distinct 4 rows in set (0.00 sec) Jim -- James Fryer / [EMAIL PROTECTED] / [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Gripe with MySQL
Donny, I think you misunderstand the point of my comment, and possibly Stormblade's too. The point is that there are some things that are misleading, and it would be kinder for MySQL to report errors than silently performing something unexpected and continuing without warning - at least if you run rm -rf /* you'll notice pretty quickly that everything has disappeared and learn not to do it again :D If you use a client like PHPMyadmin or one of the other 80 million that are around you won't have to worry about error checking because they have already done it for you. Using MySQL + JDBC to make a very custom app, sorry. if you don't have error checking in yourself, that's your problem not mysql's problem. True, but still doesn't help when someone inexperienced with MySQL makes a mistake and buggers the database... again. I don't see why you object to making things easier to use, even if it is only activated by a -n00b option in mysql_safe. Thanks, Mike -Original Message- From: Donny Simonton [mailto:[EMAIL PROTECTED] Sent: 20 April 2004 17:37 To: 'Michael McTernan'; 'Stormblade' Cc: [EMAIL PROTECTED] Subject: RE: Gripe with MySQL Everybody should remember as well, if you run rm -rf /*.* on your server you will delete everything from your server, but linux will stay running. Even though that's not documented either. If you use a client like PHPMyadmin or one of the other 80 million that are around you won't have to worry about error checking because they have already done it for you. Now as far as your clients/customers, if you don't have error checking in yourself, that's your problem not mysql's problem. Donny -Original Message- From: Michael McTernan [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 9:04 AM To: Stormblade Cc: [EMAIL PROTECTED] Subject: RE: Gripe with MySQL Hi, ENUM columns can also trip you up if you insert a value outside of the ENUM; an empty string is inserted instead. This is documented behaviour (mysql.com seems to be going slowly though, so can't dig a reference right now), even if it is undesired in some cases. Thanks, Mike -Original Message- From: news [mailto:[EMAIL PROTECTED] Behalf Of Stormblade Sent: 20 April 2004 01:42 To: [EMAIL PROTECTED] Subject: Gripe with MySQL Ok. Love MySQL and I will be using it for my personal use and recommending it to clients as a lower cost alternative. I've only been using it for a very short time but there one major gripe I have with it and I believe it's just a design thing. MySQL seems to put the burden of error checking and such on the client. - All fields have a default value even when I don't tell it to? - Allow Null will only result in an error if I explicitly try to set the field to null. These are the two things that I really dislike. I think it's a poor design to rely on clients only for error checking. MySQL supports foreign keys. This is good because the database handles referential integrity. But it shouldn't stop there. I should also be able to tell the database not to allow a field to be empty/null and it should not put anything in there that I have not told it to. One scenario I can think of is this. My company uses MySQL as it's database. Different departments implement various interfaces to this database. All it would take is ONE client to have ONE bad SQL and although the insert works (Thanks to default values being put in) the data is not valid. I've only been working with MySQL for a little bit so this is just my first impressions. I'll be very happy to be told I'm wrong or that future updates (5.0 perhaps) will change some of the things I've mentioned. Relying on clients for database integrity is a bad idea in my experience. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- 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: Learning curve
J, Thanks for your response. I should say, it's not the entire university which has fallen so far behind, just my department. Also, the records we need to manage with this system will be only within this department... at first. Could you possibly link me to some of those projects on SF and FM? I agree with standing on the shoulders of giants when it comes to developing new custom systems. Hey! I just saw you're living squarebanks, I used to live there too. How long have you lived there? How's the weather today? -Mike Caskey Joshua J. Kugler wrote: Mike - You didn't indicate your department, so I'm not sure what your background is. Your message, overall, is a bit scary, as any university that far behind right now would be worrisome. I'm not exactly sure what you're asking for (as you didn't ouline your requirements), but I would first take a look on sites like sourceforge or freshmeat for systems that already do what you want. I'm sure the kind of record keeping you do has been done before. But as to your main quesiton, it is very doable. You just need to keep in mind multi-user issue like record locking. Search the archives for messages by me about record locking for an elegant way to do it via a flag field. If you can't find it, let me know, and I'll type it up again. j- k- On Thursday 15 April 2004 02:06 pm, Mike T. Caskey said something like: Hi all! I'm wondering if anyone can help me find out how much time/training is needed to accomplish my task using MySQL. My background: I'm fresh to the world of MySQL and databases in general. I do have some fundamental knowledge in the area of programming and databases, but nothing too in-depth. My story: I work for a University that is seemingly falling behind the technical times. My department is using MS Access as the primary software for handling data, but we're still mainly hard-copy for our records-management. Obviously, there are problems with keeping hard-copy for everything. I was buried in paperwork for a short while before I decided to create simple databases/forms using OpenOffice.org, since it was so easy. Someone in management noticed the consistency emerging from my office and inquired. When I told them about my databases, they decided everyone in the department could benefit from them and assigned the project of making this available to all. My problem: My databases are single-user systems for use in OpenOffice.org and would be difficult to roll them out to my entire team. I don't want to install OO.o on everyones computer and I don't want to learn MS Access as it is known for being a temporary solution. So I need something that can keep up with the times and can be rolled out easily (web interface?). I also need to be able to append scanned images to records (PDF or JPEG?). This is all pretty complex and I'm definitely not technically equipped to create this just yet. MySQL?: I believe a good question would be whether or not MySQL would be a good solution for this. What do you think? Also, how long would it take me to learn the necessary information? Lastly, how long would it take to develop such a system? I appreciate your time and information! Thanks, Mike T. Caskey
Re: Learning curve
Thanks for the info Daniel, Could you maybe link me to some of the projects you've seen for uploading images too? -Mike Caskey Daniel Kasak wrote: Mike T. Caskey wrote: Hi all! I'm wondering if anyone can help me find out how much time/training is needed to accomplish my task using MySQL. My background: I'm fresh to the world of MySQL and databases in general. I do have some fundamental knowledge in the area of programming and databases, but nothing too in-depth. My story: I work for a University that is seemingly falling behind the technical times. My department is using MS Access as the primary software for handling data, but we're still mainly hard-copy for our records-management. Obviously, there are problems with keeping hard-copy for everything. I was buried in paperwork for a short while before I decided to create simple databases/forms using OpenOffice.org, since it was so easy. Someone in management noticed the consistency emerging from my office and inquired. When I told them about my databases, they decided everyone in the department could benefit from them and assigned the project of making this available to all. My problem: My databases are single-user systems for use in OpenOffice.org and would be difficult to roll them out to my entire team. I don't want to install OO.o on everyones computer and I don't want to learn MS Access as it is known for being a temporary solution. So I need something that can keep up with the times and can be rolled out easily (web interface?). I also need to be able to append scanned images to records (PDF or JPEG?). This is all pretty complex and I'm definitely not technically equipped to create this just yet. MySQL?: I believe a good question would be whether or not MySQL would be a good solution for this. What do you think? Also, how long would it take me to learn the necessary information? Lastly, how long would it take to develop such a system? I appreciate your time and information! Thanks, Mike T. Caskey If you are determined to do a little extra work ( over what's required for Access ), I'd go for PHP. I've completely converted our sales DB front-ends from Access to PHP and it is a lot better. It's much faster, MUCH more stable and we can provide remote access to the DB. But it certainly is more work to get it running and to make changes. I've seen code floating around for uploading images to a MySQL DB via a PHP-driven web site. The following links have been placed here by the NUS Consulting internal spam filter and are for use by NUS Consulting staff only. Please ingore these links. Spam http://screamer.nusconsulting.com.au/internal/canit/b.php?c=si=29945m=502c7a4e0550 Not spam http://screamer.nusconsulting.com.au/internal/canit/b.php?c=ni=29945m=502c7a4e0550 Forget previous vote http://screamer.nusconsulting.com.au/internal/canit/b.php?c=fi=29945m=502c7a4e0550
Re: Learning curve
Eric, Right, I think the OO.o thing just isn't going to work for me, unless I can get the IT department to like it, but they're already deeply invested in MSO. The MS Access interface idea would be a quick solution, but I wonder if there would be version conflicts in the future, since the MSO upgrades are performed on an individual basis. If a person or two coulnd't use the system for a few days, due to upgrades, that would be a real problem. So a web interface seems to be the best solution, based on that, but I'm not a web programmer... yet. Can you suggest a good training resource for MySQL/PHP? Are there any good classroom-based training companies that don't charge an arm and a leg? Thanks, Mike Caskey Eric Frazier wrote: Hi, There is an alterative to the web/php type interface. It is only worthwhile if you already have some one with decent Access knowledge. Access is a great database front end, really nice to use in some ways, and does some things you can't do with a web interface out of the box. It is possible and even fairly easy to make an Access application that does your job, but runs as a front end to your mysql database on a central server. Lookup myODBC for more info on this. Of course you should be able to do the exact same thing with open office :) I have only done it with Access. It does bring up the issue of installs on everyone's machines and I guess that might be the killer for you. Everyone has a web browser of course.. Eric Lead Programmer D.M. Contact Management 250.383.0836 ext 229 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Learning curve
Peter, I really appreciate your offer to help out! Since I'm fairly fresh in the area, I appreciate any help I can get. Can you recomend any formal online training resources for MySQL/PHP? Thanks, Mike Caskey Peter Lovatt wrote: Hi I would echo this. We are finding that applications based on MySql and php are an increasing part of our business as the easy deployment and familiarity of a web type interface gains popularity. It works particularly well when you need a lot of people to access data when setting them up with installed software would be a headache. We have applications that sound close to what you are looking for. I would be happy to share some code and perhaps do a little database work if that would help you get started. Let me know Peter --- Excellence in internet and open source software --- Sunmaia Birmingham UK www.sunmaia.net tel : 0121-242-1473 fax : 0870 7621758 International +44-121-242-1473 --- -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: 15 April 2004 23:23 To: Mike T. Caskey; [EMAIL PROTECTED] Subject: Re: Learning curve Mike T. Caskey wrote: Hi all! I'm wondering if anyone can help me find out how much time/training is needed to accomplish my task using MySQL. My background: I'm fresh to the world of MySQL and databases in general. I do have some fundamental knowledge in the area of programming and databases, but nothing too in-depth. My story: I work for a University that is seemingly falling behind the technical times. My department is using MS Access as the primary software for handling data, but we're still mainly hard-copy for our records-management. Obviously, there are problems with keeping hard-copy for everything. I was buried in paperwork for a short while before I decided to create simple databases/forms using OpenOffice.org, since it was so easy. Someone in management noticed the consistency emerging from my office and inquired. When I told them about my databases, they decided everyone in the department could benefit from them and assigned the project of making this available to all. My problem: My databases are single-user systems for use in OpenOffice.org and would be difficult to roll them out to my entire team. I don't want to install OO.o on everyones computer and I don't want to learn MS Access as it is known for being a temporary solution. So I need something that can keep up with the times and can be rolled out easily (web interface?). I also need to be able to append scanned images to records (PDF or JPEG?). This is all pretty complex and I'm definitely not technically equipped to create this just yet. MySQL?: I believe a good question would be whether or not MySQL would be a good solution for this. What do you think? Also, how long would it take me to learn the necessary information? Lastly, how long would it take to develop such a system? I appreciate your time and information! Thanks, Mike T. Caskey If you are determined to do a little extra work ( over what's required for Access ), I'd go for PHP. I've completely converted our sales DB front-ends from Access to PHP and it is a lot better. It's much faster, MUCH more stable and we can provide remote access to the DB. But it certainly is more work to get it running and to make changes. I've seen code floating around for uploading images to a MySQL DB via a PHP-driven web site. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and Unicode
At 13:03 -0400 4/20/04, Stormblade wrote: On Tue, 20 Apr 2004 11:49:46 -0500, Paul DuBois wrote: Make sure the server really is picking up the option value: SHOW VARIABLES LIKE 'character%'; Look for 'character_set_server'. If it's utf8, then perhaps your GUI tools are overriding the setting? Dunno. You might try creating a database through the GUI and then looking at the server query log to see what query is actually being sent to it. Using WinMySQLadmin 1.4 which came bundled with it I viewed the variables and it seems that it's not picking it up. The variable you mention is still set to latin1. Adding that line you mentioned didn't change anything. Is there a different format for the ini file on *Nix vs Windows? No. But you're asking me to guess what you did. What file did you add the lines to, and what lines did you add? Did you restart the server? The file was my ini file. my.ini which is located in the windows directory. I copy and pasted the line you gave here and put it under the [mysqld] section in that ini file. Yes I restarted the server. Hmm. That should have done it. There weren't any messages in the error log about the server not liking the option or anything? Here's my variables: character_set_server: latin1 character_set_system: utf8 character_set_database: latin1 character_set_client: latin1 character_set_connection: latin1 character_set_results: latin1 -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Learning curve
Rhino... I'm with Joshua on this one. Any university that is still doing everything with hardcopy and that has few if any computer systems to store things at this point in time is scary. Fortunately, they appear to be getting at least a glimmer of the idea that they could do much better with a computer system of the kind that you envision. It's not really every department/peice of information that's hard-copy. It's just that some systems are either more complex, or they're part of a new business process that was just implemented by non-tech types. The wild thing is that many people here are more afraid of soft-copy than anything else, due to data-loss experiences in the past. I'm going to have to put a lot of effort in showing some the power and stability that is available. As for the nature of your applications, Java servlets would be my first choice if I were in your shoes. (I'm a Java developer, not a PHP developer, so I'm biased ;-) Servlets would let all of your users access the programs via the web and eliminate a lot of the work involved in distributing a desktop application. I think you'd probably want to use CLOBs or BLOBs (or both) to store your data in the database. These can easily be stored in the database and then read again when needed via applications and/or servlets. I'm was thinking PHP because what I've heard about the learning curve, I was thinking it might take a while longer to get the hang of Java. How do you think the learning curves compare? It's impossible to estimate how long it would take you to do the necessary work. For one thing, you've been very vague in describing your own skills. You have also neglected to mention how many applications you're building, whether you will have help, how skilled your helpers are, what sort of testing has to be completed to satisfy your employers, how much of your time you'll be able to devote to this project every week - you do have other responsibilities, right? - and many other factors. I've been working with database for 20+ years now and I know quite a bit about application development, database design, how to organize a project, testing, etc. etc. so I'm confident *I* could do the work you're describing by myself in relatively short order, depending on how much work you are actually trying to do. Whether *you* could do it in a reasonable amount of time is not clear to me. If you already know at least one major programming language, like Java or PHP (something that works with MySQL and lets you build serious web-based applications), fluently, you have a good start. If you know something about database design and application design, you have a real fighting chance. But if your programming knowledge is limited to being able to write a basic batch file and you have no idea how to do application or database design, you are either going to need to take a *lot* of time to teach this to yourself or you will have to spend a lot of money on courses to learn it more quickly - and that may not sit well with your employers because most employers are notoriously reluctant to spend money on education. If you don't have the time or patience to teach yourself what you need to know and if your employer won't pay for courses, you will likely be limited to small, simple things that make only small dents on the amount of hardcopy that you manage. I don't want to sound negative because I think you've got a good attitude and the beginnings of a vision for how to really improve things in your department. I also see a lot of myself in you, especially in the desire to make the world (or at least your department) a better place. But you should know that as soon as this project looks like it is going to cost any significant amount of time or money, The Powers That Be will kill it so fast it will make your head spin. At least that's been my experience. Everyone wants a free solution but very few are willing to spend money or time (and time IS money) unless they can see a very clear payback that improves their bottom line. The warm fuzzy feeling you get from having a new system is not something that the bean-counters will value since they can't put a dollar figure on it. They want to see real dollar savings, not just warm fuzzies. Again, I really don't want to discourage you, just warn you not to get your hopes up too high. Frankly, I hope you go ahead and do it anyway, even at the risk of some disappointments. I think you will learn a lot of interesting things and get a real kick out of accomplishing the sorts of things you are describing. Good luck and check in once in a while to tell us how you're doing with this idea. Rhino Wow, that was a lot, and I appreciate it! You're right, I didn't give a lot of detail where it was probably needed for a good response, but I'm new to this area and your response was very helpful. To be honest, I wasn't fully aware of the scope of this kind of project and you provided me with some of the questions that
Re: mysqlclient missing
At 18:16 +0200 4/20/04, erri wrote: I find this: /mysql-standard-4.0.18-ibm-aix5.2.0.0-powerpc/lib/libmysqlclient.a /mysql-standard-4.0.18-ibm-aix5.2.0.0-powerpc/lib/libmysqlclient_r.a Is this mysqlclient ? Yes, but it doesn't look like it's under /usr/local/mysql to me. :-) At 9:51 +0200 4/20/04, erricharl wrote: Hello. I have installed binary version for aix 5.2 from www.mysql.net and when i try to compile cyrus-sasl with mysql support it don't find mysqlclient libraries. I use --with-mysql=/usr/local/mysql/ to build but i get: checking for mysql_select_db in -lmysqlclient... no configure: WARNING: MySQL library mysqlclient does not work Doesn't it include client libraries ? Thanks. If you look under /usr/local/mysql, do you find any client libraries? If so, then it's a problem of the cyrus-sasl configure script not finding them. Sometimes configure scripts allow more specific options such as --with-mysql-libs. If the client libraries are present, you might check whether such an option is allowed. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Speed
I have two queries that are very similar. One of the queries takes a few minutes (3:43:07 last run) to complete, while the other takes less than a second to complete. I know these are two different queries and shouldn't take the same amount of time, but I based the fast query on the slower one. I would like to make the slower query faster and I don't have the slightest clue on how to do it. Here are my queries and their explanations if that helps: Please let me know if there's anything that I can do. Thanks, Craig Slow Query: SELECT insco.insconame, CONCAT_WS(, , pt.ptlname, pt.ptfname) name, pt.id, coverage.covpatinsno, cpt.cptcode , DATE_FORMAT(proc.procdt, %m-%d-%Y) procdt, insco.inscophone, proc.id procid, payrec.payrecamt current, fee.expected, coverage.covinsco, proc.proccpt FROM payrec LEFT JOIN procrec AS proc ON payrec.payrecpatient = proc.procpatient LEFT JOIN patient AS pt ON payrec.payrecpatient = pt.id LEFT JOIN coverage ON coverage.covpatient = pt.id AND (proc.proccov1 = coverage.id OR proc.proccurcovid = coverage.id) LEFT JOIN insco ON coverage.covinsco = insco.id LEFT JOIN cpt ON proc.proccpt = cpt.id LEFT JOIN feeProfiles AS fee ON fee.cpt_ID = proc.proccpt AND fee.insurance_ID = coverage.covinsco WHERE payrec.payreccat = 5 AND coverage.covinsco != GROUP BY insconame, name, cptcode, procdt ORDER BY insconame Slow Query Explanation: +--++---+-+-++--+--+ | table| type | possible_keys | key | key_len | ref| rows | Extra| +--++---+-+-++--+--+ | payrec | ALL| NULL | NULL|NULL | NULL | 179 | Using where; Using temporary; Using filesort | | coverage | ALL| PRIMARY | NULL|NULL | NULL | 935 | Using where | | proc | ALL| NULL | NULL|NULL | NULL | 420 | Using where | | pt | eq_ref | PRIMARY | PRIMARY | 4 | payrec.payrecpatient |1 | Using where | | insco| eq_ref | PRIMARY | PRIMARY | 4 | coverage.covinsco |1 | | | cpt | eq_ref | PRIMARY | PRIMARY | 4 | proc.proccpt |1 | | | fee | eq_ref | unqq | unqq| 8 | proc.proccpt,coverage.covinsco |1 | | +--++---+-+-++--+--+ Fast Query: SELECT insco.insconame, CONCAT_WS(, , pt.ptlname, pt.ptfname) name, pt.id, coverage.covpatinsno, cpt.cptcode , DATE_FORMAT(proc.procdt, %m-%d-%Y) procdt, pt.pthphone, proc.id procid, payrec.payrecamt current, fee.expected, coverage.covinsco, proc.proccpt FROM payrec LEFT JOIN procrec AS proc ON payrec.payrecpatient = proc.procpatient LEFT JOIN patient AS pt ON payrec.payrecpatient = pt.id LEFT JOIN coverage ON coverage.covpatient = pt.id AND (proc.proccov1 = coverage.id OR proc.proccurcovid = coverage.id) LEFT JOIN insco ON coverage.covinsco = insco.id LEFT JOIN cpt ON proc.proccpt = cpt.id LEFT JOIN feeProfiles AS fee ON fee.cpt_ID = proc.proccpt AND fee.insurance_ID = coverage.covinsco WHERE payrec.payreccat = 6 AND payrec.payrecsource = 0 GROUP BY name, cptcode, procdt ORDER BY name, insconame Explanaion: +--++---+-+-++--+--+ | table| type | possible_keys | key | key_len | ref| rows | Extra| +--++---+-+-++--+--+ | payrec | ALL| NULL | NULL|NULL | NULL | 179 | Using where; Using temporary; Using filesort | | proc | ALL| NULL | NULL|NULL | NULL | 420 | | | pt | eq_ref | PRIMARY | PRIMARY | 4 | payrec.payrecpatient |1 | | | coverage | ALL| PRIMARY | NULL|NULL | NULL | 935 | | | insco| eq_ref | PRIMARY | PRIMARY | 4 | coverage.covinsco
Re: Setting Dynamic Variables
At 12:33 -0400 4/20/04, Lou Olsten wrote: I've found two methods that both seem to work fine (on 4.0.18) for setting dynamic variables: mysql set @@session.autocommit=0; mysql set session autocommit=0; Which is the preferred (latest) method? Is one eventually going to be deprecated? 1) They're the same. 2) Neither is the latest; they were both added in 4.0.3. 3) No. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb Hot Backup Tool
Does anyone have any experience with Innodb Hot Backup Tool? Thanks, Ann
RE: Gripe with MySQL
On Tue, 20 Apr 2004 17:57:08 +0100, Michael McTernan wrote: Donny, I think you misunderstand the point of my comment, and possibly Stormblade's too. The point is that there are some things that are misleading, and it would be kinder for MySQL to report errors than silently performing something unexpected and continuing without warning - at least if you run rm -rf /* you'll notice pretty quickly that everything has disappeared and learn not to do it again :D That's for sure. Not only that but you CAN do something to prevent it. You could for example, replace the rm command with another version which doesn't allow you to do that without many checks and prompts :) But doing an rm -fr /* is very different from what we were talking about. If you use a client like PHPMyadmin or one of the other 80 million that are around you won't have to worry about error checking because they have already done it for you. Using MySQL + JDBC to make a very custom app, sorry. Same here for now. This is where I feel MySQL shines and why I've chosen to use it (Besides the cost of course). Not only that but say you have a app with a ton of SQL. How can you verify all the SQL statements include ALL the fields. Checking each field is the job of the client yes but if an SQL statement leaves out a field that is required by the database...that's just it. None of them are required. MySQL will happily fill in your blanks for you so you won't know there were blanks until you view it. if you don't have error checking in yourself, that's your problem not mysql's problem. True, but still doesn't help when someone inexperienced with MySQL makes a mistake and buggers the database... again. I don't see why you object to making things easier to use, even if it is only activated by a -n00b option in mysql_safe. As I mentioned in my other reply there are some checks that belong on the client and some that really belong on the server. So yes some error checking SHOULD be the server's problem. But not all of it. Just saying if you don't have error checking in yourself doesn't help. What type of error checking? There's not just one global error checking type. Thanks, Mike -Original Message- From: Donny Simonton [mailto:[EMAIL PROTECTED] Sent: 20 April 2004 17:37 To: 'Michael McTernan'; 'Stormblade' Cc: [EMAIL PROTECTED] Subject: RE: Gripe with MySQL Everybody should remember as well, if you run rm -rf /*.* on your server you will delete everything from your server, but linux will stay running. Even though that's not documented either. If you use a client like PHPMyadmin or one of the other 80 million that are around you won't have to worry about error checking because they have already done it for you. Now as far as your clients/customers, if you don't have error checking in yourself, that's your problem not mysql's problem. Donny -Original Message- From: Michael McTernan [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 9:04 AM To: Stormblade Cc: [EMAIL PROTECTED] Subject: RE: Gripe with MySQL Hi, ENUM columns can also trip you up if you insert a value outside of the ENUM; an empty string is inserted instead. This is documented behaviour (mysql.com seems to be going slowly though, so can't dig a reference right now), even if it is undesired in some cases. Thanks, Mike -Original Message- From: news [mailto:[EMAIL PROTECTED] Behalf Of Stormblade Sent: 20 April 2004 01:42 To: [EMAIL PROTECTED] Subject: Gripe with MySQL Ok. Love MySQL and I will be using it for my personal use and recommending it to clients as a lower cost alternative. I've only been using it for a very short time but there one major gripe I have with it and I believe it's just a design thing. MySQL seems to put the burden of error checking and such on the client. - All fields have a default value even when I don't tell it to? - Allow Null will only result in an error if I explicitly try to set the field to null. These are the two things that I really dislike. I think it's a poor design to rely on clients only for error checking. MySQL supports foreign keys. This is good because the database handles referential integrity. But it shouldn't stop there. I should also be able to tell the database not to allow a field to be empty/null and it should not put anything in there that I have not told it to. One scenario I can think of is this. My company uses MySQL as it's database. Different departments implement various interfaces to this database. All it would take is ONE client to have ONE bad SQL and although the insert works (Thanks to default values being put in) the data is not valid. I've only been working with MySQL for a little bit so this is just my first impressions. I'll be very happy to be told I'm wrong or that future updates (5.0 perhaps) will change some of the things I've mentioned. Relying on
Re: MySQL and Unicode (Solved)
Embarrassing but I blame it on hunger! It's working now. Bottom line was I hadn't restarted the server. Now I know I said I did. Here's where I blame hunger. I was restarted A server. My Tomcat server. I kept restarting it thinking I was restarting the MySQL serverdon't ask. Like I said I blame it on hunger. In any case, after a true restart it set all the variables to utf8 just fine. I went into Navicat and it picked it up just fine when I created a new database and table. So I just have to re-create the tables and re-import and I'm good to go. I may just re-create the tables (There's only 2) that will contain unicode data. Anyway, thanks for the help. I'm gonna go eat now. -- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) The worth of your opinion is in direct proportion to the number of people who have asked for it. My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and Unicode
On Tue, 20 Apr 2004 12:13:07 -0500, Paul DuBois wrote: At 13:03 -0400 4/20/04, Stormblade wrote: On Tue, 20 Apr 2004 11:49:46 -0500, Paul DuBois wrote: Make sure the server really is picking up the option value: SHOW VARIABLES LIKE 'character%'; Look for 'character_set_server'. If it's utf8, then perhaps your GUI tools are overriding the setting? Dunno. You might try creating a database through the GUI and then looking at the server query log to see what query is actually being sent to it. Using WinMySQLadmin 1.4 which came bundled with it I viewed the variables and it seems that it's not picking it up. The variable you mention is still set to latin1. Adding that line you mentioned didn't change anything. Is there a different format for the ini file on *Nix vs Windows? No. But you're asking me to guess what you did. What file did you add the lines to, and what lines did you add? Did you restart the server? The file was my ini file. my.ini which is located in the windows directory. I copy and pasted the line you gave here and put it under the [mysqld] section in that ini file. Yes I restarted the server. Hmm. That should have done it. There weren't any messages in the error log about the server not liking the option or anything? If by error log you mean the mysql.err file in the data subdirectory then no it shows only normal connections and shutdowns. No errors. Here's my variables: character_set_server: latin1 character_set_system: utf8 character_set_database: latin1 character_set_client: latin1 character_set_connection: latin1 character_set_results: latin1 -- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) The worth of your opinion is in direct proportion to the number of people who have asked for it. My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and Unicode
On Tue, 20 Apr 2004 11:49:46 -0500, Paul DuBois wrote: Make sure the server really is picking up the option value: SHOW VARIABLES LIKE 'character%'; Look for 'character_set_server'. If it's utf8, then perhaps your GUI tools are overriding the setting? Dunno. You might try creating a database through the GUI and then looking at the server query log to see what query is actually being sent to it. Using WinMySQLadmin 1.4 which came bundled with it I viewed the variables and it seems that it's not picking it up. The variable you mention is still set to latin1. Adding that line you mentioned didn't change anything. Is there a different format for the ini file on *Nix vs Windows? No. But you're asking me to guess what you did. What file did you add the lines to, and what lines did you add? Did you restart the server? The file was my ini file. my.ini which is located in the windows directory. I copy and pasted the line you gave here and put it under the [mysqld] section in that ini file. Yes I restarted the server. Here's my variables: character_set_server: latin1 character_set_system: utf8 character_set_database: latin1 character_set_client: latin1 character_set_connection: latin1 character_set_results: latin1 -- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) The worth of your opinion is in direct proportion to the number of people who have asked for it. My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 -- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) The worth of your opinion is in direct proportion to the number of people who have asked for it. My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Gripe with MySQL
--- Original Message --- From: Stormblade [EMAIL PROTECTED] To: Donny Simonton [EMAIL PROTECTED] Cc: Sent: Tue, 20 Apr 2004 12:59:58 -0400 Subject: RE: Gripe with MySQL On Tue, 20 Apr 2004 11:37:12 -0500, Donny Simonton wrote: Everybody should remember as well, if you run rm -rf /*.* on your server you will delete everything from your server, but linux will stay running. Even though that's not documented either. If you use a client like PHPMyadmin or one of the other 80 million that are around you won't have to worry about error checking because they have already done it for you. Now as far as your clients/customers, if you don't have error checking in yourself, that's your problem not mysql's problem. Donny One clarification. Database checking does not eliminate the need for client error checking. The two of these things have nothing to do with each other. The database should allow for database protecting checks as it knows nothing and shouldn't know anything about the client. Whether the client performs their own error checking or not is irrelevant. Obviously this is, as I mentioned, a design thing and not everyone will agree on a design. I prefer the server to allow a database administration to setup checks to help ensure that data is going in as was intended. The database administrator in many companies have nothing to do with writing the clients or the SQL that accesses the database. There are some checks that I feel are better located on the server and there are some that are better located at the client end. When I tell a field that it should not be blank. I don't mean for the database to take it upon itself to ensure that it's never ever blank. If a record comes in to a server, that server should include a way to tell whether that record is invalid according to the server's specifications. This is not a new model and many many applications do this. Both client and server have responsibilities. We differ on what we feel those responsibilities are. I feel that I should be able to define what an invalid record is AND have the server reject that record if it is invalid. The server has nothing to do with whether the SQL was valid or invalid or whether the SQL is sending bad data like the wrong dates or text fields that are chopped off. That's all client side. However, the server should know what a valid record looks like and reject those that are not valid. Having worked in a distributed environment and using various middleware I can tell you that in an environment like that it is crucial that the server be able to tell invalid records from valid ones and have the option to handle them (reject, accept/fix, log, etc). Right now MySQL only does an accept/fix type thing. It makes it a valid record even though it really isn't. -- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) The worth of your opinion is in direct proportion to the number of people who have asked for it. My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Process Monitoring
Hi, We have a MySQL-Server-4.0.17 installed in our enterprise working fine... :-) Some times our application takes too bandwidth from the database server. So, I´m trying to figure out what (perhaps what select) is taking so manu bandwidth. SHOW FULL PROCESSLIST shows me the follow: mysql show full processlist; +--+-+---+-+-+-- +---+---+ | Id | User| Host | db | Command | Time | State | Info | +--+-+---+-+-+-- +---+---+ | 3303 | filmeemcasa | 200.247.127.132:43570 | filmeemcasa | Sleep | 74 | | NULL | | 4226 | filmeemcasa | 200.247.127.132:33898 | filmeemcasa | Sleep | 0 | | NULL | | 4276 | filmeemcasa | 200.247.127.132:47532 | filmeemcasa | Sleep | 9 | | NULL | | 4280 | filmeemcasa | 200.247.127.132:47918 | filmeemcasa | Sleep | 11 | | NULL | | 4281 | filmeemcasa | 200.247.127.132:47933 | filmeemcasa | Sleep | 315 | | NULL | | 4308 | filmeemcasa | 200.247.127.132:50119 | filmeemcasa | Sleep | 72 | | NULL | | 4312 | filmeemcasa | 200.247.127.132:50284 | filmeemcasa | Sleep | 0 | | NULL | | 4315 | filmeemcasa | 200.247.127.132:50287 | filmeemcasa | Sleep | 71 | | NULL | | 4329 | spv | 200.247.127.132:54006 | spvvideo| Sleep | 76 | | NULL | | 4330 | filmeemcasa | pardal:3180 | NULL| Sleep | 2163 | | NULL | | 4331 | filmeemcasa | pardal:3184 | NULL| Sleep | 64 | | NULL | | 4333 | filmeemcasa | 200.247.127.132:54025 | filmeemcasa | Sleep | 72 | | NULL | | 4338 | filmeemcasa | 200.247.127.132:54640 | filmeemcasa | Sleep | 10 | | NULL | | 4339 | root| localhost | NULL| Query | 0 | NULL | show full processlist | +--+-+---+-+-+-- +---+---+ 14 rows in set (0.00 sec) but I can´t get to see the select that is being executed. Does anyone knows how can I find it? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
capacity planning
Hi - Is there any info out there on how to put together a capacity plan for MySQL? My tables are filling up pretty quick - 20% more volume a month and would like to be able to project a year by year analysis of memory, resources needed. Thanks, Ann
speeding up UPDATE and INSERT
Hi there,
I'm currently writing a Data Mining program in Perl, my problem is that the
writing to the database takes awful long.
Are there any ideas how to speed up the inserting and the updating, besides
using the bind operator?!
the SQLstatement looks like
INSERT INTO `Counts` (`id`, `date`, `count`, `avg)VALUES ('', CURDATE(),
'$count', '$avg')
or
UPDATE `Counts` SET `count` = '$count', `avg` = '$avg' WHERE `id` = '$id'
so if anybody has an idea how to get this done faster (dumping the data to a
csv file first and updating all at once, or something like this) I would
love to hear it.
thanks a lot in advance.
regards
Matthias
_
Matthias Eireiner
email: [EMAIL PROTECTED]
www.bvcapital.com
_
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Gripe with MySQL
Just something I noticed missing here The lack of error checking on the server side means better performance in my opinion. When you're throwing a couple thousand hits per second at it, this is visible. I would have to agree that error checking does belong on the client side (at least from my experience), and it can be a lot more productive as a database, and not a workflow tool. Of course, I do agree about the ENUM and stuff being annoying, but if you know to work with it, performance is much more valuable. P Stormblade [EMAIL PROTECTED] 04/20/2004 01:20 PM To: [EMAIL PROTECTED], Donny Simonton [EMAIL PROTECTED] cc: [EMAIL PROTECTED], 'Stormblade' [EMAIL PROTECTED] Subject:RE: Gripe with MySQL On Tue, 20 Apr 2004 17:57:08 +0100, Michael McTernan wrote: Donny, I think you misunderstand the point of my comment, and possibly Stormblade's too. The point is that there are some things that are misleading, and it would be kinder for MySQL to report errors than silently performing something unexpected and continuing without warning - at least if you run rm -rf /* you'll notice pretty quickly that everything has disappeared and learn not to do it again :D That's for sure. Not only that but you CAN do something to prevent it. You could for example, replace the rm command with another version which doesn't allow you to do that without many checks and prompts :) But doing an rm -fr /* is very different from what we were talking about. If you use a client like PHPMyadmin or one of the other 80 million that are around you won't have to worry about error checking because they have already done it for you. Using MySQL + JDBC to make a very custom app, sorry. Same here for now. This is where I feel MySQL shines and why I've chosen to use it (Besides the cost of course). Not only that but say you have a app with a ton of SQL. How can you verify all the SQL statements include ALL the fields. Checking each field is the job of the client yes but if an SQL statement leaves out a field that is required by the database...that's just it. None of them are required. MySQL will happily fill in your blanks for you so you won't know there were blanks until you view it. if you don't have error checking in yourself, that's your problem not mysql's problem. True, but still doesn't help when someone inexperienced with MySQL makes a mistake and buggers the database... again. I don't see why you object to making things easier to use, even if it is only activated by a -n00b option in mysql_safe. As I mentioned in my other reply there are some checks that belong on the client and some that really belong on the server. So yes some error checking SHOULD be the server's problem. But not all of it. Just saying if you don't have error checking in yourself doesn't help. What type of error checking? There's not just one global error checking type. Thanks, Mike -Original Message- From: Donny Simonton [mailto:[EMAIL PROTECTED] Sent: 20 April 2004 17:37 To: 'Michael McTernan'; 'Stormblade' Cc: [EMAIL PROTECTED] Subject: RE: Gripe with MySQL Everybody should remember as well, if you run rm -rf /*.* on your server you will delete everything from your server, but linux will stay running. Even though that's not documented either. If you use a client like PHPMyadmin or one of the other 80 million that are around you won't have to worry about error checking because they have already done it for you. Now as far as your clients/customers, if you don't have error checking in yourself, that's your problem not mysql's problem. Donny -Original Message- From: Michael McTernan [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 9:04 AM To: Stormblade Cc: [EMAIL PROTECTED] Subject: RE: Gripe with MySQL Hi, ENUM columns can also trip you up if you insert a value outside of the ENUM; an empty string is inserted instead. This is documented behaviour (mysql.com seems to be going slowly though, so can't dig a reference right now), even if it is undesired in some cases. Thanks, Mike -Original Message- From: news [mailto:[EMAIL PROTECTED] Behalf Of Stormblade Sent: 20 April 2004 01:42 To: [EMAIL PROTECTED] Subject: Gripe with MySQL Ok. Love MySQL and I will be using it for my personal use and recommending it to clients as a lower cost alternative. I've only been using it for a very short time but there one major gripe I have with it and I believe it's just a design thing. MySQL seems to put the burden of error checking and such on the client. - All fields have a default value even when I don't tell it to? - Allow Null will only result in an error if I explicitly try to set the field to null. These are the two things that I really dislike. I think it's a poor design to rely on clients only for error checking. MySQL supports foreign
Randomly selecting from table
Is it possible to randomly select from all entries in a table, but have it be 2 different ones each time? If so, what documentation should I be looking at? I am using PHP and MySQL together, if this helps. Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Process Monitoring
try getting 'mytop'. Do a google on it... It's like the 'top' utility, but displays information regarding mysql procs. P Ronan Lucio [EMAIL PROTECTED] 04/20/2004 06:58 PM To: [EMAIL PROTECTED] cc: Subject:Process Monitoring Hi, We have a MySQL-Server-4.0.17 installed in our enterprise working fine... :-) Some times our application takes too bandwidth from the database server. So, I´m trying to figure out what (perhaps what select) is taking so manu bandwidth. SHOW FULL PROCESSLIST shows me the follow: mysql show full processlist; +--+-+---+-+-+-- +---+---+ | Id | User| Host | db | Command | Time | State | Info | +--+-+---+-+-+-- +---+---+ | 3303 | filmeemcasa | 200.247.127.132:43570 | filmeemcasa | Sleep | 74 | | NULL | | 4226 | filmeemcasa | 200.247.127.132:33898 | filmeemcasa | Sleep | 0 | | NULL | | 4276 | filmeemcasa | 200.247.127.132:47532 | filmeemcasa | Sleep | 9 | | NULL | | 4280 | filmeemcasa | 200.247.127.132:47918 | filmeemcasa | Sleep | 11 | | NULL | | 4281 | filmeemcasa | 200.247.127.132:47933 | filmeemcasa | Sleep | 315 | | NULL | | 4308 | filmeemcasa | 200.247.127.132:50119 | filmeemcasa | Sleep | 72 | | NULL | | 4312 | filmeemcasa | 200.247.127.132:50284 | filmeemcasa | Sleep | 0 | | NULL | | 4315 | filmeemcasa | 200.247.127.132:50287 | filmeemcasa | Sleep | 71 | | NULL | | 4329 | spv | 200.247.127.132:54006 | spvvideo| Sleep | 76 | | NULL | | 4330 | filmeemcasa | pardal:3180 | NULL| Sleep | 2163 | | NULL | | 4331 | filmeemcasa | pardal:3184 | NULL| Sleep | 64 | | NULL | | 4333 | filmeemcasa | 200.247.127.132:54025 | filmeemcasa | Sleep | 72 | | NULL | | 4338 | filmeemcasa | 200.247.127.132:54640 | filmeemcasa | Sleep | 10 | | NULL | | 4339 | root| localhost | NULL| Query | 0 | NULL | show full processlist | +--+-+---+-+-+-- +---+---+ 14 rows in set (0.00 sec) but I can´t get to see the select that is being executed. Does anyone knows how can I find it? Thanks, Ronan -- 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: Process Monitoring
Are you logging slow queries? If so, have you looked in the slow query log? -Original Message- From: Ronan Lucio To: [EMAIL PROTECTED] Sent: 4/20/04 1:58 PM Subject: Process Monitoring Hi, We have a MySQL-Server-4.0.17 installed in our enterprise working fine... :-) Some times our application takes too bandwidth from the database server. So, I´m trying to figure out what (perhaps what select) is taking so manu bandwidth. SHOW FULL PROCESSLIST shows me the follow: mysql show full processlist; +--+-+---+-+-+-- +---+---+ | Id | User| Host | db | Command | Time | State | Info | +--+-+---+-+-+-- +---+---+ | 3303 | filmeemcasa | 200.247.127.132:43570 | filmeemcasa | Sleep | 74 | | NULL | | 4226 | filmeemcasa | 200.247.127.132:33898 | filmeemcasa | Sleep | 0 | | NULL | | 4276 | filmeemcasa | 200.247.127.132:47532 | filmeemcasa | Sleep | 9 | | NULL | | 4280 | filmeemcasa | 200.247.127.132:47918 | filmeemcasa | Sleep | 11 | | NULL | | 4281 | filmeemcasa | 200.247.127.132:47933 | filmeemcasa | Sleep | 315 | | NULL | | 4308 | filmeemcasa | 200.247.127.132:50119 | filmeemcasa | Sleep | 72 | | NULL | | 4312 | filmeemcasa | 200.247.127.132:50284 | filmeemcasa | Sleep | 0 | | NULL | | 4315 | filmeemcasa | 200.247.127.132:50287 | filmeemcasa | Sleep | 71 | | NULL | | 4329 | spv | 200.247.127.132:54006 | spvvideo| Sleep | 76 | | NULL | | 4330 | filmeemcasa | pardal:3180 | NULL| Sleep | 2163 | | NULL | | 4331 | filmeemcasa | pardal:3184 | NULL| Sleep | 64 | | NULL | | 4333 | filmeemcasa | 200.247.127.132:54025 | filmeemcasa | Sleep | 72 | | NULL | | 4338 | filmeemcasa | 200.247.127.132:54640 | filmeemcasa | Sleep | 10 | | NULL | | 4339 | root| localhost | NULL| Query | 0 | NULL | show full processlist | +--+-+---+-+-+-- +---+---+ 14 rows in set (0.00 sec) but I can´t get to see the select that is being executed. Does anyone knows how can I find it? Thanks, Ronan -- 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: Query Speed
Craig Gardner wrote: Thank you very much. That's what fixed my problem. Robert J Taylor wrote: Can you restrict to Not Null instead of != ? (I.e, can you scrub the data not to have empty strings?). The explain shows 3 extra where calculations per row...that's painful. Great! Glad that solved your problem. Robert Taylor [EMAIL PROTECTED] P.S. I didn't respond to the list initially, so I'm correcting that now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: speeding up UPDATE and INSERT
At 12:24 -0700 4/20/04, Matthias Eireiner wrote:
Hi there,
I'm currently writing a Data Mining program in Perl, my problem is that the
writing to the database takes awful long.
Are there any ideas how to speed up the inserting and the updating, besides
using the bind operator?!
the SQLstatement looks like
INSERT INTO `Counts` (`id`, `date`, `count`, `avg)VALUES ('', CURDATE(),
'$count', '$avg')
or
UPDATE `Counts` SET `count` = '$count', `avg` = '$avg' WHERE `id` = '$id'
so if anybody has an idea how to get this done faster (dumping the data to a
csv file first and updating all at once, or something like this) I would
love to hear it.
thanks a lot in advance.
You might find some useful ideas here:
http://dev.mysql.com/doc/mysql/en/Insert_speed.html
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Randomly selecting from table
Guess you could use the rand() function and look for a matching row id!?! Is it possible to randomly select from all entries in a table, but have it be 2 different ones each time? If so, what documentation should I be looking at? I am using PHP and MySQL together, if this helps. Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Process Monitoring
Hi, The mysql administrator tool is a lot better for this. They still seem to be working out some bugs, but there is a lot of flexibility with the graphs. I have been able to see peaks in bandwidth and queries that I never noticed before.. Thanks, Eric At 12:35 PM 4/20/2004, Peter J Milanese wrote: try getting 'mytop'. Do a google on it... It's like the 'top' utility, but displays information regarding mysql procs. P Ronan Lucio [EMAIL PROTECTED] 04/20/2004 06:58 PM To: [EMAIL PROTECTED] cc: Subject:Process Monitoring Hi, We have a MySQL-Server-4.0.17 installed in our enterprise working fine... :-) Some times our application takes too bandwidth from the database server. So, I´m trying to figure out what (perhaps what select) is taking so manu bandwidth. SHOW FULL PROCESSLIST shows me the follow: mysql show full processlist; +--+-+---+-+-+-- +---+---+ | Id | User| Host | db | Command | Time | State | Info | +--+-+---+-+-+-- +---+---+ | 3303 | filmeemcasa | 200.247.127.132:43570 | filmeemcasa | Sleep | 74 | | NULL | | 4226 | filmeemcasa | 200.247.127.132:33898 | filmeemcasa | Sleep | 0 | | NULL | | 4276 | filmeemcasa | 200.247.127.132:47532 | filmeemcasa | Sleep | 9 | | NULL | | 4280 | filmeemcasa | 200.247.127.132:47918 | filmeemcasa | Sleep | 11 | | NULL | | 4281 | filmeemcasa | 200.247.127.132:47933 | filmeemcasa | Sleep | 315 | | NULL | | 4308 | filmeemcasa | 200.247.127.132:50119 | filmeemcasa | Sleep | 72 | | NULL | | 4312 | filmeemcasa | 200.247.127.132:50284 | filmeemcasa | Sleep | 0 | | NULL | | 4315 | filmeemcasa | 200.247.127.132:50287 | filmeemcasa | Sleep | 71 | | NULL | | 4329 | spv | 200.247.127.132:54006 | spvvideo| Sleep | 76 | | NULL | | 4330 | filmeemcasa | pardal:3180 | NULL| Sleep | 2163 | | NULL | | 4331 | filmeemcasa | pardal:3184 | NULL| Sleep | 64 | | NULL | | 4333 | filmeemcasa | 200.247.127.132:54025 | filmeemcasa | Sleep | 72 | | NULL | | 4338 | filmeemcasa | 200.247.127.132:54640 | filmeemcasa | Sleep | 10 | | NULL | | 4339 | root| localhost | NULL| Query | 0 | NULL | show full processlist | +--+-+---+-+-+-- +---+---+ 14 rows in set (0.00 sec) but I can´t get to see the select that is being executed. Does anyone knows how can I find it? Thanks, Ronan -- 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: Gripe with MySQL
-Original Message- From: Michael McTernan [mailto:[EMAIL PROTECTED] if you don't have error checking in yourself, that's your problem not mysql's problem. True, but still doesn't help when someone inexperienced with MySQL makes a mistake and buggers the database... again. I don't see why you object to making things easier to use, even if it is only activated by a -n00b option in mysql_safe. It also doesn't help when you have ODBC-connected software from other vendors that does hostile things. We've got one app here that likes to try to insert records with a primary key of in one of our databases. Unfortunatly and NULL aren't the same thing, so setting NOT NULL doesn't help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb Hot Backup Tool
We're using an evaluation version right now - works great, though I've yet to test a backup (and I need to do that soon). David. - Original Message - From: McConnell, Ann M. [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 10:54 AM Subject: Innodb Hot Backup Tool Does anyone have any experience with Innodb Hot Backup Tool? Thanks, Ann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Process Monitoring
Victor, Are you logging slow queries? If so, have you looked in the slow query log? Yes, I am. But, it shows many of them and no one at this time. Or, it doesn´t show slow queries when the problem is occurring. I restarted the server 1 hour ago and after I did it, the server seems to be OK. I´m thinking that MySQL can be losting itself in the memory usage. Is it possible? But, I can´t understand what can made so many traffic going out from the server... :-/ Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
speeding up UPDATE and INSERT
Hi there,
I'm currently writing a Data Mining program in Perl, my problem is that the
writing to the database takes awful long.
Are there any ideas how to speed up the inserting and the updating, besides
using the bind operator?!
the SQLstatement looks like
INSERT INTO `Counts` (`id`, `date`, `count`, `avg)VALUES ('', CURDATE(),
'$count', '$avg')
or
UPDATE `Counts` SET `count` = '$count', `avg` = '$avg' WHERE `id` = '$id'
so if anybody has an idea how to get this done faster (dumping the data to a
csv file first and updating all at once, or something like this) I would
love to hear it. thanks a lot in advance.
regards
Matthias
_
Matthias Eireiner
email: [EMAIL PROTECTED]
www.bvcapital.com
_
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Process Monitoring
Do you have any around the approximate time? The timestamp will be will the query was written to the slow log and how long it took. What are the symptoms that lead you to believe that it is a slow running query? -Original Message- From: Ronan Lucio To: Victor Pendleton; [EMAIL PROTECTED] Sent: 4/20/04 3:20 PM Subject: Re: Process Monitoring Victor, Are you logging slow queries? If so, have you looked in the slow query log? Yes, I am. But, it shows many of them and no one at this time. Or, it doesn´t show slow queries when the problem is occurring. I restarted the server 1 hour ago and after I did it, the server seems to be OK. I´m thinking that MySQL can be losting itself in the memory usage. Is it possible? But, I can´t understand what can made so many traffic going out from the server... :-/ Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Process Monitoring
Victor, Do you have any around the approximate time? I know the time that the problem occurred. In the slow-log, it shows the line Time. Is it line the hour the query ran? The timestamp will be will the query was written to the slow log and how long it took. What are the symptoms that lead you to believe that it is a slow running query? Actually, I don´t know if the problem´s origem is a slow query. I´d like to know what query was running in such moment to see if exist a query receiving too many data. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Gripe with MySQL
On Tue, 20 Apr 2004 15:33:18 -0400, Peter J Milanese wrote: Just something I noticed missing here The lack of error checking on the server side means better performance in my opinion. No doubt. When you're throwing a couple thousand hits per second at it, this is visible. I would have to agree that error checking does belong on the client side (at least from my experience), and it can be a And what error checking are you referring to? If you are making a general statement then I'll have to disagree with you and thankfully many other tools do as well. Server side checks are preferred over client side checks in many applications. I hope that when you build a web application you don't rely purely on Javascript to validate the data entered on a form. See, speed isn't everything all the time. Sure if you're writing a device driver or something yeah you want pure blazing speed. But when dealing with data? Many are more than willing to give up speed for a bit more security. lot more productive as a database, and not a workflow tool. Of course, I do agree about the ENUM and stuff being annoying, but if you know to work with it, performance is much more valuable. It boils down to how you intend to use it. This is why at the start I said that yes I plan to use MySQL and I explained where and how. It's just that with the lack of server side checking (Such as records coming in without all the data they should have.) simply means that it's not suitable (ie dangerous) for some purposes. That's all. Most of the other DBMS' that I've worked with include this. MySQL is the first one that I've used that did not. As it was pointed out, they didn't support transactions and so it makes sense that they did as they did. Personally I disagree with that design decision is all. Heh you ever been in a design meeting? Where all the developers get together to discuss how to design/approach some application they're building? Almost never do you see everyone agreeing with everyone else. It's usually not a matter of good design bad design but more a matter of preference. Each has their pros and cons. One of the Pros to the design that MySQL took is performance. They don't have to worry about checking various things. I've already mentioned one of the Cons. IMO I would like to see MySQL have the ability to function similar to say SQLServer in this regard. Make it a toggle. This way if you are using it in a situation where speed is more important to you then you just don't enable server side validation. Well, we'll see how things go. According to some they are already adding features that will make MySQL more flexible so that those who don't need/want the server side checks can happily not have them and those who do can. -- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) The worth of your opinion is in direct proportion to the number of people who have asked for it. My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Process Monitoring
On Tue, Apr 20, 2004 at 01:13:14PM -0700, Eric wrote: Hi, The mysql administrator tool is a lot better for this. They still seem to be working out some bugs, but there is a lot of flexibility with the graphs. I have been able to see peaks in bandwidth and queries that I never noticed before.. Depends what you want/need. mytop is a fairly minimalist console tool, like top. MySQL Administrator is the opposite. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 219 days, processed 4,118,124,222 queries (216/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb Hot Backup Tool
On Tue, Apr 20, 2004 at 01:54:37PM -0400, McConnell, Ann M. wrote: Does anyone have any experience with Innodb Hot Backup Tool? Yes. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 219 days, processed 4,118,140,114 queries (216/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Process Monitoring
The timestamp is when the query was written to the log. You will have to subtract the query time to get when the query began. Unless you have queries that are running for long durations the timestamp and actual time should be close. Are you performing reads or writes to this disk? Have you captured or monitored the CPU and memory usage during this time? -Original Message- From: Ronan Lucio To: Victor Pendleton; [EMAIL PROTECTED] Sent: 4/20/04 3:34 PM Subject: Re: Process Monitoring Victor, Do you have any around the approximate time? I know the time that the problem occurred. In the slow-log, it shows the line Time. Is it line the hour the query ran? The timestamp will be will the query was written to the slow log and how long it took. What are the symptoms that lead you to believe that it is a slow running query? Actually, I don´t know if the problem´s origem is a slow query. I´d like to know what query was running in such moment to see if exist a query receiving too many data. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Process Monitoring
The timestamp is when the query was written to the log. You will have to subtract the query time to get when the query began. Unless you have queries that are running for long durations the timestamp and actual time should be close. OK, so, slow query shouldn´t be my problem, because there is no slow query logged when the system is consuming too many bandwidth. Are you performing reads or writes to this disk? The machine is only database server. It has many write disk process, but all of them are mysql task like temporary tables and update queries. Have you captured or monitored the CPU and memory usage during this time? Yes, it seems to be fine. The main problem seems to be the large outgoing traffic there I captured with MRTG graphics. And I know that this isn´t an attack or anything else because the MRTG show the traffic going out from the database server and going in to the application server. Running a top in the application server, it show just the coldfusion process using the CPU and in the database server, just the mysql using de CPU. Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySql Client Program Questions
I have a couple of questions concerning the MySql client program. 1. Are the option for the client program, i.e. --auto-rehash, etc. documented anywhere? I searched the included HTML file and could not find a reference to the client options. What does the --auto-rehash option do? Heck, what do all of the rest of the options do? 2.When in the client program, what do the clear, ego, go, and rehash commands do? Is there documentation for these commands? 3. Speaking specifically of Win2K platforms, what would be required to install just the client program for other users to be able to access the database server program residing on a different machine? Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[book announcement] High Performance MySQL (O'Reilly) is now shipping...
MySQL users, I just wanted to send a quick note to let anyone interested know that High Performance MySQL (the book I started a long time ago) is now available and shipping. O'Reilly brought copies to the MySQL Conference last week and several folks have reported Amazon.com shipping, so it's time to say something. :-) If you were one who pre-ordered on Amazon.com, thanks! Your copy should show up soon. Book site: http://HighPerformanceMySQL.com/ On Amazon.com: http://www.amazon.com/exec/obidos/ASIN/0596003064/jeremydzawodny/ref=nosim On the O'Reilly site: http://www.oreilly.com/catalog/hpmysql/ There's a sample chapter (Replication) on-line as well as the TOC and description. The description says... As users come to depend on MySQL, they find that they have to deal with issues of reliability, scalability, and performance--issues that are not well documented but are critical to a smoothly functioning site. This book is an insider's guide to these little understood topics. Author Jeremy Zawodny has managed large numbers of MySQL servers for mission-critical work at Yahoo!, maintained years of contacts with the MySQL AB team, and presents regularly at conferences. Jeremy and Derek have spent months experimenting, interviewing major users of MySQL, talking to MySQL AB, benchmarking, and writing some of their own tools in order to produce the information in this book. In High Performance MySQL you will learn about MySQL indexing and optimization in depth so you can make better use of these key features. You will learn practical replication, backup, and load-balancing strategies with information that goes beyond available tools to discuss their effects in real-life environments. And you'll learn the supporting techniques you need to carry out these tasks, including advanced configuration, benchmarking, and investigating logs. /shameless_plug Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 219 days, processed 4,118,373,992 queries (216/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to speed up a simple query? need some help here...
Create composite index on (cat, date). Use EXPLAIN to see if MySQL uses index: http://dev.mysql.com/doc/mysql/en/EXPLAIN.html This partially solved my problem. Thanks a lot. However I am facing a new problem here. The query where I am ordering by a column is much more slowly than the same query NOT using SQL_CALC_FOUND_ROWS select SQL_CALC_FOUND_ROWS * from table by date desc - this query takes about 1.2 s select * from table by date desc - this query takes about 0.1 s Using explain I have noticed that the first query is using filesort and I cannot figure out why exactly... I could use count() to figure out the total number of results for the navigation and this would be defintelly much faster since now as I have tested but I was thinking if anyone else had this problem before and if you have found any work around... Thanks, Arthur -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [book announcement] High Performance MySQL (O'Reilly) is now shipping...
I got mine (even autographed) at the MySQL Users Conference. Excellent reading -- definitely required for anyone whose livelihood depends on MySQL. (this was a non-paid endorsement) Robert Taylor [EMAIL PROTECTED] Jeremy Zawodny wrote: MySQL users, I just wanted to send a quick note to let anyone interested know that High Performance MySQL (the book I started a long time ago) is now available and shipping. O'Reilly brought copies to the MySQL Conference last week and several folks have reported Amazon.com shipping, so it's time to say something. :-) If you were one who pre-ordered on Amazon.com, thanks! Your copy should show up soon. Book site: http://HighPerformanceMySQL.com/ On Amazon.com: http://www.amazon.com/exec/obidos/ASIN/0596003064/jeremydzawodny/ref=nosim On the O'Reilly site: http://www.oreilly.com/catalog/hpmysql/ There's a sample chapter (Replication) on-line as well as the TOC and description. The description says... As users come to depend on MySQL, they find that they have to deal with issues of reliability, scalability, and performance--issues that are not well documented but are critical to a smoothly functioning site. This book is an insider's guide to these little understood topics. Author Jeremy Zawodny has managed large numbers of MySQL servers for mission-critical work at Yahoo!, maintained years of contacts with the MySQL AB team, and presents regularly at conferences. Jeremy and Derek have spent months experimenting, interviewing major users of MySQL, talking to MySQL AB, benchmarking, and writing some of their own tools in order to produce the information in this book. In High Performance MySQL you will learn about MySQL indexing and optimization in depth so you can make better use of these key features. You will learn practical replication, backup, and load-balancing strategies with information that goes beyond available tools to discuss their effects in real-life environments. And you'll learn the supporting techniques you need to carry out these tasks, including advanced configuration, benchmarking, and investigating logs. /shameless_plug Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Website
On 20.04.2004 17:53 (+0100), Lehman, Jason (Registrar's Office) wrote: Does anyone know what is going on with the MySQL website? No, it's accessible as usual. But with Firefox, only at the second try. Could also be a browser problem, I'm using an older nightly build. -- Yves Goergen [EMAIL PROTECTED] BlackBoard Internet Newsboard System -- blackboard.unclassified.de Free (GPL), easy to use and install, secure, innovative! (PHP+MySQL) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Modelling specialized M:N relationships?
Hi, i have a M:N relationship between PRODUCT and PRICE. 1 product may be associated to N prices and 1 prices may belong to N products. I got special cases the some kinda product MUST have only 1 price associated with it. I don't what would be the best way to model this flag for the special case. So far, i got 3 tables. - PRODUCT (PRODUCT_ID as PK) - PRICE (PRICE_ID as PK) - PRODUCT_PRICE (with fields PRODUCT_ID and PRICE_ID) Where should i put this flg or should tie the relations??? Thanks, ltcmelo __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
genome sequence
hi I am trying to enter a genome sequence of length 170 in a table. I have created the table as create table sequence(Seq blob); I am using Python script to enter this sequence into the table. I am getting an error OperationalError: (2006, 'MySQL server has gone away') I am not able to store this sequence into the table. what is wrong??? Liz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Gripe with MySQL
The lack of error checking on the server side means better performance in my opinion. When you're throwing a couple thousand hits per second at it, this is visible. If error checking is largely dispensed with, client and server will be both be faster, except during crashes, which may be frequent. Generally serverside error checking is quicker. So IMO, the above is incorrect. PB - Original Message - From: Peter J Milanese To: Stormblade Cc: Donny Simonton ; [EMAIL PROTECTED] ; [EMAIL PROTECTED] ; 'Stormblade' Sent: Tuesday, April 20, 2004 2:33 PM Subject: RE: Gripe with MySQL Just something I noticed missing here The lack of error checking on the server side means better performance in my opinion. When you're throwing a couple thousand hits per second at it, this is visible. I would have to agree that error checking does belong on the client side (at least from my experience), and it can be a lot more productive as a database, and not a workflow tool. Of course, I do agree about the ENUM and stuff being annoying, but if you know to work with it, performance is much more valuable. P Stormblade [EMAIL PROTECTED] 04/20/2004 01:20 PM To: [EMAIL PROTECTED], Donny Simonton [EMAIL PROTECTED] cc: [EMAIL PROTECTED], 'Stormblade' [EMAIL PROTECTED] Subject:RE: Gripe with MySQL On Tue, 20 Apr 2004 17:57:08 +0100, Michael McTernan wrote: Donny, I think you misunderstand the point of my comment, and possibly Stormblade's too. The point is that there are some things that are misleading, and it would be kinder for MySQL to report errors than silently performing something unexpected and continuing without warning - at least if you run rm -rf /* you'll notice pretty quickly that everything has disappeared and learn not to do it again :D That's for sure. Not only that but you CAN do something to prevent it. You could for example, replace the rm command with another version which doesn't allow you to do that without many checks and prompts :) But doing an rm -fr /* is very different from what we were talking about. If you use a client like PHPMyadmin or one of the other 80 million that are around you won't have to worry about error checking because they have already done it for you. Using MySQL + JDBC to make a very custom app, sorry. Same here for now. This is where I feel MySQL shines and why I've chosen to use it (Besides the cost of course). Not only that but say you have a app with a ton of SQL. How can you verify all the SQL statements include ALL the fields. Checking each field is the job of the client yes but if an SQL statement leaves out a field that is required by the database...that's just it. None of them are required. MySQL will happily fill in your blanks for you so you won't know there were blanks until you view it. if you don't have error checking in yourself, that's your problem not mysql's problem. True, but still doesn't help when someone inexperienced with MySQL makes a mistake and buggers the database... again. I don't see why you object to making things easier to use, even if it is only activated by a -n00b option in mysql_safe. As I mentioned in my other reply there are some checks that belong on the client and some that really belong on the server. So yes some error checking SHOULD be the server's problem. But not all of it. Just saying if you don't have error checking in yourself doesn't help. What type of error checking? There's not just one global error checking type. Thanks, Mike -Original Message- From: Donny Simonton [mailto:[EMAIL PROTECTED] Sent: 20 April 2004 17:37 To: 'Michael McTernan'; 'Stormblade' Cc: [EMAIL PROTECTED] Subject: RE: Gripe with MySQL Everybody should remember as well, if you run rm -rf /*.* on your server you will delete everything from your server, but linux will stay running. Even though that's not documented either. If you use a client like PHPMyadmin or one of the other 80 million that are around you won't have to worry about error checking because they have already done it for you. Now as far as your clients/customers, if you don't have error checking in yourself, that's your problem not mysql's problem. Donny -Original Message- From: Michael McTernan [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 9:04 AM To: Stormblade Cc: [EMAIL PROTECTED] Subject: RE: Gripe with MySQL Hi, ENUM columns can also trip you up if you insert a value outside of the ENUM; an empty string is inserted instead. This is documented behaviour (mysql.com seems to be going slowly though, so can't dig a reference right now), even if it is undesired in some cases.
genome sequence
hi, I am trying to enter genome sequences of length 170 and more into mysql database. I have created a table sequence like: create table sequence(seq blob); i am using python scripts to put the sequence into this field. the python GUI gives me this error: OperationalError: (2006, 'MySQL server has gone away') I am not able to enter the sequence. what is wrong??? Liz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
