Tag: mysql

Posted on

CakePHP 3: Date/Time Disappearing during Hydration

When generating a date/time for saving to a MySQL database, I’ve always tended to generate it with the PHP date method (date('Y-m-d H:i:s')). However, using CakePHP 3, with dateTime validation set on the field, the date/time would disappear during hydration of the entity, but without a validation error.

I still suspected that validation was the issue here, so started having a look at the validation method to try to work out what was going wrong. Before I got very far, I noticed that the datetime validation method validates any value that is a Cake DateTime object. Therefore, the simple solution is to create the date/time using Cake’s built-in Time class:

use Cake\I18n\Time;
$datetime = Time::now();

Cake then happily saves the DateTime in the standard MySQL format.

Posted on

mysqldump all databases from XAMPP on Windows

A quick note to hopefully save others the time I’ve just wasted. If you want to dump out all your databases, perhaps in preparation for an upgrade to XAMPP as I’m about to try:

  1. Open up a command prompt (in Win 8+ type ‘Command’ and press <Enter> from the Start screen);
  2. Type: 
    cd\xampp\mysql\bin

    (or wherever your myslq.exe is located)

  3. Type: 
    mysqldump --user=root --password=myrootpwd --all-databases > backup.sql

Other options for this command can be found on the MySQL site.

Note that you are doing this from the Windows Command prompt – don’t get distracted, as I was, by running the MySQL command line tool. If you try and run mysqldump from the:

mysql>

prompt, you’ll get various syntax errors such as:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysqldump...' at line 1

However, if running the MySQL command line tool is what has brought you to this post, you launch this by following steps 1 & 2 above and then typing:

mysql --user=root --password=myrootpwd

When executing commands at the:

mysql>

prompt, don’t forget that it allows you to enter multiple lines at once and that clicking <Enter> will just give you a new line unless you finish your line with a semi-colon – this will tell MySQl that you want to execute the command.

 

Posted on

CakePHP/MySQL: Want a Boolean? Use TINYINT(1), not BIT(1)

I was recently creating some new MySQL tables that stored booleans, so I thought I would do things (what I presumed to be) properly and make them BIT(1) columns, rather than TINYINT(1). However, when I came to try to save values to this field using CakePHP, everything was saved as 1, even if the value in the data array was 0, false, “”, “0” or anything else ‘zero-ey’ or ‘falsey’.

It turns out that BIT fields are not supported by Cake, and so you should just use TINYINT(1) instead!

Furthermore, Cake will assume a TINYINT(1) field is intended as a boolean field, and will only allow you to assign it a value of 0 or 1, even though TINYINT(1) can store values from -128 to 127 (or 0 to 255 unsigned). If you try to save any value other than 0 or 1 to this field, it will be saved as 1 (but note that false, “” and an empty array will cause a save error).

Posted on

Resetting the root password/privileges for MySQL/PHPMyAdmin (XAMPP 1.7.3, MySQL 5.1.41)

We use XAMPP for our local development (on Windows), and I have a habit of messing around with the database and doing bad things by mistake, because it’s only for local development, so it doesn’t matter that much. It’s still annoying when I lock myself out of all my databases though!

While setting up a wordpress site locally for testing, I managed to remove the global privileges of the root user in PHPMyAdmin, thinking it was the user for the wordpress database. I did the usual thing of searching for solutions online, and after trying a few different things involving the command line and MySQL Workbench, I came across this Apache Friends forum thread: http://www.apachefriends.org/f/viewtopic.php?f=16&t=46832. The instructions boil down to the following:

  • Open mysql/resetroot.bat in a text editor
  • Add “–datadir=c:/xampp/mysql/data” into the “mysql\bin\mysqld.exe…” line (line 18), after “–no-defaults”. Change the datadir parameter to the path to your data directory – the one shown is the default.
  • Run resetroot.bat – this should reset the MySQL root password and privileges. You can then login to MySQL/PHPMyAdmin using the username root and no password. For security, you should set a password for the MySQL admin user in the XAMPP Security panel: http://localhost/security/