Tag Archives: mysql

MySQL: Count the total occupancy rate for a time period (month)

Recently I encountered a somewhat interesting MySQL/PHP task: determine occupancy for a reservation system. This entailed determining how many reservation days fall within a particular month. It was not possible to simply determine each reservation length (datediff from/to) and sum them because some reservations started before the month in question and other reservations lasted until the month was over. Therefore, one solution is to get the reservation lengths then compare the start and end of the reservation to the start

Set your own date format in MySQL

Use DATE_FORMAT in MySQL to create your date strings formatted however you like: SELECT CONCAT(YEAR(res_hotel_booking_bookings.created),’-‘,DATE_FORMAT(res_hotel_booking_bookings.created,’%m’) ,’-‘,DATE_FORMAT(res_hotel_booking_bookings.created,’%d’)) AS creationDate, Count(res_hotel_booking_bookings.id) As reservationsMade FROM res_hotel_booking_bookings WHERE res_hotel_booking_bookings.created >= ‘2013-10-15 00:00:00’ AND res_hotel_booking_bookings.created <= ‘2013-10-17 24:00:00’ GROUP BY YEAR(res_hotel_booking_bookings.created), MONTH(res_hotel_booking_bookings.created), DAY(res_hotel_booking_bookings.created) ORDER BY creationDate DESC There are more concise ways to do this, but this was adapted from an earlier method that used CONCAT so I stuck with that… 🙂

Evaluating a RAM disk as a MySQL data store

DDR3 Memory

I have a situation with a relatively small set of MySQL data (5GB) and a decently large set of available ECC DDR2 memory (32GB). Therefore, I am evaluating storing the entire MySQL data directory on a ramdisk. Along those lines, here are some initial resources: 12 RAM Disk Software Benchmarked for Fastest Read and Write Speed • Raymond.CChttp://www.raymond.cc/blog/12-ram-disk-software-benchmarked-for-fastest-read-and-write-speed/Memory or RAM is much faster speed than a traditional hard drive. If you require to run a software that is database intensive,

How to dump specific tables using mysqldump?

I had forgotten how to dump a specific table using mysqldumb (normally I just dump the entire DB) but thankfully RolandoMySQLDBA had the simple answer! Thanks for the help 🙂 “If you are dumping tables t1, t2, and t3 from mydb” mysqldump -u… -p… mydb t1 t2 t3 > mydb_tables.sql via mysql – How do you mysqldump specific table(s)? – Database Administrators. Rating 5/5 stars, easy and intuitive!

[Err] [Bak] 1286 – Unknown table engine ‘InnoDB 00:00:00’

Last night I had an error show up a couple times while using Navicat to backup database on Weblogs.us. This was the error: [Err] [Bak] 1286 – Unknown table engine ‘InnoDB 00:00:00’ On this server MyISAM was preferred so I had specifically disabled InnoDB, yet something was trying to deal with InnoDB and it was causing the backup error . To determine which table(s) were causing the problem, I used the following SQL statement: SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA =

mysql error 1130 host is not allowed to connect to this mysql server

Question from Steve. Hello, I installed XAMPP on my Window sLaptop and then tried to access it via Navicat from my Desktop as root I get this error: mysql error number 1130 host STEVE-DESKTOP is not allowed to connect to this mysql server Can you please help me? Answer from JD. Sure Steve, I am happy to help! Your issue is caused by there being insufficient permissions in the MySQL users table, specifically your desktop machine does not have permissions

1030 Got error 28 from storage engine mysql [solved]

If you receive an error from your MySQL databaser server that reads: #1030 Got error 28 from storage engine Then  you will want to check and make sure your server has sufficient free space. One of my servers gave this error and it was because the drive  that MySQL was installed on had no more free space. So, I repeat, if you get this error check and make sure you have free space! After freeing up some space everything started working