Database issues after auto-upgrade to Matomo 4.0.0

Support & Bugs
1

Since yesterday, my archiving cronjob sends me errors over email:

  • Four times, starting at 05:00u about every hour: Base table or view not found: 1146 Table ‘deb45029n2_piwi1.piwi_archive_invalidations’ doesn’t exist
  • Followed by an email that started at 09:16 until 16u and then again the next day: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘log_visit.config_director’ in ‘field list’ - in plugin DevicePlugins

Before logging in to see what’s going on, Matomo told me the database needed an upgrade. This looked normal.

After logging in though, the interface shows:

Schermafbeelding 2020-11-26 om 12.29.40
Schermafbeelding 2020-11-26 om 12.29.40802×1094 80.6 KB

Note I did not upgrade Matomo by hand. It updates automatically.

I found some issues related to missing columns on GitHub but they’re all not very recent. Any idea what’s going on and how I can fix this? And whether data is missing?

Database as reported by Matomo’s System Check: 10.4.15-MariaDB-log-cll-lve.

After posting this issue I did notice the instructions to downgrade mention the column that’s missing (not sure how that fits in yet though):
https://matomo.org/faq/how-to/how-do-i-downgrade-from-matomo-4-to-matomo-3/

2

Hi @mackaaij

it sounds like no database migrations were executed for some reason. Do you maybe usually upgrade the database using the core:update command? Any chance you can access your server and execute a command in the Matomo directory called ./console core:update ?

After the Matomo files were upgraded do you remember if there was a screen shown similar to this one?

1350×2149 341 KB

3

You might also need to perform the manual three step update as by the sounds some files were somehow not correctly updated. Any chance you can check out https://matomo.org/docs/update/#the-manual-three-step-update ? and perform a manual update (be important to backup config.ini.php as mentioned in the guide)?

4

Thanks @thomas_matomo! ./console core:update returns access denied, I guess I’ll ask my hosting provider why that is.

I do recall a screen asking for a database upgrade, but I don’t recall it listing a lot of information.

So, I need to do both core:update and manually upgrade, right? And then everything should be in order? Is any data lost?

5

Hi @mackaaij

I would definitely start with the manual upgrade and then the update screen might show up again. It’s bit hard to say in general because it looks like Matomo was not fully updated and it’s hard to say what happened.

If the upgrade screen doesn’t show up after manually updating the files, then I suggest in the database (if you have access to it), you run the following query:

update `matomo_option` set option_value = '3.14.0' where option_name = 'version_core'

Depending on your DB prefix you might need to change matomo_option eg with piwik_option.

This should then bring up the Matomo upgrade screen and you wouldn’t need to execute that console command.

6

After running chmod 777 console (it was 644 before) I was able to run ./console core:update which resulted in:

Everything is already up to date. (in green)

Didn’t do much else. I’ll replace the Matomo files (all but config.ini.php) this evening and run the database command to change the version number if required.

7

@thomas_matomo I unzipped matomo.zip locally, sent the files via ftp and made sure to replace config/config.ini.php afterwards. The database version was set to 4.0.0 so I guess that was different from 3.14.0 already so it triggered an update when visiting the site.

This didn’t fix the problem though.

So I changed the version to 3.14.0, thought it might trigger another update routine. The update screen re-appeared but upgrading this way also didn’t fix the errors.

I also tried to change the version to 3.14.0 and run ./console core:update - didn’t help.

What did work was extract matomo.zip into a new folder and add config/config.ini.php. I noticed three days of visitor logs are missing :frowning: But at least the errors are gone so this is my current state.

Anything I can do to fill the gap? Maybe something with server logs?

Also, what is stored in files instead of the database? So I can copy these files in manually?

8

So I understand things are working now ? Great you managed to get it working!

Yes you could reply server logs to import the data from the previous day see https://github.com/matomo-org/matomo-log-analytics/

You could import the data like this:

python /path/to/matomo/misc/log-analytics/import_logs.py --url=http://mysite/matomo/ --idsite=1234 --recorders=4  access.log

In the Visitors -> Visits log report you could check when the reports stopped and started again and use this information to append extra parameters to only reply the missing gaps

--exclude-older-than="2020-11-27 13:14:15" --exclude-newer-than="2020-11-30 01:00:00"

The dates are just an example.

If the access log files include the requests to matomo.php or piwik.php (meaning you replay the logs from your Matomo instance) then you should append the parameter --replay-tracking as then the tracked data will be even more accurate as the original tracking requests will be replayed instead of just the data from your website logs.

Hope this helps!

1 Like
9

I’m getting the exact same error. (found this thread by doing a web search on the error text). Piwik error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'log_visit.config_director' in 'field list' - in plugin DevicePlugins.

I did a manual upgrade (using a script I created based on the Matomo docs) and ran the database upgrade as well. (It took quite a long time to run, but eventually finished without any errors).

Now, however, when I log in to view my stats I’m seeing red errors all over the place.

image
image740×559 27.5 KB

I’d rather not re-install Matomo, is there a better option than this?

10

