[SOLVED] XAMPP Row size too large, innoDB sql import

Ran into some problems recently with XAMPP. Exported a large mySQL production database and tried to import it locally in XAMPP (through Shell access).

Error: ERROR 1118 <42000> at line 437919: Row size too large <> 8126>. Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

First I tried changing table to ROW_FORMAT=COMPRESSED doing so:

ALTER TABLE <tablename>
    ENGINE=InnoDB
    ROW_FORMAT=COMPRESSED
    KEY_BLOCK_SIZE=8;

No success with that. Then I changed innodb_buffer_pool_size to 32MB (default setting was 8 or 16) in my.ini (mySQL config file in XAMPP)

xampp control panel

# Comment the following if you are using InnoDB tables
#skip-innodb
innodb_data_home_dir = "/Sites/xampp-portable-win32-1.8.3-5-VC11/mysql/data"
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = "/Sites/xampp-portable-win32-1.8.3-5-VC11/mysql/data"
#innodb_log_arch_dir = "/Sites/xampp-portable-win32-1.8.3-5-VC11/mysql/data"
## You can set .._buffer_pool_size up to 50 - 80 %
## of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 32M
innodb_additional_mem_pool_size = 2M
## Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 10M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_file_per_table = 1
innodb_file_format = Barracuda

Doing so made it possible to import a 1GB sql table without any problem.

The above applies to XAMPP portable version xampp-portable-win32-5.5.19-0-VC11.zip

 

PHP UTF8 test-template (jQuery, Bootstrap)

This is just a webdevelopment template for PHP (all errors, xdebug), HTML, jQuery, Bootstrap (UTF8 encoding).

<?php
ini_set('display_errors' , 'On');
error_reporting(E_ALL);
session_start();
header("Content-Type:text/html;charset=utf-8");
ini_set('xdebug.var_display_max_depth', -1);
ini_set('xdebug.var_display_max_children', -1);
ini_set('xdebug.var_display_max_data', -1);
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>...</title>
<script src="//code.jquery.com/jquery-1.11.2.min.js"></script>
<script src="//code.jquery.com/jquery-migrate-1.2.1.min.js"></script>
<link href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap.min.css" rel="stylesheet">
<script src="//maxcdn.bootstrapcdn.com/bootstrap/3.3.4/js/bootstrap.min.js"></script>
</script>

</head>
<body>

</body>
</html>

Password protect a subsite in a WordPress network with .htaccess

A wordpress network / multisite setup uses virtual directories.

When you have a wordpress network setup on www.yourdomain.com, you can have multiple blogs with only one WordPress installation, for example:

www.yourdomain.com
www.yourdomain.com/johndoe
www.yourdomain.com/janedoe

Suppose you want to password protect only one subsite e.g. ‘janedoe’, you can’t just create a ‘janedoe’ folder on your webserver with a particular .htaccess in it.

You need to create a secured environment for the virtual folder in your main .htaccess (where all other WordPress rules reside) using the following code:

SetEnvIfNoCase Request_URI "^/janedoe/" SECURED
AuthType Basic
AuthName "restricted area"
AuthUserFile /home/mydomain/.htpasswd
require valid-user
Satisfy any
Order allow,deny
Allow from all
Deny from env=SECURED

Enable Apache Error Logging for Mod_Rewrite in Xampp

Mod_Rewrite enables you to do fancy stuff with URLs, but it isn’t always transparent when it comes to debugging. In a development environment it can be useful to enable apache error logging to see where a mod_rewrite line fails.

In XAMPP you can do this by opening httpd.conf (open control panel > config > Apache (httpd.conf)) and append mod_rewrite.c:trace3 to LogLevel.

LogLevel warn mod_rewrite.c:trace3

CSS use EM as a relative font-size, instead of PX

It goes back to Internet Explorer 3.0, but EMs are still the better way to define font-sizes. It takes a small learning curve to get the hang of it, but when it comes to quick changes in overall font-size, this is the way to go. Default font-size for most common browsers is 16 pixels. There are only three articles you need to read to tame that size with relative ems.

1em = 12pt = 16px = 100%

Useful reads

The EM formula

Child pixels / Parent pixels = Size in EM

Not good at calculating? Use the EM calculator

Still targeting Internet Explorer 6?

While Internet Explorer handles EM, it does so only when the base font is set to a percentage!

body { font-size: 62.5%; }
body wrapper { font-size: 1em; }

PHP long running script without Ajax

<?php
header("Content-Type:text/html;charset=utf-8");
session_start();
?>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type"  content="text/html; charset=utf-8" />
</head>
<body>
<?php

$executiontime = 5; // max time for scripting
$sleeptime = 3; // seconds to pauze after execution time limit


