dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
2014
share rss forum feed

TuxRaiderPen

join:2009-09-19

MySQL SSL and local-infile settings

I tried this in the Linux/Unix forum, but got nothing but crickets.... same on MySQL forum, [U|K]buntu forums... so maybe some web developers can possibly shed some light on this....

In an attempt to consolidate distro's used down internally I am looking to replace development and testing servers from a mish mosh of RH and CentOS to Kbuntu 12.04 deriviatives.

I have some testing servers setup but MySQL is being a problem child with 2 issues.

1) SSL settings

I need to be able to replicate the db to the production servers which will be housed off site, I want to use SSL for this and for remote client (developer) connetions.

I have done what was done in the past for the older servers and followed along with 2 HOWTWO's with no success.... it still is disabled.

I generated keys, added ssl and the key locations to my.cnf, no joy.

»mifosforge.jira.com/wiki/display···n+Ubuntu

»www.howtoforge.com/how-to-set-up···-squeeze

I erased the generated keys and PEM's when I decided to try it a gain with a different procedure.

I disabled apparmor thinking it was an issue.

No joy.

2) LOAD LOCAL INFILE via phpmyadmin

Some of the data I need to import into our db(s) comes from exports in a CSV like format, we need extra columns for our internal use and improvement of the data in general... so we use myphpadmin and/or just php to upload the file and import via LOAD DATA to account for the new columns...

Unfortunately via myphpadmin we get "Errror #1148 used command not allowed with the version of MySQL"

The solution is:

local-infile
*OR*
local-infile = 1

in my.cnf in the [mysqld and [mysql] areas...

Did that, restarted mysql (more times than I can count) , myphpadmin still fails with this error.

Even with apparmor disbabled, doubtful its even related, no joy.

via CLI I can scp the file to the server and then do:

~$ mysql -umyuser -pmypass --local-infile=1 mydb
 

Thats not a viable option for the long haul process of automating this import process... temporarily while play its fine, if all the software works as it should, which means theres little to convert other host, user, pass etc... this automation process will run with out user review except for the normal bad data (original host allows embedded CR/LF in the data! URRRGRH! )....

I just can't get mysql to accept this via my.cnf

Distro is basically the minimal kubuntu with tasksel LAMP Server install process. It may be kubuntu based, but there is no X server. The box is buired in some cabinent some place, and SSH is the only access.

Server version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu)

The only things that have been done manually is to add things like wget, screen, some Perl stuff for CPAN use, build essential for building perl modules etc.. All the LAMP stuff is what comes from the tasksel process.

Any clues? ? Ideas? Hammer? C4? Cemtex? ?

--
1311393600 - Back to Black.....Black....Black....


ekiM
Oh Well

join:2001-01-06
/usr/home

Are the versions of MySQL the same across machines (and what is it)?

re #1
The second link you provided (the "Howto" site) is good/detailed.
Where in that process do you have failure?
Message in mysql errorlog is?

re#2
PHP/Mysql connectivity libraries are an inconsistent world between versions.
I do not understand why this can not be done via a shell script (scp and load).
I would use tried and true *nix and mysql tools. Another software layer doesn't help IMHO.

"chirp, chirp"
--
"I can't sing, I ain't pretty and my legs are thin."


TuxRaiderPen

join:2009-09-19

said by ekiM:
Are the versions of MySQL the same across machines (and what is it)?

On the testing servers yes
Server version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu)

For offisite/current production, 5.0.96-community

But since SSL is not enabled there is no way to test that part of replication.

said by ekiM:
The second link you provided (the "Howto" site) is good/detailed.
Where in that process do you have failure?

SSL is NOT enabled, thus no SSL connections can be made server to server for replication or for remote clients ie: MySQL Query Browser etc..

