RE: Within-group aggregate query help please - customers and latest subscription row

[Hal�sz S�ndor]

 2011/10/24 16:31 -0700, Daevid Vincent 
  WHERE cs.customer_id = 7
GROUP BY customer_id

Well, the latter line is now redundant.

How will you make the '7' into a parameter?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Within-group aggregate query help please - customers and latest subscription row

[Daevid Vincent]

Okay, it seems I am learning... slowly...

So there needs to be a second WHERE in the sub-select...

To get ONE customer's last subscription (0.038s):

SELECT 
c.customer_id,
c.email,
c.name,
c.username,
s.subscription_id,
s.`date`
FROM
customers AS c 
INNER JOIN customers_subscriptions AS s 
ON c.customer_id = s.customer_id 
INNER JOIN 
(SELECT 
MAX(`date`) AS LastDate,
customer_id 
FROM
customers_subscriptions AS cs 
  WHERE cs.customer_id = 7
GROUP BY customer_id
) AS `x`
ON s.customer_id = x.customer_id 
AND s.date = x.LastDate
WHERE c.customer_id = 7;

To get ALL customers and their last subscription row (1m:28s)

SELECT 
c.customer_id,
c.email,
c.name,
c.username,
s.subscription_id,
s.`date`
FROM
customers AS c 
INNER JOIN customers_subscriptions AS s 
ON c.customer_id = s.customer_id 
INNER JOIN 
(SELECT 
MAX(`date`) AS LastDate,
customer_id 
FROM
customers_subscriptions AS cs 
  GROUP BY customer_id
) AS `x`
ON s.customer_id = x.customer_id 
AND s.date = x.LastDate
ORDER BY customer_id LIMIT 10;

Thanks to "you know who you are" for pointing me in the right direction. 

Hopefully this helps someone else.

d.

