SQL

An example of MySQL code that executes TRIM() to remove a prefix and/or a suffix from all entries that match a WHERE clause

 in Programming / SQL  tagged MariaDB / mysql / prefix / suffix / trim by

The following code will remove the prefix http://wow.example.com from all rows that match the where clause:

Update `my_table`
set
`my_column` = TRIM(
  LEADING 'http://wow.example.com'
  FROM `my_column`)
WHERE (`my_column` LIKE '%http://wow.example.com/%');

The next block will remove the suffix index.php from all entries that match the where clause:

Update `my_table`
set
`my_column` = TRIM(
  TRAILING 'index.php'
  FROM `my_column`)
WHERE (`my_column` LIKE '%/index.php%');

In case we need to remove the string needle both from the prefix and the suffix while using a where clause, we can use the following code:

Update `my_table`
set
`my_column` = TRIM(
  BOTH 'needle'
  FROM `my_column`)
WHERE (`my_column` LIKE '%needle%');

Count how many submissions per score

 in SQL  tagged count / group by / max / order by / sql / truncate by

Recently, we had access to a database that contained the scores of a programming competitions system.
The database contained several contests, each contest contained several challenges and any competitor could make multiple submissions.
We wanted to extract a couple of charts showing

  • how many submissions we had per score and
  • how many submissions we had per score while filtering out the best submission (max score) per contestant per challenge per contest

The following code will return the number of submissions per score per challenge per contest.


SELECT contest_id, challenge_id, TRUNCATE(score, 1), COUNT(*)
FROM submissions
GROUP BY contest_id, challenge_id, TRUNCATE(score, 1)
ORDER BY contest_id, challenge_id, TRUNCATE(score, 1);

The next one will return the number of submissions per score per challenge per contest while filtering out the best submission (max score) per contestant per challenge per contest:


SELECT contest_id, challenge_id, TRUNCATE(max_score, 1), COUNT(*)
FROM
(
  SELECT contest_id, challenge_id, competitor_id, MAX(score) AS max_score
  FROM submissions
  GROUP BY contest_id, challenge_id, competitor_id
) AS max_scores
GROUP BY contest_id, challenge_id, TRUNCATE(max_score, 1)
ORDER BY contest_id, challenge_id, TRUNCATE(max_score, 1);

MySQL Workbench: A solution to Error Code 1175 2

 in SQL  tagged mysql / MySQL Workbench / safe mode / Workbench by

While using MySQL Workbench, we tried to execute an UPDATE command that did not use the Key of the table in the WHERE clause. In our case, it did not matter and it was expected to perform that update without that restriction.

When we tried to execute the code though, we got the following error:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

and the following solution from Workbench:

To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

We did not want to close the session for many reasons (we had data in other tabs, we would lose the undo history, we would have to revert the change later on, etc.).

To solve this issue without reconnecting nor changing the configuration of the tool, we appended the following code above the update statement and executed both lines together (we selected the two commands and pressed CTRL+Shift+Enter):

SET SQL_SAFE_UPDATES = 0;

This line of code, instructed the tool to ignore Error 1175 and allowed us to complete our task.

mysqldump: Got error: 1044: Access denied for user ‘username’@’ip’ to database ‘db’ when doing LOCK TABLES

 in Programming / SQL  tagged mysql / mysqldump by

When trying to issue the command

mysqldump -h "mysql.example.com" -u "username" -p"password" db table_A table_B

to get the MySQL dump for two tables (table_A and table_B), you will get the error

mysqldump: Got error: 1044: Access denied for user 'username'@'ip' to database 'db' when doing LOCK TABLES

when your account does not have the ‘LOCK TABLES’ right.

The ‘LOCK TABLES’ right gives you access to the command LOCK that can be used to emulate transactions or to get more speed when updating tables by preventing other sessions from modifying the locked tables.

To mitigate the problem, use the --single-transaction option as follows

mysqldump -h "mysql.example.com" -u "username" -p"password" --single-transaction db table_A table_B

 

Documentation:

·   --single-transaction

This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.

When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.

While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.

This option is not supported for MySQL Cluster tables; the results cannot be guaranteed to be consistent due to the fact that the NDBCLUSTER storage engine supports only the READ_COMMITTED transaction isolation level. You should always use NDB backup and restore instead.

To dump large tables, combine the --single-transaction option with the --quick option.