Mariadb support

[animalcarpet]

I'm going to be using mariadb quite a bit over the coming months so I'm planning to do some testing of the project against various mariadb versions and make any necessary changes. This will probably necessitate some changes with the installer to recognise the different versions and possibly a few other changes to accommodate information_schema differences.

[hepabolu]

That sounds like a really good plan.

[animalcarpet]

The good news is that most of this appears to work out of the box on the versions I've tested. Roles support needs a rewrite because the schema is completely different and Column Defaults are broken because mariadb represents NULL as 'NULL' for default values (so don't try setting that as a string). I will have to abandon the principal of a single test script for all versions.

[hepabolu]

Having to abandon the single script for all versions is a shame. It will make the code harder to maintain.

[animalcarpet]

The problem is that roles work from a different table name with fewer and differently spec'd columns and different syntax for create. As a consequence, even though the test methods called can be the same, the test setup and parameters have to be different and the and not all the same tests are applicable.

…

On Mon, 24 Sep 2018 15:45 Helma van der Linden, ***@***.***> wrote: Having to abandon the single script for all versions is a shame. It will make the code harder to maintain. — You are receiving this because you were assigned. Reply to this email directly, view it on GitHub <#41 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AcQVpj550UP3S0pby0ov65xNHTUNymwcks5ueO_1gaJpZM4W1W8m> .

[sq6jnx]

I think we can workaround this problem with NOT using mysql.user or information_schema.columns and using own views. Example below.

Using:

DROP VIEW IF EXISTS tap.mysql__user;
CREATE SQL SECURITY INVOKER VIEW tap.mysql__user AS
SELECT
    `user`
    , `host`
    /* ... */
    , `password_expired`
    , NULL AS `account_locked`
FROM `mysql`.`user`;

and applying diff:

diff --git a/mytap-user-576.sql b/mytap-user-576.sql
index 0d6e1b9..d2269c3 100644
--- a/mytap-user-576.sql
+++ b/mytap-user-576.sql
@@ -26,11 +26,11 @@ BEGIN
   DECLARE ret BOOLEAN;
 
   SELECT 1 INTO ret
-  FROM `mysql`.`user`
+  FROM tap.mysql__user
   WHERE `host` = hname
   AND `user` = uname
   AND `password_expired` <> 'Y'
-  AND `account_locked` <> 'Y';
+  AND (tap.mysql_variant() = 'MariaDB' OR `account_locked` <> 'Y');
 
   RETURN COALESCE(ret, 0);
 END //

I managed to run some tests from tests/test-mytap-user.my on MariaDB (10.4.8-MariaDB-1:10.4.8+maria~bionic)

[sq6jnx]

OK, I feel we're getting closer. But, I can not understand what is the meaning of these statements:

    SET @sql1 = 'CREATE USER ''__taprole__''@''localhost''';
    SET @sql2 = 'CREATE USER __nohost__';
    SET @sql3 = 'GRANT ''__taprole__''@''localhost'', __nohost__ TO ''__tapuser__''@''localhost''';
    SET @sql4 = 'SET DEFAULT ROLE __nohost__ TO ''__tapuser__''@''localhost''';

Help?

[sq6jnx]

Or better, if anyone can translate it to MariaDB I think I'll be able to do the rest (compatibility layer + tests).