Re-Connecting LO Base to an SQL DB

users
#1

Hi All

(In particular I hope *Heinz* picks this up as he was so helpful last time!!)

I have just upgraded my PClinuxOS system with the latest 2013.02 version. To do this I used an ISO disk and ran the upgrade without overwriting or formatting 'home' to preserve my data etc. It installed perfectly and almost all my stuff is available and working.

The two areas I have problems with are -
1) I have no sound (but will work on that later).
2) More importantly I can not access my MySQL Database!!

MySQL is part of the new installation and I have set it to 'Start on Boot'.
I re-installed MyAdmin via Synaptic
I should have the Connector in the installation.
I edited out the "skip networking" line in /etc/my.cnf

Now if I try to run MyAdmin I get the error ...

/"//The mysqli extension is missing. Please check your PHP configuration. <a href="Documentation.html#faqmysql" target="documentation"><img src="themes/dot.gif" title="Documentation" alt="Documentation" class="icon ic_b_help" /></a>"/

So I installed mysqli via Synaptic, did a re-boot and tried again but still get the same error!!

I decided to move on anyway and started Base, selected Connect to existing database, using JDBC, entered DB Name, localhost, port 3306 and tested the connection. It gives me "JDBC Driver loaded OK"

I accepted the Driver Class and tested and I get the error ...

/"Error code: 1045//
//Access denied for user 'ianw'@'localhost' (using password: YES)"/

Where have I gone wrong or can you see any errors on my part??

This MySQL Database was working perfectly before I upgraded the OpSys!!

Very many thanks as always!!

IanW
Pretoria RSA

#2

Hi Ian,
I don't use MyAdmin but prefer to use the mysql command-line client. This may
be a "fad" of mine, but I like to have "total control" of what I do by using
bare-bone commands...
Could it be that you have to specify something in the php.ini configuration
file regarding mysqli?
Regarding your connection problem: provided that you have added the .jar file
for the JDBC-connector (which it seems you have done!) the situation seems to most
likely have to do with missing privileges for your userid ianw, since I do assume
you entered the password when trying to connect to the server via LO-Base.
Sorry I can't be of more help. Good luck in any case.
Regards from snowed-in Salzburg
Heinrich

P.S.: below is a snippet of my php.ini

#3

Thanks Heinrich!!

I have looked at my php.ini file and the only difference with yours is that I DO NOT have the line "snipped" above!!

Could this be a problem??

IanW
Pretoria RSA

#4

Hi Ian,
I am sure this is of no consequence. From MySQLI runtime options...

mysqli.allow_local_infile integer
Allow accessing, from PHP's perspective, local files with LOAD DATA statements

Have you looked at ALL php.ini files on your system? I have a couple of those
hanging around. Actually, the most reliable place to look for active definitions
is through a file "phpinfo.php", i.e.

#5

Hi Ian,

MySQL is part of the new installation and I have set it to 'Start on Boot'.
I re-installed MyAdmin via Synaptic
I should have the Connector in the installation.
I edited out the "skip networking" line in /etc/my.cnf

Now if I try to run MyAdmin I get the error ...

/"//The mysqli extension is missing. Please check your PHP
configuration. <a href="Documentation.html#faqmysql"
target="documentation"><img src="themes/dot.gif" title="Documentation"
alt="Documentation" class="icon ic_b_help" /></a>"/

1) Is your mysql server even running ?

From the console/command line, try typing :

ps aux | grep mysql

This should return at least a line containing "mysqld" and mysql.sock
indicating that the server is up and running.

2) Now, from the same command line, try typing :

mysql -p

and enter the password for the current user. If your user didn't need a
password, remove the -p

Post the response here.

3)From the same command line, try typing :

mysqladmin -p extended-status

This should give a whole list of details about the running mysql server
(if indeed it is running). Again, the -p parameter is only necessary if
the user connecting to the mysql server requires a password to do so. If
not, you don't need the -p parameter.

Really, you should be asking these questions on the PCLinux forums,
these are not LO questions.

Alex

#6

Hi Alex

Thanks for your reply. (Sorry about the delay but for some reason yesterday I got no eMails in from the LO Forum???!!!)

