Tech-Evangelist has put together an excellent cheatsheet for MySQL. There’s lots of other MySQL cheatsheets out there, but this one is unique in the fact that it gives examples of usage rather than just snippets of code.
This cheatsheet also includes a summary of commonly used MySQL data types, which will be really helpful to […]
Category Archives: Databases
MySQL Cheatsheet By Example
Comments (2)
PostgreSQL text substition: Replacing all instances of a substring
I have a bunch of old paths like ./data/test_data/070608_SLXA-EAS20_FC5459/… stored in the processing table of my Postgres DB. I want to update the filesystem paths (file_path column) to /home/solexa/solexa_datasets/070608_SLXA-EAS20_FC5459/… using the Postgres replace() function.
“UPDATE processing SET file_path = replace(file_path, ‘./data/test_data/’, ‘/home/solexa/solexa_datasets/’) WHERE file_path LIKE ‘%./data/test_data%’;”
The same can also be done with regexp_replace() if better suited.
managing mysql binary logs
Binary logs is how mysql keeps track of what changed in the databases. While it is recommended (in case you want to recover the database ) and sometimes even required ( if you want to replicate the db ) to keep such logs if you have a server where the database changes frequently those logs […]
Web Analysis Tools: what’s free?
This is a quick review of free tools for web analytics / stats-analysis / weblog analysis. I’ll follow up with some more detailed posts about non-web tracking. Follow-up posts will extend this into game development, but this post is purely about web stuff.
Where to start with analysing data?
Analysis of data about what your consumers are […]
Problem transferring a mysql database with rsync
A little more then a year ago I wrote a post presenting three different methods to transfer a mysql database. The third method suggested in that post was copying the mysql database files directly from one server ( or location ) to another. This involved locking the tables with a read lock or even shutting […]
6 things to remember when moving WordPress blogs to a different host
I recently had to move all my WordPress sites and blogs to a new server because Bluehost sucks. My sites were going down on a near-daily basis for way too long, and they couldn’t care less. So I did what I really did not want to do, and moved six WordPress sites to a new […]
ERROR: duplicate key violates unique constraint
With Postgres, I was getting a lot of error messages like this when trying to do an INSERT:ERROR: duplicate key violates unique constraint “processing_lanes_pkey”
The issue was that the serial sequence got out of whack somehow. I looked in the database and found the the top processing_lanes_id (the primary key) in the table was 2727, […]
MySQL: counting results
You have a query and you want to display the results on a web page but because there are so many results you want to paginate the data so the user can have some links like “prev page, page 1, page 2, next page, last page” that you can see on a lot of sites […]
Rules of Thumb Database
Rules of Thumb.Org. ‘A rule of thumb is a homemade recipe for making a guess. It is an easy-to-remember guide that falls somewhere between a mathematical formula and a shot in the dark. A farmer, for instance, knows to plant his corn when oak leaves are the size of squirrels’ ears. An economics professor knows […]
Live Blogging Oracle OpenWorld 2008
Bookmark this page for continuous coverage of Oracle OpenWorld events and happenings. I’m planning to keep this live blog open until the end of the conference on Thursday. Feel free to send in your comments and questions during live blog sessions. Also, follow me on Twitter for updates when the live blog is in standby […]