Practical experience with MariaDB vs MySQL
Results 1 to 4 of 4

Thread: Practical experience with MariaDB vs MySQL

  1. #1
    Senior Member
    Join Date
    Apr 2003
    Location
    Flanders Fields
    Posts
    5,833

    Practical experience with MariaDB vs MySQL

    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:
    Code:
    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.
    IMPORTANT: STOP using the mysql extension. Use mysqli or pdo instead.
    World War One happened 100 years ago. Visit Old Grey Horror for the agony and irony.

  2. #2
    Senior Member
    Join Date
    Apr 2016
    Posts
    121
    Never mind. What I tried didn't produce the correct results.
    Last edited by pbismad; 08-20-2017 at 06:58 PM.
    Programming should not be a painful activity. If you are experiencing pain while programming, you are probably doing something wrong.

  3. #3
    Senior Member
    Join Date
    Apr 2003
    Location
    Flanders Fields
    Posts
    5,833
    I've managed to fix this one query I think:
    Code:
    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.
    IMPORTANT: STOP using the mysql extension. Use mysqli or pdo instead.
    World War One happened 100 years ago. Visit Old Grey Horror for the agony and irony.

  4. #4
    High Energy Magic Dept. NogDog's Avatar
    Join Date
    Aug 2006
    Location
    Ankh-Morpork
    Posts
    14,843
    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-...supported.html (shrug?)
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •