New answers tagged mariadb
0
IF NOT (_field IS NULL AND _txtTrainee IS NULL AND _data IS NULL) THEN
SET @SQL := CONCAT('INSERT INTO trainees (txtTrainee,', _field, ')',
' VALUE (?,?) ON DUPLICATE KEY UPDATE',
' ', _field, '=VALUES(', _field, ')')";
PREPARE stmt FROM @SQL;
EXECUTE stmt USING _txtTrainee, _data;
DEALLOCATE ...
0
First column names can't be replaced in prepared tatements.
second preparared statements need user defiend variables as parameters
the last point is a security risk, with the filed name or columnname that could be3 used for sql inejction, so you should have a whitelist for columnames like in te examle
CREATE TABLE trainees (txtTrainee varchar(100), field1 ...
0
DELIMITER $$
CREATE TRIGGER `StatusUpdate` AFTER UPDATE ON `cases` FOR EACH ROW BEGIN
DECLARE OldStatusText VARCHAR(32);
DECLARE NewStatusText VARCHAR(32);
DECLARE OldPriorityText VARCHAR(32);
DECLARE NewPriorityText VARCHAR(32);
DECLARE WorkLogText VARCHAR(256);
SET WorkLogText = NULL;
IF NEW.`Status` != OLD.`Status` THEN
...
3
As correctly noted by Charlieface, this is a Gaps and Islands problem. Another way of solving this specific variation – also involving a window function, though a different one this time – would go like this:
WITH
partitioned AS
(
SELECT
*
, DATEDIFF(Date, '1970-01-01') - ROW_NUMBER() OVER (ORDER BY Date ASC) AS PartID
FROM
...
1
This is a type of gaps-and-islands problem, of which there are a number of solutions.
Here is one:
We can identify the starting points of each island by using LAG to check the previous row (with a default for the first row)
We can then number the islands using a running COUNT
Then simply group by that number
WITH StartingPoints AS (
SELECT *,
...
0
The solution which does not use user-defined variables and/or CTEs:
SELECT t1.`Date` range_start,
MIN(t2.`Date`) range_finish,
DATEDIFF(MIN(t2.`Date`), t1.`Date`) + 1 range_length
FROM test t1
JOIN test t2 ON t2.`Date` >= t1.`Date`
WHERE NOT EXISTS ( SELECT NULL
FROM test t3
WHERE t3.`Date` = t1.`Date` -...
1
You have misplaced your setting; lower_case_table_names belongs to the [mariadb] section, as shown in the docs. If the section is missing, you simply need to add it.
You also should get rid of a duplicate configuration file; one is sufficient, and having two will get you more grief in the future.
If you want to understand better what's wrong, read this ...
0
I'm not exactly sure of the reason for this, but after a while I noticed that my IDE would run a USE database; statement before each set of ALTERs was executed. I tried this on the command line and it worked.
1
The DROP DATABASE and CREATE DATABASE commands are generally not part of a default dump operation. You will need to add one item to your command:
--databases or -B:
This will allow you to dump several databases if you wish, but you can also use this option if you would like to have CREATE DATABASE and USE inlaced in the output before each database is defined....
1
Purging the last binlog on the Primary is risky.
The data in the replication stream is sent to the Replica(s) at the same time it is added to the last binlog on the Primary.
If the network goes down, the binlog is vital for providing the replication stream to the Replicas when the network comes back up.
I like to keep enough binlogs to hold a week's worth of ...
0
Yes, replicas function fully even though they aren't replicating.
"IO" is the communication with the master where new binary logs are fetched. "SQL" is the application of these fetched binary logs on the tables and everything in the server.
At the moment you have lost everything beyond the master 'mysql-bin.000001' at 2314.
If what is ...
0
{ Related to @nbk's suggestion (in a comment on the question) to use:
sed -e 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' -i myfilename.sql
}
I think there are two main options for running Unix commands in Windows:
"Windows Subsystem for Linux Documentation (WSL)" — this is provided by Microsoft. According to the "Windows interoperability with ...
0
That's not the correct syntax for using a cursor. But why even use a cursor when JOIN will do the job much faster. Or a GROUP_CONCAT.
BEGIN
SELECT
CM.career_id AS id,
CM.name AS career,
AC.name AS area,
GROUP_CONCAT(sd.name FROM specialization_detail
WHERE sd.career_id = row.id)
FROM ...
0
If you are running out of memory dropping the innodb buffer pool size can be done. Looking at Innodb_buffer_pool_read_requests vs Innodb_buffer_pool_reads shows a very high cache ration. As swap/crashing is the alternate reducing this is recommended.
Note mysqldump significantly trashes both innodb buffer pool and table cache. Maybe the swapping can explain ...
2
If LATERAL joins were supported (they are in MySQL 8+):
SELECT
rrr.name
, SUM(rrr.action = 'respond' ) AS total_l2_respond_by
, SUM(rrr.action = 'return' ) AS total_l2_return_by
, SUM(rrr.action = 'resolved') AS total_l2_resolved_by
FROM
master_apps_issue AS mai
CROSS JOIN LATERAL
(
SELECT
mai.l2_respond_by, 'respond'
WHERE
mai....
1
It isn't really possible to do this in one scan: the grouping and filtering are completely different.
It seems a full join would be more in order here:
select
ifnull(resp.l2_respond_by, ret.l2_return_by) as userid,
resp.total as total_respondby,
ret.total as total_returnby
from (
select l2_respond_by, count(l2_respond_by) as total
from ...
1
There are going to be some assumptions in this answer. As there is no sample dataset with which to test the results, keep these in mind and be sure to adjust the query to suit your situation:
the COUNT() functions are using an id of some sort rather than a user_id, as this makes it much easier to verify accuracy
the source table is assumed to contain a ...
0
Got the same problem and found a convenient solution - at least for my tasks.
If you always have the same ORDER BY eg popularity or whatever and don't do frequent updates, eg if it's enough on a daily basis, an idea is to set up a cronjob which resorts the table every night.
ALTER TABLE my_table ORDER BY my_sort_column ASC/DESC
This way you get the sorted ...
0
Try this:
START TRANSACTION;
loop (write a loop inside the transaction)
SELECT * FROM testinnodb WHERE c=1;
endloop
COMMIT;
1
I would not expect either to have an 'edge'.
5.6 and 10.0(?) had the same InnoDB FULLTEXT; they have not diverged much since then (with respect to FULLTEXT).
If you switch to MariaDB, you may as well go to 10.5.
Will you be using non-English text and searches? If so there may be issues.
1
If someone else stumbles across this thread (like me) looking for a solution to a
4047 InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE
or similar error from the mariadb for nextcloud, occurring whenever nextcloud tries to write to the DB: the important clue for me was, that this error might be related to an utf8 encoding issue. ...
3
You should look at the MariaDB error log. The authoritative documentation on this is found in the MariaDB Knowledgebase: Error Log.
If you're using Ubuntu, then I assume you have systemd, which means you can also see the error log with:
journalctl -u mariadb.service
... although I personally don't think this is as good/nice as MariaDB's own error log file. ...
0
Check this:
WITH
-- gathering all mentioned dates with ther weights
cte1 AS ( SELECT name, sides, start dt, 1 weight
FROM sample
UNION ALL
SELECT name, sides, `end`, -1
FROM sample ),
-- calculating the cumulative sum for each date
cte2 AS ( SELECT DISTINCT
name,
dt,
...
0
If you're using systemd, then I think the reason why you're not seeing the error log at /var/log/mariadb/mysqld.log as specified in your my.cnf might be that you have put log-error=/var/log/mariadb/mysqld.log in the wrong option group - [mysqld_safe] is not used when you're using systemd. You could move it to e.g. the [mysqld] section instead, which should ...
0
log-error or log_error?
log-error is the correct name to assign to when setting options in the options file my.cnf.
log_error is the correct system variable name when reading like SHOW VARIABLES LIKE '%error%'.
Reference links: MariaDB and MySQL.
6
Add this index:
ALTER TABLE StoreData.data ADD INDEX (itemName, DatetimeRecorded);
The itemName column needs to go first. This will narrow down the search by equality on that column.
Then the DatetimeRecorded column narrows down the search further within the matching rows to the date range you want. That index will not help narrow down by hours and minutes ...
Top 50 recent answers are included
