Hi guys,

I need to write in bash a script which do these things:

1) Login to MariaDB;
2) Truncate a table to make it empty;
3) Import data from a file to this table.

I found this guide https://notearena.com/lesson/how-to-access-databasemysql-mariadb-in-linux-shell-scripting/
and I'd like to know if it's the right way! :)

Now I do everything manually...

Regards.
^Bart

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 31/10/2023 15:42, ^Bart wrote:

Hi guys,

I need to write in bash a script which do these things:

1) Login to MariaDB;
2) Truncate a table to make it empty;
3) Import data from a file to this table.

I found this guide https://notearena.com/lesson/how-to-access-databasemysql-mariadb-in-linux-shell-scripting/ and I'd like to know if it's the right way! :)

Now I do everything manually...

Regards.
^Bart

I'd almost certainly write that in C or PHP. The APIS are very simple
for something like that.



--
If I had all the money I've spent on drink...
..I'd spend it on drink.

Sir Henry (at Rawlinson's End)

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Am 31.10.2023 um 16:42:12 Uhr schrieb ^Bart:

I found this guide https://notearena.com/lesson/how-to-access-databasemysql-mariadb-in-linux-shell-scripting/
and I'd like to know if it's the right way! :)

At first glace, that looks good, give it a try on a test database.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 10/31/2023 11:42 AM, ^Bart wrote:

Hi guys,

I need to write in bash a script which do these things:

1) Login to MariaDB;
2) Truncate a table to make it empty;
3) Import data from a file to this table.

I found this guide https://notearena.com/lesson/how-to-access-databasemysql-mariadb-in-linux-shell-scripting/ and I'd like to know if it's the right way! :)

Now I do everything manually...

Regards.
^Bart

You don't need anything but SQL and the CLI.

Just create a text file with the SQL commands necessary to use the
database, truncate the table and import from the file. Call it
reload.sql, for instance.

Then use the CLI source command to run that file, i.e.

mysql -u user -ppassword
source reload.sql

Or you could do it with

mysql -u user -ppassword < reload.sql


It's not a good idea to have the password in a file.

--
==================
Remove the "x"'s from my email address
Jerry Stuckle
stucklex.jerryx@gmail.com
==================

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 31/10/2023 16.42, ^Bart wrote:

Hi guys,

I need to write in bash a script which do these things:

1) Login to MariaDB;
2) Truncate a table to make it empty;
3) Import data from a file to this table.

I found this guide https://notearena.com/lesson/how-to-access-databasemysql-mariadb-in-linux-shell-scripting/ and I'd like to know if it's the right way! :)

Now I do everything manually...

The main issue with that script is the base64 encoded password, from
security standard point of view that is still as if the password was
stored in plain text.

As Jerry pointed out, you can just do everything with a simple file with
the sql commands and run it with the command line, but with the
exception that I wouldn't use the password in the command line but let
the mysql command for prompting me to enter the password, as otherwise
you store the plain text password in bash history.

mysql -hhost -uuser -p < reload.sql

sure if you need to automate this like running cron job, then you will
need to provide a password, then see to that the file is just readable
by the user it's executed as and that user ain't someone that people
just can login in with, don't forget that by default root will be able
to read the file.

You may also consider to create a store procedure that does everything
you need,

https://mariadb.com/kb/en/create-procedure/ https://mariadb.com/kb/en/stored-routine-privileges/ https://mariadb.com/kb/en/call/

then you don't have to have a script on each machine you may need to
execute the truncation and stuff from and of course you shouldn't let
every database user to use it, just those that are authorized to do the
job.

mysql -hhost -uuser -p database -e "CALL sp_you_created;"

--
//Aho

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

You don't need anything but SQL and the CLI.

Ok!

Just create a text file with the SQL commands necessary to use the
database, truncate the table and import from the file.  Call it
reload.sql, for instance.

Ok.

Then use the CLI source command to run that file, i.e.

mysql -u user -ppassword
source reload.sql

So... I should have a file where I'll store the login data and I could
name it like "login.sql" and inside of it I should insert the "reload.sql".

At the end I should add the link to "login.sql" in crontab because I
should run it every 5 minutes.

Or you could do it with

mysql -u user -ppassword < reload.sql

Ok.

It's not a good idea to have the password in a file.

I know but this is a test machine inside of my lan, it's not in DMZ and
just me or one colleague can login to it but you got the point about
security risks and in your opinion could I solve this "issue" with the
idea showed in the link which I posted in my first message or do you
have another idea?

Regards.
^Bart

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

The main issue with that script is the base64 encoded password, from
security standard point of view that is still as if the password was
stored in plain text.

I didn't think about this issue... :\

sure if you need to automate this like running cron job, then you will
need to provide a password, then see to that the file is just readable
by the user it's executed as and that user ain't someone that people
just can login in with, don't forget that by default root will be able
to read the file.

