Open Database Function

[martin]

I have installed a new database called kmymoney using Mysql. There is a
'Grant All' for the user of Kmymoney but there are no tables or data.

Following the user guide in Chapter 22 with QMYSQL3 - Mysql selected
operation of the Generate SQL button then the OK button produces the
following error message.

"Error in function open : opening database
Driver = QMYSQL3, Host = localhost, User = martin, Database = KMyMoney
Driver Error:
Database Error No -1:
Executed:
Query error No -1: "

Bug2?

Using kde4 on another computer the software manager installs QT4
drivers. The 'Open Database' function does not appear to recognise
these drivers so the 'QMYSQL' functionality is absent. If you interfere
with the package manager, in the nicest possible way, and install QT3
drivers the functionality of QMYSQL3 is recognised and it produces the
same error message as above.


martin welsh

[Fernando Vilas]

Post by martin
I have installed a new database called kmymoney using Mysql. There is a
'Grant All' for the user of Kmymoney but there are no tables or data.
Following the user guide in Chapter 22 with QMYSQL3 - Mysql selected
operation of the Generate SQL button then the OK button produces the
following error message.
"Error in function open : opening database
Driver = QMYSQL3, Host = localhost, User = martin, Database = KMyMoney
Query error No -1: "
Bug2?
Using kde4 on another computer the software manager installs QT4
drivers. The 'Open Database' function does not appear to recognise
these drivers so the 'QMYSQL' functionality is absent. If you interfere
with the package manager, in the nicest possible way, and install QT3
drivers the functionality of QMYSQL3 is recognised and it produces the
same error message as above.

Regardless of which Qt version you have, you are trying to login as user
"martin", when you granted privileges to user "Kmymoney". I am not as familiar
with MySQL as I am with Oracle and PostgreSQL, but it seems like that would be
one problem.

Which version of KMyMoney are you running? If you are running the port for KDE
SC 4.x, then it should not even attempt to use the Qt3 drivers.

As for it not finding the correct driver, there could be other factors at play.
What OS / distribution are you running? We may be able to help more with that
information.

[Jack]

Post by martin
I have installed a new database called kmymoney using Mysql. There is
a 'Grant All' for the user of Kmymoney but there are no tables or
data.
Following the user guide in Chapter 22 with QMYSQL3 - Mysql selected
operation of the Generate SQL button then the OK button produces the
following error message.
"Error in function open : opening database
Driver = QMYSQL3, Host = localhost, User = martin, Database = KMyMoney
Query error No -1: "
Bug2?
Using kde4 on another computer the software manager installs QT4
drivers. The 'Open Database' function does not appear to recognise
these drivers so the 'QMYSQL' functionality is absent. If you
interfere with the package manager, in the nicest possible way, and
install QT3 drivers the functionality of QMYSQL3 is recognised and it
produces the same error message as above.

Hello Martin,

I just managed to succeed in creating a mysql database for KMM 1.0.5 on
archlinux, with no error messages, so I know this is possible. It
looks like the manual may not really have enough detail for this, so
I'll list what I did:

0) I am assuming that mysql is installed and the server is running. I
also installed the gui tools, so I could do some of the database admin
interactively, instead of command line.
1) Create the database. Either call it KMyMoney, or remember to change
the name in the KMM dialog.
2) Create the appropriate user in mysql. Again, whatever user name you
create in mysql is the one you have to specify in KMM.
3) Using the gui or mysqladmin, grant that user sufficient privileges
to that database. I just granted all privileges. They are probably
not all necessary, and I will try to add the absolutely required list
to a future version of the manual.
4) In KMM, either click "Create Tables" or "Generate SQL." The first
would be easier, but I had already created the SQL in a file, and
decided to use that. (I
5) As the database user you created in step 2, run the generated sql to
create the necessary tables in the database. (I just used "mysql -u
jack -p password KMyMoney < KMMgeneratedsqlfile.sql")
6) Now, you should be able to click the "OK" button in the KMM save as
database dialog. In my case, it complained about wanting to delete
some data in the database before saving the new data, so I just said OK.

Please let us know if this works for you. If not, please let us know
which steps worked, and where you have trouble, so we can provide more
specific suggestions, and also make the manual more complete.

(Fernando - I started KMM from the console, and once it opens the
database, I get a lot of messages like "request balance for A000148 at
2010-07-18" for many accounts, for yesterday and today. The data looks
otherwise OK. Should I bother to try to reproduce this in the latest
svn version?)

Jack

[Fernando Vilas]

On Sunday, July 18, 2010 12:45:16 Jack wrote:
[snip manual update part]

Post by Jack
(Fernando - I started KMM from the console, and once it opens the
database, I get a lot of messages like "request balance for A000148 at
2010-07-18" for many accounts, for yesterday and today. The data looks
otherwise OK. Should I bother to try to reproduce this in the latest
svn version?)

Jack,

Thanks for working to update the manual on this.

As far as the messages, they are written to the console when a balance
calculation fails, so it falls back to another, slower, method. It is on the
list of things to fix. If we want to make the executable quieter on the
console, we can always comment out that statement.

[Jack]

Post by Fernando Vilas
[snip manual update part]

Post by Jack
(Fernando - I started KMM from the console, and once it opens the
database, I get a lot of messages like "request balance for A000148
at 2010-07-18" for many accounts, for yesterday and today. The data
looks otherwise OK. Should I bother to try to reproduce this in the
latest svn version?)

Jack,
Thanks for working to update the manual on this.
As far as the messages, they are written to the console when a
balance calculation fails, so it falls back to another, slower,
method. It is on the list of things to fix. If we want to make the
executable quieter on the console, we can always comment out that
statement.

I wouldn't necessarily remove it, but I'd add "Warning:" or other
wording so the user doesn't panic and think there is a real problem
with the file. I might also use the real account name, since I don't
know any way to map A000148 to a real name without actually looking
into the file.

Also, FYI, at the moment, I am only planning on working on the version
4 manual, and making minimal, if any, changes to the 1.0 manual.

[tonyb]

Judging from the posts in this thread, I guess maybe the manual needs some
clarification here.

With mysql, all this generating of the sql is unnecessary. Provided you have
the mysql server running, and the correct Qt drivers installed (Qt3 for KMM
version 0.x or 1.x, Qt4 for 3.x or greater), and the userid under which you
run KMM has permissions to create a database, KMM will do all this for you.

Start KMM, if you have an existing conventional file, open it, otherwise use
File/New to create a skeleton file. Then use SaveAs Database; KMM will do it
all for you, and you will be able to use Open Database to retrieve the data in
a later session.

If you don't have permission to create databases, then the administrator will
need to create it for you, but there is no necessity to create all the tables.
The SaveAs function will do thisu. Note the the default name of the database
is KMyMoney, and case is significant. You may change it of course in the
SaveAs and Open dialogs.

As for the Generate Sql function, this is primarily there for use with
database systems where automatic creation of the tables is not yet
implemented, or may not work correctly, within KMM.

[martin]

I have tried again following your instructions and attach details of the
results.
Unless stated otherwise both machines had a mysql database created
called kmymoney with 'grant all' to the username of the computer and
kmymoney.

The first operating system is

