holland-discuss team mailing list archive

[Andrew Garner <muzazzi@xxxxxxxxx>]

Regarding this sanity check message, this is actually a mysqldump in mysql
v5.0 bug.  Specifically mysqldump has a function where it issues the
flushes related to -the -master-data (or if --lock-all-tables is being
used):

static int do_flush_tables_read_lock(MYSQL *mysql_con)

Where in 5.0 it does:

  return
    ( mysql_query_with_error_report(mysql_con, 0, "FLUSH TABLES") ||
      mysql_query_with_error_report(mysql_con, 0,
                                    "FLUSH TABLES WITH READ LOCK") );

This does write the FLUSH TABLES to the binary log - this is only avoided
by using FLUSH LOCAL TABLES or some variant that tells MySQL not to write
the flush to the binary log.

In 5.1+ mysqldump is a little smarter and you have these statements instead:

    ( mysql_query_with_error_report(mysql_con, 0,
                                    ((opt_master_data != 0) ?
                                        "FLUSH /*!40101 LOCAL */ TABLES" :
                                        "FLUSH TABLES")) ||
      mysql_query_with_error_report(mysql_con, 0,
                                    "FLUSH TABLES WITH READ LOCK") );

So if --master-data is specified it always does a FLUSH LOCAL TABLES
instead.   With just --lock-all-tables but no --master-data, it will still
write FLUSH TABLES to the binary log. :-\

This is largely cosmetic - the binary log positions recorded by holland are
likely fine as long as nothing else is actually writing to the database.
 Holland would have to scan through the binary logs and verify that only
FLUSH TABLES would run to do any better sanity checking.  In MySQL 5.1+
this warning should only come up if some external process actually writes
to slave or mysqldump is slightly misconfigured.

Regarding an empty my.cnf, I am not aware of a bug here.  Holland attempts
to read the [client] section from  ~/.my.cnf and all adds any user options
from [mysql:client] (user,password,host,port,socket).  If the ~/.my.cnf is
not readable or has no [client] section and there are no other credentials
in the backupsets/${name}.conf in the [mysql:client] section, an
effectively empty ${backup_directory}/${backupset}/my.cnf will be generated
in holland 1.0.

~Andrew



On Thu, Apr 11, 2013 at 8:30 AM, Brandon Metcalf <bwmetcalf@xxxxxxxxx>wrote:

> Yes, old version of mysql: 5.0.77.  I'm running Holland 1.0.8 and turning
> off bin-log-position eliminates the message.  I guess I can always rely on
> getting the coordinates from backup.conf?
>
> Another bug seems to be that my.cnf is completely empty in the backup:
>
> # cat newest/my.cnf
> [client]
>
>
>
>
>
> On Thu, Apr 11, 2013 at 6:24 AM, Tim Soderstrom <tim@xxxxxxxxxxxxxxxxxxxxx
> > wrote:
>
>> Oooh if it doesn't work that means you're probably running 5.0 which
>> makes me a sad panda. :) The FLUSH TABLES is probably coming from Holland
>> and, if so, then it is likely a bug. To setting 'bin-log-position = no' to
>> see if you can run a backup. If so, it means that 'stop-slave' and
>> 'bin-log-position' are conflicting with each other. I'm not sure if this
>> was fixed in 1.0.8, but what version of Holland are you on?
>>
>> On Apr 11, 2013, at 8:20 AM, Brandon Metcalf wrote:
>>
>> OK, here's what it contains.  -v is an unrecognized option is my version.
>>
>> [root@www-nym008-02 ~]# mysqlbinlog  --start-position=794214533
>> --stop-position=794214675 ./mysql-bin.000008
>> /*!40019 SET @@session.max_insert_delayed_threads=0*/;
>> /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
>> DELIMITER /*!*/;
>> # at 794214533
>> #130410 20:45:32 server id 1012217115  end_log_pos 794214604 Query
>> thread_id=261537 exec_time=0 error_code=0
>> SET TIMESTAMP=1365651932/*!*/;
>> SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
>> @@session.unique_checks=1/*!*/;
>> SET @@session.sql_mode=0/*!*/;
>> /*!\C utf8 *//*!*/;
>>  SET
>> @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
>> FLUSH TABLES
>> /*!*/;
>> # at 794214604
>> #130410 20:46:02 server id 1012217115  end_log_pos 794214675 Query
>> thread_id=261540 exec_time=0 error_code=0
>> SET TIMESTAMP=1365651962/*!*/;
>> FLUSH TABLES
>> /*!*/;
>> DELIMITER ;
>> # End of log file
>> ROLLBACK /* added by mysqlbinlog */;
>> /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
>>
>>
>> On Thu, Apr 11, 2013 at 6:04 AM, Brandon Metcalf <bwmetcalf@xxxxxxxxx>wrote:
>>
>>> Arg.  Looks like I hit a known bug in an older version of mysql:
>>>
>>> $ mysqlbinlog -v -v -v --start-position=794214533
>>> --stop-position=794214675 mysql-bin.000008
>>> mysqlbinlog: unknown variable 'ssl-ca=/etc/mysql/ca-cert.pem'
>>>
>>> Commenting out this line in /etc/my.cnf doesn't seem to help.  I'm
>>> transferring to another machine to see if I can read it.
>>>
>>> I'm pretty sure nothing is connecting to this server, but I don't have
>>> read_only set.
>>>
>>> I'll update here when I figure out how to read the binlog.
>>>
>>>
>>> On Thu, Apr 11, 2013 at 5:24 AM, Tim Soderstrom <
>>> tim@xxxxxxxxxxxxxxxxxxxxx> wrote:
>>>
>>>> Just to cover the simple case, you have confirmed you're not directly
>>>> writing to the slave outside of replication? Just in case, do you have
>>>> read_only set on the slave? I would say you best bet is to see what writes
>>>> are occurring by looking at the binary log directly:
>>>>
>>>> # mysqlbinlog -v -v -v --start-position=794214533
>>>> --stop-position=794214675 mysql-bin.000008
>>>>
>>>> Tim S.
>>>>
>>>> On Apr 11, 2013, at 5:39 AM, Brandon Metcalf wrote:
>>>>
>>>> > I'm dumping mysql dbs on a server that is both a slave and a master
>>>> and getting the following:
>>>> >
>>>> > 2013-04-10 20:46:03,650 [WARNING] Sanity check on master status
>>>> failed.  Previously recorded mysql-bin.000008:794214533 but currently found
>>>> mysql-bin.000008:794214675
>>>> > 2013-04-10 20:46:03,650 [WARNING] ALERT! Binary log position changed
>>>> during backup!
>>>> >
>>>> > I'm stopping the slave which is indicated in the logs and my options
>>>> looks like:
>>>> >
>>>> > [mysqldump]
>>>> > file-per-database       = yes
>>>> > lock-method             = auto-detect
>>>> > databases               = "wiki", "wordpress_docs"
>>>> > dump-routines           = yes
>>>> > stop-slave              = yes
>>>> > flush-logs              = no
>>>> > bin-log-position        = yes
>>>> >
>>>> > Indeed, the two DBs that I'm dumping have different binlog
>>>> coordinates in the CHANGE MASTER statement in the dumps.
>>>> >
>>>> > What's going on here?  Thanks.
>>>> > _______________________________________________
>>>> > Mailing list: https://launchpad.net/~holland-discuss
>>>> > Post to     : holland-discuss@xxxxxxxxxxxxxxxxxxx
>>>> > Unsubscribe : https://launchpad.net/~holland-discuss
>>>> > More help   : https://help.launchpad.net/ListHelp
>>>>
>>>>
>>>
>>
>>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~holland-discuss
> Post to     : holland-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~holland-discuss
> More help   : https://help.launchpad.net/ListHelp
>
>