RE: Python related MySQL question

[mathias fatene]

Look at db.use_result() and db.store_result() here :
http://www.birgerblixt.com/doc/packages/python-mysql/MySQLdb-2.html#ss2.
2

Best Regards

Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-Original Message-
From: Smelly Socks [mailto:[EMAIL PROTECTED] 
Sent: mardi 26 avril 2005 06:20
To: mysql@lists.mysql.com
Subject: Re: Python related MySQL question


Hi!

I am porting a function library app I wrote in PHP to Python.  At work
they only use Python.  I've researched how to connect to a MySql
database using Python, and how to retrieve rows.  However, I am
wondering how to do the
following:

I can do this in Python =>  $da=MYSQL_QUERY("select *  from prefs where
user_name='$user_name'  "); I can do this in Python =>
$peek=mysql_fetch_array($da);

I cannot do the following:
$title   =$peek[4];  //title window
$logic   =$peek[5];  //logic window

Can anyone shed light on how to get the pieces of the array and stick
them into variables?

Thanks very much!

Cheers!

-Warren




- Original Message -
From: "Spenser" <[EMAIL PROTECTED]>
To: "David Bailey" <[EMAIL PROTECTED]>
Cc: 
Sent: Thursday, April 14, 2005 12:27 PM
Subject: Re: book advice


> Check out "MySQL Tutorial" by Luke Welling (MySQL Press).  It's easy 
> to understand and not overwhelming.
>
>
> --
> 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: Crosstab in Mysql

[Alvaro Cobo]

You guys are the best!!!. 

I have spent hours trying to solve this problema and Peter´s answer was the 
solution. I´ll write a post with the whole explanation of the problem. 

Thanks and best regards, 

Alvaro. 

PD: Mathias, I´ll send the tables with data and the solution. Thanks!. 
  - Original Message - 
  From: Peter Brawley 
  To: Alvaro Cobo 
  Cc: mysql@lists.mysql.com 
  Sent: Monday, April 25, 2005 11:02 PM
  Subject: Re: Crosstab in Mysql


  Alvaro,

  I suspect the error message refers to the subquery missing a non-aggregate 
column on which to GROUP BY.

  I may misunderstand your query--I'm not clear why you can't just write ...

  SELECT 
f.PK_partic, 
f.FK_IS, 
f.OB_familia, 
Sum( IF( insumo_or = "Animal1", cantidad_or, 0 )) AS Animal1, 
Sum( IF( insumo_or = "Animal2", cantidad_or, 0 )) AS Animal2, 
Sum( IF( insumo_or = "Animal3", cantidad_or, 0 )) AS Animal3, 
Sum( IF( insumo_or = "Animal4", cantidad_or, 0 )) AS Animal4, 
Sum( IF( insumo_or = "Animal5", cantidad_or, 0 )) AS Animal5, 
Sum( IF( insumo_or = "Animal6", cantidad_or, 0 )) AS Animal6
  FROM tbl_ISv2CRfamilia AS f
  INNER JOIN tbl_ISv2CROriginal AS a ON f.PK_partic = a.FK_partic
  GROUP BY f.FK_partic

  HTH

  PB

  -

  Alvaro Cobo wrote: 
Hi guys: 

I am quite new in SQL and I need to build a crosstab based in two tables using 
Mysql and PHP, but it is becoming quite dificult. 

I've got the next query, but it keeps giving the next error: 

"#1241 - Operand should contain 1 column(s)" 

/*GENERAL EXPLANATION OF THE QUERY

I work in a project to give animals to farmers: I have two tables: 

tbl_ISv2CRfamilia with the families which are going to receive animals. 
(PK_partic, int(11), Autonumbering ID (PK); 
FK_IS; varchar(255); Foreign key which conects to the project table
nombre_partic, varchar(255), Name of the family
OB_familia, varchar(255), community of the family)

tbl_ISv2CROriginal with the animales they actually have received. 
(FK_partic, int(11), Foreign key which conects to the family ID
insumo_or, varchar(255), Animal given
cantidad_or, int(11), number of animals given of this specie)

And I need to have a table like this: 

Family, animal1, animal2, animal3, ..., animaln
John Smith 34013... 0
*/


The query and subquiery is as follows. 

SELECT tbl_ISv2CRfamilia.PK_partic, 
tbl_ISv2CRfamilia.FK_IS, 
tbl_ISv2CRfamilia.OB_familia, 
(SELECT Sum(
IF (
insumo_or = "Animal1", cantidad_or, 0
) ) AS "Animal1", Sum(
IF (
insumo_or = "Animal2", cantidad_or, 0
) ) AS "Animal2", Sum(
IF (
insumo_or = "Animal3", cantidad_or, 0
) ) AS "Animal3", Sum(
IF (
insumo_or = "Animal4", cantidad_or, 0
) ) AS "Animal4", Sum(
IF (
insumo_or = "Animal5", cantidad_or, 0
) ) AS "Animal5", Sum(
IF (
insumo_or = "Animal6", cantidad_or, 0
) ) AS "Animal6"
FROM tbl_ISv2CROriginal
GROUP BY FK_partic
)
FROM tbl_ISv2CRfamilia
INNER JOIN tbl_ISv2CROriginal ON tbl_ISv2CRfamilia.PK_partic = 
tbl_ISv2CROriginal.FK_partic
GROUP BY FK_partic

What is wrong with that? I have tried everything, and no solution. 

Thanks in advance. 

Alvaro

  
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
  

--


  No virus found in this outgoing message.
  Checked by AVG Anti-Virus.
  Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005

Re: Python related MySQL question

[Smelly Socks]

Hi!

I am porting a function library app I wrote in PHP to Python.  At work they
only use Python.  I've researched how to connect to a MySql database using
Python, and how to retrieve rows.  However, I am wondering how to do the
following:

I can do this in Python =>  $da=MYSQL_QUERY("select *  from prefs where
user_name='$user_name'  ");
I can do this in Python =>  $peek=mysql_fetch_array($da);

I cannot do the following:
$title   =$peek[4];  //title window
$logic   =$peek[5];  //logic window

Can anyone shed light on how to get the pieces of the array and stick them
into variables?

Thanks very much!

Cheers!

-Warren




- Original Message -
From: "Spenser" <[EMAIL PROTECTED]>
To: "David Bailey" <[EMAIL PROTECTED]>
Cc: 
Sent: Thursday, April 14, 2005 12:27 PM
Subject: Re: book advice


> Check out "MySQL Tutorial" by Luke Welling (MySQL Press).  It's easy to
> understand and not overwhelming.
>
>
> --
> 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: Index help ?

[mathias fatene]

I think the second can be better (more different values). But it
contains almost the same data than the table. 
Try :
explain Select machine,count(*) from syslog WHERE date1 > (NOW()
- INTERVAL 24 
hour) AND message LIKE 'sshd%' GROUP BY machine;

But an index with(date1, message, machine)  sould be sufficient.

Best Regards

Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-Original Message-
From: Michael Gale [mailto:[EMAIL PROTECTED] 
Sent: mardi 26 avril 2005 05:49
To: mysql@lists.mysql.com
Subject: Index help ?


Hello,

I have the following table setup:

IDhostnamefacilityprioritydatemessage


ID is auto incrementing.

This is used to store all of the syslog messages, currently there are 
over 7 million:

The following query takes forever:
Select machine,count(*) from syslog WHERE date1 > (NOW() - INTERVAL 24 
hour) AND message LIKE 'sshd%' GROUP BY machine;

I have created the following indexs but when I use Explain it says that 
the query has to search all the rows:

  datehostfacility  1   date1 A
352489
  datehostfacility  2   machine   A
1409956
  datehostfacility  3   facility  A
1409956

  datemesghost  1   date1 A
640889
  datemesghost  2   message(15)   A
7049783
  datemesghost  3   machine   A
7049783
  datemesghost  4   facility  A
7049783
  datemesghost  5   priority  A
7049783

What would the proper index be ?

Michael

-- 
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: Crosstab in Mysql

[Peter Brawley]

Alvaro,

I suspect the error message refers to the subquery missing a
non-aggregate column on which to GROUP BY.

I may misunderstand your query--I'm not clear why you can't just write
...

SELECT 
  f.PK_partic, 
  f.FK_IS, 
  f.OB_familia, 
  Sum( IF( insumo_or = "Animal1", cantidad_or, 0 )) AS Animal1, 
  Sum( IF( insumo_or = "Animal2", cantidad_or, 0 )) AS Animal2, 
  Sum( IF( insumo_or = "Animal3", cantidad_or, 0 )) AS Animal3, 
  Sum( IF( insumo_or = "Animal4", cantidad_or, 0 )) AS Animal4, 
  Sum( IF( insumo_or = "Animal5", cantidad_or, 0 )) AS Animal5, 
  Sum( IF( insumo_or = "Animal6", cantidad_or, 0 )) AS Animal6
FROM tbl_ISv2CRfamilia AS f
INNER JOIN tbl_ISv2CROriginal AS a ON f.PK_partic = a.FK_partic
GROUP BY f.FK_partic

HTH

PB

-

Alvaro Cobo wrote:

  Hi guys: 

I am quite new in SQL and I need to build a crosstab based in two tables using Mysql and PHP, but it is becoming quite dificult. 

I've got the next query, but it keeps giving the next error: 

"#1241 - Operand should contain 1 column(s)" 

/*GENERAL EXPLANATION OF THE QUERY

I work in a project to give animals to farmers: I have two tables: 

tbl_ISv2CRfamilia with the families which are going to receive animals. 
(PK_partic, int(11), Autonumbering ID (PK); 
FK_IS; varchar(255); Foreign key which conects to the project table
nombre_partic, varchar(255), Name of the family
OB_familia, varchar(255), community of the family)

tbl_ISv2CROriginal with the animales they actually have received. 
(FK_partic, int(11), Foreign key which conects to the family ID
insumo_or, varchar(255), Animal given
cantidad_or, int(11), number of animals given of this specie)

And I need to have a table like this: 

Family, animal1, animal2, animal3, ..., animaln
John Smith 34013... 0
*/


The query and subquiery is as follows. 

SELECT tbl_ISv2CRfamilia.PK_partic, 
tbl_ISv2CRfamilia.FK_IS, 
tbl_ISv2CRfamilia.OB_familia, 
(SELECT Sum(
IF (
insumo_or = "Animal1", cantidad_or, 0
) ) AS "Animal1", Sum(
IF (
insumo_or = "Animal2", cantidad_or, 0
) ) AS "Animal2", Sum(
IF (
insumo_or = "Animal3", cantidad_or, 0
) ) AS "Animal3", Sum(
IF (
insumo_or = "Animal4", cantidad_or, 0
) ) AS "Animal4", Sum(
IF (
insumo_or = "Animal5", cantidad_or, 0
) ) AS "Animal5", Sum(
IF (
insumo_or = "Animal6", cantidad_or, 0
) ) AS "Animal6"
FROM tbl_ISv2CROriginal
GROUP BY FK_partic
)
FROM tbl_ISv2CRfamilia
INNER JOIN tbl_ISv2CROriginal ON tbl_ISv2CRfamilia.PK_partic = tbl_ISv2CROriginal.FK_partic
GROUP BY FK_partic

What is wrong with that? I have tried everything, and no solution. 

Thanks in advance. 

Alvaro

  
  

No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
  



No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005

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

Index help ?

[Michael Gale]

Hello,
I have the following table setup:
IDhostnamefacilityprioritydatemessage
ID is auto incrementing.
This is used to store all of the syslog messages, currently there are 
over 7 million:

The following query takes forever:
Select machine,count(*) from syslog WHERE date1 > (NOW() - INTERVAL 24 
hour) AND message LIKE 'sshd%' GROUP BY machine;

I have created the following indexs but when I use Explain it says that 
the query has to search all the rows:

 datehostfacility  1   date1 A352489
 datehostfacility  2   machine   A   1409956
 datehostfacility  3   facility  A   1409956
 datemesghost  1   date1 A640889
 datemesghost  2   message(15)   A   7049783
 datemesghost  3   machine   A   7049783
 datemesghost  4   facility  A   7049783
 datemesghost  5   priority  A   7049783
What would the proper index be ?
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Crosstab in Mysql

[mathias fatene]

Hi,
What do you obtain with :
Select FK_partic,
Sum(IF(insumo_or = "Animal1", cantidad_or, 0) ) AS "Animal1", 
Sum(IF (insumo_or = "Animal2", cantidad_or, 0) ) AS "Animal2", 
Sum(IF (insumo_or = "Animal3", cantidad_or, 0) ) AS "Animal3", 
Sum(IF (insumo_or = "Animal4", cantidad_or, 0) ) AS "Animal4", 
Sum(IF (insumo_or = "Animal5", cantidad_or, 0) ) AS "Animal5", 
Sum(IF (insumo_or = "Animal6", cantidad_or, 0) ) AS "Animal6"
FROM tbl_ISv2CROriginal
Group by FK_partic

?


Best Regards

Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-Original Message-
From: Alvaro Cobo [mailto:[EMAIL PROTECTED] 
Sent: mardi 26 avril 2005 04:38
To: mysql@lists.mysql.com
Subject: Crosstab in Mysql


Hi guys: 

I am quite new in SQL and I need to build a crosstab based in two tables
using Mysql and PHP, but it is becoming quite dificult. 

I've got the next query, but it keeps giving the next error: 

"#1241 - Operand should contain 1 column(s)" 

/*GENERAL EXPLANATION OF THE QUERY

I work in a project to give animals to farmers: I have two tables: 

tbl_ISv2CRfamilia with the families which are going to receive animals. 
(PK_partic, int(11), Autonumbering ID (PK); 
FK_IS; varchar(255); Foreign key which conects to the project table
nombre_partic, varchar(255), Name of the family
OB_familia, varchar(255), community of the family)

tbl_ISv2CROriginal with the animales they actually have received. 
(FK_partic, int(11), Foreign key which conects to the family ID
insumo_or, varchar(255), Animal given
cantidad_or, int(11), number of animals given of this specie)

And I need to have a table like this: 

Family, animal1, animal2, animal3, ..., animaln
John Smith 34013... 0
*/


The query and subquiery is as follows. 

SELECT tbl_ISv2CRfamilia.PK_partic, 
tbl_ISv2CRfamilia.FK_IS, 
tbl_ISv2CRfamilia.OB_familia, 
(SELECT Sum(
IF (
insumo_or = "Animal1", cantidad_or, 0
) ) AS "Animal1", Sum(
IF (
insumo_or = "Animal2", cantidad_or, 0
) ) AS "Animal2", Sum(
IF (
insumo_or = "Animal3", cantidad_or, 0
) ) AS "Animal3", Sum(
IF (
insumo_or = "Animal4", cantidad_or, 0
) ) AS "Animal4", Sum(
IF (
insumo_or = "Animal5", cantidad_or, 0
) ) AS "Animal5", Sum(
IF (
insumo_or = "Animal6", cantidad_or, 0
) ) AS "Animal6"
FROM tbl_ISv2CROriginal
GROUP BY FK_partic
)
FROM tbl_ISv2CRfamilia
INNER JOIN tbl_ISv2CROriginal ON tbl_ISv2CRfamilia.PK_partic =
tbl_ISv2CROriginal.FK_partic GROUP BY FK_partic

What is wrong with that? I have tried everything, and no solution. 

Thanks in advance. 

Alvaro


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

Crosstab in Mysql

[Alvaro Cobo]

Hi guys: 

I am quite new in SQL and I need to build a crosstab based in two tables using 
Mysql and PHP, but it is becoming quite dificult. 

I've got the next query, but it keeps giving the next error: 

"#1241 - Operand should contain 1 column(s)" 

/*GENERAL EXPLANATION OF THE QUERY

I work in a project to give animals to farmers: I have two tables: 

tbl_ISv2CRfamilia with the families which are going to receive animals. 
(PK_partic, int(11), Autonumbering ID (PK); 
FK_IS; varchar(255); Foreign key which conects to the project table
nombre_partic, varchar(255), Name of the family
OB_familia, varchar(255), community of the family)

tbl_ISv2CROriginal with the animales they actually have received. 
(FK_partic, int(11), Foreign key which conects to the family ID
insumo_or, varchar(255), Animal given
cantidad_or, int(11), number of animals given of this specie)

And I need to have a table like this: 

Family, animal1, animal2, animal3, ..., animaln
John Smith 34013... 0
*/


The query and subquiery is as follows. 

SELECT tbl_ISv2CRfamilia.PK_partic, 
tbl_ISv2CRfamilia.FK_IS, 
tbl_ISv2CRfamilia.OB_familia, 
(SELECT Sum(
IF (
insumo_or = "Animal1", cantidad_or, 0
) ) AS "Animal1", Sum(
IF (
insumo_or = "Animal2", cantidad_or, 0
) ) AS "Animal2", Sum(
IF (
insumo_or = "Animal3", cantidad_or, 0
) ) AS "Animal3", Sum(
IF (
insumo_or = "Animal4", cantidad_or, 0
) ) AS "Animal4", Sum(
IF (
insumo_or = "Animal5", cantidad_or, 0
) ) AS "Animal5", Sum(
IF (
insumo_or = "Animal6", cantidad_or, 0
) ) AS "Animal6"
FROM tbl_ISv2CROriginal
GROUP BY FK_partic
)
FROM tbl_ISv2CRfamilia
INNER JOIN tbl_ISv2CROriginal ON tbl_ISv2CRfamilia.PK_partic = 
tbl_ISv2CROriginal.FK_partic
GROUP BY FK_partic

What is wrong with that? I have tried everything, and no solution. 

Thanks in advance. 

Alvaro

RE: database migration puzzle.

[mathias fatene]

Hi,
I hope that this link will help
http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html

Best Regards

Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-Original Message-
From: Kenneth Wagner [mailto:[EMAIL PROTECTED] 
Sent: mardi 26 avril 2005 01:03
To: mysql@lists.mysql.com
Subject: database migration puzzle.


Hi all,

I have removed mysql 4_0_20d and installed 4.1. 

My puzzle is this:

1. I have prior databases in 4.0 (intact data directory with InnoDB
files *.idb, etc.) data directory with sub directories.
2. I want to bring in some of the databases to the new 4.1 version.

The 4.0 databases have not been dumped, unloaded or exported.

How to go about it?

Many thanks.

Ken Wagner


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

database migration puzzle.

[Kenneth Wagner]

Hi all,

I have removed mysql 4_0_20d and installed 4.1. 

My puzzle is this:

1. I have prior databases in 4.0 (intact data directory with InnoDB files 
*.idb, etc.) data directory with sub directories.
2. I want to bring in some of the databases to the new 4.1 version.

The 4.0 databases have not been dumped, unloaded or exported.

How to go about it?

Many thanks.

Ken Wagner

RE: Query question

[mathias fatene]

Hi,
If my englsih is so bad, i'll try to explain and stop this thread now.
I'm not teaching, i'm answering questions. If someone wants to read
docs, he (she) doesn't ask a question on the list. So if i answer, i
answer the question, just the question.

You want to know my level of knowledgne, 10 years, oracle, sybase,
sqlserver, db2. I can help for migration from or to...
I said don't use joins for the query given in the example or queries
using just the joining columns from the first table.  Normal forms is
bla bla here ...

See also about covering indexes. That can help.

This is the query given by Jeff :
>>> So, if record 100 in table1 links to 5 corresponding records in
table2, 
>>> I want to pull the latest record from table2 where table2.parentid =
100 and table2.user not like 'john'

The only clause is about table2.parentid = 100  or child.id = 100.
that's the same.
All the other clauses are on table2. This is the exampel given by Jeff.

If you want absolutely LEFT outer joins for that (without other columns
from table1), i say you good luck, this can (also) do the trick.

That's all.

-
If you give me real examples, i can help you to give you to find the
right (if i can) query plan. Tuning is my first target when i think a
query. 
I never suggest nested loops, but relationnal algebra.
I'm not supposed speeking to students but DBAs, for specific question. 

Sorry if i run up against your sensitivity, but we are not speaking
about the same thing.

And please if you have to criticize or complete an answer, it's your
right. The list is for that. If you want to speak to me as your student,
this is enough. I never did it when i was teacher 11 years ago.


Best Regards

Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: lundi 25 avril 2005 23:02
To: mathias fatene
Cc: 'Jeff McKeon'; mysql@lists.mysql.com; [EMAIL PROTECTED]
Subject: RE: Query question


"mathias fatene" <[EMAIL PROTECTED]> wrote on 04/25/2005 04:24:42 PM:

> Hi,
> Im sorry to disappoint you but this is an anti-performance solution. 
> Use joins rathers than subqueries, and don't use joins if you can (all

> data in the mother table).
> 
> Imagine that table2 has 30.000.000 records, and not good indexes. you 
> can wait for your answer a long time.
> 
> Best Regards
> 
> Mathias FATENE
> 
> Hope that helps
> *This not an official mysql support answer
> 


Mathias,

I do appreciate your energy and willingness to contribute to the list. I

am not affiliated with MySQL or any of its subsidiaries and I have no 
special privileges to police what happens on this list. I am a fellow 
contributor just as you. With that said, I feel that I must seriously 
question your level of experience and ability to form useful responses.

When you say "and don't use joins if you can (all data in the mother 
table)", It seems to me that you are proposing that in order to
eliminate 
JOINs in queries that all data should be flattened into one single
table. 
Not only is this incorrect advice but it undermines the many reasons for

using a relational database system (RDBMS) in the first place. I would 
love to compare the performance of a properly normalized and indexed 
relational data structure against a single "flat" table for all but the 
most trivial of data sets.  The nomalized data will not only take up
less 
room on the disk but it will perform extremely well (especially for
larger 
data sets). The single-table model you proposed will not scale to more 
than a few hundred thousand rows before the table's size becomes a 
bottleneck.

Some queries will take "a long time" to finish against 30 million row 
tables, even with good indexes on them. Your extreme counter example was
a 
non-starter. The original poster acknowledges that they are new (no 
offence intended) and I feel that your posts were hardly helpful at best

and most likely counter-productive. Please, take the time to read your 
ansers from the perspective of the person you are responding to. Try to 
keep in mind not only their language skills (as this is a multi-national

list) but their experience level and even sometimes their age (we have 
many students looking for help on here and some of them are still 
teenagers). Please be more accurate, thoughtful, and descriptive the
next 
time you post, OK?

With greatest humility,

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


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

RE: Query question

[Jeff McKeon]

thanks, I'll give that a try tomorrow.  :o)
 
 
Jeffrey S. McKeon
Manager of Information Technology
Telaurus Communications LLC
[EMAIL PROTECTED]
+1 (973) 889-8990 ex 209

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Monday, April 25, 2005 4:36 PM
To: Jeff McKeon
Cc: mysql@lists.mysql.com
Subject: RE: Query question




"Jeff McKeon" <[EMAIL PROTECTED]> wrote on 04/25/2005
04:08:29 PM:

> Thanks all but I don't have a mysql version high enough for
subqueries.
> 
> Thanks,
> 
> Jeff
> 
> > -Original Message-
> > From: Peter Brawley [mailto:[EMAIL PROTECTED] 
> > Sent: Monday, April 25, 2005 4:01 PM
> > To: Jeff McKeon
> > Cc: mysql@lists.mysql.com
> > Subject: Re: Query question
> > 
> > 
> > Jeff,
> > 
> > Something like ...
> > 
> > SELECT *
> > FROM table2 AS a
> > WHERE datestamp = (
> >   SELECT MAX( b.datestamp )
> >   FROM table2 AS b
> >   WHERE a.parentID = b.parentID
> > );
> > 
> > PB
> > 
> > -
> > 
> > 
> > Jeff McKeon wrote:
> > 
> > >I have a table that contains records that link back to a 
> > main talbe in 
> > >a many to one configuration linked by table1.id =
table2.parentid
> > >
> > >Table1 (one)
> > >Table2 (many)
> > >
> > >I want to pull the latest records from table2 for each 
> > record in table1 
> > >where certain criteria applie.
> > >
> > >So, if record 100 in table1 links to 5 corresponding
records 
> > in table2, 
> > >I want to pull the latest record from table2 where
table2.parentid = 
> > >100 and table2.user not like 'john'
> > >
> > >There is a datestamp field in table2.
> > >
> > >I just can't figure out how to do this.
> > >
> > >Thanks,
> > >
> > >Jeff
> > >
> > >
> > >  
> > >
> > 
> > 
> > -- 
> > No virus found in this outgoing message.
> > Checked by AVG Anti-Virus.
> > Version: 7.0.308 / Virus Database: 266.10.2 - Release Date:
4/21/2005
> > 
> > 

OK, then you need to collect your child-table maximums in one
pass and build your actual query in the second (the non-subquery version
of the example I sent). Let's find all of the child records where user
not like 'john'. ( I will exclude all users whose name starts with
'john') 

CREATE TEMPORARY TABLE lastRecords 
SELECT parentID, max(datetime_field_name_here) as latest 
FROM table2 
WHERE user NOT LIKE 'john%' 
GROUP BY parentID; 

You had to exclude 'john' at this stage because you want the
latest child record that isn't 'john'. Make sense? Of course, you will
need to adjust this to meet whatever conditions you really want. 

SELECT t1.*, t2.* 
FROM table1 t1 
LEFT JOIN lastRecords r 
ON r.parentID = t1.id 
LEFT JOIN table2 t2 
ON t2.parentID = r.parentID 
AND t2.datetime_field_name_here = r.latest; 

That will give you all of the records from table1 and only the
most recent record from table2 (if it even exists). I used the LEFT JOIN
(not an INNER JOIN) so that you can see all of the records from table1.
If I had used INNER JOINs you would have only seen those records that
matched up with the conditions you placed on table2. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

RE: Query question

[SGreen]

"mathias fatene" <[EMAIL PROTECTED]> wrote on 04/25/2005 04:24:42 PM:

> Hi,
> Im sorry to disappoint you but this is an anti-performance solution.
> Use joins rathers than subqueries, and don't use joins if you can (all
> data in the mother table).
> 
> Imagine that table2 has 30.000.000 records, and not good indexes. you
> can wait for your answer a long time.
> 
> Best Regards
> 
> Mathias FATENE
> 
> Hope that helps
> *This not an official mysql support answer
> 


Mathias,

I do appreciate your energy and willingness to contribute to the list. I 
am not affiliated with MySQL or any of its subsidiaries and I have no 
special privileges to police what happens on this list. I am a fellow 
contributor just as you. With that said, I feel that I must seriously 
question your level of experience and ability to form useful responses.

When you say "and don't use joins if you can (all data in the mother 
table)", It seems to me that you are proposing that in order to eliminate 
JOINs in queries that all data should be flattened into one single table. 
Not only is this incorrect advice but it undermines the many reasons for 
using a relational database system (RDBMS) in the first place. I would 
love to compare the performance of a properly normalized and indexed 
relational data structure against a single "flat" table for all but the 
most trivial of data sets.  The nomalized data will not only take up less 
room on the disk but it will perform extremely well (especially for larger 
data sets). The single-table model you proposed will not scale to more 
than a few hundred thousand rows before the table's size becomes a 
bottleneck.

Some queries will take "a long time" to finish against 30 million row 
tables, even with good indexes on them. Your extreme counter example was a 
non-starter. The original poster acknowledges that they are new (no 
offence intended) and I feel that your posts were hardly helpful at best 
and most likely counter-productive. Please, take the time to read your 
ansers from the perspective of the person you are responding to. Try to 
keep in mind not only their language skills (as this is a multi-national 
list) but their experience level and even sometimes their age (we have 
many students looking for help on here and some of them are still 
teenagers). Please be more accurate, thoughtful, and descriptive the next 
time you post, OK?

With greatest humility,

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Query question

[Peter Brawley]

Title: Message




Jeff,

3.23.
 
no control over this right now or i'd upgrade, believe me!

Yep, I maintain websites with the same
problem. Shawn Green just posted a solution that doesn't need inline
user variable assignment.

PB

-



Jeff McKeon wrote:

  
  
  
  3.23.
   
  no control over this right now or i'd upgrade,
believe me!
   
   
  jeff
  
-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED]] 
Sent: Monday, April 25, 2005 4:43 PM
To: Jeff McKeon
Cc: mysql@lists.mysql.com
Subject: Re: Query question


That's real syntax for inline assignment of a column value to a user
variable. What MySQL version are you using?

PB

Jeff McKeon wrote:

  
  Peter,
   
  I'm unfamiliar with the "@d := " section
you describe.  Is this psudo code or real syntax?
   
  thanks,
   
  Jeff
   
   
  
-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED]]

Sent: Monday, April 25, 2005 4:17 PM
To: Jeff McKeon
Cc: mysql@lists.mysql.com
Subject: Re: Query question


Jeff,

Then do it with 2 queries,
SELECT @d := MAX( datestamp )
FROM table2
WHERE parentID = X;

SELECT *
FROM table2
WHERE parentID = X AND [EMAIL PROTECTED];

PB

-

Jeff McKeon wrote:

  Thanks all but I don't have a mysql version high enough for subqueries.

Thanks,

Jeff

  
  
-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED]] 
Sent: Monday, April 25, 2005 4:01 PM
To: Jeff McKeon
Cc: mysql@lists.mysql.com
Subject: Re: Query question


Jeff,

Something like ...

SELECT *
FROM table2 AS a
WHERE datestamp = (
  SELECT MAX( b.datestamp )
  FROM table2 AS b
  WHERE a.parentID = b.parentID
);

PB

-


Jeff McKeon wrote:



  I have a table that contains records that link back to a 
  

main talbe in 


  a many to one configuration linked by table1.id = table2.parentid

Table1 (one)
Table2 (many)

I want to pull the latest records from table2 for each 
  

record in table1 


  where certain criteria applie.

So, if record 100 in table1 links to 5 corresponding records 
  

in table2, 


  I want to pull the latest record from table2 where table2.parentid = 
100 and table2.user not like 'john'

There is a datestamp field in table2.

I just can't figure out how to do this.

Thanks,

Jeff


 

  

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005



  
  

  

  
  
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
  

  
  

No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
  



No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005

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

RE: Query question

[Jeff McKeon]

3.23.
 
no control over this right now or i'd upgrade, believe me!
 
 
jeff

-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED] 
Sent: Monday, April 25, 2005 4:43 PM
To: Jeff McKeon
Cc: mysql@lists.mysql.com
Subject: Re: Query question


That's real syntax for inline assignment of a column value to a
user variable. What MySQL version are you using?

PB

Jeff McKeon wrote: 

Peter,
 
I'm unfamiliar with the "@d := " section you describe.
Is this psudo code or real syntax?
 
thanks,
 
Jeff
 
 

-Original Message-
From: Peter Brawley
[mailto:[EMAIL PROTECTED] 
Sent: Monday, April 25, 2005 4:17 PM
To: Jeff McKeon
Cc: mysql@lists.mysql.com
Subject: Re: Query question


Jeff,

Then do it with 2 queries,

SELECT @d := MAX( datestamp )
FROM table2
WHERE parentID = X;

SELECT *
FROM table2
WHERE parentID = X AND [EMAIL PROTECTED];

PB

-

Jeff McKeon wrote: 

Thanks all but I don't have a mysql
version high enough for subqueries.

Thanks,

Jeff

  

-Original Message-
From: Peter Brawley
[mailto:[EMAIL PROTECTED] 
Sent: Monday, April 25, 2005 4:01 PM
To: Jeff McKeon
Cc: mysql@lists.mysql.com
Subject: Re: Query question


Jeff,

Something like ...

SELECT *
FROM table2 AS a
WHERE datestamp = (
  SELECT MAX( b.datestamp )
  FROM table2 AS b
  WHERE a.parentID = b.parentID
);

PB

-


Jeff McKeon wrote:



I have a table that contains records
that link back to a 
  

main talbe in 


a many to one configuration linked by
table1.id = table2.parentid

Table1 (one)
Table2 (many)

I want to pull the latest records from
table2 for each 
  

record in table1 


where certain criteria applie.

So, if record 100 in table1 links to 5
corresponding records 
  

in table2, 


I want to pull the latest record from
table2 where table2.parentid = 
100 and table2.user not like 'john'

There is a datestamp field in table2.

I just can't figure out how to do this.

Thanks,

Jeff


 

 

Re: Query question

[Peter Brawley]

Title: Message




That's real syntax for inline assignment of a column value to a user
variable. What MySQL version are you using?

PB

Jeff McKeon wrote:

  
  
  
  Peter,
   
  I'm unfamiliar with the "@d := " section
you describe.  Is this psudo code or real syntax?
   
  thanks,
   
  Jeff
   
   
  
-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED]] 
Sent: Monday, April 25, 2005 4:17 PM
To: Jeff McKeon
Cc: mysql@lists.mysql.com
Subject: Re: Query question


Jeff,

Then do it with 2 queries,
SELECT @d := MAX( datestamp )
FROM table2
WHERE parentID = X;

SELECT *
FROM table2
WHERE parentID = X AND [EMAIL PROTECTED];

PB

-

Jeff McKeon wrote:

  Thanks all but I don't have a mysql version high enough for subqueries.

Thanks,

Jeff

  
  
-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED]] 
Sent: Monday, April 25, 2005 4:01 PM
To: Jeff McKeon
Cc: mysql@lists.mysql.com
Subject: Re: Query question


Jeff,

Something like ...

SELECT *
FROM table2 AS a
WHERE datestamp = (
  SELECT MAX( b.datestamp )
  FROM table2 AS b
  WHERE a.parentID = b.parentID
);

PB

-


Jeff McKeon wrote:



  I have a table that contains records that link back to a 
  

main talbe in 


  a many to one configuration linked by table1.id = table2.parentid

Table1 (one)
Table2 (many)

I want to pull the latest records from table2 for each 
  

record in table1 


  where certain criteria applie.

So, if record 100 in table1 links to 5 corresponding records 
  

in table2, 


  I want to pull the latest record from table2 where table2.parentid = 
100 and table2.user not like 'john'

There is a datestamp field in table2.

I just can't figure out how to do this.

Thanks,

Jeff


 

  

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005



  
  

  

  
  

No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
  



No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005

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

RE: Query question

[SGreen]

"Jeff McKeon" <[EMAIL PROTECTED]> wrote on 04/25/2005 04:08:29 PM:

> Thanks all but I don't have a mysql version high enough for subqueries.
> 
> Thanks,
> 
> Jeff
> 
> > -Original Message-
> > From: Peter Brawley [mailto:[EMAIL PROTECTED] 
> > Sent: Monday, April 25, 2005 4:01 PM
> > To: Jeff McKeon
> > Cc: mysql@lists.mysql.com
> > Subject: Re: Query question
> > 
> > 
> > Jeff,
> > 
> > Something like ...
> > 
> > SELECT *
> > FROM table2 AS a
> > WHERE datestamp = (
> >   SELECT MAX( b.datestamp )
> >   FROM table2 AS b
> >   WHERE a.parentID = b.parentID
> > );
> > 
> > PB
> > 
> > -
> > 
> > 
> > Jeff McKeon wrote:
> > 
> > >I have a table that contains records that link back to a 
> > main talbe in 
> > >a many to one configuration linked by table1.id = table2.parentid
> > >
> > >Table1 (one)
> > >Table2 (many)
> > >
> > >I want to pull the latest records from table2 for each 
> > record in table1 
> > >where certain criteria applie.
> > >
> > >So, if record 100 in table1 links to 5 corresponding records 
> > in table2, 
> > >I want to pull the latest record from table2 where table2.parentid = 
> > >100 and table2.user not like 'john'
> > >
> > >There is a datestamp field in table2.
> > >
> > >I just can't figure out how to do this.
> > >
> > >Thanks,
> > >
> > >Jeff
> > >
> > >
> > > 
> > >
> > 
> > 
> > -- 
> > No virus found in this outgoing message.
> > Checked by AVG Anti-Virus.
> > Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
> > 
> > 

OK, then you need to collect your child-table maximums in one pass and 
build your actual query in the second (the non-subquery version of the 
example I sent). Let's find all of the child records where user not like 
'john'. ( I will exclude all users whose name starts with 'john')

CREATE TEMPORARY TABLE lastRecords
SELECT parentID, max(datetime_field_name_here) as latest
FROM table2
WHERE user NOT LIKE 'john%'
GROUP BY parentID;

You had to exclude 'john' at this stage because you want the latest child 
record that isn't 'john'. Make sense? Of course, you will need to adjust 
this to meet whatever conditions you really want.

SELECT t1.*, t2.*
FROM table1 t1
LEFT JOIN lastRecords r
ON r.parentID = t1.id
LEFT JOIN table2 t2
ON t2.parentID = r.parentID
AND t2.datetime_field_name_here = r.latest;

That will give you all of the records from table1 and only the most recent 
record from table2 (if it even exists). I used the LEFT JOIN (not an INNER 
JOIN) so that you can see all of the records from table1. If I had used 
INNER JOINs you would have only seen those records that matched up with 
the conditions you placed on table2.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Query question

[Peter Brawley]

Title: Message




Mathias,

>Im sorry to disappoint you but this
is an anti-performance solution.
>Use joins rathers than subqueries,
and don't use joins if you can (all data in the mother table).

This 2-query solution uses neither a join
nor a subquery. What do you mean?

PB

-



mathias fatene wrote:

  
  
  
  Hi,
  Im sorry to disappoint you but this is
an anti-performance solution.
  Use joins rathers than subqueries, and
don't use joins if you can (all data in the mother table).
   
  Imagine that table2 has 30.000.000 records,
and not good indexes. you can wait for your answer a long time.
   
  Best
Regards
  
  Mathias
FATENE
   
  Hope
that helps
  *This
not an official mysql support answer
   
  
-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED]] 
Sent: lundi 25 avril 2005 22:17
To: Jeff McKeon
Cc: mysql@lists.mysql.com
Subject: Re: Query question


Jeff,

Then do it with 2 queries,
SELECT @d := MAX( datestamp )
FROM table2
WHERE parentID = X;

SELECT *
FROM table2
WHERE parentID = X AND [EMAIL PROTECTED];

PB

-

Jeff McKeon wrote:

  Thanks all but I don't have a mysql version high enough for subqueries.

Thanks,

Jeff

  
  
-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED]] 
Sent: Monday, April 25, 2005 4:01 PM
To: Jeff McKeon
Cc: mysql@lists.mysql.com
Subject: Re: Query question


Jeff,

Something like ...

SELECT *
FROM table2 AS a
WHERE datestamp = (
  SELECT MAX( b.datestamp )
  FROM table2 AS b
  WHERE a.parentID = b.parentID
);

PB

-


Jeff McKeon wrote:



  I have a table that contains records that link back to a 
  

main talbe in 


  a many to one configuration linked by table1.id = table2.parentid

Table1 (one)
Table2 (many)

I want to pull the latest records from table2 for each 
  

record in table1 


  where certain criteria applie.

So, if record 100 in table1 links to 5 corresponding records 
  

in table2, 


  I want to pull the latest record from table2 where table2.parentid = 
100 and table2.user not like 'john'

There is a datestamp field in table2.

I just can't figure out how to do this.

Thanks,

Jeff


 

  

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005



  
  

  

  
  

No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
  



No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005

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

RE: Query question

[Jeff McKeon]

Peter,
 
I'm unfamiliar with the "@d := " section you describe.  Is this psudo
code or real syntax?
 
thanks,
 
Jeff
 
 

-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED] 
Sent: Monday, April 25, 2005 4:17 PM
To: Jeff McKeon
Cc: mysql@lists.mysql.com
Subject: Re: Query question


Jeff,

Then do it with 2 queries,

SELECT @d := MAX( datestamp )
FROM table2
WHERE parentID = X;

SELECT *
FROM table2
WHERE parentID = X AND [EMAIL PROTECTED];

PB

-

Jeff McKeon wrote: 

Thanks all but I don't have a mysql version high enough
for subqueries.

Thanks,

Jeff

  

-Original Message-
From: Peter Brawley
[mailto:[EMAIL PROTECTED] 
Sent: Monday, April 25, 2005 4:01 PM
To: Jeff McKeon
Cc: mysql@lists.mysql.com
Subject: Re: Query question


Jeff,

Something like ...

SELECT *
FROM table2 AS a
WHERE datestamp = (
  SELECT MAX( b.datestamp )
  FROM table2 AS b
  WHERE a.parentID = b.parentID
);

PB

-


Jeff McKeon wrote:



I have a table that contains records
that link back to a 
  

main talbe in 


a many to one configuration linked by
table1.id = table2.parentid

Table1 (one)
Table2 (many)

I want to pull the latest records from
table2 for each 
  

record in table1 


where certain criteria applie.

So, if record 100 in table1 links to 5
corresponding records 
  

in table2, 


I want to pull the latest record from
table2 where table2.parentid = 
100 and table2.user not like 'john'

There is a datestamp field in table2.

I just can't figure out how to do this.

Thanks,

Jeff


 

  

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 -
Release Date: 4/21/2005






  

RE: Query question

[mathias fatene]

Hi,
Im sorry to disappoint you but this is an anti-performance solution.
Use joins rathers than subqueries, and don't use joins if you can (all
data in the mother table).
 
Imagine that table2 has 30.000.000 records, and not good indexes. you
can wait for your answer a long time.
 
Best Regards

Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 

-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED] 
Sent: lundi 25 avril 2005 22:17
To: Jeff McKeon
Cc: mysql@lists.mysql.com
Subject: Re: Query question


Jeff,

Then do it with 2 queries,

SELECT @d := MAX( datestamp )

FROM table2

WHERE parentID = X;


SELECT *
FROM table2
WHERE parentID = X AND [EMAIL PROTECTED];

PB

-

Jeff McKeon wrote: 

Thanks all but I don't have a mysql version high enough for subqueries.



Thanks,



Jeff



  

-Original Message-

