Instructions on joining the Members Only Forum
Search the Community
Showing results for tags 'mysql'.
Found 1 result
Ok, So… In other threads I mentioned upgrading PattayaTalk.com to the latest version of Invision Power Board (That’s our forum software). The first step in upgrading such a large site is to set up a test server and clone the site over there, then upgrade the test server completely and fix any issues that arise (recording everything so you can reproduce it easily), and then once its all done and your happy with it, upgrade the live site and fix the same issues hopefully quickly since you have the notes and files from your last attempt. Some of the issues so far I have found interesting, and some of them quite challenging - so I figured I would share in the public area, so that anyone else going down this road may learn a few tricks (searchable via Google) Martin worked on this upgrade for several weeks a while back and eventually gave up. At the time he preferred to do this himself and did not consult me on the technical issues, so the info I got was informal - discussed over beer (or likely in a gogo bar), but I remember him telling me that both the reputation system and the calendar were broken beyond repair after attempting the upgrade, which is the reason the board is still on a several year old version. Another issue was the skin, but again at the time I seem to remember him telling me we could get around that issue. This website is hosted on a Linux server. My experience with Linux until this pretty much consisted of a lot of laughing and eye rolling. As a lifelong enterprise windows developer I have always held a bit of contempt for Linux… not because its useless, but rather because in my view it is way too limiting to be taken seriously in the “Enterprise” world. As I’ve gotten older that belief has softened, but not totally disappeared. I still believe it to be way too limiting for Enterprise use, except in a very narrow area, but I do also realize that there are things Linux does better than windows. One of those things is serving up static websites. The forum is not a static website, obviously, but it is written in PHP, which is another thing Linux does better than Windows - or if not better, than at least with less hassle and overhead. If I were starting this community up from the start I would be tempted to use a .NET based solution instead, so I could customize it to my hearts content using the skills I have honed over a lifetime of professional practice. But, since it already exists - I decided it is time to set my prejudice aside and learn how to properly administer Linux/Apache so that I can properly handle things going forward. So begins this journey to upgrade pattayatalk.com :) So, Martin told me about vague problems with Skins, Reputation System and Calendar. If he had told me the specifics of the problems, we may have been upgraded years ago. But he didn’t, so I had to learn all this myself. So, I got a new server set up with CentOS 7, and since I am a linux newbie, it took me a few days (a hour here and there) to get that installed, configured and working properly. I eventually learned all the “gotchas” of installing linux, mysql, apache and php. Turns out that, like windows, you can’t just pick those from the installer for the OS to pre-install and expect them to work. I ended up having to re-install CentOS 4 different times due to various conflicts, and this last time (which seems to have done the trick), I opted for the “minimal install” with no features added automatically - and then I installed everything I needed manually. The main reason for this was PHP. Picking that from the start installs a old version… 5.4? I forget the exact version, but its 5*. That works for our forum as it is now, but the upgrade needs version 7 - and removing 5.4 and installing 7 caused so many errors that after several hours of trying to fix it, I just reverted and tried again. Lesson: Install minimal and load the versions of each program you need one at a time. I am skipping over all the lessons learned in getting to that point - simply because most linux people will have already learned the same things (like how to use Nano, Sudo, what a wheel is and why you need to plaster one on the bottom of your user account to get around (lol)), locking out root, and what to do when your trusty internet instructions don’t work. (Nano is not installed by default - and installing it requires editing a text file. Which requires Nano. According to one article anyway - another taught me about vi ) All told so far it has been very simple technically, but it is still a new language and while my knowledge of how things work allows a great deal of insight, how things are MADE to work is not always intuitive, and sometimes google is a bit slow in giving up the gold. One other area I will touch on is permissions. Permissions in linux a bit weird from a windows perspective. And SELinux (Security Enhanced Linux) is a right pain in the ass. In fact (and all you linux heads keep ur traps shut on this one for now), I ended up just turning off Selinux for the time being on this test server as it was causing way too many issues that just magically went away once I turned it off. I do realize that will not work for us in the long run, but it will for now on this test server so I can get the important parts of the upgrade done. On our live site, I take the time to learn it properly. In fact my favorite go-to training site (https://www.pluralsight.com/) has a course on SELinux that I plan on watching over next weekend, after which ill turn it back on here on the test server and get it configured properly. If any of you are techies and need to keep sharp on areas of technology you are not fully up to date on (and anyone that says there are no such areas is a liar lol), Plural Sight is great. It does cost $300 a year, but its value is far beyond that. I tend to turn on the odd video when I’m working on something else… and while my absorption rate isnt even close to 100%, I do pick up enough to be competent even in areas I have never touched. Well worth the expense for anyone actively working in the software world. You would think since I do use it so often I would have thought to watch a linux basics course of which it turns out they have several. Doh. (Including one called “Deploying a Linux Web Server for Windows Admins”). Double Doh. Well, I did not watch that… but guess I need to now, just to see how stupid I was and how many hours I wasted on google. But then again, I like many other techies before me tend to learn more and faster by hands on, not watching videos. So I don’t consider it a waste of time - just a Doh moment. :) - I also noticed they have one titled “CompTIA Linux+”... I think I see yet another certification in my future, just for the hell of it (if they test that one in bangkok that is - not sure if they do or not). So… at this point we have the forum backup (files) and database installed and working on the test server (able to log in, see posts and admin and nothing obviously broken). I logged in as a standard user, posted a test post, then moderated it, deleted it and the user (as admin), and everything was working - so I made another checkpoint and downloaded the latest forum software and followed the instructions for uploading the proper files to the server and initiating the upgrade process. Now, while I touched on this earlier I will do so again here real fast. The initial screen for the upgrade told me to create a folder named /var/www/html/forums/datastore and make it writable. I found the folder, it already existed, so I checked its permissions and it was already writable by “Apache”, which should have worked. After well over a hour of trying to fix that, I finally gave up and set it to 777 (writable and executable by all, which is a bad thing!) thinking that would at least get things moving… wrong. Still no go. Turns out this was some vague setting inside SELinux. I set SELinux to permissive mode (temporarily), and whoa and behold problem (that problem anyway) solved. At that point I had done so much crap with the folders and files trying to figure out what permissions were hosed (including replacing the owner/group and all permissions on all nested files (recursive) on the whole damn folder), that I decided it would be better to revert back to my last checkpoint so as to make sure permissions were not messed up anywhere. So I did that, and my first course of action was to disable SELinux (lol). That got me to the login screen for the upgrade process. After logging in (admin account), I was immediately hit with a huge wall of red. Error messages galore. Over 300 of them. But… after looking a bit closer I found that all but a handful were related to IP.Gallery, which was still installed and as far as I could see, enabled - but not actively used anywhere, and checking our account online we had not paid for it for a couple years and we did not have a current license. After consulting with Invision, I reverted back to the last checkpoint, turned off SELinux again, then logged in to (the old) admin and removed the Gallery app from IPB. I then uploaded the latest IPB files again, and took another checkpoint before starting the upgrade process again. This time around there were only a handful of issues… but one of them will be a PIA. Most of them were permissions issues - certain folders (about a dozen, maybe 10) that needed to be writable and were not. Easily fixed. One however, was PHP. We had version 5.6 or 5.4 or something like that (I forget the exact version, but it was 5*), and the minimum version for the new upgraded IPB needed 6.something at a minimum, and 7.0.0 recommended. And since we were upgrading anyway, I figured I would get the latest version - 7.0.28. This version required adding a new repository, which after learning how to do was quite simple. Trying to install it on top of the current version failed however. I had to remove the old version first. I did that… then installed the latest version. All went very smooth… (which should have worried me). From that point on and for the next 4 hours nothing I did would make php pages load in apache. Something was seriously broken. After a few hours I simply gave up trying and this is when I deleted all my existing checkpoints and did a new re-install of CentOS with no features added, adding in everything manually instead. Now, at this point it had been well over a week since starting this project (10 days?), but of course not at all full time - just a hour or 3 per day. On this last attempt getting the install done, configured, updated and ready to try again took well under an hour (I’m getting pretty good at this). Kinda reminds me of that time I found and installed a very old copy of The Bards Tale (my favorite Commodore 64 game from WAY the fuck back in the day), but for some reason it just wasn’t quite the magical experience it was before puberty. Funny how that works. (DOS anyone?) So, after all that I was back at square one. But it was very fast getting back to the upgrade wizard this next time, and setting permissions was even faster than last time (it really is very simple), so there we were at step 2: Database conversion. Turns out that IPB v4.+ needs the database to be based on UTF8, and the old one is not. So it needed to do some fairly extensive updates to the database using a tool they provide for the job. The name of the tool is convertutf8 and the main executable is convertutf8/cli.php (I put that here to be searchable by google). This tool is great, but oh so flawed. The first time I ran it in the browser, and after starting it (which makes no sense) it tells me in text at the bottom that if the process hangs repeatedly you can run this in the command line and gave the process to do so. I ignored that for now, trying to keep things simple. It started running… after about 2 hours it seemed to hang, or at least not move forward, but it was very hard to tell because it only updated its status on each table - and the table it stuck on was the Posts table, which has well over a million rows and I would have expected it to take some time. Eventually I refreshed the page, which resulted in a 500 server error. Back to the front page of the upgrade and it was nice enough to tell me that the upgrade to the database was in process and had finished x of x tables, and click here to continue. That was nice. Clicked there… and it was still on the posts table. 1 hour later I got the bright idea to check the server CPU to see if it was busy. It wasn't. (by the way, found a nice external tool called htop that works great in monitoring that). Turns out it was failing (or at least the CPU was dropping off) just a few seconds in to the update. So… I went to the command line version. Oh so much more informative. The CLI version said that a upgrade was in process and did I want to continue or revert and start over. I opted to start over. This time it went faster I think - at least it got to the posts table within 15 minutes, and did not die on it at all - it made it to one of the reputation tables, then it died. It died with this error message (putting it here to be google searchable) fatal error: allowed memory size of 134217728 bytes exhausted (tried to allocate 32 bytes) 2 subsequent attempts at restarting this failed at the exact same place. Googling found that this was due to a limitation in PHP settings of 128MB of memory. Well, I have 12GB on the machine, so screw that. Right? I changed the php setting (easy enough to do) to 2048MB, restarted apache… restarted the process… reverted conversion and started from begining (just to make sure)... and poof. Failed with the exact same fucking error message. I confirmed that the change I made to the php settings took (phpinfo anyone?), and spent the next 2 hours trying different things - setting memory_limit to -1, to 4GB and then 10GB… nothing worked. It all failed with the exact same error message, implying that it had a cap of 128MB. So I downloaded a copy of /convertutf8/ and all its various files, and scanned them (text search) to find if it was somehow or somewhere setting the memory value to 128MB manually for the script. Nothing found. So I got to thinking… it must be setting that value somewhere or somehow. So I ran that script again, with verbose logging and let it fail. This time it failed and was nice enough to tell me the exact file and line number the failure was happening on: /var/www/html/forums/admin/convertutf8/system/Db/Db.php line 368 Again, I posted that to make it searchable here. So, looking at that method inside the php file, there were a shitload of for and foreach loops in the method. The methed itself was called “public function preparedQuery( $query, array $_binds )” - so I tried a little “hack” - I added this line to the very begining of that method: ini_set(‘memory_limit’, ‘-1’); That, in theory, should allow the proceeding code to use as much memory as it wanted up to the amount installed on the server. Now I am not a php developer, so I was not very confident, but it turns out that did the trick. Next run the program completed 100% without any error. I am hopeful that anyone having the same issue will find this here as the solution is not to be found on google (well, it is now!). So, after that I returned to the upgrade wizard and was hit with a new error. It was a file missing or corrupt error on: /var/www/html/forums/admin/convertutf8/system/Db/Db.php Lol. So now it decides to get smart. Bastard. I replaced the file I modified with the original, which made that error go away. Checksum’s suck when your trying to hack shit. Just sayin. Next the upgrade process started going thru a process whereby it made changes to the database tables (remember that last bit was just converting to UTF8). This required some commands to be run in the console to prevent long running queries from stopping (nice, now the damn thing gets smart about that), and so on we go thru about a hour of that (about 30 or so manual queries and a shitton of automatic ones). The next error happened on the ibf_cal_events table. This was not a manual code run thing - it was (or tried to be) automated. The error was “0000-00-00 00:00:00” is not a valid Date for field ‘event_end_date’. When it crashed, it gave me the option to "continue". Rather than do that, since this was one of the places Martin said failed so badly, I investigated instead. Tried the command in the command line, same result. I think the command it was running was: Alter table ibf_cal_events drop index approved Again, for google. So, I loaded up phpmyadmin to take a look at the data in the table. Total of about 4500 rows (not so bad). Sorted the data by event_end_date, and immediately noticed that most of the data either had a valid date/time in that field or it was set to NULL. There were, however, about 200 rows where the value was ‘0000-00-00 00:00:00’ - and funnily enough searching for this value in that field failed, since that value is not a valid date time. So rather than screw around with advanced SQL to detect that value without actually detecting it, I just exported the whole table to excel, sorted by that field, selected all the affected event IDs, and wrote custom sql to update those rows with a event_end_date of NULL. That fixed that problem. At that point I restarted the process again and it continued all the way thru to the end this time. Initial upgrade of the board was complete. Ish. Next I logged in to the new admin interface, and right away I really like the look of it. But the first thing on the screen showed a queue of “background tasks for the upgrade” that are taking place, and will finish when they finish. It gives the option to run those now to get them done faster - which I am doing at the moment. Its been running for the last 4 hours, and is not even close to being done. Just upgrading the posts table if it maintains its current speed will take somewhere in the neighborhood of 15 hours to complete. That said, the process is proceeding smoothly and so far no more errors. So… there it is so far. The successful upgrade to the forum software, at least on the back end. I have not even looked at the forum yet to see whats wrong with it, but based on what I’ve seen in the upgrade process, I can bet my hat that I successfully fixed all of the errors (except the skin) that plagued Martin. The major SQL related errors were related to the Reputation system and and the Calendar. The 2 areas Martin said were irreparably damaged during his run of the scripts. So while I have not yet looked at the forum, I am fairly confident that those major issues are resolved - and I kept my code and all that in a onenote notebook, so when I go to do the upgrade for real, it will go fairly quickly. For now, I am going to wait for these upgrade processes to completely finish before I do anything else - even before I look at the forum. When I do the real upgrade I will let them run in the background, provided I find no issues once they are done, to prevent the upgrade from taking more than a couple hours. From what I understand these processes are making minute changes to the data for posts and such, and the way it works so I understand is if a post that has not been updated in this way is requested by a user via the main website, the update process is done on that post/object just before it is served to the customer - so running the processes over a period of time while the forum is live will not impact anything. Even so, since this is a test I am going to let it run until its finished before I continue my testing. I am going to post this today and update it as soon as I complete the next step in the testing/upgrade procedure.