Migrating Sqlite Databases to MySQL

Recently I had to migrate a relatively simple (no FKs or views) Grafana 4 database of about 20 tables and 80,000 rows from sqlite3 to mysql5.6. Below are some notes I made on the 3 methods I tried.

  1. First up was MySQL Workbench on Mac OS. This required first finding and installing a working ODBC driver for sqlite3, which was a hassle. (The Devart one worked.) Then configuring it in the Mac’s ODBC Manager. After clicking a few UI buttons, the import failed with an error message for each table and Workbench crashed, like usual for the past 2 decades. Tried again with another crash. Moving on …
  2. Next up was Navicat Premium Trial Edition. It was able to create a sql file that was syntactically valid with MySQL but had 3 major problems:
    1. it applied the TEXT cast to most of the string and date values
    2. which then meant it thought it had to declare most of the character and date columns as LONGTEXT and LONGBLOB
    3. which meant the index definitions using those columns were invalid because of length. In other words, a mess.
  3. Finally I used a bash script that enumerated the table names using .tables and did a sql dump using .mode insert on each table of individual INSERT statements. That worked fine.

Some other ideas to consider for more complex migrations are:

  • Use the Amazon database migration tools. There’s at least 3 that I’m aware of.
  • Use the professional-grade pgloader to migrate to Postgresql, then use another tool to pivot to MySQL.

The MySQL grants you need for grafana are:

GRANT USAGE ON grafana.* TO 'grafana'@'%' IDENTIFIED BY 'mypw';
GRANT ALL PRIVILEGES ON grafana.* TO 'grafana'@'%';

There is problem with the Grafana initial table creation script: it uses CREATE IF NOT EXISTS for the tables, but not the indexes, so you see this:

CREATE UNIQUE INDEX `UQE_user_login` ON `user` (`login`);

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

Very Good Article on Boeing 737 MAX Fiasco

New Republic has a very good recent article on the Boeing 737 MAX, titled “Crash Course.”

Overall, the article appeared to be written by a non-expert journalist. For example, airline accidents in Indonesia are in fact very common. But overall the collection of facts rings true and is a good summary to date.

It’s the first time I’ve seen the $1 million per airplane penalty mentioned that Boeing would owe to Southwest if sim training would be needed.

Essentially, that perverse incentive is likely the root cause for everything that followed.

It would be interesting to interview the people involved in negotiating that. Because airliner accidents often end airlines.

It’s also interesting that FAA staff didn’t want to work on the certification. The only other time I’ve heard of misgivings like that is for high-risk warbird and amphibious checkrides.

Indonesia Report: Pilots, Ground Crew Share Blame With Boeing For Lion Air Crash

US Congressional Hearings, Oct. 29-30, 2019

Boeing employee raised concern over Max sensor three years before crashes, email shows
Boeing CEO Pleads Initial Ignorance of MCAS Message Details

Posted in Tech | Leave a comment

PSA: Running Mac OS X csrutil Without a Recovery OS Partition

This blog post contains my notes for advanced users (Mac OS software developers and IT staff), not end-users.

Starting with Mac OS X El Capitan (10.11), some low-level system operations, like setting the NVRAM, are restricted by default by System Integrity Protection (SIP), which is controlled by the csrutil command.

However, the csrutil disable (or enable) command can only be run in a terminal after booting into a recovery OS partition (officially) or an installer (works for me, see below.)

Machines with a cloned OS often don’t have a recovery partition, or if they do, it doesn’t show up in the Startup Manager interfaces.

Even when there is a hidden recovery OS partition, versions of software starting with El Capitan have removed the Disk Util menu option to enable the recovery OS partition with Debug (apparently to hide the EFI partitions) when hidden. (Leave a note if you’ve used an older version of Diskutil to enable it. 🙂 )

You have 5 options of varying difficulty to choose from if you don’t want to do a full re-install of Mac OS X:

