Database Troubles

[Brianelvio]

Database Issues

Hi there!

I've had some issues creating the database earlier on, because I am starting fresh and not from an old database I tried to import the supplied SQL file into the database. This resulted in many troubling errors starting from:

DROP TRIGGER IF EXISTS `penguins_OnInsert`;

CREATE TRIGGER `penguins_OnInsert` BEFORE INSERT ON `penguins` FOR EACH ROW IF NEW.`swid` IS NULL OR NEW.`swid` = '' THEN
	SET NEW.`swid` = CONCAT('{', uuid(), '}');
END IF
;
CREATE TRIGGER `penguins_AfterUpdate` AFTER UPDATE ON `penguins` FOR 

I've fixed this by changing this to the following:

DROP TRIGGER IF EXISTS `penguins_OnInsert`;

DELIMITER $$

CREATE TRIGGER `penguins_OnInsert` BEFORE INSERT ON `penguins`
FOR EACH ROW
BEGIN
    IF NEW.`swid` IS NULL OR NEW.`swid` = '' THEN
        SET NEW.`swid` = CONCAT('{', uuid(), '}');
    END IF;
END$$

This runs just fine, however when the import continues it got stuck on many other places as well (Procedures)

So instead of using the sql file to import I found a copy of the old database in your other branch and worked around it by using the DatabasePort.py and making the script setup the new database. This runs fine un-till it get's to IMPORT_PUFFLE_FROM_CP_STRUCT.

I can see nearly everything is created this time and I'm manually importing the three tables that I miss which are bans, care_items, memberships and penguins.

Can you confirm that ^^^ is how the DB should look?

So far so good.

Now when I start the server it works fine un-till it needs to contact the DB.
It sets up the DB pool correctly and can connect just fine.

But when it starts using functions like e.g. (SetSensei)
It suddenly is incapable of establishing the connection?

I'm using the following:

Windows Server 2012 Standard
Redis for windows
MySQL
Python 2.7
Below a list of modules:

Please let me know if you need any more info.

[valid22]

That was quite detailed, and I appreciate it. You've put a lot of work on that.

The database structure part: You are good to go (you could have just run DatabasePort.py, by giving old-db name as a dummy database :male_shrug:)

Now to the issue, it seems MySQL is unable to find the server on default IP/Port it's supposed to run. Clarify these, make sure your didn't set custom port for MySQL server, if so you might wanna change some codes (and get a coffee). Also that your MySQL database is hosted on your remote environment (localhost)

Next I would suggest you to try these changes, open in a text editor

Timeline/Database/__init__.py, line 38

Try changing that line to

Registry.DBPOOL = ReconnectingMySQLConnectionPool('MySQLdb', user=user, passwd=passd, db=db, cp_reconnect=True, port='3306', host='127.0.0.1')

Sometimes, depending upon the os, MySQL accepts connection only via 127.0.0.1. If above doesn't work, you can try changing host='...' to host='0.0.0.0'.

Else, if you are hosting your database on a different server (outside your vps), change host to that server IP (make sure in this case you allow remote connection)

And (yh while drinking your coffee) if you have set custom port, change the port parameter value too

[Brianelvio]

Haha no worries! I get that it can be hard to reproduce or answer if info is missing or undetailed.

Thanks for the fix! It's now connecting like it should.

Unfortunately getting another error thrown:

Looking into this myself now, but if you might know the fix before I find it I'd appreciate it ;)

P.s. got my coffee =)

[valid22]

Did I forget to mention? Timeline has some self-healing capabilities 😋. Just restart the server and check if that error still persists.

[Brianelvio]

Brilliant my dude!

Thanks for creating this piece of art 🥇

It's been a long time since I've been working in this scene, me and a friend were, back in the day the first operational cpps with a webclient. Back then that was the shizzle, eventually got the game running on SmartFoxServer. Unfortunately most of that data is lost. Talking +/- 10 years ago 😝

Anyways, thanks for the quick replies as well!

[valid22]

Sweet! Have a great (coffee) day!