- discuss - lists.mariadb.org

[Maria-discuss] Retrieve authentication plugins for user
by William Edwards 13 Oct '22

13 Oct '22

Hi, When creating an account with multiple authentication plugins, in this case 'unix_socket' OR 'mysql_native_password', the mysql.user.plugin column is set to either one. How should I retrieve *all* used authentication plugins for a given user? Example: ``` MariaDB [(none)]> CREATE USER 'testing'@'localhost' IDENTIFIED VIA unix_socket OR mysql_native_password USING 'invalid'; Query OK, 0 rows affected (0.005 sec) MariaDB [(none)]> select user,host,password,authentication_string,plugin from mysql.user where User = 'testing'; +---------+-----------+----------+-----------------------+-----------------------+ | User | Host | Password | authentication_string | plugin | +---------+-----------+----------+-----------------------+-----------------------+ | testing | localhost | invalid | invalid | mysql_native_password | +---------+-----------+----------+-----------------------+-----------------------+ 1 row in set (0.003 sec) ``` William Edwards

2 1

[Maria-discuss] What is the right way to update based on a DB change?
by ngtech1ltd＠gmail.com 03 Oct '22

03 Oct '22

Hey Everybody, I have the next scenario which I am not sure about the right way for a solution. I have a radius server that authenticate clients from multiple WIFI Access Points. I have couple systems and they need to know the current client login identifier (MAC or IP Address). It is stored in the DB and the DB is being updated on every login. I can try to give a static IP for every user and it will resolve this issue but there is an overhead for this. So what I was thinking instead is to either run a query that will fetch the latest changes or use a trigger to update another table which in turn will be read by an external software every 1-5 seconds. I believe that the right solution is to use a trigger that will update another table with the details and on the other table I will have a Processed column that will be updated when the software finished processing the row. Then in turn every 24 hours I will run a DB cleanup cron. I will try later on to share the schema. Any advice is more than welcome. Thanks, Eliezer ---- Eliezer Croitoru NgTech, Tech Support Mobile: +972-5-28704261 Email: <mailto:ngtech1ltd@gmail.com> ngtech1ltd(a)gmail.com Web: <https://ngtech.co.il/> https://ngtech.co.il/ My-Tube: <https://tube.ngtech.co.il/> https://tube.ngtech.co.il/

3 3

[Maria-discuss] LevelDB and KV best performance
by ngtech1ltd＠gmail.com 23 Aug '22

23 Aug '22

Hey, I have been using BDB and LevelDB and other KV embedded DB as an improvement for SquidGuard. I have seen the next MDEV: https://jira.mariadb.org/browse/MDEV-3841 And was wondering what is the most recommended way to store a simple KV list in MaraiDB these days. Currently MariaDB works fine for me but when I am comparing the performance of BDB to MariaDB even if am iterating a full DB file 6 times to find out if 6 different Keys are present it seems like a simple embedded DB is faster then MariaDB. I am not stating a fact since I don’t have the metrics but this is what it seems like. The DB is mainly for READ so I was thinking about an option to use a master and READ ONLY slaves to speedup the DB. The DB contains about 80 million rows so it’s not that big since the only thing I need is the KEY to match a category. Currently I am using a simple InnoDB table and the buffers are maximized to the maximum that the machine can offer. Thanks, Eliezer ---- Eliezer Croitoru NgTech, Tech Support Mobile: +972-5-28704261 Email: <mailto:ngtech1ltd@gmail.com> ngtech1ltd(a)gmail.com Web: <https://ngtech.co.il/> https://ngtech.co.il/ My-Tube: <https://tube.ngtech.co.il/> https://tube.ngtech.co.il/

1 0

[Maria-discuss] Change case with unique constraint
by Keith Edmunds 22 Aug '22

22 Aug '22

I can't change the case of a column with a unique index, example below. MariaDB [test]> show columns in kae; +-------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(2048) | NO | UNI | NULL | | +-------+---------------+------+-----+---------+----------------+ 2 rows in set (0.001 sec) MariaDB [test]> select * from kae; +----+------+ | id | name | +----+------+ | 1 | test | +----+------+ 1 row in set (0.001 sec) MariaDB [test]> update kae set name="Test" where id=1; ERROR 1062 (23000): Duplicate entry 'Test' for key 'unique_name' MariaDB [test]>

4 3

[Maria-discuss] "Rewind" type of mechanisms?
by Sam R. 19 Aug '22

19 Aug '22

