Back to MySQL

不知从什么时候开始,Fedora 和 CentOS 里的 MySQL 都给换成了 MariaDB。我一直搞不懂好端端的 MySQL 为什么还要分出一个 MariaDB,更搞不懂 MariaDB 的优越性在哪里,反正 MySQL 对我来说够用了,倒是 MariaDB 安装和启动时使用了不同于 MySQL 的名称,让我很不习惯。比如

yum install mysql-server

换成了

yum install mariadb
service mysqld start

换成了

service mariadb start

幸好,除此之外,一切照旧,用到 MySQL 的 PHP 程序都能继续在 MariaDB 使用,所以我就一直用着 MariaDB。直到今天使用 Magento2,要求 MySQL 5.6 以上版本,而 CentOS 下把 MariaDB 用 yum update 到最新版本,仍是 5.5.40。

于是我被逼着去下载 MySQL 5.6。不用不知道,一用发现 CentOS 下安装 MySQL 超级方便,只要安装好 RPM 包——http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm,就可以恢复使用

yum install mysql-server

执行安装。

早知如此,我真不该碰 MariaDB——这个并没有给我带来实际利益的程序也花了我一天去熟悉。

Change FreePBX default MySQL connection

我可能吹毛求疵了:我感觉如果要修改 FreePBX 里默认的 MySQL 的连接位置好麻烦。

我修改成 MySQL socket 方式连接,而且也修改了 socket file 的位置,然后要修改 FreePBX 好多个文件才能让 FreePBX 对接 MySQL。大部分文件属于 FreePBX 配置文件,还好办,象填表格一样填就是。但 /var/www/html/admin/modules/userman/DB_Helper.class.php 根本没为 MySQL socket 连接方式考虑,DSN 只兼容 host 连接,所以要大改 262 行(以FreePBX 2.11.0.39为例)。

要改成多行:


if (preg_match('/^unix\((.+)\)$/', $amp_conf['AMPDBHOST'], $matches) === 1) {
	$ampdbsocket = $matches[1];
	$dsn = "mysql:unix_socket=".$ampdbsocket.";dbname=".$amp_conf['AMPDBNAME'];
}
else {
	$dsn = "mysql:host=".$amp_conf['AMPDBHOST'].";dbname=".$amp_conf['AMPDBNAME'];
}

直接修改程序是我最不愿意的。FreePBX 似乎也没提供安全升级的办法,所以 User Management 模块一升级,上述修改就作废,FreePBX web portal 就出错。

Use long established business for your hosting

前段时间用了一个名不见经传的公司的 VPS 产品。当时看它性能不错才切换过去,但是它操作极不规范,到期前不催续费,我想主动续费都没有链接可以让我续。突然有一天,它意识到有未收账款,也不通知我付款,就停了我的帐号。然后我付款,重新开通服务。中间中断了大约半天的服务,让我损失惨重——那天以后来自 Google 和 Facebook 的流量突然就下降了。这也让我百思不得其解:

  1. 我知道一个网站的 uptime 是很重要的指标,但是,好像影响过头了?以致我怀疑流量下降不是 uptime 的问题,但转折点就在宕机的那一天,是巧合吗?
  2. Google 和 Facebook 使用同样的算法?

总之,不正规的公司是绝对不敢用了。离开之前,我自己清空数据,以下一行命令删除 MySQL 里所有的数据库,抄来的,很方便。

mysql -uroot -pPASS -e "show databases" | grep -v Database | grep -v mysql| grep -v information_schema| grep -v test | gawk '{print "drop database " $1 ";"}' | mysql -uroot -pPASS

Job for mariadb.service failed bug fixed

Last month I set up a development server using Fedora 20. On that server I wanted to change MySQL socket file from /var/lib/mysql/mysql.sock to /dev/shm/mysql.sock. Basically I wanted to move MySQL socket file to shared memory for performance gain.

I made the change in /etc/my.cnf, but when I tried to start mysqld, I got the error message:

Job for mariadb.service failed. See ‘systemctl status mariadb.service’ and ‘journalctl -xn’ for details.

