Archive for the ‘DataBase’ Category

PostgreSQL 8.3 adding uuid-ossp on Debian

Sunday, October 3rd, 2010

UUID is a nice way to generate (almost) unique id’s.

However on PostgreSQL 8.3 the data type exists, but if you wish to be able to generate UUID’s you need to get a module (in this case uuid-ossp).

On Debian this module is in the contrib package, so if you have not installed this before do it now

user@server:~$ sudo apt-get install postgresql-contrib

Then you need to “load” the module into PostgreSQL; or more accurately you tell PostgreSQL about the new functions this module has, how to reach them etc.
Depending on you security settings (in pg_hba.conf) you may need to log in as the postgres user on your system.

postgres@sever:~$ psql -d MYDB -U postgres -f /usr/share/postgresql/8.3/contrib/uuid-ossp.sql

Tags: , , , , ,
Posted in debian, PostgreSQL, ubuntu | No Comments »

MySQL execute statements directly from the (linux) console

Wednesday, August 18th, 2010

Executing a query quickly from the console is nice to be able to do (without having to enter the MySQL console).

  • This will simply output the result to the console where you are. 
     user@server:~$ mysql -uUSERNAME -p DATABASENAME --execute "REPAIR TABLE position";

    Replace USERNAME and DATABASENAME with your vars

  • If you would like the output to a file 
     mysql -uUSERNAME -p DATABASENAME --execute "SELECT * FROM test" > test.sql;

    Replace USERNAME and DATABASENAME with your vars

  • If you would like the output to be sent as a email 
     mysql -uUSERNAME -p DATABASENAME --execute "SELECT * FROM test" | mail -s 'SUBJECT' example@mail.com

    Replace USERNAME, DATABASENAME , SUBJECT and example@mail.com with your vars

Tags: ,
Posted in MySQL | No Comments »

MYSQL Creating several UUIDs at one single time

Wednesday, July 14th, 2010

I recently ran into the problem that I had too add two rows to a database that uses a UUID (in this case converted to a binary) as a key.
The problem I had was that running two insert statements after each other (just separated by “;”) then the same id was generated.
A workaround I have found is to sleep for a second between the two statements.

Examples (using SELECT instead of INSERT):
Getting the same id twice

SELECT UNHEX(REPLACE(UUID(),'-','')) AS uuid1,  UNHEX(REPLACE(UUID(),'-','')) AS uuid2;
-- Shows: "849392548f1311df91b70019dbd264f1"	"849392908f1311df91b70019dbd264f1"

Adding a sleep to get different ids

SELECT UNHEX(REPLACE(UUID(),'-','')) AS uuid1, SLEEP(1), UNHEX(REPLACE(UUID(),'-','')) AS uuid2
-- Shows: "3901c1928f1511df91b70019dbd264f1"	"0"	"399a5b148f1511df91b70019dbd264f1"

And another one using INSERTS

INSERT INTO testTable (id) VALUES (UNHEX(REPLACE(UUID(),'-','')));
SLEEP(1);
INSERT INTO testTable (id) VALUES (UNHEX(REPLACE(UUID(),'-','')));

Tags: ,
Posted in MySQL | No Comments »

MySQL inserting UUID to a binary(16) field

Wednesday, June 16th, 2010

It is advantageous to store a UUID (acc to RFC 4122) in a binary(16) field in the database.
It is not hard to create such a table and to insert data into it.

CREATE TABLE IF NOT EXISTS test_table (
  id BINARY(16) NOT NULL,
  name VARCHAR(128) NOT NULL,
  PRIMARY KEY  (id)
) 
 
INSERT INTO test_table (id, name) VALUES
(UNHEX(REPLACE(UUID(),'-','')), 'test1'),
(UNHEX(REPLACE(UUID(),'-','')), 'test2'),
(UNHEX(REPLACE(UUID(),'-','')), 'test3')

Tags:
Posted in MySQL | 1 Comment »

MySQL importing a sql file

Monday, June 7th, 2010

To simply import a sql file (and get any results printed to the prompt)

mysql -uUSERNAME -p DBNAME < FILENAME

The parameters are
-u username (note no space between the u and USERNAME)
-p password (can be given as the username (without a space between p and PASSWORD; or if omitted mysql will ask for it)
DBNAME is the name of the database to import to (if the sql file don’t create a database on it’s own)
FILENAME is the name of the file to import

Other parameters can be
-h server host (dns name or ip) if -h is omitted then localhost is assumed.
-P port number
-f force (will not break on errors)

To import a sql file and then have to output to a file (for instance when running from a crontab)

mysql -uUSERNAME -p DBNAME < FILENAME > OUTFILE

Works just as the previous import except that any output will be written to OUTFILE.

Tags: ,
Posted in MySQL | No Comments »

MySQL creating a database and user from the console

Wednesday, March 24th, 2010

Here is how to quickly create a database and add a user to a mysql database (the mysql console).
(Yes some values need to be changed)

  • mysql> use mysql;
  • mysql> CREATE DATABASE dbname;
  • mysql> INSERT INTO user (Host,User,Password) VALUES (‘localhost’, ‘username’, PASSWORD(‘password’));
  • mysql> GRANT ALL ON dbname.* TO ‘username’;
  • mysql> FLUSH PRIVILEGES;
  • mysql> exit

Update: This approch will not work when using MySQL server under Windows.

Posted in MySQL | 2 Comments »

mysqldump Couldn’t execute references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)

Friday, February 12th, 2010

If you get an error messages like “mysqldump: Couldn’t execute ‘SHOW FIELDS FROM `store_information`’: View ‘database.viewname’ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)” then a number of things might be wrong:

  • The view is “corrupted” (this could happen if the table the view is of has been updated in a way so that the view is no longer correct – for instance if the table structure is updated)
  • The user you are using are lacking permissions (to the view/table (that the view is referencing) )

What I usually do when this happens to solve it is to:

  1. Verify if the user running the mysqldump has the permissions (or test as root if you can – as root has more permissions).

    If this is the case: update the permissions on the user running the dump – or change to another user

  2. Try to find out if the table the view is referencing has been updated (think back what have I done/check with the rest of the team)

    Or if you do not wish to/can not do this, check a previous backup up the base table (from when mysqldump worked with the view) and compare the structure with the current structure in the database.

    3. If this is the case: I just drop the view and recreate the view so that it gets corrected in the underlying db structure and re-run the dump.

More info for people using this notebook as a article; here is some manual pages that will help you along if you don’t know how to alter permissions/create views.
More information about user permissions
More information about views

Tags: ,
Posted in MySQL | 5 Comments »

Looking at the database structure (in MSSQL)

Tuesday, December 11th, 2007

This query will show how the current database looks (what columns exists in what tables etc), the select might be a * instead but this is the info I find interesting.

select TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH

from INFORMATION_SCHEMA.COLUMNS

order by TABLE_NAME, ORDINAL_POSITION

Tags: , ,
Posted in DataBase, SQLServer | No Comments »