Hi! I would like to know about following topic regarding MariaDB.We would like to run DB cluster with HA (high availability). -- E.g. with MaxScale. My Q:Does MariaDB support such mechanism which would be similar to pg_rewind? Reference: PostgreSQL supports such a rewind operation.Or, are there plans to support such regarding MariaDB? ( If such would be needed / useful. ) Rewind: "A typical scenario is to bring an old primary server back online after failover as a standby that follows the new primary." Target: robustness/stability of a DB cluster. Avoiding needs to manually fix DB cluster. Best Regards Sam

3 7

[Maria-discuss] mysql_upgrade and error 'Table rebuild required. Please do "ALTER TABLE `table1` FORCE" or dump/reload to fix it!'
by Karl Levik 18 Aug '22

18 Aug '22

Hello list, After upgrading a database from 10.5 to 10.6 I get messages like below for a couple of the tables when I run mysql_upgrade: error : Table rebuild required. Please do "ALTER TABLE `table1` FORCE" or dump/reload to fix it! But at the end of the output it says: Repairing tables `database1`.`table1` FIXED `database1`.`table2` FIXED .... and so on - it lists all the tables that it previously said I needed to rebuild as "fixed". So, does that mean I don't need to do anything, or should I still run the ALTER TABLE statements? It seems I can SELECT + DELETE from and INSERT into the tables without getting any errors, so presumably I don't have to worry? Regards, Karl

1 0

[Maria-discuss] Repos empty?
by William Edwards 15 Aug '22

15 Aug '22

Hello, The Debian repos @ https://mariadb.org/download/?t=repo-config don't seem to contain any packages and no Release file anymore. Last updated yesterday at 18:41 (IDK which timezone). E.g. http://ams2.mirrors.digitalocean.com/mariadb/repo/10.3/debian/dists/buster/… (the issue isn't with this specific mirror). When choosing a repo on mariadb.org, the error "An error occured. The mirrors could not be loaded." is shown. -- With kind regards, William Edwards

2 4

[Maria-discuss] Major upgrade instructions missing for Galera
by William Edwards 10 Aug '22

10 Aug '22

Hi, The documentation at https://mariadb.com/kb/en/upgrading-galera-cluster/ is missing major upgrade instructions > MariaDB 10.4. Someone else caught this as well: https://mariadb.com/kb/en/upgrading-from-104-to-105-galera-changes/ Is this intentional? -- With kind regards, William Edwards

3 6

[Maria-discuss] ibtmp1 for internal temporary tablespaces
by William Edwards 09 Aug '22

09 Aug '22

Hi, User-created temporary tables are stored in ibtmp1. Are internal temporary tablespaces (those created by MariaDB while executing queries) too? I am unable to find the answer in the documentation[1]. Context: I'd like to store ibtmp1 in a different path using innodb_temp_data_file_path, but ONLY for user-created temporary tables. William Edwards [1]: https://mariadb.com/kb/en/innodb-temporary-tablespaces/ -- With kind regards, William Edwards

2 1

[Maria-discuss] Some questions about mariadb-dump and mariabackup
by jkmdb＠w3.org 02 Aug '22

02 Aug '22

Hi! Sorry to bother you about this but I haven't found the answers in the doc. ** mariadb-dump Could you tell me what is the difference of calling mariadb-dump on innodb tables using --skip-lock-tables --single-transaction? >From what I read these seem to be time / performace optimizations for doing innodb tables dumps but if we omit them, we would get the same data dumped, even it takes longer. Could you confirm this? ** mariabackup We have a setup that stores the binary logs under /var/log/mysql (set up with the log_bin configuration option) and the databases themselves under /var/lib/mysql Suppose I call mariabackup to make a backup of the database as follows (some options obmitted): mariabackup --backup --target-dir=my_backup_dir Could you tell me if calling mariabackup --prepare --target-dir=my_backup_dir needs access to the binary logs stored under /var/log/mysql or if mariabackup made a copy of all that it needs in my_backup_dir? ** mariabackup and incremental backups This is basically the same question as the above. If i'm making incremental backups as follows (some options obmitted): mariabackup --backup \ --target-dir=my_backup_dir/inc1 --incremental-basedir=full_backup_dir When I do the prepare for the incremental backups, does mariabackup need access to the /var/log/mysql bin logs or do the full + each incremental backup dirs has all the info needed to do the prepare? mariabackup --prepare \ --target-dir=my_backup_dir/inc1 --incremental-basedir=full_backup_dir Many thanks in advance! --josé

3 2