Re: tmp table created using remote database - slow query
Rhys Johnson wrote: Thanks for the reply Chris I seem to have the remote database working just as fast as the local one after comparing the my.cnf settings. Query times have dropped from 3.6s to 0.4s. However, now when I log in from a shell prompt remotely into mysql it takes about 10s for my password to be accepted. Hence the web app still runs slow. I can log in remotely using mysql administrator and other client programs and it logs in as normal. It is just really slow from a shell. The only settings I changed were key_buffer max_allowed_packet no_auto_rehash Not sure how changing these values has caused my logins to be so slow Always CC the list, you'll get better and faster responses. That sounds like a dns issue. If you log in again now is it fast? If you use the ip address instead of the domain name is it fast? This might help you: http://www.debuntu.org/2006/07/21/75-how-to-optimize-mysql-response-time/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
find in list
Hello, I want to do a query where I only gt the results by an numeric value: select * from MyTable where 1 IN someFieldInMyTable I know this query doesn't work, but maybe anyone knows what I mean. 1 can be a value betwenn 1 and 23 someFieldInMyTable is normaly a list like: 1,2,3,4,5,6,9,21 br Charlie
Re: find in list
Charlie Schaubmair schreef: Hello, I want to do a query where I only gt the results by an numeric value: select * from MyTable where 1 IN someFieldInMyTable I know this query doesn't work, but maybe anyone knows what I mean. 1 can be a value betwenn 1 and 23 someFieldInMyTable is normaly a list like: 1,2,3,4,5,6,9,21 br Charlie Hey try: SELECT * FROM MyTable WHERE someFieldInMyTable='%1%' This will also give you results like 21 What I usally do in these cases is build the values like this: [1][2][3][21] And then te query: SELECT * FROM MyTable WHERE someFieldInMyTable='%[1]%' Mike -- Medusa, Media Usage Advice B.V. Science Park Eindhoven 5216 5692 EG SON tel: 040-24 57 024 fax: 040-29 63 567 url: www.medusa.nl mail: [EMAIL PROTECTED] Uw bedrijf voor Multimedia op Maat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AW: find in list
Hey Charlie,
maybe there is, but i don't know why... because when you do a like query
it also finds the 21 when you do a like on the 1.
what you maybe can do (not tested) is:
SELECT *, CONCAT(',',someFieldInMyTable,',') as wherefield FROM MyTable WHERE
wherefield='%,1,%'
Now your someFieldInMyTable is CONCATed to ,1,2,3,4,5,6,9,21, and over that
value the where clause is done...
Maybe it helps
Mike
Charlie Schaubmair schreef:
Hello Mike,
thx, but isn't there another way?
br
Charlie
-Ursprüngliche Nachricht-
Von: Mike van Hoof [mailto:[EMAIL PROTECTED]
Gesendet: Mittwoch, 16. August 2006 10:46
An: Charlie Schaubmair
Cc: mysql@lists.mysql.com
Betreff: Re: find in list
Charlie Schaubmair schreef:
Hello,
I want to do a query where I only gt the results by an
numeric value:
select * from MyTable where 1 IN someFieldInMyTable I know
this query
doesn't work, but maybe anyone knows what I mean.
1 can be a value betwenn 1 and 23
someFieldInMyTable is normaly a list like: 1,2,3,4,5,6,9,21
br
Charlie
Hey
try:
SELECT * FROM MyTable WHERE someFieldInMyTable='%1%'
This will also give you results like 21
What I usally do in these cases is build the values like this:
[1][2][3][21]
And then te query:
SELECT * FROM MyTable WHERE someFieldInMyTable='%[1]%'
Mike
--
Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024
fax: 040-29 63 567
url: www.medusa.nl
mail: [EMAIL PROTECTED]
Uw bedrijf voor Multimedia op Maat
--
Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024
fax: 040-29 63 567
url: www.medusa.nl
mail: [EMAIL PROTECTED]
Uw bedrijf voor Multimedia op Maat
Re: AW: find in list
Sorry, query was wrong... has to be:
SELECT *, CONCAT(',',someFieldInMyTable,',') as wherefield FROM MyTable
WHERE wherefield LIKE '%,1,%'
Mike van Hoof schreef:
Hey Charlie,
maybe there is, but i don't know why... because when you do a like
query it also finds the 21 when you do a like on the 1.
what you maybe can do (not tested) is:
SELECT *, CONCAT(',',someFieldInMyTable,',') as wherefield FROM
MyTable WHERE wherefield='%,1,%'
Now your someFieldInMyTable is CONCATed to ,1,2,3,4,5,6,9,21, and over
that value the where clause is done...
Maybe it helps
Mike
Charlie Schaubmair schreef:
Hello Mike,
thx, but isn't there another way?
br
Charlie
-Ursprüngliche Nachricht-
Von: Mike van Hoof [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 16.
August 2006 10:46
An: Charlie Schaubmair
Cc: mysql@lists.mysql.com
Betreff: Re: find in list
Charlie Schaubmair schreef:
Hello,
I want to do a query where I only gt the results by an
numeric value:
select * from MyTable where 1 IN someFieldInMyTable I know
this query
doesn't work, but maybe anyone knows what I mean.
1 can be a value betwenn 1 and 23
someFieldInMyTable is normaly a list like: 1,2,3,4,5,6,9,21
br
Charlie
Hey
try:
SELECT * FROM MyTable WHERE someFieldInMyTable='%1%'
This will also give you results like 21
What I usally do in these cases is build the values like this:
[1][2][3][21]
And then te query:
SELECT * FROM MyTable WHERE someFieldInMyTable='%[1]%'
Mike
--
Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024
fax: 040-29 63 567
url: www.medusa.nl
mail: [EMAIL PROTECTED]
Uw bedrijf voor Multimedia op Maat
--
Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024
fax: 040-29 63 567
url: www.medusa.nl
mail: [EMAIL PROTECTED]
Uw bedrijf voor Multimedia op Maat
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL - HHHHangs when a stored procedure is called - 5.0.22/InnoDB/WinXP-SP2
Hi,
I have the following database objects in a purely InnoDB database:
--
CREATE TABLE Person (
PersonIDint not null PRIMARY KEY,
Ttl char(15),
FllNm varchar(50),
frstNm varchar(15) not null,
midNm varchar(15),
lstNm varchar(15) not null,
Gender char(1) not null DEFAULT 'M' CHECK Gender in
('M','F'),
dob dateCHECK DOB '19000101',
nicNo varchar(13),
mrtlSttschar(1) not null DEFAULT 'M' CHECK mrtlStts in
('S','M','D','W'),
cellNo varchar(15),
website varchar(80),
Sttsint not null DEFAULT 1, /* 1=Active,
2=Inactive */
index PersonDob_ndx (dob),
index PersonNIC_ndx (nicno),
index PersonCell_ndx (cellNo),
index PersonFllNm_ndx (FllNm),
index PersonNm_ndx (frstNm, midNm, lstNm)
) ENGINE=InnoDB;
CREATE FUNCTION CharValIsNumeric (v VARCHAR(15))
RETURNS boolean
BEGIN
declare i, l int(2);
set l=char_length(v);
set i=1;
while (i = l) and (substring(v,i,1) in
('1','2','3','4','5','6','7','8','9','0')) do
set i=i+1;
end while;
IF i l THEN
return 1;
else
return 0;
end if;
END;
create procedure PersonAdd (Ttl_char(15),
frstNm_ varchar(15),
midNm_ varchar(15),
lstNm_ varchar(15),
Gender_ char(1),
dob_char(8),/* mmdd */
nicNo_ varchar(13),
mrtlStts_ char(1),
cellNo_ varchar(15),
website_varchar(80),
machine_no_ int)
begin
insert into Person (PersonID, Ttl, frstNm, midNm, lstNm, Gender, dob,
nicNo, mrtlStts,
cellNo, website)
select (machine_no_ * 100) + (CASE WHEN ((max(PersonID)) -
((max(PersonID) div 100) * 100)) is null Then
0
ELSE
((max(PersonID)) - ((max(PersonID) div 100) *
100))
END) + 1 as PersonID, Ttl_, frstNm_, midNm_, lstNm_, Gender_,
dob_, nicNo_,
mrtlStts_, cellNo_, website_
from Person;
end;
create trigger Person_Check_bi
before insert on Person
for each row
begin
if new.gender 'M' and new.gender 'F' then
set @errmsg = 'Gender value not equal to either M or F';
insert into tmp1 (checkCol) values (1);
end if;
if new.dob '19000101' then
set @errmsg = 'Date of birth set far back in time! Less than
1900';
insert into tmp1 (checkCol) values (1);
end if;
if new.mrtlStts 'S' and
new.mrtlStts 'M' and
new.mrtlStts 'D' and
new.mrtlStts 'W' then
set @errmsg = 'Marital Status not equal to one of S,M,D,W -
Single,
Married, Divorced and Widowed';
insert into tmp1 (checkCol) values (1);
end if;
if CharValIsNumeric (ltrim(rtrim(new.nicno)))=0 then
set @errmsg = 'NIC No contains non-numeric characters';
insert into tmp1 (checkCol) values (1);
end if;
if CharValIsNumeric (ltrim(rtrim(new.cellno)))=0 then
set @errmsg = 'Cell/Mobile No contains non-numeric characters';
insert into tmp1 (checkCol) values (1);
end if;
end;
create trigger Person_Check_bu
before update on Person
for each row
begin
if new.gender 'M' and new.gender 'F' then
set @errmsg = 'Gender value not equal to either M or F';
insert into tmp1 (checkCol) values (1);
end if;
if new.dob '19000101' then
set @errmsg = 'Date of birth set far back in time! Less than
1900';
insert into tmp1 (checkCol) values (1);
end if;
if new.mrtlStts 'S' and
new.mrtlStts 'M' and
new.mrtlStts 'D' and
new.mrtlStts 'W' then
set @errmsg = 'Marital Status not equal to one of S,M,D,W -
Single,
Married, Divorced
problem on a trigger
Hello, i'm new about triggers... I'm trying to create one trigger: it may insert into a column the user that insert the record... my table- CREATE TABLE `test`.`t2` ( `s1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, `utente` VARCHAR(80) NOT NULL DEFAULT '', PRIMARY KEY(`s1`) ) ENGINE = InnoDB; then my trigger- create trigger inserisci_t after insert on t2 for each row update t2 set utente= current_user where s1= NEW.s1; When i try to insert:- insert into t2(s1) values (8); it responds:- Can't update table 't2' in stored function/trigger because it is already used by statement which invoked this function/trigger. Any suggestion about my trigger? Thank you in advance! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: problem with InnoDB
Hi, There is a problem with CPU utlization when using INNODB. The CPU utilization goes to 100% in a dual processor solaris box. With the same setup, myISAM uses only 60% of the CPU. Could someone please let me know what could be the problem. There are some other processes running on the same box. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL - HHHHangs when a stored procedure is called - 5.0.22/InnoDB/WinXP-SP2
Hi,
I am replying to my own post - only to add that in response to the
MySQL misbehaving, I just kill it and restart it. More than once, I
also restarted the service, drop the database and re-created the
database and all the tables, procedures, triggers, grants, etc. But
still when I call the stored procedure, MySQL hangs.
Any help?
--
Thanks in advance,
Asif
On 8/16/06, Asif Lodhi [EMAIL PROTECTED] wrote:
Hi,
I have the following database objects in a purely InnoDB database:
--
CREATE TABLE Person (
PersonIDint not null PRIMARY KEY,
Ttl char(15),
FllNm varchar(50),
frstNm varchar(15) not null,
midNm varchar(15),
lstNm varchar(15) not null,
Gender char(1) not null DEFAULT 'M' CHECK Gender in
('M','F'),
dob dateCHECK DOB '19000101',
nicNo varchar(13),
mrtlSttschar(1) not null DEFAULT 'M' CHECK mrtlStts in
('S','M','D','W'),
cellNo varchar(15),
website varchar(80),
Sttsint not null DEFAULT 1, /* 1=Active,
2=Inactive */
index PersonDob_ndx (dob),
index PersonNIC_ndx (nicno),
index PersonCell_ndx (cellNo),
index PersonFllNm_ndx (FllNm),
index PersonNm_ndx (frstNm, midNm, lstNm)
) ENGINE=InnoDB;
CREATE FUNCTION CharValIsNumeric (v VARCHAR(15))
RETURNS boolean
BEGIN
declare i, l int(2);
set l=char_length(v);
set i=1;
while (i = l) and (substring(v,i,1) in
('1','2','3','4','5','6','7','8','9','0')) do
set i=i+1;
end while;
IF i l THEN
return 1;
else
return 0;
end if;
END;
create procedure PersonAdd (Ttl_char(15),
frstNm_ varchar(15),
midNm_ varchar(15),
lstNm_ varchar(15),
Gender_ char(1),
dob_char(8),/* mmdd */
nicNo_ varchar(13),
mrtlStts_ char(1),
cellNo_ varchar(15),
website_varchar(80),
machine_no_ int)
begin
insert into Person (PersonID, Ttl, frstNm, midNm, lstNm, Gender, dob,
nicNo, mrtlStts,
cellNo, website)
select (machine_no_ * 100) + (CASE WHEN ((max(PersonID)) -
((max(PersonID) div 100) * 100)) is null Then
0
ELSE
((max(PersonID)) - ((max(PersonID) div 100) *
100))
END) + 1 as PersonID, Ttl_, frstNm_, midNm_, lstNm_, Gender_,
dob_,
nicNo_,
mrtlStts_, cellNo_, website_
from Person;
end;
create trigger Person_Check_bi
before insert on Person
for each row
begin
if new.gender 'M' and new.gender 'F' then
set @errmsg = 'Gender value not equal to either M or F';
insert into tmp1 (checkCol) values (1);
end if;
if new.dob '19000101' then
set @errmsg = 'Date of birth set far back in time! Less than
1900';
insert into tmp1 (checkCol) values (1);
end if;
if new.mrtlStts 'S' and
new.mrtlStts 'M' and
new.mrtlStts 'D' and
new.mrtlStts 'W' then
set @errmsg = 'Marital Status not equal to one of S,M,D,W -
Single,
Married, Divorced and Widowed';
insert into tmp1 (checkCol) values (1);
end if;
if CharValIsNumeric (ltrim(rtrim(new.nicno)))=0 then
set @errmsg = 'NIC No contains non-numeric characters';
insert into tmp1 (checkCol) values (1);
end if;
if CharValIsNumeric (ltrim(rtrim(new.cellno)))=0 then
set @errmsg = 'Cell/Mobile No contains non-numeric characters';
insert into tmp1 (checkCol) values (1);
end if;
end;
create trigger Person_Check_bu
before update on Person
for each row
begin
if new.gender 'M' and new.gender 'F' then
set @errmsg = 'Gender value not equal to either M or F';
insert into tmp1 (checkCol) values (1);
end if;
if new.dob '19000101' then
RE: problem with InnoDB
Hi, There is a problem with CPU utlization when using INNODB. The CPU utilization goes to 100% in a dual processor solaris box. With the same setup, myISAM uses only 60% of the CPU. Could someone please let me know what could be the problem. There are some other processes running on the same box. So it is making the other processes slower. Thanks Prasad The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ddmmyyyy-format date hangs MySQL - 5.0.22/InnoDB/WinXP-SP2
Hi,
In my earlier post, I was making a mistake (though I didn't do so in
the posted text!) - I was passing the dob (the date field) in the
ddmm format. When I passed the date field in mmdd format, the
stored procedure ran fine and the record got inserted. The problem is
MySQL hangs/goes in limbo quitely instead of throwing back an error.
Certainly smells like a bug. ???
I have psted the text of my original post at the end of this message.
--
Asif
I have the following database objects in a purely InnoDB database:
--
CREATE TABLE Person (
PersonIDint not null PRIMARY KEY,
Ttl char(15),
FllNm varchar(50),
frstNm varchar(15) not null,
midNm varchar(15),
lstNm varchar(15) not null,
Gender char(1) not null DEFAULT 'M' CHECK
Gender in ('M','F'),
dob dateCHECK DOB '19000101',
nicNo varchar(13),
mrtlSttschar(1) not null DEFAULT 'M' CHECK
mrtlStts in ('S','M','D','W'),
cellNo varchar(15),
website varchar(80),
Sttsint not null DEFAULT 1, /*
1=Active, 2=Inactive */
index PersonDob_ndx (dob),
index PersonNIC_ndx (nicno),
index PersonCell_ndx (cellNo),
index PersonFllNm_ndx (FllNm),
index PersonNm_ndx (frstNm, midNm, lstNm)
) ENGINE=InnoDB;
CREATE FUNCTION CharValIsNumeric (v VARCHAR(15))
RETURNS boolean
BEGIN
declare i, l int(2);
set l=char_length(v);
set i=1;
while (i = l) and (substring(v,i,1) in
('1','2','3','4','5','6','7','8','9','0')) do
set i=i+1;
end while;
IF i l THEN
return 1;
else
return 0;
end if;
END;
create procedure PersonAdd (Ttl_char(15),
frstNm_ varchar(15),
midNm_ varchar(15),
lstNm_ varchar(15),
Gender_ char(1),
dob_char(8),/* mmdd */
nicNo_ varchar(13),
mrtlStts_ char(1),
cellNo_ varchar(15),
website_varchar(80),
machine_no_ int)
begin
insert into Person (PersonID, Ttl, frstNm, midNm, lstNm, Gender, dob,
nicNo, mrtlStts,
cellNo, website)
select (machine_no_ * 100) + (CASE WHEN ((max(PersonID)) -
((max(PersonID) div 100) * 100)) is null Then
0
ELSE
((max(PersonID)) - ((max(PersonID) div 100)
* 100))
END) + 1 as PersonID, Ttl_, frstNm_, midNm_, lstNm_,
Gender_, dob_, nicNo_,
mrtlStts_, cellNo_, website_
from Person;
end;
create trigger Person_Check_bi
before insert on Person
for each row
begin
if new.gender 'M' and new.gender 'F' then
set @errmsg = 'Gender value not equal to either M or F';
insert into tmp1 (checkCol) values (1);
end if;
if new.dob '19000101' then
set @errmsg = 'Date of birth set far back in time!
Less than 1900';
insert into tmp1 (checkCol) values (1);
end if;
if new.mrtlStts 'S' and
new.mrtlStts 'M' and
new.mrtlStts 'D' and
new.mrtlStts 'W' then
set @errmsg = 'Marital Status not equal to one of
S,M,D,W - Single,
Married, Divorced and Widowed';
insert into tmp1 (checkCol) values (1);
end if;
if CharValIsNumeric (ltrim(rtrim(new.nicno)))=0 then
set @errmsg = 'NIC No contains non-numeric characters';
insert into tmp1 (checkCol) values (1);
end if;
if CharValIsNumeric (ltrim(rtrim(new.cellno)))=0 then
set @errmsg = 'Cell/Mobile No contains non-numeric characters';
insert into tmp1 (checkCol) values (1);
end if;
end;
create trigger Person_Check_bu
before update on Person
for each row
begin
if new.gender 'M' and new.gender 'F' then
set @errmsg = 'Gender value not equal to either M or F';
insert into tmp1 (checkCol) values (1);
end if;
if new.dob '19000101' then
set @errmsg = 'Date of birth set far back in time!
Less than 1900';
insert into tmp1 (checkCol) values (1);
Using Header to post data to another site
Hi all, I am working on a problem where I need to select data from my own database, and then post it to another website using the Get method. After having wasted about 4 days trying http_request, $_Get and whatnot, I stumbled upon the header command. So far, the only way I have been able to actually post data to that other site is through this header command: header (Location:$url); This has the drawback that the user gets to see the URL I am sending him to, it contains a password that the user should not see. I have tried to omit 'Location', and although it doesn't generate an error, the info also doesn't reach the intended website. I hope there is a smarter way to have PHP perform this task, without me actually having to reveal sensitive info to the user. Is there anyone willing to point me in the right direction? Kind regards, Dirk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using Header to post data to another site
Hi all, I am working on a problem where I need to select data from my own database, and then post it to another website (using the Get) method. After having wasted about 4 days trying http_request, $_Get and whatnot, I stumbled upon the header command. So far, the only way I have been able to actually post data to the other site is through this header command: header (Location:$url); This has the drawback that the user gets to see the URL I am sending him to, because it contains a password. I have tried to omit 'Location', and although it doesn't generate an error, the info also doesn't reach the intended website. I hope there is a smarter way to have PHP perform this task, without me actually having to reveal sensitive info to the user. Is there anyone willing to point me in the right direction? Kind regards, Dirk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Using Header to post data to another site
First of all, I think your post is intended to the PHP mailing list, this is the MySQL list. Assuming that you need to send information to the other web site without actually entering it, you could setup a system with a simple Web Service running on your receiving web server, and just call it from your sending web server. Search on Google for NuSOAP and you will probably find what you need, if you like that idea. /Peter -Original Message- From: Dirk Poot [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 16, 2006 9:34 PM To: mysql@lists.mysql.com Subject: Using Header to post data to another site Hi all, I am working on a problem where I need to select data from my own database, and then post it to another website (using the Get) method. After having wasted about 4 days trying http_request, $_Get and whatnot, I stumbled upon the header command. So far, the only way I have been able to actually post data to the other site is through this header command: header (Location:$url); This has the drawback that the user gets to see the URL I am sending him to, because it contains a password. I have tried to omit 'Location', and although it doesn't generate an error, the info also doesn't reach the intended website. I hope there is a smarter way to have PHP perform this task, without me actually having to reveal sensitive info to the user. Is there anyone willing to point me in the right direction? Kind regards, Dirk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table hangs on a large query
At 09:43 AM 8/15/2006, Randy Paries wrote: On 8/15/06, mos [EMAIL PROTECTED] wrote: At 09:24 AM 8/15/2006, you wrote: Dan thanks for the help so i will remove the redundant keys below is the explain plans but here is the weird part. I exported the database and imported into a testDB to play with and the query that was giving me grief in the previous message returned immediately Thanks Randy Randy, Then it could have been index corruption. You could do a back up of the table and then run a Repair Table on it (or MyISAMChk) and then Optimize it. See http://dev.mysql.com/doc/refman/5.0/en/repair.html Mike Mike Thanks for the help, I will try that this evening. Is there some sort of maintenance i should be running on this db routinely to help this? Some people will routinely do a Repair Table every time MySQL starts up just in case MySQL was shut down inappropriately. This could be a bit anal if you ask me, but it seems to work for them. There is a quick repair that does a quick test of the table(s). http://mysql.com/doc/refman/5.0/en/table-maintenance.html If you do a lot of deletes from the table then you will need to OPTIMIZE the table to remove the holes. If you want to just rebalance the index trees then use ANALYZE TABLE (shouldn't be necessary if you just did an Optimize Table). Keep in mind if you are dealing with large tables, 10 million rows, then you may need to change your My.Cnf (or my.ini) file to allocate more RAM to key_buffer_size because rebuilding indexes will need quite a bit of RAM. On my larger tables (60 million rows) it took 24+ hours to rebuild the index. After getting 3gb more RAM and incrementing key_buffer_size=1500M, the keys were rebuilt in 40 minutes or so. http://www.databasejournal.com/features/mysql/article.php/3367871 Hope this helps. Mike Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
1 to many relationships
hey guys ; it's not Customers Per issue it's the other way around issues per customer i enter in Customer 1 and then down the road i wanna add a issue associated with that customer, so goto that customer and Click Add issue the a form Comes up and i add the issue and Click Add issue, i need that issue to associate with that customer, every Customer Refrences the same issue table i have Customers /Issues as tables, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 to many relationships
Brian E Boothe wrote: hey guys ; it's not Customers Per issue it's the other way around issues per customer i enter in Customer 1 and then down the road i wanna add a issue associated with that customer, so goto that customer and Click Add issue the a form Comes up and i add the issue and Click Add issue, i need that issue to associate with that customer, every Customer Refrences the same issue table Then it is many to many. Many customers are associated with many issues. i have Customers /Issues as tables, -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 to many relationships
Gerald L. Clark said: Brian E Boothe wrote: hey guys ; it's not Customers Per issue it's the other way around issues per customer i enter in Customer 1 and then down the road i wanna add a issue associated with that customer, so goto that customer and Click Add issue the a form Comes up and i add the issue and Click Add issue, i need that issue to associate with that customer, every Customer Refrences the same issue table Then it is many to many. Many customers are associated with many issues. i have Customers /Issues as tables, -- Gerald L. Clark Supplier Systems Corporation I beg to differ. It would be a many to many if several customers were related to the same issue. If each customer can have one or more issues, but no issue can 'belong' to more than one customer then its 1 to many and you can put the customer ID in the issues table. If several customers can have the same issue (row) then you will need an intermediate table which has two columns: customerID and issueID. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary tables
You were right. Its a global privilege not a table one. I granted it at a global level. it can't be granted at the databae level Dan Buettner wrote: Curtis, you might need to make sure 'admin'@'localhost' has 'FILE' privileges in the proper database, for load data infile. Note that 'admin'@'%' is not the same as 'admin'@'localhost' See http://dev.mysql.com/doc/refman/5.0/en/grant.html Also here is a comment from the same page: QUOTE Posted by Cristian Gafton on July 17 2006 6:28pm [Delete] [Edit] Please note that the current MySQL priviledge model does not support creating a read-only user for an application that needs to work with temporary tables. In other words, the 'SELECT,CREATE TEMPORARY TABLES' privileges are not sufficient to let an application work with temporary tables against a read-only set of tables. MySQL also requires INSERT, DELETE and UPDATE grants to allow temporary tables to be used. Since the temporary tables are not 'defined' at the time of the grant, one would have no choice but to grant INSERT,DELETE,UPDATE on db.*, which negates the possibility of a read-only user. It would be nice if MySQL would have a more generic 'TEMPORARY TABLES' permission that would allow one to create, insert, delete from and drop temporary tables without having to give up insert/update/delete privileges on the real tables /QUOTE Dan On 8/15/06, Curtis Maurand [EMAIL PROTECTED] wrote: here is what I get. I'm logged in as the user admin (really a regular user.) mysql create temporary table customer_tmp as select * from customer limit 0; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql load data infile '/home/bluetarp/ezauth/customers.txt' into table customer_tmp; ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES) Dan Buettner wrote: Or possibly that the mysql user on the box does not have access to the data file in question. Can you post the error messages you get? Dan On 8/15/06, Curtis Maurand [EMAIL PROTECTED] wrote: This may sound like a stupid question, but I have to ask. I've been running a script that goes like the following. use ecommerce; create temporary table customer_tmp as select * from customer limit 0; load data infile '/home/bluetarp/ezauth/customers.txt' into table \customer_tmp; at which point the script fails for permssion reasons. It seems that this user can create a temporary table, but not load data into it? What did I miss on permissions to allow this to work? Thanks Curtis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query needed
VenuGopal Papasani wrote:
Once again i send the table data:
Code Period Value
c12004 22
c12005 10
c22005 15
c32005 20
c42005 15
c52005 5
c62005 30
c72005 25
c12006 5
c22006 15
c32006 40
c42006 30
From this I need the sum of values for period 2005 and codes c1+c2-c4-c5
(this is not constant its just an example there is lot of codes like
this..)
For ex:- the result that I want to get is:
the value for code c1, period 2005 is 10
for code c2, period 2005 is 15
sum of c1 and c2 is 10 + 15 = 25
The value for code c4, period 2005 is 15
for code c5, period 2005 is 5
Sum of c4 and c5 is 15 + 5 = 20
Finally the result is (c1+c2) - (c4-c5) = 25 - 20 = 5
You mean, (c1+c2) - (c4+c5), right?
Peter Lauri wrote:
SELECT SUM(IF(code='c1', code, IF(code='c2', code, 0))) - SUM(IF(code='c4',
code, IF(code='c5', code, 0))) FROM datavalue;
You can simplify this using IN. Also, we need to sum the values, not the codes.
SELECT SUM(IF(code IN ('c1', 'c2'), value, 0))
- SUM(IF(code IN ('c4', 'c5'), value, 0)) AS total
FROM datavalue
WHERE period = 2005;
VenuGopal Papasani wrote:
if it is static then it works fine.but we have lots of codes in a table
which should be done similar operation.instead varifying staticly with
c1,c2
can we make dynamic.
Well,
SELECT SUM(IF(code IN (list of + codes), value, 0))
- SUM(IF(code IN (list of - codes), value, 0)) AS total
FROM datavalue
WHERE period = 2005;
seems simple enough, especially if this is to be truly dynamic.
Another method would be to store a coefficient for each code in a (perhaps
temporary) table.
CREATE TABLE code_values (code CHAR(2), coeff INT);
Set coeff to 1 for codes which specify addition, and -1 for codes that specify
subtraction. For example, to get c1 + c2 - c4 - c5:
INSERT INTO code_values VALUES ('c1', 1), ('c2', 1), ('c4', -1), ('c5', -1);
then join the tables to get your result:
SELECT period, SUM(d.value * cv.coeff) AS total
FROM datavalue d
JOIN code_values cv ON (d.code = cv.code)
WHERE d.period = 2005
GROUP by d.period;
++-+
| period | SUM(d.value * cv.coeff) |
++-+
| 2005 | 5 |
++-+
I would guess that you have a number of standard queries you must run from time
to time. In that case, you could store the coefficients of each query in a
permanent table. For example, if c1 + c2 - c4 - c5 and c6 + c7 - c3 were
two standard sums, you could do something like (results are using your sample
data above):
CREATE TABLE sum_queries (query_id INT, code CHAR(2), coeff INT);
INSERT INTO sum_queries VALUES
(1, 'c1', 1), (1, 'c2', 1), (1, 'c4', -1), (1, 'c5', -1),
(2, 'c6', 1), (2, 'c7', 1), (2, 'c3', -1);
SELECT period, SUM(d.value * sq.coeff) AS total
FROM datavalue d
JOIN sum_queries sq ON (d.code = sq.code)
WHERE d.period = 2005
AND sq.query_id = 1
GROUP by d.period;
++---+
| period | total |
++---+
| 2005 | 5 |
++---+
SELECT period, SUM(d.value * sq.coeff) AS total
FROM datavalue d
JOIN sum_queries sq ON (d.code = sq.code)
WHERE d.period = 2005
AND sq.query_id = 2
GROUP by d.period;
++---+
| period | total |
++---+
| 2005 |35 |
++---+
Of course, a query description table linked by query_id would be a good idea in
this case.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Random Error
Hello, occassionally we come accross this error: 2013 - Lost connection to MySQL server during query not all the time, but now and then, no real pattern that we can see of find in the logs. but appear to happen mainly with SELECT's is there a way to fix this or trace it down to a cause? TIA Mike(mickalo)Blezien === Thunder Rain Internet Publishing Providing Internet Solution that Work http://www.thunder-rain.com === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random Error
Mike, can you provide more details? OS platform, networking overview, types of apps experiencing this error (PHP app, mysql cli, etc), does it happen with local as well as network connections, etc. This will help us help you. Dan On 8/16/06, Mike Blezien [EMAIL PROTECTED] wrote: Hello, occassionally we come accross this error: 2013 - Lost connection to MySQL server during query not all the time, but now and then, no real pattern that we can see of find in the logs. but appear to happen mainly with SELECT's is there a way to fix this or trace it down to a cause? TIA Mike(mickalo)Blezien === Thunder Rain Internet Publishing Providing Internet Solution that Work http://www.thunder-rain.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: Random Error
It's PHP I believe, oscommerce web site, MySQL 4.0.27. this is for an associate of ours and I am not much of a PHP programmer, strickly Perl :) Mike - Original Message - From: Dan Buettner [EMAIL PROTECTED] To: Mike Blezien [EMAIL PROTECTED] Cc: MySQL List mysql@lists.mysql.com Sent: Wednesday, August 16, 2006 3:24 PM Subject: Re: Random Error Mike, can you provide more details? OS platform, networking overview, types of apps experiencing this error (PHP app, mysql cli, etc), does it happen with local as well as network connections, etc. This will help us help you. Dan On 8/16/06, Mike Blezien [EMAIL PROTECTED] wrote: Hello, occassionally we come accross this error: 2013 - Lost connection to MySQL server during query not all the time, but now and then, no real pattern that we can see of find in the logs. but appear to happen mainly with SELECT's is there a way to fix this or trace it down to a cause? TIA Mike(mickalo)Blezien === Thunder Rain Internet Publishing Providing Internet Solution that Work http://www.thunder-rain.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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random Error
At first blush, I'd look into a few different possibilities - - An idle connection timeout is being enforced on a persistent connection pool - Too many individual connections are overwhelming a Windows server (see http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server-on-windows.html) - Flaky networking between db server and client (web server most likely in this case). Could be physical or logical (or both). Dan On 8/16/06, Mike Blezien [EMAIL PROTECTED] wrote: It's PHP I believe, oscommerce web site, MySQL 4.0.27. this is for an associate of ours and I am not much of a PHP programmer, strickly Perl :) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random Error
A little more searching also uncovered this, which appears to be right up your alley: http://dev.mysql.com/doc/refman/4.1/en/gone-away.html On 8/16/06, Dan Buettner [EMAIL PROTECTED] wrote: At first blush, I'd look into a few different possibilities - - An idle connection timeout is being enforced on a persistent connection pool - Too many individual connections are overwhelming a Windows server (see http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server-on-windows.html) - Flaky networking between db server and client (web server most likely in this case). Could be physical or logical (or both). Dan On 8/16/06, Mike Blezien [EMAIL PROTECTED] wrote: It's PHP I believe, oscommerce web site, MySQL 4.0.27. this is for an associate of ours and I am not much of a PHP programmer, strickly Perl :) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Header to post data to another site
At 09:34 AM 8/16/2006, you wrote: Hi all, I am working on a problem where I need to select data from my own database, and then post it to another website (using the Get) method. After having wasted about 4 days trying http_request, $_Get and whatnot, I stumbled upon the header command. So far, the only way I have been able to actually post data to the other site is through this header command: header (Location:$url); This has the drawback that the user gets to see the URL I am sending him to, because it contains a password. I have tried to omit 'Location', and although it doesn't generate an error, the info also doesn't reach the intended website. I hope there is a smarter way to have PHP perform this task, without me actually having to reveal sensitive info to the user. Is there anyone willing to point me in the right direction? Kind regards, Dirk Dirk, I've come up with 2 solutions. I'd try and create a temporary cookie on the user's machine before going to the other site, then the site can read the username/pw in the cookie. Of course you should find some way of encrypting the cookie contents to prevent someone from reading it. MD5()? Of course the username/pw must exist on both webservers which could be a pain to maintain. And of course you'd have to prevent him from re-using the cookie later on or decoding the original username/pw etc.. So using a cookie has a slight security risk. You could also have your web page (webserver #1) generate a unique random id (maybe an MD5 based on the his PHP session # or some other unique random ID). But don't send this info to the user just yet! Instead have your webserver #1 contact webserver #2 using either MySQL via TCP/IP or some other 3rd party program see http://dev.mysql.com/doc/refman/5.0/en/windows-and-ssh.html, and store this random id in the database of webserver #2. Then and only then redirect the user to webserver #2 with this random id in the URL. You should of course use SSH when contacting the other MySQL server. This tells webserver#2 to expect a user in the next 2 minutes to allow him to connect to web server #2 if it finds this random id in the table. After 2 minutes the id will expire. And of course after the user connects to webserver#2, you would delete the random id (or have it expire in 24 hours etc.-it's up to you). Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Header to post data to another site
It sounds as if you're using the PHP HTTP module. You probably want the HTTP_Client module instead as it has a method for doing POSTs. In general you should use GET for HTTP requests which only solicit information from another server and don't request that server to change anything stored on it. For requests that change data on another server, you should use POST. Here is some documentation for HTTP_Client: http://pear.php.net/ manual/en/html/package.http.http-client.html I've pretty much gotten away from sending any username/password pairs in unencrypted http. If you send HTTPS, the header part is not encrypted but the body is.* If you send cookies they are in the header and thus subject to snooping, but since authentication cookies should be only valid for a limited time, I don't think this is too much of a problem. I think if I were doing what you describe I would use HTTPS and put both the un/pw and data in the body of the request, rather than trying to send one request to get a cookie and then use that cookie to send the actual data. Douglas Sims [EMAIL PROTECTED] *er, did I state that correctly? On Aug 16, 2006, at 4:27 PM, mos wrote: At 09:34 AM 8/16/2006, you wrote: Hi all, I am working on a problem where I need to select data from my own database, and then post it to another website (using the Get) method. After having wasted about 4 days trying http_request, $_Get and whatnot, I stumbled upon the header command. So far, the only way I have been able to actually post data to the other site is through this header command: header (Location:$url); This has the drawback that the user gets to see the URL I am sending him to, because it contains a password. I have tried to omit 'Location', and although it doesn't generate an error, the info also doesn't reach the intended website. I hope there is a smarter way to have PHP perform this task, without me actually having to reveal sensitive info to the user. Is there anyone willing to point me in the right direction? Kind regards, Dirk Dirk, I've come up with 2 solutions. I'd try and create a temporary cookie on the user's machine before going to the other site, then the site can read the username/pw in the cookie. Of course you should find some way of encrypting the cookie contents to prevent someone from reading it. MD5()? Of course the username/ pw must exist on both webservers which could be a pain to maintain. And of course you'd have to prevent him from re-using the cookie later on or decoding the original username/pw etc.. So using a cookie has a slight security risk. You could also have your web page (webserver #1) generate a unique random id (maybe an MD5 based on the his PHP session # or some other unique random ID). But don't send this info to the user just yet! Instead have your webserver #1 contact webserver #2 using either MySQL via TCP/IP or some other 3rd party program see http:// dev.mysql.com/doc/refman/5.0/en/windows-and-ssh.html, and store this random id in the database of webserver #2. Then and only then redirect the user to webserver #2 with this random id in the URL. You should of course use SSH when contacting the other MySQL server. This tells webserver#2 to expect a user in the next 2 minutes to allow him to connect to web server #2 if it finds this random id in the table. After 2 minutes the id will expire. And of course after the user connects to webserver#2, you would delete the random id (or have it expire in 24 hours etc.-it's up to you). Mike -- 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: problem with InnoDB
[EMAIL PROTECTED] wrote: Hi, There is a problem with CPU utlization when using INNODB. The CPU utilization goes to 100% in a dual processor solaris box. With the same setup, myISAM uses only 60% of the CPU. As I said before, stuff like 'count(*)' queries cannot use an index in innodb - if you are doing a lot of those type of queries, it will cause a problem. Others include max(), min() (any aggregate function actually). http://dev.mysql.com/doc/refman/5.1/en/innodb-tuning.html The easiest way to work out what queries are causing the problem is to enable the slow-log and go through that, 'explain'ing each query and adding indexes where necessary. http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 to many relationships
Brian E Boothe wrote: hey guys ; it's not Customers Per issue it's the other way around issues per customer i enter in Customer 1 and then down the road i wanna add a issue associated with that customer, so goto that customer and Click Add issue the a form Comes up and i add the issue and Click Add issue, i need that issue to associate with that customer, every Customer Refrences the same issue table i have Customers /Issues as tables, As William said you have a 1 to many relationship so you can get away with this as a very simple example: create table customers (customerid int auto_increment primary key, customername varchar(255)); create table issues (issueid int auto_increment primary key, issuetitle varchar(255), customerid int); then you have a simple inner join to find the issues that each customer has. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL - HHHHangs when a stored procedure is called - 5.0.22/InnoDB/WinXP-SP2
CREATE FUNCTION CharValIsNumeric (v VARCHAR(15))
RETURNS boolean
BEGIN
declare i, l int(2);
set l=char_length(v);
set i=1;
while (i = l) and (substring(v,i,1) in
('1','2','3','4','5','6','7','8','9','0')) do
set i=i+1;
end while;
What if the string (v) doesn't contain a number or is null?
I'm guessing that's your problem - it's getting into that while loop and
never able to get out.
Maybe you're better off using a regular expression here:
http://dev.mysql.com/doc/refman/5.1/en/regexp.html
http://dev.mysql.com/doc/refman/5.1/en/pattern-matching.html
http://dev.mysql.com/doc/refman/5.1/en/string-comparison-functions.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Very long pauses on particular tables
Hi all. I discovered a very strange and painful problem on a customer's system last night. I have built a gtk2-perl GUI front-end for accessing data in a MySQL database. It works remarkably well on my laptop, and was working remarkably well on their system as well, but something has recently happened to the system. Now when we open forms based on particular tables, it takes a VERY long time to perform a VERY simple query ( ie select * from table_name ). The tables themselves only have a few thousand records ( 3,000 I think ). It takes about 5 minutes to open the form. There's nothing weird in the data - basically all numeric and varchar stuff - no big text or binary fields or anything. It was working *perfectly* last time I was there, I swear! I've tried using the command-line client, from my laptop, to connect to their MySQL server. To my surprise, executing queries on ALL tables is instantaneous. But when I fire up the GUI ( which is using DBD::mysql ), on my laptop, the problem re-surfaces. Why would connecting with the command-line client give me instantaneous queries, but connecting via DBD::mysql display these huge pauses? I then tried pointing their GUI at my laptop's MySQL installation. Guess what? No more delays. So I've isolated the problem to being something to do with their server. Or have I? At this point, I should probably point out that there is some *strange* stuff going on in their /etc/hosts. They've got all the hostnames that refer to the server on the '127.0.0.1' line. When I told them they needed to have 127.0.0.1 just for 'localhost' and they needed to add the IP address of the server against the FQDN they said that this breaks a number of other things :( I didn't have time to sort through these 'other things', unfortunately, but anyway, could this be causing problems? If so, why does it only affect certain tables? There are 2 tables that it affects. It takes about 5 seconds for the app to make the initial MySQL connection, and I was under the impression that networking ( ie /etc/hosts ) issues would have their biggest affect at this point, and that after logging in, things should be OK. I have done a full 'check table' thing ( from MySQL Administrator ), and it replied that there was no problem in any table. When I restarted MySQL, it had some ... issues ... shutting down and starting up again, but came up eventually. Unfortunately logging was not enabled at this point :-| I didn't set this thing up ... I'm probably looking at a re-install of MySQL ( it's currently at 4.1.10 ), and a restore of their data, right? Can anyone comment on whether their wacko /etc/hosts could be causing us problems? Any other ideas? -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very long pauses on particular tables
Daniel Kasak wrote: Hi all. I discovered a very strange and painful problem on a customer's system last night. I have built a gtk2-perl GUI front-end for accessing data in a MySQL database. It works remarkably well on my laptop, and was working remarkably well on their system as well, but something has recently happened to the system. Now when we open forms based on particular tables, it takes a VERY long time to perform a VERY simple query ( ie select * from table_name ). The tables themselves only have a few thousand records ( 3,000 I think ). It takes about 5 minutes to open the form. There's nothing weird in the data - basically all numeric and varchar stuff - no big text or binary fields or anything. It was working *perfectly* last time I was there, I swear! I've tried using the command-line client, from my laptop, to connect to their MySQL server. To my surprise, executing queries on ALL tables is instantaneous. But when I fire up the GUI ( which is using DBD::mysql ), on my laptop, the problem re-surfaces. Why would connecting with the command-line client give me instantaneous queries, but connecting via DBD::mysql display these huge pauses? I then tried pointing their GUI at my laptop's MySQL installation. Guess what? No more delays. So I've isolated the problem to being something to do with their server. Or have I? At this point, I should probably point out that there is some *strange* stuff going on in their /etc/hosts. They've got all the hostnames that refer to the server on the '127.0.0.1' line. When I told them they needed to have 127.0.0.1 just for 'localhost' and they needed to add the IP address of the server against the FQDN they said that this breaks a number of other things :( I didn't have time to sort through these 'other things', unfortunately, but anyway, could this be causing problems? If so, why does it only affect certain tables? There are 2 tables that it affects. It takes about 5 seconds for the app to make the initial MySQL connection, and I was under the impression that networking ( ie /etc/hosts ) issues would have their biggest affect at this point, and that after logging in, things should be OK. I have done a full 'check table' thing ( from MySQL Administrator ), and it replied that there was no problem in any table. When I restarted MySQL, it had some ... issues ... shutting down and starting up again, but came up eventually. Unfortunately logging was not enabled at this point :-| I didn't set this thing up ... I'd see if you can get logging enabled. When the pauses happen try a 'show process list;' and see what else mysql is doing at the time. Maybe you have a deadlock situation - client a and b are trying to access the same table at the same time and it's conflicting in a strange way. The process list may give you some further ideas. Are you using the ip address to connect to the server or a name? Can the server resolve the names that are connecting to it? (ie is reverse dns set up properly). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very long pauses on particular tables
Chris wrote: I'd see if you can get logging enabled. I will do that next visit, yes. I was pushed for time - in between 2 other commitments, and wasn't expecting problems. When the pauses happen try a 'show process list;' and see what else mysql is doing at the time. Maybe you have a deadlock situation - client a and b are trying to access the same table at the same time and it's conflicting in a strange way. The process list may give you some further ideas. I didn't think of that, but I did restart MySQL a couple of times, and this was after-hours, so I would be surprised if anyone else was on the system at the time. But I'll do that too, thanks. Are you using the ip address to connect to the server or a name? I tried both. They were using a FQDN with an internal DNS server, but I switched it to using the IP address. It didn't affect anything. Can the server resolve the names that are connecting to it? (ie is reverse dns set up properly). Um. Don't know that one. I'll forward this off to their IT guy and see. Thanks for the quick response :) Dan -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very long pauses on particular tables
Daniel Kasak wrote: Chris wrote: I'd see if you can get logging enabled. I will do that next visit, yes. I was pushed for time - in between 2 other commitments, and wasn't expecting problems. When the pauses happen try a 'show process list;' and see what else mysql is doing at the time. Maybe you have a deadlock situation - client a and b are trying to access the same table at the same time and it's conflicting in a strange way. The process list may give you some further ideas. I didn't think of that, but I did restart MySQL a couple of times, and this was after-hours, so I would be surprised if anyone else was on the system at the time. But I'll do that too, thanks. Are you using the ip address to connect to the server or a name? I tried both. They were using a FQDN with an internal DNS server, but I switched it to using the IP address. It didn't affect anything. Can the server resolve the names that are connecting to it? (ie is reverse dns set up properly). Um. Don't know that one. I'll forward this off to their IT guy and see. Could be a simple network issue too.. are pings fast/slow? The reason I asked about that last one was some of the suggestions on this page: http://www.debuntu.org/2006/07/21/75-how-to-optimize-mysql-response-time/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very long pauses on particular tables
Chris wrote: Could be a simple network issue too.. are pings fast/slow? Pings are very fast. The reason I asked about that last one was some of the suggestions on this page: http://www.debuntu.org/2006/07/21/75-how-to-optimize-mysql-response-time/ OK. I've had a read of that, and it seems to make sense ... apart from the fact that this only seems to affect particular tables. But anyway, I'll certainly DNS lookups for MySQL. Thanks for your help. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very long pauses on particular tables
Daniel Kasak wrote: Chris wrote: Could be a simple network issue too.. are pings fast/slow? Pings are very fast. The reason I asked about that last one was some of the suggestions on this page: http://www.debuntu.org/2006/07/21/75-how-to-optimize-mysql-response-time/ OK. I've had a read of that, and it seems to make sense ... apart from the fact that this only seems to affect particular tables. But anyway, I'll certainly DNS lookups for MySQL. Missed that bit.. try an optimize or analyze over the slow tables. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AW: find in list
Charlie Schaubmair wrote:
Hello,
I want to do a query where I only gt the results by a numeric value:
select * from MyTable where 1 IN someFieldInMyTable
I know this query doesn't work, but maybe anyone knows what I mean.
1 can be a value betwenn 1 and 23
someFieldInMyTable is normaly a list like: 1,2,3,4,5,6,9,21
Mike van Hoof wrote:
try:
SELECT * FROM MyTable WHERE someFieldInMyTable='%1%'
This will also give you results like 21
What I usally do in these cases is build the values like this:
[1][2][3][21]
And then the query:
SELECT * FROM MyTable WHERE someFieldInMyTable='%[1]%'
Mike
Charlie Schaubmair wrote:
Hello Mike,
thx, but isn't there another way?
Mike van Hoof wrote:
maybe there is, but i don't know why... because when you do a like query
it also finds the 21 when you do a like on the 1.
what you maybe can do (not tested) is:
SELECT *, CONCAT(',',someFieldInMyTable,',') as wherefield FROM MyTable
WHERE wherefield='%,1,%'
Now your someFieldInMyTable is CONCATed to ,1,2,3,4,5,6,9,21, and over
that value the where clause is done...
Mike van Hoof wrote:
Sorry, query was wrong... has to be:
SELECT *, CONCAT(',',someFieldInMyTable,',') as wherefield FROM MyTable
WHERE wherefield LIKE '%,1,%'
You've just discovered why this is not the way to store a list of attributes.
Stuffing multiple values in a single cell is a bad idea. The correct way to
implement this is to store the attributes in a separate table, one per row.
That is, instead of
MyTable
===
MyTable_id other columns attributes
1 ... 1,2,3,4,5,6,9,21
2 ... 5,7,13
you would do this:
MyTable_attributes
===
MyTable_id other columns
1 ...
2 ...
MyTable_attributes
==
MyTable_id attribute
1 1
1 2
1 3
1 4
1 5
1 6
1 9
1 21
2 5
2 7
2 13
Then finding rows in MyTable which have attribute 1 becomes trivial:
SELECT m.*
FROM MyTable m
JOIN MyTable_attributes ma ON m.MyTable_id = ma.MyTable_id
WHERE ma.attribute = 1;
With a UNIQUE index on (attribute, MyTable_id) in MyTable_attributes, this will
be very quick.
That said, you can find what you want with your current schema using the
FIND_IN_SET() function
http://dev.mysql.com/doc/refman/4.1/en/string-functions.html.
SELECT *
FROM MyTable_attributes
WHERE FIND_IN_SET(1, someFieldInMyTable);
This works so long as the values in someFieldInMyTable are separated by commas.
If you switch to some other separator, such as enclosing attributes in
brackets, it won't work.
Note that no index on someFieldInMyTable can be used for this query, however, so
it requires a full-table scan.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: AW: find in list
Hello Michael,
thx, I know normalisation.
BUT normalisation isn't always the best (fastest) way to store, or select
the datas, this is why I don't use it most time.
Often I'm testing my projects with normalisation and without and my last
very big problem with big select statements is very fast without
normalisation and with normalisation it was very slow.
br
Charlie
-Ursprüngliche Nachricht-
Von: Michael Stassen [mailto:[EMAIL PROTECTED]
Gesendet: Donnerstag, 17. August 2006 05:00
An: mysql@lists.mysql.com
Cc: Mike van Hoof; Charlie Schaubmair
Betreff: Re: AW: find in list
Charlie Schaubmair wrote:
Hello,
I want to do a query where I only gt the results by a
numeric value:
select * from MyTable where 1 IN someFieldInMyTable I
know this query doesn't work, but maybe anyone knows what I mean.
1 can be a value betwenn 1 and 23
someFieldInMyTable is normaly a list like: 1,2,3,4,5,6,9,21
Mike van Hoof wrote:
try:
SELECT * FROM MyTable WHERE someFieldInMyTable='%1%'
This will also give you results like 21 What I
usally do in these cases is build the values like this:
[1][2][3][21]
And then the query:
SELECT * FROM MyTable WHERE someFieldInMyTable='%[1]%'
Mike
Charlie Schaubmair wrote:
Hello Mike,
thx, but isn't there another way?
Mike van Hoof wrote:
maybe there is, but i don't know why... because when you
do a like query it also finds the 21 when you do a like on the 1.
what you maybe can do (not tested) is:
SELECT *, CONCAT(',',someFieldInMyTable,',') as wherefield
FROM MyTable WHERE wherefield='%,1,%'
Now your someFieldInMyTable is CONCATed to
,1,2,3,4,5,6,9,21, and over that value the where clause is done...
Mike van Hoof wrote:
Sorry, query was wrong... has to be:
SELECT *, CONCAT(',',someFieldInMyTable,',') as wherefield
FROM MyTable WHERE wherefield LIKE '%,1,%'
You've just discovered why this is not the way to store a
list of attributes.
Stuffing multiple values in a single cell is a bad idea. The
correct way to implement this is to store the attributes in a
separate table, one per row.
That is, instead of
MyTable
===
MyTable_id other columns attributes
1 ... 1,2,3,4,5,6,9,21
2 ... 5,7,13
you would do this:
MyTable_attributes
===
MyTable_id other columns
1 ...
2 ...
MyTable_attributes
==
MyTable_id attribute
1 1
1 2
1 3
1 4
1 5
1 6
1 9
1 21
2 5
2 7
2 13
Then finding rows in MyTable which have attribute 1 becomes trivial:
SELECT m.*
FROM MyTable m
JOIN MyTable_attributes ma ON m.MyTable_id = ma.MyTable_id
WHERE ma.attribute = 1;
With a UNIQUE index on (attribute, MyTable_id) in
MyTable_attributes, this will be very quick.
That said, you can find what you want with your current
schema using the
FIND_IN_SET() function
http://dev.mysql.com/doc/refman/4.1/en/string-functions.html.
SELECT *
FROM MyTable_attributes
WHERE FIND_IN_SET(1, someFieldInMyTable);
This works so long as the values in someFieldInMyTable are
separated by commas.
If you switch to some other separator, such as enclosing
attributes in brackets, it won't work.
Note that no index on someFieldInMyTable can be used for this
query, however, so it requires a full-table scan.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
