Help with DbDrv error - connecting to database

mcarroll · 2017-09-29 07:17:41

Hi all, I have 2 Raspberry Pi's, one is connecting to my database fine, the other throws the following errors continuously:

2017-09-29;15:21:39.362;Error;DBDRV: SQL error detail (code=1065    state='S1000'    string='[MySQL][ODBC 5.1 Driver][mysqld-5.5.52-MariaDB]Query was empty');
2017-09-29;15:21:39.382;Error;DBDRV: SQL request failed (idx=3    result=-1    sql='');
2017-09-29;15:21:39.382;Error;DBDRV: SQL error detail (code=1065    state='S1000'    string='[MySQL][ODBC 5.1 Driver][mysqld-5.5.52-MariaDB]Query was empty');
2017-09-29;15:21:39.522;Error;DBDRV: SQL request failed (idx=0    result=-1    sql='');
2017-09-29;15:21:39.522;Error;DBDRV: SQL error detail (code=1065    state='S1000'    string='[MySQL][ODBC 5.1 Driver][mysqld-5.5.52-MariaDB]Query was empty');
2017-09-29;15:21:39.542;Error;DBDRV: SQL request failed (idx=1    result=-1    sql='');

I have swapped the config from the working unit to the other one, with the same result. Did I forget to do something on the second unit, when I configured the database connector?

Thanks in advance
Michael

Last edited by mcarroll (2017-09-29 07:18:12)

jaroslav_sobota · 2017-09-29 13:39:26

Hi Michael,
sorry to hear about your troubles. Something might be wrong with the ODBC connector configuration. What version of REX are you running on your Raspberry Pi?

RexCore -v

Can you please check the /etc/odbcinst.ini on both devices? Are they the same?

cat /etc/odbcinst.ini

Thanks,
Jaroslav

mcarroll · 2017-09-30 23:15:19

Thanks for the quick reply Jaroslav, I'm away for the weekend, but, I'll get back to you ASAP when I'm back with my Pi's!

mcarroll · 2017-10-04 02:12:31

Ok so working PI version:
RexCore version: 2.50.1 revision 7567
and /etc/odbcinst.ini:

[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1
[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1
[MySQL]
Description=MySQL Driver
Driver=libmyodbc.so
Setup=libodbcmyS.so
UsageCount=1
[PostgreSQL]
Description=PostgreSQL Driver
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
UsageCount=1
[MSSQL]
Description=Microsoft SQL (FreeTDS) Driver
Driver=libtdsodbc.so
Setup=libtdsS.so
UsageCount=1

The Pi thats not working:
RexCore version: 2.50.5 revision 8614
and /etc/odbcinst.ini:

[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1
[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1
[MySQL]
Description=MySQL Driver
Driver=libmyodbc.so
Setup=libodbcmyS.so
UsageCount=1
[PostgreSQL]
Description=PostgreSQL Driver
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
UsageCount=1
[MSSQL]
Description=Microsoft SQL (FreeTDS) Driver
Driver=libtdsodbc.so
Setup=libtdsS.so
UsageCount=1

Nothing is jumping out at me, other than the slight difference in versions of RexCore?

Anyway, thanks again in advance!

jaroslav_sobota · 2017-10-04 08:10:23

Hi Michael,
thanks for the information. For a complete record, what version of REX development tools do you have on your PC? In RexDraw, go to Help->About RexDraw. I should have asked for this in my previous post, sorry for that.

How critical is this issue for you? How fast do you need it working?

Thanks,
Jaroslav

tomáš čechura · 2017-10-05 13:18:12

Hi Michael,

please, can you send all your project files (including DB configuration file *.rio) so I can have a look at it? Post it here or send it to support(at)rexcontrols.com.

Thank you, Tomas

mcarroll · 2017-10-11 03:56:29

Thanks Tomas and Jaroslav, I've emailed the files in a 7zip to the support email address.

I forgot to add my version of Rex on my PC is 2.50.1

Ideally I'd like to have a solution within the next week or so. I realise the main holdup has been me

Michael

mcarroll · 2017-10-11 23:38:17

Hmmm, I notice that the most recent version is 2.50.5. What I might do (since I do have another fresh Pi handy) is uninstall and reinstall the latest version of the windows programmer and upload the config to the fresh Pi and see what happens.

mcarroll · 2017-10-12 05:08:01

OK still no go, fresh install of stretch, fresh windows install and Rexcore v2.50.5 produces the following error:

DBDRV: connect to 'DRIVER=MySQL;SERVER=***.**.*.**;PORT=3306;DATABASE=n**;UID=root;PWD=**********;;' failed!!!! ([01000] [unixODBC][Driver Manager]Can't open lib 'MySQL' : file not found)

*I blanked out the actual IP and database name, as well as password as I copied this straight from RexView

EDIT:Since it looks like a MySQL error, I'll install that and make sure I can connect to the database from the command line, which I was able to do before, even with the errors in Rex.

Last edited by mcarroll (2017-10-12 05:09:44)

mcarroll · 2017-10-12 05:57:26

I'm wondering if this is a Raspbian Stretch issue. I'm going to load Jessie and see if I have the same issues.

EDIT:Just confirmed that the 2 previous Pis' (both the working and non-working one) were using Jessie, so I probably chased my tail a bit on that one, anyway I'll try a fresh install of Jessie on the fresh Pi and see how we go.

Last edited by mcarroll (2017-10-12 06:17:13)

mcarroll · 2017-10-13 05:58:10

OK, so using a fresh install of Jessie seems to fix most of the problems. I'm just having the following error, which is obviously complaining about the syntax in the RIO file, which I'm not why.

DBDRV: SQL error detail (code=1064, state='37000', string='[MySQL][ODBC 5.1 Driver][mysqld-5.5.52-MariaDB]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'siteId, sensorId, temp) VALUES (11816, 'top#50', 1)' at line 1')

I'll have an ask of my database-ing friend, but, I've posted it here, in case someone has an insight to what the cause may be.

Thanks
Michael

tomáš čechura · 2017-10-16 11:16:21

Hi Michael,

it seems that you are missing first opening bracket in your SQL query. It should look like:

INSERT INTO rainfall (siteId, sensorId, temp) VALUES (11816, 'top#50', ?1);

Please, can you specify which combination of Raspbian image and REX Control System worked/did'n work for you? Just to see if the issue is on our site.

Thank you for cooperation.

Regards, Tomas

mcarroll · 2017-10-26 04:23:04

Thanks for that spot Tomas, I've modified that file so much, I'm deleting things that I shouldn't

Anyway I've mostly got everything sorted with your help. The only odd thing that is happening, is the system log is showing duplicate entry errors. I looks like it's trying to send 2 entries with the exact same datetime stamp. I've 'fixed' it by changing the line to

INSERT IGNORE

as the values are appearing in the database normally. Also, when a 0 value is sent, the value the database shows a blank line after the entry, which seems a bit weird.

I'm going to smother the 0 value send anyway, as I'm not interested in a 0 value rainfall, only the actual rainfall values. I'll check back in when I have this sorted out.

Michael.

Last edited by mcarroll (2017-10-26 04:24:03)

REXYGEN Community Forum

Announcement

REXYGEN forum has been moved. This one is closed.
Head over to the new REXYGEN Community Forum at https://forum.rexygen.com.

Looking forward to meeting you there!

#1 2017-09-29 07:17:41