Re: Unable to open database file

[Matthew Slowe]

Is MySQL working ok? Can you connect to it using the interactive client?

Sent from my mobile device.

On 6 Sep 2012, at 06:27, "Phil Garrelhas" <phil.ga...@gmail.com> wrote:

Hello All,

 

Having an issue with the setup of simplesamlphp as an idp.

 

We are running Linux Redhat, Apache, MySQL and PHP.

 

We would be the Identity Provider using accounts in our MySQL database.

 

I've completed the setup (or so I thought). The "Sanity Check" runs successfully.

 

But if I try to click on any other link, "Log in as administrator", "Diagnostics on hostname, port and protocol", "PHP info" and "Test Configured Authentication Sources", i receive "The Website cannot display page" error in Internet Explorer. The following errors display when I tail my messages log file.

 

Sep  5 15:55:02 [SERVER] simplesamlphp[20833]: 3 [XXXXXXXXXX] Error loading session: SQLSTATE[HY000] [14] unable to open database file
Sep  5 15:56:51 [SERVER] simplesamlphp[19923]: 3 [XXXXXXXXXX] Error loading session: SQLSTATE[HY000] [14] unable to open database file
Sep  5 16:29:52 [SERVER] simplesamlphp[20891]: 3 [XXXXXXXXXX] Error loading session: SQLSTATE[HY000] [14] unable to open database file
Sep  5 16:29:55 [SERVER] simplesamlphp[20830]: 3 [XXXXXXXXXX] Error loading session: SQLSTATE[HY000] [14] unable to open database file
Sep  5 16:30:10 [SERVER] simplesamlphp[20893]: 3 [XXXXXXXXXX] Error loading session: SQLSTATE[HY000] [14] unable to open database file
Sep  5 16:30:15 [SERVER] simplesamlphp[19926]: 3 [XXXXXXXXXX] Error loading session: SQLSTATE[HY000] [14] unable to open database file
Sep  5 16:30:24 [SERVER] simplesamlphp[20832]: 3 [XXXXXXXXXX] Error loading session: SQLSTATE[HY000] [14] unable to open database file
Sep  5 17:01:19 [SERVER] simplesamlphp[19928]: 3 [XXXXXXXXXX] Error loading session: SQLSTATE[HY000] [14] unable to open database file
Sep  5 17:01:25 [SERVER] simplesamlphp[20833]: 3 [XXXXXXXXXX] Error loading session: SQLSTATE[HY000] [14] unable to open database file
Sep  5 17:02:46 [SERVER] simplesamlphp[19922]: 3 [XXXXXXXXXX] Error loading session: SQLSTATE[HY000] [14] unable to open database file

 

If I remove the mysql credentials from the authsources.php file, then Internet Explorer says the page doesn't exist.

 

Thoughts on what I'm missing?

 

 

--
You received this message because you are subscribed to the Google Groups "simpleSAMLphp" group.
To view this discussion on the web visit https://groups.google.com/d/msg/simplesamlphp/-/BWgJGafZcPsJ.
To post to this group, send email to simple...@googlegroups.com.
To unsubscribe from this group, send email to simplesamlph...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/simplesamlphp?hl=en.

[Phil Garrelhas]

Yep. I can. I even assigned the MySQL account ALL permissions. Still the same error. 

Sent from my iPhone

[Olav Morken]

On Wed, Sep 05, 2012 at 14:05:38 -0700, Phil Garrelhas wrote:
[...]

> Sep 5 17:02:46 [SERVER] simplesamlphp[19922]: 3 [XXXXXXXXXX] Error loading
> session: SQLSTATE[HY000] [14] unable to open database file

These look like SQLite errors. Are you using SQLite to store the
session? Have you given your web server write access to the database
file? Maybe a SELinux policy is blocking access?

Best regards,
Olav Morken
UNINETT / Feide

[Phil Garrelhas]

Hi Olav,

 

Thank you for the suggestions. I  can't believe I didn't think to check SELinux.

 

Unfortunately, I put it into permissive mode to test, and still receive the same errors.

 

I don't believe that I'm using SQLite to store the sessions. How would I check to be sure?

 

