Local Community

Database completely gone

Support
,
#1

Issue Summary

I think the same as HELP NEEDED on Local: can't connect to any databases anymore or import them after computer shut down unexpectedly

However, I followed How can I connect to MySQL using TCP/IP rather than a socket on macOS/Linux? so my PhpStorm was still able to access the database - so I exported to SQL files (one file per db table)

So how do I now create a new table and import? My Adminer looks just like that other article’s screenshot, and my running wp db create --dbuser=root --dbpass=root returns ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Troubleshooting Questions

  • Does this happen for all sites in Local, or just one in particular?
    Just one

System Details

  • Which version of Local is being used?
    5.10.3+5332

  • What Operating System (OS) and OS version is being used?
    macOS 11.2.3

  • Attach the Local Log. See this Community Forum post for instructions on how to do so:
    local-lightning.log (245.0 KB)

#2

@ben.turner any help?

I tried reverting back to v5.10.0 and then v5.9.4 and this one site (of course the most valuable to me) said Error establishing a database connection the same way across all 3 versions (latest and the two I tried downgrading to since it never had this issue until upgrading to this latest version).

#3

Unsure if related (since the errors are ‘sql’), but I tried importing a brand new site and got this error:

Screen Shot 2021-04-20 at 10.24.01 AM
Screen Shot 2021-04-20 at 10.24.01 AM496×610 43.6 KB

Again, I was able to work on other existing sites.

#4

Whoah, the Local log has some crazy messages coming from the mysql process:

2021-03-01T17:39:06.399032Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: fsp0fsp.cc:3538:xdes_mtr_get_bit(descr, XDES_FREE_BIT, header_page % FSP_EXTENT_SIZE, mtr) == FALSE thread 123145493684224

I’ve never seen that error before, but when I google innodb assertion failure I get a bunch of things related to a corrupted DB.

If you have a pretty current version of this site somewhere (like production or staging) it might be easier to delete the Local site and re-import the remote one.

It’s interesting to me that it’s only this specific site that’s having problems, which makes me wonder about a few things:

  • How much ram does this computer have and is there enough to process this DB?
  • On the flip side, how large is this DB? Is it causing issues on this specific hardware?
  • Are there any interesting tables? (vague I know, but maybe that makes you think of something?)

One option would to be looking into the actual mysql log for this site. If you want, you can attach a copy of the sitename/logs/mysql/error.log so that we can take a look and see if anything is out of the ordinary.

#5

MBP 2019 16" with i9 and 32GB RAM

sitename/logs/mysql does not exist, just sitename/logs/nginx and sitename/logs/php – same for other sites’ folders

DB can’t be large. Probably 20 posts/pages and 1,000 Gravity Forms entries. No unexpected tables.

I guess I’ll scrap this local site and re-import and then replace the wp-content folder with this one (since I had development plugins in it that the live site doesn’t have)

So any idea about the import error? I tried both a tar.gz from my webhost backup as well as a Duplicator .zip and both failed with the same error but the Duplicator one seems like it actually kept the import as a new site. Oddly, though, trusting the cert doesn’t work even though I entered my computer password. Restarting the site made it appear as if the cert was now trusted but the browser still disagreed: NET::ERR_CERT_AUTHORITY_INVALID

Thank you VERY much for helping me get un-stuck.

local-lightning.log (555.0 KB)

#6

Nice! Good luck with re-importing the DB!

It’s hard to say without seeing the contents of those individual SQL files. You mention that it was a backup from the webhost – was that an automated backup, or did you export the DB using something like phpMyAdmin?

If I were to guess, there might be some specific MySQL table things that Local isn’t able to automatically import. A couple of observations or things to look into:

  • When Local creates a site, it names the database local – do any of those sql files try using a different database? Maybe a different table that isn’t created yet?
  • Those table names (cssjs.sql, crawler.sql) seem like plugin specific ones. Maybe there’s a collation or character set difference between the two versions of MySQL? Check the version of MySQL on the remote host and consider matching that version within Local.
  • The MySQL user that Local creates is root. I don’t think this would break things, but maybe there’s some custom views or commands that are trying to be run as a specific user that doesn’t exist?

Hope that help gives you a few ideas of things to follow up on!

#7

Bummer no specific solution, but thanks for sharing your thoughts. Again, it was working before the previous Local update. Maybe it borked but none of the others did because it’s my first (starred, at the top) site? Unlikely but the worst one of mine it could have happened to :frowning:

Guess if no one else had these issues, I’ll deal with things on my own. Thanks anyway.

1 Like
#8

@ben.turner Just one more confirmation - the database IS there because PhpStorm can get to it. See links in my initial post here of the way you helped me previously accomplish this (new TCP/IP user on the IPv4 loopback).

