Re: deleting big tables
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 eddy.ada...@gmail.com 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 paum...@gmail.com 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 eddy.ada...@gmail.com: 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
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 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
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 m...@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 eroomy...@gmail.com: 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 will...@techservsys.com 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: 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 eddy.ada...@gmail.comwrote:
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: [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 shafi.ah...@sifycorp.comwrote: 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 shafi.ah...@sifycorp.comwrote: 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 shafi.ah...@sifycorp.comwrote: 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: Keynote videos from Percona Live MySQL Conference
Thank you Baron, Much appreciated. On Fri, Apr 13, 2012 at 11:32 AM, Baron Schwartz ba...@xaprb.com 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: 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 dotanco...@gmail.com 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: 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 supor...@avanutri.com.br 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 supor...@avanutri.com.br To: Shafi AHMED shafi.ah...@sifycorp.com, 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 mysqldatadir/avanutri/obras.frm from disk and restart the service; the table will be gone. There shouldn't be any other files named obras.something 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 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 amoiz.sh...@gmail.com 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: [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 legr...@gmail.com 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: 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 h...@tbbs.net 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: 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 m...@good-stuff.co.uk 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.comhttp://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- Thanks Suresh Kuna MySQL DBA
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 aim.prab...@gmail.comwrote: 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 dnel...@allantgroup.com 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: 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 h.rei...@thelounge.netwrote: 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 sureshkumar...@gmail.comwrote: 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 h.rei...@thelounge.netwrote: 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
-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-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 = 1024Mskip-federated
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 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-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 w...@singerwang.comwrote: 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 sureshkumar...@gmail.comwrote: 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-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
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, a.sm...@ukgrid.net 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 sureshkumar...@gmail.com: 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, a.sm...@ukgrid.net 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.comhttp://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 eroomy...@gmail.com 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 bferr...@baywinds.org wrote: On 09/08/2011 02:56 AM, Johan De Meersman wrote: - Original Message - From: Bruce Ferrellbferr...@baywinds.org** 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 r...@grib.nl 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, a.sm...@ukgrid.net 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=26895http://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.comhttp://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 eroomy...@gmail.com 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 shafi.ah...@sifycorp.com 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
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 adarsh.sha...@orkash.com 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 eroomy...@gmail.com 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 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: about the config file
yes. On Tue, Aug 2, 2011 at 3:48 PM, Feng He short...@gmail.com 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: Deleting records older than X hours
use event scheduler. On Mon, Aug 1, 2011 at 12:00 PM, hezjing hezj...@gmail.com 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 adarsh.sha...@orkash.comwrote: 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.comhttp://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- 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 adarsh.sha...@orkash.comwrote: 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 yogeshk...@gmail.com 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
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 yogeshk...@gmail.com 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: MySQL ignores foreign key constraints
WHat are the table engine types ? On Fri, May 20, 2011 at 4:37 PM, Mimi Cafe mimic...@googlemail.com 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: 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 mdyk...@gmail.com 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 adarsh.sha...@orkash.com 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 rames...@gmail.com 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 adarsh.sha...@orkash.comwrote: 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 adarsh.sha...@orkash.comwrote: 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 mdyk...@gmail.com mdyk...@gmail.com 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 adarsh.sha...@orkash.com adarsh.sha...@orkash.com 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 rames...@gmail.com 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 sureshkumar...@gmail.com 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 rames...@gmail.com 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 rames...@gmail.com wrote: Lost all the records once i done the repair table with use_frm. On 19 May 2011 10:30, Suresh Kuna sureshkumar...@gmail.com wrote: Run this - repair table SystemEvents use_frm ; On Thu, May 19, 2011 at 10:24 AM, Ramesh rames...@gmail.com 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 sureshkumar...@gmail.com 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 rames...@gmail.com 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 rames...@gmail.com wrote: Is that index and all will be fine in that table, or have to create again? On 19 May 2011 11:11, Ramesh rames...@gmail.com wrote: Lost all the records once i done the repair table with use_frm. On 19 May 2011 10:30, Suresh Kuna sureshkumar...@gmail.com wrote: Run this - repair table SystemEvents use_frm ; On Thu, May 19, 2011 at 10:24 AM, Ramesh rames...@gmail.com 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 sureshkumar...@gmail.com 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 rames...@gmail.com 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: 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 h...@tbbs.net 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: Mysql issue / crashing
It will, try it out. On Wed, Apr 20, 2011 at 1:11 PM, Brent Clark brentgclarkl...@gmail.comwrote: 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 brentgclarkl...@gmail.comwrote: 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 brentgclarkl...@gmail.comwrote: 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 brentgclarkl...@gmail.com 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 brentgclarkl...@gmail.comwrote: 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: Mysql issue / crashing
What is the version of MYSQL you are using currently ? On Tue, Apr 19, 2011 at 3:46 PM, Brent Clark brentgclarkl...@gmail.comwrote: 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 brentgclarkl...@gmail.com 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 brentgclarkl...@gmail.comwrote: 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 brentgclarkl...@gmail.comwrote: 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 brentgclarkl...@gmail.com 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: 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 c...@etrak-plus.com 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: 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 adarsh.sha...@orkash.comwrote: 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: 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 hes...@gmail.com 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 sstap...@mnsi.net 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 kranthikiran@gmail.comwrote: Hi all, How to install multiple instances in windows??? Thank you. -- 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 joh...@pixelated.netwrote: 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 dae...@daevid.com 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 claudio.na...@gmail.comwrote: did you remove /etc/my.cnf? 2010/10/15 ml ml mliebher...@googlemail.com 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 himanshu.ra...@naukri.com 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: Backing up the InnoDB tables
use xtra backup On Wed, Oct 13, 2010 at 5:37 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Would really appreciate some help or suggestions on this please, if anyone can assist ? Regards Neil -- Forwarded message -- From: Tompkins Neil neil.tompk...@googlemail.com Date: Tue, Oct 12, 2010 at 5:45 PM Subject: Backing up the InnoDB tables To: [MySQL] mysql@lists.mysql.com 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: 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 sangpr...@gmail.com 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: 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 inno...@gmail.com wrote: 2010/10/10 kranthi kranthikiran@gmail.com: 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: 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 sangpr...@gmail.com 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 sangpr...@gmail.com 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 ekilimc...@gmail.com -- 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 joh...@pixelated.net 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 sureshkumar...@gmail.comwrote: 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 joh...@pixelated.netwrote: 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: 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 joh...@pixelated.netwrote: 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: 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 alba.albe...@libero.itwrote: 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: 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 sabika.makhd...@gmail.comwrote: 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 redwa...@logicworks.net 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: 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 franks1...@gmail.com wrote: hi as the title thanks -- - Lin Chun -- 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 nsjav...@idh.com 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: 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 pol.cota...@gmail.comwrote:
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 sac.fai...@gmail.com 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
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: 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 fikm...@163.com 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?
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 robert.gill...@basx.com.au 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: 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 ishaq...@yahoo.co.uk wrote: Hi Guys, Which command can allow me view all accounts in a Mysql database -- Thanks Suresh Kuna MySQL DBA
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 muralikrishn...@gmail.comwrote: 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 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 muralikrishn...@gmail.comwrote: 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 muralikrishn...@gmail.comwrote: 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: 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 carlos.pr...@gmail.comwrote: 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 carlos.pr...@gmail.com *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: 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
sq...@dahl-stamnes.netwrote:
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: 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 wagnerbianch...@gmail.comwrote: 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: 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 madu...@gmail.com 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: 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 sureshkumar...@gmail.comwrote: 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: 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 jror...@gmail.com 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 carlos.pr...@gmail.com: 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=database name # 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=database name # 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 manasi.s...@artificialmachines.com 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: 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 sureshkumar...@gmail.com: 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: 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: 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 f...@thefsb.org 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 sorri...@jlab.org 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 sorri...@jlab.org 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: 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 sac.fai...@gmail.com 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: 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 sac.fai...@gmail.com 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 sac.fai...@gmail.com 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 sureshkumar...@gmail.comwrote: 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 sac.fai...@gmail.comwrote: 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: 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 sureshkumar...@gmail.com *To:* Jeetendra Ranjan jeetendra.ran...@sampatti.com *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 sureshkumar...@gmail.comwrote: 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 sureshkumar...@gmail.com *To:* Jeetendra Ranjan jeetendra.ran...@sampatti.com *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 sureshkumar...@gmail.com *To:* Jeetendra Ranjan jeetendra.ran...@sampatti.com *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 sureshkumar...@gmail.comwrote: 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 sureshkumar...@gmail.com *To:* Jeetendra Ranjan jeetendra.ran...@sampatti.com *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
