Re: InnoDB how to.
Hi Can you through us more lights on error ? where does this occur ? -bash-2.05b$ perror 10 30 OS error code 10: No child processes OS error code 30: Read-only file system -bash-2.05b$ -Praj On Wed, 5 Apr 2006 08:48:08 +0700 Truong Tan Son [EMAIL PROTECTED] wrote: [S] Dhandapani wrote: mysql show global variables like '%innodb%'; +-++ | Variable_name | Value | +-++ | have_innodb | YES OK, all thing is let default setting. mysql *|SET AUTOCOMMIT=0;|* Query OK, 0 rows affected (0.00 sec) mysql *|INSERT INTO CUSTOMER VALUES (15, 'John');|* Query OK, 1 row affected (0.00 sec) ERROR 1030 (HY000): Got error -1 from storage engine What is error ? I follow Document: http://dev.mysql.com/doc/refman/5.0/en/innodb-transactions-with-different-apis.html The error is same. Please teach me. Best regards, -- mysql *|ROLLBACK;|* Query OK, 0 rows affected (0.00 sec) mysql *|SELECT * FROM CUSTOMER;|* +--++ | A| B | +--++ || | +--++ the inset stattement has been rollbacked. Reference http://dev.mysql.com/doc/refman/5.0/en/innodb-transactions-with-different-apis.html Regards, Dhandapani Dba Sify Limited. Truong Tan Son wrote: Dear Sir, I install MySQL 5.0.18 on RH EL4, but do not know how to use InnoDB for ROLLBACK. ./configure --prefix=/usr/local/mysql \ --exec-prefix=/usr/local/mysql \ --with-innodb make make install **/etc/my.cnf: # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /usr/local/mysql/var/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/var/ innodb_log_arch_dir = /usr/local/mysql/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 innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 with setting above, I could not use InnoDB. How to know InnoDB is installed, and start it ? Thanks you and best regards, -- ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] www.sify.com - your homepage on the internet for news, sports, finance, astrology, movies, entertainment, food, languages etc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB how to.
Prasanna Raj [EMAIL PROTECTED] wrote: Can you through us more lights on error ? where does this occur ? **ERROR: InnoDB: A new raw disk partition was initialized or InnoDB: innodb_force_recovery is on: we do not allow InnoDB: database modifications by the user. Shut down InnoDB: mysqld and edit my.cnf so that newraw is replaced InnoDB: with raw, and innodb_force_... is removed. Please teach me raw. **/etc/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 /usr/local/mysql/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 = 3306 socket = /tmp/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] init_connect='SET AUTOCOMMIT=0' innodb_force_recovery = 3 port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M # 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=mysql-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=mysql-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 = /usr/local/mysql/var/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/var/ innodb_log_arch_dir = /usr/local/mysql/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 innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout
Re: InnoDB how to.
Gotit Thnx ;) You have set innodb_force_recovery to 3 which means , do not run transaction rollbacks after recovery. Just try removing the innodb_force_recovery from my.cnf file or change the value More info : http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html --Praj On Wed, 5 Apr 2006 14:02:06 +0700 Truong Tan Son [EMAIL PROTECTED] wrote: Prasanna Raj [EMAIL PROTECTED] wrote: Can you through us more lights on error ? where does this occur ? **ERROR: InnoDB: A new raw disk partition was initialized or InnoDB: innodb_force_recovery is on: we do not allow InnoDB: database modifications by the user. Shut down InnoDB: mysqld and edit my.cnf so that newraw is replaced InnoDB: with raw, and innodb_force_... is removed. Please teach me raw. **/etc/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 /usr/local/mysql/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 = 3306 socket = /tmp/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] init_connect='SET AUTOCOMMIT=0' innodb_force_recovery = 3 port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M # 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=mysql-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=mysql-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 =
(WORKED) Re: InnoDB how to.
Prasanna Raj [EMAIL PROTECTED] wrote: Gotit Thnx ;) You have set innodb_force_recovery to 3 which means , do not run transaction rollbacks after recovery. http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html 1 (SRV_FORCE_IGNORE_CORRUPT) 2 (SRV_FORCE_NO_BACKGROUND) 3 (SRV_FORCE_NO_TRX_UNDO) 4 (SRV_FORCE_NO_IBUF_MERGE) 5 (SRV_FORCE_NO_UNDO_LOG_SCAN) 6 (SRV_FORCE_NO_LOG_REDO) I remove innodb_force_recovery from my.cnf file, it works very good. Thanks your valuable help. Just try removing the innodb_force_recovery from my.cnf file or change the value More info : http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html --Praj On Wed, 5 Apr 2006 14:02:06 +0700 Truong Tan Son [EMAIL PROTECTED] wrote: Prasanna Raj [EMAIL PROTECTED] wrote: Can you through us more lights on error ? where does this occur ? **ERROR: InnoDB: A new raw disk partition was initialized or InnoDB: innodb_force_recovery is on: we do not allow InnoDB: database modifications by the user. Shut down InnoDB: mysqld and edit my.cnf so that newraw is replaced InnoDB: with raw, and innodb_force_... is removed. Please teach me raw. **/etc/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 /usr/local/mysql/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 = 3306 socket = /tmp/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] init_connect='SET AUTOCOMMIT=0' innodb_force_recovery = 3 port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M # 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=mysql-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=mysql-bin # Point the following paths to different dedicated disks #tmpdir
Re: MySQL Error
john simms wrote: Hello MySQL! Hello User! At last! It has taken a couple of days - but, I have, at last, found you. *hides* haha, you think so ... You have invaded my computer without being requested, or invited. Did i? Man, my schedules are really weird nowadays. Whenever I try to access a local (to my home) message board, I get a black 'page' on my monitor screen with the above message (MySQL Error) on the top left of the black page. Local message board? Ethernet one or really local like 127.0.0.1? I guess the font color is white because otherwise it would be hard to read , er and it's not usually a fault of MySQL it also could be bad coded PHP/JSP whatever your board is written in causing that Error. What Error are you exactly getting? It is driving me NUTS!! I want it off. But how do I remove it? Tried a rubber? Advice and step-by-step guidance/instructions please? 1. Give us more info about the MySQL version, Type of Board and any other Info you can get helping find that Error causing Problem. 2. Adress us as Humans not as a software 3. Let somone else, who might have knowledge of coding, have a look at it. Greets Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Password expire?
NiCK Song wrote: Hi, experts How can I make mysql database users password with expire date? Does mysql can do it? -- NiCK Set a DATE field with the time of expire. Then do something like SELECT * FROM users WHERE datefield_of_insert CURDATE(); HTH Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow query log
Hello, Is there a way to enable the Slow Query Log on the fly without having to restart mysqld Regards, Marc.
Re: Slow query log
On Wed, 2006-04-05 at 11:38 +0200, Mechain Marc wrote: Is there a way to enable the Slow Query Log on the fly without having to restart mysqld No. Petr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Restoring a binary log ( mysqlbinlog )
Hi , Yesterday our main database was dropped by mystake. This has never happened before. Furtunately we had a daily backup performed with mysqldump at about 06:00 am that day. As the problem ocurred at about 12:00am we had to restore the transactions from the binary log ( we have several binary log files for each day ) For this pourpose we used mysqlbinlog in order to prepare a source file with the transactions to be restored, but as we needed to restore transactions for only one database, we used the option -d win mysqlbinlog like this mysqlbinlog -d hrdb --start-date-time=2006-04-04 06:00:00 src.sql after that we used the resulting file like this mysql -e source src.sql all the process took about 1 hour to complete and our data was perfectly restored. But examinig the source file src.sql we noted that there were some transactions pertaining other databases than hrdb which we think shouldn't be there because of the -d option used with mysqlbinlog. These transaction were table creation querys like CREATE TABLE intra.table1 AS SELECT * FROM table2 In this query table1 is located in database intra and table2 belongs to database hrdb since the creation of table1 occurs in database intra we thought this query shouldn't be in the source Am I right ? The problem with this is that the time for the whole restore operation would have been considerabily lower if all of these useless transactions were excluded ( this write operations were created as a result from executing reports not data modification ) thanks Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow query log
Hi, Thank you for your answer. But is there a chance to be able to do it one day? I think it could be a nice feature. Marc. -Message d'origine- De : Petr Chardin [mailto:[EMAIL PROTECTED] Envoyé : mercredi 5 avril 2006 13:06 À : Mechain Marc Cc : MySQL Objet : Re: Slow query log On Wed, 2006-04-05 at 11:38 +0200, Mechain Marc wrote: Is there a way to enable the Slow Query Log on the fly without having to restart mysqld No. Petr -- 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: Restoring a binary log ( mysqlbinlog )
Hi All Correct me if iam wrong You might have run the create table syntax from hrdb database .So it gives you the create table also when you do mysqlbinlog . CREATE TABLE intra.table1 AS SELECT * FROM NO DATABASE NAMEtable2 , which means you are under hrdb database. Else you can use below syntax which wont get loaded under hrdb CREATE TABLE intra.table1 AS SELECT * FROM hrdb.table2 ( OR ) use intra ; CREATE TABLE table1 AS SELECT * FROM hrdb.table2 --Praj On Wed, 05 Apr 2006 08:44:52 -0300 Mauricio Pellegrini [EMAIL PROTECTED] wrote: Hi , Yesterday our main database was dropped by mystake. This has never happened before. Furtunately we had a daily backup performed with mysqldump at about 06:00 am that day. As the problem ocurred at about 12:00am we had to restore the transactions from the binary log ( we have several binary log files for each day ) For this pourpose we used mysqlbinlog in order to prepare a source file with the transactions to be restored, but as we needed to restore transactions for only one database, we used the option -d win mysqlbinlog like this mysqlbinlog -d hrdb --start-date-time=2006-04-04 06:00:00 src.sql after that we used the resulting file like this mysql -e source src.sql all the process took about 1 hour to complete and our data was perfectly restored. But examinig the source file src.sql we noted that there were some transactions pertaining other databases than hrdb which we think shouldn't be there because of the -d option used with mysqlbinlog. These transaction were table creation querys like CREATE TABLE intra.table1 AS SELECT * FROM table2 In this query table1 is located in database intra and table2 belongs to database hrdb since the creation of table1 occurs in database intra we thought this query shouldn't be in the source Am I right ? The problem with this is that the time for the whole restore operation would have been considerabily lower if all of these useless transactions were excluded ( this write operations were created as a result from executing reports not data modification ) thanks Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow query log
Mechain Marc wrote: Hi, Thank you for your answer. But is there a chance to be able to do it one day? I think it could be a nice feature. Marc. That should be asked to one of the devs. Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restoring a binary log ( mysqlbinlog )
Hi, please see comments below.. thank you Mauricio On Wed, 2006-04-05 at 09:02, Prasanna Raj wrote: Hi All Correct me if iam wrong You might have run the create table syntax from hrdb database .So it gives you the create table also when you do mysqlbinlog . CREATE TABLE intra.table1 AS SELECT * FROM NO DATABASE NAMEtable2 , which means you are under hrdb database. Thast exactly the situation. Else you can use below syntax which wont get loaded under hrdb CREATE TABLE intra.table1 AS SELECT * FROM hrdb.table2 ( OR ) use intra ; CREATE TABLE table1 AS SELECT * FROM hrdb.table2 Would this prevent this kind of querys from appearing in the source ? --Praj On Wed, 05 Apr 2006 08:44:52 -0300 Mauricio Pellegrini [EMAIL PROTECTED] wrote: Hi , Yesterday our main database was dropped by mystake. This has never happened before. Furtunately we had a daily backup performed with mysqldump at about 06:00 am that day. As the problem ocurred at about 12:00am we had to restore the transactions from the binary log ( we have several binary log files for each day ) For this pourpose we used mysqlbinlog in order to prepare a source file with the transactions to be restored, but as we needed to restore transactions for only one database, we used the option -d win mysqlbinlog like this mysqlbinlog -d hrdb --start-date-time=2006-04-04 06:00:00 src.sql after that we used the resulting file like this mysql -e source src.sql all the process took about 1 hour to complete and our data was perfectly restored. But examinig the source file src.sql we noted that there were some transactions pertaining other databases than hrdb which we think shouldn't be there because of the -d option used with mysqlbinlog. These transaction were table creation querys like CREATE TABLE intra.table1 AS SELECT * FROM table2 In this query table1 is located in database intra and table2 belongs to database hrdb since the creation of table1 occurs in database intra we thought this query shouldn't be in the source Am I right ? The problem with this is that the time for the whole restore operation would have been considerabily lower if all of these useless transactions were excluded ( this write operations were created as a result from executing reports not data modification ) thanks Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow query log
2006/4/5, Mechain Marc [EMAIL PROTECTED]: Hi, Thank you for your answer. But is there a chance to be able to do it one day? I think it could be a nice feature. You still have the option to sponsor that feature ;-D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very long query for such a simple result
You must be able to run it as a single call, or as a single TRANSACTION?
What do you mean by single call? One PHP command? One command on
the commandline? one script run?
-Sheeri
On 4/4/06, Ed Reed [EMAIL PROTECTED] wrote:
Thanks for the quick reply Peter. Unfortunately, this is all legacy stuff
that I have to make work together and the problemreport table does not
reference the employeeid in the employees table. It was all create about ten
years ago and the data has just always been migrated to the db du jour. I'm
currently using MySQL 4.1x but most importantly I must be able to run the
entire query in a single call.
Thanks for the help.
Peter Brawley [EMAIL PROTECTED] 4/4/06 2:35:49 PM
Ed,
The big slowdown in your query is likely the join on
ProblemReports.Responsible = CONCAT(Employees.FirstName, '
',Employees.LastName))
Eek. Does the employees table not have a primary key, and does the
problemreports table not reference that key? If not, I would think a first
priority would be to fix that.
Meanwhile, you can simplify your monster query by writing the user's problem
priorities to a temp table, then doing a simple pivot on priority to generate
your sentence. Something like ...
DROP TEMPORARY TABLE IF EXISTS ProbSum;
CREATE TEMPORARY TABLE ProbSum
SELECT Priority
FROM ProblemReports, Employees
WHERE ProblemReports.Status='Open'
AND ProblemReports.Responsible=CONCAT(Employees.FirstName, '
',Employees.LastName)
AND Employees.DateTerminated IS NULL
AND Employees.UserName='User1'
SELECT COUNT(*) INTO @n FROM ProbSum;
SELECT
IF( @n = 0,
'',
CONCAT( 'You have',
@n,
'Problem Report',
IF(@n=1,'','s'),
': Priorities(High=',
SUM(IF(Priority='High',1,0)),
',Med=',
SUM(IF(Priority='Med',1,0)),
',Low=',
SUM(IF(Priority='Low' ,1,0)),
')'
)
)
FROM probsum;
DROP TEMPORARY TABLE probsum;
All this would be easier in a stored procedure, if you have MySql 5.
PB
-
Ed Reed wrote:
Can someone help me simplify this query please? It's meant to return a single
string result that looks something like this, You have 12 open Problem
Reports: Priorities(High=5, Med=6, Low=1) The relavent columns from the two
tables are Table: ProblemReportsFields: PRNo, Status, Priority, Responsible
Table: EmployeesFields: Firstname, Lastname, DateTerminated, UserName Here's
the query Select IF((@a:=(SELECT Count(PRNo) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1')))0, If (@a=1, ConCat('You have one open
Problem Report: Prioritiy = ', (Select Priority From ProblemReports,
Employees WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1'))), Concat('You have ', @a, ' open Problem
Reports: Priorities(', (Select ConCat('High=',Count(Priority)) From
ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='High')),', ',(Select
ConCat('Med=',Count(Priority)) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='Med')),', ',(Select
ConCat('Low=',Count(Priority)) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='Low')),')')),''); No virus found
in this incoming message.Checked by AVG Free Edition.Version: 7.1.385 / Virus
Database: 268.3.5/300 - Release Date: 4/3/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Password expire?
NiCK Song [EMAIL PROTECTED] wrote on 04/04/2006 11:05:57 PM: Hi, experts How can I make mysql database users password with expire date? Does mysql can do it? -- NiCK Sorry!! MySQL does not auto-expire any user accounts. You will need to script something to do that manually on a schedule you want to set. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: very long query for such a simple result
WOW!!! THAT WAS AWESOME!!!
Thanks a lot Peter. Ok, so what is SUM(1)? How is it able to do this? And where
can I learn more about it?
Thanks again.
Peter Brawley [EMAIL PROTECTED] 4/4/06 10:13:00 PM
Ed,e: Thanks for the quick reply Peter. Unfortunately, this is all legacy stuff
that I have to make work together and the problemreport table does not
reference the employeeid in the employees table. It was all create about ten
years ago and the data has just always been migrated to the db du jour. I'm
currently using MySQL 4.1x but most importantly I must be able to run the
entire query in a single call. Thanks for the help. OK, that's doable in a
subquery, and you can get the total from SUM(1), so something like ...
SELECT
IF( SUM(1) = 0,
'',
CONCAT( 'You have ',
SUM(1),
' Problem Report',
IF(SUM(1) = 1,'','s'),
': Priorities(High=',
SUM(IF(Priority='High',1,0)),
',Med=',
SUM(IF(Priority='Med',1,0)),
',Low=',
SUM(IF(Priority='Low' ,1,0)),
')'
)
)
FROM (
SELECT Priority
FROM ProblemReports, Employees
WHERE ProblemReports.Status='Open'
AND ProblemReports.Responsible=CONCAT(Employees.FirstName, '
',Employees.LastName)
AND Employees.DateTerminated IS NULL
AND Employees.UserName='User1'
) AS priorities;
PB
-
Peter Brawley [EMAIL PROTECTED] 4/4/06 2:35:49 PM Ed,The big
slowdown in your query is likely the join on ProblemReports.Responsible =
CONCAT(Employees.FirstName, ' ',Employees.LastName)) Eek. Does the employees
table not have a primary key, and does the problemreports table not reference
that key? If not, I would think a first priority would be to fix
that.Meanwhile, you can simplify your monster query by writing the user's
problem priorities to a temp table, then doing a simple pivot on priority to
generate your sentence. Something like ...DROP TEMPORARY TABLE IF EXISTS
ProbSum;CREATE TEMPORARY TABLE ProbSumSELECT Priority FROM ProblemReports,
Employees WHERE ProblemReports.Status='Open' AND
ProblemReports.Responsible=CONCAT(Employees.FirstName, ' ',Employees.LastName)
AND Employees.DateTerminated IS NULL AND Employees.UserName='User1'SELECT
COUNT(*) INTO @n FROM ProbSum;SELECT IF( @n = 0, '', CONCAT( 'You
have', @n, 'Problem Report',
IF(@n=1,'','s'), ': Priorities(High=',
SUM(IF(Priority='High',1,0)), ',Med=',
SUM(IF(Priority='Med',1,0)), ',Low=',
SUM(IF(Priority='Low' ,1,0)), ')'))FROM
probsum;DROP TEMPORARY TABLE probsum;All this would be easier in a stored
procedure, if you have MySql 5.PB -Ed Reed wrote: Can someone help me
simplify this query please? It's meant to return a single string result that
looks something like this, You have 12 open Problem Reports:
Priorities(High=5, Med=6, Low=1) The relavent columns from the two tables are
Table: ProblemReportsFields: PRNo, Status, Priority, Responsible Table:
EmployeesFields: Firstname, Lastname, DateTerminated, UserName Here's the query
Select IF((@a:=(SELECT Count(PRNo) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1')))0, If (@a=1, ConCat('You have one open Problem
Report: Prioritiy = ', (Select Priority From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1'))), Concat('You have ', @a, ' open Problem
Reports: Priorities(', (Select ConCat('High=',Count(Priority)) From
ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='High')),', ',(Select
ConCat('Med=',Count(Priority)) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='Med')),', ',(Select
ConCat('Low=',Count(Priority)) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='Low')),')')),''); No virus found
in this incoming message.Checked by AVG Free Edition.Version: 7.1.385 / Virus
Database: 268.3.5/300 - Release Date: 4/3/2006No virus found in this
incoming message.Checked by AVG
Re: very long query for such a simple result
Hi Ed,
Count(1) works just as well. Sum(1) just adds 1 for each row so it's
logically equivalent.
PB
-
Ed Reed wrote:
WOW!!! THAT WAS AWESOME!!!
Thanks a lot Peter. Ok, so what is SUM(1)? How is it able to do this? And where can I learn more about it?
Thanks again.
Peter Brawley [EMAIL PROTECTED] 4/4/06 10:13:00 PM
Ed,e: Thanks for the quick reply Peter. Unfortunately, this is all legacy stuff that I have to make work together and the problemreport table does not reference the employeeid in the employees table. It was all create about ten years ago and the data has just always been migrated to the db du jour. I'm currently using MySQL 4.1x but most importantly I must be able to run the entire query in a single call. Thanks for the help. OK, that's doable in a subquery, and you can get the total from SUM(1), so something like ...
SELECT
IF( SUM(1) = 0,
'',
CONCAT( 'You have ',
SUM(1),
' Problem Report',
IF(SUM(1) = 1,'','s'),
': Priorities(High=',
SUM(IF(Priority='High',1,0)),
',Med=',
SUM(IF(Priority='Med',1,0)),
',Low=',
SUM(IF(Priority='Low' ,1,0)),
')'
)
)
FROM (
SELECT Priority
FROM ProblemReports, Employees
WHERE ProblemReports.Status='Open'
AND ProblemReports.Responsible=CONCAT(Employees.FirstName, ' ',Employees.LastName)
AND Employees.DateTerminated IS NULL
AND Employees.UserName='User1'
) AS priorities;
PB
-
Peter Brawley [EMAIL PROTECTED] 4/4/06 2:35:49 PM Ed,The big slowdown in your query is likely the join on ProblemReports.Responsible = CONCAT(Employees.FirstName, ' ',Employees.LastName)) Eek. Does the employees table not have a primary key, and does the problemreports table not reference that key? If not, I would think a first priority would be to fix that.Meanwhile, you can simplify your monster query by writing the user's problem priorities to a temp table, then doing a simple pivot on priority to generate your sentence. Something like ...DROP TEMPORARY TABLE IF EXISTS ProbSum;CREATE TEMPORARY TABLE ProbSumSELECT Priority FROM ProblemReports, Employees WHERE ProblemReports.Status='Open' AND ProblemReports.Responsible=CONCAT(Employees.FirstName, ' ',Employees.LastName) AND Employees.DateTerminated IS NULL AND Employees.UserName='User1'SELECT COUNT(*) IN
TO @n FROM ProbSum;SELECT IF( @n = 0, '', CONCAT( 'You have', @n, 'Problem Report', IF(@n=1,'','s'), ': Priorities(High=', SUM(IF(Priority='High',1,0)), ',Med=', SUM(IF(Priority='Med',1,0)), ',Low=', SUM(IF(Priority='Low' ,1,0)), ')'))FROM probsum;DROP TEMPORARY TABLE probsum;All this would be easier in a stored procedure, if you have MySql 5.PB -Ed Reed wrote: Can someone help me simplify this query please? It's meant to return a single string result that looks something like this, "You have 12 open Problem Reports: Priorities(High=5, Med=6, Low=1)" The relavent columns from the two tables are Table: ProblemReportsFields: PRNo, Status, Priority, Responsible Table: EmployeesFields: Firstname, Lastname, DateTerminated, UserName Here's the query Select IF((@a:=(SELECT Count(PRNo) From ProblemReports, Employees WHER
E (ProblemReports.Status='Open') AND (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND (Employees.UserName='User1')))0, If (@a=1, ConCat('You have one open Problem Report: Prioritiy = ', (Select Priority From ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND (Employees.UserName='User1'))), Concat('You have ', @a, ' open Problem Reports: Priorities(', (Select ConCat('High=',Count(Priority)) From ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And (Priority='High')),', ',(Select ConCat('Med=',Count(Priority)) From ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND (ProblemRep
orts.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And (Priority='Med')),', ',(Select ConCat('Low=',Count(Priority)) From ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And (Priority='Low')),')')),''); No virus found in this incoming message.Checked by AVG Free
Re: Password expire?
On 4/5/06, NiCK Song [EMAIL PROTECTED] wrote: Hi, experts How can I make mysql database users password with expire date? Does mysql can do it? -- NiCK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] I guess you'll have to build it in your app. MySQL doesn't support anything like this (or at least I couldn't find it). I use a method like this: My server has TWO accounts, when the user logs on my app with its name/password, PHP connects using a read-only account to check if the name/pass is valid accourding to a table named users at the test database, and it also checks a datetime value named expire at the same table, if the password is no longer valid, PHP denies access, else it makes another connection, this time with an account with read-write privileges so the user is logged into the app. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table Size
Hi, What command used to check table size and database size? thanks - Blab-away for as little as 1¢/min. Make PC-to-Phone Calls using Yahoo! Messenger with Voice.
Re: Table Size
At 2:15 pm -0700 5/4/06, Ravi Kumar wrote: What command used to check table size and database size? For table size: http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html I imagine (though I don't know for sure) that you can get the same info from the information_schema database that was introduced in v5. http://dev.mysql.com/doc/refman/5.0/en/information-schema.html HTH, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Thank you MySQL Cluster
Thank you MySQL Cluster, for ignoring my information request after submitting about a year and a half ago. I was told in email that I was supposed to be contacted, that never happened. Thank you for automatically signing me up for all sorts of MySQL mailinglists for seminars that I do not want to attend. Should I also thank you for selling my email address to a 3rd party company (Continuent, Inc) advertising 'Replication and load-balancing' Webcasts, or did they automatically gather emails from the main MySQL mailinglist? And, no thanks, I'm no longer interested in MySQL Cluster. :) Atle - Flying Crocodile Inc, Unix Systems Administrator -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restoring a binary log ( mysqlbinlog )
It will be written in binary file but when you do mysqlbinlog -d hrdb --start-date-time=2006-04-04 06:00:00 src.sql It wont be loaded in src.sql --Praj On Wed, 05 Apr 2006 09:53:34 -0300 Mauricio Pellegrini [EMAIL PROTECTED] wrote: Hi, please see comments below.. thank you Mauricio On Wed, 2006-04-05 at 09:02, Prasanna Raj wrote: Hi All Correct me if iam wrong You might have run the create table syntax from hrdb database .So it gives you the create table also when you do mysqlbinlog . CREATE TABLE intra.table1 AS SELECT * FROM NO DATABASE NAMEtable2 , which means you are under hrdb database. Thast exactly the situation. Else you can use below syntax which wont get loaded under hrdb CREATE TABLE intra.table1 AS SELECT * FROM hrdb.table2 ( OR ) use intra ; CREATE TABLE table1 AS SELECT * FROM hrdb.table2 Would this prevent this kind of querys from appearing in the source ? --Praj On Wed, 05 Apr 2006 08:44:52 -0300 Mauricio Pellegrini [EMAIL PROTECTED] wrote: Hi , Yesterday our main database was dropped by mystake. This has never happened before. Furtunately we had a daily backup performed with mysqldump at about 06:00 am that day. As the problem ocurred at about 12:00am we had to restore the transactions from the binary log ( we have several binary log files for each day ) For this pourpose we used mysqlbinlog in order to prepare a source file with the transactions to be restored, but as we needed to restore transactions for only one database, we used the option -d win mysqlbinlog like this mysqlbinlog -d hrdb --start-date-time=2006-04-04 06:00:00 src.sql after that we used the resulting file like this mysql -e source src.sql all the process took about 1 hour to complete and our data was perfectly restored. But examinig the source file src.sql we noted that there were some transactions pertaining other databases than hrdb which we think shouldn't be there because of the -d option used with mysqlbinlog. These transaction were table creation querys like CREATE TABLE intra.table1 AS SELECT * FROM table2 In this query table1 is located in database intra and table2 belongs to database hrdb since the creation of table1 occurs in database intra we thought this query shouldn't be in the source Am I right ? The problem with this is that the time for the whole restore operation would have been considerabily lower if all of these useless transactions were excluded ( this write operations were created as a result from executing reports not data modification ) thanks Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
