Re users in mysql

[Peter Goggin]

I have set up mysql under redhat 7.3 and imported the database from mysql
running on windows.

I have the root user running using passwords, but i cannot get the other
users to log on even thogh the passwords shoudl have been carrid across with
the import.

I have tried to reset the user passwords but still cannot get a logon.

For exmple if i log on and change a user password with:

GRANT INSERT ON stamps.customers TO reguser@'%'
IDENTIFIED BY 'regpassword';
 should let me log on as

mysql -D stamps -u reguser -pregpassword

I get the error message:
ERROR 1045 Access denied for user 'reguser@localhost' (Using password = YES)

I have tried changing the password with
update mysql.user set password = PASSWORD('regpassword')  and this does not
help

Can anyone suggest what I need todo?

Do I need to drop the users and run the full set of grant scripts again?

Regards

Peter Goggin


-
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

deliver-problem to this list

[hans schneidhofer]

hi,hope anybody can help :
since a few days I have more and more trouble with sending and receiving
mails from  and to this list.
sending to results very often in such a message :
Von:Mailer-Daemon [EMAIL PROTECTED]
An: [EMAIL PROTECTED]
Betreff:NDN: ERROR 1146
Datum:  23 Sep 2002 06:03:54 +0100  
Sorry. Your message could not be delivered to:

Ian Fieldhouse (Mailbox or Conference is full.)


or the next :
Von:[EMAIL PROTECTED]
An: [EMAIL PROTECTED]
Betreff:Nondeliverable mail
Datum:  23 Sep 2002 00:02:06 +0200  
--Transcript of session follows ---
[EMAIL PROTECTED]
The system cannot find the path specified.




Von:[EMAIL PROTECTED]
Antwort an: [EMAIL PROTECTED]
An: [EMAIL PROTECTED]
Betreff:[expert] new behavior in /var/www/html ?
Datum:  22 Sep 2002 15:25:59 +0200  

and for receiving post, I got a mail tomorrow like this here :
Von:[EMAIL PROTECTED]
An: [EMAIL PROTECTED]
Betreff:ezmlm warning
Datum:  22 Sep 2002 21:09:39 +  
Hi! This is the ezmlm program. I'm managing the
[EMAIL PROTECTED] mailing list.


Messages to you from the mysql mailing list seem to
have been bouncing. I've attached a copy of the first bounce
message I received.

If this message bounces too, I will send you a probe. If the probe
bounces,
I will remove your address from the mysql mailing list,
without further notice.

has anyone an idea, whats going on here - 
I'm sure, I don't have made any changes in my adressbox, but now I get
more and more errors like this here.
hope anyone has an idea
bye hans




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

[hans schneidhofer]

