GROUP BY - INNER JOIN and LIMIT - how to get result
I have tables: CREATE TABLE `tblNames` ( ` IdName` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(60) DEFAULT NULL, PRIMARY KEY (`IdName`), ) ENGINE=MyISAM CREATE TABLE `tblStatusy` ( `IdStatus` int(11) NOT NULL AUTO_INCREMENT, `IdName` int(11) DEFAULT NULL, `Status` varchar(60) DEFAULT NULL, `Data` datetime DEFAULT NULL, PRIMARY KEY (`IdStatus`), KEY `ixIDName` (`IdName `) ) ENGINE=MyISAM How to get result which will be look like this: tblNames.Id, tblNames.Name, (subquery which will return tblStatusy.Status, tblStatusy.Data ordered by Data DESC LIMIT 1), (subquery which will return tblStatusy.Status, tblStatusy.Data ordered by Data DESC LIMIT 1,1), (subquery which will return tblStatusy.Status, tblStatusy.Data ordered by Data DESC LIMIT 2,1) Any idea how to get this? Best regards
Re: Mysql server full with idle connections
300 seconds is 5 minutes, while this whole thing happened probably under a minute. I'm looking for possible causes, not workarounds :-) On Fri, Nov 26, 2010 at 8:57 PM, Prabhat Kumar aim.prab...@gmail.comwrote: Hi, to avoid this change ur wait_timeout value to 300 Secs or less, along with this you can also write a script to kill those process (mysql process) which are in sleep mode for more than certain time.. hope this will helpful.. On Wed, Nov 24, 2010 at 6:41 PM, Johan De Meersman vegiv...@tuxera.be wrote: On Wed, Nov 24, 2010 at 1:13 PM, Nigel Wood nw...@plus.net wrote: Quick thought: what is your idle timeout set to on the MySQL server? Could you have configured it to reap these idle connections? I could, probably, but the applications are generally well-behaved, and it's not a recurring problem. I hope. I'm more interested in the cause, atm. On Wed, Nov 24, 2010 at 1:58 PM, John Daisley daisleyj...@googlemail.com wrote: Seen this a lot with poorly written web apps which open connections but dont close them when finished. Try setting wait_timeout and/or interactive_timeout to close unused connections. Well, yes, but as far as we're aware nothing new has been deployed - this setup is several years old. I suppose it's possible that one of those kind of bugs is hiding somewhere in a forgotten corner of code, but given that we're running Drupal and Wordpress, I'd be surprised at something like that remaining unnoticed for so long. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- 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: Mysql server full with idle connections
Johan De Meersman vegiv...@tuxera.be writes: Hi, You may check the network at that time. We had have a similar problem caused by broken network-card, use `/sbin/ifconfig | grep errors' to check errors/dropped counter. for example, broken network-card may cause many connections, since normal tcp connect open/close can't be completed. -peter Hey, Late last night, I got a call that one of our servers was down. Turns out the machine had all 2500 connection slots in use, but none of them appeared to be doing anything: hardly any CPU was being used, load 0.05, and tcpdump confirmed that pretty much no data was being sent, either. Disks were writeable - so not hanging write flood - and when I eventually sent a kill, the daemon did a clean shutdown and came back up without a hitch. Unfortunately I couldn't log in while this was going on because I never configured a few extra connections for super - that's been fixed by now. Munin shows no discernable cause, no ramp-up on the load or whatever on either the DB or the webservers; just a very sudden increase in open connections and an equally sudden drop in network traffic. Neither slowlog nor sys/errorlog shows anything out of the ordinary. I can think of only two causes: bug in the app code, or bug in MySQL. This is 5.0.32-Debian_7etch3-log (Debian package) on Debian Etch, running on x86_64. Does anyone know of similar things happening ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MyQuery 3.4.1 Released
I have released MyQuery 3.4.1 today. MyQuery is a Windows based Ad-Hoc query tool with some interesting features: - Colour coded syntax - Based on Scintilla - Code folding - Based on Scintilla - Ability to strat a script run inside the script. - Support for error/stop/continue script editing - Highly configurable with user defined tools and many other features - Powerful plugin API And a bunch more things. MyQuery is completely free and Open Source. Version 3.4.1 is a minor bugfix version, where the main thing being fixed is a bug that caused issues to run MyQuery without Admin rights on Windows 7. Read more on the blog here: http://karlssonondatabases.blogspot.com/2010/11/announcement-myquery-341-released.html Or just go ahead and download it from here: http://sourceforge.net/projects/myquery/ Best regards Anders Karlsson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql_upgrade fails with Access denied
I'm trying to run mysql_upgrade but I get: # mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect FATAL ERROR: Upgrade failed I've also tried specifying my root password with --password and also specifying -u mysql but it still fails with error 1045. My /etc/passwd file looks like there is no password for user mysql. What else should I try? You should use the password for the _MySQL_ root user, which is _not_ stored in /etc/passwd, but within MySQL. / Carsten Thanks everyone. I got mysqld to start with --skip-grant-tables by adding skip-grant-tables to /etc/mysql/my.cnf and restarting mysql. mysql_upgrade ran without a problem. - Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql server full with idle connections
On Mon, Nov 29, 2010 at 2:21 PM, Peter Wang wangx...@gmail.com wrote: We had have a similar problem caused by broken network-card, use `/sbin/ifconfig | grep errors' to check errors/dropped counter. for example, broken network-card may cause many connections, since normal tcp connect open/close can't be completed. Ahh, an interesting suggestion. However, 4017 dropped RX packets over an uptime of 1088 days isn't a lot :-) I guess that kind of behaviour would also have shown up in Nagios and Munin; nor would killing and restarting mysqld be likely to have fixed the issue. Still, I'd never have thought of that - thanks. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Verifying security
I've got a couple security questions for you guys. Is there a way to verify I've set a password for mysql's root? I was previously running mysqld without --skip-networking until I noticed port: 3306 was referenced in mysqld.err. The strange thing is 'nmap localhost' never found an open 3306 port and I wasn't running a firewall on that system. Does this behavior make sense to anyone? - Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: localhost vs domain for connection string
-Original Message- From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Thursday, November 25, 2010 3:29 AM To: Jerry Schwartz Cc: Brent Clark; mysql mailing list Subject: Re: localhost vs domain for connection string On Wed, Nov 24, 2010 at 4:44 PM, Jerry Schwartz je...@gii.co.jp wrote: [JS] This might or might not be enabled by default. I'm running on Windows, and I seem to remember having to change it. # Enable named pipe, bypassing the network stack enable-named-pipe Windows' named pipes are not the same as unix sockets, although the general idea is similar. I'm not sure, but I think the Unix socket file is always created. [JS] I don't remember either. I also don't remember if the original question was about *nix or Windows. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Verifying security
To verify that root has a password, do the following: 1) service mysql restart --skip-grant-tables 2) In MySQL, SELECT CONCAT(,user,'''@''',host,) mysql_user,password from mysql.user where user='root'; This will show every host that root can login as along with the PASSWORD function-encrypted of the root password 3) service mysql restart Make sure you have the user 'root'@'localhost'; With regard to --skip-networking, keep in mind that this blocks TCP/IP DB Connections This will not block socket-based connections i.e., this will block 'root'@'127.0.0.1', but not 'root'@'localhost' Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: Grant [mailto:emailgr...@gmail.com] Sent: Monday, November 29, 2010 9:02 AM To: mysql@lists.mysql.com Subject: Verifying security I've got a couple security questions for you guys. Is there a way to verify I've set a password for mysql's root? I was previously running mysqld without --skip-networking until I noticed port: 3306 was referenced in mysqld.err. The strange thing is 'nmap localhost' never found an open 3306 port and I wasn't running a firewall on that system. Does this behavior make sense to anyone? - Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
From Maurizio Ponti, Switzerland
Dear Sirs, I would like to post the list: Topic: mysql server installation, password problems Dear Sirs, I downloaded the last MySQL server version some weeks ago, then I forgot the root password. I disinstalled everything and reinstalled from new, but I'm always asked for the old password in order to define a new one. It seems that an old file related to the password is still there in my computer and I could not erase it by disinstalling the server. Could you tell me which is the file and how could I delete it? Or what should I do in order to solve the problem? Thank you very much. Maurizio
Re: From Maurizio Ponti, Switzerland
Start mysqld with --skip-grant-tables option, give an update on the root
password, mentioning the new password the you want to put for your access
with root user and be happy.
Well, make some like this:
shell mysqld --skip-grant-tables
Open another tty, terminal or prompt:
shell mysql
mysql update mysql.user set password = PASSWORD('12345') where user ='root'
and host = 'localhost';
mysql \q
Stop MySQL...
# Linux or Unix
shell /etc/init.d/mysql restart
# MS Windows
C:\ net stop MySQL
C:\ net start MySQL
And the, create a new connection with MySQL Serber using user new password.
Best regards.
--
WB
2010/11/29 Maurizio Ponti maurizio.po...@gmail.com
Dear Sirs, I would like to post the list:
Topic: mysql server installation, password problems
Dear Sirs, I downloaded the last MySQL server version some weeks ago,
then I forgot the root password. I disinstalled everything and
reinstalled from new, but I'm always asked for the old password in order
to define a new one. It seems that an old file related to the password
is still there in my computer and I could not erase it by disinstalling
the server. Could you tell me which is the file and how could I delete
it? Or what should I do in order to solve the problem? Thank you very
much. Maurizio
Re: From Maurizio Ponti, Switzerland
as root, stop your mysql server in the normal way
ie :$ service mysqld stop
run mysql explicitly to skipp credentials
ie. (run it in the back ground)
$ /usr/libexec/mysqld --skip-grant-tables
once the server starts, you should be able to:
$ mysql -u root
assming you get in (no reason you shouldn't if you got this far), you
can use SQL statements to manipulate user data; you can't use GRANT or
SET PASSWORD so
use mysql;
update user set Password = PASSWORD('yourpassword') where User = 'root';
exit your session and kill your mysqld process. (get the pid via ps
-aux, then kill that)
restart mysql normally, and you should be good. (I had to do this
over the weekend on a system someone else setup but failed to record
the credentials).
- michael dykman
On Mon, Nov 29, 2010 at 12:28 PM, Maurizio Ponti
maurizio.po...@gmail.com wrote:
Dear Sirs, I would like to post the list:
Topic: mysql server installation, password problems
Dear Sirs, I downloaded the last MySQL server version some weeks ago,
then I forgot the root password. I disinstalled everything and
reinstalled from new, but I'm always asked for the old password in order
to define a new one. It seems that an old file related to the password
is still there in my computer and I could not erase it by disinstalling
the server. Could you tell me which is the file and how could I delete
it? Or what should I do in order to solve the problem? Thank you very
much. Maurizio
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: From Maurizio Ponti, Switzerland
Hi Michael,
I am not sure whether your UPDATE statement will affect all root users
password or only which one that will access from a localhost.
Best regards.
--
WB
2010/11/29 Michael Dykman mdyk...@gmail.com
as root, stop your mysql server in the normal way
ie :$ service mysqld stop
run mysql explicitly to skipp credentials
ie. (run it in the back ground)
$ /usr/libexec/mysqld --skip-grant-tables
once the server starts, you should be able to:
$ mysql -u root
assming you get in (no reason you shouldn't if you got this far), you
can use SQL statements to manipulate user data; you can't use GRANT or
SET PASSWORD so
use mysql;
update user set Password = PASSWORD('yourpassword') where User = 'root';
exit your session and kill your mysqld process. (get the pid via ps
-aux, then kill that)
restart mysql normally, and you should be good. (I had to do this
over the weekend on a system someone else setup but failed to record
the credentials).
- michael dykman
On Mon, Nov 29, 2010 at 12:28 PM, Maurizio Ponti
maurizio.po...@gmail.com wrote:
Dear Sirs, I would like to post the list:
Topic: mysql server installation, password problems
Dear Sirs, I downloaded the last MySQL server version some weeks ago,
then I forgot the root password. I disinstalled everything and
reinstalled from new, but I'm always asked for the old password in order
to define a new one. It seems that an old file related to the password
is still there in my computer and I could not erase it by disinstalling
the server. Could you tell me which is the file and how could I delete
it? Or what should I do in order to solve the problem? Thank you very
much. Maurizio
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
Re: Verifying security
without flags to tell it otherwise, nmap only scan ports 1-1024, higher numbered ports would have to be specified via nmap -p 1-5000 but see how much longer it takes. - michael dykman On Mon, Nov 29, 2010 at 9:02 AM, Grant emailgr...@gmail.com wrote: I've got a couple security questions for you guys. Is there a way to verify I've set a password for mysql's root? I was previously running mysqld without --skip-networking until I noticed port: 3306 was referenced in mysqld.err. The strange thing is 'nmap localhost' never found an open 3306 port and I wasn't running a firewall on that system. Does this behavior make sense to anyone? - Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] mySQL query assistance...
On Mon, Nov 29, 2010 at 14:35, Don Wieland d...@dwdataconcepts.com wrote: Hi all, Is there a list/form to get some help on compiling mySQL queries? I am executing them via PHP, but do not want to ask for help here if it is no the appropriate forum. Thanks ;-) Yes. For MySQL queries, write to the MySQL General list at my...@lists.mysql.com. For PHP-specific database questions (for any database backend, not strictly MySQL), such as problems in connecting to the database, questions on support for database platform/version, or even query processing, you should use php...@lists.php.net. For your convenience, both have been CC'd on this email. -- /Daniel P. Brown Dedicated Servers, Cloud and Cloud Hybrid Solutions, VPS, Hosting (866-) 725-4321 http://www.parasane.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: From Maurizio Ponti, Switzerland
It will affect all users named root. If you need to limit it on a
per-host basis, first SELECT User, Host from mysql.user where User
='root' and qualify with the update with the Host names you find
there.
- md
On Mon, Nov 29, 2010 at 1:58 PM, Wagner Bianchi
wagnerbianch...@gmail.com wrote:
Hi Michael,
I am not sure whether your UPDATE statement will affect all root users
password or only which one that will access from a localhost.
Best regards.
--
WB
2010/11/29 Michael Dykman mdyk...@gmail.com
as root, stop your mysql server in the normal way
ie :$ service mysqld stop
run mysql explicitly to skipp credentials
ie. (run it in the back ground)
$ /usr/libexec/mysqld --skip-grant-tables
once the server starts, you should be able to:
$ mysql -u root
assming you get in (no reason you shouldn't if you got this far), you
can use SQL statements to manipulate user data; you can't use GRANT or
SET PASSWORD so
use mysql;
update user set Password = PASSWORD('yourpassword') where User = 'root';
exit your session and kill your mysqld process. (get the pid via ps
-aux, then kill that)
restart mysql normally, and you should be good. (I had to do this
over the weekend on a system someone else setup but failed to record
the credentials).
- michael dykman
On Mon, Nov 29, 2010 at 12:28 PM, Maurizio Ponti
maurizio.po...@gmail.com wrote:
Dear Sirs, I would like to post the list:
Topic: mysql server installation, password problems
Dear Sirs, I downloaded the last MySQL server version some weeks ago,
then I forgot the root password. I disinstalled everything and
reinstalled from new, but I'm always asked for the old password in order
to define a new one. It seems that an old file related to the password
is still there in my computer and I could not erase it by disinstalling
the server. Could you tell me which is the file and how could I delete
it? Or what should I do in order to solve the problem? Thank you very
much. Maurizio
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] mySQL query assistance...
This is the general list. If your problem is with MySQL and queries, let us know. Best regards. -- WB 2010/11/29 Daniel P. Brown daniel.br...@parasane.net On Mon, Nov 29, 2010 at 14:35, Don Wieland d...@dwdataconcepts.com wrote: Hi all, Is there a list/form to get some help on compiling mySQL queries? I am executing them via PHP, but do not want to ask for help here if it is no the appropriate forum. Thanks ;-) Yes. For MySQL queries, write to the MySQL General list at my...@lists.mysql.com. For PHP-specific database questions (for any database backend, not strictly MySQL), such as problems in connecting to the database, questions on support for database platform/version, or even query processing, you should use php...@lists.php.net. For your convenience, both have been CC'd on this email. -- /Daniel P. Brown Dedicated Servers, Cloud and Cloud Hybrid Solutions, VPS, Hosting (866-) 725-4321 http://www.parasane.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
Light a candle for 26/11 and Show you remember
Hi mysql@lists.mysql.com , I just light a candle for 26/11. It is time to show that we have neither forgiven nor forgotten 26/11. It is time to remember those who paid with their lives for the fanaticism of a few, to salute those who gave up their lives trying to shield others, and to honor those who survived the ordeal of terror. It is time to light a candle, to show that as we move on, we cherish their memories, we value their sacrifice and we hail their courage. Please Light a Candle Now at: http://www.indiajaiho.com/LightACandle.htm Warm Regards, Sharath -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
INSERT DELAYED and logging
I'm adding a table to our site that logs all page loads. In the past, when I built this, I used MyISAM and INSERT DELAYED. I went back to look at the documentation to see if I should still do this, and saw this (taken from http://dev.mysql.com/doc/refman/5.1/en/insert-delayed.html): Note that INSERT DELAYED is slower than a normal INSERT if the table is not otherwise in use. There is also the additional overhead for the server to handle a separate thread for each table for which there are delayed rows. This means that you should use INSERT DELAYED only when you are really sure that you need it. Does that mean that I shouldn't use it if all I'm doing is INSERT (essentially an append-only table), with only very occasional SELECTs? In addition, the last time I took this approach for logging, it worked well until the table got to 65M+ rows, when it would crash every now and then. I know I can archive off the table on a per month/quarter basis as well. Waynn
Re: INSERT DELAYED and logging
Well, analyze if you need to create an excessive overhead into the MySQL Server because a simple INSERT. What you must have a look is it: - How much data this connection is delivering to MySQL's handlers? - A word DELAYED in this case is making MySQL surfer? Perhaps, you are sophisticating something that do not need it. Besides it, analyzing your log table, I imagine this table can be an Archive table instead of MyISAM. Log tables or history tables can be controlled by Archive Storage Engine to have more compressed data. Although, Archive Storage Engine only supports SELECT and INSERT. Maybe, a good deal to you, get rid of you INSERT DELAYED: - ALTER TABLE tbl_name ENGINE = ARCHIVE; Best regards. -- WB 2010/11/29 WLGades wlga...@gmail.com I'm adding a table to our site that logs all page loads. In the past, when I built this, I used MyISAM and INSERT DELAYED. I went back to look at the documentation to see if I should still do this, and saw this (taken from http://dev.mysql.com/doc/refman/5.1/en/insert-delayed.html): Note that INSERT DELAYED is slower than a normal INSERT if the table is not otherwise in use. There is also the additional overhead for the server to handle a separate thread for each table for which there are delayed rows. This means that you should use INSERT DELAYED only when you are really sure that you need it. Does that mean that I shouldn't use it if all I'm doing is INSERT (essentially an append-only table), with only very occasional SELECTs? In addition, the last time I took this approach for logging, it worked well until the table got to 65M+ rows, when it would crash every now and then. I know I can archive off the table on a per month/quarter basis as well. Waynn
can't stop mysql under ubuntu
Hi, I'm running mysql 5.1.37 for ubuntu 9.10 (64 bits OS). I installed mysqld with apt-get, and going with /etc/init.d/mysql for start/stop the server. I can start mysql server successfully, but can't stop the server with /etc/init.d/mysql stop. The error message is: Nov 30 10:46:05 kickseed kernel: [73702.681483] type=1503 audit(1291085165.873:164): operation=open pid=15424 parent=15423 profile=/usr/sbin/mysqld requested_mask=r:: denied_mask=r:: fsuid=0 ouid=0 name=/sys/devices/system/cpu/ Nov 30 10:46:33 kickseed kernel: [73730.137574] type=1503 audit(1291085193.373:165): operation=open pid=15448 parent=15447 profile=/usr/sbin/mysqld requested_mask=r:: denied_mask=r:: fsuid=0 ouid=0 name=/sys/devices/system/cpu/ Nov 30 10:46:33 kickseed kernel: [73730.164645] type=1503 audit(1291085193.403:166): operation=open pid=15468 parent=15467 profile=/usr/sbin/mysqld requested_mask=r:: denied_mask=r:: fsuid=0 ouid=0 name=/sys/devices/system/cpu/ Nov 30 10:46:33 kickseed kernel: [73730.356014] type=1503 audit(1291085193.593:167): operation=open pid=15653 parent=15474 profile=/usr/sbin/mysqld requested_mask=r:: denied_mask=r:: fsuid=0 ouid=0 name=/sys/devices/system/cpu/ Nov 30 10:46:34 kickseed kernel: [73731.183992] type=1503 audit(1291085194.423:168): operation=open pid=15662 parent=15661 profile=/usr/sbin/mysqld requested_mask=r:: denied_mask=r:: fsuid=0 ouid=0 name=/sys/devices/system/cpu/ Nov 30 10:46:35 kickseed kernel: [73732.197943] type=1503 audit(1291085195.443:169): operation=open pid=15678 parent=15677 profile=/usr/sbin/mysqld requested_mask=r:: denied_mask=r:: fsuid=0 ouid=0 name=/sys/devices/system/cpu/ Nov 30 10:46:35 kickseed kernel: [73732.232258] type=1503 audit(1291085195.473:170): operation=open pid=15689 parent=15688 profile=/usr/sbin/mysqld requested_mask=r:: denied_mask=r:: fsuid=0 ouid=0 name=/sys/devices/system/cpu/ Nov 30 10:46:54 kickseed kernel: [73751.579821] type=1503 audit(1291085214.853:171): operation=open pid=15735 parent=15734 profile=/usr/sbin/mysqld requested_mask=r:: denied_mask=r:: fsuid=0 ouid=0 name=/sys/devices/system/cpu/ Also I tried to add the line /sys/devices/system/cpu/ r, to /etc/apparmor.d/usr.sbin.mysqld then restart apparmor, mysql still coundn't be stopped with /etc/init.d/mysql stop. Please help, thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: INSERT DELAYED and logging
No, I think it's a good idea to do INSERT DELAYED here - it's only logging application, and it's generally more important to not slow down the application for that. It's only ever into a single table, so there's only going to be a single delay thread for it anyway. Archive tables are a good idea, agreed, but I suspect that inserts into that are going to be slower than into regular MyISAM because of the compression, so why not use that overhead to (slightly) speed up your end-user experience instead ? You can always partition the table based on the log date or whatever, if your table risks getting too big. On Tue, Nov 30, 2010 at 1:03 AM, Wagner Bianchi wagnerbianch...@gmail.comwrote: Well, analyze if you need to create an excessive overhead into the MySQL Server because a simple INSERT. What you must have a look is it: - How much data this connection is delivering to MySQL's handlers? - A word DELAYED in this case is making MySQL surfer? Perhaps, you are sophisticating something that do not need it. Besides it, analyzing your log table, I imagine this table can be an Archive table instead of MyISAM. Log tables or history tables can be controlled by Archive Storage Engine to have more compressed data. Although, Archive Storage Engine only supports SELECT and INSERT. Maybe, a good deal to you, get rid of you INSERT DELAYED: - ALTER TABLE tbl_name ENGINE = ARCHIVE; Best regards. -- WB 2010/11/29 WLGades wlga...@gmail.com I'm adding a table to our site that logs all page loads. In the past, when I built this, I used MyISAM and INSERT DELAYED. I went back to look at the documentation to see if I should still do this, and saw this (taken from http://dev.mysql.com/doc/refman/5.1/en/insert-delayed.html): Note that INSERT DELAYED is slower than a normal INSERT if the table is not otherwise in use. There is also the additional overhead for the server to handle a separate thread for each table for which there are delayed rows. This means that you should use INSERT DELAYED only when you are really sure that you need it. Does that mean that I shouldn't use it if all I'm doing is INSERT (essentially an append-only table), with only very occasional SELECTs? In addition, the last time I took this approach for logging, it worked well until the table got to 65M+ rows, when it would crash every now and then. I know I can archive off the table on a per month/quarter basis as well. Waynn -- 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: can't stop mysql under ubuntu
Yep, it's definitely an apparmor issue. Are you using your distribution's packages ? I would say this is more a thing for their support - I for one keep as far away from apparmor as I can :-) On Tue, Nov 30, 2010 at 3:54 AM, Jorg W Young jorgwyoung...@gmail.comjorgwyoung%2...@gmail.com wrote: Hi, I'm running mysql 5.1.37 for ubuntu 9.10 (64 bits OS). I installed mysqld with apt-get, and going with /etc/init.d/mysql for start/stop the server. I can start mysql server successfully, but can't stop the server with /etc/init.d/mysql stop. The error message is: Nov 30 10:46:05 kickseed kernel: [73702.681483] type=1503 audit(1291085165.873:164): operation=open pid=15424 parent=15423 profile=/usr/sbin/mysqld requested_mask=r:: denied_mask=r:: fsuid=0 ouid=0 name=/sys/devices/system/cpu/ Nov 30 10:46:33 kickseed kernel: [73730.137574] type=1503 audit(1291085193.373:165): operation=open pid=15448 parent=15447 profile=/usr/sbin/mysqld requested_mask=r:: denied_mask=r:: fsuid=0 ouid=0 name=/sys/devices/system/cpu/ Nov 30 10:46:33 kickseed kernel: [73730.164645] type=1503 audit(1291085193.403:166): operation=open pid=15468 parent=15467 profile=/usr/sbin/mysqld requested_mask=r:: denied_mask=r:: fsuid=0 ouid=0 name=/sys/devices/system/cpu/ Nov 30 10:46:33 kickseed kernel: [73730.356014] type=1503 audit(1291085193.593:167): operation=open pid=15653 parent=15474 profile=/usr/sbin/mysqld requested_mask=r:: denied_mask=r:: fsuid=0 ouid=0 name=/sys/devices/system/cpu/ Nov 30 10:46:34 kickseed kernel: [73731.183992] type=1503 audit(1291085194.423:168): operation=open pid=15662 parent=15661 profile=/usr/sbin/mysqld requested_mask=r:: denied_mask=r:: fsuid=0 ouid=0 name=/sys/devices/system/cpu/ Nov 30 10:46:35 kickseed kernel: [73732.197943] type=1503 audit(1291085195.443:169): operation=open pid=15678 parent=15677 profile=/usr/sbin/mysqld requested_mask=r:: denied_mask=r:: fsuid=0 ouid=0 name=/sys/devices/system/cpu/ Nov 30 10:46:35 kickseed kernel: [73732.232258] type=1503 audit(1291085195.473:170): operation=open pid=15689 parent=15688 profile=/usr/sbin/mysqld requested_mask=r:: denied_mask=r:: fsuid=0 ouid=0 name=/sys/devices/system/cpu/ Nov 30 10:46:54 kickseed kernel: [73751.579821] type=1503 audit(1291085214.853:171): operation=open pid=15735 parent=15734 profile=/usr/sbin/mysqld requested_mask=r:: denied_mask=r:: fsuid=0 ouid=0 name=/sys/devices/system/cpu/ Also I tried to add the line /sys/devices/system/cpu/ r, to /etc/apparmor.d/usr.sbin.mysqld then restart apparmor, mysql still coundn't be stopped with /etc/init.d/mysql stop. Please help, thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- 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: can't stop mysql under ubuntu
2010/11/30 Johan De Meersman vegiv...@tuxera.be: Yep, it's definitely an apparmor issue. Are you using your distribution's packages ? I would say this is more a thing for their support - I for one keep as far away from apparmor as I can :-) yep I 'm using the distribution packages all from apt-get. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: can't stop mysql under ubuntu
On Tue, Nov 30, 2010 at 7:26 AM, Jorg W Young jorgwyoung...@gmail.comjorgwyoung%2...@gmail.com wrote: 2010/11/30 Johan De Meersman vegiv...@tuxera.be: Yep, it's definitely an apparmor issue. Are you using your distribution's packages ? I would say this is more a thing for their support - I for one keep as far away from apparmor as I can :-) yep I 'm using the distribution packages all from apt-get. Then I would definitely say to contact your distro support or mailing lists, and check the forums. It may be a known issue. -- 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: INSERT DELAYED and logging
What I'm confused by though, is this line. Note that INSERT DELAYED is slower than a normal INSERT if the table is not otherwise in use. What's the definition of in use? Does a logging table do that given that it's pretty much append-only/write-only? Waynn On Mon, Nov 29, 2010 at 10:19 PM, Johan De Meersman vegiv...@tuxera.bewrote: No, I think it's a good idea to do INSERT DELAYED here - it's only logging application, and it's generally more important to not slow down the application for that. It's only ever into a single table, so there's only going to be a single delay thread for it anyway. Archive tables are a good idea, agreed, but I suspect that inserts into that are going to be slower than into regular MyISAM because of the compression, so why not use that overhead to (slightly) speed up your end-user experience instead ? You can always partition the table based on the log date or whatever, if your table risks getting too big. On Tue, Nov 30, 2010 at 1:03 AM, Wagner Bianchi wagnerbianch...@gmail.com wrote: Well, analyze if you need to create an excessive overhead into the MySQL Server because a simple INSERT. What you must have a look is it: - How much data this connection is delivering to MySQL's handlers? - A word DELAYED in this case is making MySQL surfer? Perhaps, you are sophisticating something that do not need it. Besides it, analyzing your log table, I imagine this table can be an Archive table instead of MyISAM. Log tables or history tables can be controlled by Archive Storage Engine to have more compressed data. Although, Archive Storage Engine only supports SELECT and INSERT. Maybe, a good deal to you, get rid of you INSERT DELAYED: - ALTER TABLE tbl_name ENGINE = ARCHIVE; Best regards. -- WB 2010/11/29 WLGades wlga...@gmail.com I'm adding a table to our site that logs all page loads. In the past, when I built this, I used MyISAM and INSERT DELAYED. I went back to look at the documentation to see if I should still do this, and saw this (taken from http://dev.mysql.com/doc/refman/5.1/en/insert-delayed.html): Note that INSERT DELAYED is slower than a normal INSERT if the table is not otherwise in use. There is also the additional overhead for the server to handle a separate thread for each table for which there are delayed rows. This means that you should use INSERT DELAYED only when you are really sure that you need it. Does that mean that I shouldn't use it if all I'm doing is INSERT (essentially an append-only table), with only very occasional SELECTs? In addition, the last time I took this approach for logging, it worked well until the table got to 65M+ rows, when it would crash every now and then. I know I can archive off the table on a per month/quarter basis as well. Waynn -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
