Scenario Master – Master replication

MasterA is a client facing server
MasterB is a warm standby server (read only)

MasterB restarted abruptly and when instances were braught back up MasterA (it’s slave) was showing the following error:

MasterA has the following error in show slave status:
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’

Solution:

Slave: stop slave;

Master: flush logs
Master: show master status; — take note of the master log file and master log position

Slave: CHANGE MASTER TO MASTER_LOG_FILE=’log-bin.00000X′, MASTER_LOG_POS=106;
Slave: start slave;

When setting up a query using a temporary lookup table, I got this error:

ERROR 1137 (HY000): Can't reopen table: 'tmp_journals'

It transpires that since 4.1 the way MySQL handles temporary tables has changed. This affects joins, unions and subqueries. There is an obvious fix:

mysql> CREATE TEMPORARY TABLE tmp_journals_2 LIKE tmp_journals;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tmp_journals_2 SELECT * FROM tmp_journals;
Query OK, 3228659 rows affected (2.01 sec)
Records: 3228659  Duplicates: 0  Warnings: 0 

Then the query is easy:

SELECT COUNT(1) cnt, journal_invoice_ref
FROM tmp_journals
GROUP BY journal_date 
HAVING cnt > 10000</pre>

UNION

SELECT COUNT(1) cnt, journal_invoice_ref
FROM tmp_journals_2
GROUP BY journal_invoice_ref
HAVING cnt < 10

If you have seen the following error on the Perf Report tab “Message: SimpleXMLElement::__construct()…” – it has been fixed in revision 281. This only affects alerts 11 and 12 so you might not run into it immediately. The solution is to either remove lines “586, 590, 639, 650″ from the bin/kontroll-reporter-5.0.x_linux-x86-2.0.1.pl file, or to run [...]

The Problem

I've had MySQL on my Windows 7 laptop for a bit (as part of wampserver), mostly for local offline WordPress development.

However, even though MySQL is relatively stable, I've been observing a vast quantity of intermittent MySQL errors, as reported by WordPress in the PHP error log (C:\wamp\logs\php_error.log). Here are some examples:

[05-Jan-2010 09:47:51] WordPress database error Error on delete of
'C:\Windows\TEMP\#sql17e0_1a2_6.MYD' (Errcode: 13) for query SELECT t.*, tt.* 
FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id 
INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = 
tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (3) 
ORDER BY t.name ASC made by require, require_once, include, get_footer, 
locate_template, load_template, require_once, dynamic_sidebar, 
call_user_func_array, …

Error message:Slave I/O thread: error reconnecting to masterLast_IO_Error: error connecting to masterDiagnosis:Check that the slave can connect to the master instance, using the following steps:Use ping to check the master is reachable. eg ping master.yourdomain.comUse ping with ip address to check that DNS isn't broken. eg. ping 192.168.1.2Use mysql client to connect from slave to master. eg

Error message: Slave I/O thread: error reconnecting to master Last_IO_Error: error connecting to master Diagnosis: Check that the slave can connect to the master instance, using the following steps: Use ping to check the master is reachable. eg ping master.yourdomain.com … Continue reading →

Came across an error on the sql for the overview.php page. It wasn’t correctly displaying aggregate data on the charts. So here’s the fix for rev50 to rev51. This isn’t a deal breaker on the release but if you’re inclined to open system/application/models/model_main.php and replace one line it’s easier than waiting for our next release.

2001c2001
< $sql = "select $xval,DATE_FORMAT(Creation_time,'%m-%d %H:%i') as Date from server_statistics WHERE Creation_time BETWEEN '$sday' AND '$eday' GROUP BY DAY(Creation_time),HOUR(Creation_time) ORDER BY Creation_time";
---
> $sql = "select max($xval) as $xval,DATE_FORMAT(Creation_time,'%m-%d %H:%i') as Date from server_statistics WHERE Creation_time BETWEEN '$sday' AND '$eday' GROUP BY DAY(Creation_time),HOUR(Creation_time) ORDER BY Creation_time";

Fixed a lingering JS issue with the ExtJS code in header.php file that was preventing IE7 from loading the main.php page. It was….. an extra comma! If you are inclined to edit one file here is the diff change, otherwise like other bug fixes it will be included with the next release. The file is system/application/views/header.php and the change is for line #327 for revision 42+.

< {id: 'innodb_total', header: "innodb_total", width: 75, sortable: true, renderer: 'fileSize', dataIndex: 'innodb_total'},
---
> {id: 'innodb_total', header: "innodb_total", width: 75, sortable: true, renderer: 'fileSize', dataIndex: 'innodb_total'}


If you run IE7 and have tried Kontrollbase you may have noticed an infinite progress bar for the authentication. While login works on all other browsers, it was broken on IE7. The change has been committed to the repo and will be available in the next release. If you are inclined to edit one file here is the diff change. The file is system/application/controllers/login.php and the change is for line #68.

68c68
< $user_system_user_id = $this->session->userdata('user_system_user_id');
---
> $user_system_user_id = $this->phpsession->get('user_system_user_id');


I was setting up a slave manually for a quick-and-dirty experiment, when I found this one:

mysql  %gt; slave start;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

Hmm... I did the CHANGE MASTER TO already, but just for the sake of it, let's do it again.

mysql %gt; change master to master_host='127.0.0.1', master_port=22346, 
master_user='msandbox', master_password='msandbox', 
master_log_file='mysql-bin.000002', master_log_pos=106;
Query OK, 0 rows affected (0.00 sec)

mysql  %gt; slave start;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

Again! Why is it asking to execute CHANGE MASTER TO, which has been accepted right now?

Then it came to me. Because I did a manual configuration, and I …