Questions tagged [mariadb]
Open source RDBMS designed to be a "drop-in" replacement for MySQL. Add a version-specific tag like mariadb-10.2 if that context is important.
0
votes
0answers
6 views
How to optimize SQL query which involves GROUP_CONCAT function with ORDER BY
I wrote the following SQL query to group results and then sort results in each group by date:
SELECT
origin,
provider,
SUBSTRING_INDEX(GROUP_CONCAT(price
ORDER BY departure_date
...
-2
votes
0answers
28 views
How would you do this simple query in the most optimized way MYSQL and also MARIADB
pkid clientname location
1 Jones NY
2 Jones NJ
3 Jones TX
4 Jones FL
5 Jones IA
6 Jones NC
7 Jones SC
8 Jones VA
9 Smith FL
10 Smith CO
...
2
votes
2answers
13 views
First order by importance then group by section
In this table scheme http://sqlfiddle.com/#!9/101a9c/9/0
id section rev importance
2 1 b 6
1 1 a 5
3 2 c 9
5 3 e 6
4 ...
0
votes
0answers
8 views
MariaDB crash after / while table check on small VPS
I'm facing an issue with my MariaDB server on a Debian Linux inside a small LXC container. (A bigger one with dual core and more memory has got the same issue, another identical LXC container is ...
1
vote
1answer
11 views
Why would a file generated by mysqldump be substantially larger than the data directory
I have a MariaDB server - 5.5.60
I have a database xyz that has both MyISAM and InnoDB tables
The majority of the tables are MyISAM but in this database in particular, the data is mainly in InnoDB ...
0
votes
0answers
23 views
Unable to call PASSWORD() with VARCHAR() parameter inside a procedure
DELIMITER //
CREATE PROCEDURE custUpdateInfo(IN customer_id varchar(20), IN passwordd VARCHAR(20), IN named varchar(20), IN addressd VARCHAR(60), IN phone_number DECIMAL(10) UNSIGNED, IN email_id ...
-1
votes
0answers
48 views
Mariadb docker container SQLSTATE[HY000] [2002] No such file or directory error [on hold]
I have developed an web application that consists of a app, a website and a mariadb database. I put them together in a docker-compose file and tested it on my local computer and it works fine. I tried ...
0
votes
0answers
18 views
How do I debug/set up MariaDB SSL connection?
[ I originally posted this on ServerFault but then was pointed at this community ]
Since moving from MySQL to a newer MariaDB installation on Debian 9, I a struggling with SSL connections.
I have ...
0
votes
0answers
30 views
init_connect not executing
From this question I was able to make my init_connect = "CALL mydb.Warnings()", but a user login in does not triggers the SP.
the SP is like:
CREATE DEFINER=`root`@`localhost` PROCEDURE `Warnings`...
0
votes
1answer
16 views
How to use 'mysql -uroot -pXXXX' on Debain 9.8?
My apologies for this lame question. Debian 9.8 changed some things and mysql is broke but I can't seem to fix it.
I installed MySQL on Debian 9.8, which now installs MariaDB 10.1. After install I ...
0
votes
1answer
23 views
Access denied for user 'root'@'localhost' What proces is causing this?
I got this connections constantly in logs, how to trace what app is trying to connect?
root@localhost is allowed to connect and working for some apps. I think there is some app without changed ...
0
votes
1answer
14 views
Mariadb times out on startup after system reboot
This is continued from:
https://stackoverflow.com/q/55071021/8644833
Mariadb shuts down after running for a minute or two. If I remove Mariadb and reinstall (keeping my databases), everything works ...
0
votes
1answer
24 views
Mysql replication rebuild dump database lock
I need to rebuild master-slave replication of whole instance of mariadb on innodb. What I often see is that people do read lock then dump master then unlock, like:
FLUSH TABLES WITH READ LOCK;
SHOW ...
0
votes
0answers
23 views
Encrypting a Database (mariadb/mysql) With Veracrypt
as explained in this question I created a mysql (mariadb) database in a separate volume. In my case it is a veracrypt container.
Even after changing ownership of the mounted folder to mysql, and ...
1
vote
1answer
29 views
Point in time recovery with mariabackup
I have a full backup dir and a bunch of hourly (incremental) backup directories taken with mariabackup, which is a port of the XtraBackup.
How does one restore the MariaDB up to a specific minute (...
1
vote
1answer
40 views
Show a Warning on logon in MariaDB
I pretend to show a personalized message at login in MariaDB ver. 10.1.37 , in accordance with the data in one table.
My idea was to use a stored procedure as shown in fromdual.com but i can't find ...
1
vote
2answers
41 views
User rank based on cumulative rows
UPDATE:
Full table schema and data: https://www.db-fiddle.com/f/jT3Mfwd1BJ8GrsBxxPDC8M/1
Table structure (some fields are hidden):
+---------+---------+------------+-------------+-------+-------+
| ...
1
vote
1answer
26 views
How to fix the sys.host_summary table in MariaDB 10.3.13
using mysqldump while doing a backup gives the following error:
mysqldump: Got error: 1356: "View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view ...
0
votes
1answer
26 views
Upgrading from mysql 5.7 on FreeBSD to mariadb or MySQL8
I am looking into upgrading some major components of a webserver (php, apache, mysql).
I've done the update to 5.7 from 5.6. I've updated to php7.3.
What I wonder now is: I see that a mysql ...
0
votes
0answers
24 views
Determine cause of “MySQL server has gone away”
I've got a long running process on a headless server. It is a C program that monitors a modem for Caller Id and hangs up on telemarketers. On startup the program opens a connection to a MariaDB 10.3 ...
1
vote
2answers
35 views
Expand compact data
For the sake of simplicity, I decided to store a value only when it changes.
+------------+--------+-------+
| date | entity | value |
+------------+--------+-------+
| 2019-01-01 | 1 | ...
0
votes
1answer
19 views
How to use Unicode chars on PHPMyAdmin?
On a WordPress site I want to replace " !" and " ?" by " !" and " ?". The challenge is that the replacement string not uses a common space char but a thin space char (U+2009).
PHPMyAdmin is used for ...
0
votes
2answers
60 views
mariadb 10.3.13 table_open_cache problems
Just upgraded mysql 5.6 to mariadb 10.3.13 - now when the server hits open_tables = 2000, my php queries stop working - if I do a flush tables it starts working correctly again. This never happened ...
0
votes
1answer
34 views
Why does MariaDB choose different index use for the same data?
I have this table (this is the output from show create table on both servers, ie. using diff to compare the output shows only the auto increment value, because one has moved on slightly from the ...
0
votes
1answer
21 views
Cannot import table due to zombie constraint
I tried to refresh my copy of the production database by emptying and importing a SQL file. I've done it hundreds of times, only this time the import failed.
I tried dropping & recreating the ...
0
votes
2answers
54 views
Mysqldump multiple tables with where clauses in single transaction
Suppose, we have 2 tables: t1,t2 in database db:
Expected the below code to dump tables based on conditions specified in the where clauses:
mysqldump --login-path=login_path \
--single-...
-1
votes
0answers
23 views
Sync MySQL (MaraDB) databases through github
Is there a way to be able to sync multiple database through text files on github?
This is for an open source project I am considering and need a way to have local databases to sync on demand. I ...
1
vote
1answer
81 views
MariaDB 10.1.38 - Specified key was too long; max key length is 767 bytes
I have a table that has 3 columns:
id(int 11) | user_id(int 4) | title(varchar 512)
____________|________________|___________________
1 | 3 | Thing X
2 | 3 ...
0
votes
2answers
44 views
MySql | How to compute this [closed]
I have this table
Grade Lec Lab
1.5 3 0
1.5 3 0
1.5 2 1
The formula will be like this
1.5 * 3
1.5 * 3
1.5 * (2+1) = 13.5 / ...
0
votes
2answers
45 views
MySql | How to compute every row in same table
I have this table
Grade Lec Lab
1.50 3 0
1.50 3 0
1.50 2 1
The formula will be
1.50 * 3
1.50 * 3
1.50 * (2 + 1) = 13.5
The results will divide to total number ...
0
votes
1answer
26 views
csv engine and mysql start
my question is about speed when perform service mysql start.
with many tables (some not often used).
what engines consumes more or less start time ?
example: innodb --> the more tables, the more ...
0
votes
1answer
18 views
Batch query a lot of MySQLdump files
I have a website for an event. I set up a cron job to back up the database every hour. The backup command is
mysqldump -u"$DB_USER" -p"$DB_PASS" --database "$DB_NAME" >"${OUTPUT_DIR}/${DB_NAME}.$(...
0
votes
1answer
18 views
Does any MariaDB engine have the option to declaratively or configuratively abstract highly-redundant data?
I have an application which is architected in a "NoSQL" style around one fully denormalized "main table" which currently just holds a primary key and one JSON-valued column. For reasons which are ...
0
votes
1answer
23 views
Question about encrypting multiple MariaDB schemas on the same server
I have 1 server running 1 installation of MariaDB
In that installation of MariaDB, I have a CustomerService database and WorkFlow database.
Can I encrypt CustomerService with one key and WorkFlow ...
0
votes
0answers
12 views
MariaDB Galera journalctl
I've noticed something weird.
I have 3 nodes galera cluster, installed using mariadb.
I'm pretty sure that I did all three nodes installations exactly the same.
All three nodes display their --...
2
votes
2answers
114 views
Unexpected RAM usage on MySQL
We have a server which hosts about 10 customers in a multi-tenant web application, each customer has its own database which is cloned from a template, and each database is a little less than 3000 ...
-1
votes
1answer
23 views
MariaDB - Epoch date comparisons
On MariaDB 10, I'm trying to work with dates, particularly whether the current date is within 7 days of a date stored in the database. A longtext column holds an Epoch datetime, which I convert into a ...
0
votes
0answers
18 views
Returning a set datatype from MariaDB function
Given below is the syntax for the MariaDB's CREATE FUNCTION:
CREATE [OR REPLACE]
[DEFINER = {user | CURRENT_USER | role | CURRENT_ROLE }]
[AGGREGATE] FUNCTION [IF NOT EXISTS] func_name ([...
0
votes
1answer
26 views
MySQL workbench can't deal with MariaDB's new Invisible columns
Recently upgraded to MariaDB 10.3 which has a new feature whereby we can create an invisible column like so:
ALTER TABLE MyTable ADD COLUMN `TestA` INT INVISIBLE;
But once I create this column, ...
1
vote
1answer
50 views
MariaDB 10.3: InnoDB much slower than MyISAM
I'm using MariaDB 10.3.12 on Arch Linux and haven't modified any of the default settings under /etc/mysql. The following script takes 0.75 s to run on average using the default InnoDB engine:
drop ...
1
vote
1answer
29 views
Unexpected results while using HAVING without GROUP BY
Given below is the snapshot of the SQL queries:
MariaDB [university]> select * from department;
+------------+----------+-----------+
| dept_name | building | budget |
+------------+----------+...
1
vote
1answer
26 views
MariaDB 10.2.22 Galera Cluster Using Bad Indexes After Analyze
I'm using Drupal on a MariaDB Galera 10.2.22 3 node cluster and I'm having some problems with one query. I have a table (InnoDB) called field_data_field_department which has a bunch of indexes. If I ...
0
votes
0answers
16 views
Azure MariaDB log_bin_trust_function_creators
I'm testing AzureDB for MariaDB and for my DB to work properly I must change log_bin_trust_function_creators variabl. However, it isn't available in Azure Portal Server Parameters.
I've checked and ...
0
votes
1answer
28 views
Safe online dumping of an InnoDB database in the presence of DDL changes
When dumping an InnoDB database using mysqldump, it is often recommended to use the --single-transaction and --skip-table-locks options. This allows for minimal blocking of other concurrent reads + ...
0
votes
0answers
30 views
SELECT … INTO OUTFILE “Read-only file system” error
I'm using MariaDB 10.3.12 on CentOS 7, using the MariaDB-server RPM package from MariaDB's yum repository.
I'm trying to do a simple SELECT ... INTO OUTFILE into a different directory than /var/lib/...
0
votes
2answers
41 views
What kind of SSD to speed up querying a lot of data?
For research purposes I am using a big MariaDB database (~500 GB) with 20 Tables (engine: myISAM) and around 9 Billion rows. Due to missing free storage on my Laptop (16 GB RAM), I put the data on an ...
0
votes
1answer
16 views
10.3.9-MariaDB can not query equal operator with special character
I have a MariaDB database that has a filed TagName with value ~!@#$%^&*()_+|}{":?><./';[]\=-`
I had set mode SET @@SQL_MODE = CONCAT(@@SQL_MODE, ',NO_BACKSLASH_ESCAPES');
When I try to ...
0
votes
0answers
13 views
Is the result of ST_Union defined?
With MariaDB 10.1, I get a simplified LINESTRING when I provide two lines can be joined.
SELECT ST_AsText(geom)
FROM (
SELECT ST_Union(
LineString(Point(0,0),Point(1,1)),
LineString(Point(1,...
0
votes
1answer
16 views
Does MySQL or MariaDB support a LineString constructor that takes points?
It seems with MySQL and MariaDB I'm getting the following when I try to use the LineString constructor. In PostGIS I can do this with ST_MakeLine(pt1,pt2)
> SELECT ST_MakeLine(Point(0,0), Point(0,...
1
vote
2answers
59 views
Overly eager explicit row locking causes deadlock
I'm running into deadlock problems on MariaDB using the INNODB engine.
In my use case, I've got a number of threads working on a table. Each thread first reads a number of rows and then updates those....