Yes, this is what I need a cron job to automate the process.

You may also consider to create a store procedure that does everything
you need,

https://mariadb.com/kb/en/create-procedure/ https://mariadb.com/kb/en/stored-routine-privileges/ https://mariadb.com/kb/en/call/

[CUT]

I didn't know it and this is a good idea to start to study something new
to improve "my skills" (and obviously to improve the security field!);
I'm working on a test machine in our lan (not in DMZ) so just me and one colleague can login there, now I need to solve this "problem" because
another colleague is waiting me with Knime to do queries from MariaDB
and I'll use the dirty way! :)

Really thanks also to you for this great reply! :)

Have a nice day!
^Bart

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

I'd almost certainly write that in C or PHP.  The APIS are very simple
for something like that.

Thanks for your reply, I used PHP years and years ago and I don't
remember it very well but really thanks to share this solution! :)

^Bart

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

I found this guide
https://notearena.com/lesson/how-to-access-databasemysql-mariadb-in-linux-shell-scripting/
and I'd like to know if it's the right way! :)

At first glace, that looks good, give it a try on a test database.

Thanks for your reply, sure I'll do it in a test db! :)

^Bart

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Thanks for your reply, I used PHP years and years ago and I don't
remember it very well but really thanks to share this solution! :)

I did in my crontab this:

*/5 * * * * /usr/bin/mysql -u my_username -h localhost -p my_password < /home/itoffice/query.sql

It doesn't work... :/

If I run query.sql after I did a manual login in MariaDB it works.

I think I must use php like what this site said: https://stackoverflow.com/questions/9948220/how-can-i-set-a-crontab-to-execute-a-mysql-query-and-log-the-output

I think also I should save php file not in my home folder like
/home/itoffice but in /var/www/html right?

^Bart

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 02/11/2023 09.50, ^Bart wrote:

You don't need anything but SQL and the CLI.

Ok!

Just create a text file with the SQL commands necessary to use the
database, truncate the table and import from the file.  Call it
reload.sql, for instance.

Ok.

Then use the CLI source command to run that file, i.e.

mysql -u user -ppassword < reload.sql

So... I should have a file where I'll store the login data and I could
name it like "login.sql" and inside of it I should insert the "reload.sql".

No, the sql file will only include the raw SQL queries in the same way
as you would have when you type in the mysql after you logged in.

Credentials can be stored in a configuration file, here is an example
for MySQL, but works exactly the same way for MariaDB:

https://www.serverlab.ca/tutorials/linux/database-servers/how-to-create-a-credential-file-for-mysql/

In you case I would maybe create a new user, create a profile (I hope
this part is in MariaDB). See to that the default login shell is
/sbin/nologin and then you see to that the cronjob is run as this user
(in this example: dbcronuser).

* 5 * * * dbcronuser (mysql --login-path=backups <
/home/dbcronuser/reload.sql)

--
//Aho

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 02/11/2023 11.46, ^Bart wrote:

Thanks for your reply, I used PHP years and years ago and I don't
remember it very well but really thanks to share this solution! :)

I did in my crontab this:

*/5 * * * * /usr/bin/mysql -u my_username -h localhost -p my_password < /home/itoffice/query.sql

this in /etc/crontab or did you use it as your personal crontab with
"crontab -e"?

if the later, then your user need to have the right to read the file and
of course have the right to access the content in the itoffice directory.

if /etc/crontab then add who is executing the command and it can be good
to put parentheses around command too

*/5 * * * * (root) (/usr/bin/mysql -umy_username -pmy_password
databasename <
/home/itoffice/query.sql)

I think I must use php like what this site said: https://stackoverflow.com/questions/9948220/how-can-i-set-a-crontab-to-execute-a-mysql-query-and-log-the-output

No you don't, it's just what he did.

I think also I should save php file not in my home folder like
/home/itoffice but in /var/www/html right?

That is much about how you use the php script, if you use it like a bash
script then no you don't put it there. If it's part of your web site,
then yes.

Php can be used for websites, scripting and even create gui
applications, it may not be the best option in all situations.

--
//Aho

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

*/5 * * * * (root) (/usr/bin/mysql -umy_username -pmy_password
databasename <
 /home/itoffice/query.sql)

*/5 * * * * (itoffice) (/usr/bin/mysql -u username -p password db_name < /home/it_office/query.sql)

It doesn't work :\ but it doesn't work also if I insert it in a php
script; here below is the working *.sql file used from MariaDB CLI:

USE db_name;
TRUNCATE items_temp;

