[SOLVED] substring not expanding in bash

vincix · 06-20-2018, 06:44 AM

I'm using the following script the import all database dumps that reside in /root/mysql.

Code:

#!/bin/sh
*# store start date to a variable
imeron=`date`
echo "Import started: OK"
for dumpfile in "/root/mysql/*"
        do
ddl="set names utf8; "
ddl="$ddl set global net_buffer_length=1000000;"
ddl="$ddl set global max_allowed_packet=1000000000; "
ddl="$ddl SET foreign_key_checks = 0; "
ddl="$ddl SET UNIQUE_CHECKS = 0; "
ddl="$ddl SET AUTOCOMMIT = 0; "
ddl="$ddl USE ${dumpfile##*/};"
ddl="$ddl source ${dumpfile##*/}; "
ddl="$ddl SET foreign_key_checks = 1; "
ddl="$ddl SET UNIQUE_CHECKS = 1; "
ddl="$ddl SET AUTOCOMMIT = 1; "
ddl="$ddl COMMIT ; "
echo "Import started: OK"
time mysql -h 127.0.0.1 -u root -ppassword -e "$ddl"
imeron2=`date`
echo "Start import:$imeron"
echo "End import:$imeron2"
done

When I run it, I get the following error:
ERROR 1049 (42000) at line 1: Unknown database '*'

The point of using ${dumpfile##*/} is, of course, to select only the file itself, and not the whole path. Any ideas how I make it expand to what it should be? The problem is that the quotes turn it into the asterix.

pan64 · 06-20-2018, 07:02 AM

probably you need to exchange # and * at the beginning of line 2.
also I recommend you to use www.shellcheck.net to check your shell scripts.

vincix · 06-20-2018, 07:37 AM

No, that's not the issue. Actually, this was just a c/p error. And mysql wouldn't have said "unknown database '*'" if you'd have only written * in its cli. The problem is that bash is not expanding correctly and I don't know how to make it do so.
I changed the USE and SOURCE lines into this:

Quote:

ddl="$ddl USE '${dumpfile##*/}'";
ddl="$ddl SOURCE '${dumpfile##*/}'";

But I get:
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''*' SOURCE '*' SET foreign_key_checks = 1' at line 1

Thanks for the link. I tested the script, seemed ok except this:
With regards to for dumpfile in "/root/mysql/*":
Since you double quoted this, it will not word split, and the loop will only run once.

jlinkels · 06-20-2018, 07:38 AM

The '*#' in line 2 of the script is definitely a bash error. It should have produced an error message but not more than that.

The error "ERROR 1049 (42000) at line 1: Unknown database '*'" refers to the result of the mysql execution. Since the variable $ddl is one line holding the command, the error messages refers to ddl. It is not a bash error, it is a mysql error.

I never used -e to execute a mysql command, but always

Code:

echo $ddl | myqsl

In general, the mysql command is:

Code:

mysql -u user -ppassword database

I am missing database in your command, but then again I never used the -e option

Some more hints to easy debugging:

Run the bash script with bash -x like in my signature
Use var=$(command) instead of var=`command`
Use string:${varname} instead of string:$varname

jlinkels

jlinkels · 06-20-2018, 07:41 AM

Now I see your post #3.

You have to echo the $ddl before you can make any assumption why there is an error in your command.

Copy/paste the echoed string in a mysql shell, see where it goes wrong, correct and debug there.

jlinkels

pan64 · 06-20-2018, 07:46 AM

I do not really like it. You post a script which is not the one you use, just something similar. Never mind.

You ought to insert set -xv at the beginning of your script and you will see what's happening.

michaelk · 06-20-2018, 07:50 AM

Quote:

Thanks for the link. I tested the script, seemed ok except this:
With regards to for dumpfile in "/root/mysql/*":
Since you double quoted this, it will not word split, and the loop will only run once.

What that means is that using quotes bash will not perform any wildcard expansion and the value of dumpfile will only be /root/mysql/* and therefore removing the path leaves * thus the unknown database error.

Remove the quotes and try your script again.

Code:

for dumpfile in /root/mysql/*
do
...
done

scasey · 06-20-2018, 08:50 AM

As an aside (tho I think that michaelk has identified the root cause)...

Is there some reason for the (to me) clumsy population of ddl?
Why not

Code:

ddl="set names utf8; \
set global net_buffer_length=1000000;\
set global max_allowed_packet=1000000000; \
SET foreign_key_checks = 0; \
SET UNIQUE_CHECKS = 0; \
SET AUTOCOMMIT = 0; \
USE ${dumpfile##*/};\
source ${dumpfile##*/}; \
SET foreign_key_checks = 1; \
SET UNIQUE_CHECKS = 1; \
SET AUTOCOMMIT = 1; \
COMMIT ; "

or maybe a here document to build your ddl?
...the

Code:

ddl="$ddl plus something else"

seems fraught with opportunities for typos, etc.

vincix · 06-20-2018, 09:07 AM

@pan64 Sorry, it was my mistake, the rest of the script is identical, I assure you (with the exception of the changes that I said I made).

@michaelk first I replaced them with single quotes, which was just as bad

Now I removed the double quotes from /root/mysql/* altogether.

Anyway, after adding the simple quotes like this

Code:

ddl="$ddl USE '${dumpfile##*/}'";