From: Peter Brawley [mailto:[EMAIL PROTECTED] 

Sent: Monday, April 25, 2005 4:01 PM

To: Jeff McKeon

Cc: mysql@lists.mysql.com

Subject: Re: Query question





Jeff,



Something like ...



SELECT *

FROM table2 AS a

WHERE datestamp = (

  SELECT MAX( b.datestamp )

  FROM table2 AS b

  WHERE a.parentID = b.parentID

);



PB



-





Jeff McKeon wrote:





I have a table that contains records that link back to a 

  

main talbe in 



a many to one configuration linked by table1.id = table2.parentid



Table1 (one)

Table2 (many)



I want to pull the latest records from table2 for each 

  

record in table1 



where certain criteria applie.



So, if record 100 in table1 links to 5 corresponding records 

  

in table2, 



I want to pull the latest record from table2 where table2.parentid = 

100 and table2.user not like 'john'



There is a datestamp field in table2.



I just can't figure out how to do this.



Thanks,



Jeff





 



  

-- 

No virus found in this outgoing message.

Checked by AVG Anti-Virus.

Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005











  

Re: Query question

[Peter Brawley]

Jeff,

Then do it with 2 queries,
SELECT @d := MAX( datestamp )
FROM table2
WHERE parentID = X;

SELECT *
FROM table2
WHERE parentID = X AND [EMAIL PROTECTED];

PB

-

Jeff McKeon wrote:

  Thanks all but I don't have a mysql version high enough for subqueries.

Thanks,

Jeff

  
  
-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED]] 
Sent: Monday, April 25, 2005 4:01 PM
To: Jeff McKeon
Cc: mysql@lists.mysql.com
Subject: Re: Query question


Jeff,

Something like ...

SELECT *
FROM table2 AS a
WHERE datestamp = (
  SELECT MAX( b.datestamp )
  FROM table2 AS b
  WHERE a.parentID = b.parentID
);

PB

-


Jeff McKeon wrote:



  I have a table that contains records that link back to a 
  

main talbe in 


  a many to one configuration linked by table1.id = table2.parentid

Table1 (one)
Table2 (many)

I want to pull the latest records from table2 for each 
  

record in table1 


  where certain criteria applie.

So, if record 100 in table1 links to 5 corresponding records 
  

in table2, 


  I want to pull the latest record from table2 where table2.parentid = 
100 and table2.user not like 'john'

There is a datestamp field in table2.

I just can't figure out how to do this.

Thanks,

Jeff


 

  


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005



  
  

  



No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005

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

Re: Query question

[Peter Brawley]

Jeff,

Then do it with 2 queries,
SELECT @d := MAX( datestamp )
FROM table2
WHERE parentID = X;

SELECT *
FROM table2
WHERE parentID = X AND [EMAIL PROTECTED];

PB

-

Jeff McKeon wrote:

  Thanks all but I don't have a mysql version high enough for subqueries.

Thanks,

Jeff

  
  
-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED]] 
Sent: Monday, April 25, 2005 4:01 PM
To: Jeff McKeon
Cc: mysql@lists.mysql.com
Subject: Re: Query question


Jeff,

Something like ...

SELECT *
FROM table2 AS a
WHERE datestamp = (
  SELECT MAX( b.datestamp )
  FROM table2 AS b
  WHERE a.parentID = b.parentID
);

PB

-


Jeff McKeon wrote:



  I have a table that contains records that link back to a 
  

main talbe in 


  a many to one configuration linked by table1.id = table2.parentid

Table1 (one)
Table2 (many)

I want to pull the latest records from table2 for each 
  

record in table1 


  where certain criteria applie.

So, if record 100 in table1 links to 5 corresponding records 
  

in table2, 


  I want to pull the latest record from table2 where table2.parentid = 
100 and table2.user not like 'john'

There is a datestamp field in table2.

I just can't figure out how to do this.

Thanks,

Jeff


 

  


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005



  
  

  



No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005

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

RE: Query question

[mathias fatene]

Here we are Shawn,

With empty tables :
+++---+--+---+--+---
--+--+--+-+
| id | select_type| table | type | possible_keys | key  |
key_len | ref  | rows | Extra   |
+++---+--+---+--+---
--+--+--+-+
|  1 | PRIMARY| a | ALL  | NULL  | NULL |
NULL | NULL |0 | Using where |
|  2 | DEPENDENT SUBQUERY | b | ALL  | NULL  | NULL |
NULL | NULL |0 | Using where |
+++---+--+---+--+---
--+--+--+-+
2 rows in set (0.00 sec)

mysql>
mysql> explain select parentid,max(datestamp) from table2
->  group by parentid;
++-++--+---+--+-+---
---+--+-+
| id | select_type | table  | type | possible_keys | key  | key_len |
ref  | rows | Extra   |
++-++--+---+--+-+---
---+--+-+
|  1 | SIMPLE  | table2 | ALL  | NULL  | NULL |NULL |
NULL |0 | Using temporary; Using filesort |
++-++--+---+--+-+---
---+--+-+

One or two table scans ?


Best Regards

Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED] 
Sent: lundi 25 avril 2005 22:01
To: Jeff McKeon
Cc: mysql@lists.mysql.com
Subject: Re: Query question


Jeff,

Something like ...

SELECT *
FROM table2 AS a
WHERE datestamp = (
  SELECT MAX( b.datestamp )
  FROM table2 AS b
  WHERE a.parentID = b.parentID
);

PB

-


Jeff McKeon wrote:

>I have a table that contains records that link back to a main talbe in 
>a many to one configuration linked by table1.id = table2.parentid
>
>Table1 (one)
>Table2 (many)
>
>I want to pull the latest records from table2 for each record in table1

>where certain criteria applie.
>
>So, if record 100 in table1 links to 5 corresponding records in table2,

>I want to pull the latest record from table2 where table2.parentid = 
>100 and table2.user not like 'john'
>
>There is a datestamp field in table2.
>
>I just can't figure out how to do this.
>
>Thanks,
>
>Jeff
>
>
>  
>


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005


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



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

RE: Query question

[Jeff McKeon]

Thanks all but I don't have a mysql version high enough for subqueries.

Thanks,

Jeff

