Re: ~BCP for mysql~

[Ehrwin Mina]

Hi,

You can try to use the command select into outfile.

thanks,

ehrwin


At 03:29 PM 3/22/2006, Mohammed Abdul Azeem wrote:

Hi,

Can we any use bcp to perform bulk copy for MYSQL as in SYBASE ? If not,
is there any other tool which can do the same operation. I need to
perform a "bcp out" from Mysql and then "bcp in" to Sybase. Please help
me out regarding the same.

Thanks in advance,
Abdul.


This email has been Scanned for Viruses!
  www.newbreak.com



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



Ehrwin C. Mina

Chikka Phils. Inc.

A CMMI LEVEL 5,
AND A ISO 9001:2000; ISO 15000:2002; ISO 17799:2002 CERTIFIED COMPANY


This message and any attachment are confidential and may be 
privileged or otherwise protected from disclosure. If you are not the 
intended recipient, you must not copy this message or attachment or 
disclose the contents to any other person. If you have received this 
transmission in error, please notify the sender immediately and 
delete the message and any attachment from your system. Chikka does 
not accept liability for any omissions or errors in this message 
which may arise as a result of E-Mail-transmission or for damages 
resulting from any unauthorized changes of the content of this 
message and any attachment thereto. Chikka does not guarantee that 
this message is free of viruses and does not accept liability for any 
damages caused by any virus transmitted therewith.



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

Re: ~BCP for mysql~

[Douglas Sims]

Try "SELECT INTO OUTFILE..."

Here's the MySQL documentation on that: http://dev.mysql.com/doc/ 
refman/5.0/en/select.html


Good luck!



On Mar 22, 2006, at 1:29 AM, Mohammed Abdul Azeem wrote:


Hi,

Can we any use bcp to perform bulk copy for MYSQL as in SYBASE ? If  
not,

is there any other tool which can do the same operation. I need to
perform a "bcp out" from Mysql and then "bcp in" to Sybase. Please  
help

me out regarding the same.

Thanks in advance,
Abdul.


This email has been Scanned for Viruses!
  www.newbreak.com



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


Douglas Sims
[EMAIL PROTECTED]




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

~BCP for mysql~

[Mohammed Abdul Azeem]

Hi,

Can we any use bcp to perform bulk copy for MYSQL as in SYBASE ? If not,
is there any other tool which can do the same operation. I need to
perform a "bcp out" from Mysql and then "bcp in" to Sybase. Please help
me out regarding the same.

Thanks in advance,
Abdul.


This email has been Scanned for Viruses!
  www.newbreak.com



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

Re: Please help: recovering db from crash

[Foo Ji-Haw]

Thanks for coming to the rescue, Mark and Bruce.

Mark Leith wrote:

This is actually for Linux/Unix, not Windows.

What error do you get from MySQL when trying to log in? Does the 
mysqld(-nt) process show within Task Manager? What does the new error 
log say?


You may need to reset permissions:

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

Or you may need to set the appropriate datadir / basdir etc. depending 
on how you set up MySQL:


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

Best regards

Mark

Adrian Bruce wrote:


not sure, but it may be worth trying the following

run the script:
mysql_install_db --user=root
In the installation dir

this should change ownership and make mysql recognise the data dir.

good luck
Ade

Foo Ji-Haw wrote:


Hi all,

My Windows-based database server crashed (no fault of MySQL. 
probably OS or hardware), and I managed to copy out the data files. 
I am using version 5.0 of the Essentials package.


I tried to install a similar setup on another server, then copy the 
data\ folder over. The MySQL service starts, but I am not able to 
login, even as root.


Is there anyone who can advise me on the recovery steps?

Appreciate your feedback!









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

Re: ~Mysql cluster info~

[Dan Trainor]

Good evening -

I'd like to chime in saying that I've been using 5.1.7 with a lot of 
success.  I'm sure there's a bit to go with it's development, but half 
the stuff that the MySQL dev team is working on, we will never use. 
Your case may vary.


I can't speak for the MySQL guys, but as far as my testing is concerned, 
I've seen 5.1.7 to be very nice so far - specifically in regards to cluster.


Thanks
-dant



Jimmy Guerrero wrote:

Hello,

Not at this time, currently 5.1 is in Beta. 


We should see a release candidate soon, but I can't commit to a specific
date at this time.

Thanks,

Jimmy Guerrero, Senior Product Manager
MySQL Inc, www.mysql.com
Houston, TX USA