That was my first time heard about mariadb. I did some background research on mariadb and the relationship between mysqld and mariadb. But that did not help to solve the problem. So I had to revert to the default MySQL socket file /var/lib/mysql/mysql.sock.

Today I had some free minutes and recalled this unresolved issue. To my surprise, I just changed

socket=/var/lib/mysql/mysql.sock

to

socket=/dev/shm/mysql.sock

in /etc/my.cnf. And this time mysqld can start without any errors.

What changes have I made to the system? Nothing but a yum update.

So I assume there was a bug in mariadb (or mysqld) which is fixed by the recent release.

Mariadb updated
Mariadb updated

Change MySQL socket path for FreePBX

FreePBX 12 alpha 如果掉电重启,已多次发生 DB error,改装 FreePBX 11 仍有发生。虽然我已查明这是 MySQL socket 被锁定造成的,删除 /var/lib/mysql/mysql.sock 即可,但嫌 MySQL 不能自行修复,于是想到将 socket 移入内存 /dev/shm,掉电即不复存在,重启时每次都是重新生成 socket,不再有被锁定之忧。

怎么更改 MySQL socket path 呢?其实也不复杂,只需更改三个文件。

1. /etc/my.cnf
MySQL 自身的配置文件

socket=/var/lib/mysql/mysql.sock

更改为

socket=/dev/shm/mysql.sock

2. /etc/freepbx.conf
告诉 FreePBX socket 的新位置

$amp_conf['AMPDBHOST']  = 'localhost';

更改为

$amp_conf['AMPDBHOST']  = 'unix(/dev/shm/mysql.sock)';

你可能觉得 ‘unix(/dev/shm/mysql.sock)’ 的写法很奇怪。我摸索了好久才找到 PEAR DB 所用的 dsn 字符串中 MySQL socket path 的格式。

3. /etc/amportal.conf
告诉 asterisk socket 的新位置

AMPDBHOST=localhost;

更改为

AMPDBHOST=unix(/dev/shm/mysql.sock)

重启 MySQL 和 asterisk,确认 FreePBX System Status 是不是一片优雅的绿色(主要看 Server Status 块中的 Asterisk 和 MySQL 是否正常)?

FreePBX server green status
FreePBX server green status

Reinstate Magento order status after upgrade

Recently I upgraded a Magento store from 1.4.0.1 to 1.5.0.1.

Magento has changed order tables from key-value type to flat structure in 1.4.1.0, which caused a big problem for my upgrade – all orders lost their status and state value.

I have not discovered what exact reason made it happen, but I know it is my store specific, because I have other stores successfully upgraded from 1.3 to 1.5.0.1 without any problems.

Just in case you had the same issue, you can run this mysql query code to reinstate Magento order status(and state as well).


UPDATE magento_sales_flat_order AS o
INNER JOIN (SELECT s1.parent_id, s1.status, c.state
 FROM magento_sales_flat_order_status_history s1
 JOIN (
 SELECT parent_id, MAX(entity_id) AS entity_id
 FROM magento_sales_flat_order_status_history
 GROUP BY parent_id) AS s2
 ON s1.parent_id = s2.parent_id AND s1.entity_id = s2.entity_id
 LEFT JOIN magento_sales_order_status_state AS c
 ON s1.status = c.status
) as s3
ON o.entity_id = s3.parent_id, magento_sales_flat_order_grid AS g
SET o.status = s3.status, o.state = s3.state, g.status = s3.status
WHERE g.entity_id = s3.parent_id;

How to pager array items in Magento

The Mage class Mage_Page_Block_Html_Pager is handy to pager collection items, but it is designed to work with a collection derived from Mage_Core_Model_Mysql4_Collection_Abstract.

Now I run into a situation to pager arbitrary array items. The array items are pre-built and not loaded via a resource model, so the pager lost a clue how to control which items to display. (Which makes me think – Magento is powerful, but not so powerful without the help of mysql – Magento collection functionality rely heavily on mysql operation – it is a bit off topic.)

So I need to tell the pager how to load the array after array items are loaded already. The following is my solution class.

<?php
class Qian_Msdk_Model_PagerableCollection extends Varien_Data_Collection {

/***
* load only current page items
*/
public function load($printQuery = false, $logQuery = false) {
if ($this->isLoaded()) {
return $this;
}
$this->loadForPager();
$this->_setIsLoaded();
return $this;
}

public function loadForPager() {
if (!$this->getPageSize()) {  //not pagerized
return $this;
}
$items = array();
$currentPage = $this->getCurPage();
$i = 0;
foreach ($this->_items as $item) {
if ($i < ($currentPage-1) * $this->getPageSize()) {
$i++;
}
elseif ($i >= $currentPage * $this->getPageSize()) {
break;
}
else {
$items[] = $item;
$i++;
}
}
$this->_items = $items;
return $this;
}

/***
* get the size of collection before pagerize
*/
public function getSize()
{
if (is_null($this->_totalRecords)) {
$this->_totalRecords = count($this->_items);
}
return intval($this->_totalRecords);
}

}

xls is the best data import format for phpmyadmin

尽管我挺不愿意看到这个结果,但事实是 xls () 是在 phpmyadmin 中导入大量数据的最佳选择。xls 优于 csv 和 ods (Open Document Spreadsheet) 格式,尤其是在行数据差异比较大时(比如有 empty cell)。

尽管我用 Open Office Calc 生成原始数据,但保存为 Open Office 嫡出的 ods 格式再导入时,数据变得参差不齐。我正绝望时,试着保存为 xls 格式再导入,一切 OK。

回想 microsoft 所出的各种应用程序,确实有它强大的地方,至少有一点无人能及——在各应用程序间流畅地拷贝、粘贴。

phpMyAdmin 2002 error

最近把家里的测试服务器 Zend Server php 5.2 卸载了,重新安装了 5.3。phpMyAdmin 随着 5.2 卸载也被卸载了。我用

yum install phpMyAdmin

重新安装了 phpMyAdmin。在 config.inc.php 设置了必要的参数,但 phpMyAdmin prompted:

#2002 – The server is not responding (or the local MySQL server’s socket is not correctly configured)

phpMyAdmin 2002 error
phpMyAdmin 2002 error

我浪费了很多时间检查了所有可能的错误,却解决不了问题。最后在 phpmyadmin.net 下载了源码,使用同样的 config.inc.php,顺利登录!

A tool to synchronise Magento database between servers

我时不时需要在测试服务器上加载生产服务器的实时数据,以前都是把数据下载到本地的测试服务器后,手工键入一些命令完成数据加载,每次都要花费几分钟时间。为了避免一再“浪费”这几分钟,我今天一次性投入了几小时完成了一个 php 脚本。虽然这是为 magento 的数据迁移而写的脚本,但我写完一看,用在其他地方也是可以的。

为了安全起见,该脚本是用 php 命令行运行的,所有输出针对 terminal 美化,不是 browser。保存源码为 data_mover.php,同一目录下要有 mysqldump 得到的经 gzip 的 sql 文件,文件名以 FILENAME_PREFIX 开头,以 .sql.gz 结尾。启动时只需键入

/path/to/php -f data_mover.php

即可。

初始化 PDO 对象时,按理只需要 host=localhost,不需要 unix_socket=MYSQL_SOCKET。但奇怪的是,如果通过 apache 调用本程序(虽然不是本程序的初衷,但我希望它在浏览器下也能运行),仅指定 host=localhost 作 PDO __construct() 参数,会产生一个莫名其妙的错误:

SQLSTATE[HY000] [2002] Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)

似乎是 PDO bug。可以通过 host=127.0.0.1 或者追加 unix_socket=MYSQL_SOCKET 来避免。如果通过 php 命令行启动则没有这个问题。

而后,还涉及怎么删除所有数据表的问题。看似简单的一个问题,我发现 mysql 竟然没有一个类似于 DROP/TRUNCATE TABLE * 单行命令。于是除了本脚本用的方法外,我还想了不下两种办法:

