New answers tagged mariadb
0
SELECT t1.*
FROM custOrders t1, custOrders t2
WHERE t1.number_orders < t2.number_orders
AND t2.name = 'x';
PS. custOrders.name must be defined as unique by according index.
0
Tiers_association seems to be over-normalized. Most importantly, do not normalize "continuous" values, such as dates, ints, and doubles.
Why are there so many "percent DOUBLE" values. Are they in many-to-many tables? If so, I don't see the pair of ids. In "1,n", it is not obvious which of the links is the "1" and which is the "n".
relation1 -- You do ...
0
34357 tables?? What the heck is going on? Perhaps WordPress, with one db per customer? Or something like that? Anyway, you need to set table_open_cache much higher than 400. Try for 4000. Ditto for table_definition_cache. You may need to increase what the OS lets each process have; see ulimit -n.
Lower long_query_time to 1 (seconds) and turn on the ...
1
I don't know about mysql/mariadb, but a postgresql DB allows you to store arrays. An array of 86400 sensorValues would allow you to have one record per entry_id, dim_id, dim_date.
create table sensorvalues (
entry_id serial primary key,
dim_id int,
dim_date date,
sensorvalue int[86400]
);
1
Today you allow 2 emails; tomorrow you will allow 3. That begs for having a separate table. Such will let you use UNIQUE.
A schema design rule: Don't use multiple columns to implement an "array"; use a separate table.
0
bash only handles Unix shell commands, no MySQL's internal commands.
This PURGE command is a command to MySQL. It needs to be issued in the "mysql" commandline tool. Or some other way to MySQL commands -- such as phpmyadmin, MySQL Workbench, PDO, etc.
0
The error is related to the difference between Innodb definition and the frm file definition.
It probably occurred because of a crash during an ALTER TABLE or similar statement at some point.
To restore the consistency an ALTER TABLE wtb_device_auths FORCE will recreate the table (while exclusively locking it). This recreation should make both the innodb ...
0
For a first question this is quite detailed and asks a little too much at once.
In general the core tables look ok.
The number of tables with a single column, or a single column and an auto increment, particular those connected to POKEAMON_MOVEMENT_* single element, maybe could be reduced, potentially along with their joining table.
I haven't tried to ...
0
From linux Command Prompt,
ulimit -n 16000 and press Enter to allow more than 1024 Open File handles
Rate Per Second = RPS
Suggestions to consider for your my.cnf [mysqld] section from available info
max_connections=100 # from 500 since max_used_connections was 38 in 31 days
thread_cache_size=100 # from 2000 to conserve RAM only 38 threads_created in ...
1
The tuner results on innodb say you have 6.1G of data therefore innodb_buffer_pool_size could be reduced to say 5G and see what impact that has on the "InnoDB Read buffer efficiency".
This will reduce the 16.3g ram usage down to 11.3G for mysql. Given you currently have total ~13G usage I suggest its pretty safe to drop the allocation down to ~16G.
"...
1
Yes, you can achieve this with the CHECK option in the CREATE/ALTER TABLE statement, but only for MySQL 8.0.
Reference: 13.1.20.7 CHECK Constraints (MySQL Dev Documentation)
Example
Create Table
create table emlchk
(
check (email1 <> email2),
emlchk_id int not null auto_increment,
email1 varchar(100),
email2 varchar(100),
primary key (emlchk_id)
)
...
-3
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(email1,email2);
0
You need to embrace the filepath in quotes. This works for me (tested on MariaDB 10.4, but should work on older versions as well):
DELIMITER //
CREATE PROCEDURE test(filepath text, _query text)
BEGIN
DECLARE stmt TEXT;
SET stmt := CONCAT(_query, ' into OUTFILE \'' , filepath, '\'');
PREPARE s FROM stmt;
EXECUTE s;
DEALLOCATE PREPARE s;
END //
...
0
The problem is that although the user was not listed in mysql.user table, there was an entry in mysql.db with that user.
So the solution is: check also mysql.db for colliding usernames.
3
I guess you can use CHECK constraint
ALTER TABLE foo
ADD CONSTRAINT email1_is_not_email2_CHK
CHECK (email1 <> email2) ;
0
Rate Per Second = RPS
Suggestions for your my.cnf [mysqld] section,
innodb_buffer_pool_size=50G # from 52G for 80% of 62G
sort_buffer_size=4M # from 52M for this per connection RAM request
join_buffer_size=1M # from 52M to support row pointers for join per connection
read_rnd_buffer_size=192K # from 52M to reduce handler_read_rnd_next RPS
...
0
There is certainly no such service by Oracle as they wouldn't want anyone to migrate away from their own database system.
MariaDB Corporation has a paid migration service to help migrating to MariaDB. This is run by experts in the field.
AWS has a fully automated service that appears to be able to migrate your schema and data, but presumably not much ...
1
rsync is usually not a good SST method as it can block writes on the donor node while performing SST. Use mariabackup instead to avoid that.
Use wsrep_provider_options="gcache.recover=yes; gcache.size = xM;" where x is enough megabytes to hold all write sets for any expected downtime, if you want to be able to use IST and avoid SST after restarts. E.g. the ...
0
Test
DELETE
FROM tmp_VendorRate_stage_1 v
WHERE EXISTS ( SELECT NULL
FROM tmp_VendorRate_stage_1_orig vd
WHERE v.RowOriginationCodeID = 0
AND v.RowCodeID = vd.RowCodeID
AND v.TimezonesID = vd.TimezonesID )
Ensure the indices by (RowCodeID, TimezonesID, RowOriginationCodeID) exists (in _orig ...
1
Beginning a transaction causes any pending transaction to be committed. See Section 13.3.3, “Statements That Cause an Implicit Commit”, for more information.
-- https://dev.mysql.com/doc/refman/8.0/en/commit.html
and
Transactions cannot be nested. This is a consequence of the implicit commit performed for any current transaction when you issue a START ...
1
I don't think it's a bug. You use GROUP BY and at that moment server leaves only one random value for f_date in each row. After that you apply your filter for results returned by the query inside the view.
According to MariaDB documentation:
You can use any of the grouping functions in your select expression.
Their values will be calculated based on ...
1
For general monitoring, this comes closer to covering all bases, but without being able to "cause damage":
GRANT SELECT, PROCESS, REFERENCES, SHOW DATABASES,
REPLICATION CLIENT, SHOW VIEW
ON *.* TO username@'...';
The ON *.* is necessary (though clumsy) syntax.
If the data is sensitive, remove SELECT. It can even be argued that being able to ...
1
One more way:
GRANT PROCESS, SELECT ON *.* TO dynatrace;
1
From the MariaDB documentation:
To set a global privilege, use *.* for priv_level.
So your command should be
GRANT PROCESS ON *.* TO dynatrace;
1
@nbk had given what you want - atleast what you made us to understand.
But...
Think of re-designing your whole job. Do not go by your fluctuating number of columns. There are better ways. json is one. Transactional database is what is been used in lot of projects. Those are the right methods to store / retrieve than having variable number of columns.
-1
Thanks to #danblack and dbfiddle.uk in short I determined that I was missing one of the columns in the ON DUPLICATE KEY UPDATE.
Every single column must be defined for ON DUPLICATE KEY UPDATE to work!
I effective had columns id, a, b, c and d in the table. I forgot to define one of the columns (that wasn't id as that is the first half of the requirement).
...
1
This is documented behaviour:
MySQL requires indexes on foreign keys and referenced keys so that
foreign key checks can be fast and not require a table scan. In the
referencing table, there must be an index where the foreign key
columns are listed as the first columns in the same order. Such an
index is created on the referencing table ...
1
Basic Google-Fu produced the following information:
The innodb_io_capacity variable defines the number of I/O operations per second (IOPS) available to InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the change buffer.
I wouldn't set this to the maximum IOPS value of your SSD disk as you need IOPS for InnoDB ...
2
You should always (if possible) name your constraints:
CREATE TABLE T ( x int not null );
ALTER TABLE T
ADD COLUMN jsonf JSON DEFAULT NULL;
ALTER TABLE T
ADD CONSTRAINT T_C1 CHECK(JSON_VALID(jsonf)) ;
ALTER TABLE T DROP CONSTRAINT T_C1;
ALTER TABLE T DROP COLUMN jsonf;
If you have anonymous constraints, you can find them in:
...
3
You must assign a name to the CHECK constraint during add:
ALTER TABLE `mydb`.`table1`
ADD CONSTRAINT check_json_validity CHECK(JSON_VALID(jsonf));
Then you may freely remove it by name:
ALTER TABLE
DROP CONSTRAINT check_json_validity;
If you forget to assing the name you may to see it in SHOW CREATE TABLE output.
1
Not until MySQL 8.0 is there a "case-insensitive, accent-sensitive" collation:
mysql> set names utf8mb4 collate utf8mb4_0900_as_ci;
Query OK, 0 rows affected (0.00 sec)
mysql> select 'a'='A', "a" = "à" ;
+---------+------------+
| 'a'='A' | "a" = "à" |
+---------+------------+
| 1 | 0 |
+---------+------------+
1 row in set (0.00 sec)
1
Ok, I know what happened. What I did:
1) vi /etc/selinux/config
2) Change state to from enforcing to disabled
3) Apply init 6 (restart server)
0
I used to use MySQL on a 256MB, non-dedicated, machine. But, thanks to OS bloat, more MySQL/MariaDB features, etc, 512MB may be the minimum these days.
If you set things too high in my.cnf, there will be swapping, which is terrible for performance.
When using a tiny VM, decrease, don't increase, things in my.cnf.
The main thing to decrease is ...
0
After I installed MySQL Workbench beside MariaDB 10.x on Ubuntu 19.10.x and suddenly MariaDB was disrupted sudo mv /var/lib/mysql/tc.log /var/lib/mysql/tc_bkp.log worked for me.
1
The update command for Table 1 to update all names score
Would look like
UPDATE table1 as t1
INNER JOIN (SELECT DISTINCT(name) AS distname, SUM(points)-SUM(penalizes) AS score
FROM table2
GROUP BY name) t2
ON t1.distname = t2.distname SET t1.score = t2.score;
But better would be a trigger, which update only the rows in table1, username is actualy ...
2
This could be a serious problem when replicating between MySQL 8.0 and MariaDB 10.x.
The default (for good technical reasons) COLLATION for the 8.0 is utf8mb4_0900_ai_ci. Note the "255" associated with it. MariaDB has not yet adopted the Unicode 9.0 collations.
Furthermore, Oracle (MySQL 8.0) did a major rewrite of the collation code, thereby possibly ...
0
'publish' is a string
+ is an _arithmetic operator
So 'publish' + _pad_term_counts() is probably not anything useful.
How long does each SELECT take?
Please provide SHOW CREATE TABLE and EXPLAIN SELECT and how big the tables are.
Descending index -- Not a problem; MySQL simply scans through the data in reverse order.
However, no index is useful for ...
1
REQURE SSL only requires a TLS connection. Use REQUIRE X509 to enforce a TLS client certificate, or better, use REQUIRE SUBJECT or REQUIRE ISSUER to ensure that is the identical certificate that matches the one issued.
0
Rate per Second = RPS
Suggestions for your my.cnf [mysqld] section,
innodb_io_capacity=1900 # from 200 to enable higher IOPS on your SSD
read_buffer_size=512K # from 512M to be a reasonable limit
read_rnd_buffer_size=128K # from 512M to reduce handler_read_rnd_next RPS
innodb_buffer_pool_size=48G # from ~ 80G
max_write_lock_count=16 # from 1 to allow ...
0
Just to let you know that I've found the issue: related tables on the new server were not broken so everything worked "fine", but the indexes needed to by repaired after the copy from old server (probably because of the big MySQL version upgrade). I just ran mysqlcheck -A --auto-repair and the perf are at least equivalent now! The EXPLAIN SELECT doesn't show ...
0
Which values are you concerned about?
Some are GLOBAL only. my.ini and a restart is needed to change them from outside. Or a user with SUPER can change them from a connection.
Some are SESSION, but initialized to the corresponding GLOBAL when you connected. You can override these with SET SESSION .... Once you have done that, no one else can change them ...
Top 50 recent answers are included
