setting root psswd on Win2K ??

[Bryan Capitano]

Having some problems setting root password on MySQL. Can anybody help
me?

I've just installed MySQL 3.23.38 on a Windows 2000 server.
I'm following the directions in the documenation for setting the root
password:

>mysql -u root mysql
mysql>UPDATE user
mysql>SET password=password('mynewpass')
mysql>WHERE user='root';
mysql>FLUSH PRIVILEGES;

okay, all seemed fine. now if I log out and log back as pain old nobody:

>mysql
mysql>use mysql;
mysql>drop table user;
okay 1 table affected.

How come just anybody can log into my MySQL server and start dropping
tables
in the mysql database?? This shouldn't be right. What have I done wrong?

Either I have a serious mis-understanding or something is seriously not
right here.

Thanks for any help!






-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Mandrake and myisampack

[Eric]

I had mandrake 7.2 running on my system.  It crashed and I 
transfered my back up files to a system running Debian 2.2.
The file were db.frm db.myd db.myi.  These are packed files
and the mysql I am now running does not use these.
What can I do??
 
Need help   Eric.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: command mysql -u $user without typing it ????

[Carl Troein]

Matthew Darcy writes:

> I was aware of the user table as a standard table.
> 
> I did a select on it and it all looked fine. Loads of Y's and % for host
> Yet this user still cannot connect. The only explaination is corruption.

It sounds to me like you haven't removed the @localhost entry,
but you want user@% to be matched when you connect from localhost.
Could that be it? If so, section 4.2.8 is your (rather dull) friend.

//C

-- 
 Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280
 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/
 Amiga user since '89, and damned proud of it too.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Restoring database from hotcopy

[Travis Farmer]

I have the database files, now how do I put them back into mysql?

Restore table doesn't seem to work.

Thanks in advance,
~Travis


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Interesting Problem

[Yoed Anis]

Hi... I have an interesting problem I don't know which way to solve. I tried
posting this on the PHP site (since I'm coding with PHP and mysql) but they
said I might want to try my odds here.. since they suggested I go with the
mysql solution, but I'm clueless where to start. So I'll shoot it out to you
guys and see what you might offer.

I have two databases, say X, and Y:

CREATE TABLE X(
Id int(11) NOT NULL auto_increment,
Dep_Date date,
Return_Date date,
Cat1_Status varchar(100),
Cat2_Status varchar(100),
Cat3_Status varchar(100),
Cat4_Status varchar(100),
PRIMARY KEY (Id));

CREATE TABLE Y(
Id int(11) NOT NULL auto_increment,
Dep_Date date,
Return_Date date,
A_Status varchar(100),
B_Status varchar(100),
C_Status varchar(100),
D_Status varchar(100),
E_Status varchar(100),
PRIMARY KEY (Id));

Now what I am trying to do is get it to display on one page one listing in
Chronoligical order based on the Dep_Date from BOTH of these tables. Trying
something simple like
mysql_query("SELECT Id, Dep_Date, Return_DateFROM X,Y WHERE Dep_Date LIKE
'%$SelectDate%' OR Return_Date LIKE '%$SelectDate%' ORDER BY Dep_Date");
Will give you a ton of errors, and I'm not very fimilar with JOIN and SQL
and how that works. My idea was to create two querys, but the results in
somesort of array, and then order the array by date... I was wondering
though if this is a good efficient way or if you guys have any better
suggestions as to what I should do.

Thanks for your time and help,
Yoed


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: command mysql -u $user without typing it ????

[Doug Thompson]

Matt:

I don't remember you saying what machine you're running mysql on.

Try adding another copy of the same user only @localhost.  My win32 installation 
requires that for a dos box.  The hosting 
service I use doesn't seem to care as it almost seems to ignore the Host column.

GRANT ALL PRIVILEGES ON *.* TO foo@localhost IDENTIFIED BY 'password' WITH GRANT 
OPTION;
GRANT ALL PRIVILEGES ON *.* TO foo@"%" IDENTIFIED BY 'password' WITH GRANT OPTION;

Good luck.
Doug


On Wed, 9 Jan 2002 20:38:46 -, Matthew Darcy wrote:

>I was doing a flush privileges.
>
>but this was coming up with the same thing no rows affected which to me was
>concerning.
>
>I have now got 1 user working but 1 user not.
>
>I am going to delete all users and start again with the users, as at the
>start I was not doing flush privileges and I was also trying put hostnames
>without DNS into the hosts field. I may have corrupted the user accounts in
>some way.
>
>I'll keep you posted.
>
>Thanks for the help and the good explainations though.
>
>I am sure it will be a user error on my part if 1 user is working and
>another 1 not.
>
>Cheers.
>
>Matt.
>
>
>-Original Message-
>From: James Montebello [mailto:[EMAIL PROTECTED]]
>Sent: 09 January 2002 19:43
>To: Matthew Darcy
>Cc: Ho Kam; Gerald Clark; MySql List
>Subject: RE: command mysql -u $user without typing it 
>
>
>
>You must do a "FLUSH PRIVILEGES" after making any changes to the user
>permissions before they will actually take effect.  This is documented.
>
>james montebello
>
>On Wed, 9 Jan 2002, Matthew Darcy wrote:
>
>> query ok I would expect to see, but "no rows affected" ??? this must mean
>it
>> has not altered the database at all ??
>>
>> I created a 2 new users, dba and matt
>>
>> I did grant all privileges to *.* to dba@"%"
>>
>> I cannot conntect from anything host as dba.
>>
>> I then did
>>
>> grant all privileges to *.* to matt@"jaguar.no-dns.co.uk" (my laptop)
>>
>> I got the no rows affected message and I cannot connect from the machine
>> jaguar.
>>
>> something must be wrong. I used stand the pricinpals of the grant command
>> but I cannot understand why no rows are affected. It does not surprise me
>> that I cannot connect when no rows are affected.
>>
>> any other suggestions.
>>
>> Thanks for going into detail on this I am learning.
>>
>> Matt.
>>
>>
>> -Original Message-
>> From: Ho, Kam [mailto:[EMAIL PROTECTED]]
>> Sent: 09 January 2002 16:57
>> To: '[EMAIL PROTECTED]'; Ho, Kam; Gerald Clark
>> Cc: MySql List
>> Subject: RE: command mysql -u $user without typing it 
>>
>>
>> The "Query OK, 0 rows affected" indicate that the user table is updated so
>> test2 on localhost can access the mysql databases.
>>
>> Do you still get the "access is denyed for user @localhostto database
>mysql"
>> message?
>>
>>
>> -Original Message-
>> From: Matthew Darcy [mailto:[EMAIL PROTECTED]]
>> Sent: Wednesday, January 09, 2002 11:51 AM
>> To: Ho Kam; Gerald Clark
>> Cc: MySql List
>> Subject: RE: command mysql -u $user without typing it 
>>
>>
>> tired that but I am getting
>>
>> Query OK, 0 rows affected (0.00 sec)
>>
>>
>> don't understand why it is not granting to the user ?
>>
>> Matt.
>>
>>
>> -Original Message-
>> From: Ho, Kam [mailto:[EMAIL PROTECTED]]
>> Sent: 09 January 2002 16:10
>> To: '[EMAIL PROTECTED]'; Gerald Clark
>> Cc: MySql List
>> Subject: RE: command mysql -u $user without typing it 
>>
>>
>> Try this:
>>
>> mysql> grant all privileges on *.* to test2@"localhost"
>>
>> also read manual about the grant command.
>>
>> -Original Message-
>> From: Matthew Darcy [mailto:[EMAIL PROTECTED]]
>> Sent: Wednesday, January 09, 2002 11:04 AM
>> To: Gerald Clark
>> Cc: MySql List
>> Subject: RE: command mysql -u $user without typing it 
>>
>>
>> I read similar notes on this in the manual, saying that invoking mysql
>> without the username will try to take the unix username if it exists in
>the
>> database.
>>
>> I tried this with test2, it opened an mysql session no problem, but when I
>> did a connect mysql it said "access is denyed for user @localhostto
>database
>> mysql"
>>
>> to me this looks like it is trying to connect with no user instead of
>> test2@localhost
>>
>> can you explain this ?
>>
>> Thanks,
>>
>> Matt.
>>
>>
>> -Original Message-
>> From: Gerald Clark [mailto:[EMAIL PROTECTED]]
>> Sent: 09 January 2002 15:56
>> To: [EMAIL PROTECTED]
>> Cc: MySql List
>> Subject: Re: command mysql -u $user without typing it 
>>
>>
>>
>>
>> Matthew Darcy wrote:
>>
>> >
>> >I have 3 UNIX users on my server.
>> >
>> >test1, test2, and test3.
>> >
>> >I have created 3 users for mysql test1, test2, and test3 (shock horror)
>> >
>> >If I want to connect to the database as root then I understand I must do
>> >mysql -u root -p
>> >
>> >but I would like test1 2 and 3 to be able to type mysql and be either
>> logged
>> >in as their unix username, ie test2 types mysql and connects to the
>> database
>> >as t

Re: Problems with User logins.

[Tom Ray]

For the sweet love of god! Thanks Neil. After I wrote you that email, my 
head suddenly slipped out of my butt and I realized I didn't run a 
reload via the mysqladmin...funny..that seems to make everything work.

thanks for the jolt.

Neil Silvester wrote:

>Did you FLUSH PRIVILEGES?
>
>-Original Message-
>From: Tom Ray [mailto:[EMAIL PROTECTED]]
>Sent: Thursday, 10 January 2002 2:07 PM
>To: [EMAIL PROTECTED]
>Subject: Problems with User logins.
>
>
>I'm kinda new to mySQL and I set up a user (myself) and a database for 
>myself on our webserver at work. I did the "insert into" into the User 
>table and the Db table. However, when I try to log into the database I 
>get errors. I go to my mysql path on the machine and do:
>
>./mysql -u username -p database
>
>it asks me for my password and I type it in and I get :
>
>ERROR 1045: Access denied for user: 'tomr@localhost' (Using password: YES)
>
>Now I know the database works just fine, I can get into it if I log in 
>as root and manage it just fine. In the User table I gave myself no 
>privelages (as recommended) but in the Db table I gave myself full 
>control. I'm guess this is some sort of user issue, but I can't put my 
>finger on it. Anyone got any ideas?
>
>Otherwise I'll go in to work tomorrow and listen to my boss mock my 
>mysql skill all day :)
>
>
>-
>Before posting, please check:
>   http://www.mysql.com/manual.php   (the manual)
>   http://lists.mysql.com/   (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail
><[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Suggestions - FullText ??

[Jon Shoberg]

I have a simple table with A LOT of data, 2.1M rows.  this is purely a
development/test/learning endeavor.

The table structure is:
ID-> auto-increment
url   -> varchar(255)
topic -> varchar(255)
about -> tinytext


Anyone use fulltext indexing under this scenario?  The DB server is a P3-866
with 256mb, any guesses how long indexing will take?  Its pupose could
guiding search functionality on a website.

Any comments/suggestions on using FULLTEXT ?

Thanks


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Kylix Frustrated Me...!!!

[Mike]

One word  ZEOS http://www.zeoslib.org/ K1 sucks on MySQL support but using
ZEOS I have had no problems. K1 will run on 2.4.4 the earlier 2.4 had memory
issues from what we have seen. Using both K1 and K2 on 2.2 and 2.4.4 RH
boxes

Cheers

M:)

I like to set small goals that might lead into larger ones, like making
toast; this might lead into making a sandwhich and possibly my own space
program.
- Stephen Wright




-Original Message-
From: Daniel Kasak [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 08, 2002 3:35 PM
To: [EMAIL PROTECTED]
Subject: Re: Kylix Frustrated Me...!!!


Prabu Subroto <[EMAIL PROTECTED]> wrote:

>>Dear Friends,
>>
>>I am trying to develop an application with Kylix
>>ver.1, MySQL ver.11.15 distrib 3.23.41 for suse linux
>>(i686).
>>I am using MySQLConnection object to connect my MySQL
>>database server (and with so :
>>libmysqlclient.so.10.0.0).
>>
>



Kylix 1 won't work with the libmysqlclient.so.10 libraries.
You need to find an older version of mysql with a libmysqlclient.so.6 at
the latest. Then compile the client stuff (configure --disable-server or
something like that - check it our anyway).
You could complain to Borland about their shoddy support on this matter.
Or you could complain that Kylix doesn't work properly with a 2.4
kernel. Or the IDE and it's crashiness. But they wouldn't listen. I'm
waiting for Kylix 3, and if there's no decent upgrade plan from Kylix 1,
it will be the warez version...
But anyway good luck.

Dan



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Problems with User logins.

[Tom Ray]

I'm kinda new to mySQL and I set up a user (myself) and a database for 
myself on our webserver at work. I did the "insert into" into the User 
table and the Db table. However, when I try to log into the database I 
get errors. I go to my mysql path on the machine and do:

./mysql -u username -p database

it asks me for my password and I type it in and I get :

ERROR 1045: Access denied for user: 'tomr@localhost' (Using password: YES)

Now I know the database works just fine, I can get into it if I log in 
as root and manage it just fine. In the User table I gave myself no 
privelages (as recommended) but in the Db table I gave myself full 
control. I'm guess this is some sort of user issue, but I can't put my 
finger on it. Anyone got any ideas?

Otherwise I'll go in to work tomorrow and listen to my boss mock my 
mysql skill all day :)


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: [MySQL] RE: 4416 Great Growth Potential 185528 ---Hmmm

[Ashley M. Kirchner]

Mike wrote:

> Ok so where is database SQL Mysql in the message anywhere

At the end of the Disclaimer:


> DISCLAIMER: Certain statements.
>
> .. profiled. All information provided about the profiled
> companies may include information provided by outside sources, such as
> research reports, public filings or computer databases and information
> provided by management of the profiled company.

--
H | "Life is the art of drawing without an eraser." - John Gardner
  +
  Ashley M. Kirchner    .   303.442.6410 x130
  Director of Internet Operations / SysAdmin. 800.441.3873 x130
  Photo Craft Laboratories, Inc.. 3550 Arapahoe Ave, #6
  http://www.pcraft.com . .  ..   Boulder, CO 80303, U.S.A.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: 4416 Great Growth Potential 185528 ---Hmmm

[Mike]

Ok so where is database SQL Mysql in the message anywhere
Sorry there Billy but this list happens to be Very clean of Spam. I get
about 190+ messages daily from different lists and the only one I can be
assured to have little to no spam is this one. My poor filter works overtime
on the others.
In all considered I like the list.

Cheers

M;)


If Barbie is so popular, why do we have to buy her friends?
- Stephen Wright

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 10, 2002 4:08 AM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: 4416 Great Growth Potential 185528


OTC News Alerts' Last 3 Picks have gained 358%!
Here is our next Hot Pick!

Single Source Financial Services Corp. (OTCBB : SSFL)

URGENT BUY AT $ .75 PER SHARE!

SELL TARGET $4.50 PER SHARE!

SSFL is the fastest growing, publicly-traded credit card transaction
processor through its contractual agreements with Visa USA, KeyCorp
(NYSE : KEY), and Total Systems Services (NYSE : TSS).

Revenues are soaring for SSFL with the capacity to process in excess
of $120,000,000 per month in credit card transactions!

Watch for analyst "Strong Buy Recommendations" and several advisory
Newsletters picking SSFL due to rapid expansion in the Company's
business.  SSFL's processing volume on behalf of banking institutions
has more than DOUBLED in the last 4 months!

Our track record proves we offer valuable research that builds your
wealth.  We target serious gains for serious investors with a 500%
investment return on SSFL.

DISCLAIMER: Certain statements contained in this newsletter may be
forward-looking statements within the meaning of The Private Securities
Litigation Reform Act of 1995. These statements may be identified by
such terms as "expect", "believe", "may", "will", and "intend" or
similar terms. We are NOT a registered investment advisor or a broker
dealer. This is NOT an offer to buy or sell securities. No
recommendation that the securities of the companies profiled should be
purchased, sold or held by individuals or entities that learn of the
profiled companies. This is an independent electronic publication that
was paid $10,000 by a third party for the electronic dissemination of
this company information. Be advised that investments in companies
profiled are considered to be high-risk and use of the information
provided is for reading purposes only. If anyone decides to act as an
investor they are advised not to invest without the proper advisement
from an attorney or a registered financial broker, if any party decides
to participate as an investor then it will be that investor's sole risk.
Be advised that the purchase of such high-risk securities may result in
the loss of some or all of the investment. The publisher of this
newsletter makes no warranties or guarantees as to the accuracy or the
completeness of the disclosure. Investors should not rely solely on the
information presented. Rather, investors should use the information
provided in this newsletter as a starting point for doing additional
independent research on the profiled companies in order to allow the
investor to form their own opinion regarding investing in the profiled
companies. Factual statements made about the profiled companies are made
as of the date stated and are subject to change without notice.
Investing in micro-cap securities is highly speculative and carries an
extremely high degree of risk. It is possible that an investor's entire
investment may be lost or impaired due to the speculative nature of the
companies profiled. All information provided about the profiled
companies may include information provided by outside sources, such as
research reports, public filings or computer databases and information
provided by management of the profiled company.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: 4416 Great Growth Potential 185528

[Colin Faber]

Billy,

Block yahoo.com mail they don't do anything about spammers abusing their
services ;-)

I did that and found that the spam I see dropped by 99%



Billy Harvey wrote:
> 
> On Wed, 2002-01-09 at 23:08, [EMAIL PROTECTED] wrote:
> > OTC News Alerts' Last 3 Picks have gained 358%!
> > Here is our next Hot Pick!
> 
> Dear MySQL list,
> 
> I'm afraid I've had it.  I don't post often here - but I probably help
> on average one person a day by working directly with them in the
> background.  I considered it payback for the value I got from others
> over the years.
> 
> But, I can't stand the quantity of spam here.  The list needs to be made
> subscriber only, or I suspect the only people that will be left here
> soon will be the company and the spammers.
> 
> Good luck to all.  Someone let me know if the policy changes.
> 
> Thanks for a great product.
> 
> Billy
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-- 
Colin Faber
(303) 859-1491
fpsn.net, Inc.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: 4416 Great Growth Potential 185528

[Billy Harvey]

On Wed, 2002-01-09 at 23:08, [EMAIL PROTECTED] wrote:
> OTC News Alerts' Last 3 Picks have gained 358%!
> Here is our next Hot Pick!

Dear MySQL list,

I'm afraid I've had it.  I don't post often here - but I probably help
on average one person a day by working directly with them in the
background.  I considered it payback for the value I got from others
over the years.

But, I can't stand the quantity of spam here.  The list needs to be made
subscriber only, or I suspect the only people that will be left here
soon will be the company and the spammers.

Good luck to all.  Someone let me know if the policy changes.

Thanks for a great product.

Billy


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

4416 Great Growth Potential 185528

[818552stockalert]

OTC News Alerts' Last 3 Picks have gained 358%!
Here is our next Hot Pick!

Single Source Financial Services Corp. (OTCBB : SSFL)

URGENT BUY AT $ .75 PER SHARE!

SELL TARGET $4.50 PER SHARE!

SSFL is the fastest growing, publicly-traded credit card transaction
processor through its contractual agreements with Visa USA, KeyCorp 
(NYSE : KEY), and Total Systems Services (NYSE : TSS).

Revenues are soaring for SSFL with the capacity to process in excess
of $120,000,000 per month in credit card transactions!

Watch for analyst "Strong Buy Recommendations" and several advisory
Newsletters picking SSFL due to rapid expansion in the Company's 
business.  SSFL's processing volume on behalf of banking institutions
has more than DOUBLED in the last 4 months!

Our track record proves we offer valuable research that builds your
wealth.  We target serious gains for serious investors with a 500%
investment return on SSFL.

DISCLAIMER: Certain statements contained in this newsletter may be 
forward-looking statements within the meaning of The Private Securities 
Litigation Reform Act of 1995. These statements may be identified by 
such terms as "expect", "believe", "may", "will", and "intend" or 
similar terms. We are NOT a registered investment advisor or a broker 
dealer. This is NOT an offer to buy or sell securities. No 
recommendation that the securities of the companies profiled should be 
purchased, sold or held by individuals or entities that learn of the 
profiled companies. This is an independent electronic publication that 
was paid $10,000 by a third party for the electronic dissemination of 
this company information. Be advised that investments in companies 
profiled are considered to be high-risk and use of the information 
provided is for reading purposes only. If anyone decides to act as an 
investor they are advised not to invest without the proper advisement 
from an attorney or a registered financial broker, if any party decides 
to participate as an investor then it will be that investor's sole risk. 
Be advised that the purchase of such high-risk securities may result in 
the loss of some or all of the investment. The publisher of this 
newsletter makes no warranties or guarantees as to the accuracy or the 
completeness of the disclosure. Investors should not rely solely on the 
information presented. Rather, investors should use the information 
provided in this newsletter as a starting point for doing additional 
independent research on the profiled companies in order to allow the 
investor to form their own opinion regarding investing in the profiled 
companies. Factual statements made about the profiled companies are made 
as of the date stated and are subject to change without notice. 
Investing in micro-cap securities is highly speculative and carries an 
extremely high degree of risk. It is possible that an investor's entire 
investment may be lost or impaired due to the speculative nature of the 
companies profiled. All information provided about the profiled 
companies may include information provided by outside sources, such as 
research reports, public filings or computer databases and information 
provided by management of the profiled company.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Best Practice - Allowing Access to MySQL

[Vernon A Webb]

I have setup a database that GRANTs a specific user access 
to a database, which is theirs and will be their 
responsibility to manage. 

They have privileges on that database only. Using MySQLGUI 
they can log in and manage that database, but from the drop-
down menu they also see the other databases that exist on 
the server which is not such a bad thing, I guess, but I 
would prefer that they not be there at all, only their 
database. Is there a way to hide the other databases from 
them? 

Also, if people were not to use the MySQLQUI (which is a 
dog by the way, and an old dog at that) how else would they 
manage the database without giving them login privileges to 
the server?

