I've been helping to upgrade a site that has run for years on mysql. The old DB server runs mysql 5.1.67. The new db server runs MariaDB 10.1.25. I've already run into one query that runs fine on the old server but won't run on the new:

INSERT INTO `foo_PROD`.`images`
  SELECT
    id,
    vid,
    IF(
        (container_cdn_uri REGEXP ('^http://[a-z0-9[.hyphen.]]+[.period.]r[0-9]{1,2}[.period.]cf1[.period.]rackcdn[.period.]com')),
        CONCAT("https://", MID(container_cdn_uri, 8, (LOCATE(".r", container_cdn_uri) - 8)), ".ssl.cf1.rackcdn.com"),
        container_cdn_uri
    ),
    container_name,
    root_filename,
    filename_extension,
    is_default,
    time_added,
    original_width,
    original_height,
    remote_url_hash,
    time_added_local,
    3
  FROM `bar_prod`.`images`;

The complaint is about the POSIX syntax in the regext (e.g., [.hyphen.]):

#1139 - Got error 'POSIX collating elements are not supported at offset 15' from regexp

Does anyone have any thoughts about these? I'm kinda thinking I should insist that we run MySQL on the new server too for quality/compatibility's sake but also worry about Oracle turning into real jerks with MySQL in the near future.

    Never mind. What I tried didn't produce the correct results.

      I've managed to fix this one query I think:

      INSERT INTO `foo_PROD`.`images`
  SELECT
    id,
    vid,
    IF(
        (container_cdn_uri REGEXP ('^http://[a-z0-9\\-]+\\.r[0-9]{1,2}\\.cf1\\.rackcdn\\.com')),
        CONCAT("https://", MID(container_cdn_uri, 8, (LOCATE(".r", container_cdn_uri) - 8)), ".ssl.cf1.rackcdn.com"),
        container_cdn_uri
    ),
    container_name,
    root_filename,
    filename_extension,
    is_default,
    time_added,
    original_width,
    original_height,
    remote_url_hash,
    time_added_local,
    3
  FROM `bar_prod`.`images`;

      The trick is escaping the escape character.

      That said, I'm still wondering if anyone has thoughts about MariaDB versus MySQL. MariaDB is supposed to be a drop-in replacement for MySQL, but as we see with this problem here, it is not. I wonder if there might be other problems (performance? lack of features?). Seems like we're balancing potential flakiness of a community version of the db engine against the potential evil of Oracle, who know owns MySQL.

        I guess one question would be whether or not the difference you ran into is, in fact, a MariaDB vs. MySQL difference, or whether it's a configuration issue you could also run into in MySQL, or even a version issue you might run into in MySQL? A little Googling did not give me a quick, definitive answer, but I did find at least one example that appears to be someone having the same sort of issue in MySQL: https://cmsdk.com/mysql/mysql-regex-with--character--doesnt-work-anymore-39posix-collating-elements-are-not-supported.html (shrug?)

          Write a Reply...