Laravel 5.4 migration: Specified key was too long error solution

Three days ago Laravel 5.4 was released, so I have decided to give it a try. I have made a new Laravel 5.4 project and just when I was about to run the default auth migrations that are shipped with the Laravel almost without any other changes, I got these two red errors:

[Illuminate\Database\QueryException]
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter table `users` add unique `users_email_unique`(`email`))

 

[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

What the hell is going on!?!? I have checked the migrations file and everything was okay there unchanged compared to Laravel 5.3. But, according to Laravel Documentation one thing changed:

Laravel 5.4 uses the utf8mb4 character set by default, which includes support for storing “emojis” in the database. If you are upgrading your application from Laravel 5.3, you are not required to switch to this character set.

And, according to their documentation the problem is that I’m running version of MySQL prior to 5.7.7 or MariaDB prior to 10.2.2 (my case). In case you are running versions of MySQL or MariaDB greater than these than most probably that you won’t have this problem at all.

In case you are having this problem – here are the possible solutions:

1. Upgrade MariaDB / MySQL

To update MariaDB on Mac using Brew, first you need to unlink the current one using:

brew unlink mariadb

and then install a dev one using

brew install mariadb --devel

That will install MariaDB version 10.2.3 that works fine. Remember that after installation is done you need to  stop/start the service:

brew services stop mariadb
brew services start mariadb

2. Stick with utf8

In case you don’t want to use the utf8mb4 (that is now setup by default in Laravel 5.4) – instead you want to stick with utf8 that was in use in Laravel 5.3 and all versions before, simply edit /config/database.php  and find these two lines in mysql driver:

'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',

and replace them with with

'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',

That will stop using the utf8mb4 and will change back to utf8. The good thing about this is that you’ll be able to fit 25% more characters into the same columns in database compared with utf8mb4 and that you’ll be able to use existing databases and projects without the need of converting them if you don’t want to. And the downside is that some characters will not be able to be saved to database like this mac command character ⌘ or some emojis. Utf8 can only store Plane Unicode while utf8mb4 can store any unicode character.  Utf8mb4 is also 100% backwards compatible with utf8.

3. Use utf8mb4 without MySQL/MariaDB upgrade

In case you want to use utf8mb4 but you don’t want to or simply can’t upgrade your MySQL/MariaDB there is a simple solution  proposed by Laravel documentation. All you need to do is edit AppServiceProvider located at App\Providers\AppServiceProvider.php and add the following line to the boot() method and load the Schema facade:

use Illuminate\Support\Facades\Schema;

/**
 * Bootstrap any application services.
 *
 * @return void
 */
public function boot()
{
    Schema::defaultStringLength(191);
}

What that will do is actually change the default maximum field length in the database and actually shorten your maximum string length from 255 to maximum of 191 characters (utf8 uses 3 bytes per character while utf8mb4 uses 4 bytes per character your field can now hold 25% less characters 255 * 75% = 191.25). So if you don’t set the string field by hand in the migration the new default will be 191. You can increase the length if you need it manually by defining it:

$table->string('name', 255);

But this will not be possible on indexed fields since these can’t be longer than 191 characters. So keep this in mind in case you’re planing to use utf8mb4.

In case you want to convert the database from utf8 to utf8mb4 for any existing projects or if you are upgrading from Larvel 5.3 or older to Laravel 5.3 this is the code you need to run for every database, table and column:

Make sure you have the database backups before making these changes!!!!

# For each database:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# For each table:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# For each column:
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# (Don’t just blindly copy-paste this! The exact statement depends on the column type, maximum length, and other properties. The above line is just an example for a `VARCHAR` column.)

Once again: when converting from utf8 to utf8mb4, the maximum length of a column or index key is unchanged in terms of bytes but since utf8mb4 uses 25% more bytes per character your columns can now accept less characters. Therefore, it is smaller in terms of characters, because the maximum length of a character is now four bytes instead of three.

For example, a TINYTEXT column can hold up to 255 bytes, which correlates to 85 three-byte or 63 four-byte characters. Let’s say you have a TINYTEXT column that uses utf8 but must be able to contain more than 63 characters. Given this requirement, you can’t convert this column to utf8mb4 unless you also change the data type to a longer type such as TEXT — because if you’d try to fill it with four-byte characters, you’d only be able to enter 63 characters, but not more.

The same goes for index keys. The InnoDB storage engine has a maximum index length of 767 bytes, so for utf8 or utf8mb4 columns, you can index a maximum of 255 or 191 characters, respectively. If you currently have utf8 columns with indexes longer than 191 characters, you will need to index a smaller number of characters when using utf8mb4.

If you plan on using utf8mb4 make sure to setup your MySQL server accordingly by editing /etc/my.cnf file accordingly:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Conclusion

If your application won’t use any special characters or emojis or languages such as Chinese, Japanese, and Korean you’re most probably fine sticking with utf8. But if you can – you should move to utf7mb4 since that will stop you from loosing 4-byte characters when a user ads these in comments or in message or whenever you store these in your database. You should always strive to full unicode support everywhere in your apps and updating your database and code might take some time but it’s definitely worth the time and effort.

Tags: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was to

CodeIgniter white page on cPanel

After cPanel upgrade from EasyApache3 to EasyApache4 all my CodeIgniter installations just broke on that server, returning just a white page without any error message. I spent more than one hour trying to figure out what the problem is. While testing out I noticed empty files named like these “0bce68a7a37e584ace98f0fd242a237d1662613e” or “296eed9bfb533552b0c3a9c8fdd784067eea216d”   (without any extension) started popping in my project folder. I figured out that these must be session paths so something is wrong with that.

I tried enabling php error logging but it didn’t help since it didn’t log anything since all code is right and it was working before but it just stopped after I started using EasyApache4.

So I went and enabled CodeIgniter logging that proved to be a better idea. To enable it open config.php for editing, locate

$config['log_threshold'] = 0;

And change that value to 4. That will enable internal logging system and logs will be saved in /application/logs folder. What I found out just conf

ERROR - 2016-11-29 22:36:29 --> Severity: Warning --> mkdir(): Invalid path Session_files_driver.php 117
ERROR - 2016-11-29 22:36:29 --> Severity: error --> Exception: Session: Configured save path '' is not a directory, doesn't exist or cannot be created. Session_files_driver.php 119

My session save path at config.php was set to null:

$config['sess_save_path'] = NULL;

Once I got it changed to default (alternatively you can set it up to any other folder you want locally, so you can have full control over your sessions):

$config['sess_save_path'] = sys_get_temp_dir();

everything started working again! Now this is second time I have similar quiet errors with CodeIgniter where it reports absolutely nothing – instead it just gives white screen and this can be really frustrating to debug.

 


	
			
		

Validating URL in PHP without regular expressions

Validating many things in PHP is often done using regular expressions, but since those might be complicated to understand, versions of PHP later than 5.20 have new validating mechanism built in. It’s done using filter_var function. Here are some basic examples of both old (regular expression) and new (filter_var) validation functions:

Validating URL using regular expressions:

function isValidURL($url) {
	return preg_match('|^http(s)?://[a-z0-9-]+(.[a-z0-9-]+)*(:[0-9]+)?(/.*)?$|i', $url);
}

Validating URL using filter_var:

function isValidURL($url) {
	if (filter_var($url, FILTER_VALIDATE_URL, FILTER_FLAG_HOST_REQUIRED)) return true;
	else return false;
}

You will notice FILTER_VALIDATE_URL and FILTER_FLAG_HOST_REQUIRED flags in filter_var function. There are many more and here are some more real world examples

var_dump((bool) filter_var('http://www.website.com', FILTER_VALIDATE_URL, FILTER_FLAG_HOST_REQUIRED));
var_dump((bool) filter_var('http://website.com', FILTER_VALIDATE_URL, FILTER_FLAG_HOST_REQUIRED));
var_dump((bool) filter_var('www.website.com', FILTER_VALIDATE_URL, FILTER_FLAG_HOST_REQUIRED));
var_dump((bool) filter_var('website.com', FILTER_VALIDATE_URL, FILTER_FLAG_HOST_REQUIRED));

Output:

bool(true)
bool(true)
bool(true)
bool(false)

Here are some most common URL validation flags explanation

  • FILTER_FLAG_SCHEME_REQUIRED – Require the scheme (eg, http://, ftp:// etc) within the URL.
  • FILTER_FLAG_HOST_REQUIRED – Require host of the URL (eg, www.google.com)
  • FILTER_FLAG_PATH_REQUIRED – Require a path after the host of the URL. ( eg, /folder/file.ext)
  • FILTER_FLAG_QUERY_REQUIRED – Require a query at the end of the URL (eg, ?key=value)

Validating Email using regular expressions:

function isValidEmail($email) {
    return preg_match("^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$^", $email);
}

Validating Email using filter_var:

function isValidEmail($email) {
	if (filter_var($email, FILTER_VALIDATE_EMAIL)) return true;
	else return false;
}

Conclusion
Validating URLs, Emails, IPs and many other things using regular expressions is history now. Code for validations using filter_var is easier to understand easier to write and looks more php geeky. You don’t have to search for “working” regular expressions for your validations… It’s all there and it’s well documented and all you need is just need to use it.
Continue Reading

Tags: url validation in javascript without http, php validate url

Web host is adding ?PHPSESSID to the end of all URLs

Today I a customer of mine came to me with a problem with web site that he have just moved from one host to another. And on that new host all his links on his site suddenly had ?PHPSESSID=k234j2knk… in the end. Since that is totally unusable since that site doesn’t even uses sessions and could affect his search engine rankings, he wanted it out of the way. The new host he moved that site to was shared and it doesn’t allow editing of php.ini any way so I had to make some other solution.

After failing with adding one of those on top of php files, as suggested on most pages that I found on Google:

// stop PHP from automatically embedding PHPSESSID on local URLs
ini_set('session.use_trans_sid', false);

// only use cookies (no url based sessions)
ini_set('session.use_only_cookies', true);

I suggested him to just switch hosts again, but he said he already paid up front for the whole year… and that it’s not an option… and that he wants that off his site… So, I’ve Googled some more and finally found a simple solution (that doesn’t require editing of 100’s of files in his case because of poorly programmed site). All I had to do is just put one line of code in .htaccess file and BOOM! All those nasty ?PHPSESSID were gone!

php_flag session.use_trans_sid off