I'm running a MYSQL database. In my simplesamlphp config.php file, the store.type => sql. Is that what you're referring to?

 

Thanks!

Phil..

[Steve Moitozo II]

It sounds like you didn't change the default store.sql.dsn in config.php

'store.sql.dsn' => 'sqlite:/path/to/sqlitedatabase.sq3',

-S2

--

Steve Moitozo II

--

You received this message because you are subscribed to the Google Groups "simpleSAMLphp" group.

To view this discussion on the web visit https://groups.google.com/d/msg/simplesamlphp/-/YPJETT7kySgJ.

[Phil Garrelhas]

Hi Steve,

 

We are using MySQL. Is SQLite still required for simplesamlphp?

 

I read through the simplesamlphp installation instructions, and didn't see a reference to it. Can you point me in the right direction?

 

Thanks,

 

Phil...

[Olav Morken]

On Wed, Sep 12, 2012 at 11:45:46 -0700, Phil Garrelhas wrote:
> Hi Steve,
>
> We are using MySQL. Is SQLite still required for simplesamlphp?
>
> I read through the simplesamlphp installation instructions, and didn't see
> a reference to it. Can you point me in the right direction?

It looks like you have changed the settings in config.php to store the
session in a database, but you haven't updated the DSN to the database
in the same file. Therefore you are trying to use the default, which is
just an example pointing to an SQLite database.

(Note that the database used for authentication is not necessarily the
same database as the one that is used for session storage. They are
therefore configured independently.

[Phil Garrelhas]

Hi Olav,

 

Thanks for the reply.

 

So, here is our scenario. Our website (www.example.com) is hosted with us. It is a Drupal site with a MySQL database. We would be using simplesamlphp to have our site acting as the Identity Provider. There is another service (www.example2.com) that members of our site would be able to access from within our site. This is why SSO is being implemented, so that www.example2.com can use the same credentials stored in our www.example.com MySQL database.

 

To set that up, I followed the installation instructions from simplesamlphp. I set up the config.php file to use sqlauth.

 

'store.type' => 'sql',

 

In my authsources, I have:

 

'example-sql' => array(
                'sqlauth:SQL',
                'dsn' => 'mysql:host=localhost;port=5432;dbname=www_example_ca',
                'username' => '(username)',
                'password' => '(password)',
                'query' => 'SELECT "uid", "name", "mail" FROM users',
        ),
        *
*
'drupal-userpass' => array(
        'drupalauth:UserPass',

        // The filesystem path of the Drupal directory.
        'drupalroot' => '/usr/local/apache2/htdocs/ocls',

        // Whether to turn on debug
        'debug' => true,
'attributes' => array(
                              array('drupaluservar' => 'uid',  'callit' => 'uid'),
                              array('drupaluservar' => 'name', 'callit' => 'cn'),
                              array('drupaluservar' => 'mail', 'callit' => 'mail'),
                              ),

Should the store.sql.dsn be pointing to the raw MySQL database file?

 

Since we are using Drupal here, I read that you can use a SQLite database for the session storage, and to set it up, you just need to create (touch) an empty file, and point the store.sql.dns to it.

 

'store.sql.dsn' => 'sqlite:/var/simplesamlphp/www/sqlite/simplesaml.sq3',

 

I've tried a few different options, and now I'm receiving the following error when I tail /var/log/messages

 

simplesamlphp[4688]: 3 [XXXXXXXXXX] Error loading session: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database

 

The simplesaml.sq3 file has the appropriate rights/group info. I've also turned off SELinux to test to see if it was an issue there as well.

 

Any ideas where to look next?

Thanks!

 

Phil...

[Phil Garrelhas]

Also, after changing the permission on both the sqlite database file and it's directory (to both have write access), I now get:

 

 simplesamlphp[4691]: 3 [XXXXXXXXXX] Error loading session: SQLSTATE[HY000] [14] unable to open database file

Seems like I'm back to square one!

 

Thoughts?

[Phil Garrelhas]

I guess everyone is fresh out of ideas? :)

 

I can't seem to figure this out. I'm sure the solution will be something very simple!

 

Phil...

[Phil Garrelhas]

Comel,

 

Thanks for the help.

 

As mentioned, I moved the simplesaml.sq3 file to /var/simplesaml/data/sqlite/simplesaml.sq3.

 

I performed the below steps.

 

The commands ran successfully. Although, I still receive the same page in Internet Explorer "Website cannot display the page. Most likely causes: website is under maintenance. website has a programming error".

 

When I tail /var/log/messages, I receive the same error as well:

 

 simplesamlphp[2049]: 3 [XXXXXXXXXX] Error loading session: SQLSTATE[HY000] [14] unable to open database file

Phil....

On Tuesday, 18 September 2012 17:52:03 UTC-4, comel wrote:

Try opening database with sqlite cli tool:

1) as root:

