Code, the universe and everything

Friday, January 27, 2017

Cakephp 3 console and shared hosting

Quick one:

If you ever need to run a cake console app, and your site is on some shared hosting provider with only cpanel-ish access, running cake commands doesn't work *exactly* as advertised in their documentation. I managed to get my scripts running with the following syntax:

cd /home/[your_dir1]/public_html/yourSiteName.com/ ; /usr/local/bin/php bin/cake.php [app] [appFunction]

Hope that helps someone somewhere!

Wednesday, May 7, 2014

Simple plots in Microsoft Sho

MS Sho is *really* kool.

Been looking for a while for something like this. With all the fuss being made about data and stats these days it seemed odd to me that the MS camp didn't put out some r/ python-esque data analysis tools. Turns out I just wasn't looking in the right places. "Sho: the .NET Playground for Data" is a nifty, .net-friendly data analysis environment from MS research. It has a kool, R-feeling, console that makes for speedy data exploration.

From the start menu

Sho console

So first of all, data. Getting it into Sho is super easy.

Generating data: Nifty random functions to generate data:

>>> x = rand(20)
>>> x
[ 0.9505 0.7515 0.7579 0.6926 0.7209 0.2979 0.3866 0.4590 ...4 cols... 0.5072 0.6721 0.9819 0.2756 0.0660 0.1953 0.9479 0.1866]

Reading data from a delimited file:

Use 'dlmreadJaggedArray' with a specified delimiter

Reading data from SQL server (of course!):

Easiest thing ever - connecting to a local, default instance, using windows authentication,

Ok so now some simple plots:

>>> x = rand(20)
>>> y = rand(20)
>>> plot(x)

>>> plot(x,'.')

>>> plot(x,y,'.')

>>> bar(x)

>>> hist(x)

Notice the awesome "number of bins" slider!!

so these commands pull up a new window with the plots, however!! you can use some 'show' commands to view the plots in the console itself (except for the histogram :/ )

So that's some Sho basics, but what's kool is that you can do all this from other .net languages!!

Here's how you make a histogram from c#:

DoubleArray y1 = ArrayRandom.RandomDoubleArray(20);

//histogram control
ShoHistControl hist1 = new ShoHistControl(y1, 6);
hist1.Width = 620;
hist1.Height = 460;
hist1.Location = new Point(1, 1);

//form to display histogram control
Form f1 = new Form();
f1.Text = "Histogram 1";
f1.Width = 640;
f1.Height = 480;
f1.MaximizeBox = false;

f1.Controls.Add(hist1);

f1.ShowDialog();

produces this:

basic scatter/ line/ bar plots are easier, you don't need to create a form to contain it, so this is all you need:

int[] x = { 1, 2, 2, 8, 2, 3, 2, 1 };
int[] y = { 1, 2, 3, 4, 5, 6, 7, 8 };

IFigure fig1 = ShoPlotHelper.Figure();

fig1.Plot(x, y, ".");

to produce this!:

So long story short, MS Sho is super fun and I for one am going to continue finding reasons to use it. One of the biggest advantages in my opinion is now I have a simple option for making quick, stand-alone dashboard applications which are easy to distribute to clients, something which was trickier to do with R.

Anyway folks, hope this is helpful to someone, any questions/ comments/ corrections are surely welcome.

oh P.S!! the guys at MS research did a great job of documenting all this, you can find out everything you need to know about Sho at their documentation page

update:

Plot from powershell!:

add-type -Path "C:\Program Files (x86)\Sho 2.1\bin\ShoArray.dll"
add-type -Path "C:\Program Files (x86)\Sho 2.1\bin\ShoViz.dll"

$r = 2.7
$x = 0.7
$x1 = 0

#number of iterations
$i=100

$i1 = $i

$points = [ShoNS.Array.FloatArray]::Zeros($i)
$xAxis = @()

while($i -gt 0){
$x1 = $r * ($x - ($x * $x) )
$points[$i1-$i] = $x1
$xAxis += @($i1-$i)
$x = [System.Decimal]::Parse($x1)
$i --
}

