problem with making mysql accessible from other machines

[Shannon R.]

good day all!


i'm getting really confused with how to properly allow
users to access mysql from other machines.

that is, i tried this as mysql root user:


GRANT SELECT, INSERT, UPDATE, DELETE ON my_sql_db.* TO
[EMAIL PROTECTED] IDENTIFIED BY 'password';


after this, i can access mysql as 'dbuser' from the
localhost. the mysql servers ip add by the way is
192.168.1.1, and since i also want that user to be
able to access from 192.168.1.2, i also did this
command:


GRANT SELECT, INSERT, UPDATE, DELETE ON my_sql_db.* TO
[EMAIL PROTECTED] IDENTIFIED BY 'password';


that should have been it but whenever i try to access
mysql from 192.168.1.2, i keep getting an "Can't
connect to MySQL server" error.

did i do anything wrong? that's what the documentation
said and i just followed it.

also, what if i also want to allow mysql access from
192.168.1.3? but not with the rest of the addresses on
192.168.0.0? how do i do that?

and there's yet another thing which confused me. if i
do 'mysql -u root -p' from the mysql server itself, i
can connect to it, but if i do 'mysql -u root -p -h
192.168.0.1', or just pointing to itself, i get the
same 'cant connect to mysql' error. anyone can shed
any light on why is this so?


thanks!
shannon




__
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail

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

Query on multiple table with SUM and GROUP

[Denis Geser]

I am using MySql to store telephone taxes statistics.
I have 1 table by month with 60'000 rows each.

My problem is that I need some stats for 3 month.
For example I need to know the total Time(in second) and total Cost
for each Number on a 3 month basis.

Is it possible to do it without creating new tables, and with only one query?

The expectec result would be:

NumberTotal Time Total Cost
501   1690.80
502610.30

CREATE TABLE may (Number VARCHAR(10), Time INTEGER, Cost DECIMAL(5,2));
CREATE TABLE june (Number VARCHAR(10), Time INTEGER, Cost DECIMAL(5,2));

insert may values ("501", 31, 0.10);
insert may values ("502", 51, 0.20);
insert may values ("501", 93, 0.40);
insert may values ("501", 25, 0.10);

insert june values ("501", 10, 0.10);
insert june values ("502", 10, 0.10);
insert june values ("501", 10, 0.10);

Regards

Denis

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

Re: what os to use for mysql on amd64?

[Jeremy Zawodny]

On Fri, Jul 30, 2004 at 11:46:02AM -0500, Pete Harlan wrote:
> On Thu, Jul 29, 2004 at 06:26:23PM +0300, Egor Egorov wrote:
> ...
> > No. I've forgot to tell that the -Max binary is linked dynamically
> > because it uses SSL.
> 
> Is there a reason the SSL libraries can't also be linked statically?

I think it's a licensing issue, but I haven't looked at the OpenSSL
license and don't remember the specifics from when I last heard.

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

[book] High Performance MySQL -- http://highperformancemysql.com/

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

RE: Installation Problem with 4.0.20

[dave.prout]

Thanks very much Michael, you were right.

I spent hours worrying about this yesterday. The documentation implied
that you don't have to log on at all.

Cheers

Dave 

> -Original Message-
> From: Michael Stassen [mailto:[EMAIL PROTECTED] 
> Sent: 02 August 2004 05:11
> To: Prout,DA,Dave,XSJ67 PROUTDA R
> Cc: [EMAIL PROTECTED]
> Subject: Re: Installation Problem with 4.0.20
> 
> Which mysql username did you use to log in?  I've not used 
> mysql on Windows, but I expect that if you simply ran 
> mysqlshow on a new install, you'd connect as the anonymous 
> user.  The anonymous user only has rights to the test db, so 
> that's all you'd see.  Try
> 
>mysqlshow -u root
> 
> or, if you've set the root password,
> 
>mysqlshow -u root -p
> 
> instead.
> 
> Michael
> 
> [EMAIL PROTECTED] wrote:
> 
> > I've just downloaded 4.0.20 for windows
> >  
> > Installed on the D: drive. Set up an .ini pointing at D: drive
> >  
> > Ran  D:\mysql\bin\mysqld
> >  
> > When I then ran mysqlshow I only had the test database, not 
> the mysql 
> > database as the documentation said I should.
> >  
> > I then deleted everything and installed again, and got the 
> same result.
> >  
> > Does this mean the installation has failed ?
> >  
> > Dave
> > UK
> 
> 

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

Re: Locking in MyISAM

[Jeremy Zawodny]

On Fri, Jul 23, 2004 at 01:44:16PM -0400, Michael Sleman wrote:
> Does MyISAM lock the whole table when doing SELECTs?

Yes, each client obtains a read lock on the table for a SELECT query.

> How about if there are several processors? Is there locking?

It's no different with multiple CPUs.

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

[book] High Performance MySQL -- http://highperformancemysql.com/

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

Re: perpetual "connecting to master"

[Jeremy Zawodny]

On Sun, Aug 01, 2004 at 08:42:50PM +0300, Issac Goldstand wrote:
> I just set up another reeplication slave for one of my servers - it's not
> something new to me.  I'm using 4.1.3-beta on a new server which will
> eventually take over as the master.   I set up a server id, did change
> master to... and started the slave - but the slave seems to perpetually stay
> in a state of "Connecting to master...".  I've done thie before, but I've
> never encounterred this - can anyone give pointers on how to troubleshoot?

The first thing I'd try is to connect from the slave machine to the
master machine using the same credentials.

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

[book] High Performance MySQL -- http://highperformancemysql.com/

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

Re: using mysql in commercial software

[Jeremy Zawodny]

On Mon, Jul 26, 2004 at 01:26:15PM -0500, gerald_clark wrote:
> 
> Steve Richter wrote:
> 
> >exactly!  Is Linux distributed under the same type of license as MySql?  If
> >I sell software that runs on linux I dont have to give away my code, right?
> >To use my software you first have to install no charge Linux and MySql.  Why
> >would that not be permitted?
>
> Because the MySQL license does not allow you to use it free with
> commercial software that requires MySQL.  If you are running
> commercial software that requires MySQL you must buy a license.

And this is where the confusion start.  MySQL is covered by the GPL.
So is Linux.

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

[book] High Performance MySQL -- http://highperformancemysql.com/

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

Re: using mysql in commercial software

[Jeremy Zawodny]

On Mon, Jul 26, 2004 at 05:43:58PM -0400, Steve Richter wrote:
> 
> looks like the answer is no.  As soon as fee based software touches the
> mysql install on the PC, the user is obligated to pay the $250.  At least I
> guess it is the user who has to pay.

So a copy of MySQL on Windows is free until I buy a copy of MS Access
to build forms that display the data stored in MySQL?

I don't think your "fee based software" claim is accurate at all.

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

[book] High Performance MySQL -- http://highperformancemysql.com/

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

Re: using mysql in commercial software

[Jeremy Zawodny]

On Mon, Jul 26, 2004 at 11:49:02PM -0500, mos wrote:
> At 04:43 PM 7/26/2004, you wrote:
> 
> >looks like the answer is no.  As soon as fee based software touches the
> >mysql install on the PC, the user is obligated to pay the $250.
> 
> Actually I believe the MySQL 4.x license is more stringent than that. If 
> the MySQL database is distributed within a company for free, then a license 
> is still required, unless the application is distributed under the
> GPL.

Huh?

I bet it depends on the country.  In the USA, companies are considered
singular legal entities.  "Internal distribution" is aking to giving
copies to yourself.

The GPL doesn't restrict that.

> Wouldn't it be nice if MySQL created a nice little table with a
> couple of columns "License" and "No License" and a bunch of examples
> down the left hand side?

Yes, it would de-mystify a lot of this.

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

[book] High Performance MySQL -- http://highperformancemysql.com/

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

Re: perpetual "connecting to master"

[Issac Goldstand]

Don't I feel stupid.  It was the password.  I'd actually pasted the password
into both the master and slave (and did it again just in case yesterday), so
I feel quite foolish about that.  However, I could swear that at one point
if there was an authentication failure, I'd get some report of a lockout on
the master (from either the master or via an error on the slave) which would
alert me to this problem.  (Of course based on my recent track record, I
could be fantasizing about that for all I know ;-))

Anyway, thanks...  Embarassingly,
  Issac

- Original Message - 
From: "Jeremy Zawodny" <[EMAIL PROTECTED]>
To: "Issac Goldstand" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, August 02, 2004 11:31 AM
Subject: Re: perpetual "connecting to master"


> On Sun, Aug 01, 2004 at 08:42:50PM +0300, Issac Goldstand wrote:
> > I just set up another reeplication slave for one of my servers - it's
not
> > something new to me.  I'm using 4.1.3-beta on a new server which will
> > eventually take over as the master.   I set up a server id, did change
> > master to... and started the slave - but the slave seems to perpetually
stay
> > in a state of "Connecting to master...".  I've done thie before, but
I've
> > never encounterred this - can anyone give pointers on how to
troubleshoot?
>
> The first thing I'd try is to connect from the slave machine to the
> master machine using the same credentials.
>
> Jeremy
> -- 
> Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
> <[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/
>
> [book] High Performance MySQL -- http://highperformancemysql.com/
>


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

Re: using mysql in commercial software

[Alec . Cawley]

Jeremy Zawodny <[EMAIL PROTECTED]> wrote on 02/08/2004 09:41:40:

> On Mon, Jul 26, 2004 at 11:49:02PM -0500, mos wrote:
> > At 04:43 PM 7/26/2004, you wrote:
> > 
> > >looks like the answer is no.  As soon as fee based software touches 
the
> > >mysql install on the PC, the user is obligated to pay the $250.
> > 
> > Actually I believe the MySQL 4.x license is more stringent than that. 
If 
> > the MySQL database is distributed within a company for free, then a 
license 
> > is still required, unless the application is distributed under the
> > GPL.
> 
> Huh?
> 
> I bet it depends on the country.  In the USA, companies are considered
> singular legal entities.  "Internal distribution" is aking to giving
> copies to yourself.
> 
> The GPL doesn't restrict that.

Not only that, but I would have thought that you would normally be willing 
to offer distribute the source of your application, and/or the GPL source 
of MySQL, to colleagues in the same company. The GPL does not require you 
to distribute your source to the world, only to those people to whom you 
distribute your application. It seems to me that intra-company 
distribution would fulfill the GPL in all but the most paranoid of 
companies.

> > Wouldn't it be nice if MySQL created a nice little table with a
> > couple of columns "License" and "No License" and a bunch of examples
> > down the left hand side?
> 
> Yes, it would de-mystify a lot of this.

I have a couple of simple rules which, while they might not satisfy the 
lawyers, seem to me to satisfy the spirit of MySQL's licence:

If:
1. You are being paid money for software, and
2. That software would not work without MySQL
then MySQL deserves a share of that money, and they have defined the 
amount they think they deserve by the licence fee - take it or leave it.

If you are distributing your application under the GPL, then you are being 
paid (if you are) for development and not for software, and no licence fee 
is due.

Alec

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

Re: LOAD DATA INFILE insists on being LOCAL

[Patrick Connolly]

Somewhere about Sun, 01-Aug-2004 at 11:31AM -0400 (give or take), Michael Stassen 
wrote:

|> 
|> Patrick Connolly wrote:

[...]

|> > Looks to me the mysql user should have no trouble with it:
|> > 
|> > -rw-rw-r--1 pat  pat   332 Jun 28 20:42 Orders.txt
|> 
|> Every piece of the path to this file must be executable by mysql, as well.

I think that's the main problem I have.  Since it's in a directory
beginning with /home/ and that directory is rwx--, one would have
to change that far back.  Since this machine is not used by anyone
else, perhaps it would not be a problem changing that.  Is that what
people normally do?  Anything else I can think of seems incredibly
complicated.


|> 
|> > |> : Also, to use LOAD DATA INFILE on server files, you must have
|> > |> : the FILE privilege. See section 5.5.3 Privileges Provided by MySQL.
|> > 
|> > Think we can count that one out as the problem since LOCAL which would
|> > have the same requirement does work.
|> 
|> The FILE privilege is not required with LOCAL.  It is required without 
|> LOCAL, and for SELECT INTO OUTFILE.  My bet would be that you don't have the 
|> FILE privilege.  You can check with

Good guess.  That was part of the problem (though I'd not have guessed
from the error message).  I forgot that GRANT ALL does not include
FILE.

[...]

|> How LOAD DATA LOCAL works was changed in 3.23.49 and 4.0.2.  This
|> is documented here
|> .

I had read that, but I'd not made the connexion with the error message.

Thanks again.

best

-- 
   ___ Patrick Connolly  
 {~._.~}   
 _( Y )_  Good judgment comes from experience 
(:_~*~_:) Experience comes from bad judgment
 (_)-(_)


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

help-in-normalizing

[vijaya raj]

hi friends
i am trying to create a data model for my new database.
i have some problems in normalising my data model.
i have two tables with many-to-many relation.
how to i normalize them to one-to-many form.
 
--
molecule_info
--
mol_id
mol_name
---
 
---
plant_info
---
plant_id
plant_name

 
here, i one mol_name is found in more than one plant_name.
the same way, one plant_name has more than one mol_name.
how do i make them one-to-many.
 
kindly help.
thanks 
vijay 


-
 ALL-NEW Yahoo! Messenger - all new features - even more fun!  

Newbie: Ranking-Statement

[Horst Jäger]

Hi,
suppose I've got a table "RichMan" with columns
ID, Name, Bucks
1, 'John', 300
2, 'Joe', 700
3, 'Al', 400
4, 'Fred', 250
5, 'Jim', 480
and I sort them with respect to "Bucks". Then John would be on the first 
place because he ist the richest, followed by 'Jim', etc.

Now I'm looking for an SQL-Statement "select blablabla from RichMan 
blablabla ID"

which returns me the rank of the ID if sorted by Bucks.
So:
"select blablabla from RichMan blablabla 1" would return 4 because John 
with the ID 1 is the 4th-richest.
"select blablabla from RichMan blablabla 3" would return 2 because Al with 
the ID 3 is the 3rd-richest.

And so on.
It would also be interesting to do it the other way round: give the rank 
and get the ID

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

Re: problem with making mysql accessible from other machines

[Egor Egorov]

"Shannon R." <[EMAIL PROTECTED]> wrote:


> i'm getting really confused with how to properly allow
> users to access mysql from other machines.
> 
> that is, i tried this as mysql root user:
> 
> 
> GRANT SELECT, INSERT, UPDATE, DELETE ON my_sql_db.* TO
> [EMAIL PROTECTED] IDENTIFIED BY 'password';
> 
> 
> after this, i can access mysql as 'dbuser' from the
> localhost. the mysql servers ip add by the way is
> 192.168.1.1, and since i also want that user to be
> able to access from 192.168.1.2, i also did this
> command:
> 
> GRANT SELECT, INSERT, UPDATE, DELETE ON my_sql_db.* TO
> [EMAIL PROTECTED] IDENTIFIED BY 'password';
> 
> 
> that should have been it but whenever i try to access
> mysql from 192.168.1.2, i keep getting an "Can't
> connect to MySQL server" error.

Check if your mysql server is ready to accept network connection. 
To check it, telnet to 192.168.1.2 port 3306 and see if MySQL 
answered. 





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

Re: help-in-normalizing

[Whil Hentzen]

> --
> molecule_info
> --
> mol_id
> mol_name
> ---
>
> ---
> plant_info
> ---
> plant_id
> plant_name
> 
>
> here, i one mol_name is found in more than one plant_name.
> the same way, one plant_name has more than one mol_name.
> how do i make them one-to-many.

You're most of the way there. You need a third table with the two IDs:

join_table
--
mol_id
plant_id

So if molecule 1 is found in plants A, B, C, and D, and molecule 2 is found in 
plants B, D, and E, you'd have seven rows:
mol_id   plant_id
1   A
1   B
1   C
1   D
2   B
2   D
2   E

-- 
Whil

Moving to Linux: Freedom, Choice, Security, Opportunity
http://www.hentzenwerke.com


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

Re: perpetual "connecting to master"

[Egor Egorov]

"Issac Goldstand" <[EMAIL PROTECTED]> wrote:

> I just set up another reeplication slave for one of my servers - it's not
> something new to me.  I'm using 4.1.3-beta on a new server which will
> eventually take over as the master.   I set up a server id, did change
> master to... and started the slave - but the slave seems to perpetually stay
> in a state of "Connecting to master...".  I've done thie before, but I've
> never encounterred this - can anyone give pointers on how to troubleshoot?

Check the connections - perhaps, the slave could not connect to the master server? 
You can check it by telnetting to the master host port 3306 and check if MySQL
responded.





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

Re: Query on multiple table with SUM and GROUP

[Egor Egorov]

Denis Geser <[EMAIL PROTECTED]> wrote:
> I am using MySql to store telephone taxes statistics.
> I have 1 table by month with 60'000 rows each.
> 
> My problem is that I need some stats for 3 month.
> For example I need to know the total Time(in second) and total Cost
> for each Number on a 3 month basis.
> 
> Is it possible to do it without creating new tables, and with only one query?


I think so, see GROUP BY clause syntax and grouping functions:

http://dev.mysql.com/doc/mysql/en/SELECT.html
http://dev.mysql.com/doc/mysql/en/Group_by_functions_and_modifiers.html





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

Re: help-in-normalizing

[Rhino]

- Original Message - 
From: "vijaya raj" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, August 02, 2004 7:11 AM
Subject: help-in-normalizing


> hi friends
> i am trying to create a data model for my new database.
> i have some problems in normalising my data model.
> i have two tables with many-to-many relation.
> how to i normalize them to one-to-many form.
>
> --
> molecule_info
> --
> mol_id
> mol_name
> ---
>
> ---
> plant_info
> ---
> plant_id
> plant_name
> 
>
> here, i one mol_name is found in more than one plant_name.
> the same way, one plant_name has more than one mol_name.
> how do i make them one-to-many.
>
The standard way of normalizing a many-to-many relationship is to turn it
into two one-to-many relationships via a third table. This third table is
usually called either an "association" table or an "intersection" table.

In your case, the "intersection" table would look like this:

plant_molecule
-
plant_id
molecule_id

The primary key of the plant_molecule table would be the *combination* of
the plant_id and the molecule_id. Also, plant_id would be a foreign key
pointing back to the plant_info table and molecule_id would be a foreign key
pointing back to the molecule_info table.

Therefore, the tables would look something like this, using bogus data:

PLANT_INFO
plant_id plant_name
---
1  orchid
2  hibiscus
3  sycamore

MOLECULE_INFO
molecule_idmolecule_name
----
Apotassium dichromate
Bsodium calcite
Cbenzine
Dcellulose
Epotassium nitrate

PLANT_MOLECULE
plant_idmolecule_id
---
1 A
1 D
2 D
2 B
2 C
3 E
3 D

The intersection table lists the combinations of primary keys that actually
exist. If you don't remember what the ids represent, you can always join the
intersection table back to the one or both of the original tables to see
what they mean.

By the way, the intersection table can also be used to store additional
information, provided it is about the *combination* shown in its row. For
example, if you want to show the quantity of each molecule in each plant, it
would be appropriate to add that to the intersection table like this:

PLANT_MOLECULE
plant_idmolecule_idquantity
---
1 A3.0E12
1 D4.7E09
2 D2.1E05
2 B9.8E22
2 C0.8E08
3 E0.1E31
3 D6.2E03

Rhino


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

Re: FreeBSD and MySQL - mysqld eats CPU alive

[Egor Egorov]

"adp" <[EMAIL PROTECTED]> wrote:

> I have several MySQL and FreeBSD installs across a few different sites, and
> I consistently have problems with mysqld. It will begin to eat up all of the
> CPU and eventually become unresponsive (or the machine will just burn). I
> can't seem to manually reproduce this, but given enough time a FreeBSD box
> with mysqld will go down.

Running MySQL on FreeBSD requires some magic. One of the famous magicians is
Jeremy Zawodny. Check his sacred MySQL-on-FreeBSD scriptures at
http://jeremy.zawodny.com/blog/archives/cat_mysql.html

I hope you will find a revelation there.  ;) 





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

