New answers tagged

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