$vis = [ShoNS.Visualization.ShoPlotHelper]::Figure()

$vis.Plot($xAxis, $points)

Friday, December 23, 2011

Put it where?

disclaimer: this is kind of pointless. read at your own
risk

--------------------------------------------------------------------------------------

So let’s put a database in an alternate data stream. for some reason. Furthermore let’s make it all sneaky and make it look like some random unsuspicious system file, like thumbs.db :

CREATE DATABASE [hiddendb] ON PRIMARY

( NAME = N'hiddendb', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\thumbs.db:mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'hiddendb_log', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\thumbs.db:ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

Holy crap, that actually worked somehow…

Can we put data in it though?

use hiddendb

select *

into dbo.randomrows

from spt.dbo.exported, spt.conversion.modeloff --some other tables

(343917 row(s) affected)

yup, seems so. (column names have been hidden to protect innocent).

But wait…

My thumbs.db file still only shows up as 0 KB! it totally isn't though, you can see it's real size in sys.database_files

So anyway that gets me thinking….said database is in an alternate data stream. SQL server uses alternate data streams when running checkdb. How would that work with this fancy db?

Msg 1823, Level 16, State 2, Line 1

A database snapshot cannot be created because it failed to start.

Msg 5123, Level 16, State 1, Line 1

CREATE FILE encountered operating system error 123(The filename, directory name, or volume label syntax is incorrect.) while attempting to open or create the physical file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\thumbs.db:mdf:MSSQL_DBCC26'.

Msg 7928, Level 16, State 1, Line 1

The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.

DBCC results for 'hiddendb'.

so that was fun! hope you find this interesting!

Saturday, November 26, 2011

the shy mssqlsystemresource db

Ever wondered how Microsoft hid mssqlsystemresource in SQL server?

Well I did!...let’s put on our reversing hats and figure this out..

Ok so first of all what do we know? It’s hidden..it doesn’t show up in the object explorer or in sys.databases

So how does sql server come up with that list of databases to display in the object explorer?

Let’s run a trace to find out!

Ok so we see the object explorer gets a list of databases from master.sys.databases.

So how does master.sys.databases get a list of databases? And how does it manage to miss the mssqlsystemresource database?

To figure out what sys.databases is made of, we can look in the mssqlsystemresource database itself.

To look at the contents of mssqlsystemresource, we need to do the following:

1. Stop the SQL server service

2. Copy the mdf and ldf files from the binn directory to some other location

3. Attach these database files with a different name

Then!

Look at the source for the sys.databases view, you’ll notice the following restriction (line 97 –ish)

WHERE d.id < 0x7fff

While selecting from the sys.sysdbreg table (and others).

**note: 7fff = 32767

According to (http://msdn.microsoft.com/en-us/library/ms179503.aspx)

This table exists in the master database, and we need to be connected using DAC in order to view it.

Sure enough, the mssqlsystemresource table is registered with a database ID of 32767, which is why it does not show up in sys.databases, and therefore neither in object explorer.

and now we know!

Sunday, October 16, 2011

Breaking a non-clustered index

So let’s say you want to get a better understanding of how DBCC checkdb works, but you unfortunately only have no corrupted databases to play with. One option is to create your own broken database!

We’ll be targeting non-clustered indexes specifically, so that we can repair the database without data loss.

First, we create a test database:

create database testdb

then we create a table, and a non-clustered index to go with it

create table dbo.randomRows

(

id int not null,

name nvarchar(20),

value int

)

create index idx_randRowsId

on dbo.randomRows(id)

now let’s insert some test data

insert into dbo.randomRows (id, name, value)

values

(1, 'joan',100),

(2, 'jason',10),

(3, 'chocolates',40),

(4, 'tea', 50)

so now, we’re at the point where we can start breaking stuff

first thing we need to know is in which ‘page’ the index is physically stored. Pages are the ‘fundamental unit of storage’ in SQL Server. A SQL server database is made up of a number of physical files. Data files exist on the file system, within these data files, space is organized into a series of 8 KB pages.

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

To find the page number the index is physically stored in, we can query the system view “sys.system_internals_allocation_units

“

But before we can find the index in that view, we first need to find it’s partition ID using sys.partitions

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

From the table name, we can derive the partition, based on the object_id, like so:

select partition_id

from sys.partitions a, sys.indexes b

where

a.object_id=b.object_id

and a.index_id=b.index_id

and a.object_id=object_id('dbo.randomRows')

and b.type=2

we can then join to the sys.system_internals_allocation_units table based on this partition_id=container_id, and the column of interest to us is ‘first_page’. The following query gives us the information we need:

select first_page

from sys.partitions a, sys.system_internals_allocation_units b

where a.partition_id=b.container_id

and object_id=object_id('dbo.randomRows')

and index_id <> 0

in my case, the result of this query gave the following:

0x130100000100

To derive the actual page number from this value, we need to first reverse the bytes to get the actual page number,

So from 130100000100, we get:

00 01 00 00 01 13

The first 4 digits gives the file number, in this case 1, and the rest of the digits is the page number

113 hex =(1 * 16²) + 16 + 3 = page number 275

Another way to page number is with some sub-string-ery, like so:

select

Convert (int, SubString (first_page, 4, 1) +

SubString (first_page, 3, 1) +

SubString (first_page, 2, 1) +

SubString (first_page, 1, 1))

from sys.partitions a, sys.system_internals_allocation_units b

where a.partition_id=b.container_id

and object_id=object_id('dbo.randomRows')

and index_id <> 0

which also returned 275

to view more information about this page, we can use the DBCC page command - http://support.microsoft.com/kb/83065

to view the results of DBCC page, we need to turn on trace flag 3604.

dbcc traceon(3604)

dbcc page(testdb, 1,275,1)

which gives the following results:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

PAGE: (1:275)

BUFFER:

BUF @0x046C0750

bpage = 0x0FF3A000 bhash = 0x00000000 bpageno = (1:275)

bdbid = 7 breferences = 0 bcputicks = 0

bsampleCount = 0 bUse1 = 47970 bstat = 0xb

blog = 0x212121cc bnext = 0x00000000

PAGE HEADER:

Page @0x0FF3A000

m_pageId = (1:275) m_headerVersion = 1 m_type = 2

m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000

m_objId (AllocUnitId.idObj) = 85 m_indexId (AllocUnitId.idInd) = 256

Metadata: AllocUnitId = 72057594043498496

Metadata: PartitionId = 72057594039107584 Metadata: IndexId = 2

Metadata: ObjectId = 245575913 m_prevPage = (0:0) m_nextPage = (0:0)

pminlen = 13 m_slotCnt = 4 m_freeCnt = 8024

m_freeData = 160 m_reservedCnt = 0 m_lsn = (30:231:48)

m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0

m_tornBits = 0 DB Frag ID = 1

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED

PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED

ML (1:7) = NOT MIN_LOGGED

DATA:

Slot 0, Offset 0x60, Length 16, DumpStyle BYTE

Record Type = INDEX_RECORD Record Attributes = NULL_BITMAP Record Size = 16

Memory Dump @0x14F8C060

00000000: 16010000 00110100 00010000 00020000 ††††††††††................

Slot 1, Offset 0x70, Length 16, DumpStyle BYTE

Record Type = INDEX_RECORD Record Attributes = NULL_BITMAP Record Size = 16

Memory Dump @0x14F8C070

00000000: 16020000 00110100 00010001 00020000 ††††††††††................

Slot 2, Offset 0x80, Length 16, DumpStyle BYTE

Record Type = INDEX_RECORD Record Attributes = NULL_BITMAP Record Size = 16

Memory Dump @0x14F8C080

00000000: 16030000 00110100 00010002 00020000 ††††††††††................

Slot 3, Offset 0x90, Length 16, DumpStyle BYTE

Record Type = INDEX_RECORD Record Attributes = NULL_BITMAP Record Size = 16

Memory Dump @0x14F8C090

00000000: 16040000 00110100 00010003 00020000 ††††††††††................

OFFSET TABLE:

Row - Offset

3 (0x3) - 144 (0x90)

2 (0x2) - 128 (0x80)

1 (0x1) - 112 (0x70)

0 (0x0) - 96 (0x60)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

This is enough information for us to break the index. For any one of the index records listed, we can just overwrite some data .

We know the page address is 275, and we know pages are 8 KB in size = 8192 bytes. We can therefore find address within the physical file to modify, which would damage this index; 275 * 8192 =2252800.

Once we take the database offline, you can use a simple hex editor to navigate to address 2252800 and make your adjustments.

As an additional check, you can compare the memory dumps for individual index records (from DBCC page command) to see if they match patterns in the data file opened in your hex editor (btw I’m using Xvi32).

You can make literally any change, for this file I’ll just change that 16 hex value to 17

Save the file, and bring your database back online. Once that’s done, you can go ahead and run your DBCC command to check for errors:

dbcc checkdb(testdb) with no_infomsgs

you might get something similar to the following:

Msg 8939, Level 16, State 98, Line 1

Table error: Object ID 245575913, index ID 2, partition ID 72057594039107584, alloc unit ID 72057594043498496 (type In-row data), page (1:275). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.

Msg 8928, Level 16, State 1, Line 1

Object ID 245575913, index ID 2, partition ID 72057594039107584, alloc unit ID 72057594043498496 (type In-row data): Page (1:275) could not be processed. See other errors for details.

Msg 8980, Level 16, State 1, Line 1

Table error: Object ID 245575913, index ID 2, partition ID 72057594039107584, alloc unit ID 72057594043498496 (type In-row data). Index node page (0:0), slot 0 refers to child page (1:275) and previous child (0:0), but they were not encountered.

CHECKDB found 0 allocation errors and 3 consistency errors in table 'randomRows' (object ID 245575913).

CHECKDB found 0 allocation errors and 3 consistency errors in database 'testdb'.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (testdb).

Success! , our database is broken. Furthermore, we only broke a non-clustered index, so repairing this error should not cause us to lose any data.

So something like this:

use master

alter database testdb set single_user

dbcc checkdb(testdb, repair) with no_infomsgs

should produce something similar to this:

Repair: The Nonclustered index successfully rebuilt for the object "dbo.randomRows, idx_randRowsId" in database "testdb".

Repair: The page (1:275) has been deallocated from object ID 245575913, index ID 2, partition ID 72057594039107584, alloc unit ID 72057594043498496 (type In-row data).

Msg 8945, Level 16, State 1, Line 1

Table error: Object ID 245575913, index ID 2 will be rebuilt.

The error has been repaired.

Msg 8928, Level 16, State 1, Line 1

Object ID 245575913, index ID 2, partition ID 72057594039107584, alloc unit ID 72057594043498496 (type In-row data): Page (1:275) could not be processed. See other errors for details.

The error has been repaired.

Msg 8939, Level 16, State 98, Line 1

The error has been repaired.

Msg 8980, Level 16, State 1, Line 1

The error has been repaired.

CHECKDB found 0 allocation errors and 3 consistency errors in table 'randomRows' (object ID 245575913).

CHECKDB fixed 0 allocation errors and 3 consistency errors in table 'randomRows' (object ID 245575913).

CHECKDB found 0 allocation errors and 3 consistency errors in database 'testdb'.

CHECKDB fixed 0 allocation errors and 3 consistency errors in database 'testdb'.

And this command:

dbcc checkdb(testdb) with no_infomsgs

should give us no errors, like so:

Command(s) completed successfully.

so that's more or less it. Have fun!

btw to create this post, I used Microsoft SQL Server "Denali" (CTP3) - 11.0.1440.19 (Intel X86) and good old, reliable XVI32

**edit - modified the "locating the partition" query to only show non-clustered indexes for the given object

Friday, January 27, 2017

Cakephp 3 console and shared hosting

Wednesday, May 7, 2014

Simple plots in Microsoft Sho

Friday, December 23, 2011

Put it *where*?

Saturday, November 26, 2011

the shy mssqlsystemresource db

Sunday, October 16, 2011

Breaking a non-clustered index

Put it where?