Let’s say you have a gallery and it consists of three database tables (photos, category and gallery). Each of these tables has lots of related data, obviously…

By following the old database routine, I started indexing data from top to bottom (from gallery to photos table).

But this resulted in a weird behavior where SOLR would index gallery id and name, and only the first category; It simply ignored all other gallery categories and photos.

After spending days mind boggling what could go wrong, it suddenly occurred to me that the proper way to index data into SOLR would be to index is “opposite direction”, from bottom to top that is (from photos to gallery).

It was a great relief to finally see all relational data properly indexed :)

The example that bothered me was:

MySQL schema

photos
- id
- categoryId
- views
- url
- title

category
- id
- name
- galleryId

gallery
- id
- name

SOLR db-data-config.xml

<dataConfig>
	<dataSource
	    type="JdbcDataSource"
	    driver="com.mysql.jdbc.Driver"
	    url="jdbc:mysql://localhost/database"
	    user="username"
	    password="password"/>

	<document name="gallery">

		<entity rootEntity="true" name="photos" query="select *, rand() as uuid from photos">
		<field name="photo_id" column="id" />
		<field name="photo_categoryId" column="categoryId" />
		<field name="photo_views" column="views" />
		<field name="photo_url" column="url" />
		<field name="photo_title" column="title" />

			<entity name="category" query="select * from category where id='${photos.categoryId}'">
			<field name="category_id" column="id" />
			<field name="category_name" column="name" />
			<field name="category_galleryId" column="galleryId" />

				<entity name="gallery" query="select * from gallery where id='${category.galleryId}'">
				<field name="gallery_id" column="id" />
				<field name="gallery_name" column="name" />
				</entity>

			</entity>

		</entity>

	</document>

</dataConfig>

DELETE
	$table1
FROM
	$table1
LEFT JOIN
	$table2
ON
	$table1.fieldId = $table2.fieldId
WHERE
	$table2.fieldId == NULL
;

SELECT FLOOR(1 + (RAND() * 100))

where 100 is a maximum allowed number in random sequence

This example will update a year in table field which was wrongly set to 2011 and set it to 2010.

e.g. 2011-09-20 will be changed to 2010-09-20 throughout the table.

UPDATE $table \
SET $field = $field - INTERVAL 1 YEAR \
WHERE year($field)=2011;

I was implementing PhishTank database to one of my projects.

PhishTank is a service, operated by OpenDNS which updates a current list of phishing sites on the Internet.

When dealing with large number or requests it is advised that you download their hourly updated database dump.

I decided to grab the .csv dump and import it in my database using cron.

The heart of this cron is the following command which imports .csv data into local database

mysql -u$username -p$password -e \
"load  \
    data local
infile  \
    \"./verified_online.csv\" \
into table  \
    $database.$table \
fields terminated by \",\" \
enclosed by '\"' \
lines terminated by \"\n\" \
( \
    phish_id, \
    phish_detail_url, \
    url, \
    submission_time, \
    verified, \
    verification_time, \
    online, \
    target \
);"

Make sure that you already have the table created with following fields “phish_id, phish_detail_url, url, submission_time, verified, verification_time, online, target”

I had to make a clone of a website on a development server. The site was running on MySQL 4 and dev server on version 5. Ideally, all queries would run fine, but a huge query with lots of joins was failing for some reason.

In order to keep a clone as is, I decided to try and install MySQL 4 along with version 5 on the same server.

On dev server MySQL 5 was installed using apt-get, and I installed MySQL 4 like this:

Download MySQL 4 source from a local mirror

$wget http://dev.mysql.com/get/Downloads/MySQL-4.1/mysql-4.1.22.tar.gz/from/http://mysql.blic.net/

Unpack and install

$tar zxvf mysql-4.1.22.tar.gz
$sudo groupadd mysql
$sudo useradd -g mysql mysql
$cd mysql-4.1.22
$./configure --prefix=/usr/local/mysql
$make
$sudo make install
$sudo cp ./support-files/my-medium.cnf /etc/my.cnf
$cd /usr/local/mysql
$sudo ./bin/mysql_install_db --user=mysql
$sudo chown -R root .
$sudo chown -R mysql var
$sudo chgrp -R mysql .

Start the daemon

$sudo /usr/local/mysql/bin/mysqld_safe --user=mysql --port=3308 --sock=/tmp/mysql4.sock &

Connect to installed MySQL 4 using shell

$/usr/local/mysql/bin/mysql --socket=/tmp/mysql4.sock

Display database version

mysql> select version();
+------------+
| version() |
+------------+
| 4.1.22-log |
+------------+

To connect phpMyAdmin to MySQL 4 edit the phpMyAdmin config file and change server value ‘localhost’ to ‘:/tmp/mysql4.sock‘

From web interface just type ‘mysql’ for username, and leave password empty. (Do set a password for MySQL user later, this was just an example)