Programming, tutorials, mechatronics, operating systems, and other tech stuff

Fixing mysql crash caused by InnoDB

1 comment

The Problem

I ran a moodle application on ubuntu inside virtual machine (virtualbox). It has been working fine for a long time until one day, it decided to stop working in the middle of an exam participated by -+ 40 students. It shows that the database is somehow crashed.


I tried to open the mysql using mysql client, and i'm unable to.

ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock'
I check the state of mysql server and turn out it have forcibly stopped, so i try to run it again using:

/etc/init.d/mysql start
and get

start: Job failed to start

First, because i ran the system in a vm, i thought it was either the disk space or memory, so i ran df command then found out it currently use only 5% of the total disk spaces. And then i ran htop and it only used <500MB from the total of 8GB allocated to the vm so i ruled out both possibility.

Ok, so i know there are something definitely wrong with the mysql server. So, just in case, i made a backup to the mysql data directory into my home dir (and you should do to, backup is very important afterall)

sudo cp -rp /var/lib/mysql ~/mysql/
The -p option here is to keep the permission, ownership and timestamp intact.

I checked the mysql logs:

cat /var/log/mysql.log
cat /var/log/mysql.err
both shows nothing, so i checked the error log

cat /var/log/mysql.err
And checking some latest lines, i got

mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.
And bunch of log text which i didn't particularly understood, stating that mysql server is crashing.


After scouring the web for solution, i came across a possibility that my innodb database is corrupted. So as a trial and error, i remove these 3 files:

Then try to run the mysql server again using /etc/init.d/mysql start and it runs. With this, i am sure that it's the innodb fault.
Before continuing, stopped the mysql server again, then copy those 3 files from the backup i've made, then start the mysql server again. As expected, it failed to start.

After scouring the web again, i've found a solution to run mysql server in innodb force recovery mode. I open /etc/mysql/my.cnf using nano, then add these lines:

innodb_force_recovery = 2
innodb_purge_threads = 0
As described here, i tried with number 1 first, then continue upward until the server could run. Found out that my magic number is 2. Which means preventing master and purge threads from running.
Taken from the link above:
Prevents the master thread and any purge threads from running. If a crash would occur during the purge operation, this recovery value prevents it. 
After the mysql server runs, i connect to the mysql using

mysql -u root -p
to make sure that my databases and tables are still there. And they are.
In this state, you won't be able to insert or update anything in the database. You can only read and drop things. So the idea here is to dump the corrupted table to a file somewhere, drop the table from database, and then import the dumped file back into database.

I run mysqlcheck to see which tables is corrupted:
mysqlcheck -u root -p --all-databases
and it says all my tables is OK.
in my case, i have to pipe the command to "more" command because i have a lot of tables.
mysqlcheck -u root -p --all-databases | more
This confuse me as i don't know which table i should dump. So i dump my database into a file as a backup. Luckily, my database is not too big to dump.

mysqldump -u root -p [pass] [db name] > [db name].sql
and it produce a single -+ 600MB sql file for a database in my case (it still pretty big for me).

If in your case you found out the corrupted table when you do mysqlcheck, you should only dump that corrupted table to avoid bloating the dump file. Especially when your database is big.

mysqldump -u root -p [pass] [db name] [table name] > [db name].[table name].sql

Then i drop the whole database, create a new database with same name, and import the dumped file back into that database.

Be careful, in your case, you might only need to drop the corrupted table, not the whole database.

And after the import proccess is finished. My moodle application can run normally.

1 comment :

Post a Comment

Create, Add, and Use Custom Font in PHP FPDF Library

By default, fonts which can be used to generate pdf file using PHP FPDF Library are only predefined standard fonts bundled along FPDF Library such as Courier, Helvetica, Arial, Times, Symbol, and ZapfDingbats. Contrary to apps like MS Word which could use fonts installed in the computer, FPDF won't recognize any fonts installed on the computer.

FPDF could not directly use fonts from ttf files. instead, the ttf file should be converted into fpdf font library which consists of php files and a "z" file. We need to convert the ttf font into those library using a script provided by fpdf library in order to be able to use the fonts in our documents.
In this tutorial, i'll explain how to convert, add and use custom font in FPDF library.
For how to use fpdf library to generate pdf file in pdf, see Generate Printable Invoice in PHP using FPDF Library.

Create The FPDF Font Library by Converting TTF Files