LOAD DATA LOCAL INFILE '/home/itoffice/data_bridge/items.txt'
INTO TABLE items_temp
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(id_tc_anartic, cod_art, desc_art, ump, umi, coef_ump_umi, gest_maga,
cod_iva, cod_cat_cont, desc_cat_cont, cod_class_pat, art_stato,
disabilitato, ean, peso_netto, peso_lordo, um_peso, lunghezza,
larghezza, altezza, diametro , cod_cat01 , desc_cat01 ,
cod_cat02, desc_cat02, cod_cat03, desc_cat03, cod_cat04, desc_cat04,
cod_cat05, desc_cat05, cod_cat06, desc_cat06, cod_cat07,
desc_cat07, cod_cat08, desc_cat08, cod_cat09, desc_cat09, cod_cat10, desc_cat10, cod_cat11, desc_cat11, cod_cat12, desc_cat12,
cod_cat13, desc_cat13, cod_cat14, desc_cat14, cod_cat15, desc_cat15,
cod_cat16, desc_cat16, cod_cat17, desc_cat17, cod_cat18,
desc_cat18, cod_cat19, desc_cat19, cod_cat20, desc_cat20, dt_creazione, dt_last_upd);

I tried to insert it in a php file but in this case nothing happen :\

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Parametri da inserire per collegarsi al DB
$servername = "localhost";
$username = "username";
$password = "password";
$database = "database_name";
$options = [MYSQLI_OPT_LOCAL_INFILE => true];
$conn = mysqli_connect($servername, $username, $password, $database); $conn->options(MYSQLI_OPT_LOCAL_INFILE, true);

if (!$conn) {
die("Connessione al database fallita: " . mysqli_connect_error());
}

// Query SQL
$query = "USE database_name;
TRUNCATE items_temp;

LOAD DATA LOCAL INFILE '/home/itoffice/data_bridge/items.txt'
INTO TABLE items_temp
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(id_tc_anartic, cod_art, desc_art, ump, umi, coef_ump_umi, gest_maga,
cod_iva, cod_cat_cont, desc_cat_cont, cod_class_pat, art_stato,
disabilitato, ean, peso_netto, peso_lordo, um_peso, lunghezza,
larghezza, altezza, diametro, cod_cat01, desc_cat01, cod_cat02,
desc_cat02, cod_cat03, desc_cat03, cod_cat04, desc_cat04, cod_cat05, desc_cat05, cod_cat06, desc_cat06, cod_cat07, desc_cat07, cod_cat08, desc_cat08, cod_cat09, desc_cat09, cod_cat10, desc_cat10, cod_cat11, desc_cat11, cod_cat12, desc_cat12, cod_cat13, desc_cat13, cod_cat14, desc_cat14, cod_cat15, desc_cat15, cod_cat16, desc_cat16, cod_cat17, desc_cat17, cod_cat18, desc_cat18, cod_cat19, desc_cat19, cod_cat20, desc_cat20, dt_creazione, dt_last_upd)";

if (mysqli_multi_query($conn, $query)) {
echo "Query eseguita con successo.";
} else {
echo "Errore nell'esecuzione della query: " . mysqli_error($conn);
}

mysqli_close($conn);


I opened the php.ini file and I have:

; Allow accessing, from PHP's perspective, local files with LOAD DATA statements
; https://php.net/mysqli.allow_local_infile
mysqli.allow_local_infile = On

Regards.
^Bart

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 02/11/2023 15.07, ^Bart wrote:

*/5 * * * * (root) (/usr/bin/mysql -umy_username -pmy_password
databasename <
  /home/itoffice/query.sql)

*/5 * * * * (itoffice) (/usr/bin/mysql -u username -p password db_name < /home/it_office/query.sql)

It doesn't work :\ but it doesn't work also if I insert it in a php
script; here below is the working *.sql file used from MariaDB CLI:

What error mail is sent from crontab? should be sent to the local
machines root user unless you have set MAILTO to another email address.

in worst case use less and read the /var/spool/mail/root should be in
the end of the file.
sure you can use the alpine or mutt to read the mail too.

By the way, do you have characters like '#;" in your password? Those
tend to cause issues when using this way. I would recommend the usage of profile.


--
//Aho

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 11/2/2023 6:46 AM, ^Bart wrote:

Thanks for your reply, I used PHP years and years ago and I don't
remember it very well but really thanks to share this solution! :)

I did in my crontab this:

*/5 * * * * /usr/bin/mysql -u my_username -h localhost -p my_password < /home/itoffice/query.sql

It doesn't work... :/

If I run query.sql after I did a manual login in MariaDB it works.

I think I must use php like what this site said: https://stackoverflow.com/questions/9948220/how-can-i-set-a-crontab-to-execute-a-mysql-query-and-log-the-output

I think also I should save php file not in my home folder like
/home/itoffice but in /var/www/html right?

^Bart

No space between your -p and your password. This is different from
other parameters.

--
==================
Remove the "x"'s from my email address
Jerry Stuckle
stucklex.jerryx@gmail.com
==================

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

No space between your -p and your password.  This is different from
other parameters.

