Raised This Month: $13 Target: $400
 3% 

Hlstatsx enable utf8mb4 encoding


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
MrGarfield
Senior Member
Join Date: Feb 2013
Old 12-07-2019 , 09:15   Hlstatsx enable utf8mb4 encoding
Reply With Quote #1

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
MrGarfield is offline
NomisCZ
AlliedModders Donor
Join Date: Mar 2014
Location: Czech_Republic
Old 12-07-2019 , 11:30   Re: Hlstatsx enable utf8mb4 encoding
Reply With Quote #2

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'] = '[email protected]';
        
$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
__________________

Last edited by NomisCZ; 12-07-2019 at 11:34.
NomisCZ is offline
MrGarfield
Senior Member
Join Date: Feb 2013
Old 12-10-2019 , 13:04   Re: Hlstatsx enable utf8mb4 encoding
Reply With Quote #3

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

Last edited by MrGarfield; 12-10-2019 at 13:04.
MrGarfield is offline
NomisCZ
AlliedModders Donor
Join Date: Mar 2014
Location: Czech_Republic
Old 12-10-2019 , 17:17   Re: Hlstatsx enable utf8mb4 encoding
Reply With Quote #4

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
__________________

Last edited by NomisCZ; 12-10-2019 at 17:17.
NomisCZ is offline
MrGarfield
Senior Member
Join Date: Feb 2013
Old 12-12-2019 , 13:13   Re: Hlstatsx enable utf8mb4 encoding
Reply With Quote #5

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 is offline
MrGarfield
Senior Member
Join Date: Feb 2013
Old 12-16-2019 , 13:11   Re: Hlstatsx enable utf8mb4 encoding
Reply With Quote #6

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

Last edited by MrGarfield; 12-17-2019 at 03:21.
MrGarfield is offline
NomisCZ
AlliedModders Donor
Join Date: Mar 2014
Location: Czech_Republic
Old 12-19-2019 , 18:39   Re: Hlstatsx enable utf8mb4 encoding
Reply With Quote #7

Quote:
Originally Posted by MrGarfield View Post
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.
__________________
NomisCZ is offline
MrGarfield
Senior Member
Join Date: Feb 2013
Old 12-20-2019 , 15:51   Re: Hlstatsx enable utf8mb4 encoding
Reply With Quote #8

ok great thank you very much and nice work anyway
MrGarfield is offline
andrepires68
Member
Join Date: Feb 2010
Location: Portugal
Old 03-16-2020 , 09:28   Re: Hlstatsx enable utf8mb4 encoding
Reply With Quote #9

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:


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
andrepires68 is offline
NomisCZ
AlliedModders Donor
Join Date: Mar 2014
Location: Czech_Republic
Old 03-16-2020 , 10:54   Re: Hlstatsx enable utf8mb4 encoding
Reply With Quote #10

Quote:
Originally Posted by andrepires68 View Post
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:


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.
__________________
NomisCZ is offline
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


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


Powered by vBulletin®
Copyright ©2000 - 2020, vBulletin Solutions, Inc.
Theme made by Freecode