Perl, DBI and MySQL utf8mb4 Character Set Support

Perl LogoMySQL’s modern UTF-8 encoding is named utf8mb4 (4 bytes), not utf8 (3 bytes.)

For new applications, especially web, you should start with utf8mb4. For existing applications, you need to decide if an upgrade is worthwhile, and test extensively before a production upgrade.


  • Note that changing your database character set for production applications should be treated seriously, like the major project that it is.
  • If all your data is in fact currently US-ASCII, then the database migration will be easy since it is a subset of UTF-8. (However your applications may need to do Unicode normalization of strings before insert, for comparisons to work later.)
  • Be careful with converting binary columns to UTF-8, like blobs. The result may be undefined, so test.
  • Since collation is language-specific, the various Unicode collations are almost never the right ones
  • You do need UTF-8 for people and place names, but there’s no reason to use UTF-8 for columns that will always be US-ASCII, like database id’s, IPv6 addresses, etc. MySQL has to allocate more space for UTF-8, so it is a disadvantage when not needed.

Testing utf8mb4

  1. ensure you’re choosing an up-to-date version of MySQL 5.6 or 5.7 that if necessary supports long keys (>768 bytes) using DYNAMIC/BARRACUDA with SHOW VARIABLES LIKE “innodb_file_format”;
  2. identify some test strings using ie. emoji and write some small test apps in each of the application languages you support
  3. dump and restore your data on a test instance, especially if you have Asian or emoji characters. Run mysql_upgrade.
  4. convert your schemas, tables and columns to utf8mb4. Update my.cnf.
  5. test using the test programs in #1
  6. update your business applications’ client settings and get acceptance testing. Now is a good time to write a central database connection function, and also a central transaction retry function.
  7. test your database tools, including backup and restore.

Production Migration to utf8mb4

  1. upgrade client libraries on application servers and verify
  2. update all applications in advance if possible and verify
  3. schedule downtime
  4. during downtime, deploy new applications settings on all servers and dump and migrate database
  5. do acceptance testing

If you have applications written in Perl, you need to first upgrade DBD::mysql to a version greater than 4.041. (Even CentOS 7 comes with only 4.023.)

Before (CentOS 7):

$ perl -e 'use DBD::mysql; print $DBD::mysql::VERSION'

# mysql_config
-bash: mysql_config: command not found


# yum install mysql-devel
# cpan DBD::mysql

$ perl -e 'use DBD::mysql; print $DBD::mysql::VERSION'
4.043 DBD::mysql – all your UTF-8 bugs are belong to us!!
SO: Trying to install Perl-Mysql DBD, mysql_config can’t be found
MySQL utf8 vs utf8mb4 – What’s the difference between utf8 and utf8mb4? HN MySQL 8.0: When to use utf8mb3 over utf8mb4?
Using Databases with DBI: What Not To Do

Posted in Linux, MySQL, MySQL Cluster, Open Source, Oracle, Perl, Tech | Leave a comment

Lecture: Silicon Valley Perl Meetup – REST API Server Programming With Perl

Perl LogoI gave a talk at the Silicon Valley Perl Meetup on “REST API Server Programming With Perl.”

Here are the slides:

  1. Part 1: “The REST API Landscape in 2017”
  2. Part 2: “REST API Server Programming with Perl, Swagger and the Mojolicious Framework”

github: Perl Petstore Enhanced REST API Framework, Sample REST API Clients

There was an extended audience discussion afterwards with some interesting observations:

  1. “If you use boolean; before use JSON;, you may get the true/false behavior you want.”
  2. “Catalyst fixed their module dependencies issues a few years ago and can be installed quickly now.”
  3. “Even though Swagger2 provides some input parameter validation, all the 3-letter security acronyms have to be handled.”
  4. “Upgrading our spec file from Swagger 1.0 to Swagger 2.0 didn’t work until we added a directive like ‘x-mojo-controller2’.”
  5. “Consider just validating a part of the spec when doing input validation of requests.”

Swagger 2.0: How to specify an input parameter of type ‘object’?
Reading the Swagger 2.0 spec:

  • body location supports input objects now. path and query locations request input arrays for now, and objects later (limited by not having Content-type per input parameter).
  • output response can be objects.
  • having said that, whether your validator supports that or not requires testing.

File Structure

“The Swagger representation of the API is made of a single file. However, parts of the definitions can be split into separate files, at the discretion of the user. This is applicable for $ref fields in the specification as follows from the JSON Schema definitions.

