Restoring a database on ms sql server for linux (docker)

Right-O Jeeves, I downloaded the Northwind backup for sql server, but how do I restore it with sqlcmd? The butler answered:

RESTORE DATABASE Northwind FROM DISK='/tmp/Northwind.bak'

But that produced

Msg 3201, Level 16, State 2, Server 61ea3d23c7fb, Line 1
Cannot open backup device '/tmp/Northwind.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Server 61ea3d23c7fb, Line 1
RESTORE DATABASE is terminating abnormally.

Jeeves isn't in top form these days. Either that or he is still smarting over that incident of the purple socks. He left me scratching around and I figured out for myself that you need to move the file to the location where sql server's data is peristed. In my case, I started the docker container as :

docker run -d -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=*****'   -p 1433:1433 -v /var/opt/mssql:/var/opt/mssql -d microsoft/mssql-server-linux

This means that the Northwind.bak file has to be copied to '/var/opt/mssql', and then

Directory lookup for the file "D:\MSSQLDataFiles\UNTC\DB_All\DB\NORTHWND.MDF" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Server 61ea3d23c7fb, Line 1
File 'Northwind' cannot be restored to 'D:\MSSQLDataFiles\UNTC\DB_All\DB\NORTHWND.MDF'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Server 61ea3d23c7fb, Line 1
Directory lookup for the file "D:\MSSQLDataFiles\UNTC\DB_All\DB\NORTHWND_log.ldf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Server 61ea3d23c7fb, Line 1
File 'Northwind_log' cannot be restored to 'D:\MSSQLDataFiles\UNTC\DB_All\DB\NORTHWND_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Server 61ea3d23c7fb, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Server 61ea3d23c7fb, Line 1
RESTORE DATABASE is terminating abnormally.

This is paydirt! According to MSDN, you need to make a close note of these paths, and change your restore command in sqlcmd appropriatelty. Something like:

RESTORE DATABASE Northwind FROM DISK='/var/opt/mssql/Northwind.bak' WITH MOVE 'Northwind' TO 'D:\MSSQLDataFiles\UNTC\DB_All\DB\NORTHWND.MDF', MOVE 'Northwind_log' TO 'D:\MSSQLDataFiles\UNTC\DB_All\DB\NORTHWND.LDF'

What, still the same error!! Then I found a stackoverflow answer that lead me down the right track. You have to use the actual file system paths on linux rather than the virtual paths that are shown in the error.

RESTORE DATABASE Northwind FROM DISK='/var/opt/mssql/Northwind.bak' WITH MOVE 'Northwind' TO '/var/opt/mssql/data/NORTHWND.MDF', MOVE 'Northwind_log' TO '/var/opt/mssql/data/NORTHWND_log.ldf' Go

What-ho! What-ho! What-ho!

May 22, 2019, 8:14 a.m. » Tagged: sql-server , docker , northwind , linux

Why you shouldn't use PHP + MySql + Apache bundles

Ancient history.

These bundles are known by many different names like XAMPP, WAMPP, LAMPP etc. They all have a few things in common, such as  PHP, MySql and Apache being bundle installer togther with a bunch of other stuff like Wordpress, Joomla and phpmyadmin. Sometimes Apache makes way for nginx. These bundles were very popular a decade ago and sadly even today they are used far more often than they should be. These were and still are primarily used by developers on their desktops. The reason for their popularity was the fact that one does not need to seperately install any of the servers but instead they are all installed with reasonable defaults.

All these bundles are obsolete now rather they became obsolete as far back march 2012.

PHP Development server.

When php 5.4 was released in 2012, it completely eleminated the need to setup apache for development enviorenments. This is thanks to the inclusion of a PHP built in webserver. Since then all that was needed to test your scripts locally was to enter the terminal, move into the folder with the scripts and just type

php -S localhost:8000

And the built in webserver would immidiately start to deliver your files. You can view them in the browser just as you would with apache.

What about mysql?

Exactly! What about mysql? First of all do you really need mysql? Don't be put off by the lite in sqlite. It's a fantastic database and it's perfectly capable of powering almost all the websites out there. Let's admit it almost all the websites out there including this one get's very little traffic. A vast majority of the page views on almost any website doesn't result in a database write operation. Sqlite may suck at write operations but it handles read operations really well. So whenever possible switch to sqlite and do away with all that hassle.

On the other hand if you really do need to use mysql, it's fairly straightforward to install the server from the downloads available for your operating system on the mysql website. Alternatively you could install mariadb or percona server.

Using this approach helps to avoid having too many black boxes in your system. Often while working with mentees i've had to ask where are your errors logs? Where is the php.ini file why are you not using a debugger and an IDE and often they didn't have an answer due to the fact they had used a bundle. And these bundles make it very hard to customize each installation to meet your requirements + updating individual paakges are a nightmare.

May 21, 2019, 2:31 a.m. » Tagged: php , mysql , apache

Getting back to SQL Server

Having decided to establish friendly relations with microsoft SQL server after an estrangement of nearly 15 years, I am playing around with SQL server on linux installed via docker.  Here are some more of the interesting issues that I encountered.

Table Variables.

Why on earth does SQL server define something called a table variable. 

DECLARE @HelloWorld TABLE (
    ID INT IDENTITY,
    Description VARCHAR(1000)
)

Why not use the more common temporary table? well mssql has them too, been having them for a longer time. One SO Q&A has a lengthy discussion on this and one thing that jumped out is:

Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option

When I first started on mysql ever so long ago, I always used temporary tables, those days mysql didn't have subqueries and all these years later, mysql subqueries are appallingly slow. On postgresql i've never ever created a temporary table just checked the syntax and it says

Although the syntax of CREATE TEMPORARY TABLE resembles that of the SQL standard, the effect is not the same. In the standard, temporary tables are defined just once and automatically exist (starting with empty contents) in every session that needs them. PostgreSQL instead requires each session to issue its own CREATE TEMPORARY TABLE command for each temporary table to be used. This allows different sessions to use the same temporary table name for different purposes, whereas the standard's approach constrains all instances of a given temporary table name to have the same table structure.

Mysql also follows the ansi syntax but behaviour is the same as in postgresql. sql server also behaves the same but the syntax is different

CREATE TABLE #HelloWorld (
    ID INT IDENTITY,
    Description VARCHAR(1000)
)

Temporary tables are identified by a # prefix to the name.

Limit and Offset

the last version of MSSQL that i worked with was SQL Server 2000. That didn't have the LIMIT clause but had something else; TOP. If you wanted to use an OFFSET you had tie yourself up in knots. While I was away they had introduced window functions, so you can now use the ROW_NUMBER partition function, which made things slightly easier.

SELECT  *
FROM    (
        SELECT  TOP 20
                t.*, ROW_NUMBER() OVER (ORDER BY field1) AS rn
        FROM    table1 t
        ORDER BY
                field1
        ) t
WHERE   rn > 10

 

In 2012, they apparently introduced OFFSET and FETCH keywords  which is very different to offset in say postgresql. And the FETCH keyword is an unnecessary complicated way for doing LIMIT,OFFSET, complicated but at least it works. Here is a query on Northwinds

SELECT * FROM customers ORDER BY contactname OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY

It must be added that LIMIT is not part of ANSI SQL but it's very common.

 

 

 

Dec. 24, 2016, 3:02 p.m. » Tagged: sql , partition , microsoft

Changing the backend db of a live django app.

I am feeling lucky.

In theory if you use django, changing your RDBS from say sqlite to mysql is a breeze. Simple as generating a dump of your existing data with ./manage.py dumpdata, change database connection string in settings.py and perform  a./manage.py migrate on the new backend. Now you can import the previously exported data through ./manage.py loaddata . Well that's the theory but in practice there's always a glitch.

Squash Migrations

The ./manage.py migrate step on the new database invariably throws an error or two dozen. Squashing migrations often works. The idea behind squash is to take a series of create and alter statements and combine them together into one single create statement. That doesn't always work either. Then the simplest fix is to delete all the migrations from each app's migrations/ folder.  Then a fresh ./manage.py makemigrations followed by ./manage.py migrate . Now even if your luck didn't hold the first time, you are still likely to end up with a newly created database on your hot new server. You can now use the loaddata management command to put the data back in.

The migration can stil fail in some edge cases. For example you may find that some of the model fields you have used such as ArrayField)are incompatible with the new RDBMS you are moving to. I leave you to solve that yourself with the reminder that ArrayField should never have been used in the first place. And the moral of the story is that you shouldn't rely too heavily on non standard vendor specific features and extensions present in your RDBMS.

 

Odo

Anyone who has ever tried to use Django's loaddata command can tell you that it will choke on the django Content Types app. Once you get past that hurdle, you will still find all sorts of errors popping up. Of course you can use MySql's SELECT INTO OUTFILE or Postgresql's COPY command to make the import export process less painfull but the only catch is if you have a large number of models, you will find yourself doing a lot of typing. Enter Odo.