Re: Query on multiple table with SUM and GROUP

[Whil Hentzen]

On Monday 02 August 2004 02:39, Denis Geser wrote:
> I am using MySql to store telephone taxes statistics.
> I have 1 table by month with 60'000 rows each.
>
> My problem is that I need some stats for 3 month.
> For example I need to know the total Time(in second) and total Cost
> for each Number on a 3 month basis.
>
> Is it possible to do it without creating new tables, and with only one
> query?

The tool you're looking for is the UNION statement. It looks like

select field1, field2 from tableA ;
  union ;
select field1, field2 from tableB ;
  union ;
select field1, field2 from tableC

You'll have to mess around a bit to get the totals, etc.

-- 
Whil

Moving to Linux: Freedom, Choice, Security, Opportunity
http://www.hentzenwerke.com


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

Re: load data in file with pipe

[Egor Egorov]

rmck <[EMAIL PROTECTED]> wrote:

> I thought I could wrap this LOAD DATA option in my perl script which could
> speed things up... Any help is great  

You can consider using bulk insert. 
See http://dev.mysql.com/doc/mysql/en/INSERT.html  and note that you can
insert more than a single row at once. This acts just like LOAD DATA but 
of course, an INSERT SQL statement is more useful inside a perl script. 





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

Re: Data loading

[Egor Egorov]

Michael Gale <[EMAIL PROTECTED]> wrote:

> 
>I have a question about data loading using mysql 4.0.20. If you need to load 
> let's say 50,000 items into a database.
> Now 50,000 is not a lot for a DB. 
> 
> So my question is would it be fast to load the file using the local infile which is 
> a security concern or should I be
> using a loop with INSERTS ?

LOAD DATA is alot faster than INSERTing data row by row because it uses bulk
insert methodic. See http://dev.mysql.com/doc/mysql/en/INSERT.html and note that 
you can INSERT more than one row at once. 

Indexes are updated only a single time per each INSERT statement so inserting
data in bulk will give you a great performance. 





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

Re: File size limit exceeded, Linux/MySQl-4.0.20-i686-icc

[Egor Egorov]

Nathan Boeger <[EMAIL PROTECTED]> wrote:

> Its accessed over the net by the web servers. I don't think we would 
> have file contentions.
> 
> Actually, we just replaced the mysql binary with an older 4.0.16 version 
> that we compiled and used before we started to use the 4.0,20-i686-icc. 
> Now the db is working without any problems. I wonder if others have had 
> problems with this particular binary.

Please try on MySQL official binary version which is not compiled by icc. 
I.e. download mysql-standard-4.0.20-pc-linux-i686.tar.gz

If everything will be ok on this build then it's a bug in icc and it 
definitely needs to be reported. You can contact me by email in this case
or directly open a bug in http://bugs.mysql.com

Thank you!





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

Re: what os to use for mysql on amd64?

[Egor Egorov]

"mc" <[EMAIL PROTECTED]> wrote:

> Here are the results from my installation... in case you may find them
> useful :)

So it seems that 64bit binaries are also dynamically linked...:(






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

Re: MySQL 4.0.20 and UTF-8?

[Egor Egorov]

Yves Goergen <[EMAIL PROTECTED]> wrote:

>>>I'm using UTF-8 in my newsboard, too, and I have no major problems with 
>>>it. UTF-8 doesn't need to be handled as binary, I believe, since all 
>>>characters should be in a range over the control characters. Anyone 
>>>please correct me, if that's wrong...
>> 
>> Tris is true for latin1 characters in UTF8. When you are working with national
>> characters in UTF8, you can easily hit in control characters ranges.
> 
> But what is this binary good for? Can't I just insert those control 
> characters into a normal string/text field?

You can.





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

Re: what os to use for mysql on amd64?

[Egor Egorov]

Pete Harlan <[EMAIL PROTECTED]> wrote:

>> No. I've forgot to tell that the -Max binary is linked dynamically
>> because it uses SSL.
> 
> Is there a reason the SSL libraries can't also be linked statically?

There was some reasons. Afair, MySQL is not the only software which could not
be statically linked with OpenSSL. Licensing issues or technical - I don't 
remember.

> Do you recommend against running the -Max binary, because it doesn't
> use your customized library fixes?

On Linux, yes - because of the glibc.

> The section of your manual describing what some of the glibc problems
> are is helpful; do you know if the linuxthreads-2.2.2 patch made it
> into glibc at some point?  It's for 2.2.2, while we're running 2.2.5
> (on Debian Stable) and 2.3.2 (on Debian Testing).

No, thread stack size is still too big in latest 2.2.x glibc branch. :( 
It's not clear what's about thread stack size in glibc 2.3.x. 

> We're moving to 64-bit soon, primarily to be able to run MySQL with
> more RAM.

You can compile and replace the system glibc by yourself. I did so and it's
safe in terms of thread stack size. But it's not the easiest task and you
should be very careful doing this, because it's a main library in the OS. Also
you have to take your Linux's vendor glibc source package and apply all his
patches to your customized glibc to ensure that replacing will not break
something else. 






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

Re: MySQL and SSL

[Egor Egorov]

Wolfgang Riedel <[EMAIL PROTECTED]> wrote:

>>>could someone please tell me which versions of mysql support SSL
>>>connections for both clients and replication slaves?
>> 
>> The "-Max" build. 
> i'd installed MySQL-Max-4.0.20-0.i386.rpm from download area of 
> www.mysql.org, but the server doesn't start with --ssl option:
> in mysqld-err.log:
> /usr/sbin/mysqld-max: ERROR: unknown variable 
> 'ssl-ca=/usr/share/ssl/certs/ca-bundle.crt'
> 
> (the certificate exists)
> with my self-compiled version of mysql-4.0.20 it runs

Oops, I lied. :) -Max was built with SSL a long time ago (if I remember right),
and now it's not. Sorry. 





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

Re: JOIN/WHERE and index confusion

[SGreen]

Why do I include some WHERE restrictions in my ON clauses and encourage 
others to do it, too? Because I believe that the WHERE clause is applied 
AFTER the temporary virtual table (the result of applying all of the JOIN 
and ON clauses in the full statement) is complete.  That is also why I am 
such a strong proponent of using the explicit JOIN forms of INNER JOIN, 
RIGHT JOIN, and LEFT JOIN. 

I believe that the ON clause in *each* JOIN works to reduce the size of 
the virtual table *as it is being constructed*.  For me, the smaller the 
virtual table, the better the query's performance.  If it doesn't work 
this way then how are we able to do a LEFT JOIN on two tables and look for 
null values from the right-hand table as a means of detecting non-matches? 
Why does the EXPLAIN of a JOIN show which indexes are being used for each 
new table if they aren't going to be used? Why is there a STRAIGHT JOIN 
modifier? I believe that if the order of the JOINs and the presence of the 
ON clauses were not important to performance then there wouldn't be so 
many tools to help us optimize their performance.

Humbly,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



Michael Stassen <[EMAIL PROTECTED]> wrote on 07/30/2004 05:42:24 
PM:

> Why do you expect moving the a.timestamp restriction from the WHERE 
clause 
> to the JOIN will help?
> 
> Michael
> 
> [EMAIL PROTECTED] wrote:
> 
> > Yes, I can think of two things you can try in order to speed up your 
> > query. First - try the STRAIGHT JOIN clause with one small but 
critical 
> > change to your 
statement(http://dev.mysql.com/doc/mysql/en/SELECT.html):
> > 
> > SELECT STRAIGHT JOIN *
> > FROM a
> > LEFT JOIN b ON a.a_id = b.a_id
> > AND a.timestamp BETWEEN 2004010100 AND 20040101235959
> > JOIN c ON a.c_id = c.c_id
> > JOIN d ON c.d_id = d.d_id
> > JOIN e ON c.e_id = e.e_id
> > GROUP BY c.d_id, c.e_id
> > 
> > The second thing to try would be to pre-calculate (hand optimize) the 
> > different stages of your query (don't join all of the tables together 
all 
> > at once). Although, when I started to do it myself, I was mimicking 
what 
> > the STRAIGHT JOIN should do for us automatically. So I guess I really 
have 
> > only one suggestion, hmmph...
> > 
> > It worked wonders for someone else recently, I think it's what you 
need 
> > for this situation.
> > 
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
> 
> 

RE: How do you archive db daily?

[SGreen]

Actually, no, it isn't in memory (except for that breif period of time 
before the write cache is committed to disk). You are actually creating a 
new, disk-based database and populating it with data. 

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

"Jacob, Raymond A Jr" <[EMAIL PROTECTED]> wrote on 07/30/2004 
07:40:20 PM:

> Shawn:
> Thank you for your reply. I did not know you could reference a 
> Database in a select clause, I thought you
> could only reference tables.
> I do have a followup question. I assume once I have created the 
> dbArchYesterday that
> mysqld has the database in memory.Is there way to:
> 1. write the database to disk,
> 2. unload the database from memory, if it is in memory,
> in order to file utilities such as tar and gzip to archive and 
> compress the
> database into to a tgz file? 
> 
> Thank you,
> raymond
> PS: Is there a document that describes how mysql loads and unloads 
> tables and databases in memory?
> 
> 
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Friday, July 30, 2004 9:03
> To: Jacob, Raymond A Jr
> Cc: [EMAIL PROTECTED]
> Subject: Re: How do you archive db daily?
> 
> 
> 
> Why not keep your server running and just use scripted SQL to 
> migrate the records from one DB to the other? 
> 
> CREATE DATABASE dbArchYesterday; 
> 
> USE dbArchYesterday; 
> 
> CREATE TABLE table1 like dbActive.table1; 
> CREATE TABLE table2 like dbActive.table2; 
> CREATE TABLE table3 like dbActive.table3; 
> CREATE TABLE table4 like dbActive.table4; 
> 
> INSERT table1(list of columns to load) 
> SELECT list of columns to read 
> FROM dbActive.table1 
> WHERE alertdate between 'yesterday 00:00:00' and 'yesterday 23:59:59'; 
> 
> INSERT table2(list of columns to load) 
> SELECT list of columns to read 
> FROM dbActive.table2 
> WHERE alertdate between 'yesterday 00:00:00' and 'yesterday 23:59:59'; 
> 
> INSERT table3(list of columns to load) 
> SELECT list of columns to read 
> FROM dbActive.table3 
> WHERE alertdate between 'yesterday 00:00:00' and 'yesterday 23:59:59'; 
> 
> INSERT table4(list of columns to load) 
> SELECT list of columns to read 
> FROM dbActive.table4 
> WHERE alertdate between 'yesterday 00:00:00' and 'yesterday 23:59:59'; 
> 
> DELETE FROM dbActive.Table1 
> WHERE alertdate between 'yesterday 00:00:00' and 'yesterday 23:59:59'; 
> 
> DELETE FROM dbActive.Table2 
> WHERE alertdate between 'yesterday 00:00:00' and 'yesterday 23:59:59'; 
> 
> DELETE FROM dbActive.Table3 
> WHERE alertdate between 'yesterday 00:00:00' and 'yesterday 23:59:59'; 
> 
> DELETE FROM dbActive.Table4 
> WHERE alertdate between 'yesterday 00:00:00' and 'yesterday 23:59:59'; 
> 
> 
> That way you never have to stop your server and you can transform 
> any data you need to change during the move. This process also 
> allows you to build additional  summary tables during the move, if 
> you want them.  By scripting the entire thing and replacing my 
> (hopefully) obvious placeholders with live names and dates, this 
> should perform rather well with little or no intervention on your 
> part.  Another advantage to scripting this process is that you can 
> monitor each stage and abort the deletes at the end if things did 
> not go well. 
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine 
> 
> 
> "Jacob, Raymond A Jr" <[EMAIL PROTECTED]> wrote on 07/29/2004 
> 06:26:26 PM:
> 
> > I am running MySql 3.23.58-1 on a snort database containing IDS 
alerts.
> > At 12:00am I would like move the previous day's alerts from four 
tables to 
> > a backup database named for the previous day.
> > Has anyone implemented such a backup schedule? And if so can someone 
> > send me a script?
> > 
> > I had the following ideas on the subject:
> > 1.a. mysql shutdown.
> >b. cp -r database //2004-07-29
> >c. mysqlstart 
> >   /* I need a single user mode for the delete to work */
> >d. echo "Delete iphdr; Delete tcphdr;Delete acid_event; Delete 
> > event"  | mysql -p xxx -u  
> >e. go to multiuser mode.
> > 
> > 2. a. Assuming logging turned on 
> > mysqlhotcopy snortdb
> > ( echo "Delete iphdr; Delete tcphdr;Delete acid_event; 
> > Delete event" ; mysqlbinlog snort.log ) | mysql -p xxx -u yyy
> > 
> > 3.  a. $ mysql -p xxx -u yyy
> > 1.  if a week then  purge tables:
> > $mysql> Delete iphdr;
> >  ( repeat for the rest of the tables.)
> > 
> > 
> > 2.  mysql -p xxx -u yyy
> > mysql > Select iphdr.*
> >  from iphdr, event_id
> >  into outfile /.../backup/2004-07-29/iphdr.txt
> >  where timestamp.event_id < 2004-07-29;
> >  mysql> Delete iphdr;
> >  ( repeat for the rest of the tables.)
> > 
> > mysql > use backsnort_db
> >  Select iphdr.*
> >  from iphdr, event_id
> >  

In Statement Help

[Andrew Dixon]

Hi Everyone.

I require some help with an IN statement I'm trying to get working. I have
inherited a database from someone else in which there is a table with
project information and a field containing which geographic regions the
projects relate. As a single project can relate to multiple geographic
regions the person who created the database create this field as a varchar
field and populated it with a pipe (|) delimited list of ID's of the
geographic regions (ID from another table). An example of the data in this
field is:

1|5|9|10|12

Or 

1

Or

9|5|7

I have been asked to write a query to return all the projects for a single
geographic region. This is what I have come up with:

SELECT  project_title 
FROMprojects
WHERE   9 IN (REPLACE(geo_region,'|',','))
ORDER BYproject

Where 9 is the geographic region ID I'm looking for and geo_region is the
field containing the pipe delimited list. Now with the above three lines of
data I would have expected it to return 2 rows, however it only returns one
(the last one). If I change the number to 5, I would expect 2 rows as well,
however it doesn't return any. It only appears to return a row if the number
appears at the beginning of the list!!! I'm not sure where I'm going wrong,
or even if what I'm trying to do is possible (please say it is!!!). Any
advise would be very gratefully received.

Best Regards,

Andrew



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

RE: better performance with optimize!?? (jboss)

[Victor Pendleton]

Have you run an explain before each scenario to see if the access path is
different? 

-Original Message-
From: Jeremy Zawodny
To: Marc
Cc: [EMAIL PROTECTED]
Sent: 8/1/04 8:28 PM
Subject: Re: better performance with optimize!?? (jboss)

On Sun, Aug 01, 2004 at 01:42:24PM +0200, Marc wrote:
> I use Mysql with JBOSS as applicationserver.
> 
> i have strange response-time differences, which i can't explain.
> 
> 1) after reboot the computer, it takes about 300ms to read 12 entities
(cmp,
> read ahead, 2 rows each entity) One entity is accessed by primary key,
the
> others by
> foreign key.
> 
> 2) when i reboot the computer and run mysql optimize first, it takes
only
> about 80ms to read the 12 entities!!

