[Track Metadata] Track storage slowing down website and causing Database Connection warnings

[disphor1a]

Describe the bug A clear and concise description of what the bug is. Radio Station Pro is writing very large lines to the database, which is causing the server to slow down. When navigating from page to page, or saving edits, a warning page appears saying:

"DATABASE CONNECTION LOST" "502 Bad Gateway2 "There has been a critical error on this website"

If I refresh the page several times, it eventually reappears, but this is making the site almost unusable to visitors and editors.

To Reproduce Steps to reproduce the behavior:

1. Go to '...'
2. Click on 'any link to navigate'
3. See error: "DATABASE CONNECTION LOST"

Expected behavior A clear and concise description of what you expected to happen. This problem seemed to start after the last big update of Radio Station Pro. Normally the site was running fine.

Screenshots If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: [windows 10]
• Browser [e.g. chrome]
• although this problem is experienced by other editors and users, so it's not specific to any particular browser or OS

Additional context Add any other context about the problem here.

My colleague has been looking at the logs and said the following:

“I've pulled down the wp_options table, and there's a few things that seem to be writing an immense amount of data to that table

ast-block-templates radio_player_stream_data - 15MB

So you should check if you need whatever AST Block Templates is, and the Radio Player function is completely broken and creating all the slow downs.”

He also said:

"It looks like 107GB of space was being used by a log file that hasn't been cleared in over a month. I've emptied it down and visited the site to try to reproduce the slowness and I can see already within seconds it's climbed back up to 100MB.

There are only 2 queries in there total, which means these are 2 huge queries. They seem to be from the options table in the database, so it's not a plugin specific table. It's not like your options table is particularly large. I can't even really get the whole query to render on my screen, but it looks like it's setting an option value for some sort of song, which is causing the large usage:

Here's a very small snippet of the query - it's just a random part at the start, so it does not contain the full thing but perhaps this gives you an idea:

UPDATE wp_options SET option_value = 'a:129072:{i:0;a:7:{s:6:\"stream\";s:42:\"http://radio.canstream.co.uk:8037/live.mp3\";s:3:\"url\";s:50:\"https://radio.canstream.co.uk:9037/status-json.xsl\";s:11:\"currentsong\";a:3:{s:4:\"text\";s:0:\"\";s:5:\"title\";s:0:\"\";s:6:\"artist\";s:0:\"\";}s:9:\"listeners\";a:3:{s:7:\"current\";i:20;s:6:\"unique\";i:0;s:5:\"total\";i:20;}s:4:\"meta\";N;s:4:\"type\";s:7:\"icecast\";s:9:\"timestamp\";i:1653571052;}i:1;a:7:{s:6:\"stream\";s:42:\"http://radio.canstream.co.uk:8037/live.mp3\";s:3:\"url\";s:50:\"https://radio.canstream.co.uk:9037/status-json.xsl\";s:11:\"currentsong\";a:3:{s:4:\"text\";s:0:\"\";s:5:\"title\";s:0:\"\";s:6:\"artist\";s:0:\"\";}s:9:\"listeners\";a:3:{s:7:\"current\";i:20;s:6:\"unique\";i:0;s:5:\"total\";i:20;}s:4:\"meta\";N;s:4:\"type\";s:7:\"icecast\";s:9:\"timestamp\";i:1653571065;}i:2;a:7:{s:6:\"stream\";s:42:\"http://radio.canstream.co.uk:8037/live.mp3\";s:3:\"url\";s:50:\"https://radio.canstream.co.uk:9037/status-json.xsl\";s:11:\"currentsong\";a:3:{s:4:\"text\";s:0:\"\";s:5:\"title\";s:0:\"\";s:6:\"artist\";s:0:\"\";}s:9:\"listeners\";a:3:{s:7:\"current\";i:20;s:6:\"unique\";i:0;s:5:\"total\";i:20;}s:4:\"meta\";N;s:4:\"type\";s:7:\"icecast\";s:9:\"timestamp\";i:1653571071;}i:3;a:7:{s:6:\"stream\";s:42:\"http://radio.canstream.co.uk:8037/live.mp3\";s:3:\"url\";s:50:\"https://radio.canstream.co.uk:9037/status-json.xsl\";s:11:\"currentsong\";a:3:{s:4:\"text\";s:0:\"\";s:5:\"title\";s:0:\"\";s:6:\"artist\";s:0:\"\";}s:9:\"listeners\";a:3:{s:7:\"current\";i:20;s:6:\"unique\";i:0;s:5:\"total\";i:20;}s:4:\"meta\";N;s:4:\"type\";s:7:\"icecast\";s:9:\"timestamp\";i:1653571082;}i:4;a:7:{s:6:\"stream\";s:42:\"http://radio.canstream.co.uk:8037/live.mp3\";s:3:\"url\";s:50:\"https://radio.canstream.co.uk:9037/status-json.xsl\";s:11:\"currentsong\";a:3:{s:4:\"text\";s:0:\"\";s:5:\"title\";s:0:\"\";s:6:\"artist\";s:0:\"\";}s:9:\"listeners\";a:3:{s:7:\"current\";i:20;s:6:\"unique\";i:0;s:5:\"total\";i:20;}s:4:\"meta\";N;s ujima-error-logs.zip :4:\"type\";s:7:\"icecast\";s:9:\"timestamp\";i:1653571088;}i:5;a:7:{s:6:\"stream\";s:42:\"http://radio.canstream.co.uk:8037/live.mp3\";s:3:\"url\";s:50:\"https://radio.canstream.co.uk:9037/status-json.xsl\";s:11:\"currentsong\";a:3:{s:4:\"text\";s:0:\"\";s:5:\"title\";s:0:\"\";s:6:\"artist\";s:0:\"\";} Based on this it looks radio related, so I checked the plugin list and found these plugins:

radio-station radio-station-pro

I noticed immediately upon turning them off that the PHP memory limit was no longer being hit, so I think it's a fair assessment that the slowness and disk space usage is coming from those plugins."

I have attached a recent error log file, which seems to show Radio Station Pro is writing very large files to the database.

The plugin is core to the radio station website that I'm managing, so not using your plugin isn't an option, especially since there don't appear to be any other plugins that have similar functionality. I have also paid for the Pro version.

I've tried everything including long discussions with my hosting company 34SP.com who have tried all they can to fix the problem, so i'm writing to you in the hope that you can fix this problem. The radio station website is an essential part of Ujima Radio, so I consider this to be an urgent problem.

Thanks

Mike

[nickpiggott]

To add a little more detail to this bug.

In the table wp_options, a row with the option_name "radio_player_stream_data" is being created with an option_value of a JSON array (as seen in the log file except above) that grows to be about 48MB in size. I am assuming that SQL is then choking off the field size. The option_id value seems to differ - in the case of the DB I was looking at it was 10141.

I've manually deleted that row from the table, and the performance has apparently improved dramatically.

So something is creating a row with option_name "radio_player_stream_data" and with an excessively large option_value field.

[disphor1a]

From: Nick Piggott @.> Sent: Saturday, September 2, 2023 12:13 PM To: netmix/radio-station @.> Cc: disphor1a @.>; Author @.> Subject: Re: [netmix/radio-station] Radio Station Pro is slowing down the radio website and causing Database Connection warning pages (Issue #469)

To add a little more detail to this bug.

In the table wp_options, a row with the option_name "radio_player_stream_data" is being created with an option_value of a JSON array (as seen in the log file except above) that grows to be about 48MB in size. I am assuming that SQL is then choking off the field size. The option_id value seems to differ - in the case of the DB I was looking at it was 10141.

I've manually deleted that row from the table, and the performance has apparently improved dramatically.

So something is creating a row with option_name "radio_player_stream_data" and with an excessively large option_value field.

— Reply to this email directly, view it on GitHub https://github.com/netmix/radio-station/issues/469#issuecomment-1703804037 , or unsubscribe https://github.com/notifications/unsubscribe-auth/BCIT6OSREOWIWVMEMNNROEDXYMIFHANCNFSM6AAAAAA4GKOVFY . You are receiving this because you authored the thread. https://github.com/notifications/beacon/BCIT6OXBYSGJDJSBTG2QDATXYMIFHA5CNFSM6AAAAAA4GKOVF2WGG33NNVSW45C7OR4XAZNMJFZXG5LFINXW23LFNZ2KUY3PNVWWK3TUL5UWJTTFRX6IK.gif Message ID: @. @.> >

[majick777]

@nickpiggott @disphor1a

I've done some revamping of this feature in the Pro code and will release a Beta update containing this shortly.

In the meanwhile, you might want to do something like this temporarily (say in a .php file in your /wp-content/mu-plugins/ directory to keep the cached data clear and stop it from building up in your database:

<?php
add_action( 'init', 'clear_radio_station_stream_metadata' );
function clear_radio_station_stream_metadata() {
    delete_option( 'radio_player_stream_data' );
}

[majick777]

@nickpiggott @disphor1a

Please test the latest Pro Beta version (2.6.4.1) which fixes this problem. To install see https://radiostation.pro/docs/FAQ/#development-versions

The intention of this metadata storage feature was two-fold. First as a cache to reduce the number of outgoing metadata requests needed to the streaming server. And second as a storage for a potential "latest tracks played" shortcode. These have now been split and improved upon separately as the combined implementation was causing the excessive storage in one option field as reported.

In the first case, while track records older than an hour were removed from the options table anyway, there were flaws in the code preventing duplicate records. So this was the first thing fixed, with a hard record limit added to this cache to be safe. Additionally, timestamped entries are now stored under stream keys to support multiple streams.

And in the second case, a separate custom table has been added to store track metadata in the database for the longer term, with an option added to the plugin settings to enable/disable this feature. This sets the underlying data structure so that in future latest tracks played for a stream can be displayed.

[tonyzeoli]

@nickpiggott @disphor1a Thanks for reporting this issue. We're certainly glad you found it as we're not streaming at this point ourselves, so we rely on our users to report issues like this one, which wouldn't be easy to see happening unless we were hosting our own shows and streaming ourselves - maybe that's something we need to work on.

If you are using the PRO version, which I believe you are from the earlier post, please note that PRO support is generally at support@netmix.com and not through our free version of Radio Station's GitHub repo. While it is definitely helpful to post it here because if you're a developer, maybe that's just something you usually do, we try to support PRO users by email.

It's certainly useful to have this here so others find the issue in a Google search instead of having it be obfuscated by private communication.

I did notice on the site here: https://ujimaradio.com/ujima-radio-weekly-schedule/, that the Show descriptions are not centered and kind of push to the left. You might want to check the other schedule options on our demo.radiostation.pro site if you're not already aware.

@majick777 question: will the forthcoming fix dump the cache of other users who don't know about this issue?

[majick777]

@tonyzeoli Since the cache storage wasn't be used yet anyway, I changed the options key for it anyway so that a new cache would be started with the correct data format going forward, rather than trying to reformat existing data.

In the newer version, both caches can be cleared by going to /?clear-stream-metadata=1... the option key radio_player_stream_data can be safely deleted by this or the method above as it's no longer used.

[tonyzeoli]

@majick777 Sounds good. T?hanks for catching up with all of this!

[majick777]

Closing as resolved in Pro beta 2.6.4.1