You got the point, it works without space from -p and password but...

It works from command line:
$ mysql -u MY_USER -pMY_PASSWORD < /home/itoffice/query.sql

It doesn't work from crontab -e also from root:
*/5 * * * * -u MY_USER -pMY_PASSWORD < /home/itoffice/query.sql

I don't know why... :\

^Bart

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 03/11/2023 14:42, ^Bart wrote:

No space between your -p and your password.  This is different from
other parameters.

You got the point, it works without space from -p and password but...

It works from command line:
$ mysql -u MY_USER -pMY_PASSWORD < /home/itoffice/query.sql

It doesn't work from crontab -e also from root:
*/5 * * * * -u MY_USER -pMY_PASSWORD < /home/itoffice/query.sql

I don't know why... :\

perhaps adding 'mysql' would help?

^Bart

--
"Nature does not give up the winter because people dislike the cold."

― Confucius

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 04/11/2023 09.42, ^Bart wrote:

perhaps adding 'mysql' would help?

I did it but I didn't past it on my reply! :)

I also inserted the full path of Mysql but... nothing appened...

Did you look at the mail from crontab? by default it goes to the
localhost root user (you can change that with setting the MAILTO
variable), if something wen t wrong while running the mysql command it
will be in that mail (if no output, no mail).

You could check your /var/log/mysql/mysql.{err,log} too

I hope the "-u MY_USER" was a typo, otherwise loose the space after '-u'.

--
//Aho

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

perhaps adding 'mysql' would help?

I did it but I didn't past it on my reply! :)

I also inserted the full path of Mysql but... nothing appened...

^Bart

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 11/4/2023 5:09 AM, J.O. Aho wrote:

On 04/11/2023 09.42, ^Bart wrote:

perhaps adding 'mysql' would help?

I did it but I didn't past it on my reply! :)

I also inserted the full path of Mysql but... nothing appened...

Did you look at the mail from crontab? by default it goes to the
localhost root user (you can change that with setting the MAILTO
variable), if something wen t wrong while running the mysql command it
will be in that mail (if no output, no mail).

You could check your /var/log/mysql/mysql.{err,log} too

I hope the "-u MY_USER" was a typo, otherwise loose the space after '-u'.

The space after the -u is fine. It's only the password that can't have
the space.

--
==================
Remove the "x"'s from my email address
Jerry Stuckle
stucklex.jerryx@gmail.com
==================

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Did you look at the mail from crontab? by default it goes to the
localhost root user (you can change that with setting the MAILTO
variable), if something wen t wrong while running the mysql command it
will be in that mail (if no output, no mail).

You could check your /var/log/mysql/mysql.{err,log} too

I hope the "-u MY_USER" was a typo, otherwise loose the space after '-u'.

I found this solutions:

*/5 * * * * /home/itoffice/job01.sh

This is job01.sh

#!/bin/bash
#sostituisce tutte le virgole presenti nel file con dei punti
sed -i -e 's/,/./gip' /home/itoffice/data_bridge/articoli.txt

/usr/bin/mysql -umy_username -pmy_password < /home/itoffice/articoli.sql

#copia del file importato nella directory di backup
mv -v /home/itoffice/data_bridge/articoli.txt "/home/itoffice/data_bridge_bck/articoli_$(date +"%d-%m-%Y_%H-%M".txt)"

I found these information to fix php load data:

https://itecnote.com/tecnote/php-load-data-local-infile-forbidden-after-php-mariadb-update/
https://itecnote.com/tecnote/php-load-data-local-infile-forbidden-after-php-mariadb-update/

But now I need to do other things in my company so... I know it's a
dirty way to use a *.sh file but I dont't have free time to fix it maybe
next weeks... ;)

Have a nice day!
^Bart

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 05/11/2023 11:23, ^Bart wrote:

Did you look at the mail from crontab? by default it goes to the
localhost root user (you can change that with setting the MAILTO
variable), if something wen t wrong while running the mysql command it
will be in that mail (if no output, no mail).

You could check your /var/log/mysql/mysql.{err,log} too

I hope the "-u MY_USER" was a typo, otherwise loose the space after '-u'.

I found this solutions:

*/5 * * * * /home/itoffice/job01.sh

This is job01.sh

#!/bin/bash
#sostituisce tutte le virgole presenti nel file con dei punti
sed -i -e 's/,/./gip' /home/itoffice/data_bridge/articoli.txt

Yes, the root will not have the same LANG settings as your user and
those you need to change the comma to full stop for decimal values.
You could have added the same LANG/LC to the cron and this hadn't been a
issue.

But now I need to do other things in my company so... I know it's a
dirty way to use a *.sh file but I dont't have free time to fix it maybe
next weeks... ;)

Tend to become one of those things that will be never fixed and next
time another ad-hoc solution is used to fix next issue.

--
//Aho

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)