Month: February 2024

Rehoming all my Domains, Oh My !

Domains and registrars, and services, and what?

Google is selling their domain registration business to Squarespace. If your webserver is at a dynamic Internet address, the address needs to be monitored so it can be updated on the name server when it changes. Squarespace name servers won’t accept dynamic updates.

Monitoring the network to see the router’s public Internet address change and updating Google Domains‘ name server was done with Google provided DDNS instructions and settings. Squarespace, the provider they’re selling their Domain Name business to, does not support DDNS. Once Squarespace is actually managing the domain name it will keep the old information about the Internet address in its name server but doesn’t provide a way to automate updates. Once the domain name is on Squarespace and my Internet provider updates my modem’s Internet address, access to this website by name goes down unless I’ve set up another way to keep the website address updated.

Two ways I found to avoid this are move to a registrar that supports DDNS, like Namecheap, or find a DNS provider that supports DDNS and doesn’t require registering a domain with them, like FreeDNS (at afraid.org, yes, but don’t be), and use of their name servers as custom name servers with the domain registrar. That approach requires two service providers for each domain, a registrar and a DNS service.

There’s a fee with registrars for migrating a domain to them. Not much but if you can just change a setting and then there’s no need to pay to move to a different registrar then why not do that?

“THAT”, in this case means leaving the domains with Google and updating the name servers on Google’s domain registration record to the FreeDNS name servers and then keeping the Internet address updated on the FreeDNS name servers.

I’ve moved one domain to Namecheap to see how I like that, an $11 move. It will give me a hand at a third domain control panel, Google Domains, Squarespace, and Namecheap.

The others I’ve created records for them on FreeDNS, updated the name server records on Google Domains and will start using the Squarespace control panel to manage them when they transfer from Google. Squarespace doesn’t support DDNS but if custom nameservers are supported the move from Google Domains will go without a hitch.

Haven’t moved boba.org yet. Want to interact with the other sites a bit before deciding to use FreeDNS and their name servers with Squarespace domain registration or move to a registrar that supports DDNS with their name servers.

I do have to spend time out of the house to interact with the sites through the new DNS / name server setups. Sure, could do it through the phone if I turn off the WiFi but LTE isn’t very good here and I don’t like phone screen for web browsing. If LTE was good could tether the computer to the phone and browse the sites on the pc as I’d like. Kind’a lucky the weak signal, more fun to go out. Maybe find a coffee shop in a mall, buy a cup, sit in one of the seats and figure out how to choose the better option, then compare the details and make the choice.

Goodbye Google Domains!! ?? !!

…hello Namecheap ddns or, hmm, domain hosting too?

This domain, boba.org, is on a server I control, behind a dynamic IP address. Google Domains provides the domain hosting and supports DDNS which made it easy to have Google nameservers be authoritative, keep the A record updated, and manage the physical server.

Now Google’s giving up the domain name business, along with all the convenient features they bundle like DDNS, redirects, privacy, etc.

It’s being transferred to Squarespace. And Squarespace doesn’t include DDNS or offer it as a bundle.

Still need a way to update domain record with new address when it changes BUT can’t do that with Squarespace nameservers.

Checking if domain record can have nameserver but no A record with IP. IF SO, domain record points to nameserver that can be updated, e.g. Namecheap free DNS, and domain continues to function when IP changes even though new domain host doesn’t offer dynamic IP updating.

Will see what happens and update…

MySQL export to .csv

The elusive MySQL export to CSV *WITH* headers.

It took a while, review of a number of weblogs, support sites, and error message causes but export from MySQL to .csv with the column titles is finally working!

SELECT convert('Title' using utf8mb4) 'Title',
	convert('Year' using utf8mb4) 'Year',
	convert('Time' using utf8mb4) 'Time',
	convert('Rating' using utf8mb4) 'Rating',
	convert('Genre' using utf8mb4) 'Genre'  
UNION ALL
SELECT * from 
	(SELECT * from movies_i_want order by Title) a
INTO OUTFILE '/var/lib/mysql-files/movies_i_want.csv' 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"';

and the file it produces…

"Title","Year","Time","Rating","Genre"
"Amazon Women on the Moon","1987","85","R","Comedy,Sci-Fi"
"Blade of the Immortal","2017","140","R","Action,Drama"
"Bound","1996","108","NR","Crime,Romance,Thriller"
 ...

The first SELECT above produces a single row table with the column’s titles as the field values in the row. The table is not named.

mysql> SELECT 'Title', 'Year', 'Time', 'Rating', 'Genre';
+-------+------+------+--------+-------+
| Title | Year | Time | Rating | Genre |
+-------+------+------+--------+-------+
| Title | Year | Time | Rating | Genre |
+-------+------+------+--------+-------+

The full SELECT expression uses convert('Title' using utf8mb4) 'Title'… for each field because UNION failed from incompatible collations between the first SELECT’s row and the “SELECT ... movies_i_want” statement.

