The command to use to list all code inside a MySQL stored procedure is as follows

SHOW PROCEDURE CODE <procedurename>

For example:

SHOW PROCEDURE CODE spSaveUser

I was in Webmin’s MySQL viewer and realized that there was no way to show a list of existing stored procedures in my code.

The command to list all defined stored procedures is as follows:

SHOW PROCEDURE STATUS;

There is also another command to list all defined functions:

SHOW FUNCTION STATUS;

One of my recent assignments has been to document the process of importing GeoNames data into a mysql table using Windows.

In particular, towns and regions for a specific country. One of the better examples I found was part of a Linux shell script, so I am documenting it to use with a DOS batch file on Windows 7 instead.

Instructions

1. Download data for a single country

A country by country file named after the two-letter codes can be found at:

http://download.geonames.org/export/dump/

Choose and extract the .txt file, for example bw.txt from BW.zip for  Botswana.

2. Create a mysql table that will hold the country’s data

On your destination MySQL database, create a table using the following code:

CREATE TABLE geoname (
geonameid int PRIMARY KEY,
name varchar(200),
asciiname varchar(200),
alternatenames varchar(4000),
latitude decimal(10,7),
longitude decimal(10,7),
fclass char(1),
fcode varchar(10),
country varchar(2),
cc2 varchar(60),
admin1 varchar(20),
admin2 varchar(80),
admin3 varchar(20),
admin4 varchar(20),
population int,
elevation int,
gtopo30 int,
timezone varchar(40),
moddate date
) CHARACTER SET utf8;

3. Create a DOS batch file (.cmd) and import file (.sql) that will populate country data into the  geoname  MySQL table

Save the following  code to a file called geo_import.sql

LOAD DATA LOCAL INFILE 'BW.txt'
INTO TABLE geoname
CHARACTER SET 'UTF8'
(geonameid, name, asciiname, alternatenames, latitude, longitude, fclass, fcode, country, cc2, admin1, admin2, admin3, admin4, population, elevation, gtopo30, timezone, moddate);

4. Save the following code to a file called geo_import.cmd

@echo off
mysql -hlocalhost -uuser1 -ppassword1 --local-infile=1 sampledb < geo_import.sql

5. Replace user1 and password1 with your MySQL credentials. Also, replace sampledb with the name of your MySQL database

This will result in about 3700+ entries.

What have we imported?

Each of the imported records belongs to a specific class, in order to understand how those 3700+ entries are distributed, you may run the following query:

select fclass, fcode, count(*)
from geoname
where country='BW'
group by fclass, fcode

fclass and fcode stand for Feature Class and Feature codes.  They join to create a code for a specific feature or type of site for a country.

So given the query above, the following sample counts are returned:

P.PPL= 629

This represents the number of populated places such as cities, towns, villages

T.PAN= 337

This represents “a near-level shallow, natural depression or basin

Based on this knowledge, we are ready to create a query that will limit the lists of places to only the ones that are relevant.

The full set of Feature Classes and codes are available from this Text file:

http://download.geonames.org/export/dump/featureCodes_en.txt

Displaying towns and populated areas

So towns and populated areas are represented by fclass=’P’ so it should be easy enough to filter out all populated areas. Also, as a bonus, the regions (provinces or states) to which a town belongs can also be retrieved.  They are represented by fclass=’ADM1′.

The resulting mysql query to obtain towns with their respective regions in Botswana is as follows:

select
ge.country AS country,
ge.geonameid AS geonameid,
ge.asciiname AS townname,
rg.asciiname AS regionname
from
(geoname ge
join geoname rg ON (((ge.country = rg.country)
and (ge.admin1 = rg.admin1)
and (rg.fclass = 'A')
and (rg.fcode = 'ADM1'))))
where
(ge.fclass = 'P')
and ge.country='BW'
order by ge.asciiname

This results in the following data:

'BW', '7697027', 'Artesia', 'Kgatleng District'
'BW', '1106239', 'Babusi', 'South East District'
'BW', '1106247', 'Badiri', 'South East District'
'BW', '933897', 'Bakwena', 'Central District'
'BW', '7883436', 'Bapeding', 'Central District'
'BW', '7745756', 'Bathobabangwe', 'Southern District'
'BW', '7746970', 'Bathobantle', 'Southern District'
'BW', '7908221', 'Batsetsi', 'Kweneng District'
etc...

Summary