sqlite3 /var/simplesamlphp/www/sqlite/simplesaml.sq3 .schema

2) as user under which apache is running, e.g. www-data:

sudo -u www-data sqlite3 /var/simplesamlphp/www/sqlite/simplesaml.sq3 .schema

3) try with direct php call as user under which apache is running, e.g.

sudo -u www-data php -r '$db = new PDO("sqlite:/var/simplesamlphp/www/sqlite/simplesaml.sq3");'

And report the results...

BTW For security put sqlite database outside www directory, e.g. to /var/simplesamlphp/data/.

[comel]

Try (just temporary) with 'store.sql.dsn' => 'sqlite:/tmp/sqlitedatabase.sq3' in config.php. Do not touch the database file, it should be created automatically on first request. Give output of: ls -l /tmp/sqlitedatabase.sq3.


BTW If MySQL is working for you, why don't you use it for sessions?

[Phil Garrelhas]

Looks like it created the file. But I still get the same error in my web browser.

 

Although I no longer get an "unable to open database file" error in my /var/log/messages.

 

I still get the following error in my /var/log/httpd/logfile

 

PHP Parse error:  syntax error, unexpected T_STRING, expecting ')' in /var/simplesamlphp-test/config/authsources.php on line 34, referer: http://www.example.com/simplesaml/module.php/core/frontpage_config.php

 

Line 34 (through to line 40) in authsources looks like this:

 

 'example-sql' => array(
                'sqlauth:SQL',

                'dsn' => 'mysql:host=localhost;port=5432;dbname=www_example.com',
                'username' => 'dbusername',
                'password' => 'dbpassword',

                'query' => 'SELECT "uid", "name", "mail" FROM users',
        ),

Here is the output to the requested ls -l command.

 

[root@ tmp]# ls -l /tmp/simplesaml.sq3
-rw-r--r--. 1 apache apache 7168 Sep 21 11:09 /tmp/simplesaml.sq3

[Thijs Kinkhorst]

Hi Phil,

Op vrijdag 21 september 2012 17:22:46 schreef Phil Garrelhas:

> I still get the following error in my /var/log/httpd/logfile
>
> PHP Parse error: syntax error, unexpected T_STRING, expecting ')' in
> /var/simplesamlphp-test/config/authsources.php on line 34, referer:
> http://www.example.com/simplesaml/module.php/core/frontpage_config.php
> Line 34 (through to line 40) in authsources looks like this:
>
> 'example-sql' => array(
> 'sqlauth:SQL',
> 'dsn' =>
> 'mysql:host=localhost;port=5432;dbname=www_example.com', 'username' =>
> 'dbusername',
> 'password' => 'dbpassword',
> 'query' => 'SELECT "uid", "name", "mail" FROM users',
> ),

If you get this parse error about line 34 the reason is probably somewhere
before, not after that line; perhaps a missing quote, comma or semicolon in
that file causes parsing to fail at line 34. You can use php -l
authsources.php to check the file syntax.

PHP's syntax is described here:
http://www.php.net/manual/en/language.basic-syntax.php

BTW, you leaked your database password in your previous mail - depending on
your setup you may want to change it now.

Cheers,
Thijs

--
Thijs Kinkhorst <th...@uvt.nl> – LIS Unix

Universiteit van Tilburg – Library and IT Services • Postbus 90153, 5000 LE
Bezoekadres > Warandelaan 2 • Tel. 013 466 3035 • G 236 • http://www.uvt.nl