MariaDB is a fork (offshoot) of MySQL, one of the most popular database management systems in the world. MariaDB was created by the creator of MySQL to address some of the limitations and shortcomings in MySQL, and is intended to be a drop in replacement for MySQL as well. At the request of Monty Widenius, creator of MySQL and MariaDB, I ported my best-selling MySQL Crash Course to focus specifically on MariaDB. The new book, MariaDB Crash Course, was just released and is now available for purchase.

Note: As already noted, MariaDB is based on MySQL. Indeed, it can be thought of as MySQL plus new features and improvements. In other words, if you have used MySQL then you already know how to use MariaDB. As such, if you have a copy of my MySQL Crash Course or my Sams Teach Yourself SQL In 10 Minutes, then you'll likely NOT want a copy of this new title.

At the request of Monty Widenius, creator of MySQL and now MariaDB, I ported my MySQL Crash Course to create a new book entitled MariaDB Crash Course. The book won't be out for a couple of months, but it is now available for pre-order.

I've been doing quite a bit of work with MariaDB, a drop-in replacement for MySQL (forked from the MySQL codebase by Monty Widenius, the creator of MySQL). One issue I ran into is that the current Windows installer does not create the Windows service that you generally would want to use. And because I had to (yet again) dig around today for the solution, I am posting it here for the next time I need it.

At a command prompt, go to the bin folder under the MariaDB installation folder, and then run:

You should see a message telling you that the service (named MySQL by default) was created.

A reader just pointed out to me that my my Sams Teach Yourself SQL In 10 Minutes is in Amazon.com's Top 10 lists in the databases, SQL, DBMSs, and databases categories. It's also #1 in the Relational Databases category! Cool! :-)

At the request of a reader, I created an OpenOffice Base version of the example database used in my Sams Teach Yourself SQL in 10 Minutes. The file can be downloaded from the book page.

To use this, you'll obviously need OpenOffice. And as it turns out, Base is actually a really nice database to experiment with, as it features a built-in screen that lets you simply enter and execute SQL. So, once you have OpenOffice installed, here's what you need to do:

• Open the book database with OpenOffice Base
• Select the Query tab on the left
• Click the "Create Query in SQL View" task to display the Query Design screen
• Type your SQL in the window
• Click the Run Query button (it has an image of a green check mark on top of 2 documents) to execute your SQL, you can also just press F5 to run
• Your results will be shown above your SQL code (unless you generate an error)

Thank you Roy (no last name provided) for the inspiration.

I just received three new translations of my books, two into Simplified Chinese and one into Korean:

• Sams Teach Yourself Microsoft SQL Server T-SQL In 10 Minutes is now available in Simplified Chinese
• MySQL Crash Course is now available in Simplified Chinese
• Sams Teach Yourself SQL In 10 Minutes is now available in Korean

Links to the new translated versions, as well as all other translations, are on the specific book pages.

I am particularity excited about the Korean translation, as this is my first book in Korean, bringing the number of languages that my books have been translated into to 16. Here is a full list of all the translations (that I am aware of).

I've discussed SQL injection attacks several times on this blog. But, judging by how many sites fall victim to these attacks, it's one of those topics that we can never discuss enough. Which is why I want to point out a new Adobe Developer Center article by Adobe Enterprise Developer Support Consultant Ryan Wagener entitled Secure your ColdFusion application against SQL injection attacks.

My Sams Teach Yourself SQL In 10 Minutes and Sams Teach Yourself Regular Expressions In 10 Minutes have both been published in Chinese by Turing Book. Links are on the books pages. And here is a full list of all book translations.

It's been two and a half years since I published my MySQL Crash Course (a MySQL specific adaptation of my Sams Teach Yourself SQL in 10 Minutes), and during this time the book has proven to be incredibly popular, especially with MySQL beginners. And today I was informed that MySQL Crash Course is now in its seventh printing! Seven printings in two and a half years, I think that's my new personal record.

(BTW, it's the same edition, so no need to buy another copy if you have one already).

George Poulose's Query Tools have long been a core part of my SQL tools and utilities collection. And George just let me know that he has released new Windows 64bit versions of his ODBC and ODO tools.

SQLite is a vital core component of newly released AIR, providing a local SQL data store for your desktop AIR applications. Dave McAllister has announced that Adobe has joined the SQLite Consortium, supporting the continued growth and improvements in SQLite.

Amazon.com has created a feature page highlighting the 3 new ColdFusion Web Application Construction Kit books (as well as SQL books that may be of interest to ColdFusion developers).

The Microsoft bookstore (on the Microsoft campus in Redmond, WA) has only sold Microsoft Press books to date. But, starting next week they will be selling select books published by other publishers. And apparently my Sams Teach Yourself Microsoft SQL Server T-SQL in 10 Minutes is one that they have selected to sell. So, any of you working for (or visiting) Microsoft, feel free to pick up a copy! :-)

