public function execute()
{
if (!$this->isReady('host', 'path')) {
return self::IGNO;
}
self::metadata($flush);
$where = trim((string) $this->option('where'));
if ('' == $where) {
$query = sprintf('DROP TABLE IF EXISTS %s', $this->option('path'));
} else {
$query = sprintf('DELETE FROM %s WHERE %s', $this->option('path'), $where);
$this->optimize = true;
}
$ignore = array_flip(explode(',', (string) $this->status));
foreach (explode(',', trim($this->option('host', '{}'))) as $id) {
if (!isset(self::$hosts[$id]) || isset($ignore[$id])) {
continue;
}
$ignore[$id] = true;
$server = self::$hosts[$id];
$db = Server::instance($server['name'])->getlink();
$this->dbpools[] = array($db, $db->async($query), $server['name']);
}
return self::WAIT;
}
/**
* 执行check table操作
*/
public function execute()
{
if (!$this->isReady('host', 'path')) {
return self::IGNO;
}
$exist = sprintf('DESC %s', self::$mysql->escape($this->option('path')));
$query = sprintf('CHECK TABLE %s', self::$mysql->escape($this->option('path')));
$return = self::FAIL;
$ignore = array_flip(explode(',', (string) $this->status));
foreach (explode(',', trim($this->option('host', '{}'))) as $id) {
if (!isset(self::$hosts[$id]) || isset($ignore[$id])) {
continue;
}
$mysql = Server::instance(self::$hosts[$id]['name'])->getlink();
if ((bool) $mysql->query($exist)) {
// @see: http://dev.mysql.com/doc/refman/5.1/en/check-table.html
$ok = false;
foreach (array('FAST', 'MEDIUM', 'MEDIUM', 'MEDIUM', 'EXTENDED') as $mode) {
if (self::success($mysql->getAll($mysql->query(sprintf('%s %s', $query, $mode))))) {
$ok = true;
break;
}
}
if (!$ok) {
continue;
}
}
$return = self::SUCC;
$ignore[$id] = true;
}
return $return;
}
public function test_should_server_get_link_works_fine()
{
$ob = Server::instance('edp1_9801')->getlink();
$this->assertTrue($ob instanceof \Myfox\Lib\Mysql);
$my = Server::instance('edp1_9801')->getlink();
$this->assertEquals($ob, $my);
}
/**
* 执行
*
* @access public
* @return Boolean true or false (next loop)
*/
public function execute($loop = true)
{
$mysql = \Myfox\Lib\Mysql::instance('default');
$count = 0;
$query = sprintf('SELECT host_id AS id, host_name AS name FROM %shost_list', $mysql->option('prefix'));
foreach ((array) $mysql->getAll($mysql->query($query)) as $host) {
$db = Server::instance($host['name'])->getlink();
try {
$databases = $db->getAll($db->query('SHOW DATABASES'));
} catch (\Exception $e) {
$this->log->error('EXCEPTION', $e->getMessage());
continue;
}
foreach ((array) $databases as $dbname) {
$dbname = reset($dbname);
if (preg_match('/^(mysql|test|information_schema)$/', $dbname) || !preg_match('/^\\w+_\\d+$/', $dbname)) {
continue;
}
foreach ((array) $db->getAll($db->query('SHOW TABLE STATUS FROM ' . $dbname)) as $row) {
$table = sprintf('%s.%s', $dbname, $row['Name']);
$logvar = array('server' => $host['name'], 'table' => $table, 'engine' => $row['Engine'], 'create' => $row['Create_time'], 'update' => $row['Update_time'], 'check' => $row['Check_time']);
if (true !== $this->option['full'] && $row['Check_time'] > $row['Update_time'] && !empty($row['Engine'])) {
$this->log->debug('CHECK_IGN1', $logvar);
continue;
}
switch (self::realcheck($table, $db)) {
case 0:
$this->log->debug('CHECK_IGN2', $logvar);
break;
case 1:
$this->log->notice('CHECK_OK', $logvar);
break;
default:
$this->log->debug('CHECK_FAIL', $logvar);
break;
}
if (++$count % 10 == 0) {
self::breakup();
}
}
}
}
return false;
}
public function test_should_import_consist_works_fine()
{
$hosts = self::$mysql->getAll(self::$mysql->query(sprintf('SELECT host_id, host_name FROM %shost_list WHERE host_type = %d', self::$mysql->option('prefix'), Server::TYPE_REALITY)));
$create = "CREATE TABLE IF NOT EXISTS test.test_a (\n id int(10) unsigned not null auto_increment primary key,\n num1 int(10) not null default 0,\n char1 varchar(15) not null default ''\n ) ENGINE=MYISAM DEFAULT CHARSET=UTF8";
$ids = array();
foreach ($hosts as $server) {
$mysql = Server::instance($server['host_name'])->getlink();
$mysql->query('DROP TABLE IF EXISTS test.test_a');
$mysql->query($create);
$this->assertEquals(3, $mysql->query("INSERT INTO test.test_a (id,num1,char1) VALUES (1, 2, 'bbb'),(2,3,'cccc'),(3,4,'dddd')"));
$ids[] = (int) $server['host_id'];
}
$this->assertEquals(1, self::$mysql->query(sprintf("INSERT INTO %sroute_info (real_table,hosts_list,table_name,modtime,route_flag)" . " VALUES ('test.test_a', '%s', 'test',%d,%d)", self::$mysql->option('prefix'), implode(',', $ids), time(), \Myfox\App\Model\Router::FLAG_IMPORT_END)));
\Myfox\App\Setting::set('monitor_consist_check', date('Y-m-d H:i:s', time() - 3600));
$this->assertEquals(array(), Monitor::import_consist_monitor());
$server = reset($hosts);
Server::instance($server['host_name'])->getlink()->query(sprintf('DELETE FROM test.test_a ORDER BY id ASC LIMIT 1'));
\Myfox\App\Setting::set('monitor_consist_check', date('Y-m-d H:i:s', time() - 3600));
$result = Monitor::import_consist_monitor();
$this->assertTrue(!empty($result));
$result = reset($result);
$this->assertTrue(!empty($result['checks']));
}
/**
* 单机装入
*
* @access private
* @return Mixture
*/
private function onehost($host, $fname, $engine = 'MYISAM')
{
self::metadata($flush);
if (!isset(self::$hosts[$host])) {
return;
}
$table = Table::instance($this->option('table'));
$mysql = Server::instance(self::$hosts[$host]['name'])->getlink();
$this->pools[$host] = array('server' => self::$hosts[$host]['name'], 'handle' => null, 'commit' => array(), 'rollback' => array());
list($dbname, $tbname) = explode('.', $this->option('bucket'), 2);
$querys = array(sprintf('CREATE DATABASE IF NOT EXISTS %s', $dbname), sprintf('CREATE TABLE IF NOT EXISTS %s (%s) ENGINE=%s DEFAULT CHARSET=UTF8', $this->option('bucket'), 'BRIGHTHOUSE' == $engine ? $table->sqlcreate('ib') : $table->sqlcreate(), $engine));
if ($this->option('replace', $table->get('load_type', 0))) {
array_unshift($querys, sprintf('DROP TABLE IF EXISTS %s', $this->option('bucket')));
$this->pools[$host]['commit'] = array();
$this->pools[$host]['rollback'] = array(sprintf('DROP TABLE IF EXISTS %s', $this->option('bucket')));
} else {
if ('BRIGHTHOUSE' == $engine) {
$this->pools[$host]['commit'] = array('COMMIT');
$this->pools[$host]['rollback'] = array('ROLLBACK');
} else {
$maxid = (int) $mysql->getOne($mysql->query(sprintf('SELECT MAX(%s) FROM %s', $table->autokid(), $this->option('bucket'))));
if ($maxid) {
$this->pools[$host]['rollback'] = array(sprintf('DELETE FROM %s WHERE %s > %u', $this->option('bucket'), $table->autokid(), $maxid));
} else {
$this->pools[$host]['rollback'] = array('DROP TABLE IF EXISTS %s', $this->option('bucket'));
}
}
}
if ('BRIGHTHOUSE' == $engine) {
array_push($querys, 'SET AUTOCOMMIT=0');
}
foreach ($querys as $sql) {
if (false === $mysql->query($sql)) {
$this->setError($mysql->lastError());
return false;
}
}
$import = $table->get('sql_import');
if (empty($import)) {
$import = self::IMPORTSQL;
}
if ('MYISAM' == $engine) {
$import = preg_replace('/\\s+ENCLOSED\\s+BY\\s+("|\')?NULL("|\')?/i', ' ENCLOSED BY ""', $import);
}
$this->pools[$host]['handle'] = $mysql->async(strtr($import, array('{FILE}' => $fname, '{TABLE}' => $this->option('bucket'), '{FS}' => chr(1), '{TAB}' => chr(9))));
}
/**
* 获取某个分片表的unique key
* @param {Array} route 某路由信息
* @return {Array} unique key数组
*/
private function get_unique_keys($route, $keys = array())
{
$hosts = preg_split('/,/', $route['hosts_list'], -1, PREG_SPLIT_NO_EMPTY);
$poped = array_pop($hosts);
if (substr($poped, -1) === '$') {
array_push($hosts, substr($poped, 0, strlen($poped) - 1));
}
$mysql;
$host = (int) array_pop($hosts);
try {
$mysql = Server::instance($this->hosts[$host])->getlink();
} catch (\Exception $e) {
$this->log->error('GET SERVER FAULT', array('host' => $host));
return false;
}
$sql = 'SELECT %s FROM ' . $this->mysql->escape($route['real_table']);
$result = array();
if (empty($keys)) {
$columns = $mysql->getAll($mysql->query(sprintf('DESCRIBE %s', $this->mysql->escape($route['real_table']))));
if (empty($columns)) {
return false;
}
$part = '';
foreach ($columns as $column) {
if (!preg_match('/(int\\([1]?[0-9]\\))|(varchar\\([1-6]?[0-9]\\))/i', $column['Type']) || preg_match('/(num$)|(max$)|(min$)|(pv$)|(uv$)|(pv$)|(uv$)/i', $column['Field'])) {
continue;
}
$part = $part . sprintf('COUNT(DISTINCT(%s)) AS %s,', $this->mysql->escape($column['Field']), $this->mysql->escape($column['Field']));
}
$part = $part . 'COUNT(*) AS total';
$get = $mysql->getAll($mysql->query(sprintf($sql, $part)));
foreach ($columns as $column) {
if (empty($get[0][$column['Field']])) {
continue;
}
if ($get[0][$column['Field']] >= $get[0]['total'] * 0.8) {
array_push($result, $column['Field']);
}
}
} else {
$part = '';
foreach ($keys as $key) {
$part = $part . sprintf('COUNT(DISTINCT(%s)) AS %s,', $this->mysql->escape($key), $this->mysql->escape($key));
}
$part = $part . 'COUNT(*) AS total';
$get = $mysql->getAll($mysql->query(sprintf($sql, $part)));
foreach ($keys as $key) {
if ($get[0][$key] >= $get[0]['total'] * 0.8) {
array_push($result, $key);
}
}
}
return $result;
}
/**
* 获取表结构
*
* @access private static
* @return void
*/
private static function describe($table, $server, $dbname)
{
foreach ((array) $server as $name) {
$mysql = Server::instance($name)->getlink();
$column = $mysql->getAll($mysql->query(sprintf('DESC %s', $table)));
if (!empty($column)) {
break;
}
}
if (empty($column)) {
return false;
}
$pk = 'autokid';
$cl = array();
foreach ((array) $column as $row) {
if (0 === strcasecmp('PRI', $row['Key'])) {
$pk = $row['Field'];
}
if (0 !== strcasecmp('auto_increment', $row['Extra'])) {
$cl[] = $row['Field'];
}
}
self::$columns[$dbname] = array('prikey' => $pk, 'column' => implode(',', $cl));
return true;
}
/**
* 单机装入
*
* @access private
* @return Mixture
*/
private function onehost($host, $fname)
{
self::metadata($flush);
if (!isset(self::$hosts[$host])) {
return;
}
$table = Table::instance($this->option('table'));
$mysql = Server::instance(self::$hosts[$host]['name'])->getlink();
$this->pools[$host] = array('server' => self::$hosts[$host]['name'], 'handle' => null, 'commmit' => array(), 'rollback' => array());
list($dbname, $tbname) = explode('.', $this->option('bucket'), 2);
$querys = array(sprintf('CREATE DATABASE IF NOT EXISTS %s', $dbname), sprintf('CREATE TABLE IF NOT EXISTS %s (%s) ENGINE=MyISAM DEFAULT CHARSET=UTF8', $this->option('bucket'), $table->sqlcreate()));
if ($this->option('replace', $table->get('load_type', 0))) {
array_unshift($querys, sprintf('DROP TABLE IF EXISTS %s', $this->option('bucket')));
$this->pools[$host]['commmit'] = array();
$this->pools[$host]['rollback'] = array(sprintf('DROP TABLE IF EXISTS %s', $this->option('bucket')));
} else {
$maxid = (int) $mysql->getOne($mysql->query(sprintf('SELECT MAX(%s) FROM %s', $table->autokid(), $this->option('bucket'))));
if ($maxid) {
$this->pools[$host]['rollback'] = array(sprintf('DELETE FROM %s WHERE %s > %u', $this->option('bucket'), $table->autokid(), $maxid));
} else {
$this->pools[$host]['rollback'] = array('DROP TABLE IF EXISTS %s', $this->option('bucket'));
}
}
foreach ($querys as $sql) {
if (false === $mysql->query($sql)) {
$this->setError($mysql->lastError());
return false;
}
}
$import = $table->get('sql_import');
if (empty($import)) {
$import = self::IMPORTSQL;
}
$this->pools[$host]['handle'] = $mysql->async(strtr($import, array('{FILE}' => $fname, '{TABLE}' => $this->option('bucket'))));
}
public function test_should_import_numsplit_to_ib_works_fine()
{
$task = new \Myfox\App\Task\Import(-1, array('table' => 'numsplit_v2'));
$this->assertEquals(Task::IGNO, $task->execute());
self::cleanTable('default', 'route_info');
\Myfox\App\Model\Router::set('numsplit_v2', array(array('field' => array('thedate' => '2011-06-10', 'cid' => 1), 'count' => 1201), array('field' => array('thedate' => '2011-06-10', 'cid' => 2), 'count' => 998)));
$task = new \Myfox\App\Task\Import(10, array('table' => 'numsplit_v2', 'route' => 'cid=1,thedate=20110610', 'file' => realpath(__DIR__ . '/resource/numsplit_import_data_file.txt'), 'bucket' => 'numsplit_0.t_2_0', 'hosts' => '4,5', 'engine' => 'BRIGHTHOUSE'), '1,1,999999,-98');
$this->drop_test_table('ibtest_1', 'numsplit_0.t_2_0');
$this->drop_test_table('ibtest_2', 'numsplit_0.t_2_0');
$this->assertEquals(Task::WAIT, $task->execute());
$this->assertEquals(Task::SUCC, $task->wait());
$this->assertEquals('4,5', $task->result());
$ib1 = \Myfox\App\Model\Server::instance('ibtest_1')->getlink();
$ib2 = \Myfox\App\Model\Server::instance('ibtest_2')->getlink();
$this->assertContains('BRIGHTHOUSE', json_encode($ib1->getAll($ib1->query('SHOW CREATE TABLE numsplit_0.t_2_0'))));
$this->assertContains('BRIGHTHOUSE', json_encode($ib2->getAll($ib1->query('SHOW CREATE TABLE numsplit_0.t_2_0'))));
$this->assertEquals(10, $ib1->getOne($ib1->query('SELECT COUNT(*) FROM numsplit_0.t_2_0')));
$this->assertEquals(10, $ib2->getOne($ib2->query('SELECT COUNT(*) FROM numsplit_0.t_2_0')));
}
private static function check_table_exists($host, $table)
{
return (bool) \Myfox\App\Model\Server::instance($host)->getlink()->query(sprintf('DESC %s', $table));
}