Replication
Hi, I am setting up a replication with 1 master and multiple slave. My question is can i use the same user (from master) for setting up replication on multiple slaves. Is there any advantage for replication by creating different user for different slave. Thanks Regards, -- Krishna Chandra Prajapati
Re: Replication
I think we can use the same replication account for all the slaves, as having a user for each slave will be a maintenance headache. On 4/9/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi, I am setting up a replication with 1 master and multiple slave. My question is can i use the same user (from master) for setting up replication on multiple slaves. Is there any advantage for replication by creating different user for different slave. Thanks Regards, -- Krishna Chandra Prajapati
Re: Merge Tables and Replication
[EMAIL PROTECTED] wrote: Does this sound about right? Anybody see any road hazards? If not, and this line of thinking is reasonable, should the DB with the older records also be replicated so that when a new old records table needs to be created, I don't have to repeat everything on the slave? Most of the problems documented here http://dev.mysql.com/doc/refman/5.0/en/merge-table-problems.html and some of it here http://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html The problems that stand out - A MERGE table cannot maintain uniqueness constraints over the entire table. - Key reads are slower. When you read a key, the MERGE storage engine needs to issue a read on all underlying tables to check which one most closely matches the given key. To read the next key, the MERGE storage engine needs to search the read buffers to find the next key. -- raj shekhar facts: http://rajshekhar.net | opinions: http://rajshekhar.net/blog Yoda of Borg are we: Futile is resistance. Assimilate you, we will 'Borg? Sounds Swedish.' - Lily, Star Trek First Contact -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication
It depends how public your database is. If you ever need to shut off one client then you might use separate users. Otherwise it's just annoying complexity, use a single user. Remember to use a different 'server-id' for each client! Ben Krishna Chandra Prajapati wrote: Hi, I am setting up a replication with 1 master and multiple slave. My question is can i use the same user (from master) for setting up replication on multiple slaves. Is there any advantage for replication by creating different user for different slave. Thanks Regards, -- Ben Clewett Road Tech Computer Systems Ltd http://www.tachomaster.co.uk http://www.roadrunner.uk.com 01923 46 Shenley Hall Rectory Lane Shenley Hertfordshire WD7 9AN * This e-mail is confidential and may be legally privileged. It is intended solely for the use of the individual(s) to whom it is addressed. Any content in this message is not necessarily a view or statement from Road Tech Computer Systems Limited but is that of the individual sender. If you are not the intended recipient, be advised that you have received this e-mail in error and that any use, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited. We use reasonable endeavours to virus scan all e-mails leaving the company but no warranty is given that this e-mail and any attachments are virus free. You should undertake your own virus checking. The right to monitor e-mail communications through our networks is reserved by us Road Tech Computer Systems Ltd. Shenley Hall, Rectory Lane, Shenley, Radlett, Hertfordshire, WD7 9AN. - VAT Registration No GB 449 3582 17 Registered in England No: 02017435, Registered Address: Charter Court, Midland Road, Hemel Hempstead, Hertfordshire, HP2 5GE. * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select count(). Help-a-newb
Is it possible to get the count() for forums, threads and messages extrapolating from the following query? Thanx for helping a newb. select sf_conferences.id, sf_conferences.name from ((#variables.tableprefix#conferences left JOIN sf_forums ON sf_conferences.id=sf_forums.conferenceidfk) left JOIN sf_threads ON sf_forums.id = sf_threads.forumidfk) left JOIN sf_messages ON sf_threads.id = sf_messages.threadidfk GROUP BY sf_conferences.id, sf_conferences.name -- View this message in context: http://www.nabble.com/select-count%28%29.-Help-a-newb-tp16582786p16582786.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
We have just moved to a new web server where we are running mysql version: 5.0.45
We have just moved to a new web server where we are running mysql version:
5.0.45
On the old machine, when we gave the following command:
update table1 set passwd = password('xx') where user_name=xx;
and then gave the following command,
SELECT user_name FROM alon_protein_passwd WHERE (user_name = xx AND
passwd=password('xx'))
we got the expected results.
Now when we do this, on the new machine, we get no result i.e. Empty set.
Any help will be appreciated.
--
Malka Cymbalista
Webmaster, Weizmann Institute of Science
[EMAIL PROTECTED]
08-934-3036
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: We have just moved to a new web server where we are runningmysql version: 5.0.45
We have just moved to a new web server where we are running mysql
version: 5.0.45
On the old machine, when we gave the following command:
update table1 set passwd = password('xx') where user_name=xx;
Here you're updating 'table1'...
and then gave the following command,
SELECT user_name FROM alon_protein_passwd WHERE (user_name = xx
AND passwd=password('xx'))
..but here you're selecting from 'alon_protein_passwd'
we got the expected results.
Now when we do this, on the new machine, we get no result i.e. Empty set.
Any help will be appreciated.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: We have just moved to a new web server where we are runningmysql version: 5.0.45
Thanks for the answer but that's not it. It was my typo. I am really updating
and selecting from the same table.
--
Malka Cymbalista
Webmaster, Weizmann Institute of Science
[EMAIL PROTECTED]
08-934-3036
On 4/9/2008 at 1:54 PM, in message
[EMAIL PROTECTED], Edward Kay
[EMAIL PROTECTED] wrote:
We have just moved to a new web server where we are running mysql
version: 5.0.45
On the old machine, when we gave the following command:
update table1 set passwd = password('xx') where user_name=xx;
Here you're updating 'table1'...
and then gave the following command,
SELECT user_name FROM alon_protein_passwd WHERE (user_name = xx
AND passwd=password('xx'))
..but here you're selecting from 'alon_protein_passwd'
we got the expected results.
Now when we do this, on the new machine, we get no result i.e. Empty set.
Any help will be appreciated.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: We have just moved to a new web server where we arerunningmysql version: 5.0.45
Thanks for the answer but that's not it. It was my typo. I am really updating and selecting from the same table. Perhaps not it either, but strings should be in single quotes, not double quotes. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: confirm subscribe to mysql@lists.mysql.com
On Wednesday 09 April 2008 13:02:57 [EMAIL PROTECTED] wrote: To confirm that you would like [EMAIL PROTECTED] added to the mysql mailing list, please click on the following link: http://lists.mysql.com/s/mysql/47fca2617dc97f36/nsabbi=tiscali.it This confirmation serves two purposes. First, it verifies that we are able to get mail through to you. Second, it protects you in case someone forges a subscription request in your name. --- Administrative commands for the mysql list --- I can handle administrative requests automatically. Please do not send them to the list address! Instead, send your message to the correct command address: To subscribe to the list, send a message to: [EMAIL PROTECTED] To remove your address from the list, just send a message to the address in the ``List-Unsubscribe'' header of any list message. If you haven't changed addresses since subscribing, you can also send a message to: [EMAIL PROTECTED] or for the digest to: [EMAIL PROTECTED] For addition or removal of addresses, I'll send a confirmation message to that address. When you receive it, simply reply to it to complete the transaction. If you need to get in touch with the human owner of this list, please send a message to: [EMAIL PROTECTED] Please include a FORWARDED list message with ALL HEADERS intact to make it easier to help you. --- Enclosed is a copy of the request I received. Received: (qmail 6852 invoked by uid 48); 9 Apr 2008 11:02:57 - Date: 9 Apr 2008 11:02:57 - Message-ID: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Unsubscribe request From: [EMAIL PROTECTED] This message was generated because of a request from 89.97.249.170. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: confirm subscribe to mysql@lists.mysql.com
On Wednesday 09 April 2008 13:03:39 [EMAIL PROTECTED] wrote: To confirm that you would like [EMAIL PROTECTED] added to the mysql mailing list, please click on the following link: http://lists.mysql.com/s/mysql/47fca28b736fbf40/Nicola.Sabbi=poste. it This confirmation serves two purposes. First, it verifies that we are able to get mail through to you. Second, it protects you in case someone forges a subscription request in your name. --- Administrative commands for the mysql list --- I can handle administrative requests automatically. Please do not send them to the list address! Instead, send your message to the correct command address: To subscribe to the list, send a message to: [EMAIL PROTECTED] To remove your address from the list, just send a message to the address in the ``List-Unsubscribe'' header of any list message. If you haven't changed addresses since subscribing, you can also send a message to: [EMAIL PROTECTED] or for the digest to: [EMAIL PROTECTED] For addition or removal of addresses, I'll send a confirmation message to that address. When you receive it, simply reply to it to complete the transaction. If you need to get in touch with the human owner of this list, please send a message to: [EMAIL PROTECTED] Please include a FORWARDED list message with ALL HEADERS intact to make it easier to help you. --- Enclosed is a copy of the request I received. Received: (qmail 7272 invoked by uid 48); 9 Apr 2008 11:03:39 - Date: 9 Apr 2008 11:03:39 - Message-ID: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Unsubscribe request From: [EMAIL PROTECTED] This message was generated because of a request from 89.97.249.170. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: We have just moved to a new web server where we arerunningmysql version: 5.0.45
Thanks for the email but that didn't help. I gave the commands again using only single quotes and I still get no results. On the old machine we were running MySQL 4.0.15a. Is there any difference with the password function in these 2 versions? -- Malka Cymbalista Webmaster, Weizmann Institute of Science [EMAIL PROTECTED] 08-934-3036 On 4/9/2008 at 2:17 PM, in message [EMAIL PROTECTED], Martijn Tonies [EMAIL PROTECTED] wrote: Thanks for the answer but that's not it. It was my typo. I am really updating and selecting from the same table. Perhaps not it either, but strings should be in single quotes, not double quotes. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: We have just moved to a new web server where wearerunningmysql version: 5.0.45
Thanks for the email but that didn't help. I gave the commands again using only single quotes and I still get no results. On the old machine we were running MySQL 4.0.15a. Is there any difference with the password function in these 2 versions? Could be, I remember MySQL 4.1 having a new password mechanism. If this uses the same function, I guess you shouldn't be surprised it returns a very different value in 4.1 and up. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: We have just moved to a new web server where wearerunningmysql version: 5.0.45
Thanks. So how does one use the password function in MySQL 5? -- Malka Cymbalista Webmaster, Weizmann Institute of Science [EMAIL PROTECTED] 08-934-3036 On 4/9/2008 at 2:57 PM, in message [EMAIL PROTECTED], Martijn Tonies [EMAIL PROTECTED] wrote: Thanks for the email but that didn't help. I gave the commands again using only single quotes and I still get no results. On the old machine we were running MySQL 4.0.15a. Is there any difference with the password function in these 2 versions? Could be, I remember MySQL 4.1 having a new password mechanism. If this uses the same function, I guess you shouldn't be surprised it returns a very different value in 4.1 and up. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help needed to tune Innodb on ZFS (on Solaris)
Hi, I installed Mysql 5.0.45 on Solaris 10 01/08 configured to run the datadir on a ZFS filesystem on a Raid1 pool. Both myisam and innodb tables are on the same filesystem. Innodb is configured to run with a buffer_pool_size=256M, with doublewrite set to OFF and with file_per_table to Off. The log files are 2 x 48 MB each. The problem I have is that importing the same 7 MB sql dump takes 9 seconds if engine=Myisam and 98 when engine is Innodb. Following some advice found in various bloggers I created the zfs filesystem I created with a recordsize of 16K, and set flush_log_at_trx_commit to 0. In some way the benchmark have improved, but I still find Innodb much slower than Myisam. Does anyone have any experience on this particular configuration? Is there any other trick to follow to improve Innodb's performance on ZFS? Three more things that I noticed: - setting innodb_flush_method=O_DIRECT causes mysql to complain that the directio() is not implemented. - dropping the db and reimporting it from the dump is slower at every iteration. Is there any way to keep the next reimports as fast as the first one without recreating from scratch the ibdata files? - why using innodb_file_per_tables is so much slower than running innodb with a single table space? Thanks in advance for any help, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: We have just moved to a new web server wherewearerunningmysql version: 5.0.45
Thanks. So how does one use the password function in MySQL 5? Most probably just the same, but your MySQL 4 password hashes are incompatible with MySQL 5 password hashes. A search on password in the MySQL docs returns quite some stuff, see: http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html http://dev.mysql.com/doc/mysql/search.php?version=5.0q=passwordfrom=%2Fdoc%2Frefman%2F5.0%2Fen%2Flang=en Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: We have just moved to a new web server wherewearerunningmysql version: 5.0.45
PS, the document I send you also mentions: To change the password but create a short hash, use the OLD_PASSWORD() function instead: Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: We have just moved to a new web server where we are running mysql version: 5.0.45
At 1:13 PM +0300 4/9/08, Malka Cymbalista wrote:
We have just moved to a new web server where we are running mysql
version: 5.0.45
On the old machine, when we gave the following command:
update table1 set passwd = password('xx') where user_name=xx;
and then gave the following command,
SELECT user_name FROM alon_protein_passwd WHERE (user_name = xx
AND passwd=password('xx'))
we got the expected results.
Now when we do this, on the new machine, we get no result i.e. Empty set.
Any help will be appreciated.
The password hashing algorithm changed in MySQL 4.1. You can read about
it here:
http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html
This is likely the cause of the changes that you're observing. I
imagine that you'll either need to upgrade the passwords in your
table, or look for them using OLD_PASSWORD() rather than PASSWORD().
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb status
Hi All, show engine innodb status gives the following information. I am not able to draw conclusion. SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 97641, signal count 97303 --Thread 1140881760 has waited at log0log.c line 1986 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x2ba97e301ad8 created in file log0log.c line 801 a writer (thread id 1140881760) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file log0log.c line 1986 Last time write locked in file log0log.c line 1782 Mutex spin waits 0, rounds 1480195, OS waits 17032 RW-shared spins 155109, OS waits 76582; RW-excl spins 5896, OS waits 3598 Thanks, -- Krishna Chandra Prajapati
Re: We have just moved to a new web server where we are running mysql version: 5.0.45
Thanks to Martin and Paul for their answers.
--
Malka Cymbalista
Webmaster, Weizmann Institute of Science
[EMAIL PROTECTED]
08-934-3036
On 4/9/2008 at 3:54 PM, in message [EMAIL PROTECTED], Paul
DuBois [EMAIL PROTECTED] wrote:
At 1:13 PM +0300 4/9/08, Malka Cymbalista wrote:
We have just moved to a new web server where we are running mysql
version: 5.0.45
On the old machine, when we gave the following command:
update table1 set passwd = password('xx') where user_name=xx;
and then gave the following command,
SELECT user_name FROM alon_protein_passwd WHERE (user_name = xx
AND passwd=password('xx'))
we got the expected results.
Now when we do this, on the new machine, we get no result i.e. Empty set.
Any help will be appreciated.
The password hashing algorithm changed in MySQL 4.1. You can read about
it here:
http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html
This is likely the cause of the changes that you're observing. I
imagine that you'll either need to upgrade the passwords in your
table, or look for them using OLD_PASSWORD() rather than PASSWORD().
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select count(). Help-a-newb
Hi I think you would do this : SELECT sf_conferences.id, sf_conferences.name, count(*) AS `count` FROM (( LEFT JOIN sf_forums ON sf_conferences.id=sf_forums.conferenceidfk) LEFT sf_threads ON sf_forums.id = sf_threads.forumidfk) LEFT JOIN sf_messages ON sf_threads.id = sf_messages.threadidfk GROUP BY sf_conferences.id, sf_conferences.name; I hope this is what you want :) contiw a écrit : Is it possible to get the count() for forums, threads and messages extrapolating from the following query? Thanx for helping a newb. select sf_conferences.id, sf_conferences.name from ((#variables.tableprefix#conferences left JOIN sf_forums ON sf_conferences.id=sf_forums.conferenceidfk) left JOIN sf_threads ON sf_forums.id = sf_threads.forumidfk) left JOIN sf_messages ON sf_threads.id = sf_messages.threadidfk GROUP BY sf_conferences.id, sf_conferences.name -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select count(). Help-a-newb
Thank You Richard! Merci beaucoup. wConti Richard-175 wrote: Hi I think you would do this : SELECT sf_conferences.id, sf_conferences.name, count(*) AS `count` FROM (( LEFT JOIN sf_forums ON sf_conferences.id=sf_forums.conferenceidfk) LEFT sf_threads ON sf_forums.id = sf_threads.forumidfk) LEFT JOIN sf_messages ON sf_threads.id = sf_messages.threadidfk GROUP BY sf_conferences.id, sf_conferences.name; I hope this is what you want :) contiw a écrit : Is it possible to get the count() for forums, threads and messages extrapolating from the following query? Thanx for helping a newb. select sf_conferences.id, sf_conferences.name from ((#variables.tableprefix#conferences left JOIN sf_forums ON sf_conferences.id=sf_forums.conferenceidfk) left JOIN sf_threads ON sf_forums.id = sf_threads.forumidfk) left JOIN sf_messages ON sf_threads.id = sf_messages.threadidfk GROUP BY sf_conferences.id, sf_conferences.name -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/select-count%28%29.-Help-a-newb-tp16582786p16590541.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed to tune Innodb on ZFS (on Solaris)
On Wed, Apr 9, 2008 at 5:51 AM, Nico Sabbi [EMAIL PROTECTED] wrote: The problem I have is that importing the same 7 MB sql dump takes 9 seconds if engine=Myisam and 98 when engine is Innodb. Is autocommit turned off? http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html When importing data into InnoDB, make sure that MySQL does not have autocommit mode enabled because that requires a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET AUTOCOMMIT and COMMIT statements: SET AUTOCOMMIT=0; ... SQL import statements ... COMMIT; There is also a note in there about forcedirectio and certain solaris setups. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed to tune Innodb on ZFS (on Solaris)
Il Wednesday 09 April 2008 19:40:32 Rob Wultsch ha scritto: On Wed, Apr 9, 2008 at 5:51 AM, Nico Sabbi [EMAIL PROTECTED] wrote: The problem I have is that importing the same 7 MB sql dump takes 9 seconds if engine=Myisam and 98 when engine is Innodb. Is autocommit turned off? http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html When importing data into InnoDB, make sure that MySQL does not have autocommit mode enabled because that requires a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET AUTOCOMMIT and COMMIT statements: SET AUTOCOMMIT=0; ... SQL import statements ... COMMIT; yes, autocommit is off There is also a note in there about forcedirectio and certain solaris setups. I read it, but directio seems to be unavailable on ZFS (as fas as I can tell) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