MySQL has released a beta of a new version of their Java driver. As per this post, MySQL Connector/J 5.1.2 is a Type-IV pure-Java JDBC driver that is suitable for use with any MySQL version including MySQL-4.1, MySQL-5.0, MySQL-5.1 beta or the MySQL-6.0 Falcon alpha release. And this new driver contains code that automatically detects ColdFusion and then adjusts settings to optimize performance for ColdFusion use. Thank to Tom Jordahl for bringing this one to my attention.

Pinalkumar Dave is a DBA with extensive SQL Server (and ColdFusion) experience. I just stumbled upon his blog SQL Authority (via a link in a comment on my own blog) and am more than impressed by some of his SQL Server related posts. If you use SQL Server, then this is one blog you should add to your regular reading list.

After lots of rather painful delays, my new book Sams Teach Yourself SQL Server T-SQL in 10 Minutes is ready to ship. This book (which is the SQL Server version of my MySQL Crash Course) is based on my best-selling Sams Teach Yourself SQL in 10 Minutes and goes in to far more detail than the generic SQL book, and even includes coverage of new SQL Server 2005 functionality. Details, and a chapter listing, can be found on the book page.

Like many DBMSs, Apache Derby (included with ColdFusion 8) supports identity fields - fields that auto-increment each time a row is add. These are commonly used for primary key values, as the DBMS itself ensures that these values are unique and never reused.

One problem with identity fields is that sometimes you may need to insert a row providing an explicit value to be used, essentially overriding identity functionality. Some DBMSs (like SQL Server) allow you to turn off identity processing with an explicit directive while a row is inserted and then turn it on again, and numbering automatically continues from the new highest value. Other DBMSs (like MySQL) allow you to simply insert values with specific values and, if present, these are used instead of auto generated values.

Derby also supports identity fields. These can be defined as ALWAYS in which case Derby always generates the value (which can never be manually specified), or BY DEFAULT in which case identity fields are generated only if an explicit value is not provided.

It's a rather nice implementation. But, it does not work as you'd expect. You can indeed specify an explicit value if BY DEFAULT is used, and your value will be used. But Derby does not seem to pay attention to explicitly provided values and does not update the internal counters accordingly, so when you next insert a row without an explicit value it may generate the exact same value as the one you specified. And if that column is a primary key, well, obviously the second INSERT is going to fail.

There is a workaround. When the table is created you may specify an optional START WITH value. So, if you need to load the table with 25 rows you can set START WITH to 26, and that will be the starting point for generated identity values. I guess you could also START WITH some really high number, and reserve the lower values for when you needed to explicitly provide a value.

Still, this is a hack, and it makes BY DEFAULT rather useless, which is a shame.

When performing full-text searches you usually want not just results, but a ranking indicating how close a match is to what you are looking for. In SQL Server 2005, ranks are accessed via ranking functions - FULLTEXT searches are ranked using function FULLTEXTTABLE() and CONTAINS searches are ranked using function CONTAINSTABLE(). Both of these functions are used the same way, and both accept search patterns, the same search patterns supported by the FULLTEXT and CONTAINS predicates themselves.

Here is an example:

This example performs a FREETEXT type search. Instead of filtering using the WHERE clause, the FREETEXTTABLE() function is used and given a search pattern instructing the full-text engine to match any rows that contain words meaning rabbit and food. FREETEXTTABLE() returns a table which is given an alias of "f" (so as to be able to refer to it in column selection and the join), this table contains a column named "key" which will contain the primary key value of the table that was indexed (productnotes in this example), and "rank" which is the rank value assigned. And finally, results here are sorted by rank descending, as the higher the rank the greater the match.

It is also possible to assign weight values to search patterns and words. The rankings assigned in the example used here assumed that all words were equally important and relevant. If this is not the case, and some words are more important than others, then the ISABOUT() function can be used to assign relative weights, and the full-text search engine will then use these values when determining rankings.

SQL Server 2005 supports two forms of full-text search, FREETEXT and CONTAINS. CONTAINS is used to search for rows that contain words, phrases, partial phrases, words with the same stem, proximity searches, synonyms (using a thesaurus lookup), and more.

Here is a simple example:

WHERE CONTAINS(note_text, 'handsaw') means find the word handsaw in column note_text.

CONTAINS also supports the use of wildcards:

'"anvil*"' means match any word that starts with anvil. Note that unlike LIKE, full-text searching uses * as the wildcard character (instead of %). Wildcards may be used at the beginning or end of a string. Also, when passing simple text to CONTAINS then that text is enclosed within single quotes. When passing wildcards each search phrase must be enclosed within double quotes inside those outer single quotes. Failing to do this will likely cause your searches to return no matches.

CONTAINS also supports Boolean operators AND, OR, and NOT. Here are a couple of examples:

When searching through extremely long text there is a greater likelihood of matches being found if search terms are near each other in the saved data. A simple AND search matches terms anywhere in the text, but NEAR can be used to instruct the full-text search engine to only match terms when they are close together. Here is an example:

'detonate NEAR quickly' means match only rows that contain the words detonate and quickly near each other.

Sometimes you may want to match a word that is part of the same family (based on the same stem). For example, if you were searching for "life" you'd also want to match "lives". Obviously, a wildcard of life* could not help here, and using li* would likely match too many false positives. This is where inflectional matching helps. Here is an example:

'FORMSOF(INFLECTIONAL, life)' instructs the full-text engine to look for any words that share the same stem as the specified word, in this case "life".

FORMSOF() also supports THESAURUS searches, where words can match synonyms. To use this functionality you must first populate an XML thesaurus file with words and their synonyms.

FREETEXT provides a simple mechanism by which to perform SQL Server 2005 full-text searches, matching by meaning as opposed to exact text match. Here is a simple example:

FREETEXT(column1, 'rabbit food') means perform a FREETEXT lookup on column column1 looking for anything that could mean rabbit food (but not necessarily those two exact words, and not necessarily as a phrase).

You can also search across all columns indexed for full-text search by using FREETEXT(*, 'search text').

If double quotes surround a search term then that exact phrase is matched, not the meaning.

SQL Server 2005 features an integrated full-text search engine, which several people have e-mailed me to ask about recently. (ColdFusion users can use <cfquery> to perform queries using SQL Server full-text searches).

If you want to play with SQL Server 2005 full-text search capabilities, you need to do the following:

1) Once a database has been created, support for full-text must be enabled before any full-text operations can be performed. To enable full-text support the sp_fulltext_database stored procedure is used. This stored procedure updates the currently selected database, so be sure to USE the correct database before issuing this statement:

If you are using SQL Server Management Studio and use the interactive New Database dialog to create your database, you can check the "Use full-text indexing" checkbox, which causes the above mentioned stored procedure to be automatically executed.

2) SQL Server stores full-text data in a catalog (a file that needs to be created). A single catalog can be used for multiple tables and indexes. If a catalog does not already exist, create one using CREATE FULLTEXT CATALOG:

This creates a catalog named my_catalog in the default catalog location. To specify the actual file location the IN PATH attribute can be specified.

3) Once a catalog has been created you can define the actual full-text indexes for each table containing columns that you want searchable. Indexes are created using CREATE FULLTEXT INDEX like this:

When creating a full-text index you specify the table and column to be indexed as table(column). More than one column may be indexed if needed, to do this simply specify the column names (comma delimited). The key with which to uniquely identify rows is required, and so KEY INDEX is used to provide the name of the table's primary key. And finally, the ON clause specifies the catalog to be used to store full-text data, and here the just created catalog is used (unless a default catalog has been defined).

Once set up, you'll be ready to use the CONTAINS and FREETEXT functions in your WHERE clauses to perform full-text searching.

I just spent way too much time debugging some SQL Server code, only to discover that my results did not match my SQL statements because of a trigger that I was unaware of. How did I find this trigger? With the help of a wonderful built in stored procedure named SP_HELPTRIGGER. The following lists all triggers associated with table myTable, along with the events they are associated with, and whether they are AFTER or INSTEAD OF:

We all rely on writing dynamic SQL in ColdFusion, but what if you need to write dynamic SQL on the server, perhaps in a stored procedure? While I'd not recommend overusing this technique (there are performance penalties to this one), if you need server-side dynamic SQL, the EXEC() function can help:

It gets a little trickier if the WHERE clause needs to compare string values, then you need quotes around the value, and those quotes need to be escaped:

It's not that often that I need a debugger for my SQL code, but every once in a while it's an absolute must, and today is one of those days. I have a several hundred line stored procedure which works with multiple view and table variables, dozens of variables, and three levels of nested cursors. And I am trying to find a sporadic logic problem. Simple stored procedure debugging can use embedded PRINT statements, but this one is more complex, and thus the need for a debugger.

But SQL Server 2005 removed the built-in debugger. Now you need to use Visual Studio .NET to debug SQL Server stored procedures. No problem, I happen to have a copy (which I use for occasional VB.NET and C# code). And sure enough, you can connect to SQL Server, open (and even edit) a stored procedure, set breakpoints, step through code, watch variables and expressions, everything you'd expect ... unless what you want to inspect is anything but a simple variable. As already said, I am working with lots of table variables (actually, as I am working with lots of values at any given time just about everything I need to inspect is a table variable). And when you inspect those it displays "Table". Gee, how useful!

The truth is that the old debugger had the same limitation, no visibility into temporary tables or table variables. But I was hoping that now that the debugger is actually Visual Studio that this limitation would be lifted, but nope.

If anyone knows of a solution or an ingenious workaround, I'd love to hear it. In the meantime, it's back to embedded PRINT statements. Bummer.

I just spent some time helping a very irate customer. You know, the "ColdFusion sucks" "we're going to dump CF" "how can you sell this ****" brand of irate. The cause of all of this anger and frustration was very serious ColdFusion performance issues. And I am sure the individual has been under a lot of pressure and so the ramblings are somewhat understandable.

But, at the end of the day, as is so often the case, the bottleneck was in database access and query processing. No, there was nothing wrong with the DBMS. And no, there was nothing wrong with the SQL used. And no, the connection and data source settings were not the problem.

So what was the problem? Simply, ColdFusion was being made to do the DBMS' job! I found dozens of queries that were returning data that was being used solely to feed other queries. I saw queries being returned and then filtered client-side, within ColdFusion. I even saw queries (some that were quite long running) that were never being used once executed (leftover code from previous edits, I assume). And more.

I've been saying this for years, but, let's go through this again. ColdFusion is NOT a DBMS. Whatever database back-end you are using, chances are that it is a big powerful application that is designed to do one thing and do it well, manage and manipulate data. It makes absolutely no sense to give your DBMS less work to do while making ColdFusion work hard doing what the DBMS is supposed to do, what it is designed to do, what it is optimized to do.

So, a few rules:

• Don't ever retrieve data you don't need.
• Don't ever retrieve too much data, only to filter the results within ColdFusion (via querying a query, or excluding data while looping).
• Don't ever retrieve data if that data is only going to be used to drive additional queries. If you have a query, and then are using the results (perhaps in a <cfloop>, or via a ValueList()) in subsequent queries, then you need to find a way to consolidate those queries and do the work on the DBMS. And yes, that may mean writing JOINs or using stored procedures.
• Don't ever perform calculations and aggregations within ColdFusion, unless the data being used is already retrieved for some other purpose. Yes, the SQL aggregate functions (and having to use GROUP BY and figuring out how to make that work while using WHERE or JOINs) can be a little tricky. But do it anyway.
• Don't ever underestimate the important of caching query results. If you use the data frequently, and it is not changing as frequently as it is used, then cache it!
• There's more, but you get the idea.

Keep the following in mind:

• ColdFusion is never going to be able to query/sort/filter/count/sum/average/etc. as fast as your DBMS will. That is not what ColdFusion is optimized to do, and it is exactly what your DBMS is optimized to do.
• Sending unnecessary data from your DBMS to ColdFusion creates unnecessary network traffic, puts unnecessary load on the ColdFusion server hardware, unnecessarily increases server memory load, and unnecessarily slows down your and other requests.
• More often that not, your ColdFusion box is going to be under heavy load while your DBMS box sits with cycles to spare. It makes no sense to not leverage that power and potential.
• And as a side benefit, by moving data processing and manipulation back where it belongs, on the DBMS, you significantly increase the likelihood that you'll be able to reuse that work and effort.

And a couple of side notes:

• None of these comments are ColdFusion specific, and are just as applicable to applications developed in PHP, ASP, JSP, etc.
• You MUST learn how to use your DBMS. In the situation that triggered this post, one particularly problematic query was taking over 10 seconds to execute. Adding an index to the table used dropped that down to a couple of milliseconds!
• "My DBA does not let me write stored procedures" is not a valid argument, and is all the justification you need to get a new DBMS or a new DBA!

Considering how data centric ColdFusion applications tend to be, all of this is critical!

Bottom line, you have a DBMS for a reason, use it!