> -Original Message-
> From: Peter Brawley [mailto:[EMAIL PROTECTED] 
> Sent: Monday, April 25, 2005 4:01 PM
> To: Jeff McKeon
> Cc: mysql@lists.mysql.com
> Subject: Re: Query question
> 
> 
> Jeff,
> 
> Something like ...
> 
> SELECT *
> FROM table2 AS a
> WHERE datestamp = (
>   SELECT MAX( b.datestamp )
>   FROM table2 AS b
>   WHERE a.parentID = b.parentID
> );
> 
> PB
> 
> -
> 
> 
> Jeff McKeon wrote:
> 
> >I have a table that contains records that link back to a 
> main talbe in 
> >a many to one configuration linked by table1.id = table2.parentid
> >
> >Table1 (one)
> >Table2 (many)
> >
> >I want to pull the latest records from table2 for each 
> record in table1 
> >where certain criteria applie.
> >
> >So, if record 100 in table1 links to 5 corresponding records 
> in table2, 
> >I want to pull the latest record from table2 where table2.parentid = 
> >100 and table2.user not like 'john'
> >
> >There is a datestamp field in table2.
> >
> >I just can't figure out how to do this.
> >
> >Thanks,
> >
> >Jeff
> >
> >
> >  
> >
> 
> 
> -- 
> No virus found in this outgoing message.
> Checked by AVG Anti-Virus.
> Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
> 
> 


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

RE: Query question

[mathias fatene]

Hi,
Why my answer doesn't answer his question. Did you heared about his
comment. Let him do it.
If you're confused, i can explain more one-to-many relashionships. 

If you think about joins and want absolutely add them, this is the error
generating performance problems asked along all RDMBS, especially with
mysql (DBMS till now).

Best Regards

Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: lundi 25 avril 2005 21:44
To: mathias fatene
Cc: 'Jeff McKeon'; mysql@lists.mysql.com
Subject: RE: Query question


"mathias fatene" <[EMAIL PROTECTED]> wrote on 04/25/2005 03:19:33 PM:

> Hi,
> You can do something like that :
> 
> 
> mysql> select * from son;
> +--+
> | a|
> +--+
> |1 |
> |2 |
> |3 |
> +--+
> 3 rows in set (0.02 sec)
> mysql> select * from mother;
> +--+--+
> | a| b|
> +--+--+
> |1 | a|
> |1 | b|
> |2 | a|
> |2 | c|
> |3 | a|
> |3 | b|
> |3 | c|
> |3 | d|
> +--+--+
> 8 rows in set (0.00 sec)
> 
> mysql> select a,max(b) from mother
> -> group by a;
> +--++
> | a| max(b) |
> +--++
> |1 | b  |
> |2 | c  |
> |3 | d  |
> +--++
> 3 rows in set (0.00 sec)
> 
> The max will be used with your datetime column. The "son" table can 
> not be used, or joined to the mother.
> 
> 
> Best Regards
> 
> Mathias FATENE
> 
> Hope that helps
> *This not an official mysql support answer
> 
> 
> 
> -Original Message-
> From: Jeff McKeon [mailto:[EMAIL PROTECTED]
> Sent: lundi 25 avril 2005 21:01
> To: mysql@lists.mysql.com
> Subject: Query question
> 
> 
> I have a table that contains records that link back to a main talbe in

> a many to one configuration linked by table1.id = table2.parentid
> 
> Table1 (one)
> Table2 (many)
> 
> I want to pull the latest records from table2 for each record in 
> table1 where certain criteria applie.
> 
> So, if record 100 in table1 links to 5 corresponding records in 
> table2, I want to pull the latest record from table2 where 
> table2.parentid = 100 and table2.user not like 'john'
> 
> There is a datestamp field in table2.
> 
> I just can't figure out how to do this.
> 
> Thanks,
> 
> Jeff
> 

I think I am decent at what I do and that confused even me. I am totally

baffled at what SQL concept you were trying to illustrate.

How did you _help_ the OP?  The question that started this thread is an 
example of a common class of SQL problems and several solutions exist. 
Your "solution" neither answered his query nor was it explained to the 
point that made it comprehendable. Please, please try to be less
confusing 
(especially when responding to newbies).

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


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

Re: Query question

[Peter Brawley]

Jeff,
Something like ...
SELECT *
FROM table2 AS a
WHERE datestamp = (
 SELECT MAX( b.datestamp )
 FROM table2 AS b
 WHERE a.parentID = b.parentID
);
PB
-
Jeff McKeon wrote:
I have a table that contains records that link back to a main talbe in a
many to one configuration linked by table1.id = table2.parentid
Table1 (one)
Table2 (many)
I want to pull the latest records from table2 for each record in table1
where certain criteria applie.
So, if record 100 in table1 links to 5 corresponding records in table2,
I want to pull the latest record from table2 where table2.parentid = 100
and table2.user not like 'john'
There is a datestamp field in table2.
I just can't figure out how to do this.
Thanks,
Jeff
 


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Query question

[SGreen]

"mathias fatene" <[EMAIL PROTECTED]> wrote on 04/25/2005 03:19:33 PM:

> Hi,
> You can do something like that :
> 
> 
> mysql> select * from son;
> +--+
> | a|
> +--+
> |1 |
> |2 |
> |3 |
> +--+
> 3 rows in set (0.02 sec)
> mysql> select * from mother;
> +--+--+
> | a| b|
> +--+--+
> |1 | a|
> |1 | b|
> |2 | a|
> |2 | c|
> |3 | a|
> |3 | b|
> |3 | c|
> |3 | d|
> +--+--+
> 8 rows in set (0.00 sec)
> 
> mysql> select a,max(b) from mother 
> -> group by a;
> +--++
> | a| max(b) |
> +--++
> |1 | b  |
> |2 | c  |
> |3 | d  |
> +--++
> 3 rows in set (0.00 sec)
> 
> The max will be used with your datetime column. The "son" table can not
> be used, or joined to the mother.
> 
> 
> Best Regards
> 
> Mathias FATENE
> 
> Hope that helps
> *This not an official mysql support answer
> 
> 
> 
> -Original Message-
> From: Jeff McKeon [mailto:[EMAIL PROTECTED] 
> Sent: lundi 25 avril 2005 21:01
> To: mysql@lists.mysql.com
> Subject: Query question
> 
> 
> I have a table that contains records that link back to a main talbe in a
> many to one configuration linked by table1.id = table2.parentid
> 
> Table1 (one)
> Table2 (many)
> 
> I want to pull the latest records from table2 for each record in table1
> where certain criteria applie.
> 
> So, if record 100 in table1 links to 5 corresponding records in table2,
> I want to pull the latest record from table2 where table2.parentid = 100
> and table2.user not like 'john'
> 
> There is a datestamp field in table2.
> 
> I just can't figure out how to do this.
> 
> Thanks,
> 
> Jeff
> 

I think I am decent at what I do and that confused even me. I am totally 
baffled at what SQL concept you were trying to illustrate.

How did you _help_ the OP?  The question that started this thread is an 
example of a common class of SQL problems and several solutions exist. 
Your "solution" neither answered his query nor was it explained to the 
point that made it comprehendable. Please, please try to be less confusing 
(especially when responding to newbies).

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

RE: Query question

[mathias fatene]

Hi,
You can do something like that :


mysql> select * from son;
+--+
| a|
+--+
|1 |
|2 |
|3 |
+--+
3 rows in set (0.02 sec)
mysql> select * from mother;
+--+--+
| a| b|
+--+--+
|1 | a|
|1 | b|
|2 | a|
|2 | c|
|3 | a|
|3 | b|
|3 | c|
|3 | d|
+--+--+
8 rows in set (0.00 sec)

mysql> select a,max(b) from mother 
-> group by a;
+--++
| a| max(b) |
+--++
|1 | b  |
|2 | c  |
|3 | d  |
+--++
3 rows in set (0.00 sec)

The max will be used with your datetime column. The "son" table can not
be used, or joined to the mother.


Best Regards

Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-Original Message-
From: Jeff McKeon [mailto:[EMAIL PROTECTED] 
Sent: lundi 25 avril 2005 21:01
To: mysql@lists.mysql.com
Subject: Query question


I have a table that contains records that link back to a main talbe in a
many to one configuration linked by table1.id = table2.parentid

Table1 (one)
Table2 (many)

I want to pull the latest records from table2 for each record in table1
where certain criteria applie.

So, if record 100 in table1 links to 5 corresponding records in table2,
I want to pull the latest record from table2 where table2.parentid = 100
and table2.user not like 'john'

There is a datestamp field in table2.

I just can't figure out how to do this.

Thanks,

Jeff


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



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

Re: Query question

[SGreen]

"Jeff McKeon" <[EMAIL PROTECTED]> wrote on 04/25/2005 03:00:55 PM:

> I have a table that contains records that link back to a main talbe in a
> many to one configuration linked by table1.id = table2.parentid
> 
> Table1 (one)
> Table2 (many)
> 
> I want to pull the latest records from table2 for each record in table1
> where certain criteria applie.
> 
> So, if record 100 in table1 links to 5 corresponding records in table2,
> I want to pull the latest record from table2 where table2.parentid = 100
> and table2.user not like 'john'
> 
> There is a datestamp field in table2.
> 
> I just can't figure out how to do this.
> 
> Thanks,
> 
> Jeff
> 

This is a FAQ. What you have is the "groupwise maximum" problem described 
here:

http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html

Except in your case you are not looking for max price for a dealer, you 
are looking for the latest date for a given parentid. Let us know if you 
need more details and someone on the list will be happy to help!! :-)

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Query question

[Jeff McKeon]

I have a table that contains records that link back to a main talbe in a
many to one configuration linked by table1.id = table2.parentid

Table1 (one)
Table2 (many)

I want to pull the latest records from table2 for each record in table1
where certain criteria applie.

So, if record 100 in table1 links to 5 corresponding records in table2,
I want to pull the latest record from table2 where table2.parentid = 100
and table2.user not like 'john'

There is a datestamp field in table2.

I just can't figure out how to do this.

Thanks,

Jeff


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

Re: question about chunking sql uploads

[SGreen]

Frank Bax <[EMAIL PROTECTED]> wrote on 04/25/2005 02:27:58 PM:

> At 02:07 PM 4/25/05, Scott Gifford wrote:
> 
> >[EMAIL PROTECTED] writes:
> >
> > > Frank Bax <[EMAIL PROTECTED]> wrote on 04/25/2005 11:47:12 AM:
> > >> Or simply use "split", default is 1000 lines, but can be changed 
via
> > >> command line.
> >
> >That's a start, but the files each need to be a valid SQL statement,
> >so that's not enough; a little fixup needs to happen at the beginning
> >and end of each file.
> 
> 
> OP did not say that the SQL statements crossed over a newline; but if 
they 
> do, it is a trivial sequence of shell commands to "fix" them so that 
they 
> are one line each. 
> 
> 
Odds are, he has one long INSERT statement (extended insert format). 
Mysqldump creates those very well and will make them as big as the source 
data unless you tell it to make smaller chunks. I working from the 
assumption he has a 9MB INSERT statement and needs to split it into nine 
1MB (or smaller) INSERT statements.

If I am right, he does have a delimiter between each of his records (the 
comma between each tuple of values) and you could break up his source file 
with those. However he has to have his original "INSERT  VALUES" at 
the head of each file and a terminating semicolon at the end of every file 
(and still stay smaller than 1M) or it won't work. 

I don't have a tool or know of a link to a tool I can send to him to help 
him split his big INSERT statement into smaller ones. I thought he might 
be able to temporarily reset the max_allowed_packet variable value on his 
destination server so that he could avoid breaking up his script but I 
haven't heard if that worked or not.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: question about chunking sql uploads

[Frank Bax]

At 02:07 PM 4/25/05, Scott Gifford wrote:
[EMAIL PROTECTED] writes:
> Frank Bax <[EMAIL PROTECTED]> wrote on 04/25/2005 11:47:12 AM:
>> Or simply use "split", default is 1000 lines, but can be changed via
>> command line.
That's a start, but the files each need to be a valid SQL statement,
so that's not enough; a little fixup needs to happen at the beginning
and end of each file.

OP did not say that the SQL statements crossed over a newline; but if they 
do, it is a trivial sequence of shell commands to "fix" them so that they 
are one line each. 

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

Re: question about chunking sql uploads

[Scott Gifford]

[EMAIL PROTECTED] writes:

> Frank Bax <[EMAIL PROTECTED]> wrote on 04/25/2005 11:47:12 AM:
>
>> At 10:44 AM 4/25/05, Art.M (Wikki) wrote:
>> >I have a large .sql file to upload which is about 9 mb and I was
>> >wondering if anyone knew of a program that could break it up into
>> >chunks of 2 mb or under? So I can upload it to a shared web server.
>> 
>> 
>> You can't upload a 9M file to webserver?  But you can upload 9x1M files? 
>
>> Fix your webserver.  You didn't google for this did you?  You also
>> didn't mention OS, so we'll assume unix based.

This is probably more common than you would expect.  I've run into it
when using shared hosting providers; they have timeouts and resource
limits on programs like phpMyAdmin, and so can't handle big chunks of
data.  It's not very easy to call up a large shared hosting provider
and tell them "fix your server", especially if you are a small
customer.

That said, I don't have a great solution.  I often "re-chunk" these
files by hand, which isn't that bad if you only need to break it into
5-10 chunks.  It would be straightforward to write a Perl script to do
this.

>> You could try compressing file with zip, gzip, etc.

This often helps, too, if the program you're importing data with
supports it.

>> Or simply use "split", default is 1000 lines, but can be changed via 
>> command line.

That's a start, but the files each need to be a valid SQL statement,
so that's not enough; a little fixup needs to happen at the beginning
and end of each file.

---ScottG.

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

RE: Converting to InnoDB?

[Carl Riches]

On Mon, 25 Apr 2005, mathias fatene wrote:
Sorry,
Alter table toto ENGINE=innodb.
You don't must, you can. You can also have differents storage ENGINES in
the same mysql database.
With innodb, you will earn ROW level locking.
Best Regards

Mathias FATENE
Thanks, Mathias!
Carl
Carl G. Riches
Software Engineer
Department of Mathematics
Box 354350  voice: 206-543-5082 or 206-616-3636
University of Washingtonfax:   206-543-0397
Seattle, WA  98195-4350 internet:  [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Converting to InnoDB?

[mathias fatene]

Sorry,
Alter table toto ENGINE=innodb.

You don't must, you can. You can also have differents storage ENGINES in
the same mysql database.
With innodb, you will earn ROW level locking.

Best Regards

Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-Original Message-
From: mathias fatene [mailto:[EMAIL PROTECTED] 
Sent: lundi 25 avril 2005 19:33
To: 'Carl Riches'; 'mysql@lists.mysql.com'
Subject: RE: Converting to InnoDB?


Yes, but your myIsam Tables stay myisam ones.

After restarting, you must change them to innodb by :

Alter table toto storage=innodb.

For new tables, they will have innodb storage.

Best Regards

Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-Original Message-
From: Carl Riches [mailto:[EMAIL PROTECTED] 
Sent: lundi 25 avril 2005 19:26
To: mysql@lists.mysql.com
Cc: Carl Riches
Subject: Converting to InnoDB?



We are running MySQL on Red Hat Enterprise Linux 3, using the Red Hat-
supplied RPM file mysql-server-3.23.58-2.3.  Our current MySQL 
configuration has MyISAM as the default database file type.  I would
like 
to change this such that InnoDB is the default.  My understanding of the

documentation says that, after changing the configuration file and 
restarting the MySQL server, there will be no problems using the
existing 
MyISAM databases.

Is that correct?

Thanks,
Carl G. Riches
Software Engineer
Department of Mathematics
Box 354350  voice: 206-543-5082 or 206-616-3636
University of Washingtonfax:   206-543-0397
Seattle, WA  98195-4350 internet:  [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: Converting to InnoDB?

[mathias fatene]

Yes, but your myIsam Tables stay myisam ones.

After restarting, you must change them to innodb by :

Alter table toto storage=innodb.

For new tables, they will have innodb storage.

Best Regards

Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-Original Message-
From: Carl Riches [mailto:[EMAIL PROTECTED] 
Sent: lundi 25 avril 2005 19:26
To: mysql@lists.mysql.com
Cc: Carl Riches
Subject: Converting to InnoDB?



We are running MySQL on Red Hat Enterprise Linux 3, using the Red Hat-
supplied RPM file mysql-server-3.23.58-2.3.  Our current MySQL 
configuration has MyISAM as the default database file type.  I would
like 
to change this such that InnoDB is the default.  My understanding of the

documentation says that, after changing the configuration file and 
restarting the MySQL server, there will be no problems using the
existing 
MyISAM databases.

Is that correct?

Thanks,
Carl G. Riches
Software Engineer
Department of Mathematics
Box 354350  voice: 206-543-5082 or 206-616-3636
University of Washingtonfax:   206-543-0397
Seattle, WA  98195-4350 internet:  [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]

Converting to InnoDB?

[Carl Riches]

We are running MySQL on Red Hat Enterprise Linux 3, using the Red Hat-
supplied RPM file mysql-server-3.23.58-2.3.  Our current MySQL 
configuration has MyISAM as the default database file type.  I would like 
to change this such that InnoDB is the default.  My understanding of the 
documentation says that, after changing the configuration file and 
restarting the MySQL server, there will be no problems using the existing 
MyISAM databases.

Is that correct?
Thanks,
Carl G. Riches
Software Engineer
Department of Mathematics
Box 354350  voice: 206-543-5082 or 206-616-3636
University of Washingtonfax:   206-543-0397
Seattle, WA  98195-4350 internet:  [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: question about chunking sql uploads

[SGreen]

Frank Bax <[EMAIL PROTECTED]> wrote on 04/25/2005 11:47:12 AM:

> At 10:44 AM 4/25/05, Art.M (Wikki) wrote:
> >I have a large .sql file to upload which is about 9 mb and I was
> >wondering if anyone knew of a program that could break it up into
> >chunks of 2 mb or under? So I can upload it to a shared web server.
> 
> 
> You can't upload a 9M file to webserver?  But you can upload 9x1M files? 

> Fix your webserver.  You didn't google for this did you?  You also 
didn't 
> mention OS, so we'll assume unix based.
> 
> You could try compressing file with zip, gzip, etc.
> Or simply use "split", default is 1000 lines, but can be changed via 
> command line.
> 
> man split 
> 
> 

I think the problem is that the default max_allowed_packet setting is only 
1M so the OP is looking for a way to split his SQL script into blocks of 
<1M. I haven't tested this but it MAY be possible to temporarily set the 
web server's max_allowed_packet to 9M (or bigger) so that the OP could get 
his entire file processed at once. Please check:

http://dev.mysql.com/doc/mysql/en/set-option.html
and
http://dev.mysql.com/doc/mysql/en/dynamic-system-variables.html?

Other than that, I remember a thread (sometime last year) in which someone 
recommended a product that chunks SQL files but I couldn't find it with a 
quick check. Here's a link to the archives for all of the MySQL lists, 
maybe you'll have better luck:

http://lists.mysql.com/

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: question about chunking sql uploads

[Frank Bax]

At 10:44 AM 4/25/05, Art.M (Wikki) wrote:
I have a large .sql file to upload which is about 9 mb and I was
wondering if anyone knew of a program that could break it up into
chunks of 2 mb or under? So I can upload it to a shared web server.

You can't upload a 9M file to webserver?  But you can upload 9x1M files? 
Fix your webserver.  You didn't google for this did you?  You also didn't 
mention OS, so we'll assume unix based.

You could try compressing file with zip, gzip, etc.
Or simply use "split", default is 1000 lines, but can be changed via 
command line.

man split 

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

RE: MYSQL to XML

[mfatene]

Hi Mikel,
Show create table shows a line CONSTRAINT ... FORIEGN KEY ...

you can add a grep on this line. But this will be difficult. You can construct
another desc2xml using just "show create table" to have it easier.

Mathias

Selon Mikel - <[EMAIL PROTECTED]>:

> Thanx Mathias for your quick and effective response, I see that your program
> almost display the format that I need, the thing is that I need the foreign
> key information too, Does MySQL have a statement besides "show create table"
> to display this information?Thanks again for your suggestions and help
>
> Greetings
>
> >From: "mathias fatene" <[EMAIL PROTECTED]>
> >To: 
> >CC: 
> >Subject: RE: MYSQL to XML
> >Date: Sun, 24 Apr 2005 21:39:14 +0200
> >MIME-Version: 1.0
> >Received: from lists.mysql.com ([213.136.52.31]) by mc3-f23.hotmail.com
> >with Microsoft SMTPSVC(6.0.3790.211); Sun, 24 Apr 2005 12:42:26 -0700
> >Received: (qmail 15912 invoked by uid 109); 24 Apr 2005 19:40:50 -
> >Received: (qmail 15893 invoked from network); 24 Apr 2005 19:40:50 -
> >Received: pass (lists.mysql.com: local policy)
> >X-Message-Info: JGTYoYF78jEQFMtosA6GPW/w+/WF28t94KBGDmreITY=
> >Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm
> >List-ID: 
> >Precedence: bulk
> >List-Help: 
> >List-Unsubscribe:
> >
> >List-Post: 
> >List-Archive: http://lists.mysql.com/mysql/183030
> >Delivered-To: mailing list mysql@lists.mysql.com
> >X-MSMail-Priority: Normal
> >X-Mailer: Microsoft Outlook, Build 10.0.2616
> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> >X-Virus-Checked: Checked
> >Return-Path: [EMAIL PROTECTED]
> >X-OriginalArrivalTime: 24 Apr 2005 19:42:26.0285 (UTC)
> >FILETIME=[BE1839D0:01C54905]
> >
> >Hi Mikel,
> >There are a lot of possibilities including commercial (:o)) products.
> >I suggest you those solutions. The output should be reparsed for your
> >needs :
> >1. the -X on client :
> > C:\Mysql>mysql -u mathias world -X -e "desc country"
> > 
> >
> > 
> >   
> > Code
> > char(3)
> > 
> > PRI
> > 
> > 
> >   
> >   
> > Name
> > char(52)
> > 
> > 
> > 
> > 
> >   
> >
> >   
> > Continent
> >...
> >...
> >
> >2. install perl DBI and DBIx-XML_RDB modules :
> >#!perl -w
> ># ---
> ># Describe2xml
> ># Author : Mathias FATENE
> ># Date   : 24 april 2005
> ># ---
> >use DBIx::XML_RDB;
> >
> >  my $userid='root';
> >  my $password='**';
> >  my $dbname='world';
> >  my $dsn = "DBI:mysql:database=$dbname;host=localhost";
> >
> >  my $xmlout = DBIx::XML_RDB->new($dsn,'mysql',$userid, $password) || die
> >"Failed to make new xmlout";
> >
> >   $xmlout->DoSql("describe country");
> >   print $xmlout->GetData;
> >
> >C:\Mysql>perl describe.pl
> >
> >
> > 
> > 
> > Code
> > char(3)
> > 
> > PRI
> > 
> > 
> > 
> > 
> > Name
> > char(52)
> > 
> > 
> > 
> > 
> > 
> > 
> >...
> >...
> >3. install Perl DBI and DBD-Mysql and use my program (formatted for your
> >needs) :
> >#!perl -w
> ># ---
> ># Describe2xml
> ># Author : Mathias FATENE
> ># Date   : April, 24 2005
> ># ---
> >use DBI;
> >
> >  my $userid='root';
> >  my $password='';
> >  my $dbname='world';
> >  my $dsn = "DBI:mysql:database=$dbname;host=localhost";
> >
> >  my $dbh = DBI->connect($dsn,$userid, $password,{'RaiseError' => 1});
> >   # ---
> >   # describe country table and print it in XML format
> >   # ---
> >   my $table="country";
> >   $sth = $dbh->prepare("describe $table");
> >   $sth->execute();
> >
> >   print "\\n";
> >   while (my @ref = $sth->fetchrow_array()) {
> >print "\ >type=\"$ref[1]\"";
> >print " primaryKey=\"true\"" if ($ref[3] eq "PRI") ;
> >print "/\>\n";
> >   }
> >   $sth->finish();
> >   print "\\n";
> >
> >   # Disconnect from the database.
> >   $dbh->disconnect();
> >
> >C:\Mysql>perl desc.pl country
> >
> >
> >
> > >type="enum('Asia','Europe','North
> >America','Africa','Oceania','Antarctica','South America')"/>
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >is this beautifull ?
> >
> >I will modify Describe2xml.pl to be 

Re: Message repeating every second in Error Log

[mfatene]

Hi,
do you continue to have it even if you EXIT your Mysql Administrator ?

Mathias

Selon [EMAIL PROTECTED]:

> Does anyone know why this message keeps repeating:
> 050425 11:31:40 229 Query   SHOW STATUS
> 229 Query   SHOW INNODB STATUS
> 050425 11:31:41 229 Query   SHOW STATUS
> 229 Query   SHOW INNODB STATUS
> 050425 11:31:42 229 Query   SHOW STATUS
> 229 Query   SHOW INNODB STATUS
> 050425 11:31:43 229 Query   SHOW STATUS
> 229 Query   SHOW INNODB STATUS
> 050425 11:31:44 229 Query   SHOW STATUS
> 229 Query   SHOW INNODB STATUS
>
>
> __
> Switch to Netscape Internet Service.
> As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register
>
> Netscape. Just the Net You Need.
>
> New! Netscape Toolbar for Internet Explorer
> Search from anywhere on the Web and block those annoying pop-ups.
> Download now at http://channels.netscape.com/ns/search/install.jsp
>
> --
> 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]

Message repeating every second in Error Log

[TheRefUmp]

Does anyone know why this message keeps repeating:
050425 11:31:40 229 Query   SHOW STATUS
229 Query   SHOW INNODB STATUS
050425 11:31:41 229 Query   SHOW STATUS
229 Query   SHOW INNODB STATUS
050425 11:31:42 229 Query   SHOW STATUS
229 Query   SHOW INNODB STATUS
050425 11:31:43 229 Query   SHOW STATUS
229 Query   SHOW INNODB STATUS
050425 11:31:44 229 Query   SHOW STATUS
229 Query   SHOW INNODB STATUS


__
Switch to Netscape Internet Service.
As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register

Netscape. Just the Net You Need.

New! Netscape Toolbar for Internet Explorer
Search from anywhere on the Web and block those annoying pop-ups.
Download now at http://channels.netscape.com/ns/search/install.jsp

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

MySQL Client Hangs on QNX

[Stephen Rasku]

I am using MySQL 4.0.17 on QNX 6.2.1b.

I have noticed this in the last couple of days.  There were two MySQL
clients running: one since 1:30 that morning; one at 1:30 the previous
morning.  These are "reset master" commands we issue from cron to clean up
the transaction logs.

This morning I tried running mysql from the command line and it hung.  I
then run with the debug option enabled.  Here is the content of
/tmp/mysql.trace:

| >my_malloc
| | my: Size: 520  MyFlags: 16
| | exit: ptr: 8085d28
| my_malloc
| | my: Size: 512  MyFlags: 48
| | exit: ptr: 8085b20
| mysql_close
| | >my_free
| | | my: ptr: 0
| | my_free
| | | my: ptr: 0
| | my_free
| | | my: ptr: 0
| | my_free
| | | my: ptr: 0
| | my_free
| | | my: ptr: 0
| | my_free
| | | my: ptr: 0
| | my_free
| | | my: ptr: 0
| | my_free
| | | my: ptr: 0
| | my_free
| | | my: ptr: 0
| | my_free
| | | my: ptr: 0
| | my_free
| | | my: ptr: 0
| | my_free
| | | my: ptr: 0
| | my_free
| | | my: ptr: 0
| | my_free
| | | my: ptr: 0
| | mysql_close
| | mysql_real_connect
| | enter: host: (Null)  db: (Null)  user: root
| | info: Using UNIX sock '/tmp/mysql.sock'
| | >vio_new
| | | enter: sd=4
| | | >my_malloc
| | | | my: Size: 84  MyFlags: 16
| # tail -f /tmp/mysql.trace
| | mysql_real_connect
| | enter: host: (Null)  db: (Null)  user: root
| | info: Using UNIX sock '/tmp/mysql.sock'
| | >vio_new
| | | enter: sd=4
| | | >my_malloc
| | | | my: Size: 84  MyFlags: 16
|

To me, it appears that it is hung in my_malloc().  However not on the first
call.  It doesn't appear that I am running out of memory:

# pidin in
CPU:X86 Processors:1 FreeMem:927Mb/1015Mb BootTime:Apr 21 16:44:25 PDT 2005
Processor1: 1586 Intel ?86 F15M2S9 2670Mhz FPU

Any ideas what is going wrong?  This system has been running fine for months
until now.

...Stephen


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

Re: mysql_fix_privilege_tables error

[Josh Trutwin]

On Fri, 22 Apr 2005 22:44:44 +0300
Gleb Paharenko <[EMAIL PROTECTED]> wrote:

> Hello.
> 
> 
> 
> Make a bug or feature report at http://bugs.mysql.com.

Already did - 10098 - it was recently closed, guess it was already
fixed in 5.0.5.

Josh

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

question about chunking sql uploads

[Art.M (Wikki)]

Hello, 

I have a large .sql file to upload which is about 9 mb and I was
wondering if anyone knew of a program that could break it up into
chunks of 2 mb or under? So I can upload it to a shared web server.
Thanks ahead for your response.

Wikki

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

Re: joining six tables by mutual column

[Nick Pasich]

Try this

If something is numeric

SELECT * FROM table1, table2, table3, table4, table5, table6 
WHERE table1.something = table2.something
  AND
 table1.something = table3.something
 AND
 table1.something = table4.something
 AND
 table1.something = table5.something
 AND
 table1.something = table6.something
 ;


If something isn't numeric.

SELECT * FROM table1, table2, table3, table4, table5, table6 
WHERE table1.something LIKE table2.something
  AND
  table1.something LIKE table3.something
  AND
  table1.something LIKE table4.something
  AND
  table1.something LIKE table5.something
  AND
  table1.something LIKE table6.something
  ;


  ( Nick Pasich )

On Mon, Apr 25, 2005 at 12:51:53AM +0200, Schalk Neethling wrote:
> Greetings everyone.
> 
> Hope someone can give me some pointers here. I have six tables in the 
> database and I need to JOIN them on a row that appears in all of the 
> tables. How do I do this? I have so far done the normal 'cross-join' 
> saying SELECT * FROM table1, table2, table3, table4, table5, table6 
> WHERE something = something;
> 
> I have also added STRAIGHT_JOIN to force the order but, how do I JOIN 
> six tables to/by one column? I have done some google searches as well as 
> looked at MySQL 2nd edition by Paul DuBois, sorry if I missed something 
> here Paul, and so far I have not found an answer. Any help or pointers 
> will be appreciated. Thank you.
> 
> -- 
> Kind Regards
> Schalk Neethling
> Web Developer.Designer.Programmer.President
> Volume4.Business.Solution.Developers
> emotionalize.conceptualize.visualize.realize
> Landlines
> Tel: +27125468436
> Fax: +27125468436
> Web
> email:[EMAIL PROTECTED]
> Global: www.volume4.com
> Messenger
> Yahoo!: v_olume4
> AOL: v0lume4
> MSN: [EMAIL PROTECTED]
> 
> We support OpenSource
> Get Firefox!- The browser reloaded - 
> http://www.mozilla.org/products/firefox/
> 
> This message contains information that is considered to be sensitive or 
> confidential and may not be forwarded or disclosed to any other party 
> without the permission of the sender. If you received this message in 
> error, please notify me immediately so that I can correct and delete the 
> original email. Thank you.
> 
> 


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

RE: MYSQL to XML

[Mikel -]

Thanx Mathias for your quick and effective response, I see that your program 
almost display the format that I need, the thing is that I need the foreign 
key information too, Does MySQL have a statement besides "show create table" 
to display this information?Thanks again for your suggestions and help

Greetings
From: "mathias fatene" <[EMAIL PROTECTED]>
To: 
CC: 
Subject: RE: MYSQL to XML
Date: Sun, 24 Apr 2005 21:39:14 +0200
MIME-Version: 1.0
Received: from lists.mysql.com ([213.136.52.31]) by mc3-f23.hotmail.com 
with Microsoft SMTPSVC(6.0.3790.211); Sun, 24 Apr 2005 12:42:26 -0700
Received: (qmail 15912 invoked by uid 109); 24 Apr 2005 19:40:50 -
Received: (qmail 15893 invoked from network); 24 Apr 2005 19:40:50 -
Received: pass (lists.mysql.com: local policy)
X-Message-Info: JGTYoYF78jEQFMtosA6GPW/w+/WF28t94KBGDmreITY=
Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm
List-ID: 
Precedence: bulk
List-Help: 
List-Unsubscribe: 

List-Post: 
List-Archive: http://lists.mysql.com/mysql/183030
Delivered-To: mailing list mysql@lists.mysql.com
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook, Build 10.0.2616
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Virus-Checked: Checked
Return-Path: [EMAIL PROTECTED]
X-OriginalArrivalTime: 24 Apr 2005 19:42:26.0285 (UTC) 
FILETIME=[BE1839D0:01C54905]

Hi Mikel,
There are a lot of possibilities including commercial (:o)) products.
I suggest you those solutions. The output should be reparsed for your
needs :
1. the -X on client :
C:\Mysql>mysql -u mathias world -X -e "desc country"


  
Code
char(3)

PRI


  
  
Name
char(52)




  
  
Continent
...
...
2. install perl DBI and DBIx-XML_RDB modules :
#!perl -w
# ---
# Describe2xml
# Author : Mathias FATENE
# Date   : 24 april 2005
# ---
use DBIx::XML_RDB;
 my $userid='root';
 my $password='**';
 my $dbname='world';
 my $dsn = "DBI:mysql:database=$dbname;host=localhost";
 my $xmlout = DBIx::XML_RDB->new($dsn,'mysql',$userid, $password) || die
"Failed to make new xmlout";
  $xmlout->DoSql("describe country");
  print $xmlout->GetData;
C:\Mysql>perl describe.pl




Code
char(3)

PRI




Name
char(52)






...
...
3. install Perl DBI and DBD-Mysql and use my program (formatted for your
needs) :
#!perl -w
# ---
# Describe2xml
# Author : Mathias FATENE
# Date   : April, 24 2005
# ---
use DBI;
 my $userid='root';
 my $password='';
 my $dbname='world';
 my $dsn = "DBI:mysql:database=$dbname;host=localhost";
 my $dbh = DBI->connect($dsn,$userid, $password,{'RaiseError' => 1});
  # ---
  # describe country table and print it in XML format
  # ---
  my $table="country";
  $sth = $dbh->prepare("describe $table");
  $sth->execute();
  print "\\n";
  while (my @ref = $sth->fetchrow_array()) {
   print "\\n";
  }
  $sth->finish();
  print "\\n";
  # Disconnect from the database.
  $dbh->disconnect();
C:\Mysql>perl desc.pl country

















is this beautifull ?
I will modify Describe2xml.pl to be more parametrized (user, db, pass,
FK, ...) as soon as possible.
Mathias

>>  Hi list, does it possible for MySQL to generate XML in the followin
format:
>>
>>  
>>  >  required="true" type="VARCHAR" size="10"/>
>>  
>>  
>>  
>>  
>>
>>  
>>  
>>  
>>
>>
>>  This XML is the structure of the ServiceType table,  I'll hope that
you >>   can
>>  help me
>>
>>  Thnx in advanced
>>
>>  Greetings
>>
>>  P.S. Any suggestions (tools) will be appreciated
>>
>>
>>
>>  Thread
>>
>>  * MySQL to XML - Mikel -, April 23 2005 1:07am
>>


--
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: User Variables

[SGreen]

"Ed Reed" <[EMAIL PROTECTED]> wrote on 04/25/2005 02:33:23 AM:

> Does anyone else have any ideas about this topic? 
> 
> Thanks
> 
> >>>Ed Reed <[EMAIL PROTECTED]> 04/23/05 1:29 pm >>>
> Thanks for the reply,
> 
> I realize that user variables disappear when the connection closes 
> but I don't understand why what I'm trying to accomplish can't be 
> done. Doesn't it make since that if you can load a single file with 
> multiple SQL commands and have that work succesfully then you should
> be able to have a single call with multple SQL commands work just as
> succesfully? Is there any way to do what I asked in my original post?
> 
> Thanks again for the reply.
> 
> 
> >>>Paul DuBois <[EMAIL PROTECTED]> 4/23/05 12:12:32 PM >>>
> At 22:04 -0700 4/22/05, Ed Reed wrote:
> >Thanks for the reply,
> >
> >So is there anyway to use User Variables with a single connection.
> >All my apps are in VB6 and VBA. They all take a query, open a
> >connection, run query, fill array from query results, close
> >connection and pass back the array. Because of backward
> >compatibility there's no way I can change them to do otherwise.
> 
> User variables disappear when the connection closes.
> 
> 
> >
> >Thanks again.
> >
> Chris < [EMAIL PROTECTED] > 04/22/05 7:56 PM >>>
> >Ed Reed wrote:
> >
> >>If I run the following in MySQLFront v3.1
> >>
> >>Set @A='Test';
> >>Select @A;
> >>
> >>I get back same result
> >>
> >>+--+
> >>| @A |
> >>+--+
> >>| Test |
> >>+--+
> >>
> >>If I run the same query in MySQL Query Browser v1.1.6 I get this,
> >>
> >>ErrNo 1060, You have an error in your SQL syntax; check the manual
> >>that corresponds to your MySQL server version for the right syntax
> >>to use near ';
> >>select @A' at line 1
> >>
> >>and If I run the same query in my application I get the same error
> >>as the Query Browser.
> >>
> >>Anyone know how I can get my application to give me what I'm looking 
for?
> >>
> >>
> >>
> >The command line interface allows you to run multiple commands at once.
> >The Query Browser and PHP interfaces allow only one query per function
> >call. So mysql_query('SET @A; SELECT @A;') would fail. You need to run
> >each query separately.
> >
> >This is certainly the case in your application, even if it's not PHP.
> >
> >If you ran the queries separately in the Query Browser, you wouldn't 
get
> >the results you expect. It would forget the value of @A because it
> >closes the connection each time. It's possible to keep the connection
> >open by Starting a transaction (even if you're using MyISAM tables).
> >
> >Chris
> >
> >--
> >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]
> 
> 
> --
> Paul DuBois, MySQL Documentation Team
> Madison, Wisconsin, USA
> MySQL AB, www.mysql.com
> 

Dude, you should chill. You originally posted during the weekend, not 
everyone keeps up at home. Sometimes it just takes a few days to get all 
of the responses to a posting.

In VB (and VBA and VBScript) *you* control when the connection opens and 
closes (not like the program "query browser" which you tried to test 
with). Using ADO as an example, this snipped of code  identifies a one 
hour time window starting 5 minutes before the most recent entry in a log 
table (it's a made-up query. I don't actually use this.) then uses that 
window to get the actual log records.

set oConn = new ADODB.Connection
set oRS = new ADODB.Recordset
oConn.Open "you connection string here"
... processing...
oConn.Execute "select @A := max(datefieldname)- interval 5 minute, @B := 
max(datefieldname)- interval 65 minute from logtable"
 more processing ...
sSQL = "SELECT * from logtable where datefieldname < @A and datefieldname 
>[EMAIL PROTECTED]"
oRS.Open sSQL, oConn
... more processing ...
oRS.close
oConn.close

Until you close the connection, it stays open. That means that all queries 
 executed _through a particular connection_ have access to any variable 
value you have already set with that connection. I set the values of @A 
and @B in one query then, lines later, I used them in building a 
recordset. Because the recordset is on the same connection the variables 
were created with, its query sees them as populated values.

However!! Command.Execute() does not accept chained statements. You cannot 
pass in two statements in a row separated by a semicolon. You must split 
your SQL and execute it as separate requests. If you didn't split them up, 
which statement's results code would Command.Execute() return with? What 
if you got an error code as a result? Which query failed? How many 
statements executed before failure? How much are you going to need to undo 
(assuming you weren't in a transaction) to recover from a failed 
statement?

Because the Recordset object support

Re: problem with update statement

[ragan_davis]

Thanks for the info. I am using 4.0.24, so maybe this is the problem.  
I was able to get around this by first checking if the mac field was 
empty, but I will look into upgrading mysql to a more current version.

thanks

- Original Message -
From: [EMAIL PROTECTED]
Date: Monday, April 25, 2005 9:26 am
Subject: Re: problem with update statement

> Hi,
> this is an example where what you describe doesn't happen (v 4.1 
> and 5.0) :
> mysql> create table ports(mac varchar(17),port_index varchar(3));
> Query OK, 0 rows affected (0.10 sec)
> 
> mysql> insert into ports 
> values('00:04:FB:23:5A:44','120'),(NULL,'120');Query OK, 2 rows 
> affected (0.08 sec)
> Records: 2  Duplicates: 0  Warnings: 0
> 
> mysql> select * from ports;
> +---++
> | mac   | port_index |
> +---++
> | 00:04:FB:23:5A:44 | 120|
> | NULL  | 120|
> +---++
> 2 rows in set (0.00 sec)
> 
> mysql> select * from ports where mac='00:04:FB:23:5A:44';
> +---++
> | mac   | port_index |
> +---++
> | 00:04:FB:23:5A:44 | 120|
> +---++
> 1 row in set (0.00 sec)
> 
> mysql>
> mysql> update ports set port_index='123' where 
> mac='00:04:FB:23:5A:44';Query OK, 1 row affected (0.41 sec)
> Rows matched: 1  Changed: 1  Warnings: 0
> 
> mysql> select * from ports;
> +---++
> | mac   | port_index |
> +---++
> | 00:04:FB:23:5A:44 | 123|
> | NULL  | 120|
> +---++
> 2 rows in set (0.00 sec)
> 
> mysql>
> 
> if you use an old version which is buggy, just migrate.
> 
> Mathias
> 
> Selon Brent Baisley <[EMAIL PROTECTED]>:
> 
> > That is very odd behavior and shouldn't be happening. I ran a quick
> > test on my machine and MySQL does not exhibit that behavior. You 
may
> > try running and repair on your table. Perhaps something is out of
> > whack. What version of MySQL are you running?
> >
> >
> > On Apr 22, 2005, at 6:08 PM, [EMAIL PROTECTED] wrote:
> >
> > > Hello, all:
> > >
> > > In a MyISAM table, I have a column named "MAC", of type 
> VARCHAR(17).> > This field is used to hold MAC addresses of 
> computers' network
> > > interface cards.  These MAC addresses are in the
> > > form "XX:XX:XX:XX:XX:XX", where X can be either a number or an
> > > uppercase letter.  I can run "select * from table where
> > > MAC='00:04:FB:23:5A:44'" and the correct record is returned.  
> However,> > performing "update table set port_index='123' where
> > > MAC='00:04:FB:23:5A:44'" does not work as I expected.  It does 
> update> > the correct record, but also updates all other records 
> whose MAC field
> > > is empty.  When I originally created this field, I used type 
> VARCHAR> > (17), null, default value NULL.  In troubleshooting 
> this, I have since
> > > change the type to CHAR(17), not null, empty default value.  
Still
> > > have the same problem.  Does anyone know what's going on here, 
> and how
> > > I could correct this?
> > >
> > > Thanks!
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > > http://lists.mysql.com/[EMAIL PROTECTED]
> > >
> > >
> > --
> > Brent Baisley
> > Systems Architect
> > Landover Associates, Inc.
> > Search & Advisory Services for Advanced Technology Environments
> > p: 212.759.6400/800.759.0577
> >
> >
> > --
> > 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: problem with update statement

[mfatene]

Hi,
this is an example where what you describe doesn't happen (v 4.1 and 5.0) :
mysql> create table ports(mac varchar(17),port_index varchar(3));
Query OK, 0 rows affected (0.10 sec)

mysql> insert into ports values('00:04:FB:23:5A:44','120'),(NULL,'120');
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from ports;
+---++
| mac   | port_index |
+---++
| 00:04:FB:23:5A:44 | 120|
| NULL  | 120|
+---++
2 rows in set (0.00 sec)

mysql> select * from ports where mac='00:04:FB:23:5A:44';
+---++
| mac   | port_index |
+---++
| 00:04:FB:23:5A:44 | 120|
+---++
1 row in set (0.00 sec)

mysql>
mysql> update ports set port_index='123' where mac='00:04:FB:23:5A:44';
Query OK, 1 row affected (0.41 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from ports;
+---++
| mac   | port_index |
+---++
| 00:04:FB:23:5A:44 | 123|
| NULL  | 120|
+---++
2 rows in set (0.00 sec)

mysql>

if you use an old version which is buggy, just migrate.

Mathias

Selon Brent Baisley <[EMAIL PROTECTED]>:

> That is very odd behavior and shouldn't be happening. I ran a quick
> test on my machine and MySQL does not exhibit that behavior. You may
> try running and repair on your table. Perhaps something is out of
> whack. What version of MySQL are you running?
>
>
> On Apr 22, 2005, at 6:08 PM, [EMAIL PROTECTED] wrote:
>
> > Hello, all:
> >
> > In a MyISAM table, I have a column named "MAC", of type VARCHAR(17).
> > This field is used to hold MAC addresses of computers' network
> > interface cards.  These MAC addresses are in the
> > form "XX:XX:XX:XX:XX:XX", where X can be either a number or an
> > uppercase letter.  I can run "select * from table where
> > MAC='00:04:FB:23:5A:44'" and the correct record is returned.  However,
> > performing "update table set port_index='123' where
> > MAC='00:04:FB:23:5A:44'" does not work as I expected.  It does update
> > the correct record, but also updates all other records whose MAC field
> > is empty.  When I originally created this field, I used type VARCHAR
> > (17), null, default value NULL.  In troubleshooting this, I have since
> > change the type to CHAR(17), not null, empty default value.  Still
> > have the same problem.  Does anyone know what's going on here, and how
> > I could correct this?
> >
> > Thanks!
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> --
> Brent Baisley
> Systems Architect
> Landover Associates, Inc.
> Search & Advisory Services for Advanced Technology Environments
> p: 212.759.6400/800.759.0577
>
>
> --
> 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 update statement

[Brent Baisley]

That is very odd behavior and shouldn't be happening. I ran a quick 
test on my machine and MySQL does not exhibit that behavior. You may 
try running and repair on your table. Perhaps something is out of 
whack. What version of MySQL are you running?

On Apr 22, 2005, at 6:08 PM, [EMAIL PROTECTED] wrote:
Hello, all:
In a MyISAM table, I have a column named "MAC", of type VARCHAR(17).
This field is used to hold MAC addresses of computers' network
interface cards.  These MAC addresses are in the
form "XX:XX:XX:XX:XX:XX", where X can be either a number or an
uppercase letter.  I can run "select * from table where
MAC='00:04:FB:23:5A:44'" and the correct record is returned.  However,
performing "update table set port_index='123' where
MAC='00:04:FB:23:5A:44'" does not work as I expected.  It does update
the correct record, but also updates all other records whose MAC field
is empty.  When I originally created this field, I used type VARCHAR
(17), null, default value NULL.  In troubleshooting this, I have since
change the type to CHAR(17), not null, empty default value.  Still
have the same problem.  Does anyone know what's going on here, and how
I could correct this?
Thanks!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Write to a mysql table from Excel

[Jay Blanchard]

[snip]
Don't be so hasty to criticise.  I took the original question to mean 
"While I have an existing spreadsheet open in EXCEL, can I cause a
selected 
row in a MySQL table to be updated?".  This is quite a different
question 
than "How can I convert one sheet from an EXCEL file into a new MySQL 
table?".  The later can easily be done several ways as you suggest, the 
former cannot.  Can the former be done with ODBC?  I didn't find any 
answers on google. 
[/snip]

A. I was not being critical, the question was very open ended and
subject to multiple interpretations, including yours. I was hoping to
guide the OP to be able to ask a question with more specific information
about what he wanted to do.

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

Re: Need help adding database to multi server mysql?

[Gleb Paharenko]

Hello.



Have you ran mysql_init_db on a new data directory?



> store the tables that store grants and such, but how to we get those

> created for the new instance.  We've been all over the MySQL.com docs



It is unclear for me. You may specify the datadir for every new

instance. See:



  http://dev.mysql.com/doc/mysql/en/mysqld-multi.html





Don Vaillancourt <[EMAIL PROTECTED]> wrote:

> [-- text/plain, encoding 7bit, charset: ISO-8859-1, 55 lines --]

> 

> We have MySQL setup as a MYSQL_MULTI configuration so that each database 

> has their own instance of MySQL.

> 

> We need another database configured and so added an entry in the my.cnf. 

> 

> We've tried to start the new database, but keep getting this error.  We 

> know that for each database there needs to be a mysql sub-folder to 

> store the tables that store grants and such, but how to we get those 

> created for the new instance.  We've been all over the MySQL.com docs 

> and can't find the solution.  Anyone have any ideas?

> 

> Thanks

> 

> 

> 050423 12:07:20  mysqld started

> 050423 12:07:20 [Warning] Asked for 196608 thread stack, but got 126976

> 050423 12:07:20  InnoDB: Database was not shut down normally!

> InnoDB: Starting crash recovery.

> InnoDB: Reading tablespace information from the .ibd files...

> InnoDB: Restoring possible half-written data pages from the doublewrite

> InnoDB: buffer...

> 050423 12:07:20  InnoDB: Starting log scan based on checkpoint at

> InnoDB: log sequence number 0 43634.

> InnoDB: Doing recovery: scanned up to log sequence number 0 43634

> 050423 12:07:20  InnoDB: Flushing modified pages from the buffer pool...

> 050423 12:07:20  InnoDB: Started; log sequence number 0 43634

> 050423 12:07:20 [ERROR] Fatal error: Can't open privilege tables: Table 

> 'mysql.host' doesn't exist

> 050423 12:07:20  mysqld ended

> 

> 

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




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

Re: mysql_fix_privilege_tables error

[Gleb Paharenko]

Hello.



Make a bug or feature report at http://bugs.mysql.com.





Josh Trutwin <[EMAIL PROTECTED]> wrote:

> Would it be possible to add:

> 

> ENGINE=MyISAM 

> 

> To all the CREATE TABLE statements in the mysql_fix_privilege_tables

> script?  The server (tested with 5.0.3 and 5.0.4) crashes when

> creating/altering these tables if the following is in /etc/my.cnf: 

> 

> default-table-type=innodb

> 

> I had to drop all the new tables and added ENGINE=MyISAM to the CREATE

> TABLE statements, re-ran the script and it worked fine.

> 

> Here is an example crash report:

> 

> 050422  9:19:43InnoDB: Assertion failure in thread 245771 in file

> ../include/data0type.ic line 466

> InnoDB: Failing assertion: type->len % type->mbmaxlen == 0

> InnoDB: We intentionally generate a memory trap.

> InnoDB: Submit a detailed bug report to http://bugs.mysql.com.

> InnoDB: If you get repeated assertion failures or crashes, even

> InnoDB: immediately after the mysqld startup, there may be

> InnoDB: corruption in the InnoDB tablespace. Please refer to

> InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html

> InnoDB: about forcing recovery.

> mysqld got signal 11;

> This could be because you hit a bug. It is also possible that this

> binary

> or one of the libraries it was linked against is corrupt, improperly

> built,

> or misconfigured. This error can also be caused by malfunctioning

> hardware.

> We will try our best to scrape up some info that will hopefully help

> diagnose

> the problem, but since we have already crashed, something is

> definitely wrong

> and this may fail.

> 

> key_buffer_size=8388600

> read_buffer_size=131072

> max_used_connections=1

> max_connections=100

> threads_connected=1

> It is possible that mysqld could use up to 

> key_buffer_size + (read_buffer_size +

> sort_buffer_size)*max_connections = 225791 K

> bytes of memory

> Hope that's ok; if not, decrease some variables in the equation.

> 

> thd=0x8ab7a70

> Attempting backtrace. You can use the following information to find

> out

> where mysqld died. If you see no messages after this, something went

> terribly wrong...

> Cannot determine thread, fp=0xbe5f3938, backtrace may not be correct.

> Stack range sanity check OK, backtrace follows:

> 0x8180bef

> 0xb7e48c85

> 0x8295abc

> 0x829492a

> 0x829286c

> 0x82af069

> 0x82ae30c

> 0x82c5f99

> 0x823754f

> 0x8233119

> 0x8227b3f

> 0x8210552

> 0x823cb76

> 0x823f164

> 0x8196522

> 0x819d604

> 0x8194278

> 0x8193d85

> 0x8193192

> 0xb7e4354e

> 0xb7d71b8a

> New value of fp=(nil) failed sanity check, terminating stack trace!

> Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html

> and follow instructions on how to resolve the stack trace. Resolved

> stack trace is much more helpful in diagnosing the problem, so please

> do 

> resolve it

> Trying to get some variables.

> Some pointers may be invalid and cause the dump to abort...

> thd->query at 0x8ae2ae0 = ALTER TABLE time_zone

>  MODIFY Use_leap_seconds enum('Y','N') COLLATE utf8_general_ci

> DEFAULT 'N' NOT NULL

> thd->thread_id=6

> The manual page at http://www.mysql.com/doc/en/Crashing.html contains

> information that should help you find out what is causing the crash.

> 

> Number of processes running now: 0

> 

> 

> Josh

> 

> 

> 

> 



-- 
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: Using InnoDB on 2 OSes

[Gleb Paharenko]

Hello.



I don't know if it is a good idea, but at least check that you have the

same version of MySQL on Linux.  On Windows, lower_case_table_names is

set to 1 by default. See:



  http://dev.mysql.com/doc/mysql/en/moving.html



Marcin Lewandowski <[EMAIL PROTECTED]> wrote:

> Hi,

> 

> I've got Win2k on one FAT32 partition, and I use MySQL 4.0.23 there. 

> I've got gentoo linux on second partition, and I want to use the same 

> databases on linux's MySQL. I've configured linux's mysql to use 

> databases from FAT32 partition, but InnoDB tables don't work. phpmyadmin 

> show them as "in usage". I suppose, that could be caused by fact, that 

> when I'm shutting down windows, mysql is just killed, without proper 

> saving innodb logfiles. Or maybe there are other reason? How to 

> configure that?

> 



-- 
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: Ordering rows whit a select from where in ( exp )

[mathias fatene]

Do that ,


 SELECT field_name FROM meta WHERE id ='13'
Union
 SELECT field_name FROM meta WHERE id ='11'
Union
 SELECT field_name FROM meta WHERE id ='7'
Union
 SELECT field_name FROM meta WHERE id ='8'
Union
 SELECT field_name FROM meta WHERE id ='9'
Union
 SELECT field_name FROM meta WHERE id ='10'
Union
 SELECT field_name FROM meta WHERE id ='12'

Mathias

Best Regards

Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-Original Message-
From: Adrian [mailto:[EMAIL PROTECTED] 
Sent: lundi 25 avril 2005 10:06
To: mysql@lists.mysql.com
Subject: Ordering rows whit a select from where in ( exp ) 


 

Hi everyone, 

 

 

 Here is my issue:   

 

 I have this Query  : SELECT field_name FROM meta WHERE id
IN
('13','11','7','8','9','10','12')

 

 I want the rows to be display in the same order as the in list
of ids.Any ideas? Should I use order by? Whit witch option ? 

 

  

  Thanks for your help.

 

  Adrian.  

 



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

RE: libCstd.so.1 not found while running mysql_install_db

[mathias fatene]

Read 7xx

Best Regards

Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-Original Message-
From: mathias fatene [mailto:[EMAIL PROTECTED] 
Sent: lundi 25 avril 2005 10:18
To: 'Anirban Karmakar'; 'mysql@lists.mysql.com'
Subject: RE: libCstd.so.1 not found while running mysql_install_db


Do you have  /cnem/server/bin/mysqld file ? Is it exec (6xx)?

Best Regards

Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-Original Message-
From: Anirban Karmakar [mailto:[EMAIL PROTECTED] 
Sent: lundi 25 avril 2005 09:12
To: mysql@lists.mysql.com
Subject: libCstd.so.1 not found while running mysql_install_db


Hi,

I installed mysql-standard-4.0.24-sun-solaris2.8-sparc on a Solaris 5.8
macine. However while i'm running the mysql_install_db script i'm
getting the error

ld.so.1: ./bin/mysqld: fatal: libCstd.so.1: open failed: No such file or
directory Killed Installation of grant tables failed!

I've my .my.cnf file as

#mysql config file

[client]
port  = 3306
socket= /tmp/mysql.sock

[mysqld]
port  = 3306
socket= /tmp/mysql.sock
user  = mysqlc
datadir   = /cnem/data

[mysql_server]
basedir   = /cnem/server

[mysql.server]
basedir   = /cnem/server

[mysqld_safe]
err-log   = /cnem/server/mysqld.log


The same configuration worked on another Sun machine. Please suggest me
how to fix it.

Thanks
Anirban

-- 
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: libCstd.so.1 not found while running mysql_install_db

[mathias fatene]

Do you have  /cnem/server/bin/mysqld file ? Is it exec (6xx)?

Best Regards

Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-Original Message-
From: Anirban Karmakar [mailto:[EMAIL PROTECTED] 
Sent: lundi 25 avril 2005 09:12
To: mysql@lists.mysql.com
Subject: libCstd.so.1 not found while running mysql_install_db


Hi,

I installed mysql-standard-4.0.24-sun-solaris2.8-sparc on a Solaris 5.8
macine. However while i'm running the mysql_install_db script i'm
getting the error

ld.so.1: ./bin/mysqld: fatal: libCstd.so.1: open failed: No such file or
directory Killed Installation of grant tables failed!

I've my .my.cnf file as

#mysql config file

[client]
port  = 3306
socket= /tmp/mysql.sock

[mysqld]
port  = 3306
socket= /tmp/mysql.sock
user  = mysqlc
datadir   = /cnem/data

[mysql_server]
basedir   = /cnem/server

[mysql.server]
basedir   = /cnem/server

[mysqld_safe]
err-log   = /cnem/server/mysqld.log


The same configuration worked on another Sun machine. Please suggest me
how to fix it.

Thanks
Anirban

-- 
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: Ordering rows whit a select from where in ( exp )

[=?ISO-8859-1?Q?Johan_H=F6=F6k?=]

Hi Adrian,
you can do
SELECT field_name FROM meta WHERE id IN
('13','11','7','8','9','10','12')
ORDER BY FIELD(id,'13','11','7','8','9','10','12')
/Johan
Adrian wrote:
 

Hi everyone, 

 

 

 Here is my issue:   

 

 I have this Query  : SELECT field_name FROM meta WHERE id IN
('13','11','7','8','9','10','12')
 

 I want the rows to be display in the same order as the in list of
ids.Any ideas? Should I use order by? Whit witch option ? 

 

  

  Thanks for your help.
 

  Adrian.  

 



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

Ordering rows whit a select from where in ( exp )

[Adrian]

 

Hi everyone, 

 

 

 Here is my issue:   

 

 I have this Query  : SELECT field_name FROM meta WHERE id IN
('13','11','7','8','9','10','12')

 

 I want the rows to be display in the same order as the in list of
ids.Any ideas? Should I use order by? Whit witch option ? 

 

  

  Thanks for your help.

 

  Adrian.  

 

RE: zip code search within x miles

[gunmuse]

Http://www.gunmuse.com

Ok I use a storelocator.

First if you have 8000 + records it becomes an issue.  BUT  Lat and long is
in minutes and minutes can be used to estimate miles.  By Breaking down the
lat and long,  Breaking down the Zip to a two digit prefix 88254 becomes 88
for indexing (Because the post offices goes in order folks with some
exceptions) Then with a wide lasso you can rope your results to do your math
check with.  Break your lat and long fields up in hours minutes and seconds
and filtering down becomes very easy to do.

Learning to read a map before determining the key and distance calculation
would help better understand this problem.

Thanks
Donny Lairson
President
29 GunMuse Lane
P.O. box 166
Lakewood NM 88254
http://www.gunmuse.com
469 228 2183


-Original Message-
From: Richard Lynch [mailto:[EMAIL PROTECTED]
Sent: Monday, April 25, 2005 12:05 AM
To: Hank
Cc: MySql
Subject: Re: zip code search within x miles


On Tue, April 19, 2005 8:55 am, Hank said:
> Talk about over complicating things... here's the above query simplifed.
>
> I can not figure out why they were self joining the table three times:
>
> SELECT b.zip_code, b.state,
>(3956 * (2 * ASIN(SQRT(
>POWER(SIN(((a.lat-b.lat)*0.017453293)/2),2) +
>COS(a.lat*0.017453293) *
>COS(b.lat*0.017453293) *
>POWER(SIN(((a.lng-b.lng)*0.017453293)/2),2) AS distance
> FROM zips a, zips b
> WHERE
>a.zip_code = '90210'
> GROUP BY distance
> having distance <= 5;

You'd have to time it, and *MAYBE* with enough indices this will all work
out, but you'd probably be better off doing two queries.

One to look up the long/lat for 90210, and another on just zips to
calculate the distance.

Benchmark on your own hardware and see for yourself.  I could be 100% wrong.

--
Like Music?
http://l-i-e.com/artists.htm


--
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: joining six tables by mutual column

[mathias fatene]

Hi,
Select * from table1 T1, table2 T2, table3 T3, table4 T4, table5 T5,
table6 T6 
Where T1.col=T2.col
 and T2.col=T3.col
 and T3.col=T4.col
 and T4.col=T5.col
 and T5.col=T6.col
 and T1.col=T6.col
[and col='val']

Doesn't this work ? Have you an example ?

Best Regards

Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-Original Message-
From: Schalk Neethling [mailto:[EMAIL PROTECTED] 
Sent: lundi 25 avril 2005 00:52
To: mysql@lists.mysql.com
Subject: joining six tables by mutual column


Greetings everyone.

Hope someone can give me some pointers here. I have six tables in the 
database and I need to JOIN them on a row that appears in all of the 
tables. How do I do this? I have so far done the normal 'cross-join' 
saying SELECT * FROM table1, table2, table3, table4, table5, table6 
WHERE something = something;

I have also added STRAIGHT_JOIN to force the order but, how do I JOIN 
six tables to/by one column? I have done some google searches as well as

looked at MySQL 2nd edition by Paul DuBois, sorry if I missed something 
here Paul, and so far I have not found an answer. Any help or pointers 
will be appreciated. Thank you.

-- 
Kind Regards
Schalk Neethling
Web Developer.Designer.Programmer.President
Volume4.Business.Solution.Developers
emotionalize.conceptualize.visualize.realize
Landlines
Tel: +27125468436
Fax: +27125468436
Web
email:[EMAIL PROTECTED]
Global: www.volume4.com
Messenger
Yahoo!: v_olume4
AOL: v0lume4
MSN: [EMAIL PROTECTED]

We support OpenSource
Get Firefox!- The browser reloaded -
http://www.mozilla.org/products/firefox/
 
This message contains information that is considered to be sensitive or
confidential and may not be forwarded or disclosed to any other party
without the permission of the sender. If you received this message in
error, please notify me immediately so that I can correct and delete the
original email. Thank you.



-- 
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]

libCstd.so.1 not found while running mysql_install_db

[Anirban Karmakar]

Hi,

I installed mysql-standard-4.0.24-sun-solaris2.8-sparc on a Solaris 5.8 macine.
However while i'm running the mysql_install_db script i'm getting the error

ld.so.1: ./bin/mysqld: fatal: libCstd.so.1: open failed: No such file
or directory
Killed
Installation of grant tables failed!

I've my .my.cnf file as

#mysql config file

[client]
port  = 3306
socket= /tmp/mysql.sock

[mysqld]
port  = 3306
socket= /tmp/mysql.sock
user  = mysqlc
datadir   = /cnem/data

[mysql_server]
basedir   = /cnem/server

[mysql.server]
basedir   = /cnem/server

[mysqld_safe]
err-log   = /cnem/server/mysqld.log


The same configuration worked on another Sun machine. Please
suggest me how to fix it.

Thanks
Anirban

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

RE: Replication - is there a "server lag"?

[mathias fatene]

Loo at :
mysql> show master status;
+---+--+--+--+
| File  | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---+--+--+--+
| binlog.03 |   79 |  |  |
+---+--+--+--+
1 row in set (0.02 sec)

And show slave status;

When reading from slave, data can be not synchronized. If you configured
log-bin, you can use mysqlbinlog to read it.



Best Regards

Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-Original Message-
From: Fagyal Csongor [mailto:[EMAIL PROTECTED] 
Sent: lundi 25 avril 2005 00:43
To: Mysql (E-mail)
Subject: Replication - is there a "server lag"?


Hi,

I am new to replication so excuse me if my question is stupid.

The manual recommends that a nice scenario to take advantage of 
replication in MySQL is to send all updating queries to the master 
server, and reading from the slave. I would like to use this setup (as 
usual, I have many more selects than inserts/updates) but I am a little 
concerned what happens if the slave is behind the master in updating its
DB.

Say I do like this:
1. update something set `a`=1 where c=d (using the master server) 2.
update something set `a`=2 where c=d (using the master server) and then
immediately 3. select `a` from something where c=d (using the slave)

What if #3 fetches the value of `a` from the slave before `a`=2 takes 
place? Is it possible that I get `a`==1? Or does replication take care 
of that?

Other than that: does anybody here have a Nagios script that checks if 
replication is running O.K.? :-)

Thanks,
- Csongor

-- 
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]