Thanks



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: DATES & Optimization

[Paul DuBois]

At 20:22 -0600 1/9/02, Paul DuBois wrote:
>At 9:02 -0600 1/9/02, Robert L. Yelvington wrote:
>>Two quick qux for the gurus on the list!
>>
>>1. If I needed to do date calculations based upon a TIME STAMP field, what's
>>the most efficient way to accomplish this with respect to data types (field
>>types...I already understand how to use the DATE functions and am aware of
>>locale time and UNIX time trappings)?  For example, someone signs up on a
>>mailing list via mysql data base, and then I want to know how many people
>>have signed up in Jan, 2001 or March, 1999 or Jan, 2002?  OR, another
>
>Jan, 2001:
>SELECT COUNT(*) FROM tbl_name WERE MONTH(t) = 1 AND YEAR(t) = 2001;

 Oops.  WHERE

>Others are similar.
>
>>example, someone signs up and I want to calculate 4 ninety day cycles with a
>>reminder sixty days from sign up, so that if I signed up on Jan 1, 2002 then
>>on Mar 1, 2002, I'd like to send notification that their account needs
>>maintenance by April 1, 2002...kinda like a billing system.
>
>Use DATE_ADD().


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: scheduling tasks within MySQL

[Paul DuBois]

At 15:16 -0500 1/9/02, Jeffrey Lomas wrote:
>I'm fairly new to mysql.  I have need for certain actions to occur 
>within a database at a certain time every month.  What is the best 
>way to go about doing this?  Is there a way to schedule these 
>actions from within mysql?

No.  Use cron to schedule jobs.

>
>Thanks
>jeff


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: DATES & Optimization

[Paul DuBois]

At 9:02 -0600 1/9/02, Robert L. Yelvington wrote:
>Two quick qux for the gurus on the list!
>
>1. If I needed to do date calculations based upon a TIME STAMP field, what's
>the most efficient way to accomplish this with respect to data types (field
>types...I already understand how to use the DATE functions and am aware of
>locale time and UNIX time trappings)?  For example, someone signs up on a
>mailing list via mysql data base, and then I want to know how many people
>have signed up in Jan, 2001 or March, 1999 or Jan, 2002?  OR, another

Jan, 2001:
SELECT COUNT(*) FROM tbl_name WERE MONTH(t) = 1 AND YEAR(t) = 2001;
Others are similar.

>example, someone signs up and I want to calculate 4 ninety day cycles with a
>reminder sixty days from sign up, so that if I signed up on Jan 1, 2002 then
>on Mar 1, 2002, I'd like to send notification that their account needs
>maintenance by April 1, 2002...kinda like a billing system.

Use DATE_ADD().


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: DBI and MySQL update log question

[Paul DuBois]

At 17:23 -0800 1/9/02, Kyle Hayes wrote:
>I am putting the finishing touches on a completely reworked replication
>server/client scheme (we cannot use MySQL's native replication for what we
>are doing).  The whole thing is written in Perl/DBI and runs
>on Linux (x86).  We are using various versions of MySQL, but all 3.23.x
>series.  We are slowly upgrading to the latest version 3.23.47.
>
>I don't know the version of the MySQL Perl DBI libraries we use, but they
>are not too old.  We have mixed Red Hat and SuSE systems.
>
>The program works by getting the contents of the update log and using the
>Perl DBI do() function on each statement.
>
>The problem that occurs causes this (debug output):
>
>  SQL: use acc;
>
>  DBD::mysql::db do failed: You have an error in your SQL syntax near ';
>  ' at line 1 at /opt/bin/replicator_client line 199,  line 4.
>
>I have found that this works _IFF_ I strip off the trailing linefeed after
>the semicolon at the end of the statement.  I could swear that we create
>SQL statement in Perl "here docs" that end with a semicolon and several
>empty lines.  Is there a DBI guru that can tell me what is going on?  I
>really notice this especially in "USE" statements...

Don't add the semicolon.  That's for when you use the mysql client,
which you aren't doing.

The error message is complaining about the semicolon, by the way,
not the linefeed.

>
>Anyone have an idea?  I haven't found anything particularly relevent in
>the mailing list archives. 
>
>Best,
>Kyle


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: I'm having trouble with End-Of-Record markers

[Paul DuBois]

At 18:21 + 1/9/02, David Ayliffe wrote:
>I'm having trouble bulk-loading data into a table.  I have a file which
>contains:
>
>AYL01ôAYL01-1ôChloeô1979-12-1 0:0:0ôF*END*
>AYL01ôAYL01-2ôMelissaô1985-3-2 0:0:0ôF*END*
>AYL01ôAYL01-3ôDavidô1979-1-12 0:0:0ôM*END*
>COL01ôCOL01-1ôSimeonô1989-12-14 0:0:0ôF*END*
>DAV01ôDAV01-1ôMurialô1990-2-2 0:0:0ôF*END*
>SMI01ôSMI01-1ôKarlaô1989-8-8 0:0:0ôF*END*
>WIL01ôWIL01-1ôRobertô1981-8-11 0:0:0ôM*END*
>WIL01ôWIL01-2ôAndrewô1987-9-9 0:0:0ôM*END*
>WIL02ôWIL02-1ôAbi-galeô1980-1-31 0:0:0ôF*END*
>WIL02ôWIL02-2ôCharlotteô1979-7-7 0:0:0ôF*END*
>WIL02ôWIL02-3ôSusanô1982-8-17 0:0:0ôF*END*
>WIL02ôWIL02-4ôClarissaô1980-12-24 0:0:0ôF*END*
>
>(Straight cut and paste)
>
>I load the data using
>LOAD DATA LOCAL INFILE "data.txt" INTO TABLE gymnast FIELDS TERMINATED
>BY "ô" LINES TERMINATED BY "*END*";
>
>But I always get a malformed table which looks like:

It's not malformed, MySQL is doing what you told it.

Your line terminator is "*END*", which doesn't
include the linefeed or carriage return or whatever's actually at
the end of your lines.

You probably want to specify "*END*\n" or "*END*\r" ?

>
>
>mysql> select * from gymnast;
>+---++---+-+--+
>| Family_id | Gymnast_id | Name  | Date_Of_Birth   | Sex  |
>+---++---+-+--+
>| AYL01 | AYL01-1| Chloe | 1979-12-01 00:00:00 | F|
>|
>AYL01   | AYL01-2| Melissa   | 1985-03-02 00:00:00 | F|
>|
>AYL01   | AYL01-3| David | 1979-01-12 00:00:00 | M|
>|
>COL01   | COL01-1| Simeon| 1989-12-14 00:00:00 | F|
>|
>DAV01   | DAV01-1| Murial| 1990-02-02 00:00:00 | F|
>|
>SMI01   | SMI01-1| Karla | 1989-08-08 00:00:00 | F|
>|
>WIL01   | WIL01-1| Robert| 1981-08-11 00:00:00 | M|
>|
>WIL01   | WIL01-2| Andrew| 1987-09-09 00:00:00 | M|
>|
>WIL02   | WIL02-1| Abi-gale  | 1980-01-31 00:00:00 | F|
>|
>WIL02   | WIL02-2| Charlotte | 1979-07-07 00:00:00 | F|
>|
>WIL02   | WIL02-3| Susan | 1982-08-17 00:00:00 | F|
>|
>WIL02   | WIL02-4| Clarissa  | 1980-12-24 00:00:00 | F|
>+---++---+-+--+
>12 rows in set (0.00 sec)
>
>mysql>
>
>
>How can I get the data into the table so it looks and acts like it
>should.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: A difficutl query I cannot do.

[Bob Hall]

On Thu, Jan 10, 2002 at 10:24:37AM +1100, Sam Russo wrote:
> I have a delimited file sent to me with students ID subjects and when they
> do these subjects. This file (table) looks like:
>  ID   TimeSlotSubject
>  
> 215   3   Eng1 
> 648   2   Maths2
> 901   4   French
> 215   2   Maths1
> 901   1   Science2
> 648   4   Art
> 215   1   Science1
> 901   3   Eng1
> 
> I need to produce the following output (on a web page using php and 
> mysql) with a mysql query.
> 
> ID1   2   3   4
> 215   Science1Maths1  Eng1
> 648   Maths2  Art
> 901   Science2Eng1
> French

You need to cross tabulate the data.
http://www.mysql.com/articles/wizard/index.html

Bob Hall

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

DBI and MySQL update log question

[Kyle Hayes]

I am putting the finishing touches on a completely reworked replication 
server/client scheme (we cannot use MySQL's native replication for what we 
are doing).  The whole thing is written in Perl/DBI and runs 
on Linux (x86).  We are using various versions of MySQL, but all 3.23.x 
series.  We are slowly upgrading to the latest version 3.23.47.

I don't know the version of the MySQL Perl DBI libraries we use, but they 
are not too old.  We have mixed Red Hat and SuSE systems.

The program works by getting the contents of the update log and using the 
Perl DBI do() function on each statement.

The problem that occurs causes this (debug output):

 SQL: use acc;
 
 DBD::mysql::db do failed: You have an error in your SQL syntax near ';
 ' at line 1 at /opt/bin/replicator_client line 199,  line 4.

I have found that this works _IFF_ I strip off the trailing linefeed after 
the semicolon at the end of the statement.  I could swear that we create 
SQL statement in Perl "here docs" that end with a semicolon and several 
empty lines.  Is there a DBI guru that can tell me what is going on?  I 
really notice this especially in "USE" statements...

Anyone have an idea?  I haven't found anything particularly relevent in 
the mailing list archives.  

Best,
Kyle

-- 
Cut costs, Fax smart.  Use iPrint2Fax worldwide and save!
- iPrint2Fax software is FREE, no fax modem required.
- FREE iPrint2Fax to a single email address.
- iPrint2Fax to PSTN based Fax (Up to 95% Savings)
- iPrint2Fax Broadcasting: Send 100s of faxes and fax to emails
   in the time it takes to send just one!
==
FREE software download available at www.iPrint2Fax.com
==

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

I'm having trouble with End-Of-Record markers

[David Ayliffe]

I'm having trouble bulk-loading data into a table.  I have a file which
contains:

AYL01ôAYL01-1ôChloeô1979-12-1 0:0:0ôF*END* 
AYL01ôAYL01-2ôMelissaô1985-3-2 0:0:0ôF*END* 
AYL01ôAYL01-3ôDavidô1979-1-12 0:0:0ôM*END* 
COL01ôCOL01-1ôSimeonô1989-12-14 0:0:0ôF*END* 
DAV01ôDAV01-1ôMurialô1990-2-2 0:0:0ôF*END* 
SMI01ôSMI01-1ôKarlaô1989-8-8 0:0:0ôF*END* 
WIL01ôWIL01-1ôRobertô1981-8-11 0:0:0ôM*END* 
WIL01ôWIL01-2ôAndrewô1987-9-9 0:0:0ôM*END* 
WIL02ôWIL02-1ôAbi-galeô1980-1-31 0:0:0ôF*END* 
WIL02ôWIL02-2ôCharlotteô1979-7-7 0:0:0ôF*END* 
WIL02ôWIL02-3ôSusanô1982-8-17 0:0:0ôF*END* 
WIL02ôWIL02-4ôClarissaô1980-12-24 0:0:0ôF*END*

(Straight cut and paste)

I load the data using
LOAD DATA LOCAL INFILE "data.txt" INTO TABLE gymnast FIELDS TERMINATED
BY "ô" LINES TERMINATED BY "*END*";

But I always get a malformed table which looks like:


mysql> select * from gymnast;
+---++---+-+--+
| Family_id | Gymnast_id | Name  | Date_Of_Birth   | Sex  |
+---++---+-+--+
| AYL01 | AYL01-1| Chloe | 1979-12-01 00:00:00 | F|
|
AYL01   | AYL01-2| Melissa   | 1985-03-02 00:00:00 | F|
|
AYL01   | AYL01-3| David | 1979-01-12 00:00:00 | M|
|
COL01   | COL01-1| Simeon| 1989-12-14 00:00:00 | F|
|
DAV01   | DAV01-1| Murial| 1990-02-02 00:00:00 | F|
|
SMI01   | SMI01-1| Karla | 1989-08-08 00:00:00 | F|
|
WIL01   | WIL01-1| Robert| 1981-08-11 00:00:00 | M|
|
WIL01   | WIL01-2| Andrew| 1987-09-09 00:00:00 | M|
|
WIL02   | WIL02-1| Abi-gale  | 1980-01-31 00:00:00 | F|
|
WIL02   | WIL02-2| Charlotte | 1979-07-07 00:00:00 | F|
|
WIL02   | WIL02-3| Susan | 1982-08-17 00:00:00 | F|
|
WIL02   | WIL02-4| Clarissa  | 1980-12-24 00:00:00 | F|
+---++---+-+--+
12 rows in set (0.00 sec)

mysql>


How can I get the data into the table so it looks and acts like it
should.


Also when I try to EXTRACT the data from the same table using my delphi
application on the last field (Sex) instead of just getting the value
'F' I get 'F'#$D  Is this some sort of end-of-record marker?


I could really do with some guidance on this one.

Thanks
DA


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Multi-table delete/update

[Becky McElroy]

Two questions regarding multi-table operations:

1) I've got multi-table delete working with a couple of BDB tables,
where it's properly deleting from one or both tables, and all
possibilities of the delete criteria are being exersized.  After the
multi-table delete command, I can 'select *' from the first table
referenced in my multi-table delete just fine, but when I do 'select *'
for the second table, I get

ERROR 1032: can't find record in 'tblname'

If I do it again, then I get the query output just fine for the second
table from that point on.
(I'm using MySQL-Max-4.0.1-2 on Red Hat linux 6.1)
It's looking like a bug. Has anyone else encountered this?

2) Does anyone know when multi-table update will be available?

Thanks.

--
Becky McElroy




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: command mysql -u $user without typing it ????

[Quentin Bennett]

Just to check:

you are doing 

GRANT ALL PRIVILEGES ON *.* to user@localhost;
FLUSH PRIVILEGES;

previous examples have had grant all privileges _to_ *.* to user@localhost

or is _to_ a synonym for _on_?

Quentin

-Original Message-
From: Matthew Darcy [mailto:[EMAIL PROTECTED]]
Sent: Thursday, 10 January 2002 12:27 p.m.
To: James Montebello
Cc: Ho Kam; Gerald Clark; MySql List
Subject: RE: command mysql -u $user without typing it 


I was aware of the user table as a standard table.

I did a select on it and it all looked fine. Loads of Y's and % for host
Yet this user still cannot connect. The only explaination is corruption.

Although I am still looking at it.


-Original Message-
From: James Montebello [mailto:[EMAIL PROTECTED]]
Sent: 09 January 2002 21:33
To: Matthew Darcy
Cc: Ho Kam; Gerald Clark; MySql List
Subject: RE: command mysql -u $user without typing it 



The mysql user permissions are just a table in the mysql database.  Whenever
you do a GRANT, you're really just doing an INSERT or UPDATE against this
table.  You can do SELECTs, INSERTs, UPDATEs, etc. against this table (the
'user' table), just like any other table.  The only way to "corrupt user
accounts" is to corrupt this table in some way.  There is no magic here.

james montebello

On Wed, 9 Jan 2002, Matthew Darcy wrote:

> I was doing a flush privileges.
>
> but this was coming up with the same thing no rows affected which to me
was
> concerning.
>
> I have now got 1 user working but 1 user not.
>
> I am going to delete all users and start again with the users, as at the
> start I was not doing flush privileges and I was also trying put hostnames
> without DNS into the hosts field. I may have corrupted the user accounts
in
> some way.
>
> I'll keep you posted.
>
> Thanks for the help and the good explainations though.
>
> I am sure it will be a user error on my part if 1 user is working and
> another 1 not.
>
> Cheers.
>
> Matt.
>
>
> -Original Message-
> From: James Montebello [mailto:[EMAIL PROTECTED]]
> Sent: 09 January 2002 19:43
> To: Matthew Darcy
> Cc: Ho Kam; Gerald Clark; MySql List
> Subject: RE: command mysql -u $user without typing it 
>
>
>
> You must do a "FLUSH PRIVILEGES" after making any changes to the user
> permissions before they will actually take effect.  This is documented.
>
> james montebello
>
> On Wed, 9 Jan 2002, Matthew Darcy wrote:
>
> > query ok I would expect to see, but "no rows affected" ??? this must
mean
> it
> > has not altered the database at all ??
> >
> > I created a 2 new users, dba and matt
> >
> > I did grant all privileges to *.* to dba@"%"
> >
> > I cannot conntect from anything host as dba.
> >
> > I then did
> >
> > grant all privileges to *.* to matt@"jaguar.no-dns.co.uk" (my laptop)
> >
> > I got the no rows affected message and I cannot connect from the machine
> > jaguar.
> >
> > something must be wrong. I used stand the pricinpals of the grant
command
> > but I cannot understand why no rows are affected. It does not surprise
me
> > that I cannot connect when no rows are affected.
> >
> > any other suggestions.
> >
> > Thanks for going into detail on this I am learning.
> >
> > Matt.
> >
> >
> > -Original Message-
> > From: Ho, Kam [mailto:[EMAIL PROTECTED]]
> > Sent: 09 January 2002 16:57
> > To: '[EMAIL PROTECTED]'; Ho, Kam; Gerald Clark
> > Cc: MySql List
> > Subject: RE: command mysql -u $user without typing it 
> >
> >
> > The "Query OK, 0 rows affected" indicate that the user table is updated
so
> > test2 on localhost can access the mysql databases.
> >
> > Do you still get the "access is denyed for user @localhostto database
> mysql"
> > message?
> >
> >
> > -Original Message-
> > From: Matthew Darcy [mailto:[EMAIL PROTECTED]]
> > Sent: Wednesday, January 09, 2002 11:51 AM
> > To: Ho Kam; Gerald Clark
> > Cc: MySql List
> > Subject: RE: command mysql -u $user without typing it 
> >
> >
> > tired that but I am getting
> >
> > Query OK, 0 rows affected (0.00 sec)
> >
> >
> > don't understand why it is not granting to the user ?
> >
> > Matt.
> >
> >
> > -Original Message-
> > From: Ho, Kam [mailto:[EMAIL PROTECTED]]
> > Sent: 09 January 2002 16:10
> > To: '[EMAIL PROTECTED]'; Gerald Clark
> > Cc: MySql List
> > Subject: RE: command mysql -u $user without typing it 
> >
> >
> > Try this:
> >
> > mysql> grant all privileges on *.* to test2@"localhost"
> >
> > also read manual about the grant command.
> >
> > -Original Message-
> > From: Matthew Darcy [mailto:[EMAIL PROTECTED]]
> > Sent: Wednesday, January 09, 2002 11:04 AM
> > To: Gerald Clark
> > Cc: MySql List
> > Subject: RE: command mysql -u $user without typing it 
> >
> >
> > I read similar notes on this in the manual, saying that invoking mysql
> > without the username will try to take the unix username if it exists in
> the
> > database.
> >
> > I tried this with test2, it opened an mysql session no problem, but when
I
> > did a connect

Re: INSERTing into joined tables?

[ryc]

> So I designed my "files" and "people" tables without any direct
> relationship with one another, thinking to link them with the SELECT
> statement.
>
> What I completely forgot, up until this point, was that I would need to
> INSERT these records (from pre-written HTML/PHP forms), and there is no
> WHERE clause in the INSERT statement to keep everything together.  In my
> scenario, a user might add a record to "files" and wish to associate
> that record to some of the records in "people", either new or
> pre-existing (typed into an HTML text input form or something).  How
> should SQL code be arranged to "link" these records over the foreign key
> table?

Don't fret too much looking for a complicated solution, the solution is just
as easy as it sounds. You must insert into each table individually. So for
instance... someone enters a new person... you insert it into the person
table. Someone enters a new file, you insert it into the files table. Now
when someone wants to associate a file with a person, the user selects the
person he wants (so you have the people_id) and the user selects the file he
wants (so you have the file_id), and then you insert a row into the
filespeople table. There is not a single command to do this for you, it will
take 3 seperate inserts.

> but... to keep it all together... is lost on me... and then later to
> have UPDATE statements to do the same thing!  Although I suspect this
> may be easier as I can use the WHERE clause in an UPDATE statement.

Update will work in the same fashion... If person 'ryan' is working on
'file1' but is moved to 'file2'... do an update to the corresponding row in
the filespeople table (or you can delete the old row, and insert the new
one, but updating would yeild better performance).

It sounds like you have setup the correct select statement but dont entirely
understand why it works. I would suggest that you sit down with a pen and
paper and 'draw' the three tables with some mock data and see how you can
use the 'filespeople' table to join the two tables.

> If anyone has a link to a tutorial on this very concept, that would be
> greatly appreciated as well!

