Small heaps of code

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.


====== Setup ======
===== Installing =====
<code>
  sudo aptitude install mysql-server
</code>

===== Configuration =====
<code>
sudo vi /etc/mysql/my.cnf   
sudo /etc/init.d/mysql restart
</code>
==== Remote Access ====
If you want to access your database remotely you need to do some configuring. 
  * First tell mysql to bind to an external ip address (the default is to bind to 127.0.0.1). Edit **my.cnf** and find the line
<code>
bind-address = 127.0.0.1
</code>
change that to the IP-address you want mysql to bind to.

  * Then you need to tell mysql which users are granted remote access. This can be done via the "Privileges" Page in phpmyadmin (if installed) or via the console using something like
<code sql>
GRANT ALL ON *.* TO user@'10.101.10.128' IDENTIFIED BY 'my_secret_password';
</code>
  * Some Notes
  * Remember that its not always very wise to GRANT ALL. Instead you can grant USAGE or any subset of SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,...
  * *.* grants the rights to all Database Objects in the System. To grant rights only to a specific DB you can use something like **GRANT ALL ON Tolldata.* TO  ...**
  * The //IDENTIFIED BY 'password' // clause is optional. If you choose to remove it, remember that you **must not** provide a password when you try to log into the database, or else the login will fail!
  * see http://dev.mysql.com/doc/refman/5.0/en/adding-users.html for more information on user privileges

===== User Management =====
To create a user
<code sql>
CONNECT db_name ;
CREATE USER 'gosmart'@'localhost' IDENTIFIED BY 'gosmart';
GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'gosmart'@'localhost';
</code>

see http://dev.mysql.com/doc/refman/5.0/en/adding-users.html for more information.


====== SQL ======
===== Working with Dates =====
==== Resources ====
  * http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_addtime
  * http://www.weberdev.com/ViewArticle/Date-Arithmetic-With-MySQL

==== Calculate Time Interval from Date ====
<code sql>
SELECT timestamp, FLOOR(HOUR(timestamp)*4 + MINUTE(timestamp)/15) FROM lane_measurements; 
</code>
==== Add Time Period to a Date ====
<code sql>
SELECT timestamp, DATE_ADD(DATE(timestamp), INTERVAL 30 MINUTE ) FROM lane_measurements;
</code>
===== Export to CSV File =====
<code sql>
SELECT * 
INTO OUTFILE "/home/hkoller/prototype_complete.csv" 
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
FROM `prototype_complete` 
ORDER BY clusterid,roadid,laneid,`interval` asc;
</code>


If you require headers in your CSV, you have to use a workaround (see [[http://dev.mysql.com/doc/refman/4.1/en/select.html here]]):
<code sql>
SELECT 'company','contact_name' -- writing the header
UNION 
SELECT company , contact_name 
INTO OUTFILE '/tmp/test.csv' FIELDS TERMINATED BY ','  
FROM answers;
</code>

===== Import from CSV File =====
<code sql>
LOAD DATA LOCAL INFILE 'road_frc.csv'
INTO TABLE road_2_frc
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(roadid,frc);
</code>
====== Hints ======

===== Table Types =====
MySQL supports different table types with individual strengths and weaknesses. The default table type  is //MyISAM// which is fast but lacks some essential DB features. In order to get a more complete featureset (Foreign Keys, Transactions,..) use InnoDB. eg.
<code sql>
CREATE TABLE test (
    id INTEGER PRIMARY KEY NOT NULL,
    name VARCHAR(20)
) **TYPE InnoDB**;
</code>

see http://www.devshed.com/c/a/MySQL/Storage-Engine-Table-Types/ for a detailed discussion of the table types


===== GROUP BY weirdness =====
It seems that MySQL allows SELECT statements with a GROUP BY clause to contain //normal fields// (fields which are not part of an aggregate function or the grouping). Here is an example query to illustrate the problem: 
<code sql>
  mysql> SELECT   species
    -> ,        MIN(birth)  -- birthdate of oldest pet per species
    -> ,        MAX(birth)  -- birthdate of youngest pet per species
    -> ,        birth       -- birthdate of ... uh oh...!
    -> FROM     pet_table
    -> GROUP BY species;
</code>

The result for column "birth" in the above example is a more or less random value. Modern MySQL versions provide the config parameter 

sql_mode=ONLY_FULL_GROUP_BY
(put in /etc/mysql/my.cnf)  

to disable this behavior (and have MySQL reject such queries like any sane DBMS should).

See http://rpbouman.blogspot.com/2007/05/debunking-group-by-myths.html  (the Section about "Running into trouble with GROUP BY" ) for details.

====== Backup ======

===== creating a dumpfile =====
<code bash>
 mysqldump databasename > dumpfile.dump
</code>
===== restoring the database =====

make sure that the database exists before the restauration or recreate it.
<code bash>
 mysql databasename < dumpfile.dump
</code>
Small heaps of code

User Tools

Site Tools

Page Tools
