Error on startup
Hi. My system is Slackware 9.1. MySQL is the default version installed on Slackware cdrom while system installation, so I hope all the path are ok :) Then launched mysqld_install_db... but when I try to startup the server an error occured wrote in /usr/local/mysql/ginlemon.err like this: 3:49:08 /usr/local/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) This is a tipical error for me, almost everytime I install mysql..:-( Any one can put me on the right way? Thanx, Lorenzo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie: Rewriting A Query- How?
Anyone any idea how I could rewrite this as a join - all the indexes are in place and each of the components of this work fine - its only when the combined subqueries are passed to the root query does it appear to go wrong (Takes around 1.5 mins to complete and causes 100% processor utilisation while executing). (May be something for the developers to look at). But in the meantime I need to get this going SELECT PROJID,PROJECTNAME FROM S_PROJECTREGISTER WHERE SNAPSHOTID = 56 AND PROJID IN ( SELECT PROJID FROM SL_PROGPROJ WHERE SNAPSHOTID = 56 AND PROGID IN ( SELECT PROGID FROM SL_PORTPROG WHERE SNAPSHOTID = 56 AND PORTID IN(100994,100996) ) ) AND PROJECTSTATUS 'Potential' Ken mailto:[EMAIL PROTECTED]
Re: Error on startup
Lorenzo Rossi [EMAIL PROTECTED] wrote: Hi. My system is Slackware 9.1. MySQL is the default version installed on Slackware cdrom while system installation, so I hope all the path are ok :) Then launched mysqld_install_db... but when I try to startup the server an error occured wrote in /usr/local/mysql/ginlemon.err like this: 3:49:08 /usr/local/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) This is a tipical error for me, almost everytime I install mysql..:-( Any one can put me on the right way? Use perror to see error message: http://www.mysql.com/doc/en/perror.html $ perror 13 Error code 13: Permission denied Check permissions on the data dir. MySQL should be owner of the MySQL data dir. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: Rewriting A Query- How?
Hi Ken, you might try: SELECT s.PROJID,s.PROJECTNAME FROM S_PROJECTREGISTER s INNER JOIN SL_PROGPROJ l ON s.PROJID = l.PROJID AND l.SNAPSHOTID = 56 INNER JOIN SL_PORTPROG p ON l.PROGID = p.PROGID AND p.SNAPSHOTID = 56 AND p.PORTID IN (100994,100996) WHERE s.SNAPSHOTID = 56 AND s.PROJECTSTATUS 'Potential' Hope this helps, /Johan Ken Brown wrote: Anyone any idea how I could rewrite this as a join - all the indexes are in place and each of the components of this work fine - its only when the combined subqueries are passed to the root query does it appear to go wrong (Takes around 1.5 mins to complete and causes 100% processor utilisation while executing). (May be something for the developers to look at). But in the meantime I need to get this going SELECT PROJID,PROJECTNAME FROM S_PROJECTREGISTER WHERE SNAPSHOTID = 56 AND PROJID IN ( SELECT PROJID FROM SL_PROGPROJ WHERE SNAPSHOTID = 56 AND PROGID IN ( SELECT PROGID FROM SL_PORTPROG WHERE SNAPSHOTID = 56 AND PORTID IN(100994,100996) ) ) AND PROJECTSTATUS 'Potential' Ken mailto:[EMAIL PROTECTED] -- Johan Hk, Facility Engineering Group - MailTo:[EMAIL PROTECTED] - http://www.pythagoras.se Pythagoras AB, Stormbyv. 2-4, SE-163 55 SPNGA, Sweden Phone: +46 8 760 00 10 Fax: +46 8 761 22 77 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join Locked Tables ?
Lianghwa Jou [EMAIL PROTECTED] wrote: Mysql seems to insists that all tables must be locked if one table is locked in a select statement. Why ? For example- lock tables A WRITE, B WRITE; select * from A, B, C where A.id=B.id and A.id=C.id; Mysql will complain that table C is not locked. Is there a way to avoid locking table C ? I only want to modify table A and B. Table C is also used in some other select statement with other tables. If I lock table C, I will have to lock all these other tables too. Yes, you should lock all tables that you are going to use. If you want table C will readable for other threads you can set READ lock on this table. -- 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]
Performance problem with 4.0.18
Hello All! After we converted our tables from MyISAM to InnoDB the database became very slow! The same database on another machine on MySQL 3.23.58 works very good. The machines are identical, my.cnf files are the same. What can be the reason of such slow performance? Thank you! Best regards, Andrey Chernyh. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mySQL, mySQL Max and stored procedure support (version problem?)
I'm running: mysql Ver 12.21 Distrib 4.0.15, for suse-linux (i686) ps says I'm running mysql-max mysql21397 0.0 1.7 71216 16064 pts/8 S10:32 0:00 /usr/sbin/mysqld-max that *should* support stored procedures, shouldn't it? but this fail create procedure pippo begin select 1 end as well as create procedure 'pippo' begin select 1 end and many other combination Is my syntax wrong? am I using the wrong version of mySQL to have SP support? Should I configure anything to have SP support? If I'm using the wrong version of mySQL which one has SP support and is considered the most stable? thx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Saving file into database
Hi, I would like to save a binary file into a mysql database, for later being able to use the file. I am using a perl interafce. Is this at all possible??? And would it be possible to then read that file from a c++ interface? would be greatful for any help/advices! Isa _ Hitta rätt på nätet med MSN Sök http://search.msn.se/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL, mySQL Max and stored procedure support (version problem?)
Hi, I'm running: mysql Ver 12.21 Distrib 4.0.15, for suse-linux (i686) ps says I'm running mysql-max mysql21397 0.0 1.7 71216 16064 pts/8 S10:32 0:00 /usr/sbin/mysqld-max that *should* support stored procedures, shouldn't it? As far as I know, MySQL 5 support stored procedures. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GRANT REVOKE Syntax problem
Hi, I have a perhaps simple problem, but it's a problem for me: I have made the following GRANT statement: mysql GRANT ALL ON ser.* TO [EMAIL PROTECTED]; Query OK, 0 rows affected (0.01 sec) But when I want to REVOKE the GRANT I get the following syntax error: mysql REVOKE ALL ON ser.* TO [EMAIL PROTECTED]; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'TO [EMAIL PROTECTED]' at line 1 What is wrong in the syntax? I only changed GRANT with REVOKE. I am using mySQL version 4.0.15. Franz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL, mySQL Max and stored procedure support (version problem?)
On Tue, 9 Mar 2004 10:52:38 +0100 Martijn Tonies [EMAIL PROTECTED] wrote: I'm running: mysql Ver 12.21 Distrib 4.0.15, for suse-linux (i686) ps says I'm running mysql-max mysql21397 0.0 1.7 71216 16064 pts/8 S10:32 0:00 /usr/sbin/mysqld-max that *should* support stored procedures, shouldn't it? As far as I know, MySQL 5 support stored procedures. But what about Max version? Docs are not crystal clear. While it is clearly stated that mySQL started to support SP from version 5, nothing is said about Max where docs just say it supports them but it is not clear from which version. It is even not that clear from which source package should I start from to have Max or plain mySQL. I gave a look to SUSE source package of release 4 and it seems that Max and plain are built from the same tarball. Since I'm not sure if I have a version that support SP it is difficult to see if my syntax is wrong or it is a problem of version. Could anyone post a correct 5 line example of working stored procedure to test? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GRANT REVOKE Syntax problem
* Franz Edler I have a perhaps simple problem, but it's a problem for me: I have made the following GRANT statement: mysql GRANT ALL ON ser.* TO [EMAIL PROTECTED]; Query OK, 0 rows affected (0.01 sec) But when I want to REVOKE the GRANT I get the following syntax error: mysql REVOKE ALL ON ser.* TO [EMAIL PROTECTED]; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'TO [EMAIL PROTECTED]' at line 1 What is wrong in the syntax? I only changed GRANT with REVOKE. I am using mySQL version 4.0.15. It's GRANT ... TO ... and REVOKE ... FROM URL: http://www.mysql.com/doc/en/GRANT.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql 1gb memory limit?
In order to get the max performance from our servers we're reading every bit we can lay our hands on about performance tuning. In this document: http://www.phpconference.de/2003/slides/business_track/kneschke_webserver-performance-tuning.pdf (it's in German), it says on page 9 that MySQL 4.1.x on 32-bit Intel Linux can only use 1GB!?!? Is this true? Anyway to overcome this limit? Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL, mySQL Max and stored procedure support (version problem?)
Ivan Sergio Borgonovo [EMAIL PROTECTED] wrote: On Tue, 9 Mar 2004 10:52:38 +0100 Martijn Tonies [EMAIL PROTECTED] wrote: I'm running: mysql Ver 12.21 Distrib 4.0.15, for suse-linux (i686) ps says I'm running mysql-max mysql21397 0.0 1.7 71216 16064 pts/8 S10:32 0:00 /usr/sbin/mysqld-max that *should* support stored procedures, shouldn't it? As far as I know, MySQL 5 support stored procedures. But what about Max version? Docs are not crystal clear. While it is clearly stated that mySQL started to support SP from version 5, nothing is said about Max where docs just say it supports them but it is not clear from which version. 5 is a number of version. MySQL-Max is a name of MySQL server, where Max means that this MySQL server supports InnoDB and BDB storage engines. It is even not that clear from which source package should I start from to have Max or plain mySQL. I gave a look to SUSE source package of release 4 and it seems that Max and plain are built from the same tarball. Since I'm not sure if I have a version that support SP it is difficult to see if my syntax is wrong or it is a problem of version. Could anyone post a correct 5 line example of working stored procedure to test? For example: mysql delimiter | mysql create procedure pippo () - begin - select 1; - end | Query OK, 0 rows affected (0.07 sec) Syntax of stored procedures is described at: http://www.mysql.com/doc/en/CREATE_PROCEDURE.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving file into database
Yes, it's possible. Just make sure you quote it (see the Perl DBI docs for the quote method) before you insert it. j- k- On Tuesday 09 March 2004 12:49 am, Isa Wolt wrote: Hi, I would like to save a binary file into a mysql database, for later being able to use the file. I am using a perl interafce. Is this at all possible??? And would it be possible to then read that file from a c++ interface? -- Joshua J. Kugler Fairbanks, Alaska Computer Consultant--Systems Designer .--- --- ... ..- .--.- ..- --. .-.. . .-. [EMAIL PROTECTED] ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Spanish Accents
Hello, I am building a web page using Windows 2000 Server, IIS 5.0, ASP (with vbscript) and MySQL database with MySQL Connector/ODBC. The content of this web page is in Spanish thus the database will contain letters with Spanish accents. My problem is that the Spanish characters are not displayed correctly on my web page. Querying the database with the mysql client I see that the character ó in the word Supervisión is ok but when my web app queries the database the web page displays Supervisi¢n. The ó character seems to be switched for the ¢ character. Does anyone know why this is happening and how I can fix it? mysql select * from menu_data where service_id = 8; +-+-+--- --+ | service_id| service_identifier | service_order | +-+-+--- --+ | 8 | Supervisión | 8 | +-+-+--- --+ Thanks, Troy *** DISCLAIMER: Este mensaje contiene información propietaria de la cual parte o toda puede contener información confidencial o protegida legalmente. Esta exclusivamente destinado al usuario de destino. Si, por un error de envio o transmisión, ha recibido este mensaje y usted no es el destinatario del mismo, por favor, notifique de este hecho al remitente. Si no es el destinatario final de este mensaje no debe usar, informar, distribuir, imprimir, copiar o difundir este mensaje bajo ningún medio. - DISCLAIMER: This e-mail contains propietary information some or all of which may be legally privileged. It is for the intended recipient only. If an addressing or transmission error has misdirected this e-mail, please notify the author by replying to this e-mail. If you are not the intended recipient you must not use, disclose, distribute, copy, print or rely on this e-mail. ***
Re: mySQL, mySQL Max and stored procedure support (version problem?)
On Tue, 09 Mar 2004 12:36:30 +0200 Victoria Reznichenko [EMAIL PROTECTED] wrote: 5 is a number of version. MySQL-Max is a name of MySQL server, where Max means that this MySQL server supports InnoDB and BDB storage engines. So this make MaxDB another product Could anyone post a correct 5 line example of working stored procedure to test? For example: mysql delimiter | mysql create procedure pippo () - begin - select 1; - end | Query OK, 0 rows affected (0.07 sec) Syntax of stored procedures is described at: http://www.mysql.com/doc/en/CREATE_PROCEDURE.html Yep but without being sure you've the right software you're always in doubt if you missed something. Anyway result was the same. So I've to upgrade. Any suggestion about which is the way to go to have stored procedure on mySQL. I was compiling 5.0 right now and I had trouble with relayrotate test. First passed, second didn't. Installation script suggest to use --force... Google doesn't offer any page for relayrotate mysql. spasiba -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqladmin processlist and pid
hi listers how would i determine the association between the id from mysqladmin processlist to a pid? what i'm after is that i notice some of the mysql threads have a high cpu utilization. i see this using top (on a linux box). i would like to know which user/program is responsible for tuning purposes. i can use mysqladmin processlist and it gives me a list (including an id) of what processes are running but how do i tie this in with unix' pid? tks tom
Re: Spanish Accents
Hola, special characters contained on a web page should be replaced by special tags for a correct display, for example, the ó in the word Supervisión should be replaced by Oacute; ( You should send SupervisiOacute;n to the browser ) I don't know ASP, I use PHP, and PHP has a funcion, called htmlspecialchars, that converts a string containing special characters on the appropriate string for html visualization. I'm quite sure ASP has an equivalent function. Hasta siempre, Giulio Il giorno 09/mar/04, alle 12:57, Brand, Troy Anthony ha scritto: Hello, I am building a web page using Windows 2000 Server, IIS 5.0, ASP (with vbscript) and MySQL database with MySQL Connector/ODBC. The content of this web page is in Spanish thus the database will contain letters with Spanish accents. My problem is that the Spanish characters are not displayed correctly on my web page. Querying the database with the mysql client I see that the character ó in the word Supervisión is ok but when my web app queries the database the web page displays Supervisi¢n. The ó character seems to be switched for the ¢ character. Does anyone know why this is happening and how I can fix it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Spanish Accents
correctly on my web page. Querying the database with the mysql client I see that the character ó in the word Supervisión is ok but when my web app queries the database the web page displays Supervisi¢n. The ó character seems to be switched for the ¢ character. Does anyone know why this is happening and how I can fix it? Try to set the correct charset / encoding in the HTML using a META-tag. Otherwise you can try to find a function to convert from one charset to another in ASP. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql 1gb memory limit?
I've got a server with 24 gigs in it and it works just fine. About 3 with 8 gigs and a few with 2 gigs. All running 4.1.1. And all of them run without any problems on Xeon's with Hyperthreading. Donny -Original Message- From: Jigal van Hemert [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 4:39 AM To: [EMAIL PROTECTED] Subject: mysql 1gb memory limit? In order to get the max performance from our servers we're reading every bit we can lay our hands on about performance tuning. In this document: http://www.phpconference.de/2003/slides/business_track/kneschke_webserver- performance-tuning.pdf (it's in German), it says on page 9 that MySQL 4.1.x on 32-bit Intel Linux can only use 1GB!?!? Is this true? Anyway to overcome this limit? Regards, Jigal. -- 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: Performance problem with 4.0.18
Can you supply us with an example? Some explain plans to corroborate your reported slowness. -Original Message- From: Andrey Chernyh To: [EMAIL PROTECTED] Sent: 3/9/04 3:35 AM Subject: Performance problem with 4.0.18 Hello All! After we converted our tables from MyISAM to InnoDB the database became very slow! The same database on another machine on MySQL 3.23.58 works very good. The machines are identical, my.cnf files are the same. What can be the reason of such slow performance? Thank you! Best regards, Andrey Chernyh. -- 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[2]: Performance problem with 4.0.18
VP Can you supply us with an example? Some explain plans to corroborate your
VP reported slowness.
Of course.
Here is the query.
It is big and ugly, I'm curently working on system optimization. But
why the same query is good at one machine and bad at another?
SELECT ...
FROM positionReports p
INNER JOIN drivers d ON p.driverID=d.ID
LEFT JOIN reverseGeo rg ON rg.latitude=p.latitude AND rg.longitude=p.longitude
LEFT JOIN reverseGeo rg1 ON rg1.latitude=p.cellLatitude AND
rg1.longitude=p.cellLongitude
LEFT JOIN companyPref cp ON cp.companyID = d.companyID
LEFT JOIN events ev ON p.eventID=ev.ID
INNER JOIN eventGroups evg ON ev.eventGroup=evg.ID
LEFT JOIN reportDetails rd ON rd.reportID=p.ID
WHERE p.`date`='2004-03-07' AND
p.`driverID` IN
(92,85,96,93,86,74,72,83,89,97,78,77,84,75,81,91,98,90,88,105,99,100,82,103,73,95,102,94,87,80,76,104,101,489,79)
AND evg.ID IN ('1','2','3','4','5','6','7','8','9','10','11') GROUP BY
1,2,3,4,5,6,7,8,9,10
ORDER BY p.gmtTime DESC, p.time DESC LIMIT 0,201
+---++---+---+-++--+--+
| table | type | possible_keys
| key | key_len | ref| rows | Extra
|
+---++---+---+-++--+--+
| p | range | IDX_POSITIONREPORTS_DRIVER_ID,IDX_POSITIONREPORTS_DATE
| IDX_POSITIONREPORTS_DRIVER_ID | 8 | NULL | 5679 | Using where;
Using temporary; Using filesort |
| d | eq_ref | PRIMARY,IDX_UNIQUE_DRIVERS_ID
| PRIMARY | 8 | p.driverID |1 |
|
| rg| ref|
IDX_REVERSE_GEO_LATITUDE,IDX_REVERSE_GEO_LONGITUDE,IDX_REVERSE_GEO_LATLON |
IDX_REVERSE_GEO_LATITUDE | 17 | p.latitude |1 |
|
| rg1 | ref|
IDX_REVERSE_GEO_LATITUDE,IDX_REVERSE_GEO_LONGITUDE,IDX_REVERSE_GEO_LATLON |
IDX_REVERSE_GEO_LATITUDE | 17 | p.cellLatitude |1 |
|
| cp| ALL| NULL
| NULL |NULL | NULL | 587 |
|
| ev| eq_ref | PRIMARY,IDX_UNIQUE_EVENTS_ID,IDX_UNIQUE_EVENTGROUPS_ID
| PRIMARY | 8 | p.eventID |1 |
|
| evg | eq_ref | PRIMARY
| PRIMARY | 8 | ev.eventGroup |1 | Using where;
Using index |
| rd| ref| IDX_REPORTDETAILS_REPORTID
| IDX_REPORTDETAILS_REPORTID| 8 | p.ID |1 |
|
+---++---+---+-++--+--+
8 rows in set (0.13 sec)
In process list I always see Copying to tmp table . I guess the
system is limited by hard disk, top shows low process load.
show variables
Result
# Variable_name Value
1 back_log 50
2 basedir /usr/local/mysql/
3 binlog_cache_size 32768
4 bulk_insert_buffer_size 8388608
5 character_set latin1
6 character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8
dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia
hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5
7 concurrent_insert ON
8 connect_timeout 5
9 convert_character_set
10 datadir /var/lib/mysql/
11 default_week_format 0
12 delay_key_write ON
13 delayed_insert_limit 100
14 delayed_insert_timeout 300
15 delayed_queue_size 1000
16 flush OFF
17 flush_time 0
18 ft_boolean_syntax + -()~*:|
19 ft_min_word_len 4
20 ft_max_word_len 254
21 ft_max_word_len_for_sort 20
22 ft_stopword_file (built-in)
23 have_bdb NO
24 have_crypt YES
25 have_innodb YES
26 have_isam YES
27 have_raid NO
28 have_symlink YES
29 have_openssl NO
30 have_query_cache YES
31 init_file
32 innodb_additional_mem_pool_size 67108864
33 innodb_buffer_pool_size 268435456
34 innodb_data_file_path ibdata1:2000M:autoextend
35 innodb_data_home_dir /var/lib/mysql/ibdata/
36 innodb_file_io_threads 4
37 innodb_force_recovery 0
38 innodb_thread_concurrency 8
39
RE: mysql 1gb memory limit?
Yes. There's a limit. Start mysql with --big-tables. I think there's a finer way of doing it, just don't remember what it was ;) P Donny Simonton [EMAIL PROTECTED] 03/09/2004 08:00 AM To: 'Jigal van Hemert' [EMAIL PROTECTED], [EMAIL PROTECTED] cc: Subject:RE: mysql 1gb memory limit? I've got a server with 24 gigs in it and it works just fine. About 3 with 8 gigs and a few with 2 gigs. All running 4.1.1. And all of them run without any problems on Xeon's with Hyperthreading. Donny -Original Message- From: Jigal van Hemert [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 4:39 AM To: [EMAIL PROTECTED] Subject: mysql 1gb memory limit? In order to get the max performance from our servers we're reading every bit we can lay our hands on about performance tuning. In this document: http://www.phpconference.de/2003/slides/business_track/kneschke_webserver- performance-tuning.pdf (it's in German), it says on page 9 that MySQL 4.1.x on 32-bit Intel Linux can only use 1GB!?!? Is this true? Anyway to overcome this limit? Regards, Jigal. -- 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]
Opinion about MaxDB, and Redhat
Hello, I only have one server available to be set up to handle a lot of data. I have looked through some documentation, and am a little confused. So I thought I would get the opinion of someone who has used this stuff. First will I see a performance different on a 2 GHz Intel w/ 500 MB Ram, running RedHat 9 if I set it to run in level 3 as compared to 5 that I am in now? Second I have tables in MS SQL Server that have over 50,000,000,000 records, will SQL Server handle that, and would it be a benefit to use MaxDB? I read through the MaxDB docs and am not sure when it is appropriate to use it instead. Also I am still learning mySQL and RedHat so don't need anything more complex that it has to be. I am used to just installing it and away I go, now I have all these different options. Thanks in advance. Todd Hackathorn
RE: mysql 1gb memory limit?
Peter, I have never heard of such a limit and I have been using it for a while. And --big-tables, which BTW, you can't easily search for on mysql.com, because of the minimum 4 characters in full text indexing, says this: --big-tables Allow large result sets by saving all temporary sets on file. This option prevents most ``table full'' errors, but also slows down queries for which in-memory tables would suffice. Since Version 3.23.2, MySQL is able to handle large result sets automatically by using memory for small temporary tables and switching to disk tables where necessary. So --big-tables, has nothing to do with any limit. Donny -Original Message- From: Peter J Milanese [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 7:32 AM To: Donny Simonton Cc: 'Jigal van Hemert'; [EMAIL PROTECTED] Subject: RE: mysql 1gb memory limit? Yes. There's a limit. Start mysql with --big-tables. I think there's a finer way of doing it, just don't remember what it was ;) P Donny Simonton [EMAIL PROTECTED] 03/09/2004 08:00 AM To: 'Jigal van Hemert' [EMAIL PROTECTED], [EMAIL PROTECTED] cc: Subject:RE: mysql 1gb memory limit? I've got a server with 24 gigs in it and it works just fine. About 3 with 8 gigs and a few with 2 gigs. All running 4.1.1. And all of them run without any problems on Xeon's with Hyperthreading. Donny -Original Message- From: Jigal van Hemert [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 4:39 AM To: [EMAIL PROTECTED] Subject: mysql 1gb memory limit? In order to get the max performance from our servers we're reading every bit we can lay our hands on about performance tuning. In this document: http://www.phpconference.de/2003/slides/business_track/kneschke_webserver- performance-tuning.pdf (it's in German), it says on page 9 that MySQL 4.1.x on 32-bit Intel Linux can only use 1GB!?!? Is this true? Anyway to overcome this limit? Regards, Jigal. -- 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]
processlist: state is Opening table
filter: select, mysql Solaris 3.23.40 connections using perl, jdbc, odbc. Yesterday, we experienced a rare mysqld failure where all connections where in the state=Opening table. Normally our 200+ connections are in state=sleep. The Time field from command show processlist showed each connection accumulating time. This is not the case when a connection is in the sleep mode. The number of connections was nearly 400 when we normally have over 200. The threads running about equal to number of connections. The threads created was at 1240 - normally is ~ number of connections. When I attempted a mysql.server stop, it timed out. I had to use a kill -9. I used a combination of myismcheck (db off) and check table (db on) to verify that all tables were ok. I did repair tables that were OK but terminated improperly probably due to the kill -9 command. The info column from the show processlist showed the command the user or program was executing. All of the connections were accounted for including the extra 170 connections. These extra connections accumulated from programs between the time server failed and time of restart. To me, it appears that the mysqld allowed connections, but then froze with all connections executing at the state Opening table. This is verified from programs making connections without failing. It appears all were waiting for mysqld to process the query. Just in case this matters, there are ~75 tables in this installation. On a normal day, open_tables=149 and table cache is set to 256. Prior to restarting mysqld, opened_tables was at 831. Any suggestions welcome. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.15 replication failure ('Event too big')
Mark Swanson [EMAIL PROTECTED] wrote: I am having replication troubles with 4.0.15. Some info: mysql show slave status; | www.x.com | replicon| 3306| 60| ns1-bin.001 | 16958428| linux-relay-bin.011 | 623915| ns1-bin.001 | Yes | No| | | 0 | Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. | 0| 8147397 | 9437494 The server bin log is fine. The slave bin log gives the error: # mysqlbinlog linux-relay-bin.011 m ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: 1701345056, event_type: 61 ERROR: Could not read entry at offset 622931 : Error in log format or read error Some google searches show this was noticed in 4.0.5 as well. I'm not specifying max_binlog_size on the master or server. I am not specifying max_allowed_packet on the master. I have not set max_packet_size either. I note that my bin logs are only 9MB on the slave. I'm currently upgrading to 4.0.18 but unless I missed it I don't see this fixed in the changelogs. Anyone have any recommendations on how to ensure this doesn't happen again? It's a relay log corruption. There were some reports about corrupted relay log, like this: http://bugs.mysql.com/bug.php?id=2886 MySQL replication master made some changes in the code in v4.0.19. So, may be 4.0.19 resolves this problem. -- 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]
Newbie Q: How do I get a COUNT of a computed field?
I'm not sure if this is a bug or a user failure, so I'm going to ask here before doing anything rash. I'm trying to classify a single field using the IN() expression into two groups: IF(t2.status IN(2,3,4), open, closed) I'd like to GROUP those together so I can COUNT them. When I try SELECT IF(t2.status IN(2,3,4), open, closed) t2st FROM tasks t2 GROUP BY t2st I get an error. It occurs to me that this could be 1: A defect in the 3.23.57 version I'm using. I'm downloading 4.1.1a as we speak, to try it in that version. If this is so, can anyone who knows about these things tell me if this is supported in a later version, and if so which version? 2: More likely, it's a fault in my (limited) knowledge of SQL. Perhaps it is necessary to create a temporary table or do something more sinister to get a count of a computed field. If this is the case, can someone knowledgeable in SQL tell me how to go about it? I've got a fairly small data set to process (the full version of my query filters down the data) so I can do this outside SQL if I have to. But I wanted to push this onto SQL if I could. aTdHvAaNnKcSe =Austin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql 1gb memory limit?
Donny- While I appreciate your bluntness, I did have this issue a time back with 4.1.x. In your email, the reference to big-tables stated that it prevents table fulls. This would dictate that it does affect limits, or working around them , likely a limit set forth by temporary tables (in memory). The other thing that I did was increase block sizes on the filesystem storing the data. I set it to allow 2TB filesizes (16TB Filesystem), as my largest table pushes about 1/4 of that. Linux kernel (32-bit 2.4.x) defaults at 2TB max, but that's the other limit you'd have to deal with (with LVM) Back to the point, the 1gig limit stated in the initial email can be overcome. Things you have to keep in mind are which OS to choose, which architecture, and the underlying filesystem. P Donny Simonton [EMAIL PROTECTED] 03/09/2004 09:09 AM To: 'Peter J Milanese' [EMAIL PROTECTED] cc: 'Jigal van Hemert' [EMAIL PROTECTED], [EMAIL PROTECTED] Subject:RE: mysql 1gb memory limit? Peter, I have never heard of such a limit and I have been using it for a while. And --big-tables, which BTW, you can't easily search for on mysql.com, because of the minimum 4 characters in full text indexing, says this: --big-tables Allow large result sets by saving all temporary sets on file. This option prevents most ``table full'' errors, but also slows down queries for which in-memory tables would suffice. Since Version 3.23.2, MySQL is able to handle large result sets automatically by using memory for small temporary tables and switching to disk tables where necessary. So --big-tables, has nothing to do with any limit. Donny -Original Message- From: Peter J Milanese [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 7:32 AM To: Donny Simonton Cc: 'Jigal van Hemert'; [EMAIL PROTECTED] Subject: RE: mysql 1gb memory limit? Yes. There's a limit. Start mysql with --big-tables. I think there's a finer way of doing it, just don't remember what it was ;) P Donny Simonton [EMAIL PROTECTED] 03/09/2004 08:00 AM To: 'Jigal van Hemert' [EMAIL PROTECTED], [EMAIL PROTECTED] cc: Subject:RE: mysql 1gb memory limit? I've got a server with 24 gigs in it and it works just fine. About 3 with 8 gigs and a few with 2 gigs. All running 4.1.1. And all of them run without any problems on Xeon's with Hyperthreading. Donny -Original Message- From: Jigal van Hemert [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 4:39 AM To: [EMAIL PROTECTED] Subject: mysql 1gb memory limit? In order to get the max performance from our servers we're reading every bit we can lay our hands on about performance tuning. In this document: http://www.phpconference.de/2003/slides/business_track/kneschke_webserver- performance-tuning.pdf (it's in German), it says on page 9 that MySQL 4.1.x on 32-bit Intel Linux can only use 1GB!?!? Is this true? Anyway to overcome this limit? Regards, Jigal. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Span a database transaction across multiple CGI scripts
Hi all Is it possible to span a database transaction across multiple CGI scripts? That is, start transaction and lock some records in one CGI script and update and commit in another CGI script. Here is an example: I have a accounts database. Only one user should edit a given account at any given time. Once an account is open for editing, it should be locked so that other users cannot open in edit mode. Multiple users should be able to edit different accounts. The list.cgi lists accounts. Once click on an account, the edit.cgi reads account info and display in an editable form. This is where I need to lock the account. After editing is completed, user clicks on the Update button and data transfer to process.cgi. After the account is updated, I issue commit and release record locks. I use MySQL 4.x and Perl. Could my requirement be implemented in MySQL? Could somebody please at least give me a hint how to implement this? Many thanks in advance. Regards Sagara __ Do you Yahoo!? Yahoo! Search - Find what youre looking for faster http://search.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql 1gb memory limit?
Peter, There is no 1 gig limit that I am aware of. I have been using MySQL 4.1 since the day it was released. And when 4.1.1 came out I switched about half of our machines to using it, and when 4.1.2 comes out in the next week or so, I will switch our stuff that is using 4.1.x to that as well. We are using Fedora core 1 and we don't have a memory limit at all. Linux does have a 2gig memory limit per process or thread. But MySQL can definitely use more than 1gig of memory. If it couldn't then I wouldn't be using it. Donny -Original Message- From: Peter J Milanese [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 9:06 AM To: Donny Simonton Cc: 'Jigal van Hemert'; [EMAIL PROTECTED] Subject: RE: mysql 1gb memory limit? Donny- While I appreciate your bluntness, I did have this issue a time back with 4.1.x. In your email, the reference to big-tables stated that it prevents table fulls. This would dictate that it does affect limits, or working around them , likely a limit set forth by temporary tables (in memory). The other thing that I did was increase block sizes on the filesystem storing the data. I set it to allow 2TB filesizes (16TB Filesystem), as my largest table pushes about 1/4 of that. Linux kernel (32-bit 2.4.x) defaults at 2TB max, but that's the other limit you'd have to deal with (with LVM) Back to the point, the 1gig limit stated in the initial email can be overcome. Things you have to keep in mind are which OS to choose, which architecture, and the underlying filesystem. P Donny Simonton [EMAIL PROTECTED] 03/09/2004 09:09 AM To: 'Peter J Milanese' [EMAIL PROTECTED] cc: 'Jigal van Hemert' [EMAIL PROTECTED], [EMAIL PROTECTED] Subject:RE: mysql 1gb memory limit? Peter, I have never heard of such a limit and I have been using it for a while. And --big-tables, which BTW, you can't easily search for on mysql.com, because of the minimum 4 characters in full text indexing, says this: --big-tables Allow large result sets by saving all temporary sets on file. This option prevents most ``table full'' errors, but also slows down queries for which in-memory tables would suffice. Since Version 3.23.2, MySQL is able to handle large result sets automatically by using memory for small temporary tables and switching to disk tables where necessary. So --big-tables, has nothing to do with any limit. Donny -Original Message- From: Peter J Milanese [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 7:32 AM To: Donny Simonton Cc: 'Jigal van Hemert'; [EMAIL PROTECTED] Subject: RE: mysql 1gb memory limit? Yes. There's a limit. Start mysql with --big-tables. I think there's a finer way of doing it, just don't remember what it was ;) P Donny Simonton [EMAIL PROTECTED] 03/09/2004 08:00 AM To: 'Jigal van Hemert' [EMAIL PROTECTED], [EMAIL PROTECTED] cc: Subject:RE: mysql 1gb memory limit? I've got a server with 24 gigs in it and it works just fine. About 3 with 8 gigs and a few with 2 gigs. All running 4.1.1. And all of them run without any problems on Xeon's with Hyperthreading. Donny -Original Message- From: Jigal van Hemert [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 4:39 AM To: [EMAIL PROTECTED] Subject: mysql 1gb memory limit? In order to get the max performance from our servers we're reading every bit we can lay our hands on about performance tuning. In this document: http://www.phpconference.de/2003/slides/business_track/kneschke_webserver- performance-tuning.pdf (it's in German), it says on page 9 that MySQL 4.1.x on 32-bit Intel Linux can only use 1GB!?!? Is this true? Anyway to overcome this limit? Regards, Jigal. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie Q: How do I get a COUNT of a computed field?
Andy, Your first suggestion hit the nail right on the head. Using the column number does the trick. Your second entry was incomprehensible to me, and to mysql as well. It reports an error. (Frankly, if that worked it would be so much magic I'd have to uninstall MySQL for being smarter than me. :-) Thanks a bunch for your time, =Austin -Original Message- From: Andy Eastham [mailto:[EMAIL PROTECTED] Austin, Try using the column number, rather than alias in the group by clause. SELECT IF(t2.status IN(2,3,4), open, closed) t2st FROM tasks t2 GROUP BY 1 Then you'll probably want SELECT IF(t2.status IN(2,3,4), open, closed), count(*) t2st FROM tasks t2 GROUP BY t2st Andy -Original Message- From: Austin Hastings [mailto:[EMAIL PROTECTED] Sent: 09 March 2004 15:10 To: [EMAIL PROTECTED] Subject: Newbie Q: How do I get a COUNT of a computed field? I'm not sure if this is a bug or a user failure, so I'm going to ask here before doing anything rash. I'm trying to classify a single field using the IN() expression into two groups: IF(t2.status IN(2,3,4), open, closed) I'd like to GROUP those together so I can COUNT them. When I try SELECT IF(t2.status IN(2,3,4), open, closed) t2st FROM tasks t2 GROUP BY t2st I get an error. It occurs to me that this could be 1: A defect in the 3.23.57 version I'm using. I'm downloading 4.1.1a as we speak, to try it in that version. If this is so, can anyone who knows about these things tell me if this is supported in a later version, and if so which version? 2: More likely, it's a fault in my (limited) knowledge of SQL. Perhaps it is necessary to create a temporary table or do something more sinister to get a count of a computed field. If this is the case, can someone knowledgeable in SQL tell me how to go about it? I've got a fairly small data set to process (the full version of my query filters down the data) so I can do this outside SQL if I have to. But I wanted to push this onto SQL if I could. aTdHvAaNnKcSe =Austin -- 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: Newbie Q: How do I get a COUNT of a computed field?
Austin Hastings [EMAIL PROTECTED] wrote: I'm not sure if this is a bug or a user failure, so I'm going to ask here before doing anything rash. I'm trying to classify a single field using the IN() expression into two groups: IF(t2.status IN(2,3,4), open, closed) I'd like to GROUP those together so I can COUNT them. When I try SELECT IF(t2.status IN(2,3,4), open, closed) t2st FROM tasks t2 GROUP BY t2st I get an error. It occurs to me that this could be 1: A defect in the 3.23.57 version I'm using. I'm downloading 4.1.1a as we speak, to try it in that version. If this is so, can anyone who knows about these things tell me if this is supported in a later version, and if so which version? 2: More likely, it's a fault in my (limited) knowledge of SQL. Perhaps it is necessary to create a temporary table or do something more sinister to get a count of a computed field. If this is the case, can someone knowledgeable in SQL tell me how to go about it? I've got a fairly small data set to process (the full version of my query filters down the data) so I can do this outside SQL if I have to. But I wanted to push this onto SQL if I could. What error message did you receive? Your query worked fine for me: mysql create table tasks(status int); Query OK, 0 rows affected (0.00 sec) mysql insert into tasks values (1),(2),(3),(4),(5); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql SELECT IF(t2.status IN(2,3,4), open, closed) t2st FROM tasks t2 GROUP BY t2st; ++ | t2st | ++ | closed | | open | ++ 2 rows in set (0.00 sec) mysql SELECT IF(t2.status IN(2,3,4), open, closed) t2st, count(*) FROM tasks t2 GROUP BY t2st; ++--+ | t2st | count(*) | ++--+ | closed |2 | | open |3 | ++--+ 2 rows in set (0.00 sec) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving file into database
Check this article: http://php.dreamwerx.net/forums/viewtopic.php?t=6 Port code/design to perl or whatever client language you want.. mysql could care less once it's got the data (correctly) On Tue, 9 Mar 2004, Isa Wolt wrote: Hi, I would like to save a binary file into a mysql database, for later being able to use the file. I am using a perl interafce. Is this at all possible??? And would it be possible to then read that file from a c++ interface? would be greatful for any help/advices! Isa _ Hitta rätt på nätet med MSN Sök http://search.msn.se/ -- 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: table name is lower case if there is an index on it (4.0.18 )
Bluemel, Marco [EMAIL PROTECTED] wrote: I have a problem with mysql 4.0.18 on WindowsXP I set the lower_case_table_names=2 as it is mentioned for windows t the mysql website. (before I'm using version 4.0.15 and had set it to 0) so the tablenames should be stored in this case as I wrote it. So I create a table in Java with an index (this is only a part of the table) String sql = ; sql += CREATE TABLE AB_OBJECTS; sql += (; sql += id INTEGER PRIMARY KEY,; sql += objectId INTEGER NOT NULL,; sql += ); _statement.execute(sql); _statement.execute(CREATE INDEX ab_object_objectid_index ON AB_OBJECTS(objectId) ); the problem is that the table is created in lower cases as 'ab_objects' but if I don't create the index, only the table, its created in upper case 'AB_OBJECTS' as it should be. I have some other tables with and without an index and all should be stored in upper case. Thank you for report! This bug is already entered to the bug database: http://bugs.mysql.com/bug.php?id=3109 -- 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]
subquery and order by
Hi, I'm new to mysql and I just installed 4.1.1 and ran into trouble combining a subquery and 'order by'. I guess the 3 queries below show my problem mysql SELECT host,facility,priority,date,time FROM logs WHERE host=(select ip from hostip where host='ams602.avctr.gxs.com'); +---+--+--++--+ | host | facility | priority | date | time | +---+--+--++--+ | 204.90.248.18 | auth | info | 2004-03-06 | 02:00:03 | | 204.90.248.18 | auth | info | 2004-03-09 | 02:00:03 | | 204.90.248.18 | mail | info | 2004-03-09 | 04:15:08 | | 204.90.248.18 | mail | info | 2004-03-09 | 04:15:09 | | 204.90.248.18 | auth | info | 2004-03-09 | 09:17:26 | +---+--+--++--+ 5 rows in set (0.01 sec) mysql SELECT host,facility,priority,date,time FROM logs WHERE host=(select ip from hostip where host='ams602.avctr.gxs.com') order by date; Empty set (0.00 sec) mysql SELECT host,facility,priority,date,time FROM logs WHERE host=(select ip from hostip where host='ams602.avctr.gxs.com') and facility='auth' order by date; +---+--+--++--+ | host | facility | priority | date | time | +---+--+--++--+ | 204.90.248.18 | auth | info | 2004-03-06 | 02:00:03 | | 204.90.248.18 | auth | info | 2004-03-09 | 02:00:03 | | 204.90.248.18 | auth | info | 2004-03-09 | 09:17:26 | +---+--+--++--+ 3 rows in set (0.01 sec) The first query selects a few records from a syslog database where entries are stored with the IP address, but which I want to search using the hostname. The second query wants to order the output by date but to my big surprise does not give any results. When I extend the query with a select on a second field and do the 'order by date' I do get a result. Am I missing something here? Thanks, Willem
RE: Newbie Q: How do I get a COUNT of a computed field?
From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] What error message did you receive? Your query worked fine for me: When I say: SELECT IF(t2.status IN(2,3,4), open, closed) t2st, COUNT(t2st) FROM tasks t2 GROUP BY t2st; I get: #1054 - Unknown column 't2st' in 'field list' The count(*) and grouping by the field number were both new to me, and I'm up and working with them. Thanks, =Austin mysql create table tasks(status int); Query OK, 0 rows affected (0.00 sec) mysql insert into tasks values (1),(2),(3),(4),(5); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql SELECT IF(t2.status IN(2,3,4), open, closed) t2st FROM tasks t2 GROUP BY t2st; ++ | t2st | ++ | closed | | open | ++ 2 rows in set (0.00 sec) mysql SELECT IF(t2.status IN(2,3,4), open, closed) t2st, count(*) FROM tasks t2 GROUP BY t2st; ++--+ | t2st | count(*) | ++--+ | closed |2 | | open |3 | ++--+ 2 rows in set (0.00 sec) -- 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]
RE: subquery and order by
I'd never actually tried that before, it definitely seems like a bug to me. SELECT * FROM WordScoreTemp WHERE word = ( SELECT word FROM Word WHERE word = 'mysql' ) The above works fine. SELECT * FROM WordScoreTemp WHERE word = ( SELECT word FROM Word WHERE word = 'mysql' ) order by score; Does not. Donny -Original Message- From: van der Scheun, Willem (GXS) [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 10:27 AM To: '[EMAIL PROTECTED]' Subject: subquery and order by Hi, I'm new to mysql and I just installed 4.1.1 and ran into trouble combining a subquery and 'order by'. I guess the 3 queries below show my problem mysql SELECT host,facility,priority,date,time FROM logs WHERE host=(select ip from hostip where host='ams602.avctr.gxs.com'); +---+--+--++--+ | host | facility | priority | date | time | +---+--+--++--+ | 204.90.248.18 | auth | info | 2004-03-06 | 02:00:03 | | 204.90.248.18 | auth | info | 2004-03-09 | 02:00:03 | | 204.90.248.18 | mail | info | 2004-03-09 | 04:15:08 | | 204.90.248.18 | mail | info | 2004-03-09 | 04:15:09 | | 204.90.248.18 | auth | info | 2004-03-09 | 09:17:26 | +---+--+--++--+ 5 rows in set (0.01 sec) mysql SELECT host,facility,priority,date,time FROM logs WHERE host=(select ip from hostip where host='ams602.avctr.gxs.com') order by date; Empty set (0.00 sec) mysql SELECT host,facility,priority,date,time FROM logs WHERE host=(select ip from hostip where host='ams602.avctr.gxs.com') and facility='auth' order by date; +---+--+--++--+ | host | facility | priority | date | time | +---+--+--++--+ | 204.90.248.18 | auth | info | 2004-03-06 | 02:00:03 | | 204.90.248.18 | auth | info | 2004-03-09 | 02:00:03 | | 204.90.248.18 | auth | info | 2004-03-09 | 09:17:26 | +---+--+--++--+ 3 rows in set (0.01 sec) The first query selects a few records from a syslog database where entries are stored with the IP address, but which I want to search using the hostname. The second query wants to order the output by date but to my big surprise does not give any results. When I extend the query with a select on a second field and do the 'order by date' I do get a result. Am I missing something here? Thanks, Willem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimizing Queries
Hello,
I'm trying to determine the best way to optimize the query below. Right now
it is taking around 9mins and we need it to take no more than 30 seconds (we
can get it under 30s on MS SQL):
explain select count(distinct(phone)) as TOTAL
FROM speedlink
WHERE
county in('247','085','145','285','215','211') AND
state = 'GA' AND
(
homeowner = 'Y' OR
probable_homeowner IN ('8','9') OR
homeowner_probability_model BETWEEN '080' AND '102'
) AND
phone IS NOT NULL AND
first IS NOT NULL AND
last IS NOT NULL
--
++-+---+--+-
---+---+-+---+--
---+-+
| id | select_type | table | type | possible_keys
| key | key_len | ref | rows| Extra |
++-+---+--+-
---+---+-+---+--
---+-+
| 1 | SIMPLE | speedlink | ref |
idx_state,idx_county,idx_phone,idx_homeowner,idx_hpm,idx_ph,idx_first,idx_la
st | idx_state | 3 | const | 2840162 | Using where |
++-+---+--+-
---+---+-+---+--
---+-+
1 row in set (0.00 sec)
Here are some of my thoughts on what could be done to speed it up, but
haven't implemented these yet:
1) make the phone field UNIQUE on data load. This would reduce the data for
other queries to be run but maybe it makes sense to have a few table sets.
2) change the numeric fields from varchars to ints, smallints or something
like that.
3) Not sure if NULL values are slower or faster than using a comparison with
' ', interested on feedback.
4) Split the table into multiple files? I'm not sure how to do this but have
seen it mentioned in some articles.
Some information on the table:
- ISAM
- 120 million rows
- 26 fields in total
- 23 fields indexed (all fields in the above query are indexed)
- speedlink.MYD is 12GB, speedlink.MYI is 24GB
Some info on the server
- Quad Xeon 900MHz
- 4GB RAM
- DB is storage on an EMC Symmetrix storage system (fibre channel SAN)
Any/all assistance is appreciated.
Thanks.
Chris.
Re: subquery and order by
van der Scheun, Willem (GXS) [EMAIL PROTECTED] wrote: I'm new to mysql and I just installed 4.1.1 and ran into trouble combining a subquery and 'order by'. I guess the 3 queries below show my problem mysql SELECT host,facility,priority,date,time FROM logs WHERE host=(select ip from hostip where host='ams602.avctr.gxs.com'); +---+--+--++--+ | host | facility | priority | date | time | +---+--+--++--+ | 204.90.248.18 | auth | info | 2004-03-06 | 02:00:03 | | 204.90.248.18 | auth | info | 2004-03-09 | 02:00:03 | | 204.90.248.18 | mail | info | 2004-03-09 | 04:15:08 | | 204.90.248.18 | mail | info | 2004-03-09 | 04:15:09 | | 204.90.248.18 | auth | info | 2004-03-09 | 09:17:26 | +---+--+--++--+ 5 rows in set (0.01 sec) mysql SELECT host,facility,priority,date,time FROM logs WHERE host=(select ip from hostip where host='ams602.avctr.gxs.com') order by date; Empty set (0.00 sec) mysql SELECT host,facility,priority,date,time FROM logs WHERE host=(select ip from hostip where host='ams602.avctr.gxs.com') and facility='auth' order by date; +---+--+--++--+ | host | facility | priority | date | time | +---+--+--++--+ | 204.90.248.18 | auth | info | 2004-03-06 | 02:00:03 | | 204.90.248.18 | auth | info | 2004-03-09 | 02:00:03 | | 204.90.248.18 | auth | info | 2004-03-09 | 09:17:26 | +---+--+--++--+ 3 rows in set (0.01 sec) The first query selects a few records from a syslog database where entries are stored with the IP address, but which I want to search using the hostname. The second query wants to order the output by date but to my big surprise does not give any results. When I extend the query with a select on a second field and do the 'order by date' I do get a result. Am I missing something here? Looks like a bug. Could you create a repeatable test case? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Optimizing Queries
Chris,
Is it faster if you remove the 'IS NOT NULL'? I know that's not the results
you want, but we have found that is NOT NULL will do a full scan. But we
normally use it with a join. Since you are using one table, I'm not sure
how it would affect it.
Donny
-Original Message-
From: Chris Fossenier [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 09, 2004 10:38 AM
To: [EMAIL PROTECTED]
Subject: Optimizing Queries
Hello,
I'm trying to determine the best way to optimize the query below. Right
now
it is taking around 9mins and we need it to take no more than 30 seconds
(we
can get it under 30s on MS SQL):
explain select count(distinct(phone)) as TOTAL
FROM speedlink
WHERE
county in('247','085','145','285','215','211') AND
state = 'GA' AND
(
homeowner = 'Y' OR
probable_homeowner IN ('8','9') OR
homeowner_probability_model BETWEEN '080' AND '102'
) AND
phone IS NOT NULL AND
first IS NOT NULL AND
last IS NOT NULL
--
++-+---+--+---
--
---+---+-+---
+--
---+-+
| id | select_type | table | type | possible_keys
| key | key_len | ref | rows| Extra |
++-+---+--+---
--
---+---+-+---
+--
---+-+
| 1 | SIMPLE | speedlink | ref |
idx_state,idx_county,idx_phone,idx_homeowner,idx_hpm,idx_ph,idx_first,idx_
la
st | idx_state | 3 | const | 2840162 | Using where |
++-+---+--+---
--
---+---+-+---
+--
---+-+
1 row in set (0.00 sec)
Here are some of my thoughts on what could be done to speed it up, but
haven't implemented these yet:
1) make the phone field UNIQUE on data load. This would reduce the data
for
other queries to be run but maybe it makes sense to have a few table sets.
2) change the numeric fields from varchars to ints, smallints or something
like that.
3) Not sure if NULL values are slower or faster than using a comparison
with
' ', interested on feedback.
4) Split the table into multiple files? I'm not sure how to do this but
have
seen it mentioned in some articles.
Some information on the table:
- ISAM
- 120 million rows
- 26 fields in total
- 23 fields indexed (all fields in the above query are indexed)
- speedlink.MYD is 12GB, speedlink.MYI is 24GB
Some info on the server
- Quad Xeon 900MHz
- 4GB RAM
- DB is storage on an EMC Symmetrix storage system (fibre channel SAN)
Any/all assistance is appreciated.
Thanks.
Chris.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing Queries
Hello Chris, Tuesday, March 9, 2004, 4:38:00 PM, you wrote: CF I'm trying to determine the best way to optimize the query below. Right now CF it is taking around 9mins and we need it to take no more than 30 seconds (we CF can get it under 30s on MS SQL): CF | 1 | SIMPLE | speedlink | ref | CF idx_state,idx_county,idx_phone,idx_homeowner,idx_hpm,idx_ph,idx_first,idx_la CF st | idx_state | 3 | const | 2840162 | Using where | Some things that have been recommended to me which may be useful for you: Is the table in question Fixed or Dynamic? Do anything you can, including separating out dynamic length fields to joined tables, to make your main table fixed in length. Your fields appear to be indexed individually rather than collectively, so out of the 8 possible indexes in use, it's only using one of them (state). Try combining common/grouped indexes together based on the type of query you run most often. If MySQL is using the wrong index (or one that isn't as efficient as it could be) force it to use another. I don't know about your table definition, but check to see if you really do need to use the datatype you've currently selected. You can shave MBs from the total table size just by optimising your use of data types. Just some thoughts anyway. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_close needed after a failed mysql_real_connect ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi The docs aren't clear on this. Is mysql_close needed on a MYSQL struct previously initilized with mysql_init after a failed mysql_real_connect ? (which returned NULL) - -- Mihai RUSUEmail: [EMAIL PROTECTED] GPG : http://dizzy.roedu.net/dizzy-gpg.txtWWW: http://dizzy.roedu.net Linux is obsolete -- AST -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFATfinPZzOzrZY/1QRAjqGAJ44YbjrQw4/Ert6OSEBQCFeMq2VewCdGwxI sNBNmBjQLdKUTPa842YHxPY= =j+9v -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update field conditionally
Using v4.0.15 on WinNT under Apache. For my sins, the client has insisted on creating a page counter! The fields are to be CounterCode (Varchar 10), CounterValue (Int 10) and CounterStartDateTime (DateTime). Setting up the table is no problem. However, client wants to have the CounterStartDateTime field updated to Now() only on the first hit to that page, so that it can be reported as the start of the count (logically). Otherwise, it remains as a NULL value, and the CounterValue remains as 0. I have tried to find out if it is possible to do a single-pass update, changing the CounterValue from 0 to 1 and the CounterStartDateTime to the current time on condition that it is currently NULL, with something like: UPDATE Table SET CounterValue = CounterValue+1, CounterStartDateTime = (IF CounterStartDateTime IS NULL, Now()) without success. I've looked through the on-line manual, and cannot find any reference to such conditional updates. Perhaps I missed it. Any clues, please? Cheers Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update field conditionally
UPDATE Table SET CounterValue = CounterValue+1, CounterStartDateTime = (IF CounterStartDateTime IS NULL, Now()) without success. It looks like you just have the syntax wrong. Try: UPDATE Table SET CounterValue = CounterValue+1, CounterStartDateTime = IF(CounterStartDateTime IS NULL, Now(), NULL); See: http://www.mysql.com/doc/en/Control_flow_functions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Optimizing Queries
Chris, Is it faster if you remove the 'IS NOT NULL'? I know that's not the results you want, but we have found that is NOT NULL will do a full scan. But we normally use it with a join. Since you are using one table, I'm not sure how it would affect it. Donny This is an interesting point. This may be off topic, but I work with a guy who has an allergic reaction to NULLs in database fields. It is his opinion that a proper database design would set default values for every field. If MySQL truly does a full scan for NOT NULL, it would seem that my co-worker is correct. Being new to MySQL (and databases in general), I was wondering what the rest of you thought about this topic. -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update field conditionally
If it was me writing the code, I'd use two different update statements:
a) an UPDATE to initialize the DateTime to Now() and set the counter to 1
when the page is first hit
b) another UPDATE to increment the counter on all of the remaining hits
Something like this (assuming Java is your programming language):
// Logic to display the rest of the web page
...
// Obtain the current row for the counter.
getCurrentCounterRow();
// Store the current counter value in a variable
counter = ; //value obtained from current row
// Adjust the counter row depending on the value of the counter
if (counter == 0) {
update COUNTER_TABLE
set CounterValue = 1;
CounterStartDateTime = now();
}
else {
update COUNTER_TABLE
set CounterValue = CounterValue + 1;
}
// Display the counter value that applies after the IF statement was
executed.
...
etc.
Just my two cents worth
Rhino
- Original Message -
From: Terry Riley [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, March 09, 2004 12:11 PM
Subject: Update field conditionally
Using v4.0.15 on WinNT under Apache.
For my sins, the client has insisted on creating a page counter!
The fields are to be CounterCode (Varchar 10), CounterValue (Int 10) and
CounterStartDateTime (DateTime).
Setting up the table is no problem. However, client wants to have the
CounterStartDateTime field updated to Now() only on the first hit to that
page, so that it can be reported as the start of the count (logically).
Otherwise, it remains as a NULL value, and the CounterValue remains as 0.
I have tried to find out if it is possible to do a single-pass update,
changing the CounterValue from 0 to 1 and the CounterStartDateTime to the
current time on condition that it is currently NULL, with something like:
UPDATE Table SET CounterValue = CounterValue+1, CounterStartDateTime =
(IF CounterStartDateTime IS NULL, Now())
without success.
I've looked through the on-line manual, and cannot find any reference to
such conditional updates. Perhaps I missed it.
Any clues, please?
Cheers
Terry Riley
--
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: Update field conditionally
Thanks, Jeremy What I actually needed was: UPDATE Table SET CounterValue = CounterValue+1, CounterStartDateTime = IF(CounterStartDateTime IS NULL, Now(), CounterStartDateTime) This prevents it going back to NULL if the value is already not NULL. Thanks again. Terry --Original Message- UPDATE Table SET CounterValue = CounterValue+1, CounterStartDateTime = (IF CounterStartDateTime IS NULL, Now()) without success. It looks like you just have the syntax wrong. Try: UPDATE Table SET CounterValue = CounterValue+1, CounterStartDateTime = IF(CounterStartDateTime IS NULL, Now(), NULL); See: http://www.mysql.com/doc/en/Control_flow_functions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JDBC timeout after 4.0.8 - 4.0.18 upgrade?
Hi, I've recently upgraded from 4.0.8a to 4.0.18, and am running into a strange error in the JDBC connector. If the DB connection is unused for a period of time (don't konw the minimum amount of time, but 8-12 hours defintiely triggers the problem), I get the following error msg in my stack: ** BEGIN NESTED EXCEPTION ** java.net.SocketException MESSAGE: Software caused connection abort: recv failed STACKTRACE: java.net.SocketException: Software caused connection abort: recv failed at java.net.SocketInputStream.socketRead0(Native Method) at java.net.SocketInputStream.read(SocketInputStream.java:147) at java.io.BufferedInputStream.fill(BufferedInputStream.java:183) at java.io.BufferedInputStream.read(BufferedInputStream.java:201) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:1399) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1775) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1020) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1109) at com.mysql.jdbc.Connection.execSQL(Connection.java:2030) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1563) From this point on, all connections to the DB fail. I don't have the autoreconnect parameter in the JConnector enabled, but I didn't have it enabled in 4.0.8 either. I haven't changed anything in my code either. It uses the java.sql.DriverManager class to do my connection pooling. Has something changed between 4.0.8 and 4.0.18 that would cause this problem? Thanks! Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update field conditionally
Hello Terry, Tuesday, March 9, 2004, 5:11:00 PM, you wrote: I know you have some solutions to the original problem already, but I just wanted to make one small observation: TR The fields are to be CounterCode (Varchar 10), CounterValue (Int 10) and TR CounterStartDateTime (DateTime). Using a varchar(10) for the CounterCode will give you a Dynamically sized table. If you changed this to char(10) you will have the speed benefits of a Fixed size table which MySQL will be able to process significantly faster. This could be especially useful if this counter is to be hit a lot of times (i.e. it's a popular site). -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JDBC timeout after 4.0.8 - 4.0.18 upgrade?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Eric B. wrote: Hi, I've recently upgraded from 4.0.8a to 4.0.18, and am running into a strange error in the JDBC connector. If the DB connection is unused for a period of time (don't konw the minimum amount of time, but 8-12 hours defintiely triggers the problem), I get the following error msg in my stack: ** BEGIN NESTED EXCEPTION ** java.net.SocketException MESSAGE: Software caused connection abort: recv failed STACKTRACE: java.net.SocketException: Software caused connection abort: recv failed at java.net.SocketInputStream.socketRead0(Native Method) at java.net.SocketInputStream.read(SocketInputStream.java:147) at java.io.BufferedInputStream.fill(BufferedInputStream.java:183) at java.io.BufferedInputStream.read(BufferedInputStream.java:201) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:1399) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1775) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1020) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1109) at com.mysql.jdbc.Connection.execSQL(Connection.java:2030) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1563) From this point on, all connections to the DB fail. I don't have the autoreconnect parameter in the JConnector enabled, but I didn't have it enabled in 4.0.8 either. I haven't changed anything in my code either. It uses the java.sql.DriverManager class to do my connection pooling. Has something changed between 4.0.8 and 4.0.18 that would cause this problem? Eric, Are you running the server on Windows? If so, then yes, something has changed. Starting in 4.0.15, network timeouts (including wait_timeout) were implemented in the Windows binaries. See Connector/J's 'troubleshooting' section in the docs at http://www.mysql.com/documentation/connector-j/index.html#id2803835 for ways of dealing with this _correctly_ (hint, 'autoReconnect' isn't one of them). Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com Meet the MySQL Team! April 14-16, 2004 http://www.mysql.com/uc2004/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFATghqtvXNTca6JD8RAs9GAJ4j9P7o2zTUKxqCU2XIuS33vxgfKwCgkuc+ LyGiyDFR0BthlJXcBv7aRGo= =QKF3 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update field conditionally
See below:
--Original Message-
If it was me writing the code, I'd use two different update statements:
a) an UPDATE to initialize the DateTime to Now() and set the counter to
1
when the page is first hit
b) another UPDATE to increment the counter on all of the remaining hits
Something like this (assuming Java is your programming language):
// Logic to display the rest of the web page
...
// Obtain the current row for the counter.
getCurrentCounterRow();
// Store the current counter value in a variable
counter = ; //value obtained from current row
// Adjust the counter row depending on the value of the counter
if (counter == 0) {
update COUNTER_TABLE
set CounterValue = 1;
CounterStartDateTime = now();
}
else {
update COUNTER_TABLE
set CounterValue = CounterValue + 1;
}
// Display the counter value that applies after the IF statement was
executed.
...
etc.
Just my two cents worth
Rhino
I'm using CFMX.
Problem is that the display of the count (on the page) has also to show
the initial start date, so I'm fairly sure I have to go the Update then
Select route, rather than the other way around. And I really don't
think I want to have a Select, Update, Select routine
I can be fairly certain that the record exists (or can code around it if
it doesn't), so I'll probably stick with what I've got.
Thanks anyway!
Terry
- Original Message -
From: Terry Riley [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, March 09, 2004 12:11 PM
Subject: Update field conditionally
Using v4.0.15 on WinNT under Apache.
For my sins, the client has insisted on creating a page counter!
The fields are to be CounterCode (Varchar 10), CounterValue (Int 10)
and
CounterStartDateTime (DateTime).
Setting up the table is no problem. However, client wants to have the
CounterStartDateTime field updated to Now() only on the first hit to
that
page, so that it can be reported as the start of the count
(logically).
Otherwise, it remains as a NULL value, and the CounterValue remains
as 0.
I have tried to find out if it is possible to do a single-pass update,
changing the CounterValue from 0 to 1 and the CounterStartDateTime to
the
current time on condition that it is currently NULL, with something
like:
UPDATE Table SET CounterValue = CounterValue+1, CounterStartDateTime =
(IF CounterStartDateTime IS NULL, Now())
without success.
I've looked through the on-line manual, and cannot find any reference
to
such conditional updates. Perhaps I missed it.
Any clues, please?
Cheers
Terry Riley
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Optimizing Queries
Why does it only use the one index? Chris. -Original Message- From: Richard Davey [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 11:00 AM To: [EMAIL PROTECTED] Subject: Re: Optimizing Queries Hello Chris, Tuesday, March 9, 2004, 4:38:00 PM, you wrote: CF I'm trying to determine the best way to optimize the query below. CF Right now it is taking around 9mins and we need it to take no more CF than 30 seconds (we can get it under 30s on MS SQL): CF | 1 | SIMPLE | speedlink | ref | CF idx_state,idx_county,idx_phone,idx_homeowner,idx_hpm,idx_ph,idx_first,idx_la CF st | idx_state | 3 | const | 2840162 | Using where | Some things that have been recommended to me which may be useful for you: Is the table in question Fixed or Dynamic? Do anything you can, including separating out dynamic length fields to joined tables, to make your main table fixed in length. Your fields appear to be indexed individually rather than collectively, so out of the 8 possible indexes in use, it's only using one of them (state). Try combining common/grouped indexes together based on the type of query you run most often. If MySQL is using the wrong index (or one that isn't as efficient as it could be) force it to use another. I don't know about your table definition, but check to see if you really do need to use the datatype you've currently selected. You can shave MBs from the total table size just by optimising your use of data types. Just some thoughts anyway. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- 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: Update field conditionally
See below: --Original Message- Hello Terry, Tuesday, March 9, 2004, 5:11:00 PM, you wrote: I know you have some solutions to the original problem already, but I just wanted to make one small observation: TR The fields are to be CounterCode (Varchar 10), CounterValue (Int 10) and TR CounterStartDateTime (DateTime). Using a varchar(10) for the CounterCode will give you a Dynamically sized table. If you changed this to char(10) you will have the speed benefits of a Fixed size table which MySQL will be able to process significantly faster. This could be especially useful if this counter is to be hit a lot of times (i.e. it's a popular site). -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html Good point, Richard. I was perhaps in a little bit too much of a hurry putting that together, and didn't even consider that! Cheers Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Optimizing Queries
Hello Chris, Tuesday, March 9, 2004, 6:15:56 PM, you wrote: CF Why does it only use the one index? It will evaluate the best index to use for the query and if all you have are single-field indexes, it can only select one of those. From the MySQL manual: If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer tries to find the most restrictive index by deciding which index will find fewer rows and using that index to fetch the rows. Might be worth checking over this page: http://www.mysql.com/doc/en/MySQL_indexes.html -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Update field conditionally
Hello Terry, Tuesday, March 9, 2004, 6:25:00 PM, you wrote: TR Good point, Richard. I was perhaps in a little bit too much of a hurry TR putting that together, and didn't even consider that! No worries. One other thought that occurred to me that might help with the original problem is as follows: Instead of having the date when the counter started as a date-time field, you could construct your table as so: counter_code char(10) :) counter_value int(10) counter_last_modified timestamp counter_started timestamp By replacing the single started date with 2 time stamps you won't ever have to actually worry about the date again because on the very first INSERT both time stamps will be set and on any future UPDATE you can simply do counter_value = counter_value + 1 and the modified field will change automatically, leaving the original started field intact. This also presents the option of showing to the client/visitor the last time a page was visited (and you just know that might be the next request on the list :) -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[3]: Update field conditionally
Hello Terry, Tuesday, March 9, 2004, 6:25:00 PM, you wrote: TR Good point, Richard. I was perhaps in a little bit too much of a hurry TR putting that together, and didn't even consider that! No worries. One other thought that occurred to me that might help with the original problem is as follows: Instead of having the date when the counter started as a date-time field, you could construct your table as so: counter_code char(10) :) counter_value int(10) counter_last_modified timestamp counter_started timestamp By replacing the single started date with 2 time stamps you won't ever have to actually worry about the date again because on the very first INSERT both time stamps will be set and on any future UPDATE you can simply do counter_value = counter_value + 1 and the modified field will change automatically, leaving the original started field intact. This also presents the option of showing to the client/visitor the last time a page was visited (and you just know that might be the next request on the list :) -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html Now that one I had considered, Richard, and then promptly forgot about! Thanks for your help. Cheers Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql 1gb memory limit?
Hi Donny, What server hardware do you use to support 24G RAM over 32-bits limit? Please advise your choice of hardware and software configuration and how long and reliable it works for you on hyperthreaded Xeons? Best regards, Igor ua3qrz -Original Message- From: Donny Simonton [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 8:00 AM To: 'Jigal van Hemert'; [EMAIL PROTECTED] Subject: RE: mysql 1gb memory limit? I've got a server with 24 gigs in it and it works just fine. About 3 with 8 gigs and a few with 2 gigs. All running 4.1.1. And all of them run without any problems on Xeon's with Hyperthreading. Donny -Original Message- From: Jigal van Hemert [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 4:39 AM To: [EMAIL PROTECTED] Subject: mysql 1gb memory limit? In order to get the max performance from our servers we're reading every bit we can lay our hands on about performance tuning. In this document: http://www.phpconference.de/2003/slides/business_track/kneschke_webserver- performance-tuning.pdf (it's in German), it says on page 9 that MySQL 4.1.x on 32-bit Intel Linux can only use 1GB!?!? Is this true? Anyway to overcome this limit? Regards, Jigal. -- 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]
Select problem with BigInt
I am new to mysql and am converting an existing program. I have encountered what appears to be a problem with bigints I have a large integer number (milliseconds since 1970) which is 13 digits. So I tried to store it in a table as a bigint type. Storing works fine. When I try to retrieve it, I don't get the records I expect. If I try to order my records by the bigint column I get completely unexpected orderings. My table definition is: CREATE TABLE `FREEWAYDATA` ( `CLIENTCLOCK` bigint default NULL, `CLOCKOFFSET` decimal(10,0) default NULL, `CORRECTEDCLOCK` bigint NOT NULL default '0', `DETECTORDATA` varchar(255) default NULL, `STATIONID` decimal(10,0) NOT NULL default '0', `THEDAY` date default NULL, PRIMARY KEY (`CORRECTEDCLOCK`,`STATIONID`) ) TYPE=MyISAM; The problem field is correctedclock. I insert records with a JDBC preparedStatement: insert into FREEWAYSERVER.FREEWAYDATA (STATIONID, CORRECTEDCLOCK, CLIENTCLOCK, DETECTORDATA, clockOffset) values (?, ?, ?, ?, ?); where the correctedclock is set using setLong(); Inserts appear to work fine. I retrieve records using selects of the form: SELECT CORRECTEDCLOCK FROM FREEWAYSERVER.FREEWAYDATA where correctedclock = ? and correctedclock ? order by correctedClock ASC where the start and end times are large integer values. What I get back varies by which values I use but is generally wrong. If I do select correctedclock from freewaydata I can see the values I want but they don't get pulled if I use the where clause. If I do select correctedclock from freewaydata order by correctedclock asc I get some order which is not numeric nor string ordering nor the insert order. If I change correctedclock to be an integer field, everything works as expected. While this is a possible workaround it messes up my data accesses. Is there something special about bigint that prevents range based selects from working they way I expect them to? This is using mysql 4.0.16 on Mac OS X 10.3 server. Randall Cayford Institute of Transportation Studies UC Berkeley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DateDiff function in SqlServer ... How do it in MySql ?
I am a user of Microsoft Sql Server and use very much the function Datediff (interval, fecha1, fecha2) to extract differences between two dates, in years, days, months, hours, etc I am a beginner with MySql and i can't see the way to do this function .. How can I extract for example difference of hours between two given dates .. someone help me please ? Thanks in advance
empty tables return non-empty result sets
I am new to mysql so this may be an obvious mistake on my part...
I am using JDBC to acess some mysql data tables. I am having trouble
with the following code:
Statement s;
ResultSet rset;
longcorrectedclock;
s = conn.createStatement();
rset = s.executeQuery(Select min(correctedclock) from summaryhistory);
if (rset.next())
correctedclock = rset.getLong(1) * CONVERSIONFACTOR;
else {
s.close();
return(null);
}
if (correctedclock == 0)
FlushSummaryHistoryZeroTimes();
My problem is that the query returns apparently valid data even if
the table is completely empty. As I understand it, rset.next()
should return false if there no rows. On an empty table it doesn't.
And after that, rset.getLong() successfully returns a 0 value.
CorrectedClock is an Int field in the table, by the way.
The same code works on several other SQL databases, just not on
mysql. Any idea as to what is wrong?
This is using mysql 4.0.16 on Mac OS X 10.3 server.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql 1gb memory limit?
If you turn on highmem support you can easily get 24gigs of memory. I think with 2.6, it's up to 64 gigs. But there are even patches for 512gigs. Not sure if those patches work, but the highmem support works just fine. Donny -Original Message- From: Igor Dorovskoy [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 1:25 PM To: 'Donny Simonton'; 'Jigal van Hemert'; [EMAIL PROTECTED] Subject: RE: mysql 1gb memory limit? Hi Donny, What server hardware do you use to support 24G RAM over 32-bits limit? Please advise your choice of hardware and software configuration and how long and reliable it works for you on hyperthreaded Xeons? Best regards, Igor ua3qrz -Original Message- From: Donny Simonton [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 8:00 AM To: 'Jigal van Hemert'; [EMAIL PROTECTED] Subject: RE: mysql 1gb memory limit? I've got a server with 24 gigs in it and it works just fine. About 3 with 8 gigs and a few with 2 gigs. All running 4.1.1. And all of them run without any problems on Xeon's with Hyperthreading. Donny -Original Message- From: Jigal van Hemert [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 4:39 AM To: [EMAIL PROTECTED] Subject: mysql 1gb memory limit? In order to get the max performance from our servers we're reading every bit we can lay our hands on about performance tuning. In this document: http://www.phpconference.de/2003/slides/business_track/kneschke_webserver- performance-tuning.pdf (it's in German), it says on page 9 that MySQL 4.1.x on 32-bit Intel Linux can only use 1GB!?!? Is this true? Anyway to overcome this limit? Regards, Jigal. -- 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 5.0 production release
Hello, Can you give an estimate of when 5.0 will be released for production? Thanks, Matthew Son
ANN: New VBMySQLDirect API
VBMySQL.com is pleased to announce the launch of a new projects page at http://projects.vbmysql.com. The first (and flagship) project is VBMySQLDirect. VBMySQLDirect is a new MySQL C API wrapper written by longtime site contributor Robert Rowe. VBMySQLDirect is a fork of the MyVbQl API and is available for Visual Basic developers and all Windows developers who have access to COM objects. VBMySQLDirect offers improved performance over ODBC, and also offers improvements over the previous MyVbQl API in terms of better memory management, BLOB support, and better ADO compatibility. VBMySQLDirect uses a more recent MySQL API as it's basis as well and therefore supports more recent functionality than MyVbQl. VBMySQLDirect is available at http://projects.vbmysql.com/vbmysqldirect Regards, Mike Hillyer www.vbmysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Pricelist
Look it through -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DateDiff function in SqlServer ... How do it in MySql ?
Gabriel Alessandria wrote: I am a user of Microsoft Sql Server and use very much the function Datediff (interval, fecha1, fecha2) to extract differences between two dates, in years, days, months, hours, etc I am a beginner with MySql and i can't see the way to do this function .. How can I extract for example difference of hours between two given dates .. someone help me please ? Thanks in advance Not quite sure what it means to get the difference between two dates in months or hours, but mysql does have a DATEDIFF function: DATEDIFF(expr,expr2) DATEDIFF() returns the number of days between the start date expr and the end date expr2. expr and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation. For more date functions, see the manual: http://www.mysql.com/doc/en/Date_and_time_functions.html Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Too many tables. MySQL can only use 31 tables in a join
Hi guys, I'm new to MySQL, it's been just few days since I've installed it and started playing with it. I'm migrating web application from another database which gives us too many problems. So far I've successfully moved the structure and data across and running field tests. What surprised me a lot was the limit on the number of tables in JOIN. The application has report builder which could easily generate much more than 31 joined tables, when many fields, searches, filters etc. involved. So I guess my question is if it's possible to get around this limit or am I done with MySQL at the very start? Because another solution would be to rewrite the report builder itself and I still have doubts I will not cross the limit. Searching through archives I didn't see any solutions to this problems, just mentions of some hack you could do to force MySQL think it's running on the 64-bit platform. Any hints including the hint on how to do this would be greatly appreciated. The MySQL version I have is 4.0.18-nt-long. -- Best regards, Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: empty tables return non-empty result sets
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Randall Cayford wrote: JDBC driver: mysql-connector-java-3.0.11-stable-bin.jar Randall Cayford wrote: I am new to mysql so this may be an obvious mistake on my part... I am using JDBC to acess some mysql data tables. I am having trouble with the following code: Statement s; ResultSet rset; longcorrectedclock; s = conn.createStatement(); rset = s.executeQuery(Select min(correctedclock) from summaryhistory); Randall, Because you are issuing a select with a 'min' query, you will always have a row returned, even on an empty table, because min() is an aggregate function. The value will be 'null' in this case, but JDBC can't return 'null' from a getLong(), you have to check ResultSet.wasNull() _after_ asking for the value as a long. I'm curious as to what other databases did this 'correctly', because if you're following the SQL Standard, you will always have a result returned from an aggregate function like min() :) Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com Meet the MySQL Team! April 14-16, 2004 http://www.mysql.com/uc2004/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFATjqutvXNTca6JD8RAlEGAJ4sjwtXb/o5g/4Nqgxas1sifmFiAgCgxTk3 sRnGubw4shfsVdoFrhCuqUo= =HxkM -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Pricelist
[EMAIL PROTECTED] wrote: Look it through Can the list administrator figure out who this moron is and remove him from the list? Jon -- -- FutureSoft, Inc. 12012 Wickchester Lane, Suite 600 Houston, TX 77079 If you no longer want to receive commercial e-mail correspondence from FutureSoft, you may remove your address from our records by visiting www.futuresoft.com/emailremoval.asp -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB tables using 90% cpu
The query is running dramatically slower than the MyISAM query, sometimes even causing mysql to freeze for a while. I searched this list and found a few people saying that on FreeBSD mysql should be compiled using linux pthreads if you are using InnoDB or else I would get this exact problem. Has this been resolved or is should I recompile? I am using native freebsd threads. - Original Message - From: Sasha Pachev [EMAIL PROTECTED] To: Cliff [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, March 08, 2004 9:18 AM Subject: Re: InnoDB tables using 90% cpu Cliff wrote: Hi, I have a whole database I wanted to convert to InnoDB from MyISAM, but do not want to use alter table because of the problems I had last time. I made a whole dump of the table using mysqldump and changed all of the table create definitions from MyISAM to InnoDB. Theoretically this should be just like creating a new innodb table from scratch and inserting new records. However, while the MyISAM tables used ~30% of the cpu usage on a query, InnoDB runs anywhere from 50-90% depending on the query. The databases combined are approximately 200MB. Here is my cnf file: [mysqld] basedir=/mysql long_query_time=3 log-slow-queries=/tmp/slowmysql.log innodb_data_home_dir = innodb_data_file_path = /mysql/data/innodb_data:300M:autoextend set-variable = innodb_buffer_pool_size=300M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_file_size=150M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 This is mysql 4.0.18 on freebsd 4.8-STABLE. We have 1GB of ram which should be plenty to run the large queries that we are doing. Thanks in advance. 50-90% CPU vs only 30% could be actually an improvement ( less disk I/O, and relatively more time to get the data). The question is - does the query actually take less time? If not, it could be because a certain optimization available with MyISAM is not available with InnoDB. Isolate the trouble query, and do an EXPLAIN. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: One thread takes over the whole server
Thanks for the reply What I need to know is if there is a way to prevent the server from geting stuck after a missbehave query like the example is sent? The problem is all other clients go so slow that no work can be done and no new connections can be made. The server is a DUAL XEON 3GH 1GB RAM, 2 SCSI HD 10.000RPM INTEL MB running SuSE Linux 9.0, my.cnf = example my.medium Andres Hocevar VENEZUELA [EMAIL PROTECTED] wrote: Hi I need to prevent users from holding down the server,issuing the next query the server becomes so slow that the other clients can't even login!, the tables are around 6Million records: SELECT (some fields ), sum(more fields) AS calcfield FROM table1 INNER JOIN table2 ON ... INNER JOIN table3 ON GROUP BY (group field) HAVING table1.field = 'something' AND calcfield BETWEEEN 1 AND 10 if the same query is changed to SELECT (some fields ), sum(more fields) AS calcfield FROM table1 INNER JOIN table2 ON ... INNER JOIN table3 ON WHERE table1.field = 'something' GROUP BY (group field) HAVING calcfield BETWEEEN 1 AND 10 the query takes 3sec! Is there a way to handle this situations in witch one thread takes over the whole server?? HAVING clause is applied before data are sent to the client without any optimization. That is why you first query is slower than second one. Turn on slow query log to find queries that take a long time to execute: http://www.mysql.com/doc/en/Slow_query_log.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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
extracting .tgz archive for maxdb
Hello! I was just wondering if I am the only one that's having problem extracting maxdb .tgz file or there is someone else out there facing similar problem. tar zxvf filename.tgz or gunzip filename.tgz , tar xvr filename.tar or zcat filename.tgz | tar xvf - should work, but i tried all and it gives me the following error maxdb-all-linux-32bit-i386-7_5_0_8/ maxdb-all-linux-32bit-i386-7_5_0_8/SDBBAS.TGZ zcat: maxdb-all-linux-32bit-i386-7_5_0_8.tgz: invalid compressed data--format violated tar: Unexpected EOF in archive tar: Unexpected EOF in archive tar: Error is not recoverable: exiting now It seems it's extracting some directories and then saying invalid compressed data. I can see some extracted stuff though but not all. Any help would be appreciated Thanks -- Desktop Support School of Computer Science Florida International Univeristy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 5.0 production release
Matthew, - Original Message - From: Matthew Son Newsgroups: mailing.database.myodbc Sent: Tuesday, March 09, 2004 10:26 PM Subject: mysql 5.0 production release Hello, Can you give an estimate of when 5.0 will be released for production? my guess is that 4.1 will be declared 'production' in September 2004. Since 5.0.0 was released 8 months after 4.1.0, that would give us an estimate that 5.0 will be declared 'production' in May 2005. But normally you can use MySQL in production long before it is officially declared 'production'. Thanks, Matthew Son Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql timed actions... Confused
Here is a log of query times I made when a certain page is loaded that uses php and mysql, does mysql 4 do some sort of scheduled maintenance I am not aware of? 2004/03/08 16:11:27OK, 77569 bytes0 seconds 2004/03/08 16:11:37OK, 77575 bytes0 seconds 2004/03/08 16:13:31OK, 77569 bytes1 seconds 2004/03/08 16:13:49OK, 77575 bytes1 seconds 2004/03/08 16:14:51OK, 77575 bytes1 seconds 2004/03/08 16:17:00OK, 77569 bytes1 seconds 2004/03/08 16:20:01OK, 77575 bytes0 seconds 2004/03/08 16:25:01OK, 77569 bytes1 seconds 2004/03/08 16:30:00OK, 77569 bytes1 seconds 2004/03/08 16:35:00OK, 77575 bytes1 seconds 2004/03/08 16:40:00OK, 77575 bytes0 seconds 2004/03/08 16:45:01OK, 77569 bytes0 seconds 2004/03/08 16:50:01OK, 77569 bytes0 seconds 2004/03/08 16:55:01OK, 77569 bytes1 seconds 2004/03/08 17:00:00OK, 77588 bytes1 seconds 2004/03/08 17:05:01OK, 77601 bytes3 seconds 2004/03/08 17:10:00OK, 77601 bytes1 seconds 2004/03/08 17:15:00OK, 77601 bytes1 seconds 2004/03/08 17:20:01OK, 77595 bytes0 seconds 2004/03/08 17:25:01OK, 77595 bytes1 seconds 2004/03/08 17:30:01OK, 77577 bytes1 seconds 2004/03/08 17:35:00OK, 77577 bytes1 seconds 2004/03/08 17:40:00OK, 77577 bytes1 seconds 2004/03/08 17:45:00OK, 77577 bytes1 seconds 2004/03/08 17:50:00OK, 77565 bytes1 seconds 2004/03/08 17:55:00OK, 77577 bytes1 seconds 2004/03/08 18:00:00OK, 77577 bytes1 seconds 2004/03/08 18:05:00OK, 77577 bytes1 seconds 2004/03/08 18:10:00OK, 77571 bytes1 seconds 2004/03/08 18:15:00OK, 77571 bytes1 seconds 2004/03/08 18:20:00OK, 77577 bytes2 seconds 2004/03/08 18:25:00OK, 77571 bytes1 seconds 2004/03/08 18:30:00OK, 77571 bytes1 seconds 2004/03/08 18:35:00OK, 77589 bytes1 seconds 2004/03/08 18:40:00OK, 77601 bytes1 seconds 2004/03/08 18:45:00OK, 77595 bytes2 seconds 2004/03/08 18:50:00OK, 77595 bytes1 seconds 2004/03/08 18:55:00OK, 77583 bytes1 seconds 2004/03/08 19:00:01OK, 77601 bytes1 seconds 2004/03/08 19:05:00OK, 77595 bytes1 seconds 2004/03/08 19:10:01OK, 77601 bytes1 seconds 2004/03/08 19:15:00OK, 77595 bytes1 seconds 2004/03/08 19:20:00OK, 77583 bytes1 seconds 2004/03/08 19:25:00OK, 77589 bytes1 seconds 2004/03/08 19:30:00OK, 77595 bytes1 seconds 2004/03/08 19:35:00OK, 77595 bytes1 seconds 2004/03/08 19:40:00OK, 77601 bytes1 seconds 2004/03/08 19:45:00OK, 77589 bytes1 seconds 2004/03/08 19:50:01OK, 77601 bytes1 seconds 2004/03/08 19:55:00OK, 77583 bytes2 seconds 2004/03/08 20:00:00OK, 77601 bytes1 seconds 2004/03/08 20:05:00OK, 77595 bytes1 seconds 2004/03/08 20:10:00OK, 77595 bytes1 seconds 2004/03/08 20:15:00OK, 77595 bytes1 seconds 2004/03/08 20:20:00OK, 77595 bytes0 seconds 2004/03/08 20:25:01OK, 77601 bytes0 seconds 2004/03/08 20:30:01OK, 77601 bytes1 seconds 2004/03/08 20:35:00OK, 77601 bytes1 seconds 2004/03/08 20:40:00OK, 77601 bytes1 seconds 2004/03/08 20:45:00OK, 77601 bytes1 seconds 2004/03/08 20:50:01OK, 77601 bytes0 seconds 2004/03/08 20:55:01OK, 77601 bytes0 seconds 2004/03/08 21:00:00OK, 77601 bytes1 seconds 2004/03/08 21:05:00OK, 77595 bytes1 seconds 2004/03/08 21:10:00OK, 77601 bytes1 seconds 2004/03/08 21:15:00OK, 77601 bytes1 seconds 2004/03/08 21:20:00OK, 77595 bytes1 seconds 2004/03/08 21:25:00OK, 77595 bytes1 seconds 2004/03/08 21:30:00OK, 77601 bytes2 seconds 2004/03/08 21:35:00OK, 77595 bytes1 seconds 2004/03/08 21:40:00OK, 77601 bytes1 seconds 2004/03/08 21:45:00OK, 77601 bytes0 seconds 2004/03/08 21:50:00OK, 77601 bytes1 seconds 2004/03/08 21:55:00OK, 77595 bytes1 seconds 2004/03/08 22:00:00OK, 77595 bytes1 seconds 2004/03/08 22:05:00OK, 77601 bytes1 seconds 2004/03/08 22:10:00OK, 77595 bytes1 seconds 2004/03/08 22:15:00OK, 77595 bytes1 seconds 2004/03/08 22:20:00OK, 77601 bytes1 seconds 2004/03/08 22:25:00OK, 77601 bytes1 seconds 2004/03/08 22:30:01OK, 77589 bytes2 seconds 2004/03/08 22:35:00OK, 77601 bytes1 seconds 2004/03/08 22:40:00OK, 77601 bytes1 seconds 2004/03/08 22:45:00OK, 77601 bytes1 seconds 2004/03/08 22:50:00OK, 77601 bytes1 seconds 2004/03/08 22:55:00OK, 77595 bytes1 seconds 2004/03/08 23:00:00OK, 77601 bytes1 seconds 2004/03/08 23:05:00OK, 77595 bytes1 seconds 2004/03/08 23:10:00OK, 77595 bytes1 seconds 2004/03/08 23:15:00OK, 77589 bytes0 seconds 2004/03/08 23:20:01OK, 77601 bytes0 seconds 2004/03/08 23:25:01OK, 77601 bytes0 seconds 2004/03/08 23:30:00OK, 77589 bytes1 seconds
Re: DateDiff function in SqlServer ... How do it in MySql ?
Michael Stassen said: Gabriel Alessandria wrote: I am a user of Microsoft Sql Server and use very much the function Datediff (interval, fecha1, fecha2) to extract differences between two dates, in years, days, months, hours, etc I am a beginner with MySql and i can't see the way to do this function .. How can I extract for example difference of hours between two given dates .. someone help me please ? Thanks in advance Not quite sure what it means to get the difference between two dates in months or hours, but mysql does have a DATEDIFF function: DATEDIFF(expr,expr2) DATEDIFF() returns the number of days between the start date expr and the end date expr2. expr and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation. For more date functions, see the manual: http://www.mysql.com/doc/en/Date_and_time_functions.html Michael For finergrain accuracy: (UNIX_TIMESTAMP(TIStop)-UNIX_TIMESTAMP(TIStart))/60 This gets difference in minutes. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump JOIN?
I would like to do the following: mysqldump -w users.user_id=enews.user_id sotx users c:/enews_users.sql Ideally this would dump all records in table users where the user_id field value is also present in the enews table. Is this possible? TIA, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date Problem
We have a table with a Date Time field and we need to update only the date portion of that field. We have table a with field Foo with value '2004-01-01 12:15:00' and a date '2004-03-01' and we need to change the date portion of Foo to the date and leave the time part alone. So Foo would change from: '2004-01-01 12:15:00' to: '2004-03-01 12:15:00' I've been struggling with this for several hours now and I know there must be an easy solution. Any suggestions? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Problem
Hello Eric, Tuesday, March 9, 2004, 11:46:23 PM, you wrote: ES We have a table with a Date Time field and we need to update only the date ES portion of that field. ES We have table a with field Foo with value '2004-01-01 12:15:00' and a date ES '2004-03-01' and we need to change the date portion of Foo to the date and ES leave the time part alone. ES So Foo would change from: ES '2004-01-01 12:15:00' ES to: ES '2004-03-01 12:15:00' I would have thought that: UPDATE x SET y = DATE_ADD(y, INTERVAL 2 DAY) would work and not alter the time, but only a test will tell. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query INTO OUTFILE Problem
Hello MYSQL Genii, I am trying to run a query that returns results to a .csv file using mysql's 'INTO OUTFILE'. The problem is whenever I write the file I lose my the field names for each column. For example, when I run the query at command line mysql: mysql SELECT distinct count(*), Product FROM LIVE WHERE dbStatus = 'Live' and vertical = 'Cars' GROUP BY Product; I get this: +--+-+ | count(*) | Product | +--+-+ |4 | BANN| | 10 | CRBN| | 256 | LEAD| | 36 | SALE| | 26 | TRBN| +--+-+ 5 rows in set (0.20 sec) I want to have the titles of my columns (like above) in my .csv file... When I use the 'INTO OUTFILE' syntax I lose all of the titles and just get the data returned like this: 4,BANN 10,CRBN 256,LEAD 36,SALE 26,TRBN Is it possible to use INTO OUTFILE and still get my field names outputted? Here is the query $var I am using for perl or php. $query = SELECT distinct count(*), Product FROM LIVE WHERE dbStatus = 'Live' and vertical = 'Cars' GROUP BY Product INTO OUTFILE '/www/vhosts/someurl.org/htdocs/report_files/apts_mnth_unit.csv' FIELDS TERMINATED BY '\,' OPTIONALLY ENCLOSED BY '\' LINES TERMINATED BY '\n'; If anyone of you geeks, I mean genii, can assist, I would be happy to name my first born in your honor. TIA! \\\|/// \\ ~ ~ // (/ @ @ /) +oOOo-(_)-oOOo--+ | Bob Bessares | | latimes.com | | 202 West 1st Street | | Los Angeles, CA 90012| | Phone : (213) 473-2505 | | Fax : (213) 473-2438 | | e-mail: [EMAIL PROTECTED] | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Problem
Richard Davey wrote: Hello Eric, Tuesday, March 9, 2004, 11:46:23 PM, you wrote: ES We have a table with a Date Time field and we need to update only the date ES portion of that field. ES We have table a with field Foo with value '2004-01-01 12:15:00' and a date ES '2004-03-01' and we need to change the date portion of Foo to the date and ES leave the time part alone. ES So Foo would change from: ES '2004-01-01 12:15:00' ES to: ES '2004-03-01 12:15:00' I would have thought that: UPDATE x SET y = DATE_ADD(y, INTERVAL 2 DAY) But that is adding two days, the original query was to add two months, so presumably it should be INTERVAL 2 MONTH - but beware that MySQL does some seriously bizarre things with dates - for example adding 2 months to 31st Dec takes you to 31st Feb which probably isn't what you want. would work and not alter the time, but only a test will tell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Date Problem
Hello Peter,
Wednesday, March 10, 2004, 12:16:51 AM, you wrote:
PB But that is adding two days, the original query was to add two
PB months, so presumably it should be INTERVAL 2 MONTH - but beware
PB that MySQL does some
It should, sorry, I'm too used to the UK date format (even though I
know MySQL doesn't use it) but you get the idea anyway.
PB seriously bizarre things with dates - for example adding 2 months
PB to 31st Dec takes you to 31st Feb which probably isn't what you
PB want.
Doesn't for me:
SELECT DATE_ADD('2004-12-31 00:00:00', INTERVAL 2 MONTH)
Gives me 2005-02-28 00:00:00, which is what I would expect. That's
on MySQL 3.23.58, so I doubt if they broke it in any version since.
MySQLs date handling has never caused any problems for me (when I
remember the correct y-m-d format :)
--
Best regards,
Richard Davey
http://www.phpcommunity.org/wiki/296.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql unexpectedly dying
Ian Pushee wrote: I am running mysql version 3.23.49 on a Debian Linux box. Recently (about a month ago) I noticed that alot of my databse access scripts were failing intermitently. It was anoying, but I have only now gotten the time to look into the problem. It turns out that my mysql server processes are dying at irregular intervals (varying from 1 to 20 minutes or so). If a script gets executed while they are being restarted, it fails. Debian uses safe_mysql to start/restart the server processes. If I run this as normal from init.d, the only thing that shows up in the logs is that the server is getting restarted (in mysql.err): 040308 14:21:14 mysqld restarted /usr/sbin/mysqld: ready for connections Number of processes running now: 0 In the normal log, there is no single query that seems to cause the server to die: in fact, it often seems to die w/out any queries being posted at all. If I run safe_mysqld from the console, I see this whenever the server restarts: /usr/bin/safe_mysqld: line 275: 25633 Killed $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 Number of processes running now: 0 040308 14:04:44 mysqld restarted If I run mysqld straight from the console I get this (after a little whiel of the server running): # mysqld mysqld: ready for connections Killed I've checked/repaired all fo the tables in the database and found no major errors. Anybody have any ideas what might be causing the server to restart like this? Check your syslog for OOM kills. When Linux kernel starts running out of RAM, it will kill processes using a hueristics to pick the one that will give it the most bang for the buck. A fat mysqld configured with a large key buffer and using lots of RAM is a prime candidate. All 2.4 versions I've tried ( have not tried this on 2.6), have a bug that under some circusmstances would rather kill a fat process than free up buffer cache. One way to avoid this is to have lots of physical RAM + a decent swap. http://www.mysql.com/doc/en/System.html recommends disabling swap if you have plenty of RAM. This is a bad idea on Linux, although it does make a lot of sense in theory. I have written a few messages to the kernel list in the past arguing that it would be nice to have the kernel work well without swap, but the gist of the replies I got was that no swap is basically a misconfiguration even if you have more RAM than disk space. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Optimizing Queries
Rich, Thanks for the email. I created a multi-field index using the fields that are in the query and the query only took 0.91 seconds. That's better than 9 minutes and definitely under 30 seconds. Thanks. Chris. -Original Message- From: Richard Davey [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 11:00 AM To: [EMAIL PROTECTED] Subject: Re: Optimizing Queries Hello Chris, Tuesday, March 9, 2004, 4:38:00 PM, you wrote: CF I'm trying to determine the best way to optimize the query below. CF Right now it is taking around 9mins and we need it to take no more CF than 30 seconds (we can get it under 30s on MS SQL): CF | 1 | SIMPLE | speedlink | ref | CF idx_state,idx_county,idx_phone,idx_homeowner,idx_hpm,idx_ph,idx_first,idx_la CF st | idx_state | 3 | const | 2840162 | Using where | Some things that have been recommended to me which may be useful for you: Is the table in question Fixed or Dynamic? Do anything you can, including separating out dynamic length fields to joined tables, to make your main table fixed in length. Your fields appear to be indexed individually rather than collectively, so out of the 8 possible indexes in use, it's only using one of them (state). Try combining common/grouped indexes together based on the type of query you run most often. If MySQL is using the wrong index (or one that isn't as efficient as it could be) force it to use another. I don't know about your table definition, but check to see if you really do need to use the datatype you've currently selected. You can shave MBs from the total table size just by optimising your use of data types. Just some thoughts anyway. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- 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: Optimizing Queries
And Chris, maybe now you can get rid of some of those single indexes and drop the entire size down quite a bit. Donny -Original Message- From: Chris Fossenier [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 6:48 PM To: 'Richard Davey'; [EMAIL PROTECTED] Subject: RE: Optimizing Queries Rich, Thanks for the email. I created a multi-field index using the fields that are in the query and the query only took 0.91 seconds. That's better than 9 minutes and definitely under 30 seconds. Thanks. Chris. -Original Message- From: Richard Davey [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 11:00 AM To: [EMAIL PROTECTED] Subject: Re: Optimizing Queries Hello Chris, Tuesday, March 9, 2004, 4:38:00 PM, you wrote: CF I'm trying to determine the best way to optimize the query below. CF Right now it is taking around 9mins and we need it to take no more CF than 30 seconds (we can get it under 30s on MS SQL): CF | 1 | SIMPLE | speedlink | ref | CF idx_state,idx_county,idx_phone,idx_homeowner,idx_hpm,idx_ph,idx_first,idx_ la CF st | idx_state | 3 | const | 2840162 | Using where | Some things that have been recommended to me which may be useful for you: Is the table in question Fixed or Dynamic? Do anything you can, including separating out dynamic length fields to joined tables, to make your main table fixed in length. Your fields appear to be indexed individually rather than collectively, so out of the 8 possible indexes in use, it's only using one of them (state). Try combining common/grouped indexes together based on the type of query you run most often. If MySQL is using the wrong index (or one that isn't as efficient as it could be) force it to use another. I don't know about your table definition, but check to see if you really do need to use the datatype you've currently selected. You can shave MBs from the total table size just by optimising your use of data types. Just some thoughts anyway. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- 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[2]: Optimizing Queries
Hello Chris, Wednesday, March 10, 2004, 12:48:02 AM, you wrote: CF Thanks for the email. I created a multi-field index using the fields that CF are in the query and the query only took 0.91 seconds. That's better than 9 CF minutes and definitely under 30 seconds. Glad to hear it. Farewell MSSQL :) -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqldump JOIN?
I've tried about 20 different variations with no luck except when i modify the where statement to use only one table. Then it works fine but it's not the results i want. I was suspecting exactly what you said: you can only refer to one table, and the first one you specify at that. I was trying to avoid creating a temp table and all that as this is a dump that i'll have to do a lot. Just thinking off the top of my head: I could probably write a batch file that executes the necessary commands to create the temp table, dump the contents, and then remove the temp table. I'll see if it works. Thanks, Rhino. -dan -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 5:58 PM To: [EMAIL PROTECTED] Subject: Re: mysqldump JOIN? According to the mysqldump article in the manual - http://www.mysql.com/doc/en/mysqldump.html - you can select specific rows to dump with the --where or -w options. I've never tried making the where clause refer to a different table; I suspect from the examples given that you can only refer to the same table. If you haven't given it a try yet, try it now and see if it works. If you have tried it and it doesn't accept a --where that refers to a second table, you could try creating a temporary table, copying the desired rows into it, then doing mysqldump on the temporary table. I can't promise this will work but it should be easy enough to try. Rhino - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 6:15 PM Subject: mysqldump JOIN? I would like to do the following: mysqldump -w users.user_id=enews.user_id sotx users c:/enews_users.sql Ideally this would dump all records in table users where the user_id field value is also present in the enews table. Is this possible? TIA, Dan -- 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: Can't create thread
Michael Bacarella wrote: We recently started getting Can't create thread errors since switching to Debian. On Red Hat 8.0 we were able to spawn more than 400 mysql threads and never encountered this error. mysql 3.23.56 compiled from source, stock kernel. (2GB of RAM) Now we get it all the time on Debian and the MySQL AB 3.23.58 binary around 245 threads, linux 2.4.23 custom kernel. (3GB of RAM, not that it matters) Are we missing a setting? Does Red Hat have some kind of userland address space hack that we're not aware of? Do you have any special kernel config options that you did not use before? Actually, we just found something interesting on the Debian box. # ps aux | grep mysqld [...] mysql25303 0.4 30.4 1228720 947112 ?S16:16 0:23 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data --user=mysql --pid-file=/usr/local/mysql/data/dbms3.pid --skip-locking [...] # tail /proc/25303/maps bee01000-bf00 rwxp 1000 00:00 0 bf00-bf001000 ---p 00:00 0 bf001000-bf20 rwxp 1000 00:00 0 bf20-bf201000 ---p 00:00 0 bf201000-bf40 rwxp 1000 00:00 0 bf40-bf401000 ---p 00:00 0 bf401000-bf60 rwxp 1000 00:00 0 bf60-bf601000 ---p 00:00 0 bf601000-bf80 rwxp 1000 00:00 0 bfffa000-c000 rwxp b000 00:00 0 the thread stacks are 2MB apiece (bf601000-bf80 is 2093056 bytes, or 2044kB)! Yet: # mysql -e 'show variables' | grep thread_stack thread_stack196608 It seems like the setting does nothing for us. We top out at exactly 256 threads. Ok, that's the problem. Thread stack on older glibc versions is hard-coded. Newer versions (at least 2.3.2) will have a truly adjustable stack size if it is compiled with FLOATING_STACKS defined, which is off by default. One possibility is that the MySQL build team moved to linking against glibc 2.3.2, but did not enable floating stacks in it. Maybe Lenz can comment on it. There are a couple of possiblities for the fix. I believe at some point in 2.3 glibc started having true user-adjustable thread stack sizes. Try compiling MySQL on your Debian box and check the stack size you are getting - maybe by some odd luck the Debian team enabled floating stacks. But even if they did, you are still limited to 1021 threads with this option. If that does not work, my next line of defense would be to clone the bk repository, and follow the instructions in Docs/linuxthreads.txt - this would give you a good old link against a patched glibc 2.2.5 which to my knowledge is problem free, and the binary you get is 100% system glibc independent (a regular statically linked binary still depends on local shared libraries for DNS and user lookups). Another possiblity, if you do not mind messing with the main glibc on the box, is to recomile it fixing up PTHREAD_THREAD_MAX and defining FLOATING_STACKS -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error on startup
Heya, Check the permission on that directory. Paul At 12:36 AM 3/9/2004, Lorenzo Rossi wrote: Hi. My system is Slackware 9.1. MySQL is the default version installed on Slackware cdrom while system installation, so I hope all the path are ok :) Then launched mysqld_install_db... but when I try to startup the server an error occured wrote in /usr/local/mysql/ginlemon.err like this: 3:49:08 /usr/local/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) This is a tipical error for me, almost everytime I install mysql..:-( Any one can put me on the right way? Thanx, Lorenzo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Paul Rigor [EMAIL PROTECTED] Go Bruins! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Security
I have used access in the past and now I have started using MySQL. I have customer table, customer order table, customer order detail table. How would I make sure that when a particular customer log-in he/she sees only the account that is set-up for them. What confused me is that MySQL has a database called mysql and a table in this database called users that is used to set a user name and password for each user. I could not figure out how a user in my case a customer that has access to a customer table could be restricted to see his/her transaction only. Any insight is very much appreciated.
Re: Security
Heya, Those are the default databases that comes with the setup. the mysql database holds info on mysql accounts. the test is an empty database. You should create a new database CREATE DATABASE customers then use customers... after that... you can setup the tables you mentioned. Goodluck! Paul At 06:34 PM 3/9/2004, Mulugeta Maru wrote: I have used access in the past and now I have started using MySQL. I have customer table, customer order table, customer order detail table. How would I make sure that when a particular customer log-in he/she sees only the account that is set-up for them. What confused me is that MySQL has a database called mysql and a table in this database called users that is used to set a user name and password for each user. I could not figure out how a user in my case a customer that has access to a customer table could be restricted to see his/her transaction only. Any insight is very much appreciated. _ Paul Rigor [EMAIL PROTECTED] Go Bruins! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 1gb memory limit?
Donny Simonton wrote: Peter, There is no 1 gig limit that I am aware of. I have been using MySQL 4.1 since the day it was released. And when 4.1.1 came out I switched about half of our machines to using it, and when 4.1.2 comes out in the next week or so, I will switch our stuff that is using 4.1.x to that as well. We are using Fedora core 1 and we don't have a memory limit at all. Linux does have a 2gig memory limit per process or thread. But MySQL can definitely use more than 1gig of memory. If it couldn't then I wouldn't be using it. I think what 1 GB refers to is that on 32-bit Linux, it is a good idea to keep your mysqld buffer memory utilization under 1 GB to keep the thread stacks from running into the heap. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Security
Thank you for the kind response. May be I did not clearly ask the question. The user table in mysql database is used to set-up a user and password. Once I set-up my tables (customer, customer orders, customer order details, etc) in say abc database what will I have to do to make sure when customer A logs in to the database can only see his/her account, orders, order details without getting access to other customer accounts. I hope my question is clear. Maru - Original Message - From: Paul Rigor [EMAIL PROTECTED] To: Mulugeta Maru [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 7:46 PM Subject: Re: Security Heya, Those are the default databases that comes with the setup. the mysql database holds info on mysql accounts. the test is an empty database. You should create a new database CREATE DATABASE customers then use customers... after that... you can setup the tables you mentioned. Goodluck! Paul At 06:34 PM 3/9/2004, Mulugeta Maru wrote: I have used access in the past and now I have started using MySQL. I have customer table, customer order table, customer order detail table. How would I make sure that when a particular customer log-in he/she sees only the account that is set-up for them. What confused me is that MySQL has a database called mysql and a table in this database called users that is used to set a user name and password for each user. I could not figure out how a user in my case a customer that has access to a customer table could be restricted to see his/her transaction only. Any insight is very much appreciated. _ Paul Rigor [EMAIL PROTECTED] Go Bruins! -- 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: mysqladmin processlist and pid
Hi Tom, You can't. MySQL's own thread ids are sequential. The OS pids are random. There's no connection between them. Besides, mysqld is really only running in a single real process, it's just that LinuxThreads shows each thread as a process. Matt - Original Message - From: Tom Roos Sent: Tuesday, March 09, 2004 6:05 AM Subject: mysqladmin processlist and pid hi listers how would i determine the association between the id from mysqladmin processlist to a pid? what i'm after is that i notice some of the mysql threads have a high cpu utilization. i see this using top (on a linux box). i would like to know which user/program is responsible for tuning purposes. i can use mysqladmin processlist and it gives me a list (including an id) of what processes are running but how do i tie this in with unix' pid? tks tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to do automatic backup?
Hi, Is there any function for MySQL database to do automatic backup? Let say, i can set the time, that MySQL will automatic backup between 7.00pm-8.00pm everyday. Thank you very much. regards, florence Thank you very much. regards, florence Y! Asia presents Lavalife - Stand a chance to win a dream date, join the Dream Guy Contest!
Re: How to do automatic backup?
cron schedule it ? Hi, Is there any function for MySQL database to do automatic backup? Let say, i can set the time, that MySQL will automatic backup between 7.00pm-8.00pm everyday. Thank you very much. regards, florence Thank you very much. regards, florence Y! Asia presents Lavalife - Stand a chance to win a dream date, join the Dream Guy Contest! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB tables using 90% cpu
Cliff wrote: The query is running dramatically slower than the MyISAM query, sometimes even causing mysql to freeze for a while. I searched this list and found a few people saying that on FreeBSD mysql should be compiled using linux pthreads if you are using InnoDB or else I would get this exact problem. Has this been resolved or is should I recompile? I am using native freebsd threads. Compare the output of EXPLAIN for both MyISAM and InnoDB. Also, if you have a Linux or even Windows system around, test the same query on them to see if there is any difference. I am not that familiar with FreeBSD, but from what I've seen, I would tend to use the approach when in doubt, use LinuxThreads. The advice you've got does make sense - InnoDB runs a number of background threads, so if your thread implementation is lacking, it would hurt InnoDB more than MyISAM. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Pricelist
Sturgeon, Jon wrote: [EMAIL PROTECTED] wrote: Look it through Can the list administrator figure out who this moron is and remove him from the list? Jon: This so-called moron is Miguel Soloranzo - the guy in charge of MySQL Windows builds. So blocking his posts to the list would be a bad idea. The problem is that somebody got infected with a virus that picked up his address, as well as the address of the list, and keeps posting viruses in his name. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB tables using 90% cpu
Sasha Pachev wrote: Cliff wrote: The query is running dramatically slower than the MyISAM query, sometimes even causing mysql to freeze for a while. I searched this list and found a few people saying that on FreeBSD mysql should be compiled using linux pthreads if you are using InnoDB or else I would get this exact problem. Has this been resolved or is should I recompile? I am using native freebsd threads. Compare the output of EXPLAIN for both MyISAM and InnoDB. Also, if you have a Linux or even Windows system around, test the same query on them to see if there is any difference. I am not that familiar with FreeBSD, but from what I've seen, I would tend to use the approach when in doubt, use LinuxThreads. The advice you've got does make sense - InnoDB runs a number of background threads, so if your thread implementation is lacking, it would hurt InnoDB more than MyISAM. FreeBSD's thread library is a user-space setup. This can result in major performance issues when doing things that block. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Pricelist
They most probably got it from this list, i too have had virus emails sending emails with my email and name in the header !! but i dont have a virus !! Sturgeon, Jon wrote: [EMAIL PROTECTED] wrote: Look it through Can the list administrator figure out who this moron is and remove him from the list? Jon: This so-called moron is Miguel Soloranzo - the guy in charge of MySQL Windows builds. So blocking his posts to the list would be a bad idea. The problem is that somebody got infected with a virus that picked up his address, as well as the address of the list, and keeps posting viruses in his name. -- Sasha Pachev Create online surveys at http://www.surveyz.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]