Devshed ( http://www.devshed.com/ ) has some good simple tutorials that can
help you understand what is going on with this SQL. There are also a lot of
other good tutorials on the site (the ones I am refering to can be found in
the mysql section I believe).

Good luck,
ryan


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: command mysql -u $user without typing it ????

[Matthew Darcy]

I was aware of the user table as a standard table.

I did a select on it and it all looked fine. Loads of Y's and % for host
Yet this user still cannot connect. The only explaination is corruption.

Although I am still looking at it.


-Original Message-
From: James Montebello [mailto:[EMAIL PROTECTED]]
Sent: 09 January 2002 21:33
To: Matthew Darcy
Cc: Ho Kam; Gerald Clark; MySql List
Subject: RE: command mysql -u $user without typing it 



The mysql user permissions are just a table in the mysql database.  Whenever
you do a GRANT, you're really just doing an INSERT or UPDATE against this
table.  You can do SELECTs, INSERTs, UPDATEs, etc. against this table (the
'user' table), just like any other table.  The only way to "corrupt user
accounts" is to corrupt this table in some way.  There is no magic here.

james montebello

On Wed, 9 Jan 2002, Matthew Darcy wrote:

> I was doing a flush privileges.
>
> but this was coming up with the same thing no rows affected which to me
was
> concerning.
>
> I have now got 1 user working but 1 user not.
>
> I am going to delete all users and start again with the users, as at the
> start I was not doing flush privileges and I was also trying put hostnames
> without DNS into the hosts field. I may have corrupted the user accounts
in
> some way.
>
> I'll keep you posted.
>
> Thanks for the help and the good explainations though.
>
> I am sure it will be a user error on my part if 1 user is working and
> another 1 not.
>
> Cheers.
>
> Matt.
>
>
> -Original Message-
> From: James Montebello [mailto:[EMAIL PROTECTED]]
> Sent: 09 January 2002 19:43
> To: Matthew Darcy
> Cc: Ho Kam; Gerald Clark; MySql List
> Subject: RE: command mysql -u $user without typing it 
>
>
>
> You must do a "FLUSH PRIVILEGES" after making any changes to the user
> permissions before they will actually take effect.  This is documented.
>
> james montebello
>
> On Wed, 9 Jan 2002, Matthew Darcy wrote:
>
> > query ok I would expect to see, but "no rows affected" ??? this must
mean
> it
> > has not altered the database at all ??
> >
> > I created a 2 new users, dba and matt
> >
> > I did grant all privileges to *.* to dba@"%"
> >
> > I cannot conntect from anything host as dba.
> >
> > I then did
> >
> > grant all privileges to *.* to matt@"jaguar.no-dns.co.uk" (my laptop)
> >
> > I got the no rows affected message and I cannot connect from the machine
> > jaguar.
> >
> > something must be wrong. I used stand the pricinpals of the grant
command
> > but I cannot understand why no rows are affected. It does not surprise
me
> > that I cannot connect when no rows are affected.
> >
> > any other suggestions.
> >
> > Thanks for going into detail on this I am learning.
> >
> > Matt.
> >
> >
> > -Original Message-
> > From: Ho, Kam [mailto:[EMAIL PROTECTED]]
> > Sent: 09 January 2002 16:57
> > To: '[EMAIL PROTECTED]'; Ho, Kam; Gerald Clark
> > Cc: MySql List
> > Subject: RE: command mysql -u $user without typing it 
> >
> >
> > The "Query OK, 0 rows affected" indicate that the user table is updated
so
> > test2 on localhost can access the mysql databases.
> >
> > Do you still get the "access is denyed for user @localhostto database
> mysql"
> > message?
> >
> >
> > -Original Message-
> > From: Matthew Darcy [mailto:[EMAIL PROTECTED]]
> > Sent: Wednesday, January 09, 2002 11:51 AM
> > To: Ho Kam; Gerald Clark
> > Cc: MySql List
> > Subject: RE: command mysql -u $user without typing it 
> >
> >
> > tired that but I am getting
> >
> > Query OK, 0 rows affected (0.00 sec)
> >
> >
> > don't understand why it is not granting to the user ?
> >
> > Matt.
> >
> >
> > -Original Message-
> > From: Ho, Kam [mailto:[EMAIL PROTECTED]]
> > Sent: 09 January 2002 16:10
> > To: '[EMAIL PROTECTED]'; Gerald Clark
> > Cc: MySql List
> > Subject: RE: command mysql -u $user without typing it 
> >
> >
> > Try this:
> >
> > mysql> grant all privileges on *.* to test2@"localhost"
> >
> > also read manual about the grant command.
> >
> > -Original Message-
> > From: Matthew Darcy [mailto:[EMAIL PROTECTED]]
> > Sent: Wednesday, January 09, 2002 11:04 AM
> > To: Gerald Clark
> > Cc: MySql List
> > Subject: RE: command mysql -u $user without typing it 
> >
> >
> > I read similar notes on this in the manual, saying that invoking mysql
> > without the username will try to take the unix username if it exists in
> the
> > database.
> >
> > I tried this with test2, it opened an mysql session no problem, but when
I
> > did a connect mysql it said "access is denyed for user @localhostto
> database
> > mysql"
> >
> > to me this looks like it is trying to connect with no user instead of
> > test2@localhost
> >
> > can you explain this ?
> >
> > Thanks,
> >
> > Matt.
> >
> >
> > -Original Message-
> > From: Gerald Clark [mailto:[EMAIL PROTECTED]]
> > Sent: 09 January 2002 15:56
> > To: [EMAIL PROTECTED]
> > Cc: MySql List
> > Subject: Re: command mysql -u $user without t

A difficutl query I cannot do.

[Sam Russo]

I have a delimited file sent to me with students ID subjects and when they
do these subjects. This file (table) looks like:
 ID TimeSlotSubject
 
215 3   Eng1 
648 2   Maths2
901 4   French
215 2   Maths1
901 1   Science2
648 4   Art
215 1   Science1
901 3   Eng1

I need to produce the following output (on a web page using php and 
mysql) with a mysql query.

ID  1   2   3   4
215 Science1Maths1  Eng1
648 Maths2  Art
901 Science2Eng1
French

any help would be appreciated.
sam russo
[EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: 2 GB limit reached

[Dan Nelson]

In the last episode (Jan 09), Dennis said:
> I just found out that this is Freebsd 4.1 running v3.22.23 of mySQL.
> Is there an issue with v3.22 related to this?

http://www.mysql.com/doc/T/a/Table_size.html says that 3.22 was limited
to 2gb or 4gb, depending on the OS.  Upgrading to 3.23 will let you use
one of the newer table types.

-- 
Dan Nelson
[EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

accumulating data in a compressed table

[Gerd v. Egidy]

Hi,

I have two tables, one with a smaller working set of data and one with all 
the historic data; both have exactly the same layout.

I want to run a script about every month which will select the oldest data 
from the working set and move it into a temporary table. Than I want to add 
all data from the temp table to the compressed historic table.

I tried it like this:

[root@intranator statistic]# myisampack -# -v -j historic historic 
working_set_tmp
myisampack: Can't create/write to file 'historic.MYI' (Errcode: 17)
Aborted: historic is not compressed

User time 0.01, System time 0.00
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 25, Physical pagefaults 165, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 0, Involuntary context switches 0

My plan was to merge the temp table and the historic table - but it looks 
like it doesn't work like this because the table is already there (strace 
shows a file exists error).

If I try it with --force the index of the historic database gets deleted.

Is there any way to accumulate historic data in a compressed table without 
creating another temporary table (wich will this time be at least as big as 
the compressed table)? I want to omit copying the already compressed data 
around.
Creating different tables is also a no-go because mysql doesn't have a union 
command (at least not the stable version) to glue the results back together.

Thank you for your help.

Regards,

Gerd

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: MySQL on Linux 2.4 question

[Heikki Tuuri]

Walt,

a possible reason is that fsync is much faster in Linux-2.4 than in 2.2.

Check that the combined size of your log files is 50 % - 100% of the buffer
pool size. Small log files cause more disk i/o and more fsyncs.

The kernel 2.4.4-SMP-64GB has been very stable on our 2-way computer.
Somewhere in about version 2.4.10 Linus changed the virtual memory. I am not
sure how stable kernels 2.4.10 - .17 are, but at least some people are
running them with success.

2.5.1 seems to be unstable.

Regards,

Heikki

.
Anyone else out there been playing with the new Linux 2.4 kernel?

I just upgraded a test server from 2.2 to 2.4 and reran some of my InnoDB
tests. The results were dramatic; updating a 600,000 row table went from 21
minutes and change to 6 minutes and change.

Haven't tried 2.4 and MyISAM tables yet; on 2.2 the same update ran in about
8 minutes.

This is obviously a big improvement, if my tests are valid. It's SUCH a big
improvement that I'm wondering if I messed something up.

Any comments/statement of support/snide remarks will all be appreciated.

Thanks,

--Walt Weaver
  Bozeman, Montana




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: InnoDB : Lock wait timeout exceeded; Try restarting transaction

[John Kemp]

Heikki,

Thanks very muich for the explanation. That's an interesting question 
for the connection modules in Apache/PHP/DBI etc. Perhaps this is a 
problem with the way the connections are opened by those programs. I 
hadn't thought of that, so it would definitely be a good thing to test 
before we say the bug is in Mysql. I already looked for LOCK TABLES in 
all of our code, and it's not there - anywhere.

Thanks for your help in clearing this up,

Cheers,

John

Heikki Tuuri wrote:

> Hi!
> 
> It is a bug if the sleeping connection is in the auto-commit mode. But we
> need more information of the problem. If you encounter it, please send the
> exact sequence of SQL commands which leads to the problem.
> 
> You may also test
> 
> SET AUTOCOMMIT=1
> 
> explicitly in your program.
> 
> Note that LOCK TABLES switches auto-commit off until the table locks have
> been released.
> 
> Regards,
> 
> Heikki
> 
> ...
> Walt / Heikki
> 
> Yes, I agree - with autocommit on, you do not need to supply COMMIT, and
> yes, that's the same as other RDBMSs. What I'm pointing out though is
> that I thought Heikki was suggesting that we need to supply the
> BEGIN/COMMIT for a single command even if autocommit was on, in order to
> not get the error "Lock wait timeout exceeded; Try restarting
> transaction". If that's the case, that would indicate that Mysql
> behaviour would be different to what I would expect.
> 
> With auto-commit ON, and no explicit "LOCK TABLES' mentioned, I would
> not expect to get any error about a lock wait timeout unless the same
> ROW were being selected as were being updated, inserted or deleted. With
> a sleeping connection being the second potential row-locker, I would not
> expect to see this error ever.
> 
> What I'm saying is that this looks like a bug. Sam mentioned that he
> only had one other connection when he got this error, and that this
> connection was a sleeping, persistent connection. The behaviour I saw
> was the same. A sleeping connection was supposedly locking a row needed
> (for a delete in my case). I don't think a sleeping connection (ie. one
> not actually running a query) should ever lock out a running query.
> 
> I hope that clears up the confusion.
> 
> John
> 
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> .
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: 2 GB limit reached

[Dennis]

I just found out that this is Freebsd 4.1 running v3.22.23 of mySQL. Is 
there an issue with v3.22 related to this?

Dennis


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: InnoDB : Lock wait timeout exceeded; Try restarting transaction

[Heikki Tuuri]

Hi!

It is a bug if the sleeping connection is in the auto-commit mode. But we
need more information of the problem. If you encounter it, please send the
exact sequence of SQL commands which leads to the problem.

You may also test

SET AUTOCOMMIT=1

explicitly in your program.

Note that LOCK TABLES switches auto-commit off until the table locks have
been released.

Regards,

Heikki

...
Walt / Heikki

Yes, I agree - with autocommit on, you do not need to supply COMMIT, and
yes, that's the same as other RDBMSs. What I'm pointing out though is
that I thought Heikki was suggesting that we need to supply the
BEGIN/COMMIT for a single command even if autocommit was on, in order to
not get the error "Lock wait timeout exceeded; Try restarting
transaction". If that's the case, that would indicate that Mysql
behaviour would be different to what I would expect.

With auto-commit ON, and no explicit "LOCK TABLES' mentioned, I would
not expect to get any error about a lock wait timeout unless the same
ROW were being selected as were being updated, inserted or deleted. With
a sleeping connection being the second potential row-locker, I would not
expect to see this error ever.

What I'm saying is that this looks like a bug. Sam mentioned that he
only had one other connection when he got this error, and that this
connection was a sleeping, persistent connection. The behaviour I saw
was the same. A sleeping connection was supposedly locking a row needed
(for a delete in my case). I don't think a sleeping connection (ie. one
not actually running a query) should ever lock out a running query.

I hope that clears up the confusion.

John




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: MySQL on Linux 2.4 question

[John Kemp]

Walt,

Yup, we use Innodb with 3.23.46 on Linux 2.4.2. I can't tell you whether 
things are better than they were on 2.2 kernels but we're updating 
tables just about that quickly I'd say. Largest table we have is a log 
table which has 50 million rows in it so far. Thanks to Innodb row 
locking, we've significantly improved insert versus select query 
performance.

I've heard (I think I read this in a magazine) that prior to 2.4.4 
kernel, there are some virtual memory issues with Mysql, so I'd be 
interesetd to know what kernel version you have... memory management 
changed a lot between 2.2 and 2.4, so that would probably account for 
the changes you're seeing.

JOhn

Weaver, Walt wrote:

> Anyone else out there been playing with the new Linux 2.4 kernel? 
> 
> I just upgraded a test server from 2.2 to 2.4 and reran some of my InnoDB
> tests. The results were dramatic; updating a 600,000 row table went from 21
> minutes and change to 6 minutes and change.
> 
> Haven't tried 2.4 and MyISAM tables yet; on 2.2 the same update ran in about
> 8 minutes.
> 
> This is obviously a big improvement, if my tests are valid. It's SUCH a big
> improvement that I'm wondering if I messed something up.
> 
> Any comments/statement of support/snide remarks will all be appreciated.
> 
> Thanks,
> 
> --Walt Weaver
>   Bozeman, Montana
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> .
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: how do I search for a substring of a field? sql

[Colin Faber]

Dan, you can use LIKE RLIKE and MATCH() AGAINST()

If that's what you're asking.


Dan wrote:
> 
> Anyone know how to seach for a subsctring instead of the whole field? sql
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-- 
Colin Faber
(303) 859-1491
fpsn.net, Inc.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: MySQL on Linux 2.4 question

[Nally, Tyler G.]

Amazing!  I've not had a chance to upgrade a server to
2.4 yet, though I've long imagined that the results would
be similar.

I imagine the biggest performance boost you received is  
due to the built-in multi-threading that the 2.4 kernel 
enjoys which is currently lacking in the 2.2 kernel.  

Linux is becoming more robust all the time along with a 
pretty hard kernel.  It's come a long way in a 
short time.  Linux is developing along a more solid path
than Windoze.  Even the big boys are turning to open
source to handle the high-traffic/high-availability
jobs.  Yahoo saved somewhere around 17 million dollars
by switching needed infrastructure over to linux/open 
source.  I just read today that MySQL is their big
database that handles 800 transactions a second for their
stock quotes and financial handlings.

In the year that M$ wants to rent the OS to you, linux
will only continue to grow in it's usage as the desktop
gets more friendly and the applications are developed 
to make people want to use linux instead of windoze.

Tyler

> -Original Message-
> From: Weaver, Walt [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 09, 2002 5:18 PM
> To: [EMAIL PROTECTED]
> Subject: MySQL on Linux 2.4 question
> 
> 
> Anyone else out there been playing with the new Linux 2.4 kernel? 
> 
> I just upgraded a test server from 2.2 to 2.4 and reran some 
> of my InnoDB
> tests. The results were dramatic; updating a 600,000 row 
> table went from 21
> minutes and change to 6 minutes and change.
> 
> Haven't tried 2.4 and MyISAM tables yet; on 2.2 the same 
> update ran in about
> 8 minutes.
> 
> This is obviously a big improvement, if my tests are valid. 
> It's SUCH a big
> improvement that I'm wondering if I messed something up.
> 
> Any comments/statement of support/snide remarks will all be 
> appreciated.
> 
> Thanks,
> 
> --Walt Weaver
>   Bozeman, Montana
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: MySQL on Linux 2.4 question

[Joel Wickard]

600,000  row table?
what are you storing on that bad boy?


- Original Message -
From: "Weaver, Walt" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, January 09, 2002 2:17 PM
Subject: MySQL on Linux 2.4 question


> Anyone else out there been playing with the new Linux 2.4 kernel?
>
> I just upgraded a test server from 2.2 to 2.4 and reran some of my InnoDB
> tests. The results were dramatic; updating a 600,000 row table went from
21
> minutes and change to 6 minutes and change.
>
> Haven't tried 2.4 and MyISAM tables yet; on 2.2 the same update ran in
about
> 8 minutes.
>
> This is obviously a big improvement, if my tests are valid. It's SUCH a
big
> improvement that I'm wondering if I messed something up.
>
> Any comments/statement of support/snide remarks will all be appreciated.
>
> Thanks,
>
> --Walt Weaver
>   Bozeman, Montana
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: how do I search for a substring of a field? sql

[Dan]

Anyone know how to seach for a subsctring instead of the whole field? sql

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

MySQL on Linux 2.4 question

[Weaver, Walt]

Anyone else out there been playing with the new Linux 2.4 kernel? 

I just upgraded a test server from 2.2 to 2.4 and reran some of my InnoDB
tests. The results were dramatic; updating a 600,000 row table went from 21
minutes and change to 6 minutes and change.

Haven't tried 2.4 and MyISAM tables yet; on 2.2 the same update ran in about
8 minutes.

This is obviously a big improvement, if my tests are valid. It's SUCH a big
improvement that I'm wondering if I messed something up.

Any comments/statement of support/snide remarks will all be appreciated.

Thanks,

--Walt Weaver
  Bozeman, Montana

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: InnoDB : Lock wait timeout exceeded; Try restarting transacti on

[John Kemp]

Walt / Heikki

Yes, I agree - with autocommit on, you do not need to supply COMMIT, and 
yes, that's the same as other RDBMSs. What I'm pointing out though is 
that I thought Heikki was suggesting that we need to supply the 
BEGIN/COMMIT for a single command even if autocommit was on, in order to 
not get the error "Lock wait timeout exceeded; Try restarting 
transaction". If that's the case, that would indicate that Mysql 
behaviour would be different to what I would expect.

With auto-commit ON, and no explicit "LOCK TABLES' mentioned, I would 
not expect to get any error about a lock wait timeout unless the same 
ROW were being selected as were being updated, inserted or deleted. With 
a sleeping connection being the second potential row-locker, I would not 
expect to see this error ever.

What I'm saying is that this looks like a bug. Sam mentioned that he 
only had one other connection when he got this error, and that this 
connection was a sleeping, persistent connection. The behaviour I saw 
was the same. A sleeping connection was supposedly locking a row needed 
(for a delete in my case). I don't think a sleeping connection (ie. one 
not actually running a query) should ever lock out a running query.

I hope that clears up the confusion.

John

Weaver, Walt wrote:

