Category Archives: MySQL

PrestaShop – Setup TEST site in sub-domain

Clone production DB to a new database and run following query in new DB


UPDATE `ps_shop` SET `name`=concat('TEST ',`name`);
UPDATE `ps_shop_url` SET `domain`=concat('test.',`domain`),`domain_ssl`=concat('test.',`domain_ssl`);
UPDATE `ps_configuration` SET `value` = concat('test.',`value`) WHERE `name` like 'PS_SHOP_DOMAIN%';
UPDATE `ps_configuration` SET `value` = '0' WHERE `name` = 'PS_SHOP_ENABLE';
UPDATE `ps_configuration` SET  `value`=concat('TEST ',`value`) WHERE `name` = 'PS_SHOP_NAME';
UPDATE `ps_configuration` SET value = '2' where `name` = 'PS_SMARTY_FORCE_COMPILE';
UPDATE `ps_configuration` SET value = '0' where `name` = 'PS_SMARTY_CACHE';

You can run following query to avoid sending mails to customer for any test:


UPDATE `ps_customer` SET `email` = CONCAT(REPLACE(REPLACE(`email` ,'@','-'),'.','_'),'@mydomain.com');

 

MySQL on Mac OSX change sql_mode in LaunchDaemons

After installing MySQL for Mac OSX a launch daemon file (/Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist) is created. Adding lines mentioned in Orange will force mysql daemon to use custom option.

<?xml version=”1.0″ encoding=”UTF-8″?>
<!DOCTYPE plist PUBLIC “-//Apple Computer//DTD PLIST 1.0//EN” “http://www.apple.com/DTDs/PropertyList-1.0.dtd”>
<plist version=”1.0″>
<dict>
<key>Label</key>             <string>com.oracle.oss.mysql.mysqld</string>
<key>ProcessType</key>       <string>Interactive</string>
<key>Disabled</key>          <false/>
<key>RunAtLoad</key>         <true/>
<key>KeepAlive</key>         <true/>
<key>SessionCreate</key>     <true/>
<key>LaunchOnlyOnce</key>    <false/>
<key>UserName</key>          <string>_mysql</string>
<key>GroupName</key>         <string>_mysql</string>
<key>ExitTimeOut</key>       <integer>600</integer>
<key>Program</key>           <string>/usr/local/mysql/bin/mysqld</string>
<key>ProgramArguments</key>
<array>
<string>/usr/local/mysql/bin/mysqld</string>
<string>–user=_mysql</string>
<string>–sql_mode=NO_ENGINE_SUBSTITUTION</string>
            <string>–open_files_limit=100000</string>
            <string>–wait_timeout=600000</string>
            <string>–max_allowed_packet=16M</string>
<string>–basedir=/usr/local/mysql</string>
<string>–datadir=/usr/local/mysql/data</string>
<string>–plugin-dir=/usr/local/mysql/lib/plugin</string>
<string>–log-error=/usr/local/mysql/data/mysqld.local.err</string>
<string>–pid-file=/usr/local/mysql/data/mysqld.local.pid</string>
</array>
<key>WorkingDirectory</key>  <string>/usr/local/mysql</string>
</dict>
</plist>

MySQL start manually with diff user and data

If you have recently update mysql database and forgot to make a backup of old database then use following technique.

  1. Download TAR version my MySQL matching the old version
  2. untar the MySQL program
  3. stop new version of mysql server
  4. start old version of mysql using “mysqld –user=mysql –console –datadir=/usr/local/mysql-5.6.25-osx10.8-x86_64/data
  5. Now old version server is up so you can continue export databases
  6. stop mysqld
  7. start new MySQl server
  8. Restore database using newly backed up files.

centos: Another MySQL daemon already running with the same unix socket

To prevent the problem from occurring, you must perform a graceful shutdown of the server from the command line rather than powering off the server.

# shutdown -h now

This will stop the running services before powering down the machine.

Based on Centos, an additional method for getting it back up again when you run into this problem is to move mysql.sock:

# mv /var/lib/mysql/mysql.sock /var/lib/mysql/mysql.sock.bak

# service mysqld start

Restarting the service creates a new entry called mqsql.sock