function statement($sql) { #$m = memory_get_usage(true); #echo "IN STATEMENT: ALLOCATED MEMORY {bytes:$m mega:" . ($m / 1024 / 1024) . " giga:" . ($m / 1024 / 1024 / 1024) . "}\n"; if ($sql[0] == '/') { $end_comment = strpos($sql, ' '); $sql = trim(substr($sql, $end_comment, strlen($sql) - $end_comment)); } $command = ""; $args = ""; $space_pos = strpos($sql, ' '); if ($space_pos !== false) { $command = trim(strtoupper(trim(substr($sql, 0, $space_pos)))); $args = trim(substr($sql, $space_pos)); } else { $command = trim($sql); } if ($args) { if ($command !== 'DELIMITER') { $args = str_replace($this->delimiter, '', $args); } } else { $command = str_replace($this->delimiter, '', $command); } switch (strtoupper(trim($command))) { #register change in delimiter so that we properly capture statements case 'DELIMITER': $this->delimiter = $args; break; #ignore SET and USE for now. I don't think we need it for anything. #ignore SET and USE for now. I don't think we need it for anything. case 'SET': break; case 'USE': $this->activeDB = $args; break; #NEW TRANSACTION #NEW TRANSACTION case 'BEGIN': #echo "STARTING NEW TRANSACTION!\n"; $this->start_transaction(); break; case 'BINLOG': unset($sql); $args = trim($args, "'\n"); #$m = memory_get_usage(true); #echo "BEFORE BINLOG PARSE: ALLOCATED MEMORY {bytes:$m mega:" . ($m / 1024 / 1024) . " giga:" . ($m / 1024 / 1024 / 1024) . "}\n"; $this->binlog_parser->set('uow_id', $this->uow_id); $this->binlog_parser->set('server_id', $this->serverId); $this->binlog_parser->consume($args); $this->gsn_hwm = $this->binlog_parser->get('gsn'); #$m = memory_get_usage(true); #echo "AFTER CONSUME: ALLOCATED MEMORY {bytes:$m mega:" . ($m / 1024 / 1024) . " giga:" . ($m / 1024 / 1024 / 1024) . "}\n"; #$this->binlog_parser->reset(); #$m = memory_get_usage(true); #echo "AFTER RESET: ALLOCATED MEMORY {bytes:$m mega:" . ($m / 1024 / 1024) . " giga:" . ($m / 1024 / 1024 / 1024) . "}\n"; break; #END OF BINLOG, or binlog terminated early, or mysqlbinlog had an error #END OF BINLOG, or binlog terminated early, or mysqlbinlog had an error case 'ROLLBACK': $this->binlog_parser->reset(); #echo "DO ROLLBACK WORK!\n"; $this->rollback_transaction(); break; case 'COMMIT': $this->binlog_parser->reset(); #echo "DO COMMIT WORK\n"; $this->commit_transaction(); break; #Might be interestested in CREATE statements at some point, but not right now. #Might be interestested in CREATE statements at some point, but not right now. case 'CREATE': break; #DML IS BAD....... :( #DML IS BAD....... :( case 'INSERT': case 'UPDATE': case 'DELETE': case 'REPLACE': case 'TRUNCATE': /* TODO: If the table is not being logged, ignore DML on it... */ if ($this->raiseWarnings) { trigger_error('Detected statement DML on a table! Changes can not be tracked!', E_USER_WARNING); } break; case 'RENAME': #TODO: Find some way to make atomic rename atomic. split it up for now $tokens = FlexCDC::split_sql($sql); $clauses = array(); $new_sql = ''; $clause = ""; for ($i = 4; $i < count($tokens); ++$i) { #grab each alteration clause (like add column, add key or drop column) if ($tokens[$i] == ',') { $clauses[] = $clause; $clause = ""; } else { $clause .= $tokens[$i]; } } if ($clause) { $clauses[] = $clause; } $new_clauses = ""; foreach ($clauses as $clause) { $clause = trim(str_replace($this->delimiter, '', $clause)); $tokens = FlexCDC::split_sql($clause); $old_table = $tokens[0]; if (strpos($old_table, '.') === false) { $old_base_table = $old_table; $old_table = $this->activeDB . '.' . $old_table; $old_schema = $this->activeDB; } else { $s = explode(".", $old_table); $old_schema = $s[0]; $old_base_table = $s[1]; } $old_log_table = str_replace('.', '_', $old_table); $new_table = $tokens[4]; if (strpos($new_table, '.') === false) { $new_schema = $this->activeDB; $new_base_table = $new_table; $new_table = $this->activeDB . '.' . $new_table; } else { $s = explode(".", $new_table); $new_schema = $s[0]; $new_base_table = $s[1]; } $new_log_table = str_replace('.', '_', $new_table); $clause = "{$old_log_table} TO {$new_log_table}"; $sql = "DELETE from `" . $this->mvlogs . "` where table_name='{$old_base_table}' and table_schema='{$old_schema}'"; my_mysql_query($sql, $this->dest) or die1($sql . "\n" . mysql_error($this->dest) . "\n"); $sql = "REPLACE INTO `" . $this->mvlogs . "` (mvlog_name, table_name, table_schema) values ('{$new_log_table}', '{$new_base_table}', '{$new_schema}')"; my_mysql_query($sql, $this->dest) or die1($sql . "\n" . mysql_error($this->dest) . "\n"); $sql = 'RENAME TABLE ' . $clause; @my_mysql_query($sql, $this->dest); # or die1('DURING RENAME:\n' . $new_sql . "\n" . mysql_error($this->dest) . "\n"); my_mysql_query('commit', $this->dest); $this->mvlogList = array(); } break; #ALTER we can deal with via some clever regex, when I get to it. Need a test case #with some complex alters #ALTER we can deal with via some clever regex, when I get to it. Need a test case #with some complex alters case 'ALTER': /* TODO: If the table is not being logged, ignore ALTER on it... If it is being logged, modify ALTER appropriately and apply to the log.*/ $tokens = FlexCDC::split_sql($sql); $is_alter_table = -1; foreach ($tokens as $key => $token) { if (strtoupper($token) == 'TABLE') { $is_alter_table = $key; break; } } if (!preg_match('/\\s+table\\s+([^ ]+)/i', $sql, $matches)) { return; } if (empty($this->mvlogList[str_replace('.', '', trim($matches[1]))])) { return; } $table = $matches[1]; #switch table name to the log table if (strpos($table, '.')) { $s = explode('.', $table); $old_schema = $s[0]; $old_base_table = $s[1]; } else { $old_schema = $this->activeDB; $old_base_table = $table; } unset($table); $old_log_table = $s[0] . '_' . $s[1]; #IGNORE ALTER TYPES OTHER THAN TABLE if ($is_alter_table > -1) { $clauses = array(); $clause = ""; for ($i = $is_alter_table + 4; $i < count($tokens); ++$i) { #grab each alteration clause (like add column, add key or drop column) if ($tokens[$i] == ',') { $clauses[] = $clause; $clause = ""; } else { $clause .= $tokens[$i]; } } $clauses[] = $clause; $new_clauses = ""; $new_log_table = ""; $new_schema = ""; $new_base_Table = ""; foreach ($clauses as $clause) { $clause = trim(str_replace($this->delimiter, '', $clause)); #skip clauses we do not want to apply to mvlogs if (!preg_match('/^ORDER|^DISABLE|^ENABLE|^ADD CONSTRAINT|^ADD FOREIGN|^ADD FULLTEXT|^ADD SPATIAL|^DROP FOREIGN|^ADD KEY|^ADD INDEX|^DROP KEY|^DROP INDEX|^ADD PRIMARY|^DROP PRIMARY|^ADD PARTITION|^DROP PARTITION|^COALESCE|^REORGANIZE|^ANALYZE|^CHECK|^OPTIMIZE|^REBUILD|^REPAIR|^PARTITION|^REMOVE/i', $clause)) { #we have four "header" columns in the mvlog. Make it so that columns added as #the FIRST column on the table go after our header columns. $tokens = preg_split('/\\s/', $clause); if (strtoupper($tokens[0]) == 'RENAME') { if (strtoupper(trim($tokens[1])) == 'TO') { $tokens[1] = $tokens[2]; } if (strpos($tokens[1], '.') !== false) { $new_log_table = $tokens[1]; $s = explode(".", $tokens[1]); $new_schema = $s[0]; $new_base_table = $s[1]; } else { $new_base_table = $tokens[1]; $new_log_table = $this->activeDB . '.' . $tokens[1]; } $new_log_table = str_replace('.', '_', $new_log_table); $clause = "RENAME TO {$new_log_table}"; } if (strtoupper($tokens[0]) == 'ADD' && strtoupper($tokens[count($tokens) - 1]) == 'FIRST') { $tokens[count($tokens) - 1] = 'AFTER `fv$gsn`'; $clause = join(' ', $tokens); } if ($new_clauses) { $new_clauses .= ', '; } $new_clauses .= $clause; } } if ($new_clauses) { $new_alter = 'ALTER TABLE ' . $old_log_table . ' ' . $new_clauses; my_mysql_query($new_alter, $this->dest) or die1($new_alter . "\n" . mysql_error($this->dest) . "\n"); if ($new_log_table) { $sql = "DELETE from `" . $this->mvlogs . "` where table_name='{$old_base_table}' and table_schema='{$old_schema}'"; my_mysql_query($sql, $this->dest) or die1($sql . "\n" . mysql_error($this->dest) . "\n"); $sql = "INSERT INTO `" . $this->mvlogs . "` (mvlog_name, table_name, table_schema) values ('{$new_log_table}', '{$new_base_table}', '{$new_schema}')"; my_mysql_query($sql, $this->dest) or die1($sql . "\n" . mysql_error($this->dest) . "\n"); $this->mvlogList = array(); } } } break; #DROP probably isn't bad. We might be left with an orphaned change log. #DROP probably isn't bad. We might be left with an orphaned change log. case 'DROP': /* TODO: If the table is not being logged, ignore DROP on it. * If it is being logged then drop the log and maybe any materialized views that use the table.. * Maybe throw an errro if there are materialized views that use a table which is dropped... (TBD)*/ if ($this->raiseWarnings) { trigger_error('Detected DROP on a table! This may break CDC, particularly if the table is recreated with a different structure.', E_USER_WARNING); } break; #I might have missed something important. Catch it. #Maybe this should be E_USER_ERROR #I might have missed something important. Catch it. #Maybe this should be E_USER_ERROR default: #if($this->raiseWarnings) trigger_error('Unknown command: ' . $command, E_USER_WARNING); if ($this->raiseWarnings) { trigger_error('Unknown command: ' . $command, E_USER_WARNING); } break; } }
function statement($sql) { $sql = trim($sql); #TODO: Not sure if this might be important.. # In general, I think we need to worry about character # set way more than we do (which is not at all) if (substr($sql, 0, 6) == '/*!\\C ') { return; } if ($sql[0] == '/') { $end_comment = strpos($sql, ' '); $sql = trim(substr($sql, $end_comment, strlen($sql) - $end_comment)); } preg_match("/([^ ]+)(.*)/", $sql, $matches); //print_r($matches); $command = $matches[1]; $command = str_replace($this->delimiter, '', $command); $args = $matches[2]; switch (strtoupper($command)) { #register change in delimiter so that we properly capture statements case 'DELIMITER': $this->delimiter = trim($args); break; case 'SET': my_mysql_query($sql, $this->dest); break; case 'USE': $this->activeDB = trim($args); $this->activeDB = str_replace($this->delimiter, '', $this->activeDB); break; #NEW TRANSACTION #NEW TRANSACTION case 'BEGIN': $this->start_transaction(); break; #END OF BINLOG, or binlog terminated early, or mysqlbinlog had an error #END OF BINLOG, or binlog terminated early, or mysqlbinlog had an error case 'ROLLBACK': $this->rollback_transaction(); break; case 'COMMIT': $this->commit_transaction(); break; #Might be interestested in CREATE statements at some point, but not right now. #Might be interestested in CREATE statements at some point, but not right now. case 'CREATE': break; #DML IS BAD....... :( #DML IS BAD....... :( case 'INSERT': case 'UPDATE': case 'DELETE': case 'REPLACE': case 'TRUNCATE': /* TODO: If the table is not being logged, ignore DML on it... */ if ($this->raiseWarnings) { trigger_error('Detected statement DML on a table! Changes can not be tracked!', E_USER_WARNING); } break; case 'RENAME': #TODO: Find some way to make atomic rename atomic. split it up for now $tokens = FlexCDC::split_sql($sql); $clauses = array(); $new_sql = ''; $clause = ""; for ($i = 4; $i < count($tokens); ++$i) { #grab each alteration clause (like add column, add key or drop column) if ($tokens[$i] == ',') { $clauses[] = $clause; $clause = ""; } else { $clause .= $tokens[$i]; } } if ($clause) { $clauses[] = $clause; } $new_clauses = ""; foreach ($clauses as $clause) { $clause = trim(str_replace($this->delimiter, '', $clause)); $tokens = FlexCDC::split_sql($clause); $old_table = $tokens[0]; if (strpos($old_table, '.') === false) { $old_base_table = $old_table; $old_table = $this->activeDB . '.' . $old_table; $old_schema = $this->activeDB; } else { $s = explode(".", $old_table); $old_schema = $s[0]; $old_base_table = $s[1]; } $old_log_table = 'mvlog_' . md5(md5($old_schema) . md5($old_base_table)); $new_table = $tokens[4]; if (strpos($new_table, '.') === false) { $new_schema = $this->activeDB; $new_base_table = $new_table; $new_table = $this->activeDB . '.' . $new_table; } else { $s = explode(".", $new_table); $new_schema = $s[0]; $new_base_table = $s[1]; } $new_log_table = 'mvlog_' . md5(md5($new_schema) . md5($new_base_table)); $clause = "{$old_log_table} TO {$new_log_table}"; $sql = "DELETE from `" . $this->mvlogs . "` where table_name='{$old_base_table}' and table_schema='{$old_schema}'"; my_mysql_query($sql, $this->dest) or die1($sql . "\n" . mysql_error($this->dest) . "\n"); $sql = "REPLACE INTO `" . $this->mvlogs . "` (mvlog_name, table_name, table_schema) values ('{$new_log_table}', '{$new_base_table}', '{$new_schema}')"; my_mysql_query($sql, $this->dest) or die1($sql . "\n" . mysql_error($this->dest) . "\n"); $sql = 'RENAME TABLE ' . $clause; @my_mysql_query($sql, $this->dest) or die1('DURING RENAME:\\n' . $new_sql . "\n" . mysql_error($this->dest) . "\n"); my_mysql_query('commit', $this->dest); $this->mvlogList = array(); $this->refresh_mvlog_cache(); } break; case 'ALTER': $tokens = FlexCDC::split_sql($sql); $is_alter_table = -1; foreach ($tokens as $key => $token) { if (strtoupper($token) == 'TABLE') { $is_alter_table = $key; break; } } if (!preg_match('/\\s+table\\s+([^ ]+)/i', $sql, $matches)) { return; } if (empty($this->mvlogList[str_replace('.', '', trim($matches[1]))])) { return; } $table = $matches[1]; #switch table name to the log table if (strpos($table, '.')) { $s = explode('.', $table); $old_schema = $s[0]; $old_base_table = $s[1]; } else { $old_schema = $this->activeDB; $old_base_table = $table; } unset($table); $old_log_table = 'mvlog_' . md5(md5($old_schema) . md5($old_base_table)); #IGNORE ALTER TYPES OTHER THAN TABLE if ($is_alter_table > -1) { $clauses = array(); $clause = ""; for ($i = $is_alter_table + 4; $i < count($tokens); ++$i) { #grab each alteration clause (like add column, add key or drop column) if ($tokens[$i] == ',') { $clauses[] = $clause; $clause = ""; } else { $clause .= $tokens[$i]; } } $clauses[] = $clause; $new_clauses = ""; $new_log_table = ""; $new_schema = ""; $new_base_Table = ""; foreach ($clauses as $clause) { $clause = trim(str_replace($this->delimiter, '', $clause)); #skip clauses we do not want to apply to mvlogs if (!preg_match('/^ORDER|^DISABLE|^ENABLE|^ADD CONSTRAINT|^ADD FOREIGN|^ADD FULLTEXT|^ADD SPATIAL|^DROP FOREIGN|^ADD KEY|^ADD INDEX|^DROP KEY|^DROP INDEX|^ADD PRIMARY|^DROP PRIMARY|^ADD PARTITION|^DROP PARTITION|^COALESCE|^REORGANIZE|^ANALYZE|^CHECK|^OPTIMIZE|^REBUILD|^REPAIR|^PARTITION|^REMOVE/i', $clause)) { #we have four "header" columns in the mvlog. Make it so that columns added as #the FIRST column on the table go after our header columns. $tokens = preg_split('/\\s/', $clause); if (strtoupper($tokens[0]) == 'RENAME') { if (strtoupper(trim($tokens[1])) == 'TO') { $tokens[1] = $tokens[2]; } if (strpos($tokens[1], '.') !== false) { $s = explode(".", $tokens[1]); $new_schema = $s[0]; $new_base_table = $s[1]; } else { $new_base_table = $tokens[1]; $new_schema = $this->activeDB; } $new_log_table = 'mvlog_' . md5(md5($new_schema) . md5($new_base_table)); $clause = "RENAME TO {$new_log_table}"; } if (strtoupper($tokens[0]) == 'ADD' && strtoupper($tokens[count($tokens) - 1]) == 'FIRST') { $tokens[count($tokens) - 1] = 'AFTER `fv$gsn`'; $clause = join(' ', $tokens); } if ($new_clauses) { $new_clauses .= ', '; } $new_clauses .= $clause; } } if ($new_clauses) { $new_alter = 'ALTER TABLE ' . $old_log_table . ' ' . $new_clauses; my_mysql_query($new_alter, $this->dest) or die1($new_alter . "\n" . mysql_error($this->dest) . "\n"); if ($new_log_table) { $sql = "DELETE from `" . $this->mvlogs . "` where table_name='{$old_base_table}' and table_schema='{$old_schema}'"; my_mysql_query($sql, $this->dest) or die1($sql . "\n" . mysql_error($this->dest) . "\n"); $sql = "INSERT INTO `" . $this->mvlogs . "` (mvlog_name, table_name, table_schema) values ('{$new_log_table}', '{$new_base_table}', '{$new_schema}')"; my_mysql_query($sql, $this->dest) or die1($sql . "\n" . mysql_error($this->dest) . "\n"); $this->mvlogList = array(); $this->refresh_mvlog_cache(); } else { $this->table_get_column_names($old_schema, $old_base_table, true); // column names may have been changed, invalidate cache } } } break; #DROP probably isn't bad. We might be left with an orphaned change log. #DROP probably isn't bad. We might be left with an orphaned change log. case 'DROP': /* TODO: If the table is not being logged, ignore DROP on it. * If it is being logged then drop the log and maybe any materialized views that use the table.. * Maybe throw an errro if there are materialized views that use a table which is dropped... (TBD)*/ if ($this->raiseWarnings) { trigger_error('Detected DROP on a table! This may break CDC, particularly if the table is recreated with a different structure.', E_USER_WARNING); } break; #I might have missed something important. Catch it. #Maybe this should be E_USER_ERROR #I might have missed something important. Catch it. #Maybe this should be E_USER_ERROR default: #if($this->raiseWarnings) trigger_error('Unknown command: ' . $command, E_USER_WARNING); if ($this->raiseWarnings) { trigger_error('Unknown command: ' . $command, E_USER_WARNING); } break; } }