It is not so difficult to find all town information for a specific country without much research. Geonames provides such a service.  Each country is contained in its own text file. All that is needed is to use scripts to import and then filter out information. In addition, the region for each town can be derived with the statement presented here.


References

The Geonames importer, a shell script import utility to run on Unix systems

The goal of this activity is to install just the MySql Server as a Windows service in 32-bit mode on a 64-bit Windows 7 operating system.

On the first part, I downloaded and set up the MySQL installer application.

In my case, I do not need the client utilities because I have previously installed them

  • Select ‘Add/Modify products and features from the Installer menu

mysql.maintenance.windows.7

  • This will take you to the licensing page. After confirming you accept the license terms, check on the ‘I accept’ checkbox and select ‘next’.

This will take you to the Feature Selection option. Select the ’32-bit’ to the right of the ‘Community Edition’ combo box. This ensures that you will install MySQL 32-bit. There is no need to check any other options

mysqlinstaller

 

  • Select next and this will take you to the Check requirements screen. Select next again to go to the Installation screen.
  • Select ‘next’ and this will begin the process of MySQL Server Community installation

mysql.installation.progress

When the server has finished installing, you should get a message saying that installation was successful

mysqlinstaller.installation

  • Select ‘next’ to go to the configuration screen.

The configuration screen presents several configuration options for the server

mysqlserverconfigurationtype

The first one is the Configuration type for the server. Because I will install this version on my laptop and will only use it occasionally, I will select the ‘Development Machine’ option.

I will enable TCP/IP networking as well since I will need to access it from my local network

  • As the last step, you can click on the ‘Show Advanced options’ checkbox.

mysql.show.advanced

This will allow you to access a page where you can define the root account password.

Select ‘next’ and set the root account password

mysql.set.root.password.win

I don’t need to define new User accounts and the root account is good enough for the purpose I need it.

Important: On a production system, you need to develop a security strategy where the root account needs to be protected and several access levels and MySQL User Accounts defined.

  • Select ‘Next’ to continue to the Windows service configuration

mysql.service

The Windows service name will be something  like MySQL<version> where version is the number of the current version number. After installation you can change the service to start manually if required.

  • Select ‘Next’. This will take you to the ‘Loggging options screen’.

mysql.configure.logging

These are the names of the log files that you can set up. I usually change these names to include the server version but I disable logging at installation time. After installation, I can turn them on one by one if required.

  • Select ‘next’ to continue. This will initiate the configuration with the options that have just been provided.

configuration

When the process is finished, you can select the ‘Show details’ button in order to view that the process of setting up the Windows service, etc. has been successful

mysql.log

  • Select ‘Next’ to proceed to the ‘Installation Complete’ screen.

Your Windows Services control panel application should now show a running service called MySQL<server version>

mysql-windows-services-after

When you inspect this service the entry points to a startup command like the following:

Example:

“C:/Program Files/MySQL/MySQL Server 5.6/bin\mysqld” –defaults-file=”C:\ProgramData\MySQL\MySQL Server 5.6\my.ini” MySQL56

This displays the executable (mysqld) and the initialization file (my.ini) that is being used.

I wanted to setup a development environment with the MySQL Server 32-bit.

I use Windows 7 64-bit. I will only need this server only occasionally, so I am choosing to install the 32-bit version.

I also don’t need the client tools, I have set them up separately as described in this post.

Also, there are other alternatives for client access such as the very lightweight HeidiSQL application that is quite ready to perform MySQL administrative tasks.

Instructions

  • First, obtain the Community version of MySQL Server.  I will stick to the GA version as this is the one that is most stable.

go to:

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

and choose the MSI package as follows:

mysql.msi.for.windows.64

  • Choose the ‘Download’ button right beside the Windows (x64, 64-bit), MySQL Installer MSI. This will take you to a screen with two downloadable files, choose the second one (a 190+ Mb file)

mysql.second.option.msi.for.windows

  • When you click on ‘Download’, you are prompted to login or sign up with an Oracle account, however this is not needed, go to the link at the bottom that says: No thanks, just start my download.

you.are.not.required.to.sign.up.in.order.to.download.mysql

The downloaded file has a name like mysql-installer-community-<mysql communityversion>.msi

  • Run the downloaded .msi file. The result will be an installer application recorded as follows in your Control Panel ->Programs and Features
Before setting up MySQL server, the MySQL installer application needs to be installed

The MySQL installer application needs to be installed before setting up MySQL server,

On the next step, the installer is used to setup only the MySQL Server option as a Windows service.