Re: delete syntax
delete from mysql.user where user='mail_admin'; Krishna On Fri, Dec 2, 2011 at 7:23 AM, Tim Dunphy bluethu...@jokefire.com wrote: hello list, I am attempting to delete a user from the mysql.user table without success. mysql delete from mysql.user where user='mail_admin@%'; Query OK, 0 rows affected (0.00 sec) mysql select user,host from mysql.user where user='mail_admin'; ++---+ | user | host | ++---+ | mail_admin | % | I would appreciate any advice you may have. Regards, Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Backup Policy
xtrabackup, mysqlhotcopy for myisam, incremental backup using zamanda. Krishna On Tue, Mar 15, 2011 at 9:09 PM, petya pe...@petya.org.hu wrote: Hi, What storage engine are you using? Peter Boros On 03/15/2011 02:12 PM, Adarsh Sharma wrote: Dear all, Taking Backup is must needed task in Database Servers. I research a lot and find techniques to perform it in Mysql. We have options RAID, mylvmbackup , mysqldump. But it depends on the company requirement too. We have a database of more than 250GB in mysql database which is increasing day by day. Currently I am using mysqldump utility of MySQL I perform a full backup about 28 days ago. But is there any mechanism or script to backup only the incremental backups on weekly or daily bases. Data is inserted in separate tables in separate databases. We cann't afford to have some proprietary solution. Any suggestions will always Welcome. Thanks best Regards, Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Re: best way to have a unique key
uuid() Krishna On Fri, Jan 21, 2011 at 12:02 AM, Anthony Pace anthony.p...@utoronto.cawrote: Due to certain reasons, the company I am doing business with has decided that the primary key, for an orders table, be a unique key; however, I don't like the possibility of it conflicting if moved to another machine. What are some pitfalls of using a unique key, that is generated by a server side script, rather than by mysql? What are the best ways to do this? Please keep in mind this variable will also be displayed on the customer's Receipt, but again, since it's random, it doesn't have to mean anything. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Re: Large table
partitioning will help u Krishna On Fri, Jan 14, 2011 at 4:18 AM, Sairam Krishnamurthy kmsram...@gmail.comwrote: All, I have a very large table. It has about 1 billion rows. Initially everything was fine. But now the table is a bit slow. Loaded takes a lot of time. I usually load in chunks of 22 million rows. Is the size of the table any way related to the performance? I am not sure about this. Will splitting the table and having a view from multiple table increase the performance? Thanks in advance. -- Thanks, Sairam Krishnamurthy +1 612 859 8161
Re: Error while running Mysql
HI Adarsh, Try memtester command #memtester 5 1 It' a linux tool to check ram for errors. http://krishna-mysql.blogspot.com/2010/12/faulty-physical-ram.html Krishna On Thu, Dec 23, 2010 at 6:08 PM, Adarsh Sharma adarsh.sha...@orkash.comwrote: Dear all, I am able o successfully build Mysql 5.5.8 from its source code on CentOS but when I issued the following command , it doesn't shows the mysql prompt, there is no error in log file. [r...@ws-test mysql-5.5.8]# bin/mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.8 Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Segmentation fault [r...@ws-test mysql-5.5.8]# Don't know what to do.Please help. Thanks Regards Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Re: MySQL Parallel Inserts
Hi Andy, I agree, prefer LOAD DATA INFILE... command for bulk loading. It is 30% faster than normal inserts. Krishna On Tue, Dec 14, 2010 at 8:02 AM, Andy listan...@gmail.com wrote: Greetings everyone. I am in a situation where I need to do parallel inserts into MySQL database from inside my Perl program. Basically, I have several million records to insert into the database, and hence I would rather do them in parallel than doing them one at a time. I looked around but did not find any information on doing this. Does MySQL not support parallel reads/writes? Thanks in advance. Andy
Re: WTA Increasing InnoDB Speed
Hi Willy, Try percona server. It gives better performance than mysql. Krishna On Sat, Oct 23, 2010 at 3:37 AM, Willy Mularto sangpr...@gmail.com wrote: Dear List, I have MySQL 5.14 installed on Dell R710 32GB RAM 600GB SAS HDD with Ubuntu 10.04 64 Bit. I deploy InnoDB as my default engine. The server is a high load server. On a fresh install and empty table it can insert around 5 millions new records per day average. But when the table getting fat the performance starting to drop around 5% per day. It forces me to re create an empty table each month. So how to keep my server has a stable performance? Many thanks for the tips. 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=prajapat...@gmail.com
Re: How to Install mysql from source
Hi, There is a detailed readme (text file) within the file you have downloaded. It will gives you the complete picture. Krishna cgi.com On Mon, Oct 18, 2010 at 9:24 PM, ml ml mliebher...@googlemail.com wrote: Hello List, i am trying to install mysql on debian lenny from source. Here is what i did (yes, i did read the INSTALL-SOURCE): ./configure --prefix=/usr/local/mysql/ make -j 4 make install cp support-files/my-medium.cnf /etc/my.cnf cd /usr/local/mysql chown -R mysql . chgrp -R mysql . bin/mysql_install_db --user=mysql FATAL ERROR: Could not find mysqld The following directories were searched: /usr/libexec /usr/sbin /usr/bin If you compiled from source, you need to run 'make install' to copy the software into the correct location ready for operation. If you are using a binary release, you must either be at the top level of the extracted archive, or pass the --basedir option pointing to that location. == Okay, this my make some sense. Whats the _correct_ way to solve this? Which mysql_install_db options do i need here? bin/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ Installing MySQL system tables... 101018 11:50:13 [ERROR] /usr/local/mysql//libexec/mysqld: unknown option '--skip-bdb' 101018 11:50:13 [ERROR] Aborting 101018 11:50:13 [Note] /usr/local/mysql//libexec/mysqld: Shutdown complete Installation of system tables failed! Examine the logs in /var/lib/mysql for more information. == Why does it also log to /var/lib/mysql ? find /var/lib/mysql/ /var/lib/mysql/ /var/lib/mysql/mysql /var/lib/mysql/test /var/lib/mysql/mysql-bin.index Cheers, Mario -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Re: mysqldump: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES
Hi Pradhan,
Obviously, it should fail. Since you have deleted the root user which is
used by mysqldump for making connection to mysql server for taking backup
Krishna
CGI.COM
On Tue, Oct 19, 2010 at 11:06 AM, Tanmay Pradhan tanma...@gmail.com wrote:
Hi,
I am using the following version of MySQL on my Mac OS X Server 10.5.8:
*** Ver 14.14 Distrib 5.4.1-beta, for apple-darwin9.5.0 (i386) using
readline 5.1 ***
In order to restrict root account login from localhost only, I did the
following:
mysql DELETE FROM user WHERE user = 'root' AND host = '%';
mysql FLUSH PRIVILEGES;
After this,
mysqldump failed with the following error:
$ /usr/local/mysql/bin/mysqldump -h localhost -u root ABC_DATABASE
abc.dump
mysqldump: Got error: 1449: The user specified as a definer
('root'@'%') does not exist when using LOCK TABLES
Even following cmd failed:
$ /usr/local/mysql/bin/mysqldump -h host_name -u user1 -p
ABC_DATABASE abc.dump
mysqldump: Got error: 1045: Access denied for user 'user1'@'IP
Address' (using password: YES) when using LOCK TABLES
Can anybody advise as how to make mysqldump work while restricting
root login access from localhost only?
Thanks for any help.
Regards,
Tanmay
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Re: Purposely Corrupting a table
Hi, Keep key_buffer_size very low and insert lots of data into table (myisam). After some time the index file will crash. Krishna CGI.COM 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=prajapat...@gmail.com
Re: Primary key not unique on InnoDB table
Hi Neil, Yes, primary key is always unique. In your case, you are using composite key (players_id,default_teams_id). _Krishna On Wed, Oct 13, 2010 at 8:07 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: I've the following table. But why isn't the primary key unique, e.g. preventing duplicates if entered ? CREATE TABLE `players_master` ( `players_id` bigint(20) NOT NULL AUTO_INCREMENT, `default_teams_id` bigint(20) NOT NULL, `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, `dob` date NOT NULL, `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL, `retirement_date` date DEFAULT NULL, `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL, `estimated_value` double NOT NULL DEFAULT '0', `contract_wage` double NOT NULL DEFAULT '0', `rating` int(11) NOT NULL, PRIMARY KEY (`players_id`,`default_teams_id`), KEY `FK_players_master_countries_id` (`countries_id`), KEY `FK_players_master_positions_id` (`positions_id`), KEY `IDX_first_name` (`first_name`), KEY `IDX_known_as` (`known_as`), KEY `IDX_second_name` (`second_name`), KEY `IDX_dob` (`dob`), KEY `IDX_estimated_value` (`estimated_value`), KEY `IDX_contract_wage` (`contract_wage`), KEY `IDX_rating` (`rating`), KEY `FK_players_master_teams_id` (`default_teams_id`), CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`) REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`) REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`) REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci I'm confused, I thought primary keys were always unique ? Cheers Neil
Re: Backup
Hi kranthi, Take a look at LVM and xtrabackup. http://www.mysqlperformanceblog.com/2009/02/24/xtrabackup-open-source-alternative-for-innodb-hot-backup-call-for-ideas/ http://marcus.bointon.com/archives/87-MySQL-backups-with-Perconas-XtraBackup.html Krishna CGI (cgi.com) On Sun, Oct 10, 2010 at 10:30 AM, kranthi kranthikiran@gmail.comwrote: 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. Thanks Regards, Kranti
Re: How to extend innodb files?
Hi Vokern, I suggest to have a single ibdata1 file and use *innodb_file_per_table* to have multiple .ibd tables. _Krishna On Tue, Sep 28, 2010 at 11:29 AM, Vokern vok...@gmail.com wrote: Hello, Currently I have the setting: innodb_data_file_path=ibdata1:10G;ibdata2:10G;ibdata3:10G;ibdata4:10G:autoextend Because the last file of ibdata4 is very large (more than 50G), if I want extend the data to more files, for example, ibdata5, ibdata6... how to do it? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Re: How to extend innodb files?
Hi Vokern, On a running MySQL Server enabling *innodb_file_per_table* makes no changes to the existing tables. The newly created table (innodb) will be affected and have thier own .ibd and .frm tables. Although, you can enable smoothly. But it's better to have it from scratch. So, that you can reclaim the free space if required simply by dropping the table. _Krishna On Tue, Sep 28, 2010 at 5:16 PM, Vokern vok...@gmail.com wrote: 2010/9/28 Krishna Chandra Prajapati prajapat...@gmail.com: Hi Vokern, I suggest to have a single ibdata1 file and use innodb_file_per_table to have multiple .ibd tables. Can I upgrade to innodb_file_per_table smoothly? thanks.
Re: MySQL Server has gone away
Hi Jitendra, Check your error log file. Some thing might have gone wrong. Krishna On Wed, Aug 18, 2010 at 9:41 PM, jitendra ranjan jitendra_ran...@yahoo.comwrote: Hi, Whenever i run any commnd on mysql it gives message as below then gives the result successfully. What is the reason of the below error message : ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id:264550 Current database: *** NONE *** Thanks in advance
Re: mysql is crashing
Hi Yectli, In mysql configuration file (my.cnf) you have given key_buffer_size = 5G For 32bit OS it should be less than or equal to 4GB and for 64bit it can be greater than 4GB. Reduce the key_buffer_size to 3GB and try. _Kirshna On Fri, Aug 13, 2010 at 3:02 AM, Yectli Huerta yhue...@msi.umn.edu wrote: Hello, I compiled a version of mysql 5.1.49 and it crashed a couple of times already. I recompiled it with debug enabled and this is the error I got. Do any of you guys/gals have any suggestions? thanks, this is from the trace file: do_select: info: select cond 0xb7e088 do_select: info: select cond 0xb7e088 do_select: info: select cond 0xb7e088 do_select: info: select cond 0xb7e088 do_select: info: select cond 0xb7e088 intern_plugin_unlock: info: unlocking plugin, name= MyISAM, ref_count= 72 ~THD(): info: freeing security context below are the messages from the error log file do_select: info: select cond 0xb7e088 do_select: info: select cond 0x2aabee71ed48 do_select: info: select cond 0xb7e088 do_select: info: select cond 0x2aabee71ed48 do_select: info: select cond 0xb7e088 *** glibc detected *** /opt/mysql/libexec/mysqld: corrupted double-linked list: 0x2aabf301a720 *** === Backtrace: = /lib64/libc.so.6[0x2b4fc154e6de] /lib64/libc.so.6[0x2b4fc154f91d] /lib64/libc.so.6(__libc_free+0x6c)[0x2b4fc154fd5c] /opt/mysql/libexec/mysqld(my_no_flags_free+0x7b)[0x78356b] /opt/mysql/libexec/mysqld(_ZN3THDD0Ev+0x3c1)[0x575dc7] /opt/mysql/libexec/mysqld(_Z10unlink_thdP3THD+0xc7)[0x587dc9] /opt/mysql/libexec/mysqld(_Z29one_thread_per_connection_endP3THDb+0x3f)[0x587e27] /opt/mysql/libexec/mysqld(handle_one_connection+0xc31)[0x58d20f] /lib64/libpthread.so.0[0x2b4fc0f6f193] /lib64/libc.so.6(__clone+0x6d)[0x2b4fc15a3dfd] === Memory map: 0040-00944000 r-xp fd:00 8440267 /opt/mysql-5.1.49/libexec/mysqld 00a44000-00a96000 rw-p 00544000 fd:00 8440267 /opt/mysql-5.1.49/libexec/mysqld 00a96000-00c6c000 rw-p 00a96000 00:00 0 [heap] 4000-40001000 ---p 4000 00:00 0 40001000-40801000 rw-p 40001000 00:00 0 40801000-40802000 ---p 40801000 00:00 0 40802000-40842000 rw-p 40802000 00:00 0 40842000-40843000 ---p 40842000 00:00 0 40843000-40883000 rw-p 40843000 00:00 0 40883000-40884000 ---p 40883000 00:00 0 40884000-408c4000 rw-p 40884000 00:00 0 408c4000-408c5000 ---p 408c4000 00:00 0 408c5000-40905000 rw-p 408c5000 00:00 0 40905000-40906000 ---p 40905000 00:00 0 40906000-40946000 rw-p 40906000 00:00 0 40946000-40947000 ---p 40946000 00:00 0 40947000-40987000 rw-p 40947000 00:00 0 40987000-40988000 ---p 40987000 00:00 0 40988000-409c8000 rw-p 40988000 00:00 0 409c8000-409c9000 ---p 409c8000 00:00 0 409c9000-40a09000 rw-p 409c9000 00:00 0 40a09000-40a0a000 ---p 40a09000 00:00 0 40a0a000-40a4a000 rw-p 40a0a000 00:00 0 2aaad000-2be4c000 r--s fd:04 62914806 /var/run/nscd/passwd 2be4c000-2c334000 r--s fd:04 62914812 /var/run/nscd/group 2c334000-2aabee331000 rw-p 2c334000 00:00 0 2aabee70-2aabee7cb000 rw-p 2aabee70 00:00 0 2aabee7cb000-2aabee80 ---p 2aabee7cb000 00:00 0 2aabee80-2aabf0b36000 rw-p 2aabee80 00:00 0 2aabf1803000-2aabf2804000 rw-p 2aabf1803000 00:00 0 2aabf300-2aabf30e6000 rw-p 2aabf300 00:00 0 2aabf30e6000-2aabf310 ---p 2aabf30e6000 00:00 0 2aabf310-2aabf4aa7000 rw-p 2aabf310 00:00 0 2b4fc0e4d000-2b4fc0e68000 r-xp fd:01 16818349 /lib64/ld-2.4.so 2b4fc0e68000-2b4fc0e6a000 rw-p 2b4fc0e68000 00:00 0 2b4fc0f67000-2b4fc0f69000 rw-p 0001a000 fd:01 16818349 /lib64/ld-2.4.so 2b4fc0f69000-2b4fc0f7d000 r-xp fd:01 16818344 /lib64/ libpthread-2.4.so 2b4fc0f7d000-2b4fc107c000 ---p 00014000 fd:01 16818344 /lib64/ libpthread-2.4.so 2b4fc107c000-2b4fc107e000 rw-p 00013000 fd:01 16818344 /lib64/ libpthread-2.4.so 2b4fc107e000-2b4fc1082000 rw-p 2b4fc107e000 00:00 0 2b4fc1082000-2b4fc1165000 r-xp fd:03 37804570 /usr/lib64/libstdc++.so.6.0.8 2b4fc1165000-2b4fc1265000 ---p 000e3000 fd:03 37804570 /usr/lib64/libstdc++.so.6.0.8 2b4fc1265000-2b4fc126b000 r--p 000e3000 fd:03 37804570 /usr/lib64/libstdc++.so.6.0.8 2b4fc126b000-2b4fc126e000 rw-p 000e9000 fd:03 37804570 /usr/lib64/libstdc++.so.6.0.8 2b4fc126e000-2b4fc128 rw-p 2b4fc126e000 00:00 0 2b4fc128-2b4fc128d000 r-xp fd:01 16818324 /lib64/libgcc_s.so.1 2b4fc128d000-2b4fc138c000 ---p d000 fd:01 16818324 /lib64/libgcc_s.so.1 2b4fc138c000-2b4fc138d000 rw-p c000 fd:01 16818324 /lib64/libgcc_s.so.1 2b4fc138d000-2b4fc138e000 rw-p 2b4fc138d000 00:00 0 2b4fc138e000-2b4fc13e2000 r-xp fd:01 16818414 /lib64/libm-2.4.so 2b4fc13e2000-2b4fc14e1000 ---p 00054000 fd:01 16818414 /lib64/libm-2.4.so 2b4fc14e1000-2b4fc14e3000 rw-p 00053000 fd:01 16818414 /lib64/libm-2.4.so 2b4fc14e3000-2b4fc1619000 r-xp fd:01 16818318 /lib64/libc-2.4.so 2b4fc1619000-2b4fc1719000 ---p 00136000 fd:01 16818318 /lib64/libc-2.4.so
Re: mysql is crashing
Hi Yectli, I am talking about mysql server. Is it 32bit or 64bit. Provide some additional information. mysql configuration file, show variables, Error log file details etc. Kirshna On Fri, Aug 13, 2010 at 8:36 PM, Yectli Huerta yhue...@msi.umn.edu wrote: On Fri, Aug 13, 2010 at 04:33:38PM +0530, Krishna Chandra Prajapati wrote: Hi Yectli, In mysql configuration file (my.cnf) you have given key_buffer_size = 5G For 32bit OS it should be less than or equal to 4GB and for 64bit it can be greater than 4GB. Reduce the key_buffer_size to 3GB and try. _Kirshna Hello, thanks for the tip. It is a 64 bit executable so 5gb should be OK. It is running on a x86_64 SLES 10.3 server. Then your mysql server should also be 64 bit. Please Check. -- Yectli
Re: phpMyAdmin and other management tools
Hi Dave, I recommend you to use Toad for MySQL. It has wizard for query creation phpMyAdimn, mysql workbench, sqlyog lack this feature. Krishna On Thu, Jul 1, 2010 at 5:08 PM, David Stoltz dsto...@shh.org wrote: Hi Folks, I'm currently using phpMyAdmin to manage the mySQL databases. I'm wondering what most people like to use? I know there is mySQL Workbench, which I haven't really fooled with yet Can anyone comment on what they use and why? Thanks! Dave
Re: Table creation fail
Hi, default cannot be used with primary key. mysql CREATE TABLE `testresults` ( - - `id_employees` INTEGER DEFAULT NULL , - `id_test_test` INTEGER DEFAULT NULL , - `testdate` DATE DEFAULT NULL , - `result` VARCHAR( 10 ) DEFAULT NULL , - `resultsdescription` MEDIUMTEXT DEFAULT NULL , - `resultsdate` DATE DEFAULT NULL , - `nextdate` DATE DEFAULT NULL , - `ptlevel` VARCHAR( 10 ) DEFAULT NULL , - `givenhere` INTEGER not null primary key); Query OK, 0 rows affected (0.11 sec) Krishna On Wed, Jun 23, 2010 at 9:51 PM, David Stoltz dsto...@shh.org wrote: Hi Folks, I use an online SQL Design tool to design my tables, etc. This generates script code that I can run in phpMySQL to create the tables, etc. The below code is causing an error - see below: CREATE TABLE `testresults` ( `id_employees` INTEGER DEFAULT NULL , `id_test_test` INTEGER DEFAULT NULL , `testdate` DATE DEFAULT NULL , `result` VARCHAR( 10 ) DEFAULT NULL , `resultsdescription` MEDIUMTEXT DEFAULT NULL , `resultsdate` DATE DEFAULT NULL , `nextdate` DATE DEFAULT NULL , `ptlevel` VARCHAR( 10 ) DEFAULT NULL , `givenhere` INTEGER DEFAULT NULL , PRIMARY KEY ( ) ); MySQL said: #1064 - 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 ') )' at line 11 The bolded line above (resultsdate line) seems to be causing the problemnothing looks wrong to me Any ideas? Thanks!
Re: Best way to purge old records from a huge table?
Hi Brian, I would suggest you to use mk-archiver (Maatkit Tools) for this activity. http://www.percona.com/files/presentations/Make_Life_Easier_Maatkit_v2.pdf Regards, Krishna On Fri, Jun 4, 2010 at 8:40 PM, Brian Dunning br...@briandunning.comwrote: Hey all - I have a table with 12,000,000 records spread over about 6 years. I'm trying to delete all but the last 2 years, but no matter how small of a group I try to delete at a time, it keeps hanging up the server and I eventually have to restart MySQL. The table looks like this: `creation` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `lat` double NOT NULL default '0', `lon` double NOT NULL default '0', `referer` int(12) NOT NULL default '0', PRIMARY KEY (`referer`,`lat`,`lon`), KEY `creation` (`creation`,`referer`) And the query I've been trying looks like this: delete from tablename where `creation` '2006-04-01 00:00:00' ...trying to do the oldest 1 month of records at a time. So am I just trying a really inefficient query? Is there a better way to do this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Re: large table issue
Hi, MySQL Partitioning will help you a lot. Try it. Regards, Krishna 2010/6/2 曹凯 tx...@hotmail.com Hi all, do you guys know how to deal with the large tables? here's my problem: I have two web servers( running Nginx ) , two DB servers( running MySQL 5.1.35 ) and a server for load balancing. What I'm maintaining is a game data tracking system. There's a game_log table which will record all detail info from many games. here's the structure: `game_log_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `game_id` int(10) unsigned NOT NULL, `event_id` int(10) unsigned NOT NULL, `player_id` int(10) unsigned NOT NULL, `session_id` varchar(128) NOT NULL COMMENT 'flash session id', `score` int(10) unsigned DEFAULT NULL, `handle_statu` int(1) unsigned NOT NULL DEFAULT '1' COMMENT '1:not handle 2:been handle', `game_end` bigint(20) DEFAULT NULL, `game_start` bigint(20) unsigned NOT NULL DEFAULT '0', `event_time` float DEFAULT '0', PRIMARY KEY (`game_log_id`), KEY `game_id` (`game_id`), KEY `event_id` (`event_id`), KEY `player_id` (`player_id`) it currently has about 1220 records( 2 or 3 of the other tables have around a million records for each ). now, it's very slow to query this table even I just query this single table. most of the time it failed. do you guys know what the problem is? or how to make it more efficient and faster? thanks in advance CK _ 一张照片的自白――Windows Live照片的可爱视频介绍 http://windowslivesky.spaces.live.com/blog/cns!5892B6048E2498BD!889.entryhttp://windowslivesky.spaces.live.com/blog/cns%215892B6048E2498BD%21889.entry
Re: Replication : request DELETE is not executed on slave
Hi dflorella, The important thing about mysql replication is same mysql version for both master as well as slave should be used. It should be taken as good practice. You need to check that master and slave are in sync. Is there any error (replication) on the slave server. Check the mode, strict or some thing else. Does the delete command exits in binlog. Regards, Krishna On Wed, Apr 28, 2010 at 2:37 PM, David Florella dflore...@legos.fr wrote: Hi, I am using MySQL replication : - The version of the master is 4.1.12-log - The version of the slave is 5.0.41 When I use the query '' DELETE QUICK FROM [TABLE] WHERE [field] 'xx' LIMIT 7500, the query is executed on the master but not on the slave. Do you know why the request is not executed on the slave? Regards, David.
Re: Mysql - Tables Export to Excel!
Hi Vikram, You can use toad for mysql ( It's a free tool) to export table structure or data. Even you can use mysqldump also. Krishna On Thu, Apr 8, 2010 at 10:42 AM, Vikram A vikkiatb...@yahoo.in wrote: Hi, I would like to export my table structure from MYSQL from a particular db. Is there any tool for doing this? Please guide me. Thank you VIKRAM A
Re: Can't drop index
Hi Neil, Just check it up that any active transaction is still there in innodb internal data dictionary. Execute show engine innodb status\G Check for active transaction related to the current table. If it's there kill that transaction id and try again. _Krishna On Fri, Apr 2, 2010 at 10:35 PM, Neil Aggarwal n...@jammconsulting.comwrote: Hello: When I try to drop an index from an InnoDB table: drop index company_n56 on company; I get this error: ERROR 1025 (HY000): Error on rename of './thymeleweb/#sql-788_1218' to './thymeleweb/company' (errno: 150) Look at the index using: show keys from company\G gives me this info for that key: Table: company Non_unique: 1 Key_name: company_n56 Seq_in_index: 1 Column_name: client_manager_person_id_oid Collation: A Cardinality: 2 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: The field is a varchar field that holds the id of a record in the person table: Field: client_manager_person_id_oid Type: varchar(256) Null: YES Key: MUL Default: NULL Extra: When I look at the person table, I do not see an index that would refer back to this table. Any ideas why I can't drop this index? Thanks, Neil -- Neil Aggarwal, (281)846-8957 MySQL pre-installed on a virtual private server for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Tokutek Acquires Oracle
Hi guys, Is the information is true. http://planet.mysql.com/ http://tokutek.com/2010/04/tokutek-acquires-oracle/ Regards, Krishna
Re: Replication - LINUX to WIN
Yes, go ahead. -Krishna On Thu, Mar 18, 2010 at 2:03 PM, Vikram A vikkiatb...@yahoo.in wrote: Hi, I have done replication with Win to Win servers with mysql version 5.0.41-community-nt. Now, We have Enterprise Linux(Red Hat 4.1.2-14) and windows server2003. Is it possible do the replication LINUX(Master) to WINDOWS SERVER2003(Slave) ? It will be great help to me. Thank you. VIKRAM A The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
Re: MySQL Storage Engine
Hi Neil, Recommend : Innodb storage engine Even, i would suggest inner join select query (if report query might takes few seconds) should be executed on slave. Before doing any thing. Plan out all the requirement. Regards, Krishna On Thu, Mar 18, 2010 at 2:48 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi I'm currently looking to develop an on line web application - which is going to be used by around 200+ concurrent users at any one time. Can you recommend to me the best/preferred MySQL database engine to use for example MyISAM ? Also, in your experiences, do you recommend that SELECT statements containing a number of INNER JOINS should be execute from a Stored procedure or purely from the webpage ? Cheers Neil
Re: MySQL Storage Engine
Hi Neil, Many times we need to generate reports (weekly, montly, yearly) from the data we have. For detailed reports we have to use joins on many tables. So, it takes time from 2 to 5. So these types of activities must be performed on slave server. If you need reports then you should have master - slave replication. http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html _Krishna On Thu, Mar 18, 2010 at 3:11 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi How do you mean executed on slave ? Neil On Thu, Mar 18, 2010 at 9:32 AM, Krishna Chandra Prajapati prajapat...@gmail.com wrote: Hi Neil, Recommend : Innodb storage engine Even, i would suggest inner join select query (if report query might takes few seconds) should be executed on slave. Before doing any thing. Plan out all the requirement. Regards, Krishna On Thu, Mar 18, 2010 at 2:48 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi I'm currently looking to develop an on line web application - which is going to be used by around 200+ concurrent users at any one time. Can you recommend to me the best/preferred MySQL database engine to use for example MyISAM ? Also, in your experiences, do you recommend that SELECT statements containing a number of INNER JOINS should be execute from a Stored procedure or purely from the webpage ? Cheers Neil
Re: mysql proxy in production?
Hi Walter, I have tried and tested haproxy (ver 1.3). But it doesn't work to my expectation. May be newer version (1.4) works better. Brent try and check http://sqlrelay.sourceforge.net/sqlrelay/ http://www.cherokee-project.com/ http://www.alobbs.com/1344/MySQL_asynchronous_balancing_with_HTTP_JSON.html Thanks, Krishna On Fri, Mar 12, 2010 at 10:32 AM, Walter Heck - OlinData.com li...@olindata.com wrote: Take a look at haProxy. It can be combined with some scripts to loadbalance mysql. http://www.alexwilliams.ca/blog/2009/08/10/using-haproxy-for-mysql-failover-and-redundancy/ We use it at Open Query for a similar case. Cheers, Walter Heck Engineer @ Open Query http://openquery.com | http://openquery.com/blog | On Fri, Mar 12, 2010 at 02:09, John Daisley daisleyj...@googlemail.com wrote: Has MySQL Proxy been improved since that article was released? I ask because although I have no personal experience with it I do know of a big UK based online payment processing company who use MySQL Proxy with MySQL 5.1 in production very successfully. On Thu, Mar 11, 2010 at 3:34 PM, Johan De Meersman vegiv...@tuxera.be wrote: Bah, just spilled coffee on my keyboard. I never was a big fan of mysqlproxy, but this is almost inconceivable. The next genius to suggest it for our environment had better have asbestos underwear. Thanks for the link, Krishna. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Re: mysql proxy in production?
Hi all, The below link shows the benchmark between different web servers ( apache, niginx, cherokee). http://lists.octality.com/pipermail/cherokee/2009-May/010343.html *http://nerdbynature.de/benchmarks/ab/2009-05-16/* Conclusion of Above Benchmarks *It seems that Cherokee continues being the faster among the web**servers. According to this benchmark, Cherokee is, for instance, 79% **faster than Apache, and 18% faster than nginx.* Thanks, Krishna On Fri, Mar 12, 2010 at 5:12 PM, Johan De Meersman vegiv...@tuxera.bewrote: On Thu, Mar 11, 2010 at 4:49 PM, Brent Clark brentgclarkl...@gmail.com wrote: On 11/03/2010 16:52, Krishna Chandra Prajapati wrote: Hi Brent You can visit the below link. http://www.mysqlperformanceblog.com/2009/06/09/mysql-proxy-urgh-performance-and-scalability/ Well thats disappointing. sigh So what are we supposed to use for loadbalancing mysql. Throw plenty of servers at it :-) If you put three servers behind the proxy, you'll get the same performance as a single server, every additional server is performance benefit :-D Seriously, though, I don't have much faith in these kind of things (in the context of read/write splitting, that is), because the proxy can never know wether there'll be a write-request in the session without notification from the client anyways. If you have to modify your application anyway, just do it toroughly (you have to fish out the connects that will write in either case, so that's half the work done) and make your application fully slave-aware. That also allows you to tighten security by assigning a select-only user for the read connections, diminishing the chances of a succesfull sql injection. If you're wondering about loadbalancing between read-only slaves, you don't need a SQL aware proxy, just balance at level 4 using ldirectord or a hardware loadbalancer with sticky sessions. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: mysql proxy in production?
Hi Brent You can visit the below link. http://www.mysqlperformanceblog.com/2009/06/09/mysql-proxy-urgh-performance-and-scalability/ http://www.mysqlperformanceblog.com/2009/06/09/mysql-proxy-urgh-performance-and-scalability/ Thanks, Krishna On Thu, Mar 11, 2010 at 7:56 PM, Brent Clark brentgclarkl...@gmail.comwrote: Hiya I work for a pretty large hosting company, and we have some clients that you could call in demand clients (Well here where I live anyway :) ). We already making use of heartbeat for high availability etc. But the one area that we have not tackled is load balancing. I just read the following, which makes use of mysql proxy. http://agiletesting.blogspot.com/2009/04/mysql-load-balancing-and-read-write.html I would like to ask, does anyone make use of mysqlproxy in production, and if so, are you using it under heavy load. How do you find how it performance under load. If anyone can share their failures, successors or even just thoughts and opinions on mysql-proxy (even SQL load balancing in general), I would be most grateful. Kind Regards Brent Clark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Re: Very slow delete for Master / Child tables with millions of rows
Hi Randall, How much memory is allocated to innodb_buffer_pool_size. Please send your mysql configuration file (my.cnf) Thanks, Krishna On Thu, Mar 11, 2010 at 8:57 PM, Price, Randall randall.pr...@vt.eduwrote: I am experiencing very slow deletes when I delete a record from a master table and have cascading deletes on two detail tables. I have an application that looks for records in the master table that are older than X days and delete them. The cascasing deletes then handles deleting all the child records in the other tables. However, this process is very slow. Depending on how many records are found to delete, this process takes anywhere from 30-40 minutes to several hours. Due to the nature of my application, I must loop through the records to delete, do some stuff for each record, then delete it. I suspect at this point, each tables' indexes need to be rebuilt. There are several indexes and the ones for the tables with 4,000,000+ rows probably takes a while. My question is: What is the best way to handle deleting master/detail records in this scenario? I have a brief diagram of my tables and the CREATE TABLE statements follow. Thanks, Randall Price +---+ | tblwsusclientinfo | +-++---+ | tblwsusclients || ID| +-+| UpdateGUID| | SusClientId |-oo| SusClientId | | ... | || ... | +-+ |+---+ ( ~ 3,000 rows) | (~ 4,000,000 rows ) | | |+-+ || tblwsusevents | |+-| || EventGUID | || ... | +--oo| EventAssociatedComputer | | ... | +-| (~ 4,300,000 rows ) CREATE TABLE `tblwsusclients` ( `SusClientId` varchar(36) NOT NULL default '', `DNSName` varchar(256) NOT NULL default '', `ServerGUID` varchar(36) NOT NULL default '', `IPAddress` varchar(15) NOT NULL default '', `LastReportTime` datetime NOT NULL default '-00-00 00:00:00', `LastSyncTime` datetime NOT NULL default '-00-00 00:00:00', `DetectionResult` varchar(256) default NULL, `ResponsiblePerson` varchar(16) default NULL, `TargetGroup` varchar(45) default NULL, `Affiliation` varchar(45) default NULL, `AddedDate` datetime default NULL, `IsActive` tinyint(1) default NULL, `UnRegisteredDate` datetime default NULL, `SCVersion` double default NULL, `BiosName` varchar(256) default NULL, `BiosVersion` varchar(45) default NULL, `OSVersion` varchar(45) default NULL, `SPVersion` varchar(45) default NULL, `Make` varchar(256) default NULL, `Model` varchar(256) default NULL, `ProcArchitecture` varchar(45) default NULL, `OSLongName` varchar(256) default NULL, `TimedOutDate` datetime default NULL, PRIMARY KEY (`SusClientId`), KEY `FK_tblwsusclients_1` (`ServerGUID`), KEY `IX_DNSName` (`DNSName`), KEY `IX_IsActive` (`IsActive`), CONSTRAINT `FK_tblwsusclients_1` FOREIGN KEY (`ServerGUID`) REFERENCES `tblwsusservers` (`ServerGUID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `tblwsusclientinfo` ( `ID` bigint(20) unsigned NOT NULL auto_increment, `UpdateGUID` varchar(36) NOT NULL default '', `SusClientId` varchar(36) NOT NULL default '', `UpdateState` varchar(256) NOT NULL default '', `LastTimeChanged` datetime default NULL, PRIMARY KEY (`ID`), KEY `IX_UpdateState` (`UpdateState`), KEY `IX_SusClientId_UpdateState` (`SusClientId`,`UpdateState`), KEY `FK_tblwsusclientinfo_1` (`UpdateGUID`), KEY `FK_tblwsusclientinfo_2` (`SusClientId`), CONSTRAINT `FK_tblwsusclientinfo_1` FOREIGN KEY (`UpdateGUID`) REFERENCES `tblupdateinformation` (`UpdateGUID`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_tblwsusclientinfo_2` FOREIGN KEY (`SusClientId`) REFERENCES `tblwsusclients` (`SusClientId`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `tblwsusevents` ( `EventGUID` varchar(36) NOT NULL default '', `EventCreationDate` datetime NOT NULL default '-00-00 00:00:00', `EventMessage` text NOT NULL, `EventId` varchar(256) NOT NULL default '', `EventSource` varchar(45) NOT NULL default '', `EventErrorCode` varchar(45) default NULL, `EventIsError` tinyint(1) NOT NULL default '0', `EventAssociatedUpdate` varchar(36) default NULL, `EventAssociatedComputer` varchar(36) default NULL, `EventAssociatedWSUSServer` varchar(36) NOT NULL default '', PRIMARY KEY (`EventGUID`), KEY
Re: Enabling Slow query log in Mysql 5.0
Hi Machiel, The below link will help you. mk-query-digesthttp://www.xaprb.com/blog/category/maatkit/ Regards, Krishna On Tue, Feb 16, 2010 at 12:51 PM, Machiel Richards machi...@rdc.co.zawrote: Hi All I hope that someone can assist me with this. We have a client with a production MySQL database running MySQL 5.0. Their slow query counts have skyrocketed over the last week and I found that their slow query logs are not enabled. However when trying to configure this I get the following message: mysql set global log_slow_queries=ON; ERROR 1238 (HY000): Variable 'log_slow_queries' is a read only variable mysql I did the same thing many times before on other databases but this specific one gives me this message. Can anyone perhaps give me some insight as to why i'm getting this and how to enable it (preferably without having to restart the database seeing it is a high availability production system)? Help is much appreciated. Regards
ERROR
Hi List, I am getting the below error on my rhel server. Mysql is running on this box. Due to below error server hangs and stop responding. After system reboot it's working. Feb 7 02:38:29 DCTNMVH1 kernel: mysqld invoked oom-killer: gfp_mask=0x201d2, order=0, oomkilladj=0 Feb 7 02:39:16 DCTNMVH1 kernel: Feb 7 02:39:54 DCTNMVH1 kernel: Call Trace: Feb 7 02:40:01 DCTNMVH1 kernel: [800c6076] out_of_memory+0x8e/0x2f3 Feb 7 02:40:06 DCTNMVH1 kernel: [8002e2f2] __wake_up+0x38/0x4f Feb 7 02:40:06 DCTNMVH1 kernel: [8009fc0d] autoremove_wake_function+0x0/0x2e Feb 7 02:40:06 DCTNMVH1 kernel: [8000f487] __alloc_pages+0x245/0x2ce Feb 7 02:40:07 DCTNMVH1 kernel: [80012e03] __do_page_cache_readahead+0x96/0x179 Feb 7 02:40:07 DCTNMVH1 kernel: [80013765] filemap_nopage+0x14c/0x360 Feb 7 02:40:07 DCTNMVH1 kernel: [8000898c] __handle_mm_fault+0x1fa/0xf99 Feb 7 02:40:07 DCTNMVH1 kernel: [80066b25] do_page_fault+0x4cb/0x830 Feb 7 02:40:07 DCTNMVH1 kernel: [8009fc0d] autoremove_wake_function+0x0/0x2e Feb 7 02:40:07 DCTNMVH1 kernel: [80128659] file_has_perm+0x48/0xa3 Feb 7 02:40:07 DCTNMVH1 kernel: [8005dde9] error_exit+0x0/0x84 Feb 7 02:40:07 DCTNMVH1 kernel: Node 1 Normal per-cpu: Feb 7 02:40:07 DCTNMVH1 kernel: cpu 0 hot: high 186, batch 31 used:0 Feb 7 02:40:07 DCTNMVH1 kernel: cpu 0 cold: high 62, batch 15 used:0 Feb 7 02:40:07 DCTNMVH1 kernel: cpu 1 hot: high 186, batch 31 used:40 Feb 7 02:40:07 DCTNMVH1 kernel: cpu 1 cold: high 62, batch 15 used:15 Feb 7 02:40:07 DCTNMVH1 kernel: cpu 2 hot: high 186, batch 31 used:0 Feb 7 02:40:07 DCTNMVH1 kernel: cpu 2 cold: high 62, batch 15 used:0 Feb 7 02:40:07 DCTNMVH1 kernel: cpu 3 hot: high 186, batch 31 used:56 Feb 7 02:40:07 DCTNMVH1 kernel: cpu 3 cold: high 62, batch 15 used:14 Feb 7 02:40:07 DCTNMVH1 kernel: cpu 4 hot: high 186, batch 31 used:0 Feb 7 02:40:07 DCTNMVH1 kernel: cpu 4 cold: high 62, batch 15 used:0 Feb 7 02:40:07 DCTNMVH1 kernel: cpu 5 hot: high 186, batch 31 used:61 Feb 7 02:40:07 DCTNMVH1 kernel: cpu 5 cold: high 62, batch 15 used:14 Feb 7 02:40:07 DCTNMVH1 kernel: cpu 6 hot: high 186, batch 31 used:0 Feb 7 02:40:07 DCTNMVH1 kernel: cpu 6 cold: high 62, batch 15 used:0 Feb 7 02:40:07 DCTNMVH1 kernel: cpu 7 hot: high 186, batch 31 used:2 Feb 7 02:40:07 DCTNMVH1 kernel: cpu 7 cold: high 62, batch 15 used:58 Feb 7 02:40:07 DCTNMVH1 kernel: cpu 8 hot: high 186, batch 31 used:0 Feb 7 02:40:07 DCTNMVH1 kernel: cpu 8 cold: high 62, batch 15 used:0 Feb 7 02:40:07 DCTNMVH1 kernel: cpu 9 hot: high 186, batch 31 used:25 Feb 7 02:40:07 DCTNMVH1 kernel: cpu 9 cold: high 62, batch 15 used:44 Is the error related to mysql. Any body else facing similar issues. Thanks, Krishna
Re: Sharding
Thanks a lot. On Fri, Jan 29, 2010 at 8:50 AM, Carlos Proal carlos.pr...@gmail.comwrote: Krishna I dont have any benchmark but you can take a look on the Cafepress presentation that includes some data (or ask the authors for deep reference) http://assets.en.oreilly.com/1/event/2/Horizontal%20Scaling%20with%20HiveDB%20Presentation.pdf Carlos On 1/22/2010 11:44 PM, Krishna Chandra Prajapati wrote: Hi Carlos, Have you tried and benchmark hivedb. any body reviewed hivedb. Please share the experience. Krishna On Fri, Jan 22, 2010 at 11:47 PM, Carlos Proal carlos.pr...@gmail.commailto: carlos.pr...@gmail.com wrote: Hi Krishna Depending on your programming language, you can use http://www.hivedb.org/ Also you can try the new Spider Storage Engine http://spiderformysql.com/ I have not tried this one but seems interesting and there are a couple reviews by Giuseppe Maxia that can help you: http://datacharmer.blogspot.com/2009/04/test-driving-spider-storage-engine.html http://datacharmer.blogspot.com/2009/07/sharding-for-masses-spider-storage.html Carlos On 1/22/2010 4:47 AM, Krishna Chandra Prajapati wrote: Hi List, I am looking opensource tool for mysql sharding. One is mysql-proxy, but it is in alpha stage. Another one is spock proxy. Any one benchmark spock proxy. Is there other tools also. Please share views with mysql sharding Any response is highly appreciated. Thanks, Krishna --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Re: SCALING INSERT
Hi shawn, As the data grows to 20 millions the insert rate will become very slow. In such case i am getting 2000 insert/seconds only. Therefore my objective is not achieved. I cannot slow up the insert rate of 10,000/second. I am getting data (inserted by users at this rate) Is there any other way to do so. (distributed servers) Thanks, Krishna On Thu, Jan 21, 2010 at 7:39 PM, Shawn Green shawn.gr...@sun.com wrote: Krishna Chandra Prajapati wrote: Hi list, I want to insert 1 records/sec into table. There can be n number of tables with unique data in each. What are the possible ways to do ? Thanks, Krishna The manual is your friend. It doesn't hurt to consult it. INSERT ... http://dev.mysql.com/doc/refman/5.1/en/insert.html LOAD DATA INFILE ... http://dev.mysql.com/doc/refman/5.1/en/load-data.html And we even have both general and specific suggestions on improving INSERT performance in our Optimization chapter: http://dev.mysql.com/doc/refman/5.1/en/optimization.html Warmest regards, -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN
Re: mysql update
Hi, How big is sample.sql dump ? there are many different things, might taking the time. dns resolve issue, network issue tcp0 0 www.myweb.:mysql A.B.C.D:8366 *TIME_WAIT* (It should established) load on the server, check mysql processlist (any wrong query is there) Thanks, Krishna 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=prajapat...@gmail.com
Sharding
Hi List, I am looking opensource tool for mysql sharding. One is mysql-proxy, but it is in alpha stage. Another one is spock proxy. Any one benchmark spock proxy. Is there other tools also. Please share views with mysql sharding Any response is highly appreciated. Thanks, Krishna
Re: Sharding
Hi Carlos, Have you tried and benchmark hivedb. any body reviewed hivedb. Please share the experience. Krishna On Fri, Jan 22, 2010 at 11:47 PM, Carlos Proal carlos.pr...@gmail.comwrote: Hi Krishna Depending on your programming language, you can use http://www.hivedb.org/ Also you can try the new Spider Storage Engine http://spiderformysql.com/ I have not tried this one but seems interesting and there are a couple reviews by Giuseppe Maxia that can help you: http://datacharmer.blogspot.com/2009/04/test-driving-spider-storage-engine.html http://datacharmer.blogspot.com/2009/07/sharding-for-masses-spider-storage.html Carlos On 1/22/2010 4:47 AM, Krishna Chandra Prajapati wrote: Hi List, I am looking opensource tool for mysql sharding. One is mysql-proxy, but it is in alpha stage. Another one is spock proxy. Any one benchmark spock proxy. Is there other tools also. Please share views with mysql sharding Any response is highly appreciated. Thanks, Krishna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Selecting, Inserting and Deleting data
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
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
Re: Selecting, Inserting and Deleting data
Hi Abhishek. insert, select and delete are in the same proportion. 1. Inserted data into a table A by user. 2. Selecting data from table A inserting data to table B after applying some rules(update). 3. Deleting data from table A. 4. Selecting data from table B using some conditions (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) moving to third party for sending sms. 5. Deleting the selected data from table B. With the above scenario, i am not able to user concurrent connections. Other wise it will send duplicate sms. Thanks, Kishna On Thu, Jan 21, 2010 at 5:28 PM, Abhishek Singh abhishek.r...@gmail.comwrote: On Thu, Jan 21, 2010 at 5:18 PM, Suresh Kuna sureshkumar...@gmail.comwrote: 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.com wrote: Hi Krishna, As table is using MyISAM engine and it acquires a table level lock, the queries will be executed one after one . By converting it into Innodb as it acquires a row level lock, doing a select and delete based on primary key will be faster and the concurrency increases. -- Thanks Suresh Kuna MySQL DBA On Thu, Jan 21, 2010 at 4:30 PM, Krishna Chandra Prajapati prajapat...@gmail.com wrote: Hi List, I am working for a messaging company, sending sms to enterprise customers. In a mysql table data is being continuously inserted by user. Most of the time we have 5 to 10 millions of data in this table. Table name : alt_send_sms engine myisam From this table, i need to select data based on below parameter. Send some where else and then delete the selected data. selection and deletion part is done in bulk. SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id, service, account, id, sms_type, mclass, mwi, coding, compress FROM alt_send_sms WHERE smsc_id = 'ite' ORDER BY msg_priority, sql_id LIMIT 0,20 delete from alt_send_sms where sql_id in () sql_id is a unique bigint column with auto_increment. Since the selection and deletion is done in bulk. Therefore, i cannot run many similar concurrent queries. As duplicate messages will be send. What can be the solution for this ? Any response is highly appreciated. Thanks, Krishna -- Thanks Suresh Kuna MySQL DBA Hi Krishna, Can you please tell me what kind query you mostly run is it select or insert? -- Abhishek Kumar Singh
SCALING INSERT
Hi list, I want to insert 1 records/sec into table. There can be n number of tables with unique data in each. What are the possible ways to do ? Thanks, Krishna
LOAD BALANCER
Hi list, Out of the below load balancer which is the best one. Is there any other load balancer available. 1 mysql proxy (Still alpha) 2 ultramonkey. 3 haproxy Thanks, Krishna
Re: innodb recovery
Hi John, The data files will give you some informations like log_file_size, mutliple tablespace is being used or not. Although my.cnf can help you a lot. With the above information, use it with newer version of mysql. Krishna On Wed, Jan 20, 2010 at 3:02 PM, Johny Brawo lydyh...@gmail.com wrote: Hello! I got all data files (ibdata1, ib_logfile, etc) recovevered from mine old Debian 3.1 box (and i dont know MySQL version :( ). I want to get that DB running again. Can i copy these files to newer version of MySQL, and if i can - how? Any commands, any parameters? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Re: Replications oddity.
Hi Brent, I believe you missed log_slave_updates component on 2 and 1. You have to enable binary logs + log_slave_updates on 2 and 1. http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_log-slave-updates Thanks, Krishna On Fri, Jan 15, 2010 at 3:07 PM, Brent Clark brentgclarkl...@gmail.comwrote: Hiya I have four servers. 1 - 2 || 4 5 I have master - master replication working flawlessly between server id 1 and 2. Server id 4 is a slave of 1. Server id 2 is a slave of 2. For the slaves replications it works great, but ... I just realised ... if I create a database on id 1, it replicates to 2 and to 4, but does not replicate to 5. And its the same if do it the inverse i.e. Create a DB on 2, replication works to 1 and 5, but not to 4. Am I doing something wrong. Or am I misunderstanding how the process works. I could make id 5 replicate from one., but I was hoping to make it off 2. If anyone could help me understand this, it would gratefully be appreciated. Kind Regards Brent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Re: Error in running trigger
Hi Ranjan, User doesn't have sufficient privileges. root access required. Thanks, Krishna On Tue, Dec 22, 2009 at 3:26 PM, Jeetendra Ranjan jeetendra.ran...@sampatti.com wrote: Hi, I have created the below trigger from root user with definer: CREATE definer=`*root...@`` TRIGGER `CONSUMER_PROFILE_before_delete` BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW begin INSERT INTO DELETED_CONSUMER_PROFILE(CONSUMER_ID,CONSUMER_TYPE_ID,ENTERPRISE_URL_ID,CONSUMER_FIRST_NAME,CONSUMER_MIDDLE_INITIAL,CONSUMER_LAST_NAME, DATE_OF_BIRTH,GENDER,MARITAL_STATUS,MARRIED_TO,LOCALE_ID,PROFILE_PHOTO_PATH,STATUS,SKIP_STEP,LAST_MOD_TIME,LAST_MOD_BY ) VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_URL_ID,old.CONSUMER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL, old.CONSUMER_LAST_NAME,old.DATE_OF_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,old.LOCALE_ID,old.PROFILE_PHOTO_PATH, old.STATUS,old.SKIP_STEP,old.LAST_MOD_TIME,old.LAST_MOD_BY ); end; But when i see the trigger using show trigger command on mysql front it doesn't show and when i run the stament to hit the trigger it gives the error SQL execution erro # 1227. Response from the database: Access denied ; you need the SUPER privilege for this operation. But this user has all privilege. Why it is not executing ? Thanks in advance. Regards Jeetendra Ranjan
Re: Uninsall mysql 5.0.45 from RHEL 5
rpm -qa | grep mysql rpm -e mysqlpackagename Thanks, Krishna On Sat, Dec 19, 2009 at 2:05 PM, Jeetendra Ranjan jeetendra.ran...@sampatti.com wrote: Hi, I have mysql 5.0.45 on Red Hat EL 5. How do i completely uninstall the same ? Thanks in advance. Jeetendra Ranjan
INSERT DATA INTO TABLE
Hi Experts, load data local infile and insert into tablename are the two methods of inserting data into a mysql table. Out of the above two method. Is there any faster method of inserting data into mysql tables. Thanks, Krishna Ch. Prajapati
Re: INSERT DATA INTO TABLE
Hi Mos, In the below two command does 1 is faster than 2. * 1. LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;* 2. *LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet* *LINES TERMINATED BY '\r\n' enclosed by '';* Thanks, Krishna Ch. Prajapati On Sat, Nov 28, 2009 at 3:50 AM, mos mo...@fastmail.fm wrote: At 07:40 AM 11/27/2009, Krishna Chandra Prajapati wrote: Hi Experts, load data local infile and insert into tablename are the two methods of inserting data into a mysql table. Out of the above two method. Is there any faster method of inserting data into mysql tables. No. Load Data is the fastest method, unless the data is already stored in another table. Tip: Remember when using Load Data, it will be faster if the table is empty and optimized (no holes) than with a table that has data in it. That's because the non-unique indexes are built after all of the data has been loaded. The alternative is to disable the non-unique indexes prior to using Load Data. Unfortunately there is no way to disable the building of unique indexes during this process unless you remove the unique index prior to loading the data and building it later. Tip: Using Insert will be much slower than Load Data but you can speed it up by loading dozens of rows using one Insert statement. Hope these tips help. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Re: question regarding mysql database location
Hi Manasi, At a time mysql can point to one data directory. For your task you can have n number of mysql installation with different data directory. After that you can use federated storage engine to perform your task. Thanks, Krishna Ch. Prajapati On Wed, Nov 25, 2009 at 12:19 PM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi All, I have asked this question before But, I think I am not able to describe it better. Sorry for asking it again. I have multiple databases but there is a limit on the folders getting created in one folder. I have mysql default directory set as /var/lib/mysql/data. Now, After 32000 folder creation I am not able to create more folders than that. Well Its not like I want to create 32000 database's in it (Which I wanted to earlier :-P). for example - I want to create 10 databases but 5 in /var/lib/mysql/data/d1 to d5 and othe 5 in /var/lib/mysql/data/d6 to d10. but I want to access all the databases that is d1-d10. as I ca change the database location after 5 databases but not able to access old five which I have created in old location. Please let me know if anymore information is needed on this. I am really looking for the solution. Please Help me. -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Re: Virtual servers, Raid 10 or Raid 01 - your opinions?
Obviously raid 10 would be better choice!. Recommended for safety and performance. Thanks, Krishna Ch. Prajapati On Wed, Nov 25, 2009 at 4:34 PM, Götz Reinicke - IT-Koordinator goetz.reini...@filmakademie.de wrote: Hi, I do get two new Sun Fire X4170 servers with 8 SAS 300GB HDs, 24 GB RAM each. Right now, we do not have a lot of data in our databases (5 small LAMP servers), which should be consolidated. (The second Sun Fire X4170 will be the Webserver-Sun, the DB-Traffic will have his own gbit switch (Webserver-Sun - MySQL-Sun) ) There are more reads than writes. To simplify matters, I'd like to use virtual MySQL-Servers (Master and Slave) on the sun and use one large raid. Also I was thinking, to use one RAID 1 for the virtualisation-system and the other six harddisks for the virtual servers. What RAID would be the better choice? 10 or 01? What do you think? Thanks for any suggestions and best regards, Götz -- Götz Reinicke IT-Koordinator Tel. +49 7141 969 420 Fax +49 7141 969 55 420 E-Mail goetz.reini...@filmakademie.de Filmakademie Baden-Württemberg GmbH Akademiehof 10 71638 Ludwigsburg www.filmakademie.de Eintragung Amtsgericht Stuttgart HRB 205016 Vorsitzende des Aufsichtsrats: Prof. Dr. Claudia Hübner Staatsrätin für Demographischen Wandel und für Senioren im Staatsministerium Geschäftsführer: Prof. Thomas Schadt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
DELETE DATA FROM TABLE
Hi Experts, I have a crm table where 12 millions records inserted/day. We are running report queries on this table and using partitioning features for faster results. we have to maintain 45 days data means 540million records. As per my calculation 540 records will use 1.8 TB of disk space. Total disk space available is 2.3TB. Deleting data doesn't free up the disk space. So, I was thinking of rotating the table. But doesn't have enough disk space. Any Idea, how this task can be performed. Any idea or suggestion is highly appreciated. Thanks Regards, Krishna Ch. Prajapati
Re: DELETE DATA FROM TABLE
Hi Gavin, I am using innodb with file-per-table. I agree with you dropping a partition will reclaim disk space. alter table table name drop partition partition name But, my concern is alter table table name drop partition partition name on very big table would might take a lot of time. (Although, I haven't tested) Thanks for the immediate response. Thanks Regard, Krishna Ch. Prajapati On Thu, Nov 19, 2009 at 2:22 PM, Gavin Towey gto...@ffn.com wrote: Assuming you're using either myisam tables, or innodb with file-per-table option turned on, then dropping a whole partition at a time will allow you to reclaim disk space. If you're using innodb with a single tablespace currently, then unfortunately, you would have to export all your data, shutdown mysql, change you're my.cnf delete the tablespace ib_log files, then restart and re-import all your data. If you need to do this, you should probably seek a bit more information about from this list or other sources. Regards, Gavin Towey -Original Message- From: Krishna Chandra Prajapati [mailto:prajapat...@gmail.com] Sent: Thursday, November 19, 2009 12:13 AM To: MySQL Subject: DELETE DATA FROM TABLE Hi Experts, I have a crm table where 12 millions records inserted/day. We are running report queries on this table and using partitioning features for faster results. we have to maintain 45 days data means 540million records. As per my calculation 540 records will use 1.8 TB of disk space. Total disk space available is 2.3TB. Deleting data doesn't free up the disk space. So, I was thinking of rotating the table. But doesn't have enough disk space. Any Idea, how this task can be performed. Any idea or suggestion is highly appreciated. Thanks Regards, Krishna Ch. Prajapati The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Re: mysql server is not starting
Hi faizal, Check your mysql error log file. Thanks, Krishna On Wed, Nov 11, 2009 at 4:17 PM, F.A.I.Z.A.L sac.fai...@gmail.com wrote: hi iam trying to start mysql services but it showing error. while i start it automatically goes to shutdown.. what is errmsg.sys? its looking this file but it was not there.. please help to fix this.. /var/LRM-INSTALL/ams/software/lrm_2.0.0.0_9.0.0.0-76641/server/mysql/mysql-5.1.34-solaris10-sparc-64bit/bin bash-3.00# /var/LRM-INSTALL/ams/software/lrm_2.0.0.0_9.0.0.0-76641/server/mysql/mysql-5.1.34-solaris10-sparc-64bit/bin/mysqld -u root 09 16:14:01 [ERROR] Can't find messagefile '/usr/local/mysql/share/english/errmsg.sys' /var/LRM-INSTALL/ams/software/lrm_2.0.0.0_9.0.0.0-76641/server/mysql/mysql-5.1.34-solaris10-sparc-64bit/bin/mysqld: Unknown error 1146 09 16:14:01 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 09 16:14:01 InnoDB: Started; log sequence number 0 46409 09 16:14:01 [ERROR] Aborting 09 16:14:01 InnoDB: Starting shutdown... 09 16:14:02 InnoDB: Shutdown completed; log sequence number 0 46409 09 16:14:02 [Warning] Forcing shutdown of 1 plugins 09 16:14:02 [Note] please help to solve
Re: Temp file issues on Ubuntu 9.10
Hi Sebastiaan, Steps to fix the issue. 1. Do proper shutdown of mysql server. 2. Check the error log file that mysql server is shutdown properly. 3. Remove log files (ib_logfile0 and ib_logfile1). 4. Start mysql server (The log files will be created automatically) Thanks, Krishna On Sun, Nov 8, 2009 at 2:51 PM, Sebastiaan van Erk sebs...@sebster.comwrote: Hi all, I just recently upgraded to Ubuntu 9.10, but now I'm having all sorts of temp file problems. For example, when I try to delete a row and violate a contraint I get: ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (temp file operation failed) Instead of telling me which constraint is violated, it tells me the temp file creation failed. I have no reason why it failed, I don't see any error messages in the log. To solve this problem I tried to make a tmpfs partition (I thought, maybe somehow my using ext4 might be a problem): mkdir /tmpfs mount -t tmpfs -o size=1g tmpfs /tmpfs mkdir /tmpfs/mysql chown mysql:mysql and changed the tmpdir in the mysql config to /tmpfs/mysql tmpdir=/tmpfs/mysql But then mysql fails on startup: /usr/sbin/mysqld: Can't create/write to file '/tmpfs/mysql/ibGgjPv7' (Errcode: 13) 091108 10:12:46 InnoDB: Error: unable to create temporary file; errno: 13 091108 10:12:46 [ERROR] Plugin 'InnoDB' init function returned error. 091108 10:12:46 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. I checked error code 13, which is permission denied, but I don't understand this, because if I change tmpdir to /tmp/mysql it does work, and I have: $ ls -ld /tmp/mysql drwxr-xr-x 2 mysql mysql 4096 2009-11-08 10:14 /tmp/mysql $ ls -ld /tmpfs/mysql drwxr-xr-x 2 mysql mysql 40 2009-11-08 10:12 /tmpfs/mysql So I don't see the difference Has anyone encountered similar problems, or know what's going on here? Best regards, Sebastiaan
Re: DROP TABLE TOOK 39MIN
Hi Michael, Already using innodb_file_per_table. Krishna On Mon, Nov 9, 2009 at 9:39 PM, Michael Dykman mdyk...@gmail.com wrote: Under InnoDb, you could use file-per-table which would have significantly reduced the inter-dependencies.. given the large data size and heavy I/O you report, it might be a wise way to go. - michael dykman On Mon, Nov 9, 2009 at 3:41 AM, Johan De Meersman vegiv...@tuxera.be wrote: Presumably because you are removing 189 gigabyte of data and 549 gigabyte of indexes, all of which need to be marked as deleted in your innodb file. I/O is rather expensive :-) On MyISAM this would have been close to instantaneous (as you probably expected), because the datafile is used only for that table, so all that's needed is three filesystem delete operations. -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon.
Re: which mysql 64 bit binary for PowerEdge 1950 ?
Hi Sagar, You must use Intel EM64T Thanks, Krishna On Sat, Oct 31, 2009 at 5:45 AM, Sanjeev Sagar sanjeev.sa...@mypointscorp.com wrote: Hello Everyone, I would like to verify that which mysql 64 bit binary i need to use for PowerEdge 1950 ? Will it be *IntelEM64T* ? Following is the cpu info Processor 1 Processor Brand: Intel(R) Xeon(R) CPU E5310 @ 1.60GHz Processor Version : Model 15 Stepping 11 Voltage: 1400 mV Processor 2 Processor Brand: Intel(R) Xeon(R) CPU E5310 @ 1.60GHz Processor Version : Model 15 Stepping 11 Voltage: 1400 mV I highly appreciate it. For 32 bit, i normally use the Linux generic x86. Not sure for mysql 64 bit? Regards, Sanjeev -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
DROP TABLE TOOK 39MIN
Hi Experts, I have a crm(customer resource management) table which contains 654 million records. Dropping table took 39min. In addition to this other queries become very slow and they are not associated with bkp_mtlog any way. why? mysql show table status like 'bkp_mtlog'; +---++-++---++--+-+--+-++-+-++---+--++-+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +---++-++---++--+-+--+-++-+-++---+--++-+ | bkp_mtlog | InnoDB | 10 | Compact| 654135647 |289 | 189507928064 | 0 | 549887164416 | 58322845696 | NULL | NULL| NULL| NULL | latin1_swedish_ci | NULL | partitioned| | +---++-++---++--+-+--+-++-+-++---+--++-+ 1 row in set (2 min 11.29 sec) mysql drop table bkp_mtlog; Query OK, 0 rows affected (39 min 7.39 sec) Thanks, Krishna
Re: 50M records each year.. how to handle
Hi Sudhir, I am handling 50M record each day. Your requirement can be easily full filled using partition and good architecture. Thanks, Krishna On Mon, Nov 2, 2009 at 2:57 PM, || Sudhir Nimavat || sudhir_nima...@yahoo.com wrote: I have come across a requirement where I need to store a very large amount of data in a table. In one of our app.. we can have around 50 Million records each year.. Can any one guide me in choosing a strategy than can handle this load. Thanks SN Sudhir NimavatSenior software engineer. Quick start global PVT LTD. Baroda - 390007 Gujarat, India Personally I'm always ready to learn, although I do not always like being taught Try the new Yahoo! India Homepage. Click here. http://in.yahoo.com/trynew
CONNECTION (SOCKET AND TCP/IP)
Hi All, Any body can explain the difference between socket connection and tcp/ip connection. Thanks, Krishna
DEADLOCK MYSQL 5.1.37
Hi guys,
I am getting very frequent deadlock in mysql 5.1.37. I am not able to
understand why this below deadlock is coming.
*** (1) TRANSACTION:
TRANSACTION 0 1036157191, ACTIVE 0 sec, process no 4101, OS thread id
1908412736 fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 368, 2 row lock(s)
MySQL thread id 746738, query id 515198930 127.0.0.1 smmanager updating
DELETE FROM alt_send_sms WHERE sql_id IN ( 24878942, 24878943, 24878945,
24878947, 24878949, 24878944, 24878946, 24878951, 24878953, 24878948,
24878950, 24878954, 24878955, 24878956, 24878957, 24878958, 24878959,
24878960, 24878961, 24878962, 24878963, 24878965, 24878966, 24878967,
24878968, 24878969, 24878970, 24878971, 24878972, 24878973, 24878974,
24878975, 24878976, 24878977, 24878978, 24878979, 24878980, 24878981,
24878982, 24878983, 24878985, 0 )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 182 page no 5 n bits 96 index `PRIMARY` of table
`smmanager`.`alt_send_sms` trx id 0 1036157191 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 32; compact format; info
bits 32
0: len 20; hex 6d746e6c20202020202020202020202020202020; asc
mtnl;; 1: len 1; hex 06; asc ;; 2: len 8; hex
817b9f68; asc { h;; 3: len 6; hex 3dc28106; asc = ;; 4:
len 7; hex 2d0110; asc - ;; 5: len 1; hex 02; asc ;; 6: len 8;
hex 4d54456463617265; asc MTEdcare;; 7: len 12; hex
393139343232393837373238; asc 919422987728;; 8: SQL NULL; 9: len 30; hex
446561722053747564656e74732048696e64692d35302054657374206973; asc Dear
Students Hindi-50 Test is;...(truncated); 10: SQL NULL; 11: len 5; hex
6170693131; asc api11;; 12: SQL NULL; 13: SQL NULL; 14: len 1; hex 02; asc
;; 15: SQL NULL; 16: SQL NULL; 17: len 1; hex 00; asc ;; 18: SQL NULL; 19:
SQL NULL; 20: SQL NULL; 21: len 1; hex 13; asc ;; 22: len 30; hex
687474703a2f2f382e362e39352e3136382f70736d732f67657c5231; asc
;...(truncated); 23: SQL NULL; 24: SQL NULL; 25: SQL NULL; 26: SQL NULL; 27:
SQL NULL; 28: SQL NULL; 29: len 1; hex 82; asc ;; 30: len 4; hex 4aad025c;
asc J \;; 31: len 4; hex 31313537; asc 1157;;
*** (2) TRANSACTION:
TRANSACTION 0 1036157190, ACTIVE 0 sec, process no 4101, OS thread id
1272312128 starting index read, thread declared inside InnoDB 495
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1216, 7 row lock(s), undo log entries 2
MySQL thread id 746823, query id 515198929 127.0.0.1 smmanager updating
DELETE FROM alt_send_sms WHERE sql_id IN ( 24878984, 24878952, 24878964,
0 )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 182 page no 5 n bits 96 index `PRIMARY` of table
`smmanager`.`alt_send_sms` trx id 0 1036157190 lock_mode X locks rec but not
gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 32; compact format; info
bits 32
0: len 20; hex 6d746e6c20202020202020202020202020202020; asc
mtnl;; 1: len 1; hex 06; asc ;; 2: len 8; hex
817b9f68; asc { h;; 3: len 6; hex 3dc28106; asc = ;; 4:
len 7; hex 2d0110; asc - ;; 5: len 1; hex 02; asc ;; 6: len 8;
hex 4d54456463617265; asc MTEdcare;; 7: len 12; hex
393139343232393837373238; asc 919422987728;; 8: SQL NULL; 9: len 30; hex
446561722053747564656e74732048696e64692d35302054657374206973; asc Dear
Students Hindi-50 Test is;...(truncated); 10: SQL NULL; 11: len 5; hex
6170693131; asc api11;; 12: SQL NULL; 13: SQL NULL; 14: len 1; hex 02; asc
;; 15: SQL NULL; 16: SQL NULL; 17: len 1; hex 00; asc ;; 18: SQL NULL; 19:
SQL NULL; 20: SQL NULL; 21: len 1; hex 13; asc ;; 22: len 30; hex
687474703a2f2f382e362e39352e3136382f70736d732f67657c5231; asc
;...(truncated); 23: SQL NULL; 24: SQL NULL; 25: SQL NULL; 26: SQL NULL; 27:
SQL NULL; 28: SQL NULL; 29: len 1; hex 82; asc ;; 30: len 4; hex 4aad025c;
asc J \;; 31: len 4; hex 31313537; asc 1157;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 32; compact format; info
bits 32
0: len 20; hex 6d746e6c20202020202020202020202020202020; asc
mtnl;; 1: len 1; hex 06; asc ;; 2: len 8; hex
817b9f74; asc { t;; 3: len 6; hex 3dc28106; asc = ;; 4:
len 7; hex 2d016b; asc - k;; 5: len 1; hex 02; asc ;; 6: len 8;
hex 4d54456463617265; asc MTEdcare;; 7: len 12; hex
393139343232393837373238; asc 919422987728;; 8: SQL NULL; 9: len 30; hex
446561722053747564656e74732048696e64692d35302054657374206973; asc Dear
Students Hindi-50 Test is;...(truncated); 10: SQL NULL; 11: len 5; hex
6170693131; asc api11;; 12: SQL NULL; 13: SQL NULL; 14: len 1; hex 02; asc
;; 15: SQL NULL; 16: SQL NULL; 17: len 1; hex 00; asc ;; 18: SQL NULL; 19:
SQL NULL; 20: SQL NULL; 21: len 1; hex 13; asc ;; 22: len 30; hex
687474703a2f2f382e362e39352e3136382f70736d732f67657c5231; asc
;...(truncated); 23: SQL NULL; 24: SQL NULL; 25: SQL NULL; 26: SQL NULL; 27:
SQL NULL; 28: SQL NULL; 29: len 1; hex 82; asc ;; 30: len 4; hex 4aad025c;
asc J \;; 31: len 4; hex 31313537; asc
Re: Mysql update query gives error of lock wait timeout
Increase your Innodb_buffer_pool_size. It will solve your problem. Thanks, Krishna On Sat, Aug 29, 2009 at 10:39 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi All, I have a query which gives an error of lock wait timeout only this transaction is running with 2 records in the table. The query is : Update Test Set TestFlag = 1 Where TestID = 5; Can this one transaction lock my entire table? -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. manasi.s...@artificialmachines.com Ph:- 9833537392 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Re: Scaling Mysql
Yes, it's in our planning On Mon, Aug 24, 2009 at 11:10 PM, Gavin Towey gto...@ffn.com wrote: Have you looked at MySQL cluster? It was created specifically for telco needs. -Original Message- From: Krishna Chandra Prajapati [mailto:prajapat...@gmail.com] Sent: Friday, August 21, 2009 9:23 PM To: wha...@bfs.de Cc: MySQL Subject: Re: Scaling Mysql Hi wharms, Yor are right. It's some kind of queue mechanism. Right now i am working i telco company (We used to send sms) Users will be inserting records into send_sms @ 30,000msg/min Then those record will be updated and moved to alt_send_sms and deleted from send_sms. After that 30,000msg/min will be taken out from alt_send for processing and sending to client. All the above task are happening concurrently. We will be dealing with million of records/hour On Fri, Aug 21, 2009 at 6:11 PM, walter harms wha...@bfs.de wrote: Krishna Chandra Prajapati schrieb: Hi list, I have two tables send_sms and alt_send_sms. Users are inserting records into send_sms @ 500/sec ie 3/min. After applying some updates to send_sms data are transferred to alt_send_sms and deleted from send sms. The same thing is happening with alt_send_sms table. Is it possible to insert 1000records/sec in send_sms table and taken out at the rate 1000records/seconds from alt_send_sms. Which engine is more better for the above senario. Hi Krishna, i see you are using some kind of queue mechanism but to get a useful answer you need to be more specific: e.g. what are your safety requirements ? After moving the data to next stage Data is deleted from current table. Tables in RAM are very fast. e.g. do you need forgein keys ? No When will data be copied (send-alt) ? 3records/min after 1 day ? 1 hour ? how long to you need to store data at alt ? Min 1 minute (we need to process immeaditely and send to the users as sms) how often is the access ? per/sec If speed is a concern do you need a database at all ? (KISS) where does the current system spend its time ? and why ? You see your request is far from simple and demands detail knowlegde about your requirements going beyound what can be done in such a ML (and this is only software, there is also hardware an economics). Here you can ask how can i improve SQL statement XX ? re, wh Thanks krishna The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Scaling Mysql
Hi list, I have two tables send_sms and alt_send_sms. Users are inserting records into send_sms @ 500/sec ie 3/min. After applying some updates to send_sms data are transferred to alt_send_sms and deleted from send sms. The same thing is happening with alt_send_sms table. Is it possible to insert 1000records/sec in send_sms table and taken out at the rate 1000records/seconds from alt_send_sms. Which engine is more better for the above senario. Regards, Krishna
Re: Scaling Mysql
Hi wharms, Yor are right. It's some kind of queue mechanism. Right now i am working i telco company (We used to send sms) Users will be inserting records into send_sms @ 30,000msg/min Then those record will be updated and moved to alt_send_sms and deleted from send_sms. After that 30,000msg/min will be taken out from alt_send for processing and sending to client. All the above task are happening concurrently. We will be dealing with million of records/hour On Fri, Aug 21, 2009 at 6:11 PM, walter harms wha...@bfs.de wrote: Krishna Chandra Prajapati schrieb: Hi list, I have two tables send_sms and alt_send_sms. Users are inserting records into send_sms @ 500/sec ie 3/min. After applying some updates to send_sms data are transferred to alt_send_sms and deleted from send sms. The same thing is happening with alt_send_sms table. Is it possible to insert 1000records/sec in send_sms table and taken out at the rate 1000records/seconds from alt_send_sms. Which engine is more better for the above senario. Hi Krishna, i see you are using some kind of queue mechanism but to get a useful answer you need to be more specific: e.g. what are your safety requirements ? After moving the data to next stage Data is deleted from current table. Tables in RAM are very fast. e.g. do you need forgein keys ? No When will data be copied (send-alt) ? 3records/min after 1 day ? 1 hour ? how long to you need to store data at alt ? Min 1 minute (we need to process immeaditely and send to the users as sms) how often is the access ? per/sec If speed is a concern do you need a database at all ? (KISS) where does the current system spend its time ? and why ? You see your request is far from simple and demands detail knowlegde about your requirements going beyound what can be done in such a ML (and this is only software, there is also hardware an economics). Here you can ask how can i improve SQL statement XX ? re, wh Thanks krishna
INFOBRIGHT STORAGE ENGINE
Hi, Is there anybody using infobright storage engine on production. Please let me know the performance. Thanks Krishna Chandra Prajapati
Re: INNODB INDEX SIZE
Thanks, I am looking answer internally how the thinks work. Regards, Krishna On Fri, Jun 26, 2009 at 2:33 PM, Moon's Father yueliangdao0...@gmail.comwrote: Hi. I think innodb will split these into many small pieces and then merge them to execute. On Thu, Jun 18, 2009 at 1:52 PM, Krishna Chandra Prajapati prajapat...@gmail.com wrote: Hi guys, On MIS (management information system) server we have 16GB of physical memory. 10GB has been allocated to innodb_buffer_pool_size. Database size is around 500GB and some tables contains 600millions records. my question is if innodb index_length(25GB) is greater than data_length(20GB). How innodb manage and execute (select query) the join between the bigger table which contains 500millions and 600millions records. Although innodb_buffer_pool has been allocated only 10GB. Any response is highly appreciated. Thanks in advance. Thanks, Krishna -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn
BULK DATA HANDLING 0.5TB
Hi guys, I'm working in a telecom company. I have table called deliverylog in which 30 million records gets inserted per/day. The table has grown to 0.5TB I have to keep 60days record in the table. So, 60days * 30 million = 1800 million records. The query is taking a lot of time to fetch the result. Please sugget me what storage engine must be used and how i can get the things done. Is there any other alternative. Any response is highly appreciated. Thanks, Krishna
MySQL: large_page_size
Hi, How much performance improvement we can get using large_page_size in mysql server. Is there anybody using on productions. Thanks, Prajapati Krishna Chandra Prajapati Email-id: prajapat...@gmail.com
MYSQLDUMP ERROR
Hi lists, I have given select privileges to database tables. when i am taking mysqldump remotely it's giving error. [prajap...@beta2 prajapati]$ mysqldump --verbose -h 152.20.1.115 -u dip dip states -pdip state.sql -- Connecting to 152.20.1.115... mysqldump: Got error: 1044: Access denied for user 'dip'@'152.20.1.%' to database 'dip' when doing LOCK TABLES Thanks, Krishna Chandra Prajapati
Re: Oracle , what else ?
as an appliance and/or a blade. And if you don't think this is formidable, then wake up and smell the coffee. This could well leap-frog certain other competitors -- which is not to say they won't catch up eventually, but it is to say that Oracle has raised the bar and it's time for competitors such as MS to jump through several flaming hoops. On Tue, Apr 21, 2009 at 6:57 PM, John Daisley john.dais...@mypostoffice.co.uk wrote: MySQL will live on regardless of who owns the brand. First and foremost MySQL is a community and that community will continue to develop MySQL and take it in the direction they want it to go. Sure Oracle could try and force some 'features' or changes through but if the community didn't like them the community would just keep developing 'pre-oracle' MySQL, even if that happens to be under a different name. Personally I would be surprised if the Oracle deal goes unchallenged. I don't think Oracle really 'want' MySQL as it makes very little money and it raises competition concerns. I wouldn't be surprised if Oracle were to look at offloading MySQL to ease competition fears, perhaps to someone like Google who are already heavily involved in the development of MySQL. On Tue, 2009-04-21 at 22:36 +0100, Andy Shellam wrote: Personally (and I hope I'm wrong) I don't believe there's room in Oracle's portfolio for two diverse RDBMSs, and I envisage them re-branding MySQL as an Oracle open-source derivative which begins as being the MySQL codebase but is slowly migrated toward Oracle's engineering, to ease the transition for growing companies moving from MySQL/Oracle open-source to the Oracle enterprise versions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=fuller.art...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com -- Krishna Chandra Prajapati
Re: Does mysql support materialized views?
No On Mon, Apr 20, 2009 at 2:58 PM, Lin Chun franks1...@gmail.com wrote: Hi I am now working with Mondrian , and need tuning the database, as the materialized views can improve the performance and doesn't need to create the fact tables cheers -- - Lin Chun -- Krishna Chandra Prajapati
Re: Sun bought by Oracle
Whats the future of 'MySQL' under Oracle ? On Mon, Apr 20, 2009 at 6:45 PM, Andy Shellam andy-li...@networkmail.euwrote: I've just been made aware by a client that Oracle have purchased Sun Microsystems. The article below on Sun's website mentions that Oracle are committed to Linux and other open platforms and mentions the fact that Java touches practically every business system around. http://www.sun.com/third-party/global/oracle/index.jsp I wonder what Oracle's plans are when it comes to MySQL? There is no mention of MySQL in the above article. Will it eventually come under the Oracle umbrella, much like BerkeleyDB did? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com -- Krishna Chandra Prajapati
Re: Small InnoDB table with many concurrent queries
change connect timeout to 10sec flatly use skip-name-resolve whats the total size of your database. On Mon, Apr 20, 2009 at 7:58 PM, living liquid | Christian Meisinger c.meisin...@livingliquid.com wrote: Hi there. I've a small table with my daily banner hits. 1. version was with myisam but with a lot of concurrent queries (all SELECTs) i get too many table locks. so i changed it to an innodb table. works great most of the time. sometimes it seems to be too much, starting at about 500 concurrent queries i see a huge amount of processes taking about 3 minutes to finish 'sending data'. the SELECT queries use the correct index and data returned is small (2 integers). the table has only 4MB and about 35000 rows. it can't be the size of the table... mysql server is connected with a 1G switch. so i don't think it's network related. mysql server is a dual xeon 2,3GHz with 8G ram and SCSI disk RAID5. did i hit a innodb limit with this server configuration? or is my my.cnf bad? my.cnf --- key_buffer = 750M If not using myisam table, you it to 400mb max_allowed_packet = 32M table_cache = 1 big (5000 to 6000) sort_buffer_size= 4M join_buffer_size= 4M read_buffer_size= 2M read_rnd_buffer_size= 4M myisam_sort_buffer_size = 128M query_cache_size= 750M query_cache_limit = 16M if the query result set is small change it between 1 to 3 mb thread_cache= 32 thread_concurrency = 16 tmp_table_size = 700M max_heap_table_size = 700M net_buffer_length = 16K skip-external-locking innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 2G too big can also be an issue innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 8M innodb_lock_wait_timeout= 120 innodb_log_file_size= 256M innodb_log_files_in_group = 3 -- thanks for any info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com -- Krishna Chandra Prajapati
Re: Error code 121
prajap...@debian:~$ perror 121 OS error code 121: Remote I/O error On Mon, Apr 13, 2009 at 3:16 PM, Kaushal Shriyan kaushalshri...@gmail.comwrote: Hi what does error 121 means in mysql. is there a way to find out the description of error code 121. Thanks and Regards Kaushal -- Krishna Chandra Prajapati MySQL DBA, Mob: 9912924044
Re: Error code 121
Give the details what you are doing which gives the below issue. On Mon, Apr 13, 2009 at 4:03 PM, Kaushal Shriyan kaushalshri...@gmail.comwrote: On Mon, Apr 13, 2009 at 3:21 PM, Krishna Chandra Prajapati prajapat...@gmail.com wrote: prajap...@debian:~$ perror 121 OS error code 121: Remote I/O error On Mon, Apr 13, 2009 at 3:16 PM, Kaushal Shriyan kaushalshri...@gmail.com wrote: Hi what does error 121 means in mysql. is there a way to find out the description of error code 121. Thanks and Regards Kaushal -- Krishna Chandra Prajapati MySQL DBA, Mob: 9912924044 Hi I have searched google for the error code 121, which says below OS error code 121: Remote I/O error MySQL error code 121: Duplicate key on write or update The issue is when i run $perror 121 OS error code 121: Remote I/O error and not the second line MySQL error code 121: Duplicate key on write or update Am i missing something ? Thanks and Regards Kaushal -- Krishna Chandra Prajapati
Re: Rename InnoDB database
Hi shuly, 1 Use innodb_file_per_table. 2 Create new database. 3 Take the dump of old database. 4 Restore in new database. 5 Drop old database. On Mon, Apr 6, 2009 at 9:50 PM, Shuly Avraham sh...@cshl.edu wrote: Hi, I need to rename a database having InnoDB tables. MySQL version is: 5.0.24-standard - so I cannot use the 'mysqladmin rename' option. What would be the best approach for doing this? Thanks, Shuly. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com -- Krishna Chandra Prajapati
Remote data
Hi all, I am looking for a solution. While being on one db server how to get the data from other server. One method is federated. Is there any other way Thanks, Krishna Chandra Prajapati
Is there any solution
Hi list, THE IDEA IS TO HAVE A COMMON LOGIN I have two mysql servers with different databases on each of them. I want to search each databases(few tables) on both the server using a single login(mysql connection) Procedure is working fine.but then i have to use two logins(mysql connection) (Federated is not working efficiently) Is there any solution apart from scripting. -- Krishna
Re: enabling storage engine with RPM install
Hi Jim, mysql rpm installation comes with default storage engine myism and innodb. Just verify show engines on mysql prompt. On Fri, Feb 20, 2009 at 5:25 AM, Jim Lyons jlyons4...@gmail.com wrote: We have 5.0.22 installed on a test machine and for some reason the innodb storage engine was not enabled. We install from RPMs so I'm not sure how to enable the storage engine. If we compiled ourselves, we'd recompile but that's not an option. Does anyone know how to enable a storage engine once mysql's been installed by an RPM? How does one make the selections in the first place with RPMs? We've always just taken what we got and it was sufficient. Thanks, Jim -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Krishna Chandra Prajapati MySQL DBA, Email-id: prajapat...@gmail.com
Re: Resend: enabling storage engine with RPM install
my.cnf Sent via BlackBerry from T-Mobile -Original Message- From: Jim Lyons jlyons4...@gmail.com Date: Thu, 19 Feb 2009 22:45:01 To: MySQLmysql@lists.mysql.com Subject: Re: Resend: enabling storage engine with RPM install Yes, I had a slip of the mind. The engine that was not supported by the install is the Federated engine. I apologize, I had a blind spot. The SHOW ENGINES command lists FEDERATED but has NO in the Support column. The question, though, is how does one add an unsupported engine to an RPM install? Is it possible? Otherwise I have to either compile from source or upgrade to a version that I hope will have it. On Thu, Feb 19, 2009 at 10:08 PM, Baron Schwartz ba...@xaprb.com wrote: Where on earth did you get an RPM that doesn't have InnoDB support? I find this unlikely. I think it is more likely that you have some configuration error that's causing InnoDB to disable itself on start. How do you know InnoDB isn't supported? And by isn't supported I mean isn't compiled into mysqld. Per your commend that InnoDB wasn't installed with mysqld -- it is not separate. It's built into the /usr/sbin/mysqld binary (or whatever that is on your system). For example, look at this: strings /usr/sbin/mysqld | grep -i innodb If you see a bunch of lines starting with InnoDB: blah blah, you have a binary that includes InnoDB, and it's just disabled for some reason. Baron On Thu, Feb 19, 2009 at 6:58 PM, Jim Lyons jlyons4...@gmail.com wrote: Sorry, but I'm resending because I made a mistake in terminology and want to be clear. The problem isn't that innodb is DISABLED on the database. The innodb engine is not supported by the database. We have 5.0.22 installed on a test machine and for some reason the innodb storage engine was not installed with it. We install from RPMs so I'm not sure how to install the storage engine. If we compiled ourselves, we'd recompile but that's not an option. Does anyone know how to install a storage engine once mysql's been installed by an RPM? How does one make the selections in the first place with RPMs? We've always just taken what we got and it was sufficient. Thanks, Jim -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Krishna Chandra Prajapati MySQL DBA, Email-id: prajapat...@gmail.com
Reading table information......
Hi all, Below are the two examples. In the example 2 its giving 'Reading table information.' where as in the example 1 its not giving. I am not able to find out why it is. Example 2 server is configured by me where as example 1 server is configured by some body else. Example 1: [pr...@beta praja]$ mysql -h 172.185.1.199 -u dip -D dip -pdip Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 177299 to server version: 5.0.32-Debian_7etch8-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql exit --- Example 2: pr...@de76:~$ mysql -h 192.168.1.10 -u dip -D dip -pdip Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 278 Server version: 5.0.32-Debian_7etch8-log Debian etch distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql -- Krishna Chandra Prajapati Mob: 9912924044 Email-id: prajapat...@gmail.com
Re: Data Inconsistent
Yes, sql_mode is blank on all server A, B, C On Wed, Jan 21, 2009 at 8:40 PM, John Daisley john.dais...@mypostoffice.co.uk wrote: Is the sql_mode set the same on A/B/C? Why are A and B letting you cram NULL into a column declared NOT NULL? Are your schemas consistent on A/B/C? Perhaps 5.0.32 does not enforce NOT NULL properly? Some tweak to config may change this? I don't know the answer, but with a bit of research in this direction, you should be there. In other words: Forget the replication part -- Focus on the NULL in a NOT NULL column part. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk __ This email has been scanned by Netintelligence http://www.netintelligence.com/email -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: prajapat...@gmail.com
Re: Data Inconsistent
Hi John, Actaually, after doing root cause analysis. I got where is the problem. mysql-5.1.30 (server C) runs replication in two mode namely STRICT and IDEMPOTANT. Both of these mode is catching the problem. I believe replicaton has been enhanced on mysql version 5.1.30 . When ever any update is done on not null column it gets executed on server A with warning. server B passes to server C. Since replication is enhanced therefore it gives replicaiton error. Developers will fix the issue. But for me. How to maintain the data consistency on all the three server. If there error keeps on coming. Server C will have different data as compared to A and B. A B -C A is replicating to B. B is replicating to C A mysql-5.0.32 (Write) B mysql-5.0.32 (Read) C mysql-5.1.30 (Report Server) Complex and big queries scanning all data. Thanks, Krishna On Wed, Jan 21, 2009 at 9:29 PM, John Daisley john.dais...@mypostoffice.co.uk wrote: I think maybe in the default sql_mode 5.0 is more forgiving when it comes to accepting invalid values, quietly converting them to the nearest acceptable value and giving a warning whereas 5.1 gives an error. Personally i would rather have the data rejected and an error returned because if MySQL is converting to the nearest acceptable value you are never 100% sure what MySQL is storing. The easy way to solve your problem is to modify the column definition. If you need to store NULL values don't specify the column as NOT NULL. Yes, sql_mode is blank on all server A, B, C On Wed, Jan 21, 2009 at 8:40 PM, John Daisley john.dais...@mypostoffice.co.uk wrote: Is the sql_mode set the same on A/B/C? Why are A and B letting you cram NULL into a column declared NOT NULL? Are your schemas consistent on A/B/C? Perhaps 5.0.32 does not enforce NOT NULL properly? Some tweak to config may change this? I don't know the answer, but with a bit of research in this direction, you should be there. In other words: Forget the replication part -- Focus on the NULL in a NOT NULL column part. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk __ This email has been scanned by Netintelligence http://www.netintelligence.com/email -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: prajapat...@gmail.com __ This email has been scanned by Netintelligence http://www.netintelligence.com/email -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: prajapat...@gmail.com
Re: Applying an XML request to a database
Hi, You can use -X for xml and -H from html. See the example below. debian76:~# mysql -h 192.168.1.10 -u teen -D teen -pteen -X Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 27178 Server version: 5.0.32-Debian_7etch8-log Debian etch distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql mysql select * from user_time_stamp limit 2; ?xml version=1.0? resultset statement=select * from user_time_stamp limit 2; row field name=user_id1072005/field field name=task1.1.1/field field name=time_in1126267377/field field name=time_total11/field /row row field name=user_id1072005/field field name=task1.1.2/field field name=time_in1126267388/field field name=time_total-999/field /row /resultset 2 rows in set (0.03 sec) On Tue, Jan 20, 2009 at 2:29 PM, J Trahair j.trah...@foreversoftware.co.ukwrote: Hi Everyone I am interested in how to get an XML request to query a database. I'm working on generating the XML requests OK, I just need to know how XML meets database. Are there any generalised pointers to begin with? Thanks in advance Jonathan Trahair -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: prajapat...@gmail.com
Data Inconsistent
Hi Baron, In production we have three servers. A B -C A is replicating to B. B is replicating to C A mysql-5.0.32 (Write) B mysql-5.0.32 (Read) C mysql-5.1.30 (Report Server) Complex and big queries scanning all data. *ISSUE*: If any query like 'update set col1='val', col2=null where userid=12345;' gets executed by webserver on A. (col2 is not null column) Then query gets executed and data gets changed on A and B. But it gives replication error on C. If i skip that error then data will be inconsistent. Server C will have different data than A and C. How to solve this issue. Krishna Chandra Prajapati
Re: Applying an XML request to a database
Yes On Tue, Jan 20, 2009 at 7:40 PM, cr.vege...@gmail.com wrote: Hi Krishna, I just tried the -X and -H options as you described. Works fine. Is it also possible to get -X or -H data from MySQL into PHP ? TIA, Cor - Original Message - From: Krishna Chandra Prajapati prajapat...@gmail.com To: J Trahair j.trah...@foreversoftware.co.uk Cc: MySQL General mysql@lists.mysql.com Sent: Tuesday, January 20, 2009 10:28 AM Subject: Re: Applying an XML request to a database Hi, You can use -X for xml and -H from html. See the example below. debian76:~# mysql -h 192.168.1.10 -u teen -D teen -pteen -X Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 27178 Server version: 5.0.32-Debian_7etch8-log Debian etch distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql mysql select * from user_time_stamp limit 2; ?xml version=1.0? resultset statement=select * from user_time_stamp limit 2; row field name=user_id1072005/field field name=task1.1.1/field field name=time_in1126267377/field field name=time_total11/field /row row field name=user_id1072005/field field name=task1.1.2/field field name=time_in1126267388/field field name=time_total-999/field /row /resultset 2 rows in set (0.03 sec) On Tue, Jan 20, 2009 at 2:29 PM, J Trahair j.trah...@foreversoftware.co.ukwrote: Hi Everyone I am interested in how to get an XML request to query a database. I'm working on generating the XML requests OK, I just need to know how XML meets database. Are there any generalised pointers to begin with? Thanks in advance Jonathan Trahair -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: prajapat...@gmail.com -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: prajapat...@gmail.com
Re: Data Inconsistent
ERROR: not null column cannot be updated with null value. This error is catched by server C mysql 5.1.30 but not my server B mysql 5.0.32 In production we have three servers. A B -C A is replicating to B. B is replicating to C A mysql-5.0.32 (Write) B mysql-5.0.32 (Read) C mysql-5.1.30 (Report Server) Complex and big queries scanning all data. *ISSUE*: If any query like 'update set col1='val', col2=null where userid=12345;' gets executed by webserver on A. (col2 is not null column) Then query gets executed and data gets changed on A and B. But it gives replication error on C. If i skip that error then data will be inconsistent. Server C will have different data than A and C. How to solve this issue. On Tue, Jan 20, 2009 at 9:04 PM, Jake Maul jakem...@gmail.com wrote: What error is shown by 'show slave status\G' on server C after you issue that query? There's all sorts of things that could break replication... On Tue, Jan 20, 2009 at 7:21 AM, Krishna Chandra Prajapati prajapat...@gmail.com wrote: Hi Baron, In production we have three servers. A B -C A is replicating to B. B is replicating to C A mysql-5.0.32 (Write) B mysql-5.0.32 (Read) C mysql-5.1.30 (Report Server) Complex and big queries scanning all data. *ISSUE*: If any query like 'update set col1='val', col2=null where userid=12345;' gets executed by webserver on A. (col2 is not null column) Then query gets executed and data gets changed on A and B. But it gives replication error on C. If i skip that error then data will be inconsistent. Server C will have different data than A and C. How to solve this issue. Krishna Chandra Prajapati -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: prajapat...@gmail.com
Re: Applying an XML request to a database
You can embedded the result of mysql into the below script.
?php
// Connecting, selecting database
$link = mysql_connect('192.168.1.8', 'dip', 'dip')
or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db('dip') or die('Could not select database');
// Performing SQL query
//$query = 'SELECT * FROM user_info limit 15';
$query = 'SELECT * FROM user_info ';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
// Printing results in HTML
echo table\n;
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo \ttr\n;
foreach ($line as $col_value) {
echo \t\t\ttd$col_value/td\n;
}
echo \t/tr\n;
}
echo /table\n;
// Free resultset
mysql_free_result($result);
// Closing connection
mysql_close($link);
?
On Tue, Jan 20, 2009 at 11:31 PM, cr.vege...@gmail.com wrote:
Hi Krishna,
Would you be so kind to give some pointers / refs ?
Or may be a snippet ...
TIA, Cor
- Original Message -
*From:* Krishna Chandra Prajapati prajapat...@gmail.com
*To:* cr.vege...@gmail.com
*Cc:* J Trahair j.trah...@foreversoftware.co.uk ; MySQL
Generalmysql@lists.mysql.com
*Sent:* Tuesday, January 20, 2009 3:48 PM
*Subject:* Re: Applying an XML request to a database
Yes
On Tue, Jan 20, 2009 at 7:40 PM, cr.vege...@gmail.com wrote:
Hi Krishna,
I just tried the -X and -H options as you described. Works fine.
Is it also possible to get -X or -H data from MySQL into PHP ?
TIA, Cor
- Original Message - From: Krishna Chandra Prajapati
prajapat...@gmail.com
To: J Trahair j.trah...@foreversoftware.co.uk
Cc: MySQL General mysql@lists.mysql.com
Sent: Tuesday, January 20, 2009 10:28 AM
Subject: Re: Applying an XML request to a database
Hi,
You can use -X for xml and -H from html. See the example below.
debian76:~# mysql -h 192.168.1.10 -u teen -D teen -pteen -X
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 27178
Server version: 5.0.32-Debian_7etch8-log Debian etch distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql
mysql select * from user_time_stamp limit 2;
?xml version=1.0?
resultset statement=select * from user_time_stamp limit 2;
row
field name=user_id1072005/field
field name=task1.1.1/field
field name=time_in1126267377/field
field name=time_total11/field
/row
row
field name=user_id1072005/field
field name=task1.1.2/field
field name=time_in1126267388/field
field name=time_total-999/field
/row
/resultset
2 rows in set (0.03 sec)
On Tue, Jan 20, 2009 at 2:29 PM, J Trahair
j.trah...@foreversoftware.co.ukwrote:
Hi Everyone
I am interested in how to get an XML request to query a database. I'm
working on generating the XML requests OK, I just need to know how XML
meets
database. Are there any generalised pointers to begin with?
Thanks in advance
Jonathan Trahair
--
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-303/48/15, Sindhi Colony
P.G.Road, Secunderabad.
Pin Code: 53
Office Number: 040-66489771
Mob: 9912924044
URL: ed-ventures-online.com
Email-id: prajapat...@gmail.com
--
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-303/48/15, Sindhi Colony
P.G.Road, Secunderabad.
Pin Code: 53
Office Number: 040-66489771
Mob: 9912924044
URL: ed-ventures-online.com
Email-id: prajapat...@gmail.com
--
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-303/48/15, Sindhi Colony
P.G.Road, Secunderabad.
Pin Code: 53
Office Number: 040-66489771
Mob: 9912924044
URL: ed-ventures-online.com
Email-id: prajapat...@gmail.com
Re: A tool
Thanks lakshmi On Wed, Jan 21, 2009 at 11:53 AM, lakshmi pathi lakshmipath...@gmail.comwrote: Hi Krishna Chandra Prajapati, How can i customize, to make it work on debian OS After months of search finally yesterday i got debian - Lastnight i tested giis binary with debian -- yes running binary version gave me an Error as Floating Exception Then i tried to compile it from source it worked well.Please refer INSTALL file on how to compile from source (it has only 5 steps) I will fix this giis binary issue - till then debian user can try it from source. Anyway debian looks cool. :) Cheers, Lakshmipathi.G On Wed, Nov 19, 2008 at 11:48 AM, Krishna Chandra Prajapati prajapat...@gmail.com wrote: Thanks, I will be waiting for your reply. On Wed, Nov 19, 2008 at 10:22 AM, lakshmi pathi lakshmipath...@gmail.com wrote: Thanks for your comments :) How can i customize, to make it work on debian OS. It should work for all ext3 Linux distros giis tested with Fedroa ,Redhat and Ubuntu ... I haven't tried it with debianso right now,i don't how to customize it for debian...I'll try and check with debian and let you know in few days time. Cheers, Lakshmipathi.G On Tue, Nov 18, 2008 at 1:55 PM, Krishna Chandra Prajapati prajapat...@gmail.com wrote: Hi, It's a nice tool. Working for fedora. But not for debian. How can i customize, to make it work on debian OS. On Mon, Nov 17, 2008 at 12:25 PM, lakshmi pathi lakshmipath...@gmail.com wrote: Hi all, If you are using mysql in linux with ext3 file system,then this might help. I have written a undelete tool. [Short functionality is ,once installed files can be recovered using the tool but files deleted before installation can't be recovered. It acts as a fail-safe.] I tried it with mysql,for screenshots check here, http://www.giis.co.in/giis_mysql_screenshots.html You can download it from http://sourceforge.net/projects/giis or from here www.giis.co.in I have recovered only tables and not yet started with complete databases, based on your comments i hope to decided on futher enhancements. (If you have already such functionality available ,please let me know - as there is no point in reinventing the wheel :) ) (ps:I posted the similar message few days back on the forum ..but didn't received any comments/info..so i'm here :) ) Cheers, Lakshmipathi.G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com -- Krishna Chandra Prajapati MySQL DBA, Mob: 9912924044 Email-id: prajapat...@gmail.com -- Cheers, Lakshmipathi.G -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Mob: 9912924044 Email-id: prajapat...@gmail.com -- Cheers, Lakshmipathi.G -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: prajapat...@gmail.com
Re: stuck commits
Hi Scott, The configuration shows that you are using default mysql configuration with very few enhancement. The enhancement must depends on the size of physical memory available. innodb_buffer_pool_size268M It should be between 50% to 70% of your ram. innodb_additional_mem_pool_size1M innodb_log_file_size5M You are using default. Increase it based on memory available. query_cache_limit 629M should be between 1M to 3M query_cache_size629M query_cache_limit is ambiguous. Please visit the link to below to find the optimal parameters. http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html Krishna On Wed, Jan 14, 2009 at 12:04 PM, Scott Edwards my...@foss.daxal.comwrote: On Tuesday 13 January 2009 07:23:52 am Krishna Chandra Prajapati wrote: Hi Scott, I believe something wrong with innodb parameters. It should be optimum. In your case it might be too high or too low. Take a look at log file size. Please send your show variables and show status data to reach at conclusion. Okay. This is not during a time of incident. Another server is handling the load from the queries and database. So far we don't notice any issues with stuck commits on the new server, but it's only handling minimal load outside of amavis queries. We would like to run this on the original system, because it has raid1+drbd+heartbeat (2nodes) where as the temporary solution only uses raid1. Variable_name Value auto_increment_increment1 auto_increment_offset 1 automatic_sp_privileges ON back_log50 basedir /usr/ binlog_cache_size 32768 bulk_insert_buffer_size 8388608 character_set_clientlatin1 character_set_connectionlatin1 character_set_database latin1 character_set_filesystembinary character_set_results latin1 character_set_serverlatin1 character_set_systemutf8 character_sets_dir /usr/share/mysql/charsets/ collation_connectionlatin1_swedish_ci collation_database latin1_swedish_ci collation_serverlatin1_swedish_ci completion_type 0 concurrent_insert 1 connect_timeout 5 datadir /var/www/mysql/ date_format %Y-%m-%d datetime_format %Y-%m-%d %H:%i:%s default_week_format 0 delay_key_write ON delayed_insert_limit100 delayed_insert_timeout 300 delayed_queue_size 1000 div_precision_increment 4 engine_condition_pushdown OFF expire_logs_days10 flush OFF flush_time 0 ft_boolean_syntax + -()~*:| ft_max_word_len 84 ft_min_word_len 4 ft_query_expansion_limit20 ft_stopword_file(built-in) group_concat_max_len1024 have_archiveYES have_bdbNO have_blackhole_engine NO have_compress YES have_crypt YES have_csvYES have_dynamic_loadingYES have_example_engine NO have_federated_engine YES have_geometry YES have_innodb YES have_isam NO have_merge_engine YES have_ndbcluster DISABLED have_opensslDISABLED have_query_cacheYES have_raid NO have_rtree_keys YES have_symlinkYES init_connect init_file init_slave innodb_additional_mem_pool_size 1048576 innodb_autoextend_increment 8 innodb_buffer_pool_awe_mem_mb 0 innodb_buffer_pool_size 268435456 innodb_checksumsON innodb_commit_concurrency 0 innodb_concurrency_tickets 500 innodb_data_file_path ibdata1:10M:autoextend innodb_data_home_dir innodb_doublewrite ON innodb_fast_shutdown1 innodb_file_io_threads 4 innodb_file_per_table OFF innodb_flush_log_at_trx_commit 1 innodb_flush_method innodb_force_recovery 0 innodb_lock_wait_timeout50 innodb_locks_unsafe_for_binlog OFF innodb_log_arch_dir innodb_log_archive OFF innodb_log_buffer_size 1048576 innodb_log_file_size5242880 innodb_log_files_in_group 2 innodb_log_group_home_dir ./ innodb_max_dirty_pages_pct 90 innodb_max_purge_lag0 innodb_mirrored_log_groups 1 innodb_open_files 300 innodb_rollback_on_timeout OFF innodb_support_xa ON innodb_sync_spin_loops 20 innodb_table_locks ON innodb_thread_concurrency 8 innodb_thread_sleep_delay 1 interactive_timeout 28800 join_buffer_size3141632 key_buffer_size 50331648 key_cache_age_threshold 300 key_cache_block_size1024 key_cache_division_limit100 language/usr/share/mysql/english/ large_files_support ON large_page_size 0 large_pages OFF lc_time_names en_US license GPL local_infileON locked_in_memoryOFF log OFF log_bin ON log_bin_trust_function_creators OFF log_error log_queries_not_using_indexes OFF log_slave_updates OFF log_slow_queriesON log_warnings1 long_query_time 3 low_priority_updatesOFF lower_case_file_system OFF lower_case_table_names 0 max_allowed_packet 16776192
mk-slave-restart
Hi Baron, I want to use mk-slave-restart (maatkit tool) to restart the slave if 1048 errors comes up. [r...@linux18 ~]# mk-slave-restart --always --daemonize --defaults-file=/etc/my1.cnf --error-numbers=1048 --host=localhost --port 3307 --user=root [r...@linux18 ~]# ps aux | grep mk-slave-restart root 22006 0.0 0.0 4004 700 pts/2S+ 14:51 0:00 grep mk-slave-restart Can you tell me whats wrong in the above syntax. It's not working. Please tell me the complete syntax. -- Krishna Chandra Prajapati
Re: stuck commits
Hi Scott, I believe something wrong with innodb parameters. It should be optimum. In your case it might be too high or too low. Take a look at log file size. Please send your show variables and show status data to reach at conclusion. On Tue, Jan 13, 2009 at 3:35 AM, Scott Edwards my...@foss.daxal.com wrote: All too frequently, I see commits stuck in this database. What can I do to speed that up? Or, abort if it takes more than 40 seconds? This query here for example appears to take 443 seconds so far. From mysqladmin processlist: Id| User | Host | db | Command |Time | State | Info 14010 | amavis | mx:53008 | amavis | Query | 443 | | commit mysqld Ver 5.0.32-Debian_7etch8-log for pc-linux-gnu on x86_64 (Debian etch distribution) I recompiled it once, but the debug symbols are still missing. The build transcript didn't include -g during compile. I'm looking into redoing that now. Thanks in advance, Scott Edwards --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: prajapat...@gmail.com
slave-skip-errors = 1048
Hi Baron, If, I have started mysql server with --slave-skip-errors=1048 then this error will be logged into mysql error file or not. Thanks, Prajapati Krishna Chandra Prajapati -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: prajapat...@gmail.com
slave-skip-errors = 1048
Hi, If, I have started mysql server with --slave-skip-errors=1048 then this error will be logged into mysql error file or not. Thanks, Prajapati Krishna Chandra Prajapati
Re: Problem with MySQL prompt
Hi Stefano, I have tested. It's working fine without any issue. Open mysql configuration(.cnf) file referencing to 1st mysql instance. Add the below entires and restart the mysql server. [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates prompt = 'mysql5.1.30 ' [r...@linux18 ~]# mysql --defaults-file=/etc/my1.cnf Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.30 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql5.1.30 Thanks, -Krishna Chandra Prajapati On Tue, Dec 23, 2008 at 2:00 PM, Stefano Elmopi stefano.elm...@sociale.itwrote: Hi Krishna, thanks for the your answer !!! I have three different my.cnf for my three instances but one general my.cnf to manage all instances, my general my.cnf is: [mysqld_multi] mysqld = /opt/local/mysql50/bin/mysqld_safe mysqladmin = /opt/local/mysql50/bin/mysqladmin user = root password = mysys2008srv log = /MYSQL/MYSQL_LOG/Mysqld_Multi_Log/mysqld_multi.log ## ISTANZE DELLA PARTE PRODUZIONE ##--- ## ISTANZA SIA DI PRODUZIONE ##-- [mysqld01] defaults-file = /etc/mysql.config/my50_SIA.cnf socket = /tmp/mysql50_SIA.sock port = 3307 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/SIA/mysql50_SIA.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/SIA ## ISTANZA IPPOFONO DI PRODUZIONE ##--- [mysqld02] defaults-file = /etc/mysql.config/my50_IPPOFONO.cnf socket = /tmp/mysql50_IPPOFONO.sock port = 3306 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO/mysql50_IPPOFONO.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO ### ## ISTANZE DELLA PARTE SVILUPPO ##- ## ISTANZA SIA DI SVILUPPO ## [mysqld11] defaults-file = /etc/mysql.config/my50_SIA_SVILUPPO.cnf socket = /tmp/mysql50_SIA_SVILUPPO.sock port = 3317 pid-file = /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO/mysql50_SIA_SVILUPPO.pid datadir= /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO but if I put the variable prompt in the specific my.cnf (for example /etc/mysql.config/my50_SIA_SVILUPPO.cnf),the prompt doesn't change By Il giorno 22/dic/08, alle ore 11:59, Krishna Chandra Prajapati ha scritto: Hi Stefano, You are running three mysql instance on single server. You can have three my.cnf say my.cnf, my1.cnf, my2.cnf with different port and socket and other information in them. In this way you can set the prompt for different instance. On Mon, Dec 22, 2008 at 3:46 PM, Stefano Elmopi stefano.elm...@sociale.it wrote: I need advice. I have three instances of MySQL on a single server. How can I change the MySQL prompt depending of the instance on which connecting ? If I put the variable prompt in the general my.cnf, the prompt is changed but for all instances. If I put in the variable prompt in the my.cnf associated with the instance in the [mysql] section, the prompt does not change. Thanks Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel. 0657060500 email:stefano.elm...@sociale.it email%3astefano.elm...@sociale.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com -- Krishna Chandra Prajapati Mob: 9912924044 Email-id: prajapat...@gmail.com Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel. 0657060500 email:stefano.elm...@sociale.it -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: prajapat...@gmail.com
Re: Problem with MySQL prompt
Hi Stefano, I believe that, different mysql configuration file for different mysql instance is a better idea. In your case, if any thing goes wrong with my.cnf Then, it is going to effect all mysql instance (either restart mysql server or any other way). It has a lots of disadvantages. You might be using mysql_multi to manage mysql instance (restart, start, stop) As, you told me. You have 1 general my.cnf and three different my.cnf for mysql instance. In one of my.cnf add and try [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates prompt = 'mysql5.1.30 ' But again, if you are connecting with socket then it will not show the changed prompt. Try, to connect using defaults-file. I feel that connecting to mysql server using socket is not reading the configuration file. i mean to say the it's using default available to it. Thanks, Krishna Chandra Prajapati On Tue, Dec 23, 2008 at 5:14 PM, Stefano Elmopi stefano.elm...@sociale.itwrote: Hi Krishna, the problem is that you use --default-file on the command line and in this mode it's working fine, I also tried, but I have the configuration of the variable default-file in the general my.cnf and I enter in MySQL in this way: mysql --socket=/tmp/mysql50_SIA_SVILUPPO.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1408 Server version: 5.0.51b-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql in my case the prompt doesn't change Il giorno 23/dic/08, alle ore 11:01, Krishna Chandra Prajapati ha scritto: Hi Stefano, I have tested. It's working fine without any issue. Open mysql configuration(.cnf) file referencing to 1st mysql instance. Add the below entires and restart the mysql server. [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates prompt = 'mysql5.1.30 ' [r...@linux18 ~]# mysql --defaults-file=/etc/my1.cnf Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.30 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql5.1.30 Thanks, -Krishna Chandra Prajapati On Tue, Dec 23, 2008 at 2:00 PM, Stefano Elmopi stefano.elm...@sociale.it wrote: Hi Krishna, thanks for the your answer !!! I have three different my.cnf for my three instances but one general my.cnf to manage all instances, my general my.cnf is: [mysqld_multi] mysqld = /opt/local/mysql50/bin/mysqld_safe mysqladmin = /opt/local/mysql50/bin/mysqladmin user = root password = mysys2008srv log = /MYSQL/MYSQL_LOG/Mysqld_Multi_Log/mysqld_multi.log ## ISTANZE DELLA PARTE PRODUZIONE ##--- ## ISTANZA SIA DI PRODUZIONE ##-- [mysqld01] defaults-file = /etc/mysql.config/my50_SIA.cnf socket = /tmp/mysql50_SIA.sock port = 3307 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/SIA/mysql50_SIA.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/SIA ## ISTANZA IPPOFONO DI PRODUZIONE ##--- [mysqld02] defaults-file = /etc/mysql.config/my50_IPPOFONO.cnf socket = /tmp/mysql50_IPPOFONO.sock port = 3306 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO/mysql50_IPPOFONO.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO ### ## ISTANZE DELLA PARTE SVILUPPO ##- ## ISTANZA SIA DI SVILUPPO ## [mysqld11] defaults-file = /etc/mysql.config/my50_SIA_SVILUPPO.cnf socket = /tmp/mysql50_SIA_SVILUPPO.sock port = 3317 pid-file = /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO/mysql50_SIA_SVILUPPO.pid datadir= /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO but if I put the variable prompt in the specific my.cnf (for example /etc/mysql.config/my50_SIA_SVILUPPO.cnf),the prompt doesn't change By Il giorno 22/dic/08, alle ore 11:59, Krishna Chandra Prajapati ha scritto: Hi Stefano, You are running three mysql instance on single server. You can have three my.cnf say my.cnf, my1.cnf, my2.cnf with different port and socket and other information in them. In this way you can set the prompt for different instance. On Mon, Dec 22, 2008 at 3:46 PM, Stefano Elmopi stefano.elm...@sociale.it wrote: I need advice. I have three instances of MySQL on a single server. How can I change the MySQL prompt depending of the instance on which connecting ? If I put the variable prompt in the general my.cnf, the prompt is changed but for all instances. If I put in the variable prompt in the my.cnf associated with the instance in the [mysql] section, the prompt does not change. Thanks Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel
Re: Problem with MySQL prompt
Hi Stefano, You are running three mysql instance on single server. You can have three my.cnf say my.cnf, my1.cnf, my2.cnf with different port and socket and other information in them. In this way you can set the prompt for different instance. On Mon, Dec 22, 2008 at 3:46 PM, Stefano Elmopi stefano.elm...@sociale.itwrote: I need advice. I have three instances of MySQL on a single server. How can I change the MySQL prompt depending of the instance on which connecting ? If I put the variable prompt in the general my.cnf, the prompt is changed but for all instances. If I put in the variable prompt in the my.cnf associated with the instance in the [mysql] section, the prompt does not change. Thanks Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel. 0657060500 email:stefano.elm...@sociale.it email%3astefano.elm...@sociale.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com -- Krishna Chandra Prajapati Mob: 9912924044 Email-id: prajapat...@gmail.com
error writing communication package 1160
Hi all, I am getting the below error on one of mysql server Got an error writing communication packets Error_code: 1160 How i can fix it. Increasing max_allowed packet will help. max_allowed_packets = 1M -- Krishna Chandra Prajapati MySQL DBA, Mob: 9912924044 Email-id: [EMAIL PROTECTED]
