Tag Archives: mysql

[Err] 2006 – MySQL server has gone away Navicat/WordPress [solved]

Leave a comment

Recently I tried upgrading to the latest version of the WordPress Download Monitor plugin. It did not go well 🙁 Thus, I tried to revert from a backup made using Navicat. Unfortunately, the restore would fail every time while trying to import my wp_posts table (a pretty important table!) I tried exporting the backup to a SQL dump and importing that. No luck. Finally, I tried resolving the actual ‘MySQL server has gone away’ issue itself and I had success. This tip

Read more Software Tips, Weblogs.us, WordPress tips, Work

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

2 comments

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

Read more programming, Software Tips, Work

Set your own date format in MySQL

Leave a comment

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… 🙂

Read more Misc Links, tech tips

Evaluating a RAM disk as a MySQL data store

Leave a comment
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,

Read more computer tips, Work

How to dump specific tables using mysqldump?

Leave a comment

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!

Read more Software Tips

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

Leave a comment

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 =

Read more computer tips, Weblogs.us, Work

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

Leave a comment

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

Read more computer tips, Work

1030 Got error 28 from storage engine mysql [solved]

2 comments

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

Read more Weblogs.us, Work