search in all tables in the data base ..

[Gregory Machin]

Hi all.
Please could you advise.
I would like to know if one could do a recursive search through a data
base and get a result of wich column and field is holding the string ?

Many Thanks .
-- 
Gregory Machin
[EMAIL PROTECTED]
[EMAIL PROTECTED]
www.linuxpro.co.za
Web Hosting Solutions
Scalable Linux Solutions 
www.iberry.info (support and admin)
www.goeducation (support and admin)
+27 72 524 8096

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

Total Sum of Monthly Counts for Older MySQL 4.0.22 (Please Help)

[Henry Chang]

Hello MySQL users,

Currently, I use MySQL 4.0.22 and I can do a straightforward count of
employees hired for each month.

select employee_hire_date, count(employee_id)
from table_employee
where employee_hire_date between "2005-01-01" and "2005-4-30"
group by month(employee_hire_date)


Date   | Count |

2005-01-01 | 123   |
2005-02-01 | 50|
2005-03-01 | 76|
2005-04-01 | 89|


However, I would like to do a grand total of the counts and the averages
that would like the below.  


Date   | Count | Sum | Avg  |
-
2005-01-01 | 123   | 338 | 84.5 |
2005-02-01 | 50| 338 | 84.5 |
2005-03-01 | 76| 338 | 84.5 |
2005-04-01 | 89| 338 | 84.5 |


Since my MySQL version is 4.0.22, I am not able to use subquery and I
prefer not to use tmp tables.  What would be the right query to solve
for the grand total sum and average??  Any help would be greatly
appreciated!!!

Thanks in Advance.

Henry



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

RE: Continued x86_64 install problems

[Richard Dale]

Hi Anne,

> I managed to install
> MySQL-client-4.0.25-0.ia64.rpm
> MySQL-server-4.0.25-0.ia64.rpm
> MySQL-devel-4.0.25-0.ia64.rpm
> ...

x86_64 != ia64

You have installed the wrong architecture executables.  Try downloading the
x86_64 versions instead.

Also have a read of our Opteron (x86_64) HOWTO:
http://hashmysql.org/index.php?title=Opteron_HOWTO


Best regards,
Richard Dale.
Norgate Investor Services
- Premium quality Stock, Futures and Foreign Exchange Data for
  markets in Australia, Asia, Canada, Europe, UK & USA -
www.premiumdata.net 



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

Re: Insert with prefix

[SGreen]

Eric Bergen <[EMAIL PROTECTED]> wrote on 07/27/2005 05:03:40 PM:

> Try this:
> concat('UP', lpad(category_id, 6, '0'));
> 
> Scott Purcell wrote:
> 
> >Hello,
> >
> >I have the following dilemma, that I do not know how to handle.
> >
> >I have the need for a table where I create a identifier. The 
> identifier consists of a two character string, eg: "UP" + the next 
> "AUTO_INCREMENT" 
> >Here is what I have.
> >
> >   category_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
> >
> >Table should look like this:
> >UP01
> >UP02
> >UP03
> >
> >So the problem is, how can I grab the auto_increment number, then 
> append the prefix while doing an insert?
> >
> >Can anyone give me some advice on this problem?
> >
> >Thanks,
> >Scott
> >
> > 
> >

Create two fields, one to hold the UP characters, the other is a regular 
auto_increment field. Then combine the two in your SELECT fields. Use the 
formula that Eric provided and you will get the OUTPUT you want. Otherwise 
you will not be able to use an auto_increment value. Rather, you will need 
to create your own values as you generate your records (much slower)

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Hour counts

[Eric Bergen]

This does make his code fall under the limitations of unix timestamps. 
In 30 years or so when we are all retired millionaires ;) some poor 
intern is going to have to figure out why the hour diff calculation is 
failing.



[EMAIL PROTECTED] wrote:


Gyurasits Zoltán <[EMAIL PROTECTED]> wrote on 07/27/2005 12:57:42 PM:

 


TIMESTAMP() is available as of MySQL 4.1.1.

I can't use this version because replication working :(

I use version 4.0.22

- Original Message - 
From: "Martijn Tonies" <[EMAIL PROTECTED]>

To: "Gyurasits Zoltán" <[EMAIL PROTECTED]>; 
Sent: Wednesday, July 27, 2005 6:31 PM
Subject: Re: Hour counts


   


Hi,

Check out the  TIMEDIFF  function.

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS 
 


SQL
 


Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

I would like to calculate the hour counts from 2 'datetime'.
Example:   2005-07-27 18:00 and 2005-07-27 19:30  => 1,5 hour

I try this  but not good!

R1 : munkaido_end-munkaido_start  /simple substract/
R2 : ROUND(ROUND((end-start)/1)+
(((end-start)/1000-(ROUND((end-start)/1)*10))/6),1)  /good if is 
 

in 
 


one
day/
R3 : ROUND((end-start)/1)-76  /-76 because from 14. to 15. I don't
understand/


start   end  R1R2 R3
07-14 15:00 07-14 17:30 23000   2.5 -74
07-14 23:00 07-15 01:30 783000 78.5 2
07-14 15:00 07-15 02:30 873000 87.5 11
07-14 15:00 07-14 16:00 1   1 -75

Please help me...(exist a function for this situation?)


Tnx!


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

 



Then your next option is to convert your datetime values to "seconds since 
epoch" by using UNIX_TIMESTAMP() (see the same link everyone else has 
already given you for details).


mysql>SELECT (unix_timestamp('2005-07-27 19:30:00') - 
unix_timestamp('2005-07-27 18:00:00'))/3600;