By convention, the Swagger specification file is named swagger.json.”

Thanks to Nvidia for hosting the meetup again.

OpenAPI Specification Version 2.0
URLs are UI
JWT Comments
Jeremy Zawodny: From mod_perl to Mojolicious at craigslist Slides What is HATEOAS?
Ask HN: What’s your biggest struggle with Microservices?

Posted in Open Source, Perl, Tech | Leave a comment

Skype and Facetime Tips for Older Mac OS X Versions

PSA for Mac OS X users on older machines like Lion and Mountain Lion: if you’re stuck on Skype 6.15, and can’t do video chat after the Microsoft’s July 3, 2017 breaking update …

Apple Facetime is a free video chat application that comes with Mac OS X.

Some tips for using Facetime:

  1. It seems to use an entire CPU core, and will quickly drain your battery, so plug in.
  2. To add a new contact, you need to know their Apple ID email or their iPhone number. For phone numbers, use the full international prefix with 0 or 1, but without +.
  3. By default if Facetime has a window visible (ie. it’s opened), it will enable the camera and consume power. To prevent that, click on the yellow dot to minimize it (ie. background it.)
  4. To make Facetime wait for calls, minimize it (click on the yellow dot) or leave Contacts open.
  5. If Facetime is not for you, try iMessage/Messages/iChat.
  6. Facetime on iPhone is paused automatically when they switch apps and shows a “Paused” message. Facetime is not paused on Mac OS X.

HN Discussion

Posted in Tech | Leave a comment

Configuring IPv6 on Linux CentOS

Linux logoConfiguring IPv6 on your linux server is this easy if your ISP is IPv6-ready (if not, see Tunnelbroker links below) on CentOS 5 and 7:

vi /etc/sysconfig/network-scripts/ifcfg-my_interface file, note the settings that start with “IPV6” and update them:

  1. DNS2=2001:4860:4860::8888 – Google Public IPv6 nameserver
  2. IPV6INIT=yes – This is needed when configuring IPv6 on the interface
  3. IPV6ADDR=my_ipv6-address – Specifies a primary static IPv6 address
  4. IPV6_DEFAULTGW=my_ipv6-address – Adds a default route through the interface specified

You don’t need new IPv6 switches, since switching is done at Layer 2. I’m using the old bargain web-managed HP Procurve J9028A. :)

(When people say IPv6-capable, that means the management features can be assigned IPv6 addresses, or that it can do Layer 3 routing functions with IPv6 addresses.)

To test:

  1. ping6
    # ping6
    PING (2607:f8b0:4005:80a::200e)) 56 data bytes
    64 bytes from (2607:f8b0:4005:80a::200e): icmp_seq=1 ttl=56 time=1.39 ms
  2. traceroute -6
    # traceroute -6 
    traceroute to (2607:f8b0:4005:80a::200e), 30 hops max, 80 byte packets
     1  gateway (xxx:xx:x:xxx::1)  3.959 ms  3.978 ms  4.005 ms
     2 (2001:470:0:274::1)  7.859 ms  7.933 ms  11.998 ms
     3 (2001:470:0:263::2)  11.296 ms  0.785 ms  11.311 ms
     4 (2001:470:0:244::2)  0.944 ms  0.946 ms  0.980 ms
     5  2001:4860:0:1004::1 (2001:4860:0:1004::1)  1.471 ms 2001:4860:0:1006::1 (2001:4860:0:1006::1)  1.478 ms 2001:4860:0:1004::1 (2001:4860:0:1004::1)  1.577 ms
     6  2001:4860:0:1::1f71 (2001:4860:0:1::1f71)  1.328 ms  1.266 ms  1.221 ms
     7 (2607:f8b0:4005:80a::200e)  1.150 ms  1.197 ms  1.210 ms


  1. on CentOS 7, leaving network manager enabled was more successful than attempting to disable it
  2. the files in network-scripts/ are space-sensitive, so don’t use spaces or you will get weird parsing errors
  3. if you’re a Perl programmer, for best results use perl 5.14 or newer and IO::Socket::IP instead of IO::Socket::INET. (Perl 5.10 can work if you upgrade IO::Socket and LWP modules.)
  4. “Check sysctl -a | grep disable_ipv6 output. And if it’s =1, set it to 0.”
  5. “When NetworkManager is running, it may disable ipv6 on the interface if it’s not configured via NM.” Configure IPv6 Addresses And Basic Troubleshooting In Linux Google Public DNS IP addresses Are you using Network-Manager in no-GUI CentOS 7 Server?