> -Original Message-
> From: Daevid Vincent [mailto:dae...@daevid.com]
> Sent: Monday, October 24, 2011 4:06 PM
> To: mysql@lists.mysql.com
> Subject: RE: Within-group aggregate query help please - customers and
latest
> subscription row
> 
> A kind (and shy) soul replied to me off list and suggested this solution,
> however,
> this takes 28 seconds (that's for a single customer_id, so this is not
going
> to scale).
> Got any other suggestions? :-)
> 
> SELECT
> c.customer_id,
> c.email,
> c.name,
> c.username,
> s.subscription_id,
> s.`date`
> FROM
> customers AS c
> INNER JOIN customers_subscriptions AS s
> ON c.customer_id = s.customer_id
> INNER JOIN
> (SELECT
> MAX(`date`) AS LastDate,
> customer_id
> FROM
> customers_subscriptions AS cs
> GROUP BY customer_id) AS `x`
> ON s.customer_id = x.customer_id
> AND s.date = x.LastDate
> WHERE c.customer_id = 7;
> 
> There are 781,270 customers (nearly 1 million) and  1,018,092
> customer_subscriptions.
> 
> Our tables have many indexes on pretty much every column and for sure the
> ones we use here.
> 
> EXPLAIN says:
> 
> id  select_type  table   typepossible_keys key
> key_len  refrows  Extra
> --  ---  --  --    ---
> ---  --  ---  ---
>  1  PRIMARY  c   const   PRIMARY   PRIMARY  4
> const 1
>  1  PRIMARY  s   ref date,customer_id  customer_id  4
> const 2
>  1  PRIMARYALL (NULL)(NULL)
> (NULL)   (NULL)   781265  Using where
>  2  DERIVED  cs  ALL (NULL)(NULL)
> (NULL)   (NULL)  1018092  Using temporary; Using filesort
> 
> > -Original Message-
> > From: Daevid Vincent [mailto:dae...@daevid.com]
> > Sent: Monday, October 24, 2011 1:46 PM
> > To: mysql@lists.mysql.com
> > Subject: Within-group aggregate query help please - customers and latest
> > subscription row
> >
> > I know this is a common problem, and I've been struggling with it for a
> full
> > day now but I can't get it.
> >
> > I also tried a few sites for examples:
> > http://www.artfulsoftware.com/infotree/queries.php#101
> >
>
http://forums.devarticles.com/general-sql-development-47/select-max-datetime
> > -problem-10210.html
> >
> > Anyways, pretty standard situation:
> >
> > CREATE TABLE `customers` (
> >   `customer_id` int(10) unsigned NOT NULL auto_increment,
> >   `email` varchar(64) NOT NULL default '',
> >   `name` varchar(128) NOT NULL default '',
> >   `username` varchar(32) NOT NULL,
> > ...
> > );
> >
> > CREATE TABLE `customers_subscriptions` (
> >   `subscriptio

RE: Within-group aggregate query help please - customers and latest subscription row

[Daevid Vincent]

A kind (and shy) soul replied to me off list and suggested this solution,
however,
this takes 28 seconds (that's for a single customer_id, so this is not going
to scale). 
Got any other suggestions? :-)

SELECT 
c.customer_id,
c.email,
c.name,
c.username,
s.subscription_id,
s.`date`
FROM
customers AS c 
INNER JOIN customers_subscriptions AS s 
ON c.customer_id = s.customer_id 
INNER JOIN 
(SELECT 
MAX(`date`) AS LastDate,
customer_id 
FROM
customers_subscriptions AS cs 
GROUP BY customer_id) AS `x`
ON s.customer_id = x.customer_id 
AND s.date = x.LastDate 
WHERE c.customer_id = 7;

There are 781,270 customers (nearly 1 million) and  1,018,092
customer_subscriptions.

Our tables have many indexes on pretty much every column and for sure the
ones we use here.

EXPLAIN says:

id  select_type  table   typepossible_keys key
key_len  refrows  Extra  
--  ---  --  --    ---
---  --  ---  ---
 1  PRIMARY  c   const   PRIMARY   PRIMARY  4
const 1 
 1  PRIMARY  s   ref date,customer_id  customer_id  4
const 2 
 1  PRIMARYALL (NULL)(NULL)
(NULL)   (NULL)   781265  Using where
 2  DERIVED  cs  ALL (NULL)(NULL)
(NULL)   (NULL)  1018092  Using temporary; Using filesort

> -Original Message-
> From: Daevid Vincent [mailto:dae...@daevid.com]
> Sent: Monday, October 24, 2011 1:46 PM
> To: mysql@lists.mysql.com
> Subject: Within-group aggregate query help please - customers and latest
> subscription row
> 
> I know this is a common problem, and I've been struggling with it for a
full
> day now but I can't get it.
> 
> I also tried a few sites for examples:
> http://www.artfulsoftware.com/infotree/queries.php#101
>
http://forums.devarticles.com/general-sql-development-47/select-max-datetime
> -problem-10210.html
> 
> Anyways, pretty standard situation:
> 
> CREATE TABLE `customers` (
>   `customer_id` int(10) unsigned NOT NULL auto_increment,
>   `email` varchar(64) NOT NULL default '',
>   `name` varchar(128) NOT NULL default '',
>   `username` varchar(32) NOT NULL,
>   ...
> );
> 
> CREATE TABLE `customers_subscriptions` (
>   `subscription_id` bigint(12) unsigned NOT NULL default '0',
>   `customer_id` int(10) unsigned NOT NULL default '0',
>   `date` date NOT NULL default '-00-00',
>   ...
> );
> 
> I want to show a table where I list out the ID, email, username, and LAST
> SUBSCRIPTION.
> 
> I need this data in TWO ways:
> 
> The FIRST way, is with a query JOINing the two tables so that I can easily
> display that HTML table mentioned. That is ALL customers and the latest
> subscription they have.
> 
> The SECOND way is when I drill into the customer, I already know the
> customer_id and so don't need to JOIN with that table, I just want to get
> the proper row from the customers_subscriptions table itself.
> 
> SELECT * FROM `customers_subscriptions` WHERE customer_id = 7 ORDER BY
> `date` DESC;
> 
> subscription_id  processor  customer_id  date
> ---  -  ---  --
>   134126370  chargem  7  2005-08-04
>  1035167192  billme   7  2004-02-08
> 
> SELECT MAX(`date`) FROM `customers_subscriptions` WHERE customer_id = 7
> GROUP BY customer_id;
> 
> gives me 2005-08-04 obviously, but as you all know, mySQL completely takes
a
> crap on your face when you try what would seem to be the right query:
> 
> SELECT subscription_id, MAX(`date`) FROM `customers_subscriptions` WHERE
> customer_id = 7 GROUP BY customer_id;
> 
> subscription_id  MAX(`date`)
> ---  ---
>  1035167192  2005-08-04
> 
> Notice how I have the correct DATE, but the wrong subscription_id.
> 
> In the example web sites above, they seem to deal more with finding the
> MAX(subscription_id), which in my case will not work.
> 
> I need the max DATE and the corresponding row (with matching
> subscription_id).
> 
> Thanks,
> 
> d


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Within-group aggregate query help please - customers and latest subscription row

[Daevid Vincent]

I know this is a common problem, and I've been struggling with it for a full
day now but I can't get it. 

I also tried a few sites for examples:
http://www.artfulsoftware.com/infotree/queries.php#101
http://forums.devarticles.com/general-sql-development-47/select-max-datetime
-problem-10210.html

Anyways, pretty standard situation:

CREATE TABLE `customers` (
  `customer_id` int(10) unsigned NOT NULL auto_increment,
  `email` varchar(64) NOT NULL default '',
  `name` varchar(128) NOT NULL default '',
  `username` varchar(32) NOT NULL,
...
);

CREATE TABLE `customers_subscriptions` (
  `subscription_id` bigint(12) unsigned NOT NULL default '0',
  `customer_id` int(10) unsigned NOT NULL default '0',
  `date` date NOT NULL default '-00-00',
  ...
);

I want to show a table where I list out the ID, email, username, and LAST
SUBSCRIPTION.

I need this data in TWO ways:

The FIRST way, is with a query JOINing the two tables so that I can easily
display that HTML table mentioned. That is ALL customers and the latest
subscription they have.

The SECOND way is when I drill into the customer, I already know the
customer_id and so don't need to JOIN with that table, I just want to get
the proper row from the customers_subscriptions table itself.

SELECT * FROM `customers_subscriptions` WHERE customer_id = 7 ORDER BY
`date` DESC;

subscription_id  processor  customer_id  date 
---  -  ---  --  
  134126370  chargem  7  2005-08-04  
 1035167192  billme   7  2004-02-08  

SELECT MAX(`date`) FROM `customers_subscriptions` WHERE customer_id = 7
GROUP BY customer_id;

gives me 2005-08-04 obviously, but as you all know, mySQL completely takes a
crap on your face when you try what would seem to be the right query:

SELECT subscription_id, MAX(`date`) FROM `customers_subscriptions` WHERE
customer_id = 7 GROUP BY customer_id;

subscription_id  MAX(`date`)
---  ---
 1035167192  2005-08-04 
 
Notice how I have the correct DATE, but the wrong subscription_id.

In the example web sites above, they seem to deal more with finding the
MAX(subscription_id), which in my case will not work.

I need the max DATE and the corresponding row (with matching
subscription_id).

Thanks,

d


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Fwd: Query help please

[Tompkins Neil]

I wondered if anyone can help me ?  Do you need any further information ?

Cheers
Neil

-- Forwarded message --
From: Tompkins Neil 
Date: Thu, Sep 23, 2010 at 9:49 AM
Subject: Query help please
To: "[MySQL]" 


Hi all,

I've the following query :

SELECT fixtures_results.seasons_id ,
home_teams_id AS teams_id ,
1 AS home ,0 AS away ,
(SELECT SUM(goals) FROM players_appearances WHERE
fixtures_results.fixtures_results_id =
players_appearances.fixtures_results_id AND players_appearances.teams_id =
home_teams_id) AS home_goals_aa,
IF(home_goals > away_goals, 1, 0) AS won_home ,
IF(home_goals = away_goals, 1, 0) AS drawn_home ,
IF(home_goals < away_goals, 1, 0) AS lost_home ,
home_goals AS scored_home ,
away_goals AS conceded_home ,
0 AS won_away ,
0 AS drawn_away ,
0 AS lost_away ,
0 AS scored_away ,
0 AS conceded_away
FROM fixtures_results
WHERE fixtures_results.competitions_id = 1
AND fixtures_results.seasons_id = 1
AND fixtures_results.status = 'approved'

Basically I have a table called player_appearances which contains a SUM of
goals for each fixture for the home and away team.  How can I use this SUM
called "home_goals_aa", in my logic like IF(home_goals > away_goals, 1, 0)
AS won_home ,
IF(home_goals = away_goals, 1, 0) AS drawn_home ,
IF(home_goals < away_goals, 1, 0) AS lost_home ,

Cheers
Neil

Query help please

[Tompkins Neil]

Hi all,

I've the following query :

SELECT fixtures_results.seasons_id ,
home_teams_id AS teams_id ,
1 AS home ,0 AS away ,
(SELECT SUM(goals) FROM players_appearances WHERE
fixtures_results.fixtures_results_id =
players_appearances.fixtures_results_id AND players_appearances.teams_id =
home_teams_id) AS home_goals_aa,
IF(home_goals > away_goals, 1, 0) AS won_home ,
IF(home_goals = away_goals, 1, 0) AS drawn_home ,
IF(home_goals < away_goals, 1, 0) AS lost_home ,
home_goals AS scored_home ,
away_goals AS conceded_home ,
0 AS won_away ,
0 AS drawn_away ,
0 AS lost_away ,
0 AS scored_away ,
0 AS conceded_away
FROM fixtures_results
WHERE fixtures_results.competitions_id = 1
AND fixtures_results.seasons_id = 1
AND fixtures_results.status = 'approved'

Basically I have a table called player_appearances which contains a SUM of
goals for each fixture for the home and away team.  How can I use this SUM
called "home_goals_aa", in my logic like IF(home_goals > away_goals, 1, 0)
AS won_home ,
IF(home_goals = away_goals, 1, 0) AS drawn_home ,
IF(home_goals < away_goals, 1, 0) AS lost_home ,

Cheers
Neil

Re: Query help, please..

[Peter Brawley]

Anders,

>I also want to find out the user's position relative to others 
depending on the result.


For a given pUserID, something like this?

SELECT userid,result,rank
FROM (
 SELECT o1.userid,o1.result,COUNT(o2.result) AS rank
 FROM object o1
 JOIN object o2 ON o1.result < o2.result OR (o1.result=o2.result AND 
o1.userid=o2.userid)

 GROUP BY o1.userid,o1.result
)
WHERE userid = pUserID;

PB

-

Anders Norrbring wrote:
I'm looking at a situation I haven't run into before, and I'm a bit 
puzzled by it.


I have this table structure:

Table USERS: userid, class
Table OBJECT: userid, class, result

Now I want to query the database for a certain user's result in a 
specified class, which is very, very easy. No problems.


But, I also want to find out the user's position relative to others 
depending on the result.


So, if the specified user's result is the 9:th best of all of the 
users, I want to have a reply from the DB query that say he has 
position number 9.


I really can't figure out how to do that... Somehow I have to make 
MySQL calculate the position based on the value in the result column.





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

Re: Query help, please..

[Jason Pruim]

On Dec 11, 2007, at 10:46 AM, Rob Wultsch wrote:


On Dec 11, 2007 8:38 AM, Anders Norrbring <[EMAIL PROTECTED]> wrote:

I'm looking at a situation I haven't run into before, and I'm a bit
puzzled by it.

I have this table structure:

Table USERS: userid, class
Table OBJECT: userid, class, result

Now I want to query the database for a certain user's result in a
specified class, which is very, very easy. No problems.

But, I also want to find out the user's position relative to others
depending on the result.

So, if the specified user's result is the 9:th best of all of the  
users,
I want to have a reply from the DB query that say he has position  
number 9.


I really can't figure out how to do that... Somehow I have to make  
MySQL

calculate the position based on the value in the result column.


Take a look at http://arjen-lentz.livejournal.com/55083.html . Very
similar ideas in play, though you also have a join.

The basic idea is that you do a count on the number of users that have
a lower score.


Is there any reason you wouldn't want to count the people in front of  
you and add 1 to get your place in line? It seems like depending on  
where you are, that may be a shorter number to count :)


But I don't know anything about how to do stuff off of separate tables  
yet still trying to grasp that :)






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




--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
[EMAIL PROTECTED]



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

Re: Query help, please..

[Rob Wultsch]

On Dec 11, 2007 8:38 AM, Anders Norrbring <[EMAIL PROTECTED]> wrote:
> I'm looking at a situation I haven't run into before, and I'm a bit
> puzzled by it.
>
> I have this table structure:
>
> Table USERS: userid, class
> Table OBJECT: userid, class, result
>
> Now I want to query the database for a certain user's result in a
> specified class, which is very, very easy. No problems.
>
> But, I also want to find out the user's position relative to others
> depending on the result.
>
> So, if the specified user's result is the 9:th best of all of the users,
> I want to have a reply from the DB query that say he has position number 9.
>
> I really can't figure out how to do that... Somehow I have to make MySQL
> calculate the position based on the value in the result column.

Take a look at http://arjen-lentz.livejournal.com/55083.html . Very
similar ideas in play, though you also have a join.

The basic idea is that you do a count on the number of users that have
a lower score.

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

Query help, please..

[Anders Norrbring]

I'm looking at a situation I haven't run into before, and I'm a bit 
puzzled by it.


I have this table structure:

Table USERS: userid, class
Table OBJECT: userid, class, result

Now I want to query the database for a certain user's result in a 
specified class, which is very, very easy. No problems.


But, I also want to find out the user's position relative to others 
depending on the result.


So, if the specified user's result is the 9:th best of all of the users, 
I want to have a reply from the DB query that say he has position number 9.


I really can't figure out how to do that... Somehow I have to make MySQL 
calculate the position based on the value in the result column.



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

Re: DELETE query help please?

[Yoge]

This should work
DELETE Item FROM Item,ItemTag WHERE Item.ProductID =ItemTag.ItemID AND 
ItemTag.TagID = '168'


Mark Kelly wrote:

Hi

I want to delete from the 'Item' table all the items identified by the 
folowing query:


SELECT 
Item.ProductID 
FROM 
Item, ItemTag 
WHERE 
ItemTag.TagID = '168' 
AND 
ItemTag.ItemID = Item.ProductID;


but I'm not sure how to go about it. Can anyone help?

Thanks

Mark


  


--
Yoge,
AdventNet, Inc.
925-965-6528
[EMAIL PROTECTED]
site24x7.com


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

Re: DELETE query help please?

[gary]

The following query should work if I understand what you're attempting 
correctly. Use at your own risk though ;)


DELETE FROM Item USING Item, ItemTag WHERE ItemTag.ItemID = 
Item.ProductID AND ItemTag.TagID = '168';


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

Re: DELETE query help please?

[Mark Kelly]

Hi.

On Thursday 05 July 2007 17:35, you wrote:
> > I want to delete from the 'Item' table
> > all the items identified by the folowing query:
>
> If you have MySQL 5+, you can do it using a sub-query:
>
> DELETE FROM
>   Item
> WHERE
>   ProductID IN (
> SELECT
>     Item.ProductID
> FROM
>     Item, ItemTag
> WHERE
>     ItemTag.TagID = '168'
> AND
>     ItemTag.ItemID = Item.ProductID
> );

I'm on 4.1 due to host restrictions, but for both versions the manual page 
on subquery syntax says

"Another restriction is that currently you cannot modify a table and select 
from the same table in a subquery. This applies to statements such as 
DELETE, INSERT, REPLACE, UPDATE"

which seems to me would stop your suggestion working. Am I misunderstanding 
(perfectly possible, as I'm a long way from expert)?

Thanks for the quick reply anyway,

Mark

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

RE: DELETE query help please?

[Chris Boget]

> I want to delete from the 'Item' table 
> all the items identified by the folowing query:

If you have MySQL 5+, you can do it using a sub-query:

DELETE FROM 
  Item 
WHERE 
  ProductID IN (
SELECT 
Item.ProductID
FROM 
Item, ItemTag
WHERE 
ItemTag.TagID = '168' 
AND 
ItemTag.ItemID = Item.ProductID
);

thnx,
Chris


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

DELETE query help please?

[Mark Kelly]

Hi

I want to delete from the 'Item' table all the items identified by the 
folowing query:

SELECT 
Item.ProductID 
FROM 
Item, ItemTag 
WHERE 
ItemTag.TagID = '168' 
AND 
ItemTag.ItemID = Item.ProductID;

but I'm not sure how to go about it. Can anyone help?

Thanks

Mark


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

Re: Reapply bin-log question( Help Please)

[Clyde Lewis - DBA]

Thanks a bunch for the suggested workaround. This method was exactly 
what I was looking for.


Thanks again.
CL

At 01:33 AM 6/20/2007, Ananda Kumar wrote:

One possible way is to spool  the contents of bin-log into a file.
mysqlbinlog oca-bin.000554 > binlog_sql.sql.

This will give you all the data present in oca-bin.000554. Then you can set
the
foreign key check to "0" at the session level and then apply the
binlog_sql.sql.

Comment our everything (all set command any other things) but not the sql's

source binlog_sql.sql.

set foreign key check back to "1".

or

from binlog_sql.sql, check the insert or update that is giving "foreign key
fail" error, know the position just before this sql and recovery till that
position and then recovery from position after this sql still till the end
of file.

regards
anandkl


On 6/20/07, Clyde Lewis - DBA <[EMAIL PROTECTED]> wrote:


Guys,

I'm attempting to reapply a number of bin-log files in a effort to
restore all changes that was made to a database. I'm performing the
following command, but continue to get a foreign key constraint error
message when doing so. Has anyone ever ran into this issue, and if
so, what is a potential workaround? A possible solution is to set
foreign key check to "0", but this from what I understand is done at
the session level and cannot be applied in my situation. Any help
would be greatly appreciated.

mysqlbinlog oca-bin.000554 | mysql -h -u -p oca
ERROR 1452 (23000) at line 125: Cannot add or update a child row: a
foreign key constraint fails (`oca/invbodytax`, CONSTRAINT
`invbodytax_ibfk_1` FOREIGN KEY (`inbtStore`, `inbtActNumber`,
`inbtRef`, `inbtItem`) REFERENCES `invbody` (`inbStore`,
`inbActNumber`, `inbRef`, `inbItem`) ON DEL)

Thanks in advance,


~
Clyde Lewis
Database Administrator




~
Clyde Lewis
Database Administrator

Re: Reapply bin-log question( Help Please)

[Ananda Kumar]

One possible way is to spool  the contents of bin-log into a file.
mysqlbinlog oca-bin.000554 > binlog_sql.sql.

This will give you all the data present in oca-bin.000554. Then you can set
the
foreign key check to "0" at the session level and then apply the
binlog_sql.sql.

Comment our everything (all set command any other things) but not the sql's

source binlog_sql.sql.

set foreign key check back to "1".

or

from binlog_sql.sql, check the insert or update that is giving "foreign key
fail" error, know the position just before this sql and recovery till that
position and then recovery from position after this sql still till the end
of file.

regards
anandkl


On 6/20/07, Clyde Lewis - DBA <[EMAIL PROTECTED]> wrote:


Guys,

I'm attempting to reapply a number of bin-log files in a effort to
restore all changes that was made to a database. I'm performing the
following command, but continue to get a foreign key constraint error
message when doing so. Has anyone ever ran into this issue, and if
so, what is a potential workaround? A possible solution is to set
foreign key check to "0", but this from what I understand is done at
the session level and cannot be applied in my situation. Any help
would be greatly appreciated.

mysqlbinlog oca-bin.000554 | mysql -h -u -p oca
ERROR 1452 (23000) at line 125: Cannot add or update a child row: a
foreign key constraint fails (`oca/invbodytax`, CONSTRAINT
`invbodytax_ibfk_1` FOREIGN KEY (`inbtStore`, `inbtActNumber`,
`inbtRef`, `inbtItem`) REFERENCES `invbody` (`inbStore`,
`inbActNumber`, `inbRef`, `inbItem`) ON DEL)

Thanks in advance,


~
Clyde Lewis
Database Administrator

Reapply bin-log question( Help Please)

[Clyde Lewis - DBA]

Guys,

I'm attempting to reapply a number of bin-log files in a effort to 
restore all changes that was made to a database. I'm performing the 
following command, but continue to get a foreign key constraint error 
message when doing so. Has anyone ever ran into this issue, and if 
so, what is a potential workaround? A possible solution is to set 
foreign key check to "0", but this from what I understand is done at 
the session level and cannot be applied in my situation. Any help 
would be greatly appreciated.


 mysqlbinlog oca-bin.000554 | mysql -h -u -p oca
ERROR 1452 (23000) at line 125: Cannot add or update a child row: a 
foreign key constraint fails (`oca/invbodytax`, CONSTRAINT 
`invbodytax_ibfk_1` FOREIGN KEY (`inbtStore`, `inbtActNumber`, 
`inbtRef`, `inbtItem`) REFERENCES `invbody` (`inbStore`, 
`inbActNumber`, `inbRef`, `inbItem`) ON DEL)


Thanks in advance,


~
Clyde Lewis
Database Administrator

Re: Bash script array from MySQL query - HELP Please!!!

[BJ Swope]

I would look at the 15th URL to see if there are specials in there that are
breaking the hash somehow.

On 5/22/07, Ben Benson <[EMAIL PROTECTED]> wrote:




I'm having problems getting a monitoring script to run.



I've put the troublesome bit in a separate script just to test, and it
goes
like this:



declare -a HNdeclares the array "HN"



HN=(`echo "SELECT url FROM hosts" | mysql --user=netmon --password=n3tm0n
--skip-column-names check_http`) runs the query and assigns each record to
a
new element in the array



echo ${#HN} echo's number of elements in array



for ((i=0;i<${#HN};i++)); do



echo ${HN[${i}]}  echo value of each element.



done



Seems simple enough yeah?! Well if I run echo "SELECT url FROM hosts" |
mysql --user=user --password=pass --skip-column-names check_http at the
command line, i get all of the records - 32. If I run the script above, it
simply refuses to put more than 14 elements in the array.



Then, to confuse it even more, if I sort the query, it gives a different
amount depending on what its sorted by!! For example, if I sort it by
'url'
it seems to generate 569 elements!



Can anyone please spot the undoubtedly obvious error I've made here?! I've
been scratching my head for days, to no avail!



Many thanks in advance,



Ben Benson







--
We are all slave to our own paradigm. -- Joshua Williams

If the letters PhD appear after a person's name, that person will remain
outdoors even after it's started raining. -- Jeff Kay

Bash script array from MySQL query - HELP Please!!!

[Ben Benson]

 

I'm having problems getting a monitoring script to run.  

 

I've put the troublesome bit in a separate script just to test, and it goes
like this:

 

declare -a HNdeclares the array "HN"

 

HN=(`echo "SELECT url FROM hosts" | mysql --user=netmon --password=n3tm0n
--skip-column-names check_http`) runs the query and assigns each record to a
new element in the array

 

echo ${#HN} echo's number of elements in array

 

for ((i=0;i<${#HN};i++)); do

 

echo ${HN[${i}]}  echo value of each element.

 

done

 

Seems simple enough yeah?! Well if I run echo "SELECT url FROM hosts" |
mysql --user=user --password=pass --skip-column-names check_http at the
command line, i get all of the records - 32. If I run the script above, it
simply refuses to put more than 14 elements in the array.

 

Then, to confuse it even more, if I sort the query, it gives a different
amount depending on what its sorted by!! For example, if I sort it by 'url'
it seems to generate 569 elements! 

 

Can anyone please spot the undoubtedly obvious error I've made here?! I've
been scratching my head for days, to no avail!

 

Many thanks in advance,

 

Ben Benson

 

Re: Help please: SELECT in binlog?

[David Precious]

Fionn Behrens wrote:

On Do, 2007-04-19 at 13:57 -0400, Jay Pipes wrote:

You can have both, AFAIK.  The general query log keeps all queries, 
including SELECTs.  Binlog only has data-modifying queries.


Thanks very much for your answer.

Maybe the fact that binlogs apparently are quite different from normal
text logs should be clearly mentioned somewhere in the docs. Especially
the mysqlbinlog manpage might be a good place to mention that SELECT
statements can not be restored with it.


I think it's already fairly clearly stated that the binlog is only for 
replaying queries which would have modified the database.  After all, 
why would you want to re-run a select query?


From the first paragraph of the manual page describing the binlog:
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

"The binary log contains all statements that update data or potentially 
could have updated it (for example, a DELETE which matched no rows). 
Statements are stored in the form of "events" that describe the 
modifications."


I agree that the mysqlbinlog manpage doesn't clearly state it, but it 
does refer you to the section of the documentation about the binlog 
which does.


Cheers

Dave P

--
David Precious
http://blog.preshweb.co.uk/

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

Re: Help please: SELECT in binlog?

[Fionn Behrens]

On Do, 2007-04-19 at 13:57 -0400, Jay Pipes wrote:

> You can have both, AFAIK.  The general query log keeps all queries, 
> including SELECTs.  Binlog only has data-modifying queries.

Thanks very much for your answer.

Maybe the fact that binlogs apparently are quite different from normal
text logs should be clearly mentioned somewhere in the docs. Especially
the mysqlbinlog manpage might be a good place to mention that SELECT
statements can not be restored with it.

kind regards,
Fionn



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

Re: Help please: SELECT in binlog?

[Jay Pipes]

Fionn Behrens wrote:

We recently switched to mysql5 and while we were at it we also changed
our logs from text to bin as suggested by the migration script we had
(probably created by debian people).

Now I unfortunately had to reconstruct what had happened during a faulty
run of our application and I could not get any SELECT statement from the
log!? The usual search engine run didnt bring up anything useful,

so my questions are:

1) Are the selects somwhere in the binlogs and I just have not found
   the right voodoo to make the come out?


No, no selects.  Only commands that change data are replicated, AFAIK.


2) If they are not there by default, can I configure mysqld to store
   SELECTs in a binlog?


Not that I know of.


3) If not, is the old text log all I can go back to?


You can have both, AFAIK.  The general query log keeps all queries, 
including SELECTs.  Binlog only has data-modifying queries.


Cheers,

jay

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

Help please: SELECT in binlog?

[Fionn Behrens]

We recently switched to mysql5 and while we were at it we also changed
our logs from text to bin as suggested by the migration script we had
(probably created by debian people).

Now I unfortunately had to reconstruct what had happened during a faulty
run of our application and I could not get any SELECT statement from the
log!? The usual search engine run didnt bring up anything useful,

so my questions are:

1) Are the selects somwhere in the binlogs and I just have not found
   the right voodoo to make the come out?

2) If they are not there by default, can I configure mysqld to store
   SELECTs in a binlog?

3) If not, is the old text log all I can go back to?


thanks for reading,
Fionn



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

Replication help, please

[Mikhail Berman]

Dear List,
 
As recently as last Sunday  January 14, 2007, we have enabled
replication between two servers in our organization.
The master server runs MySQL 4.1.10a, the slave runs 5.0.18.
 
Since then, we have had a number of interruptions in replication when
the slave server stopped replicating for different reasons. 
I was able to fix the problems pointed out by the error log on the slave
server, but I am witnessing strange behavior on the part of the slave. 
Every time, I look up slave status using "show slave status", I see the
value of Seconds_Behind_Master getting bigger nor smaller as one would
expect. 
I am pasting actual reports of "show slave status" at the end of this
E-mail.
 
Could anyone help me to find out why the slave reports such thing, and
how to overcome it.
 
mysql> show slave status\G;
*** 1. row ***
 Slave_IO_State: Waiting for master to send event
Master_Host: saruman
Master_User: alatarreplica
Master_Port: 3306
  Connect_Retry: 60
Master_Log_File: SB2000-bin.000139
Read_Master_Log_Pos: 857395571
 Relay_Log_File: alatar-relay-bin.05
  Relay_Log_Pos: 190740012
  Relay_Master_Log_File: SB2000-bin.000139
   Slave_IO_Running: Yes
  Slave_SQL_Running: Yes
Replicate_Do_DB: secdocs
Replicate_Ignore_DB:
 Replicate_Do_Table:
 Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
 Last_Errno: 0
 Last_Error:
   Skip_Counter: 0
Exec_Master_Log_Pos: 190663065
Relay_Log_Space: 858304045
Until_Condition: None
 Until_Log_File:
  Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File:
 Master_SSL_CA_Path:
Master_SSL_Cert:
  Master_SSL_Cipher:
 Master_SSL_Key:
  Seconds_Behind_Master: 285342
1 row in set (0.00 sec)
 
ERROR:
No query specified
 
mysql> show slave status\G;
*** 1. row ***
 Slave_IO_State: Waiting for master to send event
Master_Host: saruman
Master_User: alatarreplica
Master_Port: 3306
  Connect_Retry: 60
Master_Log_File: SB2000-bin.000139
Read_Master_Log_Pos: 857395745
 Relay_Log_File: alatar-relay-bin.05
  Relay_Log_Pos: 190740012
  Relay_Master_Log_File: SB2000-bin.000139
   Slave_IO_Running: Yes
  Slave_SQL_Running: Yes
Replicate_Do_DB: secdocs
Replicate_Ignore_DB:
 Replicate_Do_Table:
 Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
 Last_Errno: 0
 Last_Error:
   Skip_Counter: 0
Exec_Master_Log_Pos: 190663065
Relay_Log_Space: 858304221
Until_Condition: None
 Until_Log_File:
  Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File:
 Master_SSL_CA_Path:
Master_SSL_Cert:
  Master_SSL_Cipher:
 Master_SSL_Key:
  Seconds_Behind_Master: 285344
1 row in set (0.00 sec)
 
ERROR:
No query specified

 
Your help is greatly appreciated,
 
Mikhail Berman
Ives Group
 

A little sort/group by help please

[T.J. Mahaffey]

I've developed several job tracking applications here at my job.
One of them relies heavily on a series of date stamps in the main table.
Each job has about 6 milestones, each with a respective "sibling"  
column in the same table. The first sibling reflects the job's  
predicted date milestone and the second sibling gets populated with  
the *actual* date when that milestone is met. LIke this:


col1_date_predicted		col1_date_actual		col2_date_predicted		 
col2_date_actual


The "_date_actual" columns contain "-00-00" until their  
respective milestones have actually passed and is set to a real  
datestamp.
Now, when displaying the records in this table, I'm highlighting a  
row in blue if all of the *actual* dates are populated with legit  
datestamp.


My question: I can't for the life of me figure out how I might sort  
or group these all-populated rows (colored in blue) together in the  
list.

Can someone give me some guidance?
Thanks in advance.

--
T.J. Mahaffey
[EMAIL PROTECTED]




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

Re: help please, help please

[Dave Heath]

I am not able to install Mysql on my 2003 server at home.
  error 1045!!
  I get an accesd denied for user [EMAIL PROTECTED]


I need the full error, but have some questions:

1. Are you sure that you set a root password?
2. Are you using the command line client or some other administrative tool?

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

help please, help please

[albert abongwa]

I am not able to install Mysql on my 2003 server at home.
  error 1045!!
  I get an accesd denied for user [EMAIL PROTECTED]
  my firewall is off and I know it is not the port becuase when I configure it 
as 'anonymous" it works like a charm. Please help me


-
Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls.  Great rates 
starting at 1¢/min.

Re: a lil sql help please.

[John L Meyer]

m i l e s wrote:

Hi,

I have the following Query and Im a lil lost on this one

SELECT DISTINCT tbe_orders.order_id, tbe_orders.order_date,  
tbe_orders.order_piececount

FROM tbe_orders

The query produces the following results:

+++
+ order_id  + order_date  +  order_piececount +
+++
+ oid1  + 2006-07-08  +1  +
+ oid1  + 2006-07-08  +2  +
+ oid1  + 2006-07-08  +3  +
+ oid5  + 2006-07-08  +7  +
+ oid5  + 2006-07-08  +1  +
+ oid4  + 2006-07-08  +1  +
+ oid4  + 2006-07-08  +2  +
+ oid4  + 2006-07-08  +1  +
+++

This is actually right.  However, ideally what I'm wanting is this:

+++
+ order_id  + order_date  +  order_piececount +
+++
+ oid1  + 2006-07-08  +6  +
+ oid5  + 2006-07-08  +8  +
+ oid4  + 2006-07-08  +4  +
+++

Note the order_piececount column.

What do I need to do to my SQL statement to perform this action ?

My guess that I need to perform a secondary query inside the  
statement to get the computed value of order_piececount.


Anyone ?

M i l e s.

SELECT DISTINCT tbe_orders.order_id, tbe_orders.order_date,  
SUM(tbe_orders.order_piececount )

FROM tbe_orders GROUP BY order_id;



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

Re: a lil sql help please.

[Davor Dundovic]

At 18:51 9.7.2006, you wrote:

Hi,

I have the following Query and Im a lil lost on this one

SELECT DISTINCT tbe_orders.order_id, tbe_orders.order_date,
tbe_orders.order_piececount
FROM tbe_orders



SELECT tbe_orders.order_id, 
tbe_orders.order_date,  sum(tbe_orders.order_piececount)

FROM tbe_orders
GROUP BY tbe_orders.order_id

or

SELECT tbe_orders.order_id, 
tbe_orders.order_date,  sum(tbe_orders.order_piececount)

FROM tbe_orders
GROUP BY tbe_orders.order_id, tbe_orders.order_date


depending whether date matters or not.



Regards, Dundo.



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

a lil sql help please.

[m i l e s]

Hi,

I have the following Query and Im a lil lost on this one

SELECT DISTINCT tbe_orders.order_id, tbe_orders.order_date,  
tbe_orders.order_piececount

FROM tbe_orders

The query produces the following results:

+++
+ order_id  + order_date  +  order_piececount +
+++
+ oid1  + 2006-07-08  +1  +
+ oid1  + 2006-07-08  +2  +
+ oid1  + 2006-07-08  +3  +
+ oid5  + 2006-07-08  +7  +
+ oid5  + 2006-07-08  +1  +
+ oid4  + 2006-07-08  +1  +
+ oid4  + 2006-07-08  +2  +
+ oid4  + 2006-07-08  +1  +
+++

This is actually right.  However, ideally what I'm wanting is this:

+++
+ order_id  + order_date  +  order_piececount +
+++
+ oid1  + 2006-07-08  +6  +
+ oid5  + 2006-07-08  +8  +
+ oid4  + 2006-07-08  +4  +
+++

Note the order_piececount column.

What do I need to do to my SQL statement to perform this action ?

My guess that I need to perform a secondary query inside the  
statement to get the computed value of order_piececount.


Anyone ?

M i l e s.

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

Help Please: ERROR 1010 (HY000): Error dropping database (can't rmdir '.\a', errno: 41)

[zee ku]

I am using MySql 5.0.21-community Edition for widows XP. I can successfully 
connect to the MySql Server as a root and create a database 'a'. Which creates 
a directory 'a' under my mysql\data directory with a single file 'db.opt'. Now 
dropping the database 'a' gives the following error 

ERROR 1010 (HY000): Error dropping database (can't rmdir '.\a', errno: 41)

The directory mysql\data\a does not have any other files or hidden files. The 
commands I have executed are:


Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 5.0.21-community-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database a;
Query OK, 1 row affected (0.09 sec)

mysql> drop database a;
ERROR 1010 (HY000): Error dropping database (can't rmdir '.\a', errno: 41)
mysql>
-

I have searched web, lists, bugs and could not find any.

Please help. 

Thank you,
Zee

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: Help please

[Gleb Paharenko]

Hello.

Have a look here:
  http://dev.mysql.com/doc/refman/5.0/en/crashing.html

If you feel that there are too much sockets in a TIME_WAIT have a look here:
  http://dev.mysql.com/doc/refman/5.0/en/communication-errors.html


Logg, Connie A. wrote:
> Two days ago, a system that has been running fine started crashing...It could 
> be for a variety of reasons which I am researchinig. However (running mysql 
> 5.0.18) I notice the following from netstat:
> tcp0  0 iepm-bw.slac.stanford.:1000 iepm-bw.slac.stanford:38672 
> ESTABLISHED
> tcp0  0 iepm-bw.slac.stanford.:1000 iepm-bw.slac.stanford:38775 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford.:1000 iepm-bw.slac.stanford:38781 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38780 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38781 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38782 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38783 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38776 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38777 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38778 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38779 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38772 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38773 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38774 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38768 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38769 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38770 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38771 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38764 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38765 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38766 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38760 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38761 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38762 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38763 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38756 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38757 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38758 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38759 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38752 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38753 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38754 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38755 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38748 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38749 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38750 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38751 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38744 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38745 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38746 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38747 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38742 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38743 iepm-bw.slac.stanford.:1000 
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38672 iepm-bw.slac.stanford.:1000 
> ESTABLISHED
> 
> One of the messages in /var/log/messages is too many orphaned sockets.  Do 
> the above indicate orphaned sockets? I logged into mysql root and did a 'show 
> full processlist' and there were only one or two mysql processes.  Can 
> someone explain why there might be so many tcp sockets taken up to connect to 
> mysql (which is running on port 1000).
> 
> Thanks, Connie Logg
> SLAC


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
  

Re: Help please

[Kishore Jalleda]

No I don't think this indicates orphaned sockets, having many sockets for
mysql in the state TIME_WAIT state is quite normal, as a socket has to be
created for every connection and once a connection is established the socket
goes into a TIME_WAIT state( i am not sure for how long though), the 'show
processlist' only lists all the threads currently running..
JFYI
Socket: an end point for communication ( IP & Port)
Thread: quite similar to a process , a process can have multiple threads


Kishore Jalleda


On 2/1/06, Logg, Connie A. <[EMAIL PROTECTED]> wrote:
>
> Two days ago, a system that has been running fine started crashing...It
> could be for a variety of reasons which I am researchinig. However (running
> mysql 5.0.18) I notice the following from netstat:
> tcp0  0 iepm-bw.slac.stanford.:1000 iepm-bw.slac.stanford:38672
> ESTABLISHED
> tcp0  0 iepm-bw.slac.stanford.:1000 iepm-bw.slac.stanford:38775
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford.:1000 iepm-bw.slac.stanford:38781
> TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38780
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38781
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38782
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38783
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38776
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38777
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38778
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38779
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38772
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38773
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38774
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38768
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38769
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38770
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38771
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38764
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38765
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38766
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38760
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38761
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38762
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38763
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38756
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38757
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38758
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38759
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38752
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38753
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38754
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38755
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38748
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38749
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38750
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38751
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38744
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38745
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38746
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38747
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38742
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38743
> iepm-bw.slac.stanford.:1000 TIME_WAIT
> tcp0  0 iepm-bw.slac.stanford:38672
> iepm-bw.slac.stanford.:1000 ESTABLISHED
>
> One of the messages in /var/log/messages is too many orphaned sockets.  Do
> the above indicate orphaned sockets? I logged into mysql root and did a
> 'show full processlist' and there were only one or two mysql processes.  Can
> someone explain why there 

Help please

[Logg, Connie A.]

Two days ago, a system that has been running fine started crashing...It could 
be for a variety of reasons which I am researchinig. However (running mysql 
5.0.18) I notice the following from netstat:
tcp0  0 iepm-bw.slac.stanford.:1000 iepm-bw.slac.stanford:38672 
ESTABLISHED
tcp0  0 iepm-bw.slac.stanford.:1000 iepm-bw.slac.stanford:38775 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford.:1000 iepm-bw.slac.stanford:38781 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38780 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38781 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38782 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38783 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38776 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38777 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38778 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38779 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38772 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38773 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38774 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38768 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38769 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38770 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38771 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38764 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38765 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38766 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38760 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38761 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38762 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38763 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38756 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38757 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38758 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38759 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38752 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38753 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38754 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38755 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38748 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38749 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38750 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38751 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38744 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38745 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38746 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38747 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38742 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38743 iepm-bw.slac.stanford.:1000 
TIME_WAIT
tcp0  0 iepm-bw.slac.stanford:38672 iepm-bw.slac.stanford.:1000 
ESTABLISHED

One of the messages in /var/log/messages is too many orphaned sockets.  Do the 
above indicate orphaned sockets? I logged into mysql root and did a 'show full 
processlist' and there were only one or two mysql processes.  Can someone 
explain why there might be so many tcp sockets taken up to connect to mysql 
(which is running on port 1000).

Thanks, Connie Logg
SLAC

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

Re: Dropped table. . . Help Please

[Johannes Franken]

* Rick Dwyer <[EMAIL PROTECTED]> [2005-12-13 18:21 +0100]:
> I made a major mistake with MySQL 4.1.x.  While using Navicatt I 
> dropped my database when I meant to drop a table.  Other than backups 
> which are not that up to date, is there an undo?

If your mysqld writes binlogs (see "log-bin"-directive in my.cnf), you
can use mysqlbinlog to turn them into a mysqldump-style file of
SQL-statements, which you then feed to mysql.

If the dropped database contained MyISAM-tables, you might be able to
undelete those *.MYI, *.MYD and *.frm -files from your filesystem. Under
Linux you can use tools like e2undel and debugfs and certain versions of
mc.

Please let us know, if you need help with these tools.

Good luck!

-- 
Johannes Franken
 
MySQL Certified Professional
mailto:[EMAIL PROTECTED]
http://www.jfranken.de/

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

Re: Dropped table. . . Help Please

[Gleb Paharenko]

Hello.



If you database contained MyISAM tables, you should prevent any access

to the partition (or logical disk in Windows) and recover  deleted files

(*.MYI, *.MYD, *.frm). I'm not sure what to do with InnoDB tablespace

(not per-file), but certainly you should shutdown MySQL and copy ibdata

files to the safe place. Than read InnoDB parts of MySQL Internals to

find out the way to how recover your data.







Rick Dwyer wrote:

> I made a major mistake with MySQL 4.1.x.  While using Navicatt I dropped

> my database when I meant to drop a table.  Other than backups which are

> not that up to date, is there an undo?

> 

> Help here is greatly appreciated.

> 

> Thanks, I'm desperate.

> Rick

> 

> 



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

Dropped table. . . Help Please

[Rick Dwyer]

I made a major mistake with MySQL 4.1.x.  While using Navicatt I 
dropped my database when I meant to drop a table.  Other than backups 
which are not that up to date, is there an undo?


Help here is greatly appreciated.

Thanks, I'm desperate.
Rick


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

Re: Financial return calculations help please

[Jim Seymour]

On Wed, Oct 05, 2005 at 11:23:00AM -0700, Mike Wexler wrote:
> Jim Seymour wrote:
> 
> >I have researched repeatedly and cannot find an answer to the following. I
> >need to do something like the following (There is probably an easier
> >way).
> >
> >end_date - start_date = diff / start_date = return for period
> >
> >The table contains 401k investment values. Ideas, pointers, etc.? I am 
> >using
> >mysql v5.0.12 on Debian Linux.
> >
> >TIA,
> >
> >Jim
> >
> > 
> >
> Depending on how fancy you want to get, you would calculate either an 
> IRR (Internal Rate of Return) that basically says, what interest rate 
> would I need to get on the funds to end up with the same results I 
> actually achived. Note that calculating an interest rate is going to 
> require an iterative approximation.
> 

Thanks Mike,

What I was really looking for is how to structure a query in mysql to
arrive at the return on the investment. I know I completely left that
out of my first post. Is it even possible?

Thanks,

Jim Seymour

-- 
I started using something better than the "standard" back when IBM advertised
OS/2 Warp on TV. As Linux matured I made the transition from OS/2 v4 to Linux.
You don't have to accept less than you deserve.
"Use the Power of the Penguin" Registered Linux user #316735

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

Re: Financial return calculations help please

[Mike Wexler]

Jim Seymour wrote:


I have researched repeatedly and cannot find an answer to the following. I
need to do something like the following (There is probably an easier
way).

end_date - start_date = diff / start_date = return for period

The table contains 401k investment values. Ideas, pointers, etc.? I am using
mysql v5.0.12 on Debian Linux.

TIA,

Jim

 

Depending on how fancy you want to get, you would calculate either an 
IRR (Internal Rate of Return) that basically says, what interest rate 
would I need to get on the funds to end up with the same results I 
actually achived. Note that calculating an interest rate is going to 
require an iterative approximation.




http://www.investopedia.com/offsite.asp?URL=http://invest-faq.com/articles/analy-int-rate-return.html 
includes a description of the general concepts and a pointer to some 
programs, including (401-calc) that calculate IRR.


http://fox.wikis.com/wc.dll?Wiki~InternalRateOfReturn has some code that 
uses Newton-Raphson approximation to calculate IRR.


Googling for "Internal Rate of Return" can find you a lot more links.

There are also more sophisticated techniques like FMRR that are used 
when you have minimum investment amounts and a lower rate of return on 
short term investments of money waiting to accumulate the larger somes 
needed.



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

Financial return calculations help please

[Jim Seymour]

I have researched repeatedly and cannot find an answer to the following. I
need to do something like the following (There is probably an easier
way).

end_date - start_date = diff / start_date = return for period

The table contains 401k investment values. Ideas, pointers, etc.? I am using
mysql v5.0.12 on Debian Linux.

TIA,

Jim

-- 
I started using something better than the "standard" back when IBM advertised
OS/2 Warp on TV. As Linux matured I made the transition from OS/2 v4 to Linux.
You don't have to accept less than you deserve.
"Use the Power of the Penguin" Registered Linux user #316735

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

help please with SQL UPDATE

[Angela]

Hi,

I have two simple tables:

CREATE TABLE `new_stations` (
  `CD` char(3) default '',
  `STATION` varchar(17) default NULL,
  `ICAO` varchar(4) NOT NULL default '',
  `IATA` varchar(4) default '',
  `SYNOP` varchar(7) default '',
  `LAT` varchar(6) default '',
  `LON` varchar(7) default '',
  `ELEV` varchar(4) default '',
  `M` char(1) default '',
  `N` char(1) default '',
  `V` char(1) default '',
  `U` char(1) default '',
  `A` char(1) default '',
  `C` char(1) default '',
  `X` char(1) default '',
  `CODE` char(2) default '',
  `ATIS` varchar(22) default NULL,
  `ATC` varchar(22) default NULL,
  `PPR` varchar(22) default NULL,
  `FBO` varchar(22) default NULL,
  PRIMARY KEY  (`ICAO`)
);

And

CREATE TABLE `phonebin` (
  `record` int(11) unsigned zerofill NOT NULL auto_increment,
  `ICAO` varchar(4) NOT NULL default '',
  `ATIS` varchar(24) default NULL,
  `ATC` varchar(24) default NULL,
  `PPR` varchar(24) default NULL,
  `FBO` varchar(22) default NULL,
  `comment` varchar(240) default '',
  PRIMARY KEY  (`record`)
);

Phonebin is being populated by web users submitting phone numbers for
'Airport Terminal Information Service' - ATIS, Air Traffic Control, ATC etc.

(if you are interested the web submission page is
http://activitae.com/airbase/phonebin.htm )

A single web contributor may not provide the full set of phone numbers.
Occasionally, I would like to update the new_stations table with data from
web contributors. However, I do not want to over-write existing data in
new_stations with a null field from phonebin. To amplify, I might have
collected and correctly filled new_stations.ATIS from web contributor 1. Web
contributor 2 may provide phonebin.ATC data. I want to update new_stations
with contributor 2's data from phonebin without nulls overwriting existing
new_stations data.

I've tried 

UPDATE new_stations, phonebin set  
new_stations.ATIS=IFNULL(new_stations.ATIS,phonebin.ATIS),
new_stations.ATC=IFNULL(new_stations.ATC,phonebin.ATC),
new_stations.PPR=IFNULL(new_stations.PPR,phonebin.PPR),
new_stations.FBO=IFNULL(new_stations.FBO,phonebin.FBO) where
phonebin.icao=new_stations.icao

That worked once, for the very first update, but subsequent runs affect no
rows at all.

I could use a perl script to first fetch data and decide which row and
column to update but there has to be an SQL way - doesn't there?

All help appreciated!!

Angela

(activitae.com is a non-commercial hobby site)



-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.15/80 - Release Date: 23/08/2005



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

Re: mysqld stops suddenly ... help please

[Gleb Paharenko]

Hello.



> /usr/local/mysql/libexec/mysqld: Out of memory (Needed 32704 bytes)

> /usr/local/mysql/libexec/mysqld: Can't read dir of '/var/tmp/' (Errcode: 11)

> /usr/local/mysql/libexec/mysqld: Out of memory (Needed 8156 bytes)



Decrease your memory related variables. You have rather old versions of MySQL,

I strongly recommend you to upgrade. See this link as well:

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





[EMAIL PROTECTED] wrote:

> Hi all

> 

> a few days a go i have troubles with mysql, the service stops and this is on

> two boxes wiht diferents OS. The first one is a FreeBSD 5.3-RELEASE on a

> sparc64 the mysql Version is '4.1.5-gamma' and i got this messages in my

> logs:

> 

> 

> 050816 17:11:06  mysqld restarted

> Fatal error 'gc cannot wait for a signal' at line 194 in file

> /usr/src/lib/libc_r/uthread/uthread_gc.c (errno = 0)

> mysqld got signal 6;

> 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=0

> read_buffer_size=131072

> max_used_connections=0

> max_connections=100

> threads_connected=0

> It is possible that mysqld could use up to

> key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections =

> 217599 K

> bytes of memory

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

> 

> 050816 17:11:07  mysqld ended

> 

> 050817 11:13:51  mysqld started

> 050817 11:13:52  InnoDB: Database was not shut down normally!

> InnoDB: Starting crash recovery.

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

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

> InnoDB: buffer...

> 050817 11:13:52  InnoDB: Starting log scan based on checkpoint at

> InnoDB: log sequence number 0 43634.

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

> 050817 11:13:52  InnoDB: Flushing modified pages from the buffer pool...

> 050817 11:13:52  InnoDB: Started; log sequence number 0 43634

> /usr/local/libexec/mysqld: ready for connections.

> Version: '4.1.5-gamma'  socket: '/tmp/mysql.sock'  port: 3306  FreeBSD port:

> mysql-server-4.1.5

> 

> 

> 

> 

> 

> The other one is a solaris 8 on a sparc64 whit mysql version 3.23.45 and i

> got this error messages:

> 

> 

> 050815 19:01:08  mysqld restarted

> /usr/local/mysql/libexec/mysqld: ready for connections

> mysqld got signal 10;

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

> or one of the libraries it was linked agaist 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=16773120

> record_buffer=131072

> sort_buffer=524280

> max_used_connections=7

> max_connections=500

> threads_connected=2

> It is possible that mysqld could use up to 

> key_buffer_size + (record_buffer + sort_buffer)*max_connections = 336376 K

> bytes of memory

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

> 

> 050815 19:41:01  mysqld restarted

> /usr/local/mysql/libexec/mysqld: ready for connections

> mysqld got signal 10;

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

> or one of the libraries it was linked agaist 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=16773120

> record_buffer=131072

> sort_buffer=524280

> max_used_connections=1

> max_connections=500

> threads_connected=1

> It is possible that mysqld could use up to 

> key_buffer_size + (record_buffer + sort_buffer)*max_connections = 336376 K

> bytes of memory

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

> 

> 050815 19:41:02  mysqld restarted

> /usr/local/mysql/libexec/mysqld: ready for connections

> mysqld got signal 10;

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

> or one of the libraries it was linked agaist 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=1

mysqld stops suddenly ... help please

[mbeltran]

Hi all

a few days a go i have troubles with mysql, the service stops and this is on
two boxes wiht diferents OS. The first one is a FreeBSD 5.3-RELEASE on a
sparc64 the mysql Version is '4.1.5-gamma' and i got this messages in my
logs:


050816 17:11:06  mysqld restarted
Fatal error 'gc cannot wait for a signal' at line 194 in file
/usr/src/lib/libc_r/uthread/uthread_gc.c (errno = 0)
mysqld got signal 6;
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=0
read_buffer_size=131072
max_used_connections=0
max_connections=100
threads_connected=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections =
217599 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

050816 17:11:07  mysqld ended

050817 11:13:51  mysqld started
050817 11:13:52  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
050817 11:13:52  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 43634.
InnoDB: Doing recovery: scanned up to log sequence number 0 43634
050817 11:13:52  InnoDB: Flushing modified pages from the buffer pool...
050817 11:13:52  InnoDB: Started; log sequence number 0 43634
/usr/local/libexec/mysqld: ready for connections.
Version: '4.1.5-gamma'  socket: '/tmp/mysql.sock'  port: 3306  FreeBSD port:
mysql-server-4.1.5





The other one is a solaris 8 on a sparc64 whit mysql version 3.23.45 and i
got this error messages:


050815 19:01:08  mysqld restarted
/usr/local/mysql/libexec/mysqld: ready for connections
mysqld got signal 10;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked agaist 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=16773120
record_buffer=131072
sort_buffer=524280
max_used_connections=7
max_connections=500
threads_connected=2
It is possible that mysqld could use up to 
key_buffer_size + (record_buffer + sort_buffer)*max_connections = 336376 K
bytes of memory
Hope that's ok, if not, decrease some variables in the equation

050815 19:41:01  mysqld restarted
/usr/local/mysql/libexec/mysqld: ready for connections
mysqld got signal 10;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked agaist 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=16773120
record_buffer=131072
sort_buffer=524280
max_used_connections=1
max_connections=500
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (record_buffer + sort_buffer)*max_connections = 336376 K
bytes of memory
Hope that's ok, if not, decrease some variables in the equation

050815 19:41:02  mysqld restarted
/usr/local/mysql/libexec/mysqld: ready for connections
mysqld got signal 10;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked agaist 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=16773120
record_buffer=131072
sort_buffer=524280
max_used_connections=0
max_connections=500
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (record_buffer + sort_buffer)*max_connections = 336376 K
bytes of memory
Hope that's ok, if not, decrease some variables in the equation

050815 19:41:03  mysqld restarted
/usr/local/mysql/libexec/mysqld: Out of memory (Needed 32704 bytes)
/usr/local/mysql/libexec/mysqld: Can't read dir of '/var/tmp/' (Errcode: 11)
/usr/local/mysql/libexec/mysqld: Out of memory (Needed 8156 bytes)
mysqld got signal 10;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked agaist is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware

Re: help please : ERROR 2006: MySQL server has gone away

[Gleb Paharenko]

Hello.



4.0.17 is old enough. You may switch to the debug version and attempt to

find the clues in debug or trace files. Can you reproduce an error on the

latest release (4.1.9 now)? Do you lost connection to the server with

other statements, than 'SHOW DATABASES'? Please answer on this questions:

 -What operating system do you use?

 -Do you use official binaries?





[snip]

We still have the problem...

In the error log, I have nothing about problem. I only have that:

050203 00:34:14  mysqld started

/u01/mysql/libexec/mysqld: ready for connections.

Version: '4.0.17-log'  socket: '/tmp/mysql.sock'  port: 3306

 If I do show variables, I have max_allowed_packet = 16776192

IF I log into mysql with :mysql -uroot --max_allowed_packet=16M  -p



After I do:show databases; and I receive the answer.

After I wait 30 sec and launch the command "show databases;" again  and now I 
have the

error:



ERROR 2006: MySQL server has gone away

No connection. Trying to reconnect...

"Marois, David" <[EMAIL PROTECTED]> wrote:

[snip]

"Marois, David" <[EMAIL PROTECTED]> wrote:



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




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

RE : help please : ERROR 2006: MySQL server has gone away

[Marois, David]

Hi Michael,
Yesterday, I restarted the server and the mysql database.
And In the errorlog, I only have 
050203 00:34:14 mysqld started
/u01/mysql/libexec/mysqld: ready for connections.
Version: '4.0.17-log' socket: '/tmp/mysql.sock' port: 3306

Also, I verified and my mysql user have access to write into my errorlog file 
and in my directories.

David

David Marois
-Message d'origine-
De : Michael Dykman [mailto:[EMAIL PROTECTED] 
Envoyé : 3 février, 2005 10:02
À : Marois, David
Cc : "MySQL List"
Objet : Re: help please : ERROR 2006: MySQL server has gone away


So far, you have only showed us the client-view of the problem..  look
in you data directories on the server machine... there is likely an
error file there which should give you some insight (unless permissions
for that directory are seriously messed up).  IF there is no error file,
determine which user MySQL is running as (default: mysql) and confirm
that user has read/write permissions to that directory and
subdirectories.

There are a few misconfigurations which take MySQL a bit of time to fail
on.. I know I had similar results last summer doing a manual
source-install of MySQL 4.1 which was related to the data-directory
permissions.. Having sorted it out, the server has been extremely stable
under heavy load for several continuous months now.

 - michael dykman


On Thu, 2005-02-03 at 08:38, Marois, David wrote:
> We still have the problem...
>  
> In the error log, I have nothing about problem. I only have that:
> 050203 00:34:14  mysqld started
> /u01/mysql/libexec/mysqld: ready for connections.
> Version: '4.0.17-log'  socket: '/tmp/mysql.sock'  port: 3306
>  
> If I do show variables, I have max_allowed_packet = 16776192
>  
> IF I log into mysql with :mysql -uroot --max_allowed_packet=16M  -p
>  
> After I do:show databases; and I receive the answer.
> After I wait 30 sec and launch the command "show databases;" again  and now I 
> have the error:
>  
> ERROR 2006: MySQL server has gone away
> No connection. Trying to reconnect...
>  
> 
> David 
>  
> 
>  
> Hello.
>  
> Does the problem remain? What is in the error log? Please, send
> us information about MySQL and operating system versions. There are
> two variables: max_allowed_packet - one has client, another has server.
> Run mysql with --max_allowed_packet=16M and mysqld with the same value.
> May be you have some ulimits which cause such behaviour? 
> 
>  
> -Message d'origine-
> De : Marois, David 
> Envoyé : 2 février, 2005 09:16
> À : 'mysql@lists.mysql.com'
> Objet : Re: help please : ERROR 2006: MySQL server has gone away
> 
> 
> And 
>  
> max_allowed_packet  = 16776192 
>  
> 
> David
> 
>  
> Hi, 
> my interactive_timeout variable is
>  
> interactive_timeout 3600
>  
> Thanks !
>  
> David
>  
> 
> Hello.
>  
> I've asked you about interactive_timeout, not wait_timeout.
>  
> 
> Mark <[EMAIL PROTECTED]> wrote:
> >> -Original Message-
> >> From: Gleb Paharenko [mailto:[EMAIL PROTECTED]
> > 
> >> Sent: woensdag 2 februari 2005 12:46
> >> To: [EMAIL PROTECTED]
> >> Subject: Re: help please : ERROR 2006: MySQL server has gone away
> >> 
> >> Hello.
> >> 
> >> What's the value of the interactive_timeout system variable? See:
> >> http://dev.mysql.com/doc/mysql/en/server-system-variables.html
> >> http://dev.mysql.com/doc/mysql/en/gone-away.html
> > 
> > I believe he already answered that in part:
> > 
> >> > Also, my variable wait_timeout = 3600.
> > 
> > 1 hour, that is, instead of the default 8 (28800).
> > 
> > - Mark
> > 
> > 
> 
>  
> David Marois
>  <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]
>  
-- 
 - michael dykman
 - [EMAIL PROTECTED]





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

Re: help please : ERROR 2006: MySQL server has gone away

[Michael Dykman]

So far, you have only showed us the client-view of the problem..  look
in you data directories on the server machine... there is likely an
error file there which should give you some insight (unless permissions
for that directory are seriously messed up).  IF there is no error file,
determine which user MySQL is running as (default: mysql) and confirm
that user has read/write permissions to that directory and
subdirectories.

There are a few misconfigurations which take MySQL a bit of time to fail
on.. I know I had similar results last summer doing a manual
source-install of MySQL 4.1 which was related to the data-directory
permissions.. Having sorted it out, the server has been extremely stable
under heavy load for several continuous months now.

 - michael dykman


On Thu, 2005-02-03 at 08:38, Marois, David wrote:
> We still have the problem...
>  
> In the error log, I have nothing about problem. I only have that:
> 050203 00:34:14  mysqld started
> /u01/mysql/libexec/mysqld: ready for connections.
> Version: '4.0.17-log'  socket: '/tmp/mysql.sock'  port: 3306
>  
> If I do show variables, I have max_allowed_packet = 16776192
>  
> IF I log into mysql with :mysql -uroot --max_allowed_packet=16M  -p
>  
> After I do:show databases; and I receive the answer.
> After I wait 30 sec and launch the command "show databases;" again  and now I 
> have the error:
>  
> ERROR 2006: MySQL server has gone away
> No connection. Trying to reconnect...
>  
> 
> David 
>  
> 
>  
> Hello.
>  
> Does the problem remain? What is in the error log? Please, send
> us information about MySQL and operating system versions. There are
> two variables: max_allowed_packet - one has client, another has server.
> Run mysql with --max_allowed_packet=16M and mysqld with the same value.
> May be you have some ulimits which cause such behaviour? 
> 
>  
> -----Message d'origine-
> De : Marois, David 
> Envoyà : 2 fÃvrier, 2005 09:16
> Ã : 'mysql@lists.mysql.com'
> Objet : Re: help please : ERROR 2006: MySQL server has gone away
> 
> 
> And 
>  
> max_allowed_packet  = 16776192 
>  
> 
> David
> 
>  
> Hi, 
> my interactive_timeout variable is
>  
> interactive_timeout 3600
>  
> Thanks !
>  
> David
>  
> 
> Hello.
>  
> I've asked you about interactive_timeout, not wait_timeout.
>  
> 
> Mark <[EMAIL PROTECTED]> wrote:
> >> -Original Message-
> >> From: Gleb Paharenko [mailto:[EMAIL PROTECTED]
> > 
> >> Sent: woensdag 2 februari 2005 12:46
> >> To: [EMAIL PROTECTED]
> >> Subject: Re: help please : ERROR 2006: MySQL server has gone away
> >> 
> >> Hello.
> >> 
> >> What's the value of the interactive_timeout system variable? See:
> >> http://dev.mysql.com/doc/mysql/en/server-system-variables.html
> >> http://dev.mysql.com/doc/mysql/en/gone-away.html
> > 
> > I believe he already answered that in part:
> > 
> >> > Also, my variable wait_timeout = 3600.
> > 
> > 1 hour, that is, instead of the default 8 (28800).
> > 
> > - Mark
> > 
> > 
> 
>  
> David Marois
>  <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]
>  
-- 
 - michael dykman
 - [EMAIL PROTECTED]


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

help please : ERROR 2006: MySQL server has gone away

[Marois, David]

We still have the problem...
 
In the error log, I have nothing about problem. I only have that:
050203 00:34:14  mysqld started
/u01/mysql/libexec/mysqld: ready for connections.
Version: '4.0.17-log'  socket: '/tmp/mysql.sock'  port: 3306
 
If I do show variables, I have max_allowed_packet = 16776192
 
IF I log into mysql with :mysql -uroot --max_allowed_packet=16M  -p
 
After I do:show databases; and I receive the answer.
After I wait 30 sec and launch the command "show databases;" again  and now I 
have the error:
 
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
 
 
David 
 

 
Hello.
 
Does the problem remain? What is in the error log? Please, send
us information about MySQL and operating system versions. There are
two variables: max_allowed_packet - one has client, another has server.
Run mysql with --max_allowed_packet=16M and mysqld with the same value.
May be you have some ulimits which cause such behaviour? 

 
-Message d'origine-
De : Marois, David 
Envoyé : 2 février, 2005 09:16
À : 'mysql@lists.mysql.com'
Objet : Re: help please : ERROR 2006: MySQL server has gone away


And 
 
max_allowed_packet  = 16776192 
 
 
David

 
Hi, 
my interactive_timeout variable is
 
interactive_timeout 3600
 
Thanks !
 
David
 
 
Hello.
 
I've asked you about interactive_timeout, not wait_timeout.
 

Mark <[EMAIL PROTECTED]> wrote:
>> -Original Message-
>> From: Gleb Paharenko [mailto:[EMAIL PROTECTED]
> 
>> Sent: woensdag 2 februari 2005 12:46
>> To: [EMAIL PROTECTED]
>> Subject: Re: help please : ERROR 2006: MySQL server has gone away
>> 
>> Hello.
>> 
>> What's the value of the interactive_timeout system variable? See:
>> http://dev.mysql.com/doc/mysql/en/server-system-variables.html
>> http://dev.mysql.com/doc/mysql/en/gone-away.html
> 
> I believe he already answered that in part:
> 
>> > Also, my variable wait_timeout = 3600.
> 
> 1 hour, that is, instead of the default 8 (28800).
> 
> - Mark
> 
> 

 
David Marois
 <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]
 

Re: help please : ERROR 2006: MySQL server has gone away

[Gleb Paharenko]

Hello.



Does the problem remain? What is in the error log? Please, send

us information about MySQL and operating system versions. There are

two variables: max_allowed_packet - one has client, another has server.

Run mysql with --max_allowed_packet=16M and mysqld with the same value.

May be you have some ulimits which cause such behaviour? 





[snip]

And 

max_allowed_packet  = 16776192 

David

Hi, 

my interactive_timeout variable is

interactive_timeout 3600

  Thanks !"Marois, David" <[EMAIL PROTECTED]> wrote:

[snip]



-- 
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: help please : ERROR 2006: MySQL server has gone away

[Marois, David]

And 
 
max_allowed_packet  = 16776192 
 
 
David

 
Hi, 
my interactive_timeout variable is
 
interactive_timeout 3600
 
Thanks !
 
David
 
 
Hello.
 
I've asked you about interactive_timeout, not wait_timeout.
 

Mark <[EMAIL PROTECTED]> wrote:
>> -Original Message-
>> From: Gleb Paharenko [mailto:[EMAIL PROTECTED]
> 
>> Sent: woensdag 2 februari 2005 12:46
>> To: [EMAIL PROTECTED]
>> Subject: Re: help please : ERROR 2006: MySQL server has gone away
>> 
>> Hello.
>> 
>> What's the value of the interactive_timeout system variable? See:
>> http://dev.mysql.com/doc/mysql/en/server-system-variables.html
>> http://dev.mysql.com/doc/mysql/en/gone-away.html
> 
> I believe he already answered that in part:
> 
>> > Also, my variable wait_timeout = 3600.
> 
> 1 hour, that is, instead of the default 8 (28800).
> 
> - Mark
> 
> 

 
David Marois
 <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]
 

Re: help please : ERROR 2006: MySQL server has gone away

[Marois, David]

Hi, 
my interactive_timeout variable is
 
interactive_timeout 3600
 
Thanks !
 
David
 
 
Hello.
 
I've asked you about interactive_timeout, not wait_timeout.
 

Mark <[EMAIL PROTECTED]> wrote:
>> -Original Message-
>> From: Gleb Paharenko [mailto:[EMAIL PROTECTED]
> 
>> Sent: woensdag 2 februari 2005 12:46
>> To: [EMAIL PROTECTED]
>> Subject: Re: help please : ERROR 2006: MySQL server has gone away
>> 
>> Hello.
>> 
>> What's the value of the interactive_timeout system variable? See:
>> http://dev.mysql.com/doc/mysql/en/server-system-variables.html
>> http://dev.mysql.com/doc/mysql/en/gone-away.html
> 
> I believe he already answered that in part:
> 
>> > Also, my variable wait_timeout = 3600.
> 
> 1 hour, that is, instead of the default 8 (28800).
> 
> - Mark
> 
> 

 
David Marois
 <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]
 

Re: help please : ERROR 2006: MySQL server has gone away

[Gleb Paharenko]

Hello.



I've asked you about interactive_timeout, not wait_timeout.





Mark <[EMAIL PROTECTED]> wrote:

>> -Original Message-

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

>> Sent: woensdag 2 februari 2005 12:46

>> To: mysql@lists.mysql.com

>> Subject: Re: help please : ERROR 2006: MySQL server has gone away

>> 

>> Hello.

>> 

>> What's the value of the interactive_timeout system variable? See:

>> http://dev.mysql.com/doc/mysql/en/server-system-variables.html

>> http://dev.mysql.com/doc/mysql/en/gone-away.html

> 

> I believe he already answered that in part:

> 

>> > Also, my variable wait_timeout = 3600.

> 

> 1 hour, that is, instead of the default 8 (28800).

> 

> - Mark

> 

> 



-- 
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: help please : ERROR 2006: MySQL server has gone away

[Mark]

> -Original Message-
> From: Gleb Paharenko [mailto:[EMAIL PROTECTED] 
> Sent: woensdag 2 februari 2005 12:46
> To: mysql@lists.mysql.com
> Subject: Re: help please : ERROR 2006: MySQL server has gone away
> 
> Hello.
> 
> What's the value of the interactive_timeout system variable? See:
> http://dev.mysql.com/doc/mysql/en/server-system-variables.html
> http://dev.mysql.com/doc/mysql/en/gone-away.html

I believe he already answered that in part:

> > Also, my variable wait_timeout = 3600.

1 hour, that is, instead of the default 8 (28800).

- Mark


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

Re: help please : ERROR 2006: MySQL server has gone away

[Gleb Paharenko]

Hello.



What's the value of the interactive_timeout system variable? See:

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

  http://dev.mysql.com/doc/mysql/en/gone-away.html





"Marois, David" <[EMAIL PROTECTED]> wrote:

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

> 

> Hi,

> I have this error when I am connected in mysql:

> 

> ERROR 2006: MySQL server has gone away. No connection. Trying to reconnect...

> 

> I only did this command: show databases;

> and received the error.

> 

> I noticed that if I don't send a command since 25 sec, I have the error.

> 

> Also, my variable wait_timeout = 3600.

> 

> Any ideas ?

> 

> 

> David Marois

>  [EMAIL PROTECTED]

> 

> 



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

help please : ERROR 2006: MySQL server has gone away

[Marois, David]

Hi,
I have this error when I am connected in mysql:
 
ERROR 2006: MySQL server has gone away. No connection. Trying to reconnect...
 
I only did this command: show databases;
and received the error.
 
I noticed that if I don't send a command since 25 sec, I have the error.
 
Also, my variable wait_timeout = 3600.
 
Any ideas ?
 
 
David Marois
  [EMAIL PROTECTED]
 

Re: help please !! [MySQL][ODBC 3.51Driver][mysqld-4.1.8-nt-log]Unknow MySQL error

[Gleb Paharenko]

Hello.



You can enable logging on the server. See:

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



You need the error log, query log, slow log. But be careful!

Heavy loaded server can produce a lot of messages and your

files will grow quickly. If you use InnoDB, you can enable

the InnoDB monitors. See:

  http://dev.mysql.com/doc/mysql/en/innodb-monitor.html



You can enable ODBC trace, but it very slows down the system. See:

  http://dev.mysql.com/doc/mysql/en/myodbc-trace.html



The clues can be in one of the log files.



matias Castilla <[EMAIL PROTECTED]> wrote:

> I have a mysql server running on WIN-XP (in a critical

> health area) and it started to give me that error. 

> At first, the problem ocurred in a machine and then

> started to scatter all over the system. On Saturday I

> reset the Server Machine and the problem disapeared.

> But today applications started to fail again.

> Sometimes showing this error messeges and sometimes

> not showing the content of some tables.

> mat?as.

> 

> _

> Do You Yahoo!?

> Informaci?n de Estados Unidos y Am?rica Latina, en Yahoo! Noticias.

> Vis?tanos en http://noticias.espanol.yahoo.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]
/_/  /_/\_, /___/\___\_\___/   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]

help please !! [MySQL][ODBC 3.51Driver][mysqld-4.1.8-nt-log]Unknow MySQL error

[matias Castilla]

I have a mysql server running on WIN-XP (in a critical
health area) and it started to give me that error. 
At first, the problem ocurred in a machine and then
started to scatter all over the system. On Saturday I
reset the Server Machine and the problem disapeared.
But today applications started to fail again.
Sometimes showing this error messeges and sometimes
not showing the content of some tables.
matías.

_
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com

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

Re: Query error need help please

[Greg Donald]

On Tue, 14 Sep 2004 19:55:42 -0700, Soheil Shaghaghi <[EMAIL PROTECTED]> wrote:
> I have a subroutine which checks for multiple entries and if a user has
> voted once in the same day, it will not calculate the vote. However, I
> found out that it does not really do this.
> What it does is only look at the last entry. If the user IP address is
> the last entry it does not calculate the vote, but if it is not, it goes
> ahead and let the user vote.
> So, basically it only looks at the very last entry.
> Can someone please tell me what needs to be done to fix this?
> Thanks so much.


This is fixed in the latest version:

http://sourceforge.net/projects/destiney/

If not, let me know and I'll look into it.  I'm still maintaining the
0.3 series.


-- 
Greg Donald
http://destiney.com/

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

Query error need help please

[Soheil Shaghaghi]

Hello.
I have a subroutine which checks for multiple entries and if a user has
voted once in the same day, it will not calculate the vote. However, I
found out that it does not really do this.
What it does is only look at the last entry. If the user IP address is
the last entry it does not calculate the vote, but if it is not, it goes
ahead and let the user vote.
So, basically it only looks at the very last entry.
Can someone please tell me what needs to be done to fix this?
Thanks so much.


if(isset($_POST['submit_rating']) && isset($_POST['user_id']) && 
($_POST['submit_rating'] >= 3 && $_POST['submit_rating'] <=
10)){

$user_id = (int) $_POST['user_id'];

if(isset($_SESSION['ra'])){
$_SESSION['ra'] .= $user_id . ",";
} else {
$_SESSION['ra'] = $user_id . ",";
}

$rating = (int) $_POST['submit_rating'];
$rater_id = isset($_SESSION['userid']) ? $_SESSION['userid'] :
0;

$check_ip_sql = "
select
*
from
$tb_ratings
where
user_id = '$user_id'
order by
timestamp desc
";

$check_ip_query = mysql_query($check_ip_sql) or
die(mysql_error());
$last_rater_ip = @mysql_result($check_ip_query, "0",
"rater_ip");
$last_rater_id = @mysql_result($check_ip_query, "0",
"rater_id");
$last_rated = @mysql_result($check_ip_query, "0", "timestamp");

$yesterday = date("YmdHis",
mktime(date("H"), date("i"), date("s"),
date("m"), date("d")-10, date("Y")));

$same_ip = false;
$too_soon = false;
$same_user = false;

if($last_rater_ip == $HTTP_SERVER_VARS['REMOTE_ADDR']) $same_ip
= true;
if($last_rated > $yesterday) $too_soon = true;
if($user_id == $rater_id) $same_user = true;

if(!$same_user && (!$same_ip || !$too_soon)){
$rating_accepted = true;

$is_sql = "
insert into $tb_ratings (
id,
user_id,
rating,
rater_id,
rater_ip
) values (
'',
'$user_id',
'$rating',
'$rater_id',
'$_SERVER[REMOTE_ADDR]'
)
";

$is_query = mysql_query($is_sql) or die(mysql_error());

$gs_sql = "
select
total_ratings,
total_points,
average_rating
from
$tb_users
where
id = '$user_id'
";

$gs_query = mysql_query($gs_sql) or die(mysql_error());
$total_ratings = mysql_result($gs_query, 0,
"total_ratings");
$total_points = mysql_result($gs_query, 0,
"total_points");

$total_ratings++;
$total_points += $rating;
$average_rating = $total_points / $total_ratings;

$ps_sql = "
update
$tb_users
set
total_ratings = '$total_ratings',
total_points = '$total_points',
average_rating = '$average_rating'
where
id = '$user_id'
";

$ps_query = mysql_query($ps_sql) or die(mysql_error());

}
}



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

Re: Data loading and foreign key constraints - help please

[Todd Cranston-Cuebas]

Thank you very much. I really appreciate your analogy to the waterfall. 
This helped me out tremendously. I was able to sort out the problem and 
all is now well! It appears that this wonderful little GUI tool the 
lets you create ER diagrams that auto-generate CREATE scripts assumes 
that you won't be including foreign keys in your entities. It expects 
you to build the relationship graphically and point out the primary 
keys, but it takes care of creating the foreign keys for you. My 
mistake.

Since I put in foreign keys with the same name as the primary key in 
the related table, the GUI tool had no choice but to create "new" 
foreign keys with the same name appended with the number 1. The end 
result... total chaos. Fixed it though. I really appreciate your help.

Todd
On Aug 30, 2004, at 6:20 AM, [EMAIL PROTECTED] wrote:
Foreign keys are used to enforce foreign relationships. Translated:
Certain data values must exist in one table before another table can
contain those values in columns that participate in foreign keys. 
Because
data must first exist in one table before it can be used as data in
another, you are required to fill in your FK-related structures from 
the
top down.

Start with your top-most table(s) in your structure (these are the ones
that the foreign keys are referencing but have no foreign keys of their
own). I think you said that you called them "joblevel" and "jobtitile".
Fill those tables with data. With those values in place you can create
rows in the jobcode table that re-use certain values. You will not be 
able
to assign a value to any row in jobcode that does not exist in either
joblevel or jobtitle (for the columns that reference those tables as
foreign keys).

Keep filling in values in each layer of your structure until you get to
the "bottommost" table(s). (These are the tables that FK reference 
other
tables but have no tables that reference them.) It's kind of like a
waterfall, you can't get data into some tables until it exists in other
tables so it's like the data sort of "trickles down" the structure. 
(This
analogy could also help to visualize how  the use of the word "cascade"
describes the auto-propagation of a delete or update to the dependent
tables)

HTH,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Todd Cranston-Cuebas <[EMAIL PROTECTED]> wrote on
08/29/2004 04:09:15 AM:
I'm a total newbie to mySQL, but was hoping someone could answer a
question regarding adding a record into a database that has foreign 
key
constraints. Remember, I'm a total newbie so I'm hoping I'm using the
right words to express this. I'm taking a class that required us to 
use
an ER diagramming tool. This tool generates SQL table create scripts 
in
mySQL. After a little tweaking I got the scripts to work. An example 
is
as follows:

# Create Table: 'Jobdesc'   Job Description for Requisition
# desccode:
# jobdescription  :
# levelcode   :  (references JobCode.levelcode)
# jobcode1:  (references JobCode.jobcode)
# jobcode :
# titlecode   :  (references JobCode.titlecode)
#
CREATE TABLE Jobdesc (
 desccode   CHAR(8) NOT NULL UNIQUE,
 jobdescription MEDIUMTEXT NOT NULL,
 levelcode  CHAR(2) NOT NULL,
 jobcode1   CHAR(8) NOT NULL,
 jobcodeCHAR(8) NOT NULL,
 titlecode  CHAR(7) NOT NULL,
PRIMARY KEY (desccode,jobcode),
 INDEX idx_fk_Jobdesc (titlecode,jobcode1,levelcode),
CONSTRAINT fk_Jobdesc FOREIGN KEY (titlecode,jobcode1,levelcode)
 REFERENCES JobCode (titlecode,jobcode,levelcode)
 ON DELETE CASCADE
 ON UPDATE CASCADE) TYPE=INNODB;
This is a create script for a job description table. Job descriptions
are related to a jobcode table. That table in turn is related to
joblevel and jobtitle tables (i.e., the job title and job level
determine the job code). The jobcode is needed for each job
description.
One problem I have is that the create scripts generated from the ER
tool makes all fields in the job description entity NOT NULL. If I try
to insert the description code (desccode), the job description
(jobdescription) and the associated job code (jobcode) I get the
following error:
#1216 - Cannot add or update a child row: a foreign key constraint 
fails

This happens if I just try to insert the desccode, the jobdescription,
and jobcode data. I think this is happening because jobcode1,
levelcode, and titlecode are NOT NULL so when I update the record it
attempts to enter data (NULL) into these fields which are child rows.
Can someone explain what I should do? Should I just change these 
fields
of data into NULL? I'm literally just trying to populate the tables
with enough data to run some test queries.

Any suggestions?
Todd
--
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

Data loading and foreign key constraints - help please

[Todd Cranston-Cuebas]

I'm a total newbie to mySQL, but was hoping someone could answer a 
question regarding adding a record into a database that has foreign key 
constraints. Remember, I'm a total newbie so I'm hoping I'm using the 
right words to express this. I'm taking a class that required us to use 
an ER diagramming tool. This tool generates SQL table create scripts in 
mySQL. After a little tweaking I got the scripts to work. An example is 
as follows:

# Create Table: 'Jobdesc'   Job Description for Requisition
# desccode:
# jobdescription  :
# levelcode   :  (references JobCode.levelcode)
# jobcode1:  (references JobCode.jobcode)
# jobcode :
# titlecode   :  (references JobCode.titlecode)
#
CREATE TABLE Jobdesc (
desccode   CHAR(8) NOT NULL UNIQUE,
jobdescription MEDIUMTEXT NOT NULL,
levelcode  CHAR(2) NOT NULL,
jobcode1   CHAR(8) NOT NULL,
jobcodeCHAR(8) NOT NULL,
titlecode  CHAR(7) NOT NULL,
PRIMARY KEY (desccode,jobcode),
INDEX idx_fk_Jobdesc (titlecode,jobcode1,levelcode),
CONSTRAINT fk_Jobdesc FOREIGN KEY (titlecode,jobcode1,levelcode)
REFERENCES JobCode (titlecode,jobcode,levelcode)
ON DELETE CASCADE
ON UPDATE CASCADE) TYPE=INNODB;
This is a create script for a job description table. Job descriptions 
are related to a jobcode table. That table in turn is related to 
joblevel and jobtitle tables (i.e., the job title and job level 
determine the job code). The jobcode is needed for each job 
description.

One problem I have is that the create scripts generated from the ER 
tool makes all fields in the job description entity NOT NULL. If I try 
to insert the description code (desccode), the job description 
(jobdescription) and the associated job code (jobcode) I get the 
following error:

#1216 - Cannot add or update a child row: a foreign key constraint fails
This happens if I just try to insert the desccode, the jobdescription, 
and jobcode data. I think this is happening because jobcode1, 
levelcode, and titlecode are NOT NULL so when I update the record it 
attempts to enter data (NULL) into these fields which are child rows. 
Can someone explain what I should do? Should I just change these fields 
of data into NULL? I'm literally just trying to populate the tables 
with enough data to run some test queries.

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

Re: Some BLOB help please.

[Keith Ivey]

[EMAIL PROTECTED] wrote:
You might save some space if you compress() before storing. Depending on
file content I'm seeing 0-50% savings?
Good idea, but note that COMPRESS() and UNCOMPRESS() weren't introduced 
until MySQL 4.1.1.
With earlier versions you may be able to compress and uncompress in your 
application before
inserting and after selecting (using PHP's gzcompress() and 
gzuncompress(), for example).

--
Keith Ivey <[EMAIL PROTECTED]>
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Some BLOB help please.

[emierzwa]

You might save some space if you compress() before storing. Depending on
file content I'm seeing 0-50% savings?

select length(load_file('c:/temp/SomeFile.pdf')) as old_size
  ,length(compress(load_file('c:/temp/SomeFile.pdf'))) as new_size

Ed
-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 22, 2004 11:01 PM
To: Justin Crone
Cc: [EMAIL PROTECTED]
Subject: Re: Some BLOB help please.



Justin Crone wrote:

> 
> So as I said, I am rather pleased with the performance and the ease at

> which I was able to get this up and running. However The problem
is 
> I do have limits, and one of those being disk space. Those 10,000
files 
> are taking up 21 GB of space in the database. However the actual space

> required by the files is around 5GB on the file system. The average
file 
> size is about 1.9MB, so it would seem that each row inserted into the 
> database is conforming to that 1.9MB average, giving me this 21GB
table.

Could you explain that again?  If average file size is 1.9Mb, then 21 Gb
for 
10,780 files is about right.  On the other hand, if the total is 5 Gb,
then 
5Gb/10,780 yields about .47 Mb average per file.  So which is it?  Do
your 
files average 1.9Mb, in which case we must wonder how you stored them in

only 5Gb, or do your files average .47 Mb, in which case we must figure
out 
why they are roughly 4 times as big when stored as BLOBs?  (The manual 
<http://dev.mysql.com/doc/mysql/en/Storage_requirements.html> says BLOBs

take length + 2bytes to store .)

> I would like to know if there is something that I can change to get
these
> numbers in line with each other, or if this is just the way of things.
> Current projections for the total documents needed to complete the
rotation
> of these files is 720,000 documents. Which if the 1.9MB average keeps,
that
> puts me in the neighborhood of 1.4TB of storage.

Even at .47Mb per file, that's about 330 Gb of storage required just for
the 
720,000 files.

Michael


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



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

Re: Some BLOB help please.

[Michael Stassen]

Justin Crone wrote:

So as I said, I am rather pleased with the performance and the ease at 
which I was able to get this up and running. However The problem is 
I do have limits, and one of those being disk space. Those 10,000 files 
are taking up 21 GB of space in the database. However the actual space 
required by the files is around 5GB on the file system. The average file 
size is about 1.9MB, so it would seem that each row inserted into the 
database is conforming to that 1.9MB average, giving me this 21GB table.
Could you explain that again?  If average file size is 1.9Mb, then 21 Gb for 
10,780 files is about right.  On the other hand, if the total is 5 Gb, then 
5Gb/10,780 yields about .47 Mb average per file.  So which is it?  Do your 
files average 1.9Mb, in which case we must wonder how you stored them in 
only 5Gb, or do your files average .47 Mb, in which case we must figure out 
why they are roughly 4 times as big when stored as BLOBs?  (The manual 
 says BLOBs 
take length + 2bytes to store .)

I would like to know if there is something that I can change to get these
numbers in line with each other, or if this is just the way of things.
Current projections for the total documents needed to complete the rotation
of these files is 720,000 documents. Which if the 1.9MB average keeps, that
puts me in the neighborhood of 1.4TB of storage.
Even at .47Mb per file, that's about 330 Gb of storage required just for the 
720,000 files.

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

Some BLOB help please.

[Justin Crone]

Hello All,
I have been reading hundreds of posts and sites for information 
regarding BLOBs in MySQL. So far I have not found an answer to my 
question, so I pose it to you fine people. :)

A little background first. I have a web server that uses PHP to retrieve 
documents in PDF format for viewing and printing. When I first developed 
the application I was storing the files on the file system. However 
about 7 months into the project, my file system was starting to get all 
but unmanageable. At 175,000 documents, I decided I needed a new 
mechanism for storing the files. So I began to use BLOBS.

I have started the project again, with BLOBs not yet importing the 
documents from the old project. Currently I have 10,780 files in the 
database, and all is working  excellently. I have 3 tables, one for the 
BLOBs with unique keys, 2 table with the information regarding the BLOB, 
and a 3 table with the actual relevant information to the document.

So as I said, I am rather pleased with the performance and the ease at 
which I was able to get this up and running. However The problem is 
I do have limits, and one of those being disk space. Those 10,000 files 
are taking up 21 GB of space in the database. However the actual space 
required by the files is around 5GB on the file system. The average file 
size is about 1.9MB, so it would seem that each row inserted into the 
database is conforming to that 1.9MB average, giving me this 21GB table.

I would like to know if there is something that I can change to get 
these numbers in line with each other, or if this is just the way of 
things. Current projections for the total documents needed to complete 
the rotation of these files is 720,000 documents. Which if the 1.9MB 
average keeps, that puts me in the neighborhood of  1.4TB of storage.

Any thoughts?
Thanx,
Justin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: JOINing complication, help please

[SGreen]

hit is *much* lighter as you
are JOINING the address information tables on a *much* smaller result set.

I do have one nagging question, though Why do you insist on LEFT
joining the CONTACT and CUSTOMER tables to CONTACT_X_CUSTOMER? I would
assume that if a record exists in CONTACT_X_CUSTOMER then there would be
corresponding records in both of those tables.

I know this has been a huge post but you have a rather involved query to
work through. If the email system re-wrapped everything to that it became
illegible email me directly and I will attach these as text files and mail
them straight back.

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



   
  
  "Luc Foisy"  
  
  <[EMAIL PROTECTED]To:   <[EMAIL PROTECTED]>, 
"MYSQL-List (E-mail)"   
  -magic.com>  <[EMAIL PROTECTED]> 
  
  cc:  
  
  06/02/2004 02:09 PM Fax to:  
  
  Subject:  RE: JOINing complication, 
help please
   
  





As horible as it looks, this is what I came out with...

SELECT CONTACT_X_CUSTOMER.ID, CONTACT.LastName AS 'Last Name',
CONTACT.FirstName AS 'First Name', ---------snip
 =  17  ORDER BY CONTACT.LastName

(of course checking for NULL and TRIM() sure adds to the select)

I originally had it looking much better...

LEFT JOIN ADDRESS ON IF(CONTACT_X_CUSTOMER.ID_ADDRESS > 0,
CONTACT_X_CUSTOMER.ID_ADDRESS = ADDRESS.ID, IF(CUSTOMER.ID_ADDRESS_SHIPTO >
0, CUSTOMER.ID_ADDRESS_SHIPTO = ADDRESS.ID, CUSTOMER.ID_ADDRESS_MAIN =
ADDRESS.ID)

This actually worked great, I only had to refer to ADDRESS once in the
select for output, I only had to join the other tables CITY, PROVINCE,
COUNTRY once on ADDRESS too.

BUT the darned thing wouldn't see the indexes on the related address
fields, so when I populated the ADDRESS table with 10 records, the
return took much longer than desired. IF it did pay attention to the
indexes like I expected, then it would have been the much preferable
choice, but it didn't, so I had to do many extra joins and use aliases and
get a monstrosity :)


( i do really appologize for that big spew of SQL )


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 02, 2004 11:37 AM
To: Luc Foisy; MYSQL-List (E-mail)
Subject: Re: JOINing complication, help please



Sorry to reply to myslef but I just saw my own typo. Here is a better
example statement:

SELECT x.ID_ADDRESS, c.customerName, COALESCE(s.address, a.address,
'none') as address
FROM Customer c
INNER JOIN CONTACT_X_CUSTOMER x
  ON c.ID = x.CUSTOMER_ID
LEFT JOIN Address a
  ON a.ID = x.ID_ADDRESS
LEFT JOIN Address s
  ON s.ID = x.ID_ADDRESS_SHIPTO

(the problem was: the s and the a tables are aliases of the same table so
they should have had the same column names. SORRY !!!)




  [EMAIL PROTECTED]

   To:   "Luc Foisy"
<[EMAIL PROTECTED]>
  06/02/2004 11:31 cc:   "MYSQL-List
(E-mail)" <[EMAIL PROTECTED]>
  AM   Fax to:

   Subject:  Re: JOINing
complication, help please






Luc,

This looks like you want a list of all Customers with Contacts (because you
are basing it on the CONTACT_X_CUSTOMER table) and you want to show the
Address (if it exists) or the Shipping Address (if it exists) instead of
the Address? Am I close?

If I want to get one of two or more result choices in a column, I use an
IF(), a CASE...END, an IFNULL(), a NULLIF() or a COALESCE() statement,
depending on what it is I am choosing between.

In your case I think you want to chose which address to use based on their
existence, in this case, if it exists, it won't be null:

SELECT x.ID_ADDRESS, c.customerName, COALESCE(s.ship_to_address, a.address,
'none') as address
FROM Customer c
INNER JOIN CONTACT_X_CUSTOMER x
  ON c.ID = x.CUSTOMER_ID
LEFT JOIN Address a
  ON a.ID = x.ID_ADDRESS
LEFT JOIN Address s
  ON s.ID = x.ID_ADDRESS_SHIPTO


In this statement, the COALESCE statement will resolve to be the first
non-null expression in the list. If there is no Address t

RE: JOINing complication, help please

[Luc Foisy]

Oops, that LEFT JOIN ADDRESS ON IF(etc...) shouldn't be in the big long select 
statement

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

RE: JOINing complication, help please

[Luc Foisy]

ROVINCE = SHIPTO_PROVINCE.ID LEFT JOIN COUNTRY 
SHIPTO_COUNTRY ON SHIPTO_ADDRESS.ID_COUNTRY = SHIPTO_COUNTRY.ID LEFT JOIN ADDRESS 
MAIN_ADDRESS ON CUSTOMER.ID_ADDRESS_MAIN = MAIN_ADDRESS.ID LEFT JOIN CITY MAIN_CITY ON 
MAIN_ADDRESS.ID_CITY = MAIN_CITY.ID LEFT JOIN PROVINCE MAIN_PROVINCE ON 
MAIN_ADDRESS.ID_PROVINCE = MAIN_PROVINCE.ID LEFT JOIN COUNTRY MAIN_COUNTRY ON 
MAIN_ADDRESS.ID_COUNTRY = MAIN_COUNTRY.ID WHERE CONTACT_X_CUSTOMER.ID_CUSTOMER =  17  
ORDER BY CONTACT.LastName

(of course checking for NULL and TRIM() sure adds to the select)

I originally had it looking much better...

LEFT JOIN ADDRESS ON IF(CONTACT_X_CUSTOMER.ID_ADDRESS > 0, 
CONTACT_X_CUSTOMER.ID_ADDRESS = ADDRESS.ID, IF(CUSTOMER.ID_ADDRESS_SHIPTO > 0, 
CUSTOMER.ID_ADDRESS_SHIPTO = ADDRESS.ID, CUSTOMER.ID_ADDRESS_MAIN = ADDRESS.ID)

This actually worked great, I only had to refer to ADDRESS once in the select for 
output, I only had to join the other tables CITY, PROVINCE, COUNTRY once on ADDRESS 
too.

BUT the darned thing wouldn't see the indexes on the related address fields, so when I 
populated the ADDRESS table with 10 records, the return took much longer than 
desired. IF it did pay attention to the indexes like I expected, then it would have 
been the much preferable choice, but it didn't, so I had to do many extra joins and 
use aliases and get a monstrosity :)


( i do really appologize for that big spew of SQL )


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 02, 2004 11:37 AM
To: Luc Foisy; MYSQL-List (E-mail)
Subject: Re: JOINing complication, help please



Sorry to reply to myslef but I just saw my own typo. Here is a better
example statement:

SELECT x.ID_ADDRESS, c.customerName, COALESCE(s.address, a.address,
'none') as address
FROM Customer c
INNER JOIN CONTACT_X_CUSTOMER x
  ON c.ID = x.CUSTOMER_ID
LEFT JOIN Address a
  ON a.ID = x.ID_ADDRESS
LEFT JOIN Address s
  ON s.ID = x.ID_ADDRESS_SHIPTO

(the problem was: the s and the a tables are aliases of the same table so
they should have had the same column names. SORRY !!!)



   
   
  [EMAIL PROTECTED]
   
   To:   "Luc Foisy" <[EMAIL 
PROTECTED]>  
  06/02/2004 11:31 cc:   "MYSQL-List (E-mail)" <[EMAIL 
PROTECTED]>
  AM   Fax to: 
   
   Subject:  Re: JOINing complication, 
help please
   
   





Luc,

This looks like you want a list of all Customers with Contacts (because you
are basing it on the CONTACT_X_CUSTOMER table) and you want to show the
Address (if it exists) or the Shipping Address (if it exists) instead of
the Address? Am I close?

If I want to get one of two or more result choices in a column, I use an
IF(), a CASE...END, an IFNULL(), a NULLIF() or a COALESCE() statement,
depending on what it is I am choosing between.

In your case I think you want to chose which address to use based on their
existence, in this case, if it exists, it won't be null:

SELECT x.ID_ADDRESS, c.customerName, COALESCE(s.ship_to_address, a.address,
'none') as address
FROM Customer c
INNER JOIN CONTACT_X_CUSTOMER x
  ON c.ID = x.CUSTOMER_ID
LEFT JOIN Address a
  ON a.ID = x.ID_ADDRESS
LEFT JOIN Address s
  ON s.ID = x.ID_ADDRESS_SHIPTO


In this statement, the COALESCE statement will resolve to be the first
non-null expression in the list. If there is no Address that matches your
_X_ table's ID_ADDRESS_SHIP to then all of the columns in the table aliased
as "s" will be NULL,  Same with ID_ADDRESS and the table aliased as  "a".
If neither address exists the string 'none' is the result.

HTH!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine




  "Luc Foisy"

  <[EMAIL PROTECTED]To:   "MYSQL-List
(E-mail)" <[EMAIL PROTECTED]>
  -magic.com> cc:

  Fax to:

  06/02/2004 10:46 AM Subject:  JOINing
complication, help please







CONTACT_X_CUSTOMER.ID_ADDRESS
CUSTOMER.ID_ADDRESS_SHIPTO
CUSTOMER.ID_ADDRESS_MAIN

What I would like is to be able to JOIN conditionally based on the
absence/presence of reference

SELECT ADDRESS.ID FROM CONTACT_X_CUSTOMER LEFT JOIN CUSTOMER

Re: JOINing complication, help please

[SGreen]

Sorry to reply to myslef but I just saw my own typo. Here is a better
example statement:

SELECT x.ID_ADDRESS, c.customerName, COALESCE(s.address, a.address,
'none') as address
FROM Customer c
INNER JOIN CONTACT_X_CUSTOMER x
  ON c.ID = x.CUSTOMER_ID
LEFT JOIN Address a
  ON a.ID = x.ID_ADDRESS
LEFT JOIN Address s
  ON s.ID = x.ID_ADDRESS_SHIPTO

(the problem was: the s and the a tables are aliases of the same table so
they should have had the same column names. SORRY !!!)



   
   
  [EMAIL PROTECTED]
   
   To:   "Luc Foisy" <[EMAIL 
PROTECTED]>  
  06/02/2004 11:31 cc:   "MYSQL-List (E-mail)" <[EMAIL 
PROTECTED]>
  AM   Fax to: 
   
   Subject:  Re: JOINing complication, 
help please
   
   





Luc,

This looks like you want a list of all Customers with Contacts (because you
are basing it on the CONTACT_X_CUSTOMER table) and you want to show the
Address (if it exists) or the Shipping Address (if it exists) instead of
the Address? Am I close?

If I want to get one of two or more result choices in a column, I use an
IF(), a CASE...END, an IFNULL(), a NULLIF() or a COALESCE() statement,
depending on what it is I am choosing between.

In your case I think you want to chose which address to use based on their
existence, in this case, if it exists, it won't be null:

SELECT x.ID_ADDRESS, c.customerName, COALESCE(s.ship_to_address, a.address,
'none') as address
FROM Customer c
INNER JOIN CONTACT_X_CUSTOMER x
  ON c.ID = x.CUSTOMER_ID
LEFT JOIN Address a
  ON a.ID = x.ID_ADDRESS
LEFT JOIN Address s
  ON s.ID = x.ID_ADDRESS_SHIPTO


In this statement, the COALESCE statement will resolve to be the first
non-null expression in the list. If there is no Address that matches your
_X_ table's ID_ADDRESS_SHIP to then all of the columns in the table aliased
as "s" will be NULL,  Same with ID_ADDRESS and the table aliased as  "a".
If neither address exists the string 'none' is the result.

HTH!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine




  "Luc Foisy"

  <[EMAIL PROTECTED]To:   "MYSQL-List
(E-mail)" <[EMAIL PROTECTED]>
  -magic.com> cc:

      Fax to:

  06/02/2004 10:46 AM Subject:  JOINing
complication, help please







CONTACT_X_CUSTOMER.ID_ADDRESS
CUSTOMER.ID_ADDRESS_SHIPTO
CUSTOMER.ID_ADDRESS_MAIN

What I would like is to be able to JOIN conditionally based on the
absence/presence of reference

SELECT ADDRESS.ID FROM CONTACT_X_CUSTOMER LEFT JOIN CUSTOMER ON
CONTACT_X_CUSTOMER.ID_CUSTOMER = CUSTOMER.ID

(thats the simple part, below is what I want, but it doesn't work of
course, but the logic is kinda there)

IF(CONTACT_X_CUSTOMER.ID_ADDRESS > 0, LEFT JOIN ADDRESS ON
CUSTOMER_X_CONTACT.ID_ADDRESS = ADDRESS.ID,
 IF(CUSTOMER.ID_ADDRESS_SHIPTO > 0, LEFT JOIN ADDRESS ON
CUSTOMER.ID_ADDRESS_SHIPTO = ADDRESS.ID,
 LEFT JOIN ADDRESS ON CUSTOMER.ID_ADDRESS_MAIN =
ADDRESS.ID))

Sorry if I didn't break that up clear like, it was an attempt to make it
more clear :)

Is there ANY way I could get this to work (on the lastest version of the
3.x mysql)


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







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







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

Re: JOINing complication, help please

[SGreen]

Luc,

This looks like you want a list of all Customers with Contacts (because you
are basing it on the CONTACT_X_CUSTOMER table) and you want to show the
Address (if it exists) or the Shipping Address (if it exists) instead of
the Address? Am I close?

If I want to get one of two or more result choices in a column, I use an
IF(), a CASE...END, an IFNULL(), a NULLIF() or a COALESCE() statement,
depending on what it is I am choosing between.

In your case I think you want to chose which address to use based on their
existence, in this case, if it exists, it won't be null:

SELECT x.ID_ADDRESS, c.customerName, COALESCE(s.ship_to_address, a.address,
'none') as address
FROM Customer c
INNER JOIN CONTACT_X_CUSTOMER x
  ON c.ID = x.CUSTOMER_ID
LEFT JOIN Address a
  ON a.ID = x.ID_ADDRESS
LEFT JOIN Address s
  ON s.ID = x.ID_ADDRESS_SHIPTO


In this statement, the COALESCE statement will resolve to be the first
non-null expression in the list. If there is no Address that matches your
_X_ table's ID_ADDRESS_SHIP to then all of the columns in the table aliased
as "s" will be NULL,  Same with ID_ADDRESS and the table aliased as  "a".
If neither address exists the string 'none' is the result.

HTH!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



   
  
  "Luc Foisy"  
  
  <[EMAIL PROTECTED]To:   "MYSQL-List (E-mail)" 
<[EMAIL PROTECTED]>
  -magic.com> cc:  
  
  Fax to:  
  
      06/02/2004 10:46 AM Subject:  JOINing complication, help 
please
   
  
   
  





CONTACT_X_CUSTOMER.ID_ADDRESS
CUSTOMER.ID_ADDRESS_SHIPTO
CUSTOMER.ID_ADDRESS_MAIN

What I would like is to be able to JOIN conditionally based on the
absence/presence of reference

SELECT ADDRESS.ID FROM CONTACT_X_CUSTOMER LEFT JOIN CUSTOMER ON
CONTACT_X_CUSTOMER.ID_CUSTOMER = CUSTOMER.ID

(thats the simple part, below is what I want, but it doesn't work of
course, but the logic is kinda there)

IF(CONTACT_X_CUSTOMER.ID_ADDRESS > 0, LEFT JOIN ADDRESS ON
CUSTOMER_X_CONTACT.ID_ADDRESS = ADDRESS.ID,
 IF(CUSTOMER.ID_ADDRESS_SHIPTO > 0, LEFT JOIN ADDRESS ON
CUSTOMER.ID_ADDRESS_SHIPTO = ADDRESS.ID,
 LEFT JOIN ADDRESS ON CUSTOMER.ID_ADDRESS_MAIN =
ADDRESS.ID))

Sorry if I didn't break that up clear like, it was an attempt to make it
more clear :)

Is there ANY way I could get this to work (on the lastest version of the
3.x mysql)


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







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

re: JOINing complication, help please

[Luc Foisy]

Ok, I got a result here, still trying to determine if its correct or not :)

LEFT JOIN ADDRESS ON IF(CONTACT_X_CUSTOMER.ID_ADDRESS > 0, 
CONTACT_X_CUSTOMER.ID_ADDRESS = ADDRESS.ID, IF(CUSTOMER.ID_ADDRESS_SHIPTO > 0, 
CUSTOMER.ID_ADDRESS_SHIPTO = ADDRESS.ID, CUSTOMER.ID_ADDRESS_MAIN = ADDRESS.ID))

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

JOINing complication, help please

[Luc Foisy]

CONTACT_X_CUSTOMER.ID_ADDRESS
CUSTOMER.ID_ADDRESS_SHIPTO
CUSTOMER.ID_ADDRESS_MAIN

What I would like is to be able to JOIN conditionally based on the absence/presence of 
reference

SELECT ADDRESS.ID FROM CONTACT_X_CUSTOMER LEFT JOIN CUSTOMER ON 
CONTACT_X_CUSTOMER.ID_CUSTOMER = CUSTOMER.ID

(thats the simple part, below is what I want, but it doesn't work of course, but the 
logic is kinda there)

IF(CONTACT_X_CUSTOMER.ID_ADDRESS > 0, LEFT JOIN ADDRESS ON 
CUSTOMER_X_CONTACT.ID_ADDRESS = ADDRESS.ID,
IF(CUSTOMER.ID_ADDRESS_SHIPTO > 0, LEFT JOIN ADDRESS ON 
CUSTOMER.ID_ADDRESS_SHIPTO = ADDRESS.ID,
LEFT JOIN ADDRESS ON CUSTOMER.ID_ADDRESS_MAIN = ADDRESS.ID))

Sorry if I didn't break that up clear like, it was an attempt to make it more clear :)

Is there ANY way I could get this to work (on the lastest version of the 3.x mysql)


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

Re: Unknown error, urgent help please

[Brian Reichert]

On Tue, May 18, 2004 at 10:08:32AM -0400, Ronnie Regev wrote:
> Hi,
> Before I go on, I know nothing about mysql, but my db admin is out of town
> and I need to be pointed in the right direction please.
> Running red hat 7.3, mysql-3.23.58-1.73, phpMyAdmin 2.5.0-rc2, in a virtual
> hosting environment on Ensim webppliance-3.5.20-7.
> 
> When using phpMyAdmin and attempting to upload a csv file, the following
> error is the result:

Maybe MySQL doesn't have permission to read '/tmp/php7T1P2m' ?

Maybe you don't have the right username/hostname/password?  I don't
know how PHP combines the details of DB access.

Everything PHP is doing, you should be able to replicate from the
mysql client...

> Ronnie Regev
> System Administrator
> Microsoft Certified Professional MCP
> Daslweb Inc.
> [EMAIL PROTECTED]

-- 
Brian Reichert  <[EMAIL PROTECTED]>
37 Crystal Ave. #303Daytime number: (603) 434-6842
Derry NH 03038-1713 USA BSD admin/developer at large

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

RE: Unknown error, urgent help please

[Ronnie Regev]

I have set all privileges in the user table to yes, as follows:
Select_priv enum  --Y  
Insert_priv enum  --Y  
Update_priv enum  --Y  
Delete_priv enum  --Y  
Create_priv enum  --   Y  
Drop_priv enum  --Y  
Reload_priv enum  --Y  
Shutdown_priv enum  --Y  
Process_priv enum  --Y  
File_priv enum  --Y  
Grant_priv enum  --Y  
References_priv enum  --   Y 
Index_priv enum  --Y  
Alter_priv enum  -- Y

What else can I try?
Thanks.

Ronnie

-Original Message-
From: Egor Egorov [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 18, 2004 10:16 AM
To: [EMAIL PROTECTED]
Subject: Re: Unknown error, urgent help please

"Ronnie Regev" <[EMAIL PROTECTED]> wrote:
> Hi,
> Before I go on, I know nothing about mysql, but my db admin is out of town
> and I need to be pointed in the right direction please.
> Running red hat 7.3, mysql-3.23.58-1.73, phpMyAdmin 2.5.0-rc2, in a
virtual
> hosting environment on Ensim webppliance-3.5.20-7.
> 
> When using phpMyAdmin and attempting to upload a csv file, the following
> error is the result:
>

> _
> Database domain_ca_-_stats - Table location running on localhost 
> Error
> 
> SQL-query :  
> 
> LOAD DATA INFILE '/tmp/php7T1P2m' INTO TABLE `location` FIELDS TERMINATED
BY
> ',' ENCLOSED BY '\'' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' 
> 
> MySQL said: 
> 
> 
> Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
> Back 
>

> 
> 
> The user in question has the appropriate permissions. Thanks about al I
> know.
> 
> Im sure this is such a simple problem, but unfortunately this molehill is
my
> mountain today.
> Thanks in advance, and my apologies for posting what will be such a simple
> problem.

Does user '[EMAIL PROTECTED]' have FILE privilege?



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




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


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

Re: Unknown error, urgent help please

[Egor Egorov]

"Ronnie Regev" <[EMAIL PROTECTED]> wrote:
> Hi,
> Before I go on, I know nothing about mysql, but my db admin is out of town
> and I need to be pointed in the right direction please.
> Running red hat 7.3, mysql-3.23.58-1.73, phpMyAdmin 2.5.0-rc2, in a virtual
> hosting environment on Ensim webppliance-3.5.20-7.
> 
> When using phpMyAdmin and attempting to upload a csv file, the following
> error is the result:
> 
> _
> Database domain_ca_-_stats - Table location running on localhost 
> Error
> 
> SQL-query :  
> 
> LOAD DATA INFILE '/tmp/php7T1P2m' INTO TABLE `location` FIELDS TERMINATED BY
> ',' ENCLOSED BY '\'' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' 
> 
> MySQL said: 
> 
> 
> Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
> Back 
> 
> 
> 
> The user in question has the appropriate permissions. Thanks about al I
> know.
> 
> Im sure this is such a simple problem, but unfortunately this molehill is my
> mountain today.
> Thanks in advance, and my apologies for posting what will be such a simple
> problem.

Does user '[EMAIL PROTECTED]' have FILE privilege?



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [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]

Unknown error, urgent help please

[Ronnie Regev]

Hi,
Before I go on, I know nothing about mysql, but my db admin is out of town
and I need to be pointed in the right direction please.
Running red hat 7.3, mysql-3.23.58-1.73, phpMyAdmin 2.5.0-rc2, in a virtual
hosting environment on Ensim webppliance-3.5.20-7.

When using phpMyAdmin and attempting to upload a csv file, the following
error is the result:

_
Database domain_ca_-_stats - Table location running on localhost 
Error

SQL-query :  

LOAD DATA INFILE '/tmp/php7T1P2m' INTO TABLE `location` FIELDS TERMINATED BY
',' ENCLOSED BY '\'' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' 

MySQL said: 


Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
Back 



The user in question has the appropriate permissions. Thanks about al I
know.

Im sure this is such a simple problem, but unfortunately this molehill is my
mountain today.
Thanks in advance, and my apologies for posting what will be such a simple
problem.

Ronnie Regev
System Administrator
Microsoft Certified Professional MCP
Daslweb Inc.
[EMAIL PROTECTED]


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

RE: Null-safe equal help, please

[Knepley, Jim]

The query in question:

SELECT switch, hostname, INET_NTOA(it_iFace.ip) AS IP, location,
contact, port
FROM cam
LEFT JOIN iFace ON ( switch = it_iFace.hostID )
LEFT JOIN host USING ( hostID )
LEFT JOIN arp ON ( cam.mac = arp.mac )
WHERE arp.ip <=> if( "" = "",NULL,INET_ATON("") );

(both instances of  are the same value in this case)
If IP is empty, this returns an empty set, if IP exists it gives me what
I expect.

Simplifying the query so the WHERE clause reads:
WHERE arp.ip <=> NULL
...returns an empty set.

Whereas:
WHERE arp.ip IS NULL
...returns tens of thousands of records.




-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, December 17, 2003 8:44 AM
To: Knepley, Jim
Cc: [EMAIL PROTECTED]
Subject: RE: Null-safe equal help, please

Please reply to the list so that others can follow this discussion.
Thanks.

At 8:26 -0700 12/17/03, Knepley, Jim wrote:
>
>
>-Original Message-
>From: Paul DuBois [mailto:[EMAIL PROTECTED]
>Sent: Tuesday, December 16, 2003 5:44 PM
>To: Knepley, Jim; [EMAIL PROTECTED]
>Subject: Re: Null-safe equal help, please
>
>At 15:22 -0700 12/16/03, Knepley, Jim wrote:
>>>I've got a WHERE clause:
>>>WHERE possibly_null_value IS NULL
>>>
>>>That works fine. This null-safe equal doesn't do what I expect:
>>>WHERE possibly_null_value <=> NULL
>>>
>>>The manual, and my testing, shows that NULL <=> NULL evaluates to 1,
>so
>
>>Are you saying that this is not what you expect?  Why not?
>
>It is what I expect, but it doesn't seem to be the behavior.

You indicated before that your own testing shows that NULL <=> NULL
evaluates to 1.

You now say that this *doesn't* seem to be the behavior.

I don't follow you.

>
>
>
>>>
>>>What I _really_ want to do is this:
>>>WHERE  possibly_null_value <=> INET_ATON()
>>>
>>>...so that if no IP is specificied it'll return those 
>>>possibly_null_value columns that are, in fact, NULL.
>
>>Your requirements are unclear.  I can see two ways to interpret that
>>statement:
>
>>1) You want only possibly_null_value values that are NULL.
>>2) You can possibly_null_value values that are NULL *and*, if IP is
>specified, possbly_null_value
>>values that are equal to INET_ATON(IP).
>
>>Those are not the same thing.  (In other words, it's clear what you
>want only for the case that IP is NULL.  >It's not clear what you want 
>when IP isn't NULL.)  Can you clarify?
>
>I can see where I was unclear, as I had simplified the statement (in an

>attempt to be more clear, go figure). I'm looking for case 2 that you 
>described.

In that case, it looks to me (without knowing more) that the statement
you show above should do what you want.  It apparently does not, so
perhaps you could post to the list a few combinations of
possibly_null_value and IP values. Indicate what results you get, and
how that differs from what you expect.

>
>
>
>Thanks for your time on this.
>
>Cheers,
> Jim


--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

Are you MySQL certified?  http://www.mysql.com/certification/



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

RE: Null-safe equal help, please

[Knepley, Jim]

 

-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, December 16, 2003 5:44 PM
To: Knepley, Jim; [EMAIL PROTECTED]
Subject: Re: Null-safe equal help, please

At 15:22 -0700 12/16/03, Knepley, Jim wrote:
>>I've got a WHERE clause:
>>WHERE possibly_null_value IS NULL
>>
>>That works fine. This null-safe equal doesn't do what I expect:
>>WHERE possibly_null_value <=> NULL
>>
>>The manual, and my testing, shows that NULL <=> NULL evaluates to 1,
so

>Are you saying that this is not what you expect?  Why not?

It is what I expect, but it doesn't seem to be the behavior.



>>
>>What I _really_ want to do is this:
>>WHERE  possibly_null_value <=> INET_ATON()
>>
>>...so that if no IP is specificied it'll return those 
>>possibly_null_value columns that are, in fact, NULL.

>Your requirements are unclear.  I can see two ways to interpret that
>statement:

>1) You want only possibly_null_value values that are NULL.
>2) You can possibly_null_value values that are NULL *and*, if IP is
specified, possbly_null_value 
>   values that are equal to INET_ATON(IP).

>Those are not the same thing.  (In other words, it's clear what you
want only for the case that IP is NULL.  >It's not clear what you want
when IP isn't NULL.)  Can you clarify?

I can see where I was unclear, as I had simplified the statement (in an
attempt to be more clear, go figure). I'm looking for case 2 that you
described.



Thanks for your time on this.

Cheers,
Jim


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

RE: Null-safe equal help, please

[Paul DuBois]

Please reply to the list so that others can follow this
discussion.  Thanks.
At 8:26 -0700 12/17/03, Knepley, Jim wrote:


-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 16, 2003 5:44 PM
To: Knepley, Jim; [EMAIL PROTECTED]
Subject: Re: Null-safe equal help, please
At 15:22 -0700 12/16/03, Knepley, Jim wrote:
I've got a WHERE clause:
WHERE possibly_null_value IS NULL
That works fine. This null-safe equal doesn't do what I expect:
WHERE possibly_null_value <=> NULL
The manual, and my testing, shows that NULL <=> NULL evaluates to 1,
so

Are you saying that this is not what you expect?  Why not?
It is what I expect, but it doesn't seem to be the behavior.
You indicated before that your own testing shows that NULL <=> NULL
evaluates to 1.
You now say that this *doesn't* seem to be the behavior.

I don't follow you.



What I _really_ want to do is this:
WHERE  possibly_null_value <=> INET_ATON()
...so that if no IP is specificied it'll return those
possibly_null_value columns that are, in fact, NULL.

Your requirements are unclear.  I can see two ways to interpret that
statement:

1) You want only possibly_null_value values that are NULL.
2) You can possibly_null_value values that are NULL *and*, if IP is
specified, possbly_null_value
   values that are equal to INET_ATON(IP).

Those are not the same thing.  (In other words, it's clear what you
want only for the case that IP is NULL.  >It's not clear what you want
when IP isn't NULL.)  Can you clarify?
I can see where I was unclear, as I had simplified the statement (in an
attempt to be more clear, go figure). I'm looking for case 2 that you
described.
In that case, it looks to me (without knowing more) that the statement
you show above should do what you want.  It apparently does not, so perhaps
you could post to the list a few combinations of possibly_null_value and IP
values. Indicate what results you get, and how that differs from what
you expect.


Thanks for your time on this.

Cheers,
Jim


--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

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

Re: Null-safe equal help, please

[Paul DuBois]

At 15:22 -0700 12/16/03, Knepley, Jim wrote:
I've got a WHERE clause:
WHERE possibly_null_value IS NULL
That works fine. This null-safe equal doesn't do what I expect:
WHERE possibly_null_value <=> NULL
The manual, and my testing, shows that NULL <=> NULL evaluates to 1, so
Are you saying that this is not what you expect?  Why not?

my now-fevered mind sees no reason the two above statements are not
equivalent.
IS NULL can be used only to test whether or not something is NULL.
<=> can be used to test NULL or any other value.
col_name1 IS NULL is true only if col_name1 is NULL.
col_name1 <=> col_name2 is true if col_name1 is the same as col_name2,
even when they're both NULL
col_name1 = col_name2 is true if col_name1 is the same as col_name2,
but only if both are *not* null.
What I _really_ want to do is this:
WHERE  possibly_null_value <=> INET_ATON()
...so that if no IP is specificied it'll return those
possibly_null_value columns that are, in fact, NULL.
Your requirements are unclear.  I can see two ways to interpret that
statement:
1) You want only possibly_null_value values that are NULL.
2) You can possibly_null_value values that are NULL *and*, if IP is specified,
possbly_null_value values that are equal to INET_ATON(IP).
Those are not the same thing.  (In other words, it's clear what you
want only for the case that IP is NULL.  It's not clear what you want
when IP isn't NULL.)  Can you clarify?
(Just as a test I've also tried possibly_null_value <=> NULLIF(
ISNULL(INET_ATON()), 1 ), which is wrong for my app, but still
broken)
MySQL 4.0.15-standard

Any insight would be much appreciated.


--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

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

Null-safe equal help, please

[Knepley, Jim]

I've got a WHERE clause:
WHERE possibly_null_value IS NULL
 
That works fine. This null-safe equal doesn't do what I expect:
WHERE possibly_null_value <=> NULL
 
The manual, and my testing, shows that NULL <=> NULL evaluates to 1, so
my now-fevered mind sees no reason the two above statements are not
equivalent.
 
What I _really_ want to do is this:
WHERE  possibly_null_value <=> INET_ATON()
 
...so that if no IP is specificied it'll return those
possibly_null_value columns that are, in fact, NULL.
(Just as a test I've also tried possibly_null_value <=> NULLIF(
ISNULL(INET_ATON()), 1 ), which is wrong for my app, but still
broken)
 
MySQL 4.0.15-standard
 
Any insight would be much appreciated.


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

RE: Upgrading help please

[Andrew Rothwell]

 Yup that worked - 
I rebooted my machine - and I was allowed in - 

Thank you very much

Andrew

-Original Message-
From: Brian Snyder [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, November 05, 2003 3:30 PM
Cc: MySQL
Subject: Re: Upgrading help please

Andrew,
I had the same problem and had to stop and restart the servers. Give that a
shot.

brian

On Wed, 2003-11-05 at 17:19, Andrew wrote:
> Good day List,
> I have just upgraded from 3.23 ->4.0.16
> 
> I downloaded all the RPM's and then ran rpm -U *.rpm
> 
> It did all that it was supposed to do, and then told me to use the 
> /usr/bin/mysql_fix_privilege_tables script
> 
> which I did got horrid errors
> ERROR 2002: Can't connect to local MySQL server through socket 
> '/var/lib/mysql/mysql.sock' (2)
> 
> So I specified
> mysql_fix_privilege_tables root_password
> 
> Same Errors (2002)
> 
> Tried the other method that is on the mysql manual 
> mysql_fix_privilege_tables --password=root_password
> 
> Same error 2002.
> 
> now Unfortunately I am unable to even connect to mysql with either 
> mysqladmin or the client.
> 
> MySQL-bench-4.0.16-0.i386.rpm
> MySQL-client-4.0.16-0.i386.rpm
> MySQL-devel-4.0.16-0.i386.rpm
> MySQL-embedded-4.0.16-0.i386.rpm
> MySQL-Max-4.0.16-0.i386.rpm
> MySQL-server-4.0.16-0.i386.rpm
> MySQL-shared-4.0.16-0.i386.rpm
> MySQL-shared-compat-4.0.16-0.i386.rpm
> 
> is a list of the RPMS that I downloaded and ran.
> 
> Any ideas and help would be most appreciated.
> 
> Thank you
> Andrew
> 
> 
> Message sent using UebiMiau 2.7.2
> 


--
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: Upgrading help please

[Brian Snyder]

Andrew,
I had the same problem and had to stop and restart the servers. Give
that a shot.

brian

On Wed, 2003-11-05 at 17:19, Andrew wrote:
> Good day List,
> I have just upgraded from 3.23 ->4.0.16
> 
> I downloaded all the RPM's and then ran
> rpm -U *.rpm
> 
> It did all that it was supposed to do, and then told me to use the
> /usr/bin/mysql_fix_privilege_tables script
> 
> which I did got horrid errors
> ERROR 2002: Can't connect to local MySQL server through socket
> '/var/lib/mysql/mysql.sock' (2)
> 
> So I specified
> mysql_fix_privilege_tables root_password
> 
> Same Errors (2002)
> 
> Tried the other method that is on the mysql manual
> mysql_fix_privilege_tables --password=root_password
> 
> Same error 2002.
> 
> now Unfortunately I am unable to even connect to mysql with either
> mysqladmin or the client.
> 
> MySQL-bench-4.0.16-0.i386.rpm
> MySQL-client-4.0.16-0.i386.rpm
> MySQL-devel-4.0.16-0.i386.rpm
> MySQL-embedded-4.0.16-0.i386.rpm
> MySQL-Max-4.0.16-0.i386.rpm
> MySQL-server-4.0.16-0.i386.rpm
> MySQL-shared-4.0.16-0.i386.rpm
> MySQL-shared-compat-4.0.16-0.i386.rpm
> 
> is a list of the RPMS that I downloaded and ran.
> 
> Any ideas and help would be most appreciated.
> 
> Thank you
> Andrew
> 
> 
> Message sent using UebiMiau 2.7.2
> 


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

Upgrading help please

[Andrew]

Good day List,
I have just upgraded from 3.23 ->4.0.16

I downloaded all the RPM's and then ran
rpm -U *.rpm

It did all that it was supposed to do, and then told me to use the
/usr/bin/mysql_fix_privilege_tables script

which I did got horrid errors
ERROR 2002: Can't connect to local MySQL server through socket
'/var/lib/mysql/mysql.sock' (2)

So I specified
mysql_fix_privilege_tables root_password

Same Errors (2002)

Tried the other method that is on the mysql manual
mysql_fix_privilege_tables --password=root_password

Same error 2002.

now Unfortunately I am unable to even connect to mysql with either
mysqladmin or the client.

MySQL-bench-4.0.16-0.i386.rpm
MySQL-client-4.0.16-0.i386.rpm
MySQL-devel-4.0.16-0.i386.rpm
MySQL-embedded-4.0.16-0.i386.rpm
MySQL-Max-4.0.16-0.i386.rpm
MySQL-server-4.0.16-0.i386.rpm
MySQL-shared-4.0.16-0.i386.rpm
MySQL-shared-compat-4.0.16-0.i386.rpm

is a list of the RPMS that I downloaded and ran.

Any ideas and help would be most appreciated.

Thank you
Andrew


Message sent using UebiMiau 2.7.2


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

SQL Help please

[Dave Shelley]

SQL guru's,

I could use some help writing a bit of SQL.
There's 3 tables:
orderable_parts
partID varchar,
topCatID int,  # top level category ID
...
part_attributes
partID varchar,
attName varchar,
attValue varchar,
...
topcatattributevalues
tcavID int,
topCatID int,
attName varchar,
attValue varchar,
...
orderable_parts has parts available on our web site. Theres about 40,000 of 
them
part_attributes are related to parts. Color, size etc. ~150,000 rows
topcatattributevalues is a list of all the distinct part attributes 
available in each top level category. They're used for web searches and for 
product managers to edit orderable_parts, ~100,000 rows

I'm trying to build a function to delete records from topcatattributevalues 
that are not used in any orderable_part. Either 1 delete statement or an 
update status=-1 and a delete where status=-1 would work. But it looks to me 
like I need a 3 way outer join and I can't seem to get it to work.

I tried a variety of statements that look something like:

update topcatattributevalues t
left outer join orderable_parts o on (t.topCatID=o.topCatID),
left outer join part_attributes p on (t.attName=p.attName and 
t.attValue=p.attValue and o.partID=p.partID)
set t.status=-1
where o.partID is null

I can't seem to get it.

Any assistance would be greatly appreciated.

Thanks.
Dave.
_
MSN 8 with e-mail virus protection service: 2 months FREE*  
http://join.msn.com/?page=features/virus

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

Re: HELP PLEASE the weirdest error 2013 / connection ?

[[EMAIL PROTECTED]]

Thanks Martin,

No I do not have that option unfortunately. I really do suspect that it has 
nothing to do with mysql and that rather it is the router/IPs configuration 
and called them upon this, but they said all is fine. So I frankly have no 
clue where to go from here.

thanks again for any suggestions

ps: what is a flaky IP address?

Stew

At 03:10 PM 9/9/2003 -0700, Martin Gainty wrote:
Sounds like the First ISP is providing a IP Address that is flaky.
Can you drop the flaky first ISP and go exclusively with the second?
-Martin
- Original Message -
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, September 09, 2003 11:50 AM
Subject: HELP PLEASE the weirdest error 2013 / connection ?
>
> Hi,
>
> this is the weirdest error I have ever encountered.
>
> We get an error 2013 lost connection when we try to connect via mysql to
> another machine having mysql on it as well.   The weird part is that when
> we change the IP address of the connecting machine to another service
> provider it works fine. The problem does not happen the other way around,
> and the machines have the exact same configuration and both machine's
> mysql's have been upgraded to have the exact same version when we ran out
> of options on how to solve this. We checked passwords/hosts/users
> etc...  It just does not make sense and we frankly ran out of options.
> Please help if you can. thanks
>
> Stew
>


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

HELP PLEASE the weirdest error 2013 / connection ?

[[EMAIL PROTECTED]]

Hi,

this is the weirdest error I have ever encountered.

We get an error 2013 lost connection when we try to connect via mysql to 
another machine having mysql on it as well.   The weird part is that when 
we change the IP address of the connecting machine to another service 
provider it works fine. The problem does not happen the other way around, 
and the machines have the exact same configuration and both machine's 
mysql's have been upgraded to have the exact same version when we ran out 
of options on how to solve this. We checked passwords/hosts/users 
etc...  It just does not make sense and we frankly ran out of options. 
Please help if you can. thanks

Stew

RE: Problem Query - Help Please

[Jim Smith]

> When I execute the following query I get duplicate
> product_id's as shown
> below:
>
> SELECT * FROM product, product_category_xref, category WHERE
> product_parent_id=''
> AND product.product_id=product_category_xref.product_id
> AND category.category_id=product_category_xref.category_id
> AND product.product_publish='Y'
> AND product.product_special='Y'  ORDER BY product_name DESC\G
>
>
> Results ( As you can see product_id 4139 occurs twice and I desire the
> product_id's to be unique in this query)
>
> I have also included the descriptions of the tables.
>
> I would appreciate someone assisting me with a query that
> works correctly.

Product 2139 has two different categories BOOKS and EDUCATION and therefore
appears twice.


   category_id: 7920cfab5c630ca88ceabcfda6b3848d
product_id: 4139
  product_list: NULL
   category_id: 7920cfab5c630ca88ceabcfda6b3848d
 vendor_id: 1
 category_name: BOOKS

   category_id: 4ee8c8513ee84c95c8eb7f24e63d7222
product_id: 4139
  product_list: NULL
   category_id: 4ee8c8513ee84c95c8eb7f24e63d7222
 vendor_id: 1
 category_name: EDUCATION

If you need to show all the categories you will probably need to retrieve
thos separately
 and build a list programatically to give something like

 categories: EDUCATION, BOOKS


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

Problem Query - Help Please

[Paul Maine]

When I execute the following query I get duplicate product_id's as shown
below:

SELECT * FROM product, product_category_xref, category WHERE
product_parent_id=''
AND product.product_id=product_category_xref.product_id
AND category.category_id=product_category_xref.category_id
AND product.product_publish='Y'
AND product.product_special='Y'  ORDER BY product_name DESC\G


Results ( As you can see product_id 4139 occurs twice and I desire the
product_id's to be unique in this query)

I have also included the descriptions of the tables.

I would appreciate someone assisting me with a query that works correctly.

*** 1. row ***
product_id: 4199
 vendor_id: 1
 product_parent_id: 0
   product_sku: ToBeAs
product_s_desc: Coming Soon! Preorder Today!
A series of essays on the influential thinkers and ideas in modern times.
  product_desc: Coming Soon! Preorder Today!
By R.J. Rushdoony. This monumental work is a series of essays on the
influential thinkers and ideas in modern times. The author begins with De
Sade, who self-consciously broke with any Christian basis for morality and
law. Enlightenment thinking began with nature as the only reality, and
Christianity was reduced to one option among many. It was then, in turn,
attacked as anti-democratic and anti-freedom for its dogmatic assertion of
the supernatural. Literary figures such as Shelly, Byron, Whitman, and more
are also examined, for the Enlightenment presented both the intellectual and
the artist as replacement for the theologian and his church. Ideas, such as
the spirit of the age, truth, reason, Romanticism, persona, and Gnosticism
are related to the desire to negate God and Christian ethics. Reading this
book will help you understand the need to avoid the syncretistic blending of
humanistic philosophy with the Christian faith.
Paperback, 230 pages, and indices.
   product_thumb_image: 62c16392f436313324d9922ecf2f5a30.jpg
product_full_image: d99c1de85355c6bc853102a4d85065b3.jpg
   product_publish: Y
product_weight: 0.
product_weight_uom: pounds
product_length: 0.
 product_width: 0.
product_height: 0.
   product_lwh_uom: inches
   product_url:
  product_in_stock: 0
product_available_date: 0
   product_special: y
   product_discount_id: 0
  ship_code_id: NULL
 cdate: 1057785021
 mdate: 1059273555
  product_name: To Be As God: A Study of Modern Thought Since the
Marquis De Sade
  product_discount_use:
   category_id: 7920cfab5c630ca88ceabcfda6b3848d
product_id: 4199
  product_list: NULL
   category_id: 7920cfab5c630ca88ceabcfda6b3848d
 vendor_id: 1
 category_name: BOOKS
  category_description:
  category_thumb_image: NULL
   category_full_image: NULL
  category_publish: Y
  menu_image_1:
  menu_image_2:
  menu_image_3:
page_image:
 cdate: 1028759226
 mdate: 1028759226
  category_flypage:
   category_browsepage:
*** 2. row ***
product_id: 4139
 vendor_id: 1
 product_parent_id: 0
   product_sku: Victims
product_s_desc: The decline of Americas public education - how and
why.
  product_desc: By Samuel L. Blumenfeld. Americas most
effective critic of public education shows us how Americas public schools
were remade by educators who used curriculum to create citizens suitable for
their own vision of a utopian socialist society. This collection of essays
will show you how and why Americas public education declined. You will see
the educator-engineered decline of reading skills. The author describes the
causes for the decline and the way back to competent education methodologies
that will result in a self-educated, competent, and freedom-loving populace.
Paperback, 266 pages, and index.
   product_thumb_image: 63ad73b92ddd18d83eb6942914bcf277.jpg
product_full_image: 1440c376576aba8783f183ff145c248b.jpg
   product_publish: Y
product_weight: 0.
product_weight_uom: pounds
product_length: 0.
 product_width: 0.
product_height: 0.
   product_lwh_uom: inches
   product_url:
  product_in_stock: 0
product_available_date: 0
   product_special: y
   product_discount_id: 0
  ship_code_id: NULL
 cdate: 1056405288
 mdate: 1061947639
  product_name: The Victims of Dick and Jane
  product_discount_use:
   category_id: 7920cfab5c630ca88ceabcfda6b3848d
product_id: 4139
  product_list: NULL
   category_id: 7920cfab5c630ca88ceabcfda6b3848d
 vendor_id: 1
 category_name: BOOKS
  category_description:
  category_thumb_image: NULL
   category_full_image: 

RE: Table Query taking WAY TO LONG...HELP PLEASE!!!

[Twibell, Cory L]

All, the tables are defined as MyISAM. In fact, I left last night and
the Query was still running the convert HEAP to MyISAM Somethings
definately wrong.
I have indices on Name.key, Location.key and Location.cc

the results of the explain :
id| select_type| table   | type | possible keys| key
|key_len| ref   |rows | Extra

---
1 | SIMPLE | LOCATION|range |PRIMARY,LOC_CC_IDX|LOC_CC_IDX  |2
|NULL   |55248|
1 | SIMPLE | NAME|ref   |NAME_KEY_IDX  |NAME_KEY_IDX|15
|LOC_IDX|2|

It looks like it's using the correct indicesI don't know what else
to do...

-Original Message-
From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 27, 2003 12:23 AM
To: Twibell, Cory L
Cc: [EMAIL PROTECTED]
Subject: Re: Table Query taking WAY TO LONG...HELP PLEASE!!!


On Tue, Aug 26, 2003 at 07:38:04PM -0600, Twibell, Cory L wrote:
> All,
> 
> I have a query that is inner joined with another table based on
> country codes Select distinct Name.* from Name inner join Location
> on Location.key = Name.key and Location.cc in (' here>');
> 
> The problem is when I have more than 2 country codes, the query
> takes forever...  When I show processlist, it says converting HEAP
> to MyISAM ( this takes over an hour )...  I've tried bumping up
> max_heap_table_size to 128M and tmp_table_size to 128M, but that
> only seems to delay the converting HEAP to MyISAM message
> 
> Name contains about 3 million records and Location contains about 1.5
million records.

The table is a HEAP table?

I don't think the optimizer knows how to make an IN(...) query in a
HEAP table fast.  Have you tried this with a MyISAM table?  It should
be pretty fast, assuming you're pulling back less than 1/4th of the
rows with that query.

Can you show us the EXPLAIN output from the query?

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 4.0.13: up 25 days, processed 1,141,718,690 queries (517/sec. avg)

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


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

Re: Table Query taking WAY TO LONG...HELP PLEASE!!!

[Keith C. Ivey]

On 26 Aug 2003 at 19:38, Twibell, Cory L wrote:

> I have a query that is inner joined with another table based on
> country codes Select distinct Name.* from Name inner join Location on
> Location.key = Name.key and Location.cc in (' here>');

>From the message you're getting it seems you're using a heap table.  
Why is that?  In the documentation it says that for heap tables 
"Indexes will only be used with = and <=> (but are VERY fast)."

The indexes for heap tables are hash-based, which means they can't be 
used for range queries (since the hash values for consecutive keys 
won't be consecutive).  But if you're using IN in you're query (with 
more than one value in the list) then you need to get a range from 
the index.  Since that's not possible with a heap table, the whole 
table must be scanned.

-- 
Keith C. Ivey <[EMAIL PROTECTED]>
Tobacco Documents Online
http://tobaccodocuments.org


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

Re: Table Query taking WAY TO LONG...HELP PLEASE!!!

[Jeremy Zawodny]

On Tue, Aug 26, 2003 at 07:38:04PM -0600, Twibell, Cory L wrote:
> All,
> 
> I have a query that is inner joined with another table based on
> country codes Select distinct Name.* from Name inner join Location
> on Location.key = Name.key and Location.cc in (' here>');
> 
> The problem is when I have more than 2 country codes, the query
> takes forever...  When I show processlist, it says converting HEAP
> to MyISAM ( this takes over an hour )...  I've tried bumping up
> max_heap_table_size to 128M and tmp_table_size to 128M, but that
> only seems to delay the converting HEAP to MyISAM message
> 
> Name contains about 3 million records and Location contains about 1.5 million 
> records.

The table is a HEAP table?

I don't think the optimizer knows how to make an IN(...) query in a
HEAP table fast.  Have you tried this with a MyISAM table?  It should
be pretty fast, assuming you're pulling back less than 1/4th of the
rows with that query.

Can you show us the EXPLAIN output from the query?

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 4.0.13: up 25 days, processed 1,141,718,690 queries (517/sec. avg)

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

Re: Table Query taking WAY TO LONG...HELP PLEASE!!!

[Martin Gainty]

Indexing columns will help but you *may* need to take a peek at some other
factors such as Memory Management
Read Jeremy's article on MySQL Performance Tuning
http://www.linux-mag.com/2001-12/mysql_01.html

Hth,
Martin

- Original Message -
From: "Roger Baklund" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: "Twibell, Cory L" <[EMAIL PROTECTED]>
Sent: Tuesday, August 26, 2003 7:19 PM
Subject: Re: Table Query taking WAY TO LONG...HELP PLEASE!!!


> * Twibell, Cory L
> > I have a query that is inner joined with another table based on
> > country codes
> > Select distinct Name.* from Name inner join Location on Location.key =
> > Name.key and Location.cc in ('');
> >
> > The problem is when I have more than 2 country codes, the query
> > takes forever...
> [...]
>
> Please show the output of the following commands:
>
> SHOW CREATE TABLE Name;
> SHOW CREATE TABLE Location;
> EXPLAIN Select distinct Name.*
>   from Name inner join Location on
> Location.key =  Name.key and
> Location.cc in ('');
>
> You probably need to define some indexes:
>
> http://www.mysql.com/doc/en/MySQL_indexes.html >
> http://www.mysql.com/doc/en/CREATE_INDEX.html >
>
> --
> Roger
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>

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

Re: Table Query taking WAY TO LONG...HELP PLEASE!!!

[Roger Baklund]

* Twibell, Cory L 
> I have a query that is inner joined with another table based on 
> country codes
> Select distinct Name.* from Name inner join Location on Location.key = 
> Name.key and Location.cc in ('');
> 
> The problem is when I have more than 2 country codes, the query 
> takes forever...
[...]

Please show the output of the following commands:

SHOW CREATE TABLE Name;
SHOW CREATE TABLE Location;
EXPLAIN Select distinct Name.* 
  from Name inner join Location on 
Location.key =  Name.key and 
Location.cc in ('');

You probably need to define some indexes:

http://www.mysql.com/doc/en/MySQL_indexes.html >
http://www.mysql.com/doc/en/CREATE_INDEX.html >

-- 
Roger


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

Table Query taking WAY TO LONG...HELP PLEASE!!!

[Twibell, Cory L]

All,

I have a query that is inner joined with another table based on country codes
Select distinct Name.* from Name inner join Location on Location.key = 
Name.key and Location.cc in ('');

The problem is when I have more than 2 country codes, the query takes forever...
When I show processlist, it says converting HEAP to MyISAM ( this takes over an hour 
)...
I've tried bumping up max_heap_table_size to 128M and tmp_table_size to 128M, but that 
only 
seems to delay the converting HEAP to MyISAM message

Name contains about 3 million records and Location contains about 1.5 million records.

Using MySQL 4.0.14-pro

Thanks,

Cory Twibell
Lockheed Martin Space Systems Company
Electronic Combat Development Systems
[EMAIL PROTECTED]
(303) 971-3184


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

Help Please: Substitute Text to replace Stored Procedures for use with MySQL

[Chrisbarbers]

Hi

 

I have purchased a package that uses ASP and SQL, my webhost provides MYSQL 
and I have loaded my access database and all seems to be working fine. 

Except the stored procedures (Queries) the webhost says MYSQL doesn't support 
stored procedures, as this site confirms (wait for v5.0) but the same effect 
can be achieved using ASP, an example of what they offer in help is below: 

SQLQuery = "SELECT * FROM tblAPCategories"
Set RS = gsconnect.Execute(SQLQuery)
Do While Not RS.EOF
%>
<%=RS("catID")%>, etc... 

This works fine for dumping the contents of these fields to the screen, my 
problem is that my ASP app calls stored procedures in the following way: 

sSQL = "qdpAPGetMaxCatLevel" etc... the variable seems to be called 
repeatedly after this, I'm assuming sSQL is the variable, the query for this is: 

SELECT Max(catLevel) AS calcMaxLevel
FROM tblAPCategories; 

The answer is 3 which I can get to using the ASP text suggested, but I can't 
seem to work out how to replace the call to the stored procedure with the ASP 
text to make the ASP function as intended, it just crashes out at the point 
this is called, and believe me I have tried everything, except what works 
obviously.

I even tried creating a table with the same name and typing in the answer 
just to see if I could further, it knew it was a table and stopped just the same.

Can anyone help, if I can crack one I think I can crack the rest, although to 
be fair this appears to be one of the easier queries. 

Thanks in advance for anyone's help. 

Chris

RE: Help please

[Rob]

Are you sure the mysql service is running?  You can either check in the
control panel, but an easier is way is to launch 
WinMySQLadmin, which should come with your distribution.  Check in the
bin directory for the following file winmysqladmin.exe.  Launching this
program will start the service automatically.  It will then minimize
itself on your toolbar as an icon.  You can accessed it from there to
turn the service on/off

-Original Message-
From: Huw Parker [mailto:[EMAIL PROTECTED] 
Sent: Thursday, June 12, 2003 1:56 PM
To: [EMAIL PROTECTED]
Subject: Help please


Hi people.

Installing MySQL 3.23.54 under Windows 2000
(according to instructions in Next handbook 'Introduction to MySQL.',
and from CD attached to book)

Installing MySQL
- worked fine.

Starting the Server
c:\mysql\bin\mysqld-nt
- worked fine.

Running the MySQL command line interface
c:\mysql\bin\mysql
- fails with message
ERROR 2003: Can't connect to MySQL server on 'localhost' (10061)

Have installed / uninstalled / tried several times, always fails at this
point. Can you assist please?

Thanks very much,

Huw Parker


All information contained in this email is confidential and may be used
by the intended recipient only.


All information contained in this email is confidential and may be used by the 
intended recipient only.

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

Help please

[Huw Parker]

Hi people.

Installing MySQL 3.23.54 under Windows 2000
(according to instructions in Next handbook 'Introduction to MySQL.', and
from CD attached to book)

Installing MySQL
- worked fine.

Starting the Server
c:\mysql\bin\mysqld-nt
- worked fine.

Running the MySQL command line interface
c:\mysql\bin\mysql
- fails with message
ERROR 2003: Can't connect to MySQL server on 'localhost' (10061)

Have installed / uninstalled / tried several times, always fails at this
point. Can you assist please?

Thanks very much,

Huw Parker

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

Qwery help please

[Jay Blanchard]

I have two tables;
TESTtblRC
++++-+
| id | npanxx | RCname | RCstate |
++++-+
|  1 | 11 | ONE| CA  |
|  2 | 22 | ONE| CA  |
|  3 | 33 | ONE| CA  |
|  4 | 44 | TWO| CA  |
|  5 | 55 | TWO| CA  |
|  6 | 66 | THREE  | CA  |
|  7 | 77 | FOUR   | CA  |
|  8 | 88 | FOUR   | CA  |
|  9 | 99 | FIVE   | CA  |
++++-+

TESTtblRCscope
++-+-+
| id | originating | terminating |
++-+-+
|  1 | ONE | TWO |
|  2 | ONE | THREE   |
|  3 | TWO | ONE |
|  4 | THREE   | ONE |
|  5 | FOUR| FIVE|
|  6 | FIVE| FOUR|
++-+-+

Given an 'originating' I want to return a distinct list of 'RCname'
(from 1st table) that does not match the originating or terminating in
the second table or itself. So, given an originating of 'ONE' I should
return Rcname's FOUR and FIVE (ONE is iteself, TWO and THREE are its
terminatings) TIA!

Jay

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

RE: mysql install---help please

[Black, Kelly W [PCS]]

Sorry you would use the name of the system, or the ip like so...
(in pseudo terms)
mysql -u yourpersonaluserid -p -h thehostiuse.com
or
mysql -u yourpersonalloginid -p -h ipaddress (such as 203.12.34.56)

sql query
~KB 


-Original Message-
From: katherine bjork [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 25, 2003 4:13 PM
To: Black, Kelly W [PCS]
Subject: Re: mysql install---help please


I typed in

  /usr/local/mysql/bin/mysql -u userid -p -h localhost password:

prompted for password, gave only one I ever use
response was
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: 
YES)

where userid was my login

On Tuesday, March 25, 2003, at 03:50 PM, Black, Kelly W [PCS] wrote:

> Try
>
> /usr/local/mysql/bin/mysql -u userid -p -h hostname
> password:
>
> mysql>
>
> Regards,
>
> Kelly Black
>
> Linux was very clearly the answer, but what was the question again?
>
>
> -Original Message-
> From: Jennifer Goodie [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, March 25, 2003 3:16 PM
> To: katherine bjork; mysql
> Subject: RE: mysql install---help please
>
>
> You said that you changed the password to the password you wanted, but 
> in
> all the examples you are trying to access the server without a 
> password.
> Try using the -p flag so it prompts you for your password, then type in
> whatever you set the password to be.
>
>
> -Original Message-
> From: katherine bjork [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, March 25, 2003 3:02 PM
> To: mysql
> Subject: mysql install---help please
>
>
> Tried for the second time to install mysql on my mac and again a
> problem during the install related to the password.
>
> I typed in
>
> /usr/local/mysql/bin/mysqladmin -u root password 'new-password'
>
> and changed 'new-password' to the password I wanted.
>
> Got the -->
>   on the next line but nothing else so I assumed it worked.
>
> When I try to do anything such as check to see if mysql and php are
> happy I get
>
> Warning:  mysql_connect() [function.mysql-connect]: Access denied for
> user: '[EMAIL PROTECTED]' (Using password: NO) in
> /Library/WebServer/Documents/mysql_up2.php on line 5
> Warning:  mysql_select_db() [function.mysql-select-db]: Access denied
> for user: '[EMAIL PROTECTED]' (Using password: NO) in
> /Library/WebServer/Documents/mysql_up2.php on line 9
> Warning:  mysql_select_db() [function.mysql-select-db]: A link to the
> server could not be established in
> /Library/WebServer/Documents/mysql_up2.php on line 9
> Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)
>
> When I go back and try /usr/local/mysql/bin/mysqladmin -u root password
> 'new-password' again I get
>
> /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' 
> failed
> error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)'
>
> Also tried
>
> /usr/local/mysql/bin/mysqladmin -u root -h $hostname password
> 'new-password'
>
> but was denied.
>
> How the heck do I give myself access to mysql?
>
> Please don't point me to the mysql.org page...been there I do not
> understand the "grants et al". Need plain english as in explicit
> instruction on what to do. BTW version is 4 on mac osx running apache
> 1.3 with php 4.3
>
> Katherine
>
>
>
> -- 
> 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]