A quick article on how to populate a database column from another table using a string comparison.
I have several database tables which replicate country names and I would rather they all use the ccTLD two letter code. This article was written because it took me so long to work it out.
- UPDATE `table_to_update` a
- INNER JOIN `table_to_read` b
- ON a.`CountryName` COLLATE utf8_general_ci LIKE b.`CountryName` COLLATE utf8_general_ci
- SET a.`ccTLD` = b.`ccTLD`
mysqldump: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) when trying to connect
- MySQL Database v5.0.45
- MySQL Workbench v188.8.131.5254 build 833
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.
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.
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.
- -- 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\_]+$';
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.
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";
- 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
- s.columnPublished <= NOW()
- AND (
- OR columnFullName LIKE @ThisSearch
- ORDER BY relevance DESC, columnFullName ASC
Just a quick note to myself on how to reorder columns as I was having difficulty using a phpMyAdmin interface to do this.
Taken from the best forum for programming Qs&As: http://stackoverflow.com/questions/4095481/easy-way-to-re-order-columns
So in the phpMyAdmin interface, apparently, you can drag the columns by clicking and holding on the headers when viewing a table structure... Though maybe this is derived from a template and depending on if you are using MS Internet Explorer...
Can't get it working? I use whatever is most useful and Google's Chrome is the fastest browser I have. Here are some ways to do this: