Killing MySQL Processes

Once in a while you might face problems related to MySQL being not responding. Most of the time it’s due to large database size or you’re running a select query on an un-indexed table. Instead of waiting for the process to complete (which may take hours), you can cancel the stalling processes using the following method.

First, open up another MySQL session and call the following query:

SHOW PROCESSLIST;

Next, you will see results like this:

+—-+—————–+———–+——+———+——+————————+——————+———-+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+—-+—————–+———–+——+———+——+————————+——————+———-+
| 254772 | event_scheduler | localhost | NULL | Daemon | 2693 | Waiting on empty queue | NULL | 0.000 |
| 435342 | root | localhost | NULL | Query | 0 | Table lock | SHOW PROCESSLIST | 0.000 |
+—-+—————–+———–+——+———+——+————————+——————+———-+

Look for the ID of the query which you wish to terminate and use it in the following query, for example:

KILL 435342;

That’s it, the process will then be terminated by MySQL. Hope it helps.

Failed Setting Triangles Error

Recently, a reader sent me the following screenshot which shows a corrupted scene from my book’s demo project.

He also showed me the errors and warning he get when running the demo project:

Warning:

Lightmap snapshot ‘LightmapSnapshot’ is inconsistent – 62 files out of 248 are missing. Realtime GI will be disabled. Please rebuild lighting for this scene.

Errors:

Failed setting triangles. Some indices are referencing out of bounds vertices. IndexCount: 600, VertexCount: 11191
UnityEditor.UnityBuildPostprocessor:OnPostprocessScene()

Failed setting triangles. Some indices are referencing out of bounds vertices. IndexCount: 96, VertexCount: 11191
UnityEditor.UnityBuildPostprocessor:OnPostprocessScene()

This happens when Unity tries to convert the demo project to the latest version, which sometimes might mess up the files.

Solution:

To solve this issue, reimport the mesh which has been corrupted by the conversion (in this case, terrain and rocks) by right clicking on the asset in Unity and choose “Reimport”, or delete the prefabs from the scene and place it again manually.

Once you have done the steps above, re-bake the lighting again and everything will work fine.

Minimal Qt Project

Today I was trying to experiment on how small can a Qt project get if I only use the essential modules and scrap away all the rest.

First I created a new Qt project by going to:
File -> New File or Project… -> Application -> Qt Widgets Application

When I build the new project I’m getting this empty window, which is expected:

Then I did the following:

1. Delete mainwindow.ui
2. Delete mainWindow.h and mainWindow.cpp
3. In main.cpp, remove QApplication and replace it with QGuiApplication instead.
4. Use QWindow instead of QMainWindow

The reason why I removed QApplication and QMainWindow is because they both rely on QtWidget module. This way we can reduce the size further by removing one more dependency. Now all I left are just 2 file: Myproject.pro and main.cpp, and they now look like this:

MyProject.pro

TARGET = MyProject
TEMPLATE = app
SOURCES += main.cpp

main.cpp

#include 
#include 

int main(int argc, char *argv[])
{
    QGuiApplication app(argc, argv);
    QWindow window;
    window.setTitle("Hello World!");
    window.resize(640, 480);
    window.show();

    return app.exec();
}

Then I build the project again, which the result should look totally the same as before. However, we no longer need QtWidget.dll anymore which reduces the number of DLL files to just 5:

– libgcc_s_dw2-1.dll
– libstdc++-6.dll
– libwinpthread-1.dll
– Qt5Core.dll
– Qt5Gui.dll

The total size of entire executable folder, including all the DLL files mentioned above, is now only 11.3MB. Its still fairly large if compare to other application framework, but considering two things:

1. There are tons of functionalities in QtCore and QtGui alone, including all the helper classes and even OpenGL
2. All the DLL files in the entire Qt SDK is at the staggering size of 1.87GB in total

11.3MB is actually pretty damn impressive!

Import and Export Large MySQL Database

For most of the time, web-based database management control panel such as phpMyAdmin does the job well for importing and exporting backups. However, as your database grows larger and larger, it will reach to a point where it can no longer be done using any web-based control panels anymore.

For instance, our database has reached 500mb just a couple of months ago and phpMyAdmin would stop halfway when doing the job and showing a timeout message. It’s a standard for web server to stop loading a page and declare as timeout once it waited for a certain amount of time. You can of course change the web server configuration to make the timeout really really long but that is not practical and may break your web server.

A better way would be using the command line tools came together with MySQL. Let’s try to import a large database using the command line tool. The first thing you need to do is to open up the command prompt, or terminal. For Windows operating system, you need to access to the bin folder in your MySQL installation directory, for example:

cd C:\xampp\mysql\bin

Then, use the following command to import the SQL file to your database.

mysql -u username -p database_name < file.sql