import os, sys

if __name__ == '__main__': #pragma nocover
    # Setup environ
    sys.path.append(os.getcwd())

    os.environ.setdefault("DJANGO_SETTINGS_MODULE", "myapp.settings")

    import django
    django.setup()

from odo import odo
from django.apps import apps

for m in apps.get_models():
    odo("sqlite://db.sqlite3::{0}".format(m._meta.db_table),"/tmp/{0}.csv".format(m._meta.db_table))

The above code will export data from every single table in your database into a set of CSV files using sqlite's .output command for the export, while most home brewed exporters would save the data line by line. Right now, you can import these into another database right? What if I told you that you can actually import export without intermediate CSV dumps? yep, quite possible but your milage might vary.

Dec. 22, 2016, 8:37 a.m. » Tagged: odo , python , django , sqlite , mysql

Database clients for Microsft SQL server on linux.

TLDR; Need to connect to microsoft sql server from your linux desktop? DBeaver maybe your best bet.

sqlcmd

My opinion of sqlcmd is that it sucks like most everything else by Microsoft. I am no fan of their technology but sometime in my line of work I do have close encounters with them. I try to avoid it as much as possible but sometimes , there's no choice. Microsoft SQL server is a product that I need to work with every once in a whie.  Rather than sqlcmd, I thought to try out VS Code to connect to the sql server. Unfortunately I had to upgrade my Linux Mint Version due partly to inistallation conflicts that sqlcmd had with it (Mint Rosa). But what do I get when I try to run the sql-server extension on VS Code?

Failed to load sql tools service 

HeidiSQL

This is a known issue with Mint 18. It will probably be fixed soon but rather than waiting for it, I thought to try our HeidiSQL, which works under wine. I am perfectly happy with using psql client for postgersql and the mysql console for mysql but having a single GUI that can connect to all three does have it's advantages too. But it will apparently not work out of the box with mssql 

Once you install native_mdac and mdac28 you need to close and open heidisql again. Oh, one more thing, it's heidiSQL portable that worked for me rather than the standard download. The standard download complained about a missing mysql.dll but the portable version worked with both mysql and mssql but not so postgresql. Heidi would complain about a missing libpq.dll which is there in the same folder at heideisql.exe. Typing `wine heidisql.exe` from the command line reveals:

err:module:import_dll Library MSVCR120.dll (which is needed by L"C:\\heidi\\LIBEAY32.dll") not found
err:module:import_dll Library LIBEAY32.dll (which is needed by L"C:\\heidi\\SSLEAY32.dll") not found
err:module:import_dll Library MSVCR120.dll (which is needed by L"C:\\heidi\\SSLEAY32.dll") not found
err:module:import_dll Library SSLEAY32.dll (which is needed by L"C:\\heidi\\libpq.dll") not found
err:module:import_dll Library MSVCR120.dll (which is needed by L"C:\\heidi\\LIBEAY32.dll") not found
err:module:import_dll Library LIBEAY32.dll (which is needed by L"C:\\heidi\\libpq.dll") not found
err:module:import_dll Library MSVCR120.dll (which is needed by L"C:\\heidi\\libpq.dll") not found

Apparently this can be fixed by installing vcrun13 in winetricks, but winetricks just hangs on it without producing any output.

DBeaver

So I decided to ditch all these and switch to DBeaver. DBeaver is JDBC and automatically downloads the driver. So it works with practically any RDBMS and CSV files too! DBeaver also has the advantage being compatible with almost any SQL or noSQL database. 

Dec. 17, 2016, 1:03 a.m. » Tagged: heidisql , sql-server , winetricks , wine , DBeaver , sqlcmd

Mint Upgrade

I hate updating my OS. I only do so when I am left with no choice, like when so many packages conflicts appear as they are doing now. This time around the conflicts are related Oracle and SQL Server clients on Mint 17.3 (Ubuntu 14.04). In the old days upgrading mint was particularly diffcult because it doesn't have the 'dist upgrade' option found in other ubuntu deravatives. But luckily mintupgrade has appeared on the scene in recent times. It's rather slow but at least the computer is usable while the upgrade process is running. Whatever method you use for the upgrade, there are always one or two post upgrade issues. This is what Mint Sara (18) (Ubuntu 16.04)) came up with.

missing datetime in python.

Trying to start the django dev server with ./manage.py shell and this is the result:

