Move MYSQL database from one PC to another (Windows 7)

Step 1: Find where the mySQL data exist. Look for the my.ini file and inside it find for the datadir path. In Windows 7 you must check the directory C:\ProgramData\MySQL\MySQL Server 5.5\data) 

Step 2: Copy all the folders and ib_data files in order to transfer to your new mySQL installation. DO NOT COPY the IB_LOGFILEx files.

Step 3: Go to target computer, stop the mySQL Service 

Step 4:  In the target computer find the location of the mySQL data. Backup the directory of the mySQL Data and then paste the data of the Step1.

Step 5: Restart mySQL Service

MySQL - How to create a calendar table

1. Create the table
CREATE TABLE `calendar` (
`cdate` date NOT NULL DEFAULT '2000-01-01',
`cday` int(2) unsigned NOT NULL DEFAULT '1',
`cmonth` int(2) unsigned NOT NULL DEFAULT '1',
`cyear` int(4) unsigned NOT NULL DEFAULT '2000',
PRIMARY KEY (`cdate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


2. Create an auxiliary table ints (0-9)
CREATE TABLE ints (i INTEGER);
INSERT INTO ints VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

3. Fill your calendar with dates from 01-01-2005 until 31-12-2030

INSERT INTO calendar (cdate, cday, cmonth, cyear)
SELECT cal.date as cdate, DAY(cal.date) as cday, MONTH(cal.date) as cmonth, YEAR(cal.date) as cyear
FROM (
SELECT '2005-01-01' + INTERVAL d.i*1000 + c.i* 100 + a.i * 10 + b.i DAY as date
FROM ints a JOIN ints b JOIN ints c JOIN ints d
ORDER BY d.i*1000 + c.i*100 + a.i*10 + b.i) cal
WHERE cal.date BETWEEN '2005-01-01' AND '2030-12-31'

Note: The above fourth join generates up to 10000 dates. In case you need more dates, add extra joints of the table 'ints'

INSERT INTO WHERE NOT EXISTS

If you want to insert values into a MySQL table, but only if those values don’t already exist, and you don’t want to use a primary key on the table (and deal with the resulting error suppression ), here’s an elegant one-query method of doing so.

INSERT INTO [table name] SELECT '[value1]', '[value2]' FROM DUAL
WHERE NOT EXISTS(
SELECT [column1] FROM [same table name]
WHERE [column1]='[value1]'
AND [column2]='[value2]' LIMIT 1)

OK, technically it’s not a single query.
But it is only one round trip to the MySQL server.

Insert "zero" values in AUTONUMBER field in mySQL

Στη mySQL όταν θέλει κανείς να εισάγει ένα dump script και έχει πίνακες με τιμές 0 σε πεδία που είναι auto_number, τότε διαπιστώνει κανείς ότι δεν δέχεται την τιμή 0,
βάζει αυτόματα - κατά την εκτέλεση του dump - αντί για 0 το 1 και στη συνέχεια χτυπούν τα PKs.

Αυτό που μπορεί να κάνει κανείς είναι να «πειράξει» το sql-mode της mySQL.

Από το αρχείο my.ini (windows) ή my.cnf (linux) προσθέτοντας
στην παράμετρο sql-mode, την τιμή NO_AUTO_VALUE_ON_ZERO ( η τιμή της μεταβλητής είναι μια λίστα από παραμέτρους
της mySQL που η παρουσία τους τις ενεργοποιεί)
όπως φαίνεται παρακάτω:

sql-mode="...,NO_AUTO_VALUE_ON_ZERO"

Κάνεις restart την mySQL, τρέχεις το dump και εφόσον όλα είναι ΟΚ μπορείς να επαναφέρεις την παράμετρο sql-mode
στην αρχική της μορφή.

mySQL - UTF-8 for JSP and Tomcat

How to display UTF-8 characters in Tomcat using MySQL as the data source!

1. Make sure your MySQL database is using the UTF-8 character set.

2.Tell the JDBC connector that it has to talk to the database using UTF-8.
To do this add useUnicode=true&characterEncoding=UTF-8 to the connection URL in the JDBC configuration. Your connection URL will look something like this: jdbc:mysql://localhost:3306/mydatabase?useUnicode=true&characterEncoding=UTF-8.

3. Make sure the browser knows that what it's receiving is actually UTF-8.
In your JSP files add <%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>at the top.
In your Servlets, make sure the right HTTP headers are sent back to the client by adding the line response.setContentType("text/html;charset=UTF-8");.
Of course, you'll have to use another MIME type than text/html if you're not going to display HTML.

4. Tell Java that you're using UTF-8 by configuring the Java options.
Add the parameter -Dfile.encoding=UTF-8 to your Java options, either in the catalina.bat file or by clicking on the Java tab in the Monitor Tomcat program.

5. if you're using Struts to handle web forms - is to make sure all input from the client is converted to UTF-8.
This is done with a little bit of coding and a configuration change

http://www.vegard.net/archives/bg000815.php

mySQL: UPSERT (INSERT OR UPDATE) between 2 tables

In order to INSERT new rows or UPDATE if the rows exist you can use the following SQL:

Step 1:
INSERT INTO PRODUCT_T (ID, NAME, DESCR)
SELECT T2.ID, T2.NAME, T2.DESCR FROM
(IMP_PRODUCT_T T2 LEFT OUTER JOIN PRODUCT_T T1 ON T2.ID = T1.ID)
WHERE T1.ID IS NULL;

Step 2:
UPDATE PRODUCT_T T1
INNER JOIN IMP_PRODUCT_T T2 ON T1.ID = T2.ID
SET T1.NAME = T2.NAME, T1.DESCR = T2.DESCR


Note: It has poor efficiency but it is standards compliant!!!

mySQL: Using AUTO_INCREMENT keys after INSERTING a row