AlliedModders - Hlstatsx enable utf8mb4 encoding

AlliedModders (https://forums.alliedmods.net/index.php)

- HLstatsX:CE (https://forums.alliedmods.net/forumdisplay.php?f=156)

- - Hlstatsx enable utf8mb4 encoding (https://forums.alliedmods.net/showthread.php?t=320159)

MrGarfield

12-07-2019 09:15

Hlstatsx enable utf8mb4 encoding

Hi everybody

I'm blocking on utf8mb4 encoding i do not know how to do it ?
if there was a little tutorial it would be nice

thank you

NomisCZ

12-07-2019 11:30

Re: Hlstatsx enable utf8mb4 encoding

Solution #1 - convert DB tables/columns to utf8mb4_unicode_ci:

1. - A phpMyAdmin - select database -> Operations -> Collation -> utf8mb4_unicode_ci -> Change all tables collations and Change all tables columns collations -> Go.

1. - B PHP script - change DB credentials in setConfig() function and run it

PHP Code:


		
			
<?php

class Convert {

    private $config;
    private $dbConn;
    private $localWordsList;

    public function __construct()
    {
        $this->setConfig();
        
        if (!$this->getConnection()) {
            print "Failed to connect to DB!";
            exit();
        }

        $this->doConvert();

    }

    private function setConfig()
    {
        // Change it ...
        $this->config['dbUser'] = 'my_stats';
        $this->config['dbPass'] = 'P@ssword';
        $this->config['dbHost'] = 'localhost';
        $this->config['dbName'] = 'my_stats';
    }

    private function getConnection()
    {

        $dbConn = new mysqli($this->config['dbHost'], $this->config['dbUser'], $this->config['dbPass'], $this->config['dbName']);

        if ($dbConn->connect_errno) {
            return false;
        }

        $this->dbConn = $dbConn;
        return true;
    }

    private function doQuery($query) {

        $result = $this->dbConn->query($query);
        return $result ?: false;
    }

    private function getDBTables()
    {

        $query = "SHOW TABLES";
        $result = $this->doQuery($query);
        if ($result) {

            $finalResult = array();

            while($tables = mysqli_fetch_array($result)) {
                
                $finalResult[] = $tables[0];
            }

            return $finalResult;
        }
        return false;
    }

    private function getDBColumns($dbTable)
    {

        $query = "SHOW COLUMNS FROM $dbTable";
        $result = $this->doQuery($query);
        if ($result) {

            $finalResult = array();

            while($columns = mysqli_fetch_array($result)) {
                
                $finalResult[] = $columns[0];
            }

            return $finalResult;
        }
        return false;
    }
    
    public function doConvert()
    {
        $tables = $this->getDBTables();

        foreach ($tables as $key => $value) {

            $query = "ALTER TABLE $value CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;";

            if ($this->doQuery($query)) {
                echo "[OK] Table " . $value . " converted<br>";

                $columns = $this->getDBColumns($value);

                echo '------------------------------<br>';
                
                foreach ($columns as $columnKey => $column) {
                    echo '| '.$column.' |<br>';
                }
                
                echo '------------------------------<br>';

            } else {
                echo "[ERR] ".$value." error<br>";
            }
        }
    }
}

new Convert();

2. Edit these files (utf8mb4) https://github.com/NomisCZ/hlstatsx-...oped_q=utf8mb4, https://github.com/NomisCZ/hlstatsx-...c400e3ec30a547

Solution #2 - update HLStatsX daemon and website:

1. Stop daemon
2. Run all migration queries: https://github.com/NomisCZ/hlstatsx-...sql/migrations
3. Download latest daemon: https://github.com/NomisCZ/hlstatsx-...master/scripts
4. Replace all daemon files (backup your hlstatsx.conf before)
5. Install Perl packages (1.5. Install Perl packages) - https://github.com/NomisCZ/hlstatsx-...llation#-linux
6. Prepare GeoIP2 (2.4 Prepare GeoIP2) - https://github.com/NomisCZ/hlstatsx-...2-installation
7. Download latest web files - https://github.com/NomisCZ/hlstatsx-...ree/master/web
8. Backup your config.php, upload all files to your webhosting and remove updater folder
9. Go to you stats.domain.com -> Admin -> Tools (right column) -> Reset All DB Collations to UTF8
10. Start daemon and check logs

MrGarfield

12-10-2019 13:04

Re: Hlstatsx enable utf8mb4 encoding

hi NomisCZ installation is pass but GeoIP2 google card not work? an idea thank you ICI

NomisCZ

12-10-2019 17:17

Re: Hlstatsx enable utf8mb4 encoding

GeoIP2 is supported only in version >= 1.7.x (daemon) and it has nothing to do with the map.

Google Maps JavaScript API error: RefererNotAllowedMapError
https://developers.google.com/maps/d...owed-map-error

MrGarfield

12-12-2019 13:13

Re: Hlstatsx enable utf8mb4 encoding

Hello NomisCZ after a big doubt I just installed hlstatsx on another server, and I realize that it comes from the host if the card does not work
http://137.74.152.160/hlstats/hlstats.php does not work
http://51.178.28.193/hlstats/hlstats.php same installation works
do you have an idea to solve the problem or contact my host

MrGarfield

12-16-2019 13:11

Re: Hlstatsx enable utf8mb4 encoding

hi so my geolocation problem and settle by cons just a derrniere thing I have no rank? it's normal that I have this image http://137.74.152.160/hlstats/hlstatsimg/mmranks/0.png
my hlstats this is here http://137.74.152.160/hlstats/hlstat...s&game=cstrike

NomisCZ

12-19-2019 18:39

Re: Hlstatsx enable utf8mb4 encoding

Quote:

Originally Posted by MrGarfield (Post 2677141)

hi so my geolocation problem and settle by cons just a derrniere thing I have no rank? it's normal that I have this image http://137.74.152.160/hlstats/hlstatsimg/mmranks/0.png
my hlstats this is here http://137.74.152.160/hlstats/hlstat...s&game=cstrike

Rank column is only for CS:GO plugin - mmranks.

MrGarfield

12-20-2019 15:51

Re: Hlstatsx enable utf8mb4 encoding

ok great thank you very much and nice work anyway

andrepires68

03-16-2020 09:28

Re: Hlstatsx enable utf8mb4 encoding

Still regarding this encoding, I've been getting this error when I click on a player on the hlstatsx page and then click Terms and actions:
https://i.imgur.com/54a8wSE.png

Last SQL Query:

SELECT
IFNULL(hlstats_Roles.name, hlstats_Events_ChangeRole.role) AS name,
IFNULL(hlstats_Roles.code, hlstats_Events_ChangeRole.role) AS code,
COUNT(hlstats_Events_ChangeRole.id) AS rolecount,
ROUND(COUNT(hlstats_Events_ChangeRole.id) / IF(0 = 0, 1, 0) * 100, 2) AS percent,
hlstats_Frags_as_res.killsTotal,
hlstats_Frags_as_res.deathsTotal,
ROUND(hlstats_Frags_as_res.killsTotal / IF(hlstats_Frags_as_res.deathsTotal = 0, 1, hlstats_Frags_as_res.deathsTotal), 2) AS kpd
FROM
hlstats_Events_ChangeRole
LEFT JOIN
hlstats_Roles
ON
hlstats_Events_ChangeRole.role = hlstats_Roles.code
LEFT JOIN
hlstats_Frags_as_res
ON
hlstats_Frags_as_res.role = hlstats_Events_ChangeRole.role
WHERE
hlstats_Events_ChangeRole.playerId = 5
AND
(
hidden <> '1'
OR hidden IS NULL
)
AND hlstats_Roles.game = 'css'
GROUP BY
hlstats_Events_ChangeRole.role
ORDER BY
rolecount desc,
name desc

NomisCZ

03-16-2020 10:54

Re: Hlstatsx enable utf8mb4 encoding

Quote:

Originally Posted by andrepires68 (Post 2687189)

Still regarding this encoding, I've been getting this error when I click on a player on the hlstatsx page and then click Terms and actions:
https://i.imgur.com/54a8wSE.png

Last SQL Query:

SELECT
IFNULL(hlstats_Roles.name, hlstats_Events_ChangeRole.role) AS name,
IFNULL(hlstats_Roles.code, hlstats_Events_ChangeRole.role) AS code,
COUNT(hlstats_Events_ChangeRole.id) AS rolecount,
ROUND(COUNT(hlstats_Events_ChangeRole.id) / IF(0 = 0, 1, 0) * 100, 2) AS percent,
hlstats_Frags_as_res.killsTotal,
hlstats_Frags_as_res.deathsTotal,
ROUND(hlstats_Frags_as_res.killsTotal / IF(hlstats_Frags_as_res.deathsTotal = 0, 1, hlstats_Frags_as_res.deathsTotal), 2) AS kpd
FROM
hlstats_Events_ChangeRole
LEFT JOIN
hlstats_Roles
ON
hlstats_Events_ChangeRole.role = hlstats_Roles.code
LEFT JOIN
hlstats_Frags_as_res
ON
hlstats_Frags_as_res.role = hlstats_Events_ChangeRole.role
WHERE
hlstats_Events_ChangeRole.playerId = 5
AND
(
hidden <> '1'
OR hidden IS NULL
)
AND hlstats_Roles.game = 'css'
GROUP BY
hlstats_Events_ChangeRole.role
ORDER BY
rolecount desc,
name desc

Illegal mix of collations - utf8mb4_0900_ai_ci, so some tables/columns have different collation then others ...

https://www.monolune.com/what-is-the..._ci-collation/
https://dev.mysql.com/doc/relnotes/m...ews-8-0-1.html
and search Google, this is not a problem with HLStatsX but with your MySQL 8.x probably.

All times are GMT -4. The time now is 17:07.

Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.