-Original Message-
From: Mohammed Abdul Azeem [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, March 21, 2006 9:30 PM

To: Jimmy Guerrero
Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: RE: ~Mysql cluster info~


Hello Jimmy,

Do we have a production release of MYSQL cluster 5.1 ? If yes 
please let me know the path from where i can download the same.


Thanks in advance,
Abdul.

On Tue, 2006-03-21 at 07:16 -0600, Jimmy Guerrero wrote:

Hello,

MySQL 5.0 Cluster is an in-memory database. Meaning that the entire 
database (tables, indexes, etc.) must fit in RAM along with 
your other 

OS and application processes.

In 5.1, we have introduced disk-based data support. Note, that 
although data can now be stored on disk, indexes must still 
reside in 

memory.

Might be worth checking out, however 55 GB is def. on the 
large size 

for a MySQL Cluster configuration.

Jimmy Guerrero, Senior Product Manager
MySQL Inc, www.mysql.com






-Original Message-
From: Mohammed Abdul Azeem [mailto:[EMAIL PROTECTED]
Sent: Monday, March 20, 2006 9:33 PM
To: mysql@lists.mysql.com
Subject: ~Mysql cluster info~


Hi,

Iam new to clustering in mysql. I went through the reference
manual 5.0 and found that the RAM memory requirements for 
implementing a cluster is almost twice the size of the database.


My problem is i have a database which is 55GB. So does it
mean that i need to have 110 GB RAM memory ? Can anyone let 
me know whether it is possible for me to configure a cluster 
for such a huge database. If yes, how am i suppose to proceed 
( regarding memory requirements ).


Thanks in advance,
Abdul.


This email has been Scanned for Viruses!
  www.newbreak.com



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







This email has been Scanned for Viruses!
  www.newbreak.com









--
- Dan Trainor
- id-Confirm, Inc.
- Direct:  720.241.5580

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

Re: mysql query and version problem .... Help!

[Gregory Machin]

On 3/21/06, Addison, Mark <[EMAIL PROTECTED]> wrote:
>
> > -Original Message-
> > From: Gregory Machin [mailto:[EMAIL PROTECTED]
> > Sent: 21 March 2006 11:28
> > To: mysql@lists.mysql.com
> > Subject: mysql query and version problem  Help!
> >
> > Hi.
> >
> > I have just found out that my hosting provider is using mysql
> > 4 and I'm
> > using mysql 5 the one query I need wont work and is a key
> > feature in the
> > application .. here is the query i'm using
> >
> > SELECT dealer_id, auto_id, bid_amount FROM bids b1 WHERE
> > bid_amount=(SELECT
> > MAX(b2.bid_amount)
> > FROM bids b2 WHERE b1.auto_id = b2.auto_id) AND
> > auto_dealer_id = '3' AND
> > Bid_Status = '1';
> >
> > How do I get this to work on version 4 ?
>
> You could create a tmp table with the max bids and then join on
> that. Something like:
>
> CREATE TEMPORARY TABLE max_bids
> SELECT auto_id, MAX(bid_amount) AS max_bid_amount
> FROM bids
> GROUP BY auto_id;
> SELECT dealer_id, b1.auto_id, bid_amount FROM bids b1, max_bids b2
> WHERE b1.auto_id = b2.auto_id
> AND bid_amount=max_bid_amount
> AND auto_dealer_id = '3' AND Bid_Status = '1';
>
> mark
> --


I thought about that but I'm worried about the users getting the rite data
if multiple users make the same requests at the same time  ...  i supose the
easiest would be to name the temp tables after the user making the request
??

RE: ~Mysql cluster info~

[Jimmy Guerrero]

Hello,

Not at this time, currently 5.1 is in Beta. 

We should see a release candidate soon, but I can't commit to a specific
date at this time.

Thanks,

Jimmy Guerrero, Senior Product Manager
MySQL Inc, www.mysql.com
Houston, TX USA




> -Original Message-
> From: Mohammed Abdul Azeem [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, March 21, 2006 9:30 PM
> To: Jimmy Guerrero
> Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com
> Subject: RE: ~Mysql cluster info~
> 
> 
> Hello Jimmy,
> 
> Do we have a production release of MYSQL cluster 5.1 ? If yes 
> please let me know the path from where i can download the same.
> 
> Thanks in advance,
> Abdul.
> 
> On Tue, 2006-03-21 at 07:16 -0600, Jimmy Guerrero wrote:
> > Hello,
> > 
> > MySQL 5.0 Cluster is an in-memory database. Meaning that the entire 
> > database (tables, indexes, etc.) must fit in RAM along with 
> your other 
> > OS and application processes.
> > 
> > In 5.1, we have introduced disk-based data support. Note, that 
> > although data can now be stored on disk, indexes must still 
> reside in 
> > memory.
> > 
> > Might be worth checking out, however 55 GB is def. on the 
> large size 
> > for a MySQL Cluster configuration.
> > 
> > Jimmy Guerrero, Senior Product Manager
> > MySQL Inc, www.mysql.com
> > 
> > 
> > 
> > 
> > > -Original Message-
> > > From: Mohammed Abdul Azeem [mailto:[EMAIL PROTECTED]
> > > Sent: Monday, March 20, 2006 9:33 PM
> > > To: mysql@lists.mysql.com
> > > Subject: ~Mysql cluster info~
> > > 
> > > 
> > > Hi,
> > > 
> > > Iam new to clustering in mysql. I went through the reference
> > > manual 5.0 and found that the RAM memory requirements for 
> > > implementing a cluster is almost twice the size of the database.
> > > 
> > > My problem is i have a database which is 55GB. So does it
> > > mean that i need to have 110 GB RAM memory ? Can anyone let 
> > > me know whether it is possible for me to configure a cluster 
> > > for such a huge database. If yes, how am i suppose to proceed 
> > > ( regarding memory requirements ).
> > > 
> > > Thanks in advance,
> > > Abdul.
> > > 
> > > 
> > > This email has been Scanned for Viruses!
> > >   www.newbreak.com
> > > 
> > > 
> > > 
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > > http://lists.mysql.com/[EMAIL PROTECTED]
> > > 
> > > 
> > 
> > 
> 
> 
> This email has been Scanned for Viruses!
>   www.newbreak.com
> 
> 
> 


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

RE: ~Mysql cluster info~

[Mohammed Abdul Azeem]

Hello Jimmy,

Do we have a production release of MYSQL cluster 5.1 ? If yes please let
me know the path from where i can download the same.

Thanks in advance,
Abdul.

On Tue, 2006-03-21 at 07:16 -0600, Jimmy Guerrero wrote:
> Hello,
> 
> MySQL 5.0 Cluster is an in-memory database. Meaning that the entire database
> (tables, indexes, etc.) must fit in RAM along with your other OS and
> application processes.
> 
> In 5.1, we have introduced disk-based data support. Note, that although data
> can now be stored on disk, indexes must still reside in memory.
> 
> Might be worth checking out, however 55 GB is def. on the large size for a
> MySQL Cluster configuration.
> 
> Jimmy Guerrero, Senior Product Manager
> MySQL Inc, www.mysql.com
> 
> 
> 
> 
> > -Original Message-
> > From: Mohammed Abdul Azeem [mailto:[EMAIL PROTECTED] 
> > Sent: Monday, March 20, 2006 9:33 PM
> > To: mysql@lists.mysql.com
> > Subject: ~Mysql cluster info~
> > 
> > 
> > Hi,
> > 
> > Iam new to clustering in mysql. I went through the reference 
> > manual 5.0 and found that the RAM memory requirements for 
> > implementing a cluster is almost twice the size of the database.
> > 
> > My problem is i have a database which is 55GB. So does it 
> > mean that i need to have 110 GB RAM memory ? Can anyone let 
> > me know whether it is possible for me to configure a cluster 
> > for such a huge database. If yes, how am i suppose to proceed 
> > ( regarding memory requirements ).
> > 
> > Thanks in advance,
> > Abdul.
> > 
> > 
> > This email has been Scanned for Viruses!
> >   www.newbreak.com
> > 
> > 
> > 
> > -- 
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]
> > 
> > 
> 
> 


This email has been Scanned for Viruses!
  www.newbreak.com



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

Problem restarting server

[Mike Blezien]

Hello,

we had to reboot our server and now we can't get MySQL started, in the error log 
it states:


==
060321 19:34:13  mysqld started
060321 19:34:13 [Warning] Asked for 196608 thread stack, but got 126976
InnoDB: Error: log file ./ib_logfile0 is of different size 0 67108864 bytes
InnoDB: than specified in the .cnf file 0 5242880 bytes!
060321 19:34:13 [ERROR] Can't init databases
060321 19:34:13 [ERROR] Aborting

060321 19:34:13 [Note] /usr/sbin/mysqld: Shutdown complete

060321 19:34:13  mysqld ended
=


How can this problem be fixed so we can restart MySQL server, kind of in a bind 
here now...


TIA,
Mike

Mike(mickalo)Blezien
===
Thunder Rain Internet Publishing
Providing Internet Solution that Work
http://www.thunder-rain.com
=== 



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

[RESOLVED] Re: copy one field value to another

[Ravi Malghan]

Thanks Bill.

--- Bill Adams <[EMAIL PROTECTED]> wrote:

> UPDATE table SET field_a=field_b;
> 
> b.
> 
> 
> Ravi Malghan wrote:
> 
> >Hi: is there a way to copy all field values from
> one
> >field to another from the mysql prompt?
> >
> >Thanks
> >Ravi
> >
> >__
> >Do You Yahoo!?
> >Tired of spam?  Yahoo! Mail has the best spam
> protection around 
> >http://mail.yahoo.com 
> >
> >  
> >
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
>
http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


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

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

Re: RIGHT JOIN better than INNER JOIN?

[Rhino]

Robert,

Your restatement of your original question uses "FULL JOIN" as if it means 
the same things as "INNER JOIN": that's simply not correct. A full join 
contains the results of an inner join PLUS the "orphan rows" from the 
right-hand table in the join PLUS the "orphan rows" from the left-hand table 
in the join. Furthermore, the last time I checked, which was probably at 
least a year ago now, MySQL didn't support a full join.


--
Rhino

- Original Message - 
From: "Robert DiFalco" <[EMAIL PROTECTED]>

To: "Martijn Tonies" <[EMAIL PROTECTED]>; 
Sent: Tuesday, March 21, 2006 2:04 PM
Subject: RE: RIGHT JOIN better than INNER JOIN?


For me the argument is a little pedantic. The contract of the descriptor
table is that it must reference a name; there is code and constraints to
enforce this. I am happy to have the query return nulls to indicate a
programming error that can be quickly addressed. _If_ (after buffer
tuning et al) a RIGHT JOIN still provides a substantial performance
improvement over a FULL JOIN in this case, my customers would want me to
provide that rather than have me tell them it is an "inappropriate join"
or that I am asking the database server developers to improve their
query optimizer.

I wasn't really looking to get into a philosophical debate on
correctness so let me restate my question a little better.

Is there a reason in MySQL/InnoDB why a RIGHT JOIN would substantially
out perform a FULL JOIN in those cases where the results would be
identical? It is a little difficult to test query performance
empirically since performance will change as different indices are
swapped in and out of memory buffers and such (although I have turned
query caching off), but it appears that for a table with 1-2 million
rows a query similar to what I posted here was faster with a RIGHT JOIN.

R.

-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 21, 2006 9:43 AM
To: mysql@lists.mysql.com
Subject: Re: RIGHT JOIN better than INNER JOIN?

Robert,


Of course one should use the right JOIN for the job. But let me ask
you, which join would you use here?

You have a table called Descriptors, it has a field called nameID
which is a unique key that relates to a Names table made up of a
unique identity and a VARCHAR name. I think most people would write a
simple query like this:

   SELECT desc., names.name
   FROM desc JOIN names ON desc.nameId = names.Id
   ORDER BY names.name

However, it this really correct? Every descriptor has a record in
names, so it could be equivalently written as:

   SELECT desc., names.name
   FROM desc RIGHT JOIN names ON desc.nameId = names.Id
   ORDER BY names.name

My guess is that most people conventionally write the first query.


Gee, I wonder why ... This happens to be the query that returns the rows
as it should.

What happens, if two years from now you didn't document WHY you wrote a
"right join" query instead of an inner join and someone figures out that
this could return nulls for a result column?


If you start using the wrong joins, you will make things harder on
yourself and others.


As I said: if performance isn't satisfactory (which sounds a bit strange
for this situation), then try to solve that. Either by using different
index/buffer/caching strategies or by complaining to the people who
created the database system in the first place.



Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

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




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


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.6/286 - Release Date: 20/03/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.6/286 - Release Date: 20/03/2006


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

Re: copy one field value to another

[Bill Adams]

UPDATE table SET field_a=field_b;

b.


Ravi Malghan wrote:


Hi: is there a way to copy all field values from one
field to another from the mysql prompt?

Thanks
Ravi

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

 



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

copy one field value to another

[Ravi Malghan]

Hi: is there a way to copy all field values from one
field to another from the mysql prompt?

Thanks
Ravi

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

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

Wrapping my brain around multi-parental graphs

[sheeri kritzer]

Hi all,

So, I understand the nested set idea described here:

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
http://www.sitepoint.com/article/hierarchical-data-database/2

However, from what I remember of trees and searching and stuff, a node
having more than one parent breaks this model.  For instance, in the
example given in the first article, what if you wanted to put a
"portable TV" in both "Televisions" and "Portable Electronics"?

Theoretically I guess I could just have a table with:

label lft rgt

with no categoryId, and make the entire row the primary key (ie, there
could be, say:

label | lft | rgt
portable tv | 3.2 | 3.8
portable tv | 18.2 | 18.8

in other words, have 2 entries for portable TV.  That's actually
feasible in the data model I'm thinking of, and wouldn't get too out
of hand, however, there's got to be a better way

Any enlightenment?

-Sheeri

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

Re: innodb in 4.1.18

[Bill Adams]

InnoDB always needs the shared tablespace because it puts its  
internal data dictionary and undo logs there. The .ibd files are  
not sufficient for InnoDB to operate.


well, thats what I found before. But it doesn't explain why InnoDB 
does need a logfile even when all transactions are committed or rolled 
back and mysql is shut down.


More is stored in the innodb log files besides a strict log of events.

It does explain, that I can use innodb_file_per_table which gives the 
impression that with that option a shared ibdata isn't required. Or 
maybe it is required during runtime, but not for a backup.


You might be able to set e.g. "innodb_data_file_path = ibdata:0M" but in 
any event the innodb log files track the existence of the different 
ibdata files (size, if they have been formatted, etc).


So with InnoDB it is not possible to shutdown the database server, 
backup the files and maybe use them on another server if the other 
server already has an ibdata and ib_logfile? Do I always have to do a 
mysqldump then? That takes much longer.


You can. But direct file system operations on MySQL (really any 
database) are dangerous. Same thing with why IMHO you should use "PURGE 
MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY);" rather than just 
deleting your binary logs directly (if you are using replication and all 
that). If you are going to copy the InnoDB files you probably need to 
have an exact setup. And as I am sure you have figured out you have to 
back up the .idb files and the innodb log files at the same time.


AFAIK, You cannot take the .idb file from one server and copy it to 
another server and have it just work (like you can with the MyISAM files).


b.


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

Re: drop table that doesn't exist

[James Harvard]

Not a db setting - the DROP statement itself.
http://dev.mysql.com/doc/refman/5.0/en/drop-table.html

HTH,
James Harvard

At 1:56 pm -0700 21/3/06, ChadDavis wrote:
>I'm runnning a script that creates a few tables.  I have line that drops the
>tables before the creation of the tables just in case they  already exist (
>such as on a re-creation of the tables ).
>
>mysql is giving me the following error on the drop table when I run the
>script the first time -- i.e. when there isn't a table to drop yet.
>
> ERROR 1051 at line 1: Unknown table 'MyTable'
>
>Is there a global db setting that will allow this statement to occur without
>the error?


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

drop table that doesn't exist

[ChadDavis]

I'm runnning a script that creates a few tables.  I have line that drops the
tables before the creation of the tables just in case they  already exist (
such as on a re-creation of the tables ).

mysql is giving me the following error on the drop table when I run the
script the first time -- i.e. when there isn't a table to drop yet.

 ERROR 1051 at line 1: Unknown table 'MyTable'

Is there a global db setting that will allow this statement to occur without
the error?

Re: Cannot select the database

[mysql]

>From the 5.0.x manual

How to create user accounts:

The next examples create three accounts and give them access 
to specific databases. Each of them has a username of custom 
and password of obscure. 


To create the accounts with GRANT, use the following 
statements: 

shell> mysql --user=root mysql
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON bankaccount.*
-> TO 'custom'@'localhost'
-> IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON expenses.*
-> TO 'custom'@'whitehouse.gov'
-> IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON customer.*
-> TO 'custom'@'server.domain'
-> IDENTIFIED BY 'obscure';


 The three accounts can be used as follows: 

The first account can access the bankaccount database, but 
only from the local host. 

The second account can access the expenses database, but 
only from the host whitehouse.gov. 

The third account can access the customer database, but only 
from the host server.domain. 

So you need 1 accout to access your database from localhost, 
and another account (with the same password to avoid 
confusion) to access your database from any other domain 
apart from localhost.

*
You also need to read this Charles, then you will know how 
mysql checks who is authorised to connect to the server.

http://dev.mysql.com/doc/refman/4.1/en/connection-access.html
*

HTH

Keith

In theory, theory and practice are the same;
in practice they are not.


On Tue, 21 Mar 2006, Charles Gambrell wrote:

> To: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
> From: Charles Gambrell <[EMAIL PROTECTED]>
> Subject: Re: Cannot select the database
> 
> Thanks for the links.
> 
> The link on privilge had a link to a discussion of "access denied."
> http://dev.mysql.com/doc/refman/4.1/en/access-denied.html
> 
> Where I read -
> 
> "If you have access problems with a Perl, PHP, Python, or ODBC
> program, try to connect to the server with mysql -u user_name  db_name
> or mysql -u user_name  -pyour_pass  db_name. If you are able to
> connect using the mysql client, the problem lies with your program,
> not with the access privileges. (There is no space between -p  and the
> password; you can also use the --password=your_pass  syntax to specify
> the password. If you use the -p --passwordoption with no password
> value, MySQL prompts you for the password.)"
> 
> Well, that describes my situation and when I attempt at the command
> line of the host running MySQL to connect by "mysql -u mysql mydb"  I
> get the error message "error 1044 (42000) Access denied for user "@"
> localhost to database "mydb."
> 
> So I guess I do have an access problem the user "mysql" weather it is
> on the localhost or throught PHP.
> 
> Now to figure out how to give the right permission(s) to the user.  I
> know that the mysql user can connect to the "test" database from the
> command line or through a browser and PHP.  At least it looks like I
> am narrowing the problem down.
> 
> host | user  |  db
>  
> %|   | mynewdb
> %| mysql | mynewdb
> %|   | test
> %|   | test\_%
> 
> 
> This looks like it means that from any host the mysql user can access
> the mynewdb database.  But it must not mean that.  What am I missing? 
> And I don't understand "test\_%" but I know I can connect to and open
> the test database.
> 
> Charles

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

Re: innodb in 4.1.18

[Marten Lehmann]

Hello,


This is spelled out pretty clearly in the manual:

InnoDB always needs the shared tablespace because it puts its  
internal data dictionary and undo logs there. The .ibd files are  not 
sufficient for InnoDB to operate.


well, thats what I found before. But it doesn't explain why InnoDB does 
need a logfile even when all transactions are committed or rolled back 
and mysql is shut down.


It does explain, that I can use innodb_file_per_table which gives the 
impression that with that option a shared ibdata isn't required. Or 
maybe it is required during runtime, but not for a backup.


So with InnoDB it is not possible to shutdown the database server, 
backup the files and maybe use them on another server if the other 
server already has an ibdata and ib_logfile? Do I always have to do a 
mysqldump then? That takes much longer.


Regards
Marten

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

Re: sequences

[Martijn Tonies]

Hello Chad,


>Does mysql have sequences?

No, it does not.

>If not what is the functional equivalent?

auto-increment would be the closest.

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


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

sequences

[ChadDavis]

Newbie question.

Does mysql have sequences?  If not what is the functional equivalent?

Re: Constraint checking

[Martijn Tonies]

Hello Barbara,


> When does constraint checking occur?
>
> I can create a table that has a constraint on a column that doesn't exist.
I would expect an error on the table creation, but the table gets created
successfully.
>
> I'm running on windows 5.0.18 client/server.
>
> mysql> show create table product;
>
+-+-
--+
> | Table   | Create Table  |
>
+-+-
--+
> | product | CREATE TABLE "product" (  "category" int(11) NOT NULL,  "id"
int(11) NOT NULL,
>   "price" decimal(10,0) default NULL,  PRIMARY KEY  ("category","id"))
>
+-+-

>
> mysql> create table barbconstr (col1 int(11), constraint ts_con5 check
(product.
> junk > 0));
> Query OK, 0 rows affected (2.63 sec)
>
>
> I expected this to fail with a column "junk" doesn't exist, type of
message.

MySQL doesn't support check constraints.

It supports the syntax to create one, but fully ignores whatever you put
in there. This is a "let's make the scripts compatible with other systems"
feature.

> The question is, why is this valid?  I hate to mention it, but this is who
I have to compare my apps behavior to, SQL Server will return the error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column 'COL7' is
specified in a constraint or computed-column definition.
>

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


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

Re: RIGHT JOIN better than INNER JOIN?

[gerald_clark]

Robert DiFalco wrote:


For me the argument is a little pedantic. The contract of the descriptor
table is that it must reference a name; there is code and constraints to
enforce this. I am happy to have the query return nulls to indicate a
programming error that can be quickly addressed. _If_ (after buffer
tuning et al) a RIGHT JOIN still provides a substantial performance
improvement over a FULL JOIN in this case, my customers would want me to
provide that rather than have me tell them it is an "inappropriate join"
or that I am asking the database server developers to improve their
query optimizer.

I wasn't really looking to get into a philosophical debate on
correctness so let me restate my question a little better.

Is there a reason in MySQL/InnoDB why a RIGHT JOIN would substantially
out perform a FULL JOIN in those cases where the results would be
identical? It is a little difficult to test query performance
empirically since performance will change as different indices are
swapped in and out of memory buffers and such (although I have turned
query caching off), but it appears that for a table with 1-2 million
rows a query similar to what I posted here was faster with a RIGHT JOIN.

 


You have not given enough information to even make a guess.
Show the create tables for each table, and the output of
explain for each query to see what keys are being used.

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

Re: Error with mysqld_safe

[mysql]

On Tue, 21 Mar 2006, Áquila Chaves wrote:

> [ERROR] 
> /usr/local/mysql/bin/mysqld: Can't create/write to file 
> '/var/run/mysqld/mysqld.pid' (Errcode: 13) 060321 12:12:22 
> [ERROR] Can't start server: can't create PID file: 
> Permission denied 060321 12:12:22 mysqld ended

Check the directory access permissions.
Does mysql have permission to write the PID file to 
/var/run/mysqld/ ?

Regards 

Keith

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

RE: RIGHT JOIN better than INNER JOIN?

[Robert DiFalco]

For me the argument is a little pedantic. The contract of the descriptor
table is that it must reference a name; there is code and constraints to
enforce this. I am happy to have the query return nulls to indicate a
programming error that can be quickly addressed. _If_ (after buffer
tuning et al) a RIGHT JOIN still provides a substantial performance
improvement over a FULL JOIN in this case, my customers would want me to
provide that rather than have me tell them it is an "inappropriate join"
or that I am asking the database server developers to improve their
query optimizer.

I wasn't really looking to get into a philosophical debate on
correctness so let me restate my question a little better.

Is there a reason in MySQL/InnoDB why a RIGHT JOIN would substantially
out perform a FULL JOIN in those cases where the results would be
identical? It is a little difficult to test query performance
empirically since performance will change as different indices are
swapped in and out of memory buffers and such (although I have turned
query caching off), but it appears that for a table with 1-2 million
rows a query similar to what I posted here was faster with a RIGHT JOIN.

R.

-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, March 21, 2006 9:43 AM
To: mysql@lists.mysql.com
Subject: Re: RIGHT JOIN better than INNER JOIN?

Robert,

> Of course one should use the right JOIN for the job. But let me ask 
> you, which join would you use here?
> 
> You have a table called Descriptors, it has a field called nameID 
> which is a unique key that relates to a Names table made up of a 
> unique identity and a VARCHAR name. I think most people would write a 
> simple query like this:
> 
>SELECT desc., names.name 
>FROM desc JOIN names ON desc.nameId = names.Id
>ORDER BY names.name
> 
> However, it this really correct? Every descriptor has a record in 
> names, so it could be equivalently written as:
> 
>SELECT desc., names.name 
>FROM desc RIGHT JOIN names ON desc.nameId = names.Id
>ORDER BY names.name
> 
> My guess is that most people conventionally write the first query. 

Gee, I wonder why ... This happens to be the query that returns the rows
as it should.

What happens, if two years from now you didn't document WHY you wrote a
"right join" query instead of an inner join and someone figures out that
this could return nulls for a result column?


If you start using the wrong joins, you will make things harder on
yourself and others.


As I said: if performance isn't satisfactory (which sounds a bit strange
for this situation), then try to solve that. Either by using different
index/buffer/caching strategies or by complaining to the people who
created the database system in the first place.



Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

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




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

Re: innodb in 4.1.18

[Ware Adams]

On Mar 21, 2006, at 1:08 PM, Marten Lehmann wrote:

I had a lot of trouble today because the InnoDB integration in  
MySQL is lousy. I read the manual and worked with  
innodb_per_file_table. So when I shutdown mysql I should be able to  
delete ib_logfile0, ib_logfile1 and ibdata1, because all table-data  
should be stored in the .idb and .frm files. But this obviously is  
not the case! MySQL always reports errors that it can't find the  
table files, although they are in the directory of the database.
And additionally: If I'm removing the files from a databases and  
try to create a table that existed before (but now doesn't exist  
because the files are removed), I can't create it any more. What is  
the function of ib_logfile and ibdata? I expected them to store  
transaction data only, but the seem to store more. But  
documentation is very sparse on that.


This is spelled out pretty clearly in the manual:

InnoDB always needs the shared tablespace because it puts its  
internal data dictionary and undo logs there. The .ibd files are  
not sufficient for InnoDB to operate.


right from the section on using innodb_file_per_table:

http://dev.mysql.com/doc/refman/4.1/en/multiple-tablespaces.html

And as others have said, you always need the logfiles.

Good luck,
Ware

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

Constraint checking

[Barbara Deaton]

When does constraint checking occur?

I can create a table that has a constraint on a column that doesn't exist.  I 
would expect an error on the table creation, but the table gets created 
successfully.

I'm running on windows 5.0.18 client/server.

mysql> show create table product;
+-+---+
| Table   | Create Table  |
+-+---+
| product | CREATE TABLE "product" (  "category" int(11) NOT NULL,  "id" 
int(11) NOT NULL,
  "price" decimal(10,0) default NULL,  PRIMARY KEY  ("category","id"))
+-+-

mysql> create table barbconstr (col1 int(11), constraint ts_con5 check (product.
junk > 0));
Query OK, 0 rows affected (2.63 sec)


I expected this to fail with a column "junk" doesn't exist, type of message.

The question is, why is this valid?  I hate to mention it, but this is who I 
have to compare my apps behavior to, SQL Server will return the error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column 'COL7' is 
specified in a constraint or computed-column definition.

Thanks.
Barb.

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

Re: Cannot select the database

[mysql]

Take a look at these links Charles.

http://dev.mysql.com/doc/refman/4.1/en/privilege-system.html
http://dev.mysql.com/doc/refman/4.1/en/user-account-management.html

You don't need to fully understand the mysql privilege 
access system to set up users, but it would help in the long 
term.

Regards 

Keith

In theory, theory and practice are the same;
in practice they are not.

On Tue, 21 Mar 2006, Charles Gambrell wrote:

> To: mysql@lists.mysql.com
> From: Charles Gambrell <[EMAIL PROTECTED]>
> Subject: Cannot select the database
> 
> I know this must be a simple issue and maybe I am asking it in the
> wrong place, so if the latter is the case, please direct me to the
> correct place.
> 
> I am getting my feet wet with MySQL.  I have installed running on
> WhiteBox linux and have created a datebase with one table and put some
> date in it.  All seems to work fine form the the command line.
> 
> I am now trying to connect throw a browser on a different workstation
> using PHP.  I seem to be able to connect ok.  I can select the "test"
> database that ships with MySQL but  when I try to select the database
> I created the select fails.
> 
> I am guessing this is some kind of premissions issue, that I am not
> understanding yet.
> 
> I have looked some at the db table i the mysql database and I see this -
> 
> host | user  |  db
> 
> %|   | mynewdb
> %| mysql | mynewdb
> %|   | test
> %|   | test\_%
> 
> Where do I need to be looking to see the problem and better yet,
> understanding the problem.
> 
> Thanks for the help.
> 
> Charles

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

Re: innodb in 4.1.18

[Bill Adams]

Marten Lehmann wrote:

I had a lot of trouble today because the InnoDB integration in MySQL 
is lousy. I read the manual and worked with innodb_per_file_table. So 
when I shutdown mysql I should be able to delete ib_logfile0, 
ib_logfile1 and ibdata1, because all table-data should be stored in 
the .idb and .frm files. But this obviously is not the case! 


The ib_logfile* stores, among other things, transaction history and 
whatnot. If you delete the log files or they otherwise become corrupted, 
your InnoDB tables spaces become unusable and you have to rebuild the 
table space.


MySQL always reports errors that it can't find the table files, 
although they are in the directory of the database.
And additionally: If I'm removing the files from a databases and try 
to create a table that existed before (but now doesn't exist because 
the files are removed), I can't create it any more. What is the 
function of ib_logfile and ibdata? I expected them to store 
transaction data only, but the seem to store more. But documentation is 


The ibdata file(s) contain the table data and indexes. You need both the 
ibdata file(s) and the iblog file(s). The table definitions are still 
stored in the .frm files.


At this point it sounds like you will need to delete the .frm files for 
the tables and recreate the innodb files.


Good luck.

b.


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

Error with mysqld_safe

[Áquila Chaves]

*Someone could help me? I think that the problem is related with some
configuration PATH or some configuration of permissions. But, I just
think... Below, I put the delaited description of the problem:**
*
 - I downloaded the binary file "Standard 5.0.19" of "Linux (non RPM
package)"
 - Later, I executed the following commands (such as suggested in the
documentation - http://dev.mysql.com/doc/refman/5.0/en/installing-binary.html):

shell> groupadd mysql
shell> useradd -g mysql mysql
shell> cd /usr/local
shell> gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf -
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
[EMAIL PROTECTED] mysql]# scripts/mysql_install_db –user=mysql
[EMAIL PROTECTED] mysql]# scripts/mysql_install_db --user=mysql
Installing all prepared tables
Fill help tables

To start mysqld at boot time you have to copy support-files/mysql.server
to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h panthro.THUNDERA password 'new-password'
See the manual for more instructions.

NOTE:  If you are upgrading from a MySQL <= 3.22.10 you should run
the ./bin/mysql_fix_privilege_tables. Otherwise you will not be
able to use the new GRANT command!

You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &

You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory:
cd sql-bench ; perl run-all-tests

Please report any problems with the ./bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at https://order.mysql.com

[EMAIL PROTECTED] mysql]# chown -R root  .
[EMAIL PROTECTED] mysql]# chown -R mysql data
[EMAIL PROTECTED] mysql]# chgrp -R mysql .
[EMAIL PROTECTED] mysql]# bin/mysqld_safe --user=mysql &
Starting mysqld daemon with databases from /var/lib/mysql
STOPPING server from pid file /var/run/mysqld/mysqld.pid
060321 12:10:28  mysqld ended

