RE: problems with INNODB tables
Thanks for your answer. I read http://mysql.rjweb.org/doc.php/memory where it tells you to do one thing if using MYIASM tables and another if using INNODB tables. We are using both. Any suggestions? Thanks for any help. Malki Cymbalista Webmaster, Weizmann Institute of Science malki.cymbali...@weizmann.ac.il 08-9343036 -Original Message- From: Rick James [mailto:rja...@yahoo-inc.com] Sent: Monday, April 23, 2012 9:42 PM To: Andrés Tello; Malka Cymbalista Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun Subject: RE: problems with INNODB tables Check your memory usage according to http://mysql.rjweb.org/doc.php/memory -Original Message- From: Andrés Tello [mailto:mr.crip...@gmail.com] Sent: Monday, April 23, 2012 9:00 AM To: Malka Cymbalista Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun Subject: Re: problems with INNODB tables Weird, I use a lot Innodb, and no issue, I even kill bravely the mysql process with pkill -9 -f mysql Y suppose the way drupal is being programed. PHP open and closes database connections each time a webpage with db access is issued. When a php exceution ends and the apache webserver have fullfilled the http request, again, php memory is freed and connections closed... UNLESS:.. you are using a mem cached db connection, wich I doubt it since drupal doens't requiere one, or using persistent connections, again, I doubt it, because persistante database connections aren't recommended to innodb tables... Mysql server by default can handles 100 conections, if you get to thata limit you need to fine tune the number of connections allowed. show full processlist can give you a better idea of what is going on, connections with the sleep status, are open connections with no currently no transacctions... I never use script based stop, I always use mysqladmin -u root -p -h localhost shutdown which properly tells mysql to flush tables and terminate. I can almost bet that you are using Ubuntu... ubuntu had given me sometimes very hard times because of the edgy code they use to use, ext4 last version, and so on... what can you tell us about that? How much amount of memory you have? How much concurrent apache/php users you have? Can you provide more cuantitive data please? Hardware, php version, distro, kernel... Cheers... To start, 100 process is quite a lot, something isn't fine. Each time On Mon, Apr 23, 2012 at 9:10 AM, Malka Cymbalista malki.cymbali...@weizmann.ac.il wrote: We are running MySQL version 5.0.45 on a Linux machine. Over the past few months we have been having several problems: 1. Our mysql processes have increased the memory used from about .3% per process to 8% per process 2. We sometimes can have over 100 processes running which brings the machine to its knees and we have to stop and start MySQL in order to kill all the processes. We think that maybe the processes are not finishing normally and are just hanging around. 3. The machine is a web server and in the last few months we are moving over to drupal 7 to build our sites and Drupal 7 requires INNODB tables. Sometimes, when we restart MySQL using the commands /etc/init.d/mysql stop and /etc/init.d/mysql start our sites that were built in drupal 7 do not come up. In order for the INNODB tables to work, we have to stop mysql, rename the ibdata1 file, copy it back to ibdata1 and then restart mysql. Otherwise the INNODB tables are not accessable. In the past all our tables were MYIASM. Our problems started as we started using more and more INNODB tables. Is there anything special that has to be done to configure MySQL when using INNODB tables? We clearly have a problem but we have no idea where to start looking. Our error logs don't show anything. If anyone has any suggestions, we will be happy to hear them. We are considering hiring a consultant who is an expert in MySQL. We are in Israel and we are open to suggestions. Thanks for any help. Malki Cymbalista Webmaster, Weizmann Institute of Science malki.cymbali...@weizmann.ac.ilmailto:malki.cymbali...@weizmann.ac.il 08-9343036 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Quit in MySQL logs
We are running MySQL 5.0.45 on a Linux machine. I have enabled the general query log and I notice that for some of the connections, the last command is Quit but for some of the connections, the Quit command does not appear. What do I have to do to ensure that the Quit command gets printed in the log. Thanks for any help. -- Malka Cymbalista Webmaster, Weizmann Institute of Science [EMAIL PROTECTED] 08-934-3036 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
We have just moved to a new web server where we are running mysql version: 5.0.45
We have just moved to a new web server where we are running mysql version:
5.0.45
On the old machine, when we gave the following command:
update table1 set passwd = password('xx') where user_name=xx;
and then gave the following command,
SELECT user_name FROM alon_protein_passwd WHERE (user_name = xx AND
passwd=password('xx'))
we got the expected results.
Now when we do this, on the new machine, we get no result i.e. Empty set.
Any help will be appreciated.
--
Malka Cymbalista
Webmaster, Weizmann Institute of Science
[EMAIL PROTECTED]
08-934-3036
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: We have just moved to a new web server where we are runningmysql version: 5.0.45
Thanks for the answer but that's not it. It was my typo. I am really updating
and selecting from the same table.
--
Malka Cymbalista
Webmaster, Weizmann Institute of Science
[EMAIL PROTECTED]
08-934-3036
On 4/9/2008 at 1:54 PM, in message
[EMAIL PROTECTED], Edward Kay
[EMAIL PROTECTED] wrote:
We have just moved to a new web server where we are running mysql
version: 5.0.45
On the old machine, when we gave the following command:
update table1 set passwd = password('xx') where user_name=xx;
Here you're updating 'table1'...
and then gave the following command,
SELECT user_name FROM alon_protein_passwd WHERE (user_name = xx
AND passwd=password('xx'))
..but here you're selecting from 'alon_protein_passwd'
we got the expected results.
Now when we do this, on the new machine, we get no result i.e. Empty set.
Any help will be appreciated.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: We have just moved to a new web server where we arerunningmysql version: 5.0.45
Thanks for the email but that didn't help. I gave the commands again using only single quotes and I still get no results. On the old machine we were running MySQL 4.0.15a. Is there any difference with the password function in these 2 versions? -- Malka Cymbalista Webmaster, Weizmann Institute of Science [EMAIL PROTECTED] 08-934-3036 On 4/9/2008 at 2:17 PM, in message [EMAIL PROTECTED], Martijn Tonies [EMAIL PROTECTED] wrote: Thanks for the answer but that's not it. It was my typo. I am really updating and selecting from the same table. Perhaps not it either, but strings should be in single quotes, not double quotes. 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: We have just moved to a new web server where wearerunningmysql version: 5.0.45
Thanks. So how does one use the password function in MySQL 5? -- Malka Cymbalista Webmaster, Weizmann Institute of Science [EMAIL PROTECTED] 08-934-3036 On 4/9/2008 at 2:57 PM, in message [EMAIL PROTECTED], Martijn Tonies [EMAIL PROTECTED] wrote: Thanks for the email but that didn't help. I gave the commands again using only single quotes and I still get no results. On the old machine we were running MySQL 4.0.15a. Is there any difference with the password function in these 2 versions? Could be, I remember MySQL 4.1 having a new password mechanism. If this uses the same function, I guess you shouldn't be surprised it returns a very different value in 4.1 and up. 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: We have just moved to a new web server where we are running mysql version: 5.0.45
Thanks to Martin and Paul for their answers.
--
Malka Cymbalista
Webmaster, Weizmann Institute of Science
[EMAIL PROTECTED]
08-934-3036
On 4/9/2008 at 3:54 PM, in message [EMAIL PROTECTED], Paul
DuBois [EMAIL PROTECTED] wrote:
At 1:13 PM +0300 4/9/08, Malka Cymbalista wrote:
We have just moved to a new web server where we are running mysql
version: 5.0.45
On the old machine, when we gave the following command:
update table1 set passwd = password('xx') where user_name=xx;
and then gave the following command,
SELECT user_name FROM alon_protein_passwd WHERE (user_name = xx
AND passwd=password('xx'))
we got the expected results.
Now when we do this, on the new machine, we get no result i.e. Empty set.
Any help will be appreciated.
The password hashing algorithm changed in MySQL 4.1. You can read about
it here:
http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html
This is likely the cause of the changes that you're observing. I
imagine that you'll either need to upgrade the passwords in your
table, or look for them using OLD_PASSWORD() rather than PASSWORD().
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql privileges
I would like to thank everyone who gave suggestions about how to fix the mysql privileges. Here's what we did: We did a mysqldump on the mysql table on the old machine. We brought the mysqldump into the mysql table on the new machine We ran mysql_fix_privilege_tables We gave the commandflush privileges Everything worked perfectly after that. Thanks again to everyone who helped. -- Malka Cymbalista Webmaster, Weizmann Institute of Science [EMAIL PROTECTED] 08-934-3036 On 3/19/2008 at 6:10 PM, in message [EMAIL PROTECTED], Sebastian Mendel [EMAIL PROTECTED] wrote: Brown, Charles schrieb: Here is a follow-up question: Using mysqldump, I'm about to dump all databases and import to another instance - new . My question is do I need to define all security and users in the new mysql or the security definitions and privileges will be included in the dump file created by mysqldump. i am not sure if mysqldump does include `mysql` database, but you will see if you look into it, you should run mysql_fix_privilege_tables after importing `mysql` database and FLUSH PRIVILEGES; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql privileges
We are currently running MySQL 4.0.15a on a Sun Solaris server. We are moving to a Linux machine running MySQL 5.0.45. I am having a problem with permissions in MySQL. On the current machine running 4.0.15a, when I connect to MySQL as the user super and give the command: select lname from hr where fname = shlomit; I get the expected result. On the new machine running MySQL 5.0.45, when I connect as the user super and give the same command, I get the following error: ERROR 1142 (42000): SELECT command denied to user 'super'@'localhost' for table 'hr' The MySQL permissions are the same on both machines. When I give the following command: select * from tables_priv where user=super and db =web_positions and table_name = hr; I get the following result on both machines: | Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv +--+---+---+++-++-+ | %| web_positions | super | hr | [EMAIL PROTECTED] | 2002-07-21 15:07:17 | Select | | When I give the following command, I aslo get the same results on both machines: select * from user where user =super; The results are N for all the different privileges. Has anything changed in MySQL 5.0.45 that would cause this behavior? Thanks for any help. Malka Cymbalista Webmaster, Weizmann Institute of Science [EMAIL PROTECTED] 08-934-3036
Re: mysql privileges
Thanks for your reply. When I do show grants, I get back GRANT SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'super' @ 'localhost' ( mailto:super'@ 'localhost' ) identified by password... So it looks like super doesn't have rights to select from the hr table. But why not? According to the tables_priv table, super should have right to select. I tried giving the command (as root) grant select on web_positions.hr to [EMAIL PROTECTED] identified by . I get back ERROR 2013 (HY000): Lost connection to MySQL server during query I can give any other command but when I try to give the grant command I keep getting the same error. Malka Cymbalista Webmaster, Weizmann Institute of Science [EMAIL PROTECTED] 08-934-3036 On 3/18/2008 at 3:20 PM, in message [EMAIL PROTECTED], Baron Schwartz [EMAIL PROTECTED] wrote: Hi, On Tue, Mar 18, 2008 at 8:49 AM, Malka Cymbalista [EMAIL PROTECTED] wrote: We are currently running MySQL 4.0.15a on a Sun Solaris server. We are moving to a Linux machine running MySQL 5.0.45. I am having a problem with permissions in MySQL. On the current machine running 4.0.15a, when I connect to MySQL as the user super and give the command: select lname from hr where fname = shlomit; I get the expected result. On the new machine running MySQL 5.0.45, when I connect as the user super and give the same command, I get the following error: ERROR 1142 (42000): SELECT command denied to user 'super'@'localhost' for table 'hr' The MySQL permissions are the same on both machines. When I give the following command: select * from tables_priv where user=super and db =web_positions and table_name = hr; I get the following result on both machines: | Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv +--+---+---+++-++-+ | %| web_positions | super | hr | [EMAIL PROTECTED] | 2002-07-21 15:07:17 | Select | | When I give the following command, I aslo get the same results on both machines: select * from user where user =super; The results are N for all the different privileges. Has anything changed in MySQL 5.0.45 that would cause this behavior? Probably not. You are probably not logged in as the user you think you are. Instead of checking privileges by selecting from the mysql system tables, use SHOW GRANTS to see what your privileges are and who you're logged in as. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlhotcopy
I am trying to use mysqlhotcopy on 2 different machines and I am having trouble on both of them. On the first machine, which is a Sun Solaris running mysql 4.0.15a, when I give the mysqlhotcopy command, I get the following error: DBD::mysql::db do failed: File './zemed/form_342.MYD' not found (Errcode: 24) at /usr/local/mysql/bin/mysqlhotcopy line 449. The file zemed/form_342.MYD does exist, but it's size is 0. On the second machine, which is a linux machine running mysql 5.0.45, when I give the mysqlhotcopy command, mysqlhotcopy web_positions /tmp/web_positions I get the error Invalid db.table name 'web_positions.web_positions`.`acadsec' at /usr/local/mysql/bin/mysqlhotcopy line 855. If I try to copy the mysql database, I get the error Invalid db.table name 'mysql.mysql`.`columns_priv' at /usr/local/mysql/bin/mysqlhotcopy line 855. Does anyone have any suggestions as to what I can do to get this to work. thanks. -- Malka Cymbalista Webmaster, Weizmann Institute of Science [EMAIL PROTECTED] 08-934-3036 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
log files and upgrading
Is it possible to log information to the general log file only for a specific database? We are currently running MySQL 4.0.15. We are planning on moving to a new server so and will upgrade MySQL. What is the latest most stable version that is recommended? Thanks for any information. -- Malka Cymbalista Webmaster, Weizmann Institute of Science [EMAIL PROTECTED] 08-934-3036 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
looping error message
We are running Apache 1.3.26 with mod perl 1.26, perl 5.6.1, and mysql 3.23.49 on a Sun Solaris machine. Every now and then, one of the scripts we are runing gives the following error message: null: DBD::mysql::st fetch failed: fetch() without execute() at /usr/local/lib/perl5/site_perl/local/Calendar.pm line 1667. The really annoying thing is that the error message keeps getting written to to the error log until it fills up the disk. We are looking into the source of the error message but I was wondering if anyone has any ideas as to why the error message goes into a loop and if there is anything we can do to stop it. Thanks for any information. Malki Cymbalista Webmaster, Weizmann Institute of Science Rehovot, Israel 76100 Internet: [EMAIL PROTECTED] 08-934-3036 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql 4, Solaris 9 and Perl 5.8
We are getting new Solaris machines on which I plan to install MySQL. We will be installing Solaris 9 on these machines. Offhand, does anyone know if there are problems with compiling and installing MySQL 4 on Solaris 9. I am also planning on installing Perl 5.8 on these new machines. The last time I installed Perl 5.8 on my current machine, It caused problems with MySQL ( I do not remember anymore what the problems were - it was quite a while ago and it was with MySQL 3.23.49). Does anyone know if there are problems or is anyone successfully running MySQL 4 with Perl 5.8 on Solaris 9. Thanks for any information. Malki Cymbalista Webmaster, Weizmann Institute of Science Rehovot, Israel 76100 Internet: [EMAIL PROTECTED] 08-934-3036 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: An existing table
Give the command: alter table tablename add column column_name create_definition (section 7.7 in the mysql manual) Malki Cymbalista Webmaster, Weizmann Institute of Science Rehovot, Israel 76100 Internet: [EMAIL PROTECTED] Yair Zohar [EMAIL PROTECTED] 07/07/02 01:46PM Hello, Is there a way to add a column to an existing table in a mysql database? couldn't find it on the guide. Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
