update error ERROR 1054 (42S22)

Tony931023 commented 2 years ago

Current Behaviour

when applying the automatic updates it throws the following

mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1054 (42S22) at line 19 in file: 'D:\Temp\lk\azerothcore-wotlk\modules\mod-anticheat\sql\updates\characters\AC_2022_08_12_00.sql': Unknown error 1054

Applying of file 'D:/Temp/lk/azerothcore-wotlk/modules/mod-anticheat/sql/updates/characters/AC_2022_08_12_00.sql' to database 'acore_characters' failed! If you are a user, please pull the latest revision from the repository. Also make sure you have not applied any of the databases with your sql client. You cannot use auto-update system and import sql files from AzerothCore repository with your sql client. If you are a developer, please fix your sql query.

Expected Blizzlike Behaviour

the update should not have an error

Source

No response

Steps to reproduce the problem

compile the core with the module run the world server

Extra Notes

No response

AC rev. hash/commit

AC: https://github.com/azerothcore/azerothcore-wotlk/commit/27d0086b8c1f0a844aeb2f733f5699b21717019f Mod-Anticheat: https://github.com/azerothcore/mod-anticheat/commit/1dcfd80a5a968cea5b1ccc6aa682385783a8f852

Operating system

Ubuntu 20.04 anda Windows 11

Custom changes or Modules

No response

Tony931023 commented 2 years ago

@acidmanifesto

acidmanifesto commented 2 years ago

@acidmanifesto

Try updating the local. If it is updated try dropping the two report tables and restart the world.

Tony931023 commented 2 years ago

I deleted the two tables and it still gives an error

Tony931023 commented 2 years ago

when i run it manually

VV ADMIN EDIT BY ACID_MANIFESTO FOR READABILITY VV

7 queries executed, 5 success, 2 errors, 0 warnings

Query: DROP PROCEDURE IF EXISTS `updatemodanticheatdb`

0 row(s) affected

Execution Time : 0.037 sec
Transfer Time  : 0 sec
Total Time     : 0.037 sec
-----------------------------------------------------------

Query: CREATE PROCEDURE updatemodanticheatdb() proc:BEGIN SELECT COUNT(*) INTO @COLEXISTS FROM information_schema.COLUMNS WHERE TABLE_S...

0 row(s) affected

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.001 sec
-----------------------------------------------------------

Query: CALL updatemodanticheatdb()

Error Code: 1146
Unknown error 1146

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.001 sec
-----------------------------------------------------------

Query: DROP PROCEDURE IF EXISTS `updatemodanticheatdb`

0 row(s) affected

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0 sec
-----------------------------------------------------------

Query: CREATE PROCEDURE updatemodanticheatdb() proc:BEGIN SELECT COUNT(*) INTO @COLEXISTS FROM information_schema.COLUMNS WHERE TABLE_S...

0 row(s) affected

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.001 sec
-----------------------------------------------------------

Query: CALL updatemodanticheatdb()

Error Code: 1146
Unknown error 1146

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0 sec
-----------------------------------------------------------

Query: DROP PROCEDURE IF EXISTS `updatemodanticheatdb`

0 row(s) affected

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0 sec

acidmanifesto commented 2 years ago

Try re-running the base sql in the world folder not the update folder then load up the world and see if it runs the updates cleanly. If it doesnt work then let me know and ill ping yhe person so added in the updater for it.

Tony931023 commented 2 years ago

@acidmanifesto the same error, moving to the world folder. adding them manually to mysql. and running it from the same updater

acidmanifesto commented 2 years ago

@acidmanifesto the same error, moving to the world folder. adding them manually to mysql. and running it from the same updater

I went ahead and dropped a messgae to Nyeriah in the staffer's room. So we will wait to see what the issue could be when he replies.

Tony931023 commented 2 years ago

the error is when you call Query: CALL updatemodanticheatdb()

Nyeriah commented 2 years ago

Are you using MariaDB or mysql?

Tony931023 commented 2 years ago

ubuntu: mysql Ver 15.1 Distrib 10.3.34-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 Windows: mysql-5.7.31-winx64

acidmanifesto commented 2 years ago

ubuntu: mysql Ver 15.1 Distrib 10.3.34-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 Windows: mysql-5.7.31-winx64

at the moment there may be a issue with MariaDB. So the best solution I can offer you until the issue is looked into more is to provide you a fresh drop and import of the report tables with the updates applied already. Please apply this to your Character DB:

-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               5.7.29 - MySQL Community Server (GPL)
-- Server OS:                    Win64
-- HeidiSQL Version:             12.1.0.6537
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!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 */;

-- Dumping structure for table acore_characters.daily_players_reports
DROP TABLE IF EXISTS `daily_players_reports`;
CREATE TABLE IF NOT EXISTS `daily_players_reports` (
  `guid` int unsigned NOT NULL DEFAULT 0,
  `creation_time` int unsigned NOT NULL DEFAULT 0,
  `average` float NOT NULL DEFAULT 0,
  `total_reports` bigint unsigned NOT NULL DEFAULT 0,
  `speed_reports` bigint unsigned NOT NULL DEFAULT 0,
  `fly_reports` bigint unsigned NOT NULL DEFAULT 0,
  `jump_reports` bigint unsigned NOT NULL DEFAULT 0,
  `waterwalk_reports` bigint unsigned NOT NULL DEFAULT 0,
  `teleportplane_reports` bigint unsigned NOT NULL DEFAULT 0,
  `climb_reports` bigint unsigned NOT NULL DEFAULT 0,
  `teleport_reports` bigint unsigned NOT NULL DEFAULT 0,
  `ignorecontrol_reports` bigint unsigned NOT NULL DEFAULT 0,
  `zaxis_reports` bigint unsigned NOT NULL DEFAULT 0,
  `antiswim_reports` bigint unsigned NOT NULL DEFAULT 0,
  `gravity_reports` bigint unsigned NOT NULL DEFAULT 0,
  `antiknockback_reports` bigint unsigned NOT NULL DEFAULT 0,
  `no_fall_damage_reports` bigint unsigned NOT NULL DEFAULT 0,
  `op_ack_hack_reports` bigint unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Data exporting was unselected.

-- Dumping structure for table acore_characters.players_reports_status
DROP TABLE IF EXISTS `players_reports_status`;
CREATE TABLE IF NOT EXISTS `players_reports_status` (
  `guid` int unsigned NOT NULL DEFAULT 0,
  `creation_time` int unsigned NOT NULL DEFAULT 0,
  `average` float NOT NULL DEFAULT 0,
  `total_reports` bigint unsigned NOT NULL DEFAULT 0,
  `speed_reports` bigint unsigned NOT NULL DEFAULT 0,
  `fly_reports` bigint unsigned NOT NULL DEFAULT 0,
  `jump_reports` bigint unsigned NOT NULL DEFAULT 0,
  `waterwalk_reports` bigint unsigned NOT NULL DEFAULT 0,
  `teleportplane_reports` bigint unsigned NOT NULL DEFAULT 0,
  `climb_reports` bigint unsigned NOT NULL DEFAULT 0,
  `teleport_reports` bigint unsigned NOT NULL DEFAULT 0,
  `ignorecontrol_reports` bigint unsigned NOT NULL DEFAULT 0,
  `zaxis_reports` bigint unsigned NOT NULL DEFAULT 0,
  `antiswim_reports` bigint unsigned NOT NULL DEFAULT 0,
  `gravity_reports` bigint unsigned NOT NULL DEFAULT 0,
  `antiknockback_reports` bigint unsigned NOT NULL DEFAULT 0,
  `no_fall_damage_reports` bigint unsigned NOT NULL DEFAULT 0,
  `op_ack_hack_reports` bigint unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Data exporting was unselected.

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;

If any error pops up on the console screen please let me know. There may be a good chance the collumns and updates are applied and its just complaining about it already exsisting but should move forward with no issues outside of the complaint.

acidmanifesto commented 2 years ago

@Tony931023 are you having the issue on your windows build? For linux we require Maria 10.5 or 10.6, you are listing 10.3.34, which is not supported per our wiki here

acidmanifesto commented 2 years ago

Closing due to lack of follow up. Possible unsupported mariadb version is used for anyone researching any simular issue on this module git.

Tony931023 commented 2 years ago

on my production server I am using MariaDB,

I am doing the tests on my local server with MySQL 5.7.31, the minimum version that AC supports is 5.7.0

trying this solution that you gave me here if it works well

ubuntu: mysql Ver 15.1 Distrib 10.3.34-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 Windows: mysql-5.7.31-winx64

at the moment there may be a issue with MariaDB. So the best solution I can offer you until the issue is looked into more is to provide you a fresh drop and import of the report tables with the updates applied already. Please apply this to your Character DB:

-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               5.7.29 - MySQL Community Server (GPL)
-- Server OS:                    Win64
-- HeidiSQL Version:             12.1.0.6537
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!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 */;

-- Dumping structure for table acore_characters.daily_players_reports
DROP TABLE IF EXISTS `daily_players_reports`;
CREATE TABLE IF NOT EXISTS `daily_players_reports` (
  `guid` int unsigned NOT NULL DEFAULT 0,
  `creation_time` int unsigned NOT NULL DEFAULT 0,
  `average` float NOT NULL DEFAULT 0,
  `total_reports` bigint unsigned NOT NULL DEFAULT 0,
  `speed_reports` bigint unsigned NOT NULL DEFAULT 0,
  `fly_reports` bigint unsigned NOT NULL DEFAULT 0,
  `jump_reports` bigint unsigned NOT NULL DEFAULT 0,
  `waterwalk_reports` bigint unsigned NOT NULL DEFAULT 0,
  `teleportplane_reports` bigint unsigned NOT NULL DEFAULT 0,
  `climb_reports` bigint unsigned NOT NULL DEFAULT 0,
  `teleport_reports` bigint unsigned NOT NULL DEFAULT 0,
  `ignorecontrol_reports` bigint unsigned NOT NULL DEFAULT 0,
  `zaxis_reports` bigint unsigned NOT NULL DEFAULT 0,
  `antiswim_reports` bigint unsigned NOT NULL DEFAULT 0,
  `gravity_reports` bigint unsigned NOT NULL DEFAULT 0,
  `antiknockback_reports` bigint unsigned NOT NULL DEFAULT 0,
  `no_fall_damage_reports` bigint unsigned NOT NULL DEFAULT 0,
  `op_ack_hack_reports` bigint unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Data exporting was unselected.

-- Dumping structure for table acore_characters.players_reports_status
DROP TABLE IF EXISTS `players_reports_status`;
CREATE TABLE IF NOT EXISTS `players_reports_status` (
  `guid` int unsigned NOT NULL DEFAULT 0,
  `creation_time` int unsigned NOT NULL DEFAULT 0,
  `average` float NOT NULL DEFAULT 0,
  `total_reports` bigint unsigned NOT NULL DEFAULT 0,
  `speed_reports` bigint unsigned NOT NULL DEFAULT 0,
  `fly_reports` bigint unsigned NOT NULL DEFAULT 0,
  `jump_reports` bigint unsigned NOT NULL DEFAULT 0,
  `waterwalk_reports` bigint unsigned NOT NULL DEFAULT 0,
  `teleportplane_reports` bigint unsigned NOT NULL DEFAULT 0,
  `climb_reports` bigint unsigned NOT NULL DEFAULT 0,
  `teleport_reports` bigint unsigned NOT NULL DEFAULT 0,
  `ignorecontrol_reports` bigint unsigned NOT NULL DEFAULT 0,
  `zaxis_reports` bigint unsigned NOT NULL DEFAULT 0,
  `antiswim_reports` bigint unsigned NOT NULL DEFAULT 0,
  `gravity_reports` bigint unsigned NOT NULL DEFAULT 0,
  `antiknockback_reports` bigint unsigned NOT NULL DEFAULT 0,
  `no_fall_damage_reports` bigint unsigned NOT NULL DEFAULT 0,
  `op_ack_hack_reports` bigint unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Data exporting was unselected.

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;

If any error pops up on the console screen please let me know. There may be a good chance the collumns and updates are applied and its just complaining about it already exsisting but should move forward with no issues outside of the complaint.

azerothcore / mod-anticheat