So if you’ve ever created users and given them specific permissions from with SQL or the commandline, you will be familiar with a statement called FLUSH PRIVILEGES. It is said that if you create a new user, or alter than user, you should run FLUSH PRIVILEGES in order for the Read more…
I was trying to install MySQL Community Server for Windows 10 using the standard .MSI installer provded from the MySQL website and the installer just hung, saying that there was 3 seconds remaining. About half an hour later, it still said “3 seconds remaining”. It turned out that you have Read more…
At Statvoo we found ourselves in the position where we needed to move our master MySQL database without ANY downtime and for anyone who’s tried to do this, you will know how hard this can be if it is not done exactly right. Below I will run through the steps Read more…
I often need a quick and easy few lines to retrieve some data from MySQL using Joomla without all the MVC nonsense that usually goes about this topic. So here is how I do it! [code lang=”php”] $dbSEL = JFactory::getDbo(); $dbSEL->setQuery(“SELECT MAX(`start_price`) FROM #__some_table”); $dbSEL->query(); echo $dbSEL->fetchRow(); [/code] As you Read more…
This is really actually meant for future reference for myself as lately I’ve been doing quite a lot of Joomla! mysql coding and have used the following code just short of 3million times. $db = JFactory::getDbo(); $querySelectId = "SELECT `id` FROM #__some_table WHERE `user_id`='$user_id'"; $db->setQuery($querySelectId); $db->query(); $data = $db->loadAssoc(); What Read more…
Recently I had to create a code/username maker to fit into a certain type of pattern so that all “broker codes” (as they were called) followed the same path. An example one looked like so: [code lang=”bash”] HJTH0001 [/code] The number needed to increment and I wanted to use the Read more…
This came in very handy for me at one point because I needed to have a certain field in each row 14 characters long, but half of them were only 12 characters long. In MySQL: [code lang=”sql”] update `table` set `field` = CONCAT(`field`, ‘1’) where LEN(`field`) = 13; [/code] In Read more…
In order to backup all mysql databases, you can run the following command in your linux command line: The example below is configured with username “theuser” and password “thepass”. [code lang=”bash”] mysqldump -utheuser -pthepass –all-databases > all_dbs.sql [/code]
If you get the following error while trying to connect to a MySQL database using a third party client such as TOAD or SQL Workbench, then you need to edit your mysql server’s my.cnf file. Error Message: Unable to connect to any of the specified MySQL hosts. So open the Read more…
In MySQL 5.1 LEN is called LENGTH, you use it exactly the same and pass in the link, so: LENGTH(link).