New answers tagged mariadb
0
This is what I say about MySQL/MariaDB without columnstore:
Not good: "sum up to 1 TB" versus "1,25 TB of SSD storage". As a Rule of Thumb, you should have half your disk free for maintenance and growth. As a minimum, there should be enough room for an extra copy of the largest table -- data+indexes. This allows any ALTER to run ...
2
insert into x set id = -1, s = 'apple'; blocks the row with id=-1 until then end of the transaction. Hence the other process delays without deadlocking.
This is why you should worry about max(id)+1
PROCESS 1 PROCESS 2
set autocommit = 0;
start transaction;
insert into x set id = -1, s = 'apple';
select @m := max(id) + 1 from x;
--...
2
MariaDB-10.2.2 InnoDB merged from 5.7.14
MariaDB-10.2.35 Merge new release of InnoDB 5.7.32
Lots of miscellany features have been merged at random times.
MySQL 8.0 and MariaDB 10.5 are quite different. Still, you may not notice any differences unless you reach into rarely-used features.
The two play leapfrog. For example.
MariaDB had CTEs and Windowing ...
1
In docs you can include limit=40. So:
SELECT * FROM t1 WHERE query='test;mode=any;limit=40';
Not a usual SQL way, but SphinxSE isn't quite normal.
0
Below is an analysis of what you have. Nothing points to how it could be over-filling memory. Please provide the evidence (top / htop / innodb_top / whatever) that shows mysqld larger than, say, 10GB. Maybe there are some clues there.
Analysis of GLOBAL STATUS and VARIABLES:
Observations:
Version: 10.3.27-MariaDB-0+deb10u1
32 GB of RAM
Uptime = 13:59:47;...
1
Seconds_behind_master doesn't exactly measure the replication lag. It measures the difference between the timestamp of the last binlog event fetched by the IO thread, versus the timestamp of the last binlog event executed by the SQL thread.
This can become misleading for example if the master has more binlogs that the replica hasn't fetched yet. If the ...
1
As suggested by @nbk, we've set global sql mode to the default values from 10.1
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1
MariaDB has a user statistics plugin that provides a breakdown per user.
It can be enabled dynamicly:
SET GLOBAL userstat=1;
And examined:
SELECT * FROM INFORMATION_SCHEMA.USER_STATISTICS\G
It seems like such a increase is potentially a brute force attempt or some abnormal use. I'd look at the SHOW PROCESSLIST and see if the queries being run are ...
1
Like most (all?) SQL queries the number of columns in the result is fixed. Recursive CTEs can only add rows and not columns.
You are fortunate that the result you want to generate a ARF report is a XML. Because of this you can use good old GROUP_CONCAT to generate an XML form of the DKIM.
I haven't looked up the XML spec for the report however something in ...
0
I did a lot of experimentation by checking the performance. It may help others.
The fastest way (by far) is to do the random rearrangement outside the SQL query.
SELECT ID INTO OUTFILE '/tmp/id.csv'
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' FROM t1
then
shuf -o /tmp/id.csv < /tmp/id.csv
and finally the fast INSERT step
LOAD DATA LOCAL ...
2
Possible trick.
Create intermediate table. Copy your ID from t1 into it. Add virtual generated column which calculates some hash from id value, and index it. Use this table as a source for insertion, add sorting by created index expression, and force it (without index hint it may be ignored due to 100% rows selection.. from the other side, it must be used ...
0
I think this might be the answer and might help others with this problem.
I had a test trigger on the table member
CREATE TRIGGER `test` BEFORE UPDATE ON `member` FOR EACH ROW
BEGIN
DECLARE addr VARCHAR(100);
DECLARE joint INT;
SET @joint := is_joint_member(member.member_id);
SET @addr = 'jgkjgkjg' ;
CALL dummy2; ...
0
Woo! I finally figured it out!
The problem was solved by upgrading libmariadb-dev on the Rails application server and rebuilding the mysql2 gem.
Previously, the system was running on a single server with a mariadb-10.1 database. After the switch to a separate database server, the database was on mariadb-10.3 but it was still connecting from the same Rails ...
0
Replace
SET lastid = (SELECT LAST_INSERT_ID() );
SET result = -1 * lastid ;
RETURN result;
with
SELECT -LAST_INSERT_ID();
and toss the two declarations.
If you have a more complex case, bring it on.
0
The main difference ist that you can use DECALRE ony in stored function and procedzres
DECLARE var_name
This statement is used to declare local variables within stored programs.
see manual
User-Defined Variables
User-defined variables are variables which can be created by the user and exist in the session.
see manual
Which means every user defined ...
1
Corruption! AUTO_INCREMENT=2126679551 INT has a limit of 2G! You could change it to BIGINT. But, read on; I have an even better solution (namely to get rid of id):
I recommend replacing your two indexes with these two:
PRIMARY KEY(channel_id, timestamp),
INDEX(id)
That will help in many of your activities, plus Akina's suggestion, and it will replace ...
1
DELETE t1
FROM data t1
JOIN data t2 USING (channel_id,timestamp)
WHERE t1.id > t2.id
1
Assuming the buffer pool was completely full, this would mean 25G (based on innodb_buffer_pool_dump_pct) would be loading.
The loading of the innodb buffer pool is throttled by the setting of innodb_io_capacity.
The loading is also throttled heavily if there is other server activity in innodb.
Reading this amount from disk, especially in a random read ...
2
It's just a basic, You are suppose to mention hostname while creating user. Syntax is following :
CREATE USER 'user_name'@'host_name' IDENTIFIED BY 'yourpassword' ;
i.e.
CREATE USER 'myuser'@'192.168.3.12' IDENTIFIED BY 'MyU#123seR' ;
CREATE USER 'myuser'@'mydb.in' IDENTIFIED BY 'MyU#123seR' ;
GRANT SELECT ON *.* TO 'myuser'@'mydb.in' ;
You can also use ...
4
The hostname component is the reference to the remote address of the connection. See create user.
Recommend keeping this constrained to a subnet.
Domain based grants while they look good, need reverse DNS setup to resolve this to an IP before allowing a connection. I recommend avoid them and using skip-name-resolve as a configuration option for extra speed ...
0
Wouldn't it be simpler / easier to just choose a random number who's range is outside your current range of values in the member table? E.g. something like (FLOOR(RAND(id)) * -1) + (SELECT MIN(id) FROM member) (semi-pseudocode)?
0
Face it, MyISAM survives only because it uses table locks for almost every kind of write. Even a SELECT can get caught up in the middle.
The obscure exception has to do with multiple INSERT that are inserting into a table with AUTO_INCREMENT PRIMARY KEY (and, I suspect, no other UNIQUE keys). In that case, they can play around at the "end" of the ...
0
To add to @nbk answer. I had to change it on /etc/my.cnf.d/mariadb-server.cnf and it was explicit_defaults_for_timestamp=ON`
1
open you my.conf/ini
find the section [mysqld]
and add explicit_defaults_for_timestamp = 1
sometimes you need instead
explicit_defaults_for_timestamp = ON
You can check after restarting mysql with
SHOW GLOBAL VARIABLES LIKE '%timestamp%'; if it is enabled
But this is a not standard option and could be removed at some point
0
TL;DR
Deterministic functions are better than non-deterministic ones in this use case. Either the deterministic MIN()/MAX() functions can be used to select the required data (similarly performant1 or window functions (more powerful and flexible) - in this case, the ROW_NUMBER() function.
(1) Edit: using the MySQL profiling functionality, I found that the ...
0
All the options you mention can be specified on mysqldump's command line, for what it's worth. That may be a better way to write a self-documenting backup process than stuffing those items into (hiding them in) a configuration file.
And, if you have performance problems with your dump operation you can try omitting the single-transaction option. If your ...
0
If your database structure is relatively static then dump it separately (once), and dump only tables data using SELECT .. INTO OUTFILE - this is more fast.
Of course you need:
create a script (the best way - an event procedure) which dumps all (or only changeable) tables data
create a script (maybe as stored procedure in structure dump) which restores the ...
0
gtid_executed table is from your former MySQL instance. It isn't used by MariaDB.
The global gtid%pos variables you have listed keep track of the gtid.
0
I simply don't kn ow where you get your last row.
also with Myql 8 you can ise the window function ROW_NUMBER
the rest is self explantory, the sorting comes from colA and Col1, when there are the same numbers the second column orderby2 comes and sorts first for the first table
CREATE TABLE table_1
(
id INT UNSIGNED AUTO_INCREMENT,
colA INT,
colB ...
0
SELECT idx.I, COALESCE(table_1.colB, table_2.colZ) AS `O`
FROM ( SELECT colA AS `I`
FROM table_1
UNION
SELECT colY
FROM table_2 ) idx
LEFT JOIN table_1 ON idx.I = table_1.colA
LEFT JOIN table_2 ON idx.I = table_2.colY
ORDER BY I
https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=1b4c25750ee16711547e8074cce9fed9
Top 50 recent answers are included
