Quoter::serialize_list() doesn't handle multiple NULL values

Bug #1087319 reported by Daniel Nichter on 2012-12-06
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to https://jira.percona.com/projects/PT
Fix Released
Brian Fraser

Bug Description

Quoter::seralize_list() dies on multiple NULL values because at first we couldn't represent multiple NULLs because <,> could be two empty strings or two NULLs, and <NULL,NULL> could be two literal string values of "NULL". But there is a way to do this: MySQL does it when it writes outfiles (SELECT INTO OUTFILE) by representing \N as NULL (so \\N is a literal "\N" string).

Related branches

description: updated
description: updated
Brian Fraser (fraserbn) on 2012-12-11
Changed in percona-toolkit:
assignee: nobody → Brian Fraser (fraserbn)
Brian Fraser (fraserbn) on 2012-12-12
Changed in percona-toolkit:
status: Triaged → In Progress
Changed in percona-toolkit:
importance: Undecided → Medium
status: In Progress → Fix Committed
tags: added: pt-online-schema-change pt-table-checksum pt-table-sync value-quoting
removed: all-tools
Daniel Nichter (daniel-nichter) wrote :
Download full text (5.1 KiB)

This affects pt-online-schema-change pt-table-checksum, and pt-table-sync. The first two use NibbleIterator; pt-table-sync uses the --replicate table, hence the serialized boundary values, from pt-table-checksum.

In 2.1, the relevant subs (Quoter::serialize_list() and Quoter::deserialize_list()) acted like:

   die "Cannot serialize multiple values with undef/NULL"
      if grep { !defined $_ } @args;

   return join ',', map { quotemeta } @args;


   my @escaped_parts = $string =~ /
         \G # Start of string, or end of previous match.
         ( # Each of these is an element in the original list.
            [^\\,]* # Anything not a backslash or a comma
            (?: # When we get here, we found one of the above.
               \\. # A backslash followed by something so we can continue
               [^\\,]* # Same as above.
            )* # Repeat zero of more times.
         , # Comma dividing elements

   push @escaped_parts, pos($string) ? substr( $string, pos($string) ) : $string;

   my @unescaped_parts = map {
      my $part = $_;
      # Here be weirdness. Unfortunately quotemeta() is broken, and exposes
      # the internal representation of scalars. Namely, the latin-1 range,
      # \128-\377 (\p{Latin1} in newer Perls) is all escaped in downgraded
      # strings, but left alone in UTF-8 strings. Thus, this.

      # TODO: quotemeta() might change in 5.16 to mean
      # qr/(?=\p{ASCII})\W|\p{Pattern_Syntax}/
      # And also fix this whole weird behavior under
      # use feature 'unicode_strings' -- If/once that's
      # implemented, this will have to change.
      my $char_class = utf8::is_utf8($part) # If it's a UTF-8 string,
                     ? qr/(?=\p{ASCII})\W/ # We only care about non-word
                                             # characters in the ASCII range
                     : qr/(?=\p{ASCII})\W|[\x{80}-\x{FF}]/; # Otherwise,
                                             # same as above, but also
                                             # unescape the latin-1 range.
      $part =~ s/\\($char_class)/$1/g;
   } @escaped_parts;

   return @unescaped_parts;

So rather complex. The UTF-8 stuff was motivated by a bug in DBD::mysql 3.0007 which CentOS 5 uses: it doesn't flag UTF-8 data as UTF-8. Brian says stuff was quoted because "so that we didn't run afoul of any perl / MYsql INTERACTIONS like inserting '\n' and getting back "\n"".

As for the UTF-8 bug, we tested and discuseed and decided that it should be ok, i.e. it won't affect anything. I'll blog about this later.

As for quoting, I don't think it's necessary because boundary values are straight from the table, so we save exactly whatever we got.

Consequently, this branch, which will be the new standard in 2.2, works like:

   my @parts;
   foreach my $arg ( @args ) {
      if ( defined $arg ) {
         $arg =~ s/,/\\,/g; # escape commas
         $arg =~ s/\\N/\\\\N/g; # escape literal \N
         push @parts, $arg;
      else {
         push @parts, '\N';

   my $string = join(',',...


Changed in percona-toolkit:
status: Fix Committed → Fix Released

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-599

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers