Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The title pretty much sums up the extent of my question.

They seem to appear after every statement.

Sometimes on a new line following. At other times, at the end before the ;.

What's the point?

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

They are evidently place markers within the binary logs for processing internal statements according to the context of the commands issued.

When you performed mysqlbinlog against a binary log, you see it acting as a delimiter. There are also opportunities for MySQL to process certain directives native to the particular version of MySQL cranking out the binary log.

Example:

I ran the following commands:

reset master;
use test
show binary logs;
create table rolandotable (a int);
show binary logs;
insert into rolandotable values (1);
flush logs;
show binary logs;

I got this:

mysql> reset master;
Query OK, 0 rows affected (0.21 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       107 |
+------------------+-----------+
1 row in set (0.01 sec)

mysql> use test
Database changed
mysql> create table rolandotable (a int);
Query OK, 0 rows affected (0.22 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       203 |
+------------------+-----------+
1 row in set (0.00 sec)

mysql> insert into rolandotable values (1);
Query OK, 1 row affected (0.05 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.18 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       439 |
| mysql-bin.000002 |       107 |
+------------------+-----------+
2 rows in set (0.00 sec)

Here comes the mysqlbinlog dump of mysql-bin.000001

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#111221 14:56:13 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.12-log created 111221 14:56:13 at startup
ROLLBACK/*!*/;
BINLOG '
3TnyTg8BAAAAZwAAAGsAAAAAAAQANS41LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADdOfJOEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#111221 14:56:59 server id 1  end_log_pos 203   Query   thread_id=8     exec_time=0     error_code=0
use test/*!*/;
SET TIMESTAMP=1324497419/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C cp850 *//*!*/;
SET @@session.character_set_client=4,@@session.collation_connection=4,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table rolandotable (a int)
/*!*/;
# at 203
#111221 14:58:24 server id 1  end_log_pos 271   Query   thread_id=8     exec_time=0     error_code=0
SET TIMESTAMP=1324497504/*!*/;
BEGIN
/*!*/;
# at 271
#111221 14:58:24 server id 1  end_log_pos 369   Query   thread_id=8     exec_time=0     error_code=0
SET TIMESTAMP=1324497504/*!*/;
insert into rolandotable values (1)
/*!*/;
# at 369
#111221 14:58:24 server id 1  end_log_pos 396   Xid = 99
COMMIT/*!*/;
# at 396
#111221 14:58:29 server id 1  end_log_pos 439   Rotate to mysql-bin.000002  pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

Most lines have /*!*/ as a delimter between entries. Sometimes, a special directive needs to executed between commands, such as this tag: /*!\C cp850 *//*!*/;

That directive, \C, sets a character set with mysql client for processing multibyte charsets within binlogs. I learned that from the help command in mysql client:

mysql> \?

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
notee     (\t) Don't write into outfile.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

mysql>

There are still other directives, such as the first two lines:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

The first line works as of MySQL 4.0.19

The second line works as of MySQL 5.0.3

If you run mysqlbinlog and import that output to a machine running MySQL 4.x, the second line is ignored.

I also addressed this type of directive from the output of mysqldump concerning stored procedures. That way, If you import a MySQL 5.0 mysqldump into 4.x, all commands to create stored procedures are completely ignored.

This is the agenda for using /*!*/

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.