[the pc was processing during some minutes, but I didn't got reply... So, I
press ]
[1]+  Donebin/mysqld_safe –user=mysql
[EMAIL PROTECTED] mysql]#

The message created in log file is:
060321 12:12:21  mysqld started
060321 12:12:22  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
060321 12:12:22  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 36808.
InnoDB: Doing recovery: scanned up to log sequence number 0 43655
060321 12:12:22  InnoDB: Starting an apply batch of log records to the
database...
InnoDB: Progress in percents: 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78
79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
060321 12:12:22  InnoDB: Started; log sequence number 0 43655
060321 12:12:22 [ERROR] /usr/local/mysql/bin/mysqld: Can't create/write to file
'/var/run/mysqld/mysqld.pid' (Errcode: 13)
060321 12:12:22 [ERROR] Can't start server: can't create PID file: Permission
denied
060321 12:12:22  mysqld ended

**

Re: Cannot select the database

[Nestor]

Charles,

2 thing I woul do:
1) Grant permissions to the user that is trying to access mysql on that db.
2) I was having problems accessing mysql using php, although I was
able to access
   mysql from the command line.  The fix was using the old_password() function
   on mysql.
--
   " To deal with this problem, you can change a password in a special
way. For example,
 normally you use SET PASSWORD as follows to change an account password:

  SET PASSWORD FOR 'some_user'@'some_host' = PASSWORD('mypass');

 To change the password but create a short hash, use the OLD_PASSWORD()
 function instead:

 SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('mypass');

 OLD_PASSWORD() is useful for situations in which you explicitly