So is there a way to recover that you can think of?

#9

Hmm, maybe that wasn’t accurate - maybe PhpStorm caches database tables and values… I set my wp-config.php to define( 'DB_HOST', '192.168.95.100' ); and it still didn’t work - just that it tried for much longer in the browser and then caused PHP Warning: mysqli_real_connect(): (HY000/2002): Operation timed out in .../app/public/wp-includes/wp-db.php on line 1653

And PhpStorm’s db connection settings’ “test connection” didn’t work either.

Is there not some command to awake the database in this one local site?

#10

The original error message seemed to indicate that the database was corrupted. Because of this, just because Phpstorm can connect to the database doesn’t mean that the data within the database is correct.

The reason you received a timeout error within PHPstorm, is likely due to not having a port number within the configuration settings. Can you take a look at this FAQ and ensure that you are using localhost as well as the port number for the site configuration?

#11

So if mysql -e "SHOW VARIABLES WHERE Variable_name = 'port';" returns:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/Users/cliff/Library/Application Support/Local/run/oQ5Apb3x2/mysql/mysqld.sock' (2)

… is there ANY hope, or is it torched?

#12

There’s not a .sock file in /Users/cliff/Library/Application Support/Local/run/oQ5Apb3x2/mysql nor other sites’ folders in that location.

/Users/cliff/Library/Application Support/Local/sites.json has this in it for the affected site:

  • note MySQL port of 10004 - if it helps
  • I’m unsure what the "localVersion": "5.4.1+3311" is supposed to represent (current installed version or version as of the install of this site) but my current Local version is 5.10.3+5332 
	"oQ5Apb3x2": {
		"id": "oQ5Apb3x2",
		"path": "/Users/cliff/Local Sites/calc",
		"localVersion": "5.4.1+3311",
		"workspace": null,
		"mysql": {
			"database": "local",
			"password": "root",
			"user": "root"
		},
		"ports": {},
		"hostConnections": null,
		"name": "calc",
		"domain": "calc.local",
		"phpVersion": "7.4.1",
		"database": "mysql-8.0.16",
		"environment": "custom",
		"services": {
			"mailhog": {
				"ports": {
					"SMTP": [
						10002
					],
					"WEB": [
						10001
					]
				},
				"type": "lightning",
				"version": "1.0.0"
			},
			"mysql": {
				"ports": {
					"MYSQL": [
						10004
					]
				},
				"role": "db",
				"type": "lightning",
				"version": "8.0.16"
			},
			"nginx": {
				"ports": {
					"HTTP": [
						10000
					]
				},
				"role": "http",
				"type": "lightning",
				"version": "1.16.0"
			},
			"php": {
				"role": "php",
				"version": "7.3.5",
				"ports": {
					"cgi": [
						10003
					]
				}
			}
		},
		"notes": [
			{
				"date": "2020-12-18T22:41:16.901Z",
				"body": "`~/Library/Application\\ Support/Local/ssh-entry/oQ5Apb3x2.sh`",
				"pinned": false
			},
			{
				"date": "2020-12-18T22:42:28.360Z",
				"body": "`wp i18n make-pot . languages/cliff-wp-plugin-boilerplate.pot --headers='{\"Report-Msgid-Bugs-To\":\"Your Name or Your Company <https://www.example.com/>\"}'`",
				"pinned": false
			}
		]
	},
#13

Nice job finding the port in the sites.json file. One thing to note is that this is only telling Local about where that port should be. You’ll still have to verify that MySQL is running and listening on that port in order to use it.

The raw DB files are located in that site “run” folder you quoted: Local/run/oQ5Apb3x2/mysql so there may be “hope,” but I’m afraid my knowledge of tinkering with raw MySQL files isn’t up to the task of getting in there.

#14

I didn’t find any .sock files within any of the site folders within /Users/cliff/Library/Application Support/Local/run/ (nor anywhere else on my Mac) - so I feel like maybe I’m not looking in the right spot - or the error message from 2 days ago is a misleading error, since there’s no mysqld.sock for any site, not just this one with the DB issue… Any feedback on this?

#15

You can think of those sock files as an interface for the MySQL instance for that individual site. They are created when the site has been started, and so that error basically means that Local tried to create that socket file, but couldn’t because the previous one wasn’t cleaned up properly.

1 Like
#16

Gotcha. I confirmed mysqld.sock.lock appears after starting a site that works but not in this broken one.

With all that we know, starting a new site is the only path forward - there’s no way to fix the busted creation of the socket file? No special Terminal command you’ve got in your back pocket, for example?

#17

There’s no special command to force the creation of the socket file. The only thing that can be done is to either figure out why my MySQL can’t start for this site or go about recreating the content within a new site instance.

1 Like
#18

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.