Bug: sql import creatures_objects unknown column 'modelid1'

[darkmushroom]

Current Behaviour

Importing 2024_04_07_00_creatures_objects.sql into azerothcore-wotlk
https://github.com/azerothcore/azerothcore-wotlk/tree/89b08dd5dc112be3defbfebd943369dd8ffb60b7

Results in the following error:
ERROR 1054 (42S22) at line 10: Unknown column 'modelid1' in 'field list'

Manually verifying the creature_template table confirms that this column does not currently exist.

MariaDB [acore_world]> SHOW COLUMNS FROM creature_template;
+--------------------------+----------------------+------+-----+---------+-------+
| Field                    | Type                 | Null | Key | Default | Extra |
+--------------------------+----------------------+------+-----+---------+-------+
| entry                    | int(10) unsigned     | NO   | PRI | 0       |       |
| difficulty_entry_1       | int(10) unsigned     | NO   |     | 0       |       |
| difficulty_entry_2       | int(10) unsigned     | NO   |     | 0       |       |
| difficulty_entry_3       | int(10) unsigned     | NO   |     | 0       |       |
| KillCredit1              | int(10) unsigned     | NO   |     | 0       |       |
| KillCredit2              | int(10) unsigned     | NO   |     | 0       |       |
| name                     | char(100)            | NO   | MUL | 0       |       |
| subname                  | char(100)            | YES  |     | NULL    |       |
| IconName                 | char(100)            | YES  |     | NULL    |       |
| gossip_menu_id           | int(10) unsigned     | NO   |     | 0       |       |
| minlevel                 | tinyint(3) unsigned  | NO   |     | 1       |       |
| maxlevel                 | tinyint(3) unsigned  | NO   |     | 1       |       |
| exp                      | smallint(6)          | NO   |     | 0       |       |
| faction                  | smallint(5) unsigned | NO   |     | 0       |       |
| npcflag                  | int(10) unsigned     | NO   |     | 0       |       |
| speed_walk               | float                | NO   |     | 1       |       |
| speed_run                | float                | NO   |     | 1.14286 |       |
| speed_swim               | float                | NO   |     | 1       |       |
| speed_flight             | float                | NO   |     | 1       |       |
| detection_range          | float                | NO   |     | 20      |       |
| scale                    | float                | NO   |     | 1       |       |
| rank                     | tinyint(3) unsigned  | NO   |     | 0       |       |
| dmgschool                | tinyint(4)           | NO   |     | 0       |       |
| DamageModifier           | float                | NO   |     | 1       |       |
| BaseAttackTime           | int(10) unsigned     | NO   |     | 0       |       |
| RangeAttackTime          | int(10) unsigned     | NO   |     | 0       |       |
| BaseVariance             | float                | NO   |     | 1       |       |
| RangeVariance            | float                | NO   |     | 1       |       |
| unit_class               | tinyint(3) unsigned  | NO   |     | 0       |       |
| unit_flags               | int(10) unsigned     | NO   |     | 0       |       |
| unit_flags2              | int(10) unsigned     | NO   |     | 0       |       |
| dynamicflags             | int(10) unsigned     | NO   |     | 0       |       |
| family                   | tinyint(4)           | NO   |     | 0       |       |
| trainer_type             | tinyint(4)           | NO   |     | 0       |       |
| trainer_spell            | int(10) unsigned     | NO   |     | 0       |       |
| trainer_class            | tinyint(3) unsigned  | NO   |     | 0       |       |
| trainer_race             | tinyint(3) unsigned  | NO   |     | 0       |       |
| type                     | tinyint(3) unsigned  | NO   |     | 0       |       |
| type_flags               | int(10) unsigned     | NO   |     | 0       |       |
| lootid                   | int(10) unsigned     | NO   |     | 0       |       |
| pickpocketloot           | int(10) unsigned     | NO   |     | 0       |       |
| skinloot                 | int(10) unsigned     | NO   |     | 0       |       |
| PetSpellDataId           | int(10) unsigned     | NO   |     | 0       |       |
| VehicleId                | int(10) unsigned     | NO   |     | 0       |       |
| mingold                  | int(10) unsigned     | NO   |     | 0       |       |
| maxgold                  | int(10) unsigned     | NO   |     | 0       |       |
| AIName                   | char(64)             | NO   |     |         |       |
| MovementType             | tinyint(3) unsigned  | NO   |     | 0       |       |
| HoverHeight              | float                | NO   |     | 1       |       |
| HealthModifier           | float                | NO   |     | 1       |       |
| ManaModifier             | float                | NO   |     | 1       |       |
| ArmorModifier            | float                | NO   |     | 1       |       |
| ExperienceModifier       | float                | NO   |     | 1       |       |
| RacialLeader             | tinyint(3) unsigned  | NO   |     | 0       |       |
| movementId               | int(10) unsigned     | NO   |     | 0       |       |
| RegenHealth              | tinyint(3) unsigned  | NO   |     | 1       |       |
| mechanic_immune_mask     | int(10) unsigned     | NO   |     | 0       |       |
| spell_school_immune_mask | int(10) unsigned     | NO   |     | 0       |       |
| flags_extra              | int(10) unsigned     | NO   |     | 0       |       |
| ScriptName               | char(64)             | NO   |     |         |       |
| VerifiedBuild            | int(11)              | YES  |     | NULL    |       |
+--------------------------+----------------------+------+-----+---------+-------+

