You are using Aurora MySQL on Amazon Web Services (AWS).
You have one Writer instance and one Reader instance.
You would like to perform a manual failover (so that your Writer instance is demoted to a Reader instance).
I needed to do this recently, but could not find clear instructions anywhere. Here are step-by-step instructions with screenshots.
Login to your AWS Console
Go to the RDS Console and select your Aurora MySQL Cluster
Highlight your Aurora MySQL Writer Instance, choose Failover from the Actions menu
Comment: Highlighting a specific instance may not be important. I believe if you have multiple Reader instances, the failover will use the “Failover priority” (e.g. “tier-0”, “tier-1”, … “tier-15”) to determine which Reader to promote to Writer. When I added a temporary 2nd Reader with “Failover priority” of “tier-0” and performed failover, that Reader was promoted to Writer. Please post a comment at the bottom of this page if you can confirm how failover is handled with multiple Reader instances! If the “Failover priority” is used to select which Writer instance will be promoted, please confirm if “tier-0” is indeed considered first when promoting a Reader to a Writer and I will update these instructions.
4. Confirm you would like to failover the cluster.
5. Go to the RDS Console and select your Aurora MySQL Cluster. The page will not show any changes for a few seconds.
6. Go to the RDS Console and select your Aurora MySQL Cluster (or refresh the page). Repeat until the page shows that the Writer instance has been demoted to a Reader instance.
7. Failover complete!
In my experience, the failover occurs within 10-15 seconds after submitting the failover request. Our application is aware of the separate reader and writer instances. We have not noticed any application read errors during the manual failover process. However, we have noticed temporary write errors during the failover process. Our application is designed to handle brief write failures and retry the writes, so this was not an issue.
Did you find this helpful? Let me know by sending me a comment.I tend to update and maintain posts more frequently if I know others find them helpful. Thanks for visiting!
I recently moved some of our PHP code from PhpSpreadsheet (phpoffice/phpspreadsheet) to Spout (box/spout) to address memory consumption issues while generating XLSX-formatted Spreadsheets. I have experience writing Excel Spreadsheets using several other PHP packages, so I was able to dive into the Spout source code and figure out how to access number and date formatting features.
Format Numbers
If you cast a PHP value to an (int) or a (float), Spout will save the value as a number, but your spreadsheet application will display an unformatted number. For example, the number “12,345,678.90” would be displayed in your spreadsheet application as “12345678.9”.
You can use setFormat() to format any (int) or (float) value. Find Excel number formatting examples here. See Sample Code below.
Format Dates
If you save a Date to Spout as a (string) value (e.g. “12/31/1999”), your spreadsheet application will display the pre-formatted Date, BUT the date cannot be re-formatted or used for calculations because it is being stored as a String value instead of a Date value.
You can use setFormat() to format a date, ONLY IF the date is passed as an Excel numeric (int) or (float) date value. Several PHP libraries can convert a PHP date to an Excel numeric date value. Since our project uses both Spout and PhpSpreadsheet, we use PhpSpreadsheet’s Date::PHPToExcel() to convert a Carbon value to an Excel numeric value. Find Excel date and time formatting examples here and here. See Sample Code below.
Sample Code
The following sample code produces this output in my spreadsheet application (Numbers):
Formatted Numbers and Dates from PHP box/spout
use Carbon\Carbon;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use Box\Spout\Writer\Common\Creator\Style\StyleBuilder;
use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
// Open Spreadsheet
$writer = WriterEntityFactory::createXLSXWriter();
$writer->openToFile('example.xlsx');
// Build Header Row
$styleBold = (new StyleBuilder())->setFontBold()->build();
$writer->addRow(
WriterEntityFactory::createRow([
WriterEntityFactory::createCell('Num1'),
WriterEntityFactory::createCell('Num2'),
WriterEntityFactory::createCell('Num3'),
WriterEntityFactory::createCell('Date1'),
WriterEntityFactory::createCell('Date2'),
], $styleBold)
);
// Define Number and Date Formats
$styleNum1 = (new StyleBuilder())->setFormat('#,##0.00')->build();
$styleNum2 = (new StyleBuilder())->setFormat('0.0')->build();
$styleNum3 = (new StyleBuilder())->setFormat('00000')->build();
$styleDate1 = (new StyleBuilder())->setFormat('mm/dd/yy')->build();
$styleDate2 = (new StyleBuilder())->setFormat('mm/dd/yyyy')->build();
// Define Number and Date Values
$num1 = 12345678.9;
$num2 = 1234;
$date = Date::PHPToExcel(Carbon::parse('03/14/2000'));
// Build Row of Formatted Numbers and Dates
$writer->addRow(
WriterEntityFactory::createRow([
// Output 12,345,678.90
WriterEntityFactory::createCell($num1, $styleNum1),
// Output 1234.0
WriterEntityFactory::createCell($num2, $styleNum2),
// Output 01234
WriterEntityFactory::createCell($num2, $styleNum3),
// Output 03/14/00
WriterEntityFactory::createCell($date, $styleDate1),
// Output 03/14/2000
WriterEntityFactory::createCell($date, $styleDate2),
])
);
// Close Spreadsheet
$writer->close();
Bonus: Custom Column Widths
The box/spout project has an open Pull Request (PR#715) that adds Custom Column Width support. I forked the contributor’s repository (aphofstede/spout) to my own repo (e.g. “example/spout-custom-column-widths”) and added the following snippet to the “repositories” section of our composer.json file, which instructs composer to use the “custom-column-widths” branch in my forked copy of the repository.
When I run “composer require box/spout” in our project, composer uses the “custom-column-widths” branch from my forked copy of the contributor’s repository. Replace “example/spout-custom-column-widths” with the actual path to your forked copy of the contributor’s repository.
You can use the setDefaultColumnWidth() method to set default column width for all of the worksheets in your spreadsheet.
TIP: Use method_exists() to make sure the custom method exists in case you need to temporarily deploy your project without the custom PR.
// XLS Worksheet Default Column Width
// NOTE: setDefaultColumnWidth() may only be called BEFORE openToFile()
if (method_exists($writer, 'setDefaultColumnWidth')) {
// NOTE: setDefaultColumnWidth() provided by PR#715
$writer->setDefaultColumnWidth(12);
}
You can use the setColumnWidth() method to set specific column widths for individual columns. In the example below, we set widths for Column A to Column H.
TIP: Use method_exists() to make sure the custom method exists in case you need to temporarily deploy your project without the custom PR.
// XLS Worksheet Column Widths
// NOTE: setColumnWidth() may only be called AFTER openToFile()
if (method_exists($writer, 'setColumnWidth')) {
// NOTE: setColumnWidth() provided by PR#715
$writer->setColumnWidth(8, 1); // Column A
$writer->setColumnWidth(12, 2); // Column B
$writer->setColumnWidth(12, 3); // Column C
$writer->setColumnWidth(12, 4); // Column D
$writer->setColumnWidth(12, 5); // Column E
$writer->setColumnWidth(50, 6); // Column F
$writer->setColumnWidth(12, 7); // Column G
$writer->setColumnWidth(12, 8); // Column H
}
The Custom Column Width PR may also provide these setColumnWidthForRange() and setDefaultRowHeight() methods? I have not tested these methods, but thought others may appreciate knowing they exist.
// Set Column Width to 12 for Columns A-F (1-6)
$writer->setColumnWidthForRange(12, 1, 6);
// Set Default Row Height to 3 for all rows
$writer->setDefaultRowHeight(3);
Did you find this helpful? Let me know by sending me a comment. I tend to update and maintain posts more frequently if I know others find them helpful. Thanks for visiting!
I spent quite a bit of time attempting to implement PhpSpreadsheet Cell Caching as described in their Memory Saving documentation page. I also read numerous related articles including Stack Overflow “Building very large spreadsheet with PHPSpreadsheet” where @andrewtweber claimed the Cell Caching was too slow and instead recommended “box/spout” XLSX/ODS/CSV writer.
Attempt to use the box/sprout Package
We are generating ~1MB Excel spreadsheets with ~25K rows and a total of ~197K cells. I confirmed box/spout claims of being VERY fast and VERY memory efficient, using ~94MB *less* memory than PhpSpreadsheet when generating these particular XLSX sheets. Unfortunately, I learned box/spout can NOT set column widths as of April 2021. I am tempted to deploy a custom box/spout with PR 715 “Not just another custom column widths PR”
Cell Caching Summary
I attempted to implement PhpSpreadsheet Cell Caching in a Laravel app using a few different methods. I agree that each method I attempted was too slow to be useful, primarily due to the fact that PhpSpreadsheet must make a separate call to the cache for EVERY CELL. In our case, that meant ~197,000 calls for ~197K cells. Regardless, I am sharing the examples I used so other devs can quickly determine whether or not Cell Caching is too slow for their use case.
Attempt 1: Use native Laravel Cache
I am not sure this is the best way to acquire a Simple Cache interface from Laravel, but PhpSpreadsheet seemed to be interacting with our Redis cache. However, 197K round trips over the network was going to take far too long. I gave up after waiting 5 minutes.
$cache = new Cache(); $cacheStore = $cache::getStore(); $cacheInterface = new Repository($cacheStore); Settings::setCache($cacheInterface);
$spreadsheet = new Spreadsheet();
Attempt 2: Use Shieldon Simple Cache with File Backend
I installed the “shieldon/simple-cache” package and attempted to use the File backend. Unfortunately, this had to create 197K temporary files (1 file for each of the ~197K spreadsheet cells). This took too long, even on an SSD. I gave up after waiting 5 minutes.
$path = 'cache/php-spreadsheet/'; if (!Storage::exists($path)) { Storage::makeDirectory($path); } $storagePath = Storage::path($path); $cacheInterface = new \Shieldon\SimpleCache\Driver\File([ 'storage' => $storagePath, ]); Settings::setCache($cacheInterface);
$spreadsheet = new Spreadsheet();
Attempt 3: Use Shieldon Simple Cache with SQLite Backend
I used the “shieldon/simple-cache” package again, only this time I attempted to use the Sqlite backend. My hope was that writing each of the ~197K cells to a single SQLite file might be faster. I was wrong. This took too long. I gave up after waiting 5 minutes. NOTE: Although the SQLite file contained 0 rows afterwards, the file was NOT truncated and remained at its peak size of 127MB. If you choose this method, be careful to truncate or delete your SQLite file to prevent a disk full situation.
$rebuildCache = false; $path = 'cache/php-spreadsheet/'; if (!Storage::exists($path)) { Storage::makeDirectory($path); $rebuildCache = true; } $storagePath = Storage::path($path); $cacheInterface = new \Shieldon\SimpleCache\Driver\File([ 'storage' => $storagePath, ]); $cacheInterface = new \Shieldon\SimpleCache\Driver\Sqlite([ 'storage' => $storagePath, ]); if ($rebuildCache) { $cacheInterface->rebuild(); } Settings::setCache($cacheInterface);
$spreadsheet = new Spreadsheet();
Other Options
I saw at least one other person mention that they built their own custom Simple Cache interface and that they were writing 500 cells (or 500 rows?) to each file. I’d like to try this since it would reduce the number of files created from ~197K to only 394 files (or 50 files?), but could not find a code sample. Since OP said their attempts were too slow, I abandoned this option.
A User Script is a locally installed script in your web browser that applies JavaScript and CSS customizations to a specific website while you are visiting the website. Here are a few example use cases:
Add Functionality, such as a “Download List to CSV” button that loops through a list on the web page and generates a CSV file.
Remove Functionality, such as hiding links or buttons you never intend to use to help de-clutter a web interface.
Fix Design Issues, such as the position or color of an element on the page that might be bothering you.
User Scripts can be run in any web browser by installing a User Script browser extension. Here are a few example User Script extensions for some of the more popular web browsers:
PRO TIP: If you have access to a WebDAV file sharing folder that supports BASIC authentication, Violentmonkey can sync your User Scripts settings across multiple browsers. Unfortunately, Violentmonkey does not currently support WebDAV DIGEST authentication.
Getting Started with Userscripts Extension on Safari
I’ve provided a few example scripts you can use to suppress extra menus and buttons on several financial websites. Here are step-by-step instructions to install these scripts.
Go to the Safari drop-down menu and select Preferences
When the Preferences window appears, go to the Extensions tab
Make sure the “Userscripts” extension is enabled. The check box next to the extension name should be checked.
Click the “Userscripts” icon on your Safari browser bar. The icon will look similar to this: “</>“
You should see a window with a “Search and Filter” text box, Gear icon, and Plus icon in the upper-left corner
Click the Plus icon and choose “New Javascript”. You should see a new JS User Script in the left-hand list and a new User Script template in the right-hand content area.
Download a Sample Script, open the text file, and copy/paste the code into the right-hand content area, replacing the sample template code. Repeat for each User Script you’d like to try. You can install multiple User Scripts.
Minimal QBO – Minimal interface for Intuit QuickBooks Online
Each time I use the Viscosity VPN client on macOS 11.1 (Big Sur) to establish an OpenVPN connection, the hostname displayed in new macOS terminal session would change from my computer name (e.g. “user@trogdor“) to the reverse lookup hostname of my VPN IP address (e.g. “user@ip-10-2-3-4”).
Suppose I always wanted my hostname to be “trogdor“. I ran the following command in a Terminal session to set a permanent hostname that does NOT seem to change when I connect to the VPN.
scutil --set HostName trogdor
Huge thank you to Chris Searle for posting this same solution for OSX Lion and Mountain Lion! I assume that means this solution would also work for all macOS versions between 10.7 and 11.1:
OSX 10.7: “Lion”
OSX 10.8: “Mountain Lion”
OSX 10.9: “Mavericks”
OSX 10.10: “Yosemite”
OSX 10.11: “El Capitan”
macOS 10.12: “Sierra”
macOS 10.13: “High Sierra”
macOS 10.14: “Mojave”
macOS 10.15: “Catalina”
macOS 11.0: “Big Sur”
macOS 11.1: “Big Sur”
Did this help you out? Do you have more info to share? Please reply below! I’d love to hear from you.