1) not that ez – install (ie. fix) your recovery partition, as recommended in most Internet How-to’s. Good luck, since most of the How to’s are incoherent. A suggestion would be to find one that talks only about one OS, preferably yours, instead of 4 or 5 versions.
2) ez – boot into a USB Mac OS El Capitan (or higher) installer, but don’t install. Just open the OS X Utilities ... Terminal menu and try csrutil disable. This worked for me, but if not, #5 below also worked for me.
3) didn’t try – install the recover partition to an external drive. My understanding is that this is intended for the Mac mini. Supposed to be ez.
4) didn’t try – try booting into a Lion installer and use the old Diskutil with debug mode to enable the hidden recovery HD partition. If you want to try that, boot into a Lion installer then open Terminal and type the following command, then open Diskutil last:

$ defaults write com.apple.DiskUtility DUDebugMenuEnabled 1​

5) super ez – boot into a USB installer, use Utilities … Diskutil … resize your original drive for a new 25 GB OS partition and install to it. Since you’re doing a fresh install, a recovery hd partition will be automatically created. boot into that with Option+R and run csrutil disable and reboot again to activate. This sounds kind of round-about, but is really easy, idiot-proof, and worked for me.

You’ll end up with something like this:

$ diskutil list
/dev/disk0 (internal, physical):
#:                  TYPE NAME         SIZE     IDENTIFIER
0: GUID_partition_scheme             *500.1 GB disk0
1:                   EFI EFI          209.7 MB disk0s1
2:             Apple_HFS HardDisk     474.6 GB disk0s2
3:            Apple_Boot Recovery HD  650.0 MB disk0s3 # can't see
4:             Apple_HFS Untitled      24.0 GB disk0s4
5:            Apple_Boot Recovery HD  650.0 MB disk0s5 # from option #5
$ csrutil disable
Successfully disabled System Integrity Protection. Please restart the machine for the changes to take effect.
$ csrutil status
System Integrity Protection status: disabled.

Of course, when you’re done, run csrutil enable because that’s the default, and it protects the NVRAM from malware.

Terminology: “recovery OS partition” and “recovery HD partition” refer to the same thing, but the first is conceptual and the second is an actual disk partition intended for recovery and contains the recovery OS.

Also “Disk Util” is the Mac OS app, “Diskutil” is the on-boot menu option and “diskutil” is the CLI program. Again, the names vary but they all do the same thing, but available in different environments.

developer.apple.com: Configuring System Integrity Protection
W: System Integrity Protection

Posted in Tech | Leave a comment

Cheap Macbook Pro Replacement Notebooks

Dropped your Macbook Pro, etc. and need a cheap replacement? Don’t want to spend $3,000 on a new one?

Replacing the display screen at the Apple Store or a 3rd party repair service is in the $600 – $800 range, and the battery $200 (for 2012 A1398 and newer Thin models.) Note that Apple frowns on 3rd party repairs and may not accept future repair requests. The advantage of 3rd parties is that you have more control over what they do and when.

Good news!

You can get usable Macbook Pro 15.4″ replacements on eBay for $200 (2009-2011) to $700 (2012 A1398) including Apple AC adapter and free shipping.

13″ notebooks are even cheaper – I’ve seen maxed-out 2012 models in the $400 range.

That’s right, for the price of a battery update, you can get an entire machine. I did BIN on a $280 mid-2009 A1286 that was fairly maxed-out and is in looks-new condition.

I made some offers on 2012 A1398 models (my favorite because of the keyboard feel, SSD and Retina screen), but they’re in high demand, and the sellers know it.

The thick non-Retina 2012 is the last model that was heavily user-customizable, and had all of the traditional multimedia ports (Firewire, SD, DVD, etc.) for gig professionals (musicians, photographers, etc.)

Models starting at 2012 A1398 are really designed for office and home use, and require dongles to do anything unless you want to re-purchase all your external drives, etc.

So go with a $25 or $50 offer discount on recent models, or save the haggling for 2009-2011 models that are in less demand. They have the same keyboard, but the other features are different, and Mojave may not be officially supported.

