The latest from the MySQL community

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 …

I was running a hard drive diagnostic using SeaTools for DOS v1.09PH on two exactly the same Maxtor hard drives, see details of the drives below. When I attempted to run a quick scan and then a long scan it received a warning “Overtemp–253″. After doing some research I have come to the conclusion that with certainty that this doesn’t mean the hard drive is running at 253 degrees Fahrenheit. My two drives are well ventilated and do not even feel warm to the touch. In fact this message apparently means according to Seagate that the hard drive does not support SeaTools reading temperatures.

• Brand: Maxtor
• Model: 6Y060L0
• FW: YAR41BW0

Source: Incorrect temperature values for a Maxtor disk