Caching?  In case #2, MySQL may have cached large amounts of the table
during the OPTIMIZE.

> 3) when i do the same with pure java / jdbc (outside jboss), it takes
only
> 30ms!!! it doesn't matter, if i run optimize or not!

JBoss overhead?

I dunno, I'm not a Java guy at all.

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

[book] High Performance MySQL -- http://highperformancemysql.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: FreeBSD and MySQL - mysqld eats CPU alive

[Andrey Kotrekhov]

Did you compile mysql from source or you use precompiled binary package?
I use mysql with different versions of FreeBSD without big problems.
"adp" <[EMAIL PROTECTED]> wrote:
I have several MySQL and FreeBSD installs across a few different sites, and
I consistently have problems with mysqld. It will begin to eat up all of the
CPU and eventually become unresponsive (or the machine will just burn). I
can't seem to manually reproduce this, but given enough time a FreeBSD box
with mysqld will go down.
Running MySQL on FreeBSD requires some magic. One of the famous magicians is
Jeremy Zawodny. Check his sacred MySQL-on-FreeBSD scriptures at
http://jeremy.zawodny.com/blog/archives/cat_mysql.html
I hope you will find a revelation there.  ;)


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

Best regards.
___
Andrey Kotrekhov [EMAIL PROTECTED]
ISP Alkar Teleport
тел. +380 562 34-00-44
-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Newbie: Ranking-Statement

[Brent Baisley]

Well, giving the rank and getting the ID is simple:
SELECT * FROM RichMan ORDER BY Bucks DESC LIMIT 4,1
That will return 1 record starting at the 4th record.
The first one is a bit tougher. Thinking on it.
On Aug 2, 2004, at 7:23 AM, Horst Jäger wrote:
Hi,
suppose I've got a table "RichMan" with columns
ID, Name, Bucks
1, 'John', 300
2, 'Joe', 700
3, 'Al', 400
4, 'Fred', 250
5, 'Jim', 480
and I sort them with respect to "Bucks". Then John would be on the 
first place because he ist the richest, followed by 'Jim', etc.

Now I'm looking for an SQL-Statement "select blablabla from RichMan 
blablabla ID"

which returns me the rank of the ID if sorted by Bucks.
So:
"select blablabla from RichMan blablabla 1" would return 4 because 
John with the ID 1 is the 4th-richest.
"select blablabla from RichMan blablabla 3" would return 2 because Al 
with the ID 3 is the 3rd-richest.

And so on.
It would also be interesting to do it the other way round: give the 
rank and get the ID

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


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

Re: Query on multiple table with SUM and GROUP

[Denis Geser]

> On Monday 02 August 2004 02:39, Denis Geser wrote:
> > I am using MySql to store telephone taxes statistics.
> > I have 1 table by month with 60'000 rows each.
> >
> > My problem is that I need some stats for 3 month.
> > For example I need to know the total Time(in second) and total Cost
> > for each Number on a 3 month basis.
> >
> > Is it possible to do it without creating new tables, and with only one
> > query?
> 
> The tool you're looking for is the UNION statement. It looks like
> 
> select field1, field2 from tableA ;
>   union ;
> select field1, field2 from tableB ;
>   union ;
> select field1, field2 from tableC
> 
> You'll have to mess around a bit to get the totals, etc.
> 
> -- 
> Whil
> 

I did try UNION and I am able to obtain one table, but I can not have one grand
total for each column.

Denis



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

Problem with Mysql 4.0.18 + Debian

[wiricha2]

Hello:

  I posted this before but I have not gotten a response.  I have a Debian
(woody) server running a good sized database (7.2GB of db files), Mysql
4.0.18.  I am running Apache 1.3.29 + perl and using mysql as the
backend.  In my.cnf, I have max_connections=300.

Here's the problem.  I had the site up several days, with everything
running perfectly.  Ordinarily there would be about 11 mysql processes
running.  However, after a few days of running smoothly, the number of
mysql processes increases to over 170, and the site crashes.  Formerly I'd
get errors like

DBI connect('yada yada, ...) failed: Too many connections at DB.pm line 25

However once I set max_connections to 300 (default is 90), mysql will
still accept connections, but it is still way too slow to be usable, so
the website becomes unreachable.

I've read on this list that people running MySQL w/ FreeBSD can have
similar sounding problems.  I am wondering if there is a connection.

I know that the site is getting virtually no traffic, so the problem is
not that it is being overloaded.  I have tried this scenario at least a
dozen times, and the same thing always happens.

Ryan






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

Grant privileges to a new database...

[Scott Fletcher]

What I have here is a database-driven website.  In the past, I created a
database and grant privileges to it.  Now, I'm creating a carbon copy of
it into another website which will be a test website with a test
database.  So, when I try the grant privileges to the test database, it
returned a zero row count.  So, the question I have here is it allowed
or supported in MySQL to have two database with same localhost account
and non-localhost account.  I hope I explained it clearly...

 

Thanks,

 Scott

Re: FreeBSD and MySQL - mysqld eats CPU alive

[Egor Egorov]

Andrey Kotrekhov <[EMAIL PROTECTED]> wrote:

> Did you compile mysql from source or you use precompiled binary package?
> I use mysql with different versions of FreeBSD without big problems.

On FreeBSD, you can compile MySQL from ports. 

>>> I have several MySQL and FreeBSD installs across a few different sites, and
>>> I consistently have problems with mysqld. It will begin to eat up all of the
>>> CPU and eventually become unresponsive (or the machine will just burn). I
>>> can't seem to manually reproduce this, but given enough time a FreeBSD box
>>> with mysqld will go down.
>>
>> Running MySQL on FreeBSD requires some magic. One of the famous magicians is
>> Jeremy Zawodny. Check his sacred MySQL-on-FreeBSD scriptures at
>> http://jeremy.zawodny.com/blog/archives/cat_mysql.html
>>
>> I hope you will find a revelation there.  ;)





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

RE: Problem with Mysql 4.0.18 + Debian

[Victor Pendleton]

What does mysql "show processlist" look like?

-Original Message-
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: 8/2/04 9:16 AM
Subject: Problem with Mysql 4.0.18 + Debian

Hello:

  I posted this before but I have not gotten a response.  I have a
Debian
(woody) server running a good sized database (7.2GB of db files), Mysql
4.0.18.  I am running Apache 1.3.29 + perl and using mysql as the
backend.  In my.cnf, I have max_connections=300.

Here's the problem.  I had the site up several days, with everything
running perfectly.  Ordinarily there would be about 11 mysql processes
running.  However, after a few days of running smoothly, the number of
mysql processes increases to over 170, and the site crashes.  Formerly
I'd
get errors like

DBI connect('yada yada, ...) failed: Too many connections at DB.pm line
25

However once I set max_connections to 300 (default is 90), mysql will
still accept connections, but it is still way too slow to be usable, so
the website becomes unreachable.

I've read on this list that people running MySQL w/ FreeBSD can have
similar sounding problems.  I am wondering if there is a connection.

I know that the site is getting virtually no traffic, so the problem is
not that it is being overloaded.  I have tried this scenario at least a
dozen times, and the same thing always happens.

Ryan






-- 
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: help-in-normalizing

[Rhino]

  - Original Message - 
  From: vijaya raj 
  To: Rhino 
  Sent: Monday, August 02, 2004 9:31 AM
  Subject: Re: help-in-normalizing


  dear rhino and whil
  thanks for that valuable tip.
  but, dont i have to wory about the repeating id's (are they not redundant...) in 
this table, excuse me if this query looks silly...

  PLANT_MOLECULE
  plant_idmolecule_idquantity
  ---
  1 A3.0E12
  1 D4.7E09
  2 D2.1E05
  2 B9.8E22
  2 C0.8E08
  3 E0.1E31
  3 D6.2E03

First of all, this is a table, not a query. 

There is no duplication in the table either.  Although various plant_ids and 
molecule_ids appear several times in the table, each row represents one COMBINATION of 
plant_id and molecule_id. In other words, plant_id 3, contains two molecules, E and D. 
Although the other plants also contain molecule D, as well as other molecules, this 
does not affect plant_id 3, as long as the primary key of plant_molecule is the 
COMBINATION of plant_id and molecule_id. 

Remember, if the primary key contains both the plant_id and the molecule_id, the table 
will allow only one occurrence of a given combination of plant_id and molecule_id and 
you will never be able to get two or more rows that have the plant_id 3 / molecule_id 
D combination.  

Don't let the quantity column bother you too much; I only mentioned it because 
sometimes you need to have additional information about the combination of plant_id 
and molecule_id, such as the number of molecules per gram of a specific plant. 
(Actually, you probably don't care how many grams of molecules occur in a given plant: 
I was just trying to come up with an example to illustrate how such a quantity should 
be added to your design.) You may never need any additional column in the 
plant_molecule table beyond the plant_id and molecule_id columns so just ignore the 
quantity column altogether unless you need something like that.

Rhino

Re: FreeBSD and MySQL - mysqld eats CPU alive

[Andrey Kotrekhov]

Добрый день.
Usually I run configure like this:
./configure  \
"--prefix=/usr/local/mysql_4" \
"--without-debug" \
"--with-charset=koi8_ru" \
"--with-extra-charset=koi8_ukr,win1251,win1251ukr" \
"--with-mysqld-user=X" \
--without-berkeley-db \
--without-isam \
--without-innodb \
--enable-thread-safe-client
because I need koi8 charset and etc...
And I don't have any problem
Andrey Kotrekhov <[EMAIL PROTECTED]> wrote:
Did you compile mysql from source or you use precompiled binary package?
I use mysql with different versions of FreeBSD without big problems.
On FreeBSD, you can compile MySQL from ports.
I have several MySQL and FreeBSD installs across a few different sites, and
I consistently have problems with mysqld. It will begin to eat up all of the
CPU and eventually become unresponsive (or the machine will just burn). I
can't seem to manually reproduce this, but given enough time a FreeBSD box
with mysqld will go down.
Running MySQL on FreeBSD requires some magic. One of the famous magicians is
Jeremy Zawodny. Check his sacred MySQL-on-FreeBSD scriptures at
http://jeremy.zawodny.com/blog/archives/cat_mysql.html
I hope you will find a revelation there.  ;)


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

Best regards.
___
Andrey Kotrekhov [EMAIL PROTECTED]
ISP Alkar Teleport
тел. +380 562 34-00-44
-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mysql_close() problem

[Venkateswaran, Bhaskar]

Hi,

I just upgraded to mysql-4.0.20 compiled using gcc-3.3.3. All my C++ DB
interfaces connecting to this mysql database seem to run into a problem -
when I try to disconnect, mysql_close() is called but the program just hangs
there without being able to close the connection. Can someone please advise?
Thanks.

innodb per table

[Carlos Proal]

How stable is this feature? , i have been using it for a few time and works 
fine, but im thinking to move my current productive enviroment this way and 
i dont know if its a good choice.

Any advise is appreciated.
Carlos
_
Tired of spam? Get advanced junk mail protection with MSN 8. 
http://join.msn.com/?page=features/junkmail

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

Verify the database backup from mysqldump???

[Scott Fletcher]

Is it possible to verify the database backup.  Like most machines, that
make backup of files then verify that all of it is backed up without an
error?  I use mysqldump to make a backup but I have no idea about the
verify

 