Key Features to Consider

– always get a new battery (Macbook Pros require a functioning battery to operate,) unless you can replace it yourself. Pre-A1398 is easy. I’ve done it before on an A1398 – took a day and had to sand off the power connector key, so not recommended unless you’re handy or really determined. Tip: search for a Youtube video on how to use string or jute to remove he old battery cell glue.
– hard disk vs. SSD? size? Although SSD is faster, hard disks work fine too while they’re still spinning.
– Retina vs. non-Retina display?
– GPU vs. not? May help with newer versions of Mac OS X. NVIDIA is best-supported, AMD is not supported by the dosdude1 patch
– weight – 2009-2012 Thick models weigh 5.5 pounds, ok if you go to the gym. Not ok if you have back, neck or wrist problems.
– DVD drive (2008-2012)
– Get at least 8 GB RAM, unless you can upgrade RAM yourself.
– old OS X vs. new one: Apple officially supports El Capitan (2008-2011) and Sierra, High Sierra, Mojave (2012 – now.) The most sensitive software seems to be video chat software like Zoom and Skype, so make sure it works for you. “Docker (2019) requires a 2010 and macOS version 10.12.0 [Sierra] or higher.” but minikube/docker or dockertools still work on El Capitan with most hardware versions. Verify with:

# any output means supported
sysctl -a | grep machdep.cpu.features | grep VMX

– warm vs. cool?
– AC adapter or not?
– glossy (default) vs. matte (optional) display coating
– cover or case?
– box or not?
– free shipping or not?
– cosmetics are basically that. It’s nice to own a cherry one, but enclosure scratches or missing feet don’t affect how it works. The exception is that raised bottom crew heads will scratch tables unless you apply a cover.

The special screwdrivers needed are available with upgrade kits on Amazon or eBay, and also at Central Computer locations in the Bay Area. (ie. The 2012 A1398 uses one type for the bottom case screws and a different type for the SSD screws.)

What to Avoid

– original (old) batteries. I’ve seen some work from 2012, but some not.
– logic board/power glitches or any other flakiness
– damaged screens
– “Parts only” listings. Sellers are savvy, and when they say this, beware.
– the old 2 GB RAM Macbook Pro versions (silver/chrome key tops.)
– ensure there is a Mac OS X recovery partition. It’s needed for a few things, including enabling USB boot if necessary using csrutil disable, and fixing problems.

How to Compensate for a Slower/Older Notebook

If you’re using your notebook for surfing the web:

– use uBlock Origin to block ads and malware
– disable custom fonts
– click on the battery icon to see which apps are power hogs. (Quit docker when not using it.)

If you’re migrating your SSD from an old notebook to a “new” replacement one, there’s a wrinkle: the firmwares are not backwards compatible, so you may need to fully upgrade the OS to Sierra on the new computer first, then install the old SSD drive. Or it just might not work at all and you’ll have to consult an Apple Store expert.

macworld.co.uk: How to install new MacOS on an old Mac, install Mojave on older Mac
vice.com: The 2012 Non-Retina MacBook Pro Is Still the Best Laptop Apple Sells
osxdaily.com: How to Download a Full macOS High Sierra Installer App
macworld.co.uk: How to download old Mac OS X and macOS versions
macdaddy.io: Install Disk Creator (Use USB drive with activity LED.)
dosdude1.com: macOS High Sierra Patcher Tool for Unsupported Macs
Disable SIP without recovery mode
How do I disable System Integrity Protection (SIP) AKA “rootless” on macOs [OS X]
Reset NVRAM or PRAM on your Mac

Kingston Data Traveler G4 16GB did not boot (“no entry sign” icon) High Sierra on 2009 15″ with either dood or “Install Disk Creator” software.

Posted in Tech | Leave a comment

Protected: Instrumental Music Market Analysis

This content is password protected. To view it please enter your password below:

Posted in Tech | Enter your password to view comments.