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,
Connection to Mysql
-
- Posts: 2
- Joined: 16 Jul 2020, 12:18
- Has thanked: 0
- Been thanked: 1 time
Connection to Mysql
- Attachments
-
- madsonic-service.log
- (47.56 KiB) Downloaded 195 times
- These users thanked the author leonhard2k for the post:
- Madsonic
- Rating: 7.69%
-
- Posts: 2
- Joined: 16 Jul 2020, 12:18
- Has thanked: 0
- Been thanked: 1 time
Re: Connection to Mysql
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))
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))
- Madsonic
- Administrator
- Posts: 986
- Joined: 07 Dec 2012, 03:58
- Has thanked: 1204 times
- Been thanked: 470 times
Re: Connection to Mysql
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
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