HE Tunnelbroker Links

If your ISP does not support IPv6 yet, you can tunnel traffic in and out of your machine using the HE Tunnelbroker. This is also simple to setup, taking about 5 minutes if your server has IPv6 enabled. (Note that IRC and email traffic to port 25 are filtered to reduce abuse.)

Create Hurricane Tunnel Broker on Raspberry Pi
Hurricane Electric Free IPv6 Tunnel Broker
AWS IPv6 Update – Global Support Spanning 15 Regions & Multiple AWS Services

Posted in Linux, Open Source, Perl, Tech | Leave a comment

Debugging CSS for Programmers

In the 90’s and 00’s, programmers used to scorn front-end designers who called writing HTML “programming.” That changed to fear when CSS arrived and pixel-perfect results raised the bar.

Here’s some tips for programmers struggling with fixing CSS rendering problems:

Before Getting Started

  1. Most programmers are not artists, so don’t write your own site-wide layout CSS. Either find a designer or use bootstrap or an alternative. If you’re doing more than minimal JavaScript programming, use jquery or an alternative.
  2. Treat a CSS project like learning a new programming language. Schedule a day when you’re fresh. You will need all of your concentration ability if you’re new to CSS because of the nested inheritance and browser quirks. If you finish early, that’s a nice bonus.

Getting Started

  1. You may want to capture screenshots of what the original site looks like. The Mac OS X Preview app has professional-looking annotation features by Adobe under “Tools … Annotate.”
  2. make a backup of the old CSS directory and save in a safe place. Then copy the CSS into a subdirectory so you can use the diff command on the old and new versions
  3. ensure your network environment has isolation: no load balancers, proxies or far future expires that can cache your old CSS files while testing
  4. use the W3C HTML and CSS validators to get a feeling for what’s there and pair each div with /div (I helped debug the original HTML validator.) :)
  5. use browser developer tools to examine the CSS. My favorite is Firefox’s Inspector.
  6. remember the hierarchy with CSS:
    1. the most-specific style wins
    2. the last definition wins, whether at the block or file level
    3. id’s win over classes. (Use CSS classes whenever possible. id’s are more heavily used with JavaScript DOM code.)
    4. pseudo-classes: “Note: a:hover MUST come after a:link and a:visited in the CSS definition in order to be effective! a:active MUST come after a:hover in the CSS definition in order to be effective! Pseudo-class names are not case-sensitive.”
    5. !important wins. (Try to avoid this unless for example you’re fixing a browser z-order rendering problem.)
    6. all bets are off with syntax errors, missing semicolons, or mis-specified class name lists, so fix those first. See below.
  7. do some reading online about block and inline element display. Some properties can only be applied to a block element, which usually means adding an enclosing div.

Note: if you’re paying for HTML or CSS, always validate it before payment. Even better, ask your designer to validate it weekly and before delivery.

Debugging Strategies

  1. verify if there are any CSS media screen blocks that vary with resolution
  2. verify the correctness of individual class definitions, especially lists. if “cascading seems broken”, most likely there is a syntactically-correct but stray ‘div’, ‘p’ or ‘input’ after a class name
  3. once the CSS looks sane, you can debug problems by setting enclosing div classes to red or green and refresh your browser
  4. if there’s multiple CSS files included, try varying the order
  5. to debug rounded corners issues, try setting the background to black or white for maximum contrast
  6. test on Firefox, Safari, Chrome and IE11. Budget twice as much time if you want to support IE9 and IE10
  7. try overriding a CSS element or enclosing div with an inline style to narrow down a problem.

Emergency Fallbacks

If you’re on a deadline or don’t have story points for a CSS project, there are workarounds.

  1. Although it’s better to write classes that apply to all elements, being more specific can fix individual form field problems
  2. As a temporary fix, doing an inline style will guarantee what the element looks like.

Getting Done

  1. if you’re not working solo, use the diff command to communicate changes to other developers and artists
  2. coordinate the checkin. Note that artists often are not version control experts, so you need to find out what the natural flow is or somebody’s changes will get stepped on (seen it twice already.) Investing some time now could prevent big problems later

Little CSS Stuff Newcomers Get Confused About

Related Annoyances

  1. maxlength doesn’t work on input type=number
  2. it’s more difficult to left-justify and right-justify 2 items in CSS than with tables


CSS Stats
CompressPNG and tools family

Posted in Tech | Leave a comment