I’m always happy to read my threads are found :slight_smile:

Based on my experience in this thread, I’d go with the re-install asap to close the gap in your stats.

I didn’t get around to trying to import/replay logfiles after the re-install yet. But I’ll update this thread with my experience when I’m done.

11

@577895 it seems the files were not updated correctly. Can you check the system report (link is in the left menu in the Administration area) whether some files can be maybe deleted? Maybe you only uploaded new files but did not remove the old ones or so when you did the manual update?

1 Like
12

You’re right, there’s a big long list of files/directories to delete. What is the recommended way to deal with this issue if there are files that need removed during an upgrade? Do I have to remember to check the System report each time, or is there an automated way to do this?

13

I went through and deleted all the files/folders listed for deletion in the System Report, and everything works again!! Thanks so much!

1 Like
14

Glad everything works now @577895

Regarding your other question: When you are using the one click UI update then Matomo usually deletes these files automatically for you.

If you are doing it manually by uploading the files from the extracted zip file then yes, you might be best off to always look at the system report. So far I’m not aware of a better way. You could technically backup config/config.ini.php, then remove all files in your Matomo, then upload all the new files, then copy over your config file again. But then it will remove any extra plugins you are maybe using.

15

Thanks, I’m glad too! :slight_smile:

The reason I use the script w/ the manual update is because the one-click UI update did not seem very reliable; it used to take SO LONG to run that it eventually timed out (but usually did eventually do the trick). I wanted something that I could run and feel reasonably confident that it was working properly.

I wonder if updating my script to backup the config.ini.php file, delete the contents of the directory, and then unzip the new files into it’s place would do the trick. I don’t use any extra plugins that don’t come bundled with Matomo…

16

That should work, but keep in mind that there are a few files in misc/ that you should also preserve. (Geolocation databases, your own logo if you uploaded it)

17

Hi all.

I would share with you my experience about this issue.

4 days ago I upgraded my Matomo installation to 4.0.4 version from 3.14.0 partially manual: I unzipped the sources directly into my server and then I upgraded the DB from Administration UI. This cause the problem reported by @mackaaij.

I solved the problem with:

  1. copy the config.ini.php for backup
  2. delete all directory of matomo sources
  3. unzip the latest matomo release (4.0.5)
  4. copy the config.ini.php into ./matomo/config/.
  5. run in the console the command ‘./console core:update’ (no error in db upgrade reported)

This solve my problem.

Bye,
Alex.

18

@thomas_matomo I finally got around to importing the logs. Good thing though, as my provider only seems to keep last months logs. Visitors > Visits Log now shows logs for each site.

In my case, the access logs were zipped, approximately per day with an extension .tar.gz.x.

As Python 3 was not available at my web host, I cloned https://github.com/matomo-org/matomo-log-analytics/ locally and executed it using the auth parameter --token-auth=

python3 ./matomo-log-analytics/import_logs.py --url=https://www.eenmanierom.nl/statistics/ --idsite=[ID] --recorders=1 --replay-tracking --token-auth=[TOKEN] [PATH TO LOCALLY DOWNLOADED ACCESS LOG]

I could use --replay-tracking for one site only (as far as I could figure out) as they share the Matomo URL (so matomo.php is called at one domain).

For the other sites, this seems to have led to more “hits” which wouldn’t have been recorder otherwise, probably bots. But hey, it’s better than no stats.

Note I forgot to specify a date range to import, but I guess the importer prevents adding duplicates.

The graph in the widget “Visits over time” doesn’t show the imported record visitor logs so I think I still have to do something else.

The importer summarizes the following command which is probably aimed at Matomo 3 as it returns The "--force-all-periods" option does not exist.:

./console core:archive --force-all-websites --force-all-periods=315576000 --force-date-last-n=1000 --url='[URL]'

So I tried to run it with --force-date-range="YYYY-MM-DD,YYYY-MM-DD", which did process archives. But the widget is still flat on days where Matomo was down due to the upgrade failure.

Any idea how I can get the visitor logs processed so everything is in order?

19

Hi @mackaaij if you see the data in the Visits Log then generally the data is there and it only seems to be a report generation issue. You may need to invalidate the reports for the dates that aren’t showing any data see https://matomo.org/faq/how-to/faq_155/

Usually, log analytics and Matomo does this automatically but hard to say why that’s not the case. Depending on how your archive cronjob is configured it might come up a bit later but seems that’s not the case.

Note I forgot to specify a date range to import, but I guess the importer prevents adding duplicates.

Unfortunately it doesn’t. We can’t really detect what’s been imported already and what not :frowning: If your access log files only have data for a single day then this might be less of an issue but if it stores like an entire month then you might have a few duplicates now.

20

I did try to invalidate the reports earlier using that command but it didn’t seem to work. As for some sites the gap is closed, I invalidated the reports for the sites still showing a gap. I trust this will work out fine.

Fortunately, there’s an access log for about a day. And the gap starts at night and ends in the evening a few days later. I tried to spot duplicates but didn’t notice any after the import. If I take another look and fine some, (how) can I remove these imported visits?