Pardus 2009-2 KDE 4.4.4
Kmymoney 3.98.0
qt-sql-mysql Qt toolkit version 4
mysql-server 5.1.47
mysql-client 5.1.47
File > Open Database + select Mysql + Password + OK
Error Message
"Error in function int MyMoneyStorageSql::open(const KUrl&, int, bool)
: opening database
Driver = QMYSQL, Host = localhost, User = martinHwelsh, Database =
KMyMoney
Driver Error: QMYSQL: Unable to connect
Database Error No 2002: Can't connect to local MySQL server through
socket '/var/run/mysqld/mysqld.sock' (2)
Text: Can't connect to local MySQL server through socket
'/var/run/mysqld/mysqld.sock' (2) QMYSQL: Unable to connect
Error type 1
Error type 0 "
Kmymoney exits the data file selected.
It was this operating system that initially showed only Sqlite
database until I loaded the Qt driver. Ithought that I had loaded a
Qt3 driver but it now seems to have a Qt4 driver. The package manager
is called Pisi and I have yet to get familiar with its entrails and
cannot be clearer at the moment.
This is the operating system that is used mainly it is Gnome based
which allows the use of KDE 3 for a Knoda/Mysql which is KDE3 only.
LinuxMint
KDE 3.5.10
Kmymoney 1.0.4
mysql-server 5.1.41
mysql-client 5.1.41
libqt3-mt-mysql
Database kmymoney created. Grant all to kmymoney user.
File > Open Database > Select Mysql + Password + OK
Error message;
"Error in function open : opening database
Driver = QMYSQL3, Host = localhost, User = martin, Database = KMyMoney
Query error No -1: "
Kmymoney exits the datafile.
File > Save As Database > Select Mysql + Password + OK
Error message;
"Error in function createDatabase : Error in create database KMyMoney;
do you have create permissions?
Driver = QMYSQL3, Host = localhost, User = martin, Database = KMyMoney
Executed: CREATE DATABASE KMyMoney;
Query error No -1: "
Kmymoney does not exit file.
Going back to the suggestions in the mail;
Using the same notation.
0). Mysql Server version: 5.1.41-3ubuntu12.3 (Ubuntu) is installed and
running.
1). Database kmymoney has been created. (No tables)
2). The user name for mysql is the same as the username in the Open
Database and Save as Database boxes in the control panels revealed
when you select the functions.
3). All privileges have been granted to the user/username.
4). Initially I pressed the OK buttons only and no tables were
created. The two error messages shown above were created.
5). Selecting Generate SQL produces a SQL file and I loaded this
into the kmymoney database producing 15 tables.
6). Selecting save as database produced the following error message;
"Error in function createDatabase : Error in create database KMyMoney;
do you have create permissions?
Driver = QMYSQL3, Host = localhost, User = martin, Database = KMyMoney
Executed: CREATE DATABASE KMyMoney;
Query error No -1: " - again!
I am not a Linux expert indeed in this instance I will be flattered if
you describe me as a Linux user so it is probable that is my fingers
that are the problem. Please reply in the proposed handbook revision
language.
I will be happy to have a go with Sqlite and Postgresql but I have not
used Postgresql before.
Regards
martin welsh

[Fernando Vilas]

On Tuesday, July 20, 2010 10:41:35 martin wrote:
[...]

Driver = QMYSQL, Host = localhost, User = martinHwelsh, Database =
KMyMoney
Driver Error: QMYSQL: Unable to connect
Database Error No 2002: Can't connect to local MySQL server through
socket '/var/run/mysqld/mysqld.sock' (2)
Text: Can't connect to local MySQL server through socket
'/var/run/mysqld/mysqld.sock' (2) QMYSQL: Unable to connect
Error type 1
Error type 0 "

Three things here:

1) It looks like the MySQL daemon is not running, or there is a permissions
problem on the socket. The first is more likely than the latter.

2) The user "kmymoney" has all the privileges, but the user is "martinHwelsh".
Is there such a user in MySQL? If you change that to "kmymoney" what happens?

3) As Tony mentioned before, the usernames and database names are case
sensitive. The database name is "KMyMoney", so the username should probably be
the same.

[...]

This is the operating system that is used mainly it is Gnome based
which allows the use of KDE 3 for a Knoda/Mysql which is KDE3 only.
LinuxMint

Same points as above for this computer.

[...]

Going back to the suggestions in the mail;
Using the same notation.
0). Mysql Server version: 5.1.41-3ubuntu12.3 (Ubuntu) is installed and
running.
1). Database kmymoney has been created. (No tables)
2). The user name for mysql is the same as the username in the Open
Database and Save as Database boxes in the control panels revealed
when you select the functions.
3). All privileges have been granted to the user/username.

Which user is meant here, "martin" or "KMyMoney"?

4). Initially I pressed the OK buttons only and no tables were
created. The two error messages shown above were created.

I still think this is related to a username issue.

