Code, the universe and everything
Friday, January 27, 2017
Cakephp 3 console and shared hosting
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 |
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*?
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%)
GO
Holy crap, that actually worked somehow…
Can we put data in it though?
use hiddendb
go
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'.
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
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
)
go
create index idx_randRowsId
on dbo.randomRows(id)
go
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)
go
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 * 162) + 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)
go
dbcc page(testdb, 1,275,1)
go
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
go
alter database testdb set single_user
go
dbcc checkdb(testdb, repair) with no_infomsgs
go
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
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 2057 and -4.
The error has been repaired.
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.
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