want to generate
 a short hash. "
-

   READ THIS:http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html

Nestor :-)


On 3/21/06, Charles Gambrell <[EMAIL PROTECTED]> wrote:
> I know this must be a simple issue and maybe I am asking it in the
> wrong place, so if the latter is the case, please direct me to the
> correct place.
>
> I am getting my feet wet with MySQL.  I have installed running on
> WhiteBox linux and have created a datebase with one table and put some
> date in it.  All seems to work fine form the the command line.
>
> I am now trying to connect throw a browser on a different workstation
> using PHP.  I seem to be able to connect ok.  I can select the "test"
> database that ships with MySQL but  when I try to select the database
> I created the select fails.
>
> I am guessing this is some kind of premissions issue, that I am not
> understanding yet.
>
> I have looked some at the db table i the mysql database and I see this -
>
> host | user   |  db
> 
> %|   | mynewdb
> %| mysql | mynewdb
> %|   | test
> %|   | test\_%
>
> Where do I need to be looking to see the problem and better yet,
> understanding the problem.
>
> Thanks for the help.
>
> Charles
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>

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

innodb in 4.1.18

[Marten Lehmann]

Hi,

I had a lot of trouble today because the InnoDB integration in MySQL is 
lousy. I read the manual and worked with innodb_per_file_table. So when 
I shutdown mysql I should be able to delete ib_logfile0, ib_logfile1 and 
ibdata1, because all table-data should be stored in the .idb and .frm 
files. But this obviously is not the case! MySQL always reports errors 
that it can't find the table files, although they are in the directory 
of the database.
And additionally: If I'm removing the files from a databases and try to 
create a table that existed before (but now doesn't exist because the 
files are removed), I can't create it any more. What is the function of 
ib_logfile and ibdata? I expected them to store transaction data only, 
but the seem to store more. But documentation is very sparse on that.


