refdb-users

[Refdb-users] Cannot create database in MySQL

[Torsten B. <br...@ph...>]

Hallöchen!

I installed RefDB 0.9.8 on an Ubuntu Server 8.04 running MySQL
(which was already there, as was an Apache).  The installation
showed no error messages, however, createdb fails with

    refdba: createdb references
    999:0 created:1 failed

The daemon says

    bronger@bob:~$ sudo refdbd -s -e 0 -l 7
    dbi_driver_dir went to:

    dbi is up using default driver dir
    Available libdbi database drivers:
    mysql
    sqlite
    Requested libdbi driver found
    Database directory:
    /var/lib/refdb/db
    application server started
    share extended notes by default
    use /tmp/refdbd_fifo19841 as fifo
    server waiting n_max_fd=4
    adding client 127.0.0.1 on fd 5
    server waiting n_max_fd=5
    try to read from client
    serving client on fd 5 with protocol version 5
    210-44-10-38
    send pseudo-random string to client
    parent removing client on fd 5
    server waiting n_max_fd=4
    createdb references -u root -w 07909**********
    dbi is up
    localhost
    root
    *****


    mysql
    /var/lib/refdb/db

    refdb
    connected to database server using database:
    refdb
    updated version file:
    /var/lib/refdb/db/DB_VERSION
    Main database looks ok:
    refdb
    localhost
    root
    *****


    mysql
    /var/lib/refdb/db
    UTF-8
    refdb
    connected to database server using database:
    refdb
    SELECT VERSION()
    CREATE DATABASE references CHARACTER SET 'utf8'
    1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'references CHARACTER SET 'utf8'' at line 1
    could not create reference database
    command processing done, finish dialog now
    child finished client on fd 5
    child exited with code 0
    server waiting n_max_fd=4

The MySQL database uses by default InnoDB with UTF8.

Can someone tell me what's going wrong?  Thank you!

Tschö,
Torsten.

-- 
Torsten Bronger, aquisgrana, europa vetus
                   Jabber ID: tor...@ja...

[Refdb-users] Cannot create database in MySQL

[Markus H. <mar...@mh...>]

Torsten Bronger writes:
 >     CREATE DATABASE references CHARACTER SET 'utf8'
 >     1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'references CHARACTER SET 'utf8'' at line 1

Which mysql version are you using? I'd like to check why mysql
complains about the syntax (which apparently works ok in versions 4.1
through 5.0).

regards,
Markus

-- 
Markus Hoenicka
mar...@ca...
(Spam-protected email: replace the quadrupeds with "mhoenicka")
http://www.mhoenicka.de

Re: [Refdb-users] Cannot create database in MySQL

[Torsten B. <br...@ph...>]

Hallöchen!

Markus Hoenicka writes:

> Torsten Bronger writes:
>
>>     CREATE DATABASE references CHARACTER SET 'utf8'
>> 1064: You have an error in your SQL syntax; check the manual that
>> corresponds to your MySQL server version for the right syntax to
>> use near references CHARACTER SET 'utf8'' at line 1
>
> Which mysql version are you using?

I use MySQL 5.0.67.  What I changed from the default configuration
is:

default-storage_engine = innodb
character-set-server=utf8
collation-server=utf8_unicode_ci
init_connect='set collation_connection = utf8_unicode_ci;'

Tschö,
Torsten.

-- 
Torsten Bronger, aquisgrana, europa vetus
                   Jabber ID: tor...@ja...

Re: [Refdb-users] Cannot create database in MySQL

[Torsten B. <br...@ph...>]

Hallöchen!

Markus Hoenicka writes:

> Torsten Bronger writes:
>
>>     CREATE DATABASE references CHARACTER SET 'utf8'
>> 1064: You have an error in your SQL syntax; check the manual that
>> corresponds to your MySQL server version for the right syntax to use near
>> references CHARACTER SET 'utf8'' at line 1
>
> Which mysql version are you using? I'd like to check why mysql
> complains about the syntax (which apparently works ok in versions
> 4.1 through 5.0).

I must correct myself.  First, I mixed up the MySQL version on two
of my machines.  The server runs with 5.0.51a.

Secondly, the name "references" is bad for the database because it
seems to be a reserved word or something like that (unfortunately,
at the same time it's a good name for a references DB ;-).  With
"biblio", it worked.

However, I run into trouble here and there because "deb
http://refdb.sourceforge.net/debian/svn testing main" gives me an
outdated version (from March 2007 or so).  Is it feasible to update
this?  If not, I'm highly probably able to install the SVN version.
:)

Tschö,
Torsten.

-- 
Torsten Bronger, aquisgrana, europa vetus
                   Jabber ID: tor...@ja...

Re: [Refdb-users] Cannot create database in MySQL

[Markus H. <mar...@mh...>]

Torsten Bronger writes:
 > Secondly, the name "references" is bad for the database because it
 > seems to be a reserved word or something like that (unfortunately,
 > at the same time it's a good name for a references DB ;-).  With
 > "biblio", it worked.

According to the MySQL manual "references" is a clause in the
"alter table" syntax, so it is indeed a reserved word. I'll add this
to the documentation as it is all too easy to get trapped here.

 > 
 > However, I run into trouble here and there because "deb
 > http://refdb.sourceforge.net/debian/svn testing main" gives me an
 > outdated version (from March 2007 or so).  Is it feasible to update
 > this?  If not, I'm highly probably able to install the SVN version.
 > :)
 > 