To turn ttf font into php in order to use it in our generated pdf document, we can use makefont.php script inside makefont directory provided along with fpdf library distribution. You can download fpdf library here.
First, extract the files into your web directory (htdocs or www). Inside, you'll find a folder named "makefont".
Next, create a directory to place your ttf files (ie. customfont) which will be converted into fpdf font library.
This is how my directories looks like:
  |   |-doc
  |   |-font
  |   |-makefont
  |   |-...
I took 4 fonts as an example in this tutorial.
Run your command line or terminal, then enter the customfont directory using cd command.
Then run this command:
php <path to makefont.php>\makefont.php <font file name>.ttf

Where <path to makefont.php> is either static or relative path to your makefont.php file, and the <font file name> is your font file name followed by .ttf extension. It's case sensitive.
You'll get something like this.
Repeat those steps for other fonts.

Add The Custom Fonts in FPDF Library

After you done converting the ttf files, go back to your customfont directory, you'll get some php and z file with font names in it.
Copy those files, leaving aside the ttf files, into the font directory inside fpdf17 directory.
You'll get something like this.
Then create a new php file for the pdf generator, include the fpdf.php library, and create a new FPDF object (Read Generate Printable Invoice in PHP using FPDF Library for further references on how to do that).

To add the font into the documents, use AddFont method of fpdf library.

//add new freescript font

//add new jokerman font

//add alien league (regular)
$pdf->AddFont('Alien League','','alienleagueii.php');
The first parameter is family name, second parameter is the font variant, third parameter is font's php file name.

The font family name can be chosen arbitrarily. If it is a standard family name, it will override the corresponding font.
The font variant can be either of these strings:
  • empty string: regular
  • B: bold
  • I: italic
  • BI or IB: bold italic
The default value is regular.

For example, in this tutorial, i have 2 forms of Alien League fonts, alienleagueii.php for regular font, and alienleagueiiital.php for the italic font. I need to add both font separately with same family name but different variant and font files. So it should looks like this:

//add alien league (regular)
$pdf->AddFont('Alien League','','alienleagueii.php');

//add alien league italic
$pdf->AddFont('Alien League','I','alienleagueiiital.php');
Please keep in mind that you need to add the fonts before you use it. So it's usually placed after fpdf object definition.

Use The Custom Font in PDF

To use the font, you can just call SetFont method with the family name and font variant like:

Keep in mind that Freescript font used in this tutorial have no other variants beside regulars. So $pdf->SetFont('Freescript','I',36); would not work.  But $pdf->SetFont('Alien League','I',36); will because we add the italic variants of Alien League font beforehand.

Here's the complete scripts.


$pdf = new FPDF('P','mm','A4');

//add new freescript font

//add new jokerman font

//add alien league (regular)
$pdf->AddFont('Alien League','','alienleagueii.php');

//add alien league italic
$pdf->AddFont('Alien League','I','alienleagueiiital.php');


//freescript font
$pdf->Cell(190,20,'Freescript Font',0,1,'C');

//jokerman font
$pdf->Cell(190,20,'Jokerman Font',0,1,'C');

//alien league regular font
$pdf->SetFont('Alien League','',36);
$pdf->Cell(190,20,'Alien League Regular Font',0,1,'C');

//alien league italic font
$pdf->SetFont('Alien League','I',36);
$pdf->Cell(190,20,'Alien League Italic Font',0,1,'C');


And here's what the results look like.

For the video explanation of this tutorial, please watch this video. And for other tutorials regarding PHP FPDF Library, please watch my youtube PHP FPDF Tutorial Series.


Post a Comment

Generate Printable Invoice in PHP using FPDF library

Nowadays, when almost everything is connected to internet. More people chooses web based application (or "webapp" in short) over desktop-based applications with various reasons but mostly because its flexibility and mobility.
WebApp can become potentially powerful in computing power compared to stand alone application. Because the processing is done in server-side, even a low-end mobile devices can access robust information which requires complex calculation and can also manipulate it.
But nothing is perfect, web app also have a lot of drawbacks. One of them is when it comes to physical document handling, such as document printing.

PDF generation in PHP

PDF generation in PHP gave a web-based application abilities to represent a printable, fixed- layout document to millimeter precision.
Such as printable invoices, printable reports, printable manifests, and other printable  documents.
In short, PDF generation is a best method to represent a printable document, compared to  any other method such as html, which is "dynamic layout" by nature.
The down side is, we cannot alter the produced document and it depends on browser's PDF  plugins whether to display it immediately on browser or just download it.
Here's a tutorial of how to make a PDF invoice in PHP using FPDF class.
You can download fpdf library package then extract it's content to your htdocs directory.
This package contains fpdf php library, example tutorials, and documentation.
FPDF has many methods to make and manipulate PDF content as you can see in its  documentation here.

