Tag: mysql

Getting started with WAMP: setting up

Memory exhausted problem

“Allowed memory size of 134217728 bytes exhausted”
I encountered this problem a few times, and setting memory_limit to a higher value didn’t seem to work

In php.ini:
memory_limit = -1;

Changing www root on WAMP

Open httpd.conf

Change

Listen 0.0.0.0:80
Listen [::0]:80

to

Listen 127.0.0.1:80
Listen [::1]:80

Change

DocumentRoot "C:/wamp/www/"

to

DocumentRoot "C:/Users/name/Sites/localhostwamp/"

Change

</pre>
<Directory "C:/wamp/www/">
<pre>

to

</pre>
<Directory "C:/Users/.../Sites/localhostwamp/">
<pre>

Setting mySQL password

go to shell (right click mysql shell in taskbar)

update mysql.user set Password = password('yourPasword') where User = 'root';
flush privileges;

go to phpmyadmin directory c:\…\wamp2.5\apps\phpmyadmin4.1.14\config.inc.php

replace:

$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = '';

with

$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['user'] = '';
$cfg['Servers'][$i]['password'] = '';

Or watch this dummy video tutorial:

Set login session to something less restrictive for testing purposes

in your config.inc.php file (apps/phpmyadmin…)

$cfg['LoginCookieValidity'] = 604800; /* 1 week */
$cfg['MaxNavigationItems'] = 1000;

mysql error logging

add these lines to my.ini

log-queries-not-using-indexes
log-warnings
long_query_time = 2
log=C:/Webservers/wamp2/logs/querylog.log
log-slow-queries=C:/Webservers/wamp2/logs/SlowQueryLog.log

import sql-data into mySQL database

Right click Wamp icon in taskbar > mySQL

show databases;
use <databasename>;
source c:\Temp\queries.sql;

mysqldump in wamp (not via mysql console!)

Go to the wamp mysql directory via cmd prompt

cd c:\Webservers\wamp2\bin\mysql\mysql5.6.17\bin\

Now ‘all’ mysql commands are available:

mysqldump -u root -p dbname table1 table2 > output.sql

[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

 

How to import large sql files in XAMPP?

XAMPP is a very easy to use local webserver. Can be slow sometimes, but hey! it’s for testing purposes.

Ever tried to import a large SQL file? Doing so with phpMyAdmin will cause you headaches when tweaking php.ini to accept large data sets: memory_limit, post_max_size, etc…

Ever considered using the shell option? Once you have started your Apache and mySql module in the control panel, click “Shell”.

A command prompt will open and all you need to do is use this command line:

mysql -u root -p <DB_NAME> < <location of your large sql file

Example:

mysql -u root -p test < "d:\sql exports\file.sql"

Use -v to output the import process to screen

mysql -u root -p -v test < "d:\sql exports\file.sql"

Alternative method (no quotations in source command location!)

mysql -u root -p;
use <db-name>;
source C:\Temp\filename.sql;

SQL indexes

Disable indexes before you start importing, re-enable them when completed.

ALTER TABLE `table_name` DISABLE KEYS;

ALTER TABLE `table_name` ENABLE KEYS;

Further reading: https://support.tigertech.net/mysql-large-inserts

ERROR 2006 (HY000) at line 9999: MySQL server has gone away

It’s possible that this error occurs in XAMPP. It’s easily resolved by changing

  • my.ini at “\xampp\mysql\bin\”.
  • change “max_allowed_packet” to a higher value than the default 1M. E.g. 10 MB
  • restart mysql service

Null waarde als (deel van) primary key in MYSQL

Lijkt evident, maar is het niet: als je een samengestelde ‘primary key’ definieert in mySQL waarbij een van de delen van de primary key NULL-waarden kan bevatten en identieke rijen wil uitsluiten, kom je bedrogen uit.

Neem bijvooorbeeld volgende tabel waarbij je twee identieke rijen wil uitsluiten met behulp van de samengestelde primary key ‘user, e-mail, label’:

user email label
1 test@test.be 2
2 test2@test.be NULL
2 test2@test.be NULL

Rij 2 en 3 zijn voor jou als gebruiker identiek op het eerste zicht en zouden elkaar dus logisch uitsluiten. De database zou de derde rij niet mogen toelaten indien een samengestelde primary key is opgesteld over ‘user, email en label’, maar aangezien voor MySQL NULL waarden nooit identiek zijn, is dergelijk situatie perfect mogelijk in MySQL en moet je dus op zoek gaan naar een andere oplossing!