hi list,
trying to do my first steps with mysql, i have done the following :
mysql USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql INSERT INTO mysql VALUES(localhost, sample_db,
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
ERROR 1146: Table 'mysql.mysql' doesn't exist

hmm - what I have done wrong here ? or do I have to do something before
?

these steps are a piece of description in a mysql-book mysql in 21
days and 

thanks for helping
bye hans





-
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

Case sensitivety behaviour

[Moestl, Wolfgang]

Hi,

Is there a defined behaviour for handling the case-sensitivety for user- and hostnames?
I did not find any information on this at the docs for the 4.0.3 MAX version which I'm 
using.


For example, the following database connections are valid:
root@localhost
Root@localhost

However, the following connection is resulting in an Connection failed: 1045 - Accss 
denied for user: ... error:
ROOT@localhost
ROot@localhost

It looks like the host parameter is completely insensitive for cases, every 
combination of upper- and lowercase characters it taken.


To get it even more confusing, the values for user and host at the SHOW GRANTS FOR 
[user]@[host] are BOTH FULLY case-sensitive.
Only the exact values as beeing stored at the mysql system tables are taken.

The following statements does return values:
SHOW GRANTS FOR root@localhost

The following statements will NOT return values:
SHOW GRANTS for Root@localhost
SHOW GRANTS for root@Localhost

Personnaly I think the same case-sensitivety behaviour should be implemented in every 
aspect of the database functionallity.
If connecting as Root@LOCalhOST works a SHOW GRANTS FOR Root@LOCalhOST should work as 
well.

Best regards,
Wolf



-
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

Bug at Hostname parameter for SHOW GRANTS FOR ...?

[Moestl, Wolfgang]

Hi,

If you have a host having the minus character '-' in the name the mysql command SHOW 
GRANTS FOR fails.

Example:

SHOW GRANTS FOR root@MY-COMUPTER results in the error message
You have an error in your SQL syntax near '-COMPUTER' at line 1.

It looks like the command processor stops the parsing for the hostname at the '-' 
character and tries to find an appropriate command sequence afterwards.

Is this a Bug or expected behaviour?

Best regards,
Wolf



-
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 select (not a join)

[neal]

How do you select from two tables, without joining those tables?

I just want the results of one table slapped on the end of the results from
another table because I don't want the overheard of running two seperate
queries.  For instance:

Lets say that I have these two tables (and thur values):
Foo Bar
a1   b1   a3b3  c3
a2   a2 a4  b4  c4

I would like a table (resultset) returned something like this.  Of course it
would need an additional field to specify which table the values came from:

a1 b1foo
a2 b2foo
a3 b3 c3 bar
a4 b4 c4 bar

Anyone know how to do a query like this?

Thanks.
Neal


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

[Max Morawski]

hans schneidhofer wrote:

hi list,
trying to do my first steps with mysql, i have done the following :
mysql USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql INSERT INTO mysql VALUES(localhost, sample_db,
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
ERROR 1146: Table 'mysql.mysql' doesn't exist
  

With 'use' you're changing *databases*. You need to create a *table* to 
insert data (see 'CREATE TABLE...').

Max



-
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: transactions...

[Thomas Seifert]

On Mon, 23 Sep 2002 08:40:59 +0300 (EEST) Iikka Meriläinen 
[EMAIL PROTECTED] wrote:

 On Mon, 23 Sep 2002, Daniel Kiss wrote:
 
  Hi!
 
  At 00:56 2002.09.23._ -0300, you wrote:
  Do I loose
  too much performance using InnoDB tables in autocommit mode instead of
  using MyISAM tables?
 
  The real quiestion is: Why do you want to use InnoDB tables when you don't
  want to use its transaction safe features?
 
 The reason for prefering InnoDB over MyISAM? Performance. Most benchmarks
 show InnoDB is slightly faster than MyISAM, and not to mention its some
 other benefits.
 
 But the performance could be a reason on its own to use InnoDB. I don't
 mean that MyISAM is bad, not at all. It's simpler to use, after all. But
 what I mean is that InnoDB can be used in all tasks MyISAM is suitable
 for, and some tasks will be done faster using InnoDB.

But some tasks are really bad in InnoDB, at most selects and worst if it
comes to table-scans.


Thomas

mysql, query

-
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: multi-table select (not a join)

[neal]

Oh man!

Yeah, you're right.  That's exactly what I want but apparently it wasn't
implemented until v4??!?!?!

What did people do prior to version 4 when needing to query multiple tables?
Just endure the overhead of multiple connections to the database?

Thanks.
Neal

-Original Message-
From: Herman Verkade [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 23, 2002 12:14 AM
To: 'neal'
Subject: RE: multi-table select (not a join)


You want to do a UNION. See:
http://www.mysql.com/doc/en/UNION.html

Regards,
Herman Verkade


 -Original Message-
 From: neal [mailto:[EMAIL PROTECTED]]
 Sent: 23 September 2002 07:57
 To: mySQL
 Subject: multi-table select (not a join)


 How do you select from two tables, without joining those tables?

 I just want the results of one table slapped on the end of
 the results from
 another table because I don't want the overheard of running
 two seperate
 queries.  For instance:

 Lets say that I have these two tables (and thur values):
 Foo   Bar
 a1   b1   a3  b3  c3
 a2   a2   a4  b4  c4

 I would like a table (resultset) returned something like
 this.  Of course it
 would need an additional field to specify which table the
 values came from:

 a1 b1foo
 a2 b2foo
 a3 b3 c3 bar
 a4 b4 c4 bar

 Anyone know how to do a query like this?

 Thanks.
 Neal


 -
 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

Sudden problem with Grant Tables on Windows NT

[José Fernández]

I have installed MySQL on a PC with Windows NT. As the
MySQL dir is not on c:, I have copied the 'my.cnf'
file to c:, and I have edited it to MySQL work with
InnoDB tables. All has been working fine, but
suddendly, without change any, when I have tried to
start up MySQL with the command mysqld-max
--standalone (as usually) I have got the message:
mysqld-max: Table 'mysql.host' doesn't exist
in the 'mysql.err' file.

I have found that I start up MySQL with: mysqld-max
-Sg --standalone, then it works.

How can I solve the problem to be able to start up
mysqld-max with the full functionality (without use
-Sg option)?

I was thinking that the origin of this problem was
that I have not privilege tables on my PC. But I think
also that when I install MySQL on Windows NT, the
privilege table are being creatend during the
installation process (MySQL was woriking fine at the
beginning).
Please, can you help me?

Thanks in advance

José Fernández


___
Yahoo! Messenger
Nueva versión: Webcam, voz, y mucho más ¡Gratis! 
Descárgalo ya desde http://messenger.yahoo.es

-
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

MyISAM / Performance / Nb of table per DataBase

[David Bordas]

Hi list,

I still have performance problem with my big MyISAM table.
In fact, mysql keep locking my table, even for doing select :(
I don't know why but it's very very annoying.
1% of my query are search ones.
Theses queries are slow, too slow.

So i'm thinking about splitting my table to lot of little tables.
Can i have around 5000 Tables under the same database ?
Or should i keep my big table and do not split ?

Thanks
David

Config :
Mysql 3.23.45 ( tar.gz binary )
Linux Red-hat 7.1
kernel 2.4.19
1Go ram

-
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

optimizer bug in selecting fields that don´t belong to the index used by mysql/innodb

[rafarife]

Description:
  Hello,
  
  I wrote the following text in my last message (It was an answer to Monty):
  
  Hello,
 Monty
 I have already sent to pub/mysql/secret the table definition and data 
(clientes.txt)
and my.ini file in a compressed file named clientes.zip. 
  
You can import clientes.txt and test it.

I have the problem only with InnoDb tables.

I tell you my test with the above table clientes (


  InnoDB table
  
  I ran the following query:

  Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'a2618' And 
  Cod'059336') Or Nom'a2618' Order by Nom ASC, Cod ASC Limit 50;

  Time:0.08 secs.
  Handler_read_next: 1850 

  Explain:
  tabletype   possible_keys   keykey_len  ref  rows Extra
  Clientes index PRIMARY,Nombre   Nombre 46   NULL 3899 where used

  
 I changed the table type to MyIsam (alter table clientes type=MyIsam),
 and I ran the above query:

 MyIsam table
 
 Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'a2618' And 
 Cod'059336') Or Nom'a2618' Order by Nom ASC, Cod ASC Limit 50;

 Time:0.00 secs.
 Handler_read_next: 49

 Explain:
 tabletype   possible_keys   keykey_len  ref  rows Extra
 Clientes range  PRIMARY,Nombre  Nombre 46   NULL 4057 where used

 Thanks in advance,
 Rafa

  and I have received no answer, so, Have you received the file clientes.zip?
  Have you tested it?
  Let me know about it.

  Thanks in advance,
  Rafa

How-To-Repeat:
   Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'a2618' And 
   Cod'059336') Or Nom'a2618' Order by Nom ASC, Cod ASC Limit 50;

Fix:
-

Synopsis:optimizer bug in selecting fields that don´t belong to the index used by 
mysql/innodb

Submitter-Id:   submitter ID
Originator: Rafa
Organization:   Pecomark
MySQL support:  none
Severity:   non-critical
Priority:   medium
Category:   mysqld-max-nt
Class:  sw-bug
Release:mysqld 4.0.3 beta(InnoDB)

Exectutable:   mysqld-max-nt
Environment:   Pentium III-MMX, 500 MHZ, 540 MB
System:Windows 2000
Compiler:  -
Architecture:  i

__
The NEW Netscape 7.0 browser is now available. Upgrade now! 
http://channels.netscape.com/ns/browsers/download.jsp 

Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.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

Majordomo results: Re: Majordomo results: A nice game

[Majordomo]

--

 Your message cannot be posted because it appears to be either spam or
 Command 'your' not recognized.
 simply off topic to our filter. To bypass the filter you must include
 Command 'simply' not recognized.
 one of the following words in your message:
 Command 'one' not recognized.
 
 sql,query
 Command 'sql,query' not recognized.
 
 If you just reply to this message, and include the entire text of it in the
 Command 'if' not recognized.
 reply, your reply will go through. However, you should
 Command 'reply,' not recognized.
 first review the text of the message to make sure it has something to do
 Command 'first' not recognized.
 with MySQL. Just typing the word MySQL once will be sufficient, for example.
 Command 'with' not recognized.
 
 You have written the following:
 Command 'you' not recognized.
 
 --
END OF COMMANDS
 Help for [EMAIL PROTECTED]:


This help message is being sent to you from the Majordomo mailing list
management system at [EMAIL PROTECTED]

This is version 1.94.4 of Majordomo.

If you're familiar with mail servers, an advanced user's summary of
Majordomo's commands appears at the end of this message.

Majordomo is an automated system which allows users to subscribe
and unsubscribe to mailing lists, and to retrieve files from list
archives.

You can interact with the Majordomo software by sending it commands
in the body of mail messages addressed to [EMAIL PROTECTED].
Please do not put your commands on the subject line; Majordomo does
not process commands in the subject line.

You may put multiple Majordomo commands in the same mail message.
Put each command on a line by itself.

If you use a signature block at the end of your mail, Majordomo may
mistakenly believe each line of your message is a command; you will
then receive spurious error messages.  To keep this from happening,
either put a line starting with a hyphen (-) before your signature,
or put a line with just the word

end

on it in the same place.  This will stop the Majordomo software from
processing your signature as bad commands.

Here are some of the things you can do using Majordomo:

I.  FINDING OUT WHICH LISTS ARE ON THIS SYSTEM

To get a list of publicly-available mailing lists on this system, put the
following line in the body of your mail message to [EMAIL PROTECTED]:

lists

Each line will contain the name of a mailing list and a brief description
of the list.

To get more information about a particular list, use the info command,
supplying the name of the list.  For example, if the name of the list 
about which you wish information is demo-list, you would put the line

info demo-list

in the body of the mail message.

II. SUBSCRIBING TO A LIST

Once you've determined that you wish to subscribe to one or more lists on
this system, you can send commands to Majordomo to have it add you to the
list, so you can begin receiving mailings.

To receive list mail at the address from which you're sending your mail,
simply say subscribe followed by the list's name:

subscribe demo-list

If for some reason you wish to have the mailings go to a different address
(a friend's address, a specific other system on which you have an account,
or an address which is more correct than the one that automatically appears 
in the From: header on the mail you send), you would add that address to
the command.  For instance, if you're sending a request from your work
account, but wish to receive demo-list mail at your personal account
(for which we will use [EMAIL PROTECTED] as an example), you'd put
the line

subscribe demo-list [EMAIL PROTECTED]

in the mail message body.

Based on configuration decisions made by the list owners, you may be added 
to the mailing list automatically.  You may also receive notification
that an authorization key is required for subscription.  Another message
will be sent to the address to be subscribed (which may or may not be the
same as yours) containing the key, and directing the user to send a
command found in that message back to [EMAIL PROTECTED]  (This can be
a bit of extra hassle, but it helps keep you from being swamped in extra
email by someone who forged requests from your address.)  You may also
get a message that your subscription is being forwarded to the list owner
for approval; some lists have waiting lists, or policies about who may
subscribe.  If your request is forwarded for approval, the list owner
should contact you soon after your request.

Upon subscribing, you should receive an introductory message, containing
list policies and features.  Save this message for future reference; it
will also contain exact directions for unsubscribing.  If you lose the
intro mail and would like another copy of the policies, send this message
to [EMAIL PROTECTED]:

intro demo-list

(substituting, of course, the real name of your list for demo-list).

III.UNSUBSCRIBING FROM MAILING LISTS

Your 

Re: Performance while Building Indices -- how does it scale?

[Joseph Bueno]

Chris Stoughton wrote:
 I have a table with a few million rows, wth 633 columns.  I want to 
 create 10 inidices on this table, six single-column and four two-column 
 inidices.  The database will be loaded once and remain static, and 
 queried many times.  Please note that this is a small prototype for 
 the actual database, which will have 40 times more rows.
 
 I used two strategies to create these tables:
 
 1.  Create the indices in the create table statement
 2.  Load the table, and then use the create index statement for each 
 index.
 
 With Strategy 1, the loading started nicely  using the load data 
 infile command to load approx. 2000 records at a time.  It took 3 
 seconds for each load data infile command.  After a few hundred load 
 data infile commands, however, the time increased to two minutes per 
 command.
 
 With Strategy 2, the loading went from start to finish at 3 seconds for 
 each load data infile command.  But now, creating each index is taking 
 longer.  Here is a log:
 
 Fri Sep 20 15:17:43 CDT 2002 create index objId on bestTsObj (objId)
 Fri Sep 20 15:35:51 CDT 2002 create index fieldId on bestTsObj (fieldId)
 Fri Sep 20 15:56:02 CDT 2002 create index targetObjId on bestTsObj 
 (targetObjId)
 Fri Sep 20 16:20:02 CDT 2002 create index ra on bestTsObj (ra)
 Fri Sep 20 16:49:16 CDT 2002 create index decl on bestTsObj (decl)
 Fri Sep 20 17:53:38 CDT 2002 create index u on bestTsObj (u)
 Fri Sep 20 18:42:52 CDT 2002 create index g on bestTsObj (g)
 Fri Sep 20 22:04:25 CDT 2002 create index r on bestTsObj (r)
 Sat Sep 21 10:06:44 CDT 2002 create index i on bestTsObj (i)
 Sat Sep 21 19:31:52 CDT 2002 create index z on bestTsObj (z)
 Sun Sep 22 15:45:06 CDT 2002 create index ug on bestTsObj (u,g)
 
 
 The first index was created in a reasonable amount of time (18 minutes), 
 but it is taking longer to create more indices.
 
 
 With both strategies, no process is swapping heavily, and mysqld is not 
 consuming available CPU cycles efficiently.
 
 Can you suggest how I can build multiple indices on large tables 
 efficiently?  Will it help to create a narrow table with only the 
 quantities I want to use in indices?
 
 We have installed mysql via rpm:
 bash-2.04$ mysqladmin
 mysqladmin  Ver 8.18 Distrib 3.23.36, for redhat-linux-gnu on i386
 
 bash-2.04$ uname -r
 2.4.18-xfs-1.1
 bash-2.04$
 What diagnostics do you suggest I run?
 
 Thanks
 
Hi,

You should use strategy #1 since each time you create a new index, all
index are rebuilt (this is why each new index is taking longer in
strategy#2).

Since mysqld is not consuming CPU nor swapping, it is probably doing a
lot of disc I/Os. You can verify that with vmstat utility.

You didn't specify how much RAM you have and what mysql configuration
you are using (check /etc/my.cnf). You should pay special attention to
'key_buffer_size' parameter value since it has a major impact on index
performance.

Regards,
-- 
Joseph Bueno


-
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

Query Error

[Odhiambo Washington]

Hello gurus,


Please help. Where is my mistake?



I get the following error...

MYSQL: query failed: You have an error in your SQL syntax near ')  rei_ts' at line 1


When I run the query below 


   SELECT rei_ip \
   FROM relay_ip \
   WHERE rei_ip=${sender_host_address} AND DATE_SUB(NOW(), INTERVAL 10)  rei_ts




And the tabledesign is ...



CREATE TABLE relay_ip (
  rei_aid int(11) NOT NULL auto_increment,
  rei_uname varchar(30) NOT NULL default '',
  rei_domain varchar(128) NOT NULL default '',
  rei_ip varchar(64) NOT NULL default '',
  rei_ts datetime NOT NULL default '-00-00 00:00:00',
  PRIMARY KEY  (rei_aid),
  KEY rei_ip (rei_ip)
) ;




-Wash

-- 
Odhiambo Washington   [EMAIL PROTECTED]  The box said 'Requires
Wananchi Online Ltd.  www.wananchi.com  Windows 95, NT, or better,'
Tel: +254 2 313985-9  +254 2 313922 so I installed FreeBSD.   
GSM: +254 72 743223   +254 733 744121   This sig is McQ!  :-)


If the King's English was good enough for Jesus, it's good enough for
me!
-- Ma Ferguson, Governor of Texas (circa 1920)

-
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: Query Error

[Ralf Narozny]

Odhiambo Washington wrote:

Hello gurus,


Please help. Where is my mistake?



I get the following error...

MYSQL: query failed: You have an error in your SQL syntax near ')  rei_ts' at line 1


When I run the query below 


   SELECT rei_ip \
   FROM relay_ip \
   WHERE rei_ip=${sender_host_address} AND DATE_SUB(NOW(), INTERVAL 10)  rei_ts

  


...INTERVAL 10 day)  rei_ts

missing the unit of the interval...



And the tabledesign is ...



CREATE TABLE relay_ip (
  rei_aid int(11) NOT NULL auto_increment,
  rei_uname varchar(30) NOT NULL default '',
  rei_domain varchar(128) NOT NULL default '',
  rei_ip varchar(64) NOT NULL default '',
  rei_ts datetime NOT NULL default '-00-00 00:00:00',
  PRIMARY KEY  (rei_aid),
  KEY rei_ip (rei_ip)
) ;




-Wash

  


-- 
Ralf Narozny
SPLENDID Internet GmbH  Co KG
Skandinaviendamm 212, 24109 Kiel, Germany
fon: +49 431 660 97 0, fax: +49 431 660 97 20
mailto:[EMAIL PROTECTED], http://www.splendid.de




-
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

compiling mysql on Solaris 9 on Ultra 5 using gcc 3.2 fails

[Gunter Geis]

Hello

I'm Gunter and I tried to compile the mysql Database on Solaris 9 for
Ultra Sparc arcitecture.
I use the precompiled gcc 3.2 compiler form www.sunfreeware.com.
This Compiler is linked to use the as and ld fom the Solaris operating
system.
Also I use the autoconf and the other gnu tools from this site.
Becaus the precompiled version of mysql uses a version of the
libstdc++.so.3.0.0
and on my system there is the libstdc++.so.5.0.0 version installed, I
can not use the precompiled version.

When I tried to compile the source distribution I got severel diferent
errormessages in dependency
for what I configured.

I know that error reports shuold be made by msqlbug and sended directly,
but the SUN I compiled it is a
stand alone mashine without an smtp configured.
So I copied and pasted the poutput of mysqlbug to an e-mail programm on
an other machine and send it directly, but
the mail returned as undeliverabe.

The result is, that I subscribe to this mailing list.

I used the minimum configure parameters --prefix=/usr/local/mysql
It is also reproduceable with:

./configure --prefix=/usr/local/mysql --enable-thread-safe-client
--enable-local-infile --disable-shared

The error mesage I got:

g++ -DMYSQL_SERVER
-DDEFAULT_MYSQL_HOME=\/usr/local\
-DDATADIR=\/usr/local/var\
-DSHAREDIR=\/u
sr/local/share/mysql\ -DHAVE_CONFIG_H
-I./../include
-I./../regex-I. -I../include -I..
-I.-O3
 -DDBUG_OFF   -fno-implicit-templates -fno-exceptions -fno-rtti
-DHAVE_RWLOCK_T
-c mysqld.cc
mysqld.cc: In function `void* handle_connections_sockets(void*)':
mysqld.cc:2410: invalid conversion from `size_socket*' to `socklen_t*'
mysqld.cc:2476: invalid conversion from `size_socket*' to `socklen_t*'
make[3]: *** [mysqld.o] Error 1
make[3]: Leaving directory `/opt/mysql-3.23.52/sql'
make[2]: ***[all-recursive] Error 1
make[2]: Leaving directory `/opt/mysql-3.23.52/sql'
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory `/opt/mysql-3.23.52'
make: *** [all-recursive-am] Error 2


A similar error message was created when I configured with some
options(the options recommended for Solaris), but on an other file
(sql_lex.cc also located in sql dir)
Here in line 85 the symbols and in line 87 sql_functions was
undefined.
After configuring whithout any options the file sql_lex.cc compiled

g++ -DMYSQL_SERVER
-DDEFAULT_MYSQL_HOME=\/usr/local/mysql\
-DDATADIR=\/usr/local/mysql/var\
-DSHAREDIR=\/usr/local/mysql/share/mysql\
-DHAVE_CONFIG_H -I./../include
-I./../regex-I. -I../include -I.. -I.-O3
-DDBUG_OFF   -fno-implicit-templates -fno-exceptions -fno-rtti
-DHAVE_RWLOCK_T -c sql_lex.cc


but the first mentioned error
 in mysqld.cc occoured.


I also see that in the default compiler flags there is
-mcpu=pentiumpro but the SUN Ultra 5
uses a Ultra II Processor.

How-To-Repeat:
Just unpack the distribution and call configure whithout any options.
Than call make.

Fix:

Submitter-Id:  Dont have one
Originator:   Super-User
Organization: institute of applied physiks Frankfurt germany
MySQL support: none
Synopsis:  dose not compile
Severity:  critical
Priority:  medium
Category:  mysql
Class: sw-bug
Release:   mysql-3.23.52 (Source distribution)

Environment: Sun Ultra 5, Solaris 8, Sun Untra 5,libc as delivered with

the OS
  also the newest librarys which are delivered with the gcc
3.2 from
  www.sunfreeware.com
  libstdc++.so.5.0.0 libg2c.so.0.0.0 libgcj.so.3.0.0

System: SunOS cnna 5.9 Generic sun4u sparc SUNW,Ultra-5_10
Architecture: sun4

Some paths:  /usr/local/bin/perl /usr/local/bin/make /usr/local/bin/gcc
/usr/ucb/cc
GCC: Reading specs from
/usr/local/lib/gcc-lib/sparc-sun-solaris2.9/3.2/specs
Configured with: ../configure --with-as=/usr/ccs/bin/as
--with-ld=/usr/ccs/bin/ld --disable-nls
Thread model: posix
gcc version 3.2
Compilation info: CC='gcc'  CFLAGS='-Wimplicit -Wreturn-type -Wswitch
-Wtrigraphs
-Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare
-Wwrite-strings -Wunused -mcpu=pentiumpro
 -O3 -fno-omit-frame-pointer'  CXX='gcc'  CXXFLAGS='-Wimplicit
-Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts
-Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Woverloa
ded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy
-Wnon-virtual-dtor -felide-constructors -
fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer'
LDFLAGS=''

LIBC:
-rw-r--r--   1 root bin  1828460 Apr  6 21:46 /lib/libc.a
lrwxrwxrwx   1 root root  11 Aug 20 13:32 /lib/libc.so -
./libc.so.1
-rwxr-xr-x   1 root bin   855484 Apr  6 21:46 /lib/libc.so.1
-rw-r--r--   1 root bin  1828460 Apr  6 21:46 /usr/lib/libc.a
lrwxrwxrwx   1 root root  11 Aug 20 13:32 /usr/lib/libc.so
- ./libc.so.1
-rwxr-xr-x   1 root bin   855484 Apr  6 21:46 /usr/lib/libc.so.1

Configure command: ./configure 

Re: MySQL Gui Install and Setup for Win2K

[michael . mouer]

And where would one obtain MySQL Navigator?

JMichael




Matt Hargraves [EMAIL PROTECTED]
09/22/2002 09:14 PM

 
To: Arthur Fuller [EMAIL PROTECTED], David Kramer 
[EMAIL PROTECTED]
cc: mysql mailing list [EMAIL PROTECTED]
Subject:Re: MySQL Gui Install and Setup for Win2K


Don't let them mislead you, MyCC might as well be dead too... there hasn't
been an update to it for more than 4 months.  Go get MySQL Navigator 
because
at least IT can export to a file that you can then edit.

I usually use both though, MyCC is easier to design the basic (and I mean
BASIC) database, then go into MySQL Navigator to export it so that I can
manually add all the stuff that neither of the semi-worthless apps seem to
support (half of SQL).  If MySQL AB wants to know why a lot of people use
MySQL and Oracle, it's because the tools are there and they work.  The 
only
reason that people use MySQL is because it's cheaper (OK, a LOT cheaper).
If they made tools that fully supported SQL then MySQL would get a lot 
more
industry support, IMO.

Matt

- Original Message -
From: Arthur Fuller [EMAIL PROTECTED]
To: David Kramer [EMAIL PROTECTED]
Cc: mysql mailing list [EMAIL PROTECTED]
Sent: Sunday, September 22, 2002 6:06 PM
Subject: Re: MySQL Gui Install and Setup for Win2K


 We were told by David Axmark of MySQL AB to stop using MySQLGui and 
switch
 to MyCC. MySQLGUI is apparently dead.

 - Original Message -
 From: David Kramer [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Sent: Monday, September 16, 2002 12:52 PM
 Subject: MySQL Gui Install and Setup for Win2K


  Can some one point me in the direction of some good install and setup
Doc
  for MySQL GUI.  Im running the GUI on Win2K, but my DB server is RH7.2
w/
  stock Mysql(not sure version number).  Also is the FLTK absolutely
 necessary
  to run the GUI on Win2K?  Any other hints or insights is greatly
  appreciated.
 
  Thanks,
 
  DK
 
  David Kramer
  Software Developer
  Reflect.com
  Direct: 415.369.4856
  Cell: 650.302.7889
 
 
  -
  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.php

Error with SHOW VARIABLES

[wrigley]

Description:
When I issue a SHOW VARIABLES request using the standard command
line client, I get a ERROR 2013: Lost connection to MySQL server
during query. The server error log reports:

mysqld got signal 10;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

and restarts.

How-To-Repeat:

Fix:


Submitter-Id:  submitter ID
Originator:Super-User
Organization:
 
MySQL support: [none | licence | email support | extended email support ]
Synopsis:  Error with SHOW VARIABLES
Severity:  
Priority:  
Category:  mysql
Class: 
Release:   mysql-3.23.42 (Source distribution)

Environment:

System: SunOS www3 5.8 Generic_108528-15 sun4u sparc SUNW,Ultra-250
Architecture: sun4

Some paths:  /usr/local/bin/perl /usr/local/bin/make /usr/local/bin/gcc /usr/ucb/cc
GCC: Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.8/2.95.2/specs
gcc version 2.95.2 19991024 (release)
Compilation info: CC='gcc'  CFLAGS=''  CXX='c++'  CXXFLAGS=''  LDFLAGS=''
LIBC: 
-rw-r--r--   1 root bin   1764484 Jul  8 23:17 /lib/libc.a
lrwxrwxrwx   1 root root   11 Aug 25  2000 /lib/libc.so - ./libc.so.1
-rwxr-xr-x   1 root bin   1146204 Jul  8 23:17 /lib/libc.so.1
-rw-r--r--   1 root bin   1764484 Jul  8 23:17 /usr/lib/libc.a
lrwxrwxrwx   1 root root   11 Aug 25  2000 /usr/lib/libc.so - ./libc.so.1
-rwxr-xr-x   1 root bin   1146204 Jul  8 23:17 /usr/lib/libc.so.1
Configure command: ./configure  --with-mysqld-user=mysql


-
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 Gui Install and Setup for Win2K

[Matt Hargraves]

http://sql.kldp.org/mysql/

One good thing about this one though, they seem to be able to update theirs,
the most recent update (1.4) was on September 13th, the previous version
(1.3.12) was on June 4th.  Hopefully the new version (which I haven't tried
out yet) will support some of the 4.x.x stuff in MySQL.

Matt

- Original Message -
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, September 23, 2002 7:01 AM
Subject: Re: MySQL Gui Install and Setup for Win2K


 And where would one obtain MySQL Navigator?

 JMichael




 Matt Hargraves [EMAIL PROTECTED]
 09/22/2002 09:14 PM


 To: Arthur Fuller [EMAIL PROTECTED], David Kramer
[EMAIL PROTECTED]
 cc: mysql mailing list [EMAIL PROTECTED]
 Subject:Re: MySQL Gui Install and Setup for Win2K


 Don't let them mislead you, MyCC might as well be dead too... there hasn't
 been an update to it for more than 4 months.  Go get MySQL Navigator
 because
 at least IT can export to a file that you can then edit.

 I usually use both though, MyCC is easier to design the basic (and I mean
 BASIC) database, then go into MySQL Navigator to export it so that I can
 manually add all the stuff that neither of the semi-worthless apps seem to
 support (half of SQL).  If MySQL AB wants to know why a lot of people use
 MySQL and Oracle, it's because the tools are there and they work.  The
 only
 reason that people use MySQL is because it's cheaper (OK, a LOT cheaper).
 If they made tools that fully supported SQL then MySQL would get a lot
 more
 industry support, IMO.

 Matt

 - Original Message -
 From: Arthur Fuller [EMAIL PROTECTED]
 To: David Kramer [EMAIL PROTECTED]
 Cc: mysql mailing list [EMAIL PROTECTED]
 Sent: Sunday, September 22, 2002 6:06 PM
 Subject: Re: MySQL Gui Install and Setup for Win2K


  We were told by David Axmark of MySQL AB to stop using MySQLGui and
 switch
  to MyCC. MySQLGUI is apparently dead.
 
  - Original Message -
  From: David Kramer [EMAIL PROTECTED]
  To: [EMAIL PROTECTED]
  Sent: Monday, September 16, 2002 12:52 PM
  Subject: MySQL Gui Install and Setup for Win2K
 
 
   Can some one point me in the direction of some good install and setup
 Doc
   for MySQL GUI.  Im running the GUI on Win2K, but my DB server is RH7.2
 w/
   stock Mysql(not sure version number).  Also is the FLTK absolutely
  necessary
   to run the GUI on Win2K?  Any other hints or insights is greatly
   appreciated.
  
   Thanks,
  
   DK
  
   David Kramer
   Software Developer
   Reflect.com
   Direct: 415.369.4856
   Cell: 650.302.7889
  
  
   -
   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.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

Problem with ODBC Driver and Office XP

[he]

Hello,

I m using WinXP / Office XP and need odbc support to create a circular
letter.
I have installed mysql, etc. all works fine. 
But I cant get Word to work with odbc drivers. They seem to work, they
get connection
To my database but word says something like this :
couldn't obtain table lists from data base

Dunno, how to solve this problem, would be happy someone has suggestions
J


Regards
T.Nissen 





-
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: Turn logging on or off for SQL statements

[Egor Egorov]

Owain,
Sunday, September 22, 2002, 8:37:55 PM, you wrote:

OM Is there an easy way to turn logging or SQL statement executions on or off without 
having to restart with a changed my.cnf file?  Just want to log queries to try and 
sort out some performance
OM problems. 

Nope.



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




-
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: Case sensitivety behaviour

[Victoria Reznichenko]

Moestl,
Monday, September 23, 2002, 9:49:14 AM, you wrote:

MW Is there a defined behaviour for handling the case-sensitivety for user- and 
hostnames?
MW I did not find any information on this at the docs for the 4.0.3 MAX version which 
I'm using.


MW For example, the following database connections are valid:
MW root@localhost
MW Root@localhost

MW However, the following connection is resulting in an Connection failed: 1045 - 
Accss denied for user: ... error:
MW ROOT@localhost
MW ROot@localhost

MW It looks like the host parameter is completely insensitive for cases, every 
combination of upper- and lowercase characters it taken.


MW To get it even more confusing, the values for user and host at the SHOW GRANTS FOR 
[user]@[host] are BOTH FULLY case-sensitive.
MW Only the exact values as beeing stored at the mysql system tables are taken.

MW The following statements does return values:
MW SHOW GRANTS FOR root@localhost

MW The following statements will NOT return values:
MW SHOW GRANTS for Root@localhost
MW SHOW GRANTS for root@Localhost

MW Personnaly I think the same case-sensitivety behaviour should be implemented in 
every aspect of the database functionallity.
MW If connecting as Root@LOCalhOST works a SHOW GRANTS FOR Root@LOCalhOST should work 
as well.

What OS do you use? I tested your examples on 4.0.3-max on Win and
RedHat and all worked well. I got Access denied error in any case
except root@localhost when I try to connect and SHOW GRANTS also
worked as well:

mysql show grants for 'Root'@'localhost';
ERROR 1141: There is no such grant defined for user 'Root' on host 'localhost'
mysql show grants for 'root'@'Localhost';
ERROR 1141: There is no such grant defined for user 'root' on host 'Localhost'


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

re: Slight problem storing data with Mysql...

[Victoria Reznichenko]

Marvin,
Monday, September 23, 2002, 6:52:23 AM, you wrote:

MC MySQL newbie seeking help and/or advice on storing data into a database.
MC I have a poetry section I'm trying to add to my site that will allow users to 
submit some of their work for public viewing. An example is located here: 
http://www.nubiint.com/nuwoez/nuwoez.htm.
MC By clicking on any one of the categories listed in the menu, the user is presented 
with a link that will allow them to complete a form and enter their text to be stored 
in the db I've created and
MC made available for public viewing. The problem is that the data doesn't appear to 
store in the table in the database. After adding some test data I'm able to see the 
row added to the table, but
MC I'm unable to see or view anything else, for instance the date(s) or data that's 
set as a mediumblob. 
MC How can I view this data to ensure that it is indeed in the table? 
MC Also how can I reset the ID field? After I delete the test data the ID doesn't 
reset, it continues to count from the last ID.

You can do it with ALTER TABLE or with myisamchk -A
http://www.mysql.com/doc/en/ALTER_TABLE.html
http://www.mysql.com/doc/en/myisamchk_other_options.html

MC Also how can I change the display of the date field? It currently shows as 
-00-00. Is there anyway to change it to
MC 00-00-???

Take a look at DATE_FORMAT() function:
 http://www.mysql.com/doc/en/Date_and_time_functions.html


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

re: Bug at Hostname parameter for SHOW GRANTS FOR ...?

[Egor Egorov]

Moestl,
Monday, September 23, 2002, 9:54:33 AM, you wrote:

MW If you have a host having the minus character '-' in the name the mysql command 
SHOW GRANTS FOR fails.

MW Example:

MW SHOW GRANTS FOR root@MY-COMUPTER results in the error message
MW You have an error in your SQL syntax near '-COMPUTER' at line 1.

MW It looks like the command processor stops the parsing for the hostname at the '-' 
character and tries to find an appropriate command sequence afterwards.

MW Is this a Bug or expected behaviour?

Quote host name, like 'root'@'MY-COMUPTER'



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




-
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: mysqlimport

[Egor Egorov]

vinita,
Monday, September 23, 2002, 9:12:24 AM, you wrote:

vvM I can create a database called test,
vvM mysql create database test;
vvM Query OK, 1 row affected (0.03 sec)

vvM BUT I can't create any other databases???

vvM mysql create database tem;
vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 'tem'

vvM mysql create database proj;
vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 'proj'

Seems user 'ddb' or anonymous user has privileges only on the database
'test'.



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




-
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: Performance whil Building Indices -- how does it scale?

[Chris Stoughton]

Joseph,

Thanks for the quick answer.

Very nice to know that adding an index forces a rebuild of all indices!  
(Side note -- I was going to configure the database with a minimal set 
of indices, and then watch to see how people use the database, and then 
add indices on popular columns.)

I did not notice a lot of i/o activity, but will run vmstat for a while 
and gather statistics

The machine has 1GB of RAM.

Here is the configuration:
bash-2.04$ more /etc/my.cnf
[mysqld]
datadir=/data/dp14.a/data/mysql
socket=/var/lib/mysql/mysql.sock
   [mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


There is NO SETTING for key_buffer_size -- what value do you suggest?

Thanks,

Chris


Joseph Bueno wrote:

 Chris Stoughton wrote:

 I have a table with a few million rows, wth 633 columns.  I want to 
 create 10 inidices on this table, six single-column and four 
 two-column inidices.  The database will be loaded once and remain 
 static, and queried many times.  Please note that this is a small 
 prototype for the actual database, which will have 40 times more rows.

 I used two strategies to create these tables:

 1.  Create the indices in the create table statement
 2.  Load the table, and then use the create index statement for 
 each index.

 With Strategy 1, the loading started nicely  using the load data 
 infile command to load approx. 2000 records at a time.  It took 3 
 seconds for each load data infile command.  After a few hundred 
 load data infile commands, however, the time increased to two 
 minutes per command.

 With Strategy 2, the loading went from start to finish at 3 seconds 
 for each load data infile command.  But now, creating each index is 
 taking longer.  Here is a log:

 Fri Sep 20 15:17:43 CDT 2002 create index objId on bestTsObj (objId)
 Fri Sep 20 15:35:51 CDT 2002 create index fieldId on bestTsObj (fieldId)
 Fri Sep 20 15:56:02 CDT 2002 create index targetObjId on bestTsObj 
 (targetObjId)
 Fri Sep 20 16:20:02 CDT 2002 create index ra on bestTsObj (ra)
 Fri Sep 20 16:49:16 CDT 2002 create index decl on bestTsObj (decl)
 Fri Sep 20 17:53:38 CDT 2002 create index u on bestTsObj (u)
 Fri Sep 20 18:42:52 CDT 2002 create index g on bestTsObj (g)
 Fri Sep 20 22:04:25 CDT 2002 create index r on bestTsObj (r)
 Sat Sep 21 10:06:44 CDT 2002 create index i on bestTsObj (i)
 Sat Sep 21 19:31:52 CDT 2002 create index z on bestTsObj (z)
 Sun Sep 22 15:45:06 CDT 2002 create index ug on bestTsObj (u,g)


 The first index was created in a reasonable amount of time (18 
 minutes), but it is taking longer to create more indices.


 With both strategies, no process is swapping heavily, and mysqld is 
 not consuming available CPU cycles efficiently.

 Can you suggest how I can build multiple indices on large tables 
 efficiently?  Will it help to create a narrow table with only the 
 quantities I want to use in indices?

 We have installed mysql via rpm:
 bash-2.04$ mysqladmin
 mysqladmin  Ver 8.18 Distrib 3.23.36, for redhat-linux-gnu on i386

 bash-2.04$ uname -r
 2.4.18-xfs-1.1
 bash-2.04$
 What diagnostics do you suggest I run?

 Thanks

 Hi,

 You should use strategy #1 since each time you create a new index, all
 index are rebuilt (this is why each new index is taking longer in
 strategy#2).

 Since mysqld is not consuming CPU nor swapping, it is probably doing a
 lot of disc I/Os. You can verify that with vmstat utility.

 You didn't specify how much RAM you have and what mysql configuration
 you are using (check /etc/my.cnf). You should pay special attention to
 'key_buffer_size' parameter value since it has a major impact on index
 performance.

 Regards,






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

[Victoria Reznichenko]

hans,
Sunday, September 22, 2002, 3:48:33 PM, you wrote:

hs trying to do my first steps with mysql, i have done the following :
hs mysql USE mysql;
hs Reading table information for completion of table and column names
hs You can turn off this feature to get a quicker startup with -A

hs Database changed
hs mysql INSERT INTO mysql VALUES(localhost, sample_db,
hs 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
hs ERROR 1146: Table 'mysql.mysql' doesn't exist

hs hmm - what I have done wrong here ? or do I have to do something before
hs ?

There is no table 'mysql' in the database 'mysql':
  http://www.mysql.com/doc/en/Privileges.html

BTW: GRANT is more recommended to use:
 http://www.mysql.com/doc/en/GRANT.html


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

Re: Multistatement Seperator

[Michael T. Babcock]

Davis, Tim wrote:

select count(*) from table1
go
select count (*) from table2
go

This will return 2 results, with count of each table.  How would I do the
same thing in MySQL?
  

I'm not sure if you can; I tried:
select count(table1.id) as table1_count, count(table2.id) as 
table2_count from table1, table2;
... and it returned the greater value of the two for both counts.

-- 
Michael T. Babcock
C.T.O., FibreSpeed Ltd.
http://www.fibrespeed.net/~mbabcock



-
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: Fractions

[Michael T. Babcock]

MySQL wrote:

   You might also consider using two integer type fields - one for the
   numerator and another for the denominator. This way storage and recovery are
   easy and the mathematics are wide open.
   _M

I suspect that a float add is faster than 3 integer multiplies and
sum.
  

It depends on why you're storing this information in the first place. 
 If what you want is the precision of fractions (which you lose with 
floating point), then store the numerator and denominator.  If you want 
quick and dirty calculations at certain points in your code, store both ...

Example (where $x, $y are variables):
INSERT INTO FracTable (numerator, denominator, appxval) values ($x, $y, 
$x/$y);

-- 
Michael T. Babcock
C.T.O., FibreSpeed Ltd.
http://www.fibrespeed.net/~mbabcock



-
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

Installation problems

[Sherif D Mohamad]

Hi All,

I am new to mySQL.
I installed it on Redhat Linux 7.3 using MySQL-Max-3.23.52-1.i386.rpm
While installation it asked me to change DB root password using mysqladmin

I looked for mysqladmin and didn't find it !! not all the files were
installed.

I looked into the documentation and it said for linux, using RPM, install
these 2 files  MySQL-VERSION.i386.rpm  and MySQL-client-VERSION.i386.rpm

I have the rpm for the server but I looked on www.mysql.com for
MySQL-client-VERSION.i386.rpm but could not find any RPMs for the client (or
any installation files to install clinet)

I feel I am doing something wrong so hope you can help me.

Best Regards
Sherif


-
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: Complex SQL query problem...

[Richard Bolen]

FYI - this query seemed to work.

select * from nodes 
left join nodes as n2 on n2.parent_id = nodes.node_id 
left join jobs on jobs.parent_id = nodes.node_id 
left join colors on colors.parent_id = nodes.node_id 
where nodes.node_id = ? 
and ((n2.parent_id is not NULL) or (jobs.parent_id is not NULL) or (colors.parent_id 
is not NULL))


I need to do some more testing to be sure.

Rich

-Original Message-
From: Edward Peloke [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 23, 2002 08:46
To: Richard Bolen
Subject: RE: Complex SQL query problem...


After I sent this it hit me that it may not work if the first table (jobs)
contained no rows...I believe this would only work if the tables left joined
were empty not the jobs table.  sorry...

I apologize  did not respond Friday but I left work at 4.

Eddie

-Original Message-
From: Richard Bolen [mailto:[EMAIL PROTECTED]]
Sent: Friday, September 20, 2002 4:19 PM
To: Edward Peloke
Subject: RE: Complex SQL query problem...


Does this handle the case where the ID is in the submissions table but not
the jobs table?  How would this look if there was a third table also?

Thanks again for you help!

Rich

-Original Message-
From: Edward Peloke [mailto:[EMAIL PROTECTED]]
Sent: Friday, September 20, 2002 15:59
To: MySQL Mailing List (E-mail)
Subject: RE: Complex SQL query problem...


try a left join

select count(*) from jobs
 left join submissions on
   jobs.standard_id=submissions.color_id
   where jobs.standard_id=ID_VALUE and submissions.color_id is null


Eddie

-Original Message-
From: Richard Bolen [mailto:[EMAIL PROTECTED]]
Sent: Friday, September 20, 2002 2:37 PM
To: MySQL Mailing List (E-mail)
Subject: Complex SQL query problem...


I'm trying to use a sql query to determine if an ID exists in any of 3
different tables in the database.  I need to do this in one SQL query
(ideally only using the ID once in the query).  I'm using mysql 3.23.47.

Here's an example of a query I came up with:

select count(*) from jobs, submissions where ID_VALUE in (jobs.standard_id,
submissions.color_id)


I'm just trying to determine if the ID exists.

This query works *IF AND ONLY IF* there is at least one record in each of
the tables.  If any of the table are empty, this query always returns a
count of 0 (even if there is a match in one of the non-empty tables).

Does anyone know why this is happening or could someone suggest a alternate
query?

Thanks,
Rich


Rich Bolen
Senior Software Developer
GretagMacbeth Advanced Technologies Center
79 T. W. Alexander Drive - Bldg. 4401 - Suite 250
PO Box 14026
Research Triangle Park, North Carolina 27709-4026  USA
Phone:  919-549-7575 x239,  Fax: 919-549-0421

http://www.gretagmacbeth.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


-
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

Remote ServerAccess

[Patrick Fowler]

Hello,

I'm just starting out with MySQL.  How do you set permission to allow for
access from a domain or IP.  I found this in the docs.  I this all I need?

GRANT ALL PRIVILEGES ON *.* TO monty@% - IDENTIFIED BY 'some_pass' WITH
GRANT OPTION; 


DBA / Unix Administrator
Patrick Fowler
Wynit, Inc.
6847 Ellicott Drive
East Syracuse, NY 13057
V (315) 437-7617 x2172
F (315) 437-0432
 


-
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

Porting Mssql to MySQL

[Mike Conway]

First off i am a newbie, so forgive any repatition and ideocy.

Ok i am trying to evaluate MySQL, i have a Mssql database which has been 
specifically generated by my company. Therefore i have a ddl file which 
will generate the tables, database name and relationships. I have poked 
around mysql and the help file to get a feel for it, so i can make a 
databse and put tables in etc. Ok now for the serious stuff, i need to 
ask a few questions.

1) How can i port the database i have to MySQL??
2) Could i generate the Database by tweeking the DDL file and run it 
using a batch command? What sort of tweek to the file would i need.?
3) Is there an ODBC connector so that i could get a reporting tool to 
access the MySQL database? (reporting tool is crystal reports).

I am sure there will be plenty more questions to bug you all with but 
this is enough to start with.

Thank all

mike



-
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: access denied ????

[gerald_clark]

mysql does not own its files.

toby - wrote:

 ok guyz 

 im lost

 im creating my tables and after i ve created em i tried to change two 
 of the fields to FULLTEXT and this is what i get :

 Database zabanDb running on localhost
 Error

 SQL-query :

 ALTER TABLE `ctnt_inf` ADD FULLTEXT(`summary`)


 MySQL said:


 Error on rename of '.\zabandb\ctnt_inf.MYI' to 
 '.\zabandb\#sql2-5b0-7e.MYI' (Errcode: 13)


 ACCESS DENIED ! ! ! ! !

 what the hell is rong with this thing ?

 cud anyone help me plz 



 _
 Join the world's largest e-mail service with MSN Hotmail. 
 http://www.hotmail.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





-
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

user variables in ODBC

[Klavs bryld]

It seems like MyODBC  (or ODBC in general ?) cannot accept user variables.
The following query works OK when quering directly through the API but doesnt work via 
MyODBC

Any suggestions to some workaround allowing the use of user variables???

SELECT id, 
@s:=lang lang=da,
@o:=objekter.overskrift,
@p0:=POSITION(@s IN @o),
@l:=length(@s),
if (@p0=0, 
@p1:=POSITION(lang lang= IN @o),
@p1:=LOCATE(/lang,@o,@p0+@l)
),
if (@p0=0, 
if (@p1=0,@o,LEFT(@o,@p1)),
SUBSTRING(@o,@p0,@p1-@p0)
) AS overskrift , ... and a lot more...

It's testet with windows MyODBC-version 2.50.27 in an delphi-application using 
modbc-component.

klavs




-
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: Help with ordering and grouping with distinct ...

[gerald_clark]

ORDER BY SentDate DESC

Ben Holness wrote:

Hi all,

I have a table with three columns:

Destinationvarchar(254)
SentDate   timestamp(14)
UserID varchar(32)

I want to be able to get a list of the 100 most recently used numbers, based
on the timestamp (SentDate) column.

I want to have them ordered such that the most recently used number is
first.

Using the example data below, the list I would like to finish up with is
(order manually, but I think it is right):

+--+
| Destination  |
+--+
| 447812106183 |
| 447775906857 |
| 447781484245 |
| 447967305499 |
| 447812106198 |
| 447812106154 |
| 447781484234 |
| 447775906851 |
| 447967305450 |
+--+

How do I do this with an SQL statement? The one that I thought would work
does not take the most recent timestamp when grouping :(

mysql SELECT DISTINCT Destination, SentDate FROM OutboundMessages WHERE
UserID='2' GROUP BY Destination ORDER BY SentDate LIMIT 100;
+--++
| Destination  | SentDate   |
+--++
| 447967305499 | 20020529123518 |
| 447967305450 | 20020604213249 |
| 447775906851 | 20020604214940 |
| 447812106183 | 20020606173004 |
| 447781484234 | 20020612133629 |
| 447812106198 | 20020622194238 |
| 447775906857 | 20020622204826 |
| 447812106154 | 20020622205026 |
| 447781484245 | 20020626004507 |
+--++
13 rows in set (0.37 sec)

mysql SELECT DISTINCT Destination, SentDate FROM OutboundMessages WHERE
UserID='2' ORDER BY SentDate LIMIT 100
+--++
| Destination  | SentDate   |
+--++
| 447812106183 | 20020921134113 |
| 447775906857 | 20020921134001 |
| 447781484245 | 20020626004530 |
| 447781484245 | 20020626004529 |
| 447781484245 | 20020626004507 |
| 447781484245 | 20020626002957 |
| 447781484245 | 20020625174529 |
| 447967305499 | 20020623112634 |
| 447812106198 | 20020623112541 |
| 447775906857 | 2002065446 |
| 447812106198 | 2002065434 |
| 447812106154 | 2002064006 |
| 447812106154 | 20020622205026 |
| 447775906857 | 20020622204826 |
| 447812106198 | 20020622194504 |
| 447812106198 | 20020622194250 |
| 447812106198 | 20020622194238 |
| 447967305499 | 20020621090855 |
| 447781484234 | 20020612133947 |
| 447781484234 | 20020612133629 |
| 447967305499 | 20020612133230 |
| 447967305499 | 20020606175612 |
| 447812106183 | 20020606174826 |
| 447812106183 | 20020606173004 |
| 447967305499 | 20020606171146 |
| 447967305499 | 20020606171002 |
| 447967305499 | 20020606122804 |
| 447967305499 | 20020605143105 |
| 447775906851 | 20020604214940 |
| 447967305499 | 20020604214058 |
| 447967305450 | 20020604213249 |
| 447967305499 | 20020529123940 |
| 447967305499 | 20020529123518 |
+--++

Many thanks,

Ben


-
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: The Release date of MySQL 4.1

[Egor Egorov]

andykychan,
Monday, September 23, 2002, 11:16:09 AM, you wrote:

a I would like to know when the MySQL 4.1 will be released.

a We are looking forwards to hearing from you.

It will come in alpha before this year ends.



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




-
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: Installation problems

[Victoria Reznichenko]

Sherif,
Tuesday, September 24, 2002, 2:16:13 AM, you wrote:

SDM I installed it on Redhat Linux 7.3 using MySQL-Max-3.23.52-1.i386.rpm
SDM While installation it asked me to change DB root password using mysqladmin

SDM I looked for mysqladmin and didn't find it !! not all the files were
SDM installed.

SDM I looked into the documentation and it said for linux, using RPM, install
SDM these 2 files  MySQL-VERSION.i386.rpm  and MySQL-client-VERSION.i386.rpm

SDM I have the rpm for the server but I looked on www.mysql.com for
SDM MySQL-client-VERSION.i386.rpm but could not find any RPMs for the client (or
SDM any installation files to install clinet)

Yes, you need to install MySQL-client-VERSION.i386.rpm, too. Take a
look carefully here:
 http://www.mysql.com/downloads/mysql-3.23.html


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

re: Duplicate key error

[Egor Egorov]

Graham,
Sunday, September 22, 2002, 3:15:23 PM, you wrote:

GH I am aware that this is an error of my own making, however . . .

GH I am unable to install my sql  because the scipt fails with
GH a duplicate key error

GH Preparing db table
GH Preparing host table
GH Preparing user table
GH Preparing func table
GH Preparing tables_priv table
GH Preparing columns_priv table
GH Installing all prepared tables
GH ERROR: 1062  Duplicate entry 'localhost-root' for key 1
GH ERROR: 1062  Duplicate entry 'localhost-root' for key 1
GH 020922 12:55:57  ./bin/mysqld: Shutdown Complete

GH I have removed the database to the best of my ability, and am unable to find
GH any mysql files of anything containing the text localhost-root.  The
GH output however remains the same.

GH I have a mac G4 with os 10.1.5
GH Can anyone advise me how to proceed?

What version of MySQL did you installed? Did you edit mysql_install_db
script?



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




-
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: Remote ServerAccess

[Victoria Reznichenko]

Patrick,
Monday, September 23, 2002, 4:24:13 PM, you wrote:

PF I'm just starting out with MySQL.  How do you set permission to allow for
PF access from a domain or IP.  I found this in the docs.  I this all I need?

PF GRANT ALL PRIVILEGES ON *.* TO monty@% - IDENTIFIED BY 'some_pass' WITH
PF GRANT OPTION; 

Patrick, % in the host field means any host. If you want to allow
connection only from certain host for user, you should specify host
name or IP adddress, like
GRANT ALL ON *.* TO 'someuser'@'ensita.net' IDENTIFIED BY
'soempassword' WITH GRANT OPTION;

Some info you can also find here:
 http://www.mysql.com/doc/en/Connection_access.html


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

RV: Assertion failure with MySQL 4.0.3/InnoDB

[jesus]

Hello,

Recently I have installed MySQL-Max 4.0.3 in my production server, and
in the error log I found the following message (or very similar) several
times:

InnoDB: Assertion failure in thread xxx in file btr0pcur.c line
203

Why takes place this crash?

NOTE: Querys associated to crash dump are very trivial SELECT's that
work OK in 3.23.52. The only similarity among them is the use of ORDER
BY DESC.


Best regards,


José Ceferino Ortega

-
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: access denied ????

[toby -]

thnx gerald 

but i still dont know what to do to fix this thing up 

will someone pleez enlighten me on how to 

thnx a million ...

toby .


From: gerald_clark [EMAIL PROTECTED]
To: toby - [EMAIL PROTECTED]
CC: [EMAIL PROTECTED]
Subject: Re: access denied  
Date: Mon, 23 Sep 2002 09:02:55 -0500

mysql does not own its files.

toby - wrote:

ok guyz 

im lost

im creating my tables and after i ve created em i tried to change two of 
the fields to FULLTEXT and this is what i get :

Database zabanDb running on localhost
Error

SQL-query :

ALTER TABLE `ctnt_inf` ADD FULLTEXT(`summary`)


MySQL said:


Error on rename of '.\zabandb\ctnt_inf.MYI' to 
'.\zabandb\#sql2-5b0-7e.MYI' (Errcode: 13)


ACCESS DENIED ! ! ! ! !

what the hell is rong with this thing ?

cud anyone help me plz 



_
Join the world's largest e-mail service with MSN Hotmail. 
http://www.hotmail.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






_
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: transactions...

[Dana Diederich]

Perhaps the InnoDB/MyISAM gurus can comment on this.  I want to switch from
MyISAM to Innodb, because we have database tables that have many updates,
inserts, deletes and selects going on at the same time.  My belief is that
InnoDB's better locking semantics (as compared to MyISAM) will give me
better overall performance in such a high contention environment.  For
example, each update won't block other updates or selects or deletes.

Is this a fair assumption?  Honestly, the 'other' features of InnoDB
(transactions and reliability) aren't as important to me as overall speed.

Cheers.
-Dana

 -Original Message-
 From: Thomas Seifert [SMTP:[EMAIL PROTECTED]]
 Sent: Monday, September 23, 2002 2:14 AM
 To:   [EMAIL PROTECTED]
 Subject:  Re: transactions...
 
 On Mon, 23 Sep 2002 08:40:59 +0300 (EEST) Iikka Meriläinen
 [EMAIL PROTECTED] wrote:
 
  On Mon, 23 Sep 2002, Daniel Kiss wrote:
  
   Hi!
  
   At 00:56 2002.09.23._ -0300, you wrote:
   Do I loose
   too much performance using InnoDB tables in autocommit mode instead
 of
   using MyISAM tables?
  
   The real quiestion is: Why do you want to use InnoDB tables when you
 don't
   want to use its transaction safe features?
  
  The reason for prefering InnoDB over MyISAM? Performance. Most
 benchmarks
  show InnoDB is slightly faster than MyISAM, and not to mention its some
  other benefits.
  
  But the performance could be a reason on its own to use InnoDB. I don't
  mean that MyISAM is bad, not at all. It's simpler to use, after all. But
  what I mean is that InnoDB can be used in all tasks MyISAM is suitable
  for, and some tasks will be done faster using InnoDB.
 
 But some tasks are really bad in InnoDB, at most selects and worst if it
 comes to table-scans.
 
 
 Thomas
 
 mysql, query
 
 -
 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


**
This email and any files transmitted with it are confidential
and intended solely for the individual or entity to 
whom they are addressed.  If you have received this email
in error destroy it immediately.
**
 Wal-Mart Stores, Inc. Confidential
**


-
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: Complex SQL query problem...

[Edward Peloke]

great!  hope it works !

-Original Message-
From: Richard Bolen [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 23, 2002 9:17 AM
To: MySQL Mailing List (E-mail)
Cc: Edward Peloke
Subject: RE: Complex SQL query problem...


FYI - this query seemed to work.

select * from nodes
left join nodes as n2 on n2.parent_id = nodes.node_id
left join jobs on jobs.parent_id = nodes.node_id
left join colors on colors.parent_id = nodes.node_id
where nodes.node_id = ?
and ((n2.parent_id is not NULL) or (jobs.parent_id is not NULL) or
(colors.parent_id is not NULL))


I need to do some more testing to be sure.

Rich

-Original Message-
From: Edward Peloke [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 23, 2002 08:46
To: Richard Bolen
Subject: RE: Complex SQL query problem...


After I sent this it hit me that it may not work if the first table (jobs)
contained no rows...I believe this would only work if the tables left joined
were empty not the jobs table.  sorry...

I apologize  did not respond Friday but I left work at 4.

Eddie

-Original Message-
From: Richard Bolen [mailto:[EMAIL PROTECTED]]
Sent: Friday, September 20, 2002 4:19 PM
To: Edward Peloke
Subject: RE: Complex SQL query problem...


Does this handle the case where the ID is in the submissions table but not
the jobs table?  How would this look if there was a third table also?

Thanks again for you help!

Rich

-Original Message-
From: Edward Peloke [mailto:[EMAIL PROTECTED]]
Sent: Friday, September 20, 2002 15:59
To: MySQL Mailing List (E-mail)
Subject: RE: Complex SQL query problem...


try a left join

select count(*) from jobs
 left join submissions on
   jobs.standard_id=submissions.color_id
   where jobs.standard_id=ID_VALUE and submissions.color_id is null


Eddie

-Original Message-
From: Richard Bolen [mailto:[EMAIL PROTECTED]]
Sent: Friday, September 20, 2002 2:37 PM
To: MySQL Mailing List (E-mail)
Subject: Complex SQL query problem...


I'm trying to use a sql query to determine if an ID exists in any of 3
different tables in the database.  I need to do this in one SQL query
(ideally only using the ID once in the query).  I'm using mysql 3.23.47.

Here's an example of a query I came up with:

select count(*) from jobs, submissions where ID_VALUE in (jobs.standard_id,
submissions.color_id)


I'm just trying to determine if the ID exists.

This query works *IF AND ONLY IF* there is at least one record in each of
the tables.  If any of the table are empty, this query always returns a
count of 0 (even if there is a match in one of the non-empty tables).

Does anyone know why this is happening or could someone suggest a alternate
query?

Thanks,
Rich


Rich Bolen
Senior Software Developer
GretagMacbeth Advanced Technologies Center
79 T. W. Alexander Drive - Bldg. 4401 - Suite 250
PO Box 14026
Research Triangle Park, North Carolina 27709-4026  USA
Phone:  919-549-7575 x239,  Fax: 919-549-0421

http://www.gretagmacbeth.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


-
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

replication problem

[David Piasecki]

We are having a problem which seems to have appeared recently with
replication on our mysql servers. 

- We have 3 servers set up with mysql 3.23.52 (they were running .49 but
we upgraded in an attempt to fix this problem, which did not work), set
up as a master and 2 slaves to the master.

- There is a large script that runs on the master server. This script
DROPs a large table, then recreates it. It is basically a DROP followed
by a CREATE and many INSERTs.

- Up until recently we have had no issues with this problem. Lately
however the slave servers are not updating. The error we see in the logs
is the table which it attempts to create already exists, therefore from
that point on the replication stops. We can't understand why suddenly
the DROP TABLE doesn't get replicated.

Does anyone have any experience with such a problem? 

Thanks.


David Piasecki



-
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: access denied ????

[Bruce Lewis]

I don't know how you have your system configured, but mine has the root only
functional on the localhost.

Bruce

- Original Message -
From: toby - [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Monday, September 23, 2002 9:22 AM
Subject: Re: access denied 


 thnx gerald 

 but i still dont know what to do to fix this thing up 

 will someone pleez enlighten me on how to 

 thnx a million ...

 toby .


 From: gerald_clark [EMAIL PROTECTED]
 To: toby - [EMAIL PROTECTED]
 CC: [EMAIL PROTECTED]
 Subject: Re: access denied  
 Date: Mon, 23 Sep 2002 09:02:55 -0500
 
 mysql does not own its files.
 
 toby - wrote:
 
 ok guyz 
 
 im lost
 
 im creating my tables and after i ve created em i tried to change two of
 the fields to FULLTEXT and this is what i get :
 
 Database zabanDb running on localhost
 Error
 
 SQL-query :
 
 ALTER TABLE `ctnt_inf` ADD FULLTEXT(`summary`)
 
 
 MySQL said:
 
 
 Error on rename of '.\zabandb\ctnt_inf.MYI' to
 '.\zabandb\#sql2-5b0-7e.MYI' (Errcode: 13)
 
 
 ACCESS DENIED ! ! ! ! !
 
 what the hell is rong with this thing ?
 
 cud anyone help me plz 
 
 
 
 _
 Join the world's largest e-mail service with MSN Hotmail.
 http://www.hotmail.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
 
 




 _
 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

lock statement

[Chris Barnes]

hi people,
just a quick question.

is there a way i can use the lock tables statement to lock all the tables
in a database which out having to specify all tables individually?

i dont really want to use a statement like:
lock tables table1 read, table2 read table3 read, table4 read, table5 read,
table6 read, table7 read, table8 read, table9 read, table10 read;

something like lock tables * read; or lock * write; would be nice but i cant
find anything about that in the mysql documentation.


-
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: access denied ????

[Iikka Meriläinen]

Hello,

If you're on *NIX platform, use this:
chown mysql *
in your data directory. To make things sure, chown every data file to
mysql user (I assume you've created it during install).

On Windows, check the permissions. I apologize for being inaccurate, but
you didn't specify what operating system you're using and whether you have
installed MySQL from source or from a binary package.

Regards,
Iikka

On Mon, 23 Sep 2002, toby - wrote:

 thnx gerald 

 but i still dont know what to do to fix this thing up 

 will someone pleez enlighten me on how to 

 thnx a million ...

 toby .


 From: gerald_clark [EMAIL PROTECTED]
 To: toby - [EMAIL PROTECTED]
 CC: [EMAIL PROTECTED]
 Subject: Re: access denied  
 Date: Mon, 23 Sep 2002 09:02:55 -0500
 
 mysql does not own its files.
 
 toby - wrote:
 
 ok guyz 
 
 im lost
 
 im creating my tables and after i ve created em i tried to change two of
 the fields to FULLTEXT and this is what i get :
 
 Database zabanDb running on localhost
 Error
 
 SQL-query :
 
 ALTER TABLE `ctnt_inf` ADD FULLTEXT(`summary`)
 
 
 MySQL said:
 
 
 Error on rename of '.\zabandb\ctnt_inf.MYI' to
 '.\zabandb\#sql2-5b0-7e.MYI' (Errcode: 13)
 
 
 ACCESS DENIED ! ! ! ! !
 
 what the hell is rong with this thing ?
 
 cud anyone help me plz 
 
 
 
 _
 Join the world's largest e-mail service with MSN Hotmail.
 http://www.hotmail.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
 
 




 _
 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


**
* Iikka Meriläinen   *
* E-mail: [EMAIL PROTECTED] *
* Vaala, Finland *
**


-
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: transactions...

[Paul DuBois]

At 9:40 -0500 9/23/02, Dana Diederich wrote:
Perhaps the InnoDB/MyISAM gurus can comment on this.  I want to switch from
MyISAM to Innodb, because we have database tables that have many updates,
inserts, deletes and selects going on at the same time.  My belief is that
InnoDB's better locking semantics (as compared to MyISAM) will give me
better overall performance in such a high contention environment.  For
example, each update won't block other updates or selects or deletes.

Is this a fair assumption?

Yes.  InnoDB's row-level locking fares better when your query mix contains
many updates, rather than just a lot of selects.  MyISAM tables use table-level
locking, which works well for a mix of selects, but degrades when you start
throwing updates into the mix.

   Honestly, the 'other' features of InnoDB
(transactions and reliability) aren't as important to me as overall speed.

Cheers.
-Dana


-
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: transactions...

[Dana Diederich]

We're probably 98 percent selects.  But that's part of the problem: the
selects build up at a fantastic rate when there are a flury of updates.
Hopefully, once we get to InnoDB, this won't be as likely to happen.

Cheers.
-Dana

 -Original Message-
 From: Paul DuBois [SMTP:[EMAIL PROTECTED]]
 Sent: Monday, September 23, 2002 10:26 AM
 To:   Dana Diederich; [EMAIL PROTECTED]
 Subject:  RE: transactions...
 
 At 9:40 -0500 9/23/02, Dana Diederich wrote:
 Perhaps the InnoDB/MyISAM gurus can comment on this.  I want to switch
 from
 MyISAM to Innodb, because we have database tables that have many updates,
 inserts, deletes and selects going on at the same time.  My belief is
 that
 InnoDB's better locking semantics (as compared to MyISAM) will give me
 better overall performance in such a high contention environment.  For
 example, each update won't block other updates or selects or deletes.
 
 Is this a fair assumption?
 
 Yes.  InnoDB's row-level locking fares better when your query mix contains
 many updates, rather than just a lot of selects.  MyISAM tables use
 table-level
 locking, which works well for a mix of selects, but degrades when you
 start
 throwing updates into the mix.
 
Honestly, the 'other' features of InnoDB
 (transactions and reliability) aren't as important to me as overall
 speed.
 
 Cheers.
 -Dana


**
This email and any files transmitted with it are confidential
and intended solely for the individual or entity to 
whom they are addressed.  If you have received this email
in error destroy it immediately.
**
 Wal-Mart Stores, Inc. Confidential
**


-
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: lock statement

[Paul DuBois]

At 1:13 +1000 9/24/02, Chris Barnes wrote:
hi people,
just a quick question.

is there a way i can use the lock tables statement to lock all the tables
in a database which out having to specify all tables individually?

i dont really want to use a statement like:
lock tables table1 read, table2 read table3 read, table4 read, table5 read,
table6 read, table7 read, table8 read, table9 read, table10 read;

something like lock tables * read; or lock * write; would be nice but i cant
find anything about that in the mysql documentation.

FLUSH TABLES WITH READ LOCK places a read lock on all tables.  This prevents
any updates to the tables.

However, note that this does not work for InnoDB tables, because the InnoDB
handler performs its own locking at a lower level so it doesn't see the
effect of the statement.


-
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: Remote ServerAccess

[Eric Lamendola]

Follow up question,

Well what if you have a user that you want to give permission to from any 
computer in the domain.

Such as user:   mysqluser with READ|SELECT|UPDATE from anywhere 
in:   mydomain.com

But not from any other domain.

Thanks,

Eric Lamendola
Slingo Inc.

 And if not, Hey what do I know 




At 05:17 PM 9/23/02 +0300, Victoria Reznichenko wrote:
Patrick,
Monday, September 23, 2002, 4:24:13 PM, you wrote:

PF I'm just starting out with MySQL.  How do you set permission to allow for
PF access from a domain or IP.  I found this in the docs.  I this all I need?

PF GRANT ALL PRIVILEGES ON *.* TO monty@% - IDENTIFIED BY 'some_pass' WITH
PF GRANT OPTION;

Patrick, % in the host field means any host. If you want to allow
connection only from certain host for user, you should specify host
name or IP adddress, like
 GRANT ALL ON *.* TO 'someuser'@'ensita.net' IDENTIFIED BY
 'soempassword' WITH GRANT OPTION;

Some info you can also find here:
  http://www.mysql.com/doc/en/Connection_access.html


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


-
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: multi-table select (not a join)

[Paul DuBois]

At 0:23 -0700 9/23/02, neal wrote:
Oh man!

Yeah, you're right.  That's exactly what I want but apparently it wasn't
implemented until v4??!?!?!

Right.


What did people do prior to version 4 when needing to query multiple tables?
Just endure the overhead of multiple connections to the database?

Not sure why you'd need multiple *connections*.  You can use multiple
*queries*, for example like this:

CREATE TABLE tmp SELECT ... FROM t1 ...
INSERT INTO tmp SELECT ... FROM t2 ...
INSERT INTO tmp SELECT ... FROM t3 ...

At the end of this, tmp will be the same as if you'd done UNION.
More precisely, as if you'd done UNION ALL, because duplicates won't
be removed.  To remove them, use SELECT DISTINCT when retriving from
tmp.


Thanks.
Neal

-Original Message-
From: Herman Verkade [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 23, 2002 12:14 AM
To: 'neal'
Subject: RE: multi-table select (not a join)


You want to do a UNION. See:
   http://www.mysql.com/doc/en/UNION.html

Regards,
Herman Verkade


  -Original Message-
  From: neal [mailto:[EMAIL PROTECTED]]
  Sent: 23 September 2002 07:57
  To: mySQL
  Subject: multi-table select (not a join)


  How do you select from two tables, without joining those tables?

  I just want the results of one table slapped on the end of
  the results from
  another table because I don't want the overheard of running
  two seperate
  queries.  For instance:

  Lets say that I have these two tables (and thur values):
  Foo Bar
  a1   b1   a3b3  c3
  a2   a2 a4  b4  c4

  I would like a table (resultset) returned something like
  this.  Of course it
  would need an additional field to specify which table the
  values came from:

  a1 b1foo
  a2 b2foo
  a3 b3 c3 bar
  a4 b4 c4 bar

  Anyone know how to do a query like this?

  Thanks.
   Neal


-
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: MyISAM / Performance / Nb of table per DataBase

[Paul DuBois]

At 9:35 +0200 9/23/02, David Bordas wrote:
Hi list,

I still have performance problem with my big MyISAM table.
In fact, mysql keep locking my table, even for doing select :(

That's how MyISAM works -- it uses table locks for everything.
Given that fact, if it didn't lock the table for reading, that would
allow other clients to update the table at the same time -- leading
to disastrous results.

I don't know why but it's very very annoying.
1% of my query are search ones.
Theses queries are slow, too slow.

So i'm thinking about splitting my table to lot of little tables.
Can i have around 5000 Tables under the same database ?
Or should i keep my big table and do not split ?

You might want to try converting the table to InnoDB instead.
That's a lot easier to try that creating thousands of tables.


Thanks
David

Config :
Mysql 3.23.45 ( tar.gz binary )
Linux Red-hat 7.1
kernel 2.4.19
1Go ram


-
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: multi-table select (not a join)

[neal]

Thanks for the suggestion but this would actually create a new table,
correct (the first statement that is)?  I just want a resultset with these
values, without writing to disk.

On another note, yeah youre right not a different connection object, but I
presume I would need to run two seperate queries and recieve back two
seperate resultsets.

Neal


-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 23, 2002 9:21 AM
To: neal; mySQL
Subject: RE: multi-table select (not a join)


At 0:23 -0700 9/23/02, neal wrote:
Oh man!

Yeah, you're right.  That's exactly what I want but apparently it wasn't
implemented until v4??!?!?!

Right.


What did people do prior to version 4 when needing to query multiple
tables?
Just endure the overhead of multiple connections to the database?

Not sure why you'd need multiple *connections*.  You can use multiple
*queries*, for example like this:

CREATE TABLE tmp SELECT ... FROM t1 ...
INSERT INTO tmp SELECT ... FROM t2 ...
INSERT INTO tmp SELECT ... FROM t3 ...

At the end of this, tmp will be the same as if you'd done UNION.
More precisely, as if you'd done UNION ALL, because duplicates won't
be removed.  To remove them, use SELECT DISTINCT when retriving from
tmp.


Thanks.
Neal

-Original Message-
From: Herman Verkade [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 23, 2002 12:14 AM
To: 'neal'
Subject: RE: multi-table select (not a join)


You want to do a UNION. See:
   http://www.mysql.com/doc/en/UNION.html

Regards,
Herman Verkade


  -Original Message-
  From: neal [mailto:[EMAIL PROTECTED]]
  Sent: 23 September 2002 07:57
  To: mySQL
  Subject: multi-table select (not a join)


  How do you select from two tables, without joining those tables?

  I just want the results of one table slapped on the end of
  the results from
  another table because I don't want the overheard of running
  two seperate
  queries.  For instance:

  Lets say that I have these two tables (and thur values):
  Foo Bar
  a1   b1   a3b3  c3
  a2   a2 a4  b4  c4

  I would like a table (resultset) returned something like
  this.  Of course it
  would need an additional field to specify which table the
  values came from:

  a1 b1foo
  a2 b2foo
  a3 b3 c3 bar
  a4 b4 c4 bar

  Anyone know how to do a query like this?

  Thanks.
   Neal


-
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: multi-table select (not a join)

[Paul DuBois]

At 9:55 -0700 9/23/02, neal wrote:
Thanks for the suggestion but this would actually create a new table,
correct (the first statement that is)?  I just want a resultset with these
values, without writing to disk.

Then you must upgrade to 4.x so that you have UNION support.
Either that, or write a client program that issues multiple SELECT
statements and buffers the results in memory.

What's your objection to creating the new table?  Just delete it when
you're done with it.


On another note, yeah youre right not a different connection object, but I
presume I would need to run two seperate queries and recieve back two
seperate resultsets.

Neal


-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 23, 2002 9:21 AM
To: neal; mySQL
Subject: RE: multi-table select (not a join)


At 0:23 -0700 9/23/02, neal wrote:
Oh man!

Yeah, you're right.  That's exactly what I want but apparently it wasn't
implemented until v4??!?!?!

Right.


What did people do prior to version 4 when needing to query multiple
tables?
Just endure the overhead of multiple connections to the database?

Not sure why you'd need multiple *connections*.  You can use multiple
*queries*, for example like this:

CREATE TABLE tmp SELECT ... FROM t1 ...
INSERT INTO tmp SELECT ... FROM t2 ...
INSERT INTO tmp SELECT ... FROM t3 ...

At the end of this, tmp will be the same as if you'd done UNION.
More precisely, as if you'd done UNION ALL, because duplicates won't
be removed.  To remove them, use SELECT DISTINCT when retriving from
tmp.


Thanks.
  Neal


-
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: multi-table select (not a join)

[neal]

I just presume that this will add alot of overhead to the query.  The reason
I wanted to be able to do something like a union rather than seperate
queries is because of overhead.  I dunno ... am I wrong?  Is it not that
bad?

Also, I tried the query you suggested ... can you really do this(?):
insert into tmp select userId from iteneraries

I was getting an error 'near insert into'.  It seems you're trying to
execute a subquery within a query ... can MySQL do this?

Thanks.
Neal



-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 23, 2002 10:09 AM
To: neal; mySQL
Subject: RE: multi-table select (not a join)


At 9:55 -0700 9/23/02, neal wrote:
Thanks for the suggestion but this would actually create a new table,
correct (the first statement that is)?  I just want a resultset with these
values, without writing to disk.

Then you must upgrade to 4.x so that you have UNION support.
Either that, or write a client program that issues multiple SELECT
statements and buffers the results in memory.

What's your objection to creating the new table?  Just delete it when
you're done with it.


On another note, yeah youre right not a different connection object, but I
presume I would need to run two seperate queries and recieve back two
seperate resultsets.

Neal


-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 23, 2002 9:21 AM
To: neal; mySQL
Subject: RE: multi-table select (not a join)


At 0:23 -0700 9/23/02, neal wrote:
Oh man!

Yeah, you're right.  That's exactly what I want but apparently it wasn't
implemented until v4??!?!?!

Right.


What did people do prior to version 4 when needing to query multiple
tables?
Just endure the overhead of multiple connections to the database?

Not sure why you'd need multiple *connections*.  You can use multiple
*queries*, for example like this:

CREATE TABLE tmp SELECT ... FROM t1 ...
INSERT INTO tmp SELECT ... FROM t2 ...
INSERT INTO tmp SELECT ... FROM t3 ...

At the end of this, tmp will be the same as if you'd done UNION.
More precisely, as if you'd done UNION ALL, because duplicates won't
be removed.  To remove them, use SELECT DISTINCT when retriving from
tmp.


Thanks.
  Neal


-
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: multi-table select (not a join)

[Jesse Sheidlower]

On Mon, Sep 23, 2002 at 11:20:49AM -0500, Paul DuBois wrote:

I had a question about the use of the UNION command in this
context.

The original poster asked about getting the name of the
_table_ as well as some other data, which would seem to be
relatively necessary for doing many types of things with the
results of the query. For example, if you issue a query that
gives you the union of seven different tables, and then you
want to do another query based on these results, you'll need
to know which of the seven tables a particular result came
from. The docs on UNION don't seem to address this, none of
the responses mentioned it, and I can't seem to find any
discussion of how to retrieve the table name in a SELECT query
(I acknowledge that most of the time you wouldn't need it, but
in a UNION you might).

How do you get the table name returned as part of the query
results? Or am I misunderstanding how one would work with the
results?

Jesse Sheidlower


-
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: multi-table select (not a join)

[Paul DuBois]

At 10:12 -0700 9/23/02, neal wrote:
I just presume that this will add alot of overhead to the query.  The reason
I wanted to be able to do something like a union rather than seperate
queries is because of overhead.  I dunno ... am I wrong?  Is it not that
bad?

The way to find out is to try it.  Queries that generate large result
sets are likely to write to disk anyway, even without an explicitly
created table.


Also, I tried the query you suggested ... can you really do this(?):
insert into tmp select userId from iteneraries

I was getting an error 'near insert into'.  It seems you're trying to
execute a subquery within a query ... can MySQL do this?

It's not a subquery.

The example I showed below consists of three separate queries.
Make sure to terminate each with a semicolon.  From the error message
you describe, it sounds as though you may have issued them all as
a single statement.


Thanks.
Neal



-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 23, 2002 10:09 AM
To: neal; mySQL
Subject: RE: multi-table select (not a join)


At 9:55 -0700 9/23/02, neal wrote:
Thanks for the suggestion but this would actually create a new table,
correct (the first statement that is)?  I just want a resultset with these
values, without writing to disk.

Then you must upgrade to 4.x so that you have UNION support.
Either that, or write a client program that issues multiple SELECT
statements and buffers the results in memory.

What's your objection to creating the new table?  Just delete it when
you're done with it.


On another note, yeah youre right not a different connection object, but I
presume I would need to run two seperate queries and recieve back two
seperate resultsets.

Neal


-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 23, 2002 9:21 AM
To: neal; mySQL
Subject: RE: multi-table select (not a join)


At 0:23 -0700 9/23/02, neal wrote:
Oh man!

Yeah, you're right.  That's exactly what I want but apparently it wasn't
implemented until v4??!?!?!

Right.


What did people do prior to version 4 when needing to query multiple
tables?
Just endure the overhead of multiple connections to the database?

Not sure why you'd need multiple *connections*.  You can use multiple
*queries*, for example like this:

CREATE TABLE tmp SELECT ... FROM t1 ...
INSERT INTO tmp SELECT ... FROM t2 ...
INSERT INTO tmp SELECT ... FROM t3 ...

At the end of this, tmp will be the same as if you'd done UNION.
More precisely, as if you'd done UNION ALL, because duplicates won't
be removed.  To remove them, use SELECT DISTINCT when retriving from
tmp.


Thanks.
   Neal


-
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

Web browser based GUI for MySQL

[Bill Doerrfeld]

For those of you looking for a Web browser based GUI for MySQL and/or 
who might be considering going with a commercial Web application 
server for connectivity to MySQL, you're cordially invited to check 
out Blue World Lasso Professional 6 (LP6) which was just released 
last week.

Lasso Administration in LP6 allows you to visually build and edit the 
schema of MySQL databases and contains numerous features for 
graphically maintaining MySQL databases (e.g. backup, restore, check, 
repair, import/export, and statistics).

For a free demo and additional details, link to 
http://www.blueworld.com/Lasso6/LassoPro/.

Cheers

Bill
-- 


-
Bill Doerrfeld[EMAIL PROTECTED]
Blue World Communications, Inc.   http://www.blueworld.com/
-
  Build and serve powerful data-driven Web sites
   with Lasso Studio and Lasso Professional.

-
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: multi-table select (not a join)

[Paul DuBois]

At 13:16 -0400 9/23/02, Jesse Sheidlower wrote:
On Mon, Sep 23, 2002 at 11:20:49AM -0500, Paul DuBois wrote:

I had a question about the use of the UNION command in this
context.

The original poster asked about getting the name of the
_table_ as well as some other data, which would seem to be
relatively necessary for doing many types of things with the
results of the query. For example, if you issue a query that
gives you the union of seven different tables, and then you
want to do another query based on these results, you'll need
to know which of the seven tables a particular result came
from. The docs on UNION don't seem to address this, none of
the responses mentioned it, and I can't seem to find any
discussion of how to retrieve the table name in a SELECT query
(I acknowledge that most of the time you wouldn't need it, but
in a UNION you might).

How do you get the table name returned as part of the query
results? Or am I misunderstanding how one would work with the
results?

Jesse Sheidlower

You can't get the name of the table *from the query*.  However,
I assume that you already know the name of the table, or you wouldn't
be able to write the query in the first place. :-)

So just select an extra column:

CREATE TABLE tmp SELECT 't1' AS tbl_name, t1.* FROM t1 ... ;
INSERT INTO tmp SELECT 't2', t2.* FROM t2 ... ;
INSERT INTO tmp SELECT 't3', t3.* FROM t3 ... ;

When you're done, the first column will indicate the name of the
table from which each row was obtained.

-
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! ... with locked threads

[Paul Wagorn (Worldbid)]

for some reason when things get busy, we get a whole ton of locked threads
(see processlist below...), and at some point when there are enough threads,
the whole thing bogs down to a crawl. EVEN after i kill the process that's
holding it all up, it still stays all stuck up.   I've seen people conplain
about this, but haven't seen an answer anywhere.



my questions are:

1.  under what conditions do threads get 'locked'?

2.  the slowdown seems like it may be caused by the server swapping to disk
ie temp files getting created on disk instead of in memory - under what
conditions does this happen?

3.  WHY does one process hold all the others up?  ie what is the bottleneck
that mysql can only do one of at a time?

4.  what is the most likely cause of this?

basically, it seems as though it gets to a certain level of busyness, and
all hell breaks loose  all of a sudden EVERYthing gets slow, and the # of
threads gets up to 400+

the server is NOT out of hard drive space, it's not too busy (load barely
goies above 1 or 2), 1 gig of ram.





mysql show processlist;
+-+--+---+--+-+-
-+--+---
---+
| Id  | User | Host  | db   | Command | Time
| State| Info
|
+-+--+---+--+-+-
-+--+---
---+
|  41 | world| claw.worldbid.com | worldbid | Query   | 0
| NULL | show processlist
|
| 770 | world| claw.worldbid.com | worldbid | Query   | 1
| Locked   | select O.ID, keywords, deletedate, O.company_name,
'', userID,
LEFT(bid,150), stars, O.country, sub |
| 778 | world| claw.worldbid.com | worldbid | Query   | 6
| Copying to tmp table | select O.country, count(1)
FROM Offers_To_Buy as O
JOIN Categories
LEFT JOIN User_Extras
ON(reg |
| 781 | world| claw.worldbid.com | worldbid | Query   | 2
| Locked   | select O.ID, keywords, deletedate, O.company_name,
'', userID,
LEFT(bid,150), stars, O.country, sub |
| 785 | world| claw.worldbid.com | worldbid | Query   | 3
| Locked   | select O.ID, keywords, deletedate, O.company_name,
'', userID,
LEFT(bid,150), stars, O.country, sub |
| 788 | world| claw.worldbid.com | worldbid | Query   | 3
| Locked   | UPDATE User_Extras SET last_visited=CURRENT_DATE()
WHERE registration_id='927269'|
| 789 | world| claw.worldbid.com | worldbid | Query   | 3
| Locked   | SELECT
R.ID,subscription_expiry=CURRENT_DATE(),account_disabled,source,super_user
FROM New_Registra |
| 792 | world| claw.worldbid.com | worldbid | Query   | 3
| Locked   | SELECT count(*) FROM User_Extras
|
| 798 | world| claw.worldbid.com | worldbid | Query   | 3
| Locked   | select password,R.ID,account_disabled FROM
New_Registrations AS R LEFT JOIN User_Extras ON
(R.ID=re |
| 803 | world| claw.worldbid.com | worldbid | Query   | 1
| Locked   | select password,R.ID,account_disabled FROM
New_Registrations AS R LEFT JOIN User_Extras ON
(R.ID=re |
| 805 | unauthenticated user | claw.worldbid.com | NULL | Connect | NULL
| Reading from net | NULL
|
+-+--+---+--+-+-
-+--+---
---+


-
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

Updating table using join / multi-table query

[Michael T. Babcock]

I have a table that is populated with raw data on a pass by an 
extraction program.  Its other fields are left NULL and a secondary 
program parses through this data to fill in the other fields.  I would 
like to move some of this logic out of the second program and into SQL 
statements, but it seems less than encouraging.

create table Data (
ID int unsigned not null auto_increment primary key,
TypeID tinyint unsigned null,
Raw varchar(100)
);

create table Types (
ID tinyint unsigned not null auto_increment primary key,
Name varchar(10)
);

# Program 1:
INSERT INTO Data(Raw) values (Apple), (Orange), (Potato);

# Pre-existing data:
INSERT INTO Types (Name) values (Fruit), (Vegetable), (Other);

# Program 2:
while ($data = each(query(SELECT ID, Raw from Data where TypeID is NULL)))
{
# if $data[Raw] contains Apple or Orange or Kiwi then set 
TypeID = ID for Fruit.
}

...
Could I make my life any simpler by making a cross-reference table, 
etc.?  I'm feeling a bit stuck; all my logic for this is currently in a 
program instead of on the DB side.

-- 
Michael T. Babcock
C.T.O., FibreSpeed Ltd.
http://www.fibrespeed.net/~mbabcock



-
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: multi-table select (not a join)

[Paul DuBois]

At 12:08 -0500 9/23/02, Paul DuBois wrote:
At 9:55 -0700 9/23/02, neal wrote:
Thanks for the suggestion but this would actually create a new table,
correct (the first statement that is)?  I just want a resultset with these
values, without writing to disk.

Then you must upgrade to 4.x so that you have UNION support.
Either that, or write a client program that issues multiple SELECT
statements and buffers the results in memory.

I forgot another possibility.

*IF* your tables all have identical structure and they are MyISAM
tables, you can create a MERGE table from them and query the MERGE
table.  This will select from all the constituent MyISAM tables at
once.

identical = all columns and indexes the same

-
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: multi-table select (not a join)

[Herman Verkade]

Jesse Sheidlower wrote:
 How do you get the table name returned as part of the query
 results? Or am I misunderstanding how one would work with the
 results?

Try:
SELECT *,'TABLEA' FROM TABLEA
UNION
SELECT *,'OTHERTABLE' FROM OTHERTABLE

Herman

-
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: Documentation correction...

[Jon Frisby]

Oh yes, I almost forgot...  The supported type aliases aren't mentioned in
the pseudo-BNF, and they probably should be for the sake of people who are
trying to learn MySQL after having used another DB product...

or  BIT
or  BOOLEAN
or  DOUBLE PRECISION[(length,decimals)] [UNSIGNED] [ZEROFILL]

-JF

 -Original Message-
 From: Jon Frisby [mailto:[EMAIL PROTECTED]]
 Sent: Sunday, September 22, 2002 1:12 PM
 To: mysql
 Subject: Documentation correction...


 Section 6.5.3 of the MySQL documentation has some slight mistakes:

 Under the create_definition: section, the following line appears:
 orCHECK (expr)

 But this syntax doesn't appear to actually be supported yet (it
 produces an
 error rather than being silently ignored).


 Under the type: section, the following lines appear:
 orCHAR(length) [BINARY]
 orVARCHAR(length) [BINARY]

 These should be:
 or[NATIONAL] CHAR[(length)] [BINARY]
 orNCHAR[(length)] [BINARY]
 or[NATIONAL] VARCHAR(length) [BINARY]


 The following lines also appear:
 orFLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
 orDECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
 orNUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]

 But they should be:
 orFLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
 orFLOAT[(precision)] [UNSIGNED] [ZEROFILL]
 orDECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]
 orNUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]


 Also, it may be prudent to make it more clear the full syntax for
 tbl_name
 (optional database prefix, optional `quoting`...) with a section
 describing
 the variations:

 tbl_name:
   [database.]name
 or[`database`.]`name`


 Come to think of it, col_name could benefit from that as well...

 -JF


 -
 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

How can i store japanese into mysql ?

[David yahoo]

Hi,

I try to store japanese into mysql :
PHP seems to works great with japanese strings.
$jap=,g,rfR[fhõ^øiZQlj,SO. ;
And then it displays kanjies.

But I didnt manage to store char into the database ?
How can I do ?


Thanks.


___
Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français !
Yahoo! Mail : http://fr.mail.yahoo.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: Assertion failure with MySQL 4.0.3/InnoDB

[Heikki Tuuri]

Jose,

if this occurs in an SQL query of type

... ORDER BY primarykey DESC

then this is probably the bug which is fixed in upcoming 4.0.4.

I hope 4.0.4 will be released in a few days.

Best regards,

Heikki
Innobase Oy

- Original Message -
From: jesus [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Monday, September 23, 2002 6:00 PM
Subject: RV: Assertion failure with MySQL 4.0.3/InnoDB


 Hello,

 Recently I have installed MySQL-Max 4.0.3 in my production server, and
 in the error log I found the following message (or very similar) several
 times:

 InnoDB: Assertion failure in thread xxx in file btr0pcur.c line
 203

 Why takes place this crash?

 NOTE: Querys associated to crash dump are very trivial SELECT's that
 work OK in 3.23.52. The only similarity among them is the use of ORDER
 BY DESC.


 Best regards,


 José Ceferino Ortega

 -
 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

Access problem with users.

[Julio Muñoz]

Hello,

We have 4000 db and 4000 user (more or less) in MySQL, each db has one
user assigned.
Some times, this is happened more often, show the next error:

ERROR: 1045 'Access denied for user: ‘username@host’ (using password:
YES).

So we kill/restart the mysqld process … after that the user is able to
login.

We have the next escenario.

 HP-UX 11.00
 MySQL 3.23.30 gamma-log
 512 RAM.


My.cnf (for large systems)

# The following options will be passed to all MySQL clients
[client]

#password   = your_password
port= 3306
socket  = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]

port= 3306
socket  = /tmp/mysql.sock

skip-locking
set-variable= key_buffer=256M
set-variable= max_allowed_packet=1M
set-variable= table_cache=256
set-variable= sort_buffer=1M
set-variable= record_buffer=1M
set-variable= myisam_sort_buffer_size=64M
set-variable= thread_cache=8
set-variable= thread_concurrency=8  # Try number of CPU's*2
set-variable= max_connections=200


log-bin
server-id   = 1

# Uncomment the following if you are using BDB tables
#set-variable   = bdb_cache_size=64M
#set-variable   = bdb_max_lock=10

# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname

[mysqldump]
quick
set-variable= max_allowed_packet=16M

[mysql]
no-auto-rehash

#safe-updates
# Remove the comment character if you are not familiar with SQL
[isamchk]
set-variable= key_buffer=128M
set-variable= sort_buffer=128M
set-variable= read_buffer=2M
set-variable= write_buffer=2M

[myisamchk]
set-variable= key_buffer=128M
set-variable= sort_buffer=128M
set-variable= read_buffer=2M
set-variable= write_buffer=2M

[mysqlhotcopy]
interactive-timeout

Thanks in advance.
Julio Cesar Muñoz.
Triara Operations.


-
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: optimizer bug in selecting fields that dont belong to the index used by mysql/innodb

[Heikki Tuuri]

Rafa,

- Original Message -
From: [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Monday, September 23, 2002 10:54 AM
Subject: optimizer bug in selecting fields that don´t belong to the index
used by mysql/innodb


 Description:
   Hello,

   I wrote the following text in my last message (It was an answer to
Monty):

...

   and I have received no answer, so, Have you received the file
clientes.zip?
   Have you tested it?
   Let me know about it.

I am sorry we have not yet had time to process this. We have prepared the
upcoming MySQL-4.0.4.

   Thanks in advance,
   Rafa

Thank you,

Heikki


 How-To-Repeat:
Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'a2618'
And
Cod'059336') Or Nom'a2618' Order by Nom ASC, Cod ASC Limit 50;

 Fix:
 -

 Synopsis:optimizer bug in selecting fields that don´t belong to the index
used by mysql/innodb

 Submitter-Id:   submitter ID
 Originator: Rafa
 Organization:   Pecomark
 MySQL support:  none
 Severity:   non-critical
 Priority:   medium
 Category:   mysqld-max-nt
 Class:  sw-bug
 Release:mysqld 4.0.3 beta(InnoDB)

 Exectutable:   mysqld-max-nt
 Environment:   Pentium III-MMX, 500 MHZ, 540 MB
 System:Windows 2000
 Compiler:  -
 Architecture:  i

 __
 The NEW Netscape 7.0 browser is now available. Upgrade now!
http://channels.netscape.com/ns/browsers/download.jsp

 Get your own FREE, personal Netscape Mail account today at
http://webmail.netscape.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




-
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 3.23.52, foreign keys and update/cascade problem

[j.random.programmer]

Hi all:

I'm seeing a strange problem updating a field if
that field is referenced as a FK in another table.

Consider:

CREATE TABLE A (
 id   INTEGER NOT NULL AUTO_INCREMENT,
 name VARCHAR(20),
 PRIMARY KEY (id)
);

CREATE TABLE B (
 id   INTEGER NOT NULL AUTO_INCREMENT,
 AID  INTEGER,
 name VARCHAR(20),
 PRIMARY KEY (id), 
 INDEX(AID) ,
 FOREIGN KEY (AID) REFERENCES A  (id) 
  ON UPDATE CASCADE
);


mysql insert into A values 
(null, 'one'), (null, 'two');

mysql select * from A;
++--+
| id | name |
++--+
|  1 | one  |
|  2 | two  |
++--+
2 rows in set (0.00 sec)

mysql insert into B values 
(null, 1, 'hello'), (null, 1, 'world');


mysql select * from B;
++--+---+
| id | AID  | name  |
++--+---+
|  1 |1 | hello |
|  2 |1 | world |
++--+---+
2 rows in set (0.00 sec)

AND HERE IS THE PROBLEM:

mysql update A set id = 5 where id = 1;
ERROR 1217: Cannot delete a parent row: a foreign key
constraint fails

 Note, the innodb manual says: 
If you defined ON DELETE CASCADE or SET NULL and
updated the referenced key in the parent row, InnoDB
deleted or updated the child row. This is now changed
to conform to SQL-92: you get the error 'Cannot delete
parent row'.
---

NOTE, There is no 'ON DELETE' constraint in my example
so what on earth is going on ?

Can anyone explain why an update is not getting
cascaded ? I mean, that's the whole POINT of the
update constraint. What am I doing wrong ?

Best regards,

--j


__
Do you Yahoo!?
New DSL Internet Access from SBC  Yahoo!
http://sbc.yahoo.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

MySQL 4.0.3beta on XP...didn't find mysql_fix_privilege_tablesafter install, pull unix download?

[DANIEL GADDIS]

 
I downloaded MySQL 4.0.3beta on my XP system. I didn't find
mysql_fix_privilege_tables after installing. Am I suppose to pull it
from the unix download and do by hand?
 
Thanks,
Daniel

-
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

Wrong path for temp file

[isd]

Description:
While trying to run this query:

SELECT users_1.* FROM users AS users_1, users AS users_2
WHERE users_1.email_address = users_2.email_address AND users_1.id  users_2.id
ORDER BY users_1.email_address

I got this error:

Can't create/write to file '/root/tmp/#sql107d_bdf_2.MYI'  (Errcode: 13)

I know that this is a permission denied error, but the MySQL deamon is running as 
mysql, so it shouldn't 
be trying to write to /root/tmp anyway.  It should be writing to /tmp

How-To-Repeat:
Run above query, or one like it.
Fix:
Unknown

Submitter-Id:  Joshua Kugler
Originator:[EMAIL PROTECTED]
Organization: Associated Students of the University of Alaska Fairbanks
MySQL support: none
Synopsis:  Writing temp file to wrong directory
Severity:  serious
Priority:  low
Category:  mysql
Class: sw-bug
Release:   mysql-3.23.51 (Source distribution)

Environment:

System: Linux deuel.asuaf.org 2.4.17-jjk-20020110 #1 SMP Thu Jan 10 10:35:30 AKST 2002 
i686 unknown
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i586-mandrake-linux-gnu/2.96/specs
gcc version 2.96 2731 (Mandrake Linux 8.1 2.96-0.63.1mdk)
Compilation info: CC='gcc'  CFLAGS=''  CXX='g++'  CXXFLAGS=''  LDFLAGS=''
LIBC: 
lrwxrwxrwx1 root root   13 Sep 10 15:19 /lib/libc.so.6 - libc-2.2.4.so
-rwxr-xr-x1 root root  1285896 Aug  8 15:43 /lib/libc-2.2.4.so
-rw-r--r--1 root root 27474114 Aug  8 15:01 /usr/lib/libc.a
-rw-r--r--1 root root  178 Aug  8 15:01 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr/local/mysql 
--localstatedir=/usr/local/mysql/data --enable-assembler --disable-shared


-
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: DateTime Fields

[b.ware]

clarify exactly what you mean.  show an example..  you
question is confusing.

cw
--- shahana qureshi [EMAIL PROTECTED] wrote:
 Please tell me how can I get the date and time
 values
 diffently in a single query from 'DateTime' field
 and the difference of two DateTime fields in
 HH:MM:SS
 format. 
 Thanks
 shahana
 
 __
 Do you Yahoo!?
 New DSL Internet Access from SBC  Yahoo!
 http://sbc.yahoo.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
 


__
Do you Yahoo!?
New DSL Internet Access from SBC  Yahoo!
http://sbc.yahoo.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

Querying the same field multiple times

[Donna Robinson]

filter fodder: sql, query

Hi,

Hope someone can help with this.

I have a table subjects:
 qsid  smallint ...
 name varchar(60)

The table contains eg.
Mathematics
English
Biology
... ...

I also have a table vx_students:
  vxid smallint ...
  pri_qskey smallint not null default 0
  sec_subj smallint not null default 0
  ter_subj smallint not null default 0

A common entry into vx_students might be:
mysql select * from vx_students where vxid = 1;
+--++---+---+---+
| vxid | studentkey | pri_qskey | sec_qskey | ter_qskey |
+--++---+---+---+
|1 |   1507 |   124 | 77 | 0 |
+--++---+---+---+

The two problems:

(a) I can't figure out how to construct the query to get ALL the subject 
names.  To just get one, this works:
select vxid, subjects.name
from vx_students, subjects
where vx_students.pri_qskey=subjects.qsid
and vx_students.studentkey=1;

(b) I can't figure out how to construct the query to skip if 
eg. ter_qskey == 0 
(although pri_qskey || sec_qskey || ter_qskey could be 0 ).

Here's hoping some whizz out there knows the answers.

Donna





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

[gbu]

Hi

I'm experiencing very strange innodb behavior. I'm testing the following DB
structure: stn|dt|par1|par2|par3 , where stn is 1 to N, dt 1 to M, par1,
par2, par3, par4, par5, par6 are any values and stn+dt is a primary key.
So create statement looks like
CREATE TABLE t3 (stn SMALLINT UNSIGNED NOT NULL,
dt BIGINT UNSIGNED NOT NULL,
par1 TINYINT,
par2 TINYINT,
par3 TINYINT,
par4 TINYINT,
par5 TINYINT,
par6 TINYINT,
PRIMARY KEY (stn, dt)) TYPE=INNODB

The table looks like that:
1   1   9   9   9   9   9   9
1   2   9   9   9   9   9   9
.
2   1   9   9   9   9   9   9
2   2   9   9   9   9   9   9
.
N   M-1 9   9   9   9   9   9
N   M   9   9   9   9   9   9

I'm testing it for N=(4000, 8000) and
M=(1500, 3000, 6000, 12000, 24000).
Test consits of writing down the file for current N and M,
loading it in table via 'LOAD DATA INFILE', commiting changes and then
testing speed of 500 random selects from the table.
For N=4000 all is loking good, but when N=8000 loading time is droping down
drastically when M reaches 3000 or so. I'm talking about 50 times slower
insertion here.
So I simplified the test, lefting just the loading data in table and run it
for N=4000, M=12000 and then for N=8000, M=6000. Notice the data and index
length for this cases are the same.
The first test was finished in a hour, the second was terminated by
me after 12 hour work and it did not even inserted half the rows.
If this helps the following are outputs of innodb monitor for this tests.
Typical output for N=4000, M=12000:
=
020923  3:44:12 INNODB MONITOR OUTPUT
=
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 1858, signal count 1858
Mutex spin waits 1868, rounds 19710, OS waits 132
RW-shared spins 248, OS waits 124; RW-excl spins 9149, OS waits 1602

TRANSACTIONS

Trx id counter 0 1287
Purge done for trx's n:o  0 0 undo n:o  0 0
Total number of lock structs in row lock hash table 0
---TRANSACTION 0 1286, OS thread id 441071616 inserting, active, runs or
sleeps, has 1 lock struct(s), undo log entries 11479793
MySQL thread id 1, query id 16 localhost admin
LOAD DATA INFILE '/db/tmp/df.dat' INTO TABLE t3 FIELDS TERMINATED BY ',' LINES
TERMINATED BY '
'

FILE I/O

I/O thread 0 state: waiting for i/o request
I/O thread 1 state: waiting for i/o request
I/O thread 2 state: waiting for i/o request
I/O thread 3 state: waiting for i/o request
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
42975 OS file reads, 18554 OS file writes, 3895 OS fsyncs
0.00 reads/s, 5.88 writes/s, 2.06 fsyncs/s
-
INSERT BUFFER
-
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
---
LOG
---
Log sequence number 0 671108249
Log flushed up to   0 670978039
Last checkpoint at  0 54962
0 pending log writes, 0 pending chkp writes
1360 log i/o's done, 1.56 log i/o's/second
--
BUFFER POOL AND MEMORY
--
Total memory allocated 246403343; in additional pool allocated 205696
Free list length  0
LRU list length   8187
Flush list length 8001
Buffer pool size  8192
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 61069, created 55116, written 108019
0.00 reads/s, 23.81 creates/s, 16.56 writes/s
Buffer pool hit rate 1000 / 1000
--
ROW OPERATIONS
--
1 queries inside InnoDB, 0 queries in queue; main thread: sleeping
Number of rows inserted 11879792, updated 0, deleted 0, read 0
17659.81 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

END OF INNODB MONITOR OUTPUT


17659.81 inserts/s. Pretty good I guess.
While for N=8000, M=6000 at the beginning monitor output looks the same,
after ~260 rows insert speed begins falling down, and very fast.
And the typical output of monitor becomes like this

=
020923  5:04:24 INNODB MONITOR OUTPUT
=
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 1671, signal count 1670
--Thread 441090048 has waited at btr0cur.c line 357 for 0.00 seconds the
semaphore:
X-lock on RW-latch at 12bcee78 created in file buf0buf.c line 348
a writer (thread id 441090048) has reserved it in mode exclusive
number of readers 0, waiters flag 1
Last time read locked in file buf0flu.c line 466
Last time write locked in file buf0buf.c line 1246
Mutex spin waits 416, rounds 4470, OS waits 42
RW-shared spins 60, OS waits 30; RW-excl spins 4163, OS waits 1599

TRANSACTIONS

Tinytext index howto ?

[David yahoo]

Hi,

I have a column that can have 850 chars at max.
I think that I can only put this in a tinytext col type.

How can I hav an index to this col mysql refuse me to add one ?


Thanks.



___
Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français !
Yahoo! Mail : http://fr.mail.yahoo.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

optimizing inserts

[Jamie Beu]

Where might I find information about optimizing inserts to MySQL tables.

I've purchased 2 books, and so far, the only optimizations I see concern
reads, not writes.

The only advice I've received so far on optimizing writes is to have my
application dump the records to a text file first, and do periodic LOAD
DATA INFILE commands, so as to bulk insert records.

Are there any websites/webpages or books that detail optimizing writes to
MySQL databases (aside from more memory or faster CPU or faster hard
drive).

Thanx,

Jamie Beu
Lockheed-Martin Information Systems
Software Engineer
CTIA
(407) 306-2484

The true soldier fights not because he hates what is in
 front of him, but because he loves what is behind him.
G. K. Chesterton - Illustrated London News, 1/14/11



-
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 3.23.52, foreign keys and update/cascade problem

[Paul DuBois]

At 12:10 -0700 9/23/02, j.random.programmer wrote:
Hi all:

I'm seeing a strange problem updating a field if
that field is referenced as a FK in another table.

Consider:

CREATE TABLE A (
  id   INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR(20),
  PRIMARY KEY (id)
);

CREATE TABLE B (
  id   INTEGER NOT NULL AUTO_INCREMENT,
  AID  INTEGER,
  name VARCHAR(20),
  PRIMARY KEY (id),
  INDEX(AID) ,
  FOREIGN KEY (AID) REFERENCES A  (id)
   ON UPDATE CASCADE
);

I believe that ON UPDATE CASCADE doesn't work yet.



mysql insert into A values
(null, 'one'), (null, 'two');

mysql select * from A;
++--+
| id | name |
++--+
|  1 | one  |
|  2 | two  |
++--+
2 rows in set (0.00 sec)

mysql insert into B values
(null, 1, 'hello'), (null, 1, 'world');


mysql select * from B;
++--+---+
| id | AID  | name  |
++--+---+
|  1 |1 | hello |
|  2 |1 | world |
++--+---+
2 rows in set (0.00 sec)

AND HERE IS THE PROBLEM:

mysql update A set id = 5 where id = 1;
ERROR 1217: Cannot delete a parent row: a foreign key
constraint fails

 Note, the innodb manual says: 
If you defined ON DELETE CASCADE or SET NULL and
updated the referenced key in the parent row, InnoDB
deleted or updated the child row. This is now changed
to conform to SQL-92: you get the error 'Cannot delete
parent row'.
---

NOTE, There is no 'ON DELETE' constraint in my example
so what on earth is going on ?

Can anyone explain why an update is not getting
cascaded ? I mean, that's the whole POINT of the
update constraint. What am I doing wrong ?

Best regards,

--j


-
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: Tinytext index howto ?

[Paul DuBois]

At 22:47 +0200 9/23/02, David yahoo wrote:
Hi,

I have a column that can have 850 chars at max.
I think that I can only put this in a tinytext col type.

Probably not.  The maximum size of TINYTEXT is 255 bytes.  You need
at least a TEXT type.  (MEDIUMTEXT and LONGTEXT will also work, but
are much longer than you need.)


How can I hav an index to this col mysql refuse me to add one ?

Depends on the table type.  ISAM and InnoDB won't allow you to index
BLOB or TEXT columns.  MyISAM and BDB do, but you must index just a
prefix of the column, up to a maximum of 255 characters.


-
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

Insert into multi-tables

[Chuck Payne]

Hi,

I have a question. I have two tables that I want to post data too. Is there
a way to do a sql statement that lets me post data two tables at the same
time?

Thanks,

Chuck Payne



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

[Joe Shear]

Appears to be a database memory corruption problem.  The binlog filename
had been trashed.  Eventually,
mysql/innodb blew up trying to do a malloc.

We were accidentally running two copies of ibbackup at the same time at
around 6pm on 9/21 and 9/22.
We were accidentally running a third copy at around 11am on 9/21 and
9/22.  The 11am backups
appear to have succeeded.  One of the 6pm backups reported success on
each of 9/21 and 9/22.

The binlog index file recorded 2 trashed binlog filenames.  A binlog
file was created on disk with a trashed filename.
The trashed binlog filename has a time-last-modified of 5:05.  At first
glance it would seem that the fact we
were running multiple copies of the backup software was unreleated to
the core dump.



Stack trace looks like:

0x806eea4
0x82d9218
0x83016a7
0x8301261
0x82c0b8e
0x806bf9d
0x806bc0e
0x808748c
0x809d845
0x8076eee
0x8079e3c
0x8074f14
0x80742c7


0x806eea4 handle_segfault__Fi + 428
0x82d9218 pthread_sighandler + 184
0x83016a7 chunk_alloc + 759
0x8301261 malloc + 209
0x82c0b8e my_malloc + 30
0x806bf9d get_lock_data__FP3THDPP8st_tableUibT1 + 113
0x806bc0e mysql_lock_tables__FP3THDPP8st_tableUi + 574
0x808748c open_ltable__FP3THDP13st_table_list13thr_lock_type + 280
0x809d845
mysql_update__FP3THDP13st_table_listRt4List1Z4ItemT2P4ItemUl15enum_duplicates13thr_lock_type
 + 53
0x8076eee mysql_execute_command__Fv + 5322
0x8079e3c mysql_parse__FP3THDPcUi + 216
0x8074f14 do_command__FP3THD + 1460
0x80742c7 handle_one_connection__FPv + 655



.err file looks like


020920 10:26:33  mysqld ended

020920 10:32:20  mysqld started
020920 10:32:26  InnoDB: Log file /dblog/email/ib_logfile0 did
not exist: new to be created
InnoDB: Setting log file /dblog/email/ib_logfile0 size to 48 MB
InnoDB: Database physically writes the file full: wait...
020920 10:32:30  InnoDB: Log file /dblog/email/ib_logfile1 did
not exist: new to be created
InnoDB: Setting log file /dblog/email/ib_logfile1 size to 48 MB
InnoDB: Database physically writes the file full: wait...
020920 10:32:36  InnoDB: Log file /dblog/email/ib_logfile2 did
not exist: new to be created
InnoDB: Setting log file /dblog/email/ib_logfile2 size to 48 MB
InnoDB: Database physically writes the file full: wait...
020920 10:32:42  InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 3061070860
InnoDB: Doing recovery: scanned up to log sequence number 0
3061070860
InnoDB: Last MySQL binlog file position 0 5163, file name
/data/email/mysql_binlog/binlog.927
020920 10:32:44  InnoDB: Flushing modified pages from the buffer
pool...
020920 10:32:44  InnoDB: Started
/usr/sbin/mysqld-max: ready for connections
mysqld got signal 11;
This could be because you hit a bug. It is also possible that
this binary
or one of the libraries it was linked agaist is corrupt,
improperly built,
or misconfigured. This error can also be caused by
malfunctioning hardware.
We will try our best to scrape up some info that will hopefully
help diagnose
the problem, but since we have already crashed, something is
definitely wrong
and this may fail

key_buffer_size=67104768
record_buffer=1044480
sort_buffer=1048568
max_used_connections=39
max_connections=200
threads_connected=11
It is possible that mysqld could use up to 
key_buffer_size + (record_buffer + sort_buffer)*max_connections
= 474330 K
bytes of memory
Hope that's ok, if not, decrease some variables in the equation

Attempting backtrace. You can use the following information to
find out
where mysqld died. If you see no messages after this, something
went
terribly wrong...
Stack range sanity check OK, backtrace follows:
0x806eea4
0x82d9218
0x83016a7
0x8301261
0x82c0b8e
0x806bf9d
0x806bc0e
0x808748c
0x809d845
0x8076eee
0x8079e3c
0x8074f14
0x80742c7
Stack trace seems successful - bottom reached
Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html
and follow instructions on how to resolve the stack trace.
Resolved
stack trace is much more helpful in diagnosing the problem, so
please do 
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd-query at 0x881b288 = UPDATE plx_email set modified = now(),
edit_counter = edit_counter+1, is_global = 1 where address_lcase
= [EMAIL 

Re: mysqlimport

[vinita vigine Murugiah]

HI Egor
OK, I want to create new databases, to do that I need database 
create privilege.

Grant CREATE ON ##
to ddb@localhost IDENTIFIED BY 'passwd'

What should go into #, is it a *? but I should NOT given permission 
to access other databases.
Thanks

Egor Egorov wrote:

vinita,
Monday, September 23, 2002, 9:12:24 AM, you wrote:

vvM I can create a database called test,
vvM mysql create database test;
vvM Query OK, 1 row affected (0.03 sec)

vvM BUT I can't create any other databases???

vvM mysql create database tem;
vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 'tem'

vvM mysql create database proj;
vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 'proj'

Seems user 'ddb' or anonymous user has privileges only on the database
'test'.






-
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 mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysqlimport

[Paul DuBois]

At 9:05 +1000 9/24/02, vinita vigine Murugiah wrote:
HI Egor
OK, I want to create new databases, to do that I need database 
create privilege.

Grant CREATE ON ##
to ddb@localhost IDENTIFIED BY 'passwd'

What should go into #, is it a *? but I should NOT given 
permission to access other databases.

No, it should be db_name.*

Thanks

Egor Egorov wrote:

vinita,
Monday, September 23, 2002, 9:12:24 AM, you wrote:

vvM I can create a database called test,
vvM mysql create database test;
vvM Query OK, 1 row affected (0.03 sec)

vvM BUT I can't create any other databases???

vvM mysql create database tem;
vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 'tem'

vvM mysql create database proj;
vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 'proj'

Seems user 'ddb' or anonymous user has privileges only on the database
'test'.


-
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 mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Autodia SQL patch (for doing sql2dia)

[Bryce Harrington]

Autodia is a tool for automatically generating Dia diagrams from source
code files.  For example, it helps in building inheritance diagrams.  It
handles perl, java, C++, C, and Php.

This patch adds to it a (crude) handler for SQL.  It generates the UML
boxes to describe the tables.  It also puts in lines to link tables
together, but not very well, and is peculiar to my database naming
scheme, so YMMV on that bit.  See README  screenshots for more info.

http://osdl.org/archive/bryce/patches/autodia/

(To do the opposite (i.e., dia2sql), there are a variety of tools such as
tedia2sql worth checking out.)

Bryce Harrington
Open Source Development Lab
http://www.osdl.org/




-
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: optimizing inserts

[Derek Scruggs]

 Where might I find information about optimizing inserts to MySQL tables.

In Paul DuBois's excellent MySQL from New Riders, there is a section about
loading data efficiently in which he talks a little about inserts. In a
nutshell, LOAD DATA is faster than INSERT, the fewer the indexes the faster,
the shorter the statements the faster, let MySQL handle default values...
and a few other things.

If you're using INSERT, the syntax INSERT INTO table VALUES(...),(...),...
is preferred because it allows you to batch multiple inserts.

Which leads me to a follow-on question for Paul if he's reading. If batching
is not an option, is this syntax still faster than INSERT INTO table SET
col=value,... ?

I'm working on an OO app and would like to use the objects for batch imports
 exports. I know this will be slower, of course, but the table
relationships are rather complex, the objects are stable and debugged and
I'd rather not introduce a new uncertainty, especially since import/export
will be used rarely. That said, the objects' insert methods use the SET
col=value syntax and I'm wondering if I should re-write them to use the
VALUES(...) syntax. I'd rather not do that if there's no performance
benefit.

-Derek

PS This book is my bible for MySQL and I highly recommend it.


-
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: optimizing inserts

[Paul DuBois]

At 17:25 -0600 9/23/02, Derek Scruggs wrote:
   Where might I find information about optimizing inserts to MySQL tables.

In Paul DuBois's excellent MySQL from New Riders, there is a section about
loading data efficiently in which he talks a little about inserts. In a
nutshell, LOAD DATA is faster than INSERT, the fewer the indexes the faster,
the shorter the statements the faster, let MySQL handle default values...
and a few other things.

If you're using INSERT, the syntax INSERT INTO table VALUES(...),(...),...
is preferred because it allows you to batch multiple inserts.

Which leads me to a follow-on question for Paul if he's reading. If batching
is not an option, is this syntax still faster than INSERT INTO table SET
col=value,... ?

I'm working on an OO app and would like to use the objects for batch imports
 exports. I know this will be slower, of course, but the table
relationships are rather complex, the objects are stable and debugged and
I'd rather not introduce a new uncertainty, especially since import/export
will be used rarely. That said, the objects' insert methods use the SET
col=value syntax and I'm wondering if I should re-write them to use the
VALUES(...) syntax. I'd rather not do that if there's no performance
benefit.

I am not certain, but I suspect the only difference really lies in
time to parse the different forms of the statement.  I say this because,
parsing time aside, the actions associated internally with each form
of the statement are extremely similar.

Upshot: negligible difference, if any.


-Derek

PS This book is my bible for MySQL and I highly recommend it.


-
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: Case sensitivety behaviour

[Jan Steinman]

From: Moestl, Wolfgang [EMAIL PROTECTED]

Is there a defined behaviour for handling the case-sensitivety for user- and 
hostnames?

According to the specification for the Domain Name System (DNS), Internet hostnames 
are always supposed to be case-insensitive.

Since other entities in MySQL are case-sensitive, this may seem inconsistent, but it 
is imposed by international standards. It is NOT under the control of MySQL.

To get it even more confusing, the values for user and host at the SHOW GRANTS FOR 
[user]@[host] are BOTH FULLY case-sensitive.

If verified, this is a bug. DNS-based hostnames should NEVER be case-sensitive.

The fact that you observed this using the magic hostname localhost may indicate 
that MySQL is cheating by doing its own management of this unique name. Any other 
fully qualified domain name should go through your operating system's address 
resolver, and had better be case-insensitive!

On UNIX and clones: nslookup localhost nslookup Localhost and nslookup LoCaLhOsT 
all answer the same IP.

If case-insensitivity with localhost is important, you might just map some other 
name to your machine and use that instead. This is also a good policy in case you 
later want to move your database to its own machine. For example, I have data 
defined as a CNAME in DNS for the machine I'd normally refer to as localhost. It 
seems to work -- as it should -- if I call it data, Data, dATA, etc.

-- 
: Jan Steinman -- nature photography: http://www.Bytesmiths.com
: Bytesmiths -- artists' services: http://www.Bytesmiths.com/Services
: Join the forums at http://www.Bytesmiths.com/wiki


-
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: mysqlimport

[vinita vigine Murugiah]

Hi paul

Paul DuBois wrote:

 At 9:05 +1000 9/24/02, vinita vigine Murugiah wrote:

 HI Egor
OK, I want to create new databases, to do that I need database 
 create privilege.

 Grant CREATE ON ##
 to ddb@localhost IDENTIFIED BY 'passwd'

 What should go into #, is it a *? but I should NOT given 
 permission to access other databases.


 No, it should be db_name.* 

Then I can ONLY create database called db_name,  then every time when 
I create a database, I have to ask the root to give me create permission 
on xxx database. This is inconvenient. Is there any other work around??
Thanks



 Thanks

 Egor Egorov wrote:

 vinita,
 Monday, September 23, 2002, 9:12:24 AM, you wrote:

 vvM I can create a database called test,
 vvM mysql create database test;
 vvM Query OK, 1 row affected (0.03 sec)

 vvM BUT I can't create any other databases???

 vvM mysql create database tem;
 vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 
 'tem'

 vvM mysql create database proj;
 vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 
 'proj'

 Seems user 'ddb' or anonymous user has privileges only on the database
 'test'.



 -
 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 mysql-unsubscribe-##L=##[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 mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysqlimport

[Paul DuBois]

At 9:41 +1000 9/24/02, vinita vigine Murugiah wrote:
Hi paul

Paul DuBois wrote:

At 9:05 +1000 9/24/02, vinita vigine Murugiah wrote:

HI Egor
OK, I want to create new databases, to do that I need database 
create privilege.

Grant CREATE ON ##
to ddb@localhost IDENTIFIED BY 'passwd'

What should go into #, is it a *? but I should NOT given 
permission to access other databases.


No, it should be db_name.*

Then I can ONLY create database called db_name,  then every time 
when I create a database, I have to ask the root to give me create 
permission on xxx database. This is inconvenient. Is there any other 
work around??

As far as I can tell, you're not making any sense:

- First you say you want to grant permission for a given database, and
   *NOT* to access other databases.
- Now you say you don't want to have to specify permissions for given
   databases.

You can't have it both ways.  Do you want permission for a specific
database, or for all databases?

If I am misinterpreting you, then please be more specific.

Thanks



Thanks

Egor Egorov wrote:

vinita,
Monday, September 23, 2002, 9:12:24 AM, you wrote:

vvM I can create a database called test,
vvM mysql create database test;
vvM Query OK, 1 row affected (0.03 sec)

vvM BUT I can't create any other databases???

vvM mysql create database tem;
vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 'tem'

vvM mysql create database proj;
vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 'proj'

Seems user 'ddb' or anonymous user has privileges only on the database
'test'.



-
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 mysql-unsubscribe-##L=##[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 mysql-unsubscribe-##L=##[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 mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Case sensitivety behaviour

[Paul DuBois]

At 16:43 -0700 9/23/02, Jan Steinman wrote:
  From: Moestl, Wolfgang [EMAIL PROTECTED]

Is there a defined behaviour for handling the case-sensitivety for 
user- and hostnames?

According to the specification for the Domain Name System (DNS), 
Internet hostnames are always supposed to be case-insensitive.

Since other entities in MySQL are case-sensitive, this may seem 
inconsistent, but it is imposed by international standards. It is 
NOT under the control of MySQL.

To get it even more confusing, the values for user and host at the 
SHOW GRANTS FOR [user]@[host] are BOTH FULLY case-sensitive.

If verified, this is a bug. DNS-based hostnames should NEVER be 
case-sensitive.

MySQL behaves like this:

Usernames, passwords, and database and table names are case sensitive in
grant table entries.

Hostnames and column names are not.


The fact that you observed this using the magic hostname 
localhost may indicate that MySQL is cheating by doing its own 
management of this unique name. Any other fully qualified domain 
name should go through your operating system's address resolver, and 
had better be case-insensitive!

localhost is indeed interpreted specially in MySQL.  On UNIX, it means
connect using the UNIX domain socket rather than TCP/IP.  So in this
case, DNS is not involved.

In any case, I do not observe a difference between setting up
user accounts using host 'localhost' versus 'LOCALHOST'.

I *do* observe case sensitive hostname behavior for SHOW GRANTS.
This should not be.  I'll ask about it.


On UNIX and clones: nslookup localhost nslookup Localhost and 
nslookup LoCaLhOsT all answer the same IP.

If case-insensitivity with localhost is important, you might just 
map some other name to your machine and use that instead. This is 
also a good policy in case you later want to move your database to 
its own machine. For example, I have data defined as a CNAME in 
DNS for the machine I'd normally refer to as localhost. It seems 
to work -- as it should -- if I call it data, Data, dATA, etc.

--
: Jan Steinman -- nature photography: http://www.Bytesmiths.com
: Bytesmiths -- artists' services: http://www.Bytesmiths.com/Services
: Join the forums at http://www.Bytesmiths.com/wiki


-
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

Please Help

[gberaj]

Hi

I am a mysql user.  I use a long blob field to store image data.
But whenever I make a deletion with my image data I find that the
hard disk availability is still intact.  There is no change.  What
could be the problem with my delete statement.

Please Help



Regards



Roslee AJ



-
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

Inserting into two tables...

[Chuck \PUP\ Payne]

Ok, Let's try this again, for some reason this didn't post from early today.

I have db that has two tables that I am needing to post the same information
into both tables, I can't use ID. So I am want to see if there is a sql
statement that will let me or how I can do with a php page.

I am sorry to ask, I have looked around to see if there any on the net or in
my mysql and php books but this seems like a weird task.

Chuck Payne
Magi Design and Support


-
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: mysqlimport

[vinita vigine Murugiah]

Paul DuBois wrote:

 At 9:41 +1000 9/24/02, vinita vigine Murugiah wrote:

 Hi paul

 Paul DuBois wrote:

 At 9:05 +1000 9/24/02, vinita vigine Murugiah wrote:

 HI Egor
OK, I want to create new databases, to do that I need database 
 create privilege.

 Grant CREATE ON ##
 to ddb@localhost IDENTIFIED BY 'passwd'

 What should go into #, is it a *? but I should NOT given 
 permission to access other databases.



 No, it should be db_name.*


 Then I can ONLY create database called db_name,  then every time 
 when I create a database, I have to ask the root to give me create 
 permission on xxx database. This is inconvenient. Is there any other 
 work around??


 As far as I can tell, you're not making any sense:

 - First you say you want to grant permission for a given database, and
   *NOT* to access other databases.
 - Now you say you don't want to have to specify permissions for given
   databases.

 You can't have it both ways.  Do you want permission for a specific
 database, or for all databases?

 If I am misinterpreting you, then please be more specific.

HI sorry for the confusion.
well.. I want to create a database where I can give *any* name 
(ofcourse, the database doesn't exist).

Are you trying to say, I need to ask the root, to give me create 
privilege on database xxx, even before it exist, then only I can 
create the database xxx. Then every time when I want to create a new 
database, I need to ask the root to give create privilege ???

thanks


 Thanks



 Thanks

 Egor Egorov wrote:

 vinita,
 Monday, September 23, 2002, 9:12:24 AM, you wrote:

 vvM I can create a database called test,
 vvM mysql create database test;
 vvM Query OK, 1 row affected (0.03 sec)

 vvM BUT I can't create any other databases???

 vvM mysql create database tem;
 vvM ERROR 1044: Access denied for user: 'ddb@localhost' to 
 database 'tem'

 vvM mysql create database proj;
 vvM ERROR 1044: Access denied for user: 'ddb@localhost' to 
 database 'proj'

 Seems user 'ddb' or anonymous user has privileges only on the 
 database
 'test'.




 -
 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 mysql-unsubscribe-##L=##[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 mysql-unsubscribe-##L=##[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 mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Tryout mysql++ Example Loading binary file in a BLOB ...

[Franz Alt]

I wanted to test the mysql++ example Loading binary file in a BLOB ...
because I'm looking for for a solution to load several MB up to a remote DB,
without FTP or things like that. Just 1 thread in DB, so performance should
be no problem.

Therefor I try to execute and benchmark this quite famous Code from the
online-manual, but I've got some Problems (P) and Questions (Q).
I'm using Visual C++ 6 on an Windows98 , 526MHz and 320MB Ram.

I can compile in debug and release-Version. No errors, just warnings like, I
try to translate:
\include\type_info1.hh(39) : warning C4800: 'int' : Variable wird auf
booleschen Wert ('True' oder 'False') gesetzt

Integer set to boolean value true or false
\include\convert1.hh(40) : warning C4273: 'strtol' : Inkonsistente
DLL-Bindung. dllexport angenommen.

assumed inconsistent dll-binding for dllexport
\include\sql_query1.hh(135) : warning C4355: this' : wird in
Initialisierungslisten fuer Basisklasse verwendet

-this is used in init-lists for derived classses
\load_file.cpp(5) : warning C4273: '_errno' : Inkonsistente DLL-Bindung.
dllexport angenommen.

assumed inconsistent dll-binding

Q1: Can I do something against this warnings?
Q2:  or are they taking any affect?

Then something curious: I can execute the code in Release-Mode and it runs
with no runtime error ( but the result is wrong , see below).

P1: When I run in in debug-Mode, I get an SQL-Syntax-Error 1064

Q3: Where can I find error-codes in the online-manual?

Q4: I'm not shure if std::string fill(read_buffer,blen);I included
std::string

P2:Unfortunally, the result ist not ok, since the escape manipulator
doesn't seem to do anything at all.

P3: I tried to fix the code for my VisualC++ compiler by inserting
#include string iostream , using namespace std;  or adding std::
for debug-mode.
 Then I get (often, not always) an Exception in line:
  std::string tmp_str = strbuf.str();
release binary seems to be unchanged ...

This is a sample output of 2 runnings. file aaa contains now data to
escape, but abs does. both are ascii files.

// - OUTPUT
1st
C:\Bauinfo\Projekt_MySQL\mmd_source\Konsolentest\mysql_blob_load\Releasel
abc
release: loading ... abc
load data file: 0s
Length:20
kBytes/second: 1.#INF
// the cout of the whole query
INSERT INTO blobs (blob_data) VALUES(abc / 1 ' 2   3\
)
Error: You have an error in your SQL syntax near '3\
)' at line 1 1064

2nd
C:\Bauinfo\Projekt_MySQL\mmd_source\Konsolentest\mysql_blob_load\Releasel
aaa
release: loading ... aaa
load data file: 0s
Length:28
kBytes/second: 1.#INF

INSERT INTO blobs (blob_data) VALUES(aaa bbb !§$%() 123  x-_
)
INSERT in MySQL: 0s
Length:28
kBytes/second: 1.#INF
//  END OF OUTPUT


P3) Uploaded Binary files cause no SQL-error, but are cut at the first '\0'.
therefore only some bytes are stored in BLOB (messured via phpmyadmin,
SELECT length(blob_data) ...


Has anybody compiled and tried out the original example?


now a last, my lightly changed code:
--

#include windows.h
#include sys/stat.h
#include fstream
#include sqlplus.hh
extern int errno;

#include iostream
#include string


// Für Zeittests

#define _PERFORMANCE

#include time.h
#include cstdlib
#include vector
#include algorithm

#include conio.h

using namespace std;

const char  MY_HOST[]=localhost;
const char  MY_USER[]=franz;
const char  MY_PASSWORD[]=fff123;

const char  MY_DATABASE[]=blobtest;
const char  MY_TABLE[]=blobs;
const char  MY_FIELD[]=blob_data; // BLOB field

int main(int argc, char *argv[]) {


#ifdef _DEBUG // compiled for debugging
  std::string datei = aaa;
  cout  debug: loading ...   datei endl;
  // argc=2;
#endif

#ifndef _DEBUG  // compiled for release
   std::string datei = argv[1];
 if (argc  2) {
  cerr  Usage : load_file full_file_path  endl  endl;
  return -1;
 }
 cout  release: loading ...   datei endl;
#endif

  Connection con(use_exceptions);
 try {
  con.real_connect
(MY_DATABASE,MY_HOST,MY_USER,MY_PASSWORD,3306,(int)0,60,NULL);
  Query query = con.query();
  std::ostrstream strbuf;
  std::ifstream In (datei.c_str(),ios::in | ios::binary);
  struct stat for_len;
  if ((In.rdbuf())-is_open()) {
   if (stat (datei.c_str(),for_len) == -1) return -1;
   unsigned int blen = for_len.st_size;  if (!blen) return -1;

#ifdef _PERFORMANCE
 // Performance Test
 clock_t Start, End;
 double Elapsed;
 Start = clock();
#endif

 char  *read_buffer = new char[blen];
 In.read(read_buffer,blen);
 std::string fill(read_buffer,blen);

#ifdef _PERFORMANCE
 End = clock();
 Elapsed = static_castdouble ( End - Start ) / CLOCKS_PER_SEC ;
 cout  \load data file:\   Elapsed  s  endl;
 cout  Length: blen  endl;
 cout  Bytes/second:   blen / Elapsed  endl;

 // 2nd Performance Test
 Start = clock();
#endif
   strbuf   INSERT INTO   MY_TABLE   (  MY_FIELD  )
VALUES(\   escape  fill   \);
   std::string tmp_str = strbuf.str();  // to have variable in debugger
available
   int l = tmp_str.length();
   cout  endl 

RE: Inserting into two tables...

[Derek Scruggs]

 I have db that has two tables that I am needing to post the same
 information
 into both tables, I can't use ID. So I am want to see if there is a sql
 statement that will let me or how I can do with a php page.

I'm not a SQL guru, but I'm pretty sure SQL doesn't allow this. (Objects in
PostgreSQL and other object databases essentially allow this, but I suspect
the underlying SQL is iterative.)

But it's pretty simple to do. Just create your insert statement inside a
function with a variable for the table name, then call the function with the
table names.

?
function foo($table) {
   $sql=INSERT INTO $table VALUES(...);
   //connect to db
   $result=mysql_query($sql);
   //error handling here
}

foo(table1);
foo(table2);
?

If you need them to have the same ID and you're using autoincrement, you can
add an optional parameter $ID and have the function return last_insert_id if
$ID is not set. Capture the ID the first time you call the function, then
pass that ID as a parameter the second time.

$ID=foo(table1);
foo(table2,$ID);

See http://www.php.net/manual/en/functions.arguments.php for more info on
function arguments in PHP.

-Derek


-
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

Server advice

[Jesse Sheidlower]

I know that the what kind of server should I buy? question
comes up regularly here, but the various discussions in the
archives don't seem to address my issue.

My current server is an old PII desktop with 64M memory and a
6GB hard drive, running FreeBSD. Thanks to the glories of
FreeBSD and the speed of MySQL, and my relatively limited
needs, this has been perfectly adequate up to now--I've never
had any speed-related issues, the thing never crashes (I had a
MySQL process running for about 320 days before someone
accidentally unplugged the computer).  On my busiest days I
don't get more than a few hundred queries, and my current
tables are all pretty simple.

I'm about to start work on a considerably more complex
project, and I'll need to get a new server for it. I have
about 600M of XML that I want to convert to MySQL, and I will
have to do the same thing on a roughly weekly basis--as the
underlying data gets revised elsewhere I'll need to re-import
the whole batch. When it's in the database, I'll then want to
serve it on an intranet, do various statistical analyses,
etc. The final format will involve at least six and possibly
more tables, the largest being about 2 million rows; it will
be heavily indexed. However, while I'll need the final queries
to execute with reasonable speed, I still don't expect a
particularly large amount of traffic. I want to stick with
MyISAM tables, so I can use fulltext indexes (and heavy
concurrent access won't be a major problem, so InnoDB
shouldn't be necessary); I'm using 3.23.39 now and would
probably upgrade to 4.0.X to take advantage of some of the
newer features. I want to stick with FreeBSD.

I'd be grateful for any advice on what my server needs might
be, even if that advice is the familiar bigger, faster,
stronger. My main concern is that doing my weekly importation
of the XML shouldn't take the entire week. Also, I haven't
figured out exactly how I'm going to manage the conversion
(e.g., through an object-relational model, or more directly);
this project is bigger than anything I've worked on before and
I'm trying to approach it with caution.

Thanks.

Jesse Sheidlower

-
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 installing.

[TonyDanna]

This is the mysqlbug report:
##
SEND-PR: -*- send-pr -*-
SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as
SEND-PR: will all comments (text enclosed in `' and `').
SEND-PR:
From: root
To: [EMAIL PROTECTED]
Subject: [50 character or so descriptive subject here (for reference)]