Expected Behaviour

I'm no expert. I'm assuming after the recent azerothcore refactor they probably moved these modelids to a different table. They seem important, they're just not in this table anymore.

Steps to reproduce the problem

1. Spin up a new azerothcore instance
   (This commit at time of testing)
2. install this mod, cmake, install
3. I think the sql changes are supposed to automatically import but they don't (or I did something wrong). Manually import the sql files
4. see error

Extra Notes

The only other mod installed is mod-ahbot.

AC rev. hash/commit

AC> AzerothCore rev. 89b08dd5dc11 2024-09-06 19:43:12 -0300 (master branch) (Unix, RelWithDebInfo, Static)
Connected players: 5. Characters in world: 5.
Connection peak: 6.
Server uptime: 2 hour(s) 1 minute(s) 46 second(s)
Using SSL version: OpenSSL 3.3.1 4 Jun 2024 (library: OpenSSL 3.3.1 4 Jun 2024)
Using Boost version: 1.83.0
Using CMake version: 3.30.2
Using MySQL version: 110402
Found MySQL Executable: /usr/bin/mysql
Compiled on: Linux 6.10.3-arch1-2
Worldserver listening connections on port %u
Realmlist (Realm Id: 1) configured in port 8085
VMAPs status: Enabled. LineOfSight: true, getHeight: true, indoorCheck: true
MMAPs status: Enabled
maps directory located in /home/alex/azeroth-server/data/maps. Total size: 291014951 bytes
vmaps directory located in /home/alex/azeroth-server/data/vmaps. Total size: 658130721 bytes
mmaps directory located in /home/alex/azeroth-server/data/mmaps. Total size: 2192910844 bytes
Default DBC locale: enUS.
All available DBC locales: enUS
Using World DB: ACDB 335.12-dev
Latest LoginDatabase update: 2024_01_20_00.sql
Latest CharacterDatabase update: 2024_09_03_00.sql
Latest WorldDatabase update: z_filter_disabled_and_trash.sql
LoginDatabase queue size: 0
CharacterDatabase queue size: 0
WorldDatabase queue size: 0
List of enabled modules:
|- mod-ah-bot
|- mod-guildhouse

Operating system

Arch Linux (x86_64 Linux 6.10.3-arch1-2)

Custom changes or Modules

doubled the prices for guild housing, but that's about it.

[coler-j]

This seems like it would be related to https://discord.com/channels/217589275766685707/316952484566532107/1251942043425574993 announcment:

Hello Community!

As of this commit the table structure for creature_template has been updated and all modelid fields have been removed and a new table has been introduced, creature_template_model.

As we cannot update all modules alone we need your help! If you use a module that queries the creature_template we ask that you take a few minutes to make a PR to update the query to match it to the latest table structure.

This is also a reminder that your custom queries also need to be updated to work with the latest table structure.

Probably not possible to use this module until updated, few attempts that have been closed:

• Fix sql (creature template updates) #51
• Fix sql (creature template updates) #50
• Fix sql (creature template updates) #49

[GitCrazy-wc]

Here's the SQL code that adds the models (and does cleanup if they exist).

SET @C_TEMPLATE = 500030;

DELETE FROM `creature_template_model` WHERE `CreatureID` IN (
	@C_TEMPLATE + 0,
	@C_TEMPLATE + 1,
	@C_TEMPLATE + 2
);

INSERT INTO `creature_template_model` (`CreatureID`, `Idx`, `CreatureDisplayID`, `DisplayScale`, `Probability`, `VerifiedBuild`) VALUES
	(@C_TEMPLATE + 0, 0, 25901, 1, 1, 12340),
	(@C_TEMPLATE + 1, 0, 25901, 1, 1, 12340),
	(@C_TEMPLATE + 2, 0, 18234, 1, 1, 12340);

[darkmushroom]

You're a real one @GitCrazy-wc, thank you!
I'll back up our DB and give this a shot this weekend.

[xengine-qyt]

Why not merge the PR that solves the problem

[darkmushroom]

I actually think this pull might have solved it:
#55

I haven't gotten around to testing it because I'm a lazy bastard I wanted to let my players kind of chill after the tense migration from mariadb to mysql.

[CoffeeAddict483]

Was anyone able to fix this the commit #55 wasnt able to fix it for me