Regards
Marten

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

Cannot select the database

[Charles Gambrell]

I know this must be a simple issue and maybe I am asking it in the
wrong place, so if the latter is the case, please direct me to the
correct place.

I am getting my feet wet with MySQL.  I have installed running on
WhiteBox linux and have created a datebase with one table and put some
date in it.  All seems to work fine form the the command line.

I am now trying to connect throw a browser on a different workstation
using PHP.  I seem to be able to connect ok.  I can select the "test"
database that ships with MySQL but  when I try to select the database
I created the select fails.

I am guessing this is some kind of premissions issue, that I am not
understanding yet.

I have looked some at the db table i the mysql database and I see this -

host | user   |  db

%|   | mynewdb
%| mysql | mynewdb
%|   | test
%|   | test\_%

Where do I need to be looking to see the problem and better yet,
understanding the problem.

Thanks for the help.

Charles

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

Re: Repairing packed MyISAM tables with no index file (.MYI)

[Kayra Otaner]

My question wasn't about how to compress tables. It was about how to
regenerate indexes from a myisampacked table.


On Tue, 2006-03-21 at 16:40 +0530, Dilipkumar wrote:
> Hi,
> 
> First Use this  to compress : 
> 
>  /usr/local/mysql/bin/myisampack   (table name) troubleticket.*
> 
> then use this
> 
>  /usr/local/mysql/bin/myisamchk -rq (table name) troubleticket.*
> 
> 
> This might help you.
> 
> 
> Kayra Otaner wrote:
> 
> >Hello, 
> >
> >I have been using myisampack to pack old MyISAM tables to archive huge
> >amounts of data. To save more space I decided to get rid of index (.MYI)
> >files based on the assumption that I can reconstruct those indexes
> >whenever I needed. I've rebuild indexes on plain MyISAM tables with no
> >problem. I always use : 
> >
> >repair table TABLENAME USE_FRM; 
> >
> >from MySQL console to rebuild index files from scratch. When I try the
> >same on packed MyISAM tables MySQL fails. First it gives me bunch of
> >same type of errors : 
> >
> >| test.z_976287758_978107517 | repair | info | Found block that points
> >outside data file at 382300672 | 
> >
> >Then when it is finishes complaining about blocks outside data file, it
> >actually deletes actual data file (.MYD) : 
> >
> >-rw-r- 1 0 Mar 20 21:58 z_976287758_978107517.MYD 
> >-rw-rw 1 1.0K Mar 20 21:59 z_976287758_978107517.MYI 
> >-rw-r- 1 8.7K Mar 20 20:15 z_976287758_978107517.frm 
> >
> >Typically I would expect USE_FRM to not to touch actual data, but just
> >rebuild index file. When I try the same with myisamchk console utility,
> >it does the same. I use 5.0.18 on RHEL4 and RHEL3. Tested it only on 5.x
> >so far, doesn't work it with 4.x since utils seems like different. 
> >
> >
> >Any idea on what is going on? Did I hit to a bug? 
> >
> >Thanks. 
> >
> >Kayra Otaner
> >
> >
> >  
> >
> 
> 


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