Then, a message will pop out and ask for the database password. Insert the correct password and wait for a while until all the data is being loaded to the database. If you see your mysql directory is showing in the next line, it means the operation has be done. Your command prompt should look something like this:

Next, we will export our database using the command line tool. This time, we will be using the mysqldump utility. To export your database, type in this command line:

mysqldump -u username -p database_name > file.sql

If you don't specify a target destination for the file, it will be saved to the bin folder, in this case, C:\xampp\mysql\bin.

That's all! It's very easy and quick once you're familiar with the command line.

MySQL Tips

Count Item Quantity:

We can count the quantity of data contained in a table by using COUNT.

SELECT COUNT(*) AS count FROM employee

It can also be used to count the quantity of data that matches the condition you set.

SELECT COUNT(marriageStatus = 0) AS count FROM employee

This however, is really slow compare to the previous query because internally, MySQL already contains a static variable that tell you exactly the quantity so it doesn’t count it all over again. For the second query which uses condition, MySQL has to check the condition for each of the data and count it one by one before it’s able to tell you the quantity, therefore, very slow.

Combine Repeating Queries Into One:

We can combine queries that are being called repeatedly into 1 single query to speed up the performance. There are people who use UNION for this but that actually has plenty of limitation as well as not helping much in term of performance because we’re still sending tons of repetitive commands despite it’s sent in one go.

In-order to solve this issue, we combine the condition that we use to search the data into 1 single query by using OR. The resulting query looks like this:

SELECT * FROM employee where id = 1 OR id = 2 OR id = 3

For the application side, you can add all the conditions from an array using for loop, which look like this:

string query = "SELECT * FROM employee where ";
for (int i = 0; i < myArray.length(); i++)
{
    query += "id = " + myArray.at(i) + " OR";
}
query.mid(2); // Remove the last OR

From my personal experience, this can sometime speed up the query time up to 10x, depending on how big the data you're getting.

Join Multiple Tables Together:

Sometimes we want some portion of data from Table A, but also require another portion of data from Table B, Table C and so on. We want to join the data altogether into a single result so that it's straight forward and we don't have to do it multiple times. This can be achieved through INNER JOIN, LEFT JOIN and RIGHT JOIN. You can check out the differences between them on the internet, but I'll show you an example on how I use INNER JOIN:

SELECT employeeinfo.id, employeeinfo.name, company.name FROM (SELECT id, name, companyID FROM employee AS employeeinfo) INNER JOIN company ON company.id = employeeinfo.companyID

Replace URL in Your Content:

There are times where you might need to change all the URL in your site's contents, especially if you have just moved your website to a new domain, which means all the hyperlinks and images in your website will be broken. It's pain in the arse and almost impossible to fix if the contents of your website has grown into a massive pile of data.

MySQL actually provide a pretty handy function called REPLACE which does exactly this. For example, it will change the image link localhost/testsite/mylogo.png to yourwebsite.com/mylogo.png which only replace the main URL in-front and not the image name with its extension. Here I show an example in WordPress' format:

UPDATE `wp_posts` SET `post_content` = REPLACE(post_content, 'localhost/testsite', 'yourwebsite.com')

Search Using Keywords:

This is really straight forward, you just need to use LIKE and place your keyword between the two % symbols.

SELECT * FROM employee WHERE fullname LIKE %keyword_here%

Eliminate Duplicate Rows in a Result Set:

Sometimes a query may return duplicated results when you search through a table that serves as a log or history list but you only need one if the result ever exist. For example, I want to generate a list of users who logged in to my website this month. Because a user may log in multiple times in a month, so there may be duplicated rows in the result set. So, instead of something like this:

User Name Login Timestamp
John Doe 2015-08-01 12:30:02
John Doe 2015-08-01 1:20:14
John Doe 2015-08-01 4:30:22
Sally Smith 2015-08-01 4:31:06
John Doe 2015-08-01 5:30:06
Sally Smith 2015-08-01 5:45:02

What we want is just a distinct set of results:

User Name Login Timestamp
John Doe 2015-08-01 12:30:02
Sally Smith 2015-08-01 4:31:06

In order to filter out the results, we use DISTINCT in our query:

SELECT DISTINCT userName FROM loginHistory WHERE MONTH(logDate) = MONTH(CURDATE())

It's that simple!

How To Fix Intel NUC Overheating Problem

Months ago I purchased an Intel NUC mini computer to use as a low power SVN server. I soon realize that it has a serious issue – overheating that causes the user interface to disappear and freezes the entire system.

I searched the web and found out many people are actually having similar problems like this. What really happened was the heat generated by the machine itself was not able to release quick enough and causes the hard drive to stop functioning. Intel NUC does have a built-in fan, but it’s on the upper side of the machine, while the lower side has no fan at all to release the heat effectively.