Collations can be associated with one character set. A character set can be associated with many collations. For collations to match, both character sets must match. The purpose of the first SELECT is to emit the column titles for the .csv export. It produces only one row, the column titles. Converting that SELECT’s character set to match the character set used in the live data table permitted the collation the UNION was doing to work.

Once the collations were compatible it was necessary to get each column’s title into the output as the first row of the output. The view used for the second SELECT has the same column titles in the same order as the first select. Its data needs to be the 2nd and subsequent rows.

After many attempts, several ways to state the problem were found. Unfortunately none included the column titles in the output. The column titles are a must have for free wpDataTables to show headings on the tables it displays from reading the .csv files.

ignoring the comment about collation to simplify the statements...
The following both work except for no column titles

SELECT 'Title', 'Year', 'Time', 'Rating', 'Genre'  
UNION ALL
SELECT * 
FROM movies_i_want order by Title;

SELECT 'Title', 'Year', 'Time', 'Rating', 'Genre'  
UNION ALL
SELECT 'Title', 'Year', 'Time', 'Rating', 'Genre' 
FROM movies_i_want order by Title;

To include the column titles in the output three SELECTs are needed. And be sure to include an alias for the ‘derived table’, the one created by the second SELECT.

SELECT 'Title', 'Year', 'Time', 'Rating', 'Genre'  
UNION ALL
SELECT * FROM (SELECT * FROM movies_i_want order by Title) a;

Now the output can be created in the necessary format using native MySQL. The method is a bit dense but I think simpler than the PHP, Use PHP to export MySQL to .csv.

With this and the PHP method of exporting the data a trigger is need to cause it to happen, either method can run on demand, or either method can run on a schedule.

A trigger, in other words “only when needed”, is my preferred approach. MySQL can be triggered by INSERT, UPDATE, and DELETE.

Solutions:

A MySQL trigger runs a SQL user procedure to export the data.
Problem: export destinations are limited to a single location with very restrictive access and it is not the location where either .csv file for the web tables is.

A MySQL trigger runs an external program, the PHP, to export the data.
Problem: can a trigger launch an external program?

Use PHP to export MySQL to .csv

The wpDataTables update when the source .csv files change. Next, update the source .csv files when the movies database is updated.

To update the Movies tables the underlying .csv source files must be updated and the wpDataTables tables’ links with those files must be refreshed. Without the link refresh the table doesn’t update even if the webpage is refreshed.

The .csv source files for the “Movies I Have” and “Movies I Want” tables are monitored for changes. The wpDataTables links are refreshed when the files have changed. The table display will update the next time the page is refreshed or landed on.

Another step in the chain is a database update, (INSERT, UPDATE, or DELETE), triggering the .csv file to be updated. And to make that happen, need to be able to do several things

  • Figure out how to create and trigger an event
    • Trigger the the event after INSERT, UPDATE, or DELETE on movies database
  • Write trigger(s) to a) export the new data and hopefully b) update the .csv files
  • Figure out HOW? to update the .csv files
    • PL/SQL export, then use non SQL program to update files with the export
    • PHP program that extracts the data and overwrites the .csv files
    • Program (PHP, PL/SQL) extracts the data to monitored files. Another program updates the .csv files with the data when the monitored files change.

I’ve put together the PHP to extract the data. Also need to see what can be done with SELECT * INTO OUTFILE. PHP may be able to do the whole process, extract records and update .csv file. MySQL may be able to do it with INTO OUTFILE but I think that’s very limited the destinations it can write to.

<?php
 $server = "myserver.boba.org"; 
 $username = "fan";
 $password = xx; 
 $database_name = "the_movies"; 
 $link_myMovies = mysqli_connect($server, $username, $password, $database_name);
 $Views = array("movies_i_want","movies_i_have");
 $out_path = "/var/tmp/";
 
 foreach ($Views as $view)
 {
	$query_result = null;
	$columns_total = null;
 	$str_query = "SELECT * FROM $view";
 	$query_result = mysqli_query($link_myMovies, $str_query);
 	$columns_total = mysqli_num_fields($query_result);
 
 	$col_names = array();
 
 	for ($i = 0; $i < $columns_total; $i++) 
	 {
	 	$Heading = mysqli_fetch_field_direct($query_result, $i);
	 	array_push($col_names,$Heading->name);
	 }
 
	 $fileOut = fopen("$out_path$view.csv", 'w') or die("Unable open ./$out_path$view.csv"); 
	 fputcsv($fileOut, $col_names);

	 while ($row = mysqli_fetch_array($query_result, MYSQLI_NUM)) 
	 {
	 	fputcsv($fileOut, array_values($row)); 
	 }
	 
	 fclose($fileOut) or die("Unable to close ./$view.csv");
}
?>