MySQL Replication
I am trying to understand exactly how replication works. So far, I see that changes made on a master server are replicated to the slave server(s). However, if a change is made on a slave server, is that replicated back to the master as well as all other slaves? I am asking this question as I try to develop a plan for more efficient web servers. Here is what I am planning. Please let me know if this sounds smart, or like a bad idea. Server 1: Redhat MySQL Master Servers 2 3: Load-Balanced Redhat Apache web servers w/MySQL Slaves Servers 2 3 will be serving the same content and will need access to the same data from the MySQL server(s). I am hoping that running MySQL on each of the web servers will help to reduce the overall load on the servers. Thank You, Jason Williard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sleeping Processes Timeout?
Hello, Today, I started experiencing some issues on our website with max connections exceeded errors. In looking into this, I found that we had too many sleeping processes. I was not even able to login to the mysql server from a command line. Is there a way to set a timeout for sleeping processes? This is a small example of what I am seeing when I run a 'show processlist' query: +--+--+---+---+-+--+---+ --+ | Id | User | Host | db| Command | Time | State | Info | +--+--+---+---+-+--+---+ --+ | 584 | root | localhost | blogs | Sleep | 915 | | NULL | | 595 | root | localhost | blogs | Sleep | 900 | | NULL | +--+--+---+---+-+--+---+ --+ --- Thank You Jason Williard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unable to start MySQL
Hello, I have been trying to install and run MySQL on a Redhat Enterprise machine. The installation appears to go just fine, but when I go to run it, MySQL doesn't startup. Here is what I have done so far: INSTALLATION: ./configure --with-openssl=/usr/local/ssl --with-openssl-includes=/usr/local/ssl/include --with-openssl-libs=/usr/local/ssl/lib --with-isam --with-archive-storage-engine --with-csv-storage-engine make make install /usr/bin/mysql_install_db --user=mysql After doing the above, I tried running MySQL with the following commands: /usr/bin/safe_mysqld /usr/bin/safe_mysqld --user=mysql Each of these resulted in this sort of response: --- [EMAIL PROTECTED] usr]# /usr/bin/safe_mysqld --user=mysql [1] 14204 [EMAIL PROTECTED] usr]# Starting mysqld daemon with databases from /var/lib/mysql 050215 11:31:25 mysqld ended [1]+ Done/usr/bin/safe_mysqld --user=mysql [EMAIL PROTECTED] usr]# --- Any assistance with this would be greatly appreciated. --- Thank You, Jason Williard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error After Upgrading 3.23.58 - 4.1.7
I recently upgraded MySQL from 3.23.58 to 4.1.7 on a Fedora Core 1 system.
After the upgrade, I ran into the following error with a previously
installed copy of Bugzilla. I think the core of the error is Client does
not support authentication protocol requested by server.
-[ERROR]-
DBI connect('host=localhost;database=bugz;port=3306','root',...) failed:
Client does not support authentication protocol requested by server;
consider upgrading MySQL client at
/usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi/DBI.pm line 586
DBI::__ANON__('undef','undef') called at
/usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi/DBI.pm line 637
DBI::connect('DBI','DBI:mysql:host=localhost;database=bugz;port=3306','','',
'HASH(0x9afe5a0)') called at Bugzilla/DB.pm line 146
Bugzilla::DB::_connect('DBI:mysql:host=localhost;database=bugz;port=3306')
called at Bugzilla/DB.pm line 138
Bugzilla::DB::connect_main() called at Bugzilla.pm line 143
Bugzilla::dbh('Bugzilla') called at Bugzilla/Auth/CGI.pm line 154
Bugzilla::Auth::CGI::login('Bugzilla::Auth::CGI',2) called at Bugzilla.pm
line 74
Bugzilla::login('Bugzilla',2) called at /www/htdocs/bugz/query.cgi line 60
at Bugzilla/DB.pm line 146
-
So far, my research shows that the issue may be with the Client. This is
where my knowledge is failing me as I am unsure of how to upgrade the
Client.
The install was done from source, as I have always found this to be the most
stable method of upgrading. Any assistance would be appreciated.
Thank You,
Jason Williard
Systems Administrator
PCSafe Inc.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error Starting MySQL 4.0.21
I have a new linux server that I just installed the mysql-max-4.0.21-pc-linux-i686 binary on. The installation had no errors. However, it is not starting on startup because of the following error: Can't execute ./bin/mysqld_safe from dir /var/lib I can start MySQL manually running '/usr/local/mysql/bin/mysqld_safe '. Does anyone know why I am getting this error message? Thank You, Jason Williard PCSafe, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Comparing Tables
I know that it is possible to do this, though I don't know how. I have 2 tables containing information about trouble tickets. One of the tables (table2) contains information about every ticket ever received, including tickets that were deleted from the system. For this reason, table2 has ~450,000 records where table1, which holds the good tickets, has only ~4500. I would like to clear the useless data out of table2, but don't want to run 450,000 queries to compare them one row at a time. The two tables share a common id, though one is referred to as tickid while the other is referred to as ticketidchar. Is there an easy way, using just a few queries, to select all the rows in table2 where ticketidchar does not exist in table1? Thank You, Jason
Long Running Queries
Hello, I recently began experiencing issues that I would like some assistance with. Server Details: - Windows 2003 - MySQL Max 4.0.20a I have a table with 450,000+ records in it. When I try to run a single query, such as selecting 1 row or deleting 1 row using the WHERE clause, the query can take forever to run. In a specific case, it took 57 seconds to complete the SELECT query. Any ideas on how to speed this up? -- Thank You, Jason Williard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Long Running Queries
I'm sorry to sound like a novice, but could you explain that? Are you recommending that I set the column I am searching to an Index? Thank You, Jason Williard Client Services [EMAIL PROTECTED] wrote: You need Appropriate Indexes on the tables. -Original Message- From: Jason Williard [mailto:[EMAIL PROTECTED] Sent: Thursday, October 07, 2004 1:11 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Long Running Queries Hello, I recently began experiencing issues that I would like some assistance with. Server Details: - Windows 2003 - MySQL Max 4.0.20a I have a table with 450,000+ records in it. When I try to run a single query, such as selecting 1 row or deleting 1 row using the WHERE clause, the query can take forever to run. In a specific case, it took 57 seconds to complete the SELECT query. Any ideas on how to speed this up? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Long Running Queries
Thank you for your assistance with this. Here's what you were asking for. Structure Output: # # Table structure for table 'asticketsdata' # CREATE TABLE asticketsdata ( ticketdataid int(11) NOT NULL auto_increment, ticketidchar varchar(255) NOT NULL default '', ticketidno int(11) default NULL, contents text, emailbody text, PRIMARY KEY (ticketdataid), KEY ticketdata1 (ticketidno) ) TYPE=MyISAM; Query: SELECT * FROM asticketsdata WHERE ticketidchar = 'IAZ-16393'; Thank You, Jason Williard Client Services [EMAIL PROTECTED] wrote: I have tables with millions of records. 1. I use Query-Caching and that speeds up all my selects/searches after the first time, no matter how complicated the criteria. 2. Build Indexes on the columns that are in your 'where' Criteria, that will help you big time. If you post the table structure, and the query, I can help you with your index. -Original Message- From: Jason Williard [mailto:[EMAIL PROTECTED] Sent: Thursday, October 07, 2004 1:23 AM To: Wadhwa, Amit Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Long Running Queries I'm sorry to sound like a novice, but could you explain that? Are you recommending that I set the column I am searching to an Index? Thank You, Jason Williard Client Services [EMAIL PROTECTED] wrote: You need Appropriate Indexes on the tables. -Original Message- From: Jason Williard [mailto:[EMAIL PROTECTED] Sent: Thursday, October 07, 2004 1:11 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Long Running Queries Hello, I recently began experiencing issues that I would like some assistance with. Server Details: - Windows 2003 - MySQL Max 4.0.20a I have a table with 450,000+ records in it. When I try to run a single query, such as selecting 1 row or deleting 1 row using the WHERE clause, the query can take forever to run. In a specific case, it took 57 seconds to complete the SELECT query. Any ideas on how to speed this up? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Long Running Queries
Amit, You are awesome! That fixed it quite nicely. Our system is screaming now :-) Thank You VERY MUCH!, Jason Williard Client Services [EMAIL PROTECTED] wrote: Do this and tell me if it helped ALTER TABLE `asticketsdata` ADD INDEX ( `ticketidchar` ) Regards, Amit -Original Message- From: Jason Williard [mailto:[EMAIL PROTECTED] Sent: Thursday, October 07, 2004 1:42 AM To: Wadhwa, Amit Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Long Running Queries Thank you for your assistance with this. Here's what you were asking for. Structure Output: # # Table structure for table 'asticketsdata' # CREATE TABLE asticketsdata ( ticketdataid int(11) NOT NULL auto_increment, ticketidchar varchar(255) NOT NULL default '', ticketidno int(11) default NULL, contents text, emailbody text, PRIMARY KEY (ticketdataid), KEY ticketdata1 (ticketidno) ) TYPE=MyISAM; Query: SELECT * FROM asticketsdata WHERE ticketidchar = 'IAZ-16393'; Thank You, Jason Williard Client Services [EMAIL PROTECTED] wrote: I have tables with millions of records. 1. I use Query-Caching and that speeds up all my selects/searches after the first time, no matter how complicated the criteria. 2. Build Indexes on the columns that are in your 'where' Criteria, that will help you big time. If you post the table structure, and the query, I can help you with your index. -Original Message- From: Jason Williard [mailto:[EMAIL PROTECTED] Sent: Thursday, October 07, 2004 1:23 AM To: Wadhwa, Amit Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Long Running Queries I'm sorry to sound like a novice, but could you explain that? Are you recommending that I set the column I am searching to an Index? Thank You, Jason Williard Client Services [EMAIL PROTECTED] wrote: You need Appropriate Indexes on the tables. -Original Message- From: Jason Williard [mailto:[EMAIL PROTECTED] Sent: Thursday, October 07, 2004 1:11 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Long Running Queries Hello, I recently began experiencing issues that I would like some assistance with. Server Details: - Windows 2003 - MySQL Max 4.0.20a I have a table with 450,000+ records in it. When I try to run a single query, such as selecting 1 row or deleting 1 row using the WHERE clause, the query can take forever to run. In a specific case, it took 57 seconds to complete the SELECT query. Any ideas on how to speed this up? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Long Running Queries
Before: 57 seconds After: 1 second :-D Thank You, Jason Williard Client Services Spenser wrote: I wish we had thought to have had him benchmark a query before and after he added an index. It would be interesting to see the difference in actual time that an index can make on a table with 450,000 records. On Wed, 2004-10-06 at 15:31, Jason Williard wrote: /Amit, You are awesome! That fixed it quite nicely. Our system is screaming now :-) Thank You VERY MUCH!, Jason Williard Client Services [EMAIL PROTECTED] wrote: Do this and tell me if it helped ALTER TABLE `asticketsdata` ADD INDEX ( `ticketidchar` ) Regards, Amit -Original Message- From: Jason Williard [mailto:[EMAIL PROTECTED] Sent: Thursday, October 07, 2004 1:42 AM To: Wadhwa, Amit Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Long Running Queries Thank you for your assistance with this. Here's what you were asking for. Structure Output: # # Table structure for table 'asticketsdata' # CREATE TABLE asticketsdata ( ticketdataid int(11) NOT NULL auto_increment, ticketidchar varchar(255) NOT NULL default '', ticketidno int(11) default NULL, contents text, emailbody text, PRIMARY KEY (ticketdataid), KEY ticketdata1 (ticketidno) ) TYPE=MyISAM; Query: SELECT * FROM asticketsdata WHERE ticketidchar = 'IAZ-16393'; Thank You, Jason Williard Client Services [EMAIL PROTECTED] wrote: I have tables with millions of records. 1. I use Query-Caching and that speeds up all my selects/searches after the first time, no matter how complicated the criteria. 2. Build Indexes on the columns that are in your 'where' Criteria, that will help you big time. If you post the table structure, and the query, I can help you with your index. -Original Message- From: Jason Williard [mailto:[EMAIL PROTECTED] Sent: Thursday, October 07, 2004 1:23 AM To: Wadhwa, Amit Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Long Running Queries I'm sorry to sound like a novice, but could you explain that? Are you recommending that I set the column I am searching to an Index? Thank You, Jason Williard Client Services [EMAIL PROTECTED] wrote: You need Appropriate Indexes on the tables. -Original Message- From: Jason Williard [mailto:[EMAIL PROTECTED] Sent: Thursday, October 07, 2004 1:11 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Long Running Queries Hello, I recently began experiencing issues that I would like some assistance with. Server Details: - Windows 2003 - MySQL Max 4.0.20a I have a table with 450,000+ records in it. When I try to run a single query, such as selecting 1 row or deleting 1 row using the WHERE clause, the query can take forever to run. In a specific case, it took 57 seconds to complete the SELECT query. Any ideas on how to speed this up? / -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Changing LAST_INSERT_ID()/AUTO_INCREMENT()
I have a ticketing system that sets the ID of the ticket as the LAST_INSERT_ID. By default, it was counting up from 0. I updated the ID of the last ticket to reflect a different numbering scheme (MM). I would like to automate this but I don't want to change the ID of a current ticket. I was hoping that there was a way to update the LAST_INSERT_ID. Is this possible? If so, what is the query that should be run? Thank You, Jason Williard Janix
RE: Changing LAST_INSERT_ID()/AUTO_INCREMENT()
Thanks, Mark, for the syntax. I had actually played around enough to
figure it out already and got it to work the way that I wanted to. I
guess when I asked the question, I wasn't fully aware of what I was
wanting. The LAST_INSERT_ID wasn't a major issue. I ended up using the
following code:
$new_increment = date('Ym') * 1 + 1;
$query = mysql_query( ALTER TABLE `$calls` AUTO_INCREMENT =
$new_increment );
if ( !$query ) {
die( Couldn't Alter Table! );
}
else{
echo Successfully updated AUTO_INCREMENT: $new_increment\n;
}
After a few tests, it seems to be working properly. I've gone ahead and
setup a cron job to take care of this once a month.
Thanks again,
Jason
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