1) Is your mysql server even running ?* YES*
>From the console/command line, try typing :

ps aux | grep mysql

[ian@localhost ~]$ ps aux | grep mysql
root 2946 0.0 0.0 4260 1476 ? S 10:17 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --mysqld=mysqld --pid-file=/var/run/mysqld/mysqld.pid
mysql 3071 0.0 1.1 124144 23072 ? Sl 10:17 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/localhost.localdomain.err --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --port=3306
ian 3537 0.0 1.8 198500 37676 ? Sl 10:17 0:00 /usr/sbin/mysqld --defaults-file=/home/ian/.local/share/akonadi/mysql.conf --datadir=/home/ian/.local/share/akonadi/db_data/ --socket=/home/ian/.local/share/akonadi/socket-localhost.localdomain/mysql.socket
ian 4805 0.0 0.0 4312 776 pts/1 S+ 10:28 0:00 grep --color mysql

2) Now, from the same command line, try typing :

mysql -p

and enter the password for the current user. If your user didn't need a
password, remove the -p

Post the response here.

[ian@localhost ~]$ mysql -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

3)From the same command line, try typing :

mysqladmin -p extended-status

This should give a whole list of details about the running mysql server
(if indeed it is running). Again, the -p parameter is only necessary if
the user connecting to the mysql server requires a password to do so. If
not, you don't need the -p parameter.

I get the whole list of details as you mention.

What I think the problem is - is that I now have a new installation of MySQL and thus it does not yet have any users or passwords set-up in it.

So what I tried was...

CREATE USER 'ianw'@'localhost' IDENTIFIED BY 'pwd';

Query OK, 0 rows affected (0.00 sec)

GRANT ALL ON db1.* TO 'ianw'@'localhost';

Query OK, 0 rows affected (0.00 sec)

But when I try to get into MyAdmin with this user and pwd I get

#1045 Cannot log in to the MySQL server

I'm stumped???

Thanks for the help - appreciated

IanW
Pretoria RSA

#7

Hi Ian,

> CREATE USER 'ianw'@'localhost' IDENTIFIED BY 'pwd';
Query OK, 0 rows affected (0.00 sec)

> GRANT ALL ON db1.* TO 'ianw'@'localhost';
Query OK, 0 rows affected (0.00 sec)

But when I try to get into MyAdmin with this user and pwd I get

#1045 Cannot log in to the MySQL server

I'm stumped???

Forget phpmyadmin (what PCLinuxOS seems to call MyAdmin, don't
understand why they renamed it and be the only distrib to do so ?) for
the moment.

From the console/command line, try to connect as the user ian that you

just created :

mysql -u ianw -p

Can you connect to mysql as user ianw from the command line ?

Alex

#8

>From the console/command line, try to connect as the user ian that you just created : mysql -u ianw -p
Can you connect to mysql as user ianw from the command line ?

Yes Alex I can!!

[ian@localhost ~]$ mysql -u ianw -p
Enter password:
Welcome to the MySQL monitor.

IanW
Pretoria RSA

#9

>From the console/command line, try to connect as the user ian that you just created : mysql -u ianw -p
Can you connect to mysql as user ianw from the command line ?

Yes Alex I can!!

[ian@localhost ~]$ mysql -u ianw -p
Enter password:
Welcome to the MySQL monitor.

IanW
Pretoria RSA

#10

This means that you should also be able to connect from LO. Looks like
your PHP and phmyadmin installation are screwed, can't help you there
I'm afraid, but you don't need that to connect to your databases from LO.

Alex

#11

Ian,
Which version of MySQL are you using?
Girvin Herr

Ian Whitfield wrote:

#12

Hi Gavin and Heinz,

Ian,
Which version of MySQL are you using? *MySQL 5.1.55 PCLOS 2011*

Heinz asked about 'SHOW GRANTS'

Well I'm in a real hole here!!!
This morning I decided to go back to basics and re-installed all the MySQL packages (in case)

I now have the situation that I can not CREATE USER so am unable to set any GRANTS!!
When I try I get the error message "ERROR 1396 (HY000): Operation CREATE USER failed for 'ianw'@'localhost'"

