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

[Daniel Nichter]

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).

[Daniel Nichter (daniel-nichter)]

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;
"""

then,

"""
   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
      /sxgc;

   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;
      $part;
   } @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(',',...

[Shahriyar Rzayev (rzayev-sehriyar)]

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