Tag: mariadb

phpMyAdmin and MariaDB with Docker
I used to run a MariaDB server on an old Linux machine for working with database dumps and other things, but after moving all of that to a new Raspberry Pi 4, I never really set it back up.

So instead I thought I’d play with this fancy new Docker stuff all the cool kids are talking about. I ended up getting phpMyAdmin and MariaDB working with Docker on my MacBook Pro.

Caveat:

To start up the MariaDB container thing, I ran this command:
```
docker run -d \
 -v $HOME:/home/hosthome \
 --name mariadb \
 -e MYSQL_ROOT_PASSWORD=hunter2 \
 -e MYSQL_DATABASE='default_db' \
 mariadbCode language: JavaScript (javascript)
```
A few things here you might want to make note of:
- hunter2 – My MariaDB root password.
- default_db – The default database created, just to make things easy.
- $HOME – This attached my local machine’s home directory to the MariaDB container so I can import/export files.
Secondly, I ran this to start phpMyAdmin and connect it to the Docker container:
```
docker run -d \
 --name phpmyadmin \
 --link mariadb:db \
 -p 8081:80 \
 -e UPLOAD_LIMIT='4096M' \
 phpmyadmin/phpmyadminCode language: JavaScript (javascript)
```
A few things here you might want to make note of:
- 8081 – This is the local machine port that I will connect to via HTTP
- 4096M – The default upload limit, set to 4 Gigs.
Now, once this done, you should be able to connect to phpMyAdmin via http://localhost:8081/ and do all sorts of terrible things.

Here’s a few more commands that may come in handy:

Start a shell in the MariaDB container: docker exec -it mariadb bash

Import a SQL file: docker exec -i mariadb sh -c 'exec mysql -uroot -phunter2 default_db' < /some/path/on/your/host/all-databases.sql

Start a MariaDB “mysql” shell: docker exec -it mariadb sh -c 'exec mysql -uroot -phunter2 default_db'

References that helped me:
October 31, 2020

Converting CSV to SQL

I was recently working on an issue that required me to dig through gigabytes of CSV files to look for patterns and data points. I had no intention of bringing all of this data in to Excel, because there’s not enough RAM on earth for that to work. Instead I thought it would be easier to dump the data into MariaDB (MySQL would be fine as well) and just write some simple SQL statements.

There were a few things I tried, such as LOAD DATA INFILE and other SQL type things. Nothing really worked properly, so instead I decided to just convert the CSV files into SQL statements I could import.

This is a bad idea! Don’t ever let this touch a live production database!

I wrote this little PHP script to convert all of the CSV files in a directory to SQL:

<?php
$files    = glob( './*.{csv}', GLOB_BRACE );
$db_table = 'csv_table';

foreach ( $files as $file ) {
	$row       = 0;
	$handle    = fopen( $file, 'rb' );
	$row_names = '';

	if ( false !== $handle ) {
		while ( false !== ( $data = fgetcsv( $handle ) ) ) {
			$row++;
			$values = '';

			if ( 1 === $row ) {
				// Do Header things.
				$index  = 0;

				echo sprintf( 'DROP TABLE IF EXISTS `%s`;' . PHP_EOL, $db_table );
				echo sprintf( 'CREATE TABLE `%s` (' . PHP_EOL, $db_table );

				foreach ( $data as $key => $value ) {
					$index++;
					$data[ $key ] = strtolower( preg_replace( '/[^A-Za-z0-9]/', '_', $value ) );

					// Add a comma if we're not at the end of the array.
					if ( count( $data ) !== $index ) {
						echo sprintf( '	`%s` text NOT NULL,' . PHP_EOL, $data[ $key ] );
					} else {
						echo sprintf( '	`%s` text NOT NULL' . PHP_EOL, $data[ $key ] );
					}
				}

				echo ') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;' . PHP_EOL;
				$row_names = '(`' . implode( '`,`', $data ) . '`)';
				continue;
			}

			foreach ( $data as $key => $value ) {
				$data[ $key ] = fake_sql_escape( $value );
			}

			$values = '(\'' . implode( '\',\'', $data ) . '\')';

			echo sprintf( 'INSERT INTO `%s` %s VALUES %s;' . PHP_EOL, $db_table, $row_names, $values );
		}
		fclose( $handle );
	}
}

function fake_sql_escape( $value ) {
	// https://stackoverflow.com/questions/1162491/alternative-to-mysql-real-escape-string-without-connecting-to-db/1163091#1163091
	// Totaly not safe for production!
	$return = '';
	$length = strlen( $value );

	for ( $i = 0; $i < $length; ++$i ) {
		$char = $value[ $i ];
		$ord  = ord( $char );
		if (
			"'" !== $char &&
			'"' !== $char &&
			'\\' !== $char &&
			$ord >= 32 &&
			$ord <= 126
		) {
			$return .= $char;
		} else {
			$return .= '\\x' . dechex( $ord );
		}
	}
	return $return;
}Code language: PHP (php)

Borrowing the example airtravel.csv from https://people.sc.fsu.edu/~jburkardt/data/csv/csv.html this is what the output looks like:

DROP TABLE IF EXISTS `csv_table`;
CREATE TABLE `csv_table` (
	`month` text NOT NULL,
	`1958` text NOT NULL,
	`1959` text NOT NULL,
	`1960` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('JAN','  340','  360','  417');
INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('FEB','  318','  342','  391');
INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('MAR','  362','  406','  419');
INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('APR','  348','  396','  461');
INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('MAY','  363','  420','  472');
INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('JUN','  435','  472','  535');
INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('JUL','  491','  548','  622');
INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('AUG','  505','  559','  606');
INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('SEP','  404','  463','  508');
INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('OCT','  359','  407','  461');
INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('NOV','  310','  362','  390');
INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('DEC','  337','  405','  432');Code language: JavaScript (javascript)

And if you’re too lazy to see what the input CSV looks like (I would be) here it is, in its LGPL glory:

"Month", "1958", "1959", "1960"
"JAN",  340,  360,  417
"FEB",  318,  342,  391
"MAR",  362,  406,  419
"APR",  348,  396,  461
"MAY",  363,  420,  472
"JUN",  435,  472,  535
"JUL",  491,  548,  622
"AUG",  505,  559,  606
"SEP",  404,  463,  508
"OCT",  359,  407,  461
"NOV",  310,  362,  390
"DEC",  337,  405,  432Code language: JavaScript (javascript)

There’s a lot of ways that this could be made better, but it works in a quick pinch. If you need something similar, feel free to take this as a starting point.

August 26, 2020

Tag: mariadb

phpMyAdmin and MariaDB with Docker

Converting CSV to SQL