[Taginfo-dev] Problem running taginfo GB since 2023-10-04
Jochen Topf
jochen at remote.org
Sat Nov 25 14:45:20 UTC 2023
Hi!
i have added your change with the load extension. Looks like your sqlite
setup is slightly different than mine, but that way it should work on
both. Thanks.
> App 48939 output: 2023-11-25 09:04:23 - SQLite3::CantOpenException - unable to open database: file:../../data/taginfo-db.db?mode=ro:
I am not sure when that file: syntax was introduced, or the "mode=row".
Maybe your version is too old for that? Maybe the relative path throws
it off? You can try using the normal file name syntax, so use "PATH"
instead of "file:PATH?mode=ro". The only difference this makes is that
the file is not opened in read-only mode, which should not break
anything. I just added the read-only mode to make sure nobody is
changing the database, because there was a bug in there before where the
wrong db was changed.
Jochen
On Sat, Nov 25, 2023 at 09:13:09AM +0000, Shaun McDonald wrote:
> Date: Sat, 25 Nov 2023 09:13:09 +0000
> From: Shaun McDonald <shaun at shaunmcdonald.me.uk>
> To: Jochen Topf <jochen at remote.org>
> Cc: Shaun McDonald via Taginfo-dev <taginfo-dev at openstreetmap.org>
> Subject: Re: [Taginfo-dev] Problem running taginfo GB since 2023-10-04
>
> Hi Jochen,
>
> I’ve got through the rest of the errors I’ve had. I've found that I’ve needed to make the following change:
>
> diff --git a/web/lib/sql.rb b/web/lib/sql.rb
> index 0d411b9..92ca685 100644
> --- a/web/lib/sql.rb
> +++ b/web/lib/sql.rb
> @@ -13,7 +13,9 @@ module SQL
>
> pcre_extension = taginfo_config.get('paths.sqlite3_pcre_extension')
> if pcre_extension
> + @db.enable_load_extension(true)
> @db.load_extension(pcre_extension)
> + @db.enable_load_extension(false)
> end
>
> @db.execute('PRAGMA journal_mode = OFF’)
>
> I found the solution on https://dev.to/fractaledmind/loading-extensions-4nm3
>
>
> However my next error is:
>
> App 48939 output: 2023-11-25 09:04:23 - SQLite3::CantOpenException - unable to open database: file:../../data/taginfo-db.db?mode=ro:
> App 48939 output: /var/lib/gems/3.0.0/gems/sqlite3-1.6.8-x86_64-linux/lib/sqlite3/resultset.rb:162:in `step'
> App 48939 output: /var/lib/gems/3.0.0/gems/sqlite3-1.6.8-x86_64-linux/lib/sqlite3/resultset.rb:162:in `next_hash'
> App 48939 output: /var/lib/gems/3.0.0/gems/sqlite3-1.6.8-x86_64-linux/lib/sqlite3/resultset.rb:97:in `next'
> App 48939 output: /var/lib/gems/3.0.0/gems/sqlite3-1.6.8-x86_64-linux/lib/sqlite3/resultset.rb:125:in `each'
> App 48939 output: /var/lib/gems/3.0.0/gems/sqlite3-1.6.8-x86_64-linux/lib/sqlite3/database.rb:218:in `to_a'
> App 48939 output: /var/lib/gems/3.0.0/gems/sqlite3-1.6.8-x86_64-linux/lib/sqlite3/database.rb:218:in `block in execute'
> App 48939 output: /var/lib/gems/3.0.0/gems/sqlite3-1.6.8-x86_64-linux/lib/sqlite3/database.rb:169:in `prepare'
> App 48939 output: /var/lib/gems/3.0.0/gems/sqlite3-1.6.8-x86_64-linux/lib/sqlite3/database.rb:209:in `execute'
> App 48939 output: /home/osm/taginfo-gb/taginfo/web/lib/sql.rb:31:in `attach_source'
> App 48939 output: /home/osm/taginfo-gb/taginfo/web/lib/sources.rb:13:in `block in initialize'
> App 48939 output: /home/osm/taginfo-gb/taginfo/web/lib/sources.rb:12:in `each_value'
> App 48939 output: /home/osm/taginfo-gb/taginfo/web/lib/sources.rb:12:in `initialize'
> App 48939 output: /home/osm/taginfo-gb/taginfo/web/taginfo.rb:120:in `new'
> App 48939 output: /home/osm/taginfo-gb/taginfo/web/taginfo.rb:120:in `block in <class:Taginfo>'
> App 48939 output: /var/lib/gems/3.0.0/gems/sinatra-2.2.4/lib/sinatra/base.rb:1697:in `call'
> App 48939 output: /var/lib/gems/3.0.0/gems/sinatra-2.2.4/lib/sinatra/base.rb:1697:in `block in compile!'
> App 48939 output: /var/lib/gems/3.0.0/gems/sinatra-2.2.4/lib/sinatra/base.rb:1078:in `block in process_route'
> App 48939 output: /var/lib/gems/3.0.0/gems/sinatra-2.2.4/lib/sinatra/base.rb:1076:in `catch'
> App 48939 output: /var/lib/gems/3.0.0/gems/sinatra-2.2.4/lib/sinatra/base.rb:1076:in `process_route'
> App 48939 output: /var/lib/gems/3.0.0/gems/sinatra-2.2.4/lib/sinatra/base.rb:1017:in `block in filter!'
> App 48939 output: /var/lib/gems/3.0.0/gems/sinatra-2.2.4/lib/sinatra/base.rb:1016:in `each'
> App 48939 output: /var/lib/gems/3.0.0/gems/sinatra-2.2.4/lib/sinatra/base.rb:1016:in `filter!'
> App 48939 output: /var/lib/gems/3.0.0/gems/sinatra-2.2.4/lib/sinatra/base.rb:1144:in `block in dispatch!'
> App 48939 output: /var/lib/gems/3.0.0/gems/sinatra-2.2.4/lib/sinatra/base.rb:1119:in `block in invoke'
> App 48939 output: /var/lib/gems/3.0.0/gems/sinatra-2.2.4/lib/sinatra/base.rb:1119:in `catch'
> App 48939 output: /var/lib/gems/3.0.0/gems/sinatra-2.2.4/lib/sinatra/base.rb:1119:in `invoke'
> App 48939 output: /var/lib/gems/3.0.0/gems/sinatra-2.2.4/lib/sinatra/base.rb:1142:in `dispatch!'
> App 48939 output: /var/lib/gems/3.0.0/gems/sinatra-2.2.4/lib/sinatra/base.rb:956:in `block in call!'
> App 48939 output: /var/lib/gems/3.0.0/gems/sinatra-2.2.4/lib/sinatra/base.rb:1119:in `block in invoke'
> App 48939 output: /var/lib/gems/3.0.0/gems/sinatra-2.2.4/lib/sinatra/base.rb:1119:in `catch'
> App 48939 output: /var/lib/gems/3.0.0/gems/sinatra-2.2.4/lib/sinatra/base.rb:1119:in `invoke'
> App 48939 output: /var/lib/gems/3.0.0/gems/sinatra-2.2.4/lib/sinatra/base.rb:956:in `call!'
> App 48939 output: /var/lib/gems/3.0.0/gems/sinatra-2.2.4/lib/sinatra/base.rb:945:in `call'
> App 48939 output: /var/lib/gems/3.0.0/gems/rack-contrib-2.4.0/lib/rack/contrib/jsonp.rb:42:in `call'
> App 48939 output: /var/lib/gems/3.0.0/gems/rack-2.2.8/lib/rack/null_logger.rb:11:in `call'
> App 48939 output: /var/lib/gems/3.0.0/gems/rack-2.2.8/lib/rack/head.rb:12:in `call'
> App 48939 output: /var/lib/gems/3.0.0/gems/sinatra-2.2.4/lib/sinatra/base.rb:218:in `call'
> App 48939 output: /var/lib/gems/3.0.0/gems/sinatra-2.2.4/lib/sinatra/base.rb:2004:in `call'
> App 48939 output: /var/lib/gems/3.0.0/gems/sinatra-2.2.4/lib/sinatra/base.rb:1564:in `block in call'
> App 48939 output: /var/lib/gems/3.0.0/gems/sinatra-2.2.4/lib/sinatra/base.rb:1780:in `synchronize'
> App 48939 output: /var/lib/gems/3.0.0/gems/sinatra-2.2.4/lib/sinatra/base.rb:1564:in `call'
> App 48939 output: /usr/lib/ruby/vendor_ruby/phusion_passenger/rack/thread_handler_extension.rb:107:in `process_request'
> App 48939 output: /usr/lib/ruby/vendor_ruby/phusion_passenger/request_handler/thread_handler.rb:149:in `accept_and_process_next_request'
> App 48939 output: /usr/lib/ruby/vendor_ruby/phusion_passenger/request_handler/thread_handler.rb:110:in `main_loop'
> App 48939 output: /usr/lib/ruby/vendor_ruby/phusion_passenger/request_handler.rb:419:in `block (3 levels) in start_threads'
> App 48939 output: /usr/lib/ruby/vendor_ruby/phusion_passenger/utils.rb:113:in `block in create_thread_and_abort_on_exception'
>
> Which I’m rather puzzled by and haven’t figured out the solution to yet. The permissions look ok, so I’m not sure why it’s not working. I’ve tried switching to an exact full path however hitting the same issue. Is there anything obvious I could have forgotten.
>
> Thanks,
> Shaun
>
>
> > On 19 Nov 2023, at 00:33, Shaun McDonald <shaun at shaunmcdonald.me.uk> wrote:
> >
> > It’s fine, I’ve had a busier than usual year with a house move, and now that things are starting to settle down, I’m catching up on the updates like this. Having young kids doesn’t help either, in part as they want to use the computer instead too.
> >
> > The OS upgrade hasn’t gone well, and luckily my backups have been, so I’m now setup on another VM, which is there in terms of building the data. Still need to setup the crontab, nginx side and backups again. So it might be down for another day or few whilst I get that sorts.
> >
> > If I figure out how to do it, I might also shift to hosting at home to reduce the hosting costs now that I’ve got the full fibre connection.
> >
> > Shaun
> >
> >> On 17 Nov 2023, at 08:48, Jochen Topf <jochen at remote.org> wrote:
> >>
> >> Yeah, sorry for that. I should have documented that better and announced
> >> it if I am changing something that might affect backwards compatibility.
> >> I looked at the versions needed when I used UPDATE FROM and thought:
> >> Surely everybody using taginfo has that version by now. Unfortunately I
> >> dont have the time to check taginfo on old versions and there aren't
> >> that many people who run it, anyway.
> >>
> >> So, if you are running taginfo: I kind of assume that you keep up with
> >> changes to taginfo and also to the OS you are running on. I will try not
> >> use the newest stuff so you have some time to upgrade, but you should
> >> check your system at least once a year or so.
> >>
> >> Jochen
> >>
> >> On Fri, Nov 17, 2023 at 08:40:36AM +0000, Shaun McDonald wrote:
> >>> Date: Fri, 17 Nov 2023 08:40:36 +0000
> >>> From: Shaun McDonald <shaun at shaunmcdonald.me.uk>
> >>> To: Jochen Topf <jochen at remote.org>
> >>> CC: Shaun McDonald via Taginfo-dev <taginfo-dev at openstreetmap.org>
> >>> Subject: Re: [Taginfo-dev] Problem running taginfo GB since 2023-10-04
> >>>
> >>> Hi Jochen,
> >>>
> >>> Looks like an OS upgrade will do the trick as it's on an older Ubuntu LTS. I suspected this might be the case.
> >>>
> >>> Thanks
> >>>
> >>> On 17 November 2023 08:25:23 GMT, Jochen Topf <jochen at remote.org> wrote:
> >>>> Hi!
> >>>>
> >>>> it complaines about the FROM in line 83:
> >>>> UPDATE project_unique_keys SET in_wiki = lang_count FROM wiki.wikipages_keys w WHERE project_unique_keys.key = w.key;
> >>>>
> >>>> There were some changes to this area of the code around the date you
> >>>> mentioned, so that fits.
> >>>>
> >>>> Looks like your version of Sqlite doesn't support UPDATE FROM which was
> >>>> introduced in version 3.33.0 more than three years ago. Which version
> >>>> are use using? (The versions in Debian 11 and 12, and Ubuntu 22.04
> >>>> should be okay, but Ubuntu 20.04 is no longer supported.)
> >>>>
> >>>> I will update the README to document the new version needed.
> >>>>
> >>>> Jochen
> >>>>
> >>>> On Fri, Nov 17, 2023 at 08:03:41AM +0000, Shaun McDonald wrote:
> >>>>> Date: Fri, 17 Nov 2023 08:03:41 +0000
> >>>>> From: Shaun McDonald <shaun at shaunmcdonald.me.uk>
> >>>>> To: Shaun McDonald via Taginfo-dev <taginfo-dev at openstreetmap.org>
> >>>>> Subject: [Taginfo-dev] Problem running taginfo GB since 2023-10-04
> >>>>>
> >>>>> Hi,
> >>>>>
> >>>>> I’ve been alerted that there is an issue with tag info GB which has been running quietly in the background for a year or so. Since 2023-10-04 https://taginfo.openstreetmap.org.uk/sources/db the build has been failing. I’ve tried updating the code and all minor OS updates, however I’m still getting the following error:
> >>>>>
> >>>>> [ 5:02] Writing results to database...
> >>>>> [ 5:11] writing results to database took 9 seconds (about 0 minutes)
> >>>>> [ 5:11]
> >>>>> [ 5:11] Estimated memory usage:
> >>>>> [ 5:11] tags_stat: ............... 3471 kB [size=14099 buckets=16383 sizeof(entry)=217]
> >>>>> [ 5:11] key_value_stats: ......... 64 kB [size=738 buckets=1023 sizeof(entry)=65]
> >>>>> [ 5:11] key_value_geodistribution: 195 kB [size=3319 buckets=4095 sizeof(entry)=49]
> >>>>> [ 5:11] relation_type_stats: ..... 2 kB [size=22 sizeof(value_type)=104]
> >>>>> [ 5:11] values: .................. 156559 kB [(sizeof(hash key)(8) + sizeof(hash value)(12) + 2.5 bit overhead) * bucket_count(7868309), size=5080007]
> >>>>> [ 5:11] key_combos: .............. 15279 kB [(sizeof(hash key)(8) + sizeof(hash value)(12) + 2.5 bit overhead) * bucket_count(767911), size=477878]
> >>>>> [ 5:11] users: ................... 115 kB [(sizeof(hash key)(4) + sizeof(hash value)(4) + 2.5 bit overhead) * bucket_count(14099), size=14099]
> >>>>> [ 5:11] string_store: ............ 92160 kB [chunk_size=10240kB chunks=9]
> >>>>> [ 5:11] location_index: .......... 2888673 kB [size=184875094]
> >>>>> [ 5:11] ======================================
> >>>>> [ 5:11] total: ................... 3156522 kB
> >>>>> [ 5:11]
> >>>>> [ 5:11] Actual memory usage:
> >>>>> [ 5:11] current: 4686MB
> >>>>> [ 5:11] peak: 6541MB
> >>>>> [ 5:11] ------------------------------------------------------------------------------
> >>>>> 2023-11-17T07:53:25 | 6 | db | run_similarity | Running similarity...
> >>>>> 2023-11-17T07:53:25 | 6 | db | run_similarity | Running '/home/shaunmcdonald/taginfo-tools/build/src/taginfo-similarity /home/shaunmcdonald/taginfo-gb/build/db/taginfo-db.db'...
> >>>>> 2023-11-17T07:53:50 | 6 | db | run_similarity | Running SQL script '/home/shaunmcdonald/taginfo-gb/taginfo/sources/db/post_similar_keys.sql' on database '/home/shaunmcdonald/taginfo-gb/build/db/taginfo-db.db'...
> >>>>> 2023-11-17T07:53:50 | 6 | db | update_characters | Running update_characters...
> >>>>> 2023-11-17T07:53:50 | 6 | db | update_characters | Running 'env - ruby -E utf-8 -w -I /home/shaunmcdonald/taginfo-gb/taginfo/sources/db/lib /home/shaunmcdonald/taginfo-gb/taginfo/sources/db/update_characters.rb /home/shaunmcdonald/taginfo-gb/build/db'...
> >>>>> 2023-11-17T07:53:51 | 6 | db | main | Running SQL script '/home/shaunmcdonald/taginfo-gb/taginfo/sources/db/post_grades.sql' on database '/home/shaunmcdonald/taginfo-gb/build/db/taginfo-db.db'...
> >>>>> 2023-11-17T07:53:51 | 6 | db | main | Running SQL script '/home/shaunmcdonald/taginfo-gb/taginfo/sources/db/post_indexes.sql' on database '/home/shaunmcdonald/taginfo-gb/build/db/taginfo-db.db'...
> >>>>> 2023-11-17T07:53:59 | 7 | db | finalize_database | Running SQL script '/home/shaunmcdonald/taginfo-gb/taginfo/sources/db/post.sql' on database '/home/shaunmcdonald/taginfo-gb/build/db/taginfo-db.db'...
> >>>>> 2023-11-17T07:54:05 | 7 | db | main | Done db.
> >>>>> 2023-11-17T07:54:05 | 7 | all | update_source | Done.
> >>>>> 2023-11-17T07:54:05 | 7 | all | compress_source_databases | Compressing all source databases...
> >>>>> 2023-11-17T07:54:05 | 7 | all | compress_file | Compressing 'db/taginfo-db' to 'db' using 'bzip2'
> >>>>> 2023-11-17T07:54:56 | 8 | all | compress_source_databases | Done.
> >>>>> 2023-11-17T07:54:56 | 8 | all | create_extra_indexes | Creating extra indexes...
> >>>>> 2023-11-17T07:54:56 | 8 | all | create_extra_indexes | Running SQL script '/home/shaunmcdonald/taginfo-gb/taginfo/sources/db/add_extra_indexes.sql' on database '/home/shaunmcdonald/taginfo-gb/build/db/taginfo-db.db'...
> >>>>> 2023-11-17T07:55:32 | 8 | all | create_extra_indexes | Running SQL script '/home/shaunmcdonald/taginfo-gb/taginfo/sources/db/add_ftsearch.sql' on database '/home/shaunmcdonald/taginfo-gb/build/db/taginfo-db.db'...
> >>>>> 2023-11-17T07:56:02 | 9 | all | create_extra_indexes | Done.
> >>>>> 2023-11-17T07:56:02 | 9 | all | update_master | Running master/update.sh...
> >>>>> 2023-11-17T07:56:02 | 9 | master | main | Start master...
> >>>>> 2023-11-17T07:56:02 | 9 | master | create_master_database | Running SQL script '/home/shaunmcdonald/taginfo-gb/taginfo/sources/master/languages.sql' on database '/home/shaunmcdonald/taginfo-gb/build/taginfo-master.db'...
> >>>>> 2023-11-17T07:56:02 | 9 | master | create_master_database | Running SQL script '/home/shaunmcdonald/taginfo-gb/taginfo/sources/master/master.sql' on database '/home/shaunmcdonald/taginfo-gb/build/taginfo-master.db'...
> >>>>> 2023-11-17T07:56:02 | 9 | master | create_master_database | with parameter: DIR=/home/shaunmcdonald/taginfo-gb/build
> >>>>> 2023-11-17T07:56:02 | 9 | master | create_master_database | with parameter: MIN_COUNT_POPULAR=1000
> >>>>> 2023-11-17T07:56:02 | 9 | master | create_master_database | with parameter: MIN_COUNT_SUGGESTION=10000
> >>>>> Error: near line 83: near "FROM": syntax error
> >>>>>
> >>>>> I’m wondering what could have changed?
> >>>>> Is it possible that the memory required has increased but it’s not mentioning that as the error?
> >>>>> Was there some other code or data change that I need to consider?
> >>>>>
> >>>>> Thanks,
> >>>>> Shaun
> >>>>
> >>>>> _______________________________________________
> >>>>> Taginfo-dev mailing list
> >>>>> Taginfo-dev at openstreetmap.org
> >>>>> https://lists.openstreetmap.org/listinfo/taginfo-dev
> >>>>
> >>>>
> >>>> --
> >>>> Jochen Topf jochen at remote.org https://www.jochentopf.com/ +49-351-31778688
> >>>>
> >>
> >> --
> >> Jochen Topf jochen at remote.org https://www.jochentopf.com/ +49-351-31778688
> >>
> >
>
--
Jochen Topf jochen at remote.org https://www.jochentopf.com/ +49-351-31778688
More information about the Taginfo-dev
mailing list