Re: RIGHT JOIN better than INNER JOIN?

[Martijn Tonies]

Robert,

> Of course one should use the right JOIN for the job. But let me ask you,
> which join would you use here?
> 
> You have a table called Descriptors, it has a field called nameID which
> is a unique key that relates to a Names table made up of a unique
> identity and a VARCHAR name. I think most people would write a simple
> query like this:
> 
>SELECT desc., names.name 
>FROM desc JOIN names ON desc.nameId = names.Id
>ORDER BY names.name
> 
> However, it this really correct? Every descriptor has a record in names,
> so it could be equivalently written as:
> 
>SELECT desc., names.name 
>FROM desc RIGHT JOIN names ON desc.nameId = names.Id
>ORDER BY names.name
> 
> My guess is that most people conventionally write the first query. 

Gee, I wonder why ... This happens to be the query that returns
the rows as it should.

What happens, if two years from now you didn't document
WHY you wrote a "right join" query instead of an inner join
and someone figures out that this could return nulls for a result
column?


If you start using the wrong joins, you will make things harder
on yourself and others.


As I said: if performance isn't satisfactory (which sounds a bit
strange for this situation), then try to solve that. Either by using
different index/buffer/caching strategies or by complaining to 
the people who created the database system in the first place.



Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

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

RE: RIGHT JOIN better than INNER JOIN?

[Robert DiFalco]

Martjin,

Of course one should use the right JOIN for the job. But let me ask you,
which join would you use here?

You have a table called Descriptors, it has a field called nameID which
is a unique key that relates to a Names table made up of a unique
identity and a VARCHAR name. I think most people would write a simple
query like this:

   SELECT desc., names.name 
   FROM desc JOIN names ON desc.nameId = names.Id
   ORDER BY names.name

However, it this really correct? Every descriptor has a record in names,
so it could be equivalently written as:

   SELECT desc., names.name 
   FROM desc RIGHT JOIN names ON desc.nameId = names.Id
   ORDER BY names.name

My guess is that most people conventionally write the first query. 

R.
 

-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, March 21, 2006 1:01 AM
To: mysql@lists.mysql.com
Subject: Re: RIGHT JOIN better than INNER JOIN?




> I apologize if this is a naive question but it appears through my 
> testing that a RIGHT JOIN may out perform an INNER JOIN in those cases

> where they would produce identical result sets. i.e. there are no keys

> in the left table that do not exist in the right table.
> 
> Is this true? If so, it this peculiar to MySQL or would this be true 
> with almost all database servers? i.e. Oracle, DB2, MSSQL, etc.

Use the join that is appropriate for your query, do not use a different
one.

If speed isn't OK, then bug the guys that do the implementation, but
don't start changing the query that _might_ return a different result
if, for example, someone else starts working at the application and
figures "hey, this is a right join, so it's optional" etc ...

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

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




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

RE: mysql query and version problem .... Help!

[Addison, Mark]

