Re: innodb_data_file_path
Hi Jeetendra, What is the error it it saying in error log ? Is this the fresh installation or already installed MySQL ? Thanks Suresh Kuna MySQL DBA On Mon, Jan 4, 2010 at 10:16 AM, Jeetendra Ranjan < jeetendra.ran...@sampatti.com> wrote: > Hi, > > I am using MySQL 5.0.85-community on Fedora 8. > > When i set > innodb_data_home_dir = > innodb_data_file_path = /dev/sda3:10Gnewraw;/dev/sda1:5Gnewraw > and restart the server it gives an error > > "Starting MySQL.Manager of pid-file quit without updating file.[FAILED]" > > Please suggest how to resolve this issue ? > > > Thanks > Jeetendra Ranjan > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com > > -- Thanks, Suresh Kuna
Re: innodb_data_file_path
Jeetendra, Give the MySQLD user permissions to the new directory which you are specifying. On Mon, Jan 4, 2010 at 10:57 AM, Jeetendra Ranjan < jeetendra.ran...@sampatti.com> wrote: > Hi Suresh, > > The error log show the related error as below > > > InnoDB: File name /dev/sda3 > InnoDB: File operation call: 'open'. > InnoDB: Cannot continue operation. > 100102 1:18:32 [Warning] No argument was provided to --log-bin, and > --log-bin-index was not used; so replication may break when this MySQL > server acts as a master and has his hostname changed!! Please use > '--log-bin=ip-xx-xx-xx-xx-bin' to avoid this problem. > 100102 1:18:32 InnoDB: Operating system error number 13 in a file > operation. > InnoDB: The error means mysqld does not have the access rights to > InnoDB: the directory. > > > Thanks > Jeetendra Ranjan > > > > - Original Message - > *From:* Suresh Kuna > *To:* Jeetendra Ranjan > *Cc:* mysql@lists.mysql.com > *Sent:* Monday, January 04, 2010 10:28 AM > *Subject:* Re: innodb_data_file_path > > Hi Jeetendra, > What is the error it it saying in error log ? > Is this the fresh installation or already installed MySQL ? > > Thanks > Suresh Kuna > MySQL DBA > > On Mon, Jan 4, 2010 at 10:16 AM, Jeetendra Ranjan < > jeetendra.ran...@sampatti.com> wrote: > >> Hi, >> >> I am using MySQL 5.0.85-community on Fedora 8. >> >> When i set >> innodb_data_home_dir = >> innodb_data_file_path = /dev/sda3:10Gnewraw;/dev/sda1:5Gnewraw >> and restart the server it gives an error >> >> "Starting MySQL.Manager of pid-file quit without updating file.[FAILED]" >> >> Please suggest how to resolve this issue ? >> >> >> Thanks >> Jeetendra Ranjan >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: >> http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com >> >> > > > -- > Thanks, > Suresh Kuna > > -- Thanks Suresh Kuna MySQL DBA
Re: innodb_data_file_path
Hi Jeetendra, Check the below url for details with the raw partition. http://dev.mysql.com/doc/refman/5.4/en/innodb-raw-devices.html Thanks Suresh Kuna MySQL DBA On Mon, Jan 4, 2010 at 11:06 AM, Suresh Kuna wrote: > Jeetendra, > > Give the MySQLD user permissions to the new directory which you are > specifying. > > > > > On Mon, Jan 4, 2010 at 10:57 AM, Jeetendra Ranjan < > jeetendra.ran...@sampatti.com> wrote: > >> Hi Suresh, >> >> The error log show the related error as below >> >> >> InnoDB: File name /dev/sda3 >> InnoDB: File operation call: 'open'. >> InnoDB: Cannot continue operation. >> 100102 1:18:32 [Warning] No argument was provided to --log-bin, and >> --log-bin-index was not used; so replication may break when this MySQL >> server acts as a master and has his hostname changed!! Please use >> '--log-bin=ip-xx-xx-xx-xx-bin' to avoid this problem. >> 100102 1:18:32 InnoDB: Operating system error number 13 in a file >> operation. >> InnoDB: The error means mysqld does not have the access rights to >> InnoDB: the directory. >> >> >> Thanks >> Jeetendra Ranjan >> >> >> >> - Original Message - >> *From:* Suresh Kuna >> *To:* Jeetendra Ranjan >> *Cc:* mysql@lists.mysql.com >> *Sent:* Monday, January 04, 2010 10:28 AM >> *Subject:* Re: innodb_data_file_path >> >> Hi Jeetendra, >> What is the error it it saying in error log ? >> Is this the fresh installation or already installed MySQL ? >> >> Thanks >> Suresh Kuna >> MySQL DBA >> >> On Mon, Jan 4, 2010 at 10:16 AM, Jeetendra Ranjan < >> jeetendra.ran...@sampatti.com> wrote: >> >>> Hi, >>> >>> I am using MySQL 5.0.85-community on Fedora 8. >>> >>> When i set >>> innodb_data_home_dir = >>> innodb_data_file_path = /dev/sda3:10Gnewraw;/dev/sda1:5Gnewraw >>> and restart the server it gives an error >>> >>> "Starting MySQL.Manager of pid-file quit without updating file.[FAILED]" >>> >>> Please suggest how to resolve this issue ? >>> >>> >>> Thanks >>> Jeetendra Ranjan >>> >>> -- >>> MySQL General Mailing List >>> For list archives: http://lists.mysql.com/mysql >>> To unsubscribe: >>> http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com >>> >>> >> >> >> -- >> Thanks, >> Suresh Kuna >> >> > > > -- > Thanks > Suresh Kuna > MySQL DBA > -- Thanks Suresh Kuna MySQL DBA
Re: innodb_data_file_path
Try this : /dev/sda3:10Graw;/dev/sda1:5Graw Change the newraw to raw and start the MySQL. and paste the error log ouput. Thanks Suresh Kuna MySQL DBA On Mon, Jan 4, 2010 at 12:37 PM, Jeetendra Ranjan < jeetendra.ran...@sampatti.com> wrote: > Hi Suresh, > Thanks, this link we already have but it has no information right from > creating the raw disk partition. > > Jeetendra Ranjan > > - Original Message - > *From:* Suresh Kuna > *To:* Jeetendra Ranjan > *Cc:* mysql@lists.mysql.com > *Sent:* Monday, January 04, 2010 12:12 PM > *Subject:* Re: innodb_data_file_path > > Hi Jeetendra, > Check the below url for details with the raw partition. > http://dev.mysql.com/doc/refman/5.4/en/innodb-raw-devices.html > > Thanks > Suresh Kuna > MySQL DBA > > On Mon, Jan 4, 2010 at 11:06 AM, Suresh Kuna wrote: > >> Jeetendra, >> >> Give the MySQLD user permissions to the new directory which you are >> specifying. >> >> >> >> >> On Mon, Jan 4, 2010 at 10:57 AM, Jeetendra Ranjan < >> jeetendra.ran...@sampatti.com> wrote: >> >>> Hi Suresh, >>> >>> The error log show the related error as below >>> >>> >>> InnoDB: File name /dev/sda3 >>> InnoDB: File operation call: 'open'. >>> InnoDB: Cannot continue operation. >>> 100102 1:18:32 [Warning] No argument was provided to --log-bin, and >>> --log-bin-index was not used; so replication may break when this MySQL >>> server acts as a master and has his hostname changed!! Please use >>> '--log-bin=ip-xx-xx-xx-xx-bin' to avoid this problem. >>> 100102 1:18:32 InnoDB: Operating system error number 13 in a file >>> operation. >>> InnoDB: The error means mysqld does not have the access rights to >>> InnoDB: the directory. >>> >>> >>> Thanks >>> Jeetendra Ranjan >>> >>> >>> >>> - Original Message - >>> *From:* Suresh Kuna >>> *To:* Jeetendra Ranjan >>> *Cc:* mysql@lists.mysql.com >>> *Sent:* Monday, January 04, 2010 10:28 AM >>> *Subject:* Re: innodb_data_file_path >>> >>> Hi Jeetendra, >>> What is the error it it saying in error log ? >>> Is this the fresh installation or already installed MySQL ? >>> >>> Thanks >>> Suresh Kuna >>> MySQL DBA >>> >>> On Mon, Jan 4, 2010 at 10:16 AM, Jeetendra Ranjan < >>> jeetendra.ran...@sampatti.com> wrote: >>> >>>> Hi, >>>> >>>> I am using MySQL 5.0.85-community on Fedora 8. >>>> >>>> When i set >>>> innodb_data_home_dir = >>>> innodb_data_file_path = /dev/sda3:10Gnewraw;/dev/sda1:5Gnewraw >>>> and restart the server it gives an error >>>> >>>> "Starting MySQL.Manager of pid-file quit without updating file.[FAILED]" >>>> >>>> Please suggest how to resolve this issue ? >>>> >>>> >>>> Thanks >>>> Jeetendra Ranjan >>>> >>>> -- >>>> MySQL General Mailing List >>>> For list archives: http://lists.mysql.com/mysql >>>> To unsubscribe: >>>> http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com >>>> >>>> >>> >>> >>> -- >>> Thanks, >>> Suresh Kuna >>> >>> >> >> >> -- >> Thanks >> Suresh Kuna >> MySQL DBA >> > > > > -- > Thanks > Suresh Kuna > MySQL DBA > > -- Thanks Suresh Kuna MySQL DBA
Re: mysql Create table in system database
Hi Manasi, Do you mean the copy paste of one database and rename it to another ? If so, it is not possible as the data is shared between the data nodes by partitioning. For innodb, if you are using file_per_table option, We can do so and give the "flush tables" command to read the same but the data dictionary will get effected. Thanks Suresh Kuna MySQL DBA On Thu, Jan 7, 2010 at 9:46 AM, Manasi Save < manasi.s...@artificialmachines.com> wrote: > Hi Johan, > > > Is it possible to do such manipulation in NDB, the way innodb works. > > > Thanks in advance. > > > -- > > Regards, > Manasi Save > > > Quoting Johan De Meersman : > > File permissions ? SE Linux ? AppArmor ? > > On Mon, Nov 16, 2009 at 7:48 AM, Manasi Save < > manasi.s...@artificialmachines.com> wrote: > >> Thanks Shawn for the quick response. >> >> But then What I am doing is I am doing copy paste of one database and >> rename it to another. but I cannot read the tables inside it. >> >> Can you tell me what might be the possible reason for that. >> >> -- >> Thanks and Regards, >> Manasi Save >> Artificial Machines Pvt Ltd. >> >> > Hello Manasi, >> > >> > Manasi Save wrote: >> >> Hi All, >> >> >> >> Can anyone give me any input on How mysql create table write data into >> >> system database and where it has been stored besides >> information_schema. >> >> >> >> Is there any article on mysql System Databases anyone went through as I >> >> am >> >> not able to find it on Google. >> >> >> >> I want to write a table information in mysql system database. Can >> anyone >> >> help me on this. >> >> >> >> Thanks in advance. >> >> >> > >> > MySQL does not store that information within an internal table. The >> > basic information for each table is stored within a .frm file stored in >> > the file system. The various additional pieces of metadata for each >> > storage engine are maintained in methods specific to those storage >> > engine. The information you see in the many tables exposed through >> > INFORMATION_SCHEMA is generated dynamically based on the results of >> > polling those separate sources of metadata at the time of your query. >> > >> > quoting from >> > http://dev.mysql.com/doc/refman/5.1/en/information-schema.html >> > ~~ >> > Inside INFORMATION_SCHEMA there are several read-only tables. They are >> > actually views, not base tables, so there are no files associated with >> > them. >> > ~~ >> > >> > More details are available in the manual: >> > http://dev.mysql.com/doc/refman/5.1/en/myisam-storage-engine.html >> > http://dev.mysql.com/doc/refman/5.1/en/innodb-table-and-index.html >> > http://dev.mysql.com/doc/refman/5.1/en/se-db2.html >> > http://dev.mysql.com/doc/refman/5.1/en/merge-storage-engine.html >> > http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html >> > http://dev.mysql.com/doc/refman/5.1/en/federated-description.html >> > http://dev.mysql.com/doc/refman/5.1/en/archive-storage-engine.html >> > http://dev.mysql.com/doc/refman/5.1/en/csv-storage-engine.html >> > http://dev.mysql.com/doc/refman/5.1/en/blackhole-storage-engine.html >> > >> > -- >> > Shawn Green, MySQL Senior Support Engineer >> > Sun Microsystems, Inc. >> > Office: Blountville, TN >> > >> > >> > >> >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be >> >> > -- Thanks Suresh Kuna MySQL DBA
Re: Are there any difference between max_connection and max_user_connection?
Hi Faizal, Max_connections are the connections for the overall MySQLD server. Max_user_connections are the connections for the particular user ( i.e for an account ) of the MySQLD server. Suresh Kuna MySQL DBA On Thu, Jan 7, 2010 at 11:00 AM, F.A.I.Z.A.L wrote: > Dear all > > I can see max_connection and max_user_connection set to default value. but > daily i can see 40 to 70 users connection to the database. how? when > max_user_connections=0. what is the different between these two > parameters.. > > max_connection=100 > max_user_connections=0 > > thanks in advance. > > Cheers > Faizal S > GSM : 9840118673 > Blog: http://oradbapro.blogspot.com > -- Thanks Suresh Kuna MySQL DBA
Re: Are there any difference between max_connection and max_user_connection?
Yes it won't allow except 1 connection for root user. We have to monitor the MySQLD server and the queries runnning at that time. As of now, increase the key_buffer_size to 64 or 128 mb according to your indexes created and physical memory available. and query_cache_size is purely depends on the queries that your application hits the database. On Thu, Jan 7, 2010 at 12:20 PM, F.A.I.Z.A.L wrote: > hi Suresh > > thanks. max_connection=100(default). so, it won't allow user more than 100? > > and one more clarification. i am facing performance issue in mysqld. some > time mysqld consuming 100% cpu. so what i have to do now? > > and the below parameters are in default values. if i increase the value. it > will help performance? > > query_cache_size = 0 > key_buffer_size = 8m > sort_buffer_size = 2m > innodb_buffer_pool_size = 8m > > read_buffer = 2M > write_buffer = 2M > > > thanks in advance.. > > Cheers > Faizal S > GSM : 9840118673 > Blog: http://oradbapro.blogspot.com > > > On Thu, Jan 7, 2010 at 11:27 AM, Suresh Kuna wrote: > >> Hi Faizal, >> Max_connections are the connections for the overall MySQLD server. >> Max_user_connections are the connections for the particular user ( i.e for >> an account ) of the MySQLD server. >> >> Suresh Kuna >> MySQL DBA >> >> On Thu, Jan 7, 2010 at 11:00 AM, F.A.I.Z.A.L wrote: >> >>> Dear all >>> >>> I can see max_connection and max_user_connection set to default value. >>> but >>> daily i can see 40 to 70 users connection to the database. how? when >>> max_user_connections=0. what is the different between these two >>> parameters.. >>> >>> max_connection=100 >>> max_user_connections=0 >>> >>> thanks in advance. >>> >>> Cheers >>> Faizal S >>> GSM : 9840118673 >>> Blog: http://oradbapro.blogspot.com >>> >> >> >> >> -- >> Thanks >> Suresh Kuna >> MySQL DBA >> > > -- Thanks Suresh Kuna MySQL DBA
Re: Are there any difference between max_connection and max_user_connection?
It should be identical select statements which will improve performance and not the updates. Go through the below url for more information about optimization and performance http://dev.mysql.com/doc/refman/5.0/en/optimizing-the-server.html On Thu, Jan 7, 2010 at 4:42 PM, F.A.I.Z.A.L wrote: > hi > > i can see many 'updates' is going every min. so is it good to increase this > query_cache_size ?.. > > what are the other action i can take to improve the performance of mysql > server > > environment > version : 5.0.22 > platform : redhat 5 > > > > Cheers > Faizal S > GSM : 9840118673 > Blog: http://oradbapro.blogspot.com > > > -- Thanks Suresh Kuna MySQL DBA
Re: upgrading mysql
Hi, The step 6 in simple terms is Here we need to build two server ( both master and slave ). Instead of building two server as it takes double the time of building in one server. After building an server, make a copy of the first server files at OS level and copy it to the server and start the same. Configure the replication between the two server. By doing this, We will save the import time in second server. Thanks Suresh Kuna MySQL DBA On Wed, Jan 13, 2010 at 3:58 AM, Tom Worster wrote: > Frankly, I didn't entirely understand what you were proposing. I got lost > around step 6. > > Is the issue total time for the procedure or service downtime? > > > On 1/12/10 12:58 PM, "Lawrence Sorrillo" wrote: > > > This is two upgrades done in sequence(the reload takes about three hours > > per machine) . I can do what I am proposing in parallel. > > > > Do you see it as problematic? > > > > ~Lawrence > > > > > > Tom Worster wrote: > >> How about: > >> > >> 1 shut down the slave, upgrade it, restart it, let it catch up. > >> > >> 2 shut down the master, upgrade it, restart it, let the slave catch up. > >> > >> ? > >> > >> > >> > >> > >> > >> On 1/12/10 12:34 PM, "Lawrence Sorrillo" wrote: > >> > >> > >>> Hi: > >>> > >>> I want to upgrade a master and slave server from mysql 4.1 to mysql > 5.1. > >>> > >>> I want to so something like follows: > >>> > >>> 1. Stop all write access to the master server. > >>> 2. Ensure that replication on the slave is caught up to the last change > >>> on the master. > >>> 3. stop binary logging on the master. > >>> 4. stop replication on the slave. > >>> 5. dump the master, stop old 4.1 server, start new 5.1 server and > reload > >>> master dump file under 5.1 server ( binary logging is turned off) > >>> 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload > >>> slave dump file under 5.1 server. > >>> 7. After loading is complete, test then start binary logging on master > >>> while still preventing updates to updates. > >>> 8. After loading slave, test then start slave (get configs in place and > >>> restart server). > >>> > >>> I am thinking that in this scenario I dont have to bother with > recording > >>> binlog file names and position etc etc. > >>> That both servers will have the same databases abd replication and > >>> binary logging will start on the two databases with no data loss and > >>> continue forward. > >>> > >>> > >>> Comments? > >>> > >>> ~Lawrence > >>> > >>> > >>> > >>> > >> > >> > >> > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com > > -- Thanks Suresh Kuna MySQL DBA
Re: MySQL Master Master Replication and data loss
Hi Manasi, As both are implemented by replication, there is a possibility for loosing data. Thanks Suresh Kuna MySQL DBA On Wed, Jan 13, 2010 at 8:20 PM, Manasi Save < manasi.s...@artificialmachines.com> wrote: > Hi All, > > > I want to implement MySQL Master Master Replication. But I have read in the > various documentation that in mysql replication it is possible that I loose > data. > > Is it also applicable to MySQL Master Master Replication. > > > Thanks in advance. > > > -- > > Regards, > Manasi Save > > -- Thanks Suresh Kuna MySQL DBA
Re: MySQL Master Master Replication and data loss
Hi Manasi, Inside MySQL, there are no such parameters and the Slave delay depends on different things like network between Master and Slave, load of the MySQLD server etc... To make the slave behind for a particular period of time, use mk-slave-delay tool from the the maakit. It help your slave to be lack for a particular period of time. On Thu, Jan 14, 2010 at 2:11 PM, Manasi Save < manasi.s...@artificialmachines.com> wrote: > > Dear Suresh, > > Thank you. > In MySQL Replication, as the slave itself takes the writes from master but > in how much time period does slave goes to master. is there any parameter > where I can set this. that after every 60 seconds slave should write data > from master to its own local database. > -- > Regards, > Manasi Save > > > > > Quoting Suresh Kuna : > >> Hi Manasi, >> As both are implemented by replication, there is a possibility for loosing >> data. >> Thanks >> Suresh Kuna >> MySQL DBA >> >> On Wed, Jan 13, 2010 at 8:20 PM, Manasi Save < >> manasi.s...@artificialmachines.com> wrote: >> >> > Hi All, >> > >> > >> > I want to implement MySQL Master Master Replication. But I have read in >> the >> > various documentation that in mysql replication it is possible that I >> loose >> > data. > >> > Is it also applicable to MySQL Master Master Replication. > >> > >> > Thanks in advance. > >> > >> > -- >> > >> > Regards, >> > Manasi Save >> > >> > >> >> >> -- Thanks >> Suresh Kuna >> MySQL DBA >> > > -- Thanks Suresh Kuna MySQL DBA
Re: Performance Innodb my.cnf
Hi Ortis, How abt the hits or load i.e ( DML, DDL ) to the server. My initial assessment after looking at you cnf file is 1) Calculate and place an appropriate value for innodb_buffer_pool_size 2) Reduse the innodb_thread_concurrency to 4 or 8. and how about the no. of tables in the database and the table type. On Sun, Jan 17, 2010 at 3:03 AM, Junior Ortis wrote: > Hi guys, first thanks for all help, this list is amazing. > > Well i have a dedicated server on Fedora 11 x64, its have 12GB ram and > a SCSI 15k rpm on datadir. > > I need a improve on my mysql conf to that my software run better, its > my.cnf HOW i Can improve this :D > > Thanks !! > > HERE: > > [client] > #password = [your_password] > port= 3306 > socket = /tmp/mysql.sock > > # *** Application-specific options follow here *** > > # > # The MySQL server > # > [mysqld] > # generic configuration options > port= 3306 > socket = /tmp/mysql.sock > skip-locking > skip-external-locking > datadir = /disk3/mysql > net_buffer_length = 1024K > join_buffer_size= 1M > sort_buffer_size= 4M > read_buffer_size= 4M > read_rnd_buffer_size= 4M > table_cache = 500 > max_allowed_packet = 16M > > max_connections=30 > max_user_connections=200 > > key_buffer = 1000M > key_buffer_size = 1000M > #thread_cache = 400 > thread_stack= 128K > thread_cache_size = 1024 > thread_concurrency = 8 > #thread_stack = 128K > > default-character-set = utf8 > innodb_flush_method=O_DIRECT > innodb_buffer_pool_size= 11000M > innodb_additional_mem_pool_size=10M > innodb_log_file_size= 256M > innodb_log_buffer_size=4M > innodb_flush_log_at_trx_commit=0 > innodb_thread_concurrency=32 > innodb_file_per_table > innodb_table_locks=0 > > query_alloc_block_size = 16k > > query_cache_limit = 512M > query_cache_size= 512M > query_cache_type= 1 > > long_query_time = 3 > table_cache = 800 > #innodb_force_recovery = 3 > table_definition_cache = 800 > query_cache_min_res_unit = 5K > delay-key-write=OFF > innodb_read_io_threads = 16 > innodb_write_io_threads = 16 > innodb_support_xa = false > innodb_io_capacity = 1 > innodb_max_dirty_pages_pct = 90 > > concurrent_insert = 2 > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com > > -- Thanks Suresh Kuna MySQL DBA
Re: MySQL Replication Delete is not gettting replicated
Hi Manasi, That alone is the difference in this case. -- Thanks Suresh Kuna MySQL DBA On Tue, Jan 19, 2010 at 10:36 AM, Manasi Save < manasi.s...@artificialmachines.com> wrote: > Dear Carlos, > > Thanks for the response. But I haven't gave any privileges besides > repl_slave priv to user replication and replication2 respectively. > So does that amke any difference really? > > > Thanks in advance. > -- > Regards, > Manasi Save > > Quoting Carlos Proal : > >> >> I dont see anything unusual or missing on your config file and as the >> only thing missing are deletes, i think that might be a permission issue. >> Can you check out the grants for your replication users and see if they >> have full permissions granted ? >> >> mysql> show grants for x; >> >> where is x is replication and replication2 respectively. >> Carlos >> >> >> On 1/18/2010 1:35 AM, Manasi Save wrote: >> > Hi Anand, >> > >> > Please find below my configuration file of both the masters: >> > >> > ON MASTER 1: >> > >> > [mysqld] >> > datadir=/var/lib/mysql/ >> > socket=/var/lib/mysql/mysql.sock >> > old_passwords=1 >> > >> > log-bin=/usr/local/mysql/bin.log >> > #binlog-do-db= # input the database which should >> > be replicated >> > binlog-ignore-db=mysql# input the database that should be >> > ignored for replication >> > binlog-ignore-db=test >> > log-bin-index=/usr/local/mysql/log-bin.index >> > log_slave_updates >> > >> > server-id=2 >> > >> > auto_increment_increment=2 >> > auto_increment_offset=1 >> > >> > #information for becoming slave. > master-host = 192.168.1.1 >> > master-user = replication >> > master-password = replication >> > master-port = 3306 >> > >> > [mysql.server] >> > user=mysql >> > >> > [mysqld_safe] >> > err-log=/var/lib/mysql/mysql.log >> > pid-file=/var/lib/mysql/mysql.privatedns.com.pid >> > >> > ON MASTER 2: >> > >> > [mysqld] >> > datadir=/var/lib/mysql/ >> > socket=/var/lib/mysql/mysql.sock >> > old_passwords=1 >> > >> > log-bin=/usr/local/mysql/bin.log >> > #binlog-do-db= # input the database which should >> > be replicated >> > binlog-ignore-db=mysql# input the database that should be >> > ignored for replication >> > binlog-ignore-db=test >> > log-bin-index=/usr/local/mysql/log-bin.index >> > log_slave_updates >> > >> > server-id=1 >> > >> > auto_increment_increment=2 >> > auto_increment_offset=2 >> > >> > #information for becoming slave. > master-host = 192.168.1.2 >> > master-user = replication2 >> > master-password = replication2 >> > master-port = 3306 >> > >> > [mysql.server] >> > user=mysql >> > >> > [mysqld_safe] >> > err-log=/var/var/lib/mysql/mysql.log >> > pid-file=/var/lib/mysql/mysql.privatedns.com.pid >> > >> > Please let me know if I need to add any parameter to enable this >> > replication. > >> > Thanks in advance. > >> > -- >> > >> > Regards, >> > >> > Manasi Save >> > >> > >> > >> > Quoting Anand kumar : >> > >> > can you give us the configuration(.cnf) file from both the masters ? >> > --Anand >> > On Sat, Jan 16, 2010 at 3:06 PM, Manasi Save >> > > > <mailto:manasi.s...@artificialmachines.com>> wrote: >> > >> > Hi All, >> > >> > >> > I have configured MySQL Master-Master Replication on my >> > servers. When I am inserting or updating any data in a regular >> > table the data is getting replicated. > >> > >> > But When I am doing delete on that same table. the data is >> > only getting deleted only on the server where I am doing >> > delete. but it is not getting replicated on its slave. > >> > >> > Even if I am doing truncate it is not getting replicated. Can >> > anyone provide any input on this? >> > >> > >> > Thanks in advance. > >> > >> > -- >> > >> > Regards, >> > >> > Manasi Save >> > >> > >> >> > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com > >
Re: Selecting, Inserting and Deleting data
Hi Krishna, As table is using MyISAM engine and it acquires a table level lock, the queries will be executed one after one . By converting it into Innodb as it acquires a row level lock, doing a select and delete based on primary key will be faster and the concurrency increases. -- Thanks Suresh Kuna MySQL DBA On Thu, Jan 21, 2010 at 4:30 PM, Krishna Chandra Prajapati < prajapat...@gmail.com> wrote: > Hi List, > > I am working for a messaging company, sending sms to enterprise customers. > > In a mysql table data is being continuously inserted by user. Most of the > time we have 5 to 10 millions of data in this table. > > Table name : alt_send_sms engine myisam > > From this table, i need to select data based on below parameter. Send some > where else and then delete the selected data. > > selection and deletion part is done in bulk. > > SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id, > service, account, id, sms_type, mclass, mwi, coding, compress FROM > alt_send_sms WHERE smsc_id = 'ite' ORDER BY msg_priority, sql_id LIMIT 0,20 > > delete from alt_send_sms where sql_id in > () > > sql_id is a unique bigint column with auto_increment. > > Since the selection and deletion is done in bulk. Therefore, i cannot run > many similar concurrent queries. As duplicate messages will be send. What > can be the solution for this ? > > Any response is highly appreciated. > > Thanks, > Krishna >
Re: Selecting, Inserting and Deleting data
Innodb contains multi-version property, so it can handle more concurrent queries from user connections. On Thu, Jan 21, 2010 at 5:07 PM, Krishna Chandra Prajapati < prajapat...@gmail.com> wrote: > Hi Suresh, > > my question is how i can run concurrent connection with the above work > load. > > Thanks, > Krishna > > > On Thu, Jan 21, 2010 at 4:46 PM, Suresh Kuna wrote: > >> Hi Krishna, >> As table is using MyISAM engine and it acquires a table level lock, the >> queries will be executed one after one . >> By converting it into Innodb as it acquires a row level lock, doing a >> select and delete based on primary key will be faster and the concurrency >> increases. >> >> -- >> Thanks >> Suresh Kuna >> MySQL DBA >> >> >> >> On Thu, Jan 21, 2010 at 4:30 PM, Krishna Chandra Prajapati < >> prajapat...@gmail.com> wrote: >> >>> Hi List, >>> >>> I am working for a messaging company, sending sms to enterprise >>> customers. >>> >>> In a mysql table data is being continuously inserted by user. Most of the >>> time we have 5 to 10 millions of data in this table. >>> >>> Table name : alt_send_sms engine myisam >>> >>> From this table, i need to select data based on below parameter. Send >>> some >>> where else and then delete the selected data. >>> >>> selection and deletion part is done in bulk. >>> >>> SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id, >>> service, account, id, sms_type, mclass, mwi, coding, compress FROM >>> alt_send_sms WHERE smsc_id = 'ite' ORDER BY msg_priority, sql_id LIMIT >>> 0,20 >>> >>> delete from alt_send_sms where sql_id in >>> (....) >>> >>> sql_id is a unique bigint column with auto_increment. >>> >>> Since the selection and deletion is done in bulk. Therefore, i cannot run >>> many similar concurrent queries. As duplicate messages will be send. What >>> can be the solution for this ? >>> >>> Any response is highly appreciated. >>> >>> Thanks, >>> Krishna >>> >> >> >> > -- Thanks Suresh Kuna MySQL DBA
Re: mysql update
Did you check the server load when it took 10 min. Check the query log, number of connections and the number of queries in query log at that time. We need to analyze the system with the collected data what we have for monitoring. Thanks Suresh Kuna On Fri, Jan 22, 2010 at 12:25 PM, madunix wrote: > I have the following update procedure that update mySQL DB over the > internet between source Linux Centos (local machine on my net behind a > DMZ with real IP A.B.C.D) and target Linux fedora (web server > www.myweb.com) every day on a specific time 18:00 through a crontab on > my source linux server > > server(source) > ---DMZ---ASA---Router-InternetHostingCompany---Myweb(target) > [r...@source]# mysql -u updatex -p -h www.myweb.com test < sample.SQL > > > [r...@source]$ mysql -u updatex -p -h www.myweb.com test < sample.SQL > Enter password: * > CURTIME() > 19:41:44 > CURTIME() > 19:50:09 > > [r...@source]$ mysql -u updatex -p -h www.myweb.com test < sample.SQL > Enter password:* > CURTIME() > 08:26:08 > CURTIME() > 08:26:34 > > I did the above procedure multiple times in different times in the > day. the duration of this procedure takes from 22sec to 10min > see above, before a while it was running constant with duration of > 30sec. I checked with my ISP, hosting company and network nothing been > changed from the structure/configuration. > > [r...@source]# lsof -i -P | grep 3306 > mysqld 3806 mysql 11u IPv4 10926 TCP *:3306 (LISTEN) > mysql 15150user3u IPv4 297528 TCP > 192.168.10.5:8376->www.myweb.com:3306 (ESTABLISHED) > > [r...@target]# netstat -a |grep mysql > tcp0 0 *:mysql *:* > LISTEN > tcp0 0 www.myweb.:mysql A.B.C.D:8366 TIME_WAIT > tcp0 11 www.myweb.:mysql A.B.C.D:8372 ESTABLISHED > also i attached tcp connection between the nodes as above from source > and target, > can any one help why i have this behavior and how can i fix the delay, > thinking doing QoS or clean up and remoteexcution at that time ... > > Thanks in advance > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com > > -- Thanks Suresh Kuna MySQL DBA
Re: Problems with slave_skip_errors on replication
Hi Wagner, You have to start the server with the option as below for skipping the error. --slave-skip-errorr= 1062 or all 1062 - will skip the your error as the error number is 1062 and all will skip all the errors. You have to mention specific error numbers to skip the same. -- Thanks Suresh Kuna MySQL DBA On Mon, Jan 25, 2010 at 6:06 PM, Wagner Bianchi wrote: > Hi friends, > > Last weekend I made an environment that use a MySQL Server version 4.1 that > was defined to be the MASTER and other one version 5.1 defined as SLAVE. > > Because the application that was concept working over exception, often the > SLAVE server got new error and replication stops. > > Well, I configured the my.cnf file of the SLAVE to slave_skip_errors as you > may see specified after this massage, but, the replication continue stops, > even after this configurations. > > mysql> show variables like 'slave_skip_errors'; > +---+---+ > | Variable_name | Value | > +---+---+ > | slave_skip_errors | 1 | > +---+---+ > 1 row in set (0.00 sec) > mysql> show slave status\G > *** 1. row *** > Slave_IO_State: Queueing master event to the relay log > Master_Host: 172.28.8.70 > Master_User: slave > Master_Port: 3306 >Connect_Retry: 60 > Master_Log_File: bmg58-bin.000265 > Read_Master_Log_Pos: 251871 > Relay_Log_File: pid-file-relay-bin.07 >Relay_Log_Pos: 961348 >Relay_Master_Log_File: bmg58-bin.03 > Slave_IO_Running: Yes >Slave_SQL_Running: No > Replicate_Do_DB: > Replicate_Ignore_DB: > Replicate_Do_Table: > Replicate_Ignore_Table: > Replicate_Wild_Do_Table: > Replicate_Wild_Ignore_Table: > Last_Errno: 1062 > Last_Error: Error 'Duplicate entry '731493' for key > 'PRIMARY'' on query. Default database: 'database'. Query: 'INSERT > INTO tb_usuario (ocu_codigo, ocu_tipo, usu_codigo, > ocu_data, ocu_obs, login_responsavel, ocu_ip) > VALUES( null, 67, > 'C986CC89AC1C071835E341D18011D25Z', now(), > 'x.', 'x.sp', null)' > Skip_Counter: 0 > Exec_Master_Log_Pos: 952913 > Relay_Log_Space: 264590369 > Until_Condition: None > Until_Log_File: >Until_Log_Pos: 0 > Master_SSL_Allowed: No > Master_SSL_CA_File: > Master_SSL_CA_Path: > Master_SSL_Cert: >Master_SSL_Cipher: > Master_SSL_Key: >Seconds_Behind_Master: NULL > Master_SSL_Verify_Server_Cert: No >Last_IO_Errno: 0 >Last_IO_Error: > Last_SQL_Errno: 1062 > Last_SQL_Error: Error 'Duplicate entry '731493' for key > 'PRIMARY'' on query. Default database: 'database'. Query: 'INSERT > INTO tb_usuario (ocu_codigo, ocu_tipo, usu_codigo, > ocu_data, ocu_obs, login_responsavel, ocu_ip) > VALUES( null, 67, > 'C986CC89AC1C071835E341D18011D25Z', now(), > 'x', 'x.sp', null)' > 1 row in set (0.00 sec) > > Have other thing to do, or this problem is made by the mix of versions? > > Best regards! > -- > Wagner Bianchi - Web System Developer and Database Administrator > Phone: (31) 8654-9510 / 3272-0226 > E-mail: wagnerbianch...@gmail.com > Lattes: http://lattes.cnpq.br/2041067758113940 > Twitter: http://twitter.com/wagnerbianchi > Skype: infodbacet >
Re: Record old passwords ?
Hi Tompkins,
Check the below URL, looks like useful for your project.
20) set_password('username','hostname','oldpassword','newpassword');
(version 0.1.1) (version 0.1.4 added oldpassword) -- Changes password for
any user (if current user is root), otherwise changes own password if
current user is not root. can change the password up to 11times in 1 day and
stores the last 5 passwords which were not changed for at least 24hrs. Does
not permit the new password to be the same as any of the old passwords.
Resets update count if more than 24hrs passed from last first update of the
day. Password must be longer than '10 characters (configurable amount
through sec_config.password_length)'. Complexity requirements are set on
sec_config:
1. password_length_check
2. password_dictionary_check
3. password_lowercase_check
4. password_uppercase_check
5. password_number_check
6. password_special_character_check
7. password_username_check
Root user doesn't need to abide to the above password restrictions when
creating a new user since the latter will have to change the password and
set one of his own.
In order for a user to change one's old password, the user needs to supply
the old password apart from the new one as well.
For more details, check the below link
http://code.google.com/p/securich/wiki/Documentation
Thanks,
Suresh Kuna
MySQL DBA
On Fri, Jan 22, 2010 at 11:52 PM, Tompkins Neil <
neil.tompk...@googlemail.com> wrote:
> Hi
>
> Thanks for all the responses. In the end I opted for
> a separate UserPasswords table, which records all old passwords. When a
> user changes their password, this table is checked. NB All passwords are
> stored in SHA256.
>
> Thanks again for your advice.
>
> Regards
> Neil
>
> On Wed, Jan 20, 2010 at 12:08 PM, Jørn Dahl-Stamnes
> wrote:
>
> > On Wednesday 20 January 2010 01:10, Daevid Vincent wrote:
> > > > -Original Message-
> > > > From: John Meyer [mailto:john.l.me...@gmail.com]
> > > > Sent: Monday, January 18, 2010 5:04 PM
> > > > To: co...@obviouslymalicious.com; mysql@lists.mysql.com
> > > > Subject: Re: Record old passwords ?
> > > >
> > > > Although, on an OT, forcing people to not use a password that they
> > > > have recently used is a bad idea. What they eventually do is go with
> > > > something like "hometown01" "hometown02", etc. Or worse, they start
> > > > writing down their passwords which is a whole other security problem.
> > >
> > > Amen to that. At my work, they require a password change every month,
> but
> > > they store the last 6 passwords you used, so I do exactly what you say
> --
> > I
> > > have a logbook and store the same 6 passwords in it and just cycle
> them.
> > > Other "tricks" I do, is use a pattern on the keyboard and just shift
> it.
> > > None of this is secure, and I totally know it (although I'm not picking
> > > "secret" or something as my PW, it's random letters/numbers/symbols).
> But
> > I
> > > hate the policy and I'm kind of a rebel like that. ;-p
> >
> > Several years ago I worked at a place where users had to change their
> > windows
> > password every N month and they kept a long history log of used password.
> >
> > My solution to this was to write a program that asked me for my current
> > password and how many previous used password the system remembered. The
> > program worked like this:
> >
> > for (n = 0; no_of_stored_password > n; n++) {
> > set_password(random_generated_password);
> > do_a_short_sleep();
> > }
> > set_password(original_password);
> >
> > ... and the problem was solved :)
> >
> > --
> > Jørn Dahl-Stamnes
> > homepage: http://www.dahl-stamnes.net/dahls/
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
> >
> >
>
--
Thanks
Suresh Kuna
MySQL DBA
Re: Using symlinks for database creation in mysql
Not a problem as you are doing it from a whole data directory. Thanks Suresh Kuna MySQL DBA On Fri, Jan 29, 2010 at 11:56 AM, Manasi Save < manasi.s...@artificialmachines.com> wrote: > Hi All, > > > I am creating symlinks as database. > > I have mysql data directory created on /var/lib/mysql/databasename. > > > on the same path I am creating /var/lib/mydatabase > > and creating symlink from /var/lib/mydatabase to > /var/lib/mysql/databasename > > > will there any performance issues as there will be quite a few folders and > symlinks on the same path? > > > Thanks in advance. > > > -- > Regards, > Manasi Save > > -- Thanks Suresh Kuna MySQL DBA
Re: How to change mysql default database directory
Carlos - Follow the below steps 1) stop the mysqld service 2) copy the current datadir to your required location ex : D:\\datadirpath 3) Edit the my.ini by placing the option in mysqld section as datadir=D:\\datadirpath 4) save the my.ini 5) start the mysqld service It will use your new datadir and the newly created and old databases use the same datadir Thanks Suresh Kuna MySQL DBA On Fri, Jan 29, 2010 at 9:22 AM, Carlos Proal wrote: > As Suresh and others said, you have to stop the dbms, move to content of > the dir and restart the dbms. > > Also, if you have a fresh install, innodb tables will be saved on a > datafile inside the same data dir, but if is not a fresh install you may > have innodb variables (ie. innodb_data_home_dir) in the config file, and you > may need to change those too. > > Carlos > > On 1/28/2010 9:25 PM, Lucky Wijaya wrote: > >> Is that all ? So, if i changed datadir on the config file, whenever i >> create databases mysql will store it on selected directory ? How about the >> created-before database ? >> >> Btw, I'm using Windows. >> >> Thanks in advance. >> >> >> *From:* Carlos Proal >> *To:* mysql@lists.mysql.com >> *Sent:* Fri, January 29, 2010 10:16:31 AM >> *Subject:* Re: How to change mysql default database directory >> >> >> >> Absolutely, check for the variable "datadir" on the config file (my.ini >> on windows and my.cnf on *nix). >> >> Carlos >> >> On 1/28/2010 9:12 PM, Lucky Wijaya wrote: >> > Hi all, >> > >> > I've installed MySQL on 320GB Harddisk (partitioned into 3 partitions). >> I want to know if there's a way to change MySQL default database directory >> from C: to D:. >> > >> > Thanks. >> > >> > >> > >> > >> > >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: >> http://lists.mysql.com/mysql?unsub=luckyx_cool_...@yahoo.com >> >> >> > -- Thanks Suresh Kuna MySQL DBA
Re: how to switch between users
Hi Murali, We can switch to another user only by a new connection and not possible in mysql prompt. Thanks Suresh Kuna MySQL DBA On Fri, Jan 29, 2010 at 2:24 PM, muralikrishna g wrote: > i am working on my pc with mysql-5.0.27-community-nt > i have created users by using create user and i given some privileges, but > i > dont know how to switch between users on mysql command line, please help me > regarding this.. > thanks in advance > -- Thanks Suresh Kuna MySQL DBA
Re: how to dump database or tables
Which OS your are using ? Suresh Kuna MySQL DBA On Fri, Jan 29, 2010 at 2:25 PM, muralikrishna g wrote: > i am working on my pc with mysql-5.0.27-community-nt > > i have created several data bases and tables in that.. to take backup, we > have to use dump., i dont know the correct syntax how to use dump to take > backup to a specific location., after that how to resore. please help me > regarding this. > thanks in advance >
Re: hi help to take backup-mysql-windows-xp
Please paste the error and don't give the spave between -p and password. On Tue, Feb 2, 2010 at 12:05 PM, muralikrishna g wrote: > hi to all.. > i was in need to take backup of database.. > i am using sql server version:5.0.27-coomunity-nt > i have tried by using > mysqldump -u name -p password database_name > backup.sql; > > but i am getting error.. i am using windows xp system.. please help me > -- Thanks Suresh Kuna MySQL DBA
Re: how to view all acounts in a database
In the mysql prompt, execute the below use mysql ; select user from user ; will show all the accounts in a MySQL database. On Fri, Feb 5, 2010 at 5:27 PM, ishaq gbola wrote: > Hi Guys, > > Which command can allow me view all accounts in a Mysql database > > > > -- Thanks Suresh Kuna MySQL DBA
Re: How do I get a list of all defined UDF's known to the system?
Hi Robert, We can see the functions by using the below command Show function status ; -- Suresh Kuna MySQL DBA On Mon, Feb 8, 2010 at 10:37 AM, Sir Wally Lewis wrote: > How do I get a list of all defined UDF's known to the system? > > > > Kind Regards, > > > > Robert. > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com > >
Re: Default privileges of a new user?
Information schema is a Virtual DB, the default "usage" grants privilege will be there so he can see few databases like test, mysql and Information_schema but cannot access any data. If you want to restrict the remote connection, Specify the hostname instead of '%' so users can only connect from that host alone. -- Thanks Suresh Kuna MySQL DBA 2010/3/2 PengXiaoxun > I create a new account via the following statement: > CREATE USER 'monty'@'%' IDENTIFIED BY '123456'; > Without any privileges granted, the user 'monty' can access the database > information_schema via a remote host. > Why? > How can I create a new user without any privileges actually?
setting auto_increment value with a local variable
Hi, Is there any way to set the auto_increment value with the variable like below. mysql> set @id=10; mysql> alter table suresh_copy auto_increme...@id; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@id' at line 1 It is working and below but need to work ab above. mysql> alter table suresh_copy auto_increment=1000; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 >From the MySQL documentation : -- To change the value of the AUTO_INCREMENT counter to be used for new rows, do this: ALTER TABLE t2 AUTO_INCREMENT = value; You cannot reset the counter to a value less than or equal to any that have already been used. Thanks Suresh Kuna
Re: MySQLClient 3.x compatible with MySQL 5.x?
Yes, If it is a password problem.
do a update with password=old_password('xxpasswdxx');
It will work.
If you are facing any other problems, paste it.
On Tue, Apr 13, 2010 at 10:37 PM, Pecas On Line wrote:
> Hello,
>
> I need to have a MySQL 3.x client to be compatible with a new MySQL 5.x
> server, is it possible?
>
> What do I need to change on my MySQL 5.x server to allow old clients to
> work?
>
> Also, what do I need to change to allow old PHP 4.x code to talk to a MySQL
> 5.x server?
>
> Thanks,
>
> POL.
>
--
Thanks
Suresh Kuna
MySQL DBA
Re: difference btw Analyze and Optimize table..
Analyze table : Analyze table analyzes and stores the key distribution for a table. For more details check the below URL http://dev.mysql.com/doc/refman/5.1/en/analyze-table.html Optimize table : OPTIMIZE TABLE is useful when we do more deleted operations on a table with variable columns. It will do the defragmentation of the data file and recliam the space. It sorts the indexes and updates the table statistics if it is not. However, the new inserts will reuse the deleted row space. For more details check the below URL http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html On Tue, Apr 13, 2010 at 2:21 PM, F.A.I.Z.A.L wrote: > hi all > > what is the difference between > > OPTIMIZE TABLE tablename; > > and > > ANALYZE TABLE tablename; > > thank you > > Cheers > Faizal S > GSM : 9840118673 > Blog: http://oradbapro.blogspot.com > -- Thanks Suresh Kuna MySQL DBA
Re: How to corrupt a database please???
open the file and remove some data and close it for both data file and index files, So the tables will be corrupted when access. On Sun, Apr 18, 2010 at 9:55 PM, Nurudin Javeri wrote: > Hi all, I am hiring a few new junior DBA's and I want to put them thru a > simple db repair training. Does anyone know how I can deliberately corrupt > a MyISAM and InnoDB database in different ways please? So what I want to do > is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb databases - ALL WITH > DIFFERENT ERROR MESSAGES and have these newbies fix them in a 2 hour period > :-) I have fixed oodles of db's but NEVER thought I would see the say where > I would WANT to corrupt a db on purpose, but that day is here and am looking > for advise please. > > Thanks... > > Nunu > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com > > -- Thanks Suresh Kuna MySQL DBA
Re: do i have to restart the mysql server when i change some global variables?
unless and until if the variable is read-only, you don't need Lin. On Mon, May 31, 2010 at 3:17 PM, Lin Chun wrote: > hi > > as the title > > thanks > > -- > - > Lin Chun > -- Thanks Suresh Kuna MySQL DBA
Re: Replication of MySQL Stored Procedure
SP generally goes as per the database you have created. Set you binlog off while creating for the sql. sql_log_bin is the variable to do it. On Tue, Jun 8, 2010 at 1:01 AM, Sabika Gmail wrote: > I already have mysql in the replicate wild ingore table. I am running mysql > 5.1.40sp1 > > Could it be a bug? > > > On Jun 7, 2010, at 8:30 AM, Rolando Edwards > wrote: > > I think this is normal because stored procedures live in mysql.proc. >> >> You would have to filter out mysql.proc by adding this to /etc/my.cnf >> >> replicate-ignore-table=mysql.proc >> >> Rolando A. Edwards >> MySQL DBA (CMDBA) >> >> 155 Avenue of the Americas, Fifth Floor >> New York, NY 10013 >> 212-625-5307 (Work) >> 201-660-3221 (Cell) >> AIM & Skype : RolandoLogicWorx >> redwa...@logicworks.net >> http://www.linkedin.com/in/rolandoedwards >> >> >> -Original Message- >> From: Sabika Gmail [mailto:sabika.makhd...@gmail.com] >> Sent: Monday, June 07, 2010 11:14 AM >> To: mysql@lists.mysql.com >> Subject: Replication of MySQL Stored Procedure >> >> Hi! >> >> I have a database in the wild ignore table as table.%. Recently I >> created a store procedure on it and it replicated. Does any one know >> if this is normal bahvior? If I wanted to make sure store procedures >> do not replicate, what should I do? >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: >> http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net >> >> > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com > > -- Thanks Suresh Kuna MySQL DBA
Re: Which tool to use for developing Pl/Sql on MySQL?
Go with MySQL Query Browser On Thu, Jul 15, 2010 at 1:14 PM, alba.albetti wrote: > Hi, > beginning to work with MySQL I've found there exist two similar tools for > browsing the db and developing in Pl/Sql and they are SQL-Front and MySQL > Query Browser. Fot not wasting time in learning both can anyone tell me > which of these is generally used wordwide? So I make practice with the most > used one. > > Thanks in advance! > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com > > -- Thanks Suresh Kuna MySQL DBA
Re: InnoDB Tablespace
Hey john, Yes you can add it but safe to keep auto-extend at the end and monitor the disk space as well. "Now when i run the same show table status command, the comment field says: InnoDB free: 6144 kB Is that telling me that I only have 6MB of storage left even though I increased the table space by 8GB?" About the above - it is saying 6144 KB so it is 6.1 GB. On Tue, Aug 3, 2010 at 12:05 AM, Johnny Withers wrote: > I recently ran out of table space on a production server that had the > following configuration line: > > > innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:2G:autoextend:max:8G > > Before I changed this line and restarted the server, I ran SHOW TABLE > STATUS > LIKE 'table' on one of the databases and the comment filed said: > InnoDB Free: 3NNN kB (I don't remember the exact number, but know it > started > with 3 and had 4 digits. > > I modified the configuration line above to: > > > innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:2G:autoextend:max:16G > > Now when i run the same show table status command, the comment field says: > InnoDB free: 6144 kB > > Is that telling me that I only have 6MB of storage left even though I > increased the table space by 8GB? > > Also, If I wanted to add another file to this file_path variable, can I > just > add it to the end like so: > > > innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:16G; > *ibdata7:16G* > > Or will that cause MySQL to complain the file size isn't correct the next > time it starts? > > > Thanks for any help! > > > -- > - > Johnny Withers > 601.209.4985 > joh...@pixelated.net > -- Thanks Suresh Kuna MySQL DBA
Re: InnoDB Tablespace
Hi Johnny, Sorry about that - i just overlooked and the simple way to calculate the sizes is to query the information_schema table called "tables" for data and index sizes. On Tue, Aug 3, 2010 at 8:55 PM, Johnny Withers wrote: > About the above - it is saying 6144 KB so it is 6.1 GB. > > Are you sure? I would think 6144KB = 6.144 MB, or 6144 * 1000 = 6,144,000 > bytes. > > I think since InnoDB, by default, extends the table space by 8MB > increments, this is reporting the free space in this increment. How can I > tell total remaining space so I can adjust and/or add new table space before > it runs out of space next time? > > I have another server with a different config line, however, the last > innodb file specified is also max 16G and when i run show table status on > it, it reports 3983360 kB free, which i would assume is 3.9 GB? Could this > be because it's filling up space in one of the files before the last > auto-extending file, which these files are fixed sizes? > > Both servers are 5.0.77-log. One server is Cent OS, the other is RHEL. > > I'm very confused here. > > JW > > On Mon, Aug 2, 2010 at 1:53 PM, Suresh Kuna wrote: > >> Hey john, >> >> Yes you can add it but safe to keep auto-extend at the end and monitor the >> disk space as well. >> >> >> "Now when i run the same show table status command, the comment field >> says: >> InnoDB free: 6144 kB >> >> Is that telling me that I only have 6MB of storage left even though I >> increased the table space by 8GB?" >> >> About the above - it is saying 6144 KB so it is 6.1 GB. >> >> >> >> On Tue, Aug 3, 2010 at 12:05 AM, Johnny Withers wrote: >> >>> I recently ran out of table space on a production server that had the >>> following configuration line: >>> >>> >>> innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:2G:autoextend:max:8G >>> >>> Before I changed this line and restarted the server, I ran SHOW TABLE >>> STATUS >>> LIKE 'table' on one of the databases and the comment filed said: >>> InnoDB Free: 3NNN kB (I don't remember the exact number, but know it >>> started >>> with 3 and had 4 digits. >>> >>> I modified the configuration line above to: >>> >>> >>> innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:2G:autoextend:max:16G >>> >>> Now when i run the same show table status command, the comment field >>> says: >>> InnoDB free: 6144 kB >>> >>> Is that telling me that I only have 6MB of storage left even though I >>> increased the table space by 8GB? >>> >>> Also, If I wanted to add another file to this file_path variable, can I >>> just >>> add it to the end like so: >>> >>> >>> innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:16G; >>> *ibdata7:16G* >>> >>> Or will that cause MySQL to complain the file size isn't correct the next >>> time it starts? >>> >>> >>> Thanks for any help! >>> >>> >>> -- >>> - >>> Johnny Withers >>> 601.209.4985 >>> joh...@pixelated.net >>> >> >> >> >> -- >> Thanks >> Suresh Kuna >> MySQL DBA >> > > > > -- > - > Johnny Withers > 601.209.4985 > joh...@pixelated.net > -- Thanks Suresh Kuna MySQL DBA
Re: Kill All Proccesses
you can do it by a simple shell script by doing a grep of id's and passing it to mysql. On Wed, Sep 29, 2010 at 8:31 PM, Willy Mularto wrote: > AFAIK mysqladmin just kill a proccess and can not do kill all instances. > > > > sangprabv > sangpr...@gmail.com > http://www.petitiononline.com/froyo/ > > > On Sep 29, 2010, at 9:09 PM, Евгений Килимчук wrote: > > > mysqladmin kill id,id,... > > > > 2010/9/29 Willy Mularto > > Hi, > > I see so many locked tables and can not be unlocked. Is there any single > command or tools to kill all processes? > > > > > > > > > > sangprabv > > sangpr...@gmail.com > > http://www.petitiononline.com/froyo/ > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/mysql?unsub=ekilimc...@gmail.com > > > > > > > > > > -- > > Best regards, > > > > Eugene Kilimchuk > > -- Thanks Suresh Kuna MySQL DBA
Re: Backup
Hey Kranthi, If you have binlogs enabled, do a binary logs backup everyday i.e going to be your everyday backup which consists of the sql modified statements. On Sun, Oct 10, 2010 at 11:13 AM, yung wrote: > 2010/10/10 kranthi : > > > > > > Hi , > > > > My database size is 900GB.i don't want full database backup. I > > need only yesterday backup. how can I take, please help me. > > > > How about the document there: > http://dev.mysql.com/doc/refman/5.1/en/backup-strategy-example.html > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com > > -- Thanks Suresh Kuna MySQL DBA
Re: InnoDB Crash
Hey Willy - Install the new binaries and start mysql with new binary as basedir and see whether innodb has enabled or not. Check the error log why the innodb is getting disabled, make a copy of it here too. On Tue, Oct 12, 2010 at 2:57 PM, Willy Mularto wrote: > Hi List, > Last night accidentally one of my InnoDB table crash. And cause client can > not connect to MySQL, it always said cannot connect to socket, even the > daemon is launched. I tried to set innodb_force_recovery from 0 to 6 and > only number 3 bring back the connection. After that I dump the data and drop > the table. I recreate it as MyISAM and inject the dumped data. After that I > stop MySQL and remove innodb_force_recovery and restart. And clients start > complaining can not connect. Then I enable innodb_force_recovery again. I > tried to create a new InnoDB table test and MySQL complaint cannot create > the table due to the engine type is not supported. The question is how to > solve this problem? How to bring back InnoDB to my server without reinstall > the OS or MySQL itself? I have also tried to drop the database and remove > the data folder from mysql data dir and recreate the database but still no > luck. Thanks for any help. > > > > sangprabv > sangpr...@gmail.com > http://www.petitiononline.com/froyo/ > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com > > -- Thanks Suresh Kuna MySQL DBA
Re: Backing up the InnoDB tables
use xtra backup On Wed, Oct 13, 2010 at 5:37 PM, Tompkins Neil wrote: > Would really appreciate some help or suggestions on this please, if anyone > can assist ? > > Regards > Neil > > -- Forwarded message -- > From: Tompkins Neil > Date: Tue, Oct 12, 2010 at 5:45 PM > Subject: Backing up the InnoDB tables > To: "[MySQL]" > > > Hi > > On a shared MySQL server with access just to my own database, what is the > recommend backup methods and strategies for the InnoDB tables ? > > Cheers > Neil > -- Thanks Suresh Kuna MySQL DBA
Re: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!
Hey Daevid, As this time zone table won't change once it is set up. Do a copy of the table data into another database and give grants to it. On Fri, Oct 15, 2010 at 7:57 PM, Johnny Withers wrote: > I think this is one of those times you would update the mysql.user table > directly, then flush privileges. > > JW > > > On Thu, Oct 14, 2010 at 6:46 PM, Daevid Vincent wrote: > > > I'm pulling my hair out. How do I GRANT the SELECT ability to ANY USER > for > > the very specific mysql.time_zone_name table?? I don't want to GRANT it > to > > every individual user manually, I want one single GRANT that encompasses > > every user simultaneously. > > > > I've tried all of these, and they all are valid in mySQL but none of them > > actually have the desired result. > > > > GRANT SELECT ON `mysql`.`time_zone_name` TO '%'; > > GRANT SELECT ON `mysql`.`time_zone_name` TO '%'@'%'; > > GRANT SELECT ON `mysql`.`time_zone_name` TO ''@'%'; > > GRANT SELECT ON `mysql`.`time_zone_name` TO ''; > > GRANT SELECT ON `mysql`.`time_zone_name`; (this one fails) > > > > Here are the results: > > > > SELECT * FROM mysql.time_zone_name LIMIT 0, 5000 > > > > Error Code : 1142 > > SELECT command denied to user 'daevid'@'mycompany.com' for table > > 'time_zone_name' > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/mysql?unsub=joh...@pixelated.net > > > > > > > -- > - > Johnny Withers > 601.209.4985 > joh...@pixelated.net > -- Thanks Suresh Kuna MySQL DBA
Re: multiple mysql installations - Install mysql from source with a prefix
you can use mysqld_multi On Fri, Oct 15, 2010 at 8:24 PM, Claudio Nanni wrote: > did you remove /etc/my.cnf? > > 2010/10/15 ml ml > > > Hello, > > > > i installed mysql with: > > ./configure --prefix=/usr/local/myprefix/mysql/ && make && make install > > > > (at the point mysql is not running yet) > > > > Next i would like to initialize the DB but get the follwoing error: > > -- > > #:/usr/local/myprefix/mysql# bin/mysql_install_db --user=mysql > > --basedir=/usr/local/myprefix/mysql/ > > --datadir=/usr/local/myprefix/mysql/data > > Installing MySQL system tables... > > 101015 15:13:17 [Warning] option 'thread_stack': unsigned value 65536 > > adjusted to 131072 > > /usr/local/myprefix/mysql//libexec/mysqld: File > > '/var/log/mysql/mysql-bin.index' not found (Errcode: 2) > > 101015 15:13:17 [ERROR] Aborting > > > > 101015 15:13:17 [Note] /usr/local/myprefix/mysql//libexec/mysqld: > > Shutdown complete > > > > > > Installation of system tables failed! Examine the logs in > > /usr/local/myprefix/mysql/data for more information. > > > > > > > > > > > > find /usr/local/myprefix/mysql/data/ > > /usr/local/myprefix/mysql/data/ > > /usr/local/myprefix/mysql/data/mysql > > /usr/local/myprefix/mysql/data/test > > > > --- > > > > And the folder /var/log/mysql/ does not exist. I dont want it to be > > there anyway. > > > > > > Cheers, > > Mario > > > > > > > > On Fri, Oct 15, 2010 at 2:12 PM, Himanshu Raina > > wrote: > > > Hi Mario, > > > > > > While installing a new instance you don't exactly need to mention > > > anything except for the --prefix option. While starting the new > instance > > > you can provide all config parameters like config file, datadir etc. > > > > > > > > > On Fri, 2010-10-15 at 16:47 +0530, ml ml wrote: > > >> Hello List, > > >> > > >> how do i install mysql COMPLETLY in a diffrent directory? > > >> > > >> Right now i am using: > > >> ./configure --prefix=/usr/local/mysql-5.1.42 > > >> --sysconfdir=/usr/local/mysql-5.1.42/etc > > >> > > >> BUT, mysql still looks for /etc/my.cnf and for > > >> /var/log/mysql/mysql-bin.index ... > > >> > > >> So what configure options do i need if i want to install mysql > > >> seperatly in a folder? > > >> Do i also need --datadir? If yes, where sould my data dir be? In > > >> /usr/local/mysql-5.1.42/var? > > >> > > >> Thanks a lot, > > >> Mario > > >> > > > -- > > > Regards, > > > > > > Himanshu Raina > > > -- > > > Guillotine, n.: > > >A French chopping center. > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com > > > > > > > -- > Claudio > -- Thanks Suresh Kuna MySQL DBA
Re: Purposely Corrupting a table
I'll tell a much easier way to corrupt table. Open the data or index file, remove some text data in the file and save. It will show it a corrupt. ( Only for test setups ). On Tue, Oct 19, 2010 at 10:11 PM, Hank wrote: > It's easy to corrupt the MYISAM index (MYI) file... I do something > like this in linux -- assuming your table is not tiny, and mysql isn't > running or you have a lock on the table: > > dd if=table.MYI of=table2.MYI bs=2048 count=100 > > then copy table2.MYI over table.MYI and then "flush tables" and then > unlock. > > Your table will be unreadable until you rebuild the index with REPAIR > TABLE or myisamchk. The MYD file will remain intact. > > If your MYI file is smaller than 200k, then just reduce the count=#. > > -Hank > > > > > On Tue, Oct 19, 2010 at 7:53 PM, Steve Staples > wrote: > > > >> Ok, been googling all morning, and keep getting the same post (on > >> multiple different sites). > >> > >> Is there a way, where i corrupt a table purposely? I've tried playing > >> with the .MYD file, and yeah, it "marks" it deleted under the check > >> routine, but the table is still readable/writable, just doesn't have > >> any info when selecting it... > >> > >> is there another way to corrupt the table, where you can't even select > >> from it, or the responce back from a select is an error? > >> > >> > >> > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com > > -- Thanks Suresh Kuna MySQL DBA
Re: How to install multiple instances in windows
by running it on different ports. On Tue, Oct 19, 2010 at 10:42 PM, kranthi wrote: > Hi all, > >How to install multiple instances in windows??? > > > > Thank you. > > -- Thanks Suresh Kuna MySQL DBA
Re: Issue while SymLinking a Database
Hey Adarsh, If no downtime then the only way is lock with write on the table, move to the new space, create symlink, flush the table. Remember, this symlinks will have issues if you execute any maintenance on these tables which are moved. Better idea - As the tables are MyISAM, move one database dir one at a time by locking all tables and create a symlink for the database folder. On Mon, Jan 3, 2011 at 10:56 AM, Adarsh Sharma wrote: > Dear all, > > I am working on a stable solution for resolving Space Issue of data > directory of MyISAM tables. > We have a table of 70GB in /hdd-1/mysql_data path and there is 10GB space > available space in Hard Disk.Now the table expects to grow upto 150Gb. > > I have some doubts regarding Symlinking a database. The steps involved are > :- > > 1. Shutdown the server. > 2. Create a new directory and move your database to new drive. > 3. Symlink the database in the original directory and change permissions. > > This involves a lot of time to move 70Gb data to new place. > > Well this wouldn't be the perfect solution I'm looking for. > > Is there any particular solution that requires no server shutdown and any > client query operations affected and simply put new data into another > partition. > > Would Partitioning is the only rescue operation? > > Please help me to find a stable and standardized solution. > > > > Thanks & Regards > > Adarsh Sharma > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com > > -- Thanks Suresh Kuna MySQL DBA
Re: Replication issue
Run the change master again to get the relay logs from master server again. On Wed, Feb 16, 2011 at 4:50 PM, Carl wrote: > I am running master - master replication between two locations using MySQL > version 5.1.41 on Slackware Linux 13 (64bit). > > The problem from show slave status is: > > Last_Error: Relay log read failure: Could not parse relay > log event entry. The possible reasons are: the master's binary log is > corrupted (you can check this by running 'mysqlbinlog' on the binary log), > the slave's relay log is corrupted (you can check this by running > 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's > or slave's MySQL code. If you want to check the master's binary log or > slave's relay log, you will be able to know their names by issuing 'SHOW > SLAVE STATUS' on this slave. > Skip_Counter: 1 > Exec_Master_Log_Pos: 552321409 > Relay_Log_Space: 165412833 > Until_Condition: None > Until_Log_File: >Until_Log_Pos: 0 > Master_SSL_Allowed: No > Master_SSL_CA_File: > Master_SSL_CA_Path: > Master_SSL_Cert: >Master_SSL_Cipher: > Master_SSL_Key: >Seconds_Behind_Master: NULL > Master_SSL_Verify_Server_Cert: No >Last_IO_Errno: 1236 >Last_IO_Error: Got fatal error 1236 from master when reading > data from binary log: 'log event entry exceeded max_allowed_packet; Increase > max_allowed_packet on master' > Last_SQL_Errno: 1594 > Last_SQL_Error: Relay log read failure: Could not parse relay > log event entry. The possible reasons are: the master's binary log is > corrupted (you can check this by running 'mysqlbinlog' on the binary log), > the slave's relay log is corrupted (you can check this by running > 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's > or slave's MySQL code. If you want to check the master's binary log or > slave's relay log, you will be able to know their names by issuing 'SHOW > SLAVE STATUS' on this slave. > > I have tried telling it to skip that transaction (set global > sql_slave_skip_counter = 1) to no avail. > > From what I have been able to determine from searching the Internet, it > appears that the replication is failing replicating blobs ahich are > basically jpg's of members. If I understand the problem, it is caused by > blob containing a character which is the same character that is used to mark > the end of a transaction in the bin log. > > My questions: 1) Is this a reasonable/correct analysis and 2) how do I work > around the issue? > > Thanks, > > Carl > > > > > -- Thanks Suresh Kuna MySQL DBA
Re: Mysql issue / crashing
What is the version of MYSQL you are using currently ? On Tue, Apr 19, 2011 at 3:46 PM, Brent Clark wrote: > Ive added > > innodb_force_recovery=4 > > Still no go. > > > Original Message > Subject:Mysql issue / crashing > Date: Tue, 19 Apr 2011 12:15:30 +0200 > From: Brent Clark > To: mysql@lists.mysql.com > > > > Hiya > > Im getting the following > > I ran myisamchk --silent --force */*.MYI > > But still I get the following. > > I cant see how I can bring Mysql up. > > # mysqld > 110419 12:13:22 [Warning] 'for replication startup options' is > deprecated and will be removed in a future release. Please use ''CHANGE > MASTER'' instead. > 110419 12:13:22 [Note] Plugin 'FEDERATED' is disabled. > 110419 12:13:22 InnoDB: Initializing buffer pool, size = 512.0M > 110419 12:13:22 InnoDB: Completed initialization of buffer pool > InnoDB: Log scan progressed past the checkpoint lsn 10 96395066 > 110419 12:13:22 InnoDB: Database was not shut down normally! > InnoDB: Starting crash recovery. > InnoDB: Reading tablespace information from the .ibd files... > InnoDB: Restoring possible half-written data pages from the doublewrite > InnoDB: buffer... > InnoDB: Doing recovery: scanned up to log sequence number 10 96451805 > 110419 12:13:22 InnoDB: Starting an apply batch of log records to the > database... > InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 > 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 > 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 > 65 66 67 68 69 70 110419 12:13:22 - mysqld got signal 11 ; > This could be because you hit a bug. It is also possible that this binary > or one of the libraries it was linked against is corrupt, improperly built, > or misconfigured. This error can also be caused by malfunctioning hardware. > We will try our best to scrape up some info that will hopefully help > diagnose > the problem, but since we have already crashed, something is definitely > wrong > and this may fail. > > key_buffer_size=201326592 > read_buffer_size=2097152 > max_used_connections=0 > max_threads=100 > threads_connected=0 > It is possible that mysqld could use up to > key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = > 606853 K > bytes of memory > Hope that's ok; if not, decrease some variables in the equation. > > Thread pointer: 0x0 > Attempting backtrace. You can use the following information to find out > where mysqld died. If you see no messages after this, something went > terribly wrong... > stack_bottom = (nil) thread_stack 0x2 > mysqld(my_print_stacktrace+0x2d) [0xb75de06d] > mysqld(handle_segfault+0x49c) [0xb72ac0cc] > [0xb7018400] > mysqld(page_cur_parse_insert_rec+0x580) [0xb7500390] > mysqld [0xb74ea325] > mysqld(recv_recover_page+0x502) [0xb74ec2e2] > mysqld(buf_page_io_complete+0x624) [0xb74a22e4] > mysqld(fil_aio_wait+0x12d) [0xb74bdb8d] > mysqld [0xb7533d80] > /lib/i686/cmov/libpthread.so.0 [0xb6ffd4c0] > /lib/i686/cmov/libc.so.6(clone+0x5e) [0xb6ded84e] > The manual page at http://dev.mysql.com/doc/mysql/en/crashing.htmlcontains > information that should help you find out what is causing the crash > > -- Thanks Suresh Kuna MySQL DBA
Re: Mysql issue / crashing
Install the latest version of mysql on top of the current version and start the database. On Tue, Apr 19, 2011 at 9:34 PM, Brent Clark wrote: > Thanks for replying > > 5.1.55 > > > On 19/04/2011 13:55, Suresh Kuna wrote: > > What is the version of MYSQL you are using currently ? > > On Tue, Apr 19, 2011 at 3:46 PM, Brent Clark wrote: > >> Ive added >> >> innodb_force_recovery=4 >> >> Still no go. >> >> >> Original Message >> Subject:Mysql issue / crashing >> Date: Tue, 19 Apr 2011 12:15:30 +0200 >> From: Brent Clark >> To: mysql@lists.mysql.com >> >> >> >> Hiya >> >> Im getting the following >> >> I ran myisamchk --silent --force */*.MYI >> >> But still I get the following. >> >> I cant see how I can bring Mysql up. >> >> # mysqld2912 >> >> 110419 12:13:22 [Warning] 'for replication startup options' is >> deprecated and will be removed in a future release. Please use ''CHANGE >> MASTER'' instead. >> 110419 12:13:22 [Note] Plugin 'FEDERATED' is disabled. >> 110419 12:13:22 InnoDB: Initializing buffer pool, size = 512.0M >> 110419 12:13:22 InnoDB: Completed initialization of buffer pool >> InnoDB: Log scan progressed past the checkpoint lsn 10 96395066 >> 110419 12:13:22 InnoDB: Database was not shut down normally! >> InnoDB: Starting crash recovery. >> InnoDB: Reading tablespace information from the .ibd files... >> InnoDB: Restoring possible half-written data pages from the doublewrite >> InnoDB: buffer... >> InnoDB: Doing recovery: scanned up to log sequence number 10 96451805 >> 110419 12:13:22 InnoDB: Starting an apply batch of log records to the >> database... >> InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 >> 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 >> 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 >> 65 66 67 68 69 70 110419 12:13:22 - mysqld got signal 11 ; >> This could be because you hit a bug. It is also possible that this binary >> or one of the libraries it was linked against is corrupt, improperly >> built, >> or misconfigured. This error can also be caused by malfunctioning >> hardware. >> We will try our best to scrape up some info that will hopefully help >> diagnose >> the problem, but since we have already crashed, something is definitely >> wrong >> and this may fail. >> >> key_buffer_size=201326592 >> read_buffer_size=2097152 >> max_used_connections=0 >> max_threads=100 >> threads_connected=0 >> It is possible that mysqld could use up to >> key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = >> 606853 K >> bytes of memory >> Hope that's ok; if not, decrease some variables in the equation. >> >> Thread pointer: 0x0 >> Attempting backtrace. You can use the following information to find out >> where mysqld died. If you see no messages after this, something went >> terribly wrong... >> stack_bottom = (nil) thread_stack 0x2 >> mysqld(my_print_stacktrace+0x2d) [0xb75de06d] >> mysqld(handle_segfault+0x49c) [0xb72ac0cc] >> [0xb7018400] >> mysqld(page_cur_parse_insert_rec+0x580) [0xb7500390] >> mysqld [0xb74ea325] >> mysqld(recv_recover_page+0x502) [0xb74ec2e2] >> mysqld(buf_page_io_complete+0x624) [0xb74a22e4] >> mysqld(fil_aio_wait+0x12d) [0xb74bdb8d] >> mysqld [0xb7533d80] >> /lib/i686/cmov/libpthread.so.0 [0xb6ffd4c0] >> /lib/i686/cmov/libc.so.6(clone+0x5e) [0xb6ded84e] >> The manual page at http://dev.mysql.com/doc/mysql/en/crashing.htmlcontains >> information that should help you find out what is causing the crash >> >> > > > -- > Thanks > Suresh Kuna > MySQL DBA > > > -- Thanks Suresh Kuna MySQL DBA
Re: Mysql issue / crashing
It will, try it out. On Wed, Apr 20, 2011 at 1:11 PM, Brent Clark wrote: > Sorry > > Would you be so kind as to explain your thinking. > > How would upgrading Mysql fix the issue? > > Regards > Brent Clark > > > On 20/04/2011 06:23, Suresh Kuna wrote: > > Install the latest version of mysql on top of the current version and start > the database. > > On Tue, Apr 19, 2011 at 9:34 PM, Brent Clark wrote: > >> Thanks for replying >> >> 5.1.55 >> >> >> On 19/04/2011 13:55, Suresh Kuna wrote: >> >> What is the version of MYSQL you are using currently ? >> >> On Tue, Apr 19, 2011 at 3:46 PM, Brent Clark >> wrote: >> >>> Ive added >>> >>> innodb_force_recovery=4 >>> >>> Still no go. >>> >>> >>> Original Message >>> Subject:Mysql issue / crashing >>> Date: Tue, 19 Apr 2011 12:15:30 +0200 >>> From: Brent Clark >>> To: mysql@lists.mysql.com >>> >>> >>> >>> Hiya >>> >>> Im getting the following >>> >>> I ran myisamchk --silent --force */*.MYI >>> >>> But still I get the following. >>> >>> I cant see how I can bring Mysql up. >>> >>> # mysqld2912 >>> >>> 110419 12:13:22 [Warning] 'for replication startup options' is >>> deprecated and will be removed in a future release. Please use ''CHANGE >>> MASTER'' instead. >>> 110419 12:13:22 [Note] Plugin 'FEDERATED' is disabled. >>> 110419 12:13:22 InnoDB: Initializing buffer pool, size = 512.0M >>> 110419 12:13:22 InnoDB: Completed initialization of buffer pool >>> InnoDB: Log scan progressed past the checkpoint lsn 10 96395066 >>> 110419 12:13:22 InnoDB: Database was not shut down normally! >>> InnoDB: Starting crash recovery. >>> InnoDB: Reading tablespace information from the .ibd files... >>> InnoDB: Restoring possible half-written data pages from the doublewrite >>> InnoDB: buffer... >>> InnoDB: Doing recovery: scanned up to log sequence number 10 96451805 >>> 110419 12:13:22 InnoDB: Starting an apply batch of log records to the >>> database... >>> InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 >>> 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 >>> 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 >>> 65 66 67 68 69 70 110419 12:13:22 - mysqld got signal 11 ; >>> This could be because you hit a bug. It is also possible that this binary >>> or one of the libraries it was linked against is corrupt, improperly >>> built, >>> or misconfigured. This error can also be caused by malfunctioning >>> hardware. >>> We will try our best to scrape up some info that will hopefully help >>> diagnose >>> the problem, but since we have already crashed, something is definitely >>> wrong >>> and this may fail. >>> >>> key_buffer_size=201326592 >>> read_buffer_size=2097152 >>> max_used_connections=0 >>> max_threads=100 >>> threads_connected=0 >>> It is possible that mysqld could use up to >>> key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = >>> 606853 K >>> bytes of memory >>> Hope that's ok; if not, decrease some variables in the equation. >>> >>> Thread pointer: 0x0 >>> Attempting backtrace. You can use the following information to find out >>> where mysqld died. If you see no messages after this, something went >>> terribly wrong... >>> stack_bottom = (nil) thread_stack 0x2 >>> mysqld(my_print_stacktrace+0x2d) [0xb75de06d] >>> mysqld(handle_segfault+0x49c) [0xb72ac0cc] >>> [0xb7018400] >>> mysqld(page_cur_parse_insert_rec+0x580) [0xb7500390] >>> mysqld [0xb74ea325] >>> mysqld(recv_recover_page+0x502) [0xb74ec2e2] >>> mysqld(buf_page_io_complete+0x624) [0xb74a22e4] >>> mysqld(fil_aio_wait+0x12d) [0xb74bdb8d] >>> mysqld [0xb7533d80] >>> /lib/i686/cmov/libpthread.so.0 [0xb6ffd4c0] >>> /lib/i686/cmov/libc.so.6(clone+0x5e) [0xb6ded84e] >>> The manual page at http://dev.mysql.com/doc/mysql/en/crashing.htmlcontains >>> information that should help you find out what is causing the crash >>> >>> >> >> >> -- >> Thanks >> Suresh Kuna >> MySQL DBA >> >> >> > > > -- > Thanks > Suresh Kuna > MySQL DBA > > > -- Thanks Suresh Kuna MySQL DBA
Re: Mysql issue / crashing
Okie cool, Can you paste the error log details when it came up with force recovery 6. On Wed, Apr 20, 2011 at 6:16 PM, Brent Clark wrote: > On 20/04/2011 10:10, Suresh Kuna wrote: > >> It will, try it out. >> > > Thanks for replying. > > My Colleague and I, we tried a different route. > > We retried innodb_force_recovery. > > But this time we started at 1 and progressed to 6. > > At 6 we were able to able to start working. > > So for our recovery procedure we have opted for mysqldump and reimport. > Dont get me wrong, we know its slower, and may not be bullet proof, but we > are not seeing missing data, but we are reimporting for an extra measure. > > Brent > P.s. The one cool thing is that we have been able to add is > 'innodb_file_per_table'. > > > -- Thanks Suresh Kuna MySQL DBA
Re: LEFT JOIN and WHERE: identical or not and what is better?, etc.
I would go with join rather than where condition. 2011/4/26 Halász Sándor > >>>> 2011/04/25 17:42 +0300, Andre Polykanine >>>> > Here is the first one. > We have two queries: > SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON > `Blogs`.`UserId`=`Users`.`Id`; > and the following one: > SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE > `Blogs`.`UserId`=`Users`.`Id`; > > 1. Are they identical? > 2. Which is better (faster, more optimal, more kosher, I mean, better > style...)? > <<<<<<<< > > >>>> 2011/04/25 10:16 -0500, Johnny Withers >>>> > The only difference once MySQL parses these two queries is the first one is > a LEFT JOIN, which will produce all records from the blogs table even if > there is no matching record in the users table. The second query produces > an > INNER JOIN which means only rows with matching records in both tables will > be returned. > > ... > > I prefer to write the INNER JOIN out though because it leaves my WHERE > clause to do filtering. > <<<<<<<< > and it is usual to write all about the joining in the FROM-clause --the > tables and the criterion for joining them-- and reserve the WHERE-clause for > filtering the result: > > SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId` = > `Users`.`Id` > > SELECT `Blogs`.* FROM `Blogs` JOIN `Users` ON `Blogs`.`UserId` = > `Users`.`Id` > > That is, if you already had a table with the joined outcome, you would use > the WHERE-clause to determine what of it enters into further processing. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com > > -- Thanks Suresh Kuna MySQL DBA
Re: Restore only one database or one table
It really depends on What kind of backup you performed a month ago rather than the type of tables at the moment... On Thu, May 19, 2011 at 6:50 AM, Michael Dykman wrote: > What tables types are you using? If MyISAM, this can be done easily. > If InnoDB it will depend on your settings (file-per-table) > > - michael dykman > > On Tue, May 17, 2011 at 7:11 AM, Adarsh Sharma > wrote: > > Dear all, > > > > I read all the different ways to backp and restore data in mysql. > > Say, i perform a complete backup of all databases 1 month ago > > > > Now, is it possible to restore only a single database from a complete > backup > > file of 250 GB that contains backup of more than 50 databases. > > > > Or if we want to restore only selected tables in a database. > > > > How to do this ? > > > > Is it possible or not. > > > > > > Thanks > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com > > > > > > > > -- > - michael dykman > - mdyk...@gmail.com > > May the Source be with you. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com > > -- Thanks Suresh Kuna MySQL DBA
Re: MyISAM Repair table problem
use repair table table_name use_frm ; and try it out. It needs to be run inside mysql. On Thu, May 19, 2011 at 9:30 AM, Ramesh wrote: > Hi, > > I am trying to repair the table and i got this error > > I tried with myisamchk --rq --tmpdir= /var/lib/mysql/tablog/TabEvents.MYI > > [root@uidsyslog001 mysql]# myisamchk --rq --tmpdir=/var/lib/mysql > /var/lib/mysql/tablog/TabEvents.MYI > check record delete-chain > - recovering (with sort) MyISAM-table /var/lib/mysql/tablog/TabEvents.MYI > Data records: 58354301 > - Fixing index 1 > Wrong bytesec: 0- 0- 0 at 15899573240; Skipped > MyISAM-table '/var/lib/mysql/Syslog/SystemEvents.MYI' is not fixed because > of errors > Try fixing it by using the -safe-recover (-o), the --force (-f) option or > by > not using the --quick (-q) flag > > Then i tried the below one > > [root@> myisamchk --safe-recover --force --tmpdir=/var/lib/mysql > /var/lib/mysql/tablog/TabEvents.MYI > - recovering (with keycache) MyISAM-table '/var/lib/mysql > /var/lib/mysql/tablog/TabEvents.MYI' > Data records: 78918751 > Wrong bytesec: 0- 0- 0 at 15899573240; Skipped > Data records: 82882799 > > What might be the problem and how to make the table repair successfully. > > Guidance needed > > Thanks > Ramesh > -- Thanks Suresh Kuna MySQL DBA
Re: Test mail
Looks like your test succeeded and you win!!! On Thu, May 19, 2011 at 9:41 AM, Adarsh Sharma wrote: > > Hi all, > > Thanks > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com > > -- Thanks Suresh Kuna MySQL DBA
Re: Restore only one database or one table
Try to take a tab separated dump, so you can restore what ever you want in terms of tables or databases. On Thu, May 19, 2011 at 9:53 AM, Adarsh Sharma wrote: > I take a complete backup through mysqldump command. > It includes MyISAM & Innodb tables both. > > But now i am thinking to take backup in compressed format. > > Thanks > > > Suresh Kuna wrote: > > It really depends on What kind of backup you performed a month ago rather > than the type of tables at the moment... > > On Thu, May 19, 2011 at 6:50 AM, Michael Dykman > wrote: > > > > What tables types are you using? If MyISAM, this can be done easily. > If InnoDB it will depend on your settings (file-per-table) > > - michael dykman > > On Tue, May 17, 2011 at 7:11 AM, Adarsh Sharma > > wrote: > > > Dear all, > > I read all the different ways to backp and restore data in mysql. > Say, i perform a complete backup of all databases 1 month ago > > Now, is it possible to restore only a single database from a complete > > > backup > > > file of 250 GB that contains backup of more than 50 databases. > > Or if we want to restore only selected tables in a database. > > How to do this ? > > Is it possible or not. > > > Thanks > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com > > >-- > - michael dykman > - mdyk...@gmail.com > > May the Source be with you. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com > > > > -- Thanks Suresh Kuna MySQL DBA
Re: MyISAM Repair table problem
Run this - repair table SystemEvents use_frm ; On Thu, May 19, 2011 at 10:24 AM, Ramesh wrote: > mysql> repair table SystemEvents.frm; > > +--++--++ > | Table| Op | Msg_type | > Msg_text | > > +--++--++ > | SysEvents.frm | repair | Error| Table 'SysEvents.frm' doesn't exist | > | SysEvents.frm | repair | error| > Corrupt| > > +--++--++ > 2 rows in set (0.00 sec) > > But the SysEvents.frm is there in the datadir. > > > > On 19 May 2011 09:35, Suresh Kuna wrote: > >> use repair table table_name use_frm ; and try it out. >> >> It needs to be run inside mysql. >> >> >> On Thu, May 19, 2011 at 9:30 AM, Ramesh wrote: >> >>> Hi, >>> >>> I am trying to repair the table and i got this error >>> >>> I tried with myisamchk --rq --tmpdir= >>> /var/lib/mysql/tablog/TabEvents.MYI >>> >>> [root@uidsyslog001 mysql]# myisamchk --rq --tmpdir=/var/lib/mysql >>> /var/lib/mysql/tablog/TabEvents.MYI >>> check record delete-chain >>> - recovering (with sort) MyISAM-table >>> /var/lib/mysql/tablog/TabEvents.MYI >>> Data records: 58354301 >>> - Fixing index 1 >>> Wrong bytesec: 0- 0- 0 at 15899573240; Skipped >>> MyISAM-table '/var/lib/mysql/Syslog/SystemEvents.MYI' is not fixed >>> because >>> of errors >>> Try fixing it by using the -safe-recover (-o), the --force (-f) option or >>> by >>> not using the --quick (-q) flag >>> >>> Then i tried the below one >>> >>> [root@> myisamchk --safe-recover --force --tmpdir=/var/lib/mysql >>> /var/lib/mysql/tablog/TabEvents.MYI >>> - recovering (with keycache) MyISAM-table '/var/lib/mysql >>> /var/lib/mysql/tablog/TabEvents.MYI' >>> Data records: 78918751 >>> Wrong bytesec: 0- 0- 0 at 15899573240; Skipped >>> Data records: 82882799 >>> >>> What might be the problem and how to make the table repair successfully. >>> >>> Guidance needed >>> >>> Thanks >>> Ramesh >>> >> >> >> >> -- >> Thanks >> Suresh Kuna >> MySQL DBA >> > > -- Thanks Suresh Kuna MySQL DBA
Re: MyISAM Repair table problem
Can you paste the table files in the datadir and the execution part of the below query. On Thu, May 19, 2011 at 11:11 AM, Ramesh wrote: > > Lost all the records once i done the repair table with use_frm. > > On 19 May 2011 10:30, Suresh Kuna wrote: > >> Run this - repair table SystemEvents use_frm ; >> >> >> On Thu, May 19, 2011 at 10:24 AM, Ramesh wrote: >> >>> mysql> repair table SystemEvents.frm; >>> >>> +--++--++ >>> | Table| Op | Msg_type | >>> Msg_text | >>> >>> +--++--++ >>> | SysEvents.frm | repair | Error| Table 'SysEvents.frm' doesn't exist >>> | >>> | SysEvents.frm | repair | error| >>> Corrupt| >>> >>> +--++--++ >>> 2 rows in set (0.00 sec) >>> >>> But the SysEvents.frm is there in the datadir. >>> >>> >>> >>> On 19 May 2011 09:35, Suresh Kuna wrote: >>> >>>> use repair table table_name use_frm ; and try it out. >>>> >>>> It needs to be run inside mysql. >>>> >>>> >>>> On Thu, May 19, 2011 at 9:30 AM, Ramesh wrote: >>>> >>>>> Hi, >>>>> >>>>> I am trying to repair the table and i got this error >>>>> >>>>> I tried with myisamchk --rq --tmpdir= >>>>> /var/lib/mysql/tablog/TabEvents.MYI >>>>> >>>>> [root@uidsyslog001 mysql]# myisamchk --rq --tmpdir=/var/lib/mysql >>>>> /var/lib/mysql/tablog/TabEvents.MYI >>>>> check record delete-chain >>>>> - recovering (with sort) MyISAM-table >>>>> /var/lib/mysql/tablog/TabEvents.MYI >>>>> Data records: 58354301 >>>>> - Fixing index 1 >>>>> Wrong bytesec: 0- 0- 0 at 15899573240; Skipped >>>>> MyISAM-table '/var/lib/mysql/Syslog/SystemEvents.MYI' is not fixed >>>>> because >>>>> of errors >>>>> Try fixing it by using the -safe-recover (-o), the --force (-f) option >>>>> or by >>>>> not using the --quick (-q) flag >>>>> >>>>> Then i tried the below one >>>>> >>>>> [root@> myisamchk --safe-recover --force --tmpdir=/var/lib/mysql >>>>> /var/lib/mysql/tablog/TabEvents.MYI >>>>> - recovering (with keycache) MyISAM-table '/var/lib/mysql >>>>> /var/lib/mysql/tablog/TabEvents.MYI' >>>>> Data records: 78918751 >>>>> Wrong bytesec: 0- 0- 0 at 15899573240; Skipped >>>>> Data records: 82882799 >>>>> >>>>> What might be the problem and how to make the table repair >>>>> successfully. >>>>> >>>>> Guidance needed >>>>> >>>>> Thanks >>>>> Ramesh >>>>> >>>> >>>> >>>> >>>> -- >>>> Thanks >>>> Suresh Kuna >>>> MySQL DBA >>>> >>> >>> >> >> >> -- >> Thanks >> Suresh Kuna >> MySQL DBA >> > > -- Thanks Suresh Kuna MySQL DBA
Re: MyISAM Repair table problem
The index file will rebuild by using the above command. On Thu, May 19, 2011 at 11:16 AM, Ramesh wrote: > Is that index and all will be fine in that table, or have to create again? > > On 19 May 2011 11:11, Ramesh wrote: > >> >> Lost all the records once i done the repair table with use_frm. >> >> On 19 May 2011 10:30, Suresh Kuna wrote: >> >>> Run this - repair table SystemEvents use_frm ; >>> >>> >>> On Thu, May 19, 2011 at 10:24 AM, Ramesh wrote: >>> >>>> mysql> repair table SystemEvents.frm; >>>> >>>> +--++--++ >>>> | Table| Op | Msg_type | >>>> Msg_text | >>>> >>>> +--++--++ >>>> | SysEvents.frm | repair | Error| Table 'SysEvents.frm' doesn't >>>> exist | >>>> | SysEvents.frm | repair | error| >>>> Corrupt | >>>> >>>> +--++--++ >>>> 2 rows in set (0.00 sec) >>>> >>>> But the SysEvents.frm is there in the datadir. >>>> >>>> >>>> >>>> On 19 May 2011 09:35, Suresh Kuna wrote: >>>> >>>>> use repair table table_name use_frm ; and try it out. >>>>> >>>>> It needs to be run inside mysql. >>>>> >>>>> >>>>> On Thu, May 19, 2011 at 9:30 AM, Ramesh wrote: >>>>> >>>>>> Hi, >>>>>> >>>>>> I am trying to repair the table and i got this error >>>>>> >>>>>> I tried with myisamchk --rq --tmpdir= >>>>>> /var/lib/mysql/tablog/TabEvents.MYI >>>>>> >>>>>> [root@uidsyslog001 mysql]# myisamchk --rq --tmpdir=/var/lib/mysql >>>>>> /var/lib/mysql/tablog/TabEvents.MYI >>>>>> check record delete-chain >>>>>> - recovering (with sort) MyISAM-table >>>>>> /var/lib/mysql/tablog/TabEvents.MYI >>>>>> Data records: 58354301 >>>>>> - Fixing index 1 >>>>>> Wrong bytesec: 0- 0- 0 at 15899573240; Skipped >>>>>> MyISAM-table '/var/lib/mysql/Syslog/SystemEvents.MYI' is not fixed >>>>>> because >>>>>> of errors >>>>>> Try fixing it by using the -safe-recover (-o), the --force (-f) option >>>>>> or by >>>>>> not using the --quick (-q) flag >>>>>> >>>>>> Then i tried the below one >>>>>> >>>>>> [root@> myisamchk --safe-recover --force --tmpdir=/var/lib/mysql >>>>>> /var/lib/mysql/tablog/TabEvents.MYI >>>>>> - recovering (with keycache) MyISAM-table '/var/lib/mysql >>>>>> /var/lib/mysql/tablog/TabEvents.MYI' >>>>>> Data records: 78918751 >>>>>> Wrong bytesec: 0- 0- 0 at 15899573240; Skipped >>>>>> Data records: 82882799 >>>>>> >>>>>> What might be the problem and how to make the table repair >>>>>> successfully. >>>>>> >>>>>> Guidance needed >>>>>> >>>>>> Thanks >>>>>> Ramesh >>>>>> >>>>> >>>>> >>>>> >>>>> -- >>>>> Thanks >>>>> Suresh Kuna >>>>> MySQL DBA >>>>> >>>> >>>> >>> >>> >>> -- >>> Thanks >>> Suresh Kuna >>> MySQL DBA >>> >> >> > -- Thanks Suresh Kuna MySQL DBA
Re: MySQL ignores foreign key constraints
WHat are the table engine types ? On Fri, May 20, 2011 at 4:37 PM, Mimi Cafe wrote: > Hi > > An ideas why MySQL silently ignores any foreign key constraints I define > for > the following tables? > > > mysql> desc book; > > +--+---+--+-+-+- > --+ > | Field| Type | Null | Key | Default | > Extra | > > +--+---+--+-+-+- > --+ > | pkisbn | varchar(20) | NO | PRI | NULL| > | > | fkpublisher_id | tinyint(3) unsigned | NO | MUL | NULL| > | > | title| varchar(50) | NO | | NULL| > | > | subtitle | varchar(50) | NO | | NULL| > | > 13 rows in set (0.01 sec) > > mysql> desc book_author; > +-++--+-+-+---+ > | Field | Type | Null | Key | Default | Extra | > +-++--+-+-+---+ > | fkauthor_id | mediumint(10) unsigned | NO | MUL | NULL| | > | fkisbn | varchar(20)| NO | MUL | NULL| | > +-++--+-+-+---+ > 2 rows in set (0.00 sec) > > mysql> desc author; > > +-++--+-+-+- > ---+ > | Field | Type | Null | Key | Default | Extra > | > > +-++--+-+-+- > ---+ > | pkauthor_id | mediumint(10) unsigned | NO | PRI | NULL| > auto_increment | > | fname | varchar(20)| NO | | NULL| > | > | initial | varchar(5) | YES | | NULL| > | > | lname | varchar(20)| NO | | NULL | > | > > +-++--+-+-+- > ---+ > 4 rows in set (0.00 sec) > > > Mimi > -- Thanks Suresh Kuna MySQL DBA
Re: Query on wait_timeout
Good question Yogesh, I can say the best solution is Create a deadlock and test it, you will come to know more about it. On Wed, Jun 15, 2011 at 3:38 PM, Yogesh Kore wrote: > Hi, > > Small doubt for wait_timeout. > > If my wait_timeout is set for 180 seconds and if any deadlock occures and > both query are waiting to execute. What wil happen in that case? > 1. Do the connection will wait till deadlock is removed or > 2. Connection will close after 180 seconds as both queries are ideal and > waiting for each other. > > Thanks, > Yogesh > -- Thanks Suresh Kuna MySQL DBA
Re: Query on wait_timeout
Try this out:-) Below are the steps to generate a deadlock so that the behaviour of a deadlock can be illustrated: -- 1) Create Objects for Deadlock Example USE TEMPDB CREATE TABLE dbo.foo (col1 INT) INSERT dbo.foo SELECT 1 CREATE TABLE dbo.bar (col1 INT) INSERT dbo.bar SELECT 1 -- 2) Run in first connection BEGIN TRAN UPDATE tempdb.dbo.foo SET col1 = 1 -- 3) Run in second connection BEGIN TRAN UPDATE tempdb.dbo.bar SET col1 = 1 UPDATE tempdb.dbo.foo SET col1 = 1 -- 4) Run in first connection UPDATE tempdb.dbo.bar SET col1 = 1 Connection two will be chosen as the deadlock victim On Thu, Jun 16, 2011 at 10:53 AM, Adarsh Sharma wrote: > How we can create a deadlock manually to test this problem. > > Thanks > > > Suresh Kuna wrote: > >> Good question Yogesh, I can say the best solution is >> >> Create a deadlock and test it, you will come to know more about it. >> >> On Wed, Jun 15, 2011 at 3:38 PM, Yogesh Kore >> wrote: >> >> >> >>> Hi, >>> >>> Small doubt for wait_timeout. >>> >>> If my wait_timeout is set for 180 seconds and if any deadlock occures and >>> both query are waiting to execute. What wil happen in that case? >>> 1. Do the connection will wait till deadlock is removed or >>> 2. Connection will close after 180 seconds as both queries are ideal and >>> waiting for each other. >>> >>> Thanks, >>> Yogesh >>> >>> >>> >> >> >> >> >> > > -- Thanks Suresh Kuna MySQL DBA
Re: Deleting records older than X hours
use event scheduler. On Mon, Aug 1, 2011 at 12:00 PM, hezjing wrote: > Hi > > I want to delete the records which are older than two hours from a table. > > Currently, I have scheduled a cron job script to delete the records every > one hour. I'm wondering if there is a more elegant way of doing this with > out the cron job script? > > > > -- > > Hez > -- Thanks Suresh Kuna MySQL DBA
Re: How to view Query Execution time
Usually, at the end of the query running it displays the time how much it took. Or else enable the profiling and run the query to check the exact time it took for execution at all levels. On Mon, Aug 1, 2011 at 12:11 PM, Adarsh Sharma wrote: > Dear all, > > I want to know how much time did it take to run a sample query. > In postgresql, we enable timing by \timing command. > > Is there is any way to enable in Mysql > > Thanks > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?** > unsub=sureshkumar...@gmail.com<http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com> > > -- Thanks Suresh Kuna MySQL DBA
Re: about the config file
yes. On Tue, Aug 2, 2011 at 3:48 PM, Feng He wrote: > Hello, > > In mysql's config file my.cnf, are the variable names with "_" and "-" the > same? > for example, > > log_error = ... > log-error = ... > > Thanks. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com > > -- Thanks Suresh Kuna MySQL DBA
Re: Reg...My Hung MYSQL instance
Hello Shafi, Can you paste your error log and configuration file with the total memory you have on the server. On Tue, Aug 23, 2011 at 2:04 PM, Andrew Moore wrote: > It will only do what you let it. If your server ui consuming too much > memory > it because you've let it. > On Aug 23, 2011 9:22 AM, "Shafi AHMED" wrote: > > Dear, Today suddenly my database went into hung state due to Out of > Memory > > [ Killed process 1330 (mysqld) ]. > > > > Please advise me folks.This happens now often > > > > Shafi > > > > > > > > > > Get your world in your inbox! > > > > Mail, widgets, documents, spreadsheets, organizer and much more with your > Sifymail WIYI id! > > Log on to http://www.sify.com > > > > ** DISCLAIMER ** > > Information contained and transmitted by this E-MAIL is proprietary to > > Sify Technologies 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 ad...@sifycorp.com > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/mysql?unsub=eroomy...@gmail.com > > > -- Thanks Suresh Kuna MySQL DBA
Re: mysql
tility, e.g. service mysql status > > Since the script you are attempting to invoke has been converted to an > Upstart job, you may also use the status(8) utility, e.g. status mysql > mysql stop/waiting > root@server1:/var/run/mysqld# /etc/init.d/mysql status > Rather than invoking init scripts through /etc/init.d, use the service(8) > utility, e.g. service mysql status > > Since the script you are attempting to invoke has been converted to an > Upstart job, you may also use the status(8) utility, e.g. status mysql > mysql stop/waiting > root@server1:/var/run/mysqld# ps -ef | grep mysql > root 17018 6569 0 12:15 pts/100:00:00 grep --color=auto mysql > root@server1:/var/run/mysqld# ps -ef | grep mysqld > root 17022 6569 0 12:15 pts/100:00:00 grep --color=auto mysqld > > root@server1:/var/run/mysqld# /etc/init.d/mysql start > Rather than invoking init scripts through /etc/init.d, use the service(8) > utility, e.g. service mysql start > > Since the script you are attempting to invoke has been converted to an > Upstart job, you may also use the start(8) utility, e.g. start mysql > mysql stop/post-start, process 19215 >post-start process 19216 > > > root@server1:/var/log/mysql# tail -f error.log > 110823 12:33:00 [Note] Plugin 'FEDERATED' is disabled. > 110823 12:33:00 InnoDB: Started; log sequence number 0 1754746 > 110823 12:33:00 [ERROR] Can't start server: Bind on TCP/IP port: > Cannot assign requested address > 110823 12:33:00 [ERROR] Do you already have another mysqld server > running on port: 3306 ? > 110823 12:33:00 [ERROR] Aborting > > 110823 12:33:00 InnoDB: Starting shutdown... > 110823 12:33:06 InnoDB: Shutdown completed; log sequence number 0 1754746 > 110823 12:33:06 [Note] /usr/sbin/mysqld: Shutdown complete > > 110823 12:33:30 [Note] Plugin 'FEDERATED' is disabled. > 110823 12:33:30 InnoDB: Started; log sequence number 0 1754746 > 110823 12:33:30 [ERROR] Can't start server: Bind on TCP/IP port: > Cannot assign requested address > 110823 12:33:30 [ERROR] Do you already have another mysqld server > running on port: 3306 ? > 110823 12:33:30 [ERROR] Aborting > > 110823 12:33:30 InnoDB: Starting shutdown... > > oot@server1:/var/log/mysql# mysql -uroot -p -h127.0.0.1 -P3306 > Enter password: > ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111) > > > > > > > > > > > > > > On Tue, Aug 23, 2011 at 11:55 AM, Adarsh Sharma > wrote: > > Follow the steps :- > > > > 1. Check mysqld is running or not > > > >> /etc/init.d/mysql status > > > > 2. If not > /etc/init.d/mysql start > > > > If error occurs , check the logs & usually this error means ur server is > not > > running at the moment. > > > > > > Good Luck ! > > > > Claudio Nanni wrote: > >> > >> You have 2 options: use tcp/ip or find the right .sock file > >> > >> use this: > >> mysql -uUSER -p -h127.0.0.1 -P3306 > >> > >> or check in the my.cnf where the server creates the .sock file > >> you have to use the same with the local client. > >> > >> Ciao Mad! > >> > >> Claudio > >> > >> > >> > >> 2011/8/23 Andrew Moore > >> > >> > >>> > >>> That's too bad. How did you configure things? What trouble shooting > have > >>> you > >>> done so far? > >>> On Aug 23, 2011 9:18 AM, "madu...@gmail.com" > wrote: > >>> > >>>> > >>>> When I try to start my mysql DB I keep getting the following message: > >>>> "Can't connect to local MySQL server through socket > >>>> '/var/run/mysqld/mysqld.sock'" > >>>> > >>>> Thanks > >>>> > >>>> -- > >>>> MySQL General Mailing List > >>>> For list archives: http://lists.mysql.com/mysql > >>>> To unsubscribe: > http://lists.mysql.com/mysql?unsub=eroomy...@gmail.com > >>>> > >>>> > >> > >> > >> > >> > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com > > -- Thanks Suresh Kuna MySQL DBA
Re: trying to change wait_timeout
Set the variable wait_timeout=xxx value under the mysqld section of the configuration file and restart the mysqld server. Now check show global variables like 'wait_timeout"; It should be you xxx value what ever you set. On Thu, Sep 8, 2011 at 7:25 PM, Andrew Moore wrote: > Check that you're looking at the variable in the GLOBAL scope not the > SESSION scope. > > SHOW GLOBAL VARIABLE ... > > Andy > > On Thu, Sep 8, 2011 at 11:34 AM, Bruce Ferrell >wrote: > > > On 09/08/2011 02:56 AM, Johan De Meersman wrote: > > > >> - Original Message - > >> > >>> From: "Bruce Ferrell" > >>> To: mysql@lists.mysql.com > >>> Sent: Thursday, 8 September, 2011 3:10:16 AM > >>> Subject: trying to change wait_timeout > >>> > >>> I've read the documentation on MySQL for version 5.1 and it says all > >>> I have to do is to place the following: > >>> wait_timeout=xxx > >>> under [mysqld] > >>> > >> That, and restart the service, of course. You *did* think of restarting > >> the service, I trust? :-p > >> > >> That being said, it is also a dynamic variable, so if you didn't > restart, > >> prefer not to restart *and* are certain your config file is correct; you > can > >> also do "set global wait_timeout=xxx" to have it take effect immediately > for > >> all new sessions. Yes, that means you'll have to disconnect/reconnect to > see > >> the change in your own session. > >> > > > > Good question to ask. Yes, I did restart mysql. Both before and after > > show variables like 'wait_time%' returns 28800. Most confusing. > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/mysql?** > > unsub=eroomy...@gmail.com< > http://lists.mysql.com/mysql?unsub=eroomy...@gmail.com> > > > > > -- Thanks Suresh Kuna MySQL DBA
Re: strange mysql update ..
Nice Rik! On Thu, Sep 8, 2011 at 3:19 PM, Rik Wasmus wrote: > > I fired the update statement in a wrong way ..like this .. > > > > update user_info set login_date='2011-08-05 04:15:05' and user_id > =16078845 > > limit 1 ; > > ( I forgot to use where . instead of where I used and ) > > update user_info set login_date='2011-08-05 04:15:05' where user_id > > =16078845 limit 1 ; ( this is the query intended ) > > > > after the update ..I got this message .. > > mysql> update user_info set login_date='2011-08-05 04:15:05' and user_id > > =16078845 limit 1; > > Query OK, 1 row affected, 1 warning (0.02 sec) > > Rows matched: 1 Changed: 1 Warnings: 0 > > > > It shows that one record is affected and one row changed .. > > I did show warnings ..the output is like this .. > > > > | Warning | 1292 | Truncated incorrect DOUBLE value: '2011-08-05 > 04:15:05' > > > So my question is what happened exactly ? > > Why no records updated ? > > A lot of casting: > > (1) login_date='2011-08-05 04:15:05' and user_id =16078845; > > And implies boolean, so the result is the either true or false. MySQL > doesn't > like using non-numbers as booleans (the '2011-08-05 04:15:05') , this is > the > double spoken of. > > (2) login_date = false (or true, but that doesn't matter) > > But MySQL doesn't know booleans, to a number it is: > > (3) login_date = 0 > > But the column is a DATETIME (or TIMESTAMP) column, 0 or 1 is an incorrect > value, cast to: > > (4) login_date = -00-00 00:00:00 > > So, somewhere there's (or was, may be overwritten) a record with that > value, > just 1 due to the limit 1, otherwise, the whole table would have that as a > login_date (doesn't matter wether it was true or false). > > > Check out: > DB 5.1.58-1-log:(none) mysql> SELECT 1 AND 1; > +-+ > | 1 AND 1 | > +-+ > | 1 | > +-+ > 1 row in set (0.00 sec) > > DB 5.1.58-1-log:(none) mysql> SELECT 0 AND 1; > +-+ > | 0 AND 1 | > +-+ > | 0 | > +-+ > 1 row in set (0.01 sec) > > DB 5.1.58-1-log:(none) mysql> SELECT '1' AND 1; > +---+ > | '1' AND 1 | > +---+ > | 1 | > +---+ > 1 row in set (0.03 sec) > > DB 5.1.58-1-log:(none) mysql> SELECT 'a' AND 1; > +---+ > | 'a' AND 1 | > +---+ > | 0 | > +---+ > 1 row in set, 1 warning (0.03 sec) > > DB 5.1.58-1-log:(none) mysql> SHOW WARNINGS; > +-+--+---+ > | Level | Code | Message | > +-+--+---+ > | Warning | 1292 | Truncated incorrect DOUBLE value: 'a' | > +-+--+---+ > 1 row in set (0.01 sec) > -- > Rik Wasmus > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com > > -- Thanks Suresh Kuna MySQL DBA
Re: MySQL daemons restarting every 7 minutes
Can yo paste the complete error log, Ram memory size and configuration file here and make sure the machine has enough memory to run the services. Check the sys log for what is happening just before the service restart. On Wed, Sep 7, 2011 at 10:51 PM, wrote: > Hi, > > as of yesterday the MySQL Daemons keep restarting every 7 mins or so on > one of my FreeBSD servers. The only work carried out recently related to > MySQL on this server was to temporarily disable replication (its a slave) of > one DB, and then re-enable it (via restore of data and updating the log file > and pos). > Now I keep seeing this: > > 110907 18:03:58 mysqld_safe mysqld restarted > 110907 18:03:58 [Note] Plugin 'FEDERATED' is disabled. > 110907 18:03:58 InnoDB: Initializing buffer pool, size = 2.0G > 110907 18:03:59 InnoDB: Completed initialization of buffer pool > 110907 18:03:59 InnoDB: Started; log sequence number 0 44233 > 110907 18:03:59 [Note] Event Scheduler: Loaded 0 events > 110907 18:03:59 [Note] Slave SQL thread initialized, starting replication > in log 'mysql-bin.002818' at position 46048, relay log > './tau-relay-bin.37' position: 251 > 110907 18:03:59 [Note] /usr/local/libexec/mysqld: ready for connections. > Version: '5.1.58' socket: '/tmp/mysql.sock' port: 3306 FreeBSD port: > mysql-server-5.1.58 > 110907 18:03:59 [Note] Slave I/O thread: connected to master > 'slave@kappa:3306',replication > started in log 'mysql-bin.002818' at position 46048 > > Not seeing any other info, such as why the daemons stopped, just this > repeated over and over... > I didn't find much useful info searching on the internet, came up with > this: > > http://bugs.mysql.com/bug.php?**id=26895<http://bugs.mysql.com/bug.php?id=26895> > > but its an unresolved bug. > > I have tried, restoring all DBs from a working server and that didn't work. > So wouldnt seem to be related to the contents of the databases. > > System is FreeBSD 8.2 amd64, MySQL 5.1.58 (I upgrade this from 5.1.51 as a > try and fix it quick approach but no joy). > > Any one chip in from experience what this may be?? I'm considering > upgrading to 5.5 to give that a try... > > thanks in advance, > > Andy. > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?** > unsub=sureshkumar...@gmail.com<http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com> > > -- Thanks Suresh Kuna MySQL DBA
Re: MySQL daemons restarting every 7 minutes
can you remove it from service and start it normally using mysqld_safe with log warnings enabled in the cnf file. On Fri, Sep 9, 2011 at 4:16 PM, wrote: > Hi, > > that really is the complete error log, that exact same info gets repeated > over and over, there is zero in the syslog and I get this behaviour when > running with no my.cnf (I do obviously have one but I tried without and it I > still see the prob, so that probably makes things easier from a > troubleshooting perspective)... > The system has loads of free RAM (8GB total). > Really the system isn't giving me much to go in in terms of clues.... > > Andy. > > > Quoting Suresh Kuna : > > Can yo paste the complete error log, Ram memory size and configuration >> file >> here and make sure the machine has enough memory to run the services. >> >> Check the sys log for what is happening just before the service restart. >> >> > > > -- Thanks Suresh Kuna MySQL DBA
Re: MySQL daemons restarting every 7 minutes
can you check for any table crashes in the db by using mysqlcheck. and enable the general log for the database. On Fri, Sep 9, 2011 at 10:37 PM, wrote: > No need for that really is there? I posted what was requested. > The part for the shutdown: > > 110909 17:27:31 InnoDB: Starting shutdown... > 110909 17:27:32 InnoDB: Shutdown completed; log sequence number 1589339 > 110909 17:27:32 [Note] /usr/local/libexec/mysqld: Shutdown complete > > 110909 17:27:32 mysqld_safe mysqld from pid file /var/db/mysql/tau.pid > ended > > 110909 17:27:35 mysqld_safe Starting mysqld daemon with databases from > /var/db/mysql > 110909 17:27:35 InnoDB: The InnoDB memory heap is disabled > 110909 17:27:35 InnoDB: Mutexes and rw_locks use GCC atomic builtins > 110909 17:27:35 InnoDB: Compressed tables use zlib 1.2.3 > 110909 17:27:35 InnoDB: Initializing buffer pool, size = 2.0G > 110909 17:27:36 InnoDB: Completed initialization of buffer pool > 110909 17:27:36 InnoDB: highest supported file format is Barracuda. > 110909 17:27:36 InnoDB: Waiting for the background threads to start > 110909 17:27:37 InnoDB: 1.1.8 started; log sequence number 1589339 > 110909 17:27:37 [Note] Slave SQL thread initialized, starting replication > in log 'mysql-bin.002830' at position 293541, relay log > './tau-relay-bin.000920' position: 253 > 110909 17:27:37 [Note] Event Scheduler: Loaded 0 events > 110909 17:27:37 [Note] /usr/local/libexec/mysqld: ready for connections. > Version: '5.5.15' socket: '/tmp/mysql.sock' port: 3306 Source > distribution > 110909 17:27:37 [Note] Slave I/O thread: connected to master > 'slave_user@kappa:3306',**replication started in log 'mysql-bin.002830' at > position 293541 > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?** > unsub=sureshkumar...@gmail.com<http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com> > > -- Thanks Suresh Kuna MySQL DBA
Re: Question about slow storage and InnoDB compression
I would recommend to go for a 15K rpm SSD raid-10 to keep the mysql data and add the Barracuda file format with innodb file per table settings, 3 to 4 GB of innodb buffer pool depending the ratio of myisam v/s innodb in your db. Check the current stats and reduce the tmp and heap table size to a lower value, and reduce the remaining buffer's and cache as well. On Tue, Sep 13, 2011 at 9:06 PM, Maria Arrea wrote: > Hello > > I have upgraded our backup server from mysql 5.0.77 to mysql 5.5.15. We > are using bacula as backup software, and all the info from backups is stored > in a mysql database. Today I have upgraded from mysql 5.0 to 5.5 using IUS > repository RPMS and with mysql_upgrade procedure, no problem so far. This > backup systems hold the bacula daemon, the mysql server and the backup of > other 100 systems (Solaris/Linux/Windows) > > Our server has 6 GB of ram, 1 quad Intel Xeon E5520 and 46 TB of raid-6 > SATA disks (7200 rpm) connected to a Smart Array P812 controller & Red Hat > Enterprise Linux 5.7 x64. Our mysql has dozens of millions of lines, and we > are using InnoDB as storage engine for bacula internal data. We add hundred > of thousands lines /day to our mysql (files are incrementally backed up > daily from our 100 servers). So, we have a 7-8 concurrent writes (in > different lines, of course) , and theorically we only read from mysql when > we restore from backup. > > Daily we launch a cron job that executes an "optimize table" in each table > of our database to compact the database. It takes almost an hour. We are > going to increase the memory of the server from 6 to 12 GB in a couple of > weeks, and I will change my.cnf to reflect more memory. My actual my.cnf is > attached below: > > > These are my questions: > > > - We have real slow storage (raid 6 SATA), but plenty CPU and ram . Should > I enable innodb compression to make this mysql faster? > - This system is IOPS-constrained for mysql (fine for backup, though). > Should I add a SSD only to hold mysql data? > - Any additional setting I should use to tune this mysql server? > > > > my.cnf content: > > [client] > port = 3306 > socket = /var/lib/mysql/mysql.sock > > > [mysqld] > innodb_flush_method=O_DIRECT > max_connections = 15 > wait_timeout = 86400 > port = 3306 > socket = /var/lib/mysql/mysql.sock > key_buffer = 100M > max_allowed_packet = 2M > table_cache = 2048 > sort_buffer_size = 16M > read_buffer_size = 16M > read_rnd_buffer_size = 12M > myisam_sort_buffer_size = 384M > query_cache_type=1 > query_cache_size=32M > thread_cache_size = 16 > query_cache_size = 250M > thread_concurrency = 6 > tmp_table_size = 1024M > max_heap_table = 1024M > > > skip-federated > innodb_buffer_pool_size= 2500M > innodb_additional_mem_pool_size = 32M > > [mysqldump] > max_allowed_packet = 16M > > [mysql] > no-auto-rehash > > [isamchk] > key_buffer = 1250M > sort_buffer_size = 384M > read_buffer = 8M > write_buffer = 8M > > [myisamchk] > key_buffer = 1250M > sort_buffer_size = 384M > read_buffer = 8M > write_buffer = 8M > > [mysqlhotcopy] > interactive-timeout > > > Regards > > Maria > -- Thanks Suresh Kuna MySQL DBA
Re: Question about slow storage and InnoDB compression
Thanks for correcting me in the disk stats Singer, A typo error of SSD instead of SAS 15k rpm. Compression may not increase the memory requirements : To minimize I/O and to reduce the need to uncompress a page, at times the buffer pool contains both the compressed and uncompressed form of a database page. To make room for other required database pages, InnoDB may “evict” from the buffer pool an uncompressed page, while leaving the compressed page in memory. Or, if a page has not been accessed in a while, the compressed form of the page may be written to disk, to free space for other data. Thus, at any given time, the buffer pool may contain both the compressed and uncompressed forms of the page, or only the compressed form of the page, or neither. More details and benefits about the barracuda file format can be found in the below url Which helps to know the pros and cons on file format http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_antelope http://dev.mysql.com/doc/innodb/1.1/en/glossary.html#glos_barracuda http://www.mysqlperformanceblog.com/2008/04/23/real-life-use-case-for-barracuda-innodb-file-format/ http://dev.mysql.com/doc/innodb/1.1/en/innodb-other-changes-file-formats.html I would go with the Singer suggestions in "What you want to do is" part. Thanks Suresh Kuna On Wed, Sep 14, 2011 at 7:21 AM, Singer X.J. Wang wrote: > Comments: > 1) There is no such thing as 15K RPM SSDs... SSDs are NON ROTATIONAL > STORAGE, therefore RPMS make no sense.. > 2) Upgrading to Barracuda file format isn't really worth it in this case, > you're not going to get any real benefits. In your scenario I doubt InnoDB > table compression will help, as it will significantly increase your memory > requirements as it to keep uncompressed and compressed copies in RAM. > > Questions: > 1) Why are you putting your MySQL data on the same volume as your Bacula > backups? Bacula does large sequential I/O and MySQL will do random I/O based > on teh structure. > > What you want to do is: > > 1) you have 5MB InnoDB Log Files, that's a major bottleneck. I would use at > 256MB or 512MB x 2 InnoDB log files. > 2) dump and import the database using innodb_file_per_table so that > optimization will free up space.. > 3) are you running Bacula on the server as well? If so, decrease the buffer > pool to 1-2GB.. if not bump it up to to 3GB as you need some memory for > bacula > > and 4, this is the most important one: > How big is your MySQL data? Its not that big, I figure in the 80-100GB > range. Get yourself a pair of 240GB SSDs, mount it locally for MySQL. > > S > > > > > > On Tue, Sep 13, 2011 at 21:19, Suresh Kuna wrote: > >> I would recommend to go for a 15K rpm SSD raid-10 to keep the mysql data >> and >> add the Barracuda file format with innodb file per table settings, 3 to 4 >> GB >> of innodb buffer pool depending the ratio of myisam v/s innodb in your db. >> Check the current stats and reduce the tmp and heap table size to a lower >> value, and reduce the remaining buffer's and cache as well. >> >> > >> On Tue, Sep 13, 2011 at 9:06 PM, Maria Arrea wrote: >> >> > Hello >> > >> > I have upgraded our backup server from mysql 5.0.77 to mysql 5.5.15. We >> > are using bacula as backup software, and all the info from backups is >> stored >> > in a mysql database. Today I have upgraded from mysql 5.0 to 5.5 using >> IUS >> > repository RPMS and with mysql_upgrade procedure, no problem so far. >> This >> > backup systems hold the bacula daemon, the mysql server and the backup >> of >> > other 100 systems (Solaris/Linux/Windows) >> > >> > Our server has 6 GB of ram, 1 quad Intel Xeon E5520 and 46 TB of raid-6 >> > SATA disks (7200 rpm) connected to a Smart Array P812 controller & Red >> Hat >> > Enterprise Linux 5.7 x64. Our mysql has dozens of millions of lines, and >> we >> > are using InnoDB as storage engine for bacula internal data. We add >> hundred >> > of thousands lines /day to our mysql (files are incrementally backed up >> > daily from our 100 servers). So, we have a 7-8 concurrent writes (in >> > different lines, of course) , and theorically we only read from mysql >> when >> > we restore from backup. >> > >> > Daily we launch a cron job that executes an "optimize table" in each >> table >> > of our database to compact the database. It takes almost an hour. We are >> > going to increase the memory of the server from 6 to 12 GB in a couple >> of >> > weeks, and I will change my.cnf to reflect more memory. My actual my.cnf >> is >
Re: Moving database from one machine to another machine..
If the hardware on master and slave, version of mysql server, configuration and memory allocations are same then you can do a clean shutdown of mysql on slave and copy the files to master. Check if any memories needs to be adjusted and start mysql adding the innodb_file_per_table option on master server. So the table created in future also take the advantage of per table option. On Thu, Sep 15, 2011 at 1:41 AM, Reindl Harald wrote: > > > Am 14.09.2011 22:06, schrieb Prabhat Kumar: > > Hi, > > > > I have 2 machine. Master and a slave replication. > > > > few days back I have switched slave machine (innodb_file_per_table) from > single > > innodb file to one per file table. > > > > Now I want to do for Master. > > > > Now question, > > > > Is it recommendable this method, stop MYSQL services on both and copy > mysql > > file's at system level (using scp or rync) form slave machine to master > > (after deleting ibdata1 and ib_log). update the variable > innodb_file_per_table > > to switch master to one per file table. and start master.. > > > > or I can go with usual process.. export and import > > if you have a consistent mysql-server which can be stopped and the whole > datadir > copied whereever you want this was and will always be the best solution > > said this independent of the software becasue the only interesting fact is > if the can data migrated 100% consistent, every sort of export/import > is per design complexer, slower and maybe unsafer > > > -- Thanks Suresh Kuna MySQL DBA
Re: Moving database from one machine to another machine..
Hi Prabhat, FYI On Thu, Sep 15, 2011 at 10:33 AM, Suresh Kuna wrote: > If the hardware on master and slave, version of mysql server, configuration > and memory allocations are same then you can do a clean shutdown of mysql on > slave and copy the files to master. Check if any memories needs to be > adjusted and start mysql adding the innodb_file_per_table option on master > server. So the table created in future also take the advantage of per table > option. > > > On Thu, Sep 15, 2011 at 1:41 AM, Reindl Harald wrote: > >> >> >> Am 14.09.2011 22:06, schrieb Prabhat Kumar: >> > Hi, >> > >> > I have 2 machine. Master and a slave replication. >> > >> > few days back I have switched slave machine (innodb_file_per_table) from >> single >> > innodb file to one per file table. >> > >> > Now I want to do for Master. >> > >> > Now question, >> > >> > Is it recommendable this method, stop MYSQL services on both and copy >> mysql >> > file's at system level (using scp or rync) form slave machine to master >> > (after deleting ibdata1 and ib_log). update the variable >> innodb_file_per_table >> > to switch master to one per file table. and start master.. >> > >> > or I can go with usual process.. export and import >> >> if you have a consistent mysql-server which can be stopped and the whole >> datadir >> copied whereever you want this was and will always be the best solution >> >> said this independent of the software becasue the only interesting fact is >> if the can data migrated 100% consistent, every sort of export/import >> is per design complexer, slower and maybe unsafer >> >> >> > > > -- > Thanks > Suresh Kuna > MySQL DBA > -- Thanks Suresh Kuna MySQL DBA
Re: Question about slow storage and InnoDB compression
gt; KEY_BLOCK_SIZE=16 | | > | Pool | InnoDB | 10 | Compressed | 8 | 2048 | 16384 | 0 | 16384 | 0 | 9 | > 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=COMPRESSED > KEY_BLOCK_SIZE=16 | | > | Status | InnoDB | 10 | Compressed | 21 | 780 | 16384 | 0 | 0 | 0 | | > 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=COMPRESSED > KEY_BLOCK_SIZE=16 | | > | Storage | InnoDB | 10 | Compressed | 1 | 16384 | 16384 | 0 | 0 | 0 | 2 | > 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=COMPRESSED > KEY_BLOCK_SIZE=16 | | > | UnsavedFiles | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 0 | 0 | 1 > | 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=COMPRESSED > KEY_BLOCK_SIZE=16 | | > | Version | InnoDB | 10 | Compressed | 1 | 16384 | 16384 | 0 | 0 | 0 | | > 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=COMPRESSED > KEY_BLOCK_SIZE=16 | | > > > +++-++---++-+-+--+---++-+-+-+---+--+-+-+ > > > I am still benchmarking, but I see a 15-20% performance gain after > enabling compression using bacula gui (bat). > > Regards > > Maria > > - Original Message - > From: Maria Arrea > Sent: 09/14/11 09:50 AM > To: mysql@lists.mysql.com > Subject: Re: Question about slow storage and InnoDB compression > > The server hosting bacula and the database only has one kind of disk: > SATA, maybe I should buy a couple of SSD for mysql. I have read all your > mails, and still not sure if I should enable innodb compression. My ibfile > is 50 GB, though. Regards Maria Questions: 1) Why are you putting your MySQL > data on the same volume as your Bacula backups? Bacula does large sequential > I/O and MySQL will do random I/O based on teh structure. What you want to do > is: 1) you have 5MB InnoDB Log Files, that's a major bottleneck. I would use > at 256MB or 512MB x 2 InnoDB log files. 2) dump and import the database > using innodb_file_per_table so that optimization will free up space.. 3) are > you running Bacula on the server as well? If so, decrease the buffer pool to > 1-2GB.. if not bump it up to to 3GB as you need some memory for bacula and > 4, this is the most important one: How big is your MySQL data? Its not that > big, I figure in the 80-100GB range. Get yourself a pair of 240GB SSDs, > mount it locally for MySQL. S On Tue, Sep 13, 2011 at 21:19, Suresh Kuna < > sureshkumar...@gmail.com > wrote: I would recommend to go for a 15K rpm > SSD raid-10 to keep the mysql data and add the Barracuda file format with > innodb file per table settings, 3 to 4 GB of innodb buffer pool depending > the ratio of myisam v/s innodb in your db. Check the current stats and > reduce the tmp and heap table size to a lower value, and reduce the > remaining buffer's and cache as well. On Tue, Sep 13, 2011 at 9:06 PM, Maria > Arrea < maria_ar...@gmx.com > wrote: > Hello > > I have upgraded our > backup server from mysql 5.0.77 to mysql 5.5.15. We > are using bacula as > backup software, and all the info from backups is stored > in a mysql > database. Today I have upgraded from mysql 5.0 to 5.5 using IUS > repository > RPMS and with mysql_upgrade procedure, no problem so far. This > backup > systems hold the bacula daemon, the mysql server and the backup of > other > 100 systems (Solaris/Linux/Windows) > > Our server has 6 GB of ram, 1 quad > Intel Xeon E5520 and 46 TB of raid-6 > SATA disks (7200 rpm) connected to a > Smart Array P812 controller & Red Hat > Enterprise Linux 5.7 x64. Our mysql > has dozens of millions of lines, and we > are using InnoDB as storage engine > for bacula internal data. We add hundred > of thousands lines /day to our > mysql (files are incrementally backed up > daily from our 100 servers). So, > we have a 7-8 concurrent writes (in > different lines, of course) , and > theorically we only read from mysql when > we restore from backup. > > Daily > we launch a cron job that executes an "optimize table" in each table > of > our database to compact the database. It takes almost an hour. We are > > going to increase the memory of the server from 6 to 12 GB in a couple of > > weeks, and I will change my.cnf to reflect more memory. My actual my.cnf is > > attached below: > > > These are my questions: > > > - We have real slow > storage (raid 6 SATA), but plenty CPU and ram . Should > I enable innodb > compression to make this mysql faster? > - This system is IOPS-constrain
Re: mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES
Hello Shafi, Adding to Prabhat alternatives, you can use --force to the mysqldump command to ignore the errors and continue taking backup. Regarding the error, we need to check whether the table is present or not and the engine type specifically. Thanks Suresh Kuna On Sat, Sep 24, 2011 at 3:31 AM, Prabhat Kumar wrote: > correct. mysqldump by default has --lock-tables enabled, which means it > tries to lock all tables to be dumped before starting the dump. And doing > LOCK TABLES t1, t2, ... for really big number of tables will inevitably > exhaust all available file descriptors, as LOCK needs all tables to be > opened. > > Workarounds: --skip-lock-tables will disable such a locking completely. > Alternatively, --lock-all-tables will make mysqldump to use FLUSH TABLES > WITH READ LOCK which locks all tables in all databases (without opening > them). In this case mysqldump will automatically disable --lock-tables > because it makes no sense when --lock-all-tables is used. or try with add > --single_transaction to your mysqldump command > > On Fri, Sep 23, 2011 at 9:49 AM, Dan Nelson > wrote: > > > In the last episode (Sep 23), Shafi AHMED said: > > > I have a mysql database of 200G size and the backup fails due to the > > foll. > > > Issue. > > > > > > mysqldump: Got error: 1017: Can't find file: > > > './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES > > > > > > Can someone assist pls.? > > > > $ perror 24 > > OS error code 24: Too many open files > > > > You need to bump up the max files limit in your OS. It may be defaulting > > to > > a small number like 1024. If you can't change that limit, edit your > my.cnf > > and lower the table_open_cache number. You'll lose performance though, > > since mysql will have to stop accessing some tables to open others. > > > > http://dev.mysql.com/doc/refman/5.5/en/not-enough-file-handles.html > > > > -- > >Dan Nelson > >dnel...@allantgroup.com > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com > > > > > > > -- > Best Regards, > > Prabhat Kumar > MySQL DBA > > My Blog: http://adminlinux.blogspot.com > My LinkedIn: http://www.linkedin.com/in/profileprabhat > -- Thanks Suresh Kuna MySQL DBA
Re: Databasename/Tablename is marked as crashed and should be repaired
Hi, The permanent solution is to convert the table into Innodb engine. Thanks Suresh Kuna On Fri, Oct 14, 2011 at 1:00 PM, Mark Goodge wrote: > On 14/10/2011 08:07, James wrote: > >> Hello, >> >> I have the following error on my mysql server log and managed to repaired >> the broken table. However, it keeps occurring by time to time. I am using >> MyISAM storage engine to all database and having some locking table which >> I >> know / aware about the disadvantage of MyISAM. >> >> './Databasename/Tablename' is marked as crashed and should be repaired >> >> Are there any ways to solved permanently? Any advise would be appreciated. >> > > If it's happening repeatedly, and the MySQL server itself is running > without any problems (ie, it isn't crashing and restarting) then you may > have problems with the hardware - with the disk itself. > > Mark > -- > Sent from my Babbage Difference Engine > http://mark.goodge.co.uk > http://www.ratemysupermarket.**com <http://www.ratemysupermarket.com> > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?** > unsub=sureshkumar...@gmail.com<http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com> > > -- Thanks Suresh Kuna MySQL DBA
Re: Additional Software to Download and Install
Visit MySQL Tools Group on LinkedIN to find more details on tools for MySQL. 2011/10/15 Halász Sándor > >>>> 2011/10/14 11:12 -0700, AndrewMcHorney >>>> > I just downloaded the MySql server software. I am now looking for software > that is gui based and will allow me to easily define a database, create > tables and to do updates of records within the tables. It would be fantastic > if the software had report generating capabilities and also would allow me > to create and execute sql commands and to write stored procedures to process > the data. The tables are going to be fairly simple. > <<<<<<<< > Navicat is good for the database work, but not for pretty reports. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com > > -- Thanks Suresh Kuna MySQL DBA
Re: [MYSQL] INTERSECT, MINUS
Hi, EXISTS function provides a simple way to find intersection between tables (INTERSECT operator from relational model). If we have table1 and table2, both having id and value columns, the intersection could be calculated like this: SELECT * FROM table1 WHERE EXISTS(SELECT * FROM table2 WHERE table1.id= table2.id AND table1.value=table2.value) For more details on intersect and minus, check this blog - http://www.bitbybit.dk/carsten/blog/?p=71 Thanks Suresh Kuna On Sun, Oct 16, 2011 at 5:12 PM, Grega Leskovšek wrote: > WHat is wring with the following three sentences? > > SELECT p.name, p.gender, e.pizza FROM Person p, Eats e WHERE p.name = > e.name AND p.gender = 'female' AND (e.pizza = 'mushroom') > INTERSECT > SELECT p.name, p.gender, e.pizza FROM Person p, Eats e WHERE p.name = > e.name AND p.gender = 'female' AND (e.pizza = 'pepperoni'); > > > SELECT * FROM Person MINUS SELECT * FROM Person WHERE name='Amy'; > > > mysql> SELECT name FROM Person MINUS SELECT name FROM Person WHERE > Person.age < > 18; > > I've tried my first time sets and am not sure where is the problem, > I've tried to google but when translating to my db it just doesn't > work, > You can download the create db sql here: > http://s3.amazonaws.com/dbclass-resources/docs/pizza.sql > > ♥♥♥ When the sun rises I receive and when it sets I forgive! ♥♥♥ > ˜♥ -> http://moj.skavt.net/gleskovs/ <- ♥ Always, Grega Leskovšek > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com > > -- Thanks Suresh Kuna MySQL DBA
Re: how to make MyISAM as default engine for MySQL 5.6.4?
Hi, Try the 3 options available in the below URL option, i.e ignore built in innodb and set the default storage engine to another storage engine. http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#option_mysqld_ignore-builtin-innodb Thanks Suresh Kuna MySQL DBA 2011/12/24 Sharl.Jimh.Tsin > hello,all: > today,i download the latest source tarball of MySQL 5.6.x branch,and > build it from source for myself. > first,i configure it with "WITH_INNOBASE_STORAGE_ENGINE:BOOL=OFF" > flag,after that,i start mysqld failed,it returns "[ERROR] > Unknown/unsupported storage engine: InnoDB" error message. > > and i rebuild it with INNODB engine enabled,it works.but when i add > "|ignore-builtin-innodb|" option to my.cnf file,and also make myisam as > the default engine with "default-storage-engine=MyISAM" in my.cnf.the > server start failed again,error is "[ERROR] Unknown/unsupported storage > engine: InnoDB". > > so,i just want to know that how to disable INNODB fully in 5.6.4,and > make myisam default? > > any reply is wanted!! thanks~ > > -- > Best regards, > Sharl.Jimh.Tsin (From China **Obviously Taiwan INCLUDED**) > > Using Gmail? Please read this important notice: > http://www.fsf.org/campaigns/jstrap/gmail?10073. > > -- Thanks Suresh Kuna MySQL DBA
Re: RES: Force drop table
Enable the option innodb_force_recovery =1 in my.cnf file, restart the database, ( can try upto 4 depending on the description below url ) and take the dump of all the innodb tables, remove the ibdata and data file belongs to innodb and re-import. It should be fine. http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html Thanks Suresh Kuna 2012/1/24 Suporte Avanutri > I've tried this before, but the server stills going down. The first error > is always this: > > Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */* FROM 'usuario': Lost > connection to MySQL server during query (2013) > > This is followed by other similar errors: "couldn't execute one thing", > couldn't execute another thing, etc". > > I've got the error while trying to execute this: mysqldump -u USER -pPASS > --force --databases DATABASE (and tried --all-databases too). > > Thanks in advance for the help, guys. I'm starting to learn this thing by > myself, your help has great value to me. > > -Mensagem original- > De: Johan De Meersman [mailto:vegiv...@tuxera.be] > Enviada em: terça-feira, 24 de janeiro de 2012 13:01 > Para: Suporte Avanutri > Cc: Shafi AHMED; mysql@lists.mysql.com > Assunto: Re: RES: Force drop table > > > > - Original Message - > > From: "Suporte Avanutri" > > To: "Shafi AHMED" , mysql@lists.mysql.com > > Sent: Tuesday, 24 January, 2012 3:43:36 PM > > Subject: RES: Force drop table > > > > 120124 12:29:28 InnoDB: Error: table `avanutri/obras` does not exist in > the InnoDB internal > > InnoDB: data dictionary though MySQL is trying to drop it. > > InnoDB: Have you copied the .frm file of the table to the > > InnoDB: MySQL database directory from another database? > > That's a pretty good question it's asking :-) > > Earlier in your log it mentions that InnoDB wasn't shut down properly - > did it crash while you were deleting that table, by any chance? > > Shut the service down, delete the file /avanutri/obras.frm > from disk and restart the service; the table will be gone. There shouldn't > be any other files named obras. if all is well. > > If you can, it is probably also a good idea to make a full dump of all the > databases and reinitialize the InnoDB tablespaces - there may still be > internal references or pages allocated to that table. Check the online > manual for more information on doing that. > > -- > Bier met grenadyn > Is als mosterd by den wyn > Sy die't drinkt, is eene kwezel > Hy die't drinkt, is ras een ezel > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > > -- Thanks Suresh Kuna MySQL DBA
Re: How to interrupt MySQL interpreter output?
login into another session, check the thread id and kill it. ( Kill threadid.) On Fri, Jan 27, 2012 at 3:51 AM, Dotan Cohen wrote: > If I see that a query is taking a long time to finish, how can I > interrupt the MySQL CLI interpreter? Ctrl-C does not work. Thanks. > > -- > Dotan Cohen > > http://gibberish.co.il > http://what-is-what.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > > -- Thanks Suresh Kuna MySQL DBA
Re: Keynote videos from Percona Live MySQL Conference
Thank you Baron, Much appreciated. On Fri, Apr 13, 2012 at 11:32 AM, Baron Schwartz wrote: > If you were not at the Percona Live MySQL Conference over the last few > days, the keynote videos are recorded for your convenience. You can > see them at http://www.percona.tv/ > > Presentations will be posted at http://www.percona.com/live/ as well, > after the speakers submit them to us for posting. I will mention them > when they're ready. > > - Baron > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql > > -- Thanks Suresh Kuna MySQL DBA
Re: [Warning] Aborted connection...... (Got timeout reading communication packets)
Hello Shafi, The below blog will give you more information on the error - http://sureshkuna.blogspot.in/2010/12/aborted-connection-31084472-to-db-ms.html Thanks Suresh Kuna On Mon, May 21, 2012 at 1:15 PM, Shafi AHMED wrote: > Ladies and Gentlemen: > > I am getting below errors and therefore the user sessions terminate causing > business impact...Can some one who is expertise already in this advice at > the earliest? > > > 120513 8:19:45 [Warning] Aborted connection 1167257 to db: 'iib' user: > 'iibuser' host: '210.18.3.94' (Got timeout reading communication packets) > > > OS version: RHEL 5.3 > > DB version: MYSQL 5.1 > > Table involved in the DB is of type : inndoDB > > Background : This is an online exam registration site DB and the concurrent > connex invariably reaches to 200 for 500 users which should not be the > case. > > Ideally the concurrent connex must be <10. > > Normally , we run truncate table before the exam starts up. > > A similar setup(in terms of DB/OS/config etc ) works fine which is > actually > DR at different site. > > PS: Network segment between web and DB tier has been thoroughly checked and > seems to be fine. > > Thanks a ton! > > > Best Rgs, >Shafi AHMED >Sify - Chennai > > > > > > > > Get your world in your inbox! > > Mail, widgets, documents, spreadsheets, organizer and much more with your > Sifymail WIYI id! > Log on to http://www.sify.com > > ** DISCLAIMER ** > Information contained and transmitted by this E-MAIL is proprietary to > Sify Technologies 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 ad...@sifycorp.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > > -- Thanks Suresh Kuna MySQL DBA
Re: [Warning] Aborted connection...... (Got timeout reading communication packets)
This needs to be investigated on the server, and cannot be guessed. On Mon, May 21, 2012 at 2:29 PM, Shafi AHMED wrote: > Sorry- a typo :) > > Hi suresh: I have gone through your blog..and feel it is more generic... > Can you please elaborate why the other setup(DR) works fine when the > similar > prod(with no application code/web/db structural changes etc) has gone thru' > failures with such warning msgs ? > > > Best Rgs, >Shafi AHMED >Sify - Chennai > > > > > -Original Message- > From: Shafi AHMED [mailto:shafi.ah...@sifycorp.com] > Sent: Monday, May 21, 2012 2:04 PM > To: 'Suresh Kuna' > Cc: 'mysql@lists.mysql.com'; 'shafi...@gmail.com' > Subject: RE: [Warning] Aborted connection.. (Got timeout reading > communication packets) > > Hi suresh: I have gone through your blog..and feel it is more generic... > Can you please elaborate why the other setup(DR) works fine when the > similar > prod(with no application code/web/db structural changes etc) has gone thru' > failures with such warning msgs ? > > Thanks again... > > > Best Rgs, >Shafi AHMED >Sify - Chennai > > > > > -Original Message- > From: Suresh Kuna [mailto:sureshkumar...@gmail.com] > Sent: Monday, May 21, 2012 1:29 PM > To: Shafi AHMED > Cc: mysql@lists.mysql.com; shafi...@gmail.com > Subject: Re: [Warning] Aborted connection.. (Got timeout reading > communication packets) > > Hello Shafi, > > The below blog will give you more information on the error - > > http://sureshkuna.blogspot.in/2010/12/aborted-connection-31084472-to-db-ms.h > tml > > Thanks > Suresh Kuna > > On Mon, May 21, 2012 at 1:15 PM, Shafi AHMED > wrote: > > > Ladies and Gentlemen: > > > > I am getting below errors and therefore the user sessions terminate > causing > > business impact...Can some one who is expertise already in this advice at > > the earliest? > > > > > > 120513 8:19:45 [Warning] Aborted connection 1167257 to db: 'iib' user: > > 'iibuser' host: '210.18.3.94' (Got timeout reading communication packets) > > > > > > OS version: RHEL 5.3 > > > > DB version: MYSQL 5.1 > > > > Table involved in the DB is of type : inndoDB > > > > Background : This is an online exam registration site DB and the > concurrent > > connex invariably reaches to 200 for 500 users which should not be the > > case. > > > > Ideally the concurrent connex must be <10. > > > > Normally , we run truncate table before the exam starts up. > > > > A similar setup(in terms of DB/OS/config etc ) works fine which is > > actually > > DR at different site. > > > > PS: Network segment between web and DB tier has been thoroughly checked > and > > seems to be fine. > > > > Thanks a ton! > > > > > > Best Rgs, > >Shafi AHMED > >Sify - Chennai > > > > > > > > > > > > > > > > Get your world in your inbox! > > > > Mail, widgets, documents, spreadsheets, organizer and much more with your > > Sifymail WIYI id! > > Log on to http://www.sify.com > > > > ** DISCLAIMER ** > > Information contained and transmitted by this E-MAIL is proprietary to > > Sify Technologies 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 ad...@sifycorp.com > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/mysql > > > > > > > -- > Thanks > Suresh Kuna > MySQL DBA > > -- Thanks Suresh Kuna MySQL DBA
Re: Understanding Slow Query Log
Disable log-queries-not-using-indexes to log only queries > 100 sec.
Just do "> /var/lib/mysql/slow-queries.log" it will clear the log.
On Sat, Sep 1, 2012 at 12:34 PM, Adarsh Sharma wrote:
> Hi all,
>
> I am using Mysql Ver 14.14 Distrib 5.1.58 in which i enabled slow query log
> by setting below parameters in my.cnf :
>
> log-slow-queries=/usr/local/mysql/slow-query.log
> long_query_time=100
> log-queries-not-using-indexes
>
> I am assuming from the inf. from the internet that long_query_time is in
> seconds , but i see the slow query log , there are lots of statements (
> queries ) :
>
> # User@Host: user1[user1] @ abc.dd.aa.com [192.112.111.111]
> # Query_time: 0.052784 Lock_time: 0.43 Rows_sent: 1 Rows_examined:
> 141145
> SET timestamp=1346409734;
> select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME >
> date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and
> ENTITY_NAME='FETL-ImpressionRC-conversion';
> # Time: 120831 10:43:14
> # User@Host: user1[user1] @ abc.dd.aa.com [192.112.111.111]
> # Query_time: 0.053599 Lock_time: 0.79 Rows_sent: 1 Rows_examined:
> 141145
> SET timestamp=1346409794;
> select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME >
> date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and
> ENTITY_NAME='FETL-click-enhancer-deferred';
> # User@Host: user1[user2] @ abc.dd.aa.com [192.112.111.111]
> # Query_time: 0.054185 Lock_time: 0.86 Rows_sent: 1 Rows_examined:
> 141145
> SET timestamp=1346409794;
> select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME >
> date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and
> ENTITY_NAME='FETL-ImpressionRC-conversion';
> # Time: 120831 10:43:22
> # User@Host: user2[user2] @ abc.dd.aa.com [192.112.111.111]
> # Query_time: 0.000163 Lock_time: 0.45 Rows_sent: 1 Rows_examined: 13
>
>
>
> I don't understand the query time unit in slow query log because i expect
> queries to be logged that takes > 100 s. I tested with sleep command for
> 60s , it doesn't logged in slow query log and when i sleep for 120 s it
> logged but i don't why the other queries are logging in slow log.
>
> # Query_time: 120.000259 Lock_time: 0.00 Rows_sent: 1 Rows_examined:
> 0
> SET timestamp=1346443103;
> SELECT SLEEP(120);
>
> And also my slow log is increasing and decided to purge thorogh below
> command :
>
> cat /dev/null > /var/lib/mysql/slow-queries.log
>
>
> Anyone any ideas about this.
>
>
> Thanks
>
--
Thanks
Suresh Kuna
MySQL DBA
Re: Replication problem
You can paste the show slave status\G here for us to review and on Master, show global variables like 'log-bin%'; show master status ( 3 to 4 times continuously ) On Fri, Aug 29, 2014 at 5:11 PM, wagnerbianchi.com wrote: > Hello guys, some points to check here: > > 1-) Is the master server configured with sync_binlog=1 ? > 2-) About the SHOW SLAVE STATUS output, when slave seems to be just reading > events from master, is the Exec_Master_Log_Pos incrementing or not? > 3-) Why are you reconfiguring all the replication just because the link > went down? > > Cheers, > -- > *WB* > > 2014-08-29 17:46 GMT-03:00 Andrew Moore : > > > Whilst there are a few possibilities, check on the master that your > binary > > logs are being written to. Another possible reason could be filtering. > > On 29 Aug 2014 21:36, "william drescher" > wrote: > > > > > > > > Replication novice > > > > > > I have a master server at the office and a replication server at home. > > > This setup has been working for a couple of years. Occasionally the > > > replication server gets out of sync (usually following a internet > problem > > > and the vpn going down.) > > > I just stop the slave, make sure there is nothing going to the master > > > (when the office is closed), > > > copy the database, > > > transfer the file, > > > load the backup, and > > > start the slave and all is well. > > > > > > This time there was not a communications problem of which I am aware. > > The > > > slave status said the slave_IO_state was "Waiting for master to send > > event" > > > but it was not replicating. > > > > > > I did the usual > > > > > > now it is not updating the replication database (transactions made on > the > > > master do not show on the slave - using phpMyAdmin on both servers) BUT > > > show master status shows the correct log file and the position is > > > incrementing AND show slave status shows the same master log file and > the > > > same position as the master. So, looking at the status info it seems > to > > be > > > running fine, but the transactions do not appear to appear on the > slave. > > > > > > I seek suggestions how to 1) find out what goes wrong when the vpn goes > > > down, and 2) (much more important now) how to find out whether or not > the > > > slave is actually replicating or not. > > > > > > --bill > > > > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe:http://lists.mysql.com/mysql > > > > > > > > > -- -- Thanks Suresh Kuna MySQL Database Consutant & MongoDB DBA Hadoop Admin
Re: deleting big tables
Hi Pau, Would you please paste the timeout error ? If you want to get rid of a table then the recommendation is to drop the table in non-peak hours. Thanks Suresh Kuna On Sat, May 16, 2015 at 2:00 PM, Pau Marc Muñoz Torres wrote: > Hello every body > > i have a big table in my sql server and i want to delete it, it also have > some indexes. I tried to "drop table" and "delete" commands but i > eventually get a time out. Wath can i do with it, does it exist any method > to delete tables quicly? > > i know that drop and delete are not equivalent but i want to get rid of all > information inside > > thanks > > Pau Marc Muñoz Torres > skype: pau_marc > http://www.linkedin.com/in/paumarc > http://www.researchgate.net/profile/Pau_Marc_Torres3/info/ > -- -- -- Thanks Suresh Kuna
Re: deleting big tables
/s, 0.00 non-youngs/s > > Pages read 13648346, created 340720, written 33498386 > > 84.73 reads/s, 0.00 creates/s, 47.49 writes/s > > Buffer pool hit rate 929 / 1000, young-making rate 76 / 1000 not 0 / 1000 > > Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead > > 0.00/s > > LRU len: 5931, unzip_LRU len: 0 > > I/O sum[8119]:cur[172], unzip sum[0]:cur[0] > > -- > > ROW OPERATIONS > > -- > > 0 queries inside InnoDB, 0 queries in queue > > 1 read views open inside InnoDB > > Main thread process no. 1173, id 139714143528704, state: flushing log > > Number of rows inserted 0, updated 0, deleted 74140498, read 74808849 > > 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s > > > > END OF INNODB MONITOR OUTPUT > > > > > > > > Pau Marc Muñoz Torres > > skype: pau_marc > > http://www.linkedin.com/in/paumarc > > http://www.researchgate.net/profile/Pau_Marc_Torres3/info/ > > > > > > 2015-05-17 10:31 GMT+02:00 Pothanaboyina Trimurthy < > > skd.trimur...@gmail.com>: > > > >> Hi Pou, > >> Before killing those connections first check for the undo log entries > >> from the engine innodb status. If there are too many undo log entries it > >> will take some time to clean up those entries. If you force fully kill > >> those connections there are more chances to crash the DB instance. > >> On 17 May 2015 1:54 pm, "Adarsh Sharma" wrote: > >> > >>> Hi Pou, > >>> > >>> This is the reason why your drop commands taking too much time because > >>> they > >>> are in waiting state.Even it is quite surprising to me the purpose of > the > >>> delete command. I would say ,kill all pids ( 37,58,59,66 ) and just > drop > >>> the table ( it will delete everything ). Please take a backup if > needed. > >>> > >>> mysql > drop table ensemblmotive ; > >>> > >>> Thanks, > >>> Adarsh > >>> > >>> > >>> > >>> > >>> On Sun, 17 May 2015 at 13:44 Pau Marc Muñoz Torres > >>> wrote: > >>> > >>> > this is my process list > >>> > > >>> > > >>> > > >>> > > >>> > ++--+---+--+-++-+--+ > >>> > | Id | User | Host | db | Command | Time | > >>> > State | Info | > >>> > > >>> > > >>> > ++--+---+--+-++-+--+ > >>> > | 37 | pau | localhost | UTR | Killed | 260012 | query > >>> > end | delete from ensemblmotive| > >>> > | 58 | pau | localhost | UTR | Query | 81396 | Waiting for table > >>> > metadata lock | drop index iutr on ensemblmotive | > >>> > | 59 | pau | localhost | UTR | Query | 45331 | Waiting for table > >>> > metadata lock | drop table ensemblmotive | > >>> > | 66 | pau | localhost | UTR | Query | 0 | > >>> > NULL| show processlist | > >>> > > >>> > > >>> > ++--+---+--+-++-+--+ > >>> > > >>> > process with id 37 have been there for a long time, i tried to kill > it > >>> and > >>> > drop the table. what can i do? > >>> > > >>> > > >>> > Pau Marc Muñoz Torres > >>> > skype: pau_marc > >>> > http://www.linkedin.com/in/paumarc > >>> > http://www.researchgate.net/profile/Pau_Marc_Torres3/info/ > >>> > > >>> > > >>> > 2015-05-17 7:23 GMT+02:00 Adarsh Sharma : > >>> > > >>> >> Hi Pau, > >>> >> > >>> >> Ideally drop table should not take that much time , you have to > check > >>> if > >>> >> your command is executing or it is in waiting stage. May be you are > >>> not > >>> >> able to get lock on that table. > >>> >> > >>> >> Cheers, > >>> >> Adarsh Sharma > >>> >> > >>> >> > >>> >> On Sat, 16 May 2015 at 23:34 Pau Marc Muñoz Torres < > paum...@gmail.com > >>> > > >>> >> wrote: > >>> >> > >>> >>> Hello every body > >>> >>> > >>> >>> i have a big table in my sql server and i want to delete it, it > also > >>> >>> have > >>> >>> some indexes. I tried to "drop table" and "delete" commands but i > >>> >>> eventually get a time out. Wath can i do with it, does it exist any > >>> >>> method > >>> >>> to delete tables quicly? > >>> >>> > >>> >>> i know that drop and delete are not equivalent but i want to get > rid > >>> of > >>> >>> all > >>> >>> information inside > >>> >>> > >>> >>> thanks > >>> >>> > >>> >>> Pau Marc Muñoz Torres > >>> >>> skype: pau_marc > >>> >>> http://www.linkedin.com/in/paumarc > >>> >>> http://www.researchgate.net/profile/Pau_Marc_Torres3/info/ > >>> >>> > >>> >> > >>> > > >>> > >> > > > -- -- -- Thanks Suresh Kuna
