initial attempt at mysql fulltext search for nodes #1913

jywarren · 2018-01-03T21:57:24Z

Should resolve several issues, including:

jywarren · 2018-01-03T22:41:44Z

@icarito I'm unable to get unstable to build this and generate a new schema.rb file with RAILS_ENV=production rake db:migrate ... not sure why. But if we can get that to run and check in a new schema.rb.example file, i think we could make this work...

jywarren · 2018-01-03T22:50:15Z

ok, got it! used local mysql.

PublicLabBot · 2018-01-03T22:58:49Z

	1 Warning
⚠️	New migrations added. Please update `schema.rb.example` by overwriting it with a copy of the up-to-date `db/schema.rb`.

	1 Message
📖	@jywarren Thank you for your pull request! I’m here to help with some tips and recommendations. Please take a look at the list provided and help us review and accept your contribution! And don’t be discouraged if you see errors – we’re here to help.

Generated by 🚫 Danger

jywarren · 2018-01-05T16:35:44Z

We can later integrate this with

plots2/app/services/typeahead_service.rb

Line 38 in 111fda5

search = Node.search do

jywarren · 2018-01-05T16:36:13Z

We'd want to later expand it to include node tags... probably in that method i just linked to.

jywarren · 2018-01-05T18:00:12Z

@icarito -- wow

jywarren · 2018-01-05T18:25:37Z

Adding a new index for revision title

jywarren · 2018-01-05T21:11:03Z

I think we're at the point where we need to start removing Solr infrastructure...!! @icarito

jywarren · 2018-01-05T21:11:16Z

like the solr tests for this typeahead feature

jywarren · 2018-01-08T21:35:49Z

This seems ready to go except I can't get it to build on unstable due to the secrets.yml changes from Rails 4.1 upgrade. @icarito if you have a moment to rejigger the container to get this building on unstable, much appreciated, thanks!

jywarren · 2018-01-08T21:36:15Z

http://unstable.publiclab.org/dashboard

icarito · 2018-01-10T12:09:06Z

Tried adding SECRET_KEY_BASE and SECRET_TOKEN env variables but that didn't change anything, looking for what might have changed to need to configure this.

icarito · 2018-01-10T12:14:37Z

Ah I think I know what's going on, need to pass the secret across the Dockerfile... making patch

icarito · 2018-01-10T12:40:02Z

@jywarren rejiggered!

icarito · 2018-01-10T12:45:39Z

Logs are showing this now:

 
Started GET "/" for 200.121.220.250 at 2018-01-10 12:44:26 +0000 
Processing by HomeController#home as HTML 
Read fragment views/feature_home-carousel/c50d8214fcbb21d17c797b0912595828 (0.1ms) 
Read fragment views/feature_home-banners/c50d8214fcbb21d17c797b0912595828 (0.1ms) 
Read fragment views/home-activity/c50d8214fcbb21d17c797b0912595828 (0.1ms) 
Read fragment views/trending-tags/36db21af3871a21d6c52a5b5258b01b9 (0.1ms) 
  Rendered dashboard/_node_comment.html.erb (7.7ms) 
  Rendered dashboard/_node_comment.html.erb (4.3ms) 
  Rendered dashboard/_node_comment.html.erb (8.3ms) 
  Rendered dashboard/_node_meta.html.erb (3.4ms) 
  Rendered dashboard/_node_wiki.html.erb (9.6ms) 
  Rendered dashboard/_node_comment.html.erb (8.0ms) 
  Rendered dashboard/_node_meta.html.erb (2.0ms) 
  Rendered dashboard/_node_wiki.html.erb (6.9ms) 
  Rendered dashboard/_node_meta.html.erb (2.9ms) 
  Rendered dashboard/_node_wiki.html.erb (7.6ms) 
  Rendered dashboard/_node_meta.html.erb (1.9ms) 
  Rendered dashboard/_node_wiki.html.erb (5.7ms) 
  Rendered dashboard/_node_moderate.html.erb (0.0ms) 
  Rendered dashboard/_node_meta.html.erb (17.8ms) 
  Rendered dashboard/_node_default.html.erb (20.3ms) 
  Rendered dashboard/_node_moderate.html.erb (0.0ms) 
  Rendered dashboard/_node_meta.html.erb (6.8ms) 
  Rendered dashboard/_node_default.html.erb (9.1ms) 
  Rendered dashboard/_node_moderate.html.erb (0.0ms) 
  Rendered dashboard/_node_meta.html.erb (6.9ms) 
  Rendered dashboard/_node_default.html.erb (9.3ms) 
  Rendered dashboard/_node_moderate.html.erb (0.0ms) 
  Rendered dashboard/_node_meta.html.erb (6.6ms) 
  Rendered dashboard/_node_default.html.erb (9.0ms) 
  Rendered dashboard/_node_moderate.html.erb (0.0ms) 
  Rendered dashboard/_node_meta.html.erb (7.2ms) 
  Rendered dashboard/_node_question.html.erb (11.5ms) 
  Rendered dashboard/_node_moderate.html.erb (0.0ms) 
  Rendered dashboard/_node_meta.html.erb (7.0ms) 
  Rendered dashboard/_node_question.html.erb (11.3ms) 
  Rendered dashboard/_node_wiki.html.erb (2.1ms) 
  Rendered dashboard/_activity.html.erb (168.9ms) 
  Rendered home/home.html.erb within layouts/application (170.6ms) 