Description:
precise description of the problem (multiple lines)
How-To-Repeat:
code/input/activities to reproduce the problem (multiple
lines)
Fix:
how to correct or work around the problem, if known (multiple
lines)

Submitter-Id:  submitter ID
Originator:root
Organization:
 organization of PR author (multiple lines)
MySQL support: [none | licence | email support | extended email support
]
Synopsis:  synopsis of the problem (one line)
Severity:  [ non-critical | serious | critical ] (one line)
Priority:  [ low | medium | high ] (one line)
Category:  mysql
Class: [ sw-bug | doc-bug | change-request | support ] (one
line)
Release:   mysql-3.23.51 (Source distribution)

Environment:
machine, os, target, libraries (multiple lines)
System: Linux DjinN 2.4.18-5 #1 Mon Jun 10 15:31:48 EDT 2002 i686
unknown
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 2731 (Red Hat Linux 7.3 2.96-110)
Compilation info: CC='gcc'  CFLAGS=''  CXX='g++'  CXXFLAGS='' 
LDFLAGS=''
LIBC:
lrwxrwxrwx1 root root   13 Jul 21 15:26 /lib/libc.so.6
- libc-2.2.5.so
-rwxr-xr-x1 root root  1260480 Apr 15 08:44
/lib/libc-2.2.5.so
-rw-r--r--1 root root  2310808 Apr 15 09:02 /usr/lib/libc.a
-rw-r--r--1 root root  178 Apr 15 08:55 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr/local/mysql/