Thanks,

 Scott

Re: using mysql in commercial software

[Marc Slemko]

On Mon, 2 Aug 2004 01:35:44 -0700, Jeremy Zawodny <[EMAIL PROTECTED]> wrote:
> On Mon, Jul 26, 2004 at 01:26:15PM -0500, gerald_clark wrote:
> >
> > Steve Richter wrote:
> >
> > >exactly!  Is Linux distributed under the same type of license as MySql?  If
> > >I sell software that runs on linux I dont have to give away my code, right?
> > >To use my software you first have to install no charge Linux and MySql.  Why
> > >would that not be permitted?
> >
> > Because the MySQL license does not allow you to use it free with
> > commercial software that requires MySQL.  If you are running
> > commercial software that requires MySQL you must buy a license.
> 
> And this is where the confusion start.  MySQL is covered by the GPL.
> So is Linux.

As far as the server goes, sure.

However there is a key difference in that APIs such as glibc on Linux
are licensed under the LGPL.  The mysql client libraries used to be
the same way, then they changed them to be under the GPL.  This means
that, according to the most common interpretation of the GPL, just
linking with them automatically requires your code be under the GPL. 
Does this still apply to, say, Java code where you are using the
standard JDBC interface?  How about if you use mysql specific SQL
calls?  I would suggest perhaps not, but it isn't a simple question.

So, for example, you could not sell an application under terms not
compatible with the GPL and include the mysql client drivers.  Even
selling an application that is linked against them, but requires the
user to get them themselves, is arguably not permitted.  You can go
read the FSF's FAQ for their interpretation, but that is just their
interpretation.

However, remember the GPL only covers copying, distribution, and
modification.  Not use.

Also note that MySQL AB allows an exception designed for the client
libraries to be more compatible with other open source licenses:
http://dev.mysql.com/doc/mysql/en/MySQL_FOSS_License_Exception.html

I believe that MySQL AB is deliberately vague and confusing on their
licensing page to try to get people to buy mysql licenses.  All their
words there don't matter though, what matters is the actual license. 
It would, however, be nice if their commentary were a bit closer to
the reality of what the GPL means.

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

RE: Problem with Mysql 4.0.18 + Debian

[William R. Mussatto]

Victor Pendleton said:
> What does mysql "show processlist" look like?
>
> -Original Message-
> From: [EMAIL PROTECTED]
> To: [EMAIL PROTECTED]
> Sent: 8/2/04 9:16 AM
> Subject: Problem with Mysql 4.0.18 + Debian
>
> Hello:
>
>   I posted this before but I have not gotten a response.  I have a
> Debian
> (woody) server running a good sized database (7.2GB of db files), Mysql
> 4.0.18.  I am running Apache 1.3.29 + perl and using mysql as the
> backend.  In my.cnf, I have max_connections=300.
>
> Here's the problem.  I had the site up several days, with everything
> running perfectly.  Ordinarily there would be about 11 mysql processes
> running.  However, after a few days of running smoothly, the number of
> mysql processes increases to over 170, and the site crashes.  Formerly
> I'd
> get errors like
>
> DBI connect('yada yada, ...) failed: Too many connections at DB.pm line
> 25
>
> However once I set max_connections to 300 (default is 90), mysql will
> still accept connections, but it is still way too slow to be usable, so
> the website becomes unreachable.
>
> I've read on this list that people running MySQL w/ FreeBSD can have
> similar sounding problems.  I am wondering if there is a connection.
>
> I know that the site is getting virtually no traffic, so the problem is
> not that it is being overloaded.  I have tried this scenario at least a
> dozen times, and the same thing always happens.
>
> Ryan
>
Are you running mod_perl?

-- 
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061



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

Re: problem with making mysql accessible from other machines

[James Weisensee]

> i'm getting really confused with how to properly
> allow users to access mysql from other machines.
> 
> that is, i tried this as mysql root user:
> 
> GRANT SELECT, INSERT, UPDATE, DELETE ON my_sql_db.*
> TO [EMAIL PROTECTED] IDENTIFIED BY 'password';
> 
> after this, i can access mysql as 'dbuser' from the
> localhost. the mysql servers ip add by the way is
> 192.168.1.1, and since i also want that user to be
> able to access from 192.168.1.2, i also did this
> command:



The MySQL server IP address is 192.168.1.1
(see below)



> 
> GRANT SELECT, INSERT, UPDATE, DELETE ON my_sql_db.*
> TO [EMAIL PROTECTED] IDENTIFIED BY 'password';
> 
> that should have been it but whenever i try to
> access mysql from 192.168.1.2, i keep getting an
> "Can't connect to MySQL server" error.
> 
> did i do anything wrong? that's what the
> documentation said and i just followed it.
> 
> also, what if i also want to allow mysql access from
> 192.168.1.3? but not with the rest of the addresses
> on 192.168.0.0? how do i do that?


What is the subnetmask?  I assume 255.255.0.0


> 
> and there's yet another thing which confused me. if
> i do 'mysql -u root -p' from the mysql server
itself,
> i can connect to it, but if i do 'mysql -u root -p
-h
> 192.168.0.1', or just pointing to itself, i get the
> same 'cant connect to mysql' error. anyone can shed
> any light on why is this so?
> 

Above in this email you stated that the MySQL server's
IP was 192.168.1.1 not 192.168.0.1



HTH,
James


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

Re: JOIN/WHERE and index confusion

[Michael Stassen]

Shawn,
I see what you are saying, but I don't believe it works that way.  If that 
were the case, putting the condition in the ON clause rather than the WHERE 
clause would be at the top of the list of recommended optimizations in the 
manual, but it's not.  In fact, the manual recommends against it, "You 
should generally not have any conditions in the ON part that are used to 
restrict which rows you want in the result set, but rather specify these 
conditions in the WHERE clause. There are exceptions to this rule." 
.

I think you are giving the optimizer (and its authors) way too little 
credit.  If what you say were so,

  SELECT * FROM a, b WHERE a.id=b.id AND a.val = 2;
or
  SELECT * FROM a JOIN b ON a.id=b.id WHERE a.val = 2;
would be dramatically slower (and take a lot more memory) than
  SELECT * FROM a JOIN b ON a.id=b.id AND a.val = 2;
I think that's your point, but, in fact, the EXPLAINs for those 3 queries 
are identical.

EXPLAIN SELECT * FROM a, b WHERE a.id=b.id AND a.val=2;
+---++-+-+---+--+-+
| table | type   | possible_keys   | key | ref   | rows | Extra   |
+---++-+-+---+--+-+
| a | ref| PRIMARY,val_idx | val_idx | const | 1883 | Using where |
| b | eq_ref | PRIMARY | PRIMARY | a.id  |1 | |
+---++-+-+---+--+-+
I'm not sure what you mean by "Why does the EXPLAIN of a JOIN show which 
indexes are being used for each new table if they aren't going to be used?"
In all 3 cases the optimizer compares making a depend on b to making b 
depend on a, and sees that far fewer rows are to be selected in a based on 
the a.val=2 condition than are to be selected in b (no condition).  It uses 
the index on a.val to quickly select the subset of a's rows with val=2, then 
uses the index on b.id to match up rows in b.

The STRAIGHT JOIN modifier potentially does 2 things.  With 'a STRAIGHT JOIN 
b', you are declaring that b depends on a.  Thus, the optimizer does not 
consider the 'a depends on b' case.  This can speed up queries in the very 
rare case that the optimizer mistakenly chooses a to depend on b when you 
know it should do the reverse.  Even when the optimizer will get it right, 
this could speed up a complex join when you know which table should come 
first by telling the optimizer to skip considering the other possible 
dependencies and simply use the one you specified.  This is documented here 
.

The LEFT JOIN is similar to the STRAIGHT JOIN, in that 'a LEFT JOIN b' 
declares that b depends on a.  So, WHERE conditions can be used to restrict 
rows in table a pre join, but WHERE conditions can't be used to determine 
which rows to fetch from b to join to a, they have to be applied afterward. 
 That's how we can look for NULLs in b with the WHERE clause, as you said. 
That's also why moving a condition from the WHERE clause to the ON clause 
could help in *certain situations*.  If you want the rows of table a with 
a.val=2 whether they exist in b or not (a.id=b.id), but when they do match 
you only want the rows where b.type='c', moving the b.type='c' condition to 
the ON clause would reduce the "virtual table as it is being constructed" 
and keep the NULL rows, while leaving it in the WHERE would have to filter 
it afterward and would drop the NULL rows (making the LEFT JOIN pointless).

Michael
[EMAIL PROTECTED] wrote:
Why do I include some WHERE restrictions in my ON clauses and encourage 
others to do it, too? Because I believe that the WHERE clause is applied 
AFTER the temporary virtual table (the result of applying all of the JOIN 
and ON clauses in the full statement) is complete.  That is also why I am 
such a strong proponent of using the explicit JOIN forms of INNER JOIN, 
RIGHT JOIN, and LEFT JOIN. 

I believe that the ON clause in *each* JOIN works to reduce the size of 
the virtual table *as it is being constructed*.  For me, the smaller the 
virtual table, the better the query's performance.  If it doesn't work 
this way then how are we able to do a LEFT JOIN on two tables and look for 
null values from the right-hand table as a means of detecting non-matches? 
Why does the EXPLAIN of a JOIN show which indexes are being used for each 
new table if they aren't going to be used? Why is there a STRAIGHT JOIN 
modifier? I believe that if the order of the JOINs and the presence of the 
ON clauses were not important to performance then there wouldn't be so 
many tools to help us optimize their performance.

Humbly,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

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

Re: help-in-normalizing

[Whil Hentzen]

>   dear rhino and whil
>   thanks for that valuable tip.
>   but, dont i have to wory about the repeating id's (are they not
> redundant...) in this table, excuse me if this query looks silly...
>
>   PLANT_MOLECULE
>   plant_idmolecule_idquantity
>   ---
>   1 A3.0E12
>   1 D4.7E09
>   2 D2.1E05
>   2 B9.8E22
>   2 C0.8E08
>   3 E0.1E31
>   3 D6.2E03

Well, yes, you do have to make sure that you don't have more than one row with 
the same plant_id/mol_id combination. IOW, you _don't_ want two rows that 
both have 

>   plant_idmolecule_id
>   ---
>   1 A
>   1 A

because then you'd get twice the number of plants/molecules as you wanted.

-- 
Whil

Moving to Linux: Freedom, Choice, Security, Opportunity
http://www.hentzenwerke.com


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

MySQL & Crystal Reports V9

[Kerry Frater]

Anyone know of some decent docs on how CR works with MySQL using ODBC 3.51.

Just installed it today to see what it's like and am finding it cumbersome.
This is most likely due to ignorance rather than the product.

I was expecting to see a list of tables available for reporting when using a
link though instead of having to write SQL statements.

Regards

ORDER BY lithuanian

[tl]

Hello,

I have a problem.

  
Lithuanian letters:
ąčęėįšųūž
ĄČĘĖĮŠŲŪŽ
aceeisuuz


What I must use to sort by this ORDER?


When I use

mysql> CREATE TABLE w
   (
   s  VARCHAR(5) CHARACTER SET cp1257 COLLATE cp1257_lithuanian_ci
   );
Query OK, 0 rows affected (0.04 sec)


mysql> insert into w values('ąčęėį'),('šųūža');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from w;
+---+
| s |
+---+
| ??ę?? |
| ?ų??a |
+---+
2 rows in set (0.00 sec)

I got question mark '?'?

