search

Sarjuuk / aowow

Database viewer for TrinityCore based on aowow by @LordJZ, based on the JS-Engine of Wowhead
204 stars 217 forks source link

Importing db_structure.sql failed. #414

Closed luyingwei80 closed 5 months ago

luyingwei80 commented 5 months ago

Report error when importing db_structure.sql:

[SQL] Query db_structure start [ERR] 6125 - Failed to add the foreign key constraint. Missing unique key for constraint 'FK_acc_weightscales' in the referenced table 'aowow_account_weightscales' [ERR] -- MySQL dump 10.16 Distrib 10.2.10-MariaDB, for debian-linux-gnu (x86_64)

-- Host: localhost Database: sarjuuk_aowow

-- Server version 10.2.10-MariaDB-10.2.10+maria~xenial-log

/!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /; /!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /; /!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /; /!40101 SET NAMES utf8 /; /!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE /; /!40103 SET TIME_ZONE='+00:00' /; /!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /; /!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /; /!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' /; /!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 /;

-- -- Table structure for table aowow_account

DROP TABLE IF EXISTS aowow_account; /!40101 SET @saved_cs_client = @@character_set_client /; /!40101 SET character_set_client = utf8 /; CREATE TABLE aowow_account ( id int(10) unsigned NOT NULL AUTO_INCREMENT, extId int(10) unsigned NOT NULL COMMENT 'external user id', user varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'login', passHash varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL, displayName varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'nickname', email varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, joinDate int(10) unsigned NOT NULL COMMENT 'unixtime', allowExpire tinyint(1) unsigned NOT NULL, dailyVotes smallint(5) unsigned NOT NULL DEFAULT 0, consecutiveVisits smallint(5) unsigned NOT NULL DEFAULT 0, curIP varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL, prevIP varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL, curLogin int(15) unsigned NOT NULL COMMENT 'unixtime', prevLogin int(15) unsigned NOT NULL, locale tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '0,2,3,6,8', userGroups smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'bitmask', avatar varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'icon-string for internal or id for upload', title varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'user can obtain custom titles', description mediumtext COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'markdown formated', excludeGroups smallint(5) unsigned NOT NULL DEFAULT 1 COMMENT 'profiler - exclude bitmask', userPerms tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT 'bool isAdmin', status tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT 'flag, see defines', statusTimer int(10) unsigned NOT NULL DEFAULT 0, token varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'creation & recovery', PRIMARY KEY (id), UNIQUE KEY user (user) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPACT; /!40101 SET character_set_client = @saved_cs_client /;

-- -- Table structure for table aowow_account_banned

DROP TABLE IF EXISTS aowow_account_banned; /!40101 SET @saved_cs_client = @@character_set_client /; /!40101 SET character_set_client = utf8 /; CREATE TABLE aowow_account_banned ( id int(16) unsigned NOT NULL, userId int(10) unsigned NOT NULL COMMENT 'affected accountId', staffId int(10) unsigned NOT NULL COMMENT 'executive accountId', typeMask tinyint(4) unsigned NOT NULL COMMENT 'ACCBAN', start int(10) unsigned NOT NULL COMMENT 'unixtime', end int(10) unsigned NOT NULL COMMENT 'automatic unban @ unixtime', reason varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (id), KEY FK_acc_banned (userId), CONSTRAINT FK_acc_banned FOREIGN KEY (userId) REFERENCES aowow_account (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPACT; /!40101 SET character_set_client = @saved_cs_client */;

-- -- Table structure for table aowow_account_bannedips

DROP TABLE IF EXISTS aowow_account_bannedips; /!40101 SET @saved_cs [ERR] 1146 - Table 'aowow.aowow_account_weightscales' doesn't exist [ERR] CREATE TABLE aowow_spell_sounds ( id smallint(5) unsigned NOT NULL COMMENT 'SpellVisual.dbc/id', animation smallint(5) unsigned NOT NULL, ready smallint(5) unsigned NOT NULL, precast smallint(5) unsigned NOT NULL, cast smallint(5) unsigned NOT NULL, impact smallint(5) unsigned NOT NULL, state smallint(5) unsigned NOT NULL, statedone smallint(5) unsigned NOT NULL, channel smallint(5) unsigned NOT NULL, casterimpact smallint(5) unsigned NOT NULL, targetimpact smallint(5) unsigned NOT NULL, castertargeting smallint(5) unsigned NOT NULL, missiletargeting smallint(5) unsigned NOT NULL, instantarea smallint(5) unsigned NOT NULL, persistentarea smallint(5) unsigned NOT NULL, casterstate smallint(5) unsigned NOT NULL, targetstate smallint(5) unsigned NOT NULL, missile smallint(5) unsigned NOT NULL COMMENT 'not predicted by js', impactarea smallint(5) unsigned NOT NULL COMMENT 'not predicted by js', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='!ATTENTION!\r\nthe primary key of this table is NOT a spellId, but spellVisualId\r\n\r\ncolumn names from LANG.sound_activities'; /!40101 SET character_set_client = @saved_cs_client */;

-- -- Table structure for table aowow_spelldifficulty

DROP TABLE IF EXISTS aowow_spelldifficulty; /!40101 SET @saved_cs_client = @@character_set_client /; /!40101 SET character_set_client = utf8 /; CREATE TABLE aowow_spelldifficulty ( normal10 mediumint(8) unsigned NOT NULL, normal25 mediumint(8) unsigned NOT NULL, heroic10 mediumint(8) unsigned NOT NULL, heroic25 mediumint(8) unsigned NOT NULL, KEY normal10 (normal10), KEY normal25 (normal25), KEY heroic10 (heroic10), KEY heroic25 (heroic25) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /!40101 SET character_set_client = @saved_cs_client /;

-- -- Table structure for table aowow_spellfocusobject

DROP TABLE IF EXISTS aowow_spellfocusobject; /!40101 SET @saved_cs_client = @@character_set_client /; /!40101 SET character_set_client = utf8 /; CREATE TABLE aowow_spellfocusobject ( id smallint(5) unsigned NOT NULL, name_loc0 varchar(83) COLLATE utf8mb4_unicode_ci NOT NULL, name_loc2 varchar(89) COLLATE utf8mb4_unicode_ci NOT NULL, name_loc3 varchar(95) COLLATE utf8mb4_unicode_ci NOT NULL, name_loc4 varchar(95) COLLATE utf8mb4_unicode_ci NOT NULL, name_loc6 varchar(90) COLLATE utf8mb4_unicode_ci NOT NULL, name_loc8 varchar(91) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /!40101 SET character_set_client = @saved_cs_client /;

-- -- Table structure for table aowow_spelloverride

DROP TABLE IF EXISTS aowow_spelloverride; /!40101 SET @saved_cs_client = @@character_set_client /; /!40101 SET character_set_client = utf8 /; CREATE TABLE aowow_spelloverride ( id bigint(20) NOT NULL, spellId1 bigint(20) NOT NULL, spellId2 bigint(20) NOT NULL, spellId3 bigint(20) NOT NULL, spellId4 bigint(20) NOT NULL, spellId5 bigint(20) NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /!40101 SET character_set_client = @saved_cs_client /;

-- -- Table structure for table aowow_spellrange

DROP TABLE IF EXISTS aowow_spellrange; /!40101 SET @saved_cs_client = @@character_set_client /; /!40101 SET character_set_client = utf8 /; CREATE TABLE aowow_spellrange ( id tinyint(3) unsigned NOT NULL, rangeMinHostile tinyint(3) unsigned NOT NULL, rangeMinFriend tinyint(3) unsigned NOT NULL, rangeMaxHostile smallint(5) unsigned NOT NULL, rangeMaxFriend smallint(5) unsigned NOT NULL, rangeType tinyint(3) unsigned NOT NULL, name_loc0 varchar(27) COLLATE utf8mb4_unicode_ci NOT NULL, name_loc2 varchar(27) COLLATE utf8mb4_unicode_ci NOT NULL, name_loc3 varchar(27) COLLATE utf8mb4_unicode_ci NOT NULL, name_loc4 varchar [ERR] 1146 - Table 'aowow.aowow_articles' doesn't exist [ERR] INSERT INTO aowow_articles VALUES (13,4,0,NULL,0,2,'[b][color=c4]Rogues[/color][/b] are a leather-clad melee class capable of dealing large amounts of damage to their enemies with very fast attacks. They are masters of stealth and assassination, passing by enemies unseen and striking from the shadows, then escaping from combat in the blink of an eye.\r\n\r\nThey are capable of using poisons to cripple their opponents, massively weakening them in battle. Rogues have a powerful arsenal of skills, many of which are strengthened by their ability to stealth and to incapacitate their victims.\r\n[ul]\r\n[li]Rogues can use a wide variety of melee weapons, such as daggers, fist weapons, one-handed maces, one-handed swords and one-handed axes.[/li]\r\n[li]By coating their weapons with [url=items=0.-3&filter=na=poison;ub=4]poison[/url] rogues can severely cripple or weaken their enemies.[/li]\r\n[li]When using [spell=1784] rogues will be unseen except by the most perceptive enemies.[/li]\r\n[/ul]',NULL),(14,1,0,NULL,0,2,'[b]Overview:[/b] The [b]humans[/b] are the most populous and the youngest race in Azeroth. The humans have become the [i]de facto[/i] leaders of the Alliance, with their youthful ambitions and resilience.\n\n[b]Capital City:[/b] The human seat of power is in the rebuilt city of [zone=1519].\n\n[b]Starting Zone:[/b] Humans begin questing in [zone=12].\n\n[b]Mounts:[/b] [npc=384] sells armoried ponies in Stormwind, and [npc=33307] at the Argent Tournament has a few distinct models.',NULL),(13,1,0,NULL,0,2,'[b][color=c1]Warriors[/color][/b] are a very powerful class, with the ability to tank or deal significant melee damage. The warrior\'s Protection tree contains many talents to improve their survivability and generate threat versus monsters. Protection warriors are one of the main tanking classes of the game.\n\nThey also have two damage-oriented talent trees - [icon name=ability_rogue_eviscerate][url=spells=7.1.26]Arms[/url][/icon] and [icon name=ability_warrior_innerrage][url=spells=7.1.256]Fury[/url][/icon], the latter of which includes the talent [spell=46917], which allows the warrior to wield two two-handed weapons at the same time! They are capable of strong melee AoE damage with spells such as [spell=845], [spell=1680], [spell=46924]. A warrior fights while in a specific [i]stance[/i], which grants him bonuses and access to different sets of abilities. He will use [spell=71] for tanking, and [spell=2457] or [spell=2458] for melee DPS.\n\n[ul]\n[li]All warriors can buff their raid or group by using a [i]shout[/i], [spell=6673] or [spell=469], and Fury warriors can provide the passive buff [spell=29801] which significantly increases the melee and ranged critical strike chance of his allies.[/li]\n[li]Warriors start out with only [spell=2457] at first, but learn [spell=71] at level 10 and [spell=2458] at level 30.[/li]\n[li]Warriors have numerous useful methods of getting to their target in a hurry! All warriors can use [spell=100] or [spell=20252] to reach an enemy and Protection warriors have [spell=3411], which allows them to intercept a friendly target and protect them from an attack.[/li]\n[/ul]',NULL),(13,2,0,NULL,0,2,'[b][color=c2]Paladins[/color][/b] bolster their allies with holy auras and blessing to protect their friends from harm and enhance their powers. Wearing heavy armor, they can withstand terrible blows in the thickest battles while healing their wounded allies and resurrecting the slain. In combat, they can wield massive two-handed weapons, stun their foes, destroy undead and demons, and judge their enemies with holy vengeance. Paladins are a defensive class, primarily designed to outlast their opponents.\n\nThe paladin is a mix of a melee fighter and a secondary spell caster. The paladin has a great deal of group utility due to the paladin\'s healing, blessings, and other abilities. Paladins can have one active aura per paladin on each party member and use specific blessings for specific players. Paladins are pretty hard to kill, thanks to their assortment of defensive abilities. T [SQL] Finished with error

Screenshots image

System:

Sarjuuk commented 5 months ago

probably same as #413

luyingwei80 commented 5 months ago

Yes, it should be the same as #413. Is there a solution? I apologize, as I am not familiar with database languages. Thank you.

luyingwei80 commented 5 months ago

I believe I have identified the issue. The SQL file imports without issue in MariaDB, but encounters errors when imported into MySQL.

Sarjuuk commented 5 months ago

Yes, it should be the same as #413. Is there a solution? I apologize, as I am not familiar with database languages. Thank you. me neither ^_~

it's probably best to restructure aowow_weightscale_data so the foreign key doesn't include the userId. It shouldn't really be necessary and i forgot why i included it in the first place.

luyingwei80 commented 5 months ago

It may be an issue with the MySQL dump tool storing Mariadb when exporting db_structure.sql. I imported it directly into Mariadb using navicat and it went very smoothly, but I reported an error when importing into the MySQL database. Now I can export the MariaDB database to an SQL file through Navicat and import it smoothly into the MySQL database.

In fact, when directly importing into the MySQL database, not only is there an error in the foreign key of aowow_weightscale_data, but there are also multiple other errors in the report that prevent the correct import. Therefore, it is estimated that there is still a formatting issue with the MySQL dump tool when exporting the database.

Sarjuuk commented 5 months ago

In fact, when directly importing into the MySQL database, not only is there an error in the foreign key of aowow_weightscale_data, but there are also multiple other errors in the report that prevent the correct import.

Would you kindly also post these other errors here?

luyingwei80 commented 5 months ago

Within the extensive textual content of the top post, I have included the entire import report, which includes numerous [err] indicators. Most of these errors indicate that certain tables do not exist.

Sarjuuk commented 5 months ago

sorry, but this amorphous blob of a log doesn't make sense.

like:

--
-- Table structure for table `aowow_account_bannedips`
--

DROP TABLE IF EXISTS `aowow_account_bannedips`;
/*!40101 SET @saved_cs
[ERR] 1146 - Table 'aowow.aowow_account_weightscales' doesn't exist
[ERR] 
CREATE TABLE `aowow_spell_sounds` (
  `id` smallint(5) unsigned NOT NULL COMMENT 'SpellVisual.dbc/id',
  `animation` smallint(5) unsigned NOT NULL,
  `ready` smallint(5) unsigned NOT NULL,
  `precast` smallint(5) unsigned NOT NULL,
  `cast` smallint(5) unsigned NOT NULL,

none of these tables are next to each other.

btw: you may want to update to 7d5930865ca2348ba454ea401f1cd7ef3f269984 Maybe it really was just the weightscale thing and it's working now.

luyingwei80 commented 5 months ago

Excellent, it is now functioning normally. I am deeply grateful for your selfless dedication.