Skip to main content

MySQL Bind Address

There are times when you need MySQL to be able to bind to more than one address. In this particular occasion, I was running Nagios locally on the same system as Drupal, my content management system. For some reason Nagios was trying to connect using the loopback address (127.0.0.1) instead of the server IP Address (192.168.230.131). However, MySQL only allows you to set one bind address (bind-address) in the /etc/my.cnf configuration file. The solution is to allow it to bind to ALL IP Addresses (using bind-address=0.0.0.0) in the MySQL configuration file.

Shrink Transaction Logs

You do NOT need to run this (I already did), but you might be interested in this SQL to shrink transaction logs. You do need to do a backup of the databases after you truncate them (I'm not concerned since these old databases are lookup only - no new data should be written to them).

USE MASTER
GO

BACKUP LOG
    file
WITH TRUNCATE_ONLY
GO

USE db
GO

DBCC SHRINKFILE ( 'dbfile' )
GO
USE Master
GO

I used the following code to generate the list of files in the database.

SELECT 'SELECT name from ' + rtrim ( name ) + '..sysfiles'
FROM sysdatabases
WHERE name LIKE 'dbfile%'
ORDER BY name

Don't forget to back-up the database once you're finished as well. A FULL database backup should be done!

Finding info in Files

Sometimes you want to be able to quickly search the contents of some files for a command or some other piece of text. The following command (for Cygwin obviously) will work:

for i in `find /cygdrive/c/Devel/SQL/ | grep sql`; do echo $i; grep cursor $i; done;

For regular linux, the command would be:

for i in `find ~/devel/sql/ | grep sql `; do echo $i; grep 'cursor' $i; done;

Both of these commands search for the word cursor in the SQL files.

QEMU on Windows

Download the QEMU program (from http://www.h7.dion.ne.jp/~qemu-win/) and install it into a directory on Windows. I use C:\QEMU.

# Verify that C:\QEMU is in the path

# Create a "base" directory to handle all your Virutal Machines. In my case, I selected C:\Virtual. I also create a subdirectory for each virtual machine I'm going to build.

# Create a base directory for the ISO images (CD-ROM images). You'll need this path for the batch file.

# Create a batch file (.CMD extension) to launch your virtual machine. Below is my batch file (AKA, a script) to launch Damn Small Linux (DSL) version 1.5.

VMWare Information

Here are some links with additional information on VMware and virutalization.

Virtual Infrastructure articles and sites:
http://www.vmware.com http://www.vmware.com/vmtn/vmworld/ http://www.vmware.com/vmtn/blog/ http://www.vmware.com/vmtn/blog/sherrod/ http://vmware.itst.org/ http://www.vmguru.com/ http://www.run-virtual.com/ http://trivore.com/vmware/ http://tihlde.org/~larstr/vmktree/ http://chitchat.at.infoseek.co.jp/vmware/ http://www.vmts.net/ http://blogs.technet.com/megand/default.aspx http://sanbarrow.com/index.html http://www.roudybob.net/ http://www.redmondmag.com/columns/article.asp?EditorialsID=1220 http://www.vmwareprofessional.com/ http://www.platespin.com/spinblog/ http://www.jsequeira.com/cgi-bin/virtualization/BrowseFacets http://www.virtual-machines.nl/vm/index.php?option=com_content&task=cate...§ionid=5&id=2&Itemid=20&limit=25&limitstart=0 http://about-virtualization.com/mambo/index.php?option=com_content&task=... http://www.virtual-strategy.com/article/articleview/1328/1/2/ http://www.computerworld.com.au/index.php/id;1734537766;fp;2;fpid;2 http://www.microsoft.com/presspass/features/2005/oct05/10-10virtualizati... http://www.microsoft.com/presspass/press/2005/oct05/10-10VirtualizationS... http://www.cio.in/esntech/viewArticle/ARTICLEID=569

Here are some links with additional information on VMware and virutalization.

Virtual Infrastructure articles and sites:

http://www.vmware.com http://www.vmware.com/vmtn/vmworld/ http://www.vmware.com/vmtn/blog/ http://www.vmware.com/vmtn/blog/sherrod/ http://vmware.itst.org/ http://www.vmguru.com/ http://www.run-virtual.com/ http://trivore.com/vmware/ http://tihlde.org/~larstr/vmktree/ http://chitchat.at.infoseek.co.jp/vmware/ http://www.vmts.net/ http://blogs.technet.com/megand/default.aspx http://sanbarrow.com/index.html http://www.roudybob.net/ http://www.redmondmag.com/columns/article.asp?EditorialsID=1220 http://www.vmwareprofessional.com/ http://www.platespin.com/spinblog/ http://www.jsequeira.com/cgi-bin/virtualization/BrowseFacets http://www.virtual-machines.nl/vm/index.php?option=com_content&amp;task=category§ionid=5&amp;id=2&amp;Itemid=20&amp;limit=25&amp;limitstart=0 http://about-virtualization.com/mambo/index.php?option=com_content&amp;task=blogcategory&amp;id=0&amp;Itemid=11 http://www.virtual-strategy.com/article/articleview/1328/1/2/ http://www.computerworld.com.au/index.php/id;1734537766;fp;2;fpid;2 http://www.microsoft.com/presspass/features/2005/oct05/10-10virtualizationlicensing.mspx http://www.microsoft.com/presspass/press/2005/oct05/10-10VirtualizationStrategyPR.mspx http://www.cio.in/esntech/viewArticle/ARTICLEID=569</span></span></p>

Sharing an RDP Local Console Session

Connect to the system and modify the computer properties (right-click on My Computer and choose Properties).

Under the Remote tab, enable Allow Remote Assistance.

Apply the settings and disconnect.

Have the user needing to share the local console do the following:

Click on Start > Help and Support.

Look for the heading called Support on the right side of the window and click on the Remote Assistance link under the heading.

Choose to Invite a user to share your session.

At the bottom of the support window, click on "Save invite as a file" rather than using Windows Messenger or email.

Choose not to set a connection password (unless you feel it is necessary).

Save the file to a folder where the person needing to shadow the active session can find it.

The file may now be clicked on (opened) and will initiate a connection to an active local console session.

The user at the local console will need to accept the incoming connection. The new session will now open an RDP session that is controlled by the desktop user through an interface that is similar to the Netmeeting interface of old.

Remember that if you are the user connecting to the local console and you are doing so through Remote Desktop Client and connecting to servers, you will need to connect using the "/console" command option which is added to the end of the target string. For Remote Desktop Client running on Windows 2000 Professional, you will need to be careful to set the quotation marks around the path and executable, but place the command option outside the quotes.

IE Links

The Windows File Explorer, sometimes just called File Explorer, doesn't work or doesn't work as expected. This could be a patch that didn't fully install or some other problem with Microsoft Windows. I've found that you can use (at least some versions of) Internet Explorer, in some cases, to browse files on your computer.

One way to do this is to use the command line (if you can run cmd.exe or powershell.exe) like the following: %program files%\Internet Explorer\iexplore.exe" -e -root C:\.

You can pass in alternate directories as well, like your Documents folder using %program files%\Internet Explorer\iexplore.exe" -e -root %USERPROFILE%. This works since the %USERPROFILE% variable is set by Windows when you log in to Windows by entering your username and password.

Unicode Problems

The following SQL will highlight some problems with Unicode under SQL Server.

DECLARE @nstring nchar(12)
SET @nstring = N'?'
SELECT UNICODE(@nstring), NCHAR(UNICODE(@nstring))
SELECT @nstring
DECLARE @nstring2 char(12)
SET @nstring2 = '?'
SELECT ASCII(@nstring2), UNICODE(@nstring2), NCHAR(UNICODE(@nstring2))
SELECT @nstring2

Also, Unicode, especially on Microsoft Windows, is NOT the same thing as UTF-8.

Verified Updates in SQL

Sometimes, on a high-volume / high TPS database, I want to make sure I've made updates to ALL the recovers, and not leave some orphaned data out there. This usually occurs because someone has a locked record while they're making a change. So when I'm making updates to multiple records (remember, SQL is SET based, so it applies to all records that match a condition), I use a belt and suspenders approach.

I verify the number of records that are going to be updated from the new value to the old value both before and AFTER I make the change. If I'm updating a set of users that are going to be changed from "Admin" to "SysAdmin", the code would look like the following:

BEGIN TRANSACTION
SELECT COUNT(*) AS 'admin' FROM Users WHERE level = 'admin'
SELECT COUNT(*) AS 'sysadmin' FROM Users WHERE level = 'sysadmin'
UPDATE Users SET level = 'sysadmin' where level='admin'
SELECT COUNT(*) AS 'admin' FROM Users WHERE level = 'admin'
SELECT COUNT(*) AS 'sysadmin' FROM Users WHERE level = 'sysadmin'

-- COMMIT
-- ROLLBACK

It should return something like the following:

admin
-----------
23

sysadmin
---------------
5

*Data Updated....*
admin
-----------
0

sysadmin
---------------
28

If all the numbers match up, I can highlight and execute the COMMIT statement that is commented out. If the numbers DO NOT MATCH I can execute the ROLLBACK statement to safely undo my work.

Windows Registry

The Microsoft Windows registry is a file (some people describe it as a hierarchical database, and that is not a completely inaccurate description because of how the file is laid out) that stores configuration information for the Windows Operating System, as well as many of the software programs installed on the system. All of the information inside the Registry can be manipulated with a variety of tools to further customize how Windows looks, how it works, or the software on it. But this is not without substantial risk. Changing application (or system configurations) outside of appropriate tools (like the Control Panel, as one example) can cause a lot of problems. You may not even be aware of the impact until much later (the next time you use a program - fater a reboot, for example). I want to stress this - Editing the Registry is very risky and can cause many problems if you don't know what you're doing or edit the wrong things - to the point where Windows may no longer function.

The two most common tools for editing the registry on Windows are RegEdt32.exe and RegEdit.exe. However, there are other tools available and Yannis Grammatis identifies them on his website http://www.chaminade.org/MIS/Articles/RegistryEdit.htm.

Again, I want to reinforce my earlier warning.....

Editing the Registry is very risky and can cause many problems if you don't know what you're doing or edit the wrong things - to the point where Windows may no longer function.

So with these notes, why would you want to edit the registry? For some people it's a way to tweak (modify some obscure setting) that you normally wouldn't be able to access. Other settings may expose functionality for a program or feature you wouldn't normally be able to access - for instance a specific mouse may not fully emulate a different mouse - but by tweaking the setting in the registry you can convince Windows that the feature is supported.

This is not something I normally recommend doing - it is only for advanced users or people who don't worry about "breaking" their computer.