mysql.servers

Sometimes one can see an error like:

MySQL database failed! : SQL flush privileges failed : Table 'mysql.servers' doesn't exist at...

A guy named Chris Brown suggested the following:

USE mysql;

CREATE TABLE `servers` (
`Server_name` char(64) NOT NULL,
`Host` char(64) NOT NULL,
`Db` char(64) NOT NULL,
`Username` char(64) NOT NULL,
`Password` char(64) NOT NULL,
`Port` int(4) DEFAULT NULL,
`Socket` char(64) DEFAULT NULL,
`Wrapper` char(64) NOT NULL,
`Owner` char(64) NOT NULL,
PRIMARY KEY (`Server_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL Foreign Servers table';

Ignore duplicates for unique fields

So, you add multiple data rows into table with UNIQUE index on some field but when you add the duplicates into that field the routine is stopped.

How to make the insert skip those values?

Create the index in this way:

CREATE INDEX NotAllowed
ON Dest (Value)
WITH(IGNORE_DUP_KEY = ON);
go

The size of NULL

How much space does NULLs take for different types?

What nullable types allocate space even when null?

http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/dc168f01-075f-4f29-a669-eba93b4db6a0

Do columns occupy space even if they store NULL?

http://sankarreddy.spaces.live.com/blog/cns!1F1B61765691B5CD!387.entry

Space used for NULL

http://social.msdn.microsoft.com/Forums/en/sqlgetstarted/thread/e6b8abb8-851e-498d-93f0-2701f8e44b22

Null Values

http://msdn.microsoft.com/en-us/library/ms191504.aspx

MSSQL: users and roles

How to get list of users with their roles?

select name, RoleName FROM sys.sysmembers sm
JOIN
( select 'RoleName' = name, 'RoleId' = uid, 'IsAppRole' = isapprole
from sysusers where issqlrole = 1 or isapprole = 1 ) roles
ON sm.groupuid=roles.RoleID
JOIN
( select * from master.sys.sysusers where islogin=1 ) logins
ON logins.uid=sm.memberuid;

Metadata in SQL Server

What is metadata? Usually this is used as a description of data.

"Depending on your viewpoint, metadata is used to add context and understanding about data that users are accessing, or it can be used to hide complexity from end users who are not required to know or understand the technical details of data. Metadata can also be used by systems to determine data types, to reveal the structure and relationships between data, or to determine if and when data last changed, and what changes were made to data. Metadata is also used by applications and systems to perform type checking, data validation, and formatting of data." ( TechNet )

Metadata is managed with the folllowing stored procedures:

Add an extended property	sp_addextendedproperty
Delete an extended property	sp_dropextendedproperty
Update an extended property	sp_updateextendedproperty
View an extended property	fn_listextendedproperty sys.extended_properties

Here is an example of extended property update:

USE AdventureWorks;
GO
EXEC sys.sp_updateextendedproperty
@name = N'MS_Description',
@value = N'New Description for the Address table',
@level0type = N'SCHEMA', @level0name = Person,
@level1type = N'TABLE', @level1name = Address;
GO

You can view the metadata with SELECT statement but you cannot modify it using UPDATE:

sp_configure 'allow updates',1
update sys.extended_properties set value='111' WHERE name='Purpose';

Result:
Ad hoc updates to system catalogs are not allowed.

We can not update neither system tables nor system views. This is regardless whether we've set "allow updates" or not. Use special procedures.

You can read more about metadata at http://msdn.microsoft.com/en-us/library/cc966384.aspx#ECAA

How to add 1 day to the date

MySQL : dateadd(dd, 1, getdate())
Oracle : SYSTIMESTAMP+NUMTODSINTERVAL(1,'day')
DB2 : CURRENT TIMESTAMP+1 DAY
PostgreSQL : LOCALTIMESTAMP+INTERVAL '1 day'

BCP and problems with Unicode

If you try loading the unicode file into mssql table that's what happens:

> bcp madhu1.dbo.testunicode in "D:\testunicode.txt" -S ABCSqlSrv -U sa -P sa -f "d:\fmt.txt"

Starting copy...
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1

=================================

contents of testunicode.txt are :

मधुकर|pune|33
राहुल|pune|36

==========================

contents of format file fmt.txt are

8.0
3
1 SQLNCHAR 0 0 "|" 1 name SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 0 0 "|" 2 address SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 0 "\r\n" 3 age SQL_Latin1_General_CP1_CI_AS

================

However, this can be avoided by using -w option and removing -f option

Sphinx example for PHP

Here is a great example for Sphinx written in PHP : http://www.sphinxsearch.com/wiki/doku.php?id=php_example_script

Have fun.

Resetting forgotten root password

if you forgot the root password - here is the recipe to reset it:

1) kill mysqld
2) run

mysqld_safe –skip-grant-tables &

mysql -u root mysql

mysql> UPDATE user SET Password=PASSWORD(’newrootpassword’) WHERE User=’root’;
mysql> FLUSH PRIVILEGES;

3) kill the started MySQL server
4) restart it

Resetting forgotten root password

if you forgot the root password - here is the recipe to reset it:

1) kill mysqld
2) run

mysqld_safe –skip-grant-tables &

mysql -u root mysql

mysql> UPDATE user SET Password=PASSWORD(’newrootpassword’) WHERE User=’root’;
mysql> FLUSH PRIVILEGES;

3) kill the started MySQL server
4) restart it

Example of MSSQL quiet installation

It installs only MSSQL engine. Other possible features can be: SSMS, ADV_SSMS, BOL...

Setup.exe /QUIET
/INDICATEPROGRESS
/Action=Install
/FEATURES=SQLENGINE
/INSTANCENAME="EVAL33"
/SQLSYSADMINACCOUNTS="BUILTIN\Administrators"
/SECURITYMODE=SQL /TCPENABLED="1"
/NPENABLED="1"
/ADDCURRENTUSERASSQLADMIN="True"
/SQLSVCSTARTUPTYPE="Automatic"
/SQLSVCACCOUNT="NT AUTHORITY\LOCAL SERVICE"
/SAPWD="xxxxx"

Auto-increment field

How to create it in

a) MySQL:

CREATE TABLE `t1` ( `id` INT UNSIGNED AUTO_INCREMENT , ... ) AUTO_INCREMENT=... ;

b) PostgreSQL:
first create the sequence:

CREATE SEQUENCE "seq1" INCREMENT BY ... START WITH ... ;

and then - create the table:

CREATE TABLE "t1" ( "id" INT NOT NULL DEFAULT nextval('seq1') ...);

c) Microsoft SQL Server

CREATE TABLE [t1] ( [id] [int] NOT NULL identity(1, 2) ...);

(this means start with 1 and increment by 2 each time )

Paint & SQL Server. How to export diagram to image.

I think many people wondered why Paint application still exists in Windows? It has too few features. It is completely basic (or boring!).

But this tool can greatly help you with database design.

Say, you have the database diagram built in MSSQL Management Studio but you have no idea how to export it into the image.

Just PAINT !!!

Select all of the tables in the diagram, press Ctrl+C (or copy in your usual way) and paste it into Paint.

Do you like it?

Ukrainian, Russian and English character sets for Sphinx fulltext search

If you need all of those language for fulltext search the charset_table string should be the following:

charset_table = 0..9, A..Z->a..z, _, a..z, U+410..U+42F->U+430..U+44F, U+430..U+44F, U+404->U+454, U+406->U+456, U+407->U+457, U+490->U+491

ER tools for PostgreSQL

Indeed, PostgreSQL is a great database engine. It's free and open-source. Great tool for the server.

But what about developers? Which ER tools can we use for database design? I'm not talking about the databases with 5 tables, I mean more complex designs.

• MySQL Workbench: a tool developed by Sun. It exports design into the MySQL-specific code, thus you have to modify it by hand in the text editor. Pros: you can show the graphic database design to the customer. Contras: MySQL-specific SQL and ... BUGGY !! (at least - the latest version. Just click the "Not NULL" checkbox in the fields list ... and the program dies).
• Database Designer for PostgreSQL from MicroOLAP. Never used but look at the prices: starting from 124.95 USD for personal use. I think the tool is good, but the price...

Comparing to Microsoft SQL Server: you can buy the Developer edition where diagramming tool is surely pesent just for 60 bucks and be happy. This is a full-featured product which you cannot use in industry. Marvelous!

Any more ideas guys?