Unfortunately RefDB has to make do without a Debian maintainer. We
can't update the .debs unless someone "in the know"
volunteers. However, the svn version or the latest prerelease should
work fine. RefDB does not install stuff in weird places, and a "make
uninstall" should remove most traces of it from your system if you
ever want to get rid of it.

regards,
Markus

-- 
Markus Hoenicka
mar...@ca...
(Spam-protected email: replace the quadrupeds with "mhoenicka")
http://www.mhoenicka.de

Re: [Refdb-users] Cannot create database in MySQL

[Tom H. <to...@fu...>]

Here is a list of reserved words in MySQL 5.1

http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

If I understand correctly the goal is a package, deb or RPM that  
works with a dependency of MySQL as a package also and whatever  
version the package requires.

Meaning..

apt-get install refdb..

leads to a MySQL dependency and then the apt-get installs MySQL and  
we are good to go ?

Same goes for RedHat..

Yum install refdb ...
  dependency check and install of MySQL.

This seems reasonable.

Answer the following questions and I can try to help you to make sure  
your DB tables are backwards compatible.

Do you use Stored procedure or user created functions ?
Does your DB script that creates the Refdb Database have any
"create procedure, create function, statements ? "

Are you using foreign keys or transactions ?
If you are are you specifying the table engine = innodb in you create  
table statements.

Are you using timestamp fields. No big deal here, just the defaults  
for that datatype have been switched around a bit, so the definition  
of the column is _not_ backward compatible, but the funtionality  
usually is.

I have not installed refdb in a couple of years, but I have been  
working with MySQL quite a bit, so I can help out with understanding  
MySQL changes over the years.

Best regards,

Tom Hanlon

On 31 Jan 2009, at 11:50, Markus Hoenicka wrote:

> Torsten Bronger writes:
>> Secondly, the name "references" is bad for the database because it
>> seems to be a reserved word or something like that (unfortunately,
>> at the same time it's a good name for a references DB ;-).  With
>> "biblio", it worked.
>
> According to the MySQL manual "references" is a clause in the
> "alter table" syntax, so it is indeed a reserved word. I'll add this
> to the documentation as it is all too easy to get trapped here.
>
>>
>> However, I run into trouble here and there because "deb
>> http://refdb.sourceforge.net/debian/svn testing main" gives me an
>> outdated version (from March 2007 or so).  Is it feasible to update
>> this?  If not, I'm highly probably able to install the SVN version.
>> :)
>>
>
> Unfortunately RefDB has to make do without a Debian maintainer. We
> can't update the .debs unless someone "in the know"
> volunteers. However, the svn version or the latest prerelease should
> work fine. RefDB does not install stuff in weird places, and a "make
> uninstall" should remove most traces of it from your system if you
> ever want to get rid of it.
>
> regards,
> Markus
>
> -- 
> Markus Hoenicka
> mar...@ca...
> (Spam-protected email: replace the quadrupeds with "mhoenicka")
> http://www.mhoenicka.de
>
> ---------------------------------------------------------------------- 
> --------
> This SF.net email is sponsored by:
> SourcForge Community
> SourceForge wants to tell your story.
> http://p.sf.net/sfu/sf-spreadtheword
> _______________________________________________
> Refdb-users mailing list
> Ref...@li...
> https://lists.sourceforge.net/lists/listinfo/refdb-users

Re: [Refdb-users] Cannot create database in MySQL

[Markus H. <mar...@mh...>]

Quoting Tom Hanlon <to...@fu...>:

> If I understand correctly the goal is a package, deb or RPM that works
> with a dependency of MySQL as a package also and whatever version the
> package requires.
>
> Meaning..
>
> apt-get install refdb..
>
> leads to a MySQL dependency and then the apt-get installs MySQL and we
> are good to go ?
>

not quite. RefDB can use a variety of database engines by means of  
libdbi. So installing a refdb (or refdb-server, as Debian tends to  
split client/server programs into its components) package should pull  
in libdbi, which may or may not be available in a reasonable version.

> Do you use Stored procedure or user created functions ?

no

> Does your DB script that creates the Refdb Database have any
> "create procedure, create function, statements ? "

no

> Are you using foreign keys or transactions ?

no and yes. RefDB uses a wrapper function which is a no-op for  
database engines without transaction support. With MySQL, RefDB always  
uses transactions as these seem to be silently ignored if the table  
engine does not support them.

> If you are are you specifying the table engine = innodb in you create
> table statements.
>

not at this time. RefDB uses whichever engine is set as default in  
MySQL and does not rely on any features not supported by the simplest  
one (MyISAM) as of 5.0.

> Are you using timestamp fields. No big deal here, just the defaults for
> that datatype have been switched around a bit, so the definition of the
> column is _not_ backward compatible, but the funtionality usually is.
>

The t_meta table uses a datetime field which is filled with  
UTC_TIMESTAMP at the time of creation and with a strftime() generated  
string whenever the table is updated. I'm not sure whether your  
concerns about timestamp apply here.

> I have not installed refdb in a couple of years, but I have been
> working with MySQL quite a bit, so I can help out with understanding
> MySQL changes over the years.
>

Your help will be much appreciated. As far as I know the changes in  
the 5.x series have been quite substantial, and it is quite hard to  
keep track if you're not working with MySQL directly. Of course, the  
best way to find out is to install and test-drive a recent RefDB  
version...

regards,
Markus

-- 
Markus Hoenicka
mar...@ca...
(Spam-protected email: replace the quadrupeds with "mhoenicka")
http://www.mhoenicka.de