if(!isset($_GET["continue"])){

	$_SESSION["numbers"] = range(1,100000);
	$_SESSION["genesis"] = microtime(true); // absolute start time
	echo count($_SESSION["numbers"]);

}

$_SESSION["starttime"] = microtime(true); // 'this round' start time

foreach($_SESSION["numbers"] as $key=>$value){

		sleep(1);
		// abort and 'reload' upon exceeding execution time
		if((microtime(true) - $_SESSION["starttime"]) > $executiontime){
			echo "<p>" . count($_SESSION["numbers"]) . " items left";			
			echo "<br>This round: " . round((microtime(true) - $_SESSION["starttime"]),2) . " seconds";		
			echo "<br>Total time: " . round((microtime(true) - $_SESSION["genesis"])/60,2) . " minutes";
			echo "<p><strong>Pause script...</strong> Resume in $sleeptime seconds...</p>";
			die ('<meta http-equiv="refresh" content="'.$sleeptime.';URL='.$_SERVER['SCRIPT_URI'].'?continue=1" /></body></html>');
		}
		
		// do some stuff while execution time is still ok
	
		unset($_SESSION["numbers"][$key]);
}

echo "<br>Completed...: total time " . round((microtime(true) - $_SESSION["genesis"])/60,2) . " minutes";

Posting HTML forms with special characters, while keeping your database clean.

The best practice when storing data in a database is to store it in its most purest form.
When allowing users to edit data through HTML webpages you need to encode some characters so your HTML-forms won’t break. You can do this by using htmlspecialchars (or htmlentities). Below is an example with htmlspecialchars where only the double quotes are escaped (ENT_COMPAT flag).

Mind the accept-charset in the form value: I try to work with UTF-8 and UTF-8 only. (see your collation in mySQL is also set to UTF-8!)

You can run this on code on localhost (e.g. XAMPP)

<?php
header("Cache-Control: no-cache, must-revalidate"); // HTTP/1.1
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT"); // Date in the past
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>htmlspecialchars (utf-8 encoding)</title>
</head>
<body>
<h1>htmlspecialchars (utf-8 encoding)</h1>
<h2>Before form post</h2>
<?php
$dbvalue = "String: ? < > ' - \" `´& % ‰ € ® 2011";
$formvalue = htmlspecialchars($dbvalue, ENT_COMPAT,"UTF-8");
?>
<p><strong>String</strong> is a value coming from a database record in its cleanest form: <span style="color:green;"><?php echo htmlspecialchars($dbvalue); ?></span> </p>
<p>For use in a text form, especially the double quotes, must be encoded so the <em>value=&quot;&quot;</em> doesn't break. We use <strong>htmlspecialchars</strong> (ENT_COMPAT) function. ENT COMPAT only forces double quotes to be changed into &amp;quot; (besides < > ? &)</p>
<form action="<?php $_SERVER['PHP_SELF']; ?>" method="post" accept-charset="UTF-8">
	<label>String:
	<input name="string" type="text" value="<?php echo $formvalue; ?>" size="50" /></label>
	<br />
	The value of title inside this form looks like <span style="color:red;"><?php echo htmlspecialchars($formvalue); ?></span><br />
	<input name="submit" type="submit" value="submit this form" />
</form>
<?php if($_POST){ ?>
<h2>Yes, the form was posted</h2>
<p>When the form is <strong>submitted</strong>, the <strong>string</strong> field will again have a value in its purest form (no &amp;quot; values but &quot;) (not the htmlspecialchars formatting)</p>
<p><strong>String</strong> has submitted value: <span style="color:green;">
	<?php echo $_POST['string']; ?>
	</span></p>
<?php } ?>
</body>
</html>

XHTML: some good practices

Some good practices on XHTML

  • <doctype> always use a doctype before <html>-tag: don’t get in quirksmode –recommended list of doctypes (W3 schools)
    • Transitional is most common
    • Strict: prohibits the use of target=”_blank” and a few other HTML tags, like s, center, strike, u, applet, iframe, font, isindex, dir, basefont
  • <h1> Only one h1 per page: use for the main topic
  • <blockquote> Only to refer to bits of text coming from other locations
  • <q> Use for a single ‘quote’
  • <table> Never use for lay-out purposes
  • <cite> References to books, websites, articles
  • <address> identify contact information to the author of a page

Other usefull tips

Disallow compatibility mode

Tell Internet Explorer never to go into compatibilty mode (use an older IE engine than the one installed). Put this after the <title>-tag:


Linking style sheets

Use <link> for simplicity and better performance


Validate your code

http://validator.w3.org

Forget about HTML attributes like

  • vspace
  • hspace
  • bgcolor
  • text
  • link
  • alink
  • vlink
  • img align: use CSS-floats instead

Low level format browser specific attributes like

  • leftmargin
  • topmargin
  • marginwidth
  • marginheight