[Dxspider-support] DBI interface error

[IZ5FSA]

The CREATE TABLE goes in error on my Server version: 
10.5.12-MariaDB-0+deb11u1 Debian 11

> ERROR 1064 (42000): 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 ''spot' ( 'rowid' int(11) NOT NULL AUTO_INCREMENT, 
> 'freq' double NOT NULL, 'sp...' at line 1
I have worked around it and found that it doesn't like sigle hyphen *'* 
but want the backtick *`*.

Now I have created the table and i have rebooted but DxSpider cannot 
still access to MariaDB.

> DBI connect('dxcluster:localhost:3306','sysop',...) failed: Access 
> denied for user 'sysop'@'localhost' (using password: YES)  at 
> /spider/perl/DXSql.pm line 63.
I've changed the DBI setup params into old parameters file (not Mojo branch)

> spider/local/DXVars.pm
and (1) access don't have error and (2) table can be populated.

I think that this feature in MOJO branch still point to old DXVars.pm path.

[SOLVED]

Thanks guys for your suggests.

73 de Leo IZ5FSA


Il 25/03/2022 09:03, Danilo Brelih via Dxspider-support ha scritto:
>
> If I remember correctly DXSpider doesn't create database itself. Try 
> creating database manually first.
>
> - Create database with:
>
>  CREATE DATABASE dxspider;
>
> - Give right privilages to the user you are using for dxspider with:
> |
> |
> |GRANT ALL PRIVILEGES ON dxspider.* to 'sysop'@'localhost';|
>
> - Creating table with:
>
> CREATE TABLE 'spot' ( 'rowid' int(11) NOT NULL AUTO_INCREMENT, 'freq' 
> double NOT NULL, 'spotcall' varchar(14) NOT NULL, 'time' int(11) NOT 
> NULL, 'comment' varchar(255) DEFAULT NULL, 'spotter' varchar(14) NOT 
> NULL, 'spotdxcc' smallint(6) DEFAULT NULL, 'spotterdxcc' smallint(6) 
> DEFAULT NULL, 'origin' varchar(14) DEFAULT NULL, 'spotitu' tinyint(4) 
> DEFAULT NULL, 'spotcq' tinyint(4) DEFAULT NULL, 'spotteritu' 
> tinyint(4) DEFAULT NULL, 'spottercq' tinyint(4) DEFAULT NULL, 
> 'spotstate' char(2) DEFAULT NULL, 'spotterstate' char(2) DEFAULT NULL, 
> 'ipaddr' varchar(40) DEFAULT NULL, PRIMARY KEY ('rowid'), KEY 
> 'spot_ix1' ('time'), KEY 'spot_ix2' ('spotcall'), KEY 
> 'spiderweb_spotter' ('spotter') ) ENGINE=InnoDB AUTO_INCREMENT=2598318 
> DEFAULT CHARSET=utf8mb4
>
> Reboot.
>
> GL Dan, S50U
>
>
> ----- Dne 25. Mar. 2022 ob 07:55 je The DXSpider Support list 
> <dxspider-support at tobit.co.uk> napisal(a):
>
>     Nope... error still remain the same.
>
>     Is there a documentation around the mysql/sqlite dxspider support?
>
>     reading source all seems to be nice...
>
>
>     Il 25/03/2022 00:20, Dirk Koopman via Dxspider-support ha scritto:
>     > Try changing the $dsn strings to be the same (i.e. lose the
>     ':3306').
>     >
>     > On 24/03/2022 23:04, IZ5FSA via Dxspider-support wrote:
>     >> Hi OMs,
>     >>
>     >> I have this error on access to MariaDB.
>     >>
>     >>> DBI connect('dxcluster:localhost:3306','sysop',...) failed:
>     Access
>     >>> denied for user 'sysop'@'localhost' (using password: YES )  at
>     >>> /spider/perl/DXSql.pm line 63.
>     >> In  /spider/local-new/DXVars.pm I have:
>     >>
>     >>> # the SQL database DBI dsn
>     >>> #$dsn = "dbi:SQLite:dbname=$root/data/dxspider.db";
>     >>> #$dbuser = "";
>     >>> #$dbpass = "";
>     >>>
>     >>> # the SQL database DBI dsn
>     >>> $dsn = "dbi:mysql:dxcluster:localhost:3306";
>     >>> $dbuser = "sysop";
>     >>> $dbpass = "MyPassword";
>     >> In MariaDB the user "sysop" have grant full access to the
>     dxcluster
>     >> database.
>     >>
>     >>
>     >> If I use this script, access is fine...
>     >>
>     >>> #!/usr/bin/perl
>     >>>
>     >>> use DBI;
>     >>>
>     >>> $source = "DBI:mysql:dxcluster:localhost";
>     >>> $username = "sysop";
>     >>> $password = "MyPassword";
>     >>>
>     >>> $dbc = DBI->connect($source, $username, $password)
>     >>> or die "Unable to connect to mysql: $DBI::errstr\n";
>     >>>
>     >>> $sql = $dbc->prepare("SELECT count(*) FROM spot_backup");
>     >>>
>     >>> $out = $sql->execute()
>     >>> or die "Unable to execute sql: $sql->errstr";
>     >>>
>     >>> while (($id, $name) = $sql->fetchrow_array())
>     >>> {
>     >>>  print "Id: $id Name: $name\n";
>     >>> }
>     >> ...and obviously it return
>     >>
>     >>> Id: 0 Name:
>     >> because table is empty.
>     >>
>     >> I cannot find any documentation of this DBI feature in docs.
>     >>
>     >> Where is the trick? In the MariaDB parameters? Anyone have
>     tryed this
>     >> kind of error?
>     >>
>     >> best 73 de Leo IZ5FSA
>     >>
>     >>
>     >>
>     >>
>     >> _______________________________________________
>     >> Dxspider-support mailing list
>     >> Dxspider-support at tobit.co.uk
>     >> https://mailman.tobit.co.uk/mailman/listinfo/dxspider-support
>     >
>     >
>     > _______________________________________________
>     > Dxspider-support mailing list
>     > Dxspider-support at tobit.co.uk
>     > https://mailman.tobit.co.uk/mailman/listinfo/dxspider-support
>
>     _______________________________________________
>     Dxspider-support mailing list
>     Dxspider-support at tobit.co.uk
>     https://mailman.tobit.co.uk/mailman/listinfo/dxspider-support
>
>
> _______________________________________________
> Dxspider-support mailing list
> Dxspider-support at tobit.co.uk
> https://mailman.tobit.co.uk/mailman/listinfo/dxspider-support
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mailman.tobit.co.uk/pipermail/dxspider-support/attachments/20220325/7dfac0e8/attachment-0001.htm>