Houston, we have a problem.

Yes. This has happened to me before. I think it’s funniest thing to see a BSOD on a Mac. The other funny is when you see a BSOD in a VM. Kind of funny anyway. Hopefully its not production.

Intermediate SQL – Post 9 – DROP Column, Table, and Database

For this little lesson, we will create a database, a couple of tables, add some data to those tables, alter them, query them, and then drop them.

I’ll get to the syntax of each of those as we go here, but for the CREATE and ALTER portions, you already know from previous lessons – so here we go.

CREATE Database Linux
GO
 
USE Linux
GO
 
CREATE TABLE Distributions
(
ID			int PRIMARY KEY IDENTITY NOT NULL,
Name			varchar (255),
ReleaseDate		date,
VersionNumber	        varchar (16),
CodeName		varchar (25)
)
GO
 
SET NOCOUNT ON
 
INSERT INTO Distributions (Name, ReleaseDate, VersionNumber, CodeName)
VALUES ('Ubuntu','12/10/2012', '12.10', 'Quantal')
INSERT INTO Distributions (Name, ReleaseDate, VersionNumber, CodeName)
VALUES ('Ubuntu','12/04/2012', '12.04', 'Precise')
INSERT INTO Distributions (Name, ReleaseDate, VersionNumber, CodeName)
VALUES ('Ubuntu','10/01/2011', '11.10', 'Oneiric')
INSERT INTO Distributions (Name, ReleaseDate, VersionNumber, CodeName)
VALUES ('Mint','12/10/2012', '14', 'Nadya')
INSERT INTO Distributions (Name, ReleaseDate, VersionNumber, CodeName)
VALUES ('Mint','11/04/2012', '13', 'Maya')
GO
 
ALTER TABLE Distributions
DROP COLUMN ReleaseDate
GO
 
DROP TABLE Distributions
GO
 
USE msdb
GO
 
DROP DATABASE Linux
GO

Alright – so with that, we created a table and then added some random stuff to that table. After that we altered the table and dropped the column called “Release Date”. Note that dropping a column removes the entire column and all data in it. This is different than a delete statement. A delete statement to remove the values from one row or to make every single row NULL for a particular column is a totally different query. This removes the entire column and all data in it, so that our table no longer has that column. If you wanted to delete a single column from a row, you would do this:

DELETE ReleaseDate
FROM Distributions
WHERE ID = 3

If you wanted to set all columns to NULL, you could do this:

DELETE ReleaseDate
FROM Distributions

Neither of these delete the column however, it just sets all the data in them to NULL – if you want to remove an entire column, you need to DROP it using ALTER TABLE.

The next one you see is that we dropped the table. This means that the entire table gets removed from the database. Once again, it’s not DELETE. It’s DROP TABLE.

The last drop you see is to DROP DATABASE. This one in particular you have to be careful with (for obvious reasons). Firstly, no one can be using it. If they are using it, you’ll get a warning. Since I assumed that you created this database and you are the only one using it, I just decided to USE msdb, and then drop it. If there are other open connections on that database, you need to set it offline to drop it.

ALTER DATABASE Linux
SET OFFLINE
WITH ROLLBACK IMMEDIATE
GO
 
DROP DATABASE Linux
GO

Just be warned – this will force disconnect anyone who is connected and then drop the database. This means deleting all tables and data in those tables. If you don’t have a backup, all that data is gone. So once again, be extremely careful with this. Ask your system admin if you’re not comfortable with it, and if it’s your own server, then use your own judgement.

Intermediate SQL – Post 8 – ALTER TABLE

Well, finally I can get  back to writing some SQL goodies. I have to say I’m pretty excited.

We left off creating some tables – and those tables are all good. Let’s say now that we need to change something in a table – such as how many characters or the data type of a column that we created (we’ll get to datatypes later).

Let’s do this quick so we can see what we are looking at:

USE AdventureWorks2012
GO
 
SELECT DISTINCT City FROM Person.Address
WHERE len(City) = (SELECT max(len(City)) FROM Person.Address)
 
