Connection to Mysql

Post your Server Bug Report
leonhard2k
Posts: 2
Joined: 16 Jul 2020, 12:18
Has thanked: 0
Been thanked: 1 time

Connection to Mysql

Unread post by leonhard2k »

Hi!
I am trying to connect madsonic on windows to my sql Database.
Tried with mysql 5 and 8.
Connection works but the schema and tables are not created correctly.
Indexes are missing, tables are missing and so on, its only creating a part of the needed dataset it seems.
Is there a sql script to create the needed tables in mysql ?
It seems madsonic tries to create the tables at startup, but why isnt it creating everything then ?

Thx in advance,
Attachments
madsonic-service.log
(47.56 KiB) Downloaded 131 times
These users thanked the author leonhard2k for the post:
Madsonic
Rating: 7.69%
leonhard2k
Posts: 2
Joined: 16 Jul 2020, 12:18
Has thanked: 0
Been thanked: 1 time

Re: Connection to Mysql

Unread post by leonhard2k »

I was able to connect now to Mysql now:
Here are some hints if anyone else has also issues:

I had to remove the root password, this should only be done when you set that only local connections are allowed.
update user set password=PASSWORD("") where User='root';

My Connections String in config file looks like this:
-Dmadsonic.db=jdbc:mysql://localhost:3306/madsonic?user=root&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC


I also had to change the timezone in my sql DB and add a colum to user_settings table
SET GLOBAL time_zone = "+2:00" 0,000 sec
ALTER TABLE `madsonic`.`user_settings` ADD COLUMN show_index tinyint

For the login into webfrontend i had to choose remember me checkbox, because else i could not log in (screen stayed at login page)

I also tried to use plsql in between, but there was an error in creation script. Creation user table was not sucessfull since the term user is a sql command an therefore it would need to be changed to "user" with the commas in creation step:

CREATE TABLE ""USER"" ("USER"NAME VARCHAR(256) NOT NULL PRIMARY KEY, PASSWORD VARCHAR(256) NOT NULL, BYTES_STREAMED BIGINT DEFAULT 0 NOT NULL, BYTES_DOWNLOADED BIGINT DEFAULT 0 NOT NULL, BYTES_UPLOADED BIGINT DEFAULT 0 NOT NULL, ldap_authenticated BOOLEAN DEFAULT FALSE NOT NULL, EMAIL VARCHAR(256), GROUP_ID INT NOT NULL, LOCKED VARCHAR(4) NOT NULL, COMMENT VARCHAR(256))
Ksmoove
Posts: 8
Joined: 23 Nov 2013, 00:05
Has thanked: 1 time
Been thanked: 2 times

Re: Connection to Mysql

Unread post by Ksmoove »

following.

I've been trying to do this as well
User avatar
Madsonic
Administrator
Administrator
Posts: 984
Joined: 07 Dec 2012, 03:58
Answers: 7
Has thanked: 1201 times
Been thanked: 470 times

Re: Connection to Mysql

Unread post by Madsonic »

Hi there,

We finish the external database support in one of the next versions of Madsonic 7.
At the moment there are some troubles with the SQL schemas.

stay tuned ;)

Best regards,
The Madsonic Team
Post Reply