New answers tagged mariadb
0
thanks everyone, lack of experience with multiple versions added to my issues as well assuming that I was running the client of the installed mysql version 5.7 would have me use that version was wrong as it would still load the wrong my.cnf which
/usr/local/mysql2/bin/mysql -u grshop_grpim -p --verbose --help | grep -A 1 "Default options" showed me.
I ...
0
First, you need to make sure MySQL slave user is active and has the needed permission then check if MySQL OS user on the slave machine has the needed access.
0
When you run /usr/local/mysql2/bin/mysql in your last code snippet, that is obviously your MySQL 5.7 mysql command-line client, but the output shows it's connecting to your MariaDB server:
Server version: 5.5.5-10.3.18-MariaDB-log MariaDB Server
If you want it to connect to your MySQL 5.7 server, then you need to specify the non-default port or socket and ...
0
Wrong approach - mariabackup isn't designed to work remotely. See also MDEV-15075.
So you need to run mariabackup locally on one of the nodes in the cluster.
To prevent the backup from slowing down the cluster, you can prevent the node from engaging Flow Control and allow it to fall behind the other nodes by doing:
SET GLOBAL wsrep_desync=ON;
Once ...
2
A partial answer:
$ mysql -u root --column-type-info -v
Welcome to the MySQL monitor. Commands end with ; or \g.
...
Note the extra parameters. Now I will issue just one simple command; see what it comes back with:
mysql> SELECT * FROM things;
--------------
SELECT * FROM things
--------------
ERROR 1046 (3D000): No database selected
mysql> use ...
0
You have a disconnect between what you have on disk and what you have on the index internal data dictionary. MySQL 8.0 has improved how the data dictionary is handled, but up to 10.4 mariadb has not implemented it.
You cannot just drop it, because it won't be found on disk, but innodb still see it. For older versions of MySQL and MariaDB you will find ...
4
Assuming you use a recent enough version* of MariaDB, you can use window functions instead of variables to get a running total:
-- CREATE VIEW
-- running_total AS
SELECT
id, credit, debit,
SUM(credit-debit) OVER (ORDER BY id) AS balance
FROM
tbl_total
ORDER BY
id ;
You can create a view with the above query.
*: Window functions are ...
1
The algorythm as I see it.
We don't know how many replaces we need. So we must use recursive CTE or iterative/recursive code.
In it we must:
Find first pattern occurency offset in source data starting from some offset (initially 1)
Split source by head, pattern and tail
Perform pattern replace and encode
Concat head and encoded replace, calculate its ...
0
Put the options first. That is, all parameters that start with - or -- before the non-option parameters (database account) and redirection (> ... or < ...)
If that does not suffice, then remember that the command line processor is handling the first level of quotes, so change to
"--where=insertDateTime < '2019-08-13 18:00:00'"
(but I am not ...
1
If the Pis are the source of the data, use "multi-source" replication from them to MariaDB.
Then have 3 nodes for the cluster (2 is mostly useless), and have all the data for both databases on all the nodes.
Or, more simply, the 2 Pis replicate to a single Master (no cluster).
1
SELECT DISTINCT `int`, SUBSTRING_INDEX(SUBSTRING_INDEX(`set_2`, ',', `mask`), ',', -1) `set`
FROM `test_set`,
-- generate masks based on SET values list count
(SELECT 1 `mask` UNION
SELECT 2 UNION
SELECT 3) `mask`
WHERE `set_2` & 1;
fiddle
If server version allows build mask in recursive CTE (dynamic count may be used)
0
The most obvious way is to incapsulate all the database into the API-like set of the stored routines. All tables modifications should be followed by SR's modifications with respect to the chosen syntax and semantics. Most routines will be a kind of CRUD and some routines will perform some special tasks like report generation or data aggregation.
0
One thing you can (and should) do from the application is to wrap multiple INSERTs into a single transaction:
BEGIN;
do several inserts
COMMIT;
More on what to expect
If you have more than a few dozen active connections, they will stumble over each other. In this situation, it is likely that throughput will go down. 5000 is much too high.
A connection, ...
0
innodb_file_per_table = On -- only applies during CREATE TABLE or ALTER TABLE
table_open_cache = 8192
table_cache = 70000 -- old name for table_open_cache; 70K is too high
init-connect='SET NAMES utf8' -- keep in mind that user=root skips init_connect
long-query-time = 30 -- so high as to be virtually useless
"one of these only has a 2.6G ibdata1 file" -...
1
table_cache=10G
Is the old name for table_open_cache. They limit the number of entries. 10G is unreasonable. 9000 is high, but probably OK.
tmp_table_size = 8G
max_heap_table_size = 4G
are dangerously high. If just a few threads decide to build a big temp table, you could run out of RAM, possibly out of swap space. Set them to no more than 1% of RAM.
...
1
Two things:
As you posted 600% of CPU usage, I'll guess that you are using Linux, so no worries, actually this means that you are just using 100% of 6 processors, so you still have 10 processors totally free. When you start seeing a CPU usage above 1400% then you can start worrying.
More important than point number 1 is the crazy mess you have in your ...
0
With your current configuration, if Server A goes down, HAProxy will switch to Server B. But as soon as Server A is back up and running on port 3306, HAProxy will switch back to Server A, which is not what you want.
The best way to handle this use case is to use the stick-table parameter.
With a stick table based on the destination IP address, HAProxy will ...
0
Because the fileName value is from the database you don't need to put the single quotes around it.
SELECT data FROM table WHERE id = 123
INTO DUMPFILE fileName
I'm not sure how filenames with spaces will come out though if you have any. You could always protect against that by using REPLACE on the filename to either remove them or swap them out with a ...
0
As long as you know the name of the table to query you can get a list of the columns stored in it by querying the INFORMATION_SCHEMA Columns Table
Read that list into a temporary table and count how many items you have in there. Now build a new SQL query by looping through them and adding them to your new query string.
I'm not at my own computer to get the ...
1
I had a similar issue. In my case after importing the users table from an old server and upon restarting mysql, I couldn't start it again.
I fixed my user table by:
1- Editing my.cnf and adding:
[mysqld]
skip-grant-tables
2- #service mysql start
3- #mysql_upgrade
4- #service mysql restart
Don't forget to edit your my.cnf again and remove skip-grant-...
1
Another way to solve this would be by using Recursive Common Table Expressions which is available since MariaDB 10.2.2. This solution does not need a numbers table but recursively iterates the result until the computation of the SUM is complete.
I made a small example which is similar to your data:
CREATE TABLE test_json (
prices JSON NOT NULL,
...
0
In a 3node cluster. Am upgrading node3,node2 and node1.
apt-get install mariadb-backup-10.2
is required for 2nd node(donor node) to initially pick mariabackup using 10.2.16.
This will ensure /usr/bin/mariabackup file on 2nd node(donor).
3rd node should have below values.
wsrep_sst_method=mariabackup
wsrep_sst_donor="donor/2ndnode"
When upgrading ...
0
This is not what mariabackup is for. You backup and restore in the same "major" version (where "major" means it just means first 2 number of the version). If you need an upgrade, this is what mysql_upgrade is for.
0
Use php or any other language to create the query and execute it.
//php
$str ="";
for($i=1;$i<=99;$i++)
{
$str.=", max(col".$i.") ";
}
$str = ltrim($str,",");
$query = "SELECT $str FROM table_name ";
1
https://mariadb.com/kb/en/library/procedure-analyse/ (Also works in MySQL.)
SELECT * FROM blah PROCEDURE ANALYSE();
For example, using a table of Canadian cities; notice the min and max values:
mysql> SELECT * FROM canada PROCEDURE ANALYSE()\G
*************************** 1. row ***************************
Field_name: world.canada.id
...
3
It would be helpful to have SHOW CREATE TABLE, but I will make some guesses...
You have INDEX(QualityLevel), and that index is smaller than the buffer_pool?
You have been doing a bunch of queries; these were not the first? (Hence some things are cached.)
You did not use SQL_NO_CACHE to avoid the Query cache?
How much RAM do you have? ...
0
If I understand correctly, the table structure you're proposing appears to introduce a large overhead and will also make effective querying impossible.
First of all, note that InnoDB tables can be modified to add, drop and many other operations without locking the table for DML (i.e. SELECT, INSERT, UPDATE, DELETE etc) - see the MariaDB documentation for an ...
0
I ended up deleting the tc.log file in /var/lib/mysql. When I started mysql again, it created a new tc.log and started up.
sudo rm -f /var/lib/mysql/tc.log
Top 50 recent answers are included