:(

Please help me.




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

Mysql and PHPMYadmin

[Barry Zimmerman]

Hi,

Hoping that someone can help me, as I have tried everywhere for help and
still cannot get this to work.

I am using MySQL 4.1.1

I have a sql file that contains french accent characters. I have 8 fields in
my database. Now the content of the text file imports into my database, when
I import the text into the database some fields enter the accent characters
correctly and others do not.

The field type varchar imports the text no problem.

The field type text does not, it converts my accents into ? and does not
import them.

I have tried to change the character sets and this also does not work. Does
anyone know how I can solve this problem. Also what is the correct character
set and collation for french characters.

Please could someone help.

Thanks

Barry
--
Barry Zimmerman
Administrator

Fishingvenues.com
http://www.fishingvenues.com
--


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

Mysql and PhpMyadmin problem

[Barry Zimmerman]

Hi,

Hoping that someone can help me, as I have tried everywhere for help and
still cannot get this to work.

I am using MySQL 4.1.1

I have a sql file that contains french accent characters. I have 8 fields in
my database. Now the content of the text file imports into my database, when
I import the text into the database some fields enter the accent characters
correctly and others do not.

The field type varchar imports the text no problem.

The field type text does not, it converts my accents into ? and does not
import them.

I have tried to change the character sets and this also does not work. Does
anyone know how I can solve this problem. Also what is the correct character
set and collation for french characters.

Please could someone help.

Thanks

Barry
--
Barry Zimmerman
Administrator

Fishingvenues.com
http://www.fishingvenues.com
--


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

Re: using mysql in commercial software

[Jeremy Zawodny]

On Mon, Aug 02, 2004 at 09:24:59AM -0700, Marc Slemko wrote:
> 
> I believe that MySQL AB is deliberately vague and confusing on their
> licensing page to try to get people to buy mysql licenses.  All
> their words there don't matter though, what matters is the actual
> license.  It would, however, be nice if their commentary were a bit
> closer to the reality of what the GPL means.

You're far from being alone in that assessment.

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

[book] High Performance MySQL -- http://highperformancemysql.com/

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

Re: MaxDB and cluster

[Jeremy Zawodny]

On Tue, Jul 20, 2004 at 04:18:42PM +0800, [EMAIL PROTECTED] wrote:
> I am new to MaxDB and clustering. It seems that both products provide
> enterprise features and high availability.
> Is clustering replacing MaxDB?
> What are the difference between them?
> Are they targeting different users?

Based on what I've read, MaxDB's goal is to be able to host an SAP
install.  I suspect that MySQL will get to that point too, but it
sounds like MaxDB is gonna be there first.

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

[book] High Performance MySQL -- http://highperformancemysql.com/

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

RE: Mysql and PHPMYadmin

[Victor Pendleton]

Do you know the ASCII values for the characters that are being turned into
question marks? Are you able to verify that the data is not be imported by
querying the underlying ascii value? 

-Original Message-
From: Barry Zimmerman
To: [EMAIL PROTECTED]
Sent: 8/2/04 1:00 PM
Subject: Mysql and PHPMYadmin

Hi,

Hoping that someone can help me, as I have tried everywhere for help and
still cannot get this to work.

I am using MySQL 4.1.1

I have a sql file that contains french accent characters. I have 8
fields in
my database. Now the content of the text file imports into my database,
when
I import the text into the database some fields enter the accent
characters
correctly and others do not.

The field type varchar imports the text no problem.

The field type text does not, it converts my accents into ? and does not
import them.

I have tried to change the character sets and this also does not work.
Does
anyone know how I can solve this problem. Also what is the correct
character
set and collation for french characters.

Please could someone help.

Thanks

Barry
--
Barry Zimmerman
Administrator

Fishingvenues.com
http://www.fishingvenues.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: JOIN/WHERE and index confusion

[SGreen]

You are absolutely right, I was giving the developers too little credit. I 
humbly apologize to all who contributed to the query optimizer. It was 
never my intent to impugn the fine work that has gone into MySQL, my 
optimization strategy is merely a reflection of my own pessimism. Please 
forgive my horribly negative attitude but I have been bit in the a** far 
too many times by those fine developers in Redmond that create a 
commercial SQL-based database server (and the associated desktop and 
personal database products) to allow me to assume much when it comes to 
performance.  MySQL is one of those few excellent products that can 
actually do what it says it can do.  Old habits are dying hard.

I agree with your analysis. If WHERE conditions and ON conditions were not 
pre-selected and processed during the construction of the virtual table, 
the performance of MySQL would not be a great as it 
is.(http://dev.mysql.com/doc/mysql/en/Where_optimisations.html)  However, 
my pessimist side assumes that any optimization will be overlooked and 
that I need to help every way I can.  I tend to believe that my WHEREs 
will not be analyzed until after the virtual table is built (worst case, 
again). That way I can consciously attempt to minimize the size of the 
virtual table (even though I *know* that some of my WHERE conditions will 
be applied as appropriate as it  is being constructed)

Your analysis of whether to put a condition of a LEFT JOIN  into the WHERE 
clause vs. the ON clause is bang on. As you say, that is the nature of a 
LEFT JOIN.

I also recognize that JOINing is one of the few times that MySQL will 
apply more than one index to a query. Each joined table has the 
opportunity to have one if its indexes used during the building of the 
virtual table. Then, if I understand the process correctly, one of the 
remaining indexes from one of the source tables participating in the 
virtual table could be chosen to assist with any remaining WHERE and GROUP 
BY conditions. That's why the EXPLAIN shows that more than one index is in 
use when the docs clearly state that at most 1 index (per table) can be 
used in the processing of any statement. My confusing question about the 
EXPLAIN output was meant to be more rhetorical than to represent actual 
puzzlement.

I had two reasons why I suggested that Eamon try the STRAIGHT JOIN. First, 
the biggest impact on his virtual table size would come from applying his 
WHERE clause directly to table a. Second, according to 
"Where_optimizations" (referenced above), "If all columns in ORDER BY and 
GROUP BY clauses come from the same table, that table is preferred first 
when joining. ". His GROUP BY was on two columns from his c table which 
meant that the optimizer would have started at c and could have continued 
to build the virtual table by JOINing tables d and e to it until it 
finally added a (which should have stripped out most of c, d, and e from 
the virtual table). This was my imagined worst case scenario, that a would 
be JOINed to the virtual table second to last. (sorry designers! :-) ) 

One day really soon I am going to have to sit down and crawl through the 
optimizer code and convince myself that I don't have to be such a 
back-seat driver. I just haven't had the time.

Warmest Wishes!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



Michael Stassen <[EMAIL PROTECTED]> wrote on 08/02/2004 01:19:02 
PM:

> Shawn,
> 
> I see what you are saying, but I don't believe it works that way.  If 
that 
> were the case, putting the condition in the ON clause rather than the 
WHERE 
> clause would be at the top of the list of recommended optimizations in 
the 
> manual, but it's not.  In fact, the manual recommends against it, "You 
> should generally not have any conditions in the ON part that are used to 

> restrict which rows you want in the result set, but rather specify these 

> conditions in the WHERE clause. There are exceptions to this rule." 
> .
> 
> I think you are giving the optimizer (and its authors) way too little 
> credit.  If what you say were so,
> 
>SELECT * FROM a, b WHERE a.id=b.id AND a.val = 2;
> 
> or
> 
>SELECT * FROM a JOIN b ON a.id=b.id WHERE a.val = 2;
> 
> would be dramatically slower (and take a lot more memory) than
> 
>SELECT * FROM a JOIN b ON a.id=b.id AND a.val = 2;
> 
> I think that's your point, but, in fact, the EXPLAINs for those 3 
queries 
> are identical.
> 
> EXPLAIN SELECT * FROM a, b WHERE a.id=b.id AND a.val=2;
> 
+---++-+-+---+--+-+
> | table | type   | possible_keys   | key | ref   | rows | Extra  |
> 
+---++-+-+---+--+-+
> | a | ref| PRIMARY,val_idx | val_idx | const | 1883 | Using 
where |
> | b | eq_ref | PRIMARY | PRIMARY | a.id  |1 |  |
> 
+---++-+-+--

replication slave lags way behind master

[Jon Drukman]

i've got one master and one slave.  the master is VERY busy, tons of 
inserts/updates/deletes all the time.  (it's an extremely high traffic 
message board system.)

we've got a situation right now where the slave starts lagging WAY 
behind the master.  it's as if it simply can't run through the binary 
log fast enough to keep up.  both machines are identical hardware-wise, 
and very powerful (dual 3ghz P4, 4G RAM, 15K RPM scsi disks in RAID0+1). 
 the slave does not show undue load or anything.  mysql is the only 
process (besides normal linux stuff) running on both machines.  disk is 
fine, cpu is fine.  i don't know where to look next.

one of my developers says:
> The only thing I can think of is that on db2, we're running so many
> inserts/updates/deletes in parallel, while on db3 they have to run
> sequentially.  One set of long-running updates can hose up the whole
> queue.  Even if there's a series of 1-second updates that run on db2
> against a table that nobody else is accessing, that would add up on
> the db3 side.
(db2 is the master, db3 is the slave)
any ideas where to look for tuning/optimization?  we've converted some 
of the highest contention tables to InnoDB.  we may have to do more but 
these are big tables and it will take hours to convert them.

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

Re: FreeBSD and MySQL - mysqld eats CPU alive

[Ken Menzel]

Hi,
   It sounds like you are using the default thread library.  I would
install MySQL from /usr/ports/databases (WITH_LINUX_THREADS, see
options in Makefile) or barring that try Jeremey's excellent build
recipe at http://jeremy.zawodny.com/blog/archives/000458.html  .  We
used to have the same problem and everything runs great now.

Ken

- Original Message - 
From: "adp" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, July 30, 2004 2:58 PM
Subject: FreeBSD and MySQL - mysqld eats CPU alive


> I have several MySQL and FreeBSD installs across a few different
sites, and
> I consistently have problems with mysqld. It will begin to eat up
all of the
> CPU and eventually become unresponsive (or the machine will just
burn). I
> can't seem to manually reproduce this, but given enough time a
FreeBSD box
> with mysqld will go down.
>
> Our servers are generally heavily loaded.
>
> I would say that I'm doing something wrong (although what I could be
doing
> wrong I'm not sure), but I recently began working with another
company that
> has the EXACT SAME PROBLEM. They are even thinking of moving to
PostgreSQL,
> but we are trying to fix mysqld instead for now.
>
> This behavior has been seen on:
>
> FreeBSD 4.4, 4.7, 4.9, 4.10
> MySQL 3.x and 4.x
> Typical load: 50 qps
> With and without replication enabled.
> Some sites are SELECT heavy, some are INSERT heavy.
>
> For one site I think we will be moving from FreeBSD to Linux for the
MySQL
> servers since MySQL seems to run like a champ on Linux. We will
continue to
> use FreeBSD for everything else.
>
> Anyone experienced this problem? Is it mysqld or FreeBSD? I can't
pinpoint
> the exact issue.
>
>
>
> -- 
> 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]

.cnf files not included

[James B]

Hi 
 
Just installed myqsl for windows w/ installer and looked in the
directory and found no example ".cnf" files 
 
 
james

Re: using mysql in commercial software

[mos]

At 03:41 AM 8/2/2004, Jeremy Zawodny wrote:
On Mon, Jul 26, 2004 at 11:49:02PM -0500, mos wrote:
> At 04:43 PM 7/26/2004, you wrote:
>
> >looks like the answer is no.  As soon as fee based software touches the
> >mysql install on the PC, the user is obligated to pay the $250.
>
> Actually I believe the MySQL 4.x license is more stringent than that. If
> the MySQL database is distributed within a company for free, then a 
license
> is still required, unless the application is distributed under the
> GPL.

Huh?
I bet it depends on the country.  In the USA, companies are considered
singular legal entities.  "Internal distribution" is aking to giving
copies to yourself.
The GPL doesn't restrict that.

Well, I'm not so sure. Here it is straight from the horses mouth namely 
[EMAIL PROTECTED]

>>
 "Free use for those who never copy,
modify or distribute. As long as you never distribute (internally or
externally) the MySQL Software in any way, you are free to use it for
powering your application, irrespective of whether your application is under
GPL license or not,"
http://www.mysql.com/products/licensing/opensource-license.html.
Changes in our licensing began in version 3.23.19  Changed copyright for all
files to GPL.  Prior versions can/could use the client libraries under the
LGPL license. <<
You notice they say "never distribute (internally or externally) the MySQL 
software in any way..".  I don't know if the "MySQL Software" they are 
referring to are the MySQL libraries  libmysql.dll or libmysqld.dll files 
which is needed to run most MySQL applications, or any file that comes from 
MySQL AB.  I agree with you that it is rather stringent to prevent internal 
distribution of a MySQL application within a company, and I see no logical 
reason for preventing it. As I read it, the language they're using prevents 
the finance department from receiving a copy of the database and 
application from the sales department. But I'm not a lawyer, and I don't 
play one on TV, so if you want further clarification, contact 
[EMAIL PROTECTED]

Mike 

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

SSL in -max

[Jeremy Rice]

> Oops, I lied. :) -Max was built with SSL a long time ago (if I
> remember right), and now it's not. Sorry. 

...I noticed this earlier, too.

Note that your website still claims this ("The Max version includes
additional features such as the Berkeley DB storage engine and other
features that have not been exhaustively tested or are not required for
general usage, such as OpenSSL support, user-defined functions (UDFs),
and BIG_TABLE support.")

...It would be friendly of your company to remove this from the Download
pages.

Or, better yet, put SSL back in.  :D

(And I could take this opportunity to carp about how miserable the
Makefiles are with respect to building in SSL, yet MySQL repeatedly
claims "this is not a bug"... but I digress...)

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

can't log in mysql server

[Jean Zhong]

Hello everyone,

I met a strange problem.
I can't log in mysql server using my password
suddently.
I check the modified date of user.MYD & user.MYI in
/usr/local/mysql/data/mysql/ folder. Both of them were
not modified recently. That means I didn't change the
password for mysql server.

Did anyone meet the same problem before? Does anyone
know how to deal with it? Thank you very much.

I still tried to reset the password following the
mysql manual:

> mysqld_safe --skip-grant-tables &
or
> safe_mysqld --skip-grant-tables &

But it gave me the following errors in the
localhost.err:

040802 16:08:02  mysqld started
Warning: One can only use the --user switch if running
as root
040802 16:08:03  InnoDB: Started
040802 16:08:03  /usr/local/mysql/bin/mysqld: Can't
create/write to file
'/usr/local/mysql/data/localhost.pid' (Errcode: 13)
/usr/local/mysql/bin/mysqld: ready for connections.
Version: '4.0.18-debug-debug'  socket:
'/tmp/mysql.sock'  port: 3306

And, when I tried to connect to the mysql server:
> mysql -u root 

and update the password, use:
> UPDATE mysql.user SET Password=PASSWORD('newpwd')
->   WHERE User='root';

I got the following error:

ERROR 1017: Can't find file: './mysql/user.frm'
(errno: 13)


Does anyone have any idea about this?

I really appreciate your help and looking forward to
your reply.

Jean


 



__
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail 

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

RE: can't log in mysql server

[John McCaskey]

Did your client side host change?  The user/pass are sometimes setup to
only allow access from a specific host subnet or single ip address.

John A. McCaskey


-Original Message-
From: Jean Zhong [mailto:[EMAIL PROTECTED] 
Sent: Monday, August 02, 2004 1:27 PM
To: [EMAIL PROTECTED]
Subject: can't log in mysql server


Hello everyone,

I met a strange problem.
I can't log in mysql server using my password
suddently.
I check the modified date of user.MYD & user.MYI in
/usr/local/mysql/data/mysql/ folder. Both of them were not modified
recently. That means I didn't change the password for mysql server.

Did anyone meet the same problem before? Does anyone
know how to deal with it? Thank you very much.

I still tried to reset the password following the
mysql manual:

> mysqld_safe --skip-grant-tables &
or
> safe_mysqld --skip-grant-tables &

But it gave me the following errors in the
localhost.err:

040802 16:08:02  mysqld started
Warning: One can only use the --user switch if running
as root
040802 16:08:03  InnoDB: Started
040802 16:08:03  /usr/local/mysql/bin/mysqld: Can't create/write to file
'/usr/local/mysql/data/localhost.pid' (Errcode: 13)
/usr/local/mysql/bin/mysqld: ready for connections.
Version: '4.0.18-debug-debug'  socket:
'/tmp/mysql.sock'  port: 3306

And, when I tried to connect to the mysql server:
> mysql -u root

and update the password, use:
> UPDATE mysql.user SET Password=PASSWORD('newpwd')
->   WHERE User='root';

I got the following error:

ERROR 1017: Can't find file: './mysql/user.frm'
(errno: 13)


Does anyone have any idea about this?

I really appreciate your help and looking forward to
your reply.

Jean


 



__
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail 

-- 
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: In Statement Help

[Rhino]

- Original Message - 
From: "Andrew Dixon" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, August 02, 2004 9:44 AM
Subject: In Statement Help


> Hi Everyone.
>
> I require some help with an IN statement I'm trying to get working. I have
> inherited a database from someone else in which there is a table with
> project information and a field containing which geographic regions the
> projects relate. As a single project can relate to multiple geographic
> regions the person who created the database create this field as a varchar
> field and populated it with a pipe (|) delimited list of ID's of the
> geographic regions (ID from another table). An example of the data in this
> field is:
>
> 1|5|9|10|12
>
> Or
>
> 1
>
> Or
>
> 9|5|7
>
> I have been asked to write a query to return all the projects for a single
> geographic region. This is what I have come up with:
>
> SELECT project_title
> FROM projects
> WHERE 9 IN (REPLACE(geo_region,'|',','))
> ORDER BY project
>
> Where 9 is the geographic region ID I'm looking for and geo_region is the
> field containing the pipe delimited list. Now with the above three lines
of
> data I would have expected it to return 2 rows, however it only returns
one
> (the last one). If I change the number to 5, I would expect 2 rows as
well,
> however it doesn't return any. It only appears to return a row if the
number
> appears at the beginning of the list!!! I'm not sure where I'm going
wrong,
> or even if what I'm trying to do is possible (please say it is!!!). Any
> advise would be very gratefully received.
>
Do you have any option of redesigning the database? Putting multiple values
in a single "cell" (intersection of a row and column) is very bad form
indeed for a database. It would be far better design to store each of the
regions in their own separate table connected to the original table via a
foreign key.

Something like this:

PROJECT
++--+
| project_number | project_name |
++--+
|  1 | Alpha|
|  2 | Beta |
|  3 | Gamma|
++--+
Primary key: project_number

REGION
+---+---+
| region_number | region_name   |
+---+---+
| 1 | North America |
| 2 | Europe|
| 3 | Africa|
| 4 | Australia |
| 5 | South America |
| 6 | Asia  |
+---+---+
Primary key: region_number

PROJECT_REGION
++---+
| project_number | region_number |
++---+
|  1 | 1 |
|  1 | 2 |
|  1 | 4 |
|  2 | 1 |
|  2 | 3 |
|  3 | 6 |
++---+
Primary key: project_number, region_number
Foreign key: project_number references PROJECT table
Foreign key: region_number references REGION table

Then the query to get the projects that are running in Region 1 is:

select p.project_number, project_name
from project p inner join project_region pr on p.project_number =
pr.project_number
where pr.region_number = '1';

If you know the name of the Region that you want but don't know it's number,
this query will do the job:

select p.project_number, project_name
from project p inner join project_region pr on p.project_number =
pr.project_number
inner join region r on r.region_number = pr.region_number
where r.region_name = 'North America';

This is *far* nicer and easier than to have to mess about with parsing
individual elements of a single field.

By the way, in devising this modified data design, I've made the assumption
that the relationship between projects and regions is *not* one-to-many but
is actually many-to-many. In other words, I think it is true that a given
region can have several projects *and* that a given project can be operating
in several regions. That's why I created the PROJECT_REGION table: it shows
the *combinations* of projects and regions. PROJECT_REGION is called an
'intersection' table (or sometimes an 'association' table). The intersection
table ensures that you can determine all the projects that run in a given
region or that you can show all the regions in which a given project is
running.

This design is very robust and will support an infinite number of projects
and regions. Any project can be running in as many regions as you like.

I realize that you might not be at liberty to modify the table design to
match what I've suggested but I would highly recommend that you consider it.
It will make your job a *lot* easier ;-)

Rhino


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

Upgrading mySQL 3.23 to 4.0

[environmentalny]

Greetings:

 I am trying to upgrade to the new version of mySQL on Fedora Core 2 Linux.  I have 
"mysql  Ver 11.18 Distrib 3.23.58, for redhat-linux-gnu (i386)" 

I downloaded the following files:
 MySQL-client-4.0.20-0.i386.rpm
 MySQL-server-4.0.20-0.i386.rpm 

and as root ran the following:
[EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-server-4.0.20-0.i386.rpm 
MySQL-client-4.0.20-0.i386.rpm
warning: MySQL-server-4.0.20-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
error: Failed dependencies:
libmysqlclient.so.10 is needed by (installed) perl-DBD-MySQL-2.9003-4
libmysqlclient.so.10 is needed by (installed) mod_auth_mysql-20030510-4.1
libmysqlclient.so.10 is needed by (installed) php-mysql-4.3.4-11
[EMAIL PROTECTED] Desktop]#

I am not sure what I need to do can someone please assist. 

Thank You
Gary


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

RE: can't log in mysql server

[Jean Zhong]

Hi, John

Thank you for the response.

I chmod for all database files under ./data/, and now
I can update the password for the root user. 

Thanks a lot.
Jean


--- John McCaskey <[EMAIL PROTECTED]> wrote:

> Did your client side host change?  The user/pass are
> sometimes setup to
> only allow access from a specific host subnet or
> single ip address.
> 
> John A. McCaskey
> 
> 
> -Original Message-
> From: Jean Zhong [mailto:[EMAIL PROTECTED] 
> Sent: Monday, August 02, 2004 1:27 PM
> To: [EMAIL PROTECTED]
> Subject: can't log in mysql server
> 
> 
> Hello everyone,
> 
> I met a strange problem.
> I can't log in mysql server using my password
> suddently.
> I check the modified date of user.MYD & user.MYI in
> /usr/local/mysql/data/mysql/ folder. Both of them
> were not modified
> recently. That means I didn't change the password
> for mysql server.
> 
> Did anyone meet the same problem before? Does anyone
> know how to deal with it? Thank you very much.
> 
> I still tried to reset the password following the
> mysql manual:
> 
> > mysqld_safe --skip-grant-tables &
> or
> > safe_mysqld --skip-grant-tables &
> 
> But it gave me the following errors in the
> localhost.err:
> 
> 040802 16:08:02  mysqld started
> Warning: One can only use the --user switch if
> running
> as root
> 040802 16:08:03  InnoDB: Started
> 040802 16:08:03  /usr/local/mysql/bin/mysqld: Can't
> create/write to file
> '/usr/local/mysql/data/localhost.pid' (Errcode: 13)
> /usr/local/mysql/bin/mysqld: ready for connections.
> Version: '4.0.18-debug-debug'  socket:
> '/tmp/mysql.sock'  port: 3306
> 
> And, when I tried to connect to the mysql server:
> > mysql -u root
> 
> and update the password, use:
> > UPDATE mysql.user SET Password=PASSWORD('newpwd')
> ->   WHERE User='root';
> 
> I got the following error:
> 
> ERROR 1017: Can't find file: './mysql/user.frm'
> (errno: 13)
> 
> 
> Does anyone have any idea about this?
> 
> I really appreciate your help and looking forward to
> your reply.
> 
> Jean
> 
> 
>  
> 
> 
>   
> __
> Do you Yahoo!?
> Yahoo! Mail Address AutoComplete - You start. We
> finish.
> http://promotions.yahoo.com/new_mail 
> 
> -- 
> 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]
> 
> 



__
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail 

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

Re: using mysql in commercial software

[Jeremy Zawodny]

On Mon, Aug 02, 2004 at 03:11:10PM -0500, mos wrote:
> At 03:41 AM 8/2/2004, Jeremy Zawodny wrote:
> >
> >I bet it depends on the country.  In the USA, companies are considered
> >singular legal entities.  "Internal distribution" is aking to giving
> >copies to yourself.
> >
> >The GPL doesn't restrict that.
> 
> 
> Well, I'm not so sure. Here it is straight from the horses mouth namely 
> [EMAIL PROTECTED]
> 
>  >>
>   "Free use for those who never copy,
> modify or distribute. As long as you never distribute (internally or
> externally) the MySQL Software in any way, you are free to use it for
> powering your application, irrespective of whether your application is under
> GPL license or not,"
> http://www.mysql.com/products/licensing/opensource-license.html.

Yes, I've read that bit of the web site before.

> You notice they say "never distribute (internally or externally) the
> MySQL software in any way..".  I don't know if the "MySQL Software"
> they are referring to are the MySQL libraries libmysql.dll or
> libmysqld.dll files which is needed to run most MySQL applications,
> or any file that comes from MySQL AB.

Given that all of the code is licensed under the GPL, it hardly
matters which "piece" they're talking about.  The GPL does not
disallow me making a copy of libmysqlclient.so and putting it on a
second machine.

If you think it does, I'd like to know *where* in the GPL you (or
whoever) sees it.  From what the GPL FAQ tells me:

  http://www.gnu.org/licenses/gpl-faq.html

   Does the GPL allow me to require that anyone who receives the
   software must pay me a fee and/or notify me?

 No. In fact, a requirement like that would make the program
 non-free. If people have to pay when they get a copy of a
 program, or if they have to notify anyone in particular, then the
 program is not free. See the definition of free software.

 The GPL is a free software license, and therefore it permits
 people to use and even redistribute the software without being
 required to pay anyone a fee for doing so.

  http://www.gnu.org/licenses/gpl-faq.html#DoesTheGPLAllowRequireFee

