Execute an .sql script on mysql with secure installation
472 views
Skip to first unread message
Tiago Oliveira
Dec 20, 2021, 12:08:23 PM12/20/21
to Ansible Project
Hi,
I am trying to use an sql script to create a database in mariadb on a openbsd server with ansible.
My playbook first create the script with the name of the database to create and then uses expect to give the command "mysql -u root -p < script.sql" and then responses the password with "" because theres no password.
I try to use the script created by ansible and it works.
I try to use the same command that ansible is using and it works.
I took the "< script.sql" part to see if the problem was the password, and ansible enter in mysql without returning any error.
When i use the playbook with "< script.sql" in command, ansible returns the error of non-zero return code returning rc=1.
Anyone know how to solve it??
I've tried also the community.mysql module, but it didnt worked also to create the first database.
Best Regards,
Tiago Oliveira
Stefan Hornburg (Racke)
Dec 20, 2021, 12:14:36 PM12/20/21
to ansible...@googlegroups.com
On 20/12/2021 18:08, Tiago Oliveira wrote:
> Hi,
> I am trying to use an sql script to create a database in mariadb on a openbsd server with ansible.
> My playbook first create the script with the name of the database to create and then uses expect to give the command "mysql -u root -p < script.sql" and then responses the password with "" because theres no password.
> I try to use the script created by ansible and it works.
> I try to use the same command that ansible is using and it works.
> I took the "< script.sql" part to see if the problem was the password, and ansible enter in mysql without returning any error.
>
> When i use the playbook with "< script.sql" in command, ansible returns the error of non-zero return code returning rc=1.
>
> Anyone know how to solve it??
Maybe you start with sharing your playbook and the contents of the script!?
> Hi,
> I am trying to use an sql script to create a database in mariadb on a openbsd server with ansible.
> My playbook first create the script with the name of the database to create and then uses expect to give the command "mysql -u root -p < script.sql" and then responses the password with "" because theres no password.
> I try to use the script created by ansible and it works.
> I try to use the same command that ansible is using and it works.
> I took the "< script.sql" part to see if the problem was the password, and ansible enter in mysql without returning any error.
>
> When i use the playbook with "< script.sql" in command, ansible returns the error of non-zero return code returning rc=1.
>
> Anyone know how to solve it??
The modules mysql_user and mysql_db should be able to create your database with the associated users.
Regards
Racke
>
> I've tried also the community.mysql module, but it didnt worked also to create the first database.
>
> Best Regards,
>
> Tiago Oliveira
>
> You received this message because you are subscribed to the Google Groups "Ansible Project" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to ansible-proje...@googlegroups.com <mailto:ansible-proje...@googlegroups.com>.
> To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/44af35a2-29db-4847-a9fe-a1f27a0dc1ebn%40googlegroups.com <https://groups.google.com/d/msgid/ansible-project/44af35a2-29db-4847-a9fe-a1f27a0dc1ebn%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
Ecommerce and Linux consulting + Perl and web application programming.
Debian and Sympa administration.
Tiago Oliveira
Dec 20, 2021, 12:28:50 PM12/20/21
to Ansible Project
Yeah, sorry.
- name: Create a new .sql file for the table '{{table_name}}'
template:
src: templates/new_table_template
dest: /home/secnet/{{table_name}}.sql
expect:
command: mysql -u root -p < {{table_name}}.sql
responses:
'Enter password:': ''
register: wp
failed_when: wp.rc not in [ 0, 1 ]
template:
src: templates/new_table_template
dest: /home/secnet/{{table_name}}.sql
expect:
command: mysql -u root -p < {{table_name}}.sql
responses:
'Enter password:': ''
register: wp
failed_when: wp.rc not in [ 0, 1 ]
The mysql_db module cant enter in mysql, returns the error "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has
the credentials. Exception message: (1698, \"Access denied for user 'root'@'localhost'\")"
}
the credentials. Exception message: (1698, \"Access denied for user 'root'@'localhost'\")"
}
since i need to enter with root user.
Regards,
Tiago
Tiago Oliveira
Dec 20, 2021, 12:42:18 PM12/20/21
to Ansible Project
oops, wrong code.
- name: Create a new .sql file for the table '{{table_name}}'
template:
src: templates/new_table_template
dest: /home/secnet/{{table_name}}.sql
template:
src: templates/new_table_template
dest: /home/secnet/{{table_name}}.sql
- name: Create the new database with name '{{table_name}}'
expect:
command: mysql -u root -p < {{table_name}}.sql
responses:
'Enter password:': ''
register: wp
failed_when: wp.rc not in [ 0, 1 ]
This one is the right one
Stefan Hornburg (Racke)
Dec 20, 2021, 1:35:00 PM12/20/21
to ansible...@googlegroups.com
On 20/12/2021 18:42, Tiago Oliveira wrote:
> oops, wrong code.
>
> - name: Create a new .sql file for the table '{{table_name}}'
> template:
> src: templates/new_table_template
> dest: /home/secnet/{{table_name}}.sql
>
> - name: Create the new database with name '{{table_name}}'
> expect:
> command: mysql -u root -p < {{table_name}}.sql
> responses:
> 'Enter password:': ''
> register: wp
> failed_when: wp.rc not in [ 0, 1 ]
>
> This one is the right one
From expect module documentation:
> oops, wrong code.
>
> - name: Create a new .sql file for the table '{{table_name}}'
> template:
> src: templates/new_table_template
> dest: /home/secnet/{{table_name}}.sql
>
> - name: Create the new database with name '{{table_name}}'
> expect:
> command: mysql -u root -p < {{table_name}}.sql
> responses:
> 'Enter password:': ''
> register: wp
> failed_when: wp.rc not in [ 0, 1 ]
>
> This one is the right one
-- snip --
If you want to run a command through the shell (say you are using <, >, |, and so on), you must specify a shell in the command such as /bin/bash -c "/path/to/something | grep else".
-- snap --
Don't understand why you try to feed an empty password to MySQL. Do you use become: yes in your playbook? This would execute the task as root user.
Regards
Racke
>
>
> --
> Ecommerce and Linux consulting + Perl and web application programming.
> Debian and Sympa administration.
>
>
>
> --
> Ecommerce and Linux consulting + Perl and web application programming.
> Debian and Sympa administration.
>
>
> --
> You received this message because you are subscribed to the Google Groups "Ansible Project" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to ansible-proje...@googlegroups.com <mailto:ansible-proje...@googlegroups.com>.
> To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/8e92a586-abe3-493d-8506-563a82801b1cn%40googlegroups.com <https://groups.google.com/d/msgid/ansible-project/8e92a586-abe3-493d-8506-563a82801b1cn%40googlegroups.com?utm_medium=email&utm_source=footer>.
> You received this message because you are subscribed to the Google Groups "Ansible Project" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to ansible-proje...@googlegroups.com <mailto:ansible-proje...@googlegroups.com>.
Antony Stone
Dec 20, 2021, 2:17:52 PM12/20/21
to ansible...@googlegroups.com
On Monday 20 December 2021 at 18:08:23, Tiago Oliveira wrote:
> I am trying to use an sql script to create a database in mariadb on a
> openbsd server with ansible.
>
> My playbook first create the script with the name of the database to create
> and then uses expect to give the command "mysql -u root -p < script.sql"
> and then responses the password with "" because theres no password.
So, why don't you simply omit the "-p" to avoid being prompted for the non-
> I am trying to use an sql script to create a database in mariadb on a
> openbsd server with ansible.
>
> My playbook first create the script with the name of the database to create
> and then uses expect to give the command "mysql -u root -p < script.sql"
> and then responses the password with "" because theres no password.
existent password?
Antony.
--
I conclude that there are two ways of constructing a software design: One way
is to make it so simple that there are _obviously_ no deficiencies, and the
other way is to make it so complicated that there are no _obvious_
deficiencies.
- C A R Hoare
Please reply to the list;
please *don't* CC me.
Reply all
Reply to author
Forward
0 new messages