Tagged: Mysql RSS

  • admin 7:42 pm on June 5, 2010 Permalink
    Tags: database normalization, Mysql   

    MySQL Normalization 

    Database normalization prevents redundancy, inconsistency and data loss. It includes several steps as follows:

    1. 1NF – First Normal Form states that all tuple values should be atomic. Atomic means that there should be only one useful piece of data for each attribute.

    The first step in database normalization is to determine the functional dependencies in the database. This means to find which values are determined by which.

    2. Decomposition and Boyce/Codd Normal Form

    BCNF has only one requirement – every functional dependency must be functionally determined by either a candidate key*, or a superset of a candidate key( superkey).

    *A candidate key is a set of attributes that must be unique for each tuple and irreducible into a smaller key.

    Example:

    candidate_key -> value

    candidate_key2 -> value2

    …

    5. 5th Normal Form requires that each join dependency is satisfied by superkeys.

    A join dependency(JD) is a set of projections on a relation which when joined reform the original relation.

     

  • admin 7:06 pm on May 23, 2010 Permalink
    Tags: csv, INTO OUTFILE, LOAD DATA, Mysql   

    Text Files and MySQL databases 

    If for some strange reason you have to import CSV file directly into MySQL here is a good query to go:

    mysql> LOAD DATA INFILE ‘file.csv’ INTO TABLE table_name FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\r\n’ (column1, column2);

    It gives you all the options such as how fields are terminated, enclosed and so on. The above example is specific for a csv file created with a Windows Text Editor. (windows… haha)

    Similarly, if you have to export a query into a text file here is how this can be done:

    mysql> SELECT column1, column2 INTO OUTFILE ‘file.csv’ FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘LINES TERMINATED BY ‘\r\n’ FROM table_name;

     

  • admin 6:08 pm on May 23, 2010 Permalink
    Tags: Mysql,   

    Advanced MySQL Priveleges 

    MySQL privileges assignment is a complex process which allows you to set different privileges for an user for different tables/databases. However, this process is very often neglected and users are granted with all privileges for a whole database.

    If improper privileges are applied this can lead to a website being hacked via MySQL. This means that the attacker executes a MySQL query which either illegally retrieves, updates or inserts information.

    Thus when you manually assign privileges make sure not to grant only the needed privileges to the corresponding tables. This is not always possible though since many popular web applications use just one user for everything.
    So here is the usual MySQL privileges granting:

    GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX ON database.* TO user@localhost IDENTIFIED BY ‘password’;

    This will grant all privileges to user@localhost. However, imagine that you can divide your script into parts with different functionality. For example, user_logs would be used to read just the logs from the ‘logs’ table. Then his privileges should be:

    GRANT SELECT ON database.logs TO user_logs@localhost IDENTIFIED BY ‘password’;

    This will make sure that even compromised user_logs will not be able to mess with the entire database.
    MySQL privileges assignment is a complex process which allows you to set different privileges for an user for different tables/databases. However, this process is very often neglected and users are granted with all privileges for a whole database.

    If improper privileges are applied this can lead to a website being hacked via MySQL. This means that the attacker executes a MySQL query which either illegally retrieves, updates or inserts information.

    Thus when you manually assign privileges make sure not to grant only the needed privileges to the corresponding tables. This is not always possible though since many popular web applications use just one user for everything.

    So here is the usual MySQL privileges granting:

    GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX ON database.* TO user@localhost IDENTIFIED BY ‘password’;

    This will grant all privileges to user@localhost. However, imagine that you can divide your script into parts with different functionality. For example, user_logs would be used to read just the logs from the ‘logs’ table. Then his privileges should be:

    GRANT SELECT ON database.logs TO user_logs@localhost IDENTIFIED BY ‘password’;

    This will make sure that even compromised user_logs will not be able to mess with the entire database.

     

  • admin 9:11 pm on November 14, 2009 Permalink
    Tags: /var/lib/mysql, Mysql, pid-file   

    Starting MySQL. ERROR! Manager of pid-file quit without updating file. 

    I got that nasty error after moving around different directories in /var/lib/mysql:

    root@server:/var/lib/mysql# /etc/init.d/mysql start

    Starting MySQL. ERROR! Manager of pid-file quit without updating file.

    There were all kind of suggestions about it on the net, most of which had something to do with incorrect shutdown or incorrect permissions for the pid file.

    One of the suggestions reminded me to read the mysql error log :) Here is what the error log said:

    root@server:/var/lib/mysql# /etc/init.d/mysql start
    Starting MySQL. ERROR! Manager of pid-file quit without updating file.
    root@server:/var/lib/mysql# mysql
    mysql                       mysql_config                mysqldumpslow               mysql_idle_processes.pm     mysqlshow                   mysql_tzinfo_to_sql
    mysqlaccess                 mysql_convert_table_format  mysql_explain_log           mysqlimport                 mysql_sock.pm               mysql_upgrade
    mysqladmin                  mysqld                      mysql_find_rows             mysql_install_db            mysql_tableinfo             mysql_upgrade_shell
    mysqlbinlog                 mysqld-debug                mysql_fix_extensions        mysql_logs.sh               mysqltest                   mysql_waitpid
    mysqlbug                    mysqld_multi                mysql_fix_privilege_tables  mysqlmanager                mysqltestmanager            mysql_zap
    mysqlcheck                  mysqld_safe                 mysqlhotcopy                mysql_secure_installation   mysqltestmanagerc
    mysql_client_test           mysqldump                   mysqlhotcopy.orig           mysql_setpermission         mysqltestmanager-pwgen
    root@server:/var/lib/mysql# mysqld
    mysqld         mysqld-debug   mysqld_multi   mysqld_safe    mysqldump      mysqldumpslow
    root@server:/var/lib/mysql# mysqld
    mysqld         mysqld-debug   mysqld_multi   mysqld_safe    mysqldump      mysqldumpslow
    root@server:/var/lib/mysql# mysqld_safe start
    Starting mysqld daemon with databases from /var/lib/mysql
    STOPPING server from pid file /var/lib/mysql/host.server.bgsnow.com.pid
    091114 13:14:56  mysqld ended
    root@server:/var/lib/mysql# ll /var/lib/mysql/host.server.bgsnow.com.p
    root@server:/var/lib/mysql# touch /var/lib/mysql/host.server.bgsnow.com.pid
    root@server:/var/lib/mysql# chown mysql: /var/lib/mysql/host.server.bgsnow.com.pid
    root@server:/var/lib/mysql# /etc/init.d/mysql start
    Starting MySQL. ERROR! Manager of pid-file quit without updating file.
    root@server:/var/lib/mysql# tail -f /var/lib/mysql
    mysql/    mysqlA/   mysqlOLD/
    root@server:/var/lib/mysql# tail -f /var/lib/mysql/mysql-slow.log
    host.server.bgsnow.com.err  ib_logfile0                mysql-slow.log             optistr2_optistreams/
    ibdata1                    ib_logfile1                optistr2_opti/             optistr2_perldesk/
    root@server:/var/lib/mysql# tail -f /var/lib/mysql/host.server.bgsnow.com.err
    InnoDB: Restoring possible half-written data pages from the doublewrite
    InnoDB: buffer…
    InnoDB: Doing recovery: scanned up to log sequence number 0 43655
    091114 13:16:20  InnoDB: Starting an apply batch of log records to the database…
    InnoDB: Progress in percents: 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 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
    InnoDB: Apply batch completed
    091114 13:16:21  InnoDB: Started; log sequence number 0 43655
    091114 13:16:21 [ERROR] Fatal error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist
    091114 13:16:21  mysqld ended
    091114 13:16:52  mysqld started
    InnoDB: Log scan progressed past the checkpoint lsn 0 36808
    091114 13:16:52  InnoDB: Database was not shut down normally!
    InnoDB: Starting crash recovery.
    InnoDB: Reading tablespace information from the .ibd files…
    InnoDB: Restoring possible half-written data pages from the doublewrite
    InnoDB: buffer…
    InnoDB: Doing recovery: scanned up to log sequence number 0 43655
    091114 13:16:52  InnoDB: Starting an apply batch of log records to the database…
    InnoDB: Progress in percents: 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 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
    InnoDB: Apply batch completed
    091114 13:16:53  InnoDB: Started; log sequence number 0 43655
    091114 13:16:53 [ERROR] Fatal error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist
    091114 13:16:53  mysqld ended
    root@server:/var/lib/mysql# ls
    ./  ../  host.server.bgsnow.com.err  ibdata1  ib_logfile0  ib_logfile1  mysql-slow.log  optistr2_opti/  optistr2_optistreams/  optistr2_perldesk/
    root@server:/var/lib/mysql# ll ../
    alternatives/     dhclient/         logrotate.status  mlocate/          mysqlA/           pgsql/            rkhunter/         sepolgen/         stateless/        yum/
    dbus/             games/            misc/             mysql/            mysqlOLD/         random-seed       rpm/              spamassassin/     texmf/
    root@server:/var/lib/mysql# ll ../
    alternatives/     dhclient/         logrotate.status  mlocate/          mysqlA/           pgsql/            rkhunter/         sepolgen/         stateless/        yum/
    dbus/             games/            misc/             mysql/            mysqlOLD/         random-seed       rpm/              spamassassin/     texmf/
    root@server:/var/lib/mysql# ll ../mysqlOLD/
    cphulkd/                   host.server.bgsnow.com.err  ib_logfile0                mysql/                     roundcube/                 sentry_stat/
    eximstats/                 host.server.bgsnow.com.pid  ib_logfile1                mysql-slow.log             sentry/                    sentry_traf/
    horde/                     ibdata1                    leechprotect/              mysql.sock                 sentry_mambo/              test/
    root@server:/var/lib/mysql# ll ../mysqlOLD/*
    root@server:/var/lib/mysql# ls
    ./  ../  host.server.bgsnow.com.err  ibdata1  ib_logfile0  ib_logfile1  mysql-slow.log  optistr2_opti/  optistr2_optistreams/  optistr2_perldesk/
    root@server:/var/lib/mysql# mv ../mysqlOLD/
    cphulkd/                   host.server.bgsnow.com.err  ib_logfile0                mysql/                     roundcube/                 sentry_stat/
    eximstats/                 host.server.bgsnow.com.pid  ib_logfile1                mysql-slow.log             sentry/                    sentry_traf/
    horde/                     ibdata1                    leechprotect/              mysql.sock                 sentry_mambo/              test/
    root@server:/var/lib/mysql# mv ../mysqlOLD/* ./
    mv: overwrite `./host.server.bgsnow.com.err’? y
    mv: overwrite `./ibdata1′? n
    mv: overwrite `./ib_logfile0′? n
    mv: overwrite `./ib_logfile1′? n
    mv: overwrite `./mysql-slow.log’? y
    root@server:/var/lib/mysql# /etc/init.d/mysql restart
    Shutting down MySQL. SUCCESS!
    Starting MySQL. SUCCESS!

    root@server:/var/lib/mysql# tail -f /var/lib/mysql/host.server.bgsnow.com.err

    InnoDB: Restoring possible half-written data pages from the doublewrite

    InnoDB: buffer…

    InnoDB: Doing recovery: scanned up to log sequence number 0 43655

    091114 13:16:20  InnoDB: Starting an apply batch of log records to the database…

    InnoDB: Progress in percents: 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 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99

    InnoDB: Apply batch completed

    091114 13:16:21  InnoDB: Started; log sequence number 0 43655

    091114 13:16:21 [ERROR] Fatal error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist

    091114 13:16:21  mysqld ended

    Obviously I have forgotten to move back the mysql directory which is responsible for the database for all privileges, databases and so on :)  So moving it back along with the rest of the core files solved the problem:

    root@server:/var/lib/mysql# mv ../mysqlOLD/* ./

    mv: overwrite `./host.server.bgsnow.com.err’? y

    mv: overwrite `./ibdata1′? n

    mv: overwrite `./ib_logfile0′? n

    mv: overwrite `./ib_logfile1′? n

    mv: overwrite `./mysql-slow.log’? y

    root@server:/var/lib/mysql# /etc/init.d/mysql restart

    Shutting down MySQL. SUCCESS!

    Starting MySQL. SUCCESS!

    Thus if you have similar problems, the first thing is to check the MySQL error log and don’t believe the misleading error about the PID :)

     

c
compose new post
j
next post/next comment
k
previous post/previous comment
r
reply
e
edit
o
show/hide comments
t
go to top
l
go to login
h
show/hide help
esc
cancel