Re: To get Processlist and Status of MySQL ?
I found some tools to capture the MySQL processlist, log errors, etc. MySQLReport http://hackmysql.com/mysqlreport and mytophttp://jeremy.zawodny.com/mysql/mytop/apps. My goal is to grab the logs of mysql including the processlist to help me assist in diagnosis of faults. What tool/apps do you use? Thanks. James On Tue, May 18, 2010 at 5:06 AM, Raj Shekhar rajl...@rajshekhar.net wrote: In infinite wisdom James Corteciano ja...@linux-source.org wrote: [1 text/plain; ISO-8859-1 (7bit)] Hi All, What is the other way to get the *processlist* and *status* of mysql server on event that the mysql server cannot be able to reach due to hung or crashed? GDB http://poormansprofiler.org/ -- Raj Shekhar - If there's anything more important than my ego around, I want it caught and shot now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ja...@linux-source.org
Re: To get Processlist and Status of MySQL ?
If your mysql server is hung, crashed or otherwise unreacheable (as you first posted), you can't connect to it and request it's status and processlist - it's threaded and thus all inline. You could use GDB or something similar to go look in the execution stack, as Raj said, and if you have full query logging enabled, you can parse your logs and see what was happening at the time of the crash - although I suspect that only completed queries get logged, so the one causing the crash may not be in there. Generally, it's a good idea to use Munin, Cacti or similar tools to graph performance data all the time, so you can have a post-mortem look at stuff like did the number of connections increase in the moments before ? On Tue, May 18, 2010 at 9:28 AM, James Corteciano ja...@linux-source.orgwrote: I found some tools to capture the MySQL processlist, log errors, etc. MySQLReport http://hackmysql.com/mysqlreport and mytophttp://jeremy.zawodny.com/mysql/mytop/apps. My goal is to grab the logs of mysql including the processlist to help me assist in diagnosis of faults. What tool/apps do you use? Thanks. James On Tue, May 18, 2010 at 5:06 AM, Raj Shekhar rajl...@rajshekhar.net wrote: In infinite wisdom James Corteciano ja...@linux-source.org wrote: [1 text/plain; ISO-8859-1 (7bit)] Hi All, What is the other way to get the *processlist* and *status* of mysql server on event that the mysql server cannot be able to reach due to hung or crashed? GDB http://poormansprofiler.org/ -- Raj Shekhar - If there's anything more important than my ego around, I want it caught and shot now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ja...@linux-source.org -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Foreign Key Problem
Hi; mysql create table if not exists Passengers (id int unsigned auto_increment primary key, foreign key (id) references Flights (flights_id), foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)); Query OK, 0 rows affected (0.00 sec) mysql select c.first_name, c.middle_name, c.last_name, c.suffix, c.discount, p.flights_id from Customers c join Passengers p on c.id=p.customer_id where flights_id=1; ERROR 1054 (42S22): Unknown column 'p.flights_id' in 'field list' mysql describe Passengers; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | id | int(10) unsigned | NO | PRI | NULL| auto_increment | | name | varchar(40) | YES | | NULL|| | weight | tinyint(3) | YES | | NULL|| ++--+--+-+-++ 3 rows in set (0.01 sec) So, why didn't the foreign key get created? It exists as a primary key in Customers. Please advise. TIA, Victor
Re: Foreign Key Problem
You're not specifying an engine, and the default is MyISAM, which doesn't support foreign keys and will likely silently ignore requests for them. Can you confirm that you've changed the default engine to InnoDB ? On Tue, May 18, 2010 at 3:44 PM, Victor Subervi victorsube...@gmail.comwrote: Hi; mysql create table if not exists Passengers (id int unsigned auto_increment primary key, foreign key (id) references Flights (flights_id), foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)); Query OK, 0 rows affected (0.00 sec) mysql select c.first_name, c.middle_name, c.last_name, c.suffix, c.discount, p.flights_id from Customers c join Passengers p on c.id=p.customer_id where flights_id=1; ERROR 1054 (42S22): Unknown column 'p.flights_id' in 'field list' mysql describe Passengers; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | id | int(10) unsigned | NO | PRI | NULL| auto_increment | | name | varchar(40) | YES | | NULL|| | weight | tinyint(3) | YES | | NULL|| ++--+--+-+-++ 3 rows in set (0.01 sec) So, why didn't the foreign key get created? It exists as a primary key in Customers. Please advise. TIA, Victor -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Foreign Key Problem
On Tue, May 18, 2010 at 10:06 AM, Johan De Meersman vegiv...@tuxera.bewrote: You're not specifying an engine, and the default is MyISAM, which doesn't support foreign keys and will likely silently ignore requests for them. Can you confirm that you've changed the default engine to InnoDB ? Got me. No, it wasn't and I'm new to this. Set up my.cnf like this: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 [mysqld_safe] skip-external-locking max_connections=200 read_buffer_size=1M sort_buffer_size=1M log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid # Make sure that you have enough free disk space. innodb_data_file_path = ibdata1:10M:autoextend # # Set buffer pool size to 50-80% of your computer's memory innodb_buffer_pool_size=256M innodb_additional_mem_pool_size=20M # # Set the log file size to about 25% of the buffer pool size innodb_log_file_size=64M innodb_log_buffer_size=8M # innodb_flush_log_at_trx_commit=1 and restarted mysqld. Then this: mysql create table if not exists Passengers (id int unsigned auto_increment primary key, foreign key (id) references Flights (flights_id), foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB; ERROR 1005 (HY000): Can't create table './seaflight/Passengers.frm' (errno: 150) So apparently it didn't like my foreign key. Do I need to do something with the table I'm referencing or what? TIA, V On Tue, May 18, 2010 at 3:44 PM, Victor Subervi victorsube...@gmail.comwrote: Hi; mysql create table if not exists Passengers (id int unsigned auto_increment primary key, foreign key (id) references Flights (flights_id), foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)); Query OK, 0 rows affected (0.00 sec) mysql select c.first_name, c.middle_name, c.last_name, c.suffix, c.discount, p.flights_id from Customers c join Passengers p on c.id=p.customer_id where flights_id=1; ERROR 1054 (42S22): Unknown column 'p.flights_id' in 'field list' mysql describe Passengers; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | id | int(10) unsigned | NO | PRI | NULL| auto_increment | | name | varchar(40) | YES | | NULL|| | weight | tinyint(3) | YES | | NULL|| ++--+--+-+-++ 3 rows in set (0.01 sec) So, why didn't the foreign key get created? It exists as a primary key in Customers. Please advise. TIA, Victor -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Foreign Key Problem
On Tue, May 18, 2010 at 6:00 PM, Victor Subervi victorsube...@gmail.comwrote: So apparently it didn't like my foreign key. Do I need to do something with the table I'm referencing or what? TIA. Well, quickfix is to convert your tables to innoDB, starting with the lowest-level (foreign-key only ones) first - I'm not sure what happens if you set a referential constraint from an innodb table to a myisam table. You can easily convert tables with alter table *yourtable* engine=innodb;. For the tables where you also want to add constraints and/or indices, alter table *yourtable* add constraint *yourconstrainthere* engine=innodb; does the trick in one go. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Foreign Key Problem
Johan De Meersman wrote: On Tue, May 18, 2010 at 6:00 PM, Victor Subervi victorsube...@gmail.comwrote: So apparently it didn't like my foreign key. Do I need to do something with the table I'm referencing or what? TIA. Well, quickfix is to convert your tables to innoDB, starting with the lowest-level (foreign-key only ones) first - I'm not sure what happens if you set a referential constraint from an innodb table to a myisam table. You can easily convert tables with alter table *yourtable* engine=innodb;. For the tables where you also want to add constraints and/or indices, alter table *yourtable* add constraint *yourconstrainthere* engine=innodb; does the trick in one go. Both ends of the key need to be InnoDB. The fields also need to match in type, nullability, and collation. For additional details about failed FK attempts, check the error details in the SHOW INNODB STATUS report. -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Foreign Key Problem
On Tue, May 18, 2010 at 1:09 PM, Shawn Green shawn.l.gr...@oracle.comwrote: Johan De Meersman wrote: For additional details about failed FK attempts, check the error details in the SHOW INNODB STATUS report. I get this: 100518 10:26:22 Error in foreign key constraint of table seaflight/Passengers: constraint foreign key (id) references Flights (flights_id), constraint foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB: Cannot resolve column name close to: ), constraint foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB Here's my command: create table if not exists Passengers (id int unsigned auto_increment primary key, constraint foreign key (id) references Flights (flights_id), constraint foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB; I think I've got those constraints right. I'm calling the data from those other tables, not the other way around. Please advise. TIA, V
Re: Foreign Key Problem
Victor Subervi wrote: On Tue, May 18, 2010 at 1:09 PM, Shawn Green shawn.l.gr...@oracle.com mailto:shawn.l.gr...@oracle.com wrote: Johan De Meersman wrote: For additional details about failed FK attempts, check the error details in the SHOW INNODB STATUS report. I get this: 100518 10:26:22 Error in foreign key constraint of table seaflight/Passengers: constraint foreign key (id) references Flights (flights_id), constraint foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB: Cannot resolve column name close to: ), constraint foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB Here's my command: create table if not exists Passengers (id int unsigned auto_increment primary key, constraint foreign key (id) references Flights (flights_id), constraint foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB; I think I've got those constraints right. I'm calling the data from those other tables, not the other way around. Please advise. TIA, V I may be confused but how can the ID of the Passengers table be both the ID of the Flight they are taking and their Customer ID at the same time? http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html You may want additional ID columns in the Passengers table to point to the parent values in those other tables. Also, -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
reports
Hi, I want to ask you the next topic, I have to deliver some reports and i want to know if exists some reporting open source tool to generate them. The reports that i need are: - how many users, sessions,cursors by hour, day, week How mysql manage them? is this information stored in some place? - size of each database, data, logs, indexes. I kown how to obtain them - Memory used, CPU used. The main report that i want to make is the first one and i don't know how to obtain it. Thanks for your help. Juan Gallego. EL Ahorro de papel nos ayuda a proteger el medio ambiente.Imprime s?lo lo necesario. Aviso de Confidencialidad: Este mensaje se dirige exclusivamente a su destinatario y puede contener informaci?n CONFIDENCIAL sometida a secreto profesional.Cualquier reproduccion, distribucion o divulgaci?n de su contenido est?n estrictamente prohibidos en virtud de la legislaci?n vigente. Si ha recibido este mensaje por error, le rogamos que nos lo comunique inmediatamente por esta misma v?a o por tel?fono (+ 34 91 339 60 00) y proceda a su destruccion, sin obtener copia del mismo ni distribuirlo ni revelar su contenido. N?tese que este correo electr?nico v?a Internet no permite asegurar ni la confidencialidad de los mensajes que se trasmiten ni la correcta recepci?n de los mismos.En caso de que el destinatario de este mensaje no desee que se le remitan mensajes a trav?s del correo electr?nico v?a internet, rogamos lo pongo en nuestro conocimiento de manera inmediata Privileged and confidential: This message is intended exclusively for its addressee and may contain information that is CONFIDENTIAL and protected bye a profesional privilege,Law strictly prhobits and dissmention, distribution or disclosure of its content,If this message has been received in error,please immediately notify us via e-mail or by telephone (+34 91 339 60 00) and delete it,whitout copyn it,distributing it,or disclosing its contents.Please note that this Internet e-mail dos not guarantee the confidentiality or proper receipt of the messages sent,If the addressee of this message does not desire to receive messages by internet e-mail,please comunicate it to us immediatly.
test - please ignore
i said: ignore! -- hartmut -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: reports
In infinite wisdom Gallego Juan juan.gall...@yellargentina.com wrote: [1 text/plain; us-ascii (quoted-printable)] Hi, I want to ask you the next topic, I have to deliver some reports and i want to know if exists some reporting open source tool to generate them. The reports that i need are: - how many users, sessions,cursors by hour, day, week How mysql manage them? is this information stored in some place? - size of each database, data, logs, indexes. I kown how to obtain them - Memory used, CPU used. Do you have monitoring for your db box? If not, you cannot generate these reports. Check out cacti and cacti-mysql-templates http://code.google.com/p/mysql-cacti-templates/ once you have cacti monitoring on your db box. -- Raj Shekhar - If there's anything more important than my ego around, I want it caught and shot now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
