Database crash __global_search’ needs repair

cpurbaugh · February 8, 2018, 2:37pm

Getting the following when trying to restore our database to a new install:

bench --site site1.local --force restore /home/frappe/frappe-bench/20180207_23             5831-site1_local-database.sql
MySQL root password:
Traceback (most recent call last):
  File "/usr/lib/python2.7/runpy.py", line 162, in _run_module_as_main
    "__main__", fname, loader, pkg_name)
  File "/usr/lib/python2.7/runpy.py", line 72, in _run_code
    exec code in run_globals
  File "/home/frappe/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 94, in <module>
    main()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 18, in main
    click.Group(commands=commands)(prog_name='bench')
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py", line 722, in __call__
    return self.main(*args, **kwargs)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py", line 697, in main
    rv = self.invoke(ctx)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py", line 1066, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py", line 1066, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py", line 895, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py", line 535, in invoke
    return callback(*args, **kwargs)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/decorators.py", line 17, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/commands/__init__.py", line 24, in _func
    ret = f(frappe._dict(ctx.obj), *args, **kwargs)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/commands/site.py", line 115, in restore
    force=context.force)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/commands/site.py", line 65, in _new_site
    admin_password=admin_password, verbose=verbose, source_sql=source_sql,force=force, reinstall=reinstall)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/installer.py", line 43, in install_db
    if not 'tabDefaultValue' in frappe.db.get_tables():
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database.py", line 784, in get_tables
    return [d[0] for d in self.sql("show tables")]
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database.py", line 176, in sql
    self._cursor.execute(query)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 165, in execute
    result = self._query(query)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 321, in _query
    conn.query(q)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 860, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1061, in _read_query_result
    result.read()
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1349, in read
    first_packet = self.connection._read_packet()
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 991, in _read_packet
    packet_header = self._read_bytes(4)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1037, in _read_bytes
    CR.CR_SERVER_LOST, "Lost connection to MySQL server during query")
pymysql.err.OperationalError: (2013, '')

codingCoffee · February 8, 2018, 2:45pm

The path to your backup seems incorrect, there’s a space in between

cpurbaugh · February 8, 2018, 2:53pm

It seems to be a copy and paste error, it was typed correctly originally. It’s only when we copied the log that that space appeared.

saidsl · February 8, 2018, 3:07pm

@cpurbaugh

Do you have any other installation that you can try to restore too other than your production/live server.
Is ERPNext hosted onsite or Virtual Machine or Cloud?
What are you upgrading from?

cpurbaugh · February 8, 2018, 3:09pm

We have the backup from last Friday that we can go back to. However, if we do that we lose 3 days of transactions, which will hurt us immensely.
Hosted on an onsite server running ESXI
V7

saidsl · February 8, 2018, 3:17pm

@cpurbaugh

Do pip list to determine if PyMySQL is installed
if not install pip install pymysql
Then try and run restore again

clarkej · February 8, 2018, 3:19pm

This mysql - Table is marked as crashed and should be repaired - Stack Overflow

suggests these to try, from the server command line:

mysqlcheck --repair --all-databases

from ‘bench mysql’ console

REPAIR TABLE `__global_search`;

for example

Julian_Robbins · February 8, 2018, 7:49pm

There is also a useful bench command to rebuild the entire search results which might be helpful … Though I appreciate you need to fis the table first. It may be possible to delete the search results and just rerun somehow …

cpurbaugh · February 8, 2018, 7:52pm

Do you mean delete the global_search table?

Julian_Robbins · February 8, 2018, 7:52pm

bench --site yoursite rebuild-global-search

Julian_Robbins · February 8, 2018, 7:53pm

I had many broken search results and frappe helped me with this
i’m not even sure if this command is shown in the help for bench which it should be

clarkej · February 9, 2018, 4:26am

No news is good news so you successfully resolved and are back online cpurbaugh?

Have you any followup learning footnote to relate?

The ‘bench rebuild-global-search’ code seems to collect ‘searchable’ doctype fields
frappe/utils.py at develop · frappe/frappe · GitHub
frappe/global_search.py at develop · frappe/frappe · GitHub

fwiw awhile back Julian posed this question If I create a Contact then delete it it still shows up in Search - #2 by clarkej

cpurbaugh · February 9, 2018, 12:52pm

No, we were not successful. ‘bench rebuild-global-search’ only seemed to make the system crash faster.

We’ve ended up reverting back to our V7 backup. We’ve lost 3 days of data though, which I will have to manually reenter. The import/export tool crashes our V10 installation immediately.

I’ve not gotten rid of the V10 installation, I want to try to find the cause of the crash. However, all of my resources are going to be focused on data recovery for the moment.

cpurbaugh · February 9, 2018, 4:47pm

It is looking like Bench Update --rebuild fixed our problem. Still testing though.

Julian_Robbins · February 10, 2018, 10:29am

Good luck with this, fingers crossed it works

Please update us … we are all here if you still need help …

krithi_ramani · February 19, 2018, 6:12pm

mysqlcheck -u root -p --repair --all-databases

This did the trick for me.

Krithi

kietmak · September 4, 2018, 12:11pm

Thanks krithi_ramani

bernealmazan · February 1, 2021, 3:16pm

This worked for me: bench rebuild-global-search
Thanks a lot clarkej.

ponyooooo · July 26, 2021, 3:05am

Hi, anyone can help how to rebuild the global search? because all created new purchase orders and other standard doctypes do not automatically can be searched on the global search. And it always needs to do a manual bench rebuild on the servers to do it.

santosh_baburao · April 10, 2022, 6:46pm

Thanks for your advise.

This solved my issue

ERPNext: v13.24.0 (version-13)

Frappe Framework: v13.24.0 (version-13)