BUT, MORE SERIOUS - I think I have fallen in a really big hole!! What I did before I re-installed my OpSys was to back-up 'home' completely to an external drive. Then when I re-installed I set it NOT to over-write or use the 'home' partition and thus I maintained all my data on the drive. (or so I thought!!)

BUT - Now digging around on Google I see that (I think) MySQL does not keep its data in 'home' but rather in '/ver/lib'. There is such a folder on my system but I can not get into it even as SU.

So is this correct?? Have I now lost my Database?? As I did not back-up the root at all!!!!
If this is so I think it is very poor practice to store data outside the 'home' partition!!!!

Meanwhile phpMyAdmin will not work for me at all even after a re-install - so as I said I'm in a real hole!!

IF I have lost the Database is there a way to make it save it in the 'home' directory in future??

Thanks for the help

IanW
Pretoria RSA

#13

Ian,

Well I'm in a real hole here!!!
This morning I decided to go back to basics and re-installed all the
MySQL packages (in case)

Seriously, that was an unnecessary, and potentially, bad move. I have no idea how PCLinuxOS packages mysql server and its dependencies, so maybe you did indeed overwrite everything.

I now have the situation that I can not CREATE USER so am unable to set
any GRANTS!!
When I try I get the error message "ERROR 1396 (HY000): Operation CREATE
USER failed for 'ianw'@'localhost'"

Yep, sounds like you have overwritten the permissions table.

So is this correct?? Have I now lost my Database?? As I did not back-up
the root at all!!!!

Yes, the mysql data is stored in /var/lib/mysql/data, or sometimes in /var/mysql/data, it DEPENDS on the particular distrib as to where it finally gets put, but usually it is somewhere in a sub-directory of /var. It has been like this for as long as I can remember, and if you had read the mysql manuals like it has been suggested in the past you would know this.

If this is so I think it is very poor practice to store data outside the
'home' partition!!!!

No, it is perfectly reasonable, given that Mysql is a server daemon. Many well behaved Linux daemons (e.g. postfix, mail, printer spool, etc) tend to store their persistent data in /var, their configuration data in /etc, and so on, nothing new there.

You can also manually configure the data directory by editing the configuration files, providing you give the mysql server process the appropriate rights, but I think we are a long way from there at the moment.

Meanwhile phpMyAdmin will not work for me at all even after a re-install
- so as I said I'm in a real hole!!

IF I have lost the Database is there a way to make it save it in the
'home' directory in future??

Yes, change the directory in your mysql configuration files. Please read the fine manual before doing this, as changing the config files without knowing what, or being sure of what, you are doing, can make even your current situation worse.

Again, reading the official manual really helps one come to terms with how everything fits together. Also, because distribs all tend to do their own thing, read the documentation associated with your Linux distrib's version of mysql.

Note that the folder name is /var/lib and not /ver/lib. And yes, you generally require root privileges to be able to read the data in this directory.

Alternatively, you can pop in a Live Distro CD/DVD and use that to look through your file system, without having to be root.

I apologise for sounding imperious, but there really is no substitute for reading the manuals, or at least a decent mysql administrator's book, when it comes to mysql (or any other database server, for that matter).

Alex

#14

Ian,

Well I'm in a real hole here!!!
This morning I decided to go back to basics and re-installed all the
MySQL packages (in case)

Seriously, that was an unnecessary, and potentially, bad move. I have no idea how PCLinuxOS packages mysql server and its dependencies, so maybe you did indeed overwrite everything.

I now have the situation that I can not CREATE USER so am unable to set
any GRANTS!!
When I try I get the error message "ERROR 1396 (HY000): Operation CREATE
USER failed for 'ianw'@'localhost'"

Yep, sounds like you have overwritten the permissions table.

So is this correct?? Have I now lost my Database?? As I did not back-up
the root at all!!!!

Yes, the mysql data is stored in /var/lib/mysql/data, or sometimes in /var/mysql/data, it DEPENDS on the particular distrib as to where it finally gets put, but usually it is somewhere in a sub-directory of /var. It has been like this for as long as I can remember, and if you had read the mysql manuals like it has been suggested in the past you would know this.

If this is so I think it is very poor practice to store data outside the
'home' partition!!!!

