If you need to set a default for a timestamp field and your server settings don’t allow the use of a zero timestamp (see my article on NO_ZERO_DATE) then the next best option is ‘1970-01-01 00:00:01’. I was trying to do this recently and the server was preventing the creation of a table Continue reading “MySQL minimum timestamp not allowed”
I was asked to do a coding test to create a basic blog. I decided to to use CodeIgniter 3.1 (yes – it’s not dead as was widely reported a few years ago) because I’ve been working with it recently. CodeIgniter may not be as fashionable as Laravel but it is considerable smaller and will run fast on a small/cheap server, it is also quick and easy to learn how to use it.
My source code is available on BitBucket if anyone is interested to take a look: https://bitbucket.org/richardwo/coding-test-blog-ci. There are 2 controllers for users and posts, 3 models for categories, posts and users, 5 admin view files and 3 front-end view files. Overall it took me about 4.5 hours.
Some other PHP Frameworks like CakePHP or Laravel can include registration and authentication by running a few commands. This would have saved me a lot of time because I had to write my own login system and it took a while.
I also ran into some issues with redirects because I was running using the PHP built-in webserver trying to make the application self contained – after some investigation it turns out I needed
$config['base_url'] = 'http://localhost:8000/'; in
application/config/config.php for the redirects to work properly.
More time was wasted writing the usual CRUD methods and creating Bootstrap forms (even if this is only a cut/paste job). CakePHP scaffolding / Code Generation with Bake would have saved more time (although out of the box it doesn’t use Bootstrap).
On a more positive side, I did like the validators where using
'is_unique[posts.slug]' will automatically check that table/field for you and return an error if it’s already been used.
Next time I’ve got to do this I’ll use Laravel as it seems more popular than CakePHP and I’ll benefit from the nice extras this has to offer over CodeIgniter. However I could just reuse my authentication code and save time.
The SQL query below can be used to change email addresses in test data so they don’t get sent to real people accidentally.
The at sign in the original address is replaced with an asterisk character and then a new domain is appended to the end.
UPDATE test_data SET email = CONCAT(REPLACE(email, '@', '*'), '@trash-mail.com') WHERE email != '';
trash-mail.com is a disposable email service where you don’t even need to create an account.
Here is an SQL query I construct to extract customers with billing and shipping address from a Magento 1.9 store.
This joins on table
sales_flat_order_address to ensure that only customers that have ordered are included.
You may need to adjust the entity attribute id’s – if you look in those tables it is pretty obvious what each value represents.
SELECT ce.entity_id AS customer_id, ce.email, cev2.value AS firstname, cev3.value AS lastname, caet.value AS billing_first_line, caev1.value AS billing_town, caev2.value AS billing_postcode, sfoa.entity_id AS sfoa_entity_id, sfoa.street AS shipping_first_line, sfoa.city AS shipping_city, sfoa.postcode AS shipping_postcode FROM customer_entity ce -- first name INNER JOIN customer_entity_varchar cev2 ON (ce.entity_id = cev2.entity_id AND cev2.attribute_id = 5) -- last name INNER JOIN customer_entity_varchar cev3 ON (ce.entity_id = cev3.entity_id AND cev3.attribute_id = 7) -- address first line INNER JOIN customer_address_entity cae ON (ce.entity_id = cae.parent_id) INNER JOIN customer_address_entity_text caet ON (cae.entity_id = caet.entity_id) -- town INNER JOIN customer_address_entity_varchar caev1 ON (cae.entity_id = caev1.entity_id AND caev1.attribute_id = 26) -- postcode INNER JOIN customer_address_entity_varchar caev2 ON (cae.entity_id = caev2.entity_id AND caev2.attribute_id = 30) -- sales INNER JOIN sales_flat_order sfo ON (ce.entity_id = sfo.customer_id) -- shipping address INNER JOIN sales_flat_order_address sfoa ON (sfo.entity_id = sfoa.parent_id) WHERE sfo.status = 'complete'
When you initially install MySQL on a cloud server (Ubuntu 14.04) the
/etc/mysql/my.cnf file is configured to work with only 32M RAM. This is pretty crazy when you consider that most cloud servers have at least 1GB of RAM, it could be holding your website up so it is something you should consider changing.
If you want a quick way of boosting the MySQL performance without having to tune the configuration, then a number of pre-built configurations are stored in:
my-huge.cnf is for a system with memory of 1G-2G.
It would be nice if you could just copy this over
my.cnf and restart MySQL but that doesn’t work. You’ll get the message start:
Job failed to start
What needs changing to make this work on a recently installed system?
[mysqld] section, add
user = mysql and that’s all you need to do if you are using MyISAM.
If you want to use InnoDB then remove the commented out section on Replication Slave (it’s unnecessary and just complicates understanding the file)
Uncomment the InnoDB section but leave the following line commented out:
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
because by default it uses:
innodb_data_file_path = ibdata1:10M:autoextend which is auto-extending.
Finally before starting MySQL you need to delete the log files with
rm /var/lib/mysql/ib_logfile* because these details have changed.
That’s all you need to do. You’ll now be able to enjoy the extra performance of having a MyISAM key buffer of 384MB and an InnoDB buffer pool of 384MB.