Running a Community Server : error in database

OneSwarm Forum » General

Running a Community Server : error in database

(14 posts) (4 voices)

Started 2 years ago by jolavillette
Latest reply from Ghostlord

Tags:

No tags yet.

jolavillette
Member

Hi all

I am trying to set up a Community Server :)

Linux ubuntu 2.6.35-22-generic #34-Ubuntu SMP Sun Oct 10 09:26:05 UTC 2010 x86_64 GNU/Linux

java version "1.6.0_20"
OpenJDK Runtime Environment (IcedTea6 1.9) (6b20-1.9-0ubuntu1)
OpenJDK 64-Bit Server VM (build 17.0-b16, mixed mode)

mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (x86_64) using readline 6.1

I am using the sample-community.conf configuration file, almost unmodified : I only changed the DB username and password, and the server IP address. All the rest is unchanged.

When starting, the CS says :

--------------------------
read log configuration
log4j:WARN No appenders could be found for logger (com.jolbox.bonecp.BoneCPConfig).
log4j:WARN Please initialize the log4j system properly.
[INFO] 16/10/10 14:47 CommunityDAO: Creating DB schema...
[WARNING] 16/10/10 14:47 CommunityDAO: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'CHARSET utf8, removed BOOLEAN DEFAULT FALSE, FOREIGN KEY(accountname) REFERENC' at line 1
[WARNING] 16/10/10 14:47 CommunityDAO: CREATE TABLE comments ( swarmid BIGINT UNSIGNED, commentid SERIAL PRIMARY KEY, accountname VARCHAR(128) CHARSET utf8 NOT NULL, time TIMESTAMP DEFAULT NOW(), reply_to BIGINT UNSIGNED DEFAULT NULL, upvote INTEGER DEFAULT 0, downvote INTEGER DEFAULT 0, ip VARCHAR(16) NOT NULL, body TEXT NOT NULL CHARSET utf8, removed BOOLEAN DEFAULT FALSE, FOREIGN KEY(accountname) REFERENCES valid_accounts(username) ON DELETE CASCADE, FOREIGN KEY(swarmid) REFERENCES published_swarms(swarmid) ON DELETE CASCADE ) TYPE=INNODB
[WARNING] 16/10/10 14:47 CommunityDAO: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'community_db.comments' doesn't exist
[WARNING] 16/10/10 14:47 CommunityDAO: CREATE INDEX comment_dates ON comments(time)
[INFO] 16/10/10 14:47 CommunityDAO: Created admin account with blank password -- change the password!
[INFO] 16/10/10 14:47 CommunityDAO: Starting reload of soft state...
[INFO] 16/10/10 14:47 CommunityDAO: db sync took: 7 for 0
[INFO] 16/10/10 14:47 CommunityDAO: Registered: 0 infrastructure keys
[INFO] 16/10/10 14:47 EmbeddedServer: max_threads: 30
2010-10-16 14:47:30.252::INFO: Logging to STDERR via org.mortbay.log.StdErrLog
[INFO] 16/10/10 14:47 KeyRegistrationServlet: Key registration servlet created.
[INFO] 16/10/10 14:47 SwarmPublishServlet: Swarm publishing servlet created.
[INFO] 16/10/10 14:47 EmbeddedServer: Authentication required for swarm publishing
[INFO] 16/10/10 14:47 EmbeddedServer: host: 127.0.0.1
[INFO] 16/10/10 14:47 EmbeddedServer: port: 8081
2010-10-16 14:47:30.336::INFO: jetty-6.1.x
2010-10-16 14:47:30.606::INFO: Started SelectChannelConnector@127.0.0.1:8081
[INFO] 16/10/10 14:47 EmbeddedServer: started embedded server
[INFO] 16/10/10 14:47 CommunityDAO: Using user timeout: 86400000 seconds
[INFO] 16/10/10 14:47 CommunityDAO: Pruned 0 old peers
[INFO] 16/10/10 14:47 CommunityDAO: Starting reload of soft state...
[INFO] 16/10/10 14:47 CommunityDAO: db sync took: 4 for 0
--------------------------------