> -Original Message-
> From: Gregory Machin [mailto:[EMAIL PROTECTED] 
> Sent: 21 March 2006 11:28
> To: mysql@lists.mysql.com
> Subject: mysql query and version problem  Help!
> 
> Hi.
> 
> I have just found out that my hosting provider is using mysql 
> 4 and I'm
> using mysql 5 the one query I need wont work and is a key 
> feature in the
> application .. here is the query i'm using
> 
> SELECT dealer_id, auto_id, bid_amount FROM bids b1 WHERE 
> bid_amount=(SELECT
> MAX(b2.bid_amount)
> FROM bids b2 WHERE b1.auto_id = b2.auto_id) AND 
> auto_dealer_id = '3' AND
> Bid_Status = '1';
> 
> How do I get this to work on version 4 ?

You could create a tmp table with the max bids and then join on
that. Something like:

CREATE TEMPORARY TABLE max_bids
SELECT auto_id, MAX(bid_amount) AS max_bid_amount
FROM bids
GROUP BY auto_id;
SELECT dealer_id, b1.auto_id, bid_amount FROM bids b1, max_bids b2 
WHERE b1.auto_id = b2.auto_id
AND bid_amount=max_bid_amount
AND auto_dealer_id = '3' AND Bid_Status = '1';

mark
--


Please Note:

 

Any views or opinions are solely those of the author and do not necessarily 
represent 
those of Independent Television News Limited unless specifically stated. 
This email and any files attached are confidential and intended solely for the 
use of the individual
or entity to which they are addressed. 
If you have received this email in error, please notify [EMAIL PROTECTED] 

Please note that to ensure regulatory compliance and for the protection of our 
clients and business,
we may monitor and read messages sent to and from our systems.

Thank You.


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

history not working -- additional info

[Andrew]

>I installed 5.0 and 5.1-beta on three different system (all are Fedora Core 
>4), and each time when I use the command-line client, I can not use the 
>familiar up-arrow feature that normally lets you scroll through queries issued 
>during previous command-line sessions.  My history is completely blank.
>
>the $HOME/.mysql_history file does exist, but all it's got in it is:
>
>_HiStOrY_V2_
>
>in the first line.
>
>Please help! what am i doing wrong?

to my surprise, i just discovered that connecting to the server over remote IP, 
with a command-line client on the remote machine, the command-line history DOES 
get preserved.

But, that, of course, is due to the separate functionality of server and client 
(as I am now clued in by this), so that .mysql_history is a client-only thing, 
and, in the case of remoteness, it's the client machine that writes to its own 
local .mysql_history file.

So, why is my client failing to write to .mysql_history? 

This appears to be an "out-of-the-box", default setting, or am I wrong?

Does it work for anyone out there?

Please help!

andrew

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

RE: RIGHT JOIN better than INNER JOIN?

[Nicolas Verhaeghe]

Robert,

A RIGHT JOIN would potentially return more results than an INNER JOIN.

I do not see how it could be, in the absolute, be faster.

How large were the tables and how did you do your testing? What platform
did you use and were you InnoDB or MyISAM?

-Original Message-
From: Robert DiFalco [mailto:[EMAIL PROTECTED] 
Sent: Monday, March 20, 2006 5:12 PM
To: mysql@lists.mysql.com
Subject: RIGHT JOIN better than INNER JOIN?


I apologize if this is a naive question but it appears through my
testing that a RIGHT JOIN may out perform an INNER JOIN in those cases
where they would produce identical result sets. i.e. there are no keys
in the left table that do not exist in the right table. 

Is this true? If so, it this peculiar to MySQL or would this be true
with almost all database servers? i.e. Oracle, DB2, MSSQL, etc.

TIA,

R.


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


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

RE: Timestamp problem in mysql5.0.18

[Jason Teagle]

See, for the UK, on that date, 2am to 2:59 inclusive do not officially
exist - hence, 3am to 3:59 for GMT+1, etc. I couldn't quite figure why 3am
was being rejected until now. I'm impressed that MySQL knows that {:v)

--
Jason Teagle
[EMAIL PROTECTED]


> -Original Message-
> From: Ricardas.S [mailto:[EMAIL PROTECTED]
> Sent: 21 March 2006 14:31
> To: [EMAIL PROTECTED]
> Subject: Re: Timestamp problem in mysql5.0.18
>
>
> Yes, I think you are right, it should be the main reason of
> insert failure.
> Thank you for good idea.
>
> Ricka
>
> - Original Message -
> From: "Jason Teagle" <[EMAIL PROTECTED]>
> To: "Ricardas.S" <[EMAIL PROTECTED]>
> Sent: Tuesday, March 21, 2006 14:55
> Subject: RE: Timestamp problem in mysql5.0.18
>
>
> > > All other date or hour values I tried, works good, but this one
> > > is not accepted. UPDATE statement behaves the same.
> > > Server time zone is GMT+2.
> > > I tried 5.0.18nt and two linux versions, result is the same.
> > > I tried to change time zone, and noticed that mysql server does
> > > not accept 2006-03-26 date with hour values which are equals GMT
> > > offset + 1.
> > > When I tried with ALLOW_INVALID_DATES option enabled, then server
> > > simply changes hour upward to 04.
> > >
> > > Is it mysql bug?
> >
> > I believe that the clocks go forward in the UK (and possibly other
> > countries) on the 26th - 2am suddenly becomes 3am. I wonder if
> this is part
> > of the problem? Seems a bit of a coincidence, especially as you
> say it went
> > to 4am.
> >
> > --
> > Jason Teagle
> > [EMAIL PROTECTED]
> >
>


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

Re: How to shutdown mysql from Java

[Alec . Cawley]

"Rhino" <[EMAIL PROTECTED]> wrote on 21/03/2006 13:41:49:

> 
> - Original Message - 
> From: "Zsolt" <[EMAIL PROTECTED]>
> To: "MySql Mailing List" 
> Sent: Tuesday, March 21, 2006 2:58 AM
> Subject: How to shutdown mysql from Java
> 
> 
> > Hi,
> >
> > my application starts mysqld (via Runtime.getRuntime().exec) and I 
would
> > like to stop it also from Java (because of technical reasons I cannot 
use
> > mysqladmin).
> >
> > What is the best way stop shutdown mysqld?
> >
> > 1. FLUSH TABLES
> >
> > 2. Process.destroy()
> >
> > 3. kill PID auf Unix
> >
> > What do you think?
> >
> Most database management systems, like MySQL and DB2, are designed to 
run 24 
> X 7 with occasional breaks for maintenance like taking backups. Why do 
you 
> want start and start MySQL from an application? Isn't this going to 
preclude 
> most of the users from using it? After all, databases usually have large 

> numbers of users, not just one.

Obviously not in this case. While MySQL can do many things, it also works 
perfectly well as a single user repository. It is perfectly reasonable for 
a sing user to regard it as part of a single application and start and 
stop it from the application.

However, the cost of an inactive MySQL running all the time is tiny. And 
treating MySQL like this would cause problems if, for example, you wanted 
to run two such applications, because they would fight over it. Or if your 
application wanted to run on a PC which already had MySQL running for some 
other purpose. 

So I would suggest that it would be wisest to do as Rhino implies and to 
install MySQL and leave it running all the time.

Alec



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

Re: RIGHT JOIN better than INNER JOIN?

[Rhino]

- Original Message - 
From: "Jochem van Dieten" <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, March 21, 2006 3:53 AM
Subject: Re: RIGHT JOIN better than INNER JOIN?



On 3/21/06, Robert DiFalco wrote:

I apologize if this is a naive question but it appears through my
testing that a RIGHT JOIN may out perform an INNER JOIN in those cases
where they would produce identical result sets. i.e. there are no keys
in the left table that do not exist in the right table.

Is this true?


If your benchmark shows it it must be true :) But is it a significant
difference? Over how many test runs? And is it worth the risk that for
some join it is actually much slower.



If so, it this peculiar to MySQL or would this be true
with almost all database servers?


Hard to tell. If you want a generic answer the way to go wouls be to
start digging in the MySQL sourcecode to find out why it is faster.
Once you know that, you can extrapolate to other databases.

No, you can't. Other databases use different optimizers, i.e. different 
algorithms to choose the access path. Even if you inspected 100 or 1000 or 
1,000,000 Fords and found every one of them to have rear-wheel drive, it 
wouldn't guarantee that BMWs or Hondas had rear-wheel drive, they might have 
front-wheel drive or all-wheel drive. The specific things you learn about 
MySQL's optimization technique cannot be extrapolated to other databases 
unless they are using the same optimization techniques.


The only mechanism I can imagine is that you are constraining the
planner so for a join between N tables you go from N! plans to (N-1)!
plans which will save you a miniscule amount on the planning time for
a small join, and somwhat more on a big join. (Does the MySQL planner
do an exhaustive search?) The downside is that the plan might be
significantly worse so you can loose big time in the executor.
Especially considering the semantic difference between both syntaxes I
would never use this as a way to constrain the planner.

Jochem



--

Rhino



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.6/286 - Release Date: 20/03/2006


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

Re: How to shutdown mysql from Java

[Rhino]

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

To: "MySql Mailing List" 
Sent: Tuesday, March 21, 2006 2:58 AM
Subject: How to shutdown mysql from Java



Hi,

my application starts mysqld (via Runtime.getRuntime().exec) and I would
like to stop it also from Java (because of technical reasons I cannot use
mysqladmin).

What is the best way stop shutdown mysqld?

1. FLUSH TABLES

2. Process.destroy()

3. kill PID auf Unix

What do you think?

Most database management systems, like MySQL and DB2, are designed to run 24 
X 7 with occasional breaks for maintenance like taking backups. Why do you 
want start and start MySQL from an application? Isn't this going to preclude 
most of the users from using it? After all, databases usually have large 
numbers of users, not just one.


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.6/286 - Release Date: 20/03/2006


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

Re: beginning confusions

[Dominik Klein]

Thanks. This gets me going.
Re proper file permissions: there seem to be mysql files all over the
place. Do I have to find them all and change permissions on all of them?
The MySQL manual I downloaded lists about eight different directories as
including MySQL files, and I'll already seen at least one case where my
installation put something in a different directory. (The rpm I
installed is mysql-4.1.11-2 according to 'rpm -q mysql'.)


I personally would suggest installing a mysql binary distribution from 
mysql.com
This way you have all files in /usr/local/mysql/ which might be easier 
for you than to start with the redheat-distribution mysql, which 
(according to you) seems to split files in different directories.


If you want to run mysql server as user "eric", make sure to replace the 
user "mysql" mentioned in the INSTALL-BINARY-instruction-file with the 
user "eric".


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

RE: ~Mysql cluster info~

[Jimmy Guerrero]

Hello,

MySQL 5.0 Cluster is an in-memory database. Meaning that the entire database
(tables, indexes, etc.) must fit in RAM along with your other OS and
application processes.

In 5.1, we have introduced disk-based data support. Note, that although data
can now be stored on disk, indexes must still reside in memory.

Might be worth checking out, however 55 GB is def. on the large size for a
MySQL Cluster configuration.

Jimmy Guerrero, Senior Product Manager
MySQL Inc, www.mysql.com




> -Original Message-
> From: Mohammed Abdul Azeem [mailto:[EMAIL PROTECTED] 
> Sent: Monday, March 20, 2006 9:33 PM
> To: mysql@lists.mysql.com
> Subject: ~Mysql cluster info~
> 
> 
> Hi,
> 
> Iam new to clustering in mysql. I went through the reference 
> manual 5.0 and found that the RAM memory requirements for 
> implementing a cluster is almost twice the size of the database.
> 
> My problem is i have a database which is 55GB. So does it 
> mean that i need to have 110 GB RAM memory ? Can anyone let 
> me know whether it is possible for me to configure a cluster 
> for such a huge database. If yes, how am i suppose to proceed 
> ( regarding memory requirements ).
> 
> Thanks in advance,
> Abdul.
> 
> 
> This email has been Scanned for Viruses!
>   www.newbreak.com
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


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

Re: Please help: recovering db from crash

[Adrian Bruce]

not sure, but it may be worth trying the following

run the script:
mysql_install_db --user=root
In the installation dir

this should change ownership and make mysql recognise the data dir.

good luck
Ade

Foo Ji-Haw wrote:


Hi all,

My Windows-based database server crashed (no fault of MySQL. probably 
OS or hardware), and I managed to copy out the data files. I am using 
version 5.0 of the Essentials package.


I tried to install a similar setup on another server, then copy the 
data\ folder over. The MySQL service starts, but I am not able to 
login, even as root.


Is there anyone who can advise me on the recovery steps?

Appreciate your feedback!



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

Timestamp problem in mysql5.0.18

[Ricardas.S]

Hi All,

Today, I encountered problem when trying to insert '2006-03-26 03:00:00'  value
into timestamp column:

CREATE TABLE `test` (
`a` timestamp
) ENGINE = InnoDB DEFAULT CHARSET = latin1 COLLATE = latin1_german1_ci;
INSERT INTO test VALUES ('2006-03-26 03:00:00' );

Server response was:
ERROR 1292 (22007) at line 4: Incorrect datetime value: '2006-03-26 03:00:00' 
for column 'a' at row 1

All other date or hour values I tried, works good, but this one
is not accepted. UPDATE statement behaves the same.
Server time zone is GMT+2.
I tried 5.0.18nt and two linux versions, result is the same.
I tried to change time zone, and noticed that mysql server does not accept 
2006-03-26 date with hour values which are equals GMT
offset + 1.
When I tried with ALLOW_INVALID_DATES option enabled, then server simply 
changes hour upward to 04.

Is it mysql bug?

Ricka


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

Re: beginning confusions

[Eric Beversluis]

On Tue, 2006-03-21 at 09:58 +0100, Dominik Klein wrote:
> > [EMAIL PROTECTED] ~]$ mysqlshow
> > +---+
> > | Databases |
> > +---+
> > | test  |
> > +---+
> 
> In standard setup, there is a user without a name and password, who can 
> connect from localhost to database "test".
> So, accessing with this user gives you a list of the databases this user 
> can see.
> 
> > [EMAIL PROTECTED] ~]$ mysqlshow mysql
> > mysqlshow: Access denied for user ''@'localhost' to database 'mysql'
> 
> As "" is not allowed to use database "mysql", you get the "access 
> denied" message.
> 
> > [EMAIL PROTECTED] ~]$ su -
> > Password:
> > [EMAIL PROTECTED] ~]# mysqlshow mysql
> > mysqlshow: Access denied for user 'root'@'localhost' (using password:
> > NO)
> 
> I suppose the root-Account for your MySQL has got a password. As you 
> have not given a password, you are not allowed to connect.
> Try "mysqlshow -p mysql"
> 
> your second mail:
> 
>  > More Confusions: When I try to follow the manual and enter
>  > 'bin/mysqld_safe --user=mysql &' I get this:
>  >
>  > [EMAIL PROTECTED] ~]$ cd /usr
>  > [EMAIL PROTECTED] usr]$ bin/mysqld_safe --user=mysql &
>  > [1] 10340
>  > [EMAIL PROTECTED] usr]$ cat: /var/run/mysqld/mysqld.pid: Permission denied
>  > rm: cannot remove `/var/run/mysqld/mysqld.pid': Permission denied
>  > Fatal error: Can't remove the pid file: /var/run/mysqld/mysqld.pid
> 
> When mysql is starting, it removes any old pid-Files. If another user 
> started MySQL before, this pid file belongs to that user and "eric" 
> cannot remove it.
> 
>  > bin/mysqld_safe: line 284: /var/log/mysqld.log: Permission denied
> 
> Again here: check permissions on /var/log/mysqld.log and wether "eric" 
> is able to write to that file.
> 
>  > mysqld start'. Isn't there a way to start mysql other than as root?
> 
> You can start mysql as any user. Just make sure you have proper file 
> permissions.
> 
> Regards
> Dominik


Thanks. This gets me going.
Re proper file permissions: there seem to be mysql files all over the
place. Do I have to find them all and change permissions on all of them?
The MySQL manual I downloaded lists about eight different directories as
including MySQL files, and I'll already seen at least one case where my
installation put something in a different directory. (The rpm I
installed is mysql-4.1.11-2 according to 'rpm -q mysql'.)

EB
> 

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

Re: Please help: recovering db from crash

[Mark Leith]

This is actually for Linux/Unix, not Windows.

What error do you get from MySQL when trying to log in? Does the 
mysqld(-nt) process show within Task Manager? What does the new error 
log say?


You may need to reset permissions:

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

Or you may need to set the appropriate datadir / basdir etc. depending 
on how you set up MySQL:


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

Best regards

Mark

Adrian Bruce wrote:


not sure, but it may be worth trying the following

run the script:
mysql_install_db --user=root
In the installation dir

this should change ownership and make mysql recognise the data dir.

good luck
Ade

Foo Ji-Haw wrote:


Hi all,

My Windows-based database server crashed (no fault of MySQL. probably 
OS or hardware), and I managed to copy out the data files. I am using 
version 5.0 of the Essentials package.


I tried to install a similar setup on another server, then copy the 
data\ folder over. The MySQL service starts, but I am not able to 
login, even as root.


Is there anyone who can advise me on the recovery steps?

Appreciate your feedback!






--
Mark Leith, Support Engineer
MySQL AB, Worcester, England, www.mysql.com
Are you MySQL certified?  www.mysql.com/certification


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

Please help: recovering db from crash

[Foo Ji-Haw]

Hi all,

My Windows-based database server crashed (no fault of MySQL. probably OS 
or hardware), and I managed to copy out the data files. I am using 
version 5.0 of the Essentials package.


I tried to install a similar setup on another server, then copy the 
data\ folder over. The MySQL service starts, but I am not able to login, 
even as root.


Is there anyone who can advise me on the recovery steps?

Appreciate your feedback!

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

mysql query and version problem .... Help!

[Gregory Machin]

Hi.

I have just found out that my hosting provider is using mysql 4 and I'm
using mysql 5 the one query I need wont work and is a key feature in the
application .. here is the query i'm using

SELECT dealer_id, auto_id, bid_amount FROM bids b1 WHERE bid_amount=(SELECT
MAX(b2.bid_amount)
FROM bids b2 WHERE b1.auto_id = b2.auto_id) AND auto_dealer_id = '3' AND
Bid_Status = '1';

How do I get this to work on version 4 ?
Many thanks
--
Gregory Machin
[EMAIL PROTECTED]
[EMAIL PROTECTED]
www.linuxpro.co.za
www.exponent.co.za
Web Hosting Solutions
Scalable Linux Solutions
www.iberry.info (support and admin)

+27 72 524 8096

Re: RIGHT JOIN better than INNER JOIN?

[Martijn Tonies]

> I apologize if this is a naive question but it appears through my
> testing that a RIGHT JOIN may out perform an INNER JOIN in those cases
> where they would produce identical result sets. i.e. there are no keys
> in the left table that do not exist in the right table. 
> 
> Is this true? If so, it this peculiar to MySQL or would this be true
> with almost all database servers? i.e. Oracle, DB2, MSSQL, etc.

Use the join that is appropriate for your query, do not use a
different one.

If speed isn't OK, then bug the guys that do the implementation,
but don't start changing the query that _might_ return a different
result if, for example, someone else starts working at the application
and figures "hey, this is a right join, so it's optional" etc ...

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

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

Re: beginning confusions

[Dominik Klein]

[EMAIL PROTECTED] ~]$ mysqlshow
+---+
| Databases |
+---+
| test  |
+---+


In standard setup, there is a user without a name and password, who can 
connect from localhost to database "test".
So, accessing with this user gives you a list of the databases this user 
can see.



[EMAIL PROTECTED] ~]$ mysqlshow mysql
mysqlshow: Access denied for user ''@'localhost' to database 'mysql'


As "" is not allowed to use database "mysql", you get the "access 
denied" message.



[EMAIL PROTECTED] ~]$ su -
Password:
[EMAIL PROTECTED] ~]# mysqlshow mysql
mysqlshow: Access denied for user 'root'@'localhost' (using password:
NO)


I suppose the root-Account for your MySQL has got a password. As you 
have not given a password, you are not allowed to connect.

Try "mysqlshow -p mysql"

your second mail:

> More Confusions: When I try to follow the manual and enter
> 'bin/mysqld_safe --user=mysql &' I get this:
>
> [EMAIL PROTECTED] ~]$ cd /usr
> [EMAIL PROTECTED] usr]$ bin/mysqld_safe --user=mysql &
> [1] 10340
> [EMAIL PROTECTED] usr]$ cat: /var/run/mysqld/mysqld.pid: Permission denied
> rm: cannot remove `/var/run/mysqld/mysqld.pid': Permission denied
> Fatal error: Can't remove the pid file: /var/run/mysqld/mysqld.pid

When mysql is starting, it removes any old pid-Files. If another user 
started MySQL before, this pid file belongs to that user and "eric" 
cannot remove it.


> bin/mysqld_safe: line 284: /var/log/mysqld.log: Permission denied

Again here: check permissions on /var/log/mysqld.log and wether "eric" 
is able to write to that file.


> mysqld start'. Isn't there a way to start mysql other than as root?

You can start mysql as any user. Just make sure you have proper file 
permissions.


Regards
Dominik

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

Re: RIGHT JOIN better than INNER JOIN?

[Jochem van Dieten]

On 3/21/06, Robert DiFalco wrote:
> I apologize if this is a naive question but it appears through my
> testing that a RIGHT JOIN may out perform an INNER JOIN in those cases
> where they would produce identical result sets. i.e. there are no keys
> in the left table that do not exist in the right table.
>
> Is this true?

If your benchmark shows it it must be true :) But is it a significant
difference? Over how many test runs? And is it worth the risk that for
some join it is actually much slower.


> If so, it this peculiar to MySQL or would this be true
> with almost all database servers?

Hard to tell. If you want a generic answer the way to go wouls be to
start digging in the MySQL sourcecode to find out why it is faster.
Once you know that, you can extrapolate to other databases.


The only mechanism I can imagine is that you are constraining the
planner so for a join between N tables you go from N! plans to (N-1)!
plans which will save you a miniscule amount on the planning time for
a small join, and somwhat more on a big join. (Does the MySQL planner
do an exhaustive search?) The downside is that the plan might be
significantly worse so you can loose big time in the executor.
Especially considering the semantic difference between both syntaxes I
would never use this as a way to constrain the planner.

Jochem