Re: why NOT NULL in PRIMARY key??
From: Mikhail Entaltsev Mikhail, Before inserting MySQL will try to find a record with the same values. But since comparison with NULL value returns always FALSE MySQL will think that there is no such record in the table. After this point you will get 2 identical records in the table. MySQL as a whole seems a bit more flexible: a UNIQUE index allows multiple NULL values (which seems to follow the NULL handling you describe), but with BDB table only a single NULL value is allowed in a UNIQUE index (so MySQL seems to be able to find a NULL value in de db). Anyway, I gues it's just a question of following a standard and optimizing the engine according to that standard. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Get a Random Row on a HUGE db
$last_row =SELECT from firebase_content LAST_INSERT_ID(); $last_row_query = $dbi-query($last_row); $last_row_result = $row-id; i think LAST_INSERT_ID will not work for what you wonna do. if you open a connection to MySQL and call LAST_INSERT_ID without a INSERT it will return 0. http://dev.mysql.com/doc/mysql/en/getting-unique-id.html For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. so it would work if you have only ONE connection for all INSERTs and your SELECT. i think you have to get the highest id first and then select a random row. instead of SELECT MAX() try SELECT `id` FROM `whatever` ORDER BY `id` DESC LIMIT 1; i tried this and it's faster then MAX(). ORDER BY RAND() seems to be the slowest method. chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql Client Connection
Dear All, I have mysql installed on top of redhat 3.0 machines and I have created user called root and assigned a password. I have tried connecting to database locally by using Mysql -h localhost -u root -p it connected When I tried using host name it is not connecting and giving an error Error 1130: Host 'node1.example.com' is not allowed to connect to this MySQL server. Can any body suggest me the right procedure. Regards bala
Re: Multi Table Delete in 3.23.47
zzapper wrote: On Tue, 26 Apr 2005 23:22:46 +0100, wrote: Hi, The following query runs fine in recent versions of mysql 4.1x etc delete from t2 using tbl_User as t1,tbl_UserTopic as t2 where (t1.txtemail='[EMAIL PROTECTED]') and (t1.intID = t2.intID); But not in 3.23.47 I can't find any reference in the manuals to why this might be. Is there any change in syntax which will make the query work in 3.23.47? I mean how did you do this before 4.x?? No trick will make it work with a 3.23. You'll need to program it yourself with your favorite langage (PHP, Perl, Templeet, C...). I know it's painful... (but it's great to have it in 4.0 as well as multi-table updates) -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOAD DATA INFILE and STR_TO_DATE
thanks, that's what I feared already.. although ugly, will do it that way so far as I don't want to do any other pre-processing on the fixed CSV's I receive. moreover I just found out STR_TO_DATE isn't available in mysql 4.0 anyway.. Michael -Original Message- From: Jigal van Hemert [mailto:[EMAIL PROTECTED] I'm afraid that the syntax states that you can only use a list of column names. I think the best you can do is make an extra varchar column to hold the raw 'date' value from the csv and use an extra query to convert it into a format suitable for the gmtDate column. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Client Connection
Hi, look at http://dev.mysql.com/doc/mysql/en/grant.html and apply grant to root@'node1.example.com'. Mathias Selon bala [EMAIL PROTECTED]: Dear All, I have mysql installed on top of redhat 3.0 machines and I have created user called root and assigned a password. I have tried connecting to database locally by using Mysql -h localhost -u root -p it connected When I tried using host name it is not connecting and giving an error Error 1130: Host 'node1.example.com' is not allowed to connect to this MySQL server. Can any body suggest me the right procedure. Regards bala -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update and select question
Hi, Look at select ... for update here : http://dev.mysql.com/doc/mysql/en/innodb-locking-reads.html Mathias Selon $B2 9bJv(B [EMAIL PROTECTED]: i use this mail first . i have a problem in under sql program: UPDATE NGLDENHDT SET EDT_HUK_FLG = :EDT_HUK_FLG WHERE KAI_CDE = :KAI_CDE AND EDT_NUM = (SELECT MAX(EDT_NUM) FROM NGLDENHDT WHERE KAI_CDE = :KAI_CDE_T1 AND EDT_NUM != ''); the error message was: You can't specify target table 'NGLDENHDT' for update in FROM clause what can i do ,help me ? thank you very much!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update and select question
$B2+9bJv(B wrote: (B i use this mail first . (B (B i have a problem in under sql program: (B (B (B UPDATE (B NGLDENHDT (B SET (B EDT_HUK_FLG = :EDT_HUK_FLG (B WHERE (B KAI_CDE = :KAI_CDE (B AND EDT_NUM = (SELECT MAX(EDT_NUM) (BFROM NGLDENHDT (BWHERE KAI_CDE = :KAI_CDE_T1 (BAND EDT_NUM != ''); (B (B the error message was: (B (B You can't specify target table 'NGLDENHDT' for update in FROM (B clause (B (B (BYou can't update and select with a subquery at the same time the very (Bsame table. (Btry a 2-fold action : (BBEGIN; (BSELECT @val:= MAX(EDT_NUM) (B FROM NGLDENHDT (BWHERE KAI_CDE = :KAI_CDE_T1 (BAND EDT_NUM != ''; (B UPDATE (B NGLDENHDT (B SET (B EDT_HUK_FLG = :EDT_HUK_FLG (B WHERE (B KAI_CDE = :KAI_CDE (B AND EDT_NUM = @val; (BCOMMIT; (B (B (B what can i do ,help me ? (B (B (B thank you very much!! (B (B (B (B-- (BPhilippe Poelvoorde (BCOS Trading Ltd. (B (B-- (BMySQL General Mailing List (BFor list archives: http://lists.mysql.com/mysql (BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Client Connection
Hello. Add user 'root'@'node1.example.com'. See: http://dev.mysql.com/doc/mysql/en/access-denied.html Dear All, I have mysql installed on top of redhat 3.0 machines and I have created user called root and assigned a password. I have tried connecting to database locally by using Mysql -h localhost -u root -p it connected When I tried using host name it is not connecting and giving an error Error 1130: Host 'node1.example.com' is not allowed to connect to this MySQL server. Can any body suggest me the right procedure. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Client Hangs on QNX
Hello. Your version of MySQL is rather old. Does the problem remains after upgrading? Stephen Rasku [EMAIL PROTECTED] wrote: I am using MySQL 4.0.17 on QNX 6.2.1b. I have noticed this in the last couple of days. There were two MySQL clients running: one since 1:30 that morning; one at 1:30 the previous morning. These are reset master commands we issue from cron to clean up the transaction logs. This morning I tried running mysql from the command line and it hung. I then run with the debug option enabled. Here is the content of /tmp/mysql.trace: | my_malloc | | my: Size: 520 MyFlags: 16 | | exit: ptr: 8085d28 | my_malloc | my_malloc | | my: Size: 512 MyFlags: 48 | | exit: ptr: 8085b20 | my_malloc | mysql_close | | my_free | | | my: ptr: 0 | | my_free | | my_free | | | my: ptr: 0 | | my_free | | my_free | | | my: ptr: 0 | | my_free | | my_free | | | my: ptr: 0 | | my_free | | my_free | | | my: ptr: 0 | | my_free | | my_free | | | my: ptr: 0 | | my_free | | my_free | | | my: ptr: 0 | | my_free | | my_free | | | my: ptr: 0 | | my_free | | my_free | | | my: ptr: 0 | | my_free | | my_free | | | my: ptr: 0 | | my_free | | my_free | | | my: ptr: 0 | | my_free | | my_free | | | my: ptr: 0 | | my_free | | my_free | | | my: ptr: 0 | | my_free | | my_free | | | my: ptr: 0 | | my_free | | mysql_close | | mysql_close | mysql_close | mysql_real_connect | | enter: host: (Null) db: (Null) user: root | | info: Using UNIX sock '/tmp/mysql.sock' | | vio_new | | | enter: sd=4 | | | my_malloc | | | | my: Size: 84 MyFlags: 16 | # tail -f /tmp/mysql.trace | | mysql_close | mysql_close | mysql_real_connect | | enter: host: (Null) db: (Null) user: root | | info: Using UNIX sock '/tmp/mysql.sock' | | vio_new | | | enter: sd=4 | | | my_malloc | | | | my: Size: 84 MyFlags: 16 | To me, it appears that it is hung in my_malloc(). However not on the first call. It doesn't appear that I am running out of memory: # pidin in CPU:X86 Processors:1 FreeMem:927Mb/1015Mb BootTime:Apr 21 16:44:25 PDT 2005 Processor1: 1586 Intel ?86 F15M2S9 2670Mhz FPU Any ideas what is going wrong? This system has been running fine for months until now. ...Stephen -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: update and select question
From: "$B2+9bJv(B" (B (B UPDATE (B NGLDENHDT (B SET (B EDT_HUK_FLG = :EDT_HUK_FLG (B WHERE (B KAI_CDE = :KAI_CDE (B AND EDT_NUM = (SELECT MAX(EDT_NUM) (BFROM NGLDENHDT (BWHERE KAI_CDE = :KAI_CDE_T1 (BAND EDT_NUM != ''); (B (B the error message was: (B (B You can't specify target table 'NGLDENHDT' for update in FROM (B (BFor a small table you could use: (B (BUPDATE (BNGLDENHDT (BSET (BEDT_HUK_FLG = :EDT_HUK_FLG (BWHERE (B KAI_CDE = :KAI_CDE (B AND EDT_NUM != '') (BORDER BY (B EDT_NUM DESC (BLIMIT 1; (B (BThis would sort the table on EDT_NUM with the biggest number first and then (Bonly update a single record. (B (BRegards, Jigal. (B (B (B-- (BMySQL General Mailing List (BFor list archives: http://lists.mysql.com/mysql (BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi Table Delete in 3.23.47
On Wed, 27 Apr 2005 09:50:37 +0100, wrote: zzapper wrote: On Tue, 26 Apr 2005 23:22:46 +0100, wrote: Hi, The following query runs fine in recent versions of mysql 4.1x etc delete from t2 using tbl_User as t1,tbl_UserTopic as t2 where (t1.txtemail='[EMAIL PROTECTED]') and (t1.intID = t2.intID); But not in 3.23.47 I can't find any reference in the manuals to why this might be. Is there any change in syntax which will make the query work in 3.23.47? I mean how did you do this before 4.x?? No trick will make it work with a 3.23. You'll need to program it yourself with your favorite langage (PHP, Perl, Templeet, C...). I know it's painful... (but it's great to have it in 4.0 as well as multi-table updates) -- Philippe Poelvoorde COS Trading Ltd. Thanx Philippe that stops me scratching about looking for a non-existant solution.I'm happy to write a perl script just wished I tried it sooner! It's so wretched when your ISP won't upgrade their S/W -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Archive Mbox Files Needed
Am analysing community participation in mailing lists. I have a python script that reads mbox files and parse the contents into Mysql database. Does anyone knows where i can get archived mbox files of the Mysql mailing lists? Thanks in advance -- Sulayman K. Sowe Aristotle University of Thessaloniki Department of Informatics Thessaloniki 54124. Greece Tel:(Office) +30-2310-998236 Mobile: +30 6934309664 Fax: +30-2310-998419 Dept. URL2:http://sweng.csd.auth.gr/sowe.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
On 4/26/05, Jay Blanchard [EMAIL PROTECTED] wrote: [snip] The same is true for any other value... Now that the columns have a NOT NULL constraint the records that previously contained NULL now hold '0'. x y x 0 x z x 0 Now, how do you uniquely identify the 2nd and 4th rows? [/snip] The database would have thrown an error when you tried to create row 4. No. NULL is not a value. It is a lack of value. Kind of a special symbol, like infinity in mathematics. You cannot say that infinity = infinity or infinity infinity. Same as you cannot say that NULL = NULL or NULL NULL. Regards, Dawid -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
[snip] The same is true for any other value... Now that the columns have a NOT NULL constraint the records that previously contained NULL now hold '0'. x y x 0 x z x 0 Now, how do you uniquely identify the 2nd and 4th rows? [/snip] The database would have thrown an error when you tried to create row 4. No. NULL is not a value. It is a lack of value. Kind of a special symbol, like infinity in mathematics. You cannot say that Actually, it's not even the lack of value. NULL is a state. A column can have two states: NULL or NON NULL. Hence: WHERE mycolumn IS NOT NULL or mycolumn IS NULL Besides a non-null state, it can have many values :-) 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]
Empty select
From: root To: mysql@lists.mysql.com Subject: [Empty select] Description: There are records in the table (select count(*) returns 1), but select * returns empty recordset How-To-Repeat: DROP TABLE IF EXISTS ugo; CREATE TABLE ugo ( AKCE_1 char(1) default '', AUT_KOD char(10) default '', BONUS_CR char(1) default '', BR_BODOVA decimal(6,2) default '0.00', BR_BODOVA2 decimal(6,2) default '0.00', BR_RATA int(11) default '0', BR_UG char(10) default '', BR_UG2 char(10) default '' ) ENGINE=MyISAM DEFAULT CHARSET=cp1250; insert into ugo ( BONUS_CR, BR_BODOVA, BR_BODOVA2) values ( 'a' , 0, 0); select count(*) from ugo; #returns 1 select * from ugo; #returns nothing ! Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:root Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ critical ] Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug ] Release: mysql-5.0.4-beta-standard (MySQL Community Edition - Standard (GPL)) Server: /usr/bin/mysqladmin Ver 8.41 Distrib 5.0.4-beta, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 5.0.4-beta-standard Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 1 day 1 hour 21 min 55 sec Threads: 4 Questions: 9563 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 64 Queries per second avg: 0.105 C compiler:2.95.3 C++ compiler: 2.95.3 Environment: machine, os, target, libraries (multiple lines) System: Linux lipa.zepter.cz 2.6.9-1.667 #1 Tue Nov 2 14:41:25 EST 2004 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc/i386-redhat-linux/3.4.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-java-awt=gtk --host=i386-redhat-linux Thread model: posix gcc version 3.4.2 20041017 (Red Hat 3.4.2-6.fc3) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=i486 -fno-strength-reduce' CXX='gcc' CXXFLAGS='-O2 -mcpu=i486 -fno-strength-reduce -felide-constructors -fno-exceptions -fno-rtti ' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Apr 18 14:11 /lib/libc.so.6 - libc-2.3.3.so -rwxr-xr-x 1 root root 1504728 Oct 28 01:00 /lib/libc-2.3.3.so -rw-r--r-- 1 root root 2404716 Oct 28 00:46 /usr/lib/libc.a -rw-r--r-- 1 root root 204 Oct 28 00:08 /usr/lib/libc.so Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--with-comment=MySQL Community Edition - Standard (GPL)' '--with-server-suffix=-standard' '--without-embedded-server' '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--libdir=/usr/lib' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--enable-thread-safe-client' '--with-readline' 'CC=gcc' 'CFLAGS=-O2 -mcpu=i486 -fno-strength-reduce' 'CXXFLAGS=-O2 -mcpu=i486 -fno-strength-reduce -felide-constructors -fno-exceptions -fno-rtti ' 'CXX=gcc' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi Table Delete in 3.23.47
Hi, Final whinge: I wish the mysql website made it a little clearer which features are recent additions, I suggested color coding. BTW regards multi table deletes there's something nasty there between 4.0 and 4.1 -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
java.lang.OutOfMemoryError
I have too many databases,when i select it by client ,but it has error . (B (Bthe error message is : java.lang.OutOfMemoryError (B (Bhow i can to resolve it.
Re: Efficient select/insert
Jonathan Mangin [EMAIL PROTECTED] wrote on 04/26/2005 05:09:23
PM:
- Original Message -
From: [EMAIL PROTECTED]
To: Jonathan Mangin [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Tuesday, April 26, 2005 3:20 PM
Subject: Re: Efficient select/insert
Jonathan Mangin [EMAIL PROTECTED] wrote on 04/26/2005
12:26:20
PM:
I would like to select several rows from one table
and insert them into another nearly identical table
using Perl/DBI:
snip
Why not use an INSERT...SELECT instead of splitting up the two steps?
snip
my $sql = INSERT table2 (col2, col3, col4) select col2, col3, col4
snip
This would be even faster if you could concatenate all of the elements
of
@array into a single list then you could say:
I thought that's what I already had. @array contains
selected primary keys from table 1.
Yes but it isn't formatted as a comma separated list to be used in an IN()
comparator.
#My PERL skills are non existent so you need to write this part.
#I am assuming that @array is holding a list of string values.
foreach my $element (@array) {
@araylist += ' + $element + ',;
}
@arraylist = left(@arraylist, length(@arraylist) -1)
#that's to remove the trailing comma at the end of the list
my $sql = select col2, col3, col4 from table1
where col1 IN (?);
my $sth = $dbh-prepare($sql);
$sth-execute(@arraylist) or die $sth-errstr();
Oh, I see. A List. Hmmm.
And did you forget insert or are you practicing black magic?
;)
Good catch! I did forget the INSERT clause. BTW, If @array is a list of
numbers, you don't need the surrounding single quotes. Just build the
string (arraylist) so that each number is not quoted but is still
separated by a comma.
snip
Excellent! Thanks a lot, Shawn.
--Jon
No prob! Glad I could help.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Multi Table Delete in 3.23.47
From: zzapper Final whinge: I wish the mysql website made it a little clearer which features are recent additions, I suggested color coding. 1) Someone from MySQL AB mentioned here recently that they are working on a new system to keep track of differences between versions for the documentation. So it might become easier to track in the future. 2) To me the various in MySQL version 4.0.2 or later you can use... plus examples are very clear most of the time 3) With the MySQL version you downloaded the documentation for that particular version was included BTW regards multi table deletes there's something nasty there between 4.0 and 4.1 Do you mean: --- Note: In MySQL 4.0, you should refer to the table names to be deleted with the true table name. In MySQL 4.1, you must use the alias (if one was given) when referring to a table name: -- I personally find the documentation pretty clear. Besides, there are many MySQL books with useful information and there is this (and other) e-mail list(s) where the people from MySQL AB answer many questions. Pretty neat for a free product IMHO ;-) Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: java.lang.OutOfMemoryError
huanggaofeng wrote: (B (BI have too many databases,when i select it by client ,but it has error . (B (Bthe error message is : java.lang.OutOfMemoryError (B (Bhow i can to resolve it. (B (B (B (BYou might try a java list. (B (B (B-- (BMySQL General Mailing List (BFor list archives: http://lists.mysql.com/mysql (BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi Table Delete in 3.23.47
zzapper wrote: On Wed, 27 Apr 2005 09:50:37 +0100, wrote: zzapper wrote: On Tue, 26 Apr 2005 23:22:46 +0100, wrote: Hi, The following query runs fine in recent versions of mysql 4.1x etc delete from t2 using tbl_User as t1,tbl_UserTopic as t2 where (t1.txtemail='[EMAIL PROTECTED]') and (t1.intID = t2.intID); But not in 3.23.47 I can't find any reference in the manuals to why this might be. Is there any change in syntax which will make the query work in 3.23.47? I mean how did you do this before 4.x?? No trick will make it work with a 3.23. You'll need to program it yourself with your favorite langage (PHP, Perl, Templeet, C...). I know it's painful... (but it's great to have it in 4.0 as well as multi-table updates) -- Philippe Poelvoorde COS Trading Ltd. Thanx Philippe that stops me scratching about looking for a non-existant solution.I'm happy to write a perl script just wished I tried it sooner! It's so wretched when your ISP won't upgrade their S/W I reckon, it's a bit hidden in the middle : Starting with MySQL 4.0.4, you can also perform UPDATE operations that cover multiple tables: in : http://dev.mysql.com/doc/mysql/en/update.html -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Get a Random Row on a HUGE db
On 4/26/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I am wanting to display a random page from my site, But I have over 12,000 articles right now and we add over 150 per day. What I wound up doing was a Virtual DOS attack on my own server because the 40 mb db was being loaded to many times. I have tons of memory and a Dell Dual Xeon 2.8 gig. Can someone think up a better way of doing this? I wish Mysql would just bring me back 1 valid random row It could be used in so many ways it should just be a part of MySql anyway. $stmt = Select * from firebase_content Order By rand() DESC Limit 0, 1; In general what such a statement does is [ except optimizations ] 1) retrieve all rand() values for all rows 2) sort them according to rand value 3) return the one with highest/lowest value. To improve the speed you could add a WHERE clause that limits the number of rows to, for example 1% of the table: SELECT * FROM firebase_content WHERE rand() 0.01 ORDER BY rand() LIMIT 1; ...so instead of returning and sorting 12000 rows you are doing it with about 120 rows which should be way faster. This is a trick I've learned while working with PostgreSQL -- and as I see it is universally useful. :) Regards, Dawid -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi Table Delete in 3.23.47
zzapper wrote: Hi, Final whinge: I wish the mysql website made it a little clearer which features are recent additions, I suggested color coding. Philippe Poelvoorde wrote: snip I reckon, it's a bit hidden in the middle : Starting with MySQL 4.0.4, you can also perform UPDATE operations that cover multiple tables: in : http://dev.mysql.com/doc/mysql/en/update.html I don't think it's hidden at all. Looking at the manual page detailing DELETE syntax, http://dev.mysql.com/doc/mysql/en/delete.html, the very first sentence describing multiple-table delete statements is, From MySQL 4.0, you can specify multiple tables in the DELETE statement to delete rows from one or more tables depending on a particular condition in multiple tables. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
Jigal Anyway, I gues it's just a question of following a standard and optimizing the engine according to that standard. Your proposal would permit dupe primary keys. It's a question of preventing them. PB - Jigal van Hemert wrote: From: "Mikhail Entaltsev" Mikhail, Before inserting MySQL will try to find a record with the same values. But since comparison with NULL value returns always FALSE MySQL will think that there is no such record in the table. After this point you will get 2 identical records in the table. MySQL as a whole seems a bit more flexible: a UNIQUE index allows multiple NULL values (which seems to follow the NULL handling you describe), but with BDB table only a single NULL value is allowed in a UNIQUE index (so MySQL seems to be able to find a NULL value in de db). Anyway, I gues it's just a question of following a standard and optimizing the engine according to that standard. Regards, Jigal. No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: group by day of week and group by hour in day
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am curious if there is a simple way to do this. I was just asked to give some data that requires me to group by day of week and also group by hour in day (two different pages). Thanx for any help. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCb6NkikQgpVn8xrARAhEpAJwK1Yp9gBn0bnfUJ07dbf2bVHpSiACfd8Wi HycjEYTbpk0NAPEtEgV5BpY= =3+gk -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Empty select on Windows also
To: mysql@lists.mysql.com Subject: [Empty select] Description: There are records in the table (select count(*) returns 1), but select * returns empty recordset How-To-Repeat: DROP TABLE IF EXISTS ugo; CREATE TABLE ugo ( AKCE_1 char(1) default '', AUT_KOD char(10) default '', BONUS_CR char(1) default '', BR_BODOVA decimal(6,2) default '0.00', BR_BODOVA2 decimal(6,2) default '0.00', BR_RATA int(11) default '0', BR_UG char(10) default '', BR_UG2 char(10) default '' ) ENGINE=MyISAM DEFAULT CHARSET=cp1250; insert into ugo ( BONUS_CR, BR_BODOVA, BR_BODOVA2) values ( 'a' , 0, 0); insert into ugo ( BONUS_CR, BR_BODOVA, BR_BODOVA2) values ( 'b' , 0, 0); select count(*) from ugo; #returns 2 select * from ugo; #returns nothing ! Submitter-Id: Originator:[EMAIL PROTECTED] Organization: MySQL support: none Synopsis: Severity: critical Priority: low Category: mysql Class: sw-bug Release: mysql-5.0.4-beta-standard Server version 5.0.4-beta-standard System: Windows 98 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
host info
Does anyone know a function that will return the hostname of the mysql server you are connecting to? Just as: mysql select database(); returns the database you're connected to, I need to display the host I'm connected to. Similar to the oracle statement: select host_name from v$instance; Thanks, Brian Stanton
Re: why NOT NULL in PRIMARY key??
From: Peter Brawley Peter, Anyway, I gues it's just a question of following a standard and optimizing the engine according to that standard. Your proposal would permit dupe primary keys. It's a question of preventing them. Sorry, but I disagree. If NULL handling is not done by the table engine but by the rest of MySQL then MySQL can compare two NULLs and can act in different ways depending on the situation (UNIQUE index in BDB can only have a single NULL entry, but MySQL allows multiple NULLs in other table types) So, if we would define that the key entry 0-NULL-Whatever equals 0-NULL-Whatever (which MySQL is capable of if you look at the definition of UNIQUE indexes with BDB tables) then allowing NULLs as part of a key entry would not permit duplicate entries. At least not more than allowing other values. Because there is a considerable performance difference between primary and secudary keys in InnoDB it would enable more flexible primary keys that can also be used for searching. It would mean breaking with the standard (PRIMARY = UNIQUE + NOT NULL), so it's not likely that it will ever be introduced ;-) Anyway, I need NULLs in the db (for various reasons which we haven't been able to solve in other ways) but I need fast searching on name/value. So I have to figure out a different method. Thanks for thinking about this! Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Maximize mysql ini for 4gig big query win 2003 box
Anybody have any suggested settings for our my.ini ? 4 gig box, running windows 2003 peak memory usage for mysql is 1.4 gig currently using myisam files, lots of little index querys, and some very large queries at night 615 tables, 88.3 gb of myd, 45.2 gb of myi, 33 of the tables/indexes break 1 gig, largest is 6 gig if you discount the piddly little tables, there are only 125 tables with size that matter, ie larger than 50mb Here's what we have now, sugestions welcome max_connections=100 query_cache_size=512M query_cache_type=1 table_cache=1024 tmp_table_size=400M thread_cache_size=8 myisam_max_sort_file_size=100G myisam_max_extra_sort_file_size=100G myisam_sort_buffer_size=369M key_buffer_size=318M read_buffer_size=64K read_rnd_buffer_size=256K sort_buffer_size=256K open-files-limit=500 myisam-recover=BACKUP,FORCE memlock -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi Table Delete in 3.23.47
On Wed, 27 Apr 2005 09:51:41 -0400, wrote: My subject is a bit of a misnomer in fact the Where refers to multiple tables but the delete is just from one table, nevertheless my query worked super-dandy in 4.1 (on my test rig) and failed on the live server 3.23.47 . that's why I couldn't work out which bit the doc refered to. BTW Whinge is a rather a mild term in the UK, -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: host info
Brian, In UNIX from mysql prompt do: mysql system /bin/hostname Mikhail -Original Message- From: Stanton, Brian [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 11:03 AM To: 'mysql@lists.mysql.com' Subject: host info Does anyone know a function that will return the hostname of the mysql server you are connecting to? Just as: mysql select database(); returns the database you're connected to, I need to display the host I'm connected to. Similar to the oracle statement: select host_name from v$instance; Thanks, Brian Stanton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
At 11:07 AM 4/27/05, Jigal van Hemert wrote: So, if we would define that the key entry 0-NULL-Whatever equals 0-NULL-Whatever (which MySQL is capable of if you look at the definition of UNIQUE indexes with BDB tables) then allowing NULLs as part of a key entry would not permit duplicate entries. At least not more than allowing other values. You cannot define that those keys are equal, because SQL standard states that 0-NULL-Whatever is *always* *not-equal* to 0-NULL-Whatever. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: host info
Unfortunately, that gives me the host of the machine I'm connecting from, not the server I'm connecting to. Thanks, Brian Stanton -Original Message- From: Berman, Mikhail [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 10:28 AM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: RE: host info Brian, In UNIX from mysql prompt do: mysql system /bin/hostname Mikhail -Original Message- From: Stanton, Brian [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 11:03 AM To: 'mysql@lists.mysql.com' Subject: host info Does anyone know a function that will return the hostname of the mysql server you are connecting to? Just as: mysql select database(); returns the database you're connected to, I need to display the host I'm connected to. Similar to the oracle statement: select host_name from v$instance; Thanks, Brian Stanton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: group by day of week and group by hour in day
James Black [EMAIL PROTECTED] wrote on 04/27/2005 10:36:20 AM: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am curious if there is a simple way to do this. I was just asked to give some data that requires me to group by day of week and also group by hour in day (two different pages). Thanx for any help. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCb6NkikQgpVn8xrARAhEpAJwK1Yp9gBn0bnfUJ07dbf2bVHpSiACfd8Wi HycjEYTbpk0NAPEtEgV5BpY= =3+gk -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] The simple answer (not good for frequent analysis) is to use the date and time functions to extract what you need from your data and group on those values (http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html): SELECT DAYOFWEEK(name_of_datetime_column), count(1) FROM tablename GROUP BY 1; (that is a mysql shorthand way of saying: group on the values in the first column) If you are going to need to do this time-based analysis frequently on larger datasets, it will save you lots of time to precompute certain values and add them as additional columns to the table. This is better and may be sufficient for your needs but this isn't the best-performing solution. For instance, if you wanted to evaluate web traffic by month, week, day of month, day of year, day of the week, and hour then adding those columns to your data table will seriously speed up several common types of queries: SELECT dayofyear, hour, sum(bytes_transferred) FROM data_table WHERE year = 2005 GROUP BY dayofyear, hour; This will give you the data you need to plot your hourly traffic (in bytes) for every day this year or you could say SELECT month, dayofmonth, hour, sum(bytes_transferred) FROM data_table WHERE year = 2005 GROUP BY month, dayofmonth, hour You get the same information but it's broken down by month, too. However, you still have to scan every row of the table (unless you allocate some serious index space) and it's faster but not the fastest way of making this kind of analysis. This general type of dimensional analysis is what the theories and practices of OLAP covers (online analytical processing). Generally, to get faster results for statistical research over values or ranges of values, you precompute some of your source data's statistics and associate those values with various dimensions (in this example we are dealing with sum(bytes_transferred) over periods of time). One way to do this is with a table that looks like: CREATE TABLE olapBytesTransferred ( year int, month int, week int, dayofyear int, dayofmonth int, dayofweek int, hour int, countBytes int, sumBytes float, avgBytes float, stdBytes float ) and populate it periodically (say once a day or once an hour) with the numbers for the raw data you have collected since the last time you checked. Then when it comes time to slice-and-dice your statistics you don't have to go through the hundreds of millions of rows of raw log data because you already have it broken down and added up by the hour. To collect statistics by the day, you only need to add up 24 hour's-worth of data for each day. This is much faster than trying to slog through and sort and analyze 10 events per day from the raw logs. If you need finer control, add more time division columns (our dimensions) to your table. Indexing these little monsters can be tricky because you need to balance the number of indexes (more index slow you down during INSERTs), index size (each index takes up space on disk and indexes that fit into memory are faster than those that require swap space to load), and performance desired (more indexes can mean faster SELECTs). You could even create breakdowns based on users per day (sticking with the web log example) or IP addresses per hour or you see where this is going. By extracting and extrapolating information from your raw logs and creating a table structure that is DESIGNED to support reports and statistical analysis, you can make that kind of information much easier to get to. Yes, it's much faster but it's not as simple. Shawn Green Database Administrator Unimin Corporation - Spruce Pine )
Re: Get a Random Row on a HUGE db
[EMAIL PROTECTED] writes: [...] So what I am trying is this. $last_row =SELECT from firebase_content LAST_INSERT_ID(); $last_row_query = $dbi-query($last_row); $last_row_result = $row-id; LAST_INSERT_ID() only works if you just inserted an element; it's maintained per-connection. See: http://dev.mysql.com/doc/mysql/en/getting-unique-id.html If your items are numbered sequentially, try just using: SELECT COUNT(*) FROM firebase_content; to get the count. That's very fast; it comes from the table summary information, IIRC. I use a similar solution for a similar problem, and have had great luck with it. To deal better with deleted items, you could periodically renumber your articles to avoid gaps in numbering. It would be great if MySQL had an optimization for this case. ScottG. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Mysql 5.0.4 still broken for my stored procedures
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 About 3 wks ago I had sent some files to the list regarding my stored procedures, and they are still broken in 5.0.4. I get an error: Internal error when parsing callable statement metadata and it happens at: com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes, line 6963 I am curious if anyone else is having a failure like this. Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCb7HRikQgpVn8xrARApS1AJ9owJYTZBCyQllrHGT96jgmAsRAFgCfYpUE vAaYxIMl7umxQ8VukTKV0j8= =e693 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
Jigal, I think MS-SQL and Oracle provide switches for treating NULLs as values, which is what your proposal amounts to. It seems to me that much of the performance advantage you are counting on from PKs would go away if PKs could have NULLs and if NULL were a value. IAC I hope MySQL doesn't go that way. PB - Jigal van Hemert wrote: From: "Peter Brawley" Peter, Anyway, I gues it's just a question of following a standard and optimizing the engine according to that standard. Your proposal would permit dupe primary keys. It's a question of preventing them. Sorry, but I disagree. If NULL handling is not done by the table engine but by the rest of MySQL then MySQL can compare two NULLs and can act in different ways depending on the situation (UNIQUE index in BDB can only have a single NULL entry, but MySQL allows multiple NULLs in other table types) So, if we would define that the key entry "0-NULL-Whatever" equals "0-NULL-Whatever" (which MySQL is capable of if you look at the definition of UNIQUE indexes with BDB tables) then allowing NULLs as part of a key entry would not permit duplicate entries. At least not more than allowing other values. Because there is a considerable performance difference between primary and secudary keys in InnoDB it would enable more flexible primary keys that can also be used for searching. It would mean breaking with the standard (PRIMARY = UNIQUE + NOT NULL), so it's not likely that it will ever be introduced ;-) Anyway, I need NULLs in the db (for various reasons which we haven't been able to solve in other ways) but I need fast searching on name/value. So I have to figure out a different method. Thanks for thinking about this! Regards, Jigal. No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.3 - Release Date: 4/25/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: java.lang.OutOfMemoryError
gerald_clark wrote: huanggaofeng wrote: I have too many databases,when i select it by client ,but it has error . the error message is : java.lang.OutOfMemoryError how i can to resolve it. You might try a java list. Gerald, Check out the java -X options. You can set the min and max amount of memory java can/will use. walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
time zones
How does one cope with time zones? For example, if I want to timestamp a record it will timestanp using the local time of my server. I thought that, e.g if my server was in New York and my customer_location was the UK, I can just add 5 hours to the time and it would be correct. While that might work for 51 weeks of the year, how do we cope with the one week where the clocks in the UK go forward one week earlier than they do in the USA, as just happened a few weeks ago? How can we reliably handle multiple time zones, and daylight savings time, with one server located in one location? Am I asking the impossible? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with character sets and Unicode in MySQL
I use the MyQSL Query Browser for a library database. I have come across a problem in MySQL: Using extended characters, as , is not a problem, as long as they are present in the standard 256 characters of a font. Things become more difficult when I need other East Europe characters. Could anyone suggest any information about this matter? How to use all the characters present in the Times New Roman font (which includes Hebrew, greek, Arab, and all East Europe characters) ? Is it possible to include them in a MySQL database ? In addition to this, I have another problem: I use the latin-1 code page for most of the text. I need to use some additional characters. (I use Internet Explorer as interface). To display the characters is not difficult: #1488; will display the hebew Aleph. No problem. The trouble is for writing a request and ordering the result list, (collation). e.g.: Standard collations of MySQL sends the special characters in the end of the list. Suppose a request which sends 6 charterhouses, it will be ordered like this : Portes Séville Transfiguration Valsainte Witham tipa and I would like : Portes Séville tipa Transfiguration Valsainte Witham The documentation at www.mysql.com indicates how things can be modified. But I've been told that the support for Unicode is not yet good in MySQL. Is it possible to find whether this is true? I would also need that the user can type : Stipa without the accent, and find the result. As I said, we have found the trick for the standard 256 ANSI characters. The question is more difficult if Unicode is needed. The chapter 11 explains this matter, but I am not sure that all this works well presently. I would just like to know if MySQL is really ready for unicode use. Well, I thank you in advance. Raúl Mauri __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
how to use row total in aggregate query ?
I have a query: I am trying to get a percentage of the total for each city SELECT userLog.city,userLog.region, COUNT(*), /* Count(userLog.id) is always 1 was hoping to get the total row returned */ COUNT(*)/COUNT(userLog.id), media.name,artist.name FROM userLog,media,artist WHERE userLog.media_id=media.id And userLog.city != '' AND media.artist_id = artist.id GROUP BY userLog.city I was hoping Count(userLog.id) would give me the total number of returned rows in the query...so I could return a percentage of the total what would be the correct syntax to include the row total ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Group By partial string
Trying to make some queries a little more efficient. I want to group HTTP referers together but not with the full URL. For example, just www.google.com instead of the full search string. I can't find a way to do this in MySQL. Is there a way to turn http://search.msn.com/results.aspx?q=things+stuffFORM=SSRE; into search.msn.com in a SELECT statement? Otherwise I have to loop through them all and parse them myself and run more queries then necessary. Eric Jensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB memory usage clarification
The following are from the InnoDB configuration page: # Set buffer pool size to 50-80% of your computer's memory, # but make sure on Linux x86 total memory usage is 2GB *Warning:* On 32-bit GNU/Linux x86, you must be careful not to set memory usage too high. | glibc| may allow the process heap to grow over thread stacks, which crashes your server. Can someone please explain what this means. We have a 32bit Linux x86 server with 16gigs of ram. Because it is 32bit and not 64bit we cant really make much use of all the ram. I am wondering which values I can safely increase without crashing the server. Here are some of the parameters we are using in our conf file: thread_concurrency = 16 table_cache = 512 innodb_buffer_pool_size = 1000M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 100M innodb_log_buffer_size = 8M From SHOW INNODB STATUS: BUFFER POOL AND MEMORY -- Total memory allocated 462835472; in additional pool allocated 3569664 Buffer pool size 24576 Free buffers 0 Database pages 23956 Modified db pages 11531 Free buffers is 0. Someone mentioned that because its a quad xeon each CPU would have 2gigs of ram to work with. Does this mean that I can set the innodb buffer pool much higher ? any feedback is welcome. thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: host info
Brian, Have you initiated mysql command with -h host-name option or just mysql with other options but -h? Mikhail Berman -Original Message- From: Stanton, Brian [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 11:33 AM To: 'mysql@lists.mysql.com' Subject: RE: host info Unfortunately, that gives me the host of the machine I'm connecting from, not the server I'm connecting to. Thanks, Brian Stanton -Original Message- From: Berman, Mikhail [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 10:28 AM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: RE: host info Brian, In UNIX from mysql prompt do: mysql system /bin/hostname Mikhail -Original Message- From: Stanton, Brian [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 11:03 AM To: 'mysql@lists.mysql.com' Subject: host info Does anyone know a function that will return the hostname of the mysql server you are connecting to? Just as: mysql select database(); returns the database you're connected to, I need to display the host I'm connected to. Similar to the oracle statement: select host_name from v$instance; Thanks, Brian Stanton -- 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]
Avoiding deadlock: beyond the basics
I've been getting the following error in my Python program which accesses InnoDB tables: OperationalError: 1213 Deadlock found when trying to get lock; try restarting transaction I've already tried all the basic steps that the documentation seems to recommend: - Set my TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED for session and globally - used UPDATE LOW PRIORITY to encourage waiting for locks to become clear - COMMIT immediately before the UPDATE to drop all locks What's puzzling is that this deadlock requires *three* active clients to manifest. Two of these clients can hit the server without problems, but if that number is increased only two active clients remain deadlock-free. Any hints or suggestions for further reading would be appreciated. Here's what I get from SHOW INNODB STATUS after a deadlock. What's especially puzzling is that what I'm seeing seems to violate the very definition of deadlock. Transaction 1 is holding no locks (since it just committed) and is attempting to acquire a lock on only one row of one table for an update. You need at least two locks to create circularity. -- = 050427 12:31:50 INNODB MONITOR OUTPUT = Per second averages calculated from the last 9 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 96, signal count 93 Mutex spin waits 657, rounds 8130, OS waits 31 RW-shared spins 83, OS waits 37; RW-excl spins 40, OS waits 28 LATEST DETECTED DEADLOCK 050427 12:31:37 *** (1) TRANSACTION: TRANSACTION 0 23662, ACTIVE 1 sec, OS thread id 25389056 updating or deleting mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1024, undo log entries 1 MySQL thread id 326, query id 13839 localhost sherman Updating UPDATE LOW_PRIORITY frames SET frame_state=run WHERE frame_jid=9 AND frame_lid=0 AND frame_frame=2 AND frame_tile=6 AND frame_state=ready *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 393 n bits 648 index `frame_state_key` of table `sherman/frames` trx id 0 23662 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 577 PHYSICAL RECORD: n_fields 5; 1-byte offs TRUE; info bits 32 0: len 1; hex 04; asc ;; 1: len 4; hex 002f; asc/;; 2: len 1; hex 00; asc ;; 3: len 2; hex 0010; asc ;; 4: len 1; hex 00; asc ;; *** (2) TRANSACTION: TRANSACTION 0 23665, ACTIVE 0 sec, OS thread id 25530880 fetching rows, thread declared inside InnoDB 283 mysql tables in use 1, locked 1 17 lock struct(s), heap size 2496 MySQL thread id 328, query id 13853 localhost sherman Copying to tmp table CREATE TEMPORARY TABLE t_active_layers (PRIMARY KEY(tal_jid,tal_lid)) SELECT DISTINCT frame_jid as tal_jid, frame_lid as tal_lid FROM frames WHERE frame_state=ready *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 393 n bits 648 index `frame_state_key` of table `sherman/frames` trx id 0 23665 lock mode S Record lock, heap no 577 PHYSICAL RECORD: n_fields 5; 1-byte offs TRUE; info bits 32 0: len 1; hex 04; asc ;; 1: len 4; hex 002f; asc/;; 2: len 1; hex 00; asc ;; 3: len 2; hex 0010; asc ;; 4: len 1; hex 00; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 1717 n bits 864 index `frame_state_key` of table `sherman/frames` trx id 0 23665 lock mode S waiting Record lock, heap no 78 PHYSICAL RECORD: n_fields 5; 1-byte offs TRUE; info bits 32 0: len 1; hex 03; asc ;; 1: len 4; hex 0009; asc ;; 2: len 1; hex 00; asc ;; 3: len 2; hex 0002; asc ;; 4: len 1; hex 06; asc ;; *** WE ROLL BACK TRANSACTION (2) TRANSACTIONS Trx id counter 0 23695 Purge done for trx's n:o 0 23695 undo n:o 0 0 History list length 31 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 25577472 MySQL thread id 330, query id 13958 localhost soren SHOW INNODB STATUS ---TRANSACTION 0 23661, not started, OS thread id 25566208 MySQL thread id 329, query id 13852 localhost sherman ---TRANSACTION 0 23665, not started, OS thread id 25530880 MySQL thread id 328, query id 13853 localhost sherman FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 45 OS file reads, 2582 OS file writes, 1804 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 2.22 writes/s, 0.67 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX
Re: MySQL Group By partial string
On Apr 27, 2005, at 1:17 PM, Eric Jensen wrote: Trying to make some queries a little more efficient. I want to group HTTP referers together but not with the full URL. For example, just www.google.com instead of the full search string. I can't find a way to do this in MySQL. Is there a way to turn http://search.msn.com/results.aspx?q=things+stuffFORM=SSRE; into search.msn.com in a SELECT statement? Otherwise I have to loop through them all and parse them myself and run more queries then necessary. It seems like it would be far more efficient to index this during insertion. Then you don't have to loop through anything. SELECT * FROM referrers,domains WHERE referrer.domain_id=domains.id AND domain.name = www.google.com; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: host info
Using 4.0.18... connected from one unix box to another using mysql -h server -u user -p database Thanks, Brian Stanton -Original Message- From: Berman, Mikhail [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 12:30 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: RE: host info Brian, Have you initiated mysql command with -h host-name option or just mysql with other options but -h? Mikhail Berman -Original Message- From: Stanton, Brian [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 11:33 AM To: 'mysql@lists.mysql.com' Subject: RE: host info Unfortunately, that gives me the host of the machine I'm connecting from, not the server I'm connecting to. Thanks, Brian Stanton -Original Message- From: Berman, Mikhail [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 10:28 AM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: RE: host info Brian, In UNIX from mysql prompt do: mysql system /bin/hostname Mikhail -Original Message- From: Stanton, Brian [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 11:03 AM To: 'mysql@lists.mysql.com' Subject: host info Does anyone know a function that will return the hostname of the mysql server you are connecting to? Just as: mysql select database(); returns the database you're connected to, I need to display the host I'm connected to. Similar to the oracle statement: select host_name from v$instance; Thanks, Brian Stanton -- 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: getting hours from two datetime columns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Is there a way to get all of the hours between two datetime columns? For example, if there is an entry that starts at 2005-4-1 3:00:00 and the end is 2005-4-1 11:00:00 I would like to know that this enry was used in hours 3 - 11, or for 8 hours. I hope this makes sense. I am thinking I am going to be doing this in my application. Thanx for any suggestions. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCb9puikQgpVn8xrARAgTBAJ0eefvxMCqjG5UUWa7vlaFYR5LSMQCfSLiF niYw22pzYxgVTy6bybgJ80Q= =Ysmy -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB memory usage clarification
Mayuran, It depends on a bunch of things. What else is running on this server? Does the distro you use have the ability to take advantage of all 16 gig (ie if you have apache running, will it be stuck in the same 4 gig as MySQL, or can it use the memory above the 4 gig limit). How big is your database? The innodb_buffer_pool_size holds data from your database in memory; if you run a query, and the data is in the buffer_pool, the query returns very quickly. If it is not in the buffer_pool, then MySQL/InnoDB has to go to disk to get the data. If your database is 100 megabytes, there is not much sense in setting a buffer_pool of 1 gigabyte. If your database is 10 gigabytes, then you will probably encounter some slowness as the disk is being accessed. How many users will connect? Each user requires some memory for the connection, for sorting, etc, etc. The following equation gives you an idea of how much memory MySQL will consume, based on various parameters: innodb_buffer_pool_size + key_buffer + max_connections * (sort_buffer + record_buffer) + max_connections * 2 MB If you try to grab too much, mysql will crash. Check your distribution to figure out what the max process size is. David Mayuran Yogarajah wrote: The following are from the InnoDB configuration page: # Set buffer pool size to 50-80% of your computer's memory, # but make sure on Linux x86 total memory usage is 2GB *Warning:* On 32-bit GNU/Linux x86, you must be careful not to set memory usage too high. | glibc| may allow the process heap to grow over thread stacks, which crashes your server. Can someone please explain what this means. We have a 32bit Linux x86 server with 16gigs of ram. Because it is 32bit and not 64bit we cant really make much use of all the ram. I am wondering which values I can safely increase without crashing the server. Here are some of the parameters we are using in our conf file: thread_concurrency = 16 table_cache = 512 innodb_buffer_pool_size = 1000M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 100M innodb_log_buffer_size = 8M From SHOW INNODB STATUS: BUFFER POOL AND MEMORY -- Total memory allocated 462835472; in additional pool allocated 3569664 Buffer pool size 24576 Free buffers 0 Database pages 23956 Modified db pages 11531 Free buffers is 0. Someone mentioned that because its a quad xeon each CPU would have 2gigs of ram to work with. Does this mean that I can set the innodb buffer pool much higher ? any feedback is welcome. thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: host info
Brian, Would it be feasible for you to work around this problem by: 1. Executing from UNIX prompt of your first server - ssh second_server 2. Executing mysql -u user -p database 3. Executing mysql system /bin/hostname Regards, Mikhail Berman -Original Message- From: Stanton, Brian [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 2:10 PM To: 'mysql@lists.mysql.com' Subject: RE: host info Using 4.0.18... connected from one unix box to another using mysql -h server -u user -p database Thanks, Brian Stanton -Original Message- From: Berman, Mikhail [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 12:30 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: RE: host info Brian, Have you initiated mysql command with -h host-name option or just mysql with other options but -h? Mikhail Berman -Original Message- From: Stanton, Brian [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 11:33 AM To: 'mysql@lists.mysql.com' Subject: RE: host info Unfortunately, that gives me the host of the machine I'm connecting from, not the server I'm connecting to. Thanks, Brian Stanton -Original Message- From: Berman, Mikhail [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 10:28 AM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: RE: host info Brian, In UNIX from mysql prompt do: mysql system /bin/hostname Mikhail -Original Message- From: Stanton, Brian [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 11:03 AM To: 'mysql@lists.mysql.com' Subject: host info Does anyone know a function that will return the hostname of the mysql server you are connecting to? Just as: mysql select database(); returns the database you're connected to, I need to display the host I'm connected to. Similar to the oracle statement: select host_name from v$instance; Thanks, Brian Stanton -- 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: java.lang.OutOfMemoryError
I had the same problem. I changed the memory settings for java: catalina.sh JAVA_OPTS='-Xms1024m -Xmx1024m' But still I had the problem By rebooting tomcat every week-end I do not have any more problem. Of course you might not be able to do that. Johanne -Message d'origine- De : huanggaofeng [mailto:[EMAIL PROTECTED] Envoyé : 27 avril 2005 08:50 À : mysql@lists.mysql.com Objet : java.lang.OutOfMemoryError I have too many databases,when i select it by client ,but it has error . the error message is : java.lang.OutOfMemoryError how i can to resolve it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: host info
I don't know if it's possible in MySQL. That said, in the mysql client, you can type '\s' for 'status'. Look for 'Current user' in the output. Eamon Daly - Original Message - From: Stanton, Brian [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, April 27, 2005 10:03 AM Subject: host info Does anyone know a function that will return the hostname of the mysql server you are connecting to? Just as: mysql select database(); returns the database you're connected to, I need to display the host I'm connected to. Similar to the oracle statement: select host_name from v$instance; Thanks, Brian Stanton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: host info
The 'Connection' output from the 'status' command is actually what I was looking for. However, most likely it will be a jdbc connection to mysql, not the mysql client, so I'll have to see if it works that way or not. Thanks, Brian Stanton -Original Message- From: Eamon Daly [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 2:04 PM To: Stanton, Brian; mysql@lists.mysql.com Subject: Re: host info I don't know if it's possible in MySQL. That said, in the mysql client, you can type '\s' for 'status'. Look for 'Current user' in the output. Eamon Daly - Original Message - From: Stanton, Brian [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, April 27, 2005 10:03 AM Subject: host info Does anyone know a function that will return the hostname of the mysql server you are connecting to? Just as: mysql select database(); returns the database you're connected to, I need to display the host I'm connected to. Similar to the oracle statement: select host_name from v$instance; Thanks, Brian Stanton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
Hi, let me fall in here. Jigal van Hemert wrote: From: Peter Brawley Peter, [[...]] Your proposal would permit dupe primary keys. It's a question of preventing them. Sorry, but I disagree. If NULL handling is not done by the table engine but by the rest of MySQL then MySQL can compare two NULLs and can act in different ways depending on the situation (UNIQUE index in BDB can only have a single NULL entry, but MySQL allows multiple NULLs in other table types) The handling of values (equality, ordering, ...) should be done at the lowest possible level, for performance reasons. If a system acts in different ways depending on the situation, it lacks systematic properties and is difficult to use and to explain. (Correct, often the situation is important, but if you define the equality of values as depending on the key property of the column then you open a can of worms!) BDB is a special case which you should not take as a model IMO. So, if we would define that the key entry 0-NULL-Whatever equals 0-NULL-Whatever [[...]] Your best way of reaching this is to use some other valuefor the purpose you were going to use NULL for. Let me add an aspect which I did not find mentioned yet: The SQL syntax differs for NULL and non-NULL values! As long as your key columns have the NOT NULL property, you can alwas say WHERE keycol = value. When you qualify by a NULL value, you need to say col IS NULL. This will add complexity to your application(s). [[...]] It would mean breaking with the standard (PRIMARY = UNIQUE + NOT NULL), so it's not likely that it will ever be introduced ;-) I sure hope it will not be, for various reasons. Anyway, I need NULLs in the db (for various reasons which we haven't been able to solve in other ways) but I need fast searching on name/value. So I have to figure out a different method. I hope you get it solved! Regards, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
connecting with PHP from remote server - security breach?
Hello, I'm wondering whether there is a way to hack into my database if I'm connecting with PHP from remote web server to my MySQL server with unencrypted connection? I'm using phpBB forums (residing on one server and using the database on other server) which do not suport SSH connections, so I'd like to know if I'm opening a security hole in my system this way? -- Znam, kaljavo ordenje na mom kaputu je... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: getting hours from two datetime columns
James, Is there a way to get all of the hours between two datetime columns? For example, if there is an entry that starts at 2005-4-1 3:00:00 and the end is 2005-4-1 11:00:00 I would like to know that this enry was used in hours 3 - 11, or for 8 hours. TIMEDIFF( '2005-4-1 11:00:00', '2005-4-1 3:00:00' ) PB -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.3 - Release Date: 4/25/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query question
I have a table that the important parts look something like: keynum int, entryDate datetime, amount varchar(10) What I want to do is a query that gets me every day of the year and just has null values for the days that don't have anything in the amount column. Is something like that possible with sql? In fact, what I would really like is: select month(entryDate) as monthPart, day(entryDate) as dayPart, amount from raindata order by dayPart, monthPart just with the whole year filled in. it will make my later code simplier if I can not have to test for values as much. --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: host info
Hi all, Mysql server knows the OS server as localhost. the hostname you see in status is the OS server from which you connect (the client one), since it's defined in the grant. The only method i can see is : ls datadir_path/*.pid its hostname.pid You can do it also with *.err Mathias Selon Stanton, Brian [EMAIL PROTECTED]: The 'Connection' output from the 'status' command is actually what I was looking for. However, most likely it will be a jdbc connection to mysql, not the mysql client, so I'll have to see if it works that way or not. Thanks, Brian Stanton -Original Message- From: Eamon Daly [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 2:04 PM To: Stanton, Brian; mysql@lists.mysql.com Subject: Re: host info I don't know if it's possible in MySQL. That said, in the mysql client, you can type '\s' for 'status'. Look for 'Current user' in the output. Eamon Daly - Original Message - From: Stanton, Brian [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, April 27, 2005 10:03 AM Subject: host info Does anyone know a function that will return the hostname of the mysql server you are connecting to? Just as: mysql select database(); returns the database you're connected to, I need to display the host I'm connected to. Similar to the oracle statement: select host_name from v$instance; Thanks, Brian Stanton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query question
Hi, If i understand : select month(entryDate) as monthPart, if (amount is nul,'',day(entryDate) ) as dayPart, amount from raindata order by dayPart, monthPart Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: mercredi 27 avril 2005 22:24 To: mysql@lists.mysql.com Subject: query question I have a table that the important parts look something like: keynum int, entryDate datetime, amount varchar(10) What I want to do is a query that gets me every day of the year and just has null values for the days that don't have anything in the amount column. Is something like that possible with sql? In fact, what I would really like is: select month(entryDate) as monthPart, day(entryDate) as dayPart, amount from raindata order by dayPart, monthPart just with the whole year filled in. it will make my later code simplier if I can not have to test for values as much. --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query question
JA, To have a SELECT statement generate a row for every day in the year, either your raindata table needs a row for every day in the year, or you need another table which has a row for every day of the year. Supposing you have such a table, call it 'calendar' with a date column named 'yearday', then you could retrieve daily rainfall including NULLs with SELECT calendar.yearday, rainfall.amount FROM calendar LEFT JOIN rainfall ON calendar.yearday = raindata.entrydate ORDER BY calendar.yearday; or if there can be multiple raindata rows for a date then SELECT calendar.yearday, SUM( rainfall.amount ) FROM calendar LEFT JOIN rainfall ON calendar.yearday = raindata.entrydate GROUP BY calendar.yearday; but your rainfall column ought to be numeric. Peter Brawley http://www.artfulsoftware.com - [EMAIL PROTECTED] wrote: I have a table that the important parts look something like: keynum int, entryDate datetime, amount varchar(10) What I want to do is a query that gets me every day of the year and just has null values for the days that don't have anything in the amount column. Is something like that possible with sql? In fact, what I would really like is: select month(entryDate) as monthPart, day(entryDate) as dayPart, amount from raindata order by dayPart, monthPart just with the whole year filled in. it will make my later code simplier if I can not have to test for values as much. --ja -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.3 - Release Date: 4/25/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: host info
Along those lines, you could use show variables like 'pid_file' if the user needing to know the hostname has privileges for this. Thanks for the idea! Thanks, Brian Stanton -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 3:42 PM To: Stanton, Brian Cc: 'mysql@lists.mysql.com' Subject: RE: host info Hi all, Mysql server knows the OS server as localhost. the hostname you see in status is the OS server from which you connect (the client one), since it's defined in the grant. The only method i can see is : ls datadir_path/*.pid its hostname.pid You can do it also with *.err Mathias Selon Stanton, Brian [EMAIL PROTECTED]: The 'Connection' output from the 'status' command is actually what I was looking for. However, most likely it will be a jdbc connection to mysql, not the mysql client, so I'll have to see if it works that way or not. Thanks, Brian Stanton -Original Message- From: Eamon Daly [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 2:04 PM To: Stanton, Brian; mysql@lists.mysql.com Subject: Re: host info I don't know if it's possible in MySQL. That said, in the mysql client, you can type '\s' for 'status'. Look for 'Current user' in the output. Eamon Daly - Original Message - From: Stanton, Brian [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, April 27, 2005 10:03 AM Subject: host info Does anyone know a function that will return the hostname of the mysql server you are connecting to? Just as: mysql select database(); returns the database you're connected to, I need to display the host I'm connected to. Similar to the oracle statement: select host_name from v$instance; Thanks, Brian Stanton -- 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: host info
That's it:o) Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Stanton, Brian [mailto:[EMAIL PROTECTED] Sent: mercredi 27 avril 2005 23:12 To: 'mysql@lists.mysql.com' Subject: RE: host info Along those lines, you could use show variables like 'pid_file' if the user needing to know the hostname has privileges for this. Thanks for the idea! Thanks, Brian Stanton -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 3:42 PM To: Stanton, Brian Cc: 'mysql@lists.mysql.com' Subject: RE: host info Hi all, Mysql server knows the OS server as localhost. the hostname you see in status is the OS server from which you connect (the client one), since it's defined in the grant. The only method i can see is : ls datadir_path/*.pid its hostname.pid You can do it also with *.err Mathias Selon Stanton, Brian [EMAIL PROTECTED]: The 'Connection' output from the 'status' command is actually what I was looking for. However, most likely it will be a jdbc connection to mysql, not the mysql client, so I'll have to see if it works that way or not. Thanks, Brian Stanton -Original Message- From: Eamon Daly [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 2:04 PM To: Stanton, Brian; mysql@lists.mysql.com Subject: Re: host info I don't know if it's possible in MySQL. That said, in the mysql client, you can type '\s' for 'status'. Look for 'Current user' in the output. Eamon Daly - Original Message - From: Stanton, Brian [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, April 27, 2005 10:03 AM Subject: host info Does anyone know a function that will return the hostname of the mysql server you are connecting to? Just as: mysql select database(); returns the database you're connected to, I need to display the host I'm connected to. Similar to the oracle statement: select host_name from v$instance; Thanks, Brian Stanton -- 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]
purge log fails
Hello, the current 4.1.11 implementation of PURGE LOGS doesn't seem to work any more. I created a script running once each day doing the following statement: purge master logs before (select adddate(current_timestamp(), interval -4 day)) That way, all logs prior to today - 4 days will become deleted. This worked fine with 4.1.9. But since I upgraded from 4.1.9 to 4.1.11 I alway get this error: DBD::mysql::db do failed: Lost connection to MySQL server during query at /vrmd/admin/cron/apps/purge_logs/purge_logs.pl line 15. This also happens when I'm doing the query manually logged in at the mysql-prompt. For testing, I simplified to statement e.g. to purge master logs before '2005-04-24'; Now I didn't lost the connection, but on the other hand, the purging wasn't done. All logfiles remained at their position. To clear a bit of space, I was running reset master. But so all logfiles have been removed, but I want to keep the logfiles for at least the last 4 days to be able to track statements in case of errors. Any idea what is going wrong? As it worked in 4.1.9, I guess there's a problem in the mysql-server? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Resetting lsn/trx for an .ibd file
It's pretty clear that although you cannot currently move .ibd files between MySQL installations, it's technically possible - it would simply be a matter of resetting the lsn/trx id's of a clean .ibd file. http://lists.mysql.com/mysql/159206 http://dev.mysql.com/doc/mysql/en/multiple-tablespaces.html We have massive InnoDB data structures where building indexes can take days; it would be very convenient if we were able to cleanly move .ibd files to our slaves, and I'm sure I'm not the only one interested in this. Has there been any work done to solve this issue? -- Sam Sgro [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connecting with PHP from remote server - security breach?
Nikola Skoric wrote: Hello, I'm wondering whether there is a way to hack into my database if I'm connecting with PHP from remote web server to my MySQL server with unencrypted connection? I'm using phpBB forums (residing on one server and using the database on other server) which do not suport SSH connections, so I'd like to know if I'm opening a security hole in my system this way? Well, this is how I understand (and I don't profess to having a thorough understanding). There are two aspects of security in a MySQL server, Host and Password, echnically Username can't count as a third I suppose. An unencrypted connection leaves you open to packet sniffing, the connection info and data being passed through in an easy to read manner (relatively).. Through that someone could learn your Username, and hashed password. Anyone with that information and access to a host that the user is allowed to connect from will be able to login as that user. So, in this instance, you would need a very restrictive Host requirement (ideally one server, that no one else has access to). Whether this is good enough is really up to the admin. This is really a case in possibility, not really something you'd have to deal with every day. As long as it's not a shared server you're connecting from and MySQL is set up to only allow connections from that server, it seems like it should be good enough. Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_get_client_version() in embedded library
the manual states at http://dev.mysql.com/doc/mysql/en/mysql-get-client-version.html that this function has been introduced in 4.0.16. But it seems that the embedded library does not yet have it as of 4.0.24. 4.1.11 has it. Does anybody know when exactly it has been added to the embedded library? [EMAIL PROTECTED]:~/Desktop$ grep -w mysql_get_client_version */*/*c mysql-dfsg-4.0.24/libmysql/libmysql.c:ulong STDCALL mysql_get_client_version(void) mysql-dfsg-4.0.24/libmysql_r/libmysql.c:ulong STDCALL mysql_get_client_version(void) mysql-dfsg-4.1-4.1.11/libmysqld/libmysql.c:ulong STDCALL mysql_get_client_version(void) mysql-dfsg-4.1-4.1.11/libmysql/libmysql.c:ulong STDCALL mysql_get_client_version(void) mysql-dfsg-4.1-4.1.11/libmysql_r/libmysql.c:ulong STDCALL mysql_get_client_version(void) -- Wolfgang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
