Re: strange behavior in mysql-server 5.1.49 and 5.1.51
Hello,
There must have been some changes in the default interpretation
respective to foreign key attributes
If I add explicitly 'not null' to the columns host_id and admin_id
in test_nkomp_admin, it works like intended:
create table test_nkomp_admin
(
host_id int unsigned not null,
admin_id varchar(15) not null,
foreign key (host_id) references test_nkomp (host_id) on delete
cascade on update cascade,
foreign key (admin_id) references test_admin (admin_id) on delete
cascade on update cascade
)engine=innodb;
cheers
gregor
On 28.10.2010 17:18, misiaQ wrote:
Some more testing performed and it seems like problem with foreign key
reference indexing, see below:
create table test_nkomp_admin2
(
host_id int unsigned,
admin_id varchar(15),
foreign key (host_id) references test_nkomp (host_id) on delete
cascade on update cascade
)engine=innodb;
create table test_nkomp_admin3
(
host_id int unsigned,
admin_id varchar(15)
)engine=myisam;
insert into test_nkomp_admin2 (host_id,admin_id) values
(1,'luke'),(1,'yoda'),(1,'anakin'),(1,'leia'),(1,'r2'),(1,'obi'),(1,'han');
insert into test_nkomp_admin2 (host_id,admin_id) values
(2,'luke'),(2,'yoda'),(2,'anakin'),(2,'leia'),(2,'r2'),(2,'obi');
insert into test_nkomp_admin2 (host_id,admin_id) values
(3,'luke'),(3,'yoda'),(3,'anakin'),(3,'leia'),(3,'r2');
insert into test_nkomp_admin2 (host_id,admin_id) values
(4,'luke'),(4,'yoda'),(4,'anakin'),(4,'leia');
insert into test_nkomp_admin2 (host_id,admin_id) values
(5,'luke'),(5,'yoda'),(5,'anakin');
insert into test_nkomp_admin2 (host_id,admin_id) values
(6,'luke'),(6,'yoda');
insert into test_nkomp_admin3 (host_id,admin_id) values
(1,'luke'),(1,'yoda'),(1,'anakin'),(1,'leia'),(1,'r2'),(1,'obi'),(1,'han');
insert into test_nkomp_admin3 (host_id,admin_id) values
(2,'luke'),(2,'yoda'),(2,'anakin'),(2,'leia'),(2,'r2'),(2,'obi');
insert into test_nkomp_admin3 (host_id,admin_id) values
(3,'luke'),(3,'yoda'),(3,'anakin'),(3,'leia'),(3,'r2');
insert into test_nkomp_admin3 (host_id,admin_id) values
(4,'luke'),(4,'yoda'),(4,'anakin'),(4,'leia');
insert into test_nkomp_admin3 (host_id,admin_id) values
(5,'luke'),(5,'yoda'),(5,'anakin');
insert into test_nkomp_admin3 (host_id,admin_id) values
(6,'luke'),(6,'yoda');
mysql select * from test_nkomp_admin where host_id=6 and admin_id=yoda ;
Empty set (0,00 sec)
mysql select * from test_nkomp_admin2 where host_id=6 and admin_id=yoda ;
+-+--+
| host_id | admin_id |
+-+--+
| 6 | yoda |
+-+--+
1 row in set (0,00 sec)
mysql select * from test_nkomp_admin3 where host_id=6 and admin_id=yoda ;
+-+--+
| host_id | admin_id |
+-+--+
| 6 | yoda |
+-+--+
1 row in set (0,00 sec)
Regards,
m
-Original Message-
From: gregor kling [mailto:gregor.kl...@dvz.fh-giessen.de]
Sent: Thursday, October 28, 2010 1:54 PM
To: mysql@lists.mysql.com
Subject: strange behavior in mysql-server 5.1.49 and 5.1.51
Hello list,
I fight a strange behavior in mysql versions 5.1.49 and 5.1.51 -
maybe a fight against myself ;-)
The following query works exactly as assumed in version 5.1.41 with
the given testbed:
select * from test_nkomp_admin where host_id=6 and admin_id=yoda;
/* version 5.1.41 ubuntu 10.04 */
mysql select * from test_nkomp_admin where host_id=6 and
admin_id=yoda ;
+-+--+
| host_id | admin_id |
+-+--+
| 6 | yoda |
+-+--+
1 row in set (0.00 sec)
whereas in versions 5.1.49 (ubuntu 10.10) and 5.1.51 (package from
debian experimental,os ubuntu 10.10) the resultset is:
mysql select * from test_nkomp_admin where host_id=6 and
admin_id=yoda ;
Empty set (0.00 sec)
prove:
select * from test_nkomp_admin where host_id=6 ;
+-+--+
| host_id | admin_id |
+-+--+
| 6 | luke |
| 6 | yoda |
+-+--+
2 rows in set (0.00 sec)
Could anyone prove this behavior, or can give hint what the problem
might be ?
/* testbed */
drop table if exists test_nkomp_admin;
drop table if exists test_nkomp;
drop table if exists test_admin;
create table test_nkomp
(
host_id int unsigned auto_increment not null primary key
)engine=innodb;
create table test_admin
(
admin_id varchar(15) not null primary key
)engine=innodb;
create table test_nkomp_admin
(
host_id int unsigned,
admin_id varchar(15),
foreign key (host_id) references test_nkomp (host_id) on delete
cascade on update cascade,
foreign key (admin_id) references test_admin (admin_id) on delete
cascade on update cascade
)engine=innodb;
insert into test_nkomp (host_id) values (1),(2),(3),(4),(5),(6);
insert into test_admin (admin_id) values
('luke'),('yoda'),('anakin'),('leia'),('r2'),('obi'),('han');
insert into test_nkomp_admin (host_id,admin_id) values
(1,'luke'),(1,'yoda'),(1,'anakin'),(1,'leia'),(1,'r2'),(1,'obi'),(1,'han');
insert into test_nkomp_admin (host_id
strange behavior in mysql-server 5.1.49 and 5.1.51
Hello list,
I fight a strange behavior in mysql versions 5.1.49 and 5.1.51 -
maybe a fight against myself ;-)
The following query works exactly as assumed in version 5.1.41 with
the given testbed:
select * from test_nkomp_admin where host_id=6 and admin_id=yoda;
/* version 5.1.41 ubuntu 10.04 */
mysql select * from test_nkomp_admin where host_id=6 and
admin_id=yoda ;
+-+--+
| host_id | admin_id |
+-+--+
| 6 | yoda |
+-+--+
1 row in set (0.00 sec)
whereas in versions 5.1.49 (ubuntu 10.10) and 5.1.51 (package from
debian experimental,os ubuntu 10.10) the resultset is:
mysql select * from test_nkomp_admin where host_id=6 and
admin_id=yoda ;
Empty set (0.00 sec)
prove:
select * from test_nkomp_admin where host_id=6 ;
+-+--+
| host_id | admin_id |
+-+--+
| 6 | luke |
| 6 | yoda |
+-+--+
2 rows in set (0.00 sec)
Could anyone prove this behavior, or can give hint what the problem
might be ?
/* testbed */
drop table if exists test_nkomp_admin;
drop table if exists test_nkomp;
drop table if exists test_admin;
create table test_nkomp
(
host_id int unsigned auto_increment not null primary key
)engine=innodb;
create table test_admin
(
admin_id varchar(15) not null primary key
)engine=innodb;
create table test_nkomp_admin
(
host_id int unsigned,
admin_id varchar(15),
foreign key (host_id) references test_nkomp (host_id) on delete
cascade on update cascade,
foreign key (admin_id) references test_admin (admin_id) on delete
cascade on update cascade
)engine=innodb;
insert into test_nkomp (host_id) values (1),(2),(3),(4),(5),(6);
insert into test_admin (admin_id) values
('luke'),('yoda'),('anakin'),('leia'),('r2'),('obi'),('han');
insert into test_nkomp_admin (host_id,admin_id) values
(1,'luke'),(1,'yoda'),(1,'anakin'),(1,'leia'),(1,'r2'),(1,'obi'),(1,'han');
insert into test_nkomp_admin (host_id,admin_id) values
(2,'luke'),(2,'yoda'),(2,'anakin'),(2,'leia'),(2,'r2'),(2,'obi');
insert into test_nkomp_admin (host_id,admin_id) values
(3,'luke'),(3,'yoda'),(3,'anakin'),(3,'leia'),(3,'r2');
insert into test_nkomp_admin (host_id,admin_id) values
(4,'luke'),(4,'yoda'),(4,'anakin'),(4,'leia');
insert into test_nkomp_admin (host_id,admin_id) values
(5,'luke'),(5,'yoda'),(5,'anakin');
insert into test_nkomp_admin (host_id,admin_id) values
(6,'luke'),(6,'yoda');
cheers
gregor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: strange behavior in mysql-server 5.1.49 and 5.1.51
Works fine on 5.0.87 (rows returned as expected).
Confirmed on 5.1.51-log.
Most likely problem with VARCHAR behavior, because this one works fine:
select * from test_nkomp_admin where host_id=6 and trim(admin_id)='luke';
Regards,
m
-Original Message-
From: gregor kling [mailto:gregor.kl...@dvz.fh-giessen.de]
Sent: Thursday, October 28, 2010 1:54 PM
To: mysql@lists.mysql.com
Subject: strange behavior in mysql-server 5.1.49 and 5.1.51
Hello list,
I fight a strange behavior in mysql versions 5.1.49 and 5.1.51 -
maybe a fight against myself ;-)
The following query works exactly as assumed in version 5.1.41 with
the given testbed:
select * from test_nkomp_admin where host_id=6 and admin_id=yoda;
/* version 5.1.41 ubuntu 10.04 */
mysql select * from test_nkomp_admin where host_id=6 and
admin_id=yoda ;
+-+--+
| host_id | admin_id |
+-+--+
| 6 | yoda |
+-+--+
1 row in set (0.00 sec)
whereas in versions 5.1.49 (ubuntu 10.10) and 5.1.51 (package from
debian experimental,os ubuntu 10.10) the resultset is:
mysql select * from test_nkomp_admin where host_id=6 and
admin_id=yoda ;
Empty set (0.00 sec)
prove:
select * from test_nkomp_admin where host_id=6 ;
+-+--+
| host_id | admin_id |
+-+--+
| 6 | luke |
| 6 | yoda |
+-+--+
2 rows in set (0.00 sec)
Could anyone prove this behavior, or can give hint what the problem
might be ?
/* testbed */
drop table if exists test_nkomp_admin;
drop table if exists test_nkomp;
drop table if exists test_admin;
create table test_nkomp
(
host_id int unsigned auto_increment not null primary key
)engine=innodb;
create table test_admin
(
admin_id varchar(15) not null primary key
)engine=innodb;
create table test_nkomp_admin
(
host_id int unsigned,
admin_id varchar(15),
foreign key (host_id) references test_nkomp (host_id) on delete
cascade on update cascade,
foreign key (admin_id) references test_admin (admin_id) on delete
cascade on update cascade
)engine=innodb;
insert into test_nkomp (host_id) values (1),(2),(3),(4),(5),(6);
insert into test_admin (admin_id) values
('luke'),('yoda'),('anakin'),('leia'),('r2'),('obi'),('han');
insert into test_nkomp_admin (host_id,admin_id) values
(1,'luke'),(1,'yoda'),(1,'anakin'),(1,'leia'),(1,'r2'),(1,'obi'),(1,'han');
insert into test_nkomp_admin (host_id,admin_id) values
(2,'luke'),(2,'yoda'),(2,'anakin'),(2,'leia'),(2,'r2'),(2,'obi');
insert into test_nkomp_admin (host_id,admin_id) values
(3,'luke'),(3,'yoda'),(3,'anakin'),(3,'leia'),(3,'r2');
insert into test_nkomp_admin (host_id,admin_id) values
(4,'luke'),(4,'yoda'),(4,'anakin'),(4,'leia');
insert into test_nkomp_admin (host_id,admin_id) values
(5,'luke'),(5,'yoda'),(5,'anakin');
insert into test_nkomp_admin (host_id,admin_id) values
(6,'luke'),(6,'yoda');
cheers
gregor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=mis...@poczta.fm
---
Pobierz slownik angielsko-polski na telefon!
Kliknij http://linkint.pl/f2839
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: strange behavior in mysql-server 5.1.49 and 5.1.51
On 10/28/2010 03:34 PM, misiaQ wrote:
Works fine on 5.0.87 (rows returned as expected).
Confirmed on 5.1.51-log.
Most likely problem with VARCHAR behavior, because this one works fine:
select * from test_nkomp_admin where host_id=6 and trim(admin_id)='luke';
The thing with the varchar was also my first guess, what could be wrong
after dumping the database.
So I tested it like this (just to be sure not having whitespaces in the
data):
mysql select * into outfile /tmp/out.txt fields enclosed by ' from
test_nkomp_admin where host_id=6;
Query OK, 2 rows affected (0.00 sec)
shell cat /tmp/out.txt
'6' 'luke'
'6' 'yoda'
yes, this one works also for me.
And this one:
mysql select * from test_nkomp_admin where host_id=6 and admin_id like
'luke%';
+-+--+
| host_id | admin_id |
+-+--+
| 6 | luke |
+-+--+
1 row in set (0.00 sec)
And this one:
mysql select * from test_nkomp_admin where host_id=6 and admin_id like
'%luke';
+-+--+
| host_id | admin_id |
+-+--+
| 6 | luke |
+-+--+
1 row in set (0.00 sec)
But not this one:
mysql select * from test_nkomp_admin where host_id=6 and admin_id like
'luke';
Empty set (0.00 sec)
So I guess, I should bag a bugreport ...
cheers
gregor
Regards,
m
-Original Message-
From: gregor kling [mailto:gregor.kl...@dvz.fh-giessen.de]
Sent: Thursday, October 28, 2010 1:54 PM
To: mysql@lists.mysql.com
Subject: strange behavior in mysql-server 5.1.49 and 5.1.51
Hello list,
I fight a strange behavior in mysql versions 5.1.49 and 5.1.51 -
maybe a fight against myself ;-)
The following query works exactly as assumed in version 5.1.41 with
the given testbed:
select * from test_nkomp_admin where host_id=6 and admin_id=yoda;
/* version 5.1.41 ubuntu 10.04 */
mysql select * from test_nkomp_admin where host_id=6 and
admin_id=yoda ;
+-+--+
| host_id | admin_id |
+-+--+
| 6 | yoda |
+-+--+
1 row in set (0.00 sec)
whereas in versions 5.1.49 (ubuntu 10.10) and 5.1.51 (package from
debian experimental,os ubuntu 10.10) the resultset is:
mysql select * from test_nkomp_admin where host_id=6 and
admin_id=yoda ;
Empty set (0.00 sec)
prove:
select * from test_nkomp_admin where host_id=6 ;
+-+--+
| host_id | admin_id |
+-+--+
| 6 | luke |
| 6 | yoda |
+-+--+
2 rows in set (0.00 sec)
Could anyone prove this behavior, or can give hint what the problem
might be ?
/* testbed */
drop table if exists test_nkomp_admin;
drop table if exists test_nkomp;
drop table if exists test_admin;
create table test_nkomp
(
host_id int unsigned auto_increment not null primary key
)engine=innodb;
create table test_admin
(
admin_id varchar(15) not null primary key
)engine=innodb;
create table test_nkomp_admin
(
host_id int unsigned,
admin_id varchar(15),
foreign key (host_id) references test_nkomp (host_id) on delete
cascade on update cascade,
foreign key (admin_id) references test_admin (admin_id) on delete
cascade on update cascade
)engine=innodb;
insert into test_nkomp (host_id) values (1),(2),(3),(4),(5),(6);
insert into test_admin (admin_id) values
('luke'),('yoda'),('anakin'),('leia'),('r2'),('obi'),('han');
insert into test_nkomp_admin (host_id,admin_id) values
(1,'luke'),(1,'yoda'),(1,'anakin'),(1,'leia'),(1,'r2'),(1,'obi'),(1,'han');
insert into test_nkomp_admin (host_id,admin_id) values
(2,'luke'),(2,'yoda'),(2,'anakin'),(2,'leia'),(2,'r2'),(2,'obi');
insert into test_nkomp_admin (host_id,admin_id) values
(3,'luke'),(3,'yoda'),(3,'anakin'),(3,'leia'),(3,'r2');
insert into test_nkomp_admin (host_id,admin_id) values
(4,'luke'),(4,'yoda'),(4,'anakin'),(4,'leia');
insert into test_nkomp_admin (host_id,admin_id) values
(5,'luke'),(5,'yoda'),(5,'anakin');
insert into test_nkomp_admin (host_id,admin_id) values
(6,'luke'),(6,'yoda');
cheers
gregor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: strange behavior in mysql-server 5.1.49 and 5.1.51
Some more testing performed and it seems like problem with foreign key
reference indexing, see below:
create table test_nkomp_admin2
(
host_id int unsigned,
admin_id varchar(15),
foreign key (host_id) references test_nkomp (host_id) on delete
cascade on update cascade
)engine=innodb;
create table test_nkomp_admin3
(
host_id int unsigned,
admin_id varchar(15)
)engine=myisam;
insert into test_nkomp_admin2 (host_id,admin_id) values
(1,'luke'),(1,'yoda'),(1,'anakin'),(1,'leia'),(1,'r2'),(1,'obi'),(1,'han');
insert into test_nkomp_admin2 (host_id,admin_id) values
(2,'luke'),(2,'yoda'),(2,'anakin'),(2,'leia'),(2,'r2'),(2,'obi');
insert into test_nkomp_admin2 (host_id,admin_id) values
(3,'luke'),(3,'yoda'),(3,'anakin'),(3,'leia'),(3,'r2');
insert into test_nkomp_admin2 (host_id,admin_id) values
(4,'luke'),(4,'yoda'),(4,'anakin'),(4,'leia');
insert into test_nkomp_admin2 (host_id,admin_id) values
(5,'luke'),(5,'yoda'),(5,'anakin');
insert into test_nkomp_admin2 (host_id,admin_id) values
(6,'luke'),(6,'yoda');
insert into test_nkomp_admin3 (host_id,admin_id) values
(1,'luke'),(1,'yoda'),(1,'anakin'),(1,'leia'),(1,'r2'),(1,'obi'),(1,'han');
insert into test_nkomp_admin3 (host_id,admin_id) values
(2,'luke'),(2,'yoda'),(2,'anakin'),(2,'leia'),(2,'r2'),(2,'obi');
insert into test_nkomp_admin3 (host_id,admin_id) values
(3,'luke'),(3,'yoda'),(3,'anakin'),(3,'leia'),(3,'r2');
insert into test_nkomp_admin3 (host_id,admin_id) values
(4,'luke'),(4,'yoda'),(4,'anakin'),(4,'leia');
insert into test_nkomp_admin3 (host_id,admin_id) values
(5,'luke'),(5,'yoda'),(5,'anakin');
insert into test_nkomp_admin3 (host_id,admin_id) values
(6,'luke'),(6,'yoda');
mysql select * from test_nkomp_admin where host_id=6 and admin_id=yoda ;
Empty set (0,00 sec)
mysql select * from test_nkomp_admin2 where host_id=6 and admin_id=yoda ;
+-+--+
| host_id | admin_id |
+-+--+
| 6 | yoda |
+-+--+
1 row in set (0,00 sec)
mysql select * from test_nkomp_admin3 where host_id=6 and admin_id=yoda ;
+-+--+
| host_id | admin_id |
+-+--+
| 6 | yoda |
+-+--+
1 row in set (0,00 sec)
Regards,
m
-Original Message-
From: gregor kling [mailto:gregor.kl...@dvz.fh-giessen.de]
Sent: Thursday, October 28, 2010 1:54 PM
To: mysql@lists.mysql.com
Subject: strange behavior in mysql-server 5.1.49 and 5.1.51
Hello list,
I fight a strange behavior in mysql versions 5.1.49 and 5.1.51 -
maybe a fight against myself ;-)
The following query works exactly as assumed in version 5.1.41 with
the given testbed:
select * from test_nkomp_admin where host_id=6 and admin_id=yoda;
/* version 5.1.41 ubuntu 10.04 */
mysql select * from test_nkomp_admin where host_id=6 and
admin_id=yoda ;
+-+--+
| host_id | admin_id |
+-+--+
| 6 | yoda |
+-+--+
1 row in set (0.00 sec)
whereas in versions 5.1.49 (ubuntu 10.10) and 5.1.51 (package from
debian experimental,os ubuntu 10.10) the resultset is:
mysql select * from test_nkomp_admin where host_id=6 and
admin_id=yoda ;
Empty set (0.00 sec)
prove:
select * from test_nkomp_admin where host_id=6 ;
+-+--+
| host_id | admin_id |
+-+--+
| 6 | luke |
| 6 | yoda |
+-+--+
2 rows in set (0.00 sec)
Could anyone prove this behavior, or can give hint what the problem
might be ?
/* testbed */
drop table if exists test_nkomp_admin;
drop table if exists test_nkomp;
drop table if exists test_admin;
create table test_nkomp
(
host_id int unsigned auto_increment not null primary key
)engine=innodb;
create table test_admin
(
admin_id varchar(15) not null primary key
)engine=innodb;
create table test_nkomp_admin
(
host_id int unsigned,
admin_id varchar(15),
foreign key (host_id) references test_nkomp (host_id) on delete
cascade on update cascade,
foreign key (admin_id) references test_admin (admin_id) on delete
cascade on update cascade
)engine=innodb;
insert into test_nkomp (host_id) values (1),(2),(3),(4),(5),(6);
insert into test_admin (admin_id) values
('luke'),('yoda'),('anakin'),('leia'),('r2'),('obi'),('han');
insert into test_nkomp_admin (host_id,admin_id) values
(1,'luke'),(1,'yoda'),(1,'anakin'),(1,'leia'),(1,'r2'),(1,'obi'),(1,'han');
insert into test_nkomp_admin (host_id,admin_id) values
(2,'luke'),(2,'yoda'),(2,'anakin'),(2,'leia'),(2,'r2'),(2,'obi');
insert into test_nkomp_admin (host_id,admin_id) values
(3,'luke'),(3,'yoda'),(3,'anakin'),(3,'leia'),(3,'r2');
insert into test_nkomp_admin (host_id,admin_id) values
(4,'luke'),(4,'yoda'),(4,'anakin'),(4,'leia');
insert into test_nkomp_admin (host_id,admin_id) values
(5,'luke'),(5,'yoda'),(5,'anakin');
insert into test_nkomp_admin (host_id,admin_id) values
(6,'luke'),(6,'yoda');
cheers
gregor
--
Mieszkania, domy, dzialki
Re: Workbench strange behavior
In regards to this issue, I submitted a bug. http://bugs.mysql.com/56157 nixofortune wrote: Hi ALL, I just start using Workbench 5.2.26 CE and this is a problem I have. When I try to run a query with a case statement, columns with datetime Type shown as BLOB in output window. To see the output data I have to right click inside of the cell, choose Open Value in Viewer and see text. Example: case when dda.cancelled_on is null then '' when dda.cancelled_on is not null then dda.cancelled_on end as 'Cancelled On', Should produce cells with a date of cancelled operation, but it returns blob icons where the dates should be. If I try to Export data as CSV file, the fileds with 'blob' icon instead of the real datetime data are empty. The code works nicely in MySQL monitor or PhPMyAdmin with properly formated CSV exports, It could be some View option that I missed or Bug in the Workbench. Has anybody experienced similar Workbench behavior, any ideas? Thanks. Igor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Workbench strange behavior
Hi ALL, I just start using Workbench 5.2.26 CE and this is a problem I have. When I try to run a query with a case statement, columns with datetime Type shown as BLOB in output window. To see the output data I have to right click inside of the cell, choose Open Value in Viewer and see text. Example: case when dda.cancelled_on is null then '' when dda.cancelled_on is not null then dda.cancelled_on end as 'Cancelled On', Should produce cells with a date of cancelled operation, but it returns blob icons where the dates should be. If I try to Export data as CSV file, the fileds with 'blob' icon instead of the real datetime data are empty. The code works nicely in MySQL monitor or PhPMyAdmin with properly formated CSV exports, It could be some View option that I missed or Bug in the Workbench. Has anybody experienced similar Workbench behavior, any ideas? Thanks. Igor
Re: Strange behavior by MySQL Stored Procedure
Does anyone have any sort of any idea on how to deal with this problem?
This is happening again and again and not all the time but randomly anytime.--Regards,Manasi Save
On Wed, 02 Jun 2010 06:46:56 -0400, Manasi Save
wrote:
Dear Venugopal,Here's theSample
Java Code Which Calls stored procedure :-//get the
connection to databaseConnection dbConnection = getConnection();//create the call for procedureString procedureCallStmtStr =
"Call XYZ()";//create callable statement objectCallableStatement cs = conn.prepareCall(procedureCallStmtStr);//execute the procedurecs.execute();//obtain
resultsetResultSet result = cs.getResultSet();//Iterate
to get the resultSet, if present//commit transactionconn.commit();//close resultset, callableStatementresult.close();cs.close();But, can it be a problem if
I am executing a stored procedure anywhere? Well, I am not aware of Java so
really cannot debug this.Thanks in advance.--Regards,Manasi SaveOn Tue, 1 Jun 2010 09:36:12 +0530 (IST),
Venugopal Rao wrote:
Stored procedures are not executed like a query.
They are executed thru a Call { procedure} method.
Please check the same or let us know how you are executing the
Query/Calling the Procedure.
Regards,
VR Venugopal Rao
--- On Fri, 28/5/10, Manasi Save
manasi.s...@artificialmachines.com wrote:
From: Manasi Save manasi.s...@artificialmachines.comSubject: Strange behavior by MySQL Stored ProcedureTo:
mysql@lists.mysql.comDate: Friday, 28 May, 2010, 5:44 PM
Dear All,I have one stored procedure
Which inserts data into one table.But sometimes it does not
insert record. This happens when I called it from java application. But If I
called same query from mysql command line. It executes successfully.Also I have one procedure which only retrieves data from table.
and it only gives one row sometime even if there are 10 rows available in for
matching condition. This too happen when I called it from Java application and
if I called it from mysql command line it gives me proper result set of 10
rows.I am not able to understand Is it something known for
mysql? Or am I doing something wrong?Any input will be a great
help.--Thanks and Regards,Manasi Save
Re: Strange behavior by MySQL Stored Procedure
Dear Venugopal,Here's theSample Java Code Which Calls
stored procedure :-//get the connection to databaseConnection dbConnection = getConnection();//create the call
for procedureString procedureCallStmtStr = "Call XYZ()";//create callable statement objectCallableStatement cs =
conn.prepareCall(procedureCallStmtStr);//execute the
procedurecs.execute();//obtain resultsetResultSet
result = cs.getResultSet();//Iterate to get the resultSet, if
present//commit transactionconn.commit();//close resultset, callableStatementresult.close();cs.close();But, can it be a problem if I am executing a stored procedure
anywhere? Well, I am not aware of Java so really cannot debug this.Thanks in advance.--Regards,Manasi SaveOn Tue, 1 Jun 2010 09:36:12 +0530 (IST), Venugopal Rao wrote:
Stored procedures are not executed like a query.
They are executed thru a Call { procedure} method.
Please check the same or let us know how you are executing the
Query/Calling the Procedure.
Regards,
VR Venugopal Rao
--- On Fri, 28/5/10, Manasi Save
manasi.s...@artificialmachines.com wrote:
From: Manasi Save manasi.s...@artificialmachines.comSubject: Strange behavior by MySQL Stored ProcedureTo:
mysql@lists.mysql.comDate: Friday, 28 May, 2010, 5:44 PM
Dear All,I have one stored procedure
Which inserts data into one table.But sometimes it does not
insert record. This happens when I called it from java application. But If I
called same query from mysql command line. It executes successfully.Also I have one procedure which only retrieves data from table.
and it only gives one row sometime even if there are 10 rows available in for
matching condition. This too happen when I called it from Java application and
if I called it from mysql command line it gives me proper result set of 10
rows.I am not able to understand Is it something known for
mysql? Or am I doing something wrong?Any input will be a great
help.--Thanks and Regards,Manasi Save
Re: Strange behavior by MySQL Stored Procedure
mysql Version :- 5.1.42-community-log
mysql Connector/J Version :- mysql-connector-java-5.1.6-bin.jar
Sample Java Code Which Calls stored procedure :-
//get the connection to database
Connection dbConnection = getConnection();
//create the call for procedure
String procedureCallStmtStr = Call XYZ();
//create callable statement object
CallableStatement cs = conn.prepareCall(procedureCallStmtStr);
//execute the procedure
cs.execute();
//obtain resultset
ResultSet result = cs.getResultSet();
//Iterate to get the resultSet, if present
//commit transaction
conn.commit();
//close resultset, callableStatement
result.close();
cs.close();
Stored procedure which is getting called :-
CREATE definer=`myus...@`localhost` PROCEDURE `AddCust`(InputUserID
BigInt, InputCustID BigInt, InputDBID BigInt, InputTimeStamp DateTime)
DETERMINISTIC
BEGIN
Declare DBName Varchar(45);
Select InputDBID into DBName;
Drop Temporary Table If Exists Temp;
Create Temporary Table Temp
(
UserID BigInt,
CustID BigInt,
MarkForDeletion Boolean
);
SET @stmt = Concat('Insert into Temp(UserID, CustID, MarkForDeletion)
Select FK_UserID, FK_CustID, MarkForDeletion
From `',DBName,'`.Tbl1
Where FK_UserID = ',InputUserID,'
and FK_CustID = ',InputCustID,';');
Prepare stmt1 From @stmt;
Execute stmt1;
Deallocate prepare stmt1;
IF Exists (Select CustID From Temp Where CustID = InputCustID)
Then
SET @stmt = Concat('Update `',DBName,'`.Tbl1
Set MarkForDeletion = 0,
TimeStamp =
','',InputTimeStamp,'','
Where FK_UserID =
',InputUserID,'
and FK_CustID =
',InputCustID,';');
Prepare stmt1 From @stmt;
Execute stmt1;
Deallocate Prepare stmt1;
ELSE
SET @stmt = Concat('Insert into ',
'`',DBName,'`.Tbl1 (FK_CustID, FK_UserID, MarkForDeletion, TimeStamp) ',
'Select ', '', InputCustID, '', ',',
'',InputUserID,'',', False',',','',InputTimeStamp,'',';');
Prepare stmt1 From @stmt;
Execute stmt1;
Deallocate Prepare stmt1;
Select InputUserID as RecordInserted;
END IF;
Thanks in advance.
--
Regards,
Manasi Save
On Fri, 28 May 2010 15:40:05 0200, Mattia Merzi wrote:
2010/5/28 Manasi Save manasi.s...@artificialmachines.com:
[...]
Or am I doing something wrong?
probably;
you better send us another e-mail writing at least:
- mysql version you are using
- mysql Connector/J version you are using
- piece of java code you are using to call the stored procedure
- source of the stored procedure (or part of it)
... probably, a subset of all of these infos will not be enough
to understand the problem.
In any case, if you have troubles using the mysql jdbc driver
but no problem using the mysql CLI and you suspect a
Connector/J bug, maybe you better write to the mysql java
support mailing list: http://lists.mysql.com/java
Greetings,
Mattia.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Strange behavior by MySQL Stored Procedure
Stored procedures are not executed like a query.
They are executed thru a Call { procedure} method.
Please check the same or let us know how you are executing the Query/Calling
the Procedure.
Regards,
VR Venugopal Rao
--- On Fri, 28/5/10, Manasi Save manasi.s...@artificialmachines.com wrote:
From: Manasi Save manasi.s...@artificialmachines.com
Subject: Strange behavior by MySQL Stored Procedure
To: mysql@lists.mysql.com
Date: Friday, 28 May, 2010, 5:44 PM
Dear All,
I have one stored procedure Which inserts data into one table.
But sometimes it does not insert record. This happens when I called it from
java application. But If I called same query from mysql command line. It
executes successfully.
Also I have one procedure which only retrieves data from table. and it only
gives one row sometime even if there are 10 rows available in for matching
condition. This too happen when I called it from Java application and if I
called it from mysql command line it gives me proper result set of 10 rows.
I am not able to understand Is it something known for mysql? Or am I doing
something wrong?
Any input will be a great help.
--
Thanks and Regards,
Manasi Save
Strange behavior by MySQL Stored Procedure
Dear All, I have one stored procedure Which inserts data into one table. But sometimes it does not insert record. This happens when I called it from java application. But If I called same query from mysql command line. It executes successfully. Also I have one procedure which only retrieves data from table. and it only gives one row sometime even if there are 10 rows available in for matching condition. This too happen when I called it from Java application and if I called it from mysql command line it gives me proper result set of 10 rows. I am not able to understand Is it something known for mysql? Or am I doing something wrong? Any input will be a great help. --Thanks and Regards, Manasi Save
Re: Strange behavior by MySQL Stored Procedure
2010/5/28 Manasi Save manasi.s...@artificialmachines.com: [...] Or am I doing something wrong? probably; you better send us another e-mail writing at least: - mysql version you are using - mysql Connector/J version you are using - piece of java code you are using to call the stored procedure - source of the stored procedure (or part of it) ... probably, a subset of all of these infos will not be enough to understand the problem. In any case, if you have troubles using the mysql jdbc driver but no problem using the mysql CLI and you suspect a Connector/J bug, maybe you better write to the mysql java support mailing list: http://lists.mysql.com/java Greetings, Mattia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Strange behavior by MySQL Stored Procedure
Hello Manasi, If possible can you please send in the code that you mentioned (procedure or trigger). Please give a detailed technical explanation explaining the query which you used from command line and the query used in the procedure. Please mention the table structure, show table status and few records from the query executed. Cheers, Anirudh Sundar 9594506474 DataVail Mumbai. On Fri, May 28, 2010 at 5:44 PM, Manasi Save manasi.s...@artificialmachines.com wrote: Dear All, I have one stored procedure Which inserts data into one table. But sometimes it does not insert record. This happens when I called it from java application. But If I called same query from mysql command line. It executes successfully. Also I have one procedure which only retrieves data from table. and it only gives one row sometime even if there are 10 rows available in for matching condition. This too happen when I called it from Java application and if I called it from mysql command line it gives me proper result set of 10 rows. I am not able to understand Is it something known for mysql? Or am I doing something wrong? Any input will be a great help. -- Thanks and Regards, Manasi Save
Re: Strange behavior with integer unsigned type...
Hello. That seems like a bug: http://bugs.mysql.com/bug.php?id=14543 Marko Domanovic wrote: mysql 5.0.15-standard UPDATE table SET fieldname = fieldname-1 when the fieldname is 0 gives me 4294967295 fieldname is integer(10) unsigned... maybe it would be more logical the expression to evaluate as 0, insted 2^32 .. -- 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: Strange behavior with integer unsigned type...
Maybe it is because I am a programmer, but (unsigned) 0 - 1 = 4294967295. What's the big deal? Gleb Paharenko wrote: Hello. That seems like a bug: http://bugs.mysql.com/bug.php?id=14543 Marko Domanovic wrote: mysql 5.0.15-standard UPDATE table SET fieldname = fieldname-1 when the fieldname is 0 gives me 4294967295 fieldname is integer(10) unsigned... maybe it would be more logical the expression to evaluate as 0, insted 2^32 .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior with integer unsigned type...
mysql 5.0.15-standard UPDATE table SET fieldname = fieldname-1 when the fieldname is 0 gives me 4294967295 fieldname is integer(10) unsigned... maybe it would be more logical the expression to evaluate as 0, insted 2^32 .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange behavior with integer unsigned type...
I noticed rather interesting thing... If you deduct 1 from the 0 which is stored in integer unsigned field, you get 2^32, not 0. I think that's how things are not working with version 4, and want to ask is this behavior bug or feature in mysql version 5, and is it customizable? Greetings, Marko -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior with integer unsigned type...
Hello. On both 4.1.16 and 5.0.17 I've got the same results, however not 2^32, but 18446744073709551615. 4.0 is deprecated and its results could be different. Please provide exact SQL statement which you're using if you still think that MySQL behaves weirdly with unsigned integers. In the manual we have: mysql SELECT CAST(1-2 AS UNSIGNED) - 18446744073709551615 Marko Domanovic wrote: I noticed rather interesting thing... If you deduct 1 from the 0 which is stored in integer unsigned field, you get 2^32, not 0. I think that's how things are not working with version 4, and want to ask is this behavior bug or feature in mysql version 5, and is it customizable? Greetings, Marko -- 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: Strange behavior with integer unsigned type...
I don't think that this behaviour is very surprising. If you carry out a mathmaticical operation that returns a result outside the data type's range then it _must_ give you an incorrect result. The only alternative would be to throw an error. I know that the manual documents that after an auto_increment column hits its maximum value it will 'roll over' to the lowest value that column will store (i.e. 0 for an unsigned int). Probably if you add 1 to 2^32 (or 2^16 for a SMALLINT, for example) you will get 0. BTW, 18446744073709551615 is the maximum value for a BIGINT (64 bit number), and IIRC MySQL uses 64 bit maths. FWIW my preferred web app middleware - Lasso - does the same thing (only with signed 64 bit numbers). HTH, James Harvard At 11:17 pm +0200 23/12/05, Gleb Paharenko wrote: On both 4.1.16 and 5.0.17 I've got the same results, however not 2^32, but 18446744073709551615. 4.0 is deprecated and its results could be different. Please provide exact SQL statement which you're using if you still think that MySQL behaves weirdly with unsigned integers. In the manual we have: mysql SELECT CAST(1-2 AS UNSIGNED) - 18446744073709551615 Marko Domanovic wrote: I noticed rather interesting thing... If you deduct 1 from the 0 which is stored in integer unsigned field, you get 2^32, not 0. I think that's how things are not working with version 4, and want to ask is this behavior bug or feature in mysql version 5, and is it customizable? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior
Hello. According to: http://dev.mysql.com/doc/mysql/en/show-processlist.html the temporary result set was larger than tmp_table_size and the thread is changing the temporary table from in-memory to disk-based format to save memory. I suggest you to play with the value of this variable which is now: tmp_table_size33554432 Also I think that upgrade to 4.1.11 might solve the problem. The page http://dev.mysql.com/doc/mysql/en/order-by-optimization.html says that MySQL 4.1 and up uses a new faster algorithm for optimization and 'ORDER BY' queries. And maybe some advices from there could be helpful for you. However, I don't see how it is related to replication, except that master makes a lot of requests to read binary logs to send the updates to the slave. Rafal Kedziorski [EMAIL PROTECTED] wrote: I get this: id: 52401 user: omk-write host: db: omk command: query time: 0 State: Copying to tmp table Info: select distinct m.media_id from category_tree c_tree, media_2_category m2c, media m, media_2_partner ... -- 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: Strange behavior
At 22:13 07.04.2005, Gleb Paharenko wrote: Hello. According to: http://dev.mysql.com/doc/mysql/en/show-processlist.html the temporary result set was larger than tmp_table_size and the thread But we get max. 10.000 long values in out result set. is changing the temporary table from in-memory to disk-based format to save memory. I suggest you to play with the value of this variable which is now: tmp_table_size33554432 I try to change this value. Also I think that upgrade to 4.1.11 might solve the problem. The page I was thinking already about this. http://dev.mysql.com/doc/mysql/en/order-by-optimization.html says that MySQL 4.1 and up uses a new faster algorithm for optimization and 'ORDER BY' queries. And maybe some advices from there could be helpful for you. However, I don't see how it is related to replication, except that master makes a lot of requests to read binary logs to send the updates to the slave. Regards, Rafal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior
At 22:13 07.04.2005, Gleb Paharenko wrote: Hello. According to: http://dev.mysql.com/doc/mysql/en/show-processlist.html the temporary result set was larger than tmp_table_size and the thread is changing the temporary table from in-memory to disk-based format to save memory. I suggest you to play with the value of this variable which is now: tmp_table_size33554432 after set this value 2 times bigger, we have the old speed. thanks for help! Best Regards, Rafal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior
Hello. Can you figure out in which state the queries from JBoss spend time the most? You may use your own program and 'SHOW PROCESSLIST' statement or something like: mysqladmin -i 1 -r processlist. I don't see a big difference between JBoss and a normal Java application except JBoss uses it's own connection pool. The problem could be at JBoss side. Another suggestion - what happens if you switch from the prepared statements to usual queries? BTW your innodb_log_file_size is about 12 times smaller then innodb_buffer_pool_size. According to: http://dev.mysql.com/doc/mysql/en/innodb-configuration.html it should be about 25% of the buffer pool size. In our J2EE application which runs under JBoss 3.2.2 we are generating own queries by using a connection from JBoss connection pool. This are prepared statements - needed from JBoss 450-500 millis - nedded from normal Java application 15-25 millis Rafal Kedziorski [EMAIL PROTECTED] wrote: -- 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: Strange behavior
At 16:59 06.04.2005, Gleb Paharenko wrote: Hello. Can you figure out in which state the queries from JBoss spend time the most? no, cause You may use your own program and 'SHOW PROCESSLIST' statement or something like: mysqladmin -i 1 -r processlist. I get this: id: 52401 user: omk-write host: db: omk command: query time: 0 State: Copying to tmp table Info: select distinct m.media_id from category_tree c_tree, media_2_category m2c, media m, media_2_partner ... I don't see a big difference between JBoss and a normal Java application except JBoss uses it's own connection pool. The problem could be at JBoss side. Another suggestion - what happens if you switch But before our update the queries speed was very good. Next time I will switch off slave configuration for test. from the prepared statements to usual queries? Have to check. BTW your innodb_log_file_size is about 12 times smaller then innodb_buffer_pool_size. According to: http://dev.mysql.com/doc/mysql/en/innodb-configuration.html thanks. Regards, Rafal it should be about 25% of the buffer pool size. In our J2EE application which runs under JBoss 3.2.2 we are generating own queries by using a connection from JBoss connection pool. This are prepared statements - needed from JBoss 450-500 millis - nedded from normal Java application 15-25 millis Rafal Kedziorski [EMAIL PROTECTED] wrote: -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior
At 16:59 06.04.2005, Gleb Paharenko wrote: Hello. Can you figure out in which state the queries from JBoss spend time the most? You may use your own program and 'SHOW PROCESSLIST' statement or something like: mysqladmin -i 1 -r processlist. I don't see a big difference between JBoss and a normal Java application except JBoss uses it's own connection pool. The problem could be at JBoss side. Another suggestion - what happens if you switch from the prepared statements to usual queries? After switch from PreparedStatement to Statement the same performance. BTW your innodb_log_file_size is about 12 times smaller then innodb_buffer_pool_size. According to: http://dev.mysql.com/doc/mysql/en/innodb-configuration.html it should be about 25% of the buffer pool size. In our J2EE application which runs under JBoss 3.2.2 we are generating own queries by using a connection from JBoss connection pool. This are prepared statements - needed from JBoss 450-500 millis - nedded from normal Java application 15-25 millis Rafal Kedziorski [EMAIL PROTECTED] wrote: -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior
At 18:35 01.04.2005, Gleb Paharenko wrote: Hello. I don't have any ideas at least now. But additional information could be helpful. Do you connect from JBoss to the slave or master server? Please use We are conecting to the active mysql (normaly master). SHOW PROCESSLIST to find in what state the server threads waste their time. If you find something interesting send it. Include also the output of SHOW STATUS and SHOW VARIABLES. SHOW STATUS: mysql show status; +++ | Variable_name | Value | +++ | Aborted_clients| 342| | Aborted_connects | 0 | | Bytes_received | 2114765083 | | Bytes_sent | 3521573247 | | Com_admin_commands | 3992 | | Com_alter_table| 2 | | Com_analyze| 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_change_db | 119962 | | Com_change_master | 0 | | Com_check | 0 | | Com_commit | 106880 | | Com_create_db | 1 | | Com_create_function| 0 | | Com_create_index | 0 | | Com_create_table | 0 | | Com_delete | 162153 | | Com_delete_multi | 0 | | Com_drop_db| 1 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_flush | 0 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open| 0 | | Com_ha_read| 0 | | Com_insert | 147742 | | Com_insert_select | 0 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables| 0 | | Com_optimize | 0 | | Com_purge | 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace| 0 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_rollback | 3691 | | Com_savepoint | 0 | | Com_select | 9075484| | Com_set_option | 32097 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 5 | | Com_show_create| 0 | | Com_show_databases | 44 | | Com_show_fields| 1284 | | Com_show_grants| 0 | | Com_show_keys | 1219 | | Com_show_logs | 0 | | Com_show_master_status | 0 | | Com_show_new_master| 0 | | Com_show_open_tables | 0 | | Com_show_processlist | 69 | | Com_show_slave_hosts | 4 | | Com_show_slave_status | 0 | | Com_show_status| 22195 | | Com_show_innodb_status | 13030 | | Com_show_tables| 1483 | | Com_show_variables | 56755 | | Com_slave_start| 0 | | Com_slave_stop | 0 | | Com_truncate | 0 | | Com_unlock_tables | 0 | | Com_update | 39024 | | Connections| 45560 | | Created_tmp_disk_tables| 0 | | Created_tmp_tables | 56631 | | Created_tmp_files | 2133 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 1 | | Handler_commit | 106883 | | Handler_delete | 1268 | | Handler_read_first | 13902 | | Handler_read_key | 3619254984 | | Handler_read_next | 3479415584 | | Handler_read_prev | 0 | | Handler_read_rnd | 7278832| | Handler_read_rnd_next | 756152091 | | Handler_rollback | 7624 | | Handler_update | 88733 | | Handler_write | 218257589 | | Key_blocks_used| 125| | Key_read_requests
Re: Strange behavior
Hello. I don't have any ideas at least now. But additional information could be helpful. Do you connect from JBoss to the slave or master server? Please use SHOW PROCESSLIST to find in what state the server threads waste their time. If you find something interesting send it. Include also the output of SHOW STATUS and SHOW VARIABLES. Rafal Kedziorski [EMAIL PROTECTED] wrote: Hi, after extending our MySQL 4.0.23a installation to master-slave configuration two specific queries sended from our JBoss are 25-30 times slower. In our J2EE application which runs under JBoss 3.2.2 we are generating own queries by using a connection from JBoss connection pool. This are prepared statements: 1. select count(distinct m.media_id) from category_tree c_tree, media_2_category m2c, media m, magix_product mp, media_type_2_magix_product mt2mp, media_file mf where c_tree.mandant_id = ? and c_tree.partner_id = ? and c_tree.category_tree_id = m2c.category_tree_id and m2c.media_id = m.media_id and mp.magix_product_id = ? and mp.magix_product_id = mt2mp.magix_product_id and m.media_type_id = mt2mp.media_type_id and mf.media_id = m.media_id and (mf.language_id = ? or mf.language_id is null) and mf.media_file_quality_id = ? and (c_tree.category_tree_id = ? or c_tree.parent_id = ? or c_tree.path like ?) 2. select distinct m.media_id from category_tree c_tree, media_2_category m2c, media m, media_2_partner m2p, magix_product mp, media_type_2_magix_product mt2mp, media_file mf where c_tree.mandant_id = ? and c_tree.partner_id = ? and c_tree.category_tree_id = m2c.category_tree_id and m2c.media_id = m.media_id and m2p.media_id = m.media_id and m2p.partner_id = ? and mp.magix_product_id = ? and mp.magix_product_id = mt2mp.magix_product_id and m.media_type_id = mt2mp.media_type_id and mf.media_id = m.media_id and (mf.language_id = ? or mf.language_id is null) and mf.media_file_quality_id = ? and (c_tree.category_tree_id = ? or c_tree.parent_id = ? or c_tree.path like ?) order by m2p.priority desc limit ?, ? Times: 1. - needed from JBoss 450-500 millis - nedded from normal Java application 15-25 millis 2. needed from JBoss 500-800 millis - nedded from normal Java application 19 millis - nedded from normal Java application 20-30 millis All other sql statements generated by JBoss for entity beans are fast like bevore switching to master-slave configuration. Thru this queries the speed of our service is 2-3 times slower. After spend some hours checking our system, I have no more idea where is the problem. This is our my.cnf: [mysqld] datadir=/drbd/mysql log-bin server-id=20 set-variable= key_buffer=128M set-variable= table_cache=512 set-variable= sort_buffer=8M set-variable= join_buffer_size=8M set-variable= query_cache_size=32M set-variable= record_buffer=4M set-variable= thread_cache_size=400 set-variable= max_connections=300 set-variable= long_query_time=10 log_long_format log_slow_queries innodb_data_file_path = ibdata1:2048M;ibdata2:10M:autoextend #innodb_buffer_pool_size = 384M innodb_buffer_pool_size = 1228M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 Regards, Rafal -- 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]
Strange behavior
Hi, after extending our MySQL 4.0.23a installation to master-slave configuration two specific queries sended from our JBoss are 25-30 times slower. In our J2EE application which runs under JBoss 3.2.2 we are generating own queries by using a connection from JBoss connection pool. This are prepared statements: 1. select count(distinct m.media_id) from category_tree c_tree, media_2_category m2c, media m, magix_product mp, media_type_2_magix_product mt2mp, media_file mf where c_tree.mandant_id = ? and c_tree.partner_id = ? and c_tree.category_tree_id = m2c.category_tree_id and m2c.media_id = m.media_id and mp.magix_product_id = ? and mp.magix_product_id = mt2mp.magix_product_id and m.media_type_id = mt2mp.media_type_id and mf.media_id = m.media_id and (mf.language_id = ? or mf.language_id is null) and mf.media_file_quality_id = ? and (c_tree.category_tree_id = ? or c_tree.parent_id = ? or c_tree.path like ?) 2. select distinct m.media_id from category_tree c_tree, media_2_category m2c, media m, media_2_partner m2p, magix_product mp, media_type_2_magix_product mt2mp, media_file mf where c_tree.mandant_id = ? and c_tree.partner_id = ? and c_tree.category_tree_id = m2c.category_tree_id and m2c.media_id = m.media_id and m2p.media_id = m.media_id and m2p.partner_id = ? and mp.magix_product_id = ? and mp.magix_product_id = mt2mp.magix_product_id and m.media_type_id = mt2mp.media_type_id and mf.media_id = m.media_id and (mf.language_id = ? or mf.language_id is null) and mf.media_file_quality_id = ? and (c_tree.category_tree_id = ? or c_tree.parent_id = ? or c_tree.path like ?) order by m2p.priority desc limit ?, ? Times: 1. - needed from JBoss 450-500 millis - nedded from normal Java application 15-25 millis 2. needed from JBoss 500-800 millis - nedded from normal Java application 19 millis - nedded from normal Java application 20-30 millis All other sql statements generated by JBoss for entity beans are fast like bevore switching to master-slave configuration. Thru this queries the speed of our service is 2-3 times slower. After spend some hours checking our system, I have no more idea where is the problem. This is our my.cnf: [mysqld] datadir=/drbd/mysql log-bin server-id=20 set-variable= key_buffer=128M set-variable= table_cache=512 set-variable= sort_buffer=8M set-variable= join_buffer_size=8M set-variable= query_cache_size=32M set-variable= record_buffer=4M set-variable= thread_cache_size=400 set-variable= max_connections=300 set-variable= long_query_time=10 log_long_format log_slow_queries innodb_data_file_path = ibdata1:2048M;ibdata2:10M:autoextend #innodb_buffer_pool_size = 384M innodb_buffer_pool_size = 1228M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 Regards, Rafal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: strange behavior in foreign keys
Also, suggest you read http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html and in particular look to see if you're getting error 1005 or 105 returned. That was the purpose of my original question to you. Bob -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Friday, May 28, 2004 11:42 AM To: [EMAIL PROTECTED] Subject: Re: strange behaviour in foreign keys [EMAIL PROTECTED] wrote: I've been running MySQL 4.0.x on a RH9 machine for some months and everything was fine. I could create tables of type InnoDB and define FOREIGN KEY's all was well. A few days ago it does not let me create tables with foreign keys anymore it says something like unable to create /databasename/tablename... If I remove the FOREIGN KEY constraint it accepts de table creation. You have incorrect foreign key definition. Use SHOW INNODB STATUS command to see more detailed error message. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: strange behavior in foreign keys
Victoria and Robert Thank you for your help. I think it is solved. The error was (as Robert said) ERROR 1005: Can't create table './alex/jmf33.frm' (errno: 150) I aslo checked SHOW INNODB STATUS where I was redirected to http://www.innodb.com/ibman.html There I found a link to a document saying that I must create INDEXes for FOREIGN KEYs to work properly. I tried the example in that document and it works. Thanks again. Any explanation why I never bumped into this before ? jmf __ O email preferido dos portugueses agora com 100Mb de espaço e acesso gratuito à Internet http://www.portugalmail.pt/servicos/email/gold/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User variables + SUM + GROUP BY = strange behavior
well, it seems to be fine without SUM and GROUP BY... E.g., SELECT @a:=Charge, @b:=Cost, @[EMAIL PROTECTED] as Margin ... produces expected results. Emmett Bishop wrote: Vadim, if I'm not mistaken, you can't set a variable then use it in the same statement. See http://dev.mysql.com/doc/mysql/en/Variables.html A little ways down the page... The general rule is to never assign and use the same variable in the same statement. -- Tripp --- Vadim P. [EMAIL PROTECTED] wrote: Sorry, the message got garbled, here is a more digestible look: -Original Message- Hello all, Could anyone comment on User Variable behavior in the example below? Thanks, Vadim. = mysql SELECT -LEFT(CallTime,10) AS CallDate, -@a := SUM(Charge), -@b := SUM(Cost), -@a - @b, -@a, -@b - FROM Calls - GROUP by CallDate - ORDER BY CallDate DESC; ++--++-++- | CallDate | @a:= SUM(Charge) | @b:= SUM(Cost) | @a - @b | @a | @b ++--++-++- ... | 2004-03-01 | 621.059 |249.310 | 30.882 | 39.512 | 8.63 | 2004-02-29 | 54.620 | 17.660 | 30.882 | 39.512 | 8.63 | 2004-02-28 | 205.581 | 17.460 | 30.882 | 39.512 | 8.63 | 2004-02-27 | 622.282 |248.920 | 30.882 | 39.512 | 8.63 | 2004-02-26 | 607.274 |277.100 | 30.882 | 39.512 | 8.63 | 2004-02-25 | 709.698 |308.580 | 30.882 | 39.512 | 8.63 | 2004-02-24 | 783.210 |298.560 | 30.882 | 39.512 | 8.63 | 2004-02-23 | 799.764 |252.890 | 30.882 | 39.512 | 8.63 ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User variables + SUM + GROUP BY = strange behavior
Not sure on how exactly variables work in MySQL but I do know that according to ANSI SQL group bys are done before other things in the query. So your query would perform the group by then it would do the actual select. This could be one reason for strange results. Thanks, Andrew From: Vadim P. [EMAIL PROTECTED] To: Emmett Bishop [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: User variables + SUM + GROUP BY = strange behavior Date: Fri, 16 Apr 2004 05:50:12 -0400 well, it seems to be fine without SUM and GROUP BY... E.g., SELECT @a:=Charge, @b:=Cost, @[EMAIL PROTECTED] as Margin ... produces expected results. Emmett Bishop wrote: Vadim, if I'm not mistaken, you can't set a variable then use it in the same statement. See http://dev.mysql.com/doc/mysql/en/Variables.html A little ways down the page... The general rule is to never assign and use the same variable in the same statement. -- Tripp --- Vadim P. [EMAIL PROTECTED] wrote: Sorry, the message got garbled, here is a more digestible look: -Original Message- Hello all, Could anyone comment on User Variable behavior in the example below? Thanks, Vadim. = mysql SELECT -LEFT(CallTime,10) AS CallDate, -@a := SUM(Charge), -@b := SUM(Cost), -@a - @b, -@a, -@b - FROM Calls - GROUP by CallDate - ORDER BY CallDate DESC; ++--++-++- | CallDate | @a:= SUM(Charge) | @b:= SUM(Cost) | @a - @b | @a | @b ++--++-++- ... | 2004-03-01 | 621.059 |249.310 | 30.882 | 39.512 | 8.63 | 2004-02-29 | 54.620 | 17.660 | 30.882 | 39.512 | 8.63 | 2004-02-28 | 205.581 | 17.460 | 30.882 | 39.512 | 8.63 | 2004-02-27 | 622.282 |248.920 | 30.882 | 39.512 | 8.63 | 2004-02-26 | 607.274 |277.100 | 30.882 | 39.512 | 8.63 | 2004-02-25 | 709.698 |308.580 | 30.882 | 39.512 | 8.63 | 2004-02-24 | 783.210 |298.560 | 30.882 | 39.512 | 8.63 | 2004-02-23 | 799.764 |252.890 | 30.882 | 39.512 | 8.63 ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Get rid of annoying pop-up ads with the new MSN Toolbar FREE! http://toolbar.msn.com/go/onm00200414ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User variables + SUM + GROUP BY = strange behavior
Hello all, Could anyone comment on User Variable behavior in the example below? Thanks, Vadim. = mysql SELECT - LEFT(CallTime,10) AS CallDate, - @a := SUM(Charge), - @b := SUM(Cost), - @a - @b, - @a, - @b - FROM Calls - GROUP by CallDate - ORDER BY CallDate DESC; ++---+-+-++- -+ | CallDate | @a := SUM(Charge) | @b := SUM(Cost) | @a - @b | @a | @b | ++---+-+-++- -+ . | 2004-03-01 | 621.059 | 249.310 | 30.882 | 39.512 | 8.63 | | 2004-02-29 |54.620 | 17.660 | 30.882 | 39.512 | 8.63 | | 2004-02-28 | 205.581 | 17.460 | 30.882 | 39.512 | 8.63 | | 2004-02-27 | 622.282 | 248.920 | 30.882 | 39.512 | 8.63 | | 2004-02-26 | 607.274 | 277.100 | 30.882 | 39.512 | 8.63 | | 2004-02-25 | 709.698 | 308.580 | 30.882 | 39.512 | 8.63 | | 2004-02-24 | 783.210 | 298.560 | 30.882 | 39.512 | 8.63 | | 2004-02-23 | 799.764 | 252.890 | 30.882 | 39.512 | 8.63 | .
User variables + SUM + GROUP BY = strange behavior
Sorry, the message got garbled, here is a more digestible look: -Original Message- Hello all, Could anyone comment on User Variable behavior in the example below? Thanks, Vadim. = mysql SELECT - LEFT(CallTime,10) AS CallDate, - @a := SUM(Charge), - @b := SUM(Cost), - @a - @b, - @a, - @b - FROM Calls - GROUP by CallDate - ORDER BY CallDate DESC; ++--++-++- | CallDate | @a:= SUM(Charge) | @b:= SUM(Cost) | @a - @b | @a | @b ++--++-++- ... | 2004-03-01 | 621.059 |249.310 | 30.882 | 39.512 | 8.63 | 2004-02-29 | 54.620 | 17.660 | 30.882 | 39.512 | 8.63 | 2004-02-28 | 205.581 | 17.460 | 30.882 | 39.512 | 8.63 | 2004-02-27 | 622.282 |248.920 | 30.882 | 39.512 | 8.63 | 2004-02-26 | 607.274 |277.100 | 30.882 | 39.512 | 8.63 | 2004-02-25 | 709.698 |308.580 | 30.882 | 39.512 | 8.63 | 2004-02-24 | 783.210 |298.560 | 30.882 | 39.512 | 8.63 | 2004-02-23 | 799.764 |252.890 | 30.882 | 39.512 | 8.63 ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User variables + SUM + GROUP BY = strange behavior
Vadim, if I'm not mistaken, you can't set a variable then use it in the same statement. See http://dev.mysql.com/doc/mysql/en/Variables.html A little ways down the page... The general rule is to never assign and use the same variable in the same statement. -- Tripp --- Vadim P. [EMAIL PROTECTED] wrote: Sorry, the message got garbled, here is a more digestible look: -Original Message- Hello all, Could anyone comment on User Variable behavior in the example below? Thanks, Vadim. = mysql SELECT -LEFT(CallTime,10) AS CallDate, -@a := SUM(Charge), -@b := SUM(Cost), -@a - @b, -@a, -@b - FROM Calls - GROUP by CallDate - ORDER BY CallDate DESC; ++--++-++- | CallDate | @a:= SUM(Charge) | @b:= SUM(Cost) | @a - @b | @a | @b ++--++-++- ... | 2004-03-01 | 621.059 |249.310 | 30.882 | 39.512 | 8.63 | 2004-02-29 | 54.620 | 17.660 | 30.882 | 39.512 | 8.63 | 2004-02-28 | 205.581 | 17.460 | 30.882 | 39.512 | 8.63 | 2004-02-27 | 622.282 |248.920 | 30.882 | 39.512 | 8.63 | 2004-02-26 | 607.274 |277.100 | 30.882 | 39.512 | 8.63 | 2004-02-25 | 709.698 |308.580 | 30.882 | 39.512 | 8.63 | 2004-02-24 | 783.210 |298.560 | 30.882 | 39.512 | 8.63 | 2004-02-23 | 799.764 |252.890 | 30.882 | 39.512 | 8.63 ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange behavior, Table Level Permission
Dear Victoria Reznichenko, Thanks for reply. Could you tell me from where I can get patch to fix this bug. or I have to use Mysql 4.0/Mysql 5.0 with regards, Udbhav Shah = Thinking is the Assets Enterprise is the Way Hard Work is the Solution - Ignited Minds __ Do you Yahoo!? Yahoo! Finance Tax Center - File online. File on time. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior, Table Level Permission
Udbhav Shah [EMAIL PROTECTED] wrote: Dear Victoria Reznichenko, Thanks for reply. Could you tell me from where I can get patch to fix this bug. or I have to use Mysql 4.0/Mysql 5.0 You can install latest available version, including the bugfix, from the development source tree: http://www.mysql.com/doc/en/Installing_source_tree.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior, Table Level Permission
Udbhav Shah [EMAIL PROTECTED] wrote: Using Mysql 4.1.1-alpha release on RH9, I have used RPM provided on Mysql site to upgrade from 3.23 to 4.1.1 I have a very strange behavior of mysql server,when I restart my server, it is not reading permission given to user at Table Level from tables_priv. Permission are still there, but when I use show grants for ... it show that no privileges. Commands used: grant all on try.TBL_MACIP to 'tryAdmin1'@'localhost'; /etc/init.d/mysqld restart mysql -utryAdmin1 mysqluse try access denied Does anyone know how to remove such unexpected behavior, because this was perfectly working in 3.23 Thank you for report. It's a known bug in version 4.1.1 and it's already fixed: http://bugs.mysql.com/bug.php?id=2546 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior, Table Level Permission
Will be fixed in 4.1.2? When can we expect that to be out? Anyone with an idea? (Also facing this problem here) - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 22, 2004 3:57 PM Subject: Re: Strange behavior, Table Level Permission Udbhav Shah [EMAIL PROTECTED] wrote: Using Mysql 4.1.1-alpha release on RH9, I have used RPM provided on Mysql site to upgrade from 3.23 to 4.1.1 I have a very strange behavior of mysql server,when I restart my server, it is not reading permission given to user at Table Level from tables_priv. Permission are still there, but when I use show grants for ... it show that no privileges. Commands used: grant all on try.TBL_MACIP to 'tryAdmin1'@'localhost'; /etc/init.d/mysqld restart mysql -utryAdmin1 mysqluse try access denied Does anyone know how to remove such unexpected behavior, because this was perfectly working in 3.23 Thank you for report. It's a known bug in version 4.1.1 and it's already fixed: http://bugs.mysql.com/bug.php?id=2546 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior, Table Level Permission
Terence [EMAIL PROTECTED] wrote: Will be fixed in 4.1.2? Yes, it's fixed in 4.1.2. When can we expect that to be out? Anyone with an idea? Soon. Probably in two weeks. - Original Message --- -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange behavior, Table Level Permission
Hello Everyone, Using Mysql 4.1.1-alpha release on RH9, I have used RPM provided on Mysql site to upgrade from 3.23 to 4.1.1 I have a very strange behavior of mysql server,when I restart my server, it is not reading permission given to user at Table Level from tables_priv. Permission are still there, but when I use show grants for ... it show that no privileges. Commands used: grant all on try.TBL_MACIP to 'tryAdmin1'@'localhost'; /etc/init.d/mysqld restart mysql -utryAdmin1 mysqluse try access denied Does anyone know how to remove such unexpected behavior, because this was perfectly working in 3.23 with regards, Udbhav Shah India = Thinking is the Assets Enterprise is the Way Hard Work is the Solution - Ignited Minds __ Do you Yahoo!? Yahoo! Finance Tax Center - File online. File on time. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored procedure strange behavior?
Philip Markwalder [EMAIL PROTECTED] wrote: I have a few questions concerning stored procedures: 1. If I create a stored procedure (like the one below), why does the returned values not change, though in the stored prcoedure the id has been generated? 2. Is there any better way to hand over multiple values and how can I unset global varaibles? thx Philip delimiter | drop procedure if exists create_obj | CREATE PROCEDURE `create_obj` ( out success int(2), out success_msg varchar(255), out obj_id int(10), inout obj_hostname varchar(255), inout obj_type varchar(25) ) LANGUAGE SQL not deterministic begin declare done int default 0; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; insert into idsdb.obj values (NULL,obj_hostname,obj_type); if ! done then select LAST_INSERT_ID() into obj_id; set success = 1; set success_msg = concat(added host with object id: , obj_id); else set success = -1; set success_msg=Could not insert new object; end if; end | call create_obj(@a,@b,@id,'test1','ddd')| select @a,@b,@id | LAST_INSERT_ID() returns wrong result inside stored procedure. I entered simple test case to the bug database: http://bugs.mysql.com/bug.php?id=3117 Thanks! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored procedure strange behavior?
Hi I have a few questions concerning stored procedures: 1. If I create a stored procedure (like the one below), why does the returned values not change, though in the stored prcoedure the id has been generated? 2. Is there any better way to hand over multiple values and how can I unset global varaibles? thx Philip delimiter | drop procedure if exists create_obj | CREATE PROCEDURE `create_obj` ( out success int(2), out success_msg varchar(255), out obj_id int(10), inout obj_hostname varchar(255), inout obj_type varchar(25) ) LANGUAGE SQL not deterministic begin declare done int default 0; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; insert into idsdb.obj values (NULL,obj_hostname,obj_type); if ! done then select LAST_INSERT_ID() into obj_id; set success = 1; set success_msg = concat(added host with object id: , obj_id); else set success = -1; set success_msg=Could not insert new object; end if; end | call create_obj(@a,@b,@id,'test1','ddd')| select @a,@b,@id | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange behavior with IF?
Hi, Can someone tell me why this query doesn't work? SELECT IF(ISNULL(network.level), 4, network.level) AS level, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) ORDER BY member.last_login DESC LIMIT 0,3 +---+-+ | level | photo_level | +---+-+ | 4 | 4 | | 4 | 4 | | 4 | 4 | +---+-+ 3 rows in set (0.01 sec) Then, when I add WHERE. SELECT IF(ISNULL(network.level), 4, network.level) AS level, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) WHERE level = member.photo_level ORDER BY member.last_login DESC LIMIT 0,3 Empty set (0.00 sec) I think it is supposed to return all rows, since all level is the same as photo_level, but why does it return empty set? --Batara -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior with IF?
Batara Kesuma [EMAIL PROTECTED] wrote: Can someone tell me why this query doesn't work? SELECT IF(ISNULL(network.level), 4, network.level) AS level, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) ORDER BY member.last_login DESC LIMIT 0,3 +---+-+ | level | photo_level | +---+-+ | 4 | 4 | | 4 | 4 | | 4 | 4 | +---+-+ 3 rows in set (0.01 sec) Then, when I add WHERE. SELECT IF(ISNULL(network.level), 4, network.level) AS level, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) WHERE level = member.photo_level ORDER BY member.last_login DESC LIMIT 0,3 Empty set (0.00 sec) I think it is supposed to return all rows, since all level is the same as photo_level, but why does it return empty set? You can't refer to the column alias in the WHERE clause. Use HAVING clause instead. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior with IF?
On Mon, 2004-02-16 at 09:07, Batara Kesuma wrote: Hi, Can someone tell me why this query doesn't work? SELECT IF(ISNULL(network.level), 4, network.level) AS level, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) ORDER BY member.last_login DESC LIMIT 0,3 +---+-+ | level | photo_level | +---+-+ | 4 | 4 | | 4 | 4 | | 4 | 4 | +---+-+ 3 rows in set (0.01 sec) Then, when I add WHERE. SELECT IF(ISNULL(network.level), 4, network.level) AS level, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) WHERE level = member.photo_level ORDER BY member.last_login DESC LIMIT 0,3 Empty set (0.00 sec) I think it is supposed to return all rows, since all level is the same as photo_level, but why does it return empty set? Conditions with fields from the table in the LEFT JOIN side (in this case, network) should be in the ON clause, not in the WHERE clause. Check the manual about using LEFT JOIN and try: SELECT IF(ISNULL(network.level), 4, network.level) AS level, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id AND network.level = member.photo_level) ORDER BY member.last_login DESC LIMIT 0,3 -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior with IF?
Hi Diana, SELECT IF(ISNULL(network.level), 4, network.level) AS level, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) ORDER BY member.last_login DESC LIMIT 0,3 +---+-+ | level | photo_level | +---+-+ | 4 | 4 | | 4 | 4 | | 4 | 4 | +---+-+ 3 rows in set (0.01 sec) Then, when I add WHERE. SELECT IF(ISNULL(network.level), 4, network.level) AS level, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) WHERE level = member.photo_level ORDER BY member.last_login DESC LIMIT 0,3 Empty set (0.00 sec) I think it is supposed to return all rows, since all level is the same as photo_level, but why does it return empty set? Conditions with fields from the table in the LEFT JOIN side (in this case, network) should be in the ON clause, not in the WHERE clause. Check the manual about using LEFT JOIN and try: SELECT IF(ISNULL(network.level), 4, network.level) AS level, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id AND network.level = member.photo_level) ORDER BY member.last_login DESC LIMIT 0,3 Thank you for the reply. I have it fixed, the problem is I should have used HAVING instead of WHERE and I couldn't use AS level, because HAVING will confuse it with network.level which is NULL. So here is the working query. SELECT IF(ISNULL(network.level), 4, network.level) AS level_alias, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) HAVING level_alias = member.photo_level ORDER BY member.last_login DESC LIMIT 0,3 Regards, Batara -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange behavior on insert
I have a PHP page that takes data from a form and inserts it into a
table:
Show columns:
+++--+-+-+--
--+
| Field | Type | Null | Key | Default | Extra
|
+++--+-+-+--
--+
| ID | mediumint(10) | | PRI | NULL|
auto_increment |
| userid | varchar(20) binary | | | |
|
| name | varchar(20) binary | | | |
|
| tier | int(2) | | | 0 |
|
| price | double | | | 0 |
|
| tierNumber | int(2) | | | 0 |
|
| Min| double | | | 0 |
|
+++--+-+-+--
--+
I have a while loop that insterts the records in the correct order (by
tierNumber).
INSERT INTO TarifBuilder SET
name='{$_POST'name']}',tier='$tier',price='$price',tierNumber='$count',M
in='{$_POST'min']}';
However when I go to the database and do a select * from tablename;
the records are in the table in the reverse order!!
Even the auto increment is in reverse order...
| 65 || 2-gaf | 0 | 0.0004688 | 0 | 0.3
|
| 66 || 2-gaf | 1536 | 0.0002917 | 1 | 0.3
|
| 67 || 2-gaf | 6144 | 0.0002344 | 2 | 0.3
|
| 68 || 2-gaf | 15360 | 0.0001172 | 3 | 0.3
|
| 69 || 3-gaf | 0 | 0.0001172 | 0 |0
|
| 70 || 3-gaf | 0 | 0.0001172 | 1 |0
|
| 122 || test-decreasing | 200 | 1.001e-05 | 2 | 0.51
|
| 121 || test-decreasing | 100 | 2.002e-05 | 1 | 0.51
|
| 120 || test-decreasing | 0 | 3.003e-05 | 0 | 0.51
|
+-++-+---+---++-
-+
Why is this?
Thanks,
Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior on insert
On 20 Nov 2003 at 11:12, Jeff McKeon wrote: However when I go to the database and do a select * from tablename; the records are in the table in the reverse order!! Even the auto increment is in reverse order... If you wish to retrieve the data in a particular order you must use order by because the database itself does not depend on a sequential storage of the records. Dan Wilterding [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Strange behavior on insert
I understand how to use the Order By clause on a select, I'm trying to better understand why does this happen on the insert. Jeff -Original Message- From: Dan Wilterding [mailto:[EMAIL PROTECTED] Sent: Thursday, November 20, 2003 11:39 AM To: [EMAIL PROTECTED] Subject: Re: Strange behavior on insert On 20 Nov 2003 at 11:12, Jeff McKeon wrote: However when I go to the database and do a select * from tablename; the records are in the table in the reverse order!! Even the auto increment is in reverse order... If you wish to retrieve the data in a particular order you must use order by because the database itself does not depend on a sequential storage of the records. Dan Wilterding [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Strange behavior on insert
As far as I know, DELETE's make gaps in the table (you could remove these by optimizing). If you INSERT into a table with gaps, your INSERTed row will try to fill the gaps created by that. Maybe it works backwards in filling the gaps? -Original Message- From: Jeff McKeon [mailto:[EMAIL PROTECTED] Sent: Thursday, November 20, 2003 8:44 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Strange behavior on insert I understand how to use the Order By clause on a select, I'm trying to better understand why does this happen on the insert. Jeff -Original Message- From: Dan Wilterding [mailto:[EMAIL PROTECTED] Sent: Thursday, November 20, 2003 11:39 AM To: [EMAIL PROTECTED] Subject: Re: Strange behavior on insert On 20 Nov 2003 at 11:12, Jeff McKeon wrote: However when I go to the database and do a select * from tablename; the records are in the table in the reverse order!! Even the auto increment is in reverse order... If you wish to retrieve the data in a particular order you must use order by because the database itself does not depend on a sequential storage of the records. Dan Wilterding [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [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: Strange behavior of group by column1 having column2 = max (column2)
Roger, Thanks for your reply. Here's the next step: I've added a date column to my table as follows: ++---+--++ | id | value | type | date | ++---+--++ | 1 | 6 | a| 2002-09-08 | | 2 | 2 | b| 2003-10-01 | | 3 | 5 | b| 2001-02-18 | | 4 | 4 | a| 1999-11-30 | | 5 | 1 | c| 2000-03-12 | | 6 |10 | d| 1998-07-11 | | 7 | 7 | c| 2002-09-15 | | 8 | 3 | d| 2003-05-28 | ++---+--++ Now I'd like to get the min value for the type, and the date where the min value occurred. So I tried the following: select min(value), type, date from A group by type; ++--++ | min(value) | type | date | ++--++ | 4 | a| 2002-09-08 | | 2 | b| 2003-10-01 | | 1 | c| 2000-03-12 | | 3 | d| 1998-07-11 | ++--++ You can see that the min value is correct, but the date is just the first date found for the type on the table, which is not the intended result. Similarly for the max -- correct max values, first date found on the table for each type. Any suggestions ? * Ana Holzbach I've tried this on MySQL 4.0.15 and 4.1.0-alpha, with the same result. I have the following table A: ++---+--+ | id | value | type | ++---+--+ | 1 | 6 | a| | 2 | 2 | b| | 3 | 5 | b| | 4 | 4 | a| | 5 | 1 | c| | 6 |10 | d| | 7 | 7 | c| | 8 | 3 | d| ++---+--+ I would like to find the max of value for each type, and the min of value for each type. For this, I ran the following two queries (I'm not sure they are well formulated for my goal): select id, value, type from A group by type having value = max(value); ++---+--+ | id | value | type | ++---+--+ | 1 | 6 | a| | 6 |10 | d| ++---+--+ 2 rows in set (0.00 sec) select id, value, type from A group by type having value = min(value); ++---+--+ | id | value | type | ++---+--+ | 2 | 2 | b| | 5 | 1 | c| ++---+--+ 2 rows in set (0.00 sec) In each case, the order in the table seems to matter: if the max value is found first, it's returned in the search for max query. Same goes for min. For example, for type a, the maximum value comes first in the table, so it's returned in the search for max query. Again for case a, the minimum value comes second in the table, so it's not found in the search for min query This seems odd. Can anyone tell me what I'm missing ? You are using a special form of the GROUP BY, not standard but accepted by mysql. Try changing it to a more standard form, and you can combine both queries in one: SELECT type,MIN(value),MAX(value) FROM A GROUP BY type -- Roger = Ana Holzbach [EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior of group by column1 having column2 = max (column2)
* Ana Holzbach Thanks for your reply. Here's the next step: I've added a date column to my table as follows: ++---+--++ | id | value | type | date | ++---+--++ | 1 | 6 | a| 2002-09-08 | | 2 | 2 | b| 2003-10-01 | | 3 | 5 | b| 2001-02-18 | | 4 | 4 | a| 1999-11-30 | | 5 | 1 | c| 2000-03-12 | | 6 |10 | d| 1998-07-11 | | 7 | 7 | c| 2002-09-15 | | 8 | 3 | d| 2003-05-28 | ++---+--++ Now I'd like to get the min value for the type, and the date where the min value occurred. So I tried the following: select min(value), type, date from A group by type; ++--++ | min(value) | type | date | ++--++ | 4 | a| 2002-09-08 | | 2 | b| 2003-10-01 | | 1 | c| 2000-03-12 | | 3 | d| 1998-07-11 | ++--++ You can see that the min value is correct, but the date is just the first date found for the type on the table, which is not the intended result. Similarly for the max -- correct max values, first date found on the table for each type. Any suggestions ? See the MAX-CONCAT trick: URL: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html It works with MIN() too, of course: select min(concat(lpad(value,6,'0'),'-',date)), type from A group by type -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior of group by column1 having column2 = max (column2)
Hi Roger, Thanks for the pointer. CONCAT would be a nightmare to maintain, especially with data where the values can have all sorts of ranges, and where we could just as well be looking for the value corresponding to the latest (or earliest) date instead. It would just create too many cases to pad, parse, etc. However, I tried the substring on 4.1 and it's a reasonable alternative. Fortunately, by the time we need this functionality 4.1 will probably be stable. Ana --- Roger Baklund [EMAIL PROTECTED] wrote: * Ana Holzbach Thanks for your reply. Here's the next step: I've added a date column to my table as follows: ++---+--++ | id | value | type | date | ++---+--++ | 1 | 6 | a| 2002-09-08 | | 2 | 2 | b| 2003-10-01 | | 3 | 5 | b| 2001-02-18 | | 4 | 4 | a| 1999-11-30 | | 5 | 1 | c| 2000-03-12 | | 6 |10 | d| 1998-07-11 | | 7 | 7 | c| 2002-09-15 | | 8 | 3 | d| 2003-05-28 | ++---+--++ Now I'd like to get the min value for the type, and the date where the min value occurred. So I tried the following: select min(value), type, date from A group by type; ++--++ | min(value) | type | date | ++--++ | 4 | a| 2002-09-08 | | 2 | b| 2003-10-01 | | 1 | c| 2000-03-12 | | 3 | d| 1998-07-11 | ++--++ You can see that the min value is correct, but the date is just the first date found for the type on the table, which is not the intended result. Similarly for the max -- correct max values, first date found on the table for each type. Any suggestions ? See the MAX-CONCAT trick: URL: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html It works with MIN() too, of course: select min(concat(lpad(value,6,'0'),'-',date)), type from A group by type -- Roger = Ana Holzbach [EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior of group by column1 having column2 = max (column2)
Sorry, I meant to say I've tried the subquery, not the substring, on 4.1, and that's probably what we'll use in the future. select date, value, type from A a1 where value = (select max(a2.value) from A a2 where a1.type = a2.type); Ana --- Ana Holzbach [EMAIL PROTECTED] wrote: Hi Roger, Thanks for the pointer. CONCAT would be a nightmare to maintain, especially with data where the values can have all sorts of ranges, and where we could just as well be looking for the value corresponding to the latest (or earliest) date instead. It would just create too many cases to pad, parse, etc. However, I tried the substring on 4.1 and it's a reasonable alternative. Fortunately, by the time we need this functionality 4.1 will probably be stable. Ana = Ana Holzbach [EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange behavior of group by column1 having column2 = max (column2)
I've tried this on MySQL 4.0.15 and 4.1.0-alpha, with the same result. I have the following table A: ++---+--+ | id | value | type | ++---+--+ | 1 | 6 | a| | 2 | 2 | b| | 3 | 5 | b| | 4 | 4 | a| | 5 | 1 | c| | 6 |10 | d| | 7 | 7 | c| | 8 | 3 | d| ++---+--+ I would like to find the max of value for each type, and the min of value for each type. For this, I ran the following two queries (I'm not sure they are well formulated for my goal): select id, value, type from A group by type having value = max(value); ++---+--+ | id | value | type | ++---+--+ | 1 | 6 | a| | 6 |10 | d| ++---+--+ 2 rows in set (0.00 sec) select id, value, type from A group by type having value = min(value); ++---+--+ | id | value | type | ++---+--+ | 2 | 2 | b| | 5 | 1 | c| ++---+--+ 2 rows in set (0.00 sec) In each case, the order in the table seems to matter: if the max value is found first, it's returned in the search for max query. Same goes for min. For example, for type a, the maximum value comes first in the table, so it's returned in the search for max query. Again for case a, the minimum value comes second in the table, so it's not found in the search for min query This seems odd. Can anyone tell me what I'm missing ? Thanks, Ana = Ana Holzbach [EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior of group by column1 having column2 = max (column2)
* Ana Holzbach I've tried this on MySQL 4.0.15 and 4.1.0-alpha, with the same result. I have the following table A: ++---+--+ | id | value | type | ++---+--+ | 1 | 6 | a| | 2 | 2 | b| | 3 | 5 | b| | 4 | 4 | a| | 5 | 1 | c| | 6 |10 | d| | 7 | 7 | c| | 8 | 3 | d| ++---+--+ I would like to find the max of value for each type, and the min of value for each type. For this, I ran the following two queries (I'm not sure they are well formulated for my goal): select id, value, type from A group by type having value = max(value); ++---+--+ | id | value | type | ++---+--+ | 1 | 6 | a| | 6 |10 | d| ++---+--+ 2 rows in set (0.00 sec) select id, value, type from A group by type having value = min(value); ++---+--+ | id | value | type | ++---+--+ | 2 | 2 | b| | 5 | 1 | c| ++---+--+ 2 rows in set (0.00 sec) In each case, the order in the table seems to matter: if the max value is found first, it's returned in the search for max query. Same goes for min. For example, for type a, the maximum value comes first in the table, so it's returned in the search for max query. Again for case a, the minimum value comes second in the table, so it's not found in the search for min query This seems odd. Can anyone tell me what I'm missing ? You are using a special form of the GROUP BY, not standard but accepted by mysql. Try changing it to a more standard form, and you can combine both queries in one: SELECT type,MIN(value),MAX(value) FROM A GROUP BY type -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange behavior -- user variables in 4.0.14b
I get the following strange behavior with a user variable. @T has the value 0 to start; after adding 1 to @T a few times, it ends up with a clearly incorrect value. I'd expect it to have a value of 280 after the second select. -- SELECT @T -- +--+ | @T | +--+ | 0| +--+ 1 row in set (0.00 sec) -- UPDATE contown_svr SET contown_id=(@T:[EMAIL PROTECTED]) -- Query OK, 280 rows affected (0.05 sec) Rows matched: 280 Changed: 280 Warnings: 280 -- SELECT @T -- +--+ | @T | +--+ | 1.8669204411349e+021 | +--+ 1 row in set (0.00 sec) --- More data: I'm using Windows 2000 with MySQL 4.0.14b, and connected through localhost. It appears to work correctly on MySQL 4.0.13 and MySQL 3.23.57. I also tried it on Linux with MySQL 4.0.13, and it worked correctly. It continues to fail with the 3.23.57 client and the 4.0.14b server. I get various values for @T, sometimes with a negative exponent. Sometimes it gives the correct value once, then twice the correct value on the second try, etc., despite @T being reset to zero. Sometimes, when I select the values in contown_svr, contown_id (which is an int) prints as something like 561.1. This happens when I have the mysql client read a file. When I cut and paste the content of the file to console, it appears to give the correct result. Any help would be appreciated. It sure sounds to me like a bug in thread synchronization within the server. Here's the smallest program I've gotten to fail. It still fails (gives wrong value to @T) even if the select returns 0 rows, but it doesn't fail if I remove the insert...select. I'll try to cut it down some more and post an example that's not missing the data--but it may take a while to get to it. - select @t:=0; drop table if exists contown_svr; create table contown_svr select * from contown where 0; insert into contown_svr select -999,pw.owner,pc.contact_id,0 from fundown pw inner join fundcont pc using (funding_id) left join contown cw on cw.contact_id = pc.contact_id left join grouptree on pw.owner=subject and cw.owner=target where subject is null and pc.contact_id 0; select @t; update contown_svr set contown_id=(@t:=(@t+1)); select @t; exit -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior of CASE .. WHEN ... THEN....
At 11:41 +0200 10/25/02, Harald Fuchs wrote: In article 000701c27193$1bf2bfa0$aa3fe7cb@jsheo, Heo, Jungsu [EMAIL PROTECTED] writes: Hello, every one. I Found a bug about CASE .. WHEN .. THEN.. mysql SELECT VERSION() ; ++ | VERSION() | ++ | 4.0.3-beta | ++ 1 row in set (0.00 sec) mysql select CASE NULL WHEN NULL THEN 0 ELSE 1 END AS RESULT ; ++ | RESULT | ++ | 1 | ++ 1 row in set (0.00 sec) I think RESULT should be '0'. Am I wrong? IF() works finely. mysql SELECT IF( NULL IS NULL, 0 , 1 ) AS RESULT ; ++ | RESULT | ++ | 0 | ++ 1 row in set (0.00 sec) Is this a bug or a mistake of mine? The latter one. While NULL IS NULL returns true, NULL = anything returns false, even if anything is NULL. [Filter fodder: SQL query] If you're using the CASE to test whether a part - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Strange behavior of CASE .. WHEN ... THEN....
I think that's confusing, but right. every null value is distinct, thus null != null. weird, but null is not a value, it's the lack of a value, so nothing can be shown about it. so, SELECT IF( NULL = NULL, 0 , 1 ) AS RESULT ; is not the same as SELECT IF( NULL is NULL, 0 , 1 ) AS RESULT ; -Original Message- From: Harald Fuchs [mailto:lists-mysql;news.protecting.net] Sent: Friday, October 25, 2002 5:42 AM To: [EMAIL PROTECTED] Subject: Re: Strange behavior of CASE .. WHEN ... THEN In article 000701c27193$1bf2bfa0$aa3fe7cb@jsheo, Heo, Jungsu [EMAIL PROTECTED] writes: Hello, every one. I Found a bug about CASE .. WHEN .. THEN.. mysql SELECT VERSION() ; ++ | VERSION() | ++ | 4.0.3-beta | ++ 1 row in set (0.00 sec) mysql select CASE NULL WHEN NULL THEN 0 ELSE 1 END AS RESULT ; ++ | RESULT | ++ | 1 | ++ 1 row in set (0.00 sec) I think RESULT should be '0'. Am I wrong? IF() works finely. mysql SELECT IF( NULL IS NULL, 0 , 1 ) AS RESULT ; ++ | RESULT | ++ | 0 | ++ 1 row in set (0.00 sec) Is this a bug or a mistake of mine? The latter one. While NULL IS NULL returns true, NULL = anything returns false, even if anything is NULL. [Filter fodder: SQL query] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange behavior of CASE .. WHEN ... THEN....
Hello, every one. I Found a bug about CASE .. WHEN .. THEN.. mysql SELECT VERSION() ; ++ | VERSION() | ++ | 4.0.3-beta | ++ 1 row in set (0.00 sec) mysql select CASE NULL WHEN NULL THEN 0 ELSE 1 END AS RESULT ; ++ | RESULT | ++ | 1 | ++ 1 row in set (0.00 sec) I think RESULT should be '0'. Am I wrong? IF() works finely. mysql SELECT IF( NULL IS NULL, 0 , 1 ) AS RESULT ; ++ | RESULT | ++ | 0 | ++ 1 row in set (0.00 sec) Is this a bug or a mistake of mine? Thanks! sql. ## Heo, Jungsu Mr. SimpleX Internet. http://www.simplexi.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Strange behavior of MySQL 3.23.51
Hello.
On Mon 2002-10-21 at 18:42:05 +0200, [EMAIL PROTECTED] wrote:
[...]
I think it's an error in MySQL. Why else this different
behaviour with and without unique index?
IMHO it's different issue.
IMHO, it's the main issue. Adding or removing an index, even an unique
one may not change the result of a SELECT. So he found a malfunction
with his setting.
To ponder about GROUP BY or not, while he has a malfunctioning mysqld,
only takes focus from real the issue. When the malfunction is
rectified, one can look into the query (which IMHO should work as it
is).
Stefan, I think the problem is that the optimizer makes a wrong guess,
related to UNIQUE index. Could you please try with the newest 3.23.x
version and if it is still reproducable, report it to
[EMAIL PROTECTED] (or even better, use mysqlbug).
Regards,
Benjamin.
PS: People, please trim the quotes in your replies. It really does not
make any sense to quote the list footer several times.
[...]
- Original Message -
From: [EMAIL PROTECTED]
To: [MYSQL] [EMAIL PROTECTED]
Sent: Monday, October 21, 2002 5:46 PM
Subject: Strange behavior of MySQL 3.23.51
I have a database structure as follows (simplyfied) :
CREATE TABLE masterTABLE (
pid int(11) unsigned NOT NULL default '0',
c1id int(11) unsigned default NULL,
c2id int(11) unsigned default NULL,
value int(11) unsigned NOT NULL default '0',
UNIQUE KEY pid2 (pid,c1id,c2id),
UNIQUE KEY pid (pid,value)
) TYPE=MyISAM;
INSERT INTO masterTABLE VALUES (1, 1, NULL, 1);
INSERT INTO masterTABLE VALUES (1, 2, NULL, 2);
INSERT INTO masterTABLE VALUES (1, NULL, 3, 3);
INSERT INTO masterTABLE VALUES (1, 4, NULL, 4);
INSERT INTO masterTABLE VALUES (1, 5, NULL, 5);
CREATE TABLE childTABLE1 (
id int(11) unsigned NOT NULL default '0',
active enum('Yes','No') NOT NULL default 'Yes',
PRIMARY KEY (id)
) TYPE=MyISAM;
INSERT INTO childTABLE1 VALUES (1, 'Yes');
INSERT INTO childTABLE1 VALUES (2, 'No');
INSERT INTO childTABLE1 VALUES (4, 'Yes');
INSERT INTO childTABLE1 VALUES (5, 'No');
CREATE TABLE childTABLE2 (
id int(11) unsigned NOT NULL default '0',
active enum('Yes','No') NOT NULL default 'Yes',
PRIMARY KEY (id)
) TYPE=MyISAM;
INSERT INTO childTABLE2 VALUES (3, 'Yes');
If I do this query:
SELECT MAX( value )
FROM masterTABLE AS m
LEFT JOIN childTABLE1 AS c1
ON m.c1id = c1.id AND
c1.active = 'Yes'
LEFT JOIN childTABLE2 AS c2
ON m.c2id = c2.id
AND c2.active = 'Yes'
WHERE m.pid=1
AND (c1.id IS NOT NULL OR c2.id IS NOT NULL)
the Result will be 5 which is probably wrong.
The expected Result ist 4.
The correct Result will be returned if you remove
both UNIQUE KEYs (pid and pid2) from Table masterTABLE.
[...]
--
[EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: Strange behavior of MySQL 3.23.51
And what about the WHERE-Clause??
It is not used in Statements with Aggregat functions? ...
cool answer ... but I really can't think so ...
-Ursprüngliche Nachricht-
Von: Mikhail Entaltsev [mailto:mike_lynx;smtp.ru]
Gesendet: Montag, 21. Oktober 2002 18:42
An: [EMAIL PROTECTED]; [MYSQL]
Cc: [EMAIL PROTECTED]
Betreff: Re: Strange behavior of MySQL 3.23.51
http://www.mysql.com/doc/en/Group_by_functions.html
...If you use a group function in a statement containing no GROUP BY
clause, it is equivalent to grouping on all rows...
So when you are asking about SELECT MAX( value ) FROM
masterTABLE AS m ...
without GROUP BY clause,
MySQL is looking MAX(value) through whole table.
It explains also why there is no syntax error.
I think it's an error in MySQL. Why else this different
behaviour with and without unique index?
IMHO it's different issue.
Best regards,
Mikhail.
- Original Message -
From: [EMAIL PROTECTED]
To: [MYSQL] [EMAIL PROTECTED]
Sent: Monday, October 21, 2002 6:21 PM
Subject: AW: Strange behavior of MySQL 3.23.51
Hmmmnn ... I don't think so ... COUNT() is also an
aggregate function and a SELECT COUNT(*) FROM table
should work on every database without grouping.
I think it's an error in MySQL. Why else this different
behaviour with and without unique index?
-Ursprüngliche Nachricht-
Von: Mikhail Entaltsev [mailto:mike_lynx;smtp.ru]
Gesendet: Montag, 21. Oktober 2002 18:01
An: [EMAIL PROTECTED]; [MYSQL]
Betreff: Re: Strange behavior of MySQL 3.23.51
Stephan,
SELECT MAX( value )
FROM masterTABLE AS m
LEFT JOIN childTABLE1 AS c1
ON m.c1id = c1.id AND
c1.active = 'Yes'
LEFT JOIN childTABLE2 AS c2
ON m.c2id = c2.id
AND c2.active = 'Yes'
WHERE m.pid=1
AND (c1.id IS NOT NULL OR c2.id IS NOT NULL)
you are using aggregate function. So you need to use group
by clause.
Just add at the end group by m.pid:
SELECT MAX( value )
FROM masterTABLE AS m
LEFT JOIN childTABLE1 AS c1
ON m.c1id = c1.id AND
c1.active = 'Yes'
LEFT JOIN childTABLE2 AS c2
ON m.c2id = c2.id
AND c2.active = 'Yes'
WHERE m.pid=1
AND (c1.id IS NOT NULL OR c2.id IS NOT NULL)
GROUP BY m.pid
Best regards,
Mikhail.
P.S. But actually it is strange that MySQl didn't report syntax
error there.
:(
- Original Message -
From: [EMAIL PROTECTED]
To: [MYSQL] [EMAIL PROTECTED]
Sent: Monday, October 21, 2002 5:46 PM
Subject: Strange behavior of MySQL 3.23.51
I have a database structure as follows (simplyfied) :
CREATE TABLE masterTABLE (
pid int(11) unsigned NOT NULL default '0',
c1id int(11) unsigned default NULL,
c2id int(11) unsigned default NULL,
value int(11) unsigned NOT NULL default '0',
UNIQUE KEY pid2 (pid,c1id,c2id),
UNIQUE KEY pid (pid,value)
) TYPE=MyISAM;
INSERT INTO masterTABLE VALUES (1, 1, NULL, 1);
INSERT INTO masterTABLE VALUES (1, 2, NULL, 2);
INSERT INTO masterTABLE VALUES (1, NULL, 3, 3);
INSERT INTO masterTABLE VALUES (1, 4, NULL, 4);
INSERT INTO masterTABLE VALUES (1, 5, NULL, 5);
CREATE TABLE childTABLE1 (
id int(11) unsigned NOT NULL default '0',
active enum('Yes','No') NOT NULL default 'Yes',
PRIMARY KEY (id)
) TYPE=MyISAM;
INSERT INTO childTABLE1 VALUES (1, 'Yes');
INSERT INTO childTABLE1 VALUES (2, 'No');
INSERT INTO childTABLE1 VALUES (4, 'Yes');
INSERT INTO childTABLE1 VALUES (5, 'No');
CREATE TABLE childTABLE2 (
id int(11) unsigned NOT NULL default '0',
active enum('Yes','No') NOT NULL default 'Yes',
PRIMARY KEY (id)
) TYPE=MyISAM;
INSERT INTO childTABLE2 VALUES (3, 'Yes');
If I do this query:
SELECT MAX( value )
FROM masterTABLE AS m
LEFT JOIN childTABLE1 AS c1
ON m.c1id = c1.id AND
c1.active = 'Yes'
LEFT JOIN childTABLE2 AS c2
ON m.c2id = c2.id
AND c2.active = 'Yes'
WHERE m.pid=1
AND (c1.id IS NOT NULL OR c2.id IS NOT NULL)
the Result will be 5 which is probably wrong.
The expected Result ist 4.
The correct Result will be returned if you remove
both UNIQUE KEYs (pid and pid2) from Table masterTABLE.
--
Stephan Skusa mailto:stephan.skusa;lippe-net.de
Lippe-Net Online-Service http://www.lippe-net.de
Herforder Strasse 309 tel.: +49 (0)521 - 977 998 - 0
33609 Bielefeld - Germany fax: +49 (0)521 - 977 998 - 9
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive
AW: Strange behavior of MySQL 3.23.51
Hmmmnn ... I don't think so ... COUNT() is also an
aggregate function and a SELECT COUNT(*) FROM table
should work on every database without grouping.
I think it's an error in MySQL. Why else this different
behaviour with and without unique index?
-Ursprüngliche Nachricht-
Von: Mikhail Entaltsev [mailto:mike_lynx;smtp.ru]
Gesendet: Montag, 21. Oktober 2002 18:01
An: [EMAIL PROTECTED]; [MYSQL]
Betreff: Re: Strange behavior of MySQL 3.23.51
Stephan,
SELECT MAX( value )
FROM masterTABLE AS m
LEFT JOIN childTABLE1 AS c1
ON m.c1id = c1.id AND
c1.active = 'Yes'
LEFT JOIN childTABLE2 AS c2
ON m.c2id = c2.id
AND c2.active = 'Yes'
WHERE m.pid=1
AND (c1.id IS NOT NULL OR c2.id IS NOT NULL)
you are using aggregate function. So you need to use group by clause.
Just add at the end group by m.pid:
SELECT MAX( value )
FROM masterTABLE AS m
LEFT JOIN childTABLE1 AS c1
ON m.c1id = c1.id AND
c1.active = 'Yes'
LEFT JOIN childTABLE2 AS c2
ON m.c2id = c2.id
AND c2.active = 'Yes'
WHERE m.pid=1
AND (c1.id IS NOT NULL OR c2.id IS NOT NULL)
GROUP BY m.pid
Best regards,
Mikhail.
P.S. But actually it is strange that MySQl didn't report syntax
error there.
:(
- Original Message -
From: [EMAIL PROTECTED]
To: [MYSQL] [EMAIL PROTECTED]
Sent: Monday, October 21, 2002 5:46 PM
Subject: Strange behavior of MySQL 3.23.51
I have a database structure as follows (simplyfied) :
CREATE TABLE masterTABLE (
pid int(11) unsigned NOT NULL default '0',
c1id int(11) unsigned default NULL,
c2id int(11) unsigned default NULL,
value int(11) unsigned NOT NULL default '0',
UNIQUE KEY pid2 (pid,c1id,c2id),
UNIQUE KEY pid (pid,value)
) TYPE=MyISAM;
INSERT INTO masterTABLE VALUES (1, 1, NULL, 1);
INSERT INTO masterTABLE VALUES (1, 2, NULL, 2);
INSERT INTO masterTABLE VALUES (1, NULL, 3, 3);
INSERT INTO masterTABLE VALUES (1, 4, NULL, 4);
INSERT INTO masterTABLE VALUES (1, 5, NULL, 5);
CREATE TABLE childTABLE1 (
id int(11) unsigned NOT NULL default '0',
active enum('Yes','No') NOT NULL default 'Yes',
PRIMARY KEY (id)
) TYPE=MyISAM;
INSERT INTO childTABLE1 VALUES (1, 'Yes');
INSERT INTO childTABLE1 VALUES (2, 'No');
INSERT INTO childTABLE1 VALUES (4, 'Yes');
INSERT INTO childTABLE1 VALUES (5, 'No');
CREATE TABLE childTABLE2 (
id int(11) unsigned NOT NULL default '0',
active enum('Yes','No') NOT NULL default 'Yes',
PRIMARY KEY (id)
) TYPE=MyISAM;
INSERT INTO childTABLE2 VALUES (3, 'Yes');
If I do this query:
SELECT MAX( value )
FROM masterTABLE AS m
LEFT JOIN childTABLE1 AS c1
ON m.c1id = c1.id AND
c1.active = 'Yes'
LEFT JOIN childTABLE2 AS c2
ON m.c2id = c2.id
AND c2.active = 'Yes'
WHERE m.pid=1
AND (c1.id IS NOT NULL OR c2.id IS NOT NULL)
the Result will be 5 which is probably wrong.
The expected Result ist 4.
The correct Result will be returned if you remove
both UNIQUE KEYs (pid and pid2) from Table masterTABLE.
--
Stephan Skusa mailto:stephan.skusa;lippe-net.de
Lippe-Net Online-Service http://www.lippe-net.de
Herforder Strasse 309 tel.: +49 (0)521 - 977 998 - 0
33609 Bielefeld - Germany fax: +49 (0)521 - 977 998 - 9
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Strange behavior
LeTortorec, Tuesday, April 09, 2002, 12:17:06 AM, you wrote: Leoen I have a table with the following fields: Leoen id=autoincrement, int (11) Leoen ts_h=decimal Leoen ts_pid=int (11) Leoen ts_day=text Leoen There is a unique record where ts_pid=60 and ts_day=2002032800 Leoen (ts_h=0). Leoen If I do [update t_timesheet set ts_h=0 where ts_pid=60 and Leoen ts_day=2002032800 ], every thing works fine. (nothing is changed Leoen because the values remain the same) Leoen But if I change the query to ts_h=1: Leoen [update t_timesheet set ts_h=1 where ts_pid=60 and Leoen ts_day=2002032800 ] Leoen The record is deleted instead of being update. Leoen I changed ts_h to a decimal, or integer, same behavior. It deletes my record Leoen instead of updating it. Leoen Any idea why this occurs? What version of MySQL do you use? Can you create a test case for this? Leoen Thank you. Leoen Jean-Louis -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange behavior
I have a table with the following fields: id=autoincrement, int (11) ts_h=decimal ts_pid=int (11) ts_day=text There is a unique record where ts_pid=60 and ts_day=2002032800 (ts_h=0). If I do [update t_timesheet set ts_h=0 where ts_pid=60 and ts_day=2002032800 ], every thing works fine. (nothing is changed because the values remain the same) But if I change the query to ts_h=1: [update t_timesheet set ts_h=1 where ts_pid=60 and ts_day=2002032800 ] The record is deleted instead of being update. I changed ts_h to a decimal, or integer, same behavior. It deletes my record instead of updating it. Any idea why this occurs? Thank you. Jean-Louis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Strange behavior
it worked for me: mysql describe t_timesheet; ++---+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | ts_h | decimal(10,0) | YES | | NULL|| | ts_pid | int(11) | YES | | NULL|| | ts_day | text | YES | | NULL|| ++---+--+-+-++ 4 rows in set (0.00 sec) mysql select * from t_timesheet; ++--+++ | id | ts_h | ts_pid | ts_day | ++--+++ | 1 |0 | 60 | 2002032800 | ++--+++ 1 row in set (0.00 sec) mysql update t_timesheet set ts_h=1 where ts_pid=60 and ts_day=2002032800; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from t_timesheet; ++--+++ | id | ts_h | ts_pid | ts_day | ++--+++ | 1 |1 | 60 | 2002032800 | ++--+++ 1 row in set (0.00 sec) -Original Message- From: LeTortorec, Jean-Louis [mailto:[EMAIL PROTECTED]] Sent: Monday, April 08, 2002 4:17 PM To: '[EMAIL PROTECTED]' Subject: Strange behavior I have a table with the following fields: id=autoincrement, int (11) ts_h=decimal ts_pid=int (11) ts_day=text There is a unique record where ts_pid=60 and ts_day=2002032800 (ts_h=0). If I do [update t_timesheet set ts_h=0 where ts_pid=60 and ts_day=2002032800 ], every thing works fine. (nothing is changed because the values remain the same) But if I change the query to ts_h=1: [update t_timesheet set ts_h=1 where ts_pid=60 and ts_day=2002032800 ] The record is deleted instead of being update. I changed ts_h to a decimal, or integer, same behavior. It deletes my record instead of updating it. Any idea why this occurs? Thank you. Jean-Louis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: PHP + MySQL problem (strange behavior)
I've had a few queries that worked strangely worked in Mysql monitor, but did not work from PHP. I was able to solve every one of them by ensure that the query in PHP did not have any line breaks in it. It normally doesn't matter if there are line breaks in the code, but on occassion, a line break can cause strange results. Hope this helps. Mike On Wed, 5 Dec 2001, Javier Muniz wrote: Hello, I'm having trouble determining what's going wrong with a MySQL query that I'm doing from PHP. I have a table with the following columns: id (int) name (varchar 20) starttime (int) duration (int) now, i have a row that has a starttime of 60, when i attempt to do the following update with PHP, it sets it to 0: UPDATE mytable SET starttime=starttime-30 WHERE name = 'myname' but when I run it from the MySQL command line, copy/pasted from the code, it sets the value of starttime to 30 as expected. Any thoughts on where this problem originates from? PHP or MySQL? They're pretty commonly used in unison so I'd imagine something like this should work fairly smoothly. I've also posted this to the PHP list in case there's someone there that's encountered this before. Javier Muniz Chief Technology Officer Granicus, LTD. Tel: (415) 522-5216 Fax: (415) 522-5215 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Michael J. Ridinger Lead Web Developer - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
PHP + MySQL problem (strange behavior)
Hello, I'm having trouble determining what's going wrong with a MySQL query that I'm doing from PHP. I have a table with the following columns: id (int) name (varchar 20) starttime (int) duration (int) now, i have a row that has a starttime of 60, when i attempt to do the following update with PHP, it sets it to 0: UPDATE mytable SET starttime=starttime-30 WHERE name = 'myname' but when I run it from the MySQL command line, copy/pasted from the code, it sets the value of starttime to 30 as expected. Any thoughts on where this problem originates from? PHP or MySQL? They're pretty commonly used in unison so I'd imagine something like this should work fairly smoothly. I've also posted this to the PHP list in case there's someone there that's encountered this before. Javier Muniz Chief Technology Officer Granicus, LTD. Tel: (415) 522-5216 Fax: (415) 522-5215 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
index question strange behavior
Questions about index mysql show index from listing; +-++-+--+-+- --+-+--+ | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | +-++-+--+-+- --+-+--+ | listing | 0 | PRIMARY |1 | my_key | A | 137677 | NULL | | listing | 1 | mls_index |1 | mls_number | A | 137677 | NULL | | listing | 1 | sold_new|1 | sold_new| A | 2 | NULL | | listing | 1 | index3 |1 | city| A | 57 | NULL | | listing | 1 | stat_city_price |1 | stat| A | 45892 | NULL | | listing | 1 | stat_city_price |2 | city| A | 45892 | NULL | | listing | 1 | stat_city_price |3 | price | A | 45892 | NULL | +-++-+--+-+- --+-+--+ 7 rows in set (0.00 sec) stat and city are enum types price is an int. mysql explain select mls_number from listing where stat='A' and city='AH'; +-+---++-+-+ --+--+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+---++-+-+ --+--+---+ | listing | range | index3,stat_city_price | stat_city_price |NULL | NULL | 304 | | +-+---++-+-+ --+--+---+ 1 row in set (0.00 sec) Question - Why is key len,ref null ?? Is it not using my key? mysql explain select mls_number from listing where stat='S' and city='AH'; +-+--++-+-+- -+--+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--++-+-+- -+--+---+ | listing | ref | index3,stat_city_price | stat_city_price | 2 | S,AH | 689 | | +-+--++-+-+- -+--+---+ 1 row in set (0.00 sec) This is what I expect mysql explain select mls_number from listing where stat='S' and city='AH' and price0 and price50 order by price; +-+--++-+-+- -+--+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--++-+-+- -+--+---+ | listing | ref | index3,stat_city_price | stat_city_price | 2 | S,AH | 689 | | +-+--++-+-+- -+--+---+ 1 row in set (0.00 sec) why didn't it use price in the key?? mysql explain select mls_number from listing where (stat='B') and city='AH' and price0 and price50 order by price; +-+---++-+-+ --+--+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+---++-+-+ --+--+---+ | listing | range | index3,stat_city_price | stat_city_price |NULL | NULL | 29 | | +-+---++-+-+ --+--+---+ 1 row in set (0.00 sec) mysql explain select mls_number from listing where (stat='S' or stat='B') and city='AH' and price0 and price50 order by price; +-+---++-+-+ --+--+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+---++-+-+ --+--+---+ | listing | range | index3,stat_city_price | stat_city_price |NULL | NULL | 2535 | | +-+---++-+-+ --+--+---+ 1 row in set (0.00 sec) Why didn't mysql use the key and 689 +29 != 2535 for rows?? stat and city are enum types price is an int Any pointers would be helpful trying to speed up queries they currently take about 16 sec table has 134000 rows. Thanks, Rick [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try:
strange behavior (to me, at least)
Howdy. Some of the following is part rant. I know this email is too long, but I want to be sure to give enough info so you folks have to ask me for more info. All of it will probably be forwarded to the bugs list, depending on what solutions I find. I'm running SuSE 7.0 and I had mysql 3.22 installed from SuSE's site via rpm, and everything worked just fine. One of our programmers insisted that I upgrade to 3.23 to take advantage of that version's new features, which I tried to do last weekend. Below is a description of what happened. I removed everything mysql-related from my system via YaST, then attempted to download and rpm the newer version from mysql.com. I decided to install mysql-max. From the page on max, I assumed that simply installing max would give me a mysql server, with the transactions capability. I was wrong. I had to also grab the regular 3.23 server. Apparently, max layers on top of a regular server install. This was not clear in the documentation on max. Ok, so I get everything installed (except for the benchmarks, which kind of failed). Mysql seems to be running, and I was able to access the program via the command line, but all I got was errors when I tried to use php. Here is a snippet from irc: mmlj4 ok, I did: mmlj4 ?php mmlj4 mysql_connect(:/var/lib/mysql/mysql.sock, $user, $pass); mmlj4 ? mmlj4 and I got: mmlj4 Warning: MySQL Connection Failed: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111) in /home/jkelly/public_html/socket.php on line 2 Someone told me to execute the following at my shell promt: mysqladmin variables -u root -p | grep socket and this came back: | socket | /var/lib/mysql/mysql.sock Ok, so we figured that a symlink would make things work, and it did... until the daemon died: ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock Ok, so yesterday I have no mysql daemon running. I reboot, and it won't come up. I tried to start it manually, and no go. As a non-root user (the daemon told me to read the docs before trying to run it as root), I get an error telling me that I already have an instance running on /var/lib/mysql/mysql.sock --- the symlink, I figure. The command ps aux | grep sql yields only postgresql, no mysql. I haven't yet tried removing the symlink to see if it will start, but I'll probably do that later tonight. Ok, I'm sure I made a stupid blunder somewhere, but can anyone tell me what to do? Thanks :) +++ Joey Kelly /Minister of the Gospel | Computer Networking Consultant/ http://nolalinuxcoop.dhs.org/~jkelly/home/ Experience hath shewn, that even under the best forms [of government] those entrusted with power have, in time, and by slow op erations, perverted it into tyranny. - Thomas Jefferson - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange behavior.
Hi, Im not sure wheter this is a bug ( and it will make me look silly ) but its been annoying :).. It could even have been fixed since 3.22.27 :) ( Yes! I will upgrade tomorrow :) I have these three tables: ***RULES*** CREATE TABLE rules (id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, owner int(10), name blob, comment blob, allow bit, allusers bit, allcds bit, date_entered DATETIME); *** ***RULEMEMBERS*** CREATE TABLE rulemembers (id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, ruleid int(10), userid int(10), date_entered DATETIME); *** insert into rulemembers (ruleid,userid) values(0,0); ***RULEITEMS*** CREATE TABLE ruleitems (id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, ruleid int(10), itemid int(10), date_entered DATETIME); *** And they are sloppy done, I know. As you probably understand they are linked togheter.. When I select data from rules I link with ruleitems and rulemembers. My SQL is looking like this (for example): select rules.id as ruleid from rules, rulemembers, ruleitems where rules.owner = 1 and rules.allow and ((rulemembers.ruleid = rules.id and rulemembers.userid = 1) OR rules.allusers) and ((ruleitems.ruleid = rules.id and ruleitems.itemid = 100) OR rules.allcds) If there isnt some obvious errors in my SQL there is something wrong with MySQL because when I DONT have any posts in ruleitems or rulemembers this statement always returns zero. (Even if rules.allcds och rules.allusers is set). Very annoying. Well, shouts to the mysqlteam for a great product .. heh. and sorry if ive been bothering you about this if there isnt any problems. cheers. --Hjalmar - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQLGUI strange behavior
Peter Adamka writes:
Hi
1, I got packages from mysql site (3.23.33).
2, The mysqlGUI is 100% statical.
3, It crashes after I try to create database and then going to admin
panel.
4, I've found something that I've not seen. It end crashes with segfoult.
Malmo
In order to pinpoint a problem, please send me also the output of SHOW
GRANTS FOR the user with which you are logging in. Also, what is a
name of the database that you have tried to create ??
I would truly like to pinpoint a bug and then to fix it.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQLGUI strange behavior
Hi
1, I got packages from mysql site (3.23.33).
2, The mysqlGUI is 100% statical.
3, It crashes after I try to create database and then going to admin
panel.
4, I've found something that I've not seen. It end crashes with segfoult.
Malmo
Malmo If I only got 20$ for evryone who's too lazy to configure his own comp.
Bill Gates "Why not?"
On Thu, 5 Apr 2001, Sinisa Milivojevic wrote:
Peter Adamka writes:
It's me again.
So I downloaded the statical compiled MysqlGGUI, and i can start it
without problem. I can also connect to mysql server and change database.
BUT: - When I try to create database the result is NULL :)
- After entering the admin panel it usually crashes after another
operation
- It refuses to create a table (I'm a root of course)
AND SO ONE ...
May it be due to different libraries?
I got standard libs from RH7.0; MySQL 3.23.35; and all packages required
for MySQLGUI.
Thanks.
Malmo
Hi!
If you have statically built mysqlgui, you do not require any
libraries.
Please check out that it is not semi-static executable. What is the
output from ldd on mysqlgui ??
If you can not create database, may be you do not have privileges, but
the admin panel should not crash.
Can you please tell me exactly what to do to crash admin panel.
Also do not use MySQL binaries from RH 7.0, but use our binaries.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQLGUI strange behavior
It's me again. So I downloaded the statical compiled MysqlGGUI, and i can start it without problem. I can also connect to mysql server and change database. BUT: - When I try to create database the result is NULL :) - After entering the admin panel it usually crashes after another operation - It refuses to create a table (I'm a root of course) AND SO ONE ... May it be due to different libraries? I got standard libs from RH7.0; MySQL 3.23.35; and all packages required for MySQLGUI. Thanks. Malmo Malmo If I only got 20$ for evryone who's too lazy to configure his own comp. Bill Gates "Why not?" - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQLGUI strange behavior
Peter Adamka writes:
It's me again.
So I downloaded the statical compiled MysqlGGUI, and i can start it
without problem. I can also connect to mysql server and change database.
BUT: - When I try to create database the result is NULL :)
- After entering the admin panel it usually crashes after another
operation
- It refuses to create a table (I'm a root of course)
AND SO ONE ...
May it be due to different libraries?
I got standard libs from RH7.0; MySQL 3.23.35; and all packages required
for MySQLGUI.
Thanks.
Malmo
Hi!
If you have statically built mysqlgui, you do not require any
libraries.
Please check out that it is not semi-static executable. What is the
output from ldd on mysqlgui ??
If you can not create database, may be you do not have privileges, but
the admin panel should not crash.
Can you please tell me exactly what to do to crash admin panel.
Also do not use MySQL binaries from RH 7.0, but use our binaries.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: AW: Strange behavior of MySQL 3.23.xx with GROUP BY ...
I think that is the wrong answer ... Standard-SQL and ANSI-SQL require that a column that is displayed and has no grouping function applied to it MUST also appear in the GROUP BY-clause!!! Sir, every dialect of SQL has it's non-ANSI quirks. I've never heard of Standard-SQL, but ANSI-SQL is a standard established by a standards organization and used by vendors as a basis for their own dialects. There has never been a commercial dialect of SQL that fully matched its contemporary ANSI standard. They have all deviated from it in some way. In this case, MySQL extends the standard by allowing you to include an extra column in the SELECT clause. The result: fldNAME fldMONAT sum(fldSALARY) boris 1 10200 stephan 1 6000 itself makes no sense ... none earned as much money in January (1) ... ! You didn't group by month. You grouped by name. Since you didn't use an aggregate function on fldMONAT, MySQL returns the value found in that field in the first row in the group. Since you did use an aggregate function on fldSALARY, MySQL returns the aggregate value for that group. Postgres gives an error, Oracle gives an error, SQL-Server 7 gives an error even M$-Access 2000 gives an error why doesn't MySQL? There are parts of the Oracle SQL dialect that will raise errors in other RDBMSs, but not in Oracle. There are parts of Transact-SQL that will raise errors in other RDBMSs, but not in SQL Server. There are parts of the MySQL SQL dialect that will raise errors in other RDBMSs, but not in MySQL. Literature: http://www.newi.ac.uk/walshea/db1/sql04/index.htm there: http://www.newi.ac.uk/walshea/db1/sql04/sld015.htm http://www.nku.edu/~raghavan/gby.html http://info-it.umsystem.edu/oracle/svslr/svslr.2.1089.html http://www.sk.postgresql.org/docs/user/sql-select.htm and so on ... I think ANSI-Specs should even tell you the same ... ! And don't forget http://www.mysql.com/doc/E/x/Extensions_to_ANSI.html -Ursprungliche Nachricht- Von: Bob Hall [mailto:[EMAIL PROTECTED]] Gesendet: Freitag, 26. Januar 2001 12:18 An: [EMAIL PROTECTED] Betreff: Re: Strange behavior of MySQL 3.23.xx with GROUP BY ... I found a very strange behaviour of MySQL 3.23.xx with GROUP BY clause ... I have a Table: CREATE TABLE groupbyTEST ( fldNAME varchar(20) NOT NULL, fldMONTH tinyint(4) DEFAULT '0' NOT NULL, fldSALARY decimal(10,0) DEFAULT '0' NOT NULL, fldDATE datetime DEFAULT '-00-00 00:00:00' NOT NULL, PRIMARY KEY (fldNAME, fldMONTH) ); with following records: INSERT INTO groupbyTEST VALUES ( 'stephan', '1', '1000', '2001-01-25 23:12:29'); INSERT INTO groupbyTEST VALUES ( 'boris', '1', '2000', '2001-01-25 23:12:45'); INSERT INTO groupbyTEST VALUES ( 'stephan', '2', '1200', '2001-01-25 23:12:56'); INSERT INTO groupbyTEST VALUES ( 'stephan', '3', '1000', '2001-01-25 23:13:08'); INSERT INTO groupbyTEST VALUES ( 'stephan', '4', '900', '2001-01-25 23:14:50'); INSERT INTO groupbyTEST VALUES ( 'boris', '2', '1200', '2001-01-25 23:15:01'); INSERT INTO groupbyTEST VALUES ( 'stephan', '5', '1900', '2001-01-25 23:15:16'); INSERT INTO groupbyTEST VALUES ( 'boris', '3', '2400', '2001-01-25 23:15:39'); INSERT INTO groupbyTEST VALUES ( 'boris', '4', '2100', '2001-01-25 23:15:50'); INSERT INTO groupbyTEST VALUES ( 'boris', '5', '2300', '2001-01-25 23:16:01'); INSERT INTO groupbyTEST VALUES ( 'boris', '6', '200', '2001-01-25 23:16:11'); A statement like: SELECT fldNAME, fldMONTH, sum(fldSALARY) FROM groupbyTEST GROUP BY fldNAME HAVING sum(fldSALARY)200 should cause an error because of the missing fldMONTH in the GROUP BY clause, but it is executed without an error and results: fldNAME fldMONAT sum(fldGEHALT) boris 1 10200 stephan 1 6000 ... makes not very much sense ... does it?? Sir, why do you think it should cause an error? The statement is valid and the result is correct. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED
Re: Strange behavior of MySQL 3.23.xx with GROUP BY ...
I found a very strange behaviour of MySQL 3.23.xx with GROUP BY clause ... I have a Table: CREATE TABLE groupbyTEST ( fldNAME varchar(20) NOT NULL, fldMONTH tinyint(4) DEFAULT '0' NOT NULL, fldSALARY decimal(10,0) DEFAULT '0' NOT NULL, fldDATE datetime DEFAULT '-00-00 00:00:00' NOT NULL, PRIMARY KEY (fldNAME, fldMONTH) ); with following records: INSERT INTO groupbyTEST VALUES ( 'stephan', '1', '1000', '2001-01-25 23:12:29'); INSERT INTO groupbyTEST VALUES ( 'boris', '1', '2000', '2001-01-25 23:12:45'); INSERT INTO groupbyTEST VALUES ( 'stephan', '2', '1200', '2001-01-25 23:12:56'); INSERT INTO groupbyTEST VALUES ( 'stephan', '3', '1000', '2001-01-25 23:13:08'); INSERT INTO groupbyTEST VALUES ( 'stephan', '4', '900', '2001-01-25 23:14:50'); INSERT INTO groupbyTEST VALUES ( 'boris', '2', '1200', '2001-01-25 23:15:01'); INSERT INTO groupbyTEST VALUES ( 'stephan', '5', '1900', '2001-01-25 23:15:16'); INSERT INTO groupbyTEST VALUES ( 'boris', '3', '2400', '2001-01-25 23:15:39'); INSERT INTO groupbyTEST VALUES ( 'boris', '4', '2100', '2001-01-25 23:15:50'); INSERT INTO groupbyTEST VALUES ( 'boris', '5', '2300', '2001-01-25 23:16:01'); INSERT INTO groupbyTEST VALUES ( 'boris', '6', '200', '2001-01-25 23:16:11'); A statement like: SELECT fldNAME, fldMONTH, sum(fldSALARY) FROM groupbyTEST GROUP BY fldNAME HAVING sum(fldSALARY)200 should cause an error because of the missing fldMONTH in the GROUP BY clause, but it is executed without an error and results: fldNAME fldMONAT sum(fldGEHALT) boris 1 10200 stephan 1 6000 ... makes not very much sense ... does it?? Sir, why do you think it should cause an error? The statement is valid and the result is correct. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: Strange behavior of MySQL 3.23.xx with GROUP BY ...
I think that is the wrong answer ... Standard-SQL and ANSI-SQL require that a column that is displayed and has no grouping function applied to it MUST also appear in the GROUP BY-clause!!! The result: fldNAME fldMONAT sum(fldSALARY) boris 1 10200 stephan 1 6000 itself makes no sense ... none earned as much money in January (1) ... ! Postgres gives an error, Oracle gives an error, SQL-Server 7 gives an error even M$-Access 2000 gives an error why doesn't MySQL? Literature: http://www.newi.ac.uk/walshea/db1/sql04/index.htm there: http://www.newi.ac.uk/walshea/db1/sql04/sld015.htm http://www.nku.edu/~raghavan/gby.html http://info-it.umsystem.edu/oracle/svslr/svslr.2.1089.html http://www.sk.postgresql.org/docs/user/sql-select.htm and so on ... I think ANSI-Specs should even tell you the same ... ! -Ursprungliche Nachricht- Von: Bob Hall [mailto:[EMAIL PROTECTED]] Gesendet: Freitag, 26. Januar 2001 12:18 An: [EMAIL PROTECTED] Betreff: Re: Strange behavior of MySQL 3.23.xx with GROUP BY ... I found a very strange behaviour of MySQL 3.23.xx with GROUP BY clause ... I have a Table: CREATE TABLE groupbyTEST ( fldNAME varchar(20) NOT NULL, fldMONTH tinyint(4) DEFAULT '0' NOT NULL, fldSALARY decimal(10,0) DEFAULT '0' NOT NULL, fldDATE datetime DEFAULT '-00-00 00:00:00' NOT NULL, PRIMARY KEY (fldNAME, fldMONTH) ); with following records: INSERT INTO groupbyTEST VALUES ( 'stephan', '1', '1000', '2001-01-25 23:12:29'); INSERT INTO groupbyTEST VALUES ( 'boris', '1', '2000', '2001-01-25 23:12:45'); INSERT INTO groupbyTEST VALUES ( 'stephan', '2', '1200', '2001-01-25 23:12:56'); INSERT INTO groupbyTEST VALUES ( 'stephan', '3', '1000', '2001-01-25 23:13:08'); INSERT INTO groupbyTEST VALUES ( 'stephan', '4', '900', '2001-01-25 23:14:50'); INSERT INTO groupbyTEST VALUES ( 'boris', '2', '1200', '2001-01-25 23:15:01'); INSERT INTO groupbyTEST VALUES ( 'stephan', '5', '1900', '2001-01-25 23:15:16'); INSERT INTO groupbyTEST VALUES ( 'boris', '3', '2400', '2001-01-25 23:15:39'); INSERT INTO groupbyTEST VALUES ( 'boris', '4', '2100', '2001-01-25 23:15:50'); INSERT INTO groupbyTEST VALUES ( 'boris', '5', '2300', '2001-01-25 23:16:01'); INSERT INTO groupbyTEST VALUES ( 'boris', '6', '200', '2001-01-25 23:16:11'); A statement like: SELECT fldNAME, fldMONTH, sum(fldSALARY) FROM groupbyTEST GROUP BY fldNAME HAVING sum(fldSALARY)200 should cause an error because of the missing fldMONTH in the GROUP BY clause, but it is executed without an error and results: fldNAME fldMONAT sum(fldGEHALT) boris 1 10200 stephan 1 6000 ... makes not very much sense ... does it?? Sir, why do you think it should cause an error? The statement is valid and the result is correct. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange behavior of MySQL 3.23.xx with GROUP BY ...
Hello, I found a very strange behaviour of MySQL 3.23.xx with GROUP BY clause ... I have a Table: CREATE TABLE groupbyTEST ( fldNAME varchar(20) NOT NULL, fldMONTH tinyint(4) DEFAULT '0' NOT NULL, fldSALARY decimal(10,0) DEFAULT '0' NOT NULL, fldDATE datetime DEFAULT '-00-00 00:00:00' NOT NULL, PRIMARY KEY (fldNAME, fldMONTH) ); with following records: INSERT INTO groupbyTEST VALUES ( 'stephan', '1', '1000', '2001-01-25 23:12:29'); INSERT INTO groupbyTEST VALUES ( 'boris', '1', '2000', '2001-01-25 23:12:45'); INSERT INTO groupbyTEST VALUES ( 'stephan', '2', '1200', '2001-01-25 23:12:56'); INSERT INTO groupbyTEST VALUES ( 'stephan', '3', '1000', '2001-01-25 23:13:08'); INSERT INTO groupbyTEST VALUES ( 'stephan', '4', '900', '2001-01-25 23:14:50'); INSERT INTO groupbyTEST VALUES ( 'boris', '2', '1200', '2001-01-25 23:15:01'); INSERT INTO groupbyTEST VALUES ( 'stephan', '5', '1900', '2001-01-25 23:15:16'); INSERT INTO groupbyTEST VALUES ( 'boris', '3', '2400', '2001-01-25 23:15:39'); INSERT INTO groupbyTEST VALUES ( 'boris', '4', '2100', '2001-01-25 23:15:50'); INSERT INTO groupbyTEST VALUES ( 'boris', '5', '2300', '2001-01-25 23:16:01'); INSERT INTO groupbyTEST VALUES ( 'boris', '6', '200', '2001-01-25 23:16:11'); A statement like: SELECT fldNAME, fldMONTH, sum(fldSALARY) FROM groupbyTEST GROUP BY fldNAME HAVING sum(fldSALARY)200 should cause an error because of the missing fldMONTH in the GROUP BY clause, but it is executed without an error and results: fldNAME fldMONAT sum(fldGEHALT) boris 1 10200 stephan 1 6000 ... makes not very much sense ... does it?? bye Stephan Skusa - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
