MySQL Error!

[3ssej]

I'm not exactly sure if this is still supported, but I do have an issue. I know absolutely nothing about MySQL databases, but I believe I have set everything up correctly. Anyway, I have an error that restarts my server every time I try to start it up. Error: https://hastebin.com/eyupaminih.sql Config: https://hastebin.com/ezigepofay.bash Any help is appreciated! Thanks, Jesse

[ProgrammerDan]

So, this is due to mysql vs. mariadb and version issues. Make sure you are using Mariadb 10.1 or newer.

[3ssej]

I'm using shared hosting right now, is that something that my host would take care of? Again, very new to mysql.

[ProgrammerDan]

Hmm, yes, you'd need to contact your shared host and ask them what options you have in terms of MySQL / MariaDB version.

Let me know what they tell you, we'll proceed from there.

[3ssej]

Alright, they finally replied with this: "We currently only run MySQL for our shared hosting option." And according to the phpMyAdmin page, they're running version 5.1.73 of MySQL.

[ProgrammerDan]

I'll look and see if we can mitigate. I might be able to construct a compatible set of queries for you to manually run that will provision the database correctly. Ping me if you don't hear back in a day or two.

[3ssej]

Alright, thanks!

[3ssej]

Ping me if you don't hear back in a day or two.

Just messaging you.

[ProgrammerDan]

Thanks, I'll see if I can look into this tonight. Schedule tonight is kind of full, so no promises. Keep pinging me until you get something :D

[ProgrammerDan]

I think the only way I can achieve comparable functionality for you is via a trigger associated with insertion for that table. I'll look into it some more, but it appears that the MySQL version you are required to use has a rather limited featureset when it comes to default dates.

[3ssej]

Alright, thank you again for all of this work on this.

[ProgrammerDan]

Just a note I haven't forgotten about you. Started looking at constructing triggers. I don't have a great way to test this so you'll be my guinea pig once I have them coded.

[ProgrammerDan]

Alright, here's my best effort to get you over the hump. First, if you've cleared the NameLayer database, run it until the failure-state (like your first pastebin). That way you'll be "through" the first half dozen migrations.

After that, run these commands on your database:

create table if not exists group_invitation(
 uuid varchar(36) NOT NULL, 
 groupName varchar(255) NOT NULL,
 role varchar(10) NOT NULL default 'MEMBERS',
 date datetime NOT NULL DEFAULT 0,
 constraint UQ_uuid_groupName unique(uuid, groupName)) charset=latin1;
DELIMITER //
CREATE TRIGGER group_invitation_time_insert_catch BEFORE INSERT ON group_invitation
  FOR EACH ROW
    BEGIN
      IF NEW.`date` = 0 OR NEW.`date` IS NULL THEN
        SET NEW.`date` = CURRENT_TIMESTAMP;
      END IF;
    END;//
DELIMITER ;

alter table faction add last_timestamp datetime NOT NULL default 0;
DELIMITER //
CREATE TRIGGER faction_insert_catch BEFORE INSERT ON faction
  FOR EACH ROW
    BEGIN
      IF NEW.`last_timestamp` = 0 OR NEW.`last_timestamp` IS NULL THEN
        SET NEW.`last_timestamp` = CURRENT_TIMESTAMP;
      END IF;
    END;//
DELIMITER ;

UPDATE managed_plugin_data WHERE plugin_name = 'NameLayer' SET current_migration_number = 8, last_migration = CURRENT_TIMESTAMP;

Then, restart your server so that migration will attempt to continue.

This should get you working; disclaimer: I've tested the individual theoretic components but not the composition. Let me know if it fails anywhere.

[3ssej]

I tried running the command, but it didn't work. Server console on start: http://pastebin.com/iN4PnTji MySQL error: http://pastebin.com/eHdwQtWR

[ProgrammerDan]

oh my bad

Run this:

UPDATE managed_plugin_data SET current_migration_number = 8, last_migration = CURRENT_TIMESTAMP WHERE plugin_name = 'NameLayer';

Just doublecheck that the alters and triggers are in place, then run the managed update; then run the server.

[3ssej]

I got further in the migration, (#17), but it still crashes after it can't do the last one. Console error: http://pastebin.com/P0TLUzSn Thank you so much for helping me.

[ProgrammerDan]

This is weird; before I debug too much more, can you do the following:

Drop all Namelayer related tables. Make sure the Namelayer "migration" entry is gone from the management table.

Then, start over. Run the server so that you get to the first failure. Run the custom scripts I gave you (including the corrected migration update line) then once that's done, restart the server and let it run through. If you get the same error, let me know.

[3ssej]

I still get the same error after doing as you said, but just to be sure, you want me to run this command, right? (This is the original command but with the old migration update line replaced by the new correct migration update line)

create table if not exists group_invitation(
 uuid varchar(36) NOT NULL, 
 groupName varchar(255) NOT NULL,
 role varchar(10) NOT NULL default 'MEMBERS',
 date datetime NOT NULL DEFAULT 0,
 constraint UQ_uuid_groupName unique(uuid, groupName)) charset=latin1;
DELIMITER //
CREATE TRIGGER group_invitation_time_insert_catch BEFORE INSERT ON group_invitation
  FOR EACH ROW
    BEGIN
      IF NEW.`date` = 0 OR NEW.`date` IS NULL THEN
        SET NEW.`date` = CURRENT_TIMESTAMP;
      END IF;
    END;//
DELIMITER ;

alter table faction add last_timestamp datetime NOT NULL default 0;
DELIMITER //
CREATE TRIGGER faction_insert_catch BEFORE INSERT ON faction
  FOR EACH ROW
    BEGIN
      IF NEW.`last_timestamp` = 0 OR NEW.`last_timestamp` IS NULL THEN
        SET NEW.`last_timestamp` = CURRENT_TIMESTAMP;
      END IF;
    END;//
DELIMITER ;

UPDATE managed_plugin_data SET current_migration_number = 8, last_migration = CURRENT_TIMESTAMP WHERE plugin_name = 'NameLayer';

[ProgrammerDan]

What version of NameLayer are you using? There is no migration 17. Current latest has up to migration 13; no further.

Be sure to get your build from here: https://build.devotedmc.com -- and it looks like you are using 1.11 so be sure to grab the latest NameLayer and CivModCore builds.

[3ssej]

Alright, now it works. Thanks so much! (I was getting my builds from another place.)

[ProgrammerDan]

Just curious, where in the world did you get a build with migration 17? I'd love to see what changes they've made.

[3ssej]

I got it here: https://build.civcraft.co/

[ProgrammerDan]

Weird, musta been using the experimental Pull branches.

Glad you're in business now.