Sorry, I'm unsure if I understood your question. The Permission table has columns of type integer. Your server returns type string. The fix I served above simply converted string to integer. Was that your question?
Here is one simple plugin which uses only the standard php library PDO to connect to your database and show the type of a permission column if you go to yourforum.com/plugin/manualPdo:
So there really is no Vanilla problem that I would be able to support.
You can check with phpMyAdmin or something like that if the tables column really is of the expected format integer. But I cannot do much more than you would be able to do. The maximum support I was able to give was to find out where you need to work on to, but sorry, that's really all.
I would advice PHP 7.4 and MySql version 8. That's what I'm using and I do not have any problems. And also don't use web servers which come with their own compiled PHP version. Make sure the mysqlnd fits to your PHP version.
Sorry what I am saying is that because my MySQL server is returning permissions as strings values instead of int values is what we believe is the issue. We can test that theory by using your fix and step over the array of permissions and convert any number that is a string value to an int value, that SEEMS that solves the problem. A logical conclusion to this would be that the forum software is probably expecting the permissions array to be in an int format not string format. I would agree with this based only on this testing.
BUT
If the above is true, WHY on a fresh install of 2021.012 does my MySQL database return string values for the permissions array but the forum behaves as expected? If what we concluded above was true then this new fresh forum should also be blank and you can't view any categories .
So based on that I believe this is not the issue it's a symptom but not the root issue.
I don't know. After values are returned from the database, they are processed by Vanilla. It seems that category permissions are also part of this problem. Maybe if you create some categories with custom permissions and / or nested categories and / or categories of a special type, the problem will occur in your test forum, too.
For PHP '1' == 1 is true, but '1' === 1 is false. My assumption is that somewhere either in the UserModel, in the PermissionModel or in the CategoryModel there is a strict comparison, but only in some if condition that isn't used in general, but only in connection with some category permission check.
Since this strict comparison could also be used in any part of the source code (Vanillas code and also any of the 3rd party packages), it's an endless effort to search for the part of the code where this could be tweaked. Besides of that: you will never know what other unexpected behaviours might occur and what consequences they will have.
@R_J Doing some more testing using your plugin (See photos below) you can see the results and you can see that my table structure is correct. I am running PHP version 7.4 and using mysqlnd extension is installed. but my MySQL version is '5.7.34-cll-lve - MySQL Community Server - (GPL)' I am going to talk to my hosting provider to see if I can get upgraded to version 8 to see if that fixes the issue. I am also going to do my own database test outside of Vanilla using PHP and PDO to test the database values as Vanilla could be the reason why ints are being returned as strings so using their code to test could poison the results
You can directly take the code from my plugin. It uses the PDO class - PHPs database standard class. You just need to fill in your database credentials
Okay after doing some more testing outside of Vanilla I have seen the same issue. And from what I have researched on the internet is that this is a know issue with PHP and MySQL that INT vales get returned as STRING values. And to combat this issue to make sure you are using mysqlnd.
I am not sure if newer versions of MySQL (ex 8) have fixed this issue but 5.7 is still not EOL of another 2 years so I would expect that Vanilla would continue to support it or else make a very clear statement that if you are running older versions of MySQL to ensure this mysqlnd is used.
I am waiting to hear back form my hosting team to see if I can enable this mysqlnd as I have it checked off in my PHP config in cPanel. I know we can blame the developers or you can blame the software but if problems like this exist should there not be minimum requirement test added to Vanilla that say it wont run unless it sees that you have this mysqlnd installed to prevent this issue?
Okay so wow, some huge revelations here and @R_J you are amazing with your help. Even though a brand new version of 2021.012 looked like it was working with String values returned form the database it was NOT working!! I WAS WRONG (I can admit when I am wrong) I didn't even notice certain thing were not there like my user Icon was not showing up, there was no way to log off... And after adding custom permissions they were not being followed as you suspected @R_J . So after trying to figure out WTF was going on with the string thing and talking to my hosting provider they said they have no servers running MySQL 8.0 so that was not an option. After doing research as to how others fixed this with the 5.7 version of MySQL I realized they kept saying what @R_J was saying was that you need to be using the MySQLnd driver. I have cPanel on my server and this option was checked and in a phpinfo() it said it was installed. What I did not realize is that it was not using it! you cannot use PDO MySQL &PDO MySQLnd at the same time (Obviously). Because my cPanel options for PHP settings are in alphabetical order I figured all these settings would be under P for PDO but fond this little reply from someone else in an article that mention cPanel has this setting under the N section for ND (go figure) So I disabled pdo_mysql and then enabled 'nd_pdo_mysql' & 'mysqlnd' once these settings took place. I ran my test code again and now it was returning INT values form the database. (YEAH). I also tested with @R_Js test plugin, it was now also returning INT values (Success). I loaded the site and everything is now functional!
To any users before you do the update please check using @R_Js plugin to see if your server is returning STRING values or INT values. If your database is returning STRING values you will have to fix this BEFORE upgrading. If the returned values are STRING then you will have to update to use mysqlnd. For every user that will be different based on your server for cPapnel do the following:
This will ensure you are using MySQLnd and then run the test plugin again and you shoudl see INT values returned. I hope this helps others. I am going to do the update again and see if I run into issues this time!
Ok, so I tried the suggestions by MrCaspian -- I have just updated to 2021.012, and have changed my PHP config options accordingly, but I'm still seeing all strings in the RJTest plugin. Do I need to do anything else? Do I need to change my database driver option or just leave it at the default "MySQL"? I even tried playing around with enabling/disabling other php database options but with no luck.
@pinchies so you need to use @R_J test plugin BEFORE you do the upgrade, and if you see STRING values and not INT values this tells you that your MySQL database suffers from the same issues which many of us are having and a well know issue it seem sin the PHP MySQL community. Because you are seeing STRING values returned try doing the changes I suggested. After applying those changes to your server run @R_Jsi test again to see if now you see INT values. If you do see INT values then you have corrected your PHP/MySQL server and you can do the upgrade but if you see STRING values then is no point in doing the upgrade as the newer code is written it seems with more strict comparator operators and where the old form was using less string comparators
// Comparing number to a string value old less strict code
// This only tests to see if they are equal in value and does not care their type
1 == '1' // Would Return TRUE
// Comparing number to a string value new strict code
// This test to see if they are IDENTICAL in value and in TYPE IE values are the same and they are both STRING or INT
1 === '1' // Would Return FALSE
Hence why in the newer more strict code when testing if a user has access to a Category using STRING values returned form the database it would return FALSE even though using less strict code would return true. Writing stricter code produces more of a desired result but you must ensure all your values are type cast properly. So three choices are:
The developer ensures that every value in the permissions array is type cast properly to the type expected (A lot of work)
Leave it up to the end user to provide their app with proper TYPE cast data from their database (Reasonable)
3) Revert to less strict code (More funny things will happen)
The solution I think here is that the developers could not have possibly known that there is this weird issue with PHP MySQL PDO on older version of MySQL that don't return the value stored in the database as its type cast. If its stored in a table that is set to TINYINT(4) you would assume the value returned form the database should be an it when you retrieve it! Maybe the developers can put a check in the code to let users know of this issue on setup that tests storing an INT value in the database and retrieving and testing its type and if its a STRING then force them to fix this before installing.
Thank you for explaining again in depth. However, I must reassure, I really did and do understand the issue -- however, even when I have changed the enabled PHP Extensions (as shown below), the same output is obtained with all strings from RJ's plugin.
@pinchies Sorry the explanation was more of a for anyone else. but was a good chance to explain it. Your settings look correct it might be the MySQLi can you also uncheck mysqli and check nd_mysqli and just test?
Sorry lets do one test maybe @R_Js plugin is not working 100% (I assumed it was) if you put this code in a test.php file on your server and fill in the blanks from your conf.php file see what this outputs This will dump the row for the admin user and specify the type of the values.
If you are using .htaccess you might have to alter it to allow this script to run I added this one line to make sure I can hit the test.php
.htaccess
####
# Prevent access to any php script by redirecting the request to /index.php
# You can add an exception by adding another RewriteCond after this one.
# Example: RewriteCond %{REQUEST_URI} !^/yourscriptname.php$
# You can comment out this section if it causes you problems.
# This is just a nice to have for security purposes.
####
RewriteCond %{REQUEST_URI} !/index.php$
RewriteCond %{REQUEST_URI} !^/test.php$
RewriteRule (.+\.php) [E=X_REWRITE:1,E=X_PATH_INFO:/$1,L]
test.php
<?php
function OpenDatabase(){
$Var = [
'DBHost' => 'localhost',
'DBUser' => '',
'DBPassword' => '',
'DBName' => '',
];
return new PDO("mysql:host={$Var['DBHost']};dbname={$Var['DBName']};",$Var['DBUser'],$Var['DBPassword'],array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
}
$dbh = OpenDatabase();
foreach($dbh->query("SELECT * FROM GDN_Permission WHERE PermissionID = 1") as $Row) {$permissions[] = $Row;}
var_dump($permissions);
exit;
?>
@R_J I'm not sure what's up with your plugin but when I test before and after my changes your plugin still says STRING values. On my test.php posted above it shows STRING before the update then INT values after the change to the MySQLnd driver.
Any ideas why your Plugin is not returning INT values when mine does?
I just wanted to confirm, that this means that @MrCaspan 's PHP extensions fix, using the native driver mysqlnd, does fix this int/string type issue. The code responsible for DB connection in the Vanilla package /library/database/class.database.php file already correctly disables the PDO::ATTR_EMULATE_PREPARES option. This means no code changes are required.
Forum permissions now appear to be working! Which means we will have fixed the root cause of the "categories not found" issue.
This also finally seems to mean I can properly use the fix counts function too!!
To sum up: if using old MySQL 5.7, the solution is to change the PHP extensions, and switch from the PDO_mysql extension, to the native driver, called nd_pdo_mysql. The correct configuration for MySQL is:
Comments
Sorry, I'm unsure if I understood your question. The Permission table has columns of type integer. Your server returns type string. The fix I served above simply converted string to integer. Was that your question?
Here is one simple plugin which uses only the standard php library PDO to connect to your database and show the type of a permission column if you go to yourforum.com/plugin/manualPdo:
So there really is no Vanilla problem that I would be able to support.
You can check with phpMyAdmin or something like that if the tables column really is of the expected format integer. But I cannot do much more than you would be able to do. The maximum support I was able to give was to find out where you need to work on to, but sorry, that's really all.
I would advice PHP 7.4 and MySql version 8. That's what I'm using and I do not have any problems. And also don't use web servers which come with their own compiled PHP version. Make sure the mysqlnd fits to your PHP version.
@R_J
Sorry what I am saying is that because my MySQL server is returning permissions as strings values instead of int values is what we believe is the issue. We can test that theory by using your fix and step over the array of permissions and convert any number that is a string value to an int value, that SEEMS that solves the problem. A logical conclusion to this would be that the forum software is probably expecting the permissions array to be in an int format not string format. I would agree with this based only on this testing.
BUT
If the above is true, WHY on a fresh install of 2021.012 does my MySQL database return string values for the permissions array but the forum behaves as expected? If what we concluded above was true then this new fresh forum should also be blank and you can't view any categories .
So based on that I believe this is not the issue it's a symptom but not the root issue.
Okay, understood.
I don't know. After values are returned from the database, they are processed by Vanilla. It seems that category permissions are also part of this problem. Maybe if you create some categories with custom permissions and / or nested categories and / or categories of a special type, the problem will occur in your test forum, too.
For PHP '1' == 1 is true, but '1' === 1 is false. My assumption is that somewhere either in the UserModel, in the PermissionModel or in the CategoryModel there is a strict comparison, but only in some if condition that isn't used in general, but only in connection with some category permission check.
Since this strict comparison could also be used in any part of the source code (Vanillas code and also any of the 3rd party packages), it's an endless effort to search for the part of the code where this could be tweaked. Besides of that: you will never know what other unexpected behaviours might occur and what consequences they will have.
@R_J Doing some more testing using your plugin (See photos below) you can see the results and you can see that my table structure is correct. I am running PHP version 7.4 and using mysqlnd extension is installed. but my MySQL version is '5.7.34-cll-lve - MySQL Community Server - (GPL)' I am going to talk to my hosting provider to see if I can get upgraded to version 8 to see if that fixes the issue. I am also going to do my own database test outside of Vanilla using PHP and PDO to test the database values as Vanilla could be the reason why ints are being returned as strings so using their code to test could poison the results
You can directly take the code from my plugin. It uses the PDO class - PHPs database standard class. You just need to fill in your database credentials
Okay after doing some more testing outside of Vanilla I have seen the same issue. And from what I have researched on the internet is that this is a know issue with PHP and MySQL that INT vales get returned as STRING values. And to combat this issue to make sure you are using mysqlnd.
I am not sure if newer versions of MySQL (ex 8) have fixed this issue but 5.7 is still not EOL of another 2 years so I would expect that Vanilla would continue to support it or else make a very clear statement that if you are running older versions of MySQL to ensure this mysqlnd is used.
I am waiting to hear back form my hosting team to see if I can enable this mysqlnd as I have it checked off in my PHP config in cPanel. I know we can blame the developers or you can blame the software but if problems like this exist should there not be minimum requirement test added to Vanilla that say it wont run unless it sees that you have this mysqlnd installed to prevent this issue?
EDIT: Link to article on PDO and PHP https://stackoverflow.com/questions/1197005/how-to-get-numeric-types-from-mysql-using-pdo
Okay so wow, some huge revelations here and @R_J you are amazing with your help. Even though a brand new version of 2021.012 looked like it was working with String values returned form the database it was NOT working!! I WAS WRONG (I can admit when I am wrong) I didn't even notice certain thing were not there like my user Icon was not showing up, there was no way to log off... And after adding custom permissions they were not being followed as you suspected @R_J . So after trying to figure out WTF was going on with the string thing and talking to my hosting provider they said they have no servers running MySQL 8.0 so that was not an option. After doing research as to how others fixed this with the 5.7 version of MySQL I realized they kept saying what @R_J was saying was that you need to be using the MySQLnd driver. I have cPanel on my server and this option was checked and in a phpinfo() it said it was installed. What I did not realize is that it was not using it! you cannot use PDO MySQL &PDO MySQLnd at the same time (Obviously). Because my cPanel options for PHP settings are in alphabetical order I figured all these settings would be under P for PDO but fond this little reply from someone else in an article that mention cPanel has this setting under the N section for ND (go figure) So I disabled pdo_mysql and then enabled 'nd_pdo_mysql' & 'mysqlnd' once these settings took place. I ran my test code again and now it was returning INT values form the database. (YEAH). I also tested with @R_Js test plugin, it was now also returning INT values (Success). I loaded the site and everything is now functional!
To any users before you do the update please check using @R_Js plugin to see if your server is returning STRING values or INT values. If your database is returning STRING values you will have to fix this BEFORE upgrading. If the returned values are STRING then you will have to update to use mysqlnd. For every user that will be different based on your server for cPapnel do the following:
This will ensure you are using MySQLnd and then run the test plugin again and you shoudl see INT values returned. I hope this helps others. I am going to do the update again and see if I run into issues this time!
Amazing! Thanks for that detailed description. I hope that anybody else will be able to solve this problem with your help 👍
Ok, so I tried the suggestions by MrCaspian -- I have just updated to 2021.012, and have changed my PHP config options accordingly, but I'm still seeing all strings in the RJTest plugin. Do I need to do anything else? Do I need to change my database driver option or just leave it at the default "MySQL"? I even tried playing around with enabling/disabling other php database options but with no luck.
Vanilla version: 2021.012
PHP version: 7.4.22
SQL Server version: 5.7.35
Server Software: LiteSpeed
@pinchies so you need to use @R_J test plugin BEFORE you do the upgrade, and if you see STRING values and not INT values this tells you that your MySQL database suffers from the same issues which many of us are having and a well know issue it seem sin the PHP MySQL community. Because you are seeing STRING values returned try doing the changes I suggested. After applying those changes to your server run @R_Jsi test again to see if now you see INT values. If you do see INT values then you have corrected your PHP/MySQL server and you can do the upgrade but if you see STRING values then is no point in doing the upgrade as the newer code is written it seems with more strict comparator operators and where the old form was using less string comparators
Hence why in the newer more strict code when testing if a user has access to a Category using STRING values returned form the database it would return FALSE even though using less strict code would return true. Writing stricter code produces more of a desired result but you must ensure all your values are type cast properly. So three choices are:
The solution I think here is that the developers could not have possibly known that there is this weird issue with PHP MySQL PDO on older version of MySQL that don't return the value stored in the database as its type cast. If its stored in a table that is set to TINYINT(4) you would assume the value returned form the database should be an it when you retrieve it! Maybe the developers can put a check in the code to let users know of this issue on setup that tests storing an INT value in the database and retrieving and testing its type and if its a STRING then force them to fix this before installing.
Thank you for explaining again in depth. However, I must reassure, I really did and do understand the issue -- however, even when I have changed the enabled PHP Extensions (as shown below), the same output is obtained with all strings from RJ's plugin.
@pinchies PM me if you are still seeing STRING values and maybe we can do a Google Meet and I can see your environment and help
@pinchies Sorry the explanation was more of a for anyone else. but was a good chance to explain it. Your settings look correct it might be the MySQLi can you also uncheck mysqli and check nd_mysqli and just test?
I did try that, but unfortunately still strings ....
Sorry lets do one test maybe @R_Js plugin is not working 100% (I assumed it was) if you put this code in a test.php file on your server and fill in the blanks from your conf.php file see what this outputs This will dump the row for the admin user and specify the type of the values.
If you are using .htaccess you might have to alter it to allow this script to run I added this one line to make sure I can hit the test.php
.htaccess
#### # Prevent access to any php script by redirecting the request to /index.php # You can add an exception by adding another RewriteCond after this one. # Example: RewriteCond %{REQUEST_URI} !^/yourscriptname.php$ # You can comment out this section if it causes you problems. # This is just a nice to have for security purposes. #### RewriteCond %{REQUEST_URI} !/index.php$ RewriteCond %{REQUEST_URI} !^/test.php$ RewriteRule (.+\.php) [E=X_REWRITE:1,E=X_PATH_INFO:/$1,L]test.php
<?php function OpenDatabase(){ $Var = [ 'DBHost' => 'localhost', 'DBUser' => '', 'DBPassword' => '', 'DBName' => '', ]; return new PDO("mysql:host={$Var['DBHost']};dbname={$Var['DBName']};",$Var['DBUser'],$Var['DBPassword'],array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)); } $dbh = OpenDatabase(); foreach($dbh->query("SELECT * FROM GDN_Permission WHERE PermissionID = 1") as $Row) {$permissions[] = $Row;} var_dump($permissions); exit; ?>@R_J I'm not sure what's up with your plugin but when I test before and after my changes your plugin still says STRING values. On my test.php posted above it shows STRING before the update then INT values after the change to the MySQLnd driver.
Any ideas why your Plugin is not returning INT values when mine does?
Maybe http server or MySQL caching? But no, I really do not have an explanation
@MrCaspan -- BINGO -- making the changes you suggested DOES indeed change the output from string() to int()!!
Ahhh.... I should have looked more closely at R_J's plugin code!
@R_J : changing line 9 in your code to:
$conn = new PDO($dsn, $db['User'], $db['Password'],array(PDO::PDO::ATTR_EMULATE_PREPARES => false));
This seems to fix it, as otherwise the PDO is not respecting the result datatype:
mysqlnd (PHP 5.3) will return native data types from prepared statements, provided you turn off the prepared statement emulation from PDO.
from https://stackoverflow.com/questions/1197005/how-to-get-numeric-types-from-mysql-using-pdo
Don't look at that unimportant plugin: what about your forum permissions? ;-)
Do you need to add that option to the config?
I just wanted to confirm, that this means that @MrCaspan 's PHP extensions fix, using the native driver mysqlnd, does fix this int/string type issue. The code responsible for DB connection in the Vanilla package /library/database/class.database.php file already correctly disables the PDO::ATTR_EMULATE_PREPARES option. This means no code changes are required.
Forum permissions now appear to be working! Which means we will have fixed the root cause of the "categories not found" issue.
This also finally seems to mean I can properly use the fix counts function too!!
VERY VERY appreciative to both @MrCaspan and @R_J !
To sum up: if using old MySQL 5.7, the solution is to change the PHP extensions, and switch from the PDO_mysql extension, to the native driver, called nd_pdo_mysql. The correct configuration for MySQL is: