Re: problem with selecting my max bid ..
Ok I tried the following SELECT dealer_id, auto_id, bid_amount FROM bids WHERE bid_amount=(SELECT MAX(bid_amount) FROM bids WHERE auto_dealer_id='3'); which gives +---+-++ | dealer_id | auto_id | bid_amount | +---+-++ | 3 | 12 | 9 | +---+-++ 1 row in set (0.00 sec) wich is the max bid overall, what I want is the max bid for each auto_id ... How would I go about this ? On 3/17/06, Michael Stassen [EMAIL PROTECTED] wrote: Gregory Machin wrote: Hi. I have the following table | bid_id | dealer_id | auto_dealer_id | auto_id | bid_amount | timestamp | Bid_Status | +-+-+--++-++-+ | 1 |3 | 3 |12 | 2 | NULL | 1 | | 2 |3 | 3 |12 | 3 | NULL | 1 | | 3 | 24 | 3 |12 | 4 | NULL | 1 | | 4 | 24 | 3 |12 | 5 | NULL | 1 | | 5 | 24 | 3 |12 | 6 | NULL | 1 | | 6 |3 | 24 |14 | 4 | NULL | 1 | | 7 |3 | 3 |13 | 4 | NULL | 1 | | 8 | 24 | 3 | 12 |7 | NULL | 1 | | 9 | 24 | 3 |13 | 59000 | NULL | 1 | | 10 | 24 | 3 |12 | 8 | NULL | 1 | | 11 | 24 | 3 |13 | 6 | NULL | 1 | where auto_dealer_id is the dealer who put the car on auction , auto_id is the id of the car on auction, bid_amount is the amount did on the car but dealer_id what I want is to get the max bid placed for each car and the dealer_id who placed it. I currnetly have the following: mysql SELECT dealer_id, auto_id, bid_id ,MAX(bid_amount) AS 'bid_amount' FROM bids WHERE auto_dealer_id = '3' AND Bid_Status = '1' GROUP BY auto_id; +---+-+++ | dealer_id | auto_id | bid_id | bid_amount | +---+-+++ | 3 | 12 | 1 | 8 | | 3 | 13 | 7 | 6 | +---+-+++ But this output is wrong because if you refer to the source table above the dealer_id should be 24 in both cases.. What have I missed ? Many Thanks Gregory Machin GROUP BY does not return rows. It returns group names and aggregate stats. You are grouping on auto_id, so you get random (actually, the first found) values for dealer_id and bid_id for each group. Many systems wouldn't even allow this query. Mysql does allow you to select columns not present in the GROUP BY clause as a convenience, but you are warned only to use columns with unique values per group http://dev.mysql.com/doc/refman/4.1/en/group-by-hidden-fields.html. Yours is a FAQ, however, with 3 solutions in the manual http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html . Michael -- 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
Index and multiple fields
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, is there, performance wise, any difference whether I create one index for multiple fields or each field with its own index? I'm running 4.0.16. thx, - - Markus -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFEGnNH1nS0RcInK9ARAq9FAJ427uJXMuujd6Etnq7fhTSOqmISKgCg2Tn4 Qpytyz4PD4CPGSMEPX4ABbI= =cyqe -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql.sock gone
No errrs on the error log.. This is very weird... |-Original Message- |From: ? ??? [mailto:[EMAIL PROTECTED] |Sent: Wednesday, March 15, 2006 6:12 AM |To: Anton Krall; mysql@lists.mysql.com |Subject: Re: mysql.sock gone | |Look at mysql's error log ($MYSQL_DATA_DIR/$HOSTNAME.err). Do |you see anything strange there? | | |Anton Krall wrote: | No crons that would delete the tmp directory.. In fact, all |the other files | stay there... Just mysql.sock goes away... | | |-Original Message- | |From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] | |Sent: Tuesday, March 14, 2006 3:15 PM | |To: Anton Krall; mysql@lists.mysql.com | |Subject: RE: mysql.sock gone | | | |Do you have any cron jobs that clear the /tmp directory |during the day? | | | |Regards | | | |--- | |** _/ ** David Logan | |*** _/ *** ITO Delivery Specialist - Database | |*_/* Hewlett-Packard Australia Ltd | |_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] | | _/ _/ _/ _/ Desk: +618 8408 4273 | | _/ _/ _/_/_/ Mobile: 0417 268 665 | |*_/ ** | |** _/ Postal: 148 Frome Street, | | _/ ** Adelaide SA 5001 | | Australia | |invent | |--- | | | |-Original Message- | |From: Anton Krall [mailto:[EMAIL PROTECTED] | |Sent: Wednesday, 15 March 2006 7:38 AM | |To: mysql@lists.mysql.com | |Subject: mysql.sock gone | | | |Guys. | | | |I just started having a problem, Im running | |mysql-standard-4.1.12-pc-linux-gnu-i686 binaries under Fedora | |Core 3 and Ive been having problem where during some parts of | |the day, /tmp/mysql.oskc goes away, I can still see mysql | |running when I do a ps ax but when I try to connect to it, it | |says it cant connect thru mysql.sock | | | |What could be the cause that would make mysql.sock dissapear? | | | |Thx for any help you can provide | | | | | |-- | |MySQL General Mailing List | |For list archives: http://lists.mysql.com/mysql | |To unsubscribe: | |http://lists.mysql.com/[EMAIL PROTECTED] | | | | | |-- | |MySQL General Mailing List | |For list archives: http://lists.mysql.com/mysql | |To unsubscribe: | |http://lists.mysql.com/[EMAIL PROTECTED] | | | | | | | | | | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index and multiple fields
Hi, Mark! Of course, it depends on queries you are running. I beleive you can find all anwers here: http://dev.mysql.com/doc/refman/5.0/en/indexes.html http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html Markus Fischer пишет: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, is there, performance wise, any difference whether I create one index for multiple fields or each field with its own index? I'm running 4.0.16. thx, - - Markus -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFEGnNH1nS0RcInK9ARAq9FAJ427uJXMuujd6Etnq7fhTSOqmISKgCg2Tn4 Qpytyz4PD4CPGSMEPX4ABbI= =cyqe -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index and multiple fields
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, thanks, somehow I wasn't able to find those pages. Basically, this means if I've a table like this id1 id2 id3 id4 id5 and I've two different select statements: select * from ... where id1 = .. and id2 = .. and the other being select * from ... where id3 = .. and id4 = .. I would create two indexes, one for id1/id2 and the other for id3/id4 , right? again, thanks - - Markus Косов Евгений wrote: Hi, Mark! Of course, it depends on queries you are running. I beleive you can find all anwers here: http://dev.mysql.com/doc/refman/5.0/en/indexes.html http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html Markus Fischer пишет: Hi, is there, performance wise, any difference whether I create one index for multiple fields or each field with its own index? I'm running 4.0.16. thx, - Markus -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFEGo1p1nS0RcInK9ARAjOsAJsGmgh1VVI3RCG1ci7sr2vBKR7VgQCgpvg8 k3wTpe1bqh7BIHaDGze+ttY= =ZqNR -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL load and unload immediately
Hi! Server system SBS (Novell Small Business suite) 6.5 sp 1 with MySQL ver. 4.0.15a, PHP 4.2.3, all of them on same machine. After restart server command the MySQL server is loading and unloading immediately. The error file : MYSQL:/data/WEB.err contain the following text: sys:/mysql/bin/mysqld.nlm: Character set 'UTF8' is not a compiled character set and is not specified in the 'sys:/mysql/share/charsets/Index' file The log file : MYSQL:/data/WEB.safe contain the following text: MySQL Server 4.0.16a, for pc-netware (i686) address : 0.0.0.0 port : 3306 daemon : sys:/mysql/bin/mysqld base directory : sys:/mysql data directory : MYSQL:/data pid file : MYSQL:/data/WEB.pid error file : MYSQL:/data/WEB.err log file : MYSQL:/data/WEB.safe mysql started: 17 Mar 2006 12:54:49 mysql stopped: 17 Mar 2006 12:54:49 Please help Tia Nanu
getting table metadata
Hi, is it possible to get information about tables by doing queries on some system tables? I am using mysql version 4.1.11 on debian sarge. In my case I need to know which columns (names and types) a table has, and how the primary key is defined. How can I get this information out of mysql by only using sql ? Best regards, Yves -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: getting table metadata
Hello Yves, is it possible to get information about tables by doing queries on some system tables? I am using mysql version 4.1.11 on debian sarge. In my case I need to know which columns (names and types) a table has, and how the primary key is defined. How can I get this information out of mysql by only using sql ? Have a look at the SHOW commands in the documentation. As for system tables, MySQL 4.1 hardly has any. 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: getting table metadata
On Friday 17 March 2006 15:52, Martijn Tonies wrote: Hello Yves, Hello Martijn, is it possible to get information about tables by doing queries on some system tables? I am using mysql version 4.1.11 on debian sarge. In my case I need to know which columns (names and types) a table has, and how the primary key is defined. How can I get this information out of mysql by only using sql ? Have a look at the SHOW commands in the documentation. I know about the show create table ... but it doesn't really satisfy my needs... Is there really no other way apart of parsing create table statements? (I needed to this with firebird, and found all I could dream of in the RDB$ tables) Best regards, Yves As for system tables, MySQL 4.1 hardly has any. 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: getting table metadata
If you upgrade to mysql 5.0.x there are also the The INFORMATION_SCHEMA tables that return meta information about tables. AFAIK they may not be available in pre 5.0 versions. Keith In theory, theory and practice are the same; in practice they are not. To unsubscribe from this list, please see detailed instructions already posted at: http://marc.theaimsgroup.com/?l=php-installm=114138567814319w=2 On Fri, 17 Mar 2006, Yves Glodt wrote: To: mysql@lists.mysql.com From: Yves Glodt [EMAIL PROTECTED] Subject: getting table metadata Hi, is it possible to get information about tables by doing queries on some system tables? I am using mysql version 4.1.11 on debian sarge. In my case I need to know which columns (names and types) a table has, and how the primary key is defined. How can I get this information out of mysql by only using sql ? Best regards, Yves -- 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: getting table metadata
Try DESC table_name. Yves Glodt пишет: On Friday 17 March 2006 15:52, Martijn Tonies wrote: Hello Yves, Hello Martijn, is it possible to get information about tables by doing queries on some system tables? I am using mysql version 4.1.11 on debian sarge. In my case I need to know which columns (names and types) a table has, and how the primary key is defined. How can I get this information out of mysql by only using sql ? Have a look at the SHOW commands in the documentation. I know about the show create table ... but it doesn't really satisfy my needs... Is there really no other way apart of parsing create table statements? (I needed to this with firebird, and found all I could dream of in the RDB$ tables) Best regards, Yves As for system tables, MySQL 4.1 hardly has any. 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: getting table metadata
Hello Yves, is it possible to get information about tables by doing queries on some system tables? I am using mysql version 4.1.11 on debian sarge. In my case I need to know which columns (names and types) a table has, and how the primary key is defined. How can I get this information out of mysql by only using sql ? Have a look at the SHOW commands in the documentation. I know about the show create table ... but it doesn't really satisfy my needs... Is there really no other way apart of parsing create table statements? (I needed to this with firebird, and found all I could dream of in the RDB$ tables) As I said -- take a look at the SHOW commands in the documentation :-) There's show columns for example. 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: getting table metadata
What you are looking for is the INFORMATION_SCHEMA views, but they are not
available before 5.0. Until then you have to parse the show create table
or DESCRIBE tablename may be easier to parse
mysql describe organizations;
+---+---
+--+-+-+---+
| Field | Type
| Null | Key | Default | Extra |
+---+---
+--+-+-+---+
| orgn_ID | char(4)
| NO | PRI | | |
| orgn_Name | char(50)
| YES | | NULL| |
| orgn_Billing_Type | enum('Bank Transfer','Credit
Card','Invoice','none','Purchase Order') | YES | | NULL
| |
| orgn_Internal_ID | char(15)
| YES | | NULL| |
| orgn_Active | enum('Yes','No')
| NO | | Yes | |
| orgn_Who | char(4)
| NO | | | |
| orgn_Timestamp| timestamp
| YES | | CURRENT_TIMESTAMP | |
| orgn_Create | datetime
| NO | | 2000-01-01 00:00:00 | |
+---+---
+--+-+-+---+
8 rows in set (0.27 sec)
-Original Message-
From: Yves Glodt [mailto:[EMAIL PROTECTED]
Sent: Friday, March 17, 2006 9:22 AM
To: mysql@lists.mysql.com
Subject: Re: getting table metadata
On Friday 17 March 2006 15:52, Martijn Tonies wrote:
Hello Yves,
Hello Martijn,
is it possible to get information about tables by doing queries on some
system
tables? I am using mysql version 4.1.11 on debian sarge.
In my case I need to know which columns (names and types) a table has,
and
how
the primary key is defined.
How can I get this information out of mysql by only using sql ?
Have a look at the SHOW commands in the documentation.
I know about the show create table ... but it doesn't really satisfy my
needs... Is there really no other way apart of parsing create table
statements?
(I needed to this with firebird, and found all I could dream of in the RDB$
tables)
Best regards,
Yves
As for system tables, MySQL 4.1 hardly has any.
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: getting table metadata
On Friday 17 March 2006 16:50, Martijn Tonies wrote: Hello Yves, snip As I said -- take a look at the SHOW commands in the documentation :-) show columns from TABLE seems to be what I need, thanks to you and the other posters, have a nice weekend! (and sorry for the noise) best regards, Yves There's show columns for example. 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: problem with selecting my max bid ..
Gregory Machin wrote: Ok I tried the following SELECT dealer_id, auto_id, bid_amount FROM bids WHERE bid_amount=(SELECT MAX(bid_amount) FROM bids WHERE auto_dealer_id='3'); which gives +---+-++ | dealer_id | auto_id | bid_amount | +---+-++ | 3 | 12 | 9 | +---+-++ 1 row in set (0.00 sec) wich is the max bid overall, what I want is the max bid for each auto_id ... No, it's the max bid received by auto_dealer number 3. It's a coincidence if that's also the max bid overall. How would I go about this ? By following the example in the link I sent. 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); You see? Rows are selected if they have the max bid of all rows with the same auto_id. You keep saying you want the max bid per auto_id, but your examples always include restrictions on auto_dealer_id. That's fine, but it's a separate issue. You can just add any additional restrictions to the main query's WHERE clause: 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'; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GUI Tools for administering and reporting
All, Is there a recommended GUI that will administer multiple MySQL 4.x databases. I need the ability to monitor connections, health, users, etc. and notify me when there is a problem with an instance. Regards, Alan L. Fisher GPI -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GUI Tools for administering and reporting
On 3/17/06, Alan Fisher [EMAIL PROTECTED] wrote: All, Is there a recommended GUI that will administer multiple MySQL 4.x databases. I need the ability to monitor connections, health, users, etc. and notify me when there is a problem with an instance. Regards, Alan L. Fisher GPI -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] MySQL Administrator can do that... But the notify stuff can be a little hard to obtain in a free tool, in windows you could schedule a task to see how things are running or simply parse the logs for information with another program. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upper Limit to max_join_size? (4.1.18)
I am having a problem where I cannot seem to increase the max_join_size of 4.1.18 above a hard limit that is way too low for my use. Has anyone run into this and know of a solution that does not involve upgrading to 5.x.x? (That is presently not an option for another month or so.) I also want to avoid using SET SQL_BIG_SELECTS=1. I know in earlier versions (perhaps 4.0.x?) I could increase the value to what I wanted. I will happily add the output from show variables if that will help. Thanks in advance for your help. --Bill Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 26460 to server version: 4.1.18-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select @@max_join_size; +-+ | @@max_join_size | +-+ | 705032704 | +-+ 1 row in set (0.00 sec) mysql SET sql_max_join_size=50; Query OK, 0 rows affected (0.00 sec) mysql select @@max_join_size; +-+ | @@max_join_size | +-+ | 705032704 | +-+ 1 row in set (0.00 sec) mysql SET sql_max_join_size=5000; Query OK, 0 rows affected (0.00 sec) mysql select @@max_join_size; +-+ | @@max_join_size | +-+ |5000 | +-+ 1 row in set (0.00 sec) mysql SET sql_max_join_size=DEFAULT; Query OK, 0 rows affected (0.00 sec) mysql select @@max_join_size; +-+ | @@max_join_size | +-+ | 705032704 | +-+ 1 row in set (0.00 sec) mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
still cannot start MySQL
I'm still having a problem starting MySQL. I get the following message: Unable to initialise database connection: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' I've check the /etc/mysql/my.cnf and the sock file is supposed to load in '/var/run/mysqld. Thanks !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META http-equiv=Content-Type content=text/html; charset=us-ascii META content=MSHTML 6.00.2900.2802 name=GENERATOR/HEAD BODY style=MARGIN-TOP: 2px; FONT: 10pt Arial; MARGIN-LEFT: 2px DIVI'm still having a problem starting MySQL./DIV DIVI get the following message:/DIV DIVUnable to initialise database connection: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' /DIV DIVI've check the /etc/mysql/my.cnf and the sock file is supposed to load in '/var/run/mysqld./DIV DIVnbsp;/DIV DIVThanks/DIV/BODY/HTML -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: still cannot start MySQL
Jon Miller wrote: I'm still having a problem starting MySQL. I get the following message: Unable to initialise database connection: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' I've check the /etc/mysql/my.cnf and the sock file is supposed to load in '/var/run/mysqld. Thanks Start the server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: GUI Tools for administering and reporting
http://nagios.org We use that to monitor dozens of servers, over 1000 individual items (disk space, web server up/down, etc). We also do some heavy monitoring of oracle databases and some moderate mysql monitoring. It can be easily adapted to do what you want. And it's free! Dan. -Original Message- From: Daniel da Veiga [mailto:[EMAIL PROTECTED] Sent: Friday, March 17, 2006 12:56 PM To: mysql@lists.mysql.com Subject: Re: GUI Tools for administering and reporting On 3/17/06, Alan Fisher [EMAIL PROTECTED] wrote: All, Is there a recommended GUI that will administer multiple MySQL 4.x databases. I need the ability to monitor connections, health, users, etc. and notify me when there is a problem with an instance. Regards, Alan L. Fisher GPI -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] MySQL Administrator can do that... But the notify stuff can be a little hard to obtain in a free tool, in windows you could schedule a task to see how things are running or simply parse the logs for information with another program. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses by TechTeam's email gateway. -- This e-mail transmission is strictly confidential and intended solely for the person or organization to whom it is addressed. It may contain privileged and confidential information and if you are not the intended recipient, you must not copy, distribute or take any action in reliance on it. If you have received this e-mail in error, please notify the sender as soon as possible and delete the e-mail message and any attachment(s). This message has been scanned for viruses by TechTeam's email gateway. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Some queries use 100% CPU after restore
I'm moving a database to a new server. I'm using MySQL v5.0.16 on 'Windows. I used the MySQL Administrator to backup on the old system and restore on the new one. Everything is fine _except_ on the new server, some queries take 2-3 minutes with MySQL using 100% of the CPU. I've dropped unneeded views, done maintenance, verfied that all the indices I expect are there, etc. What's very, very strange is that it seems that the long queries are for old records and records created since the move are OK. I realize this is a vague request but I've been poking at this for a long time without getting any good clues or making any real headway. Any kind of brainstorming on things to check would be very welcome. Thanks. Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unknown command '\'' during load
I'm trying to restore a database from a dump as part of my make-sure-this-will-restore-just-in-case process and I get the following error... ERROR at line 1189: Unknown command '\''. The only thing I've been able to find is this bug report... http://bugs.mysql.com/bug.php?id=9756 ...which claims that this (or something similar) was fixed in 5.0.6. I'm running 5.0.18 on OS X here and the dump is from a Debian box running 4.0.15. (I've also tried loading the dump on a box running 4.0.x with the same result) The table in question has just over 25 million rows, so it would be nice to be able to restore it if necessary :) I'm just trying to figure out if it's a data problem, version problem, or something else. -- Jack Baty Fusionary Media - http://www.fusionary.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Some queries use 100% CPU after restore
Have you tried Repair table or if InnoDB ALTER TABLE ENGINE=InnoDB; Sometimes I've noticed after a restore or after adding lots of rows performance is slow. REPAIR or the ALTER TABLE fixes it. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, March 17, 2006 2:37 PM To: mysql@lists.mysql.com Subject: Some queries use 100% CPU after restore Importance: High I'm moving a database to a new server. I'm using MySQL v5.0.16 on 'Windows. I used the MySQL Administrator to backup on the old system and restore on the new one. Everything is fine _except_ on the new server, some queries take 2-3 minutes with MySQL using 100% of the CPU. I've dropped unneeded views, done maintenance, verfied that all the indices I expect are there, etc. What's very, very strange is that it seems that the long queries are for old records and records created since the move are OK. I realize this is a vague request but I've been poking at this for a long time without getting any good clues or making any real headway. Any kind of brainstorming on things to check would be very welcome. Thanks. Chris -- 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: RE: Some queries use 100% CPU after restore
Have you tried Repair table or if InnoDB ALTER TABLE ENGINE=InnoDB; Sometimes I've noticed after a restore or after adding lots of rows performance is slow. REPAIR or the ALTER TABLE fixes it. I'll try that. Thanks. What's curious is that I've got one backup I can restore and have fine performance and another, later backup that I can restore and get crappy performance. I can switch back and forth all day long with quite consistent results. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some queries use 100% CPU after restore
[EMAIL PROTECTED] wrote: I'm moving a database to a new server. I'm using MySQL v5.0.16 on 'Windows. I used the MySQL Administrator to backup on the old system and restore on the new one. Everything is fine _except_ on the new server, some queries take 2-3 minutes with MySQL using 100% of the CPU. I've dropped unneeded views, done maintenance, verfied that all the indices I expect are there, etc. What's very, very strange is that it seems that the long queries are for old records and records created since the move are OK. I realize this is a vague request but I've been poking at this for a long time without getting any good clues or making any real headway. Any kind of brainstorming on things to check would be very welcome. Thanks. Chris Analyze tables. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Easy regex replace?
Hi, I could write a quick script to fix the following, but just out of curiosity, if there's a quicker way to do it from the mysql command line, that'd be cool to know. I've got a few thousand rows of data that have URI escaped characters in them, and I've fixed the code that was doing it, but now I need to cleanup the data. Is there a quick and dirty way to update such that I can only affect the portion of a string (varchar column) that matches a regexp? (e.g. replace all '%20' with ' ' leaving the rest untouched?) Yani -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select from multiple tables
Hi guys, I am kinda new to mysql and on my endeavour to build a backend for a site i am building, i need to fetch data from a couple of tables, but dont know how to do it with a single select. Heres the problem: first table (products): id id_type name desc price second table (types): id_types name What i need is to list all the products and show the type name of each of them. On the products table, on the id_type field, i have only a number pointing to its type on the types column, what i want to know is how i can do a select to fetch all the product records and replace the number on id_type with its equivalente name from the types table. Sorry if i cant explain it correctly, but heres an example: table products: id id_type namedescprice 1 2 prod1 blah45 table types: id_type name 1 razor 2 string I want the list to show: prod1 string blah45 Thanks, Miguel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Errors 1005 and 1025 - but not foreign keys
David, - Original Message - From: David Felio [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, March 16, 2006 11:31 PM Subject: Errors 1005 and 1025 - but not foreign keys I got an error 1025 trying to rename an InnoDB table. When I go to look in the database now, that table isn't there even though there is a .ibd file in the mysql data directory with the target name. If I try to create a table with the target name (as an InnoDB table), I get error 1005. I can create it as MyISAM, however. If I try to then convert that MyISAM table to InnoDB, I get the 1025 error. I tried removing the .ibd file from the mysql data dir and that did not help. In googling the error, it seems all solutions revolve around foreign keys, but there are no foreign keys in this table nor are there any foreign keys referencing this table. what is the MySQL version? Please post the error messages verbatim. If mysqld prints something to the .err log, please also post the printout. After the failing operation, run SHOW INNODB STATUS\G and post the latest foreign key error explanation in it if any. The issue may be an orphaned table in ibdata1 which does not have an .frm file: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting-datadict.html Or you may have FOREIGN KEY constraints that you are not aware of. Deleting an .ibd file manually from the database directory never helps because the InnoDB internal data dictionary is in ibdata files. How can I get rid of this mess? Thanks. David Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
