Re: Background tasks performed by MySQL?
Viktor Fougstedt wrote: Hi, and thank you both for valuable tips. The MySQLd in question runs on a mailserver, and a large amount of processes (Postfix, Maildrop and Squirrelmail) connect to it, run one or two simple queries, and then disconnects. There is only one client that is constantly connected, namely a configuration server. It does not have a cache, and only asks a few small questions every now and then. I have modified the code so that it logs any SQL-queries that take more than 2 clock seconds, which should show me if the config server is the culprit. I tried SHOW INNODB STATUS when MySQLd was taking 100% of one CPU, and the Main thread state was sleeping. Also from the same command all TRANSACTIONs seemed idle except for one, which was running my SHOW INNODB STATUS command. I draw the conclusion that whatever is happening, it's not InnoDB. Since all the tables that the configuration server uses are in InnoDB, it also seems likely that the config server is not the culprit either. Is there any way for me to find out exactly what queries have been run in the last X minutes? When the load goes up, I could check to see what queries ran before, to possibly find a pattern. If I could temporarily log queries and the time they took to complete, that would also be a good way forward. The General Query log doesn't seem to log the time a query took (as I read the manual). I have a cron-job that logs the current machine load and a SHOW STATUS every five minutes. I just awk:ed through it, and I might imagine it, but there is a possible connection between the Max_used_connections parameter increasing and the machine's load going up. Could a massive storm of connections be causing the slowdown? Some form of lock contention having to do with new connections or similar? Can I reset the Max_used_connections parameter so I get a maxlevel for the last five minutes rather than since the last restart? Lots of questions, so I am deeply grateful for any insights into any of them, /Viktor... Hi! I'm not sure if You've got any answers (I don't read the list systematically), but for me this behaviour seems a bit like locking issue. A good way to look at it is looking at processlist (SHOW PROCESSLIST) - where processes have their status displayed - look for Locked status. I administer one db, which is based on MyISAM tables. Sometimes it experienced similar behaviour - first comes one time consuming query, after it some DML, then other queries - all must wait for the first query. In my opinion first query acquire READ lock, then DML wait for WRITE lock and may be if it waits, other queries can not be performed due to this wait for WRITE lock. The solution here could be redesigning of database by limiting DML operations or using LOW PRIORITY DML queries Any thoughts or comments? Regards, Remigiusz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Assigment sign not work on Delphi
cmiiw, dont you think you are supposed to pass the query as string in delphi??? somewhat like this : querystr:='SELECT @TOTAL:=PRICE * QTY FROM INV_PRODUCT'; mysql_query(@mysqlconnection,PChar(querystr)); ... etc ... so delphi will ignore the second ':=' because it's in a string, not an assignment operator and dont forget to put '@' before TOTAL, or else you will get error from mysql because it will think TOTAL as a global server variable The Nice Spider wrote: Using Delphi to with this query: SELECT TOTAL := PRICE * QTY FROM INV_PRODUCT will caused error Parameter object is improperly defined. Inconsistent or incomplete information was provided. because Delphi look it as Parameter (a parameter of query in Delphi using : at the beginning). Is it better for MySQL using = rather than := ? Or is there any setting to set MySQL to accept the = sign? __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Regarding SET FOREIGN_KEY_CHECKS=0;
Noga, if SET FOREIGN_KEY_CHECKS=0 affects other connections to the database, that is a serious bug. If you can repeat the problem, please file a bug report to bugs.mysql.com I tested this with 5.0.15, and it worked ok: Connection 1: [EMAIL PROTECTED]:~/mysql-5.0.15/client$ ./mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.0.15-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql create table t(a int primary key) type = innodb; Query OK, 0 rows affected, 1 warning (0.08 sec) mysql create table t2(a int primary key, foreign key (a) references t(a)) type = innodb; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) mysql Connection 2: [EMAIL PROTECTED]:~/mysql-5.0.15/client$ ./mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.15-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql insert into t2 values (10); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t` (`a`)) mysql 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 - Original Message - From: Noga Woronoff [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, November 18, 2005 11:56 PM Subject: FW: Regarding SET FOREIGN_KEY_CHECKS=0; Thank you Heikki! We had a problem where code in one of our program's EJB did not turn the FK CHECK back to 1. Another program that was started afterwards caused some bad data to be inserted in the database - as though the FK CHECK was =3D 0. It wasn't until we turned the FK CHECK back to 1 in the first program that the second one started to behave correctly. We use JBoss and MySQL 4.0.21. Any feedback on the nature of JBoss-MySQL transaction management will be most helpful to us. Thank you much in advance - Noga -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Friday, November 18, 2005 2:56 PM To: Noga Woronoff Cc: Heikki Tuuri Subject: Re: Regarding SET FOREIGN_KEY_CHECKS=3D0; Noga, please forward this communication to mysql@lists.mysql.com mysql -u root -p -e SET FOREIGN_KEY_CHECKS=3D0; /tmp/dump.sql FOREIGN_KEY_CHECKS is a per-session variable. When the above shell=20 command returns, the session ends. Thus, no problem for others. Regards, Heikki Oracle/Innobase Noga Woronoff wrote: Hi Heikki - =20 =20 =20 I don't know which user group to use and wonder whether you may answer a question for me? =20 =20 =20 When you perform: =20 mysql -u root -p -e SET FOREIGN_KEY_CHECKS=3D0; /tmp/dump.sql =20 =20 =20 Does the InnoDB engine set the foreign key checks back to 1 again, automatically - once the database install is complete? Under what circumstances one can get into trouble if the FK check is not set back to 1 at the end of the transaction? Is there a white paper I can read on the subject since I cannot find anything? =20 =20 =20 Is there a way to check whether the FK check is turned ON/OF? =20 =20 =20 Thanks you MUCH in advance and GOOD LUCK in your new Oracle adventure! =20 =20 =20 Noga Woronoff =20 Interactive Constructs, Inc. =20 200 Boston Ave. Suite #1800 =20 Medford, MA 02155 =20 =20 -- 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]
Need Perfomance / Tuning Help
Hey, we´ve a site ( PHP ) where several 1.000 are online at the same time. They´re running many sql statements. Is there a way to find out which statements take a full table scan to optimize them ? And i need some help with configuring the my.cnf. Below you´ll find our one. Which options should i optimize ? uname -a : Linux m30s06db.ispgateway.de 2.4.29-grsec #10 SMP Mon Jul 4 14:26:46 CEST 2005 i686 Intel(R) Pentium(R) 4 CPU 3.00GHz GenuineIntel GNU/Linux And thats the my.cnf : # Example MySQL config file for medium systems. # # This is for a system with little memory (32M - 64M) where MySQL plays # an important part, or systems up to 128M where MySQL is used together with # other programs (such as a web server) # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /kunden/106120_40670/ms_appl/mysql_4.1.10a/var) or # ~/.my.cnf to set user-specific options. # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the --help option. # The following options will be passed to all MySQL clients [client] #password = your_password port = 3307 socket = /tmp/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3307 socket = /tmp/mysql.sock skip-locking key_buffer = 64M max_allowed_packet = 1M table_cache = 5M sort_buffer_size = 1M net_buffer_length = 8K read_buffer_size = 1M read_rnd_buffer_size = 512K myisam_sort_buffer_size = 16M record_buffer=1M log-slow-queries long_query_time = 3 # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the enable-named-pipe option) will render mysqld useless! # # #skip-networking # Replication Master Server (default) # binary logging is required for replication log-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - # the syntax is: # # CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, # MASTER_USER=user, MASTER_PASSWORD=password ; # # where you replace host, user, password by quoted strings and # port by the master's port number (3306 by default). # # Example: # # CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, # MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then # start replication for the first time (even unsuccessfully, for example # if you mistyped the password in master-password and the slave fails to # connect), the slave will create a master.info file, and any later # change in this file to the variables' values below will be ignored and # overridden by the content of the master.info file, unless you shutdown # the slave server, delete master.info and restart the slaver server. # For that reason, you may want to leave the lines below untouched # (commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication master for this slave - required #master-host = hostname # # The username the slave will use for authentication when connecting # to the master - required #master-user = username # # The password the slave will authenticate with when connecting to # the master - required #master-password = password # # The port the master is listening on. # optional - defaults to 3306 #master-port = port # # binary logging - not required for slaves, but recommended #log-bin # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using BDB tables #bdb_cache_size = 4M #bdb_max_lock = 1 # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /kunden/106120_40670/ms_appl/mysql_4.1.10a/var/ #innodb_data_file_path = ibdata1:10M:autoextend #innodb_log_group_home_dir = /kunden/106120_40670/ms_appl/mysql_4.1.10a/var/ #innodb_log_arch_dir = /kunden/106120_40670/ms_appl/mysql_4.1.10a/var/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high #innodb_buffer_pool_size = 16M #innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 5M
Re: SSH tunnel for Mysql
Hello. You have not specified what operating system you're using. MySQL manual has some notes for Windows: http://dev.mysql.com/doc/refman/5.0/en/windows-and-ssh.html Jerry Swanson wrote: How to create ssh tunnel for Mysql? TH -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL_CALC_FOUND_ROWS
Hello. data, if may grow, I like the cleanness of the SQL_CALC_FOUND_ROWS, however, currently, it is only saving me code, not performance. Often it is ok to have more complex code to get better performance. You should decide what is important for you - clearness of the code, or speed of your queries. BTW: ORDER BY with LIMIT clause sometimes can be optimized to be very fast, but don't use SQL_CALC_FOUND_ROWS in you query. See: http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html Scott Haneda wrote: 4.0.18-standard-log Having some performance issues, and I can not see why: SELECT SQL_CALC_FOUND_ROWS l.id, u.b_first_name FROM logbook AS l LEFT JOIN users AS u ON l.user_id = u.id ORDER BY id DESC LIMIT 0, 20; 20 rows in set (1.21 sec) If I take out the SQL_CALC_FOUND_ROWS, of course, it is instant, as I only get 20 records, however, I added it in to save a select: SELECT count(*) FROM logbook AS l LEFT JOIN users AS u ON l.user_id = u.id; 1 row in set (0.11 sec) So, while the first using SQL_CALC_FOUND_ROWS saves me a second select to get the data, is is significantly slower. While this is a small set of data, if may grow, I like the cleanness of the SQL_CALC_FOUND_ROWS, however, currently, it is only saving me code, not performance. mysql EXPLAIN SELECT count(*) FROM logbook AS l LEFT JOIN users AS u ON l.user_id = u.id; +---++---+-+-+---+---+-- ---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+---+-- ---+ | l | ALL| NULL | NULL|NULL | NULL | 12671 | | | u | eq_ref | PRIMARY | PRIMARY | 4 | l.user_id | 1 | Using index | +---++---+-+-+---+---+-- ---+ mysql EXPLAIN SELECT SQL_CALC_FOUND_ROWS l.id, u.b_first_name FROM logbook AS l LEFT JOIN users AS u ON l.user_id = u.id ORDER BY id DESC LIMIT 0, 20; +---++---+-+-+---+---+-- --+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+---+-- --+ | l | ALL| NULL | NULL|NULL | NULL | 12671 | Using filesort | | u | eq_ref | PRIMARY | PRIMARY | 4 | l.user_id | 1 | | +---++---+-+-+---+---+-- --+ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Perfomance / Tuning Help
Hello. Is there a way to find out which statements take a full table scan to optimize them ? See: http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html Marco Schierhorn wrote: Hey, we´ve a site ( PHP ) where several 1.000 are online at the same time. They´re running many sql statements. Is there a way to find out which statements take a full table scan to optimize them ? And i need some help with configuring the my.cnf. Below you´ll find our one. Which options should i optimize ? uname -a : Linux m30s06db.ispgateway.de 2.4.29-grsec #10 SMP Mon Jul 4 14:26:46 CEST 2005 i686 Intel(R) Pentium(R) 4 CPU 3.00GHz GenuineIntel GNU/Linux And thats the my.cnf : # Example MySQL config file for medium systems. # # This is for a system with little memory (32M - 64M) where MySQL plays # an important part, or systems up to 128M where MySQL is used together with # other programs (such as a web server) # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /kunden/106120_40670/ms_appl/mysql_4.1.10a/var) or # ~/.my.cnf to set user-specific options. # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the --help option. # The following options will be passed to all MySQL clients [client] #password = your_password port = 3307 socket = /tmp/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3307 socket = /tmp/mysql.sock skip-locking key_buffer = 64M max_allowed_packet = 1M table_cache = 5M sort_buffer_size = 1M net_buffer_length = 8K read_buffer_size = 1M read_rnd_buffer_size = 512K myisam_sort_buffer_size = 16M record_buffer=1M log-slow-queries long_query_time = 3 # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the enable-named-pipe option) will render mysqld useless! # # #skip-networking # Replication Master Server (default) # binary logging is required for replication log-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - # the syntax is: # # CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, # MASTER_USER=user, MASTER_PASSWORD=password ; # # where you replace host, user, password by quoted strings and # port by the master's port number (3306 by default). # # Example: # # CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, # MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then # start replication for the first time (even unsuccessfully, for example # if you mistyped the password in master-password and the slave fails to # connect), the slave will create a master.info file, and any later # change in this file to the variables' values below will be ignored and # overridden by the content of the master.info file, unless you shutdown # the slave server, delete master.info and restart the slaver server. # For that reason, you may want to leave the lines below untouched # (commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication master for this slave - required #master-host = hostname # # The username the slave will use for authentication when connecting # to the master - required #master-user = username # # The password the slave will authenticate with when connecting to # the master - required #master-password = password # # The port the master is listening on. # optional - defaults to 3306 #master-port = port # # binary logging - not required for slaves, but recommended #log-bin # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using BDB tables #bdb_cache_size = 4M #bdb_max_lock = 1 # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir =
RE: Regarding SET FOREIGN_KEY_CHECKS=0;
Hi Heikki - I tested the two connections in MySQL 4.0.26 and got the same results as you did: Connection 1: mysql create table t(a int primary key) type = innodb; Query OK, 0 rows affected (0.13 sec) mysql create table t2(a int primary key, foreign key (a) references t(a)) type = innodb; Query OK, 0 rows affected (0.08 sec) mysql set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) Connection 2: mysql insert into t2 values (10); ERROR 1216: Cannot add or update a child row: a foreign key constraint fails mysql Am I right to assume that when a MySQL-client session ends the FK checks is back to 1 automatically? That when an EJB turns SET FOREIGN_KEY_CHECKS=0; WITHOUT SETing FOREIGN_KEY_CHECKS=1; after a set of sql queries - the subsequent EJB session termination by JBoss should restore the FK state checks=1? I do realize that the prudent approach in the EJB is to turn the FK check back to 1 at the conclusion of the sql queries - HOWEVER just as (MySQL 4.1) mysqldump sets the FK checks to 0 at the top of the file without setting it back to 1 at the bottom of the file I assume that when the MySQL-client session ends - the FK's checks is set back to 1. I am turning my attention now to JBoss-MySQL EJB session management and any advice will be greatly appreciated. Regards - Noga -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Monday, November 21, 2005 4:21 AM To: mysql@lists.mysql.com Cc: Noga Woronoff Subject: Re: Regarding SET FOREIGN_KEY_CHECKS=0; Noga, if SET FOREIGN_KEY_CHECKS=0 affects other connections to the database, that is a serious bug. If you can repeat the problem, please file a bug report to bugs.mysql.com I tested this with 5.0.15, and it worked ok: Connection 1: [EMAIL PROTECTED]:~/mysql-5.0.15/client$ ./mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.0.15-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql create table t(a int primary key) type = innodb; Query OK, 0 rows affected, 1 warning (0.08 sec) mysql create table t2(a int primary key, foreign key (a) references t(a)) type = innodb; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) mysql Connection 2: [EMAIL PROTECTED]:~/mysql-5.0.15/client$ ./mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.15-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql insert into t2 values (10); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t` (`a`)) mysql 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 - Original Message - From: Noga Woronoff [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, November 18, 2005 11:56 PM Subject: FW: Regarding SET FOREIGN_KEY_CHECKS=0; Thank you Heikki! We had a problem where code in one of our program's EJB did not turn the FK CHECK back to 1. Another program that was started afterwards caused some bad data to be inserted in the database - as though the FK CHECK was =3D 0. It wasn't until we turned the FK CHECK back to 1 in the first program that the second one started to behave correctly. We use JBoss and MySQL 4.0.21. Any feedback on the nature of JBoss-MySQL transaction management will be most helpful to us. Thank you much in advance - Noga -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Friday, November 18, 2005 2:56 PM To: Noga Woronoff Cc: Heikki Tuuri Subject: Re: Regarding SET FOREIGN_KEY_CHECKS=3D0; Noga, please forward this communication to mysql@lists.mysql.com mysql -u root -p -e SET FOREIGN_KEY_CHECKS=3D0; /tmp/dump.sql FOREIGN_KEY_CHECKS is a per-session variable. When the above shell=20 command returns, the session ends. Thus, no problem for others. Regards, Heikki Oracle/Innobase Noga Woronoff wrote: Hi Heikki - =20 =20 =20 I don't know which user group to use and wonder whether you may answer a question for me? =20 =20 =20 When you perform: =20 mysql -u root -p -e SET FOREIGN_KEY_CHECKS=3D0; /tmp/dump.sql =20 =20 =20 Does the InnoDB engine set the foreign key checks back to 1 again, automatically - once the database install is complete? Under what circumstances one can get into trouble if the FK check is not set back to 1 at the end of the transaction? Is there a white paper I can read on the subject since I cannot find anything? =20 =20 =20 Is there a way to check whether the FK check is turned ON/OF? =20 =20 =20 Thanks you MUCH in advance and GOOD LUCK in your new Oracle
Re: MySQL installation problems.
Hello. Is this a clean new install of MySQL, or you have already installed MySQL instance? Please provide more information about your OS version (I guess it is Windows, isn't it?). May be some tips from the manual could be helpful: http://dev.mysql.com/doc/refman/5.0/en/windows-troubleshooting.html http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html Kevin Pollard wrote: Today I downloaded and installed MySQL 5.0 (the Complete Package). Everything seemed to go with the install until it got to the Processing configuration... stage. It successfully completed Prepare configuration, and Write configuration file, and Start service. But it failed when it got to Apply security settings with the error message The security settings could not be applied. Error number 2003. Can't connect ot MySQL server on 'locahost' (10061) What can I do about that? Kevin -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fwd: which release of Mysql is compatible with fedora core3 ?
Hello. i have fedora core3 installed on my system. which release of Mysql is compatible with fedora core3 ? MySQL should be suitable for most modern Linux distributions (I'm not sure if FC3 still supported). Use 5.0.15. Have a look here as well: http://dev.mysql.com/doc/refman/5.0/en/linux.html ali asghar torabi parizy wrote: Note: forwarded message attached. Yahoo! FareChase - Search multiple travel sites in one click. http://us.lrd.yahoo.com/_ylc=X3oDMTFqODRtdXQ4BF9TAzMyOTc1MDIEX3MDOTY2ODgxNjkEcG9zAzEEc2VjA21haWwtZm9vdGVyBHNsawNmYw--/SIG=110oav78o/**http%3a//farechase.yahoo.com/ Subject: which release of Mysql is compatible with fedora core3 ? From: ali asghar torabi parizy [EMAIL PROTECTED] Date: Sat, 19 Nov 2005 22:03:49 -0800 (PST) To: mysql@lists.mysql.com To: mysql@lists.mysql.com Received: from [194.225.121.253] by web35303.mail.mud.yahoo.com via HTTP; Sat, 19 Nov 2005 22:03:49 PST MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=0-843886813-1132466629=:80640 Content-Transfer-Encoding: 8bit i have fedora core3 installed on my system. which release of Mysql is compatible with fedora core3 ? Yahoo! FareChase - Search multiple travel sites in one click. http://us.lrd.yahoo.com/_ylc=X3oDMTFqODRtdXQ4BF9TAzMyOTc1MDIEX3MDOTY2ODgxNjkEcG9zAzEEc2VjA21haWwtZm9vdGVyBHNsawNmYw--/SIG=110oav78o/**http%3a//farechase.yahoo.com/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Perfomance / Optimize ( Full Table Scans ) and Help with my.cnf
Hey, we´ve a site ( PHP ) where several 1.000 are online at the same time. They´re running many sql statements. Is there a way to find out which statements take a full table scan to optimize them ? We´ve a cpu percentage of nearly 50 % with only 30 Members online. And i´ve seen that there are some full table scans. Where can i investigate them ? And i need some help with configuring the my.cnf. Below you´ll find our one. Which options should i optimize ? We´ve much free memory but our cpu is working to half of the full capacity... uname -a : Linux m30s06db.ispgateway.de 2.4.29-grsec #10 SMP Mon Jul 4 14:26:46 CEST 2005 i686 Intel(R) Pentium(R) 4 CPU 3.00GHz GenuineIntel GNU/Linux And thats the my.cnf : # Example MySQL config file for medium systems. # # This is for a system with little memory (32M - 64M) where MySQL plays # an important part, or systems up to 128M where MySQL is used together with # other programs (such as a web server) # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /kunden/106120_40670/ms_appl/mysql_4.1.10a/var) or # ~/.my.cnf to set user-specific options. # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the --help option. # The following options will be passed to all MySQL clients [client] #password = your_password port = 3307 socket = /tmp/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3307 socket = /tmp/mysql.sock skip-locking key_buffer = 64M max_allowed_packet = 1M table_cache = 5M sort_buffer_size = 1M net_buffer_length = 8K read_buffer_size = 1M read_rnd_buffer_size = 512K myisam_sort_buffer_size = 16M record_buffer=1M log-slow-queries long_query_time = 3 # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the enable-named-pipe option) will render mysqld useless! # # #skip-networking # Replication Master Server (default) # binary logging is required for replication log-bin # required unique id between 1 and 232 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - # the syntax is: # # CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, # MASTER_USER=user, MASTER_PASSWORD=password ; # # where you replace host, user, password by quoted strings and # port by the master's port number (3306 by default). # # Example: # # CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, # MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then # start replication for the first time (even unsuccessfully, for example # if you mistyped the password in master-password and the slave fails to # connect), the slave will create a master.info file, and any later # change in this file to the variables' values below will be ignored and # overridden by the content of the master.info file, unless you shutdown # the slave server, delete master.info and restart the slaver server. # For that reason, you may want to leave the lines below untouched # (commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 232 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication master for this slave - required #master-host = hostname # # The username the slave will use for authentication when connecting # to the master - required #master-user = username # # The password the slave will authenticate with when connecting to # the master - required #master-password = password # # The port the master is listening on. # optional - defaults to 3306 #master-port = port # # binary logging - not required for slaves, but recommended #log-bin # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using BDB tables #bdb_cache_size = 4M #bdb_max_lock = 1 # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /kunden/106120_40670/ms_appl/mysql_4.1.10a/var/ #innodb_data_file_path = ibdata1:10M:autoextend #innodb_log_group_home_dir = /kunden/106120_40670/ms_appl/mysql_4.1.10a/var/ #innodb_log_arch_dir = /kunden/106120_40670/ms_appl/mysql_4.1.10a/var/ # You can set .._buffer_pool_size up
trouble with deleting a user
hi to all i have a problem when i trying to delet a user by drop user directive *** drop user atp; Can't drop one or more of the requested users *** i add this user by crant: *** GRANT ALL PRIVILEGES ON *.* TO 'atp'@'%' IDENTIFIED BY 'nowayforyou' WITH GRANT OPTION; *** - Yahoo! FareChase - Search multiple travel sites in one click.
Re: Regarding SET FOREIGN_KEY_CHECKS=0;
Noga, yes, the setting SET FOREIGN_KEY_CHECKS=0 is per session. If it is somehow 'inherited' to another session, that is a serious bug. 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 Noga Woronoff wrote: Hi Heikki - I tested the two connections in MySQL 4.0.26 and got the same results as you did: Connection 1: mysql create table t(a int primary key) type = innodb; Query OK, 0 rows affected (0.13 sec) mysql create table t2(a int primary key, foreign key (a) references t(a)) type = innodb; Query OK, 0 rows affected (0.08 sec) mysql set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) Connection 2: mysql insert into t2 values (10); ERROR 1216: Cannot add or update a child row: a foreign key constraint fails mysql Am I right to assume that when a MySQL-client session ends the FK checks is back to 1 automatically? That when an EJB turns SET FOREIGN_KEY_CHECKS=0; WITHOUT SETing FOREIGN_KEY_CHECKS=1; after a set of sql queries - the subsequent EJB session termination by JBoss should restore the FK state checks=1? I do realize that the prudent approach in the EJB is to turn the FK check back to 1 at the conclusion of the sql queries - HOWEVER just as (MySQL 4.1) mysqldump sets the FK checks to 0 at the top of the file without setting it back to 1 at the bottom of the file I assume that when the MySQL-client session ends - the FK's checks is set back to 1. I am turning my attention now to JBoss-MySQL EJB session management and any advice will be greatly appreciated. Regards - Noga -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Monday, November 21, 2005 4:21 AM To: mysql@lists.mysql.com Cc: Noga Woronoff Subject: Re: Regarding SET FOREIGN_KEY_CHECKS=0; Noga, if SET FOREIGN_KEY_CHECKS=0 affects other connections to the database, that is a serious bug. If you can repeat the problem, please file a bug report to bugs.mysql.com I tested this with 5.0.15, and it worked ok: Connection 1: [EMAIL PROTECTED]:~/mysql-5.0.15/client$ ./mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.0.15-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql create table t(a int primary key) type = innodb; Query OK, 0 rows affected, 1 warning (0.08 sec) mysql create table t2(a int primary key, foreign key (a) references t(a)) type = innodb; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) mysql Connection 2: [EMAIL PROTECTED]:~/mysql-5.0.15/client$ ./mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.15-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql insert into t2 values (10); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t` (`a`)) mysql 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 - Original Message - From: Noga Woronoff [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, November 18, 2005 11:56 PM Subject: FW: Regarding SET FOREIGN_KEY_CHECKS=0; Thank you Heikki! We had a problem where code in one of our program's EJB did not turn the FK CHECK back to 1. Another program that was started afterwards caused some bad data to be inserted in the database - as though the FK CHECK was =3D 0. It wasn't until we turned the FK CHECK back to 1 in the first program that the second one started to behave correctly. We use JBoss and MySQL 4.0.21. Any feedback on the nature of JBoss-MySQL transaction management will be most helpful to us. Thank you much in advance - Noga -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Friday, November 18, 2005 2:56 PM To: Noga Woronoff Cc: Heikki Tuuri Subject: Re: Regarding SET FOREIGN_KEY_CHECKS=3D0; Noga, please forward this communication to mysql@lists.mysql.com mysql -u root -p -e SET FOREIGN_KEY_CHECKS=3D0; /tmp/dump.sql FOREIGN_KEY_CHECKS is a per-session variable. When the above shell=20 command returns, the session ends. Thus, no problem for others. Regards, Heikki Oracle/Innobase Noga Woronoff wrote: Hi Heikki - =20 =20 =20 I don't know which user group to use and wonder whether you may answer a question for me? =20 =20 =20 When you perform: =20 mysql -u root -p -e SET FOREIGN_KEY_CHECKS=3D0; /tmp/dump.sql =20 =20 =20 Does the InnoDB engine set the foreign key checks back to 1 again, automatically - once the
RE: Regarding SET FOREIGN_KEY_CHECKS=0;
Hi Heikki - Thank you. The issue is apparently in the JBoss-MySQL session management in our application. Regards - Noga -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Monday, November 21, 2005 8:16 AM To: Noga Woronoff Cc: mysql@lists.mysql.com; Jim Kan; Kevin Chee Subject: Re: Regarding SET FOREIGN_KEY_CHECKS=0; Noga, yes, the setting SET FOREIGN_KEY_CHECKS=0 is per session. If it is somehow 'inherited' to another session, that is a serious bug. 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 Noga Woronoff wrote: Hi Heikki - I tested the two connections in MySQL 4.0.26 and got the same results as you did: Connection 1: mysql create table t(a int primary key) type = innodb; Query OK, 0 rows affected (0.13 sec) mysql create table t2(a int primary key, foreign key (a) references t(a)) type = innodb; Query OK, 0 rows affected (0.08 sec) mysql set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) Connection 2: mysql insert into t2 values (10); ERROR 1216: Cannot add or update a child row: a foreign key constraint fails mysql Am I right to assume that when a MySQL-client session ends the FK checks is back to 1 automatically? That when an EJB turns SET FOREIGN_KEY_CHECKS=0; WITHOUT SETing FOREIGN_KEY_CHECKS=1; after a set of sql queries - the subsequent EJB session termination by JBoss should restore the FK state checks=1? I do realize that the prudent approach in the EJB is to turn the FK check back to 1 at the conclusion of the sql queries - HOWEVER just as (MySQL 4.1) mysqldump sets the FK checks to 0 at the top of the file without setting it back to 1 at the bottom of the file I assume that when the MySQL-client session ends - the FK's checks is set back to 1. I am turning my attention now to JBoss-MySQL EJB session management and any advice will be greatly appreciated. Regards - Noga -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Monday, November 21, 2005 4:21 AM To: mysql@lists.mysql.com Cc: Noga Woronoff Subject: Re: Regarding SET FOREIGN_KEY_CHECKS=0; Noga, if SET FOREIGN_KEY_CHECKS=0 affects other connections to the database, that is a serious bug. If you can repeat the problem, please file a bug report to bugs.mysql.com I tested this with 5.0.15, and it worked ok: Connection 1: [EMAIL PROTECTED]:~/mysql-5.0.15/client$ ./mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.0.15-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql create table t(a int primary key) type = innodb; Query OK, 0 rows affected, 1 warning (0.08 sec) mysql create table t2(a int primary key, foreign key (a) references t(a)) type = innodb; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) mysql Connection 2: [EMAIL PROTECTED]:~/mysql-5.0.15/client$ ./mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.15-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql insert into t2 values (10); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t` (`a`)) mysql 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 - Original Message - From: Noga Woronoff [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, November 18, 2005 11:56 PM Subject: FW: Regarding SET FOREIGN_KEY_CHECKS=0; Thank you Heikki! We had a problem where code in one of our program's EJB did not turn the FK CHECK back to 1. Another program that was started afterwards caused some bad data to be inserted in the database - as though the FK CHECK was =3D 0. It wasn't until we turned the FK CHECK back to 1 in the first program that the second one started to behave correctly. We use JBoss and MySQL 4.0.21. Any feedback on the nature of JBoss-MySQL transaction management will be most helpful to us. Thank you much in advance - Noga -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Friday, November 18, 2005 2:56 PM To: Noga Woronoff Cc: Heikki Tuuri Subject: Re: Regarding SET FOREIGN_KEY_CHECKS=3D0; Noga, please forward this communication to mysql@lists.mysql.com mysql -u root -p -e SET FOREIGN_KEY_CHECKS=3D0; /tmp/dump.sql FOREIGN_KEY_CHECKS is a per-session variable. When the above
Re: Regarding SET FOREIGN_KEY_CHECKS=0;
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Noga Woronoff wrote: Hi Heikki - I tested the two connections in MySQL 4.0.26 and got the same results as you did: Connection 1: mysql create table t(a int primary key) type = innodb; Query OK, 0 rows affected (0.13 sec) mysql create table t2(a int primary key, foreign key (a) references t(a)) type = innodb; Query OK, 0 rows affected (0.08 sec) mysql set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) Connection 2: mysql insert into t2 values (10); ERROR 1216: Cannot add or update a child row: a foreign key constraint fails mysql Am I right to assume that when a MySQL-client session ends the FK checks is back to 1 automatically? That when an EJB turns SET FOREIGN_KEY_CHECKS=0; WITHOUT SETing FOREIGN_KEY_CHECKS=1; after a set of sql queries - the subsequent EJB session termination by JBoss should restore the FK state checks=1? I do realize that the prudent approach in the EJB is to turn the FK check back to 1 at the conclusion of the sql queries - HOWEVER just as (MySQL 4.1) mysqldump sets the FK checks to 0 at the top of the file without setting it back to 1 at the bottom of the file I assume that when the MySQL-client session ends - the FK's checks is set back to 1. I am turning my attention now to JBoss-MySQL EJB session management and any advice will be greatly appreciated. Noga, JBoss is most likely using a connection pool, which means that the connection doesn't physically close, which also means that the value for SET FOREIGN KEY CHECKS isn't reset to its default value automatically. If you can use MysqlConnectionPoolDataSource with JBoss' connection pool, the MySQL JDBC driver will reset connection state when the logical connection is closed. If you don't use our ConnectionPoolDataSource, your application is responsible for setting session-level variables that affect server behavior to appropriate values before returning them to the connection pool. -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDgcwYtvXNTca6JD8RApLrAJ45rGcCyU4K1k2Y3Xf/2mO1hXA5GwCePG2U LuadazxAp4+gR8XH9YsNR8Y= =yZOd -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installation help ...!
Hello. I am a complete newbie here and to the world of Linux MySQL. I am trying to create a LAMP server I have installed RedHat Enterprise Linux AS 4 - update 2 on my server and it seems to work ok. I downloaded MySQL 5.0 and now want to install that I read somewhere that I need to remove rpm files from the server ... I think you can do this via add/remove programs in the RedHat Gui How do I install MySQL on the server. I have tried quite a few times but I can never seem to get access to the MYSQL prompt ... I get some error about a socket connection . Is there any documentation available for installing MySQL 5.0 on to redhat ..? Many Thanks - Paul *** The information contained within this e-mail (and any attachment) sent by Birmingham City Council is confidential and may be legally privileged. It is intended only for the named recipient or entity to whom it is addressed. If you are not the intended recipient please accept our apologies and notify the sender immediately, or telephone +(44) 121 464 Unauthorised access, use, disclosure, storage or copying is not permitted and may be unlawful. Any e-mail including its content may be monitored and used by Birmingham City Council for reasons of security and for monitoring internal compliance with the office policy on staff use. E-mail blocking software may also be used. Any views or opinions presented are solely those of the originator and do not necessarily represent those of Birmingham City Council. We cannot guarantee that this message or any attachment is virus free or has not been intercepted and amended. ***
RE: Regarding SET FOREIGN_KEY_CHECKS=0;
Hi Mark. Thank you. Your explanation describes the situation we are dealing with. I will let our chief architect Jim Kan take it from here. Regards - Noga -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED] Sent: Monday, November 21, 2005 8:31 AM To: Noga Woronoff Cc: Heikki Tuuri; mysql@lists.mysql.com; Jim Kan; Kevin Chee Subject: Re: Regarding SET FOREIGN_KEY_CHECKS=0; -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Noga Woronoff wrote: Hi Heikki - I tested the two connections in MySQL 4.0.26 and got the same results as you did: Connection 1: mysql create table t(a int primary key) type = innodb; Query OK, 0 rows affected (0.13 sec) mysql create table t2(a int primary key, foreign key (a) references t(a)) type = innodb; Query OK, 0 rows affected (0.08 sec) mysql set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) Connection 2: mysql insert into t2 values (10); ERROR 1216: Cannot add or update a child row: a foreign key constraint fails mysql Am I right to assume that when a MySQL-client session ends the FK checks is back to 1 automatically? That when an EJB turns SET FOREIGN_KEY_CHECKS=0; WITHOUT SETing FOREIGN_KEY_CHECKS=1; after a set of sql queries - the subsequent EJB session termination by JBoss should restore the FK state checks=1? I do realize that the prudent approach in the EJB is to turn the FK check back to 1 at the conclusion of the sql queries - HOWEVER just as (MySQL 4.1) mysqldump sets the FK checks to 0 at the top of the file without setting it back to 1 at the bottom of the file I assume that when the MySQL-client session ends - the FK's checks is set back to 1. I am turning my attention now to JBoss-MySQL EJB session management and any advice will be greatly appreciated. Noga, JBoss is most likely using a connection pool, which means that the connection doesn't physically close, which also means that the value for SET FOREIGN KEY CHECKS isn't reset to its default value automatically. If you can use MysqlConnectionPoolDataSource with JBoss' connection pool, the MySQL JDBC driver will reset connection state when the logical connection is closed. If you don't use our ConnectionPoolDataSource, your application is responsible for setting session-level variables that affect server behavior to appropriate values before returning them to the connection pool. -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDgcwYtvXNTca6JD8RApLrAJ45rGcCyU4K1k2Y3Xf/2mO1hXA5GwCePG2U LuadazxAp4+gR8XH9YsNR8Y= =yZOd -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index of JOINed table?
I have a simple query, (part of a union). calendar is a table of only dates, `date` as PK. sales has a multi-col index on (date, uid). (select calendar.date as date, time_format(time(sales.date),'%H:%i') as time, from calendar left join sales on date(sales.date) = calendar.date and sales.uid = 'george' where calendar.date between '2005-08-01' and '2005-08-14') First, I don't quite understand joins(!) on constants (sales.uid = 'george'). Is this proper, or should uid be included in where clause? Second, explain shows no possible keys for sales. id select_type table typepossible_keys key key_len ref rows Extra 1 PRIMARY calendarrange PRIMARY PRIMARY 3 NULL11 Using where; Using index 1 PRIMARY sales ALL NULLNULLNULLNULL23 Some versions of my query showed possible_keys, but I can't seem to find what I did and I'm frustrated. What am I doing wrong here? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to adjust the row id after modifying the tabel
Hi, all
I follow the example:
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;
The thing is that after delete one of them like this:
delete from animals where id=4;
how can I have the 'id' to be continuous. Now it will be like:
1
2
3
5
6
Or shall I create the table differently at first? If so, how?
Thanks for your help in advance.
Xiaobo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: how am I missing this key
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Using mysql 5.0.15 my query gets a result of Unknown column 'i.rid' in 'on clause' There is actually a complete query, but this is a snippet, as the selects with subqueries I don't believe will cause the problem. When I changed c.rid=i.rid to c.rid=c.rid, the query executes. It is incorrect, but at least it runs, so, it appears that that the last join is where the problem is. Any suggestions as to what might be the cause? Thanx. FROM items i, nams.netids n INNER JOIN nams.names AS na ON n.badge=na.badge INNER JOIN nams.affiliations AS a ON a.badge=na.badge INNER JOIN nams.roles AS r ON a.role=r.code LEFT OUTER JOIN nams.regterms AS t ON (na.badge=t.badge) LEFT OUTER JOIN curuse AS c ON (c.rid=i.rid) WHERE i.rid=12415 AND n.netid='alb' AND i.status='A' AND c.badge IS NULL LIMIT 1; When I use explain on mysql 4.1.08 I get this: - --+ | id | select_type| table | type | possible_keys | key| key_len | ref| rows | Extra | +++---++++-++--+--+ | 1 | PRIMARY| i | const | PRIMARY | PRIMARY| 4 | const |1 | | | 1 | PRIMARY| n | const | PRIMARY,netid_badge_ndx | PRIMARY| 12 | const |1 | | | 1 | PRIMARY| na| const | PRIMARY | PRIMARY| 4 | const |1 | Using index | | 1 | PRIMARY| a | ref| badge_ndx,role_ndx | badge_ndx | 4 | const |1 | | | 1 | PRIMARY| r | eq_ref | PRIMARY | PRIMARY| 2 | nams.a.role|1 | | | 1 | PRIMARY| t | ref| badge_ndx | badge_ndx | 4 | const | 12 | Using where | | 1 | PRIMARY| c | ref| curuse_ndx | curuse_ndx | 4 | const deptid |1 | Using where | | 6 | DEPENDENT SUBQUERY | f | ref| badge_ndx,role_ndx,college_ndx | badge_ndx | 4 | const | 1 | Using where; Using temporary; Using filesort | | 6 | DEPENDENT SUBQUERY | r | eq_ref | PRIMARY | PRIMARY| 2 | nams.f.role|1 | | | 6 | DEPENDENT SUBQUERY | d | eq_ref | PRIMARY | PRIMARY| 2 | nams.f.college |1 | Using where; Using index | | 5 | DEPENDENT SUBQUERY | f | ref| badge_ndx,role_ndx,dept_ndx | badge_ndx | 4 | const |1 | Using where; Using temporary; Using filesort | | 5 | DEPENDENT SUBQUERY | r | eq_ref | PRIMARY | PRIMARY| 2 | nams.f.role|1 | | | 5 | DEPENDENT SUBQUERY | d | eq_ref | PRIMARY,dabbr_ndx | PRIMARY| 4 | 4 | const |1 | | | 2 | DEPENDENT SUBQUERY | names | const | PRIMARY | PRIMARY| 4 | const |1 | | +++---++++-++--+--+ 20 rows in set (0.01 sec) - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDgdwkikQgpVn8xrARAky9AJ4lcBoVIsqkU2nBpca6fAZZsl5dvgCfR3H5 7ed5A7RcOLrcm2XxQh/zSD8= =+Xos -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SSH tunnel for Mysql
Both Linux computers. On 11/21/05, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. You have not specified what operating system you're using. MySQL manual has some notes for Windows: http://dev.mysql.com/doc/refman/5.0/en/windows-and-ssh.html Jerry Swanson wrote: How to create ssh tunnel for Mysql? TH -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com http://www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
re: how am I missing this key
James Black [EMAIL PROTECTED] wrote on 11/21/2005 09:39:32 AM: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Using mysql 5.0.15 my query gets a result of Unknown column 'i.rid' in 'on clause' There is actually a complete query, but this is a snippet, as the selects with subqueries I don't believe will cause the problem. When I changed c.rid=i.rid to c.rid=c.rid, the query executes. It is incorrect, but at least it runs, so, it appears that that the last join is where the problem is. Any suggestions as to what might be the cause? Thanx. FROM items i, nams.netids n INNER JOIN nams.names AS na ON n.badge=na.badge INNER JOIN nams.affiliations AS a ON a.badge=na.badge INNER JOIN nams.roles AS r ON a.role=r.code LEFT OUTER JOIN nams.regterms AS t ON (na.badge=t.badge) LEFT OUTER JOIN curuse AS c ON (c.rid=i.rid) WHERE i.rid=12415 AND n.netid='alb' AND i.status='A' AND c.badge IS NULL LIMIT 1; snip - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDgdwkikQgpVn8xrARAky9AJ4lcBoVIsqkU2nBpca6fAZZsl5dvgCfR3H5 7ed5A7RcOLrcm2XxQh/zSD8= =+Xos -END PGP SIGNATURE- The query engine became more ANSI compliant with v5.0.12. Beginning with that version the prioritization of your comma-declared CROSS JOIN (FROM items i, nams.netids n) changed so that the explicit JOINS happen first. That means that when you say LEFT OUTER JOIN curuse AS c ON (c.rid=i.rid) the table `items` has not been added to the list of tables participating in this query yet so it's columns cannot be used as part of an ON clause. Two ways around it: 1) use parentheses to modify the JOIN priorities so that the CROSS JOIN happens first FROM (items i, nams.netids n) INNER JOIN nams.names AS na ON n.badge=na.badge INNER JOIN nams.affiliations AS a ON a.badge=na.badge INNER JOIN nams.roles AS r ON a.role=r.code 2) use an explicit INNER JOIN or CROSS JOIN command (** my recommendation). FROM items i CROSS JOIN nams.netids n INNER JOIN nams.names AS na ON n.badge=na.badge INNER JOIN nams.affiliations AS a ON a.badge=na.badge INNER JOIN nams.roles AS r ON a.role=r.code A detailed explanation of this behavior is in THE FINE MANUAL: http://dev.mysql.com/doc/refman/5.0/en/join.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Index of JOINed table?
- Original Message - From: Jonathan Mangin [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, November 21, 2005 8:48 AM Subject: Index of JOINed table? I have a simple query, (part of a union). calendar is a table of only dates, `date` as PK. sales has a multi-col index on (date, uid). (select calendar.date as date, time_format(time(sales.date),'%H:%i') as time, from calendar left join sales on date(sales.date) = calendar.date and sales.uid = 'george' where calendar.date between '2005-08-01' and '2005-08-14') First, I don't quite understand joins(!) on constants (sales.uid = 'george'). Is this proper, or should uid be included in where clause? Second, explain shows no possible keys for sales. id select_type table typepossible_keys key key_len ref rows Extra 1 PRIMARY calendarrange PRIMARY PRIMARY 3 NULL11 Using where; Using index 1 PRIMARY sales ALL NULLNULLNULLNULL23 I guess I found the problem... on date(sales.date) = calendar.date If I create separate date and time columns it lists, and uses, possible_keys. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
dump to more than 1 file
is it possible to do a mysql dump to more than 1 file? We will shortly be needing to dump a db that will be in excess of 50gb so will encounter file size issues This is on 4.1.x and rhel 4 thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index of JOINed table?
Jonathan Mangin wrote: I have a simple query, (part of a union). calendar is a table of only dates, `date` as PK. sales has a multi-col index on (date, uid). (select calendar.date as date, time_format(time(sales.date),'%H:%i') as time, from calendar left join sales on date(sales.date) = calendar.date and sales.uid = 'george' where calendar.date between '2005-08-01' and '2005-08-14') First, I don't quite understand joins(!) on constants (sales.uid = 'george'). Is this proper, or should uid be included in where clause? This query makes perfect sense. For each day in the first two weeks of August (matching rows in calendar), you want a report of sales to/by george. The following simpler query does most of that job: SELECT calendar.date AS date, time_format(time(sales.date),'%H:%i') as time, FROM calendar JOIN sales ON date(sales.date) = calendar.date WHERE calendar.date BETWEEN '2005-08-01' AND '2005-08-14' AND sales.uid = 'george' but it doesn't report on days where george has no sales. You fix that by changing to a LEFT JOIN, which gives one row of output for each calendar day that has no match in sales with NULLs in the sales columns. The key here is that you must move the sales.uid = 'george' to the ON clause, because it is part of the requirement for determing matching rows in sales. If you leave it in the WHERE clause, it will undo the effect of the LEFT JOIN, because the WHERE clause conditions on the table on the right, sales, happen after matching rows are found or NULL rows are made. You see? If george has no sales on a particular calendar day, you get a column with NULLs on the sales side if both conditions are in the ON clause, but if the 'george' requirement is in the WHERE clause, days with no 'george' sales will be filtered out. Hence the query you've given above. Second, explain shows no possible keys for sales. id select_type table typepossible_keys key key_len ref rows Extra 1 PRIMARY calendarrange PRIMARY PRIMARY 3 NULL 11 Using where; Using index 1 PRIMARY sales ALL NULLNULLNULLNULL23 Some versions of my query showed possible_keys, but I can't seem to find what I did and I'm frustrated. What am I doing wrong here? The problem is this date(sales.date) = calendar.date. You've run sales.date through a functon, so the index on sales.date cannot be used. To use the index, you must rewrite the query so the actual column value is used. An index on sales.uid could have been used, but you don't have one (sales.uid is part of an index, but that part can't be used if the leftmost ppart of the index, sales.date, is not being used). You haven't told us what kind of column sales.date is, so it's hard to say what to do. Ideally, if you are going to be using it to join to calendar, it should be a DATE. In that case, you can simply do SELECT calendar.date AS date, time_format(time(sales.date),'%H:%i') as time, FROM calendar LEFT JOIN sales ON sales.date = calendar.date AND sales.uid = 'george' WHERE calendar.date BETWEEN '2005-08-01' AND '2005-08-14' I'm guessing that sales.date is not a DATE, though. If it's a DATETIME or a TIMESTAMP, one possibility would be to replace it with two columns, a DATE and a TIME. That would help this query, but may not work well with the rest of your design. You'll have to decide. If it doesn't make sense to change sales.date, there are two things you can do that should help. 1) Add an index on sales.uid. I think the best way to do that would be to replace your multi-column index on (date, uid) with one on (uid, date). That would help this query, and would help any query which selects all dates for a given uid. I expect you also want reports for all sales in a given date range for all uids. In that case, you would also want a single-column index on date. So, I'm suggesting something like ALTER TABLE sales DROP INDEX name_of_the_index_on_date_and_uid, ADD INDEX (uid, date), ADD INDEX (date); 2) Add a condition to your query to help mysql use the index on sales.date. The trick is to add it to the ON clause, because the WHERE clause is too late to pre-select rows on the right. So SELECT calendar.date AS date, time_format(time(sales.date),'%H:%i') as time, FROM calendar LEFT JOIN sales ON sales.date = calendar.date AND sales.uid = 'george' AND sales.date BETWEEN '2005-08-01' AND '2005-08-14 23:59:59' WHERE calendar.date BETWEEN '2005-08-01' and '2005-08-14' This would work best with a multi-column index on (uid, date). Because the sales.date restriction is a range, it would only use your current multi-column index on (date, uid) as if it were a single-column index on date. Thanks. Michael -- MySQL General Mailing List For list archives:
RE: dump to more than 1 file
Mysqldump has where condition, you may have to segment your data and dump it in diferents files Mysqldump --where='date between \'dateStart\' and \'dateFinish\' ' (for example) See full documentation at: http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Regards!! Edwin Cruz -Mensaje original- De: Tom Brown [mailto:[EMAIL PROTECTED] Enviado el: Lunes, 21 de Noviembre de 2005 09:57 a.m. Para: mysql@lists.mysql.com Asunto: dump to more than 1 file is it possible to do a mysql dump to more than 1 file? We will shortly be needing to dump a db that will be in excess of 50gb so will encounter file size issues This is on 4.1.x and rhel 4 thanks -- 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: Installation help ...!
Hello. I am a complete newbie here and to the world of Linux MySQL. I am trying to create a LAMP server I have installed RedHat Enterprise Linux AS 4 - update 2 on my server and it seems to work ok. I downloaded MySQL 5.0 and now want to install that I read somewhere that I need to remove rpm files from the server ... I think you can do this via add/remove programs in the RedHat Gui How do I install MySQL on the server. I have tried quite a few times but I can never seem to get access to the MYSQL prompt ... I get some error about a socket connection . Is there any documentation available for installing MySQL 5.0 on to redhat ..? please outline HOW you installed many times - then show exect error messages thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to adjust the row id after modifying the tabel
Xiaobo Chen wrote:
Hi, all
I follow the example:
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;
The thing is that after delete one of them like this:
delete from animals where id=4;
how can I have the 'id' to be continuous. Now it will be like:
1
2
3
5
6
Or shall I create the table differently at first? If so, how?
Thanks for your help in advance.
Xiaobo
The best answer is that you should not change id values. The id is the key used
to find a particular value. It should never change. Think of it like this: If
a house down the street from you were demolished, should your street address
change to avoid gaps in street numbers? Imagine the headaches that would cause.
Now, if you need numbered lines of output without gaps in the line-numbering
sequence, you can handle that in your app, or you can do it in mysql like this:
mysql SET @i = 0;
Query OK, 0 rows affected (0.00 sec)
mysql SELECT @i:= @i + 1 AS row, animals.* FROM animals;
+--++-+
| row | id | name|
+--++-+
|1 | 1 | dog |
|2 | 2 | cat |
|3 | 3 | penguin |
|4 | 5 | whale |
|5 | 6 | ostrich |
+--++-+
5 rows in set (0.00 sec)
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: trouble with deleting a user
ali asghar torabi parizy wrote: hi to all i have a problem when i trying to delet a user by drop user directive *** drop user atp; Can't drop one or more of the requested users *** i add this user by crant: *** GRANT ALL PRIVILEGES ON *.* TO 'atp'@'%' IDENTIFIED BY 'nowayforyou' WITH GRANT OPTION; *** A user in mysql is the combination of username and hostname, so the user you created is 'atp'@'%'. Hence, you need to DROP USER 'atp'@'%'; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: dump to more than 1 file
The output of mysqldump is standard output, not a file. You can pipe it into another program, or redirect the output to a file, but mysqldump does not make a file. Therefore, there is no option in mysqldump to make more than 1 file. How is your database stored on disk? The documentation Edwin pointed to shows you how to dump individual tables, so dumping each table separately might work. Also, you can pipe your output into a compression utility (bzip2 compresses text smaller than gzip). -Sheeri On 11/21/05, ISC Edwin Cruz [EMAIL PROTECTED] wrote: Mysqldump has where condition, you may have to segment your data and dump it in diferents files Mysqldump --where='date between \'dateStart\' and \'dateFinish\' ' (for example) See full documentation at: http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Regards!! Edwin Cruz -Mensaje original- De: Tom Brown [mailto:[EMAIL PROTECTED] Enviado el: Lunes, 21 de Noviembre de 2005 09:57 a.m. Para: mysql@lists.mysql.com Asunto: dump to more than 1 file is it possible to do a mysql dump to more than 1 file? We will shortly be needing to dump a db that will be in excess of 50gb so will encounter file size issues This is on 4.1.x and rhel 4 thanks -- 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: mysqld crash with archive engine 2gb
David - Sorry for the delayed response. ulimit is unlimited. I am using the RPMs for RH EL3 downloaded from a mirror so I would assumed they have been built to allow 2GB archives. I am most likely going a different route at this point as my query response time is much too slow even with a 1GB archive table. Using a separate MyISAM table for each day of data (~ 132mb/3.2m rows) gives me the response time I need -- plus there are no scenarios in which I will be performing cross-day queries (I would guess I could use the merge table if necessary). Thanks again, Marc --- Logan, David (SST - Adelaide) [EMAIL PROTECTED] wrote: Hi Marc, I would be a bit suspicious of the version of zlib or something similar. If it has only been compiled with a 32bit compiler, this could be causing an artificial limit of 2Gb on a pointer. The ARCHIVE engine uses the zlib for its compression, that comes with mysql. I am presuming if it has been compiled in 32bit mode that a pointer or two maybe overflowing. These are just thoughts as I can't really find any reason in your logs as to why this should be happening. Do you have a ulimit set for the user? This could be constricting your file growth to 2Gb. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: trouble with deleting a user
Hello. What version of MySQL are you using? In MySQL 5.0.15 it works fine: mysql GRANT ALL PRIVILEGES ON *.* TO 'atp'@'%' IDENTIFIED BY 'nowayforyou' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) mysql drop user atp; Query OK, 0 rows affected (0.00 sec) See: http://dev.mysql.com/doc/refman/5.0/en/drop-user.html ali asghar torabi parizy wrote: hi to all i have a problem when i trying to delet a user by drop user directive *** drop user atp; Can't drop one or more of the requested users *** i add this user by crant: *** GRANT ALL PRIVILEGES ON *.* TO 'atp'@'%' IDENTIFIED BY 'nowayforyou' WITH GRANT OPTION; *** - Yahoo! FareChase - Search multiple travel sites in one click. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrade RH 7.3
Hello. we would like to upgrade to 4.1.15, which I believe is the current stable release, 5.0.15 is stable as well, and, in my opinion, it is better to upgrade to it (though 4.1.15 of course). Bug database might be helpful to find some RH related issues: http://bugs.mysql.com Mike Blezien wrote: Hello, we are currently running std. MySQL 4.0.26 on an old production server using RH 7.3, using the std rpms downloaded from: http://sunsite.informatik.rwth-aachen.de/mysql/Downloads/MySQL-4.0/ we would like to upgrade to 4.1.15, which I believe is the current stable release, and was wondering if there are any problems encountered with RH 7.3 and MySQL 4.1+ that we should be aware prior to upgrading. Which rpms should we use to do the upgrade? we run the std MySQL server. TIA, -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_fix_privileges
Hello. mysql_fix_privileges usually modifies grant tables to make them compatible with new features of a new version. From my experience MySQL will perfectly run without this. If I do a dump of mysql database will it be enough?meaning if there is any problem during the execution of the script will it be enough to reload the mysql database? Yes, if you want - make copy of the database with mysqlhotcopy. See: http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html Last, one of the server on which I have no control (4.0.9-gamma) but that I have to use would need the update of the privileges tables as well: what are the consequences/risks in not running it? Check the 'Upgrading from Version 4.0 to 4.1' section of the manual: http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-4-0.html Don't forget about old passwords. mel list_php wrote: hi, i just inherited a mysql server 4.1 and need to run mysql_fix_privileges on it. However one of the databases hosted on it is production and I'd rather avoid it to be down. I ran the script on an 4.0 server test that I had installed and which was running when I ran the script. I had no problem with it, and the server is still up without downtime. I hope everything will be fine as well for the production server, but in case of how can I back up my data to revert if any problem? If I do a dump of mysql database will it be enough?meaning if there is any problem during the execution of the script will it be enough to reload the mysql database? I also had no problem with the old_passwords on my test db, shall I expect any problem with the clients who would like to connect after I run the script? Last, one of the server on which I have no control (4.0.9-gamma) but that I have to use would need the update of the privileges tables as well: what are the consequences/risks in not running it? Thanks for your help. melanie _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Perfomance / Optimize ( Full Table Scans ) and Help with my.cnf
I suggest checking out your slow query logs: http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html the queries are logged, so going into a client and running an explain on the slow queries should help you out. -Sheeri On 11/21/05, ESV Media GmbH [EMAIL PROTECTED] wrote: Hey, we´ve a site ( PHP ) where several 1.000 are online at the same time. They´re running many sql statements. Is there a way to find out which statements take a full table scan to optimize them ? We´ve a cpu percentage of nearly 50 % with only 30 Members online. And i´ve seen that there are some full table scans. Where can i investigate them ? And i need some help with configuring the my.cnf. Below you´ll find our one. Which options should i optimize ? We´ve much free memory but our cpu is working to half of the full capacity... uname -a : Linux m30s06db.ispgateway.de 2.4.29-grsec #10 SMP Mon Jul 4 14:26:46 CEST 2005 i686 Intel(R) Pentium(R) 4 CPU 3.00GHz GenuineIntel GNU/Linux And thats the my.cnf : # Example MySQL config file for medium systems. # # This is for a system with little memory (32M - 64M) where MySQL plays # an important part, or systems up to 128M where MySQL is used together with # other programs (such as a web server) # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /kunden/106120_40670/ms_appl/mysql_4.1.10a/var) or # ~/.my.cnf to set user-specific options. # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the --help option. # The following options will be passed to all MySQL clients [client] #password = your_password port = 3307 socket = /tmp/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3307 socket = /tmp/mysql.sock skip-locking key_buffer = 64M max_allowed_packet = 1M table_cache = 5M sort_buffer_size = 1M net_buffer_length = 8K read_buffer_size = 1M read_rnd_buffer_size = 512K myisam_sort_buffer_size = 16M record_buffer=1M log-slow-queries long_query_time = 3 # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the enable-named-pipe option) will render mysqld useless! # # #skip-networking # Replication Master Server (default) # binary logging is required for replication log-bin # required unique id between 1 and 232 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - # the syntax is: # # CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, # MASTER_USER=user, MASTER_PASSWORD=password ; # # where you replace host, user, password by quoted strings and # port by the master's port number (3306 by default). # # Example: # # CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, # MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then # start replication for the first time (even unsuccessfully, for example # if you mistyped the password in master-password and the slave fails to # connect), the slave will create a master.info file, and any later # change in this file to the variables' values below will be ignored and # overridden by the content of the master.info file, unless you shutdown # the slave server, delete master.info and restart the slaver server. # For that reason, you may want to leave the lines below untouched # (commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 232 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication master for this slave - required #master-host = hostname # # The username the slave will use for authentication when connecting # to the master - required #master-user = username # # The password the slave will authenticate with when connecting to # the master - required #master-password = password # # The port the master is listening on. # optional - defaults to 3306 #master-port = port # # binary logging - not required for slaves, but recommended #log-bin # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using BDB tables
MySQL 5.0.16 has been released
Hi, MySQL 5.0.16, a new GA/production version of the popular Open Source Database Management System, has been released. The Community Edition is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is a bugfix release for the current production version. If you are not aware of the changes introduced with version 5.0, please see the high-level description at http://dev.mysql.com/doc/mysql/en/MySQL_5.0_Nutshell.html and pay special attention to the manual, section 2.10.2, Upgrading from Version 4.1 to 5.0. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. News from the ChangeLog: Functionality added or changed: * The InnoDB, NDB, BDB, and ARCHIVE storage engines now support spatial columns. See Chapter 16, Spatial Extensions in MySQL. * You must now declare a prefix for an index on any column of any Geometry class, the only exception being when the column is a POINT. (Bug #12267 (http://bugs.mysql.com/12267)) * Added a --hexdump option to mysqlbinlog that displays a hex dump of the log in comments. This output can be helpful for replication debugging. * MySQL 5.0 now supports character set conversion for seven additional cp950 characters into the big5 character set: 0xF9D6, 0xF9D7, 0xF9D8, 0xF9D9, 0xF9DA, 0xF9DB, and 0xF9DC. Note: If you move data containing these additional characters to an older MySQL installation which does not support them, you may encounter errors. (Bug #12476 (http://bugs.mysql.com/12476)) Packaging changes: * The Solaris packages (pkgadd format) has changed. In earlier releases, running pkgadd simply unpacked the distribution into /usr/local/mysql-product-version/, i.e. nothing more than unpacking a TAR package would do. The new packages - install into /opt/mysql/mysql/ (vendor/product) - register the package with the name mysql - add and set group/user - initiate/update the data directory /var/lib/mysql, run mysql_install_db to initialize system tables - install a startup script as /etc/init.d/mysql - add a soft link from /usr/local/mysql to /opt/mysql/mysql (for compatibility and to enable the binaries to find character set definitions etc) - add soft links in /usr/bin, /usr/sbin, /usr/lib and other locations to files in /opt/mysql/mysql/ - stop the server when the package is removed with pkgrm Note that the server is *not* started as part of the package install, and that the directory /var/lib/mysql is not altered during package removal using pkgrm. * Please see the additional notes in the 5.0.15 announcement mail, if you did not already do so earlier. The mail is archived here: http://lists.mysql.com/mysql/190752 Bugs fixed: * ORDER BY DESC within the GROUP_CONCAT() function was not honored when used in a view. (Bug #14466 (http://bugs.mysql.com/14466)) * A comparison with an invalid date (such as WHERE col_name '2005-09-31') caused any index on col_name not to be used and a string comparison for each row, resulting in slow performance. (Bug #14093 (http://bugs.mysql.com/14093)) * Within stored routines, REPLACE() could return an empty string (rather than the original string) when no replacement was done, and IFNULL() could return garbage results. (Bug #13941 (http://bugs.mysql.com/13941)) * Inserts of too-large DECIMAL values were handled inconsistently (sometimes set to the maximum DECIMAL value, sometimes set to 0). (Bug #13573 (http://bugs.mysql.com/13573)) * Executing REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE on a view for which an underlying table had been dropped caused a server crash. (Bug #14540 (http://bugs.mysql.com/14540)) * A prepared statement that selected from a view processed using the merge algorithm could crash on the second execution. (Bug #14026 (http://bugs.mysql.com/14026)) * Deletes from a CSV table could cause table corruption. (Bug #14672 (http://bugs.mysql.com/14672)) * An update of a CSV table could cause a server crash. (Bug #13894(http://bugs.mysql.com/13894)) * For queries with nested outer joins, the optimizer could choose join orders that query execution could not handle. The fix is that now the optimizer avoids choosing such join orders. (Bug #13126 (http://bugs.mysql.com/13126)) * Starting mysqld with the --skip-innodb and --default-storage-engine=innodb (or --default-table-type=innodb caused a server crash. (Bug #9815 (http://bugs.mysql.com/9815), re-fix of bug from 5.0.5) * mysqlmanager did not start up correctly on Windows 2003. (Bug #14537 (http://bugs.mysql.com/14537)) * The parser did not correctly recognize wildcards in the host part
Can't materialize a view -- bug?
I'm doing the equivalent of a materialized view in MySQL 5.0.15 and I've hit a strange error. Here's the scenario. CREATE OR REPLACE VIEW x_view AS SELECT [select statement, blah, blah, blah]; DROP TABLE IF EXISTS x_mview; CREATE TABLE x_mview SELECT * FROM x_view; That was all working fine until I created a function that I use inside of the view. Now the last statement, the one that would materialize the view, returns a table locking error! ERROR 1100 (HY000): Table 'x_mview' was not locked with LOCK TABLES It's difficult to lock a table that doesn't exist yet... SELECT * FROM x_view works fine. The function is a simple bit of logic (it tests a count, moving average and standard deviation to see if the count is more or less than two standard deviations from the mean): CREATE FUNCTION get_signal (cnt MEDIUMINT(8), ma MEDIUMINT(8), stdv MEDIUMINT(8)) RETURNS TINYINT(1) DETERMINISTIC RETURN IF (ma 9 AND stdv 0 AND (cnt = ma + (2 * stdv)) OR cnt = ma - (2 * stdv), IF (cnt = ma + (2 * stdv), 1, -1), 0); If this is a bug, I'll be happy to file a report... but I'd really like a solution that will let me use the function. I hit a problem using functions in stored procedures, too, and I'm wondering if these are related. Nick -- Nick Arnett [EMAIL PROTECTED] Messages: 408-904-7198
Re: moving the datadir
Thanks Gleb. I get the exact same results when I connect with: # mysql -u=root --- Maybe a different issue but, for some reason I can't connect with # mysql --user=root ^^^ ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) but I can with: # mysql -user=root ^^^ MySQL v4.1.10a-standard. Thanks for your time. I'm only a sporadic user of MySQL - so some of this maybe a case of RTM. -RO On 11/19/05, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. With SHOW DATABASES you see only those databases for which you have some kind of privilege, unless you have the global SHOW DATABASES privilege. Connect to mysql under the root user. Rajesh Olafson [EMAIL PROTECTED] wrote: Hi, I'm on Solaris 9 with mysql 4.1. I've moved the datadir to a new location, and edited the mysql start script directly so that it now has the line: datadir=/export/www1/data The server restarts no problem, and my phpBB web app can talk to it no problem. However when I try to connect to it using the /usr/local/mysql/bin/mysql client I get: [EMAIL PROTECTED]:/usr/local/mysql: ./bin/mysql --user=mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 4.1.10a-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show databases; +--+ | Database | +--+ | test | +--+ 1 row in set (0.00 sec) mysql -- I'm not sure where it's getting the 'test' db from, but it's not showing the phpBB database - or other databases I have. I've tried creating a /etc/my.cnf file - but I can't get it to work. Thoughts? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com http://www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
UDF
Hello, I´m using MySQL-4.1.7 and I need to create a UDF. For all I have understood, I need to create it in C/C++ sintaxe. My doubt is: Is it the only way to have UDF server-side in MySQL(4.1.x)? Do I need to learn C/C++ to have a UDF? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UPDATE and INDEX updates
Hi I was wondering how the index process worked internally for UPDATE statements. If I was to set a value for a column with the UPDATE statement but the value was the same, would MySQL re-work the index?? I can check for data change for each column inside of my code before UPDATE but want to make sure I need to before going ahead. Mike
UNIQUE constraint, proper use
I have this SQL statement: CREATE TABLE rooms ( idSERIAL, room_name TEXT UNIQUE, location TEXT, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, last_user_updatedTEXT, PRIMARY KEY(id) ); When I run this I get the following error: ERROR 1170 (42000): BLOB/TEXT column 'room_name' used in key specification without a key length I'm more used to PostgreSQL and when I run this command there, I don't get this error message. I recognized that by changing room_name to a varchar type, I won't get the error message. Also, the PostgreSQL mailing lists had a special group from SQL-related issue, but I didn't see one of these types of lists in the lists of groups for MySQL community so I'm sorry if I irritate anyone by posting to the wrong group. Thanks. Ferindo Middleton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UNIQUE constraint, proper use
At 22:49 -0500 11/21/05, Ferindo Middleton Jr wrote: I have this SQL statement: CREATE TABLE rooms ( idSERIAL, room_name TEXT UNIQUE, location TEXT, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, last_user_updatedTEXT, PRIMARY KEY(id) ); When I run this I get the following error: ERROR 1170 (42000): BLOB/TEXT column 'room_name' used in key specification without a key length I'm more used to PostgreSQL and when I run this command there, I don't get this error message. I recognized that by changing room_name to a varchar type, I won't get the error message. Also, the PostgreSQL mailing lists had a special group from SQL-related issue, but I didn't see one of these types of lists in the lists of groups for MySQL community so I'm sorry if I irritate anyone by posting to the wrong group. Thanks. What is your question? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