No, it is perfectly reasonable, given that Mysql is a server daemon. Many well behaved Linux daemons (e.g. postfix, mail, printer spool, etc) tend to store their persistent data in /var, their configuration data in /etc, and so on, nothing new there.

You can also manually configure the data directory by editing the configuration files, providing you give the mysql server process the appropriate rights, but I think we are a long way from there at the moment.

Meanwhile phpMyAdmin will not work for me at all even after a re-install
- so as I said I'm in a real hole!!

IF I have lost the Database is there a way to make it save it in the
'home' directory in future??

Yes, change the directory in your mysql configuration files. Please read the fine manual before doing this, as changing the config files without knowing what, or being sure of what, you are doing, can make even your current situation worse.

Again, reading the official manual really helps one come to terms with how everything fits together. Also, because distribs all tend to do their own thing, read the documentation associated with your Linux distrib's version of mysql.

Note that the folder name is /var/lib and not /ver/lib. And yes, you generally require root privileges to be able to read the data in this directory.

Alternatively, you can pop in a Live Distro CD/DVD and use that to look through your file system, without having to be root.

I apologise for sounding imperious, but there really is no substitute for reading the manuals, or at least a decent mysql administrator's book, when it comes to mysql (or any other database server, for that matter).

Alex

#15

Alex Thurgood wrote:

Ian,

Well I'm in a real hole here!!!
This morning I decided to go back to basics and re-installed all the
MySQL packages (in case)

Seriously, that was an unnecessary, and potentially, bad move. I have no idea how PCLinuxOS packages mysql server and its dependencies, so maybe you did indeed overwrite everything.

I now have the situation that I can not CREATE USER so am unable to set
any GRANTS!!
When I try I get the error message "ERROR 1396 (HY000): Operation CREATE
USER failed for 'ianw'@'localhost'"

Yep, sounds like you have overwritten the permissions table.

So is this correct?? Have I now lost my Database?? As I did not back-up
the root at all!!!!

Yes, the mysql data is stored in /var/lib/mysql/data, or sometimes in /var/mysql/data, it DEPENDS on the particular distrib as to where it finally gets put, but usually it is somewhere in a sub-directory of /var. It has been like this for as long as I can remember, and if you had read the mysql manuals like it has been suggested in the past you would know this.

If this is so I think it is very poor practice to store data outside the
'home' partition!!!!

No, it is perfectly reasonable, given that Mysql is a server daemon. Many well behaved Linux daemons (e.g. postfix, mail, printer spool, etc) tend to store their persistent data in /var, their configuration data in /etc, and so on, nothing new there.

You can also manually configure the data directory by editing the configuration files, providing you give the mysql server process the appropriate rights, but I think we are a long way from there at the moment.

Meanwhile phpMyAdmin will not work for me at all even after a re-install
- so as I said I'm in a real hole!!

IF I have lost the Database is there a way to make it save it in the
'home' directory in future??

Yes, change the directory in your mysql configuration files. Please read the fine manual before doing this, as changing the config files without knowing what, or being sure of what, you are doing, can make even your current situation worse.

Again, reading the official manual really helps one come to terms with how everything fits together. Also, because distribs all tend to do their own thing, read the documentation associated with your Linux distrib's version of mysql.

Note that the folder name is /var/lib and not /ver/lib. And yes, you generally require root privileges to be able to read the data in this directory.

Alternatively, you can pop in a Live Distro CD/DVD and use that to look through your file system, without having to be root.

I apologise for sounding imperious, but there really is no substitute for reading the manuals, or at least a decent mysql administrator's book, when it comes to mysql (or any other database server, for that matter).

Alex

Ian,
Yes, you can do it, but just because you can, doesn't make it right. Storing the database in /home is a _very bad idea_! /home is the least secure location, where, as you found out, the current location in /var/lib won't even allow SU to get into it, but I think that is because you have not configured MySQL correctly yet. You need to go back and follow the initial MySQL setup procedure again, as you did in the past. You did write it down, didn't you? This is also a good example of the consequences of not backing up properly. In a past thread of yours, I am sure I mentioned the use of mysqldump, which creates a human-readable SQL backup of your database. If you had done that regularly, you would have a backup to re-create (restore) your database using the mysql program. Without that backup, I am afraid it looks like you are out of luck. With mysqldump, you can specify where the backup file goes, even /home, if you don't care about security. We learn by experience. Make a backup script, as I have, and run it at least monthly to make the backups easy. _Always_ run that backup before you do any messing with system software updates. I am not sure if Base does this, but some database clients (Rekall, for example) use the database to store the table, form, and report definitions. If that is so with Base, then you may have lost all the work you have done in Base also.
Sorry for the bad news.
Girvin Herr

