sql is not one of my strong points, could you tell me how i would get that info out please?
i’ve already got the xibo database selected.
Just looking that size of the log file and it’s 5.48gb!
Sounds like you don’t have XTR setup properly then so it’s not clearing old records.
I know it’s not for everyone but this kind of thing is exactly why we recommend using Docker as it isolates one CMS’s dependencies from another, and has all the correct setup for things like XTR and XMR.
That aside, I would truncate that log table first to empty it. Assuming you have MySQL setup with file per table, then you can also free up that 5GB of disk space at the same time.
truncate `log`;
optimize table `log`;
Now refresh the page in the browser to trigger that error again, and then you can get the messages logged by running
select * from log;so i ran the two commands:
mysql> update `setting` set value="Test" where `setting`="SERVER_MODE";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
optimize table `log`;
+----------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------+----------+----------+-------------------------------------------------------------------+
| xibo.log | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| xibo.log | optimize | status | OK |
+----------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.36 sec)
The file size dropped to 112kb. I closed the page and re-opened it on two machines:
mysql> select * FROM log;
Empty set (0.00 sec)
I even restarted the IIS site, and tried setting it back to “production” mode too. still an empty set.
You could try putting the CMS in debug mode too
update `setting` set value="DEBUG" where setting="audit";
to turn it back off
update `setting` set value="emergency" where setting="audit";Tried setting it to debug, but I still have an empty log.
Just going to restart the web server and see if a reboot adds anything to the log.
Given your earlier issues, are you sure that both systems aren’t pointing at a common database? If the CMS is in debug and test, loading any page should spit out several lines of log.
100% sure,
I cleared the log on xibo2 before the reboot to make sure they were both empty, restarted the server, and xibo2 has entries, but xibo doesn’t.
I have last nights backup, so i can restore the database files from yesterday, is that just a matter of stopping the mysql service, putting the files back and then restarting it?
That doesn’t prove they’re connected to separate databases though? It just proves only one is being written to.
If you look on the disk of the server at the settings.php file which you’ll find in the web directory of each install, that will tell you what database they’re configured to connect to.
You can’t restore MySQL like that unless the database server was stopped when you backed up those files. They all need to be from the same moment in time, or from when the server was stopped to be a good backup. What you generally do is take a backup with mysqldump which gives you a consistent backup of the database. You can restore from a mysqldump file easily. Again, the Docker install spits out a mysqldump backup for you once a day.
Our backup software takes a snapshot (these are HyperV servers and the backups should be app consistent.)
In the settings.php within the web folder i have the following:
$dbname = ‘Xibo’; - xibo.mydomain
$dbname = ‘xibo2’; - xibo2.mydomain
In that case I’m out of ideas I’m afraid.
You’d expect to be getting stuff written to that log table regardless of anything else.
If your backup files are point in time consistent, then yes you could restore it as you suggest. You’ll loose your new install’s database though, and MySQL will need to do crash recovery (which is normally fine but there’s a chance it won’t be). It really would be a good idea to automate a mysqldump overnight each night so you have a definitely valid backup each day.
I’ll certainly look into automating a mysqldump, that makes alot of sense.
I’m just restoring the files from \Programdata\MySQL\MySQL Server 5.7\Data\Xibo now
I assumed i could leave the \Programdata\MySQL\MySQL Server 5.7\Data\Xibo2 files as they are?
Do i just run
php vendor\bin\phinx migrate -c phinx.php
command again?
You can’t just restore part of it. You’ll need to restore the whole MySQL data directory - and that will mean losing your new database.
In theory yes you can just run the migration, but given your earlier issues, I would first upgrade to 1.8.13, and then upgrade to 2.3.1.
1.8.13 doesn’t use migrations so it’s a different upgrade process.
I’m happy to restore the entire folder. there is nothing in the new system anyway. it was just for testing.
I’ll download 1.8.13 and then go from there.
Thank you for your help.
1 Like
Once I have restore the files and start the mysql service, is that a way of knowing if the service is doing anything (like recovering?)
It will log its progress in your MySQL log file. Assuming it stays running then it’s generally OK.
I’ve restored the files, and mysql started up with no errors. I opened xibo.mydomain and was greeted with the 1.7.9. login screen. I logged in, and was directed to /upgrade.
From there i got the error:
Fatal Error - sorry this shouldn't happen. SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'value' cannot be null
1.7.9? I thought you were running version 1.8 before?
I thought i was too.
I switched the server to test ang got the following:
Fatal Error - sorry this shouldn't happen. PDOStatement::execute(): MySQL server has gone away
**Fatal error** : Uncaught ErrorException: Error while sending QUERY packet. PID=556 in C:\inetpub\wwwroot\Xibo\lib\Storage\PdoStorageService.php:235 Stack trace: #0 [internal function]: Slim\Slim::handleErrors(2, 'Error while sen...', 'C:\\inetpub\\wwwr...', 235, Array) #1 C:\inetpub\wwwroot\Xibo\lib\Storage\PdoStorageService.php(235): PDO->beginTransaction() #2 C:\inetpub\wwwroot\Xibo\lib\Helper\Session.php(460): Xibo\Storage\PdoStorageService->update('\n UP...', Array) #3 C:\inetpub\wwwroot\Xibo\lib\Helper\Session.php(237): Xibo\Helper\Session->updateSession('irreep2mll5o0t8...', 'init|s:1:"1";sl...', 1583445961, 1583447401) #4 [internal function]: Xibo\Helper\Session->write('irreep2mll5o0t8...', 'init|s:1:"1";sl...') #5 [internal function]: session_write_close() #6 {main} thrown in **C:\inetpub\wwwroot\Xibo\lib\Storage\PdoStorageService.php** on line **235**1.7 definitely won’t work on PHP 7.2, so other things are being changed here than what you’ve mentioned in your previous messages. 1.7 also has a very different folder structure so your setup in IIS would be very different for it.
I was trying to fault find, so i guess i changed the php version without remembering.
Is there a way i can dump the database from 1.7 and import it into a new install of 2.3?