Execute an .sql script on mysql with secure installation

[Tiago Oliveira]

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)]

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!?

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]

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 ]

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'\")"                      
}                                                                                                              

since i need to enter with root user.

 Regards,

Tiago

[Tiago Oliveira]

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

[Stefan Hornburg (Racke)]

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:

-- 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

> > 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> <https://groups.google.com/d/msgid/ansible-project/44af35a2-29db-4847-a9fe-a1f27a0dc1ebn%40googlegroups.com?utm_medium=email&utm_source=footer <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.
>
>

> --
> 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>.

[Antony Stone]

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-
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.