+--+
| (unix_timestamp('2005-07-27 19:30:00') - unix_timestamp('2005-07-27 
18:00:00'))/3600 |

+--+
|1.50 |
+--+
1 row in set (0.00 sec)


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: Insert with prefix

[Eric Bergen]

Try this:
concat('UP', lpad(category_id, 6, '0'));

Scott Purcell wrote:


Hello,

I have the following dilemma, that I do not know how to handle.

I have the need for a table where I create a identifier. The identifier consists of a two character string, eg: "UP" + the next "AUTO_INCREMENT" 
Here is what I have.


  category_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

Table should look like this:
UP01
UP02
UP03

So the problem is, how can I grab the auto_increment number, then append the 
prefix while doing an insert?

Can anyone give me some advice on this problem?

Thanks,
Scott

 




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

Re: Doubt about query optimization

[Eric Bergen]

Can you send us the actual show indexes from table and explain output 
that isn't shortend?


Ronan Lucio wrote:


Hello,

I´m using MySQL-4.1.7, and I have a query that seems,
even using where clause, an specific table isn´t being optimized
properly:

I have four tables:

real_state: cod, name, city, ag_cod, sell
agency: cod, name, ag_status
front: cod, rs_cod, ag_cod
photo: cod, rs_cod

These table have the following indexes:

real_state:
   cod (pri)
   city
   ag_cod
agency:
   cod
   name
front:
   cod
   rs_cod
   ag_cod
photo
   cod
   rs_cod

When I EXPLAIN the query:

EXPLAIN
SELECT front.rs_cod, photo.cod, real_state.descr
FROM real_state, agency, front, photo_foto
WHERE real_state.city = 1
AND real_state.sell = 1
AND front.rs_cod = real_state.cod
AND photo.rs_cod = front.rs_cod
AND agency.cod = real_state.ag_cod
AND ag_status = 'A'

It shows me (in a short):

tablekeyrows
======  
frontrs_cod   2085
real_statecod1
agencycod1
photo  rs_cod   1

But the trouble is: If I execute:

SELECT COUNT(*)
FROM real_state, front
WHERE real_state.city = 1
AND real_state.cod = front.rs_cod

Returns: 271

So, Why the index front.rs_cod isn´t being used?

Any help would be appreciated,

thank you,
Ronan





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

Insert with prefix

[Scott Purcell]

Hello,

I have the following dilemma, that I do not know how to handle.

I have the need for a table where I create a identifier. The identifier 
consists of a two character string, eg: "UP" + the next "AUTO_INCREMENT" 
Here is what I have.

   category_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

Table should look like this:
UP01
UP02
UP03

So the problem is, how can I grab the auto_increment number, then append the 
prefix while doing an insert?

Can anyone give me some advice on this problem?

Thanks,
Scott

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

Re: Mysqld chewing up cpu in the background.

[mos]

At 09:16 AM 7/27/2005, you wrote:

Thanks for the suggestion. That did help me find out. It wasn't idle.
The problem is that its taking 10 seconds to insert a single record,
composed mainly of one longtext field.  The index is killing me.  Does
anyone know of how FT indexing can be changed to do inserts quicker or
something?



You could try going back to the default word length of 4 or more. I suspect 
the common everyday words like "the" "and" are producing a lot of duplicate 
index entries.


Mike


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

Re: Hour counts

[Gyurasits Zoltán]

Working!!!  Thank you!!!

- Original Message - 
From: <[EMAIL PROTECTED]>

To: "Gyurasits Zoltán" <[EMAIL PROTECTED]>
Cc: 
Sent: Wednesday, July 27, 2005 7:09 PM
Subject: Re: Hour counts


Gyurasits Zoltán <[EMAIL PROTECTED]> wrote on 07/27/2005 12:57:42 PM:



 TIMESTAMP() is available as of MySQL 4.1.1.

I can't use this version because replication working :(

I use version 4.0.22

- Original Message - 
From: "Martijn Tonies" <[EMAIL PROTECTED]>

To: "Gyurasits Zoltán" <[EMAIL PROTECTED]>; 
Sent: Wednesday, July 27, 2005 6:31 PM
Subject: Re: Hour counts


> Hi,
>
> Check out the  TIMEDIFF  function.
>
> With regards,
>
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS

SQL

> Server
> Upscene Productions
> http://www.upscene.com
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
> I would like to calculate the hour counts from 2 'datetime'.
> Example:   2005-07-27 18:00 and 2005-07-27 19:30  => 1,5 hour
>
> I try this  but not good!
>
> R1 : munkaido_end-munkaido_start  /simple substract/
> R2 : ROUND(ROUND((end-start)/1)+
> (((end-start)/1000-(ROUND((end-start)/1)*10))/6),1)  /good if is

in

> one
> day/
> R3 : ROUND((end-start)/1)-76  /-76 because from 14. to 15. I don't
> understand/
>
>
> start   end  R1R2 R3
> 07-14 15:00 07-14 17:30 23000   2.5 -74
> 07-14 23:00 07-15 01:30 783000 78.5 2
> 07-14 15:00 07-15 02:30 873000 87.5 11
> 07-14 15:00 07-14 16:00 1   1 -75
>
> Please help me...(exist a function for this situation?)
>
>
> Tnx!
>
>
> -- 
> MySQL General Mailing List

> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>



Then your next option is to convert your datetime values to "seconds since
epoch" by using UNIX_TIMESTAMP() (see the same link everyone else has
already given you for details).

mysql>SELECT (unix_timestamp('2005-07-27 19:30:00') -
unix_timestamp('2005-07-27 18:00:00'))/3600;
+--+
| (unix_timestamp('2005-07-27 19:30:00') - unix_timestamp('2005-07-27
18:00:00'))/3600 |
+--+
|1.50 |
+--+
1 row in set (0.00 sec)


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: Hour counts

[SGreen]

Gyurasits Zoltán <[EMAIL PROTECTED]> wrote on 07/27/2005 12:57:42 PM:

> 
>  TIMESTAMP() is available as of MySQL 4.1.1.
> 
> I can't use this version because replication working :(
> 
> I use version 4.0.22
> 
> - Original Message - 
> From: "Martijn Tonies" <[EMAIL PROTECTED]>
> To: "Gyurasits Zoltán" <[EMAIL PROTECTED]>; 
> Sent: Wednesday, July 27, 2005 6:31 PM
> Subject: Re: Hour counts
> 
> 
> > Hi,
> >
> > Check out the  TIMEDIFF  function.
> >
> > With regards,
> >
> > Martijn Tonies
> > Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS 
SQL
> > Server
> > Upscene Productions
> > http://www.upscene.com
> > Database development questions? Check the forum!
> > http://www.databasedevelopmentforum.com
> >
> > I would like to calculate the hour counts from 2 'datetime'.
> > Example:   2005-07-27 18:00 and 2005-07-27 19:30  => 1,5 hour
> >
> > I try this  but not good!
> >
> > R1 : munkaido_end-munkaido_start  /simple substract/
> > R2 : ROUND(ROUND((end-start)/1)+
> > (((end-start)/1000-(ROUND((end-start)/1)*10))/6),1)  /good if is 
in 
> > one
> > day/
> > R3 : ROUND((end-start)/1)-76  /-76 because from 14. to 15. I don't
> > understand/
> >
> >
> > start   end  R1R2 R3
> > 07-14 15:00 07-14 17:30 23000   2.5 -74
> > 07-14 23:00 07-15 01:30 783000 78.5 2
> > 07-14 15:00 07-15 02:30 873000 87.5 11
> > 07-14 15:00 07-14 16:00 1   1 -75
> >
> > Please help me...(exist a function for this situation?)
> >
> >
> > Tnx!
> >
> >
> > -- 
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> > 
> 

Then your next option is to convert your datetime values to "seconds since 
epoch" by using UNIX_TIMESTAMP() (see the same link everyone else has 
already given you for details).

mysql>SELECT (unix_timestamp('2005-07-27 19:30:00') - 
unix_timestamp('2005-07-27 18:00:00'))/3600;
+--+
| (unix_timestamp('2005-07-27 19:30:00') - unix_timestamp('2005-07-27 
18:00:00'))/3600 |
+--+
|1.50 |
+--+
1 row in set (0.00 sec)


Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Hour counts

[Gyurasits Zoltán]

TIMESTAMP() is available as of MySQL 4.1.1.

I can't use this version because replication working :(

I use version 4.0.22

- Original Message - 
From: "Martijn Tonies" <[EMAIL PROTECTED]>

To: "Gyurasits Zoltán" <[EMAIL PROTECTED]>; 
Sent: Wednesday, July 27, 2005 6:31 PM
Subject: Re: Hour counts



Hi,

Check out the  TIMEDIFF  function.

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

I would like to calculate the hour counts from 2 'datetime'.
Example:   2005-07-27 18:00 and 2005-07-27 19:30  => 1,5 hour

I try this  but not good!

R1 : munkaido_end-munkaido_start  /simple substract/
R2 : ROUND(ROUND((end-start)/1)+
(((end-start)/1000-(ROUND((end-start)/1)*10))/6),1)  /good if is in 
one

day/
R3 : ROUND((end-start)/1)-76  /-76 because from 14. to 15. I don't
understand/


start   end  R1R2  R3
07-14 15:00 07-14 17:30 23000   2.5 -74
07-14 23:00 07-15 01:30 783000 78.5 2
07-14 15:00 07-15 02:30 873000 87.5 11
07-14 15:00 07-14 16:00 1   1 -75

Please help me...(exist a function for this situation?)


Tnx!


--
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: Hour counts

[mfatene]

Hi,
You can use Timediff :

mysql> SELECT TIMEDIFF('2005-07-27 18:00', '2005-07-27 19:30');
+--+
| TIMEDIFF('2005-07-27 18:00', '2005-07-27 19:30') |
+--+
| -01:30:00|
+--+
1 row in set (0.00 sec)

mysql>
mysql>
mysql>
mysql>
mysql> SELECT TIMEDIFF('2005-07-27 19:30', '2005-07-27 18:00');
+--+
| TIMEDIFF('2005-07-27 19:30', '2005-07-27 18:00') |
+--+
| 01:30:00 |
+--+
1 row in set (0.00 sec)

more in http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

Mathias

> Selon Gyurasits Zoltán <[EMAIL PROTECTED]>:
>
> > Hello All!
> >
> >
> > I would like to calculate the hour counts from 2 'datetime'.
> > Example:   2005-07-27 18:00 and 2005-07-27 19:30  => 1,5 hour
> >
> > I try this  but not good!
> >
> > R1 : munkaido_end-munkaido_start  /simple substract/
> > R2 : ROUND(ROUND((end-start)/1)+
> > (((end-start)/1000-(ROUND((end-start)/1)*10))/6),1)  /good if is in one
> > day/
> > R3 : ROUND((end-start)/1)-76  /-76 because from 14. to 15. I don't
> > understand/
> >
> >
> > start   end  R1R2  R3
> > 07-14 15:00 07-14 17:30 23000   2.5 -74
> > 07-14 23:00 07-15 01:30 783000 78.5 2
> > 07-14 15:00 07-15 02:30 873000 87.5 11
> > 07-14 15:00 07-14 16:00 1   1 -75
> >
> > Please help me...(exist a function for this situation?)
> >
> >
> > Tnx!
> >
>
>
>



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

Re: Hour counts

[mfatene]

Hi,
You can use Timediff :

mysql> SELECT TIMEDIFF('2005-07-27 18:00', '2005-07-27 19:30');
+--+
| TIMEDIFF('2005-07-27 18:00', '2005-07-27 19:30') |
+--+
| -01:30:00|
+--+
1 row in set (0.00 sec)

mysql>
mysql>
mysql>
mysql>
mysql> SELECT TIMEDIFF('2005-07-27 19:30', '2005-07-27 18:00');
+--+
| TIMEDIFF('2005-07-27 19:30', '2005-07-27 18:00') |
+--+
| 01:30:00 |
+--+
1 row in set (0.00 sec)

more in http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

Mathias

Selon Gyurasits Zoltán <[EMAIL PROTECTED]>:

> Hello All!
>
>
> I would like to calculate the hour counts from 2 'datetime'.
> Example:   2005-07-27 18:00 and 2005-07-27 19:30  => 1,5 hour
>
> I try this  but not good!
>
> R1 : munkaido_end-munkaido_start  /simple substract/
> R2 : ROUND(ROUND((end-start)/1)+
> (((end-start)/1000-(ROUND((end-start)/1)*10))/6),1)  /good if is in one
> day/
> R3 : ROUND((end-start)/1)-76  /-76 because from 14. to 15. I don't
> understand/
>
>
> start   end  R1R2  R3
> 07-14 15:00 07-14 17:30 23000   2.5 -74
> 07-14 23:00 07-15 01:30 783000 78.5 2
> 07-14 15:00 07-15 02:30 873000 87.5 11
> 07-14 15:00 07-14 16:00 1   1 -75
>
> Please help me...(exist a function for this situation?)
>
>
> Tnx!
>



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

cygwin build of client

[Jason Pyeron]

I have make going right now, but does anyone have any suggestions?

google was not much help for 4.1.x


--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
-   -
- Jason Pyeron  PD Inc. http://www.pdinc.us -
- Partner & Sr. Manager 7 West 24th Street #100 -
- +1 (443) 921-0381 Baltimore, Maryland 21218   -
-   -
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

This message is for the designated recipient only and may contain 
privileged, proprietary, or otherwise private information. If you 
have received it in error, purge the message from your system and 
notify the sender immediately.  Any other use of the email by you 
is prohibited.


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

Re: Hour counts

[Jason Pyeron]

mysql> select *,timediff(end,start) from foo;
+-+-++-+
| start   | end | id | timediff(end,start) |
+-+-++-+
| 2005-07-14 15:00:00 | 2005-07-14 17:30:00 |  1 | 02:30:00|
| 2005-07-14 23:00:00 | 2005-07-15 01:30:00 |  2 | 02:30:00|
| 2005-07-14 15:00:00 | 2005-07-15 02:30:00 |  3 | 11:30:00|
| 2005-07-14 15:00:00 | 2005-07-14 16:00:00 |  4 | 01:00:00|
+-+-++-+

On Wed, 27 Jul 2005, [iso-8859-2] Gyurasits Zolt?n wrote:


Hello All!


I would like to calculate the hour counts from 2 'datetime'.
Example:   2005-07-27 18:00 and 2005-07-27 19:30  => 1,5 hour

I try this  but not good!

R1 : munkaido_end-munkaido_start  /simple substract/
R2 : ROUND(ROUND((end-start)/1)+ 
(((end-start)/1000-(ROUND((end-start)/1)*10))/6),1)  /good if is in one day/
R3 : ROUND((end-start)/1)-76  /-76 because from 14. to 15. I don't 
understand/


start   end  R1R2  R3
07-14 15:00 07-14 17:30 23000   2.5 -74
07-14 23:00 07-15 01:30 783000 78.5 2
07-14 15:00 07-15 02:30 873000 87.5 11
07-14 15:00 07-14 16:00 1   1 -75

Please help me...(exist a function for this situation?)


Tnx!



--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
-   -
- Jason Pyeron  PD Inc. http://www.pdinc.us -
- Partner & Sr. Manager 7 West 24th Street #100 -
- +1 (443) 921-0381 Baltimore, Maryland 21218   -
-   -
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

This message is for the designated recipient only and may contain 
privileged, proprietary, or otherwise private information. If you 
have received it in error, purge the message from your system and 
notify the sender immediately.  Any other use of the email by you 
is prohibited.

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

RE: Hour counts

[Terry Spencer]

There are a few options, for more information see
http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html


"TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2) 

Returns the integer difference between the date or datetime expressions
datetime_expr1 and datetime_expr2. The unit for the result is given by the
interval argument. The legal values for interval are the same as those
listed in the description of the TIMESTAMPADD() function. 

mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
-> 3
mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
-> -1

TIMESTAMPDIFF() is available as of MySQL 5.0.0. "

It appears you require the answer in fraction hours. Set the interval to
seconds and divide the result by 3600 (60*60 = seconds in an hour)

---

" UNIX_TIMESTAMP() , UNIX_TIMESTAMP(date) 

If called with no argument, returns a Unix timestamp (seconds since
'1970-01-01 00:00:00' GMT) as an unsigned integer. If UNIX_TIMESTAMP() is
called with a date argument, it returns the value of the argument as seconds
since '1970-01-01 00:00:00' GMT. date may be a DATE string, a DATETIME
string, a TIMESTAMP, or a number in the format YYMMDD or MMDD in local
time. 

mysql> SELECT UNIX_TIMESTAMP();
-> 882226357
mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
-> 875996580"

Convert both dates to seconds using UNIX_TIMESTAMP() and subtract one form
the other. Divide the result by 3600 (60*60 = seconds in an hour) to obtain
the fractional hours.

Terry


-Original Message-
From: Gyurasits Zoltán [mailto:[EMAIL PROTECTED] 
Sent: 27 July 2005 17:12
To: mysql@lists.mysql.com
Subject: Hour counts

Hello All!


I would like to calculate the hour counts from 2 'datetime'.
Example:   2005-07-27 18:00 and 2005-07-27 19:30  => 1,5 hour

I try this  but not good!

R1 : munkaido_end-munkaido_start  /simple substract/
R2 : ROUND(ROUND((end-start)/1)+
(((end-start)/1000-(ROUND((end-start)/1)*10))/6),1)  /good if is in one
day/
R3 : ROUND((end-start)/1)-76  /-76 because from 14. to 15. I don't
understand/


start   end  R1R2  R3
07-14 15:00 07-14 17:30 23000   2.5 -74
07-14 23:00 07-15 01:30 783000 78.5 2
07-14 15:00 07-15 02:30 873000 87.5 11
07-14 15:00 07-14 16:00 1   1 -75

Please help me...(exist a function for this situation?)


Tnx!

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

Re: Hour counts

[Martijn Tonies]

Hi,

Check out the  TIMEDIFF  function.

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

I would like to calculate the hour counts from 2 'datetime'.
Example:   2005-07-27 18:00 and 2005-07-27 19:30  => 1,5 hour

I try this  but not good!

R1 : munkaido_end-munkaido_start  /simple substract/
R2 : ROUND(ROUND((end-start)/1)+
(((end-start)/1000-(ROUND((end-start)/1)*10))/6),1)  /good if is in one
day/
R3 : ROUND((end-start)/1)-76  /-76 because from 14. to 15. I don't
understand/


start   end  R1R2  R3
07-14 15:00 07-14 17:30 23000   2.5 -74
07-14 23:00 07-15 01:30 783000 78.5 2
07-14 15:00 07-15 02:30 873000 87.5 11
07-14 15:00 07-14 16:00 1   1 -75

Please help me...(exist a function for this situation?)


Tnx!


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

Re: Which Engine to use...

[Scott Hamm]

Created QAID & QEID indices, now select time went down to .2787 seconds. 

Sorry about top-posting, it's a nice feature in gmail where it hides the 
text that was previously posted. 

Here is the updated explain result.

mysql> EXPLAIN
-> SELECT *
-> FROM QA
-> LEFT JOIN Batch
-> ON Batch.QAID=QA.ID
-> LEFT JOIN QAErrors
-> ON QAErrors.ID=Batch.QEID
-> WHERE QA.ID  BETWEEN '106805' AND '107179'
-> ORDER BY QA.ID ;
++-+--+---+---+-+-+-
--+--+-+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
++-+--+---+---+-+-+-
--+--+-+
| 1 | SIMPLE | QA | range | PRIMARY | PRIMARY | 4 | NULL
| 692 | Using where |
| 1 | SIMPLE | Batch | ref | Index_1 | Index_1 | 5 | qcda
ta_dbo.QA.ID | 2 | |
| 1 | SIMPLE | QAErrors | ref | Index_2 | Index_2 | 4 | qcda
ta_dbo.Batch.QEID | 1 | Using index |
++-+--+---+---+-+-+-
--+--+-+

Hour counts

[Gyurasits Zoltán]

Hello All!


I would like to calculate the hour counts from 2 'datetime'.
Example:   2005-07-27 18:00 and 2005-07-27 19:30  => 1,5 hour

I try this  but not good!

R1 : munkaido_end-munkaido_start  /simple substract/
R2 : ROUND(ROUND((end-start)/1)+ 
(((end-start)/1000-(ROUND((end-start)/1)*10))/6),1)  /good if is in one day/
R3 : ROUND((end-start)/1)-76  /-76 because from 14. to 15. I don't 
understand/


start   end  R1R2  R3
07-14 15:00 07-14 17:30 23000   2.5 -74
07-14 23:00 07-15 01:30 783000 78.5 2
07-14 15:00 07-15 02:30 873000 87.5 11
07-14 15:00 07-14 16:00 1   1 -75

Please help me...(exist a function for this situation?)


Tnx!

Re: Which Engine to use...

[SGreen]

(I don't like top-posting but we are already in that pattern...)

This looks good, except for the "Using temporary; Using filesort" 
(http://dev.mysql.com/doc/mysql/en/explain.html)

>From http://dev.mysql.com/doc/mysql/en/order-by-optimization.html
>>>
 If you want to increase ORDER BY speed, first see whether you can get 
MySQL to use indexes rather than an extra sorting phase. If this is not 
possible, you can try the following strategies:

*   Increase the size of the sort_buffer_size variable.
*   Increase the size of the read_rnd_buffer_size variable.
*  Change tmpdir to point to a dedicated filesystem with lots of 
empty space. If you use MySQL 4.1 or later, this option accepts several 
paths that are used in round-robin fashion. Paths should be separated by 
colon characters (':') on Unix and semicolon characters (';') on Windows, 
NetWare, and OS/2. You can use this feature to spread the load across 
several directories. Note: The paths should be for directories in 
filesystems that are located on different physical disks, not different 
partitions of the same disk.
<<

I would try altering Batch.Index_1 to be (QAID,QEID) and try another 
explain. I suggest this to try to eliminate the TYPE: ALL for the QAErrors 
table in your explain output. Even with what I see here the EXPLAIN says 
you have an estimated 692 * 2 * 9871 = 13661464 rows to examine. That's 
not much for MySQL to deal with so I don't think the volume of your data 
is not going to be much of an issue 

How powerful is your server? Important factors are CPU type, RAM capacity, 
memory bus speed,  operating system, and  the HDD specs like rotational 
speed, RAID configuration,  and connection type (IDE, SCSI, etc.).


Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Scott Hamm <[EMAIL PROTECTED]> wrote on 07/27/2005 11:03:34 AM:

> mysql> EXPLAIN
> -> SELECT *
> -> FROM QA
> -> LEFT JOIN Batch
> -> ON Batch.QAID=QA.ID
> -> LEFT JOIN QAErrors
> -> ON QAErrors.ID=Batch.QEID
> -> WHERE QA.ID  BETWEEN 106805 AND 107179
> -> ORDER BY QA.ID ;
> ++-+--+---+---+-
> +-+--+--
> +--+
> | id | select_type | table | type | possible_keys | key | key_len | ref 
| 
> rows | Extra |
> ++-+--+---+---+-
> +-+--+--
> +--+
> | 1 | SIMPLE | QA | range | PRIMARY | PRIMARY | 4 | NULL | 692 | Using 
> where; Using temporary; Using filesort |
> | 1 | SIMPLE | Batch | ref | Index_1 | Index_1 | 5 | qcdata_dbo.QA.ID | 
2 | 
> |
> | 1 | SIMPLE | QAErrors | ALL | NULL | NULL | NULL | NULL | 9871 | |
> ++-+--+---+---+-
> +-+--+--
> +--+
> 3 rows in set (0.00 sec)
> 
> On 7/27/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > 
> > 
> > 
> > Scott Hamm <[EMAIL PROTECTED]> wrote on 07/27/2005 07:48:56 AM:
> > 
> > > 'Batch', 'CREATE TABLE `batch` (
> > > `QAID` int(10) default NULL,
> > > `Order` varchar(9) default NULL,
> > > `Errors` tinyint(1) NOT NULL,
> > > `Comments` varchar(255) default NULL,
> > > `QEID` int(10) default NULL,
> > > KEY `Index_1` (`QAID`)
> > > ) ENGINE=InnoDB DEFAULT CHARSET=latin1'
> > > 
> > > 'QAErrors', 'CREATE TABLE `qaerrors` (
> > > `QAID` int(10) NOT NULL,
> > > `ErrorTypeID` int(10) NOT NULL,
> > > `Order` varchar(9) NOT NULL,
> > > `ID` int(10) NOT NULL,
> > > PRIMARY KEY (`QAID`,`ErrorTypeID`,`Order`)
> > > ) ENGINE=InnoDB DEFAULT CHARSET=latin1'
> > > 
> > > 'QA', 'CREATE TABLE `qa` (
> > > `ID` int(10) NOT NULL auto_increment,
> > > `LocationID` int(10) default NULL,
> > > `OperatorID` int(10) default NULL,
> > > `QAID` int(10) default NULL,
> > > `NTID` varchar(10) default NULL,
> > > `BrandID` int(10) default NULL,
> > > `OrdersReviewed` smallint(5) default NULL,
> > > `CorrectOrders` smallint(5) default NULL,
> > > `Batch` varchar(10) default NULL,
> > > `KeyDate` datetime default NULL,
> > > `ReceiveDate` datetime default NULL,
> > > `Training` tinyint(1) NOT NULL,
> > > PRIMARY KEY (`ID`)
> > > ) ENGINE=InnoDB DEFAULT CHARSET=latin1'
> > > 
> > > On 7/25/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > > > 
> > > > 
> > > > 
> > > > Scott Hamm <[EMAIL PROTECTED]> wrote on 07/21/2005 09:39:48 AM:
> > > > 
> > > > > I'm now trying to learn engines in MySQL. When I migrated from 
M$ 
> > SQL to 
> > > > 
> > > > > MySQL to learn the migration process and executed the following:
> > > > > 
> > > > > SELECT
> > > > > *
> > > > > FROM 
> > > > > QA
> > > > > LEFT JOIN 
> > > > > Batch 
> > > > > ON 
> > > > > Batch.QAID=QA.ID
> > > > > LEFT JOIN 
> > > > > QAErrors 
> > > > > ON 
> > > > > QAErrors.ID=Batch.QEID
> > > > > WHERE 
> > > > > QA.ID   

Re: Which Engine to use...

[Scott Hamm]

mysql> EXPLAIN
-> SELECT *
-> FROM QA
-> LEFT JOIN Batch
-> ON Batch.QAID=QA.ID
-> LEFT JOIN QAErrors
-> ON QAErrors.ID=Batch.QEID
-> WHERE QA.ID  BETWEEN 106805 AND 107179
-> ORDER BY QA.ID ;
++-+--+---+---+-+-+--+--+--+
| id | select_type | table | type | possible_keys | key | key_len | ref | 
rows | Extra |
++-+--+---+---+-+-+--+--+--+
| 1 | SIMPLE | QA | range | PRIMARY | PRIMARY | 4 | NULL | 692 | Using 
where; Using temporary; Using filesort |
| 1 | SIMPLE | Batch | ref | Index_1 | Index_1 | 5 | qcdata_dbo.QA.ID | 2 | 
|
| 1 | SIMPLE | QAErrors | ALL | NULL | NULL | NULL | NULL | 9871 | |
++-+--+---+---+-+-+--+--+--+
3 rows in set (0.00 sec)

On 7/27/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> 
> 
> 
> Scott Hamm <[EMAIL PROTECTED]> wrote on 07/27/2005 07:48:56 AM:
> 
> > 'Batch', 'CREATE TABLE `batch` (
> > `QAID` int(10) default NULL,
> > `Order` varchar(9) default NULL,
> > `Errors` tinyint(1) NOT NULL,
> > `Comments` varchar(255) default NULL,
> > `QEID` int(10) default NULL,
> > KEY `Index_1` (`QAID`)
> > ) ENGINE=InnoDB DEFAULT CHARSET=latin1'
> > 
> > 'QAErrors', 'CREATE TABLE `qaerrors` (
> > `QAID` int(10) NOT NULL,
> > `ErrorTypeID` int(10) NOT NULL,
> > `Order` varchar(9) NOT NULL,
> > `ID` int(10) NOT NULL,
> > PRIMARY KEY (`QAID`,`ErrorTypeID`,`Order`)
> > ) ENGINE=InnoDB DEFAULT CHARSET=latin1'
> > 
> > 'QA', 'CREATE TABLE `qa` (
> > `ID` int(10) NOT NULL auto_increment,
> > `LocationID` int(10) default NULL,
> > `OperatorID` int(10) default NULL,
> > `QAID` int(10) default NULL,
> > `NTID` varchar(10) default NULL,
> > `BrandID` int(10) default NULL,
> > `OrdersReviewed` smallint(5) default NULL,
> > `CorrectOrders` smallint(5) default NULL,
> > `Batch` varchar(10) default NULL,
> > `KeyDate` datetime default NULL,
> > `ReceiveDate` datetime default NULL,
> > `Training` tinyint(1) NOT NULL,
> > PRIMARY KEY (`ID`)
> > ) ENGINE=InnoDB DEFAULT CHARSET=latin1'
> > 
> > On 7/25/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > > 
> > > 
> > > 
> > > Scott Hamm <[EMAIL PROTECTED]> wrote on 07/21/2005 09:39:48 AM:
> > > 
> > > > I'm now trying to learn engines in MySQL. When I migrated from M$ 
> SQL to 
> > > 
> > > > MySQL to learn the migration process and executed the following:
> > > > 
> > > > SELECT
> > > > *
> > > > FROM 
> > > > QA
> > > > LEFT JOIN 
> > > > Batch 
> > > > ON 
> > > > Batch.QAID=QA.ID
> > > > LEFT JOIN 
> > > > QAErrors 
> > > > ON 
> > > > QAErrors.ID=Batch.QEID
> > > > WHERE 
> > > > QA.ID    
> > > > BETWEEN 
> > > > '106805' 
> > > > AND 
> > > > '107179'
> > > > ORDER BY 
> > > > QA.ID   ;
> > > > 
> > > > M$ SQL executed and brought up result in 2 seconds
> > > > where MySQL took 801 seconds and where
> > > > Batch datalength is around 18.5 MB,
> > > > QAErrors is around 464KB and
> > > > QA is around 3.5MB
> > > > 
> > > > Which engine should I use and should I apply to all these tables or?
> > > > 
> > > > Batch/QAErrors/QA is most frequent used in database.
> > > > -- 
> > > > Power to people, Linux is here.
> > > 
> > > Engine choices will only help you deal with concurrency issues. MyISAM 
> 
> > > uses table locking while InnoDB uses row-level locking and supports 
> > > transactions. What it sounds like is an INDEXING issue. If you used 
> the MS 
> > > SQL technique of creating several single-column indexes (to duplicate 
> an 
> > > existing table) you will not get optimal performance from MySQL. 
> > You need to 
> > > determine the best indexes to cover the majority of your query cases. 
> > > 
> > > If you could, please post the results of SHOW CREATE TABLE for these 
> > > tables: Batch, QAErrors, and QA so that we can review your indexes. 
> > > 
> > > Shawn Green
> > > Database Administrator
> > > Unimin Corporation - Spruce Pine 
> > > 
> > > 
> > 
> > 
> > -- 
> > Power to people, Linux is here.
>  
> Here is your original query (slightly reformatted): 
> 
> SELECT * FROM QA 
> LEFT JOIN Batch 
> ON Batch.QAID=QA.ID 
> LEFT JOIN QAErrors 
> ON QAErrors.ID=Batch.QEID 
> WHERE QA.ID  BETWEEN '106805' AND '107179' 
> ORDER BY QA.ID ; 
> 
> What I was looking for were indexes on QA.ID , Batch.QAID, 
> Batch.QEID, and QAErrors.ID. Almost all of them were what I would want 
> them to be for a query like this. 
> 
> The Batch table is joined with twice, once with QA the second with 
> QAErrors. There is only one index on the Batch table so the optimizer must 
> choose between using or not using the index on QAID. Since that is not a 
> P

Re: Which Engine to use...

[SGreen]

Scott Hamm <[EMAIL PROTECTED]> wrote on 07/27/2005 07:48:56 AM:

> 'Batch', 'CREATE TABLE `batch` (
> `QAID` int(10) default NULL,
> `Order` varchar(9) default NULL,
> `Errors` tinyint(1) NOT NULL,
> `Comments` varchar(255) default NULL,
> `QEID` int(10) default NULL,
> KEY `Index_1` (`QAID`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1'
> 
> 'QAErrors', 'CREATE TABLE `qaerrors` (
> `QAID` int(10) NOT NULL,
> `ErrorTypeID` int(10) NOT NULL,
> `Order` varchar(9) NOT NULL,
> `ID` int(10) NOT NULL,
> PRIMARY KEY (`QAID`,`ErrorTypeID`,`Order`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1'
> 
> 'QA', 'CREATE TABLE `qa` (
> `ID` int(10) NOT NULL auto_increment,
> `LocationID` int(10) default NULL,
> `OperatorID` int(10) default NULL,
> `QAID` int(10) default NULL,
> `NTID` varchar(10) default NULL,
> `BrandID` int(10) default NULL,
> `OrdersReviewed` smallint(5) default NULL,
> `CorrectOrders` smallint(5) default NULL,
> `Batch` varchar(10) default NULL,
> `KeyDate` datetime default NULL,
> `ReceiveDate` datetime default NULL,
> `Training` tinyint(1) NOT NULL,
> PRIMARY KEY (`ID`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1'
> 
> On 7/25/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > 
> > 
> > 
> > Scott Hamm <[EMAIL PROTECTED]> wrote on 07/21/2005 09:39:48 AM:
> > 
> > > I'm now trying to learn engines in MySQL. When I migrated from M$ 
SQL to 
> > 
> > > MySQL to learn the migration process and executed the following:
> > > 
> > > SELECT
> > > *
> > > FROM 
> > > QA
> > > LEFT JOIN 
> > > Batch 
> > > ON 
> > > Batch.QAID=QA.ID
> > > LEFT JOIN 
> > > QAErrors 
> > > ON 
> > > QAErrors.ID=Batch.QEID
> > > WHERE 
> > > QA.ID   
> > > BETWEEN 
> > > '106805' 
> > > AND 
> > > '107179'
> > > ORDER BY 
> > > QA.ID  ;
> > > 
> > > M$ SQL executed and brought up result in 2 seconds
> > > where MySQL took 801 seconds and where
> > > Batch datalength is around 18.5 MB,
> > > QAErrors is around 464KB and
> > > QA is around 3.5MB
> > > 
> > > Which engine should I use and should I apply to all these tables or?
> > > 
> > > Batch/QAErrors/QA is most frequent used in database.
> > > -- 
> > > Power to people, Linux is here.
> > 
> > Engine choices will only help you deal with concurrency issues. MyISAM 

> > uses table locking while InnoDB uses row-level locking and supports 
> > transactions. What it sounds like is an INDEXING issue. If you used 
the MS 
> > SQL technique of creating several single-column indexes (to duplicate 
an 
> > existing table) you will not get optimal performance from MySQL. 
> You need to 
> > determine the best indexes to cover the majority of your query cases. 
> > 
> > If you could, please post the results of SHOW CREATE TABLE for these 
> > tables: Batch, QAErrors, and QA so that we can review your indexes. 
> > 
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine 
> > 
> > 
> 
> 
> -- 
> Power to people, Linux is here.

Here is your original query (slightly reformatted):

SELECT * FROM QA
LEFT JOIN Batch 
ON Batch.QAID=QA.ID
LEFT JOIN QAErrors 
ON QAErrors.ID=Batch.QEID
WHERE QA.ID BETWEEN '106805' AND '107179'
ORDER BY QA.ID;

What I was looking for were indexes on QA.ID, Batch.QAID, Batch.QEID, and 
QAErrors.ID. Almost all of them were what I would want them to be for a 
query like this.

The Batch table is joined with twice, once with QA the second with 
QAErrors. There is only one index on the Batch table so the optimizer must 
choose between using or not using the index on QAID. Since that is not a 
PRIMARY KEY or UNIQUE KEY, you may try adding QEID to Batch.Index_1 as a 
secondary column to see if that helps. It may also help to create a second 
index on either QEID (alone) or the combination of QEID,QAID. As I 
mentioned above, all of the other useful index combinations were present. 
Liberally use EXPLAIN to test any differences in index usage (after 
adding, deleting, or changing an index) without the need to wait for 
actual results. 

Now, with that bit of operational theory out of the way. I believe the 
speed killer in your statement is here:

WHERE QA.ID BETWEEN '106805' AND '107179'

(I can't find a reference in the manual to explain why I think this but I 
do remember reading this somewhere) What I think you asked the engine to 
do was to convert all of the numeric values of the ID column of the QA 
table to string values and compare them to the two string values you gave 
it. That one transformation would preclude the engine from using the 
PRIMARY KEY on the QA table, forcing a full table scan. What you probably 
should have said was:

WHERE QA.ID BETWEEN 106805 AND 107179

This statement asks the engine to compare numeric values (the data in 
QA.ID) to numbers (the two values in the BETWEEN clause). Since you have 
an index on that column, this will be a ranged index lookup and will be 
quite fast.http://dev.mysql.com/doc/mysql/en/number-syntax.html)

Try it again without the quo

Continued x86_64 install problems

[Anne Ramey]

I managed to install
MySQL-client-4.0.25-0.ia64.rpm
MySQL-server-4.0.25-0.ia64.rpm
MySQL-devel-4.0.25-0.ia64.rpm

but MySQL-shared-4.0.25-0.ia64.rpm was still giving me the same error 
about missing a glibc library even though glibc 2.3 is installed.


Though the install worked, I now get:
[EMAIL PROTECTED] mysql]# mysql
-bash: /usr/bin/mysql: cannot execute binary file

[EMAIL PROTECTED] mysql]# /etc/rc.d/init.d/mysql start
/etc/rc.d/init.d/mysql: line 128: /usr/bin/my_print_defaults: cannot 
execute binary file


I've never seen this before, and google doesn't  seem to have anything. 
 What could be causing this?  my mysql error log says:

050721 10:47:18  mysqld started
050721 10:47:18 Warning: Asked for 196608 thread stack, but got 126976
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
050721 10:47:18  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
050721 10:47:18  InnoDB: Log file ./ib_logfile0 did not exist: new to be 
created

InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
050721 10:47:19  InnoDB: Log file ./ib_logfile1 did not exist: new to be 
created

InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
050721 10:47:20  InnoDB: Started
/usr/sbin/mysqld: ready for connections.
Version: '4.0.23-standard'  socket: '/var/lib/mysql/mysql.sock'  port: 
3306  Official MySQL RPM

050721 10:47:33 /usr/sbin/mysqld: Normal shutdown

050721 10:47:33  InnoDB: Starting shutdown...
050721 10:47:36  InnoDB: Shutdown completed
050721 10:47:36 /usr/sbin/mysqld: Shutdown Complete

Other than the version #, that looks fine, but I can't get mysql to 
start again.


Anne

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

Doubt about query optimization

[Ronan Lucio]

Hello,

I´m using MySQL-4.1.7, and I have a query that seems,
even using where clause, an specific table isn´t being optimized
properly:

I have four tables:

real_state: cod, name, city, ag_cod, sell
agency: cod, name, ag_status
front: cod, rs_cod, ag_cod
photo: cod, rs_cod

These table have the following indexes:

real_state:
   cod (pri)
   city
   ag_cod
agency:
   cod
   name
front:
   cod
   rs_cod
   ag_cod
photo
   cod
   rs_cod

When I EXPLAIN the query:

EXPLAIN
SELECT front.rs_cod, photo.cod, real_state.descr
FROM real_state, agency, front, photo_foto
WHERE real_state.city = 1
AND real_state.sell = 1
AND front.rs_cod = real_state.cod
AND photo.rs_cod = front.rs_cod
AND agency.cod = real_state.ag_cod
AND ag_status = 'A'

It shows me (in a short):

tablekeyrows
======  
frontrs_cod   2085
real_statecod1
agencycod1
photo  rs_cod   1

But the trouble is: If I execute:

SELECT COUNT(*)
FROM real_state, front
WHERE real_state.city = 1
AND real_state.cod = front.rs_cod

Returns: 271

So, Why the index front.rs_cod isn´t being used?

Any help would be appreciated,

thank you,
Ronan 




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

RE: query on a very big table

[christopher . l . hood]

I have tables that are over 7 million records and I originally had the
same issue, however if you will create indexes in those tables, on the
columns that you will be using for your queries this will GREATLY speed up
your queries.

I am sure that there is a more concise way to state how you should create
indexes but you can look at the mysql online docs to figure out what is
best for you.

Chris Hood 
Investigator Verizon Global Security Operations Center 


-Original Message-
From: Christos Andronis [mailto:[EMAIL PROTECTED] 
Sent: Monday, July 25, 2005 8:20 AM
To: mysql@lists.mysql.com
Subject: query on a very big table

Hi all,
we are trying to run the following query on a table that contains over 600
million rows: 

'ALTER TABLE `typed_strengths` CHANGE `entity1_id` `entity1_id` int(10)
UNSIGNED DEFAULT NULL FIRST'

The query takes ages to run (has been running for over 10 hours now). Is
this normal?

As a side issue, is MySQL suited for such big tables? I've seen a couple
of case studies with MySQL databases over 1.4 billion rows but it is not
clear to me whether this size corresponds to the whole database or whether
it is for a single table.

The MySQL distribution we're using is 4.1.12. The database sits on a HP
Proliant DL585 server with 2 dual-core Opterons and 12 GB of RAM, running
Linux Fedora Core 3.

Thanks in advance for any responses

-Christos Andronis


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

Re: Mysqld chewing up cpu in the background.

[Dan Baughman]

Thanks for the suggestion. That did help me find out. It wasn't idle. 
The problem is that its taking 10 seconds to insert a single record,
composed mainly of one longtext field.  The index is killing me.  Does
anyone know of how FT indexing can be changed to do inserts quicker or
something?

On 7/25/05, Richard Dale <[EMAIL PROTECTED]> wrote:
> > I am fairly sure that there aren't any queries being run, but while in
> > the background, my mysqld process chews up exactly 50% of my cpu. It
> > runs queries nicely and has excellent response times for most any
> > query I throw at it, but its causing issues for other apps.
> 
> Try using mytop to see if there are queries going on.
> 
> It's like the unix "top" command.
> http://jeremy.zawodny.com/mysql/mytop/
> 
> Also try:
> SHOW PROCESSLIST;
> 
> If you use InnoDB:
> show innodb status;
> 
> Best regards,
> Richard Dale.
> Norgate Investor Services
> - Premium quality Stock, Futures and Foreign Exchange Data for
>  markets in Australia, Asia, Canada, Europe, UK & USA -
> www.premiumdata.net
> 
> 
> 
> --
> 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: UNION in JDBC - WAS Re: use of indexes

[Gleb Paharenko]

Hello.



Check with SHOW PROCESSLIST in which state MySQL thread which performs

the query is. See:

  http://dev.mysql.com/doc/mysql/en/show-processlist.html







Chris Faulkner <[EMAIL PROTECTED]> wrote:

> Hello again

> 

> Following on from this index question, the UNION worked. From a normal

> mysql client, it was returning my results sub-second. I am actually

> executing this over JDBC, using mysql-connector j.

> 

> WHen I put the SQL into my Java program - it takes a minute or so. I

> am logging the SQL and if I copy and paste it into my mysql client, it

> is fast. I can execute the query first in mysql and then in the JDBC

> client and I get the same so it is not caching. I've done a bit of

> searching but found nothing - any ideas ?

> 

> 

> Chris

> 

> On 7/22/05, Chris Faulkner <[EMAIL PROTECTED]> wrote:

>> That was exactly the problem. Thanks. MySQL can't use two indexes on

>> the same table at the same time. Thanks for the other suggestions but

>> I'll use this workaround.

>>=20

>> Chris

>>=20

>> On 7/22/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

>> > I believe the conflict here is the OR. Try this...

>> >

>> > select * from table

>> >   where field1 =3D 'VALUE1' and field2 like 'VALUE2%'

>> > union

>> > select * from table

>> >   where field3 =3D 'VALUE1' and field2 like 'VALUE2%'

>> >

>> > Currently, MySql can't utilize two indexes on the same table at

>> > the same time but it is on their list of to-do`s, this will be

>> > a cool feature. The UNION will allow you to use both composite

>> > indexes at the same time because it is two queries.

>> >

>> > Ed

>> >

>> > -Original Message-

>> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]

>> > Sent: Friday, July 22, 2005 6:04 AM

>> > To: Chris Faulkner

>> > Cc: mysql@lists.mysql.com

>> > Subject: Re: use of indexes

>> >

>> > The system cannot used the index on field2 because it is the second hal=

> f

>> >

>> > of the index in both cases, and it can only use indexes in order. It

>> > cannot use the separate indexes on field 1 and field 2 because the are

>> > ORred together.

>> >

>> > If you rephrase your query

>> >

>> > SELECT * from table

>> >   WHERE field2 LIKE 'VALUE2%"

>> > AND ((field1 =3D 'VALUE1') OR (field3 =3D 'VALUE3')) ;

>> >

>> > it becomes obvious that an index on field2 will be used, followed by

>> > searches of the results field1 and field3 .

>> >

>> > As a matter of interest, what numbers of hits do you expect on each of

>> > the

>> > three terms separately? If the field2 hit is is pretty selective, it

>> > does

>> > not really matter what the others do.

>> >

>> > Alec

>> >

>> > Chris Faulkner <[EMAIL PROTECTED]>

>> > 22/07/2005 12:46

>> > Please respond to

>> > Chris Faulkner <[EMAIL PROTECTED]>

>> >

>> >

>> > To

>> > mysql@lists.mysql.com

>> > cc

>> >

>> > Subject

>> > Re: use of indexes

>> >

>> > Hi

>> >

>> > field2 is indexed. I have 2 indexes. One is on field1 and field2, the

>> > second indexes field3 and field2.

>> >

>> > You mean a separate index which only indexes field2 ? Ithought that

>> > the type of query I am doing is a good reason for doing composite

>> > indexes.

>> >

>> >

>> > Chris

>> >

>> > On 7/22/05, Eugene Kosov <[EMAIL PROTECTED]> wrote:

>> > > Chris Faulkner wrote:

>> > > > HI

>> > > >

>> > > > I have a query like this

>> > > >

>> > > > select * from table where (

>> > > > ( field1 =3D 'VALUE1' and field2 like 'VALUE2%' )

>> > > > OR

>> > > > ( field3 =3D 'VALUE1' and field2 like 'VALUE2%' )

>> > > > )

>> > > >

>> > > > I have created two composite indexes - one on field1 + field2 and

>> > one

>> > > > on field3 + field2. Explain on the SQL indicates that the indexes

>> > are

>> > > > possibly used. The query takes an age to run and looking at my log

>> > > > indicates a full table scan.

>> > > >

>> > > > I have also tried indexing just field1 and field3 separately but

>> > this

>> > > > doesn't help. I have run an analyze.

>> > > >

>> > > > Chris

>> > > >

>> > >

>> > > Mysql use an index only if indexed field(s) present(s) in both OR

>> > arguments..

>> > > Sorry, but i can't find it in docs right now, so i can't give you any

>> > helpful link.

>> > >

>> > > I think index on field2 may help you here..

>> > >

>> > >

>> > --

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

> gmail.com

>> >

>> >

>>

> 



-- 
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]
/_/  /_/\_, /

Re: Mysql +events

[Gleb Paharenko]

Hello.





You could implement your own event notifiers using UDFs and TRIGGERs

(triggers are available only in MySQL 5, which is not production ready yet).

See:

  http://dev.mysql.com/doc/mysql/en/adding-functions.html

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







"Darryl Hoar" <[EMAIL PROTECTED]> wrote:

> Greetings,

> I am currently using Mysql 3.23.52.   I am looking for events (when a record

> is added, mod'd or deleted

> from a table, and event is sent to each client connected to the DB.

> Firebird can do this,

> but before I port my application to firebird, I was wondering if Mysql had

> something similar ?

> 

> thanks in advance,

> Darryl

> 

> 

> 



-- 
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: Re search and replace in large text fields

[Gleb Paharenko]

Hello.





Use TEXT or BLOB columns' types. See:

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



Use hexademical values in your scripts which load pages 

in the database. See:

  http://dev.mysql.com/doc/mysql/en/hexadecimal-values.html



Possibly you'll need to increase packet size. See:

  http://dev.mysql.com/doc/mysql/en/packet-too-large.html



String functions should help you:

  http://dev.mysql.com/doc/mysql/en/string-functions.html





  

Gregory Machin <[EMAIL PROTECTED]> wrote:

> Hi all...

> Please could you advise me my MySql, is a bit shacky.

> I need to capture about 700 web pages to a large text column in my

> database, the search for all the links in the pages within the column

> and replace them with new relative links .. to the pages now stored in

> the data base ..

> 

> so how do I=20

> 1 import all the pages in one directory into the column.

> 2 how do i do a search and replace the tags with the new correct ones ?

> 

> Many thanks ...

> 

> 

> --=20

> Gregory Machin

> [EMAIL PROTECTED]

> [EMAIL PROTECTED]

> www.linuxpro.co.za

> Web Hosting Solutions

> Scalable Linux Solutions=20

> www.iberry.info (support and admin)

> www.goeducation (support and admin)

> +27 72 524 8096

> 



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

[Gleb Paharenko]

Hello.





Have you been at:

  http://dev.mysql.com/doc/mysql/en/starting-server.html



Check the error log:

  http://dev.mysql.com/doc/mysql/en/error-log.html







Maurizio Dudine <[EMAIL PROTECTED]> wrote:

> SEND-PR: -*- send-pr -*-

> SEND-PR: Lines starting with `SEND-PR' will be removed

> automatically, as

> SEND-PR: will all comments (text enclosed in `<' and

> `>').

> SEND-PR:

> From: maurizio

> To: mysql@lists.mysql.com

> Subject: [Cannot start mysql]

> 

>>Description:

><1. I have installed the six packages (see Manual)

> with the YAST 2.

> 2. I have typed mysqld_install_db

> 3. When I try to open mysql typing "mysql"I get the

> message "Cannot connect to local Mysql server through

>  socket var/lib/mysql/mysql.sock

> 4. I looked for the socket and there is no

> mysql.sock anywhere on my machine

> 5. If I type "safe_mysqld" the answer is "Starting

> mysql-max demon with database from /var/lib/mysql

>  050720 mysqld ended" >

>>How-To-Repeat:

> (multiple lines)>

>>Fix:

> (multiple lines)>

> 

>>Submitter-Id:  

>>Originator:Maurizio Dudine

>>Organization:

> 

>>MySQL support: [none | licence | email support |

> extended email support ]

>>Synopsis:  

>>Severity:  <[ non-critical | serious | critical ] (one

> line)>

>>Priority:  <[ low | medium | high ] (one line)>

>>Category:  mysql

>>Class: <[ sw-bug | doc-bug | change-request |

> support ] (one line)>

>>Release:   mysql-3.23.48 (Source distribution)

> 

>>Environment:

>

> System: Linux linux 2.4.18-4GB #1 Wed Mar 27 13:57:05

> UTC 2002 i686 unknown

> Architecture: i686

> 

> Some paths:  /usr/bin/perl /usr/bin/make

> /usr/bin/gmake /usr/bin/gcc /usr/bin/cc

> GCC: Reading specs from

> /usr/lib/gcc-lib/i486-suse-linux/2.95.3/specs

> gcc version 2.95.3 20010315 (SuSE)

> Compilation info: CC='gcc'  CFLAGS=''  CXX='c++' 

> CXXFLAGS='-O2 -march=i486 -mcpu=i686

> -D_FILE_OFFSET_BITS=64  -D_LARGEFILE_SOURCE   
>   

> -felide-constructors-fno-exceptions   
>   

> -fno-rtti'  LDFLAGS=''

> LIBC: 

> -rwxr-xr-x1 root root  1394238 mar 23 

> 2002 /lib/libc.so.6

> -rw-r--r--1 root root 25361424 mar 23 

> 2002 /usr/lib/libc.a

> -rw-r--r--1 root root  178 mar 23 

> 2002 /usr/lib/libc.so

> -rw-r--r--1 root root   869190 mar 23 

> 2002 /usr/lib/libc-client.a

> lrwxrwxrwx1 root root   20 jul 19

> 12:17 /usr/lib/libc-client.so -> libc-client.so.2001a

> -rwxr-xr-x1 root root   725296 mar 23 

> 2002 /usr/lib/libc-client.so.2001a

> Configure command: ./configure  --disable-shared

> --with-mysqld-ldflags=-static

> --with-client-ldflags=-static --without-berkeley-db

> --without-innodb --enable-assembler

> --enable-large-files --infodir=/usr/share/info

> --libdir=/usr/lib --libexecdir=/usr/sbin

> --localstatedir=/var/lib/mysql --mandir=/usr/share/man

> --prefix=/usr --sysconfdir=/etc

> --with-mysqld-user=mysql --without-debug

> --datadir=/usr/share --includedir=/usr/include

> --with-extra-charsets=complex

> --with-unix-socket-path=/var/lib/mysql/mysql.sock

> --with-libwrap

> 

> 

> 

> 

>

> __ 

> Renovamos el Correo Yahoo! 

> Nuevos servicios, m$s seguridad 

> http://correo.yahoo.es

> 



-- 
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: Correct way to use innodb_file_per_table?

[Marvin Wright]

Hi Bruce,

I tried Heikki's solution but it did not work.
The databases were there and table names but it could not open the idb
files.
This must be because of the table definitions are being stored in the shared
space (according to the documentation)?

Anyway looks like I will have to dump all data, make the changes and
re-import everything.
That's a bit of a pain for something that seems so trivial, its going to
take quite a lot of time as the data is huge.
Oh well :)

Thanks for your help.

Marvin.

-Original Message-
From: Bruce Dembecki [mailto:[EMAIL PROTECTED] 
Sent: 26 July 2005 16:24
To: Marvin Wright; mysql@lists.mysql.com
Subject: Re: Correct way to use innodb_file_per_table?


On Jul 26, 2005, at 3:56 AM, Marvin Wright wrote:

>
> Regarding the file size issue, we are on a 32-bit system running 
> redhat AS3, we already have idb files in excess of 21Gb, I'm not sure 
> what the limit is though if any ?
>
No, typically a 32 bit file system would have limits like 2G or 4G...  
at 21G already I doubt you'll have a problem... That said I know little more
about Linux than how to spell it, so I'm not the best person to give
specific information on that.

> Just one thought about the shared space, do you think it would be 
> possible to back up all the current shared data files along with the 
> iblog files, change the my.cnf file to use a single ibdata file of 2 
> gig, then try to start it up.  If it did fail how about reverting the 
> my.cnf and restoring the original ibdata and iblog files, would it 
> still work after restoring the original files ?  With this I could 
> test Heikki idea without the pssibility of losing data.
>
Yes, if you start MySQL without the files (simply moving them to a holding
directory) and it doesn't come up or can't find the data after it boots you
can always put the files (and your original
my.cnf) back and go on from there. At least that's how it's supposed to work
:-)

Best Regards, Bruce


**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**


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

Re: InnoDB Netapp Snapshot

[Jeff Richards]

Hi Dobromir,

Thanks for the feedback. So you have successfully restored from a
Snapshot created in this way?

Jeff

On Wed, 2005-07-27 at 12:30 +0300, Dobromir Velev wrote:
> Hi,
-- 
Jeff Richards
Consulting Architect
Openwave Systems Asia Pacific
+61 415 638757


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

Re: "port" option ignored by commands SOLVED

[amailp]

Wolfram wrote :
>[EMAIL PROTECTED] wrote:
>> Hi,
>> 
>> 
>> using mysql  4.0.22-standard-log.
>> one instance on port 3307
>> one instance on port 3306
>> (each binary in its own independent directory)
>> 
>> unix 'root' account submits following commands and connects to mysql on 
>> default 3306
>> port instead of awaited 3307 port :
>> ~mysqld/bin/mysql  --port=3307 -p
>> 
>> 
>> What am I missing ? 
>> Is that a known bug ? similar to bug#5792 ?
>> 
>> Thanks for your help,
>> 
>> 
>> Andrew
>> 
>Use the -h option for the host, otherwise mysql tries to connect via 
>socket:
>~mysqld/bin/mysql  --port=3307 -p -h 127.0.0.1
>   
>HTH,
>Wolfram

Good, this works.

I had tried a wrong --tcp-ip option for that.
BTW , using -S/tmp/mysql_3307.sock works fine also.

Thanks a lot,

Andrew

-
NetCourrier, votre bureau virtuel sur Internet : Mail, Agenda, Clubs, Toolbar...
Web/Wap : www.netcourrier.com
Téléphone/Fax : 08 92 69 00 21 (0,34 € TTC/min)
Minitel: 3615 NETCOURRIER (0,16 € TTC/min)


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

Re: Which Engine to use...

[Scott Hamm]

'Batch', 'CREATE TABLE `batch` (
`QAID` int(10) default NULL,
`Order` varchar(9) default NULL,
`Errors` tinyint(1) NOT NULL,
`Comments` varchar(255) default NULL,
`QEID` int(10) default NULL,
KEY `Index_1` (`QAID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1'

'QAErrors', 'CREATE TABLE `qaerrors` (
`QAID` int(10) NOT NULL,
`ErrorTypeID` int(10) NOT NULL,
`Order` varchar(9) NOT NULL,
`ID` int(10) NOT NULL,
PRIMARY KEY (`QAID`,`ErrorTypeID`,`Order`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1'

'QA', 'CREATE TABLE `qa` (
`ID` int(10) NOT NULL auto_increment,
`LocationID` int(10) default NULL,
`OperatorID` int(10) default NULL,
`QAID` int(10) default NULL,
`NTID` varchar(10) default NULL,
`BrandID` int(10) default NULL,
`OrdersReviewed` smallint(5) default NULL,
`CorrectOrders` smallint(5) default NULL,
`Batch` varchar(10) default NULL,
`KeyDate` datetime default NULL,
`ReceiveDate` datetime default NULL,
`Training` tinyint(1) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1'

On 7/25/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> 
> 
> 
> Scott Hamm <[EMAIL PROTECTED]> wrote on 07/21/2005 09:39:48 AM:
> 
> > I'm now trying to learn engines in MySQL. When I migrated from M$ SQL to 
> 
> > MySQL to learn the migration process and executed the following:
> > 
> > SELECT
> > *
> > FROM 
> > QA
> > LEFT JOIN 
> > Batch 
> > ON 
> > Batch.QAID=QA.ID
> > LEFT JOIN 
> > QAErrors 
> > ON 
> > QAErrors.ID=Batch.QEID
> > WHERE 
> > QA.ID   
> > BETWEEN 
> > '106805' 
> > AND 
> > '107179'
> > ORDER BY 
> > QA.ID  ;
> > 
> > M$ SQL executed and brought up result in 2 seconds
> > where MySQL took 801 seconds and where
> > Batch datalength is around 18.5 MB,
> > QAErrors is around 464KB and
> > QA is around 3.5MB
> > 
> > Which engine should I use and should I apply to all these tables or?
> > 
> > Batch/QAErrors/QA is most frequent used in database.
> > -- 
> > Power to people, Linux is here.
>  
> Engine choices will only help you deal with concurrency issues. MyISAM 
> uses table locking while InnoDB uses row-level locking and supports 
> transactions. What it sounds like is an INDEXING issue. If you used the MS 
> SQL technique of creating several single-column indexes (to duplicate an 
> existing table) you will not get optimal performance from MySQL. You need to 
> determine the best indexes to cover the majority of your query cases. 
> 
> If you could, please post the results of SHOW CREATE TABLE for these 
> tables: Batch, QAErrors, and QA so that we can review your indexes. 
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine 
> 
> 


-- 
Power to people, Linux is here.

Re: Which Engine to use...

[Scott Hamm]

OOPS!!! Wrong tables

On 7/27/05, Scott Hamm <[EMAIL PROTECTED]> wrote:
> 
> 'Bands', 'CREATE TABLE `bands` (
> `BandID` int(11) NOT NULL auto_increment,
> `Band_Name` varchar(255) default NULL,
> PRIMARY KEY (`BandID`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1'
> 
> 'cd_type', 'CREATE TABLE `cd_type` (
> `CD_ID` int(11) NOT NULL auto_increment,
> `Type` varchar(255) NOT NULL,
> PRIMARY KEY (`CD_ID`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1'
> 
> 'title', 'CREATE TABLE `title` (
> `ID` int(11) NOT NULL auto_increment,
> `B_ID` int(11) NOT NULL,
> `Title` varchar(255) NOT NULL,
> `C_ID` int(11) NOT NULL,
> `Track` varchar(255) NOT NULL,
> PRIMARY KEY (`ID`),
> KEY `FK_Band` (`B_ID`),
> KEY `FK_CDType` (`C_ID`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1'
> 
> On 7/25/05, [EMAIL PROTECTED] <[EMAIL PROTECTED] > wrote:
> > 
> > 
> > 
> > Scott Hamm <[EMAIL PROTECTED]> wrote on 07/21/2005 09:39:48 AM:
> > 
> > > I'm now trying to learn engines in MySQL. When I migrated from M$ SQL 
> > to 
> > > MySQL to learn the migration process and executed the following:
> > > 
> > > SELECT
> > > *
> > > FROM 
> > > QA
> > > LEFT JOIN 
> > > Batch 
> > > ON 
> > > Batch.QAID=QA.ID
> > > LEFT JOIN 
> > > QAErrors 
> > > ON 
> > > QAErrors.ID=Batch.QEID
> > > WHERE 
> > > QA.ID   
> > > BETWEEN 
> > > '106805' 
> > > AND 
> > > '107179'
> > > ORDER BY 
> > > QA.ID  ;
> > > 
> > > M$ SQL executed and brought up result in 2 seconds
> > > where MySQL took 801 seconds and where
> > > Batch datalength is around 18.5 MB,
> > > QAErrors is around 464KB and
> > > QA is around 3.5MB
> > > 
> > > Which engine should I use and should I apply to all these tables or?
> > > 
> > > Batch/QAErrors/QA is most frequent used in database.
> > > -- 
> > > Power to people, Linux is here.
> >  
> > Engine choices will only help you deal with concurrency issues. MyISAM 
> > uses table locking while InnoDB uses row-level locking and supports 
> > transactions. What it sounds like is an INDEXING issue. If you used the MS 
> > SQL technique of creating several single-column indexes (to duplicate an 
> > existing table) you will not get optimal performance from MySQL. You need 
> > to 
> > determine the best indexes to cover the majority of your query cases. 
> > 
> > If you could, please post the results of SHOW CREATE TABLE for these 
> > tables: Batch, QAErrors, and QA so that we can review your indexes. 
> > 
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine 
> > 
> > 
> 
> 
> -- 
> Power to people, Linux is here. 
> 



-- 
Power to people, Linux is here.

Re: Which Engine to use...

[Scott Hamm]

'Bands', 'CREATE TABLE `bands` (
`BandID` int(11) NOT NULL auto_increment,
`Band_Name` varchar(255) default NULL,
PRIMARY KEY (`BandID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1'

'cd_type', 'CREATE TABLE `cd_type` (
`CD_ID` int(11) NOT NULL auto_increment,
`Type` varchar(255) NOT NULL,
PRIMARY KEY (`CD_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1'

'title', 'CREATE TABLE `title` (
`ID` int(11) NOT NULL auto_increment,
`B_ID` int(11) NOT NULL,
`Title` varchar(255) NOT NULL,
`C_ID` int(11) NOT NULL,
`Track` varchar(255) NOT NULL,
PRIMARY KEY (`ID`),
KEY `FK_Band` (`B_ID`),
KEY `FK_CDType` (`C_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1'

On 7/25/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> 
> 
> 
> Scott Hamm <[EMAIL PROTECTED]> wrote on 07/21/2005 09:39:48 AM:
> 
> > I'm now trying to learn engines in MySQL. When I migrated from M$ SQL to 
> 
> > MySQL to learn the migration process and executed the following:
> > 
> > SELECT
> > *
> > FROM 
> > QA
> > LEFT JOIN 
> > Batch 
> > ON 
> > Batch.QAID=QA.ID
> > LEFT JOIN 
> > QAErrors 
> > ON 
> > QAErrors.ID=Batch.QEID
> > WHERE 
> > QA.ID   
> > BETWEEN 
> > '106805' 
> > AND 
> > '107179'
> > ORDER BY 
> > QA.ID  ;
> > 
> > M$ SQL executed and brought up result in 2 seconds
> > where MySQL took 801 seconds and where
> > Batch datalength is around 18.5 MB,
> > QAErrors is around 464KB and
> > QA is around 3.5MB
> > 
> > Which engine should I use and should I apply to all these tables or?
> > 
> > Batch/QAErrors/QA is most frequent used in database.
> > -- 
> > Power to people, Linux is here.
>  
> Engine choices will only help you deal with concurrency issues. MyISAM 
> uses table locking while InnoDB uses row-level locking and supports 
> transactions. What it sounds like is an INDEXING issue. If you used the MS 
> SQL technique of creating several single-column indexes (to duplicate an 
> existing table) you will not get optimal performance from MySQL. You need to 
> determine the best indexes to cover the majority of your query cases. 
> 
> If you could, please post the results of SHOW CREATE TABLE for these 
> tables: Batch, QAErrors, and QA so that we can review your indexes. 
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine 
> 
> 


-- 
Power to people, Linux is here.

Re: Update Debian 2.0 (mysql 3.21 to 3.22)

[Gleb Paharenko]

Hello.





> Thanks for the information, I will look more. What are the specifications

> (libraries) for compile mysql 3.22?





Your compiling problem was that 'configure' script had failed while

checking compiler.



>> configure: error: installation or configuration problem: C++

>> compiler cannot create executables.



Usually 'configure' script creates a small test file, compiles and

executes it, if one of these steps fail it reports similar error. Look

though the 'configure' script's code and find the place where it checks

for compiler, than you could modify that place to see what's wrong.









[EMAIL PROTECTED] wrote:

> Thanks for the information, I will look more. What are the specifications

> (libraries) for compile mysql 3.22?

> 

> I want to use 3.22.xx that I know is stable for my application and for

> continue use ISAM format in the future and with a more modern linux

> distribution I want to use 4.1 or wait for 5.0.

> 

>> -Mensaje original-

>> De: Gleb Paharenko [mailto:[EMAIL PROTECTED]

>> Enviado el: jueves, 21 de julio de 2005 14:59

>> Para: mysql@lists.mysql.com

>> Asunto: Re: Update Debian 2.0 (mysql 3.21 to 3.22)

>>

>>

>> Hello.

>>

>> For me it is hard to say what's wrong with all these old stuff. But

>> the general way of solving such issues is described at:

>>   http://dev.mysql.com/doc/mysql/en/crashing.html

>>

>> Why are you going to use 3.22.xx and not for example 4.1?

>>

>>

>> [EMAIL PROTECTED] wrote:

>> > Hi.

>> >

>> > I need to update a debian 2.0 linux box with mysql 3.21.31 to

>> some of the

>> > last version of  mysql 3.22.

>> >

>> > I tried to use the binaries from www.mysql.com (3.22.32), but

>> when I launch

>> > mysql services with the script mysql.server in the log appeared:

>> >

>> > mysqld started on  Thu Jul 21 10:25:56 CEST 2005

>> > 050721 10:25:57  getpwnam: Success

>> > 050721 10:25:57  Aborting

>> >

>> > mysqld ended on  Thu Jul 21 10:25:57 CEST 2005

>> >

>> > I look in google and found:

>> >

>> >

>> >   Please check if a version of MySQL that you have got on CD-ROM is

>> >   static or dynamic. You can check it with ldd program.

>> >

>> >

>> >   For your info, later version of MySQL including the one you are

>> >   referring too, require kernel 2.2.xx and glibc 2.1.2 or above

>> >

>> > Debian 2.0 have the packages libc6 (2.0.7) and gcc (2.7.2.3-4.8).

>> >

>> > I also tried to compile the source code (3.22.32 and 3.22.30) but when I

>> > execute ./configure I got:

>> >

>> > checking whether the C++ compiler (gcc   ) works... no

>> > configure: error: installation or configuration problem: C++

>> compiler cannot

>> > create executables.

>> >

>> > I look in google and tried a trick that I found and execute:

>> >

>> >  CXX="gcc -O3" ./configure

>> >

>> > but I got a similar error

>> >

>> > checking whether the C++ compiler (gcc -O3   ) works... no

>> > configure: error: installation or configuration problem: C++

>> compiler cannot

>> > create executables.

>> >

>> > Any trick to compile the source code or to use the binaries

>> installing any

>> > librarie?

>> >

>> > Thanks in advance.

>> >

>> > Iago.

>> >

>> >

>> >

>> >

>> >

>> >

>> >

>> > __

>> > Renovamos el Correo Yahoo!

>> > Nuevos servicios, más seguridad

>> > http://correo.yahoo.es

>> >

>>

>>

>> --

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

>>

> 

> 

>

> __ 

> Renovamos el Correo Yahoo! 

> Nuevos servicios, más seguridad 

> http://correo.yahoo.es

> 



-- 
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: Urgent Pls....

[Gleb Paharenko]

Hello.





> chown: `mysql:mysql': invalid user



Add mysql user and group (though, I thought that RPM package

automatically does this). Please, next time send you replies 

to the list as well.





Ashok Kumar wrote:

> Hi Gleb,

>  Thank u for ur reply. I downloaded the following

> files.

> 

> MySQL-server-4.1.13-0.i386.rpm

> MySQL-client-4.1.13-0.i386.rpm

> MySQL-devel-4.1.13-0.i386.rpm

> MySQL-shared-4.1.13-0.i386.rpm

> 

>  When i tried to install the server following messages

> are coming. (In previous time i installed the same

> files in other PC, there it was working well. but here

> it's not working).

> 

> [EMAIL PROTECTED] Mysql4.1]# rpm -ia

> MySQL-server-4.1.13-0.i386.rpm

> warning: MySQL-server-4.1.13-0.i386.rpm: V3 DSA

> signature: NOKEY, key ID 5072e1f5

> chown: `mysql:mysql': invalid user

> chown: `mysql': invalid user

> 050722  8:43:31 [Warning] Asked for 196608 thread

> stack, but got 126976

> 050722  8:43:31 [ERROR] Fatal error: Can't change to

> run as user 'mysql' ;  Please check that the user

> exists!

>  

> 050722  8:43:31 [ERROR] Aborting

>  

> 050722  8:43:31 [Note] /usr/sbin/mysqld: Shutdown

> complete

>  

> /usr/bin/mysql_create_system_tables: line 663:  4260

> Broken pipe cat  < use mysql;

> set table_type=myisam;

> $c_d

> $i_d

>  

> $c_h

> $i_h

>  

> $c_u

> $i_u

>  

> $c_f

> $i_f

>  

> $c_t

> $c_c

>  

> $c_ht

> $c_hc

> $c_hr

> $c_hk

>  

> $c_tzn

> $i_tzn

> $c_tz

> $i_tz

> $c_tzt

> $i_tzt

> $c_tztt

> $i_tztt

> $c_tzls

> $i_tzls

> END_OF_DATA

>  

> Installation of system tables failed!

>  

> Examine the logs in /var/lib/mysql for more

> information.

> You can also try to start the mysqld daemon with:

> /usr/sbin/mysqld --skip-grant &

> You can use the command line tool

> /usr/bin/mysql to connect to the mysql

> database and look at the grant tables:

>  

> shell> /usr/bin/mysql -u root mysql

> mysql> show tables

>  

> Try 'mysqld --help' if you have problems with paths.

> Using --log

> gives you a log in /var/lib/mysql that may be helpful.

>  

> The latest information about MySQL is available on the

> web at

> http://www.mysql.com

> Please consult the MySQL manual section: 'Problems

> running mysql_install_db',

> and the manual section that describes problems on your

> OS.

> Another information source is the MySQL email archive.

> Please check all of the above before mailing us!

> And if you do mail us, you MUST use the

> /usr/bin/mysqlbug script!

> chown: `mysql:mysql': invalid user

> Starting MySQL..ERROR!

> 

>  I'm totally confused. Since i'm new to Linux i can't

> find the errors on that.

> 

>  I'm waiting for ur reply.

> 

> Thanks and Regards,

>  Ashok.

> 

> 

> 

> 

> --- Gleb Paharenko <[EMAIL PROTECTED]> wrote:

> 

> 

>>Hello.

>>

>>Use Linux x86 RPM downloads for 4.1.13: Server,

>>Client programs, Libraries and

>>header files, Dynamic client libraries. See:

>>

>>http://dev.mysql.com/doc/mysql/en/which-version.html

>>

>>

>>

>>Ashok Kumar <[EMAIL PROTECTED]> wrote:

>>

>>>Dear Sir/Madam,

>>>I'm totally strugling on choosing the mysql

>>>distribution for my "RedHat Linux9.0". My system

>>>config is as follows.

>>>

> 

> 

> 

> 

>   

> 

> Start your day with Yahoo! - make it your home page 

> http://www.yahoo.com/r/hs 

>  



-- 
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: concat function problems

[averyanov]

>> Hello Nuno,
>> 
>> Tuesday, July 26, 2005, 8:53:33 PM, you wrote:
>> 
>> 
>>>[EMAIL PROTECTED] wrote:
>>>(...)
>>>
When i try to execute the following query 
>>>
>>>(...)
>>>
i get this error:

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:6
Current database: test
ERROR 2013 (HY000): Lost connection to MySQL server during query


but if i change my query to this one everything is fine
SELECT 
COUNT(DISTINCT field1) as value1,
CONCAT(field2, '') as value2
FROM 
table1,
table2
GROUP 
BY value2

(here CONCAT(field2, '-') is replaced with CONCAT(field2, '') )

does anyone know what the matter is?
>> 
>> 
>>>Did you try to make the first query for a second time, when you execute
>>>the second query? It seems that the server abords or closes the 
>>>connection meanwhile. Did you waited too long to write/send the query?
>> 
>> Each time i try to execute the 'buggy' query the server crashet.
>> It crashes IMMEDIATELY (without any human-perceptible timeout)
> 
> Can you provide the output of the logs?

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=16777216
read_buffer_size=258048
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 = 92783 
K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

050727 10:39:41  mysqld restarted



"It is possible that mysqld could use up to 92783 K bytes of memory."
I don't think the problem has anything to do with available memory
size for
a) there is enough free memory
b) if not there is a good piece of swap
c) even if i suggest the problem is in insufficient memory it is not
clear why so much is required for execution of such a simple query


> 
>>>Check this for more help
>>>http://dev.mysql.com/doc/mysql/en/gone-away.html
> 
> Does following the steps in that page helps?
> 
>>>Try this query and output the result
>> 
>> 
>>>SHOW VARIABLES LIKE '%time%';
>> 
>> mysql> SHOW VARIABLES LIKE '%time%';
>> +--+---+
>> | Variable_name| Value |
>> +--+---+
>> | connect_timeout  | 5 |
>> | datetime_format  | %Y-%m-%d %H:%i:%s |
>> | delayed_insert_timeout   | 300   |
>> | flush_time   | 0 |
>> | innodb_lock_wait_timeout | 50|
>> | interactive_timeout  | 28800 |
>> | long_query_time  | 10|
>> | net_read_timeout | 30|
>> | net_write_timeout| 60|
>> | slave_net_timeout| 3600  |
>> | slow_launch_time | 2 |
>> | sync_replication_timeout | 0 |
>> | system_time_zone | MSD   |
>> | time_format  | %H:%i:%s  |
>> | time_zone| SYSTEM|
>> | wait_timeout | 28800 |
>> +--+---+
>> 16 rows in set (0.00 sec)
> 
> The variables seems OK.



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

Re: concat function problems

[Nuno Pereira]

Hello Averyanov,

[EMAIL PROTECTED] wrote:

Hello Nuno,

Tuesday, July 26, 2005, 8:53:33 PM, you wrote:



[EMAIL PROTECTED] wrote:
(...)

When i try to execute the following query 


(...)


i get this error:

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:6
Current database: test
ERROR 2013 (HY000): Lost connection to MySQL server during query


but if i change my query to this one everything is fine
SELECT 
   COUNT(DISTINCT field1) as value1,

   CONCAT(field2, '') as value2
FROM 
   table1,

   table2
GROUP 
   BY value2


(here CONCAT(field2, '-') is replaced with CONCAT(field2, '') )

does anyone know what the matter is?




Did you try to make the first query for a second time, when you execute
the second query? It seems that the server abords or closes the 
connection meanwhile. Did you waited too long to write/send the query?


Each time i try to execute the 'buggy' query the server crashet.
It crashes IMMEDIATELY (without any human-perceptible timeout)


Can you provide the output of the logs?


Check this for more help
http://dev.mysql.com/doc/mysql/en/gone-away.html


Does following the steps in that page helps?


Try this query and output the result




SHOW VARIABLES LIKE '%time%';


mysql> SHOW VARIABLES LIKE '%time%';
+--+---+
| Variable_name| Value |
+--+---+
| connect_timeout  | 5 |
| datetime_format  | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout   | 300   |
| flush_time   | 0 |
| innodb_lock_wait_timeout | 50|
| interactive_timeout  | 28800 |
| long_query_time  | 10|
| net_read_timeout | 30|
| net_write_timeout| 60|
| slave_net_timeout| 3600  |
| slow_launch_time | 2 |
| sync_replication_timeout | 0 |
| system_time_zone | MSD   |
| time_format  | %H:%i:%s  |
| time_zone| SYSTEM|
| wait_timeout | 28800 |
+--+---+
16 rows in set (0.00 sec)


The variables seems OK.

Next time reply TO THE LIST, others could help.

--
Nuno Pereira
email: [EMAIL PROTECTED]

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

Re: Problem Escaping a Boolean Query

[Dobromir Velev]

I ran into this problem a few months ago and the only workaround  I could 
think of was to escape the quotes in the table with """. Then your query 
should be something like this

select * from feeds where MATCH(feed_title, feed_content) AGAINST('"Sean
"P. Diddy" +Combs"' IN BOOLEAN MODE) order by feed_date DESC


A diiferent solution is to put  a "+" sign before every word in the search 
phrase like 

select * from feeds where MATCH(feed_title, feed_content) AGAINST('+Sean
+"P. Diddy" +Combs' IN BOOLEAN MODE) order by feed_date DESC

but it is not as accurate and returns more irrelevant results

HTH
-- 
Dobromir Velev
[EMAIL PROTECTED]
http://www.websitepulse.com/

On Wednesday 27 July 2005 03:47, Blogfreaks.com wrote:
> I'm using a boolean query to perform an exact match on musicians within a
> text field. However, if the musician's name contains a quote, I get
> inaccurate results. For example, this query works fine:
>
> select * from feeds where MATCH(feed_title, feed_content) AGAINST('"Tom
> Petty"' IN BOOLEAN MODE) order by feed_date DESC
>
> The above query returns all feeds referencing the phrase "Tom Petty". I run
> into a problem when the musician has quotes in their name:
>
> select * from feeds where MATCH(feed_title, feed_content) AGAINST('"Sean
> \"P. Diddy\" Combs"' IN BOOLEAN MODE) order by feed_date DESC
>
> I'm trying to escape "Sean "P. Diddy" Combs" with backslashes, but it
> doesn't work. This query returns every single row in my table, and it takes
> about 11 seconds to run. The other queries take a fraction of a second.
>
> Any help would be greatly appreciated. Thanks!
>
> Shaun
> http://www.blogfreaks.com


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

Re: "port" option ignored by commands

[Wolfram Kraus]

[EMAIL PROTECTED] wrote:

Hi,


using mysql  4.0.22-standard-log.
one instance on port 3307
one instance on port 3306
(each binary in its own independent directory)

unix 'root' account submits following commands and connects to mysql on default 
3306 port instead of awaited 3307 port :
~mysqld/bin/mysql  --port=3307 -p
 ~mysqld/bin/mysqldump  -u root -p demo -a --add-drop-table --add-locks --opt   
-P3307> /tmp/demotest.sql
using '--port'  or '-P' gives the same result.
This was tested without any .my.cnf file.

Then I made a test with a config file :
~mysqld/bin/mysql  --defaults-extra-file=my3307.cnf
[client]
password   = mypassword
port= 3307
This file was read : no password prompt. But I accessed the instance on port 
3306.

( unix 'mysqld' account accesses properly the 3307 instance with option 
specified in .my.cnf file)


What am I missing ? 
Is that a known bug ? similar to bug#5792 ?


Thanks for your help,


Andrew

Use the -h option for the host, otherwise mysql tries to connect via 
socket:

~mysqld/bin/mysql  --port=3307 -p -h 127.0.0.1

HTH,
Wolfram


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

"port" option ignored by commands

[amailp]

Hi,


using mysql  4.0.22-standard-log.
one instance on port 3307
one instance on port 3306
(each binary in its own independent directory)

unix 'root' account submits following commands and connects to mysql on default 
3306 port instead of awaited 3307 port :
~mysqld/bin/mysql  --port=3307 -p
 ~mysqld/bin/mysqldump  -u root -p demo -a --add-drop-table --add-locks --opt   
-P3307> /tmp/demotest.sql
using '--port'  or '-P' gives the same result.
This was tested without any .my.cnf file.

Then I made a test with a config file :
~mysqld/bin/mysql  --defaults-extra-file=my3307.cnf
[client]
password   = mypassword
port= 3307
This file was read : no password prompt. But I accessed the instance on port 
3306.

( unix 'mysqld' account accesses properly the 3307 instance with option 
specified in .my.cnf file)


What am I missing ? 
Is that a known bug ? similar to bug#5792 ?

Thanks for your help,


Andrew



-
NetCourrier, votre bureau virtuel sur Internet : Mail, Agenda, Clubs, Toolbar...
Web/Wap : www.netcourrier.com
Téléphone/Fax : 08 92 69 00 21 (0,34 € TTC/min)
Minitel: 3615 NETCOURRIER (0,16 € TTC/min)


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

Re: all user command

[Dobromir Velev]

I think what you are looking for is 

show processlist;

http://dev.mysql.com/doc/mysql/en/show-processlist.html

You could also use Jeremy Zawodny's mytop 
(http://jeremy.zawodny.com/mysql/mytop/) or any of the MySQL administraion 
software available on the Internet



On Wednesday 27 July 2005 04:21, Joeffrey Betita wrote:

> hello
>   what command should i type to see all the user connected to the database.
> thank you very much.
>
>
>
>
> rgds,
> Joeffrey

-- 
Dobromir Velev
[EMAIL PROTECTED]
http://www.websitepulse.com/

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

Re: InnoDB Netapp Snapshot

[Dobromir Velev]

Hi,
This procedure works for me and until now I haven't had any problems with it. 
You should b aware that if your database is under heavy load you might 
experience problems with transactions timing out due to the lock.

HTH

On Wednesday 27 July 2005 09:14, Jeff Richards wrote:
> Hi,
>
> I need a procedure that will allow me to take an online (i.e. database
> up) Netapp Snapshot of a large InnoDB-based database. Could someone
> please confirm that this is a valid way of making a Snapshot:
>
> * Issue a "flush tables with read lock;"
> * Issue a "flush logs;"
> * Create the Snapshot
> * Issue an "unlock tables;"
>
> The reason I ask is that this list is the only place I can find
> reference to this procedure. Everywhere else talks about either
> mysqldump or the InnoDB Hot Backup utility for making online backups.
>
> Thanks in advance!
>
> Jeff
>
> --
> Jeff Richards
> Consulting Architect
> Openwave Systems Asia Pacific
> +61 415 638757

-- 
Dobromir Velev
[EMAIL PROTECTED]
http://www.websitepulse.com/

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

Re: malloc troubles on 64-bit machine

[Joerg Bruehe]

Hi Jigal, all;


Jigal van Hemert wrote:

Hi Joerg,

From: "Joerg Bruehe"


Jigal van Hemert wrote:


050726 14:13:12  mysqld started
050726 14:13:12  InnoDB: Error: cannot allocate 7340048384 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 78086952 bytes. Operating system errno: 12


On my machine (Linux: SuSE 9.1), I have this line in
/usr/include/asm-generic/errno-base.h :
   #define ENOMEM  12  /* Out of memory */



And perror 12 also produces a similar error description.



So it looks like some address space (paging area?) was not yet free when
the restart was attempted. Maybe the MySQL server had not yet fully
terminated?



MySQL server was terminated; at least it didn't show up in the output of the
ps-command.


Hmm.
I do not know exactly which speedup optimizations might be taken in 
Fedora Core 4 (as mentioned in your first posting) in general, or in a 
64 bit version specifically, so I am speculating:


A running MySQL server as configured by you, with 7 GB buffer pool, will 
occupy substantial amounts of RAM, probably backed in the "swap area" 
(even though this is really a paging area). When the process terminates, 
all its resources need to be freed, including flushing files, closing 
file descriptors, and releasing these 7 GB. This may take some time.


Consider that there are file systems that delay writes in order to 
optimize disk I/O and to favor reads on which other processes might be 
waiting. I suspect that similar strategies might be used on the page device.


IOW: I doubt that the removal of a process from "ps" output implies that 
all its resources have already been freed, and are available.
I admit that the Linux kernel should detect such a situation and delay 
the new request (rather than reject it) as the scarce resources are just 
getting available, but maybe this is not (yet) done?




It doesn't happen all the time; the server was running for a few days now.
We have never encountered such a situation on a 32-bit machine yet. You
could simply terminate MySQL and start it immediately.


Well, on a 32 bit machine the areas are smaller, so freeing them should 
be faster.




Can memory get fragmented in some way after it is allocated?


AFAIK, this should not happen since Linux is a paging system, not swapping.
Of course I can imagine (RAM or paging space) management strategies that 
try to keep areas continuous, to allow larger I/O transfers, but IMHO 
these should not be taken so absolute that they delay operation.



All in all, I suspect that with your growing storage sizes you need 
growing amounts of time to release them.
Even though hardware gets faster, resource consumption manages to grow 
at at least the same rate ;-)


Have you ever tried to wait a bit after a failing restart and then 
attempt it again, rather than rebooting?



Sorry I can not give a more concrete help,
Jörg

--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com

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

Re: Where on count(*)

[Peter Brawley]

Pupeno

SELECT `Plans`.`id`, `Plans`.`name`, count(*) as 'count' FROM `Plans` JOIN 
`TechsPerPlan` ON `Plans`.`id` = `TechsPerPlan`.`plan` WHERE 
`TechsPerPlan`.`id` IN (17, 48, 54, 64, 75, 13, 30, 37, 45, 55, 65, 76, 11, 
33, 46, 58, 68, 80) AND count >= 3 GROUP BY `Plans`.`id`



but it selected only those with less that 3, what I am doing wrong ?


Column aliases and aggregate results like Count() are resolved after the 
Where clause so they can't occur in it. Use Having instead.


PB

-

Pupeno wrote:


I have esentially this query (the list of integers may differ):

SELECT `Plans`.`id`, `Plans`.`name`, count(*) as 'count' FROM `Plans` JOIN 
`TechsPerPlan` ON `Plans`.`id` = `TechsPerPlan`.`plan` WHERE 
`TechsPerPlan`.`id` IN (17, 48, 54, 64, 75, 13, 30, 37, 45, 55, 65, 76, 11, 
33, 46, 58, 68, 80) GROUP BY `Plans`.`id`


Of that result I want those with count bigger than N (being N a number, like 
3), I tried this:


SELECT `Plans`.`id`, `Plans`.`name`, count(*) as 'count' FROM `Plans` JOIN 
`TechsPerPlan` ON `Plans`.`id` = `TechsPerPlan`.`plan` WHERE 
`TechsPerPlan`.`id` IN (17, 48, 54, 64, 75, 13, 30, 37, 45, 55, 65, 76, 11, 
33, 46, 58, 68, 80) AND count >= 3 GROUP BY `Plans`.`id`


but it selected only those with less that 3, what I am doing wrong ?

Thanks
 




--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.9.5/58 - Release Date: 7/25/2005


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