Author: techworkshop

Configure MySQL 5.6 LONGBLOB for large binary data

The reason for this issue is a change in MySQL 5.6.20 as one could read in the change log:

As a result of the redo log BLOB write limit introduced for MySQL 5.6, the innodb_log_file_size setting should be 10 times larger than the largest BLOB data size found in the rows of your tables plus the length of other variable length fields (VARCHAR, VARBINARY, and TEXT type fields). No action is required if your innodb_log_file_size setting is already sufficiently large or your tables contain no BLOB data.

Set or increase the value of the innodb_log_file_size option in my.ini below the [mysqld] section. Its default value is 48M. Setting it to

[mysqld]
innodb_log_file_size=256M

Be careful when changing the value of innodb_log_file_size. Follow these steps to do this safely:

  • Shut the server down cleanly and normally.
  • Shutting down MySQL may not be as simple as just service mysql stop!
  • Following things should be done to shut down mysql server normally and cleanly:
    1. Double check the instance you are going to shutdown!!
    2. Stop Replication
    3. Flush the dirty pages
    4. Check the long running transactions
    5. Dump and reload the buffer pool
  • Move away (don’t delete) the log files, which are named ib_logfile0, ib_logfile1, and so on.
  • Check the error log to ensure there was no problem shutting down.
  • Then restart the server and watch the error log output carefully.
    • There should see InnoDB print messages saying that the log files don’t exist. It will create new ones and then start.
  • Verify that InnoDB is working. If it’s working, then the old log files can be deleted.

What are ORM Frameworks?

ORM is a short form of Object Relational Mapping, which means as ORM framework is written specifically in OOP (object-oriented programming) language (like PHP, C#, Java, etc…) and it is like a wrapper around a relational database (like MySQL, PostgreSQL, Oracle, etc…). So, ORM is basically mapping objects to relational tables.

What does an ORM framework do?

The ORM framework generates objects (as in OOP) that virtually map the tables in a database. So, any programmer could use these objects to interact with the database without writing an optimized SQL code.

For example:

We have 2 tables in a database:

  • Products
  • Orders

The ORM framework would create 2 objects corresponding to the above tables (like products_object and orders_object) with little configuration, which will handle all the database interactions. So, if you want to add a new product to the products table, you would have to use the products_object and save() method like below,

product = new products_object("Refrigerator","Electronics");
product.save();

You can see, how much easier an ORM framework can make things. No need to write any SQL syntax. And the application code would be very clean.

Some other advantages of using ORM frameworks

1. Syncing between OOP language and the relational database data types is always creating a problem. Sometimes variable data types have to be converted properly to insert into the database. A good ORM framework will take care of these conversions.

2. Using an ORM will create a consistent code base for your application since no SQL statements are written in the code. This makes it easier to write and debug any application, especially if more programmers are using same code base.

3. ORM frameworks will shield your application from SQL injection attacks since the framework will be filtering the data before any operation in the database.

4. Database Abstraction; Switching databases for the application is easier as, ORM will take care of writing all the SQL code, data type conversions etc …

When to use an ORM framework?

An ORM framework becomes more useful as the size and complexity of the project increases. An ORM framework may be overkilling an application on a simple database with 5 tables and 5-6 queries to be used for the application.

Consider the use of ORM when:

  • 3 or more programmers are working on an application.
  • Application database consists of 10+ tables.
  • The application is using 10+ queries.

About 80-90% of application queries can be handled by the ORM generated objects. It is inevitable that at some point straight SQL query is required, which can’t be handled by ORM generated objects.

In fact, ORM frameworks often have their own *QL query language that looks a lot like SQL. Doctrine, a popular PHP based ORM framework has DQL (Doctrine Query Language) and the very popular Hibernate (used in the Java and .Net world) has HQL. Going even further, Hibernate allows writing straight SQL if need be.

ORM Frameworks for PHP programmers

  • CakePHP, ORM, and framework for PHP 5
  • CodeIgniter, a framework that includes an ActiveRecord implementation
  • Doctrine, open source ORM for PHP 5.3.X
  • FuelPHP, ORM, and framework for PHP 5.3. Based on the ActiveRecord pattern.
  • Laravel, a framework that contains an ORM called “Eloquent” an ActiveRecord implementation.
  • Maghead, a database framework designed for PHP7 includes ORM, Sharding, DBAL, SQL Builder tools etc.
  • Propel, ORM and query-toolkit for PHP 5, inspired by Apache Torque
  • Qcodo, ORM, and framework for PHP 5
  • QCubed, A community-driven fork of Qcodo
  • Redbean, ORM layer for PHP 5, creates and maintains tables on the fly
  • Yii, ORM, and framework for PHP 5. Based on the ActiveRecord pattern.
  • Zend Framework, a framework that includes a table data gateway and row data gateway implementations. ZendDb

Best alternative to set a PostgreSQL schema using PHP PDO

To set the PostgreSQL DB connection, schema parameter is not to be included

$Conn = new PDO('pgsql:host=localhost;port=5432;dbname=db', 'user', 'pass');
$result = $Conn->exec('SET search_path TO accountschema');
if ( ! $result) {
die('Failed to set schema: ' . $Conn->errorMsg());
}

Is this a good practice? Is there a better way to do this?

In order to specify the default schema you should set the search_path instead.

$Conn->exec('SET search_path TO accountschema');

You can also set the default search_path per database user and in that case the above statement becomes redundant.

ALTER USER user SET search_path TO accountschema;

Access shell with SSH on cPanel server from Linux

Some of the important server related problems will be solved only using shell access to the server. To access the cPanel server shell using SSH, there must be an SSH client installed on PC. Most of the Linux distros include SSH client software by default. If it is not installed, then it can be easily installed with following commands,

For Ubuntu: apt-get install openssh-client
For CentOS: yum install openssh-clients

After installation, follow these steps to access the cPanel shell with SSH from Linux:

  1. Login to cPanel and go to Security > SSH/Shell Access to generate SSH key pair.
  2. Click Manage SSH Keys > Generate a New Key. You should use a password to protect the key. You will be asked the password each time you use the key.
  3. In Public Keys section click ‘Manage Authorization’ and ‘Authorize’
  4. In Private Keys section click, Vew/Download then download the key (id_dsa or id_rsa) to your PC.
  5. Save it to ~/.ssh directory on your Linux machine under a meaningful name to not overwrite your existing keys for example id_dsa.myjavahost
  6. Now make sure permissions are correct on the key (one-time task) and connect:
    mypc:~$ chmod 600 .ssh/id_dsa
    mypc:~$ ssh -p1033 -i .ssh/id_dsa yourusername@yourservername
    Enter passphrase for key '.ssh/id_dsa':
  1. Provide the password for the key, set up in step #2

You should be logged in by now.

Windows 10 setup: hangs on Checking for Updates

To upgrade Windows from 7.0 to 10, use Microsoft Media Tool to download and upgrade Windows. During this process sometimes setup hangs on ‘Getting Updates’ screen with ‘Checking for Updates’ message.

Possible reason for this may be, the current version of Windows 7.0 may not be activated. So, to complete the setup without activation, follow these steps,

  1. Leave the Windows 10 Setup window as it is
  2. Go to Services (Press Windows+R. This will prompt run dialogue box. In here, type services.msc) and stop the Windows Update Service.
  3. Go to C:\Windows\SoftwareDistribution and delete all of its contents.
  4. Restart the Windows Update Service.
  5. Go back to the Windows 10 Setup window and it should continue.

These basic steps will overcome the activation problem.