Make PDF Invoice using PHP FPDF library

First, you need to download the fpdf library here.
Then extract it somewhere in your htdocs directory. Then make a php file as the pdf generator.
In this example, i am going t use portrait, A4 size paper with mm measurement unit.

//call the FPDF library

//A4 width : 219mm
//default margin : 10mm each side
//writable horizontal : 219-(10*2)=189mm

//create pdf object
$pdf = new FPDF('P','mm','A4');


Next, add a blank page using $pdf->AddPage(); method. Then output the result using $pdf->Output(); method.

//call the FPDF library

//A4 width : 219mm
//default margin : 10mm each side
//writable horizontal : 219-(10*2)=189mm

//create pdf object
$pdf = new FPDF('P','mm','A4');
//add new page
//output the result

You'll get something like:

Congratulations! you've just made a pdf file by yourself... Now what?...

Add content to pdf file with cells

Text content in pdf is written in cells (Imagine cells as a container). You can put text inside cells, and each cells has its own properties such as width, height, and border.
Cells are stacked horizontally one after another from left to right and end with a cell which defined as a line ending by its parameter or until $pdf->Ln(); method is invoked (imagine what "enter" button do when you're typing in text editor).
Fpdf cell method have following parameters (taken from it's documentation here):
Cell(float w [, float h [, string txt [, mixed border [, int ln [, string align [, boolean fill [, mixed link]]]]]]])
w and h is for width and height.
txt is the content.
border to define the border. It could be 1 for all border, 0 for no border, and combination between L,T,R,B. Please refer to the doc for further explanation. what we need right now is 1 and 0.
ln is line ending. 1 to end the line with this cell, otherwise 0.
align is the text alignment L,C,R, no justified.
as for fill and link, we don't need it right now so kindly refer to the docs.

Setting font properties in pdf

Before adding anything to the pdf, we should first set the font that will be used. Following is the SetFont method parameters taken from here:
SetFont(string family [, string style [, float size]])
family is the font such as 'Arial','Courier','Times', etc. Should be standard font or font defined by AddFont method (i'll explain it in another tutorial, meanwhile please watch this).
style is either one or combination between B,I,U or an empty string for none.
size is the font size in points.

Now we got the basics required to build our pdf invoice, let's move to the main point.

Make the printable PDF Invoice

Here is the codes for the invoice. Put this just after AddPage() and before Output().
I included some comments as explanation in the code.

//set font to arial, bold, 14pt

//Cell(width , height , text , border , end line , [align] )

$pdf->Cell(130 ,5,'GEMUL APPLIANCES.CO',0,0);
$pdf->Cell(59 ,5,'INVOICE',0,1);//end of line

//set font to arial, regular, 12pt

$pdf->Cell(130 ,5,'[Street Address]',0,0);
$pdf->Cell(59 ,5,'',0,1);//end of line

$pdf->Cell(130 ,5,'[City, Country, ZIP]',0,0);
$pdf->Cell(25 ,5,'Date',0,0);
$pdf->Cell(34 ,5,'[dd/mm/yyyy]',0,1);//end of line

$pdf->Cell(130 ,5,'Phone [+12345678]',0,0);
$pdf->Cell(25 ,5,'Invoice #',0,0);
$pdf->Cell(34 ,5,'[1234567]',0,1);//end of line

$pdf->Cell(130 ,5,'Fax [+12345678]',0,0);
$pdf->Cell(25 ,5,'Customer ID',0,0);
$pdf->Cell(34 ,5,'[1234567]',0,1);//end of line

//make a dummy empty cell as a vertical spacer
$pdf->Cell(189 ,10,'',0,1);//end of line

//billing address
$pdf->Cell(100 ,5,'Bill to',0,1);//end of line

//add dummy cell at beginning of each line for indentation
$pdf->Cell(10 ,5,'',0,0);
$pdf->Cell(90 ,5,'[Name]',0,1);

$pdf->Cell(10 ,5,'',0,0);
$pdf->Cell(90 ,5,'[Company Name]',0,1);

$pdf->Cell(10 ,5,'',0,0);
$pdf->Cell(90 ,5,'[Address]',0,1);

$pdf->Cell(10 ,5,'',0,0);
$pdf->Cell(90 ,5,'[Phone]',0,1);

//make a dummy empty cell as a vertical spacer
$pdf->Cell(189 ,10,'',0,1);//end of line

//invoice contents

$pdf->Cell(130 ,5,'Description',1,0);
$pdf->Cell(25 ,5,'Taxable',1,0);
$pdf->Cell(34 ,5,'Amount',1,1);//end of line


//Numbers are right-aligned so we give 'R' after new line parameter

$pdf->Cell(130 ,5,'UltraCool Fridge',1,0);
$pdf->Cell(25 ,5,'-',1,0);
$pdf->Cell(34 ,5,'3,250',1,1,'R');//end of line

$pdf->Cell(130 ,5,'Supaclean Diswasher',1,0);
$pdf->Cell(25 ,5,'-',1,0);
$pdf->Cell(34 ,5,'1,200',1,1,'R');//end of line

$pdf->Cell(130 ,5,'Something Else',1,0);
$pdf->Cell(25 ,5,'-',1,0);
$pdf->Cell(34 ,5,'1,000',1,1,'R');//end of line

$pdf->Cell(130 ,5,'',0,0);
$pdf->Cell(25 ,5,'Subtotal',0,0);
$pdf->Cell(4 ,5,'$',1,0);
$pdf->Cell(30 ,5,'4,450',1,1,'R');//end of line

$pdf->Cell(130 ,5,'',0,0);
$pdf->Cell(25 ,5,'Taxable',0,0);
$pdf->Cell(4 ,5,'$',1,0);
$pdf->Cell(30 ,5,'0',1,1,'R');//end of line

$pdf->Cell(130 ,5,'',0,0);
$pdf->Cell(25 ,5,'Tax Rate',0,0);
$pdf->Cell(4 ,5,'$',1,0);
$pdf->Cell(30 ,5,'10%',1,1,'R');//end of line

$pdf->Cell(130 ,5,'',0,0);
$pdf->Cell(25 ,5,'Total Due',0,0);
$pdf->Cell(4 ,5,'$',1,0);
$pdf->Cell(30 ,5,'4,450',1,1,'R');//end of line

Save then refresh your browser. If everything is alright. you should have something like this.
Congratulation! You've just made a printable pdf invoice.

If by any chance you're still confused after following this tutorial, please do enjoy this video-version of this tutorial. And if you want the source code, feel free to ask me in the comment section of aforementioned video.


Post a Comment

Simple Ways of Getting Data from URL in PHP (Web Scraping)

When you need to get data / content from a certain website url (ie. for scrapping, data fetching, or something like that). There are 2 simple ways you can use. By using built-in php function file_get_contents() and using cURL library.

file_get_contents is so far the most simple way of getting data, but it lacks options if compared to cURL library. So decide wisely which method suits your need. If you just need to get a website content, then file_get_contents is the best choice. But if you need to do something more, like setting user agent, timeout, response type, error handling, or to access HTTPS URLs. cURL is the most capable.

Get Content from URL using PHP file_get_contents()

Getting data using file_get_contens() is as simple as :

echo $data;
It basically fetch anything from given address, whether it's a URL, or a file path. But for real-world implementation, every tcp connection does need some time to finish the request. In this case, the script would wait until the request processes finished (sending request, waiting, getting data) then continue the execution. If the connection is somehow failed, the script will keep continue so you will need to handle every possible outcome from the connection.

Get Content from URL using PHP DOMDocument class

Another way to fetch a web content in PHP is to use PHP DOMDocument class. In this method, we can directly process the retrieved document by DOM. The downside is, the content is only limited to HTML / XML. Usually, DOMDocument is already bundled in php so you don't have to install anything.
Using DOMDocument is as simple as :

$dom = new DOMDocument();
$data = $dom->getElementById("banner");
echo $data->nodeValue."\n"

First, create a new DOMDocument object by
$dom = new DOMDocument();

Then load the HTML File using loadHTMLFile method with url as parameter.

Finally, access the DOM (you can read the docs here)

In real world, not ALL html document is well formed. Whether it's an unclosed tags, mismatch brackets, malformed attribute-value pair, etc. Those will raise confuse DOMDocument causing it to throw an exception. To avoid that, we need to set
to suppress libxml's internal errors.

Get Content from URL using PHPQuery class

If you are familiar with how jquery works, this method is the best for you. This class library is originally created by Tobiasz Cudnik. This class implement css3 selector based on jquery javascript library.
By using PHPQuery class you can do something like:
$doc['ul > li'] ->addClass('active-list');
Feels familiar?

You can find more example here.

Beside those three above, there are many more alternatives methods to acquire data from webpages in PHP. IMHO, those three above is the best ways i am already familiar with.


Post a Comment