Re: Query Cache

Chris, Thanks for clearing that up. You are right, I don't want the general log or the mysql shell history. I'm not happy that I can't get at the Query Cache. I am sure there are tools which MySql developers must use to test the cache. I'll have to look at the source code directories...

SUM() of 1 and NULL is 1 ?

Hi List, I need to SUM() on months from a table like: CREATE TABLE `data` ( `Jan` float default NULL, ... ) ENGINE=MyISAM; # V 5.0.15 Months may have NULL values, like: INSERT INTO data (Jan) VALUES (1), (NULL); However, when I use SELECT SUM(Jan) AS Jan, the returned value is 1 in stead of NULL

Slightly off topic maybe but bear with me...

Has anyone here seen any software (preferably PHP and Open Source) that connects to MySQL and allows people to build/design/modify databases and tables? I'm not really looking for something like DBDesigner or phpMyAdmin although their close/similar to want I want, I'd like something more or

RE: SUM() of 1 and NULL is 1 ?

IF(SUM(IF(Jan IS NULL, 0, Jan))>0, SUM(IF(Jan IS NULL, 0, Jan)), NULL) This was just a guess :) -Original Message- From: C.R.Vegelin [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 06, 2006 11:58 AM To: mysql@lists.mysql.com Subject: SUM() of 1 and NULL is 1 ? Hi List, I need to

Re: SUM() of 1 and NULL is 1 ?

Hi Vegelin, In MySQL, SUM(1) = 1 SUM(NULL) = NULL In your case, you are inserting two values 1, NULL to Jan column, So now Jan column contains 1 and NULL, If you SUM the Jan then the result will be 1 not NULL. Because SUM() ignores NULL values. As far as i know, we dont have built-in functi

Update query help

Dear Friends, I have two tables: T1, T2. T1 has 3 columns: playerid, gameid, score T2 has 2 columns: playerid, totalscore. I wish to update table T2 such that sum of T1.score of each player, gets updated in T2.totalscore. It may be something like this: update T2, T1 set T2.totalscore = sum(

Re: SUM() of 1 and NULL is 1 ?

Hi Vegelin, This will do.. select IF(SUM(IF(Jan IS NULL, 0, Jan))>0,NULL,SUM(IF(Jan IS NULL, 0, Jan)))as jan from data; Thanks, ViSolve DB Team - Original Message - From: "ViSolve DB Team" <[EMAIL PROTECTED]> To: "C.R.Vegelin" <[EMAIL PROTECTED]>; Cc: <[EMAIL PROTECTED]> Sent: Wed

Re: Update query help

Hi, Try this.. UPDATE table2 inner join table1 on table2.playedid=table1.playerid SET table2.totalscore=sum(table1.score) Just a guess... Thanks, ViSolve DB Team - Original Message - From: "Ravi Kumar." <[EMAIL PROTECTED]> To: Sent: Wednesday, December 06, 2006 4:11 PM Subject: Upd

Re: SUM() of 1 and NULL is 1 ?

Thanks Visolve, Peter, This is a serious matter, because: - within a row: 1 + NULL = NULL - across rows with SUM(): 1 + NULL = 1 I know the manual says that group functions ignore NULL values (12.10.1), but it also says: Conceptually, NULL means "a missing unknown value" (3.3.4.6). IMHO a NULL w

deadlocks on concurrent INSERTS

Greetings, I've got 6 Java client applications (running on 6 different PC's, including one of them on the database server) reading data from a flat file and inserting data into a table on my database: mysqld Ver 5.1.12-beta-log for unknown-linux-gnu on x86_64 (MySQL Community Server (GPL))

Re: SUM() of 1 and NULL is 1 ?

Aggregates ignore NULL as per SQL standard, so this behaviour is valid. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.dat

Re: SUM() of 1 and NULL is 1 ?

Cor, This is a serious matter, because: - within a row: 1 + NULL = NULL - across rows with SUM(): 1 + NULL = 1 I know the manual says that group functions ignore NULL values (12.10.1), but it also says: Conceptually, NULL means "a missing unknown value" (3.3.4.6). IMHO a NULL with any value s

Re: MySQL Performance Degrades Significantly Over Time

Hi Daniel, We were using a software RAID-5 on top of hardware RAID-5 across 3 4-disk volume groups. (1 LUN from each array volume group built the software RAID-5). So we were able to lose 3 disks in a worst case scenario. It seems to me that neither RAID-1 or RAID-5 can lose more than one disk w

How many records in table?

What's a quick query to determine how many records a given table contains? I don't think that a SELECT query is appropriate, as I don't intend on doing anything with the data selected. Note that I'm interfacing with MySQL via php, if that matters. Thanks. Dotan Cohen http://what-is-what.com/what

RE: How many records in table?

Use SELECT in with count(*) SELECT count(*) from YOUR_TABLE Mikhail Berman -Original Message- From: Dotan Cohen [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 06, 2006 3:37 PM To: MySQL General Subject: How many records in table? What's a quick query to determine how many record

Re: How many records in table?

On 06/12/06, Mikhail Berman <[EMAIL PROTECTED]> wrote: Use SELECT in with count(*) SELECT count(*) from YOUR_TABLE Mikhail Berman Thanks, Mikhail. Will do. Dotan Cohen http://what-is-what.com/what_is/xss.html http://english-lyrics.com/ -- MySQL General Mailing List For list archives: htt

Re: deadlocks on concurrent INSERTS

What does a `show innodb status show`? On 07/12/2006, at 2:44 AM, James Neff wrote: Greetings, I've got 6 Java client applications (running on 6 different PC's, including one of them on the database server) reading data from a flat file and inserting data into a table on my database: m

Re: How many records in table?

On 12/6/06, Mikhail Berman <[EMAIL PROTECTED]> wrote: -Original Message- > From: Dotan Cohen [mailto:[EMAIL PROTECTED] > Sent: Wednesday, December 06, 2006 3:37 PM > To: MySQL General > Subject: How many records in table? > > What's a quick query to determine how many records a given tab

alter table ... import tablespace NFG?

I want to move 3 100GB .ibd files into a new DB. I followed the instructions here: http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html But it doesn't work: mysql> alter table reports discard tablespace; Query OK, 0 rows affected (0.04 sec) mysql> alter table reports import tablesp

Create stored procedures with Query Browser

Can someone tell me if it is possible to create stored procedures with Query Browser and if it is can you provide an example the works? Thanks

database schema migration

hey all, I have two tables like that: artists(id,name) albums(id,artist_id,album_name) and I need to transfer the data of this database to three tables that look like this: artists(id,name) albums(id,name) artists_albums(album_id,artist_id) any idea what's the fastest query to do this? thanx i

Re: database schema migration

Patrick Aljord wrote: hey all, I have two tables like that: artists(id,name) albums(id,artist_id,album_name) and I need to transfer the data of this database to three tables that look like this: artists(id,name) albums(id,name) artists_albums(album_id,artist_id) any idea what's the fastest que

RE: set global does not work

  Hi: I open a connection via mysql mysql>show global variables like 'wait_timeout' --> 28800 mysql> set global wait_timeout = 1000; mysql>exit; reopen mysql mysql> show session variables like 'wait_timeout'; --> 28800 I expect session value to be 1000 according the doc, as it should be given the

Re: SUM() of 1 and NULL is 1 ?

C.R.Vegelin wrote: Hi List, I need to SUM() on months from a table like: CREATE TABLE `data` ( `Jan` float default NULL, ... ) ENGINE=MyISAM; # V 5.0.15 Months may have NULL values, like: INSERT INTO data (Jan) VALUES (1), (NULL); However, when I use SELECT SUM(Jan) AS Jan, the returned value

Re: Update query help

ViSolve DB Team wrote: Hi, Try this.. UPDATE table2 inner join table1 on table2.playedid=table1.playerid SET table2.totalscore=sum(table1.score) Just a guess... Thanks, ViSolve DB Team - Original Message - From: "Ravi Kumar." <[EMAIL PROTECTED]> To: Sent: Wednesday, December 06, 200

Re: set global does not work

Hi Try mysql >set @@session.wait_timeout=1000; mysql> show variables; According to the VARIABLE "wait_timeout" [default: 28800 seconds] a running MySQL daemon clears up idle connections if their "idle period" > "wait_timeout".so the sleeping threads will be automatically cleared if the

Re: Update query help

Ravi Kumar. wrote: Dear Friends, I have two tables: T1, T2. T1 has 3 columns: playerid, gameid, score T2 has 2 columns: playerid, totalscore. I wish to update table T2 such that sum of T1.score of each player, gets updated in T2.totalscore. It may be something like this: update T2, T1 s