dl

David Lundgren

Web Developer & Systems Administrator

MySQL Transactions and Altering Schema

While reviewing the log files for a project I am working on I came across a MySQL error that dealt with mismatched character sets. Specifically the table was set to latin1 and the connection was set to UTF8. This error was rare but happened at the right time for me to catch it, as I was reviewing the log files for an unrelated problem.

Further investigation revealed that the schema and some tables were set to latin1. If we weren’t setting the connection to UTF8 this wouldn’t be a problem. So I set to work on creating a database update to include the alter database and necessary alter table statements.

The first statement among the list of statements that needs to run:

ALTER DATABASE project CHARACTER SET UTF8.

Response from MySQL: Error 1192: “Can’t execute the given command because you have active locked tables or an active transaction”

I was stuck, as this had run fine in the development, QA, and staging environments. Why would it fail in production? The tables didn’t have any locks, and there were no transactions that I was aware of. My Google-Fu led me to the MySQL 5.1 Reference section 13.3.3.┬áStatements That Cause an Implicit Commit. Armed with the knowledge that create, alter, delete table statements cause implicit commits, I was still stuck where I began.

I began reviewing our database update mechanism (based on Zend Framework Database Table Adapters) and found that it was starting a transaction before running the statements. I set forth to update the tool, but some updates needed transactions for their modifications, so I ended up running the query manually.

Sometimes there is no easy answer and you need to just Ops up and get your DBA on.

Creating a DHCP Server with vboxapi

While working on a project for work recently I stumbled across the need to create a network and associated DHCP server. The API documentation however only gave me the following reference to start the DHCP server.

void start (in wstring networkName, in wstring trunkName, in wstring trunkType)

trunkName and trunkType are not really documented in the IDHCPServer API page. Here are their documentation as I have discovered.

  • networkName: This is the IHostOnlyInterface.networkName property
  • trunkName: This should really be adapterName, as putting the IHostOnlyInterface.name property is correct
  • trunkType: This is one of the following TRUNKTYPE_* constants
    • These constants don’t exist in the VirtualBox_constants.py, but can be found in the NetworkServiceRunner.h
      • TRUNKTYPE_WHATEVER = ‘whatever’
      • TRUNKTYPE_NETFLT = ‘netflt’
      • TRUNKTYPE_NETADP = ‘netadp’
      • TRUNKTYPE_SRVNAT = ‘srvnat’

Putting this all together I ended up with code similar to the following

from vboxapi import VirtualBoxManager

# configuration
addresss     = '192.168.8.8'
netmask      = '255.255.255.0'
dhcp_from_ip = '192.168.8.100'
dhcp_to_ip   = '192.168.8.150'

## Configure a HostOnly Interface and the associated DHCP Server
vbm = VirtualBoxManager(None, None)
vb  = vbm.vbox

# create the network on the host
(progress, interface) = vb.host.createHostOnlyNetworkInterface()
interface.enableStaticIPConfig(address, netmask)

# create the dhcp server for this network
dhcpServer = vb.createDHCPServer(interface.networkName)
dhcpServer.setConfiguration(address, netmask, dhcp_from_ip, dhcp_to_ip)
dhcpServer.enabled = True
dhcpServer.start(interface.networkName, interface.name, 'netadp')

FreeBSD and sudo defaults

Several weeks ago I started transitioning some Ubuntu VM’s to FreeBSD VM’s . On previous VM’s I was able to use the following command line without any problems

sudo phing code-update

After switching to FreeBSD I found that sudo, or its “sudo -E” variant, was having problems when running in sub shells. Phing svn tasks were asking for passwords that were previously setup to use svn+ssh. Using “sudo svn list svn+ssh://svn.example.com/svn/project” worked but not when phing ran. It turns out there are two environment variables that Ubuntu’s sudo package was preserving: HOME & MAIL. NOTE: Ubuntu 14.04LTS’ sudo package appears to only preserve HOME.

I created /usr/local/etc/sudoers.d/svnusers

Defaults env_reset
Defaults env_keep+="HOME"

This made FreeBSD’s sudo work as it had on Ubuntu. A day’s worth of investigation to solve the riddle but it works as I would expect it to.

Unix::ServiceConfig released

In 2004 I started working with multiple FreeBSD servers for multiple clients, that needed to be administered by non-admin users. I know you are saying “you idiot” and “why would a non-admin user need to administer the server?” I was hired as a consultant and they wanted to be able to add users, web hosts, databases and dns entries more easily than remembering all the little things that were needed. I didn’t trust WebMin at the time due to being hacked several times prior. In response I wrote Unix::ServiceConfig which I hooked up to a perl script as a way to help me with allowing the non-admin users to more easily manage the server.

It worked really well at it’s job, and the users were happy. I haven’t updated the code since 2008, and it is primarily FreeBSD centric. But I figured it is better to release it now, than to never release it. It is under the MIT license.

Value of IT certifications

The other day the COO, a co-worker, and I were talking about things happening at the Company, and a quick side trek into the value of IT certifications came up. My original stance on the subject was that certifications weren’t valuable and that the skills we end up sharpening are better. After talking with them though I came to find out that not all certifications are created equal, some have more value than others. I don’t have all the answers to what the best measures of how to find those valuable certifications, but I did think about it longer, and I believe I’ve come up with something to help us measure wither getting a certification is worth it.

Continue reading

jQuery 1.9+ event toggler plugin

With the release of jQuery 1.9 using .toggle() as .toggle(handler(eventObject),handler(eventObject)) was deprecated and removed. Last year one of my clients needed to use this functionality as they had other triggers happening at the same time as the content being shown or hidden. To assist with the upgrade I wrote a plugin for jQuery called ToggleEvent that does something similar to the old .toggle() syntax. Just recently I upgraded another one of my work projects to jQuery 1.11.0 and I forgot about the loss of .toggle() being used this way. Luckily I remembered that I had solved the problem before and dusted off the ToggleEvent plugin.

Realizing that I had forgotten to both write about the plugin, and publish it, I have now done so at github: jQuery ToggleEvent plugin

Continue reading

PHPUnit and certainty of test cases with random data

Recently I was testing a function that generated slugs for an application. In order to make the slugs unique we would append the microtime to the slug if needed. After updating my data provider to account for using the microtime version of the slug I was receiving intermittent success for the test cases. Using microtime for generated data introduces a margin of error into the time between when the code is executed and when you compare the results.

I tried using microtime before and after the function call that generated the slugs, but I would still end up around ~0.0010 seconds behind the microtime that the slug generator was using. I could not figure out how to make these tests pass 100% of the time, within reason.

If you can’t control the generator for tests that involve random data you have 2 options:

  1. refactor to remove the randomness
  2. live with a degree of certainty

For the purposes of generating a slug with appended microtime, I determined that the degree of certainty was that the slug’s microtime at the seconds level will either be equal to or at most one second before the microtime of the test cases microtime call. If it is greater than 1 second difference then there is definitely a problem. The 1 second threshold could most likely be reduced (for example to 0.0010 seconds) if desired, but I needed to get the test written in a timely manner and a 1 second degree of certainty was acceptable at the moment.

Redux: PHP5 cronjob in Debian packages

After running into “session could not be started because it was already started with session_start() or session.auto_start” on a project, I realized that removing the cronjob is not the only thing that needs to happen to let PHP manage it’s own sessions.

  • chmod www-data:www-data /var/lib/php5
  • update /etc/php5/apache2/php5.ini and set
    session.gc_probability = 1

I’m assuming that this more paranoid than usual security measure was a way to help inexperienced admins and developers to help prevent session hijacking should the web server be breached. However, if root is gained, it doesn’t matter anyway. I’m not going to say that I am an expert in security for servers, but I can tell you that Debian, and therefore Ubuntu, are the only ones doing this type of paranoid security practice. Coming from the FreeBSD world, you are responsible for the security of your machine, not the developers or port maintainers.

</rant>

Next Page »