Tag Archives: mysql

mysql becomes mariadb

fedora and opensuse will be replacing mysql with mariadb

mysql uuid gotchas

http://dev.mysql.com/doc/refman/5.1/en/miscellaneous-functions.html#function_uuid

mysql uuid gotchas

  • Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate computers that are not connected to each other.
  • The fifth number is an IEEE 802 node number that provides spatial uniqueness. A random number is substituted if the latter is not available (for example, because the host computer has no Ethernet card, or we do not know how to find the hardware address of an interface on your operating system). In this case, spatial uniqueness cannot be guaranteed. Nevertheless, a collision should have very low probability.
  • Although UUID() values are intended to be unique, they are not necessarily unguessable or unpredictable. If unpredictability is required, UUID values should be generated some other way.

Eclipse IDE 3.7 and MySql 5.0

To get Eclipse IDE 3.7 working with MySql 5.0 you’ll need

Data Tools Platform SQL Query Builder

http://market.eclipsesource.com/yoxos/node/org.eclipse.datatools.sqldevtools.sqlbuilder.feature.feature.group

Editor for building SQL queries SQL Query Builder enables you to create, edit, or run SQL statements using the SQL Query Builder graphical interface, which provides access to your database schema and objects so that you can quickly create or edit SQL statements without actually typing any SQL code.
Data Tools Platform Enablement for MySQL

http://market.eclipsesource.com/yoxos/node/org.eclipse.datatools.enablement.mysql.feature.feature.group

DTP support for using the MySQL database The Enablement project is intended t…

http://dev.mysql.com/downloads/connector/j/

then

$ tar zxvf mysql-connector-java-5.1.18.tar.gz

# cp \
~/mysql-connector-java-5.1.18/mysql-connector-java-5.1.18-bin.jar \
/usr/lib/eclipse/plugins/mysql-connector-java-5.1.0-bin.jar

then

run eclipse

ovh vps cloud vms

Just in the process of moving all my web hosting to OVH VPS cloud VMs and thought it’d be useful to document how to turn a vanilla OVH VPS into a personal multi-site webhost !

  • use OVH control panel to replace debian with centos
  • add a user
  • disable root ssh login
  • restart sshd
  • disable/uninstall unnessesary services such as smartmontools yum-updatesd
  • yum check-update
  • run system-config-security
  • yum install redhat-lsb
  • rpm install rpmforge-release
  • yum install fail2ban
  • replace sendmail with postfix (unless you like sendmail)
  • install LAMP (inc httpd,mod_ssl and PHP 5.3.3)
  • secure mysql (change root pwd and delete guest accounts & dbs)
  • chkconfig httpd and mysqld on
  • yum install perl-Crypt-SSLeay perl-Net-SSLeay
  • yum install webalizer
  • install webmin
  • secure webmin (change user & port)
  • install virtualmin
  • enable https
  • disable virtualhost mail handling (unless your not using goog apps)
  • install rrdtools
  • install webmin systemstats
  • create virtual hosts
  • scp public_html tgz and mysql sql dumps from old webhost
  • untar tgz and mysql < sql
  • change DNS ip addresses from old webhost to new webhost
  • wait 1 min !
  • check new site is running !

Eclipse IDE, variants and add-ons

As well as the (IBM) Eclipse IDE I especially like Aptana Studio and Easy Eclipse variants.

Aptana because of its video tutorials available at aptana.tv and Easy Eclipse because of its extensive range of pre-packaged add-ons, plug-ins & extensions :

DB

PHP

SCM & Version Control

Markup & XML

Read and post comments | Send to a friend

MySQL UTF-8, backslashes ‘ ’ & singlequotes ‘ ‘ ’

1)  
watch out for the additional Unicode single quote characters u2018 ( ‘ ) & u2019 ( ’ )

2)
use '\' when inserting '' and
use '\\' for looking for '' in LIKE statements !

3a)
watch out for LIKE <> REGEXP !

3b)
where
 first_name LIKE '%\\%' or  
 last_name LIKE '%\\%' or
 email LIKE '%\\%'

3c)
where
 first_name REGEXP '.*\\.*' or  
 last_name REGEXP '.*\\.*' or
 email REGEXP '.*\\.*'

4)
documentation

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like

extract
 
because MySQL uses C escape syntax in strings (for example, u2018nu2019 to represent a newline character), you must double any u2018\u2019 that you use in LIKE strings. For example, to search for u2018nu2019, specify it as u2018\nu2019. To search for u2018\u2019, specify it as u2018\\u2019; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.

ANY FINALLY FINALLY !

(Exception: At the end of the pattern string, backslash can be specified as u2018\u2019. At the end of the string, backslash stands for itself because there is nothing following to escape.)

Read and post comments | Send to a friend

MySQL slow query log and mysqldumpslow

First enable slow query logging, then generate a slow query and finally look at the slow query log !

Enable
http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html

Generate

select sleep(2)

then run mysqldumpslow

$ mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (t, at, l, al, r, ar etc), 'at' is default
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

mysqldumpslow output

# Time: 070906 12:47:20
# User@Host: root[root] @ localhost []
# Query_time: 3  Lock_time: 0  Rows_sent: 1  Rows_examined: 0
select sleep(3);

Read and post comments | Send to a friend

MySQL leads the way in “developer relations”

http://www.planetmysql.org/kaj/?p=85

The Quality Contribution Program goal is to improve the quality of MySQL products, with the active co-operation of the MySQL user community.

The program facilitates this by
   1. visibly acknowledging the participants by attributing individual quality enhancements to them;
   2. rewarding the participants with benefits in proportion to their contribution (Awards are subscriptions to MySQL Enterprise);
   3. streamlining the process of contributing to MySQL Quality for the benefit of both current and future contributors

Quality Contributions fall into three categories: (i) bug reports, (ii) test cases, and (iii) code patches. We evaluate each contribution with a publicly verifiable set of rules, and the contributor accumulates the QA points for all contributions submitted during the last 12 months.

Start today @ http://dev.mysql.com/qualitycontribution.html

Read and post comments | Send to a friend