Applies to:

  • MySQL Database v5.0.45
  • MySQL Workbench v6.0.8.11354 build 833

What?
This is a quick article on how to get around the problem of backing up your MySQL database when attempting to "Data Export" using MySQL Workbench. This is not regarding the connection issue as I can connect to my database using MySQL Workbench (I have enabled the old authentication protocol). The error ONLY appears when I try to "data export" the database.

Why?
The quick solution for everyone else is to change/reset the password of the connecting database user, but herein lies the problem. When you read my workaround, you'll say that I haven't solved anything; but this is a production database I want to export for offline backup. I have to raise and log a formal change request and follow a workflow process in order to make a change on a live system to a service user account... Especially one used by the scripts to access the database-driven website.

How?

What?
We have a specific timetabling system for academic institutions and all our staff/students follow academic week numbers as opposed to calendar week numbers.

Why?
The aim of this article is to quickly generate a calendar for a full academic year for referencing by staff/students.

How?

What?
So this is a quick note to myself as I was playing with the relevance heuristics of a query. This example adds a column of relevance and sorts the rows accordingly.

How?
This has to be a real quick one for a dropdown search field which has to find relevant terms to autofill/autocomplete a search form:

-- where @ThisSearch is a posted (and sanitized) variable

SET @ThisSearch:="Brains";

SELECT
        columnID,
        columnFullName,
        CASE
                WHEN columnFirstName LIKE @ThisSearch THEN 20
                WHEN columnFullName LIKE @ThisSearch THEN 10
                WHEN columnLastName LIKE @ThisSearch THEN 10
                WHEN columnFullName LIKE @ThisSearch THEN 1
        END as relevance
FROM
        myTable
WHERE
        s.columnPublished <= NOW()
        AND (
                SOUNDEX(@ThisSearch)=columnFullSoundex
                OR columnFullName LIKE @ThisSearch
        )
ORDER BY relevance DESC, columnFullName ASC

What?
Quick note on how to do this.

I was tasked with cleaning up an english database by replacing all special alphabets (ë to e) and non-alphanumeric symbols with URL friendly characters.

How?

-- return all records that contain non-alphanumeric characters
SELECT * FROM myTable WHERE myColumn NOT REGEXP '^[A-Za-z0-9]+$';

-- return all records that are non-alphanumeric but ignore underscores
SELECT * FROM myTable WHERE myColumn NOT REGEXP '^[A-Za-z0-9\\_]+$';

Subcategories