#
I am gathering more information. I am running a default RedHat 7.3
install. I have done everything that is shown on the documentation on
the site. I installed it with no flags, then with flags, and still... I
have GCC 2.96. Let me know if there is something that I am missing.
Thanks !

Tony Danna


~


-
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 mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Re: mysqlimport

[Paul DuBois]

sql,query


At 11:49 +1000 9/24/02, vinita vigine Murugiah wrote:
Paul DuBois wrote:

At 9:41 +1000 9/24/02, vinita vigine Murugiah wrote:

Hi paul

Paul DuBois wrote:

At 9:05 +1000 9/24/02, vinita vigine Murugiah wrote:

HI Egor
 OK, I want to create new databases, to do that I need
database create privilege.

Grant CREATE ON ##
to ddb@localhost IDENTIFIED BY 'passwd'

What should go into #, is it a *? but I should NOT given
permission to access other databases.



No, it should be db_name.*


Then I can ONLY create database called db_name,  then every time
when I create a database, I have to ask the root to give me create
permission on xxx database. This is inconvenient. Is there any
other work around??


As far as I can tell, you're not making any sense:

- First you say you want to grant permission for a given database, and
*NOT* to access other databases.
- Now you say you don't want to have to specify permissions for given
databases.

You can't have it both ways.  Do you want permission for a specific
database, or for all databases?

If I am misinterpreting you, then please be more specific.

HI sorry for the confusion.
well.. I want to create a database where I can give *any* name
(ofcourse, the database doesn't exist).

Are you trying to say, I need to ask the root, to give me create
privilege on database xxx, even before it exist, then only I can
create the database xxx. Then every time when I want to create a
new database, I need to ask the root to give create privilege ???

Well, yes, you need to have privileges on a database to be able to create
it.  However, if you have the global CREATE privilege, you can create *any*
database.  So maybe that's what you want.  The GRANT statement for
this will look like:

GRANT CREATE ON *.* TO ddb@localhost IDENTIFIED BY 'passwd';

*.* as a level specifier means global.

Is that what you want?

-
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 mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php