Jo

Posted 2 years ago #
piatek
Administrator

I believe someone else has had this problem, and that it has to do with the version of MySQL you're using. Can you try making this change to the code and rebuilding?

http://github.com/pfleidi/OneSwarm-Community-Server/commit/fd1edbd349a9c3de72c0abd9263cebecc1f62837

(1 line change to the DB schema)

Do let me know if this works and I'll change the reference implementation. (I've been meaning to do this since the summer, but this got lost in the flurry of things we're working on now.)

Posted 2 years ago #
jolavillette
Member

Piatek, this is far beyond my skills :)
Anybody here willing to help ?

Posted 2 years ago #
piatek
Administrator

I'll try to get a permanent fix into a new release on Monday

Posted 2 years ago #
Electronic
Member

Over five years since i worked with MySQL but it looks like youre missing a sign on the first line, SQL is very kinky about the proper order of signs, mostly a space too much or ; missing. (this is just the first warning)

Posted 2 years ago #
piatek
Administrator

Actually I ended up not being able to get to this today -- will find some time this week to patch and test, tho -- keep following this thread for updates.

Posted 2 years ago #
jolavillette
Member

Thank Piatek
Meanwhile I put my CS on test at http://lavillette.dyndns.org:8081

Posted 2 years ago #
Ghostlord
Member

Hi. I found a solution to the comments table problem when I set up my server. The community server is sending the wrong syntax.
Right command is:
CREATE TABLE comments ( swarmid BIGINT UNSIGNED, commentid SERIAL PRIMARY KEY, accountname VARCHAR(128) CHARSET utf8 NOT NULL, time TIMESTAMP DEFAULT NOW(), reply_to BIGINT UNSIGNED DEFAULT NULL, upvote INTEGER DEFAULT 0, downvote INTEGER DEFAULT 0, ip VARCHAR(16) NOT NULL, body TEXT CHARSET utf8 NOT NULL, removed BOOLEAN DEFAULT FALSE, FOREIGN KEY(accountname) REFERENCES valid_accounts(username) ON DELETE CASCADE, FOREIGN KEY(swarmid) REFERENCES published_swarms(swarmid) ON DELETE CASCADE ) TYPE=INNODB;

You can send this command to the database by using the MySQL command Line Client. I don’t remember the commands exactly to open the database. But you can google on MySQL for support on that.

Posted 2 years ago #
jolavillette
Member

It worked...

I typed the following commands in the mysql command line :

USE community_db;
CREATE TABLE comments....... (I just pasted your command)
exit;

Now everything seems to be running OK : almost no warning on startup (only something about log4j), clicking on a file does actually work, etc...

Ghostlord, when me meet, remind me to buy you a drink or two :)

Posted 2 years ago #
piatek
Administrator

I've posted a new version of the community server that includes this (and some other) fixes. Thanks!

Posted 2 years ago #
Ghostlord
Member

A new version! :-)
Im looking forward to se if the http error 403 is fixed?
HTTP ERROR: 403
User not in required role
RequestURI=/files.jsp
Powered by Jetty://

Looks like the changelog file isn’t updated. What’s the news?
Great work on oneswarm btw

Posted 2 years ago #
Ghostlord
Member

No just tested it. I still get the error. Im running a private SSL server.
It occurs when one user is logged in to the web UI and when another user is logging in. Then the first user gets the error 403 forcing him to restart browser and log in again. This effectively makes the web ui only able to serve one user at a time. How can I fix this?

Posted 2 years ago #
piatek
Administrator

Will try to reproduce this -- if it happens every time, should be easy to fix. thanks!

Posted 2 years ago #
Ghostlord
Member

(If its not obvious) Forgot to mention that both users have to use separate accounts for the error to occur. If they use the same account web UI works for both.

Posted 2 years ago #

RSS feed for this topic

Reply

You must log in to post.