Traceback (most recent call last):
  File "./manage.py", line 8, in <module>
    from django.core.management import execute_from_command_line
  File "/usr/local/virtual-django-19/local/lib/python2.7/site-packages/django/__init__.py", line 1, in <module>
    from django.utils.version import get_version
  File "/usr/local/virtual-django-19/local/lib/python2.7/site-packages/django/utils/version.py", line 3, in <module>

    import datetime
ImportError: No module named datetime

The solution apparently is to replace the python binary in the virtualenv. I still haven't figured out why exactly that is so, but it certainly worked.

gvim and python

Error detected while processing /home/raditha/.vimrc: 
line    3: 
E319: Sorry, the command is not available in this version: py << EOF

What solved it was a comment on an askubuntu answer

Python3 is default interpreter for Ubuntu 16.04. If this message appears as a result of using pycommand, replace it by Python 3 equivalent 

gvim, python and execfile

That still leaves me with the following problem:

line   12:
Traceback (most recent call last):
  File "<string>", line 8, in <module>
NameError: name 'execfile' is not defined

The fix is described here: http://stackoverflow.com/a/6357418/267540

After both these changes, the first part of my .vimrc looks like this

py3 << EOF
import os.path
import sys
import vim
if 'VIRTUAL_ENV' in os.environ:
    project_base_dir = os.environ['VIRTUAL_ENV']
    sys.path.insert(0, project_base_dir)
    activate_this = os.path.join(project_base_dir, 'bin/activate_this.py')
    exec(compile(open(activate_this,"rb").read(), activate_this, 'exec'), dict(__file__=activate_this))
EOF

Interestingly both the above vim/gvim related errors appear only when gvim is started after the virtualenv has been activated. 

Dec. 15, 2016, 1:33 p.m. » Tagged: mint , vim , gvim , python

SQL Server on Linux

It's been more than 15 years since I last opened a connection to a microsoft SQL Server. Since Mircrosoft has embracced open source in general and linux in particular it seems like a good time to try it once again. Particularly since you can now run SQL server on linux thanks to docker.

I setup sql server on my linux machine using the official docker image provided by microsoft. However when ever the server was started according to the instructions the container would run for a few seconds and then shut down. If you try to connect to it during the brief period when it's alive, you get an error like this from sqlcmd:

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server..
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider: Error code 0x68.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable to establish connection.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable to establish connection due to prelogin failure.

Searched around for quite a bit but no solid answer was found. Then I re read the instructions again. Apparently this innocuous looking bit is rather important:

  • A strong system administrator (SA) password: At least 8 characters including uppercase, lowercase letters, base-10 digits and/or non-alphanumeric symbols.

If you specify too short a password the server apparently doesn't start or dies off immediately. When you run a docker image as a daemon, the container also shuts down if there are no processes running inside it. Rather lame but there are you.

Update:

To make the data persist you have to pass the container a path on the local file system for read and write. The official document says pass the parameter like what's given below, but it forgets to tell you what exactly should go into the host directory flag.

-v <host directory>:/var/opt/mssql

Almost anything will do. I used -v /var/opt/mssql:/var/opt/mssql

Dec. 13, 2016, 3:48 p.m. » Tagged: sql-server , microsoft , docker

Asp.Net in 24 hours.

Now it's time for the die hard Django fan to learn ASP.Net. Once upon a long ago, around the time that Asp.Net was born I worked with a guy who had practially every single Microsoft certificate they had on offer. We were constantly arguing about which is better PHP or ASP. Those days PHP was crap (and now it's worse) but it was still a whole lot better than ASP. So much so that when my friend started out on his own, his shop only dealt with PHP and refused to touch Asp or ASP.Net. Now things have come a full circle. 

Yesterday, I made an attempt to learn C# in 24 hours. Heck it didn't take 24 hours, mostly because the language is a lot like Java and the parts that are not like java seems like C++ or python. As Thameera pointed out in a comment yesterday, it's been having Lambda's for years.

Having learnt the basic stuff, I even wrote a bit of code. It was to parse a CSV, transform it and load it into an SQLite database. Of course I know how to load a CSV directly into SQLite (I generally prefer sqlitemon for this rather than the command line interface). But this data set needed some transformations, beside the whole point is to write some code!

Apparently System.Data.Sqlite isn't available for Dot Net Core, you need to use Microsoft.Data.Sqlite and the latter is sparesly documented, but no fear, it seems to be compatible with the former.

So on to Asp.Net , let's hope to finish it by this time tommorrow 

Dec. 7, 2016, 1:10 a.m. » Tagged: csharp , asp.net , django