Producing raw MySQL output

mysql -u -p -D -e "" -N -B

where

-N turns off column headers (names?
-B turns off borders

How to get the length of a string containing only spaces

DATALENGTH function returns string length in bytes:

DECLARE @str VARCHAR(10)
SET @str= '      '
PRINT LEN(@str)
PRINT DATALENGTH(@str)

Result:

0
6

Installing Oracle 11g on Ubuntu 9.10 + VirtualBox

1. Create the system VDI. I used 8 GB.

2. Create another VDI for Oracle. I created 20 GB one.

3. Install Ubuntu. I used virtual machine with 2 GB memory.

4. Log into system and install VirtualBox Tools, then restart OS.

5. Extract both in the host machine .zip's into single folder, otherwise you will receive error message "File not found ….. WFMLRSVCApp.ear"

6. Create the shared VB folder for this machine.

7. In the guest OS type something like:

sudo mount.vboxfs iso /mnt/iso

8. Mount Oracle disk. If you will use ReiserFS for this disk then

sudo mount -t reiserfs /dev/sdb1 /opt/oracle
sudo chown /opt/oracle

in case of ext3:

sudo mount -t ext3 /dev/sdb1 /opt/oracle -o uid=

9. Create Oracle Inventory folder:

sudo mkdir /opt/oraInventory
sudo chown hellcat /opt/oraInventory

10. Run

sudo chmod u+x /mnt/iso/install/.oui /mnt/iso/install/unzip

otherwise you will receive "...operation not permitted" error on startup.

11.

cd /mnt/iso/; ./runInstaller

12. sudo apt-get install unixodbc-dev libaio-dev

13. Install libstdc++5:

wget http://lug.mtu.edu/ubuntu/pool/universe/g/gcc-3.3/libstdc++5_3.3.6-17ubuntu1_i386.deb
ar vx libstdc++5_3.3.6-17ubuntu1_i386.deb
tar zxf data.tar.gz
sudo mkdir /usr/lib32
sudo install usr/lib32/libstdc++.so.5.0.7 /usr/lib32/
sudo ln -s /usr/lib32/libstdc++.so.5.0.7 /usr/lib32/libstdc++.so.5
sudo echo "/usr/lib32" >> /etc/ld.so.conf.d/libc.conf
sudo ldconfig

14. Figure out where Oracle put your ORACLE_HOME -- mine was /opt/oracle/product/11.2.0/dbhome_1
Run:

export ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1
sudo ln -s /usr/bin/basename /bin/basename

This will let you to get rid of "undefined reference to "nnfyboot" in the logs.

15. Change to $ORACLE_HOME . Run ./runInstaller

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;