, as I said before, and removed the double quotes from /root/mysql/* and I ended up with the same error in post #3, which seems to be entirely a mysql error.

@scasey. There's no specific logic to the script. I copied it from somewhere and it worked just fine as it was for a single database. The problem is that I have lots of databases and I wanted it automated. so I created the for loop, which created these problems.

I added an echo line in the script like this:

Code:

ddl="$ddl USE '${dumpfile##*/}'";
echo -e "This is ddl variable: $ddl\n  "

Then for each file in /root/mysql I get an error like the following:

Code:

Import started: OK
This is ddl variable: set names utf8;  set global net_buffer_length=1000000; set global max_allowed_packet=1000000000;  SET foreign_key_checks = 0;  SET UNIQUE_CHECKS = 0;  SET AUTOCOMMIT = 0;  USE 'my_database'

Import started: OK
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''my_database' SOURCE 'my_database' SET foreign_key_checks = 1' at line 1

real    0m0.005s
user    0m0.000s
sys     0m0.000s
Start import:Wed Jun 20 16:46:06 EEST 2018
End import:Wed Jun 20 16:46:06 EEST 2018

hydrurga · 06-20-2018, 09:23 AM

Your 'This is ddl variable' line in your post above has been truncated. We need to see the whole variable value to check its validity.

scasey · 06-20-2018, 09:24 AM

According to my read of the USE syntax, the database name should not be quoted:

Code:

USE db_name

The syntax error is because of the quotes around the db_name.

Try removing the single quotes from this

Code:

ddl="$ddl USE '${dumpfile##*/}'";
ddl="$ddl SOURCE '${dumpfile##*/}'";

michaelk · 06-20-2018, 09:27 AM

Code:

ddl="$ddl USE '${dumpfile##*/}'";[/quote'

Do you need to quote the file name? The semicolon is after the ending " instead of before.

vincix · 06-20-2018, 09:58 AM

This is how it looks right now:

Code:

#!/bin/sh
# store start date to a variable
imeron=`date`
echo "Import started: OK"
set -x
for dumpfile in /root/mysql/*
        do
ddl="set names utf8; "
ddl="$ddl set global net_buffer_length=1000000; "
ddl="$ddl set global max_allowed_packet=1000000000; "
ddl="$ddl SET foreign_key_checks = 0; "
ddl="$ddl SET UNIQUE_CHECKS = 0; "
ddl="$ddl SET AUTOCOMMIT = 0; "
ddl="$ddl USE ${dumpfile##*/}; "
ddl="$ddl SOURCE ${dumpfile}; "
ddl="$ddl SET foreign_key_checks = 1; "
ddl="$ddl SET UNIQUE_CHECKS = 1; "
ddl="$ddl SET AUTOCOMMIT = 1; "
ddl="$ddl COMMIT; "
echo "Import started: OK"
time mysql -h 127.0.0.1 -u root -ppassword -e "$ddl"
imeron2=`date`
echo "Start import:$imeron"
echo "End import:$imeron2"
done

I placed the semicolons where they should be (before the ending quotes) and I removed the single quotes.

Now it seems to be working as expected. Thanks all for your patience

I do have another problem now, though, for some of the databases:
"Got error 168 from storage engine"
I've looked it up on the internet, and lots of people mentioned lack of disk space, which clearly isn't the issue in my case. Could it be related to the buffer length or max packet size variables in relation to the ram available, which is only 2GB?
I'm guessing I'd have to open a new thread for this subject

scasey · 06-20-2018, 12:03 PM

You're most welcome. It's important to understand that when any compiler or interpreter reports syntax errors that one should look for typos or mis-placed characters first. Mysql is very clear about where the error starts (at the beginning of the quoted chunk of code), but very vague about what the error is.

...and I don't know about "have to" open a new thread, but it would be the better way to go, IMO

Please mark the thread as [SOLVED]

pan64 · 06-21-2018, 01:04 AM

I would suggest you to use different syntax, which will make it more readable:

Code:

for dumpfile in /root/mysql/*
do
    cat > tmpfile << EOF
set names utf8;
set global net_buffer_length=1000000;
set global max_allowed_packet=1000000000;
SET foreign_key_checks = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;
USE ${dumpfile##*/};
SOURCE ${dumpfile};
SET foreign_key_checks = 1;
SET UNIQUE_CHECKS = 1;
SET AUTOCOMMIT = 1;
COMMIT;
EOF
echo "Import started: OK"
time mysql -h 127.0.0.1 -u root -ppassword < tmpfile
....
done

In this case you can also check the content of tmpfile if that was constructed properly.