root@LAMPServer:~# mysql -u -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 166
Server version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu)
 
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> show variables like "%ssl%";
+---------------+-------------------------------------+
| Variable_name | Value                               |
+---------------+-------------------------------------+
| have_openssl  | DISABLED                            |
| have_ssl      | DISABLED                            |
| ssl_ca        | /etc/mysql/newcerts/ca-cert.pem     |
| ssl_capath    |                                     |
| ssl_cert      | /etc/mysql/newcerts/server-cert.pem |
| ssl_cipher    |                                     |
| ssl_key       | /etc/mysql/newcerts/server-key.pem  |
+---------------+-------------------------------------+
7 rows in set (0.19 sec)
 
mysql> ^CCtrl-C -- exit!
Aborted
root@LAMPServer:~# 
 

quote:
Disabled means mysql has ssl support but it's just not enabled (if you have 'NO' instead of 'DISABLED' then you don't have ssl support)
said by ekiM:
Message in mysql errorlog is?

No

said by ekiM:
PHP/Mysql connectivity libraries are an inconsistent world between versions.
I do not understand why this can not be done via a shell script (scp and load).

For current development doing the upload via scp, is an option for one file is an option, but phpMyADMIN is better as I am not really too thrilled with too many people accessing the server via shell. I prefer to have shell access pretty limited to maybe myself and a couple of others regardless of security setup etc.. No access is the best security. They have to do it via phpMyAdmin or some other way... Just because your a developer doesn't mean you get free access to stuff.

For PRODUCTION.. no dice.

There is EXISTING PHP code which should be and would be 1:1 replacement if this change in MySQL was not made. I need to be able to undo this "security enhancement."

The listed options for doing so are being ignored for some reason.

said by ekiM:
I would use tried and true *nix and mysql tools. Another software layer doesn't help IMHO.
Software that runs to check the import file, kick out bad lines of the improt file, ie: CR/LF in fields, etc.. exists already and would not need to be re-developed if it were not for this change in MySQL setup.

--
1311393600 - Back to Black.....Black....Black....


ekiM
Oh Well

join:2001-01-06
/usr/home
reply to TuxRaiderPen

If you are disabling SSL then what is the SSL question?
- Perhaps I'm missing something

As for the load file...
Why not have the users process the file via your existing scripts and then have cron perform the scp and load?

If you still want to do it all via PHP then you need to check for bugs across versions of the MySQL connection libraries against versions of PHP and MySQL. I do not think there is an *easy* way to fix the issue.
--
"I can't sing, I ain't pretty and my legs are thin."


TuxRaiderPen

join:2009-09-19

said by ekiM:
If you are disabling SSL then what is the SSL question?
- Perhaps I'm missing something
Yes your missing that I am NOT disabling it.

I am attempting to ENABLE IT.

I followed normal procedures as for previous setups, and the 2 listed links.

SSL will NOT ENABLE.

I am NOT disabling it. It won't enable to start.

said by ekiM:
As for the load file...
Why not have the users process the file via your existing scripts and then have cron perform the scp and load?
I don't think were on the same page with this.

I want a way to fix an issue with mysql defaults disabling this, and to reenable it.

Thanks but re-developing the software to run via scp etc. is not an option. And the scripts run just fine on versions of PHP 5.2.x to 5.3.x with out modification till this change by mysql.
--
1311393600 - Back to Black.....Black....Black....


ekiM
Oh Well

join:2001-01-06
/usr/home
reply to TuxRaiderPen

Some SSL ideas. I would try them individually...
Make sure your ssl keys have RSA header/footers.
Try excluding the hostname during key generation.
Try generating the keys with a non 12.04 version (prior) of openssl

These issues have caused folks problems.
The key generation is "incomplete* but has silent failures upon generation and/or use.
--
"I can't sing, I ain't pretty and my legs are thin."


watice

join:2008-11-01
New York, NY
reply to TuxRaiderPen

This is probably not the answer you are looking for. I would automate everything via CLI & bash scripts. This is my current setup. pull csv via wget, parse correctly with correct delimiters & fields, & local infile it. Setup a secure dir where your coworkers can scp their files in, & cron the parse/load on a regular basis as others suggested. If security is a concern & you'd rather not have everyone else have shell access, then... don't have everyone else have shell access! Best of luck.