Intel solved this issue recently by adding a built-in thermal pad to cool down the hard drive, but it’s only available if you purchase a new machine. For older machine like mine, we need to find other solutions.

No where can I find a thermal pad as thick as what Intel is using for their newer NUCs. However, I do find a smaller one – Thermal Conductive Pad 10x10x1.0MM 20pcs. I bought a package of these which costs me only RM10 (USD 3). Looks good enough!

Here you see it’s only 12 pieces because the original 20 pieces length was too long so I cut it shorter, save the other half for future use. Then, just stick it to the back of the cover using a double-sided tape, and viola! When I put back the cover onto the machine, the thermal pad is barely touching the hard drive, which I wondered if it will work at all.

However, I tested the machine again by turning it on for few hours and the issue never occurred anymore. Cheap and easy fix!

Change Unity3D Build Target Using CMD

Most people probably won’t need this, unless you encountered such scenario:

You're handling a huge project, and you somehow need to move the entire project folder from a Windows machine to a Mac for porting the project to iOS.
What will happen is when you open the project for the first time on Mac, Unity will load all the files and convert them into Windows formats because well, your project was set to run on Windows previously.
You waited for half an hour or so for all the files to be converted (it' a huge project!) and now you must change the build settings again, and wait for another half an hour for it to be converted to iOS!

If you want to avoid running the conversion twice, you can change your Unity project’s build target before opening the editor, by using the command prompt, like so:

Windows:

"C:\Program Files(x86)\Unity\Editor\Unity.exe" -buildTarget 

Mac:

/Applications/Unity/Unity.app/Contents/MacOS/Unity -buildTarget

Possible options are: win32, win64, osx, osxintel64, osxuniversal, linux, linux64, linuxuniversal, ios, android, web, webstreamed, webgl, xbox360, xboxone, ps3, ps4, psp2, wsa, wp8, bb10, tizen, samsungtv

For example:

"C:\Program Files (x86)\Unity\Editor\Unity.exe" -buildTarget ios

This way, Unity will straight away convert your project files to iOS formats and no need to convert it twice. Time saved!

View Debug Messages on Android

If you’re currently developing any android apps, you might be wondering how to check debug messages when running the apps on a physical device. Android SDK actually comes with an utility called Logcat, which does exactly that.

If you’re running Eclipse IDE and it is not showing any debug messages, your Eclipse is probably not linked to the Logcat. You can set it up by going to:

Window -> Show View -> Other… -> Android -> LogCat

However, if you’re running other development tools that don’t support this feature (such as Unity Engine), you can fire up Logcat manually by using the command pad/terminal.

On Windows, you need to enter the (android sdk folder)\platform-tools folder before calling Logcat:

cd C:\android-sdk-windows\platform-tools

After that, you can call Logcat by first calling the ADB (Android Debug Bridge) and launch Logcat from it, like so:

adb logcat

However, if you’re just calling adb logcat you may see the terminal going crazy by showing tons of debug texts in crazy speed. This is because the Logcat is actually displaying debug texts of ALL the processes running on your device. You definitely do not want that, don’t you?

You can add an extra parameter behind the command which filters out the process list and only display your app. For example, if your app is built using Unity Engine, you can call:

adb logcat -s Unity

…which looks like this on the terminal:

That’s all. You can now easily debug your app and know exactly what’s going when bugs occur. Happy debugging!

Set MySQL Time Zone

Sometimes your MySQL database’s timezone is set to UTC time by default and does not follow your local timezone. In this case, you’ll need to manually change its configuration.

The configuration file can usually be found in mysql folder, which, depends on the operating system you’re running, could be installed in different directory.

Linux & Mac:

  • /usr/local/mysql/support-files/my.cnf
  • /etc/my.cnf
  • /etc/mysql/my.cnf

Windows:

  • C:/AppServ/MySQL/my.cnf
  • C:/Program Files/MySQL/MySQL Server 4.1/my.ini

Once you found the config file, open it up with text editor and add this line to the end of the file:

default-time-zone = '+08:00'

In this example, I’m change the time zone to GMT+8.

Next, you need to restart MySQL in order to see the effect. You could do so by opening up command prompt and type in this command:

Linux:

sudo service mysql restart

Windows:

net stop MySQL
net start MySQL

Mac:

sudo /usr/local/mysql/support-files/mysql.server restart

Now your MySQL should run in the correct time zone!

Rect Error in Game Mode

If you are getting this error when running Unity Engine in game mode:

rect[2] == rt->GetGLWidth() rect[3] == rt->GetGLHeight()

It’s actually an error emitted by the editor itself, nothing to worry about. To remove this issue, go to Window → Layouts → Default to reset the editor layout. The error will then disappear.

The error is usually associated to conflicts between image effects and screen aspect ratio. It’s not really a serious issue, just follow the steps above and you’re good to go.