其一,删掉整个数据库重新创建。
mysqladmin -f -h localhost -u (DB_ROOT_USERNAME) -p(DB_ROOT_PASSWORD) drop (DB_NAME)
mysqladmin -h localhost -u (DB_ROOT_USERNAME) -p(DB_ROOT_PASSWORD) create (DB_NAME)
但这需要比数据表操作更高权限的用户,在这个无关大局的脚本里去使用高权限的用户的密码,实在非我所愿。

其二,是我 google 来的,方法很巧,但很可惜,因为 foreign keys 的存在,运行这条命令会出错。
mysqldump -u (DB_USERNAME) -p(DB_PASSWORD) –add-drop-table –no-data (DB_NAME) | grep ^DROP | mysql -u (DB_USERNAME) -p(DB_PASSWORD) (DB_NAME)


<?php

define('MYSQL_SOCKET', '/path/to/mysql/socket');
define('DB_NAME', 'db_name');
define('DB_USERNAME', 'db_username');
define('DB_PASSWORD', 'db_password');
define('FILENAME_PREFIX', 'filename_prefix');
define('TEST_URL', 'http://test.domain/');

if ($handle = opendir(dirname(__FILE__))) {
$found = false;
/* This is the correct way to loop over the directory. */
while (false !== ($file = readdir($handle))) {
if (substr($file, 0, 12) == FILENAME_PREFIX && substr($file, -7) == '.sql.gz') {
if ($found) {
//compare which one is newer
if (filemtime($file) > filemtime($fileFound)) {
$fileFound = $file;
}
}
else {
$found = true;
$fileFound = $file;
}
}
}

if ($found) {
echo "Found the newest file $fileFound, and will work on it.\n";
try {
$pdo = new PDO(
'mysql:host=localhost;dbname=' . DB_NAME . ';unix_socket=' . MYSQL_SOCKET,
DB_USERNAME,
DB_PASSWORD,
array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")
);
echo "Connected to database.\n";
}
catch (Exception $e) {
die ("Error occurred when connecting to database. Quoting error message: " . $e->getMessage() . "\n");
}
$sql = 'SET FOREIGN_KEY_CHECKS = 0';
$pdo->prepare($sql)->execute();

/* query all tables */
$sql = "SHOW TABLES FROM ". DB_NAME;
$stmt = $pdo->prepare($sql);
$stmt->execute();
$result = $stmtDale->fetchAll(PDO::FETCH_COLUMN, 0);
$magentoTableNames = array();
/* add table name to array */
foreach ($result as $tableName) {
if (substr($tableName, 0, 8) == 'magento_') {
$magentoTableNames[] = $tableName;
}
}

//drop all magento tables
$count = count($magentoTableNames);
if ($count > 0) {
$sql = 'DROP TABLE '. implode(',', $magentoTableNames);
$pdo->prepare($sql)->execute();
echo "Found and dropped $count magento tables.\n";
}
else {
echo "No existing magento tables found.\n";
}

//import data via pipe
echo "Importing data. It may take a while...\n";
$output = shell_exec("gunzip < $fileFound | mysql -h localhost -u " . DB_USERNAME . " -p" . DB_PASSWORD . " " . DB_NAME);
echo "Importing data completed.\n";
if ($output) {
echo "This is output during data import:\n$output\n";
}

//after import, change some data for test domains
$sql = 'UPDATE magento_core_config_data SET value=? WHERE scope=? AND scope_id=? AND path=?';
$stmt = $pdo->prepare($sql);
$stmt->execute(array(TEST_URL, 'default', 0, 'web/unsecure/base_url'));
$stmt->execute(array(TEST_URL, 'default', 0, 'web/secure/base_url'));
$stmt->execute(array(TEST_URL, 'websites', 2, 'web/unsecure/base_url'));
$stmt->execute(array(TEST_URL, 'websites', 2, 'web/secure/base_url'));
echo "Config data changed to fit testing environment.\n";
echo "All missions completed.\n";
}
else {
echo "File not found. What else can I do?\n";
}
closedir($handle);
}
else {
echo "File access not permitted.\n";
}