Re: Strange results from a query

[Michael Stassen]

In both mysql 4.0.20 and 4.1.4a-gamma, I get
mysql> CREATE TABLE master (col1 INT NOT NULL);
Query OK, 0 rows affected (0.09 sec)
mysql> CREATE TABLE sub (col1 INT NOT NULL, a CHAR(1) NULL, b CHAR(1) NULL, 
c SMALLINT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO MASTER (col1) VALUES (1), (2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> INSERT INTO sub (col1, a, b, c) VALUES
-> (1, 'a', null, '2'),
-> (1, 'a', null, '2'),
-> (2, null, 'b', '3'),
-> (2, null, 'b', '3');
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> SELECT m.col1,
->  SUM(s1.c) AS 'A-count',
->  SUM(s2.c) AS 'B-count'
->  FROM master m
->  LEFT JOIN sub s1 ON (m.col1 = s1.col1 AND s1.a is not null)
->  LEFT JOIN sub s2 ON (m.col1 = s2.col1 AND s2.b is not null)
->  GROUP BY m.col1;
+--+-+-+
| col1 | A-count | B-count |
+--+-+-+
|1 |   4 |NULL |
|2 |NULL |   6 |
+--+-+-+
2 rows in set (0.07 sec)
which is the correct result, just as Stephen did with 3.23.58.  If I then
  INSERT INTO MASTER (col1) VALUES (1), (2);
a second time, I then get
+--+-+-+
| col1 | A-count | B-count |
+--+-+-+
|1 |   8 |NULL |
|2 |NULL |  12 |
+--+-+-+
2 rows in set (0.07 sec)
I think you should double-check your data.  In your original email, you 
listed two inserts to table master, then said you'd "removed some data to 
make it clearer" in your follow-up.  I suspect your master table has 2 
copies of each row.

Michael
David Griffiths wrote:
Thanks, Stephen - that's interesting to know.
David

Stephen E. Bacher wrote:
FYI, I ran the same scenario under MySQL 3.23.58 and it produced the
results you wanted/expected:
+--+-+-+
| col1 | A-count | B-count |
+--+-+-+
|1 |   4 |   0 |
|2 |   0 |   6 |
+--+-+-+
So could it be a bug in 4.0.18?
- seb
---
David Griffiths <[EMAIL PROTECTED]> wrote:
 

One of our developers came to me yesterday with strange results from 
a query. I've created a simple version of the example. I've pasted 
the table definitions at the bottom if someone really needs to see 
them. This is on mysql 4.0.18.

insert into master (col1) values (1), (2);
insert into sub (col1, a, b, c) values
(1, 'a', null, '2'),
(1, 'a', null, '2'),
(2, null, 'b', '3'),
(2, null, 'b', '3');
mysql> select m.col1,
  -> sum(s1.c) as 'A-count',
  -> sum(s2.c) as 'B-count'
  -> FROM master m
  -> left join sub s1 on (m.col1 = s1.col1 AND s1.a is not null)
  -> left join sub s2 on (m.col1 = s2.col1 and s2.b is not null)
  -> group by m.col1;
+--+-+-+
| col1 | A-count | B-count |
+--+-+-+
|1 |   8 |NULL |
|2 |NULL |  12 |
+--+-+-+
2 rows in set (0.00 sec)
In case it's not obvious, the count for the column marked "A" should 
be 4, not 8. And for "B", it should be 6, not 12. The database seems 
to be iterating through the table twice.

If one of the outer-joins is removed, the results are correct. I 
would hazard a guess that if a third column existed in master/sub, 
and a third left-join was added, "A" would go to 12, and "B" would go 
to 16. Each outer join seems to spawn a new iteration through the data.

My question is "why", and what would be the strategy to avoid this?
  

 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: flush and flush delay

[Michael Stassen]

Right, MySQL would be configured so that the data directory is on the RAM 
disk.  On power-up you would recreate the RAM disk, fill it with the last 
backup from the flashdisk, then start mysqld.  You would, of course, lose 
everything since the last backup, but you said that was OK for your app.

It's difficult for me to say whether MyISAM or InnoDB would be faster for 
you.  It will depend on exactly how you'll be using it.  "MyISAM tables are 
really fast for select-heavy loads, while table level locks limit their 
scalability for write intensive multi-user environments. ... INNODB tables 
... have row level locking and consistent reads, which allow to reach 
excellent read write concurrency." 
 
You may find Jeremy Zawodny's book, "High Performance MySQL" 
 useful.

Michael
roland wrote:
Thanks for this. Very useful.
In particular your idea of using a RAM disk sounds interesting but I am not 
sure how you imagine I use it? I presume that on power down everythign on a 
ramdisk is lost so on every power=up I would have to put my data directory on 
the RAM-disk while the settings in mysql have the path to the data directory 
on the RAM-disk as the datadir?

Would there be any advice on how to set up the tables for maximum performance 
given the usage specifications as described in the earlier mail?

Regards,
Roland
On Sunday 17 October 2004 17:50, Michael Stassen wrote:
Those 2 variables are explained in the manual
.
  flush
This is ON if you have started mysqld with the --flush option. This
variable was added in MySQL 3.22.9.
  flush_time
If this is set to a non-zero value, all tables will be closed every
flush_time seconds to free up resources and sync unflushed data to
disk. We recommend this option only on Windows 9x or Me, or on systems with
minimal resources available. This variable was added in MySQL 3.22.18.
The --flush startup option is also explained in the manual
.
  --flush
Flush all changes to disk after each SQL statement. Normally MySQL
does a write of all changes to disk only after each SQL statement and lets
the operating system handle the synching to disk...
Both seem to do the opposite of what you want.
Let me start by saying that I've never done anything like what you are
trying, so you may get better advice from others.  That said, I'm not
surprised you're having trouble finding a way to get mysql to do what you
want, because some of your requirements (data not written to disk, loss of
data OK) are the exact oppposite of what one usually looks for in a db and
what mysql was designed for.
Note also that the question of writes is not just a matter of tables.
Changing data often involves updating indexes, which are also written to
disk.
I'm wondering if the description of --flush doesn't hold the key, "Normally
MySQL does a write of all changes to disk only after each SQL statement and
lets the operating system handle the synching to disk."  This led me to
think an alternative might be to solve this at the OS/filesystem level,
instead of in mysql.  That is, find an OS/filesystem combination which
behaves as you require: writes in memory, flush to disk only when asked.
This, in turn, led me to wonder if you've considered putting the db
(mysql's data directory) on a RAM disk, which you back up to flashdisk as
required. In the event of a power failure, you would restore the RAM disk
from the flashdisk and continue.  In that case, no modifications to mysql
would be required.
Michael
roland wrote:
Hello,
I am using a MySQL database for a somewhat odd application. In this
application I will have relatively small tables and relatively few
tables.
I would like to set the variables so that all the operations on the
database takes place in RAM and that it does not write to disk unless
explicily given a command to do so. Both on select and update operations
which will be what most operations will consist of. the reason for this
is firstly speed but almost more importantly I am using flashdisk which
has a limited life (about 200) write actions and my application will
performing update operations on a single entry at anything up to 1000
times per second. It is absolutely not critical should I lose the values
in case of a powerdown. In fact it would suffice to not use the tables on
the disk at all except to provide default values.
From the documentation I gathered that MySQL refers to writing to disk as
a flush operation. Doing a SHOW VARIABLES reveals that there are
variables called
flush   OFF
flush_delay 0
Since I have made no modifications yet these are default values. I must
misunderstanding something because this would imply that automatic
writing to disk is already inhibited and that seems unlikely.
My que

Re: mysql 4.1 + debian

[Hassan Schroeder]

Ron Gilbert wrote:
I got 4.1 from the debian site, 4.1 that is on the mysql site is a RPM 
and I've had no luck installing rpm's on debian, even using alien.
No, it's *available* as an RPM, but the *first* thing on the MySQL
download page is a binary for Linux packaged as a tar file.
Why not just use that?
--
Hassan Schroeder - [EMAIL PROTECTED]
Webtuitive Design ===  (+1) 408-938-0567   === http://webtuitive.com
  dream.  code.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql 4.1 + debian

[Ron Gilbert]

No, I did not see the reply, sorry, I just saw the original message.
I got 4.1 from the debian site, 4.1 that is on the mysql site is a RPM 
and I've had no luck installing rpm's on debian, even using alien.

Ron
On Oct 31, 2004, at 12:02 PM, Michael Stassen wrote:
If you're talking about the earlier message on this list, did you read 
the reply ?

It doesn't appear you've installed the official MySQL binary package, 
as it's not labeled "experimental".  I'd suggest downloading an 
official binary from the MySQL downloads page 
.

Michael
Ron Gilbert wrote:
I just installed debian (test) and am trying to get mysql 4.1 
working.  The only deb package for 4.1 that I could find was 
"experimental", which might explain the problem I am having.
When I start the server, I get the following in my syslog:
Oct 31 01:35:22 cove1 mysqld_safe[2523]: started
Oct 31 01:35:22 cove1 mysqld[2526]: 041031  1:35:22  [ERROR] bdb:  
unable to initialize mutex: Function not implemented
Oct 31 01:35:22 cove1 mysqld[2526]: 041031  1:35:22  [ERROR] bdb:  
process-private: unable to initialize environment lock: Function not 
implemented
Oct 31 01:35:22 cove1 mysqld[2526]: 041031  1:35:22  [ERROR] Can't 
init databases
Oct 31 01:35:22 cove1 mysqld[2526]: 041031  1:35:22  [ERROR] Aborting
Oct 31 01:35:22 cove1 mysqld[2526]:
Oct 31 01:35:22 cove1 mysqld[2526]: 041031  1:35:22  [NOTE] 
/usr/sbin/mysqld: Shutdown complete
I've googled for the mutex error and only find one hit with someone 
experiencing the same problem I am having and no solution.
Any ideas?  I'm new to debian, BTW.
Ron

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql 4.1 + debian

[Michael Stassen]

If you're talking about the earlier message on this list, did you read the 
reply ?

It doesn't appear you've installed the official MySQL binary package, as 
it's not labeled "experimental".  I'd suggest downloading an official binary 
from the MySQL downloads page .

Michael
Ron Gilbert wrote:
I just installed debian (test) and am trying to get mysql 4.1 working.  
The only deb package for 4.1 that I could find was "experimental", which 
might explain the problem I am having.

When I start the server, I get the following in my syslog:
Oct 31 01:35:22 cove1 mysqld_safe[2523]: started
Oct 31 01:35:22 cove1 mysqld[2526]: 041031  1:35:22  [ERROR] bdb:  
unable to initialize mutex: Function not implemented
Oct 31 01:35:22 cove1 mysqld[2526]: 041031  1:35:22  [ERROR] bdb:  
process-private: unable to initialize environment lock: Function not 
implemented
Oct 31 01:35:22 cove1 mysqld[2526]: 041031  1:35:22  [ERROR] Can't init 
databases
Oct 31 01:35:22 cove1 mysqld[2526]: 041031  1:35:22  [ERROR] Aborting
Oct 31 01:35:22 cove1 mysqld[2526]:
Oct 31 01:35:22 cove1 mysqld[2526]: 041031  1:35:22  [NOTE] 
/usr/sbin/mysqld: Shutdown complete

I've googled for the mutex error and only find one hit with someone 
experiencing the same problem I am having and no solution.

Any ideas?  I'm new to debian, BTW.
Ron

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: libmysqlclient.so.12 client libraries for OS X

[Michael Stassen]

MySQL does not come in pieces for Mac OS X.  The whole thing comes in one 
Mac OS X binary package in PKG format, downloaded as a disk image (.dmg) 
file .  The 
installer puts everything in /usr/local/mysql-VERSION, with a symbolic link 
at /usr/local/mysql.

The package does not include a shared library, however, as it's built with 
the --disable-shared flag 
.

Michael
Rob Kudyba wrote:
Where is there an OS X equivalent of:
"MySQL-client-VERSION.i386.rpm
The standard MySQL client programs. You probably always want to install
this package." which is referred to here:
http://dev.mysql.com/doc/mysql/en/Linux-RPM.html
Specifically, I am looking for libmysqlclient.so.12 since you cannot
package that in applications since it would break the terms of the GPL.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: synchronizing mysql database in two different places

[Bartis, Robert M (Bob)]

As suggested by Karam, please refer to 
http://dev.mysql.com/doc/mysql/en/Replication_Implementation.html for additional 
information.

-Original Message-
From: Karam Chand [mailto:[EMAIL PROTECTED]
Sent: Sunday, October 31, 2004 1:29 PM
To: Eko Budiharto; [EMAIL PROTECTED]
Subject: Re: synchronizing mysql database in two diferrent places


Hello,

You might try MySQL's inbuilt replication feature. 

If you want a more simple approach, might take a look
at SQLyog's Data Sync Wizard. More information can be
found at http://www.webyog.com

I think they have a article on the same subject in
SitePoint. You might google it.

Karam

--- Eko Budiharto <[EMAIL PROTECTED]> wrote:

> Hi,
> I a facing a situation where I have two database
> server in two different
> places that has exactly the same database. The thing
> is if the database
> is modified in either one of the places, both
> database has to be updated
> lively. 
> Can someone tell me who done this already? Please
> help.
> I am looking forward to a favorable reply from you.
> Thank you.  
> 




__
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail 

-- 
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: synchronizing mysql database in two diferrent places

[Karam Chand]

Hello,

You might try MySQL's inbuilt replication feature. 

If you want a more simple approach, might take a look
at SQLyog's Data Sync Wizard. More information can be
found at http://www.webyog.com

I think they have a article on the same subject in
SitePoint. You might google it.

Karam

--- Eko Budiharto <[EMAIL PROTECTED]> wrote:

> Hi,
> I a facing a situation where I have two database
> server in two different
> places that has exactly the same database. The thing
> is if the database
> is modified in either one of the places, both
> database has to be updated
> lively. 
> Can someone tell me who done this already? Please
> help.
> I am looking forward to a favorable reply from you.
> Thank you.  
> 




__
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

libmysqlclient.so.12 client libraries for OS X

[Rob Kudyba]

Where is there an OS X equivalent of:
"MySQL-client-VERSION.i386.rpm

The standard MySQL client programs. You probably always want to install
this package." which is referred to here:
http://dev.mysql.com/doc/mysql/en/Linux-RPM.html

Specifically, I am looking for libmysqlclient.so.12 since you cannot
package that in applications since it would break the terms of the GPL.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Linker Error

[premal mishra]

I'm using 4.0.20a on Windows 98.

My program compiler successfully but gives a linker error:

C:\SC\BIN\..\lib\mysqlclient.lib(default)  Offset 955A0H Record Type 00C3 
 Error 16: Index Range 

--- errorlevel 1

--- errorlevel 1
  
Regards
Premal.

Re: Converting table type

[Martijn Tonies]

> At 11:06 -0500 10/30/04, Michael Satterwhite wrote:
> >On Saturday 30 October 2004 10:58 am, Paul DuBois wrote:
> >>
> >>  What output does the following statement produce?
> >>
> >>  SHOW VARIABLES LIKE 'have_innodb';
> >>
> >>  If YES, the ALTER TABLE statement should have worked.
> >>  If NO, your server doesn't have InnoDB support built in.
> >>  If DISABLED, your server supports InnoDB but it was disable at
> >>  startup time with --skip-innodb.
> >
> >That was it. Thanks. I didn't think of Debian disabling the INNODB
function.
>
> Rude of them. :-)

Add to that: what about raising an error/warning message
when something like that fails??

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]

MySQL Control Center and Linux box connection

[Jerry Swanson]

I have Linux box running Red Hat and 3.23. I  install MySQL Contorl
Center and trying connect to the mysql on Linux box.

When I try to connect I receive the error message.
ERROR: HOST 'ip address' is not allowed to connect to this MySQL server.

What I'm doing wrong here?

TH

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

prepared statements and server restart

[Petri Helenius]

I tried to browse through the documentation to figure out what happens 
with prepared statements if the server is restarted while the prepared 
statement is assigned an ID and is being used repeatedly. The client 
then silently reconnects (reconnect flag is set). Is the expected 
behaviour for the client application to re-prepare the statements or 
should the client library silently re-send the statement to the server?

Pete
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mysql 4.1 + debian

[Ron Gilbert]

I just installed debian (test) and am trying to get mysql 4.1 working.  
The only deb package for 4.1 that I could find was "experimental", 
which might explain the problem I am having.

When I start the server, I get the following in my syslog:
Oct 31 01:35:22 cove1 mysqld_safe[2523]: started
Oct 31 01:35:22 cove1 mysqld[2526]: 041031  1:35:22  [ERROR] bdb:  
unable to initialize mutex: Function not implemented
Oct 31 01:35:22 cove1 mysqld[2526]: 041031  1:35:22  [ERROR] bdb:  
process-private: unable to initialize environment lock: Function not 
implemented
Oct 31 01:35:22 cove1 mysqld[2526]: 041031  1:35:22  [ERROR] Can't init 
databases
Oct 31 01:35:22 cove1 mysqld[2526]: 041031  1:35:22  [ERROR] Aborting
Oct 31 01:35:22 cove1 mysqld[2526]:
Oct 31 01:35:22 cove1 mysqld[2526]: 041031  1:35:22  [NOTE] 
/usr/sbin/mysqld: Shutdown complete

I've googled for the mutex error and only find one hit with someone 
experiencing the same problem I am having and no solution.

Any ideas?  I'm new to debian, BTW.
Ron

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]