Tag Archives: mysql

Amazon RDS Tunnel (Access from your desktop)

If you need to access an Amazon RDS instance locally you can:

  1. Add your IP on the RDS Security Group
  2. Create a tunnel to the RDS

Best chances are you have a dynamic IP address, so you already know that option 1. is a real pain.

You can create a tunnel (2.) by performing the following command on a new terminal window:

1
ssh -l <username> -L 33060:<ec2_rfc_ip>:3306 -N <ext_hostname>

As long as you keep this window open, you’ll be able to access the RDS by connecting to localhost on port 33060:

MySQL delete duplicate lines (MyISAM and InnoDB)

Sometimes this task can be a real pain in the ass.. Heres a quick & dirty way to remove.

Let’s say you have the following table:

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| col_a | varchar(10)      | YES  |     | NULL    |                |
| col_b | varchar(10)      | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

With the following data:

+----+-------+-------+
| id | col_a | col_b |
+----+-------+-------+
|  1 | YY    | ZZ    |
|  2 | YY    | XX    |
|  3 | YY    | ZZ    |
|  4 | YY    | XX    |
|  5 | XX    | XX    |
+----+-------+-------+

Note: On this example I’ll assume a unique row is identified by the pair “col_a” + “col_b”.

If you want to delete duplicate rows and you don’t care for the PRIMARY KEY or which of the rows gets selected, you can add a UNIQUE INDEX.

For MyISAM engine tables

1
ALTER IGNORE TABLE `example_table` ADD UNIQUE INDEX `idx_uq` (`col_a`, `col_b`);

Doing this will automatically purge the duplicate rows instead of shouting an error because we’re using IGNORE.

Et voila:

+----+-------+-------+
| id | col_a | col_b |
+----+-------+-------+
|  1 | YY    | ZZ    |
|  2 | YY    | XX    |
|  5 | XX    | XX    |
+----+-------+-------+

Important Note on InnoDB engine tables

If your under MySQL 5.5/InnoDB this will not work due to a known “InnoDB index limitation“.

Luckily, someone at stackoverflow.com came up with a simple solution:

1
2
SET SESSION old_alter_table=1;
ALTER IGNORE TABLE `example_table` ADD UNIQUE INDEX `idx_uq` (`col_a`, `col_b`);

The missing php function: mysql_fetch_all

The function:

1
2
3
4
5
6
7
8
function mysql_fetch_all($query, $kind = 'assoc')
{
    $result = array();
    $kind = $kind === 'assoc' ? $kind : 'row';
    eval('while(@$r = mysql_fetch_' . $kind . '($query)) array_push($result, $r);');
    return $result;

}

Usage:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
$query = "SELECT * FROM my_table;"
$res = mysql_query($query, $mysqll);
$res_array = mysql_fetch_all($res);

print_r($res_array);

/*
Output:
Array
(
    [0] => Array
        (
            [col_A] => val_A1
            [col_b] => val_B1
        )
    [1] => Array
        (
            [col_A] => val_A2
            [col_b] => val_B2
        )
* /

Criar schema.yml a partir do workbench

Para quem utiliza a ferramenta Workbench para desenhar os modelos da base de dados, e depois não quer ter o trabalho de passar os nomes todos para o schema.yml, pode tirar partido desta script.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
<?php
/*
 *  The MIT License
 *
 *  Copyright (c) 2010 Johannes Mueller <circus2(at)web.de>
 *
 *  Permission is hereby granted, free of charge, to any person obtaining a copy
 *  of this software and associated documentation files (the "Software"), to deal
 *  in the Software without restriction, including without limitation the rights
 *  to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 *  copies of the Software, and to permit persons to whom the Software is
 *  furnished to do so, subject to the following conditions:
 *
 *  The above copyright notice and this permission notice shall be included in
 *  all copies or substantial portions of the Software.
 *
 *  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 *  IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 *  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 *  AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 *  LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 *  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 *  THE SOFTWARE.
 */


// show errors
error_reporting(E_ALL);

// lets stop the time
$start = microtime(true);


// enable autoloading of classes
require_once('../lib/MwbExporter/Core/SplClassLoader.php');
$classLoader = new SplClassLoader();
$classLoader->setIncludePath('../lib');
$classLoader->register();

// show a simple text box with the output
echo '<textarea cols="100" rows="50">';

    // configure your output
    $setup = array(
        'extendTableNameWithSchemaName' => true
    );

    // create a formatter
    $formatter = new \MwbExporter\Formatter\Doctrine1\Yaml\Loader($setup);
   
    // parse the mwb file
    $mwb = new \MwbExporter\Core\Workbench\Document('data/er.mwb', $formatter);
   
    // show the export output of the mwb file
    echo $mwb->display();
 
echo "</textarea>";

// save as zip file in current directory and use .yml as file endings
echo "<br><br>";
echo $mwb->zipExport(__DIR__, 'yml');

// show some information about used memory
echo "<br><br>";
echo (memory_get_peak_usage(true) / 1024 / 1024) . " MB used";
echo "<br>";

// show the time needed to parse the mwb file
$end = microtime(true);
echo  sprintf('%0.3f', $end-$start) . " sec needed";

Atenção: Não dispensa verificação do código gerado!!!

Links úteis:
Workbench: http://www.mysql.com/downloads/workbench/
Repositório do projecto: git://github.com/johmue/mysql-workbench-schema-exporter.git

Obrigado ao johmue. :P

Doctrine use same collation on all tables (even plugins)

It really annoys me when I set my schema collate to utf8_general_ci but plugin tables are created as latin1_swedish_ci.

Solution?

Create the following method on your ProjectConfiguration.class.php

1
2
3
4
5
6
7
8
9
class ProjectConfiguration extends sfProjectConfiguration
{
    public function configureDoctrine(Doctrine_Manager $manager)
    {
        $manager->setCollate('utf8_general_ci');
        $manager->setCharset('utf8');

    }
}

Props to: http://www.prettyscripts.com/framework/symfony/symfony-and-doctrine-default-table-collation-a-better-solution