Completed 500 Internal Server Error in 178ms (ActiveRecord: 53.8ms) 
 
ActionView::Template::Error (undefined method `author' for nil:NilClass): 
    3:  
    4:     <% if node.is_a?(Node) %> 
    5:       <div class="header-icon"><i class="fa fa-book"></i></div> 
    6:       <p class="meta"><span><%= t('dashboard._node_wiki.new_page_by') %></span> <a href="/profile/<%= node.latest.author.name %>"><%= node.latest.author.name %></a></p> 
    7:     <% else %> 
    8:       <div class="header-icon"><i class="fa fa-pencil"></i></div> 
    9:       <p class="meta"><%= t('dashboard._node_wiki.new_edit_by') %> <a href="/profile/<%= node.author.try(:name) %>"><%= node.author.try(:name) %></a></p> 
  app/views/dashboard/_node_wiki.html.erb:6:in `_app_views_dashboard__node_wiki_html_erb__591707637358904511_52200780' 
  app/views/dashboard/_activity.html.erb:67:in `block in _app_views_dashboard__activity_html_erb___3452691611226790219_55323320' 
  app/views/dashboard/_activity.html.erb:65:in `each' 
  app/views/dashboard/_activity.html.erb:65:in `each_with_index' 
  app/views/dashboard/_activity.html.erb:65:in `_app_views_dashboard__activity_html_erb___3452691611226790219_55323320' 
  app/views/home/home.html.erb:124:in `block in _app_views_home_home_html_erb__4282141704495521106_55862820' 
  app/views/home/home.html.erb:123:in `_app_views_home_home_html_erb__4282141704495521106_55862820' 
  app/controllers/home_controller.rb:20:in `home'

jywarren · 2018-01-10T21:16:11Z

latest is now broken on node.rb -- this is weird because it's tested on this line:

plots2/test/unit/node_test.rb

Line 127 in a876a84

assert_equal node.revisions.first, node.latest

Aha! It looks like the Revisions table is almost empty. Can we reload the database?

irb(main):002:0> Node.last.latest
=> nil
irb(main):003:0> Node.last.revisions
=> #<ActiveRecord::AssociationRelation []>
irb(main):004:0> Revision.count
=> 32
irb(main):005:0> Node.count
=> 8437

icarito · 2018-01-11T02:13:40Z

Okay I loaded a db dump from september, and the app starts, but when I attempt migration it says:

icarito@tycho:/srv/plots_unstable/plots2$ RAILS_ENV=production docker-compose -f docker-compose-unstable.yml exec web rake db:migrate 
WARNING: The SECRET_KEY_BASE variable is not set. Defaulting to a blank string. 
== 20171013161520 Indices: migrating ========================================== 
-- add_index("rusers", ["email"], {:name=>"index_rusers_on_email"}) 
   -> 1.9896s 
-- add_index("users", ["uid"], {:name=>"index_users_uid"}) 
   -> 1.1564s 
-- add_index("node_revisions", ["timestamp"], {:name=>"index_node_revisions_timestamp"}) 
   -> 0.5442s 
== 20171013161520 Indices: migrated (3.6906s) ================================= 
 
== 20171110161510 AddIndexToken: migrating ==================================== 
-- add_index("rusers", ["persistence_token"], {:name=>"index_rusers_on_persistence_token"}) 
   -> 3.4496s 
== 20171110161510 AddIndexToken: migrated (3.4498s) =========================== 
 
== 20180103212804 RevisionFulltextIndex: migrating ============================ 
-- add_index(:node_revisions, [:body, :title], {:type=>:fulltext}) 
rake aborted! 
StandardError: An error has occurred, all later migrations canceled: 
 
Mysql2::Error: Lost connection to MySQL server during query: CREATE fulltext INDEX `index_node_revisions_on_body_and_title`  ON `node_revisions` (`body`, `title`) /usr/local/bundle/gems/mysql2-0.3.21/lib/mysql2/client.rb:80:in `_query' 
/usr/local/bundle/gems/mysql2-0.3.21/lib/mysql2/client.rb:80:in `block in query' 
/usr/local/bundle/gems/mysql2-0.3.21/lib/mysql2/client.rb:79:in `handle_interrupt' 
/usr/local/bundle/gems/mysql2-0.3.21/lib/mysql2/client.rb:79:in `query' 
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:303:in `block in execute' 
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract_adapter.rb:378:in `block in log' 
/usr/local/bundle/gems/activesupport-4.1.16/lib/active_support/notifications/instrumenter.rb:20:in `instrument' 
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract_adapter.rb:372:in `log' 
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:303:in `execute' 
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/connection_adapters/mysql2_adapter.rb:228:in `execute' 
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:525:in `add_index' 
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:656:in `block in method_missing' 
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:628:in `block in say_with_time'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:628:in `say_with_time'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:648:in `method_missing'
/app/db/migrate/20180103212804_revision_fulltext_index.rb:4:in `up'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:605:in `exec_migration'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:586:in `block (2 levels) in migrate'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:585:in `block in migrate'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract/connection_pool.rb:294:in `with_connection'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:584:in `migrate'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:759:in `migrate'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:998:in `block in execute_migration_in_transaction'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:1046:in `ddl_transaction'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:997:in `execute_migration_in_transaction'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:959:in `block in migrate'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:955:in `each'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:955:in `migrate'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:814:in `up'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:792:in `migrate'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/railties/databases.rake:34:in `block (2 levels) in <top (required)>'
ActiveRecord::StatementInvalid: Mysql2::Error: Lost connection to MySQL server during query: CREATE fulltext INDEX `index_node_revisions_on_body_and_title`  ON `node_revisions` (`body`, `title`) 
/usr/local/bundle/gems/mysql2-0.3.21/lib/mysql2/client.rb:80:in `_query'
/usr/local/bundle/gems/mysql2-0.3.21/lib/mysql2/client.rb:80:in `block in query'
/usr/local/bundle/gems/mysql2-0.3.21/lib/mysql2/client.rb:79:in `handle_interrupt'
/usr/local/bundle/gems/mysql2-0.3.21/lib/mysql2/client.rb:79:in `query'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:303:in `block in execute'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract_adapter.rb:378:in `block in log'
/usr/local/bundle/gems/activesupport-4.1.16/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract_adapter.rb:372:in `log'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:303:in `execute'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/connection_adapters/mysql2_adapter.rb:228:in `execute'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:525:in `add_index'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:656:in `block in method_missing'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:628:in `block in say_with_time'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:628:in `say_with_time'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:648:in `method_missing'
/app/db/migrate/20180103212804_revision_fulltext_index.rb:4:in `up'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:605:in `exec_migration'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:586:in `block (2 levels) in migrate'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:585:in `block in migrate'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract/connection_pool.rb:294:in `with_connection'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:584:in `migrate'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:759:in `migrate'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:998:in `block in execute_migration_in_transaction'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:1046:in `ddl_transaction'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:997:in `execute_migration_in_transaction'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:959:in `block in migrate'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:955:in `each'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:955:in `migrate'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:814:in `up'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:792:in `migrate'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/railties/databases.rake:34:in `block (2 levels) in <top (required)>'
Mysql2::Error: Lost connection to MySQL server during query
/usr/local/bundle/gems/mysql2-0.3.21/lib/mysql2/client.rb:80:in `_query'
/usr/local/bundle/gems/mysql2-0.3.21/lib/mysql2/client.rb:80:in `block in query'
/usr/local/bundle/gems/mysql2-0.3.21/lib/mysql2/client.rb:79:in `handle_interrupt'
/usr/local/bundle/gems/mysql2-0.3.21/lib/mysql2/client.rb:79:in `query'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:303:in `block in execute'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract_adapter.rb:378:in `block in log'
/usr/local/bundle/gems/activesupport-4.1.16/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract_adapter.rb:372:in `log'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:303:in `execute'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/connection_adapters/mysql2_adapter.rb:228:in `execute'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:525:in `add_index'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:656:in `block in method_missing'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:628:in `block in say_with_time'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:628:in `say_with_time'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:648:in `method_missing'
/app/db/migrate/20180103212804_revision_fulltext_index.rb:4:in `up'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:605:in `exec_migration'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:586:in `block (2 levels) in migrate'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:585:in `block in migrate'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/connection_adapters/abstract/connection_pool.rb:294:in `with_connection'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:584:in `migrate'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:759:in `migrate'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:998:in `block in execute_migration_in_transaction'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:1046:in `ddl_transaction'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:997:in `execute_migration_in_transaction'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:959:in `block in migrate'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:955:in `each'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:955:in `migrate'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:814:in `up'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/migration.rb:792:in `migrate'
/usr/local/bundle/gems/activerecord-4.1.16/lib/active_record/railties/databases.rake:34:in `block (2 levels) in <top (required)>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)
icarito@tycho:/srv/plots_unstable/plots2$

icarito · 2018-01-11T02:16:23Z

Whoa that was actually the mysql server crashing on migration!

db_1   | Version: '5.6.30'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server (GPL)
db_1   | 2018-01-11 02:12:08 7f38c97fa700  InnoDB: Assertion failure in thread 139881875482368 in file row0merge.cc line 794
db_1   | InnoDB: Failing assertion: b == &block[0] + buf->total_size
db_1   | InnoDB: We intentionally generate a memory trap.
db_1   | InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
db_1   | InnoDB: If you get repeated assertion failures or crashes, even
db_1   | InnoDB: immediately after the mysqld startup, there may be
db_1   | InnoDB: corruption in the InnoDB tablespace. Please refer to
db_1   | InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
db_1   | InnoDB: about forcing recovery.
db_1   | 02:12:08 UTC - mysqld got signal 6 ;
db_1   | This could be because you hit a bug. It is also possible that this binary
db_1   | or one of the libraries it was linked against is corrupt, improperly built,
db_1   | or misconfigured. This error can also be caused by malfunctioning hardware.
db_1   | We will try our best to scrape up some info that will hopefully help
db_1   | diagnose the problem, but since we have already crashed, 
db_1   | something is definitely wrong and this may fail.
db_1   | 
db_1   | key_buffer_size=8388608
db_1   | read_buffer_size=131072
db_1   | max_used_connections=2
db_1   | max_threads=151
db_1   | thread_count=2
db_1   | connection_count=2
db_1   | It is possible that mysqld could use up to 
db_1   | key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68108 K  bytes of memory
db_1   | Hope that's ok; if not, decrease some variables in the equation.
db_1   | 
db_1   | Thread pointer: 0x0
db_1   | Attempting backtrace. You can use the following information to find out
db_1   | where mysqld died. If you see no messages after this, something went
db_1   | terribly wrong...
db_1   | stack_bottom = 0 thread_stack 0x40000
db_1   | mysqld(my_print_stacktrace+0x2c)[0x8c0ebc]
db_1   | mysqld(handle_fatal_signal+0x481)[0x65f5d1]
db_1   | /lib/x86_64-linux-gnu/libpthread.so.0(+0xf8d0)[0x7f38fd2978d0]
db_1   | /lib/x86_64-linux-gnu/libc.so.6(gsignal+0x37)[0x7f38fbc8d067]
db_1   | /lib/x86_64-linux-gnu/libc.so.6(abort+0x148)[0x7f38fbc8e448]
db_1   | mysqld[0x9efc8d]
db_1   | mysqld[0x9dd7d4]
db_1   | /lib/x86_64-linux-gnu/libpthread.so.0(+0x80a4)[0x7f38fd2900a4]
db_1   | /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f38fbd4087d]
db_1   | The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
db_1   | information that should help you find out what is causing the crash.

icarito · 2018-01-11T02:16:44Z

Perhaps the container runs out of memory when creating the index?

icarito · 2018-01-11T02:26:15Z

Looks similar to this: https://stackoverflow.com/questions/17724636/adding-fulltext-index-crashes-mysql-service - looking for options - seems an instance of this bug: https://bugs.mysql.com/bug.php?id=82665 which suggests upgrading to mysql 5.7 might fix it. Also will consider MariaDB.

icarito · 2018-01-12T06:53:09Z

Hey, I upgraded staging unstable branch to mariadb 10.2 and was able to create the fulltext indexes.
So I was able to test it!

Jeff how does it compare? Seems quick to me and detected the unique word we used before for testing full text indexing.

icarito · 2018-01-12T06:56:18Z

I'll make another pull request with the database upgrade. Note this requires some manual steps when deploying to production as there are maintenance scripts to be run. Mariadb over Mysql selection to be agreed, either should work but possibly harder to switch between them after upgrade.

jywarren · 2018-01-12T14:13:23Z

Agree, awesome! Let's do this!!

…

On Jan 12, 2018 1:56 AM, "Sebastian Silva" ***@***.***> wrote: I'll make another pull request with the database upgrade. Note this requires some manual steps when deploying to production as there are maintenance scripts to be run. Mariadb over Mysql selection to be agreed, either should work but possibly harder to switch between them after upgrade. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#1913 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AABfJ53erhAduKuAMRymoho7DhLWOwkTks5tJwISgaJpZM4RST88> .

jywarren · 2018-01-12T14:25:47Z

We do check for if ActiveRecord::Base.connection.adapter_name == 'Mysql2' in https://github.com/jywarren/plots2/blob/mysql-fulltext/app/services/typeahead_service.rb -- are we sure this is still evaluating to "true"?

jywarren · 2018-01-12T15:05:47Z

I think this is ready for merge, to tell the truth -- as soon as the MariaDB stuff is in place. Eager!!!

icarito · 2018-01-12T16:21:42Z

db_1   | 2018-01-12  6:39:30 140265714739072 [ERROR] Missing system table mysql.roles_mapping; please run mysql_upgrade to create it
db_1   | 2018-01-12  6:39:30 140265714739072 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to h
ave type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTR
ACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQ
L40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID
_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENG
TH'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION',
'NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','AN
SI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_A
db_1   | 2018-01-12  6:39:30 140265714739072 [ERROR] mysqld: Event Scheduler: An error occurred when initializing system tables. Disabling the
 Event Scheduler.

* Remove Solr stuff and add secret_token env var * Upgrade and switch database engine to Mariadb.

icarito · 2018-01-12T17:30:50Z

Tested successfully in unstable, now trying it in stable:

  Rendered dashboard/_node_comment.html.erb (4.8ms)                                                                                                                              │·························
  Rendered dashboard/_activity.html.erb (650.8ms)                                                                                                                                │·························
  Rendered home/home.html.erb within layouts/application (655.0ms)                                                                                                               │·························
Completed 500 Internal Server Error in 741ms (ActiveRecord: 151.4ms)                                                                                                             │·························
                                                                                                                                                                                 │·························
ActionView::Template::Error (incompatible character encodings: ASCII-8BIT and UTF-8):                                                                                            │·························
    121:                                                                                                                                                                         │·························
    122: <div class="dashboard">                                                                                                                                                 │·························
    123:   <% cache('home-activity', expires_in: 60.minutes, skip_digest: true) do %>                                                                                            │·························
    124:     <%= render partial: "dashboard/activity", locals: { activity: @activity, notes: @notes } %>                                                                         │·························
    125:   <% end %>                                                                                                                                                             │·························
    126:   <%= stylesheet_link_tag "dashboard" %>                                                                                                                                │·························
    127:   <%= javascript_include_tag "dashboard" %>                                                                                                                             │·························
  app/views/home/home.html.erb:124:in `block in _app_views_home_home_html_erb___2727232513106124855_33290980'                                                                    │·························
  app/views/home/home.html.erb:123:in `_app_views_home_home_html_erb___2727232513106124855_33290980'                                                                             │·························
  app/controllers/home_controller.rb:20:in `home'                                                                                                                                │·························
                                                                                                                   ```

icarito · 2018-01-13T04:43:51Z

Deployed to production!

* initial attempt at mysql fulltext search for nodes * tweak * major rails 4.x update of schema.rb, added fulltext index * Update node.rb * Update node_test.rb * rename * self. * add title to fulltext * plugged into typeahead * rev title fulltext index * fixed fulltext multi index * updated schema example * fix * tweaked working fulltext in console * removing solr test apparatus * full text comment and user bio searching * more conditions * fixes * unique results by grouping by nid * Remove Solr stuff and add secret_token env var (publiclab#259) * Remove Solr stuff and add secret_token env var * Upgrade and switch database engine to Mariadb.

jywarren added 2 commits January 3, 2018 16:45

initial attempt at mysql fulltext search for nodes

68990fd

tweak

6497827

major rails 4.x update of schema.rb, added fulltext index

2c444d8

jywarren added 4 commits January 3, 2018 19:54

Update node.rb

343d4c9

Update node_test.rb

890691a

rename

2ae87a9

rename search to fulltext

00d65e6

self.

250750b

jywarren added 3 commits January 5, 2018 13:03

add title to fulltext

d8b24da

plugged into typeahead

cac10ce

rev title fulltext index

0ba020c

jywarren added 4 commits January 5, 2018 13:51

fixed fulltext multi index

903211c

updated schema example

c5053d2

fix

efa2bd6

tweaked working fulltext in console

0577c6c

jywarren added 4 commits January 5, 2018 16:40

removing solr test apparatus

865b30a

full text comment and user bio searching

07bce21

more conditions

ef3b8b1

fixes

e81f5c3

This was referenced Jan 8, 2018

upgrade to solr 5.5 container #1818

Closed

Attempt to reproduce Solr maxWarmingSearchers 503 error in Integration test #1836

Closed

jywarren mentioned this pull request Jan 8, 2018

disable auto_commit_after_request in sunspot.yml #1819

Closed

ebarry mentioned this pull request Jan 9, 2018

Review autosuggest results for unusual duplicates #1941

Closed

unique results by grouping by nid

3e8034d

Remove Solr stuff and add secret_token env var (#259)

a3bf893

* Remove Solr stuff and add secret_token env var * Upgrade and switch database engine to Mariadb.

jywarren mentioned this pull request Jan 12, 2018

more complete solr removal #1971

Merged

jywarren merged commit ad562ef into publiclab:master Jan 12, 2018

jywarren mentioned this pull request Jan 12, 2018

An amazing week -- thank you all! #1981

Closed

This pull request was closed.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

initial attempt at mysql fulltext search for nodes #1913

initial attempt at mysql fulltext search for nodes #1913

jywarren commented Jan 3, 2018 •

edited

Loading

jywarren commented Jan 3, 2018

jywarren commented Jan 3, 2018

PublicLabBot commented Jan 3, 2018 •

edited

Loading

jywarren commented Jan 5, 2018

jywarren commented Jan 5, 2018

jywarren commented Jan 5, 2018

jywarren commented Jan 5, 2018

jywarren commented Jan 5, 2018

jywarren commented Jan 5, 2018

jywarren commented Jan 8, 2018

jywarren commented Jan 8, 2018

icarito commented Jan 10, 2018

icarito commented Jan 10, 2018

icarito commented Jan 10, 2018

icarito commented Jan 10, 2018

jywarren commented Jan 10, 2018

icarito commented Jan 11, 2018

icarito commented Jan 11, 2018

icarito commented Jan 11, 2018

icarito commented Jan 11, 2018 •

edited

Loading

icarito commented Jan 12, 2018

icarito commented Jan 12, 2018

jywarren commented Jan 12, 2018 via email

jywarren commented Jan 12, 2018

jywarren commented Jan 12, 2018

icarito commented Jan 12, 2018

icarito commented Jan 12, 2018

icarito commented Jan 13, 2018

initial attempt at mysql fulltext search for nodes #1913

initial attempt at mysql fulltext search for nodes #1913

Conversation

jywarren commented Jan 3, 2018 • edited Loading

jywarren commented Jan 3, 2018

jywarren commented Jan 3, 2018

PublicLabBot commented Jan 3, 2018 • edited Loading

jywarren commented Jan 5, 2018

jywarren commented Jan 5, 2018

jywarren commented Jan 5, 2018

jywarren commented Jan 5, 2018

jywarren commented Jan 5, 2018

jywarren commented Jan 5, 2018

jywarren commented Jan 8, 2018

jywarren commented Jan 8, 2018

icarito commented Jan 10, 2018

icarito commented Jan 10, 2018

icarito commented Jan 10, 2018

icarito commented Jan 10, 2018

jywarren commented Jan 10, 2018

icarito commented Jan 11, 2018

icarito commented Jan 11, 2018

icarito commented Jan 11, 2018

icarito commented Jan 11, 2018 • edited Loading

icarito commented Jan 12, 2018

icarito commented Jan 12, 2018

jywarren commented Jan 12, 2018 via email

jywarren commented Jan 12, 2018

jywarren commented Jan 12, 2018

icarito commented Jan 12, 2018

icarito commented Jan 12, 2018

icarito commented Jan 13, 2018

jywarren commented Jan 3, 2018 •

edited

Loading

PublicLabBot commented Jan 3, 2018 •

edited

Loading

icarito commented Jan 11, 2018 •

edited

Loading