> John wrote:
> 
>>That's not something that is generally necessary with other RDBMSs.
>>
> 
> I disagree. I would imagine all transaction-oriented RDBMS's work this way.
> Oracle certainly does. You need to do an explicit commit or rollback to
> release the lock. (or close the cursor, etc.)
> 
> Unless, of course, autocommit is on.
> 
> --Walt Weaver
>   Bozeman, Montana
> 
> -Original Message-
> From: John Kemp [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 09, 2002 1:40 PM
> To: Heikki Tuuri
> Cc: [EMAIL PROTECTED]
> Subject: Re: InnoDB : Lock wait timeout exceeded; Try restarting
> transaction
> 
> 
> Heikki,
> 
> Hmm. That's interesting. So if you do a single command, say
> 
> INSERT INTO Table1 (X, Y, Z) VALUES ( A, B, C) ;
> 
> You actually need to write (I'm not sure of the exact transactional 
> syntax for Mysql) -
> 
> BEGIN ; --begin a transaction
> INSERT INTO Table1 (X, Y, Z) VALUES ( A, B, C) ;
> COMMIT ; -- end a transaction
> 
> Even for a single statement with a single row update/insert?
> 
> That's not something that is generally necessary with other RDBMSs.
> 
> Can you confirm that?
> 
> Thanks,
> 
> John
> 
> Heikki Tuuri wrote:
> 
> 
>>Hi!
>>
>>Looks like your are not committing your transactions. Every UPDATE and
>>INSERT automatically sets row level locks, which are only removed when you
>>do a COMMIT or ROLLBACK.
>>
>>Or you have set innodb_lock_wait_timeout too small in my.cnf.
>>
>>InnoDB does not set table level locks. Only LOCK TABLES sets table level
>>locks.
>>
>>You can use innodb_lock_monitor to make the mysqld server program print
>>(somewhat cryptic) information about who has locks and where. See the
>>
> InnoDB
> 
>>manual at http://www.innodb.com/ibman.html
>>
>>Best regards,
>>
>>Heikki Tuuri
>>Innobase Oy
>>---
>>InnoDB - transactions, row level locking, and foreign key support for
>>
> MySQL
> 
>>See http://www.innodb.com, download MySQL-Max from http://www.mysql.com
>>
>>..
>>
>>Hi SAm,
>>
>>I actually had a similar problem myself, but was unable to prove it was
>>the persistent connection itself causing this. I'm wondering if this
>>means that INNODB thinks that a connection that is now 'sleeping' (ie.
>>where a connection was created, used, but is now unused but still open)
>>might be locking the whole table erroneously for some reason? Which
>>version are you using? I could not figure out why Innodb would think the
>>table was locked, other than if someone specifically said 'LOCK TABLE'
>>in a query, which wasn't the case.
>>
>>Any thoughts?
>>
>>John
>>
>>
>>
>>Sam Lam wrote:
>>
>>
>>
>>>I recently switched to InnoDB & persistent connections from PHP.
>>>
>>>Lately I've been getting these errors "Lock wait timeout exceeded; Try
>>>restarting transaction" on an UPDATE on table. The system is in
>>>development so there is at most one other user ( a back end Perl script).
>>>
>>>When I switched PHP back to non-persistent connections I stopped getting
>>>that error.
>>>
>>>How does one use persistent PHP connections & InnoDB to avoid this error ?
>>>
>>>
>>>
>>
>>
>>
>>-
>>Before posting, please check:
>>   http://www.mysql.com/manual.php   (the manual)
>>   http://lists.mysql.com/   (the list archive)
>>
>>To request this thread, e-mail <[EMAIL PROTECTED]>
>>To unsubscribe, e-mail
>>
> <[EMAIL PROTECTED]>
> 
>>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>>
>>.
>>
>>
>>
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (t

Re: primary key that doesn't autoincrement

[Dan Nelson]

In the last episode (Jan 09), D Woods said:
> I'm a novice at setting up a mysql database and don't know the answer to
> this. I usually set up my primary keys as autoincrementing ID fields. Now I
> need to have a primary key that isn't an autoincrementing field as I want to
> store the CFTOKEN and use it as the primary key. Can I not have a
> non-autoincrementing primary key in mysql?

Sure.  Just leave off the AUTO_INCREMENT when you create the field.

-- 
Dan Nelson
[EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: primary key that doesn't autoincrement

[Rick Emery]

yes, you can

-Original Message-
From: D Woods [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 09, 2002 4:02 PM
To: [EMAIL PROTECTED]
Subject: primary key that doesn't autoincrement


I'm a novice at setting up a mysql database and don't know the answer to
this. I usually set up my primary keys as autoincrementing ID fields. Now I
need to have a primary key that isn't an autoincrementing field as I want to
store the CFTOKEN and use it as the primary key. Can I not have a
non-autoincrementing primary key in mysql?

Thanks


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

primary key that doesn't autoincrement

[D Woods]

I'm a novice at setting up a mysql database and don't know the answer to
this. I usually set up my primary keys as autoincrementing ID fields. Now I
need to have a primary key that isn't an autoincrementing field as I want to
store the CFTOKEN and use it as the primary key. Can I not have a
non-autoincrementing primary key in mysql?

Thanks


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: command mysql -u $user without typing it ????

[James Montebello]

The mysql user permissions are just a table in the mysql database.  Whenever
you do a GRANT, you're really just doing an INSERT or UPDATE against this 
table.  You can do SELECTs, INSERTs, UPDATEs, etc. against this table (the
'user' table), just like any other table.  The only way to "corrupt user
accounts" is to corrupt this table in some way.  There is no magic here.

james montebello

On Wed, 9 Jan 2002, Matthew Darcy wrote:

> I was doing a flush privileges.
> 
> but this was coming up with the same thing no rows affected which to me was
> concerning.
> 
> I have now got 1 user working but 1 user not.
> 
> I am going to delete all users and start again with the users, as at the
> start I was not doing flush privileges and I was also trying put hostnames
> without DNS into the hosts field. I may have corrupted the user accounts in
> some way.
> 
> I'll keep you posted.
> 
> Thanks for the help and the good explainations though.
> 
> I am sure it will be a user error on my part if 1 user is working and
> another 1 not.
> 
> Cheers.
> 
> Matt.
> 
> 
> -Original Message-
> From: James Montebello [mailto:[EMAIL PROTECTED]]
> Sent: 09 January 2002 19:43
> To: Matthew Darcy
> Cc: Ho Kam; Gerald Clark; MySql List
> Subject: RE: command mysql -u $user without typing it 
> 
> 
> 
> You must do a "FLUSH PRIVILEGES" after making any changes to the user
> permissions before they will actually take effect.  This is documented.
> 
> james montebello
> 
> On Wed, 9 Jan 2002, Matthew Darcy wrote:
> 
> > query ok I would expect to see, but "no rows affected" ??? this must mean
> it
> > has not altered the database at all ??
> >
> > I created a 2 new users, dba and matt
> >
> > I did grant all privileges to *.* to dba@"%"
> >
> > I cannot conntect from anything host as dba.
> >
> > I then did
> >
> > grant all privileges to *.* to matt@"jaguar.no-dns.co.uk" (my laptop)
> >
> > I got the no rows affected message and I cannot connect from the machine
> > jaguar.
> >
> > something must be wrong. I used stand the pricinpals of the grant command
> > but I cannot understand why no rows are affected. It does not surprise me
> > that I cannot connect when no rows are affected.
> >
> > any other suggestions.
> >
> > Thanks for going into detail on this I am learning.
> >
> > Matt.
> >
> >
> > -Original Message-
> > From: Ho, Kam [mailto:[EMAIL PROTECTED]]
> > Sent: 09 January 2002 16:57
> > To: '[EMAIL PROTECTED]'; Ho, Kam; Gerald Clark
> > Cc: MySql List
> > Subject: RE: command mysql -u $user without typing it 
> >
> >
> > The "Query OK, 0 rows affected" indicate that the user table is updated so
> > test2 on localhost can access the mysql databases.
> >
> > Do you still get the "access is denyed for user @localhostto database
> mysql"
> > message?
> >
> >
> > -Original Message-
> > From: Matthew Darcy [mailto:[EMAIL PROTECTED]]
> > Sent: Wednesday, January 09, 2002 11:51 AM
> > To: Ho Kam; Gerald Clark
> > Cc: MySql List
> > Subject: RE: command mysql -u $user without typing it 
> >
> >
> > tired that but I am getting
> >
> > Query OK, 0 rows affected (0.00 sec)
> >
> >
> > don't understand why it is not granting to the user ?
> >
> > Matt.
> >
> >
> > -Original Message-
> > From: Ho, Kam [mailto:[EMAIL PROTECTED]]
> > Sent: 09 January 2002 16:10
> > To: '[EMAIL PROTECTED]'; Gerald Clark
> > Cc: MySql List
> > Subject: RE: command mysql -u $user without typing it 
> >
> >
> > Try this:
> >
> > mysql> grant all privileges on *.* to test2@"localhost"
> >
> > also read manual about the grant command.
> >
> > -Original Message-
> > From: Matthew Darcy [mailto:[EMAIL PROTECTED]]
> > Sent: Wednesday, January 09, 2002 11:04 AM
> > To: Gerald Clark
> > Cc: MySql List
> > Subject: RE: command mysql -u $user without typing it 
> >
> >
> > I read similar notes on this in the manual, saying that invoking mysql
> > without the username will try to take the unix username if it exists in
> the
> > database.
> >
> > I tried this with test2, it opened an mysql session no problem, but when I
> > did a connect mysql it said "access is denyed for user @localhostto
> database
> > mysql"
> >
> > to me this looks like it is trying to connect with no user instead of
> > test2@localhost
> >
> > can you explain this ?
> >
> > Thanks,
> >
> > Matt.
> >
> >
> > -Original Message-
> > From: Gerald Clark [mailto:[EMAIL PROTECTED]]
> > Sent: 09 January 2002 15:56
> > To: [EMAIL PROTECTED]
> > Cc: MySql List
> > Subject: Re: command mysql -u $user without typing it 
> >
> >
> >
> >
> > Matthew Darcy wrote:
> >
> > >
> > >I have 3 UNIX users on my server.
> > >
> > >test1, test2, and test3.
> > >
> > >I have created 3 users for mysql test1, test2, and test3 (shock horror)
> > >
> > >If I want to connect to the database as root then I understand I must do
> > >mysql -u root -p
> > >
> > >but I would like test1 2 and 3 to be able to type mysql and be either
> > logged
> > >in as their un

RE: Complicated Query?

[Roger Baklund]

* Jerry Rehak
> I have a table with the columns names and id.  I want to be able
> to find all
> names with an id of '03' that do not have other records with id values of
> '10','20' or '37'.
>
> Is this even possible to do?
>
> name   id
> a  03
> a  11
> a  12
> a  13 I want 'a' because it has a 03 and not a 10, a 20, OR 37
> b  03
> b  10 I don't want 'b' because it has a 10
> c  04
> c  11
> c  20 I don't want 'c' because it doesn't have a 03
> d  03 I want 'd' because it has a 03 and no other records

This can be done with a simple LEFT JOIN:

SELECT t1.*
  FROM table AS t1
  LEFT JOIN table AS t2 ON
t2.name=t1.name AND
t2.id IN ('10','20','37')
  WHERE t1.id='03' AND t2.id IS NULL;

We select the rows we want from t1, left join with the rows we don't want
(t2), and put as a condition in the where clause that we only want rows
where t2 was not found.

--
Roger


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: InnoDB : Lock wait timeout exceeded; Try restarting transaction

[Weaver, Walt]

John wrote:
> That's not something that is generally necessary with other RDBMSs.

I disagree. I would imagine all transaction-oriented RDBMS's work this way.
Oracle certainly does. You need to do an explicit commit or rollback to
release the lock. (or close the cursor, etc.)

Unless, of course, autocommit is on.

--Walt Weaver
  Bozeman, Montana

-Original Message-
From: John Kemp [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 09, 2002 1:40 PM
To: Heikki Tuuri
Cc: [EMAIL PROTECTED]
Subject: Re: InnoDB : Lock wait timeout exceeded; Try restarting
transaction


Heikki,

Hmm. That's interesting. So if you do a single command, say

INSERT INTO Table1 (X, Y, Z) VALUES ( A, B, C) ;

You actually need to write (I'm not sure of the exact transactional 
syntax for Mysql) -

BEGIN ; --begin a transaction
INSERT INTO Table1 (X, Y, Z) VALUES ( A, B, C) ;
COMMIT ; -- end a transaction

Even for a single statement with a single row update/insert?

That's not something that is generally necessary with other RDBMSs.

Can you confirm that?

Thanks,

John

Heikki Tuuri wrote:

> Hi!
> 
> Looks like your are not committing your transactions. Every UPDATE and
> INSERT automatically sets row level locks, which are only removed when you
> do a COMMIT or ROLLBACK.
> 
> Or you have set innodb_lock_wait_timeout too small in my.cnf.
> 
> InnoDB does not set table level locks. Only LOCK TABLES sets table level
> locks.
> 
> You can use innodb_lock_monitor to make the mysqld server program print
> (somewhat cryptic) information about who has locks and where. See the
InnoDB
> manual at http://www.innodb.com/ibman.html
> 
> Best regards,
> 
> Heikki Tuuri
> Innobase Oy
> ---
> InnoDB - transactions, row level locking, and foreign key support for
MySQL
> See http://www.innodb.com, download MySQL-Max from http://www.mysql.com
> 
> ..
> 
> Hi SAm,
> 
> I actually had a similar problem myself, but was unable to prove it was
> the persistent connection itself causing this. I'm wondering if this
> means that INNODB thinks that a connection that is now 'sleeping' (ie.
> where a connection was created, used, but is now unused but still open)
> might be locking the whole table erroneously for some reason? Which
> version are you using? I could not figure out why Innodb would think the
> table was locked, other than if someone specifically said 'LOCK TABLE'
> in a query, which wasn't the case.
> 
> Any thoughts?
> 
> John
> 
> 
> 
> Sam Lam wrote:
> 
> 
>>I recently switched to InnoDB & persistent connections from PHP.
>>
>>Lately I've been getting these errors "Lock wait timeout exceeded; Try
>>restarting transaction" on an UPDATE on table. The system is in
>>development so there is at most one other user ( a back end Perl script).
>>
>>When I switched PHP back to non-persistent connections I stopped getting
>>that error.
>>
>>How does one use persistent PHP connections & InnoDB to avoid this error ?
>>
>>
> 
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> .
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: How Query and Fetch work?

[Nally, Tyler G.]

Not knowing the exact internals of how it works...

Assuming that it works much the same way other RDB's
work in their native GL's (generation languages), the
common practice with a relational database is to define/declare
a "cursor" with the SQL statement executed against the database.
After the cursor is defined/declared, then the next operation
that is performed is a "fetch" where information that is available
by the "cursor" is actually pulled from the database and 
somehow used.  Specifically *how* I don't know.   

I remember years ago when doing COBOL programming, when
dealing with a random access indexed file, what we had to
do to get to the data was to...

   open indexedfile-name
   start indexedfile-name at a particular key value
 if error (the start was bad and you're record pointer was
   now at the end of the indexedfile)
go back and get a new key value or end program
 if good (read operations can be performed to retrieve a
  record)
continue

   looping paragraph
 (
  read recordname next
at end
  close indexfile-name
  
  
  
 )

This is pretty much the same operation as is done with a 
RDB.  The open is the database/host/user connection.  The
start is the definition/declaration of the cursor, and the
read next's are the fetches that return values to be 
processed.

> -Original Message-
> From: Stephen Abshire [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 09, 2002 12:41 PM
> To: [EMAIL PROTECTED]
> Subject: Re: How Query and Fetch work?
> 
> 
> I like the explanation using the automobile and key. Very 
> good :-) Yet at 
> the same time if I understand what effect using the different 
> grades of fuel 
> in my car will have on its performance, I can optimize how 
> the car operates. 
> Much like the original question, if I know when MySQL caches 
> result sets and 
> when it is forced to hit the database again I can optimize 
> the performance 
> of my application.
> 
> Personally, I would like to know the answer to this question 
> myself. Anyone 
> know the internals?
> 
> 
> Original Message Follows
> From: Dibo Chen <[EMAIL PROTECTED]>
> To: Alex Shi <[EMAIL PROTECTED]>
> CC: [EMAIL PROTECTED]
> Subject: Re: How Query and Fetch work?
> Date: Wed, 09 Jan 2002 10:45:07 -0600
> 
> When you are told to fill in fuel and turn the key to drive, I suppose
> you don't care how the fuel runs the car. Do the same things 
> in any lib
> you use, pushing the "buttons" in told order would  work. 
> Well, you may
> dig deeper if you like since the code is available.
> 
> Alex Shi wrote:
>  >
>  > Yesterday I posted a question yet got response. The question
>  > is regarding to how Query works. Now I repost it in a more
>  > specific way.
>  >
>  > I am just wondering how MySQL API functions work. Let's look
>  > at following two functions:
>  >
>  > 1. mysql_query(),
>  > 2. mysql_fetch_array()
>  >
>  > To my understanding, mysql_query() will definately to its job with
>  > MySQL server. But how about the latter? Does it just fetch data
>  > from client/local buffer, which is previously put in by 
> mysql_query(),
>  > or still has to goto server side to fetch data?
>  >
>  > Alex
>  >
>  > 
> -
>  > Before posting, please check:
>  >http://www.mysql.com/manual.php   (the manual)
>  >http://lists.mysql.com/   (the list archive)
>  >
>  > To request this thread, e-mail <[EMAIL PROTECTED]>
>  > To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
>  > Trouble unsubscribing? Try: 
http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail 
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


_
MSN Photos is the easiest way to share and print your photos: 
http://photos.msn.com/support/worldwide.aspx


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: InnoDB : Lock wait timeout exceeded; Try restarting transaction

[Heikki Tuuri]

John,

MySQL runs by default in the auto-commit mode. In the auto-commit mode you
do not need to write the COMMIT after each statement.

But if you do SET AUTOCOMMIT=0, then you have to call commit explicitly.

Best regards,

Heikki Tuuri
Innobase Oy
---
Order technical MySQL/InnoDB support at https://order.mysql.com/
See http://www.innodb.com for the online manual and latest news on InnoDB


-Original Message-
From: John Kemp <[EMAIL PROTECTED]>
To: Heikki Tuuri <[EMAIL PROTECTED]>
Cc: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
Date: Wednesday, January 09, 2002 10:38 PM
Subject: Re: InnoDB : Lock wait timeout exceeded; Try restarting transaction


>Heikki,
>
>Hmm. That's interesting. So if you do a single command, say
>
>INSERT INTO Table1 (X, Y, Z) VALUES ( A, B, C) ;
>
>You actually need to write (I'm not sure of the exact transactional
>syntax for Mysql) -
>
>BEGIN ; --begin a transaction
>INSERT INTO Table1 (X, Y, Z) VALUES ( A, B, C) ;
>COMMIT ; -- end a transaction
>
>Even for a single statement with a single row update/insert?
>
>That's not something that is generally necessary with other RDBMSs.
>
>Can you confirm that?
>
>Thanks,
>
>John
>
>Heikki Tuuri wrote:
>
>> Hi!
>>
>> Looks like your are not committing your transactions. Every UPDATE and
>> INSERT automatically sets row level locks, which are only removed when
you
>> do a COMMIT or ROLLBACK.
>>
>> Or you have set innodb_lock_wait_timeout too small in my.cnf.
>>
>> InnoDB does not set table level locks. Only LOCK TABLES sets table level
>> locks.
>>
>> You can use innodb_lock_monitor to make the mysqld server program print
>> (somewhat cryptic) information about who has locks and where. See the
InnoDB
>> manual at http://www.innodb.com/ibman.html
>>
>> Best regards,
>>
>> Heikki Tuuri
>> Innobase Oy
>> ---
>> InnoDB - transactions, row level locking, and foreign key support for
MySQL
>> See http://www.innodb.com, download MySQL-Max from http://www.mysql.com
>>
>> ..
>>
>> Hi SAm,
>>
>> I actually had a similar problem myself, but was unable to prove it was
>> the persistent connection itself causing this. I'm wondering if this
>> means that INNODB thinks that a connection that is now 'sleeping' (ie.
>> where a connection was created, used, but is now unused but still open)
>> might be locking the whole table erroneously for some reason? Which
>> version are you using? I could not figure out why Innodb would think the
>> table was locked, other than if someone specifically said 'LOCK TABLE'
>> in a query, which wasn't the case.
>>
>> Any thoughts?
>>
>> John
>>
>>
>>
>> Sam Lam wrote:
>>
>>
>>>I recently switched to InnoDB & persistent connections from PHP.
>>>
>>>Lately I've been getting these errors "Lock wait timeout exceeded; Try
>>>restarting transaction" on an UPDATE on table. The system is in
>>>development so there is at most one other user ( a back end Perl script).
>>>
>>>When I switched PHP back to non-persistent connections I stopped getting
>>>that error.
>>>
>>>How does one use persistent PHP connections & InnoDB to avoid this error
?
>>>
>>>
>>
>>
>>
>>
>> -
>> Before posting, please check:
>>http://www.mysql.com/manual.php   (the manual)
>>http://lists.mysql.com/   (the list archive)
>>
>> To request this thread, e-mail <[EMAIL PROTECTED]>
>> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
>> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>>
>> .
>>
>>
>



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: INSERTing into joined tables?

[Erik Price]

On Wednesday, January 9, 2002, at 03:06  PM, Rick Emery wrote:

> First, you are describing a "one-to-many" relationship, bceasue one 
> "file"
> record can be referenced by many "people" records.  If this is the 
> case, you
> may wish to re-design your tablse such that a "people record" contains a
> "file_id" field.  You can then do away with the "filespeople" table
> altogether.  Normalization is a good thing; but not when it is at the
> detriment of good design including how one processes it.

In this one example, it's true that it's "one-to-many".  But in the 
database overall, there can be any number of files that correspond to 
any number of people.  In other words:

Joe, Lisa, Ryan, and Josh worked on File 1, File 2, and File 3
Gino only worked on File 1 and File 4
but Lisa helped him on File 4
etc

so, one person can have many files associated with them, and one file 
can have many people associated with them.

Which is why I constructed this with the foreign key.  I think it would 
be a lot easier if I could make it "one-to-many"!!  In any event, there 
are other relationships in the database that are also many-to-many, so I 
am curious what is the standard way to go about entering data into 
tables in a way that keeps them connected.

> If you do require the "filespeople" table, then you'll have to INSERT
> records programmatically with your favorite scripting (PHP,ASP,PERL)
> language or program language.
>
> FYI.  Your "filespeople" file indicates that both fields are PRIMARY 
> keys.
> That cannot be.  Only one field may be PRIMARY.  I just now tried it to 
> be
> certain.

Yeah, this is the way that "mysqlshow" outputs the data.  In reality, 
each column is a UNIQUE index (-not- a PRIMARY KEY).  For some reason, 
that's what mysqlshow gives instead of UNIQUE.

Thanks for the input, though!
Erik



>
> -Original Message-
> From: Erik Price [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 09, 2002 11:14 AM
> To: [EMAIL PROTECTED]
> Cc: Etienne Marcotte
> Subject: INSERTing into joined tables?
>
>
> Hello, everyone --
> I have received a great deal of help from many members of this list, so
> I'd like to acknowledge that now.  Someday, when I have my head wrapped
> around this stuff, I hope to return the favor.  In the meantime,
> however, I have come across my worst brainbuster yet.  Any help on this
> is greatly appreciated.
>
>
> I constructed my tables in the most "normalized" way that I could
> (without overdoing it), so that in some cases, there is no -direct-
> relationship between tables.  That is, I have a table called "people"
> and a table called "files", and a foreign key table called 
> "filespeople":
>
> Database: medialab_db  Table: people
> +-+--+--+-+
> | Field   | Type | Null | Key |
> +-+--+--+-+
> | people_id   | smallint(5) unsigned |  | PRI | (auto-incremented)
> | first_name  | varchar(36)  |  | |
> | last_name   | varchar(36)  |  | |
> +-+--+--+-+
>
> Database: medialab_db  Table: files
> +-+---+--+-+
> | Field   | Type  | Null | Key |
> +-+---+--+-+
> | file_id | mediumint(8) unsigned |  | PRI | (auto-incremented)
> | file_name   | varchar(64)   |  | |
> +-+---+--+-+
>
> Database: medialab_db  Table: filespeople
> +---+---+--+-+
> | Field | Type  | Null | Key |
> +---+---+--+-+
> | file_id   | mediumint(8) unsigned |  | PRI |
> | people_id | smallint(5) unsigned  |  | PRI |
> +---+---+--+-+
>
> The relationship, in real life, is that I would like to establish
> many-to-many relationships between "files" records and "people" records,
> so that a record in "files" would be associated with several people from
> "people".  There is a foreign key table called "filespeople".  The SQL
> used to write a SELECT statement would use the join like so:
>
> SELECT files.file_name, people.first_name, people.last_name
> FROM files, people, filespeople
> WHERE files.file_name = $filename
> AND files.file_id = filespeople.file_id
> AND people.people_id = filespeople.people_id
> (the $filename variable is a user-selected variable, I'm using PHP)
>
> So I designed my "files" and "people" tables without any direct
> relationship with one another, thinking to link them with the SELECT
> statement.
>
> What I completely forgot, up until this point, was that I would need to
> INSERT these records (from pre-written HTML/PHP forms), and there is no
> WHERE clause in the INSERT statement to keep everything together.  In my
> scenario, a user might add a record to "files" and wish to associate
> that record to some 

Re: InnoDB : Lock wait timeout exceeded; Try restarting transaction

[John Kemp]

Heikki,

Hmm. That's interesting. So if you do a single command, say

INSERT INTO Table1 (X, Y, Z) VALUES ( A, B, C) ;

You actually need to write (I'm not sure of the exact transactional 
syntax for Mysql) -

BEGIN ; --begin a transaction
INSERT INTO Table1 (X, Y, Z) VALUES ( A, B, C) ;
COMMIT ; -- end a transaction

Even for a single statement with a single row update/insert?

That's not something that is generally necessary with other RDBMSs.

Can you confirm that?

Thanks,

John

Heikki Tuuri wrote:

> Hi!
> 
> Looks like your are not committing your transactions. Every UPDATE and
> INSERT automatically sets row level locks, which are only removed when you
> do a COMMIT or ROLLBACK.
> 
> Or you have set innodb_lock_wait_timeout too small in my.cnf.
> 
> InnoDB does not set table level locks. Only LOCK TABLES sets table level
> locks.
> 
> You can use innodb_lock_monitor to make the mysqld server program print
> (somewhat cryptic) information about who has locks and where. See the InnoDB
> manual at http://www.innodb.com/ibman.html
> 
> Best regards,
> 
> Heikki Tuuri
> Innobase Oy
> ---
> InnoDB - transactions, row level locking, and foreign key support for MySQL
> See http://www.innodb.com, download MySQL-Max from http://www.mysql.com
> 
> ..
> 
> Hi SAm,
> 
> I actually had a similar problem myself, but was unable to prove it was
> the persistent connection itself causing this. I'm wondering if this
> means that INNODB thinks that a connection that is now 'sleeping' (ie.
> where a connection was created, used, but is now unused but still open)
> might be locking the whole table erroneously for some reason? Which
> version are you using? I could not figure out why Innodb would think the
> table was locked, other than if someone specifically said 'LOCK TABLE'
> in a query, which wasn't the case.
> 
> Any thoughts?
> 
> John
> 
> 
> 
> Sam Lam wrote:
> 
> 
>>I recently switched to InnoDB & persistent connections from PHP.
>>
>>Lately I've been getting these errors "Lock wait timeout exceeded; Try
>>restarting transaction" on an UPDATE on table. The system is in
>>development so there is at most one other user ( a back end Perl script).
>>
>>When I switched PHP back to non-persistent connections I stopped getting
>>that error.
>>
>>How does one use persistent PHP connections & InnoDB to avoid this error ?
>>
>>
> 
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> .
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Random Select Statement - help please

[Rick Emery]

Easier method:

SELECT * FROM people ORDER BY RAND() LIMIT 1;

-Original Message-
From: David V. Edelstein [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 09, 2002 1:08 PM
To: 'Roger Baklund'
Cc: '[EMAIL PROTECTED]'
Subject: Random Select Statement - help please 


Hi Roger,

Thanks for your reply and I apologize for my lack of specificity on my
previous email, thanks for your patience :).
 
I have a people object that has 4 entities. name, pword, user_id, picurl
This data is stored in the people table. Each user is identified by
a unique user_id. I would like to create a mySQL query that will find the
max
user_id, use this value to calculate a random value of the user_id from [1
thru max],
then return the entire row for that person(user_id) filled in with that
persons(row) four entities, 
which I can use to populate the people object.

  select @max:=max(user_id) from people;
  select round((@max-1)*rand()+1) from people limit 1;

Returns: 1 field, 1 record which is empty.

  select round((max(user_id)-1)*rand()+1) from people limit 1;

returns: 1 field, 1 record populated with a random number between 1 and max
of the user_id column.
This is great, but how can I put this together, so that the query will
return 4 fields, 1 record that is populated with the 4 entities for a single
random person? 

Thanks a bunch for your help!

Best regards,
David

ps: When you state @max: does this create a variable @max in a mysql buffer
that you can use in subsequent queries? If so when does this variable
expire? When you close the connection?

-Original Message-
From: Roger Baklund [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 09, 2002 2:40 AM
To: David V. Edelstein
Subject: RE: Random Select Statement??


> Sorry Roger,
> It's not working correctly.
> Please, how can I accomplish the following?
>
> select * from people where user_id=(get the random #) limit 1;

It is difficult to help you when you don't say what the problem is... do you
get an error message? Do you get the wrong result? No result?

These are valid sql statements, but they may need mysql version 3.23 or
later, and both statements must be run within the same session (I don't
think you can use phpadmin or similar, unless multiple commands can be
executed at the same time):

  select @max:=max(user_id) from people;
  select round((@max-1)*rand()+1) from people limit 1;

Your own statement should also work:

  select round((max(user_id)-1)*rand()+1) from people limit 1;

If it doesn't work, something is wrong... ;)

- You must enter the statements in the mysql client
- You must be connected to the correct database
- You must have select privileges on the table
- The table must be named 'people', and the field must be named 'user_id'

--
Roger

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: command mysql -u $user without typing it ????

[Matthew Darcy]

I was doing a flush privileges.

but this was coming up with the same thing no rows affected which to me was
concerning.

I have now got 1 user working but 1 user not.

I am going to delete all users and start again with the users, as at the
start I was not doing flush privileges and I was also trying put hostnames
without DNS into the hosts field. I may have corrupted the user accounts in
some way.

I'll keep you posted.

Thanks for the help and the good explainations though.

I am sure it will be a user error on my part if 1 user is working and
another 1 not.

Cheers.

Matt.


-Original Message-
From: James Montebello [mailto:[EMAIL PROTECTED]]
Sent: 09 January 2002 19:43
To: Matthew Darcy
Cc: Ho Kam; Gerald Clark; MySql List
Subject: RE: command mysql -u $user without typing it 



You must do a "FLUSH PRIVILEGES" after making any changes to the user
permissions before they will actually take effect.  This is documented.

james montebello

On Wed, 9 Jan 2002, Matthew Darcy wrote:

> query ok I would expect to see, but "no rows affected" ??? this must mean
it
> has not altered the database at all ??
>
> I created a 2 new users, dba and matt
>
> I did grant all privileges to *.* to dba@"%"
>
> I cannot conntect from anything host as dba.
>
> I then did
>
> grant all privileges to *.* to matt@"jaguar.no-dns.co.uk" (my laptop)
>
> I got the no rows affected message and I cannot connect from the machine
> jaguar.
>
> something must be wrong. I used stand the pricinpals of the grant command
> but I cannot understand why no rows are affected. It does not surprise me
> that I cannot connect when no rows are affected.
>
> any other suggestions.
>
> Thanks for going into detail on this I am learning.
>
> Matt.
>
>
> -Original Message-
> From: Ho, Kam [mailto:[EMAIL PROTECTED]]
> Sent: 09 January 2002 16:57
> To: '[EMAIL PROTECTED]'; Ho, Kam; Gerald Clark
> Cc: MySql List
> Subject: RE: command mysql -u $user without typing it 
>
>
> The "Query OK, 0 rows affected" indicate that the user table is updated so
> test2 on localhost can access the mysql databases.
>
> Do you still get the "access is denyed for user @localhostto database
mysql"
> message?
>
>
> -Original Message-
> From: Matthew Darcy [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 09, 2002 11:51 AM
> To: Ho Kam; Gerald Clark
> Cc: MySql List
> Subject: RE: command mysql -u $user without typing it 
>
>
> tired that but I am getting
>
> Query OK, 0 rows affected (0.00 sec)
>
>
> don't understand why it is not granting to the user ?
>
> Matt.
>
>
> -Original Message-
> From: Ho, Kam [mailto:[EMAIL PROTECTED]]
> Sent: 09 January 2002 16:10
> To: '[EMAIL PROTECTED]'; Gerald Clark
> Cc: MySql List
> Subject: RE: command mysql -u $user without typing it 
>
>
> Try this:
>
> mysql> grant all privileges on *.* to test2@"localhost"
>
> also read manual about the grant command.
>
> -Original Message-
> From: Matthew Darcy [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 09, 2002 11:04 AM
> To: Gerald Clark
> Cc: MySql List
> Subject: RE: command mysql -u $user without typing it 
>
>
> I read similar notes on this in the manual, saying that invoking mysql
> without the username will try to take the unix username if it exists in
the
> database.
>
> I tried this with test2, it opened an mysql session no problem, but when I
> did a connect mysql it said "access is denyed for user @localhostto
database
> mysql"
>
> to me this looks like it is trying to connect with no user instead of
> test2@localhost
>
> can you explain this ?
>
> Thanks,
>
> Matt.
>
>
> -Original Message-
> From: Gerald Clark [mailto:[EMAIL PROTECTED]]
> Sent: 09 January 2002 15:56
> To: [EMAIL PROTECTED]
> Cc: MySql List
> Subject: Re: command mysql -u $user without typing it 
>
>
>
>
> Matthew Darcy wrote:
>
> >
> >I have 3 UNIX users on my server.
> >
> >test1, test2, and test3.
> >
> >I have created 3 users for mysql test1, test2, and test3 (shock horror)
> >
> >If I want to connect to the database as root then I understand I must do
> >mysql -u root -p
> >
> >but I would like test1 2 and 3 to be able to type mysql and be either
> logged
> >in as their unix username, ie test2 types mysql and connects to the
> database
> >as test2 (mysql account)
> >or at least be prompted for the password for test2.
> >
> >The only way I can think of doing this is by setting up an alias ie
> >
> >alias mysql_connect=`mysql -u $username -p`
> >
> >there must be some sort of autologin from username like in oracle.
> >
> >Thanks,
> >
> >Matt.
> >
> The user defaults to the unix user.
> You can put a .my.cnf file in their home directory with
>
> [client]
> password=whatever
>
> to make it even easier.
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request thi

Re: I'm having trouble with End-Of-Record markers

[Gerald Clark]

Since you told it to end each line with *END* , the carriage return 
after the
*END* is part of the first field of the next line.

Try terminated by "*END*\r"

David Ayliffe wrote:

>I'm having trouble bulk-loading data into a table.  I have a file which
>contains:
>
>AYL01ôAYL01-1ôChloeô1979-12-1 0:0:0ôF*END*
>AYL01ôAYL01-2ôMelissaô1985-3-2 0:0:0ôF*END*
>AYL01ôAYL01-3ôDavidô1979-1-12 0:0:0ôM*END*
>COL01ôCOL01-1ôSimeonô1989-12-14 0:0:0ôF*END*
>DAV01ôDAV01-1ôMurialô1990-2-2 0:0:0ôF*END*
>SMI01ôSMI01-1ôKarlaô1989-8-8 0:0:0ôF*END*
>WIL01ôWIL01-1ôRobertô1981-8-11 0:0:0ôM*END*
>WIL01ôWIL01-2ôAndrewô1987-9-9 0:0:0ôM*END*
>WIL02ôWIL02-1ôAbi-galeô1980-1-31 0:0:0ôF*END*
>WIL02ôWIL02-2ôCharlotteô1979-7-7 0:0:0ôF*END*
>WIL02ôWIL02-3ôSusanô1982-8-17 0:0:0ôF*END*
>WIL02ôWIL02-4ôClarissaô1980-12-24 0:0:0ôF*END*
>
>(Straight cut and paste)
>
>I load the data using
>LOAD DATA LOCAL INFILE "data.txt" INTO TABLE gymnast FIELDS TERMINATED
>BY "ô" LINES TERMINATED BY "*END*";
>
>But I always get a malformed table which looks like:
>
>
>mysql> select * from gymnast;
>+---++---+-+--+
>| Family_id | Gymnast_id | Name  | Date_Of_Birth   | Sex  |
>+---++---+-+--+
>| AYL01 | AYL01-1| Chloe | 1979-12-01 00:00:00 | F|
>|
>AYL01   | AYL01-2| Melissa   | 1985-03-02 00:00:00 | F|
>|
>AYL01   | AYL01-3| David | 1979-01-12 00:00:00 | M|
>|
>COL01   | COL01-1| Simeon| 1989-12-14 00:00:00 | F|
>|
>DAV01   | DAV01-1| Murial| 1990-02-02 00:00:00 | F|
>|
>SMI01   | SMI01-1| Karla | 1989-08-08 00:00:00 | F|
>|
>WIL01   | WIL01-1| Robert| 1981-08-11 00:00:00 | M|
>|
>WIL01   | WIL01-2| Andrew| 1987-09-09 00:00:00 | M|
>|
>WIL02   | WIL02-1| Abi-gale  | 1980-01-31 00:00:00 | F|
>|
>WIL02   | WIL02-2| Charlotte | 1979-07-07 00:00:00 | F|
>|
>WIL02   | WIL02-3| Susan | 1982-08-17 00:00:00 | F|
>|
>WIL02   | WIL02-4| Clarissa  | 1980-12-24 00:00:00 | F|
>+---++---+-+--+
>12 rows in set (0.00 sec)
>
>mysql>
>
>
>How can I get the data into the table so it looks and acts like it
>should.
>
>
>Also when I try to EXTRACT the data from the same table using my delphi
>application on the last field (Sex) instead of just getting the value
>'F' I get 'F'#$D  Is this some sort of end-of-record marker?
>
>
>I could really do with some guidance on this one.
>
>Thanks
>DA
>
>
>-
>Before posting, please check:
>   http://www.mysql.com/manual.php   (the manual)
>   http://lists.mysql.com/   (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail 
><[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

scheduling tasks within MySQL

[Jeffrey Lomas]

I'm fairly new to mysql.  I have need for certain actions to occur 
within a database at a certain time every month.  What is the best way 
to go about doing this?  Is there a way to schedule these actions from 
within mysql?

Thanks
jeff


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Cannot import mysql dump

[Roger Baklund]

* Martin MOKREJŠ
>   I have a problem to re-import sql dump from Linux 2.2.19 running
> mysql  Ver 11.15 Distrib 3.23.44, for pc-linux-gnu (i686)
> into the same mysqld under different database.
>
>   while running the same commandline on the source at least.
>
> $ mysqldump --extended-insert Bordetella_pertussis_Tohama_I
> nonglob_data | mysql mmo
> ERROR 1064 at line 11: You have an error in your SQL syntax near
> 'PRIMARY (id),
>   KEY contig_data_id (contig_data_id),
>   KEY contig_data_id_2 (con' at line 9
> $
>
> Could someone explain me where is the error?

I don't know why this happens, but read below...

> The dump looks like:
>
> $ mysqldump --extended-insert Bordetella_pertussis_Tohama_I
> nonglob_data | less
> # MySQL dump 8.16
> #
> # Host: xxxDatabase: Bordetella_pertussis_Tohama_I
> #
> # Server version3.23.44-log
>
> #
> # Table structure for table 'nonglob_data'
> #
>
> CREATE TABLE nonglob_data (
>   id int(11) NOT NULL default '0',
>   contig_data_id int(11) NOT NULL default '0',
>   contig_data_code varchar(100) NOT NULL default '',
>   code varchar(100) NOT NULL default '',
>   descr varchar(255) default NULL,
>   dat longtext,
>   created varchar(100) NOT NULL default '',
>   UNIQUE KEY PRIMARY (id),

The error message suggests the problem is in the above line. From the
documentation:

create_definition:
  col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
[PRIMARY KEY] [reference_definition]
  orPRIMARY KEY (index_col_name,...)
  orKEY [index_name] (index_col_name,...)
  orINDEX [index_name] (index_col_name,...)
  orUNIQUE [INDEX] [index_name] (index_col_name,...)
  orFULLTEXT [INDEX] [index_name] (index_col_name,...)
  or[CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
[reference_definition]
  orCHECK (expr)

You can have "UNIQUE", "KEY" or "PRIMARY KEY", but not all at the same
time...

I have no idea why mysqldump creates this statement. But there is more...

>   KEY contig_data_id (contig_data_id),
>   KEY contig_data_id_2 (contig_data_id),
>   KEY contig_data_code (contig_data_code),
>   KEY contig_data_id_3 (contig_data_id),

Why are there multiple indexes for the same fields?

>   KEY code (code),
>   KEY contig_data_id_4 (contig_data_id),
>   KEY descr (descr),
>   KEY contig_data_code_2 (contig_data_code),
>   KEY contig_data_code_3 (contig_data_code),
>   KEY code_2 (code),
>   KEY contig_data_code_4 (contig_data_code),
>   KEY code_3 (code),
>   KEY contig_data_id_5 (contig_data_id),
>   KEY contig_data_id_6 (contig_data_id),
>   KEY code_4 (code),
>   KEY descr_2 (descr),
>   KEY descr_3 (descr),
>   KEY contig_data_code_5 (contig_data_code),
>   KEY contig_data_code_6 (contig_data_code),
>   KEY descr_4 (descr),
>   KEY contig_data_id_7 (contig_data_id),
>   KEY contig_data_id_8 (contig_data_id),
>   KEY contig_data_code_7 (contig_data_code),
>   KEY contig_data_code_8 (contig_data_code),
>   KEY code_5 (code),
>   KEY descr_5 (descr),
>   KEY code_6 (code),
>   KEY code_7 (code),
>   KEY descr_6 (descr),
>   KEY code_8 (code),
>   KEY descr_7 (descr)
> ) TYPE=MyISAM;

A lot of them, actually... can anyone explain this?

--
Roger


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: INSERTing into joined tables?

[Rick Emery]

First, you are describing a "one-to-many" relationship, bceasue one "file"
record can be referenced by many "people" records.  If this is the case, you
may wish to re-design your tablse such that a "people record" contains a
"file_id" field.  You can then do away with the "filespeople" table
altogether.  Normalization is a good thing; but not when it is at the
detriment of good design including how one processes it.

If you do require the "filespeople" table, then you'll have to INSERT
records programmatically with your favorite scripting (PHP,ASP,PERL)
language or program language.

FYI.  Your "filespeople" file indicates that both fields are PRIMARY keys.
That cannot be.  Only one field may be PRIMARY.  I just now tried it to be
certain.

-Original Message-
From: Erik Price [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 09, 2002 11:14 AM
To: [EMAIL PROTECTED]
Cc: Etienne Marcotte
Subject: INSERTing into joined tables?


Hello, everyone --
I have received a great deal of help from many members of this list, so 
I'd like to acknowledge that now.  Someday, when I have my head wrapped 
around this stuff, I hope to return the favor.  In the meantime, 
however, I have come across my worst brainbuster yet.  Any help on this 
is greatly appreciated.


I constructed my tables in the most "normalized" way that I could 
(without overdoing it), so that in some cases, there is no -direct- 
relationship between tables.  That is, I have a table called "people" 
and a table called "files", and a foreign key table called "filespeople":

Database: medialab_db  Table: people
+-+--+--+-+
| Field   | Type | Null | Key |
+-+--+--+-+
| people_id   | smallint(5) unsigned |  | PRI | (auto-incremented)
| first_name  | varchar(36)  |  | |
| last_name   | varchar(36)  |  | |
+-+--+--+-+

Database: medialab_db  Table: files
+-+---+--+-+
| Field   | Type  | Null | Key |
+-+---+--+-+
| file_id | mediumint(8) unsigned |  | PRI | (auto-incremented)
| file_name   | varchar(64)   |  | |
+-+---+--+-+

Database: medialab_db  Table: filespeople
+---+---+--+-+
| Field | Type  | Null | Key |
+---+---+--+-+
| file_id   | mediumint(8) unsigned |  | PRI |
| people_id | smallint(5) unsigned  |  | PRI |
+---+---+--+-+

The relationship, in real life, is that I would like to establish 
many-to-many relationships between "files" records and "people" records, 
so that a record in "files" would be associated with several people from 
"people".  There is a foreign key table called "filespeople".  The SQL 
used to write a SELECT statement would use the join like so:

SELECT files.file_name, people.first_name, people.last_name
FROM files, people, filespeople
WHERE files.file_name = $filename
AND files.file_id = filespeople.file_id
AND people.people_id = filespeople.people_id
(the $filename variable is a user-selected variable, I'm using PHP)

So I designed my "files" and "people" tables without any direct 
relationship with one another, thinking to link them with the SELECT 
statement.

What I completely forgot, up until this point, was that I would need to 
INSERT these records (from pre-written HTML/PHP forms), and there is no 
WHERE clause in the INSERT statement to keep everything together.  In my 
scenario, a user might add a record to "files" and wish to associate 
that record to some of the records in "people", either new or 
pre-existing (typed into an HTML text input form or something).  How 
should SQL code be arranged to "link" these records over the foreign key 
table?

INSERT INTO files (file_name) VALUES ("$filename")

and

INSERT INTO people (first_name, last_name) VALUES ("$firstname", 
"$lastname")

but... to keep it all together... is lost on me... and then later to 
have UPDATE statements to do the same thing!  Although I suspect this 
may be easier as I can use the WHERE clause in an UPDATE statement.

If anyone has a link to a tutorial on this very concept, that would be 
greatly appreciated as well!


Thank you,
Erik


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql

I'm having trouble with End-Of-Record markers

[David Ayliffe]

I'm having trouble bulk-loading data into a table.  I have a file which
contains:

AYL01ôAYL01-1ôChloeô1979-12-1 0:0:0ôF*END*
AYL01ôAYL01-2ôMelissaô1985-3-2 0:0:0ôF*END*
AYL01ôAYL01-3ôDavidô1979-1-12 0:0:0ôM*END*
COL01ôCOL01-1ôSimeonô1989-12-14 0:0:0ôF*END*
DAV01ôDAV01-1ôMurialô1990-2-2 0:0:0ôF*END*
SMI01ôSMI01-1ôKarlaô1989-8-8 0:0:0ôF*END*
WIL01ôWIL01-1ôRobertô1981-8-11 0:0:0ôM*END*
WIL01ôWIL01-2ôAndrewô1987-9-9 0:0:0ôM*END*
WIL02ôWIL02-1ôAbi-galeô1980-1-31 0:0:0ôF*END*
WIL02ôWIL02-2ôCharlotteô1979-7-7 0:0:0ôF*END*
WIL02ôWIL02-3ôSusanô1982-8-17 0:0:0ôF*END*
WIL02ôWIL02-4ôClarissaô1980-12-24 0:0:0ôF*END*

(Straight cut and paste)

I load the data using
LOAD DATA LOCAL INFILE "data.txt" INTO TABLE gymnast FIELDS TERMINATED
BY "ô" LINES TERMINATED BY "*END*";

But I always get a malformed table which looks like:


mysql> select * from gymnast;
+---++---+-+--+
| Family_id | Gymnast_id | Name  | Date_Of_Birth   | Sex  |
+---++---+-+--+
| AYL01 | AYL01-1| Chloe | 1979-12-01 00:00:00 | F|
|
AYL01   | AYL01-2| Melissa   | 1985-03-02 00:00:00 | F|
|
AYL01   | AYL01-3| David | 1979-01-12 00:00:00 | M|
|
COL01   | COL01-1| Simeon| 1989-12-14 00:00:00 | F|
|
DAV01   | DAV01-1| Murial| 1990-02-02 00:00:00 | F|
|
SMI01   | SMI01-1| Karla | 1989-08-08 00:00:00 | F|
|
WIL01   | WIL01-1| Robert| 1981-08-11 00:00:00 | M|
|
WIL01   | WIL01-2| Andrew| 1987-09-09 00:00:00 | M|
|
WIL02   | WIL02-1| Abi-gale  | 1980-01-31 00:00:00 | F|
|
WIL02   | WIL02-2| Charlotte | 1979-07-07 00:00:00 | F|
|
WIL02   | WIL02-3| Susan | 1982-08-17 00:00:00 | F|
|
WIL02   | WIL02-4| Clarissa  | 1980-12-24 00:00:00 | F|
+---++---+-+--+
12 rows in set (0.00 sec)

mysql>


How can I get the data into the table so it looks and acts like it
should.


Also when I try to EXTRACT the data from the same table using my delphi
application on the last field (Sex) instead of just getting the value
'F' I get 'F'#$D  Is this some sort of end-of-record marker?


I could really do with some guidance on this one.

Thanks
DA


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Error 13

[Daniel Rosher]

>From the Manual (21.11 Problems with File Permissions)

By default MySQL will create database and RAID directories with permission
type 0700. You can modify this behavior by setting the UMASK_DIR variable.
If you set this, new directories are created with the combined UMASK and
UMASK_DIR. For example, if you want to give group access to all new
directories, you can do:

shell> UMASK_DIR=504  # = 770 in octal
shell> export UMASK_DIR
shell> /path/to/safe_mysqld &

In MySQL Version 3.23.25 and above, MySQL assumes that the value for UMASK
and UMASK_DIR is in octal if it starts with a zero.

So either the dir has to be owned by the same user which runs mysqld  or
change the current directory permissions and the UMASK_DIR to prevent
further issues.

Regards,
Dan


> -Original Message-
> From: Quentin Bennett [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, 10 January 2002 8:27 a.m.
> To: 'P.Agenbag'; mysql
> Subject: RE: Error 13
>
>
> Hi
>
> Error 13 is from the OS - nothing to do with locks or whatever.
>
> However, when you add a new field (or change the table structure
> in (nearly)
> any way), mysqld will create a temporary table in the database directory -
> are you sure that the permissions on the directory are correct?
>
> Remember, it is the user running mysqld that needs the
> permissions, not the
> user running the command.
>
> HTH
>
> Quentin
>
> -Original Message-
> From: P.Agenbag [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, 10 January 2002 6:14 a.m.
> To: mysql
> Subject: Error 13
>
>
> Hi
> I have a table that I want to add a new field to while running on the
> server, however, when I try to add a new field, it comes up with the
> error13 message, saying it doesn't have permission, yet the files and
> folder are all chmod 777. Can it be due to the fact that there is a
> permanent connection to this table from another server and that mysql is
> preventing any major changes?
> Should I just try to make a copy of the table and make the changes to it
> and then overwrite the old one?
>
> Is there a "cleaner way of managing your tables and to make changes
> without having to resort to this "manual way"?
>
> Thanks
>
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
> The information contained in this email is privileged and confidential
> and intended for the addressee only. If you are not the intended
> recipient, you are asked to respect that confidentiality and not
> disclose, copy or make use of its contents. If received in error
> you are asked to destroy this email and contact the sender immediately.
> Your assistance is appreciated.
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: InnoDB : Lock wait timeout exceeded; Try restarting transaction

[Heikki Tuuri]

Hi!

Looks like your are not committing your transactions. Every UPDATE and
INSERT automatically sets row level locks, which are only removed when you
do a COMMIT or ROLLBACK.

Or you have set innodb_lock_wait_timeout too small in my.cnf.

InnoDB does not set table level locks. Only LOCK TABLES sets table level
locks.

You can use innodb_lock_monitor to make the mysqld server program print
(somewhat cryptic) information about who has locks and where. See the InnoDB
manual at http://www.innodb.com/ibman.html

Best regards,

Heikki Tuuri
Innobase Oy
---
InnoDB - transactions, row level locking, and foreign key support for MySQL
See http://www.innodb.com, download MySQL-Max from http://www.mysql.com

..

Hi SAm,

I actually had a similar problem myself, but was unable to prove it was
the persistent connection itself causing this. I'm wondering if this
means that INNODB thinks that a connection that is now 'sleeping' (ie.
where a connection was created, used, but is now unused but still open)
might be locking the whole table erroneously for some reason? Which
version are you using? I could not figure out why Innodb would think the
table was locked, other than if someone specifically said 'LOCK TABLE'
in a query, which wasn't the case.

Any thoughts?

John



Sam Lam wrote:

> I recently switched to InnoDB & persistent connections from PHP.
>
> Lately I've been getting these errors "Lock wait timeout exceeded; Try
> restarting transaction" on an UPDATE on table. The system is in
> development so there is at most one other user ( a back end Perl script).
>
> When I switched PHP back to non-persistent connections I stopped getting
> that error.
>
> How does one use persistent PHP connections & InnoDB to avoid this error ?
>




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

New Install Problems

[Rich]

I installed the binary rpm version of MySQL 3.23.47  yesterday and set
up the necessary permissions.  This is a single user computer, not on a
network, so I'm both 'root' and 'user'.

Today I tried to start working on the tutorial in the manual and found
that I couldn't create a database and couldn't log in to MySQL as
'root'.  I can get into MySQL as 'user', but can't create or access any
databases; this last is understandable as the database has to be created
as 'root'.

My quick solution to the problem was to uninstall the rpm and then
reinstall it, the problem is that there are apparently files still in my
system that didn't go away during the uninstall and I'm right back where
I started from.

What files do I have to delete to make a fresh install?  Or, is there
another way around the problem?

TIA

Rich
-- 
[EMAIL PROTECTED]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: command mysql -u $user without typing it ????

[James Montebello]

You must do a "FLUSH PRIVILEGES" after making any changes to the user
permissions before they will actually take effect.  This is documented.

james montebello

On Wed, 9 Jan 2002, Matthew Darcy wrote:

> query ok I would expect to see, but "no rows affected" ??? this must mean it
> has not altered the database at all ??
> 
> I created a 2 new users, dba and matt
> 
> I did grant all privileges to *.* to dba@"%"
> 
> I cannot conntect from anything host as dba.
> 
> I then did
> 
> grant all privileges to *.* to matt@"jaguar.no-dns.co.uk" (my laptop)
> 
> I got the no rows affected message and I cannot connect from the machine
> jaguar.
> 
> something must be wrong. I used stand the pricinpals of the grant command
> but I cannot understand why no rows are affected. It does not surprise me
> that I cannot connect when no rows are affected.
> 
> any other suggestions.
> 
> Thanks for going into detail on this I am learning.
> 
> Matt.
> 
> 
> -Original Message-
> From: Ho, Kam [mailto:[EMAIL PROTECTED]]
> Sent: 09 January 2002 16:57
> To: '[EMAIL PROTECTED]'; Ho, Kam; Gerald Clark
> Cc: MySql List
> Subject: RE: command mysql -u $user without typing it 
> 
> 
> The "Query OK, 0 rows affected" indicate that the user table is updated so
> test2 on localhost can access the mysql databases.
> 
> Do you still get the "access is denyed for user @localhostto database mysql"
> message?
> 
> 
> -Original Message-
> From: Matthew Darcy [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 09, 2002 11:51 AM
> To: Ho Kam; Gerald Clark
> Cc: MySql List
> Subject: RE: command mysql -u $user without typing it 
> 
> 
> tired that but I am getting
> 
> Query OK, 0 rows affected (0.00 sec)
> 
> 
> don't understand why it is not granting to the user ?
> 
> Matt.
> 
> 
> -Original Message-
> From: Ho, Kam [mailto:[EMAIL PROTECTED]]
> Sent: 09 January 2002 16:10
> To: '[EMAIL PROTECTED]'; Gerald Clark
> Cc: MySql List
> Subject: RE: command mysql -u $user without typing it 
> 
> 
> Try this:
> 
> mysql> grant all privileges on *.* to test2@"localhost"
> 
> also read manual about the grant command.
> 
> -Original Message-
> From: Matthew Darcy [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 09, 2002 11:04 AM
> To: Gerald Clark
> Cc: MySql List
> Subject: RE: command mysql -u $user without typing it 
> 
> 
> I read similar notes on this in the manual, saying that invoking mysql
> without the username will try to take the unix username if it exists in the
> database.
> 
> I tried this with test2, it opened an mysql session no problem, but when I
> did a connect mysql it said "access is denyed for user @localhostto database
> mysql"
> 
> to me this looks like it is trying to connect with no user instead of
> test2@localhost
> 
> can you explain this ?
> 
> Thanks,
> 
> Matt.
> 
> 
> -Original Message-
> From: Gerald Clark [mailto:[EMAIL PROTECTED]]
> Sent: 09 January 2002 15:56
> To: [EMAIL PROTECTED]
> Cc: MySql List
> Subject: Re: command mysql -u $user without typing it 
> 
> 
> 
> 
> Matthew Darcy wrote:
> 
> >
> >I have 3 UNIX users on my server.
> >
> >test1, test2, and test3.
> >
> >I have created 3 users for mysql test1, test2, and test3 (shock horror)
> >
> >If I want to connect to the database as root then I understand I must do
> >mysql -u root -p
> >
> >but I would like test1 2 and 3 to be able to type mysql and be either
> logged
> >in as their unix username, ie test2 types mysql and connects to the
> database
> >as test2 (mysql account)
> >or at least be prompted for the password for test2.
> >
> >The only way I can think of doing this is by setting up an alias ie
> >
> >alias mysql_connect=`mysql -u $username -p`
> >
> >there must be some sort of autologin from username like in oracle.
> >
> >Thanks,
> >
> >Matt.
> >
> The user defaults to the unix user.
> You can put a .my.cnf file in their home directory with
> 
> [client]
> password=whatever
> 
> to make it even easier.
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the li

RE: command mysql -u $user without typing it ????

[Quentin Bennett]

Are you doing 'flush privileges' are documented?

-Original Message-
From: Matthew Darcy [mailto:[EMAIL PROTECTED]]
Sent: Thursday, 10 January 2002 8:26 a.m.
To: Ho Kam; Gerald Clark
Cc: MySql List
Subject: RE: command mysql -u $user without typing it 


query ok I would expect to see, but "no rows affected" ??? this must mean it
has not altered the database at all ??

I created a 2 new users, dba and matt

I did grant all privileges to *.* to dba@"%"

I cannot conntect from anything host as dba.

I then did

grant all privileges to *.* to matt@"jaguar.no-dns.co.uk" (my laptop)

I got the no rows affected message and I cannot connect from the machine
jaguar.

something must be wrong. I used stand the pricinpals of the grant command
but I cannot understand why no rows are affected. It does not surprise me
that I cannot connect when no rows are affected.

any other suggestions.

Thanks for going into detail on this I am learning.

Matt.


-Original Message-
From: Ho, Kam [mailto:[EMAIL PROTECTED]]
Sent: 09 January 2002 16:57
To: '[EMAIL PROTECTED]'; Ho, Kam; Gerald Clark
Cc: MySql List
Subject: RE: command mysql -u $user without typing it 


The "Query OK, 0 rows affected" indicate that the user table is updated so
test2 on localhost can access the mysql databases.

Do you still get the "access is denyed for user @localhostto database mysql"
message?


-Original Message-
From: Matthew Darcy [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 09, 2002 11:51 AM
To: Ho Kam; Gerald Clark
Cc: MySql List
Subject: RE: command mysql -u $user without typing it 


tired that but I am getting

Query OK, 0 rows affected (0.00 sec)


don't understand why it is not granting to the user ?

Matt.


-Original Message-
From: Ho, Kam [mailto:[EMAIL PROTECTED]]
Sent: 09 January 2002 16:10
To: '[EMAIL PROTECTED]'; Gerald Clark
Cc: MySql List
Subject: RE: command mysql -u $user without typing it 


Try this:

mysql> grant all privileges on *.* to test2@"localhost"

also read manual about the grant command.

-Original Message-
From: Matthew Darcy [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 09, 2002 11:04 AM
To: Gerald Clark
Cc: MySql List
Subject: RE: command mysql -u $user without typing it 


I read similar notes on this in the manual, saying that invoking mysql
without the username will try to take the unix username if it exists in the
database.

I tried this with test2, it opened an mysql session no problem, but when I
did a connect mysql it said "access is denyed for user @localhostto database
mysql"

to me this looks like it is trying to connect with no user instead of
test2@localhost

can you explain this ?

Thanks,

Matt.


-Original Message-
From: Gerald Clark [mailto:[EMAIL PROTECTED]]
Sent: 09 January 2002 15:56
To: [EMAIL PROTECTED]
Cc: MySql List
Subject: Re: command mysql -u $user without typing it 




Matthew Darcy wrote:

>
>I have 3 UNIX users on my server.
>
>test1, test2, and test3.
>
>I have created 3 users for mysql test1, test2, and test3 (shock horror)
>
>If I want to connect to the database as root then I understand I must do
>mysql -u root -p
>
>but I would like test1 2 and 3 to be able to type mysql and be either
logged
>in as their unix username, ie test2 types mysql and connects to the
database
>as test2 (mysql account)
>or at least be prompted for the password for test2.
>
>The only way I can think of doing this is by setting up an alias ie
>
>alias mysql_connect=`mysql -u $username -p`
>
>there must be some sort of autologin from username like in oracle.
>
>Thanks,
>
>Matt.
>
The user defaults to the unix user.
You can put a .my.cnf file in their home directory with

[client]
password=whatever

to make it even easier.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   

Re: apostrophe's in PHP

[Becky McElroy]

I've found that using PreparedStatement.setString() takes care of any
apostrophe's within a given sql string field.




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Random Select Statement - help please

[Roger Baklund]

* David V. Edelstein
> I would like to create a mySQL query that will find the max
> user_id, use this value to calculate a random value of the
> user_id from [1 thru max], then return the entire row for
> that person(user_id) filled in with that persons(row) four
> entities, which I can use to populate the people object.

Because of the lack of sub-selects in mysql, I suggested using user
variables:

>   select @max:=max(user_id) from people;
>   select round((@max-1)*rand()+1) from people limit 1;
>
> Returns: 1 field, 1 record which is empty.

You should get a random number. I did not notice you wanted the entire
people record, sorry. Disregard the above select-statements anyway, they are
no good. :/

>   select round((max(user_id)-1)*rand()+1) from people limit 1;
>
> returns: 1 field, 1 record populated with a random number between
> 1 and max of the user_id column.
> This is great, but how can I put this together, so that the query will
> return 4 fields, 1 record that is populated with the 4 entities
> for a single random person?

Yes, you wanted * from people... try this:

  select @rnd:=round((max(user_id)-1)*rand()+1) from people;
  select * from people where user_id = @rnd;

If some user_id's are missing you can use "user_id >= @rnd limit 1" to get
the first user_id after the missing.

> Thanks a bunch for your help!

You're welcome! :)

> ps: When you state @max: does this create a variable @max in a
> mysql buffer that you can use in subsequent queries?

Yes, the variable is saved in the server memory.

> If so when does this variable
> expire? When you close the connection?

yes. :)

http://www.mysql.com/doc/V/a/Variables.html >
http://www.mysql.com/doc/e/x/example-user-variables.html >

(This last example is similar to your problem.)

--
Roger


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Error 13

[Stephen Abshire]

Your new best friend:

http://www.phpwizard.net/projects/phpMyAdmin/


Original Message Follows
From: "P.Agenbag" <[EMAIL PROTECTED]>
To: mysql <[EMAIL PROTECTED]>
Subject: Error 13
Date: Wed, 09 Jan 2002 19:13:51 +0200

Hi
I have a table that I want to add a new field to while running on the
server, however, when I try to add a new field, it comes up with the
error13 message, saying it doesn't have permission, yet the files and
folder are all chmod 777. Can it be due to the fact that there is a
permanent connection to this table from another server and that mysql is
preventing any major changes?
Should I just try to make a copy of the table and make the changes to it
and then overwrite the old one?

Is there a "cleaner way of managing your tables and to make changes
without having to resort to this "manual way"?

Thanks



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail 
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


_
Send and receive Hotmail on your mobile device: http://mobile.msn.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: command mysql -u $user without typing it ????

[Ho, Kam]

Matt:

Try the following:
Use mysql
select * from user (take a note on users that you're granting rights to)
run the grant command
select * from user (see if there is any differences)

OR

try 
grant all privileges to *.* to matt@"ipaddress" (where ipaddress ==
999.999.999.999 your real ip address)

-Original Message-
From: Matthew Darcy [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 09, 2002 2:26 PM
To: Ho Kam; Gerald Clark
Cc: MySql List
Subject: RE: command mysql -u $user without typing it 


query ok I would expect to see, but "no rows affected" ??? this must mean it
has not altered the database at all ??

I created a 2 new users, dba and matt

I did grant all privileges to *.* to dba@"%"

I cannot conntect from anything host as dba.

I then did

grant all privileges to *.* to matt@"jaguar.no-dns.co.uk" (my laptop)

I got the no rows affected message and I cannot connect from the machine
jaguar.

something must be wrong. I used stand the pricinpals of the grant command
but I cannot understand why no rows are affected. It does not surprise me
that I cannot connect when no rows are affected.

any other suggestions.

Thanks for going into detail on this I am learning.

Matt.


-Original Message-
From: Ho, Kam [mailto:[EMAIL PROTECTED]]
Sent: 09 January 2002 16:57
To: '[EMAIL PROTECTED]'; Ho, Kam; Gerald Clark
Cc: MySql List
Subject: RE: command mysql -u $user without typing it 


The "Query OK, 0 rows affected" indicate that the user table is updated so
test2 on localhost can access the mysql databases.

Do you still get the "access is denyed for user @localhostto database mysql"
message?


-Original Message-
From: Matthew Darcy [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 09, 2002 11:51 AM
To: Ho Kam; Gerald Clark
Cc: MySql List
Subject: RE: command mysql -u $user without typing it 


tired that but I am getting

Query OK, 0 rows affected (0.00 sec)


don't understand why it is not granting to the user ?

Matt.


-Original Message-
From: Ho, Kam [mailto:[EMAIL PROTECTED]]
Sent: 09 January 2002 16:10
To: '[EMAIL PROTECTED]'; Gerald Clark
Cc: MySql List
Subject: RE: command mysql -u $user without typing it 


Try this:

mysql> grant all privileges on *.* to test2@"localhost"

also read manual about the grant command.

-Original Message-
From: Matthew Darcy [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 09, 2002 11:04 AM
To: Gerald Clark
Cc: MySql List
Subject: RE: command mysql -u $user without typing it 


I read similar notes on this in the manual, saying that invoking mysql
without the username will try to take the unix username if it exists in the
database.

I tried this with test2, it opened an mysql session no problem, but when I
did a connect mysql it said "access is denyed for user @localhostto database
mysql"

to me this looks like it is trying to connect with no user instead of
test2@localhost

can you explain this ?

Thanks,

Matt.


-Original Message-
From: Gerald Clark [mailto:[EMAIL PROTECTED]]
Sent: 09 January 2002 15:56
To: [EMAIL PROTECTED]
Cc: MySql List
Subject: Re: command mysql -u $user without typing it 




Matthew Darcy wrote:

>
>I have 3 UNIX users on my server.
>
>test1, test2, and test3.
>
>I have created 3 users for mysql test1, test2, and test3 (shock horror)
>
>If I want to connect to the database as root then I understand I must do
>mysql -u root -p
>
>but I would like test1 2 and 3 to be able to type mysql and be either
logged
>in as their unix username, ie test2 types mysql and connects to the
database
>as test2 (mysql account)
>or at least be prompted for the password for test2.
>
>The only way I can think of doing this is by setting up an alias ie
>
>alias mysql_connect=`mysql -u $username -p`
>
>there must be some sort of autologin from username like in oracle.
>
>Thanks,
>
>Matt.
>
The user defaults to the unix user.
You can put a .my.cnf file in their home directory with

[client]
password=whatever

to make it even easier.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thr

Re: InnoDB : Lock wait timeout exceeded; Try restarting transaction

[John Kemp]

Hi SAm,

I actually had a similar problem myself, but was unable to prove it was 
the persistent connection itself causing this. I'm wondering if this 
means that INNODB thinks that a connection that is now 'sleeping' (ie. 
where a connection was created, used, but is now unused but still open) 
might be locking the whole table erroneously for some reason? Which 
version are you using? I could not figure out why Innodb would think the 
table was locked, other than if someone specifically said 'LOCK TABLE' 
in a query, which wasn't the case.

Any thoughts?

John



Sam Lam wrote:

> I recently switched to InnoDB & persistent connections from PHP.
> 
> Lately I've been getting these errors "Lock wait timeout exceeded; Try 
> restarting transaction" on an UPDATE on table. The system is in 
> development so there is at most one other user ( a back end Perl script).
> 
> When I switched PHP back to non-persistent connections I stopped getting 
> that error.
> 
> How does one use persistent PHP connections & InnoDB to avoid this error ?
> 
> 
> -
> Before posting, please check:
>   http://www.mysql.com/manual.php   (the manual)
>   http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> .
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Error 13

[Quentin Bennett]

Hi

Error 13 is from the OS - nothing to do with locks or whatever.

However, when you add a new field (or change the table structure in (nearly)
any way), mysqld will create a temporary table in the database directory -
are you sure that the permissions on the directory are correct?

Remember, it is the user running mysqld that needs the permissions, not the
user running the command.

HTH

Quentin

-Original Message-
From: P.Agenbag [mailto:[EMAIL PROTECTED]]
Sent: Thursday, 10 January 2002 6:14 a.m.
To: mysql
Subject: Error 13


Hi
I have a table that I want to add a new field to while running on the 
server, however, when I try to add a new field, it comes up with the 
error13 message, saying it doesn't have permission, yet the files and 
folder are all chmod 777. Can it be due to the fact that there is a 
permanent connection to this table from another server and that mysql is 
preventing any major changes?
Should I just try to make a copy of the table and make the changes to it 
and then overwrite the old one?

Is there a "cleaner way of managing your tables and to make changes 
without having to resort to this "manual way"?

Thanks



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

The information contained in this email is privileged and confidential
and intended for the addressee only. If you are not the intended 
recipient, you are asked to respect that confidentiality and not 
disclose, copy or make use of its contents. If received in error 
you are asked to destroy this email and contact the sender immediately. 
Your assistance is appreciated.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: command mysql -u $user without typing it ????

[Matthew Darcy]

query ok I would expect to see, but "no rows affected" ??? this must mean it
has not altered the database at all ??

I created a 2 new users, dba and matt

I did grant all privileges to *.* to dba@"%"

I cannot conntect from anything host as dba.

I then did

grant all privileges to *.* to matt@"jaguar.no-dns.co.uk" (my laptop)

I got the no rows affected message and I cannot connect from the machine
jaguar.

something must be wrong. I used stand the pricinpals of the grant command
but I cannot understand why no rows are affected. It does not surprise me
that I cannot connect when no rows are affected.

any other suggestions.

Thanks for going into detail on this I am learning.

Matt.


-Original Message-
From: Ho, Kam [mailto:[EMAIL PROTECTED]]
Sent: 09 January 2002 16:57
To: '[EMAIL PROTECTED]'; Ho, Kam; Gerald Clark
Cc: MySql List
Subject: RE: command mysql -u $user without typing it 


The "Query OK, 0 rows affected" indicate that the user table is updated so
test2 on localhost can access the mysql databases.

Do you still get the "access is denyed for user @localhostto database mysql"
message?


-Original Message-
From: Matthew Darcy [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 09, 2002 11:51 AM
To: Ho Kam; Gerald Clark
Cc: MySql List
Subject: RE: command mysql -u $user without typing it 


tired that but I am getting

Query OK, 0 rows affected (0.00 sec)


don't understand why it is not granting to the user ?

Matt.


-Original Message-
From: Ho, Kam [mailto:[EMAIL PROTECTED]]
Sent: 09 January 2002 16:10
To: '[EMAIL PROTECTED]'; Gerald Clark
Cc: MySql List
Subject: RE: command mysql -u $user without typing it 


Try this:

mysql> grant all privileges on *.* to test2@"localhost"

also read manual about the grant command.

-Original Message-
From: Matthew Darcy [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 09, 2002 11:04 AM
To: Gerald Clark
Cc: MySql List
Subject: RE: command mysql -u $user without typing it 


I read similar notes on this in the manual, saying that invoking mysql
without the username will try to take the unix username if it exists in the
database.

I tried this with test2, it opened an mysql session no problem, but when I
did a connect mysql it said "access is denyed for user @localhostto database
mysql"

to me this looks like it is trying to connect with no user instead of
test2@localhost

can you explain this ?

Thanks,

Matt.


-Original Message-
From: Gerald Clark [mailto:[EMAIL PROTECTED]]
Sent: 09 January 2002 15:56
To: [EMAIL PROTECTED]
Cc: MySql List
Subject: Re: command mysql -u $user without typing it 




Matthew Darcy wrote:

>
>I have 3 UNIX users on my server.
>
>test1, test2, and test3.
>
>I have created 3 users for mysql test1, test2, and test3 (shock horror)
>
>If I want to connect to the database as root then I understand I must do
>mysql -u root -p
>
>but I would like test1 2 and 3 to be able to type mysql and be either
logged
>in as their unix username, ie test2 types mysql and connects to the
database
>as test2 (mysql account)
>or at least be prompted for the password for test2.
>
>The only way I can think of doing this is by setting up an alias ie
>
>alias mysql_connect=`mysql -u $username -p`
>
>there must be some sort of autologin from username like in oracle.
>
>Thanks,
>
>Matt.
>
The user defaults to the unix user.
You can put a .my.cnf file in their home directory with

[client]
password=whatever

to make it even easier.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.ph

RE: MYSQL : XML storage

[Quentin Bennett]

Hi,

MySQL, being a relational database (quiet, you guys from other camps), is
good at storing relational data.

To store purely XML data, you may be better off using an XML database e.g.
Tomino from Software AG.

The line is a fine one, though. If your XML is purely a representation of
relational data, you may like to look at mysql -X in the latest client,
which, I believe, will produce the results in XML - I haven't tried this.

Regards

Quentin

-Original Message-
From: Clive Bredenkamp [mailto:[EMAIL PROTECTED]]
Sent: Thursday, 10 January 2002 2:52 a.m.
To: '[EMAIL PROTECTED]'
Subject: MYSQL : XML storage


Hi All,

I have about 15GB of xml files each ranging from about 400bytes to 4k (some
exceptions being up to a few MB, but mainly small), and am planning to stick
these files in a database for better mainteance. 

Does anyone have advice on the best way in which to import so many files or
any advice on storage structure.

Thanks,
Clive.


CLIVE BREDENKAMP
IT Dev Consultant
L@wyers Access Web
(031) 502 7630
082 3223 988


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

The information contained in this email is privileged and confidential
and intended for the addressee only. If you are not the intended 
recipient, you are asked to respect that confidentiality and not 
disclose, copy or make use of its contents. If received in error 
you are asked to destroy this email and contact the sender immediately. 
Your assistance is appreciated.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: 2 GB limit reached

[Quentin Bennett]

Hi,

I had this too - in the documentation, although maybe not highlighted
enough, there are instructions for overcoming 4G - you have to set MAX_ROWS
for you table to a 'large' number to tell mysqld to use > 4 byte pointers
for the file.

Regards

Quentin

-Original Message-
From: Chris Wilson [mailto:[EMAIL PROTECTED]]
Sent: Thursday, 10 January 2002 7:07 a.m.
To: Johnny Withers
Cc: [EMAIL PROTECTED]
Subject: Re: 2 GB limit reached



As I said in my message (although I've still not had time to look further
into this) this will not solve your problem completely. My 2.4.17 testbox
will happily create >4gig flies - but as soon as my mysql myisam table
reached 4gig I got a "Table full" error when trying to insert.

Going InnoDB is probably the best solution, from what I've seen
so far - will mysql switch to make that the default table type at some
point in the future?

> Enable large file support:
> 
> [root@xxx array0]# du -h bigfile
> 2.9Gbigfile
> [root@xxx array0]# ls -al bigfile
> -rw-rw-r--1 root root 30 Jan  9 11:06 bigfile
> [root@xxx array0]# uname -a
> Linux [hidden] 2.4.8 #3 SMP Tue Aug 14 09:47:47 CDT 2001 i686 unknown
> 
> 
> -
> Johnny Withers
> [EMAIL PROTECTED]
> p. 601.853.0211
> c. 601.209.4985 
> 
> -Original Message-
> From: Dennis [mailto:[EMAIL PROTECTED]] 
> Sent: Wednesday, January 09, 2002 10:52 AM
> To: [EMAIL PROTECTED]
> Subject: Re: 2 GB limit reached
> 
> 
> At 11:26 PM 01/08/2002, you wrote:
> >On Tue, Jan 08, 2002 at 08:03:07PM -0500, Dennis wrote:
> > > At 07:07 PM 01/08/2002, you wrote:
> > > >Dennis,
> > > >
> > > >You may want to look into using InnoDB tables.  I believe InnoDB
> tables
> > > >are immune to the 2gb limit (which usually comes from the
> filesystem).
> > > >Also, InnoDB claims that the innodb tables are faster than MyISAM
> tables
> > > >in some cases.  See www.innodb.com or
> > > >http://www.mysql.com/doc/I/n/InnoDB_overview.html for further
> detail.
> > >
> > > thanks, but that doesnt tell me how to recover THIS filethe
> right
> > > answer is "use a different OS", but thats out of my control here.
> >
> >Can you at least mysqldump the data out to a file?
> 
> No, but that might be cumbersome with 4 million records. :-)
> 
> db
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>> Trouble
unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php> 
> 


-- 
Chris Wilson <[EMAIL PROTECTED]>
http://www.wapmx.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

The information contained in this email is privileged and confidential
and intended for the addressee only. If you are not the intended 
recipient, you are asked to respect that confidentiality and not 
disclose, copy or make use of its contents. If received in error 
you are asked to destroy this email and contact the sender immediately. 
Your assistance is appreciated.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Help! Undefined symbols while building c++ program using libmysqlclient.a in solaris

[Ho, Kam]

Hi All:
I have problem linking my c++ program in solaris.  The same program link
correctly in Redhat linux.

I'm using mysql 3.23.47 source build using gcc 2.95.3 (source build on
solaris using Sun pre-build gcc 2.95.3)

Here's the output from gcc:
Undefined   first referenced
 symbol in file
socket
/usr/local/lib/mysql/libmysqlclient.a(libmysql.o)
getpeername
/usr/local/lib/mysql/libmysqlclient.a(violite.o)
gethostbyname
/usr/local/lib/mysql/libmysqlclient.a(libmysql.o)
setsockopt
/usr/local/lib/mysql/libmysqlclient.a(violite.o)
getservbyname
/usr/local/lib/mysql/libmysqlclient.a(libmysql.o)
floor
/usr/local/lib/mysql/libmysqlclient.a(password.o)
getsockopt
/usr/local/lib/mysql/libmysqlclient.a(libmysql.o)
inet_addr
/usr/local/lib/mysql/libmysqlclient.a(libmysql.o)
inet_ntoa
/usr/local/lib/mysql/libmysqlclient.a(my_net.o)
shutdown
/usr/local/lib/mysql/libmysqlclient.a(violite.o)
connect
/usr/local/lib/mysql/libmysqlclient.a(libmysql.o)

Thanks in advance
Kam

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

InnoDB : Lock wait timeout exceeded; Try restarting transaction

[Sam Lam]

I recently switched to InnoDB & persistent connections from PHP.

Lately I've been getting these errors "Lock wait timeout exceeded; Try 
restarting transaction" on an UPDATE on table. The system is in 
development so there is at most one other user ( a back end Perl script).

When I switched PHP back to non-persistent connections I stopped getting 
that error.

How does one use persistent PHP connections & InnoDB to avoid this 
error ?


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Random Select Statement - help please

[David V. Edelstein]

Hi Roger,

Thanks for your reply and I apologize for my lack of specificity on my
previous email, thanks for your patience :).
 
I have a people object that has 4 entities. name, pword, user_id, picurl
This data is stored in the people table. Each user is identified by
a unique user_id. I would like to create a mySQL query that will find the
max
user_id, use this value to calculate a random value of the user_id from [1
thru max],
then return the entire row for that person(user_id) filled in with that
persons(row) four entities, 
which I can use to populate the people object.

  select @max:=max(user_id) from people;
  select round((@max-1)*rand()+1) from people limit 1;

Returns: 1 field, 1 record which is empty.

  select round((max(user_id)-1)*rand()+1) from people limit 1;

returns: 1 field, 1 record populated with a random number between 1 and max
of the user_id column.
This is great, but how can I put this together, so that the query will
return 4 fields, 1 record that is populated with the 4 entities for a single
random person? 

Thanks a bunch for your help!

Best regards,
David

ps: When you state @max: does this create a variable @max in a mysql buffer
that you can use in subsequent queries? If so when does this variable
expire? When you close the connection?

-Original Message-
From: Roger Baklund [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 09, 2002 2:40 AM
To: David V. Edelstein
Subject: RE: Random Select Statement??


> Sorry Roger,
> It's not working correctly.
> Please, how can I accomplish the following?
>
> select * from people where user_id=(get the random #) limit 1;

It is difficult to help you when you don't say what the problem is... do you
get an error message? Do you get the wrong result? No result?

These are valid sql statements, but they may need mysql version 3.23 or
later, and both statements must be run within the same session (I don't
think you can use phpadmin or similar, unless multiple commands can be
executed at the same time):

  select @max:=max(user_id) from people;
  select round((@max-1)*rand()+1) from people limit 1;

Your own statement should also work:

  select round((max(user_id)-1)*rand()+1) from people limit 1;

If it doesn't work, something is wrong... ;)

- You must enter the statements in the mysql client
- You must be connected to the correct database
- You must have select privileges on the table
- The table must be named 'people', and the field must be named 'user_id'

--
Roger

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

archive logs

[Chris Pesko]

I'm new to MySQL.  Does MySQL have archive logging and can you recover to 
the point in time of failure in the event of disk crashes?  Also,  Besides 
the manual and classes,  what is the fastest way to get up and running on 
MySQL considering I am a seasoned Oracle DBA.  Are there practical books 
that have backup and recovery scripts available?


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysqlgui

[Sinisa Milivojevic]

chc chc writes:
> Hello,
> while using the MySqlGui 1.7.5, I cannot get the
> "create table Menu Option  (F9)" to work. Is there
> anything I've missed. 
> 
> thanks
> chris
> 

Nope. 

It hass not been implemented.

You can use CREATE TABLE comand ...

-- 
Regards,
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Fulltime Developer
/_/  /_/\_, /___/\___\_\___/   Larnaca, Cyprus
   <___/   www.mysql.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

mysqlgui

[chc chc]

Hello,
while using the MySqlGui 1.7.5, I cannot get the
"create table Menu Option  (F9)" to work. Is there
anything I've missed. 

thanks
chris

__
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: 2 GB limit reached

[Chris Cooper]

I've had no problems with MySQL RAID, and the performance is excellent
as long as you have your indices set up properly. One caveat I might add
is that MySQL RAID does not apply to index files (.MYI). If your index
files are going to grow > 4 GB, you'll need to consider alternate
filesystems anyway.

For your reference, I'm running v3.23.42. My database has one primary
MERGE table which collates the data from 12 month-specific tables. Each
of those month-specific tables uses MySQL RAID with the following
options:

MAX_ROWS=20 PACK_KEYS=1 RAID_TYPE=striped RAID_CHUNKS=32
RAID_CHUNKSIZE=256000

There are currently over 42 million entries in the MERGE table,
occupying over 54 GB of disk. There are also various smaller lookup
tables, etc.

--
coop

> The 2gig limit is a problem that I'm going to hit fairly shortly - perhaps
> someone with a little more knowledge can tell me what the performance will
> be like using mysql's raid rather than OS large file support? Also where
> can one find good information about linux large file support - on my
> slackware 8, 2.4.17, ext2 testbox I can create > 4 gig files using dd but mysql
> failed to create a table greater than that size (not quite sure why it's
> 4gig rather than 2gig - suggests something's working :).



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

reading field and data into a table from another

[P.Agenbag]

Hi, have two tables. One contains the names of ppl along with an id for 
each person and a date of the entry. The second table contains some 
added info for those ppl, and I would like to have one of the fields in 
the 2nd table to be in the first table in irder to make it easier to manage.

The problem is that the order of the ppl in the two tables are not the 
same and it might be that there are more than one entry for a specific 
person ( an update of details on a later date ).
Is there a quick fix sql string for this?



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: 2 GB limit reached

[Chris Wilson]

As I said in my message (although I've still not had time to look further
into this) this will not solve your problem completely. My 2.4.17 testbox
will happily create >4gig flies - but as soon as my mysql myisam table
reached 4gig I got a "Table full" error when trying to insert.

Going InnoDB is probably the best solution, from what I've seen
so far - will mysql switch to make that the default table type at some
point in the future?

> Enable large file support:
> 
> [root@xxx array0]# du -h bigfile
> 2.9Gbigfile
> [root@xxx array0]# ls -al bigfile
> -rw-rw-r--1 root root 30 Jan  9 11:06 bigfile
> [root@xxx array0]# uname -a
> Linux [hidden] 2.4.8 #3 SMP Tue Aug 14 09:47:47 CDT 2001 i686 unknown
> 
> 
> -
> Johnny Withers
> [EMAIL PROTECTED]
> p. 601.853.0211
> c. 601.209.4985 
> 
> -Original Message-
> From: Dennis [mailto:[EMAIL PROTECTED]] 
> Sent: Wednesday, January 09, 2002 10:52 AM
> To: [EMAIL PROTECTED]
> Subject: Re: 2 GB limit reached
> 
> 
> At 11:26 PM 01/08/2002, you wrote:
> >On Tue, Jan 08, 2002 at 08:03:07PM -0500, Dennis wrote:
> > > At 07:07 PM 01/08/2002, you wrote:
> > > >Dennis,
> > > >
> > > >You may want to look into using InnoDB tables.  I believe InnoDB
> tables
> > > >are immune to the 2gb limit (which usually comes from the
> filesystem).
> > > >Also, InnoDB claims that the innodb tables are faster than MyISAM
> tables
> > > >in some cases.  See www.innodb.com or
> > > >http://www.mysql.com/doc/I/n/InnoDB_overview.html for further
> detail.
> > >
> > > thanks, but that doesnt tell me how to recover THIS filethe
> right
> > > answer is "use a different OS", but thats out of my control here.
> >
> >Can you at least mysqldump the data out to a file?
> 
> No, but that might be cumbersome with 4 million records. :-)
> 
> db
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>> Trouble
unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php> 
> 


-- 
Chris Wilson <[EMAIL PROTECTED]>
http://www.wapmx.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Special chars and ORDER BY

[Christian L.]

Hello!
I use a MySQL database table to store the name of several countries in
several languages. This table is used to create a country list for a
registration form on a website. As the list contains the names in several
languages they are sorted alphabetically only in one language but not in
all of them. Now I just included an "ORDER BY country" into the SQL query
I use to fetch the data from the table in the database. However, there's a
problem with the German countrie names. There are several names that start
with special German characters (which are called umlauts). As they look
like an A with two dots, an O with two dots and an U with two dots you'd
usually find between the words starting with A, O and U in German
alphabetically sorted lists but of course this doesn't work with MySQL.
The problem is that they now appear at the end of the list but nobody will
ever find them there. Can anyone give me a hint how to solve this problem?

Christian

-- 
They that can give up liberty to obtain a little temporary safety
deserve neither liberty nor safety.
Benjamin Franklin

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: AS SEEN ON NATIONAL TV: MAKE OVER $500,000 EVERY 20 WEEKS!!

[Todd Williamsen]

I deleted it too!  Guess what, I don't miss it either!

-Original Message-
From: Reports [mailto:[EMAIL PROTECTED]] 
Sent: Wednesday, January 09, 2002 11:12 AM
To: [EMAIL PROTECTED]
Subject: AS SEEN ON NATIONAL TV: MAKE OVER $500,000 EVERY 20 WEEKS!!

Dear Friend:

The first time I received this in my e-mail I deleted it! And later 
wished I had it back! I had to wait months before someone E-mailed me 
again with this offer! I always thought I would wait until later to try
this. I 
wish I had done it sooner now! Don't do what I did! Make Sure you Go For

It! I personally thought that this would not work! But just like many of
you, I was 
involved in MLM marketing, which in most cases did not work! I was 
spending from $20 to $200 a month on FFA pages and other things to get 
leads to market my product. Unfortunately, as I'm sure many of you have
no
doubt 
noticed, most people who post to theses pages are like you and I. They 
have a product to sell! They're not there to BUY! So most of us end up 
spinning our wheels and getting NO WHERE! SO I thought when I came
across
this 
E-Mail... HECK, What did I have to lose? It was a lot less money than
what
I had 
been spending, and it was only $25.00. I thought even if it did not
work, I 
have already lost a lot more money than that from other MLM and scams on
the 
net, what was another $25.00?  And I did see this on T.V. So I gave it a
shot. 
AND BOY I AM GLAD I DID The first month only a 
few hundred came in, but the following month  I could not believe it the
response!  
It really worked!!! So now I am going to run it one more time! And I Am
sure it will 
be just as good as the first time! That is what is nice about this
program...you 
can start over and over again once you're off the 5th level. If you're 
A GOOD HONEST PERSON IT WILL WORK FOR YOU TOO!! 
GO FOR IT! MAKE YOUR DREAMS COME TRUE!

AS SEEN ON NATIONAL TV:

Making over half million dollars every 4 to 5 months from your home for 
A one-time investment of only $25 U.S.!!
 
Before you say ''BULL'', please read the following. This is the letter 
you have been hearing about on the news lately. Due to the popularity 
of this letter on the Internet, a national weekly news program recently
devoted 
an entire show to the investigation of this program described below, to 
see if it really can make people money. The show also investigated
whether
or 
not the program was legal. Their findings proved once and for all that 

there are ''absolutely NO Laws prohibiting the participation in the
program and 
if people can -follow the simple instructions, they are bound to make 
some mega bucks with only $25 out of pocket cost''!

===

This is what one participant had to say: '' Thanks to this profitable
opportunity. 
I was approached many times before but each time I passed on it. I am 
so glad I finally joined just to see what one could expect in return for
the 
minimal effort and money required. To my astonishment, I received total 
$610,470.00 in 21 weeks, with money still coming in''.

Pam Hedland, Fort Lee, New Jersey.

===

Here is another testimonial: ''' This program has been around for a 
long time but I never believed in it. But one day when I received this 
again in the mail I decided to gamble my $25 on it. I followed the
simple 
instructions and whalaa . 3 weeks later the money started to come
in.
First month I only made $240.00 but the next 2 months after that I made 
a total of $290,000.00. So far, in the past 8 months by re-entering the
program, I have made over $710,000.00 and I am playing it again. The 
key to success in this program is to follow the simple steps and NOT 
change anything.'' 

More testimonials later but first:

= PRINT THIS NOW FOR YOUR FUTURE REFERENCE 



If you would like to make at least $500,000 every 4 to 5 months easily 
and comfortably, please read the following...THEN READ IT AGAIN and
AGAIN!!! 



FOLLOW THESE SIMPLE INSTRUCTION AND YOUR FINANCIAL DREAMS WILL COME
TRUE.GUARANTEED! 


=ORDER ALL 5 REPORTS SHOWN ON THE LIST BELOW =

For each report, send $5 CASH, THE NAME & NUMBER OF THE REPORT YOU ARE
ORDERING and YOUR E-MAIL ADDRESS to the person whose name appears 
ON THAT LIST next to the report. MAKE SURE YOUR RETURN ADDRESS IS ON 
YOUR ENVELOPE TOP LEFT CORNER in case of any mail problems. 

===

When you place your order, make sure you order each of the 5 reports. 
You will need all 5 reports so that you can save them on your computer 
and resell them. YOUR TOTAL COST $5 X 5=$25.00.

Within a few days you will receive, via e-mail, each of the 5 reports 
from these 5 different individuals. Save them on your computer

Re: 2 GB limit reached

[Jeremy Zawodny]

On Wed, Jan 09, 2002 at 12:23:27PM -0500, Dennis wrote:

> is the 2GB limit only in linux 2.2? (ie does 2.4 not have that
> limit?).  Running a 2.4 kernel is the easiest option in that case.

Right.
-- 
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878   Fax: (408) 349-5454   Cell: (408) 685-5936

MySQL 3.23.41-max: up 6 days, processed 161,378,983 queries (276/sec. avg)

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: How Query and Fetch work?

[Stephen Abshire]

I like the explanation using the automobile and key. Very good :-) Yet at 
the same time if I understand what effect using the different grades of fuel 
in my car will have on its performance, I can optimize how the car operates. 
Much like the original question, if I know when MySQL caches result sets and 
when it is forced to hit the database again I can optimize the performance 
of my application.

Personally, I would like to know the answer to this question myself. Anyone 
know the internals?


Original Message Follows
From: Dibo Chen <[EMAIL PROTECTED]>
To: Alex Shi <[EMAIL PROTECTED]>
CC: [EMAIL PROTECTED]
Subject: Re: How Query and Fetch work?
Date: Wed, 09 Jan 2002 10:45:07 -0600

When you are told to fill in fuel and turn the key to drive, I suppose
you don't care how the fuel runs the car. Do the same things in any lib
you use, pushing the "buttons" in told order would  work. Well, you may
dig deeper if you like since the code is available.

Alex Shi wrote:
 >
 > Yesterday I posted a question yet got response. The question
 > is regarding to how Query works. Now I repost it in a more
 > specific way.
 >
 > I am just wondering how MySQL API functions work. Let's look
 > at following two functions:
 >
 > 1. mysql_query(),
 > 2. mysql_fetch_array()
 >
 > To my understanding, mysql_query() will definately to its job with
 > MySQL server. But how about the latter? Does it just fetch data
 > from client/local buffer, which is previously put in by mysql_query(),
 > or still has to goto server side to fetch data?
 >
 > Alex
 >
 > -
 > Before posting, please check:
 >http://www.mysql.com/manual.php   (the manual)
 >http://lists.mysql.com/   (the list archive)
 >
 > To request this thread, e-mail <[EMAIL PROTECTED]>
 > To unsubscribe, e-mail <[EMAIL PROTECTED]>
 > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail 
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


_
MSN Photos is the easiest way to share and print your photos: 
http://photos.msn.com/support/worldwide.aspx


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Complicated Query?

[Rick Emery]

Needs to be done programmatically

-Original Message-
From: Jerry Rehak [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 09, 2002 10:53 AM
To: [EMAIL PROTECTED]
Subject: Complicated Query?


I have a table with the columns names and id.  I want to be able to find all
names with an id of '03' that do not have other records with id values of
'10','20' or '37'.

Is this even possible to do?

name   id
a03
a11
a12
a13 I want 'a' because it has a 03 and not a 10, a 20, OR 37
b03
b10 I don't want 'b' because it has a 10
c04
c11
c20 I don't want 'c' because it doesn't have a 03
d03 I want 'd' because it has a 03 and no other records

Thanks for your help


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Linux Install Problem

[Kevin Smith]

Hi,

Can anyone shed some light on this problem I got when trying to compile on a
Linux platform?

/usr/local/mysql/lib/mysql
../libmysql/.libs/libmysqlclient.so: undefined reference to `atexit'
collect2: ld returned 1 exit status

Thanks,

Kevin


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Error 13

[P.Agenbag]

Hi
I have a table that I want to add a new field to while running on the 
server, however, when I try to add a new field, it comes up with the 
error13 message, saying it doesn't have permission, yet the files and 
folder are all chmod 777. Can it be due to the fact that there is a 
permanent connection to this table from another server and that mysql is 
preventing any major changes?
Should I just try to make a copy of the table and make the changes to it 
and then overwrite the old one?

Is there a "cleaner way of managing your tables and to make changes 
without having to resort to this "manual way"?

Thanks



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: How Query and Fetch work?

[Nally, Tyler G.]

Basically, the "mysql_query" submits the sql statement
to the database engine, and the "mysql_fetch_array" 
allows retrieval of selected information returned from
the query into an associative array with each columnname 
of the query an associative "key" in the array 

Example...

  --login-to-host-and-db-connectors-here--

  $sql  = "select name, address, city, state, zip ";
  $sql .= "from addressbook ";

  $sql_result = mysql_query($sql) or die ("bad SQL [$sql]");

  --print beginning html table tags and first row headers here---

  while($data_row = mysql_fetch_array($sql_result))
{
 $sel_name= $data_row["name"];
 $sel_address = $data_row["address"];
 $sel_city= $data_row["city"];
 $sel_state   = $data_row["state"];
 $sel_zip = $data_row["zip"];

 // my personal preference is to use simple fields for
 // display purposes.  I could have easily used the 
 // array names as well... such as $data_row["name"] for $sel_name

 echo("$sel_name");
 echo("$sel_address");
 echo("$sel_city");
 echo("$sel_state");
 echo("$sel_zip");
}

   --print closing html table tags---

  

> -Original Message-
> From: Dibo Chen [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 09, 2002 11:45 AM
> To: Alex Shi
> Cc: [EMAIL PROTECTED]
> Subject: Re: How Query and Fetch work?
> 
> 
> When you are told to fill in fuel and turn the key to drive, I suppose
> you don't care how the fuel runs the car. Do the same things 
> in any lib
> you use, pushing the "buttons" in told order would  work. 
> Well, you may
> dig deeper if you like since the code is available.
> 
> Alex Shi wrote:
> > 
> > Yesterday I posted a question yet got response. The question
> > is regarding to how Query works. Now I repost it in a more
> > specific way.
> > 
> > I am just wondering how MySQL API functions work. Let's look
> > at following two functions:
> > 
> > 1. mysql_query(),
> > 2. mysql_fetch_array()
> > 
> > To my understanding, mysql_query() will definately to its job with
> > MySQL server. But how about the latter? Does it just fetch data
> > from client/local buffer, which is previously put in by 
> mysql_query(),
> > or still has to goto server side to fetch data?
> > 
> > Alex
> > 
> > 
> -
> > Before posting, please check:
> >http://www.mysql.com/manual.php   (the manual)
> >http://lists.mysql.com/   (the list archive)
> > 
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: 
http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: 2 GB limit reached

[Dennis]

At 12:09 PM 01/09/2002, Dan Nelson wrote:
>In the last episode (Jan 08), Dennis said:
> > We have a database that seems to have grown too large, and now any
> > operation fails on it. How can we fix this?
>
>If you can SELECT from it, create a new MYISAM table with the RAID
>option, or create an INNODB table with multiple 2gb tablespace files.
>Then "INSERT INTO newtable SELECT * FROM oldtable".
>
>If you can't select from it, you might have to run myisamchk to repair
>the table, or transfer the table to an OS that does not have the 2gb
>limit and split the table there.
>
>--
> Dan Nelson
> [EMAIL PROTECTED]


is the 2GB limit only in linux 2.2? (ie does 2.4 not have that limit?). 
Running a 2.4 kernel is the easiest option in that case.

Dennis



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: How to send multiple statements to the server?

[Rick Emery]

Can't be done.  I know.  I tried.  I asked the same question several months
ago.
I wish we could do so.

-Original Message-
From: root [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 09, 2002 11:36 AM
To: [EMAIL PROTECTED]
Subject: How to send multiple statements to the server?


Hi!
i'm using mysql 3.23.47 and i'm triing to send 2 or more statements in
one string separated with ';' but i does not work.
some ideas?

blestan


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

AS SEEN ON NATIONAL TV: MAKE OVER $500,000 EVERY 20 WEEKS!!

[Reports]

Dear Friend:

The first time I received this in my e-mail I deleted it! And later 
wished I had it back! I had to wait months before someone E-mailed me 
again with this offer! I always thought I would wait until later to try
this. I 
wish I had done it sooner now! Don't do what I did! Make Sure you Go For 
It! I personally thought that this would not work! But just like many of
you, I was 
involved in MLM marketing, which in most cases did not work! I was 
spending from $20 to $200 a month on FFA pages and other things to get 
leads to market my product. Unfortunately, as I'm sure many of you have no
doubt 
noticed, most people who post to theses pages are like you and I. They 
have a product to sell! They're not there to BUY! So most of us end up 
spinning our wheels and getting NO WHERE! SO I thought when I came across
this 
E-Mail... HECK, What did I have to lose? It was a lot less money than what
I had 
been spending, and it was only $25.00. I thought even if it did not work, I 
have already lost a lot more money than that from other MLM and scams on
the 
net, what was another $25.00?  And I did see this on T.V. So I gave it a
shot. 
AND BOY I AM GLAD I DID The first month only a 
few hundred came in, but the following month  I could not believe it the
response!  
It really worked!!! So now I am going to run it one more time! And I Am
sure it will 
be just as good as the first time! That is what is nice about this
program...you 
can start over and over again once you're off the 5th level. If you're 
A GOOD HONEST PERSON IT WILL WORK FOR YOU TOO!! 
GO FOR IT! MAKE YOUR DREAMS COME TRUE!

AS SEEN ON NATIONAL TV:

Making over half million dollars every 4 to 5 months from your home for 
A one-time investment of only $25 U.S.!!
 
Before you say ''BULL'', please read the following. This is the letter 
you have been hearing about on the news lately. Due to the popularity 
of this letter on the Internet, a national weekly news program recently
devoted 
an entire show to the investigation of this program described below, to 
see if it really can make people money. The show also investigated whether
or 
not the program was legal. Their findings proved once and for all that 

there are ''absolutely NO Laws prohibiting the participation in the
program and 
if people can -follow the simple instructions, they are bound to make 
some mega bucks with only $25 out of pocket cost''!

===

This is what one participant had to say: '' Thanks to this profitable
opportunity. 
I was approached many times before but each time I passed on it. I am 
so glad I finally joined just to see what one could expect in return for
the 
minimal effort and money required. To my astonishment, I received total 
$610,470.00 in 21 weeks, with money still coming in''.

Pam Hedland, Fort Lee, New Jersey.

===

Here is another testimonial: ''' This program has been around for a 
long time but I never believed in it. But one day when I received this 
again in the mail I decided to gamble my $25 on it. I followed the simple 
instructions and whalaa . 3 weeks later the money started to come in.
First month I only made $240.00 but the next 2 months after that I made 
a total of $290,000.00. So far, in the past 8 months by re-entering the
program, I have made over $710,000.00 and I am playing it again. The 
key to success in this program is to follow the simple steps and NOT 
change anything.'' 

More testimonials later but first:

= PRINT THIS NOW FOR YOUR FUTURE REFERENCE 



If you would like to make at least $500,000 every 4 to 5 months easily 
and comfortably, please read the following...THEN READ IT AGAIN and
AGAIN!!! 



FOLLOW THESE SIMPLE INSTRUCTION AND YOUR FINANCIAL DREAMS WILL COME
TRUE.GUARANTEED! 


=ORDER ALL 5 REPORTS SHOWN ON THE LIST BELOW =

For each report, send $5 CASH, THE NAME & NUMBER OF THE REPORT YOU ARE
ORDERING and YOUR E-MAIL ADDRESS to the person whose name appears 
ON THAT LIST next to the report. MAKE SURE YOUR RETURN ADDRESS IS ON 
YOUR ENVELOPE TOP LEFT CORNER in case of any mail problems. 

===

When you place your order, make sure you order each of the 5 reports. 
You will need all 5 reports so that you can save them on your computer 
and resell them. YOUR TOTAL COST $5 X 5=$25.00.

Within a few days you will receive, via e-mail, each of the 5 reports 
from these 5 different individuals. Save them on your computer so they 
will be accessible for you to send to the 1,000's of people who will order
them 
from you. Also make a floppy of these reports and keep it on your desk 
in case something happens to your computer.

IMPORTANT - DO NOT alter the names of the people who

INSERTing into joined tables?

[Erik Price]

Hello, everyone --
I have received a great deal of help from many members of this list, so 
I'd like to acknowledge that now.  Someday, when I have my head wrapped 
around this stuff, I hope to return the favor.  In the meantime, 
however, I have come across my worst brainbuster yet.  Any help on this 
is greatly appreciated.


I constructed my tables in the most "normalized" way that I could 
(without overdoing it), so that in some cases, there is no -direct- 
relationship between tables.  That is, I have a table called "people" 
and a table called "files", and a foreign key table called "filespeople":

Database: medialab_db  Table: people
+-+--+--+-+
| Field   | Type | Null | Key |
+-+--+--+-+
| people_id   | smallint(5) unsigned |  | PRI | (auto-incremented)
| first_name  | varchar(36)  |  | |
| last_name   | varchar(36)  |  | |
+-+--+--+-+

Database: medialab_db  Table: files
+-+---+--+-+
| Field   | Type  | Null | Key |
+-+---+--+-+
| file_id | mediumint(8) unsigned |  | PRI | (auto-incremented)
| file_name   | varchar(64)   |  | |
+-+---+--+-+

Database: medialab_db  Table: filespeople
+---+---+--+-+
| Field | Type  | Null | Key |
+---+---+--+-+
| file_id   | mediumint(8) unsigned |  | PRI |
| people_id | smallint(5) unsigned  |  | PRI |
+---+---+--+-+

The relationship, in real life, is that I would like to establish 
many-to-many relationships between "files" records and "people" records, 
so that a record in "files" would be associated with several people from 
"people".  There is a foreign key table called "filespeople".  The SQL 
used to write a SELECT statement would use the join like so:

SELECT files.file_name, people.first_name, people.last_name
FROM files, people, filespeople
WHERE files.file_name = $filename
AND files.file_id = filespeople.file_id
AND people.people_id = filespeople.people_id
(the $filename variable is a user-selected variable, I'm using PHP)

So I designed my "files" and "people" tables without any direct 
relationship with one another, thinking to link them with the SELECT 
statement.

What I completely forgot, up until this point, was that I would need to 
INSERT these records (from pre-written HTML/PHP forms), and there is no 
WHERE clause in the INSERT statement to keep everything together.  In my 
scenario, a user might add a record to "files" and wish to associate 
that record to some of the records in "people", either new or 
pre-existing (typed into an HTML text input form or something).  How 
should SQL code be arranged to "link" these records over the foreign key 
table?

INSERT INTO files (file_name) VALUES ("$filename")

and

INSERT INTO people (first_name, last_name) VALUES ("$firstname", 
"$lastname")

but... to keep it all together... is lost on me... and then later to 
have UPDATE statements to do the same thing!  Although I suspect this 
may be easier as I can use the WHERE clause in an UPDATE statement.

If anyone has a link to a tutorial on this very concept, that would be 
greatly appreciated as well!


Thank you,
Erik


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: 2 GB limit reached

[Johnny Withers]

Enable large file support:

[root@xxx array0]# du -h bigfile
2.9Gbigfile
[root@xxx array0]# ls -al bigfile
-rw-rw-r--1 root root 30 Jan  9 11:06 bigfile
[root@xxx array0]# uname -a
Linux [hidden] 2.4.8 #3 SMP Tue Aug 14 09:47:47 CDT 2001 i686 unknown


-
Johnny Withers
[EMAIL PROTECTED]
p. 601.853.0211
c. 601.209.4985 

-Original Message-
From: Dennis [mailto:[EMAIL PROTECTED]] 
Sent: Wednesday, January 09, 2002 10:52 AM
To: [EMAIL PROTECTED]
Subject: Re: 2 GB limit reached


At 11:26 PM 01/08/2002, you wrote:
>On Tue, Jan 08, 2002 at 08:03:07PM -0500, Dennis wrote:
> > At 07:07 PM 01/08/2002, you wrote:
> > >Dennis,
> > >
> > >You may want to look into using InnoDB tables.  I believe InnoDB
tables
> > >are immune to the 2gb limit (which usually comes from the
filesystem).
> > >Also, InnoDB claims that the innodb tables are faster than MyISAM
tables
> > >in some cases.  See www.innodb.com or
> > >http://www.mysql.com/doc/I/n/InnoDB_overview.html for further
detail.
> >
> > thanks, but that doesnt tell me how to recover THIS filethe
right
> > answer is "use a different OS", but thats out of my control here.
>
>Can you at least mysqldump the data out to a file?

No, but that might be cumbersome with 4 million records. :-)

db


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: 2 GB limit reached

[Dan Nelson]

In the last episode (Jan 08), Dennis said:
> We have a database that seems to have grown too large, and now any
> operation fails on it. How can we fix this?

If you can SELECT from it, create a new MYISAM table with the RAID
option, or create an INNODB table with multiple 2gb tablespace files. 
Then "INSERT INTO newtable SELECT * FROM oldtable".

If you can't select from it, you might have to run myisamchk to repair
the table, or transfer the table to an OS that does not have the 2gb
limit and split the table there.

-- 
Dan Nelson
[EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: How to send multiple statements to the server?

[Roger Baklund]

* blestan (root)
> i'm using mysql 3.23.47 and i'm triing to send 2 or more statements in
> one string separated with ';' but i does not work.
> some ideas?

Where are you trying to send it to/from?

The mysql client (or 'monitor') accepts statements separated by ";", but for
instance the PHP function mysql_query() does not.

--
Roger


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: How to send multiple statements to the server?

[Sun]

Hi;
Mysql is not like the Linux/Unix command line. You can't perform such an operation.

Sun

root wrote:

> Hi!
> i'm using mysql 3.23.47 and i'm triing to send 2 or more statements in
> one string separated with ';' but i does not work.
> some ideas?
>
> blestan
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: command mysql -u $user without typing it ????

[Ho, Kam]

The "Query OK, 0 rows affected" indicate that the user table is updated so
test2 on localhost can access the mysql databases.

Do you still get the "access is denyed for user @localhostto database mysql"
message?


-Original Message-
From: Matthew Darcy [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 09, 2002 11:51 AM
To: Ho Kam; Gerald Clark
Cc: MySql List
Subject: RE: command mysql -u $user without typing it 


tired that but I am getting

Query OK, 0 rows affected (0.00 sec)


don't understand why it is not granting to the user ?

Matt.


-Original Message-
From: Ho, Kam [mailto:[EMAIL PROTECTED]]
Sent: 09 January 2002 16:10
To: '[EMAIL PROTECTED]'; Gerald Clark
Cc: MySql List
Subject: RE: command mysql -u $user without typing it 


Try this:

mysql> grant all privileges on *.* to test2@"localhost"

also read manual about the grant command.

-Original Message-
From: Matthew Darcy [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 09, 2002 11:04 AM
To: Gerald Clark
Cc: MySql List
Subject: RE: command mysql -u $user without typing it 


I read similar notes on this in the manual, saying that invoking mysql
without the username will try to take the unix username if it exists in the
database.

I tried this with test2, it opened an mysql session no problem, but when I
did a connect mysql it said "access is denyed for user @localhostto database
mysql"

to me this looks like it is trying to connect with no user instead of
test2@localhost

can you explain this ?

Thanks,

Matt.


-Original Message-
From: Gerald Clark [mailto:[EMAIL PROTECTED]]
Sent: 09 January 2002 15:56
To: [EMAIL PROTECTED]
Cc: MySql List
Subject: Re: command mysql -u $user without typing it 




Matthew Darcy wrote:

>
>I have 3 UNIX users on my server.
>
>test1, test2, and test3.
>
>I have created 3 users for mysql test1, test2, and test3 (shock horror)
>
>If I want to connect to the database as root then I understand I must do
>mysql -u root -p
>
>but I would like test1 2 and 3 to be able to type mysql and be either
logged
>in as their unix username, ie test2 types mysql and connects to the
database
>as test2 (mysql account)
>or at least be prompted for the password for test2.
>
>The only way I can think of doing this is by setting up an alias ie
>
>alias mysql_connect=`mysql -u $username -p`
>
>there must be some sort of autologin from username like in oracle.
>
>Thanks,
>
>Matt.
>
The user defaults to the unix user.
You can put a .my.cnf file in their home directory with

[client]
password=whatever

to make it even easier.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Complicated Query?

[Jerry Rehak]

I have a table with the columns names and id.  I want to be able to find all
names with an id of '03' that do not have other records with id values of
'10','20' or '37'.

Is this even possible to do?

name   id
a03
a11
a12
a13 I want 'a' because it has a 03 and not a 10, a 20, OR 37
b03
b10 I don't want 'b' because it has a 10
c04
c11
c20 I don't want 'c' because it doesn't have a 03
d03 I want 'd' because it has a 03 and no other records

Thanks for your help


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php