To argue otherwise (as some at MySQL AB seem intent on doing) is
absurd.  While their CEO speaks and conferences about the success of
"open source business models" another arm of their organization seems
to be trying hard to warp the GPL to suit their sales targets.

And, quite frankly, I'm sick of it.

> I agree with you that it is rather stringent to prevent internal
> distribution of a MySQL application within a company, and I see no
> logical reason for preventing it.

Probably because there isn't one. :-)

> As I read it, the language they're using prevents the finance
> department from receiving a copy of the database and application
> from the sales department.

Right.

Of course, every department is free to download a copy from the MySQL
web site, right?

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

[book] High Performance MySQL -- http://highperformancemysql.com/

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

Re: Upgrading mySQL 3.23 to 4.0

[Japheth Cleaver]

At 01:58 PM 8/2/2004, [EMAIL PROTECTED] wrote:
Greetings:
 I am trying to upgrade to the new version of mySQL on Fedora Core 2 
Linux.  I have "mysql  Ver 11.18 Distrib 3.23.58, for redhat-linux-gnu (i386)"

I downloaded the following files:
 MySQL-client-4.0.20-0.i386.rpm
 MySQL-server-4.0.20-0.i386.rpm
and as root ran the following:
[EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-server-4.0.20-0.i386.rpm 
MySQL-client-4.0.20-0.i386.rpm
warning: MySQL-server-4.0.20-0.i386.rpm: V3 DSA signature: NOKEY, key ID 
5072e1f5
error: Failed dependencies:
libmysqlclient.so.10 is needed by (installed) perl-DBD-MySQL-2.9003-4
libmysqlclient.so.10 is needed by (installed) 
mod_auth_mysql-20030510-4.1
libmysqlclient.so.10 is needed by (installed) php-mysql-4.3.4-11
[EMAIL PROTECTED] Desktop]#

I am not sure what I need to do can someone please assist.
Thank You
Gary

Install
- MySQL-shared-4.0.20-0.i386.rpm, and
- MySQL-shared-compat-4.0.20-0.i386.rpm as well.
And probably MySQL-devel-4.0.20-0.i386.rpm just for kicks.
The 2nd one is the specific package that should solve the problem, 
especially with the php-mysql package.

HTH,
Japheth "J.C." Cleaver
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Question

[Remember14a]

My connection to the mysql is correct, that is "host, username, password",
however when I click the link of form so that it will pull out data from 
mysql database, I see the php code of the form, any guidance, please.

Re: Upgrading mySQL 3.23 to 4.0

[environmentalny]

I wound up having to do the following:

[EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-shared-compat-4.0.20-0.i386.rpm

warning: MySQL-shared-compat-4.0.20-0.i386.rpm: V3 DSA signature: NOKEY, key ID 
5072e1f5
Preparing...### [100%]
   1:MySQL-shared-compat### [100%]

[EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-shared-4.0.20-0.i386.rpm

warning: MySQL-shared-4.0.20-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
Preparing...### [100%]
package MySQL-shared-4.0.20-0 is already installed

[EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-devel-4.0.20-0.i386.rpm

warning: MySQL-devel-4.0.20-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
Preparing...### [100%]
file /usr/bin/comp_err from install of MySQL-devel-4.0.20-0 conflicts with 
file from package mysql-3.23.58-9
file /usr/bin/mysql_config from install of MySQL-devel-4.0.20-0 conflicts with 
file from package mysql-3.23.58-9

[EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-server-4.0.20-0.i386.rpm 
MySQL-client-4.0.20-0.i386.rpm MySQL-devel-4.0.20-0.i386.rpm

warning: MySQL-server-4.0.20-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
Preparing...### [100%]
   1:MySQL-server   ### [ 33%]
Installing all prepared tables
040802 17:21:17  Warning: Asked for 196608 thread stack, but got 126976
040802 17:21:17  /usr/sbin/mysqld: Shutdown Complete
   
  
   
  
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'
See the manual for more instructions.
   
  
NOTE:  If you are upgrading from a MySQL <= 3.22.10 you should run
the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be
able to use the new GRANT command!
   
  
Please report any problems with the /usr/bin/mysqlbug script!
   
  
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at https://order.mysql.com
   
  
   2:MySQL-client   ### [ 67%]
   3:MySQL-devel### [100%]
[EMAIL PROTECTED] Desktop]#
   
  
Does this mean success? Also, what does this line mean? "040802 17:21:17  Warning: 
Asked for 196608 thread stack, but got 126976"

Thanks for the help

- Original Message -
From: Japheth Cleaver <[EMAIL PROTECTED]>
Date: Monday, August 2, 2004 5:12 pm
Subject: Re: Upgrading mySQL 3.23 to 4.0

> At 01:58 PM 8/2/2004, [EMAIL PROTECTED] wrote:
> >Greetings:
> >
> >  I am trying to upgrade to the new version of mySQL on Fedora 
> Core 2 
> > Linux.  I have "mysql  Ver 11.18 Distrib 3.23.58, for redhat-
> linux-gnu (i386)"
> >
> >I downloaded the following files:
> >  MySQL-client-4.0.20-0.i386.rpm
> >  MySQL-server-4.0.20-0.i386.rpm
> >
> >and as root ran the following:
> >[EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-server-4.0.20-0.i386.rpm 
> >MySQL-client-4.0.20-0.i386.rpm
> >warning: MySQL-server-4.0.20-0.i386.rpm: V3 DSA signature: NOKEY, 
> key ID 
> >5072e1f5
> >error: Failed dependencies:
> > libmysqlclient.so.10 is needed by (installed) perl-DBD-
> MySQL-2.9003-4
> > libmysqlclient.so.10 is needed by (installed) 
> > mod_auth_mysql-20030510-4.1
> > libmysqlclient.so.10 is needed by (installed) php-mysql-
> 4.3.4-11
> >[EMAIL PROTECTED] Desktop]#
> >
> >I am not sure what I need to do can someone please assist.
> >
> >Thank You
> >Gary
> 
> 
> Install
> - MySQL-shared-4.0.20-0.i386.rpm, and
> - MySQL-shared-compat-4.0.20

Re: Question

[environmentalny]

At a loss on this one...

- Original Message -
From: [EMAIL PROTECTED]
Date: Monday, August 2, 2004 5:27 pm
Subject: Re: Question

> Yes, extension of file is php and php is running on server.
> 


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

Re: Upgrading mySQL 3.23 to 4.0

[environmentalny]

I am now getting 

[EMAIL PROTECTED] Desktop]# mysql -u root -pmypasshere
ERROR 2002: Can't connect to local MySQL server through socket 
'/var/lib/mysql/mysql.sock' (2)

any thoughts...

once again thanks for the assistance

- Original Message -
From: [EMAIL PROTECTED]
Date: Monday, August 2, 2004 5:26 pm
Subject: Re: Upgrading mySQL 3.23 to 4.0

> I wound up having to do the following:
> 
> [EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-shared-compat-4.0.20-
> 0.i386.rpm
> warning: MySQL-shared-compat-4.0.20-0.i386.rpm: V3 DSA signature: 
> NOKEY, key ID 5072e1f5
> Preparing...
> ### [100%]
>   1:MySQL-shared-compat
> ### [100%]
> 
> [EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-shared-4.0.20-0.i386.rpm
> 
> warning: MySQL-shared-4.0.20-0.i386.rpm: V3 DSA signature: NOKEY, 
> key ID 5072e1f5
> Preparing...
> ### [100%]
>package MySQL-shared-4.0.20-0 is already installed
> 
> [EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-devel-4.0.20-0.i386.rpm
> 
> warning: MySQL-devel-4.0.20-0.i386.rpm: V3 DSA signature: NOKEY, 
> key ID 5072e1f5
> Preparing...
> ### [100%]
>file /usr/bin/comp_err from install of MySQL-devel-4.0.20-0 
> conflicts with file from package mysql-3.23.58-9
>file /usr/bin/mysql_config from install of MySQL-devel-
> 4.0.20-0 conflicts with file from package mysql-3.23.58-9
> 
> [EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-server-4.0.20-0.i386.rpm 
> MySQL-client-4.0.20-0.i386.rpm MySQL-devel-4.0.20-0.i386.rpm
> 
> warning: MySQL-server-4.0.20-0.i386.rpm: V3 DSA signature: NOKEY, 
> key ID 5072e1f5
> Preparing...
> ### [100%]
>   1:MySQL-server   
> ### [ 33%]
> Installing all prepared tables
> 040802 17:21:17  Warning: Asked for 196608 thread stack, but got 
> 126976040802 17:21:17  /usr/sbin/mysqld: Shutdown Complete
>
>
> 
>
>
> 
> PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
> To do so, start the server, then issue the following commands:
> /usr/bin/mysqladmin -u root password 'new-password'
> /usr/bin/mysqladmin -u root -h localhost.localdomain password 'new-
> password'See the manual for more instructions.
>
>
> 
> NOTE:  If you are upgrading from a MySQL <= 3.22.10 you should run
> the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be
> able to use the new GRANT command!
>
>
> 
> Please report any problems with the /usr/bin/mysqlbug script!
>
>
> 
> The latest information about MySQL is available on the web at
> http://www.mysql.com
> Support MySQL by buying support/licenses at https://order.mysql.com
>
>
> 
>   2:MySQL-client   
> ### [ 67%]
>   3:MySQL-devel
> ### [100%]
> [EMAIL PROTECTED] Desktop]#
>
>
> 
> Does this mean success? Also, what does this line mean? "040802 
> 17:21:17  Warning: Asked for 196608 thread stack, but got 126976"
> 
> Thanks for the help
> 
> - Original Message -
> From: Japheth Cleaver <[EMAIL PROTECTED]>
> Date: Monday, August 2, 2004 5:12 pm
> Subject: Re: Upgrading mySQL 3.23 to 4.0
> 
> > At 01:58 PM 8/2/2004, [EMAIL PROTECTED] wrote:
> > >Greetings:
> > >
> > >  I am trying to upgrade to the new version of mySQL on Fedora 
> > Core 2 
> > > Linux.  I have "mysql  Ver 11.18 Distrib 3.23.58, for redhat-
> > linux-gnu (i386)"
> > >
> > >I downloaded the following files:
> > >  MySQL-client-4.0.20-0.i386.rpm
> > >  MySQL-server-4.0.20-0.i386.rpm
> > >
> > >and as root ran the following:
> > >[EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-server-4.0.20-
> 0.i386.rpm 
> > >MySQL-client-4.0.20-0.i386.rpm
> > >warning: MySQL-server

Fwd: Question

[Remember14a]

--- Begin Message ---


Long back I had set it and was working that time,For a long time I wasn't working on same,  at this point how to check the same, I don't remember, can you guide , please.
 
How do I check if mysql, php apache are configured, also any GUI graphical user interface for Apache please. 
--- End Message ---
-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Installation Problem with 4.0.20

[Whil Hentzen]

> When I then ran mysqlshow I only had the test database, not the mysql
> database as the documentation said I should.
>
> I then deleted everything and installed again, and got the same result.
>
> Does this mean the installation has failed ?

When this happens on Linux, it means you're running mysqlshow as a regular 
user who doesn't have rights to the mysql database. I think there's a 
corrolary in Windows (anonymous user?)

-- 
Whil

Moving to Linux: Freedom, Choice, Security, Opportunity
http://www.hentzenwerke.com


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

CREATE performance degradation from 4.0.17 -> 4.0.20

[Tinley, Jeremy]

We're upgrading from 3.23.58 to 4.0.20 and found that that although the
ALTER test results of sql-bench had been greatly improved, CREATE has
shown nasty performance degradation.  Just before needing to make the
decision to revert back to 3.23.58, we found a post here where someone
had a similar problem when using SAN storage.  We see the problem using
hardware RAID, shared storage or local SCSI disks.

The machine in question is a 3ghz, 4GB RAM, reiserfs.  The data and
application reside on local SCSI disks, 10k rpm. All installations are
the MySQL provided linux-binary (x86), Standard releases.

Here is an excerpt of sql-bench results:

TestABC DE
--
alter_table_add6026 88
alter_table_drop   4315 88
create+drop12   11   11   240  223
create_MANY_tables 10   11   10   220  228
create_index111 11
create_key+drop14   15   15   231  221
create_table000 00
select_1_row088 89
select_2_rows   199 99
select_column+column199 99
select_group_when_MANY_tables   59   1110   10


Column A is MySQL 3.23.58
Column B is MySQL 4.0.15
Column C is MySQL 4.0.16
Column D is MySQL 4.0.17
Column E is MySQL 4.0.20


The biggest problem is the create set.  That's a HUGE difference in the
exact same hardware.  Thoughts?


-J

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

Re: Upgrading mySQL 3.23 to 4.0

[environmentalny]

in my Linux Services... the mysqld entry is missing

i have mysql and that is supposedly running.

- Original Message -
From: Carlos Proal <[EMAIL PROTECTED]>
Date: Monday, August 2, 2004 6:19 pm
Subject: Re: Upgrading mySQL 3.23 to 4.0

> 
> The upgrade shutdown the database, i think you must restart it 
> manually, 
> maybe using some of the init scripts.
> 
> Carlos
> 
> 
> >From: [EMAIL PROTECTED]
> >Reply-To: [EMAIL PROTECTED]
> >To: [EMAIL PROTECTED]
> >CC: Japheth Cleaver <[EMAIL PROTECTED]>
> >Subject: Re: Upgrading mySQL 3.23 to 4.0
> >Date: Mon, 02 Aug 2004 17:31:03 -0400
> >
> >I am now getting
> >
> >[EMAIL PROTECTED] Desktop]# mysql -u root -pmypasshere
> >ERROR 2002: Can't connect to local MySQL server through socket 
> >'/var/lib/mysql/mysql.sock' (2)
> >
> >any thoughts...
> >
> >once again thanks for the assistance
> >
> >- Original Message -
> >From: [EMAIL PROTECTED]
> >Date: Monday, August 2, 2004 5:26 pm
> >Subject: Re: Upgrading mySQL 3.23 to 4.0
> >
> > > I wound up having to do the following:
> > >
> > > [EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-shared-compat-4.0.20-
> > > 0.i386.rpm
> > > warning: MySQL-shared-compat-4.0.20-0.i386.rpm: V3 DSA signature:
> > > NOKEY, key ID 5072e1f5
> > > Preparing...
> > > ### [100%]
> > >   1:MySQL-shared-compat
> > > ### [100%]
> > >
> > > [EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-shared-4.0.20-0.i386.rpm
> > >
> > > warning: MySQL-shared-4.0.20-0.i386.rpm: V3 DSA signature: NOKEY,
> > > key ID 5072e1f5
> > > Preparing...
> > > ### [100%]
> > >package MySQL-shared-4.0.20-0 is already installed
> > >
> > > [EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-devel-4.0.20-0.i386.rpm
> > >
> > > warning: MySQL-devel-4.0.20-0.i386.rpm: V3 DSA signature: NOKEY,
> > > key ID 5072e1f5
> > > Preparing...
> > > ### [100%]
> > >file /usr/bin/comp_err from install of MySQL-devel-
> 4.0.20-0
> > > conflicts with file from package mysql-3.23.58-9
> > >file /usr/bin/mysql_config from install of MySQL-devel-
> > > 4.0.20-0 conflicts with file from package mysql-3.23.58-9
> > >
> > > [EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-server-4.0.20-0.i386.rpm
> > > MySQL-client-4.0.20-0.i386.rpm MySQL-devel-4.0.20-0.i386.rpm
> > >
> > > warning: MySQL-server-4.0.20-0.i386.rpm: V3 DSA signature: NOKEY,
> > > key ID 5072e1f5
> > > Preparing...
> > > ### [100%]
> > >   1:MySQL-server
> > > ### [ 33%]
> > > Installing all prepared tables
> > > 040802 17:21:17  Warning: Asked for 196608 thread stack, but got
> > > 126976040802 17:21:17  /usr/sbin/mysqld: Shutdown Complete
> > >
> > >
> > >
> > >
> > >
> > >
> > > PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
> > > To do so, start the server, then issue the following commands:
> > > /usr/bin/mysqladmin -u root password 'new-password'
> > > /usr/bin/mysqladmin -u root -h localhost.localdomain password 
> 'new-
> > > password'See the manual for more instructions.
> > >
> > >
> > >
> > > NOTE:  If you are upgrading from a MySQL <= 3.22.10 you should run
> > > the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be
> > > able to use the new GRANT command!
> > >
> > >
> > >
> > > Please report any problems with the /usr/bin/mysqlbug script!
> > >
> > >
> > >
> > > The latest information about MySQL is available on the web at
> > > http://www.mysql.com
> > > Support MySQL by buying support/licenses at 
> https://order.mysql.com> >
> > >
> > >
> > >   2:MySQL-client
> > > ### [ 67%]
> > >   3:MySQL-devel
> > > ### [100%]
> > > [EMAIL PROTECTED] Desktop]#
> > >
> > >
> > >
> > > Does this mean success? Also, what does this line mean? "040802
> > > 17:21:17  Warning: Asked for 196608 thread stack, but got 126976"
> > >
> > > Thanks for the help
> > >
> > > - Original Message -
> > > From: Japheth Cleaver <[EMAIL PROTECTED]>
> > > Date: Monday, August 2, 2004 5:12 pm
> > > Subject: Re: Upgrading mySQL 3.23 to 4.0
> > >
> > > > At 01:58 PM 8/2/2004, [EMAIL PROTECTED] wrote:
> > > > >Greetings:
> > > > >
> > > > >  I am trying to upgrade to the new version of mySQL on Fedora
> > > > Core 2
> > > > > Linux.  I have "mysql  Ver 11.18 Distrib 3.23.58, for 
> redhat-
> > > > linux-gnu (i386)"
> > > > >
> > > > >I downloaded the following files:
> > > > >  MySQL-client-4.0.20-0.i386.rpm
> > > > >  MySQL-server-4.0.20-0.i386.rpm
> > > > >
> > > > >and as root ran the following:
> > > > >[EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-server-4.0.20-
> > > 0.i386.rpm
> > > > >MySQL-client-4.0.20-0.i386.rpm
> > > > >warning: MySQL-server-4.0.20-0.i386.rpm: V3 DSA signature:
> > > NOKEY,
> > > > key ID
> > > > >5072e1f5
> > > > >error: Failed dependencies:
> > > > > l

Re: Question

[Remember14a]

Yes, extension of file is php and php is running on server.

doing something similiar to RRD in a mysql database

[Ian Holsman]

RRD (http://www.rrdtool.org) is a popular tool which sysadmins use to 
collect to network stats (among other things)

One of the best thing about RRD is that it only stores X datapoints at 
any one time, and also aggregates simple stats about the data, making it 
self-archiving. I was wondering if anyone has implemented a UDF or 
something which does a similiar thing with data in a mysql table.

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

Cant get this JOIN to work

[Scott Haneda]

tSql = "SELECT c.campaign_name, c.id
FROM campaigns as c
INNER JOIN addresses_incampaign as i on (c.user_id = i.user_id)
WHERE c.user_id = " & prepSQL(tConn, user_id) & "
GROUP BY c.id";

This works when there is data in the addresses_incampaign table, but when
there is not, it returns nothing, which is not what I want.
-- 
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com   Fax: 313.557.5052
[EMAIL PROTECTED]Novato, CA U.S.A.



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

Help understanding how the connection works

[Gonçalo Silva Dias]

Hi ppl,

 

I’m trying to access to mysql server by telnet. When I do like “telnet
localhost 3306” if I write something the error is “Bad handshake”. Well can
anyone tell me what info do I need to send to the server to establish a
connection with mysql server? So I can execute some queries?

Is this result set possible

[Scott Haneda]

Here is what I am doing now, in mysql 4.x

This gives me a name/id list from a table
SELECT campaign_name, id
FROM campaigns
WHERE user_id = 123;


This will tell me which of the above name/id's are in use in another table.
SELECT distinct(campaign_id)
FROM addresses_incampaign
WHERE user_id = 123
ORDER BY campaign_id";

The reason I need to know which campaign_id's are used in the second table,
is to see if they are allowed to delete the record from campaigns, if there
are matching records in both tables, they are not allowed to delete.

Somehow, I am looking to get a single SQL query to give me a result with a
count as a int
Campaign_name   records_in addresses_incampaign
foo 15
bar 12
example 0   (safe to delete)
anohther2

-- 
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com   Fax: 313.557.5052
[EMAIL PROTECTED]Novato, CA U.S.A.



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

archive db daily- Rough Draft

[Jacob, Raymond A Jr]

-Original Message-
From: Jacob, Raymond A Jr 
Sent: Monday, August 02, 2004 21:17
To: [EMAIL PROTECTED] 
Subject: archive db daily- Rough Draft


I  am new to Databases and would appreciate a second set of eyes looking over my sql 
script.
The script takes the existing snort database  and copies it to a database named
snort-2004-08-02. Then copies over a day's worth of information to  the database 
snort-2004-08-02. Once the database has been copied over, mysqlhotcopy is
used to copy the database to another directory. The database is then archived
and compressed to a file with a .tgz extension. After a week the database
is removed. I assume that I can find the age of the snort-2004-08-02 database
from  mysql without checking  a table in the snort-2004-08-02 database
When I determine that the database is over 7-days old I will  drop the database?

thank you,
Raymond

 ---copySnort.sql---
#copySnort.sql copy snortdb database to snort---
CREATE DATABASE snort-2004-08-02;
USE snort-2004-08-02;

 # Not sure if this is even necessary Database may use my default information.
GRANT INSERT,SELECT on snort-2004-08-02.* to [EMAIL PROTECTED];

CREATE TABLE acid_event   ( sid INT UNSIGNED NOT NULL,
cid INT UNSIGNED NOT NULL, 
signature   INT UNSIGNED NOT NULL,
sig_nameVARCHAR(255),
sig_class_idINT UNSIGNED,
sig_priorityINT UNSIGNED,
timestamp   DATETIME NOT NULL,
ip_src  INT UNSIGNED,
ip_dst  INT UNSIGNED,
ip_protoINT,
layer4_sportINT UNSIGNED,
layer4_dportINT UNSIGNED,
PRIMARY KEY (sid,cid),
INDEX   (signature),
INDEX   (sig_name),
INDEX   (sig_class_id),
INDEX   (sig_priority),
INDEX   (timestamp),
INDEX   (ip_src),
INDEX   (ip_dst),
INDEX   (ip_proto),
INDEX   (layer4_sport),
INDEX   (layer4_dport)
  );
 
INSERT snort-2004-08-02.acid_event.* 
SELECT snort.acid_event.*
FROM snort.acid_event, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and  
2004-08-01
 AND (snort.event.cid = snort.acid_event.cid); 

CREATE TABLE acid_ag  ( ag_id   INT   UNSIGNED NOT NULL 
AUTO_INCREMENT,
ag_name VARCHAR(40),
ag_desc TEXT, 
ag_ctimeDATETIME,
ag_ltimeDATETIME,

PRIMARY KEY (ag_id),
INDEX   (ag_id));

INSERT snort-2004-08-02.acid_ag.* 
SELECT snort.acid_ag.*, snort.acid_ag_alert.ag_id, snort.acid_ag_alert.ag_cid
FROM snort.acid_ag, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and  
'2004-08-01'
 AND (snort.event.cid = snort.acid_ag.cid); 


CREATE TABLE acid_ag_alert( ag_id   INT   UNSIGNED NOT NULL,
ag_sid  INT   UNSIGNED NOT NULL,
ag_cid  INT   UNSIGNED NOT NULL, 

PRIMARY KEY (ag_id, ag_sid, ag_cid),
INDEX   (ag_id),
INDEX   (ag_sid, ag_cid));

INSERT snort-2004-08-02.acid_ag_alert.* 
SELECT snort.acid_ag_alert.*
FROM snort.acid_event, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and  
2004-08-01)
 AND (snort.event.cid = snort.acid_ag.cid); 

CREATE TABLE acid_ip_cache( ipc_ip  INT   UNSIGNED NOT NULL,
ipc_fqdnVARCHAR(50),
ipc_dns_timestamp   DATETIME,
ipc_whois   TEXT,
ipc_whois_timestamp DATETIME,

PRIMARY KEY (ipc_ip),
INDEX   (ipc_ip) );

INSERT snort-2004-08-02.acid_ip_cache.* 
SELECT snort.acid_ip_cache.*
FROM snort.acid_event, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and  
2004-08-01)
 AND (snort.

on big mysql .MYD files and linux's 2GB file size limit

[Shannon R.]

Is the 2GB filesize limit in linux still there?
Specially in Gentoo and Debian linux?

If so, how can this be addressed when your mysql's
.MYD files reach 2GB?

Is there any way MySQL can split really big .MYD
files?


Regards,
Shannon





__
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail

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

SSH connection from a client machine - localhost works but not 127.0.0.1

[Whil Hentzen]

Hi folks,

Now that I can connect to my MySQL server using the Unix sockets (e.g.
   [EMAIL PROTECTED] ~] mysql -u root
I'm moving on to the next step - connecting via TCP/IP.

Since I don't have physical access to the box (well, I'm just too lazy to walk 
down to the basement), I SSH into the box from a second machine (the name of 
the box that 'www.example.com' is running on is called 'daisy'):

shell> ssh www.example.com
[EMAIL PROTECTED]'s password:
[EMAIL PROTECTED] ~] mysql -h localhost -u root -p
Enter password: 
mysql>

Works great. Less filling, too.

But if I try

[EMAIL PROTECTED] ~] mysql -h 127.0.0.1 -u root -p
Enter password: 
ERROR 1130: Host 'localhost.localdomain' is not allowed to connect to this 
MySQL server
[EMAIL PROTECTED] ~]

I've googled through a bunch of posts, and the old line about 'understanding 
the words, but not the sentences' applies here. As best I can tell, this 
problem normally comes up when the domain (such as www.example.com) isn't in 
the users table. In this case, 127.0.0.1 should be equivalent to 'localhost', 
which is in there:

mysql> select host, user, password from mysql.user ;
+--+--+--+
| host | user | password |
+--+--+--+
| localhost| root | 48bf4fd20c61a2f0 |
| daisy.example.com | root | 48bf4fd20c61a2f0 |
| localhost|  |  |
| daisy.example.com |  |  |
+--+--+--+

Do I need to change the 'localhost' entries to 'localhost.localdomain' in 
order for 127.0.0.1 to work? If I do so, will "mysql -h localhost" then fail?

I know I could just try it out myself, but I want to know WHY *s*

Thanks,

-- 
Whil

Moving to Linux: Freedom, Choice, Security, Opportunity
http://www.hentzenwerke.com


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

Re: Upgrading mySQL 3.23 to 4.0

[Carlos Proal]

supposedly ?, you can check if the mysqld daemon is running with:
ps -ef | grep mysql
if the daemon is running then the error is something else but my first bet 
is that is not running.

Carlos
From: [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Carlos Proal <[EMAIL PROTECTED]>
CC: [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: Re: Upgrading mySQL 3.23 to 4.0
Date: Mon, 02 Aug 2004 18:26:05 -0400
in my Linux Services... the mysqld entry is missing
i have mysql and that is supposedly running.
- Original Message -
From: Carlos Proal <[EMAIL PROTECTED]>
Date: Monday, August 2, 2004 6:19 pm
Subject: Re: Upgrading mySQL 3.23 to 4.0
>
> The upgrade shutdown the database, i think you must restart it
> manually,
> maybe using some of the init scripts.
>
> Carlos
>
>
> >From: [EMAIL PROTECTED]
> >Reply-To: [EMAIL PROTECTED]
> >To: [EMAIL PROTECTED]
> >CC: Japheth Cleaver <[EMAIL PROTECTED]>
> >Subject: Re: Upgrading mySQL 3.23 to 4.0
> >Date: Mon, 02 Aug 2004 17:31:03 -0400
> >
> >I am now getting
> >
> >[EMAIL PROTECTED] Desktop]# mysql -u root -pmypasshere
> >ERROR 2002: Can't connect to local MySQL server through socket
> >'/var/lib/mysql/mysql.sock' (2)
> >
> >any thoughts...
> >
> >once again thanks for the assistance
> >
> >- Original Message -
> >From: [EMAIL PROTECTED]
> >Date: Monday, August 2, 2004 5:26 pm
> >Subject: Re: Upgrading mySQL 3.23 to 4.0
> >
> > > I wound up having to do the following:
> > >
> > > [EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-shared-compat-4.0.20-
> > > 0.i386.rpm
> > > warning: MySQL-shared-compat-4.0.20-0.i386.rpm: V3 DSA signature:
> > > NOKEY, key ID 5072e1f5
> > > Preparing...
> > > ### [100%]
> > >   1:MySQL-shared-compat
> > > ### [100%]
> > >
> > > [EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-shared-4.0.20-0.i386.rpm
> > >
> > > warning: MySQL-shared-4.0.20-0.i386.rpm: V3 DSA signature: NOKEY,
> > > key ID 5072e1f5
> > > Preparing...
> > > ### [100%]
> > >package MySQL-shared-4.0.20-0 is already installed
> > >
> > > [EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-devel-4.0.20-0.i386.rpm
> > >
> > > warning: MySQL-devel-4.0.20-0.i386.rpm: V3 DSA signature: NOKEY,
> > > key ID 5072e1f5
> > > Preparing...
> > > ### [100%]
> > >file /usr/bin/comp_err from install of MySQL-devel-
> 4.0.20-0
> > > conflicts with file from package mysql-3.23.58-9
> > >file /usr/bin/mysql_config from install of MySQL-devel-
> > > 4.0.20-0 conflicts with file from package mysql-3.23.58-9
> > >
> > > [EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-server-4.0.20-0.i386.rpm
> > > MySQL-client-4.0.20-0.i386.rpm MySQL-devel-4.0.20-0.i386.rpm
> > >
> > > warning: MySQL-server-4.0.20-0.i386.rpm: V3 DSA signature: NOKEY,
> > > key ID 5072e1f5
> > > Preparing...
> > > ### [100%]
> > >   1:MySQL-server
> > > ### [ 33%]
> > > Installing all prepared tables
> > > 040802 17:21:17  Warning: Asked for 196608 thread stack, but got
> > > 126976040802 17:21:17  /usr/sbin/mysqld: Shutdown Complete
> > >
> > >
> > >
> > >
> > >
> > >
> > > PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
> > > To do so, start the server, then issue the following commands:
> > > /usr/bin/mysqladmin -u root password 'new-password'
> > > /usr/bin/mysqladmin -u root -h localhost.localdomain password
> 'new-
> > > password'See the manual for more instructions.
> > >
> > >
> > >
> > > NOTE:  If you are upgrading from a MySQL <= 3.22.10 you should run
> > > the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be
> > > able to use the new GRANT command!
> > >
> > >
> > >
> > > Please report any problems with the /usr/bin/mysqlbug script!
> > >
> > >
> > >
> > > The latest information about MySQL is available on the web at
> > > http://www.mysql.com
> > > Support MySQL by buying support/licenses at
> https://order.mysql.com> >
> > >
> > >
> > >   2:MySQL-client
> > > ### [ 67%]
> > >   3:MySQL-devel
> > > ### [100%]
> > > [EMAIL PROTECTED] Desktop]#
> > >
> > >
> > >
> > > Does this mean success? Also, what does this line mean? "040802
> > > 17:21:17  Warning: Asked for 196608 thread stack, but got 126976"
> > >
> > > Thanks for the help
> > >
> > > - Original Message -
> > > From: Japheth Cleaver <[EMAIL PROTECTED]>
> > > Date: Monday, August 2, 2004 5:12 pm
> > > Subject: Re: Upgrading mySQL 3.23 to 4.0
> > >
> > > > At 01:58 PM 8/2/2004, [EMAIL PROTECTED] wrote:
> > > > >Greetings:
> > > > >
> > > > >  I am trying to upgrade to the new version of mySQL on Fedora
> > > > Core 2
> > > > > Linux.  I have "mysql  Ver 11.18 Distrib 3.23.58, for
> redhat-
> > > > linux-gnu (i386)"
> > > > >
> > > > >I downloaded the following files:
> > > > >  MySQL-client-4.0.20-0.i386.

RE: Help understanding how the connection works

[Carlos Proal]

Hi, you cant connect to mysql with telnet, the service running in 3306 
supports a dbms specific protocol so you need to use the "mysql" command aka 
mysql client to connect to mysql and execute any sql query.

Carlos

From: Gonçalo Silva Dias <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>,<[EMAIL PROTECTED]>
Subject: Help understanding how the connection works
Date: Tue, 3 Aug 2004 02:10:56 +0100
Hi ppl,

I’m trying to access to mysql server by telnet. When I do like “telnet
localhost 3306” if I write something the error is “Bad handshake”. Well can
anyone tell me what info do I need to send to the server to establish a
connection with mysql server? So I can execute some queries?
_
Help STOP SPAM with the new MSN 8 and get 2 months FREE*  
http://join.msn.com/?page=features/junkmail

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

Re: on big mysql .MYD files and linux's 2GB file size limit

[Eric Gunnett]

Depends on what file system you are running that is a ext2 limit if you move 
to ext3 or reiser you should be fine. But then a default config on MySQL will run into 
a 4.29 Gig limit, which you can change.


Eric Gunnett
System Administrator
Zoovy, Inc.
[EMAIL PROTECTED]


>>> "Shannon R." <[EMAIL PROTECTED]> 08/02/04 06:58PM >>>
Is the 2GB filesize limit in linux still there?
Specially in Gentoo and Debian linux?

If so, how can this be addressed when your mysql's
.MYD files reach 2GB?

Is there any way MySQL can split really big .MYD
files?


Regards,
Shannon





__
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail 

-- 
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: SSH connection from a client machine - localhost works but not 127.0.0.1

[James Weisensee]

What does your '/etc/hosts' file contain?  Sounds like
it may have the following entry:

127.0.0.1   localhost.localdomain 

change it to:

127.0.01localhost 

or

Yes, add 'localhost.localdomain' to mysql.user

another option, Why not just SSH to 'daisy' and issue:

shell> mysql -u root -p

and let it default to localhost.

HTH,
James



> But if I try
> 
> [EMAIL PROTECTED] ~] mysql -h 127.0.0.1 -u root -p
> Enter password: 
> ERROR 1130: Host 'localhost.localdomain' is not
> allowed to connect to this 
> MySQL server
> [EMAIL PROTECTED] ~]
> 
> I've googled through a bunch of posts, and the old
> line about 'understanding 
> the words, but not the sentences' applies here. As
> best I can tell, this 
> problem normally comes up when the domain (such as
> www.example.com) isn't in 
> the users table. In this case, 127.0.0.1 should be
> equivalent to 'localhost', 
> which is in there:
> 
> mysql> select host, user, password from mysql.user ;
> +--+--+--+
> | host | user | password |
> +--+--+--+
> | localhost| root | 48bf4fd20c61a2f0 |
> | daisy.example.com | root | 48bf4fd20c61a2f0 |
> | localhost|  |  |
> | daisy.example.com |  |  |
> +--+--+--+
> 
> Do I need to change the 'localhost' entries to
> 'localhost.localdomain' in 
> order for 127.0.0.1 to work? If I do so, will "mysql
> -h localhost" then fail?
> 
> I know I could just try it out myself, but I want to
> know WHY *s*



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

RE: Cant get this JOIN to work

[Victor Pendleton]

If you want the data in the campaigns table regardless if an entery exists
int he addresses_incampaign table then you should use a LEFT OUTER JOIN.

-Original Message-
From: Scott Haneda
To: MySql
Sent: 8/2/04 7:37 PM
Subject: Cant get this JOIN to work

tSql = "SELECT c.campaign_name, c.id
FROM campaigns as c
INNER JOIN addresses_incampaign as i on (c.user_id = i.user_id)
WHERE c.user_id = " & prepSQL(tConn, user_id) & "
GROUP BY c.id";

This works when there is data in the addresses_incampaign table, but
when
there is not, it returns nothing, which is not what I want.
-- 
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com   Fax: 313.557.5052
[EMAIL PROTECTED]Novato, CA U.S.A.



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


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

Re: Problem with Mysql 4.0.18 + Debian

[Batara Kesuma]

Ryan,

> Here's the problem.  I had the site up several days, with everything
> running perfectly.  Ordinarily there would be about 11 mysql processes
> running.  However, after a few days of running smoothly, the number of
> mysql processes increases to over 170, and the site crashes.  Formerly
> I'd get errors like
> 
> DBI connect('yada yada, ...) failed: Too many connections at DB.pm
> line 25

Are you running Perl from CGI or mod_perl? If you run it from mod_perl,
maybe you have problem with persistent connection. Maybe you forgot to
disconnect DB handler.

--Batara

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

Re: Upgrading mySQL 3.23 to 4.0

[Batara Kesuma]

On Mon, 02 Aug 2004 17:31:03 -0400
[EMAIL PROTECTED] wrote:

> I am now getting 
> 
> [EMAIL PROTECTED] Desktop]# mysql -u root -pmypasshere
> ERROR 2002: Can't connect to local MySQL server through socket 
> '/var/lib/mysql/mysql.sock' (2)
> 
> any thoughts...
> 
> once again thanks for the assistance

It seems like your MySQL server is not running. Try:
# /etc/init.d/mysql start

And check what does the error log say. On my Fedora it is in:
/var/lib/mysql/hostname.err


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

Re: SSH connection from a client machine - localhost works but not 127.0.0.1

[Michael Stassen]

From mysql's point of view, localhost and 127.0.0.1 are not the same thing. 
 When you connect with

  mysql -h localhost -u root -p
or simply
  mysql -u root -p
(localhost is the default), you are connecting via unix socket, so the 
user=root, host=localhost entry is used.  When you connect with

  mysql -h 127.0.0.1 -u root -p
however, you are connecting to 127.0.0.1 via tcp/ip, and there must be an 
entry with user=root and host=the.client.machine.  Currently, you have 
entries for [EMAIL PROTECTED] and [EMAIL PROTECTED], so you can log in as 
root from localhost via unix socket or daisy.example.com via tcp/ip.  See 
the manual 
for the details.

If you want the client to connect via tcp/ip when you are already on the 
server, you'll have to add an entry to allow that, but there is no advantage 
to connecting via tcp/ip when you're already on the server.  I'd forget 
about 127.0.0.1 and just use the unix socket.

Michael
Whil Hentzen wrote:
Hi folks,
Now that I can connect to my MySQL server using the Unix sockets (e.g.
   [EMAIL PROTECTED] ~] mysql -u root
I'm moving on to the next step - connecting via TCP/IP.
Since I don't have physical access to the box (well, I'm just too lazy to walk 
down to the basement), I SSH into the box from a second machine (the name of 
the box that 'www.example.com' is running on is called 'daisy'):

shell> ssh www.example.com
[EMAIL PROTECTED]'s password:
[EMAIL PROTECTED] ~] mysql -h localhost -u root -p
Enter password: 
mysql>

Works great. Less filling, too.
But if I try
[EMAIL PROTECTED] ~] mysql -h 127.0.0.1 -u root -p
Enter password: 
ERROR 1130: Host 'localhost.localdomain' is not allowed to connect to this 
MySQL server
[EMAIL PROTECTED] ~]

I've googled through a bunch of posts, and the old line about 'understanding 
the words, but not the sentences' applies here. As best I can tell, this 
problem normally comes up when the domain (such as www.example.com) isn't in 
the users table. In this case, 127.0.0.1 should be equivalent to 'localhost', 
which is in there:

mysql> select host, user, password from mysql.user ;
+--+--+--+
| host | user | password |
+--+--+--+
| localhost| root | 48bf4fd20c61a2f0 |
| daisy.example.com | root | 48bf4fd20c61a2f0 |
| localhost|  |  |
| daisy.example.com |  |  |
+--+--+--+
Do I need to change the 'localhost' entries to 'localhost.localdomain' in 
order for 127.0.0.1 to work? If I do so, will "mysql -h localhost" then fail?

I know I could just try it out myself, but I want to know WHY *s*
Thanks,

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