#16

Ian,

Can you log into a TTY console as root ?
Press Ctrl+F1, this should switch you from the desktop GUI to a full screen console.

Login as root, enter the root password.
Now try reading the contents of /var and /var/lib

ls -an /var

if you have a /var/lib/mysql directory, list the contents of that too.

Some versions of msyql put the data in a DATA directory, instead of directly in /var/lib/mysql. If you have some data in there, such as an ibdata file or a FRM file that are not empty (ib_data files can be quite big), then you have a chance to recover your data.

Read up on how to reset the mysql privileges for your Linux distribs :

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

http://www.cyberciti.biz/tips/recover-mysql-root-password.html

Ask on the PCLinuxOS forums.

Alex

#17

Thanks Alex and Girvin.

(Sorry finger trouble - I know it's /var and not /ver!!)

I have accepted what has happened and am busy re-building my DB from scratch! IMHO it is still WRONG to store Data in an area that gets overwritten every time you do a upgrade or re-install. In over 30 years in computers I have never seen this before!!! Maybe on big Mainframe set-ups but defiantly not on Home Computer systems.
I will look into re-directing the data to a safer location once I have my DB back in one piece!!

The mistake I made was not knowing about this so when I re-installed my OpSys it formatted the area where the DB was stored so no chance of getting back at it!!

Girvin - I hear what you say about RTFM!! BUT - I would qualify this, It is something I have always recommended and do practice but in this case I'm just a "user". I want to set-up a DB and use it on a daily basis. I don't need, or am interested in, at this stage, becoming an SQL expert. Therin lies a big difference I think!!

So current situation - I managed to get MyAdmin working again by Flushing the Grant Tables so have set-up my new SQL DB, have linked it to Base and am currently trying to get my CSV data into it. I guess a week or twos work ahead of me at least and then, as I said, I will look into how to move the whole thing to a SAFE location.

Thanks for all the great help guys - it's greatly appreciated here!!

Regards from a nice hot South Africa!!

IanW
Pretoria RSA.

#18

Hi Ian,

I have accepted what has happened and am busy re-building my DB from
scratch! IMHO it is still WRONG to store Data in an area that gets
overwritten every time you do a upgrade or re-install. In over 30 years

Usually, an simple upgrade of mysql will not overwrite your tables.
Sometimes, it will create a new data directory, and keep your previous
data in an old/renamed directory (e.g. migrated, or "old", or even
tagged with the previous version number) - this is what happens on OSX,
for example. However, what happens exactly is very dependent on the
Linux distribution's version of mysql that they happen to have modified
to suit their system. Each distro tends to do its own thing, which can
be immensely frustrating. Debian based distros like Ubuntu/Mint and
consorts tend to use debconf, with which, personally, I still have
rather a hard time. PCLinuxOS used to be Mandrake based, but I'm not so
sure now. I imagine that RedHat and Suse have yet another mechanism for
auto-configuring the setup and migration of mysql data when upgrading
versions. So, as usual with many things Linux, YMMV, which as a casual
user can be a right, royal PITA.

If, however, you do a fresh install of your operating system, well,
then, I would say that it is to be expected that your data on the root
drive gets wiped. There are various ways to mitigate this, including
having separate partitions for /(root), /usr (for most graphical
applications and their basic configuration data), /var (for stuff like
mailserver and database data) and /home (for users' data and
configuration files), but most distributions do not offer that in the
simplified installation routine, where everything tends to get installed
into a single /(root) partition. If you keep things separate as
indicated above, you can even re-install your OS (up to a certain
extent, at least) without trashing your /home or other sensitive or
important data areas.

Anyway, enough of the preaching, you live and learn as they say, I know
I certainly did, but it did involve frequent hair pulling at times !!

Alex