SELECT max(len(ltrim(rtrim(City)))) FROM Person.Address

That first query will give us the name of the longest city in that column, and the second will tell us how many characters it is. For times sake, let’s say it’s a 21 character long string. That’s all fine and dandy, considering City is a varchar (60) field.

But what we need to change a city to something like Taumatawhakatangihangakoauauotamateapokaiwhenuakitanatahu (which, incidentally, is a real place)

If we run this query:

UPDATE Person.Address
	SET City = 'Taumatawhakatangihangakoauauotamateaturipukakapikimaungahoronukupokaiwhenuakitanatahu', ModifiedDate = getdate()
WHERE AddressID = '26158'

We will get an error back that looks like this:

Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.

Oh noes, what do we do? That error usually means your column won’t fit the data that you’re trying to put in to it. In this case we are attempting to stick a 85 letter (character) name in to a field that is only 60 characters long. No bueno.

What we have to do to make this work is alter our table. A simple query can do this. The syntax of ALTER TABLE is right here:

ALTER TABLE tablename
ALTER COLUMN columnname datatype

For example, to make this city fit, we could do this:

ALTER TABLE Person.Address
ALTER Column City varchar (90)

You may get an error when you do this though – something like this:

Msg 5074, Level 16, State 1, Line 1
The index 'IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode' is dependent on column 'City'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN City failed because one or more objects access this column.

That means that this table has an Index on it and we can’t do anything to the column that is indexed unless we remove and recreate the index. We’ll get in to indexes later. For now you can see here:

 

What we want to do is “Drop and Create to”. You can either have it on your clip board and paste it in your current query window or have it open a new query window. Usually I do the second one.

The first thing we want to do is run the “Drop Index Portion”.

USE [AdventureWorks2012]
GO
 
EXEC sys.sp_dropextendedproperty @name=N'MS_Description' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'Address', @level2type=N'INDEX',@level2name=N'IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode'
 
GO
 
/****** Object:  Index [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]    Script Date: 11/29/2012 12:11:49 PM ******/
DROP INDEX [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] ON [Person].[Address]
GO

No we can run our “Alter Table” statement:

ALTER TABLE Person.Address
ALTER Column City varchar (90)

And then we want to re-create the index:

/****** Object:  Index [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]    Script Date: 11/29/2012 12:11:49 PM ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] ON [Person].[Address]
(
	[AddressLine1] ASC,
	[AddressLine2] ASC,
	[City] ASC,
	[StateProvinceID] ASC,
	[PostalCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Nonclustered index.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'Address', @level2type=N'INDEX',@level2name=N'IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode'
GO

Once all that is done, we can successfully change the city name for our huge city name:

UPDATE Person.Address
	SET City = 'Taumatawhakatangihangakoauauotamateaturipukakapikimaungahoronukupokaiwhenuakitanatahu', ModifiedDate = getdate()
WHERE AddressID = '26158'

And there we go.

BFQ and Linux Kernel

I’ve done some pretty thorough testing on Linux with different I/O Schedulers and different options while trying to find the best performance for a desktop / work based system, and with all that testing, I’ve usually found that keeping the kernel light and stock is usually pretty good. Recently I started messing around with BFQ again, and I have to say that I am pretty impressed. All new kernels that are in the repo here are going to have the BFQ scheduler patched in and as the default scheduler.

Jobs

Sometimes people like to ask me what I do for a job. The truth is that I don’t really even know what my job entails – it basically revolves around keeping uptime on some web services functional at all times (24 x 7). That means network, servers, and infrastructure up at all times.

People before me designed a somewhat reliable system to do that, including network redundancy, firewall / router failover, load balancers, system monitoring applications and servers dedicated to that very purpose, multiple instances of servers that host different web applications, disaster recovery contingency plans, etc…

All in all we have a pretty stable system, though we do suffer from outages once in a while from a few things that really aren’t really out of our control, but things that we haven’t thought through clearly enough and that we haven’t seen all angles on. Some of these things are failover of applications like Tomcat that for some reason fail to do so cleanly (in that there is a large hiccup in the response time before it comes back up).

I’d like to think that somehow, at my  company, I am a benefit, because I bring a knowledge of Linux that my most of my co-workers don’t share. I think one of them knows quite a bit about it, but isn’t an expert. I don’t really consider myself an expert as there is a ton more to learn, but it’s nice to know that you are needed I suppose.

Some things that I have designed / touched on are creating a few servers with some  unique functions (that I have probably even spelled out here): I manage most of Nagios / OpsView installs, Unitrends Enterprise Backup (which does come highly recommended {perhaps I’ll write a post on this later}), a yum update server / repository, and a few other little things involving MySQL, Apache, and php.

Our inventory of servers isn’t exactly getting smaller – the databases we house are about 800 GB or so and always growing (a lot of this stuff is junk / temp tables that we use for daily work), but the main databases are well over 400GB. The only place we really can go is up.

Some of my favorite things I have worked with are these: The SonicWall Enterprise Firewalls – these, coupled with site to site VLANs and it’s VPN work like a champ, doing a ton of work all in one simple 1U rack space. I also really like our Compellent SAN – though I do question the integrity of our drives…

To expound on that, I used to be a huge Seagate hard drive fan. I had a ton of success with them in the past and I have always though they performed extremely well … that is until I found a Compellent SAN filled with them and saw how many of them die per year and I have since gotten a bad taste in my mouth. It may be the nature of these drives to blow out after so many reads and writes (our drives are extremely active), however, the amount of drives we have had go bad over the last 6 months is unacceptable.

In any event, I’ve also really enjoyed working with VMware. Obviously this is a given, but we use ESXi 5 – and I think it is an extremely robust program. As far as we are concerned, ESXi has never been a problem – some of the things that RUN on it may have an issue, but the actual application that runs on the servers has been borderline flawless.

Currently, my company is also exploring Hyper-V. I have played with it a bit in the past and suffice to say that I really didn’t enjoy it much – mainly because it lacked Linux support. I know it’s a Microsoft product and that they really only want you running Microsoft products on it, but since Linux is (for the most part) open source, why not integrate it? Which, as a matter of fact, I am glad they did with the release of Hyper-V 2.0 on Server 2012. Hence, why we are taking a look at it.

On the side I do some web hosting on a Linux based server – I really enjoy it. Probably more than my day job, but unfortunately, it’s not something that provides much income at this point. I’d love to get more involved in it, but most of the people who I work with in a day don’t do web design projects.

In any event, I’m not sure what the point of this post was, maybe just to make a post. I feel bad that really all I have been doing is compiling kernels religiously and not really paying much attention to the SQL portion of the site. This will change as I have a lot more to get on with.

Kernel / SQL

I will be restarting all these projects as soon as this web host merge is complete – it’s just proving to be a lot more work than I thought it would be. Not to worry – I’ll be back up and writing in no time.

Migrating Web Hosts

This site may be down for a while over the next day or so (this includes the repository) – I am migrating web hosts and I will be using this domain as my primary name servers and while I get that set up, it may take a day or so to propagate to the interwebs.

Sorry about the inconvenience. It will be better for the future though.

Ubuntu 12.10 Released

Ubuntu 12.10 was released over the weekend. With it comes a lot of new cool stuff – XFCE 4.10 if you are in to Xubuntu for one (which is what I do most of my compiles on), Gnome 3.6 if you are in to Gnome – which is pretty sleek. They also made an official Gnome Remix, which I am pretty happy about.

I have to say that I’m not a fan of Unity at all, so whatever cool new changes took place with that, I am not interested.

My quick review is this: It looks like a bunch of new packages all rolled out at the same time in what they would call a distribution release. I think, so far, it looks pretty cool and it has been working for me.

I updated all my compiling VMs and Repository VMs to Xubuntu 12.10 and so far I am pretty pleased. XFCE looks a lot more polished.

This is a vid of Gnome 3.6.1 in action with Gnome Remix 12.10 – The music is kind of lame and the guy deff needs a faster computer.