5). Selecting Generate SQL produces a SQL file and I loaded this
into the kmymoney database producing 15 tables.

How did you load it into the database? It may be a good way to track down what
is wrong. Also, as Tony mentioned, this should be automatic for MySQL. If step
4 had succeeded, this step should never have been reached.

6). Selecting save as database produced the following error message;
"Error in function createDatabase : Error in create database KMyMoney;
do you have create permissions?
Driver = QMYSQL3, Host = localhost, User = martin, Database = KMyMoney
Executed: CREATE DATABASE KMyMoney;
Query error No -1: " - again!

KMM did not detect that the database exists, so it tried to create it. It
tried to do that by logging in as "martin", then issuing the command above. It
failed because either the database already exists (as you said), or the user
"martin" does not have privileges to create a database.

I am not a Linux expert indeed in this instance I will be flattered if
you describe me as a Linux user so it is probable that is my fingers
that are the problem. Please reply in the proposed handbook revision
language.
I will be happy to have a go with Sqlite and Postgresql but I have not
used Postgresql before.

It is meant to run on MySQL, as well. We have some other users doing just
that, including Tony, the other database developer on the project.

Sorry this is not in the new handbook language. We need to update the section
on "User name and password".

What is currently there is correct, but potentially misleading to a new user.
It says to contact your DBA to get the login info, and the sample image lists
the username of the logged in user. Since you are the DBA on your own machine
(whether you want to be or not), you have to make sure you have the username
correct. In this case, it is the db user that you granted all privileges to:
"KMyMoney".

We should probably expand the section to include reasonable defaults for a new
user, while still keeping the data relevant for a more advanced user. For
instance, my database is on a desktop computer that acts as a
print/email/file/media server for my home network. I normally access it from a
laptop in another room (with a VPN over wifi).

[martin]

Dealing with LinuxMint Operating Systems.

The difficulties look as though they stem from permissions.
I tried 'Saving as a Database' using Mysql root with the mysql root
password and a database was saved.
I was unable to recall it into the system but first I will try to
resolve what exactly is necessary to load it.

The handbook suggests that you prepare an empty database.
(Administration - Creating the Database.)
I will prepare 'kmymoney' note no capitals. I will grant all access to
the user of Kmymoney.

Using KmyMoney 1.0.4 you can now generate a set of empty tables using
the SQL button - this facility is absent in the KDE4 version.

Going to KmyMoney 1.0.4 Opening Database;
Database name changed to 'kmymoney' password entered OK button pressed -
error message as before produced, KmyMoney crashes but it has produced a
set of tables.
Going to KmyMoney 1.0.4 Saving as Database:
Database name changed to kmymoney password entered OK button pressed -
Data loaded.

Stop and restart KmyMoney and after a long time it is ready to go.
Stop and restart again it starts a little quicker.

So it was finger trouble! My apologies for causing alarm and
despondency. I don't think that it is supposed to crash after trying to
Open a Database and I still do not know why the error message was generated.

I will go away now to try and get the Pardus KDE4 system to work.

martin welsh

[tonyb]

Post by martin
Using KmyMoney 1.0.4 you can now generate a set of empty tables using
the SQL button - this facility is absent in the KDE4 version.

AFAIR, it's been separated from the main Open/Save dialog in KDE4. It should
be under the Tools menu - Generate SQL.

[Thomas Baumgart]

Hi all,

Post by tonyb

Post by martin
Using KmyMoney 1.0.4 you can now generate a set of empty tables using
the SQL button - this facility is absent in the KDE4 version.

AFAIR, it's been separated from the main Open/Save dialog in KDE4. It
should be under the Tools menu - Generate SQL.

Confirmed: that's exactly where it has been moved to.
--
Regards

Thomas Baumgart

GPG-FP: E55E D592 F45F 116B 8429 4F99 9C59 DB40 B75D D3BA
-------------------------------------------------------------
"I'd like to buy Windows." "Are you crazy?" "Is this part of the licence
agreement?" "Not really, but it might ease the usage ..."
-------------------------------------------------------------