Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
[resolved] Local dump file from remote source: the harrowing journey
(Ed. : This problem has evolved. To see the latest dilemma, jump down to here.)
I can’t seem to create a new local database via command-line on Mac (Yosemite). It keeps giving me ‘Access denied’ BS. (It’s my own computer, dammit!) I just got done reseting the MySQL root password (generic instructions) suspecting there was an issue there, and that when fine, but it doesn’t seem to have fixed anything.
This works well enough:
$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
... yadda yadda yadda
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.12 sec)But this, no dice:
mysql> create database foo_bar;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'foo_bar'And these tests don’t work either, for whatever reason. I don’t know that they’re actually needed for creating a database, but I’m under the impression I should be able to login that way if needed nevertheless:
$ mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
$ mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)I don’t want to install some extra software to interface with MySQL (Coco, or whatever) when I should be able to do it easy right from the line. Any ideas why I’m hitting a wall here?
Last edited by Destry (2015-03-23 09:36:06)
Offline
Re: [resolved] Local dump file from remote source: the harrowing journey
Btw, the create database docs say:
Note: If you get an error such as ERROR 1044 (42000): Access denied for user ‘monty’@‘localhost’ to database ‘menagerie’ when attempting to create a database, this means that your user account does not have the necessary privileges to do so. Discuss this with the administrator or see Section 6.2, The MySQL Access Privilege System.
Gee, docs. No kidding. Okay down this rabbit hole we go…
Offline
Re: [resolved] Local dump file from remote source: the harrowing journey
Btw, these problems happened a while back when upgrading to Yosemite. Just found this in the troubleshooting connecting to MySQL page:
If you upgraded MySQL, run the MySQL Upgrade script
Doing that:
$ mysql_upgrade
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck with default connection arguments
mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect
FATAL ERROR: Upgrade failedRunning through other checks now…
Offline
Re: [resolved] Local dump file from remote source: the harrowing journey
This page says I should be able to run this…
mysql> SELECT User, Host, Password FROM mysql.user;And get something like this:
+------+--------------------+----------+
| User | Host | Password |
+------+--------------------+----------+
| root | localhost | |
| root | myhost.example.com | |
| root | 127.0.0.1 | |
| | localhost | |
| | myhost.example.com | |
+------+--------------------+----------+But I just get this…
mysql> SELECT User, Host, Password FROM mysql.user;
ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'user'Bah! Something is blocking me from using root, and the root login won’t even work. I don’t get it.
Offline
Re: [resolved] Local dump file from remote source: the harrowing journey
Weird. It just started working. Dilemma over. ¯\_(ツ)_/¯
Offline
Re: [resolved] Local dump file from remote source: the harrowing journey
I was close to getting popcorn to read this thread…what a journey!
For info, and I appreciate you don’t want third-party software, SequelPro is very good.
Offline
Re: [resolved] Local dump file from remote source: the harrowing journey
Thanks. I rarely have a chance to use command-line anymore, so I like to try and keep what little skills I had on in memory.
Different question, though. Does anyone know why all the foreign accents in content (e.g., French) turn to gibberish when you upload a .sql dump file? I exported it with UTF-8 turned on. This always happens to me. I don’t get it. What’s going on?
Offline
Re: [resolved] Local dump file from remote source: the harrowing journey
Destry wrote #289286:
Does anyone know why all the foreign accents in content (e.g., French) turn to gibberish when you upload a .sql dump file? I exported it with UTF-8 turned on. This always happens to me. I don’t get it. What’s going on?
Are you using mysqldump to export, or something else? I tested an instance here with a body content of ùûüÿàâæçéèëïîôœ and it exported correctly with SequelPro:
INSERT INTO `textpattern` (`ID`, `Posted`, `Expires`, `AuthorID`, `LastMod`, `LastModID`, `Title`, `Title_html`, `Body`, `Body_html`, `Excerpt`, `Excerpt_html`, `Image`, `Category1`, `Category2`, `Annotate`, `AnnotateInvite`, `comments_count`, `Status`, `textile_body`, `textile_excerpt`, `Section`, `override_form`, `Keywords`, `url_title`, `custom_1`, `custom_2`, `custom_3`, `custom_4`, `custom_5`, `custom_6`, `custom_7`, `custom_8`, `custom_9`, `custom_10`, `uid`, `feed_time`)
VALUES
(1,'2015-03-22 20:46:48','0000-00-00 00:00:00','scratch','2015-03-22 20:46:48','scratch','banana','','ùûüÿàâæçéèëïîôœ',' <p>ùûüÿàâæçéèëïîôœ</p>','','','','','',0,'Comment',0,4,1,1,'articles','','','banana','','','','','','','','','','','ea5232cd17f8203e24447441dc8dc687','2015-03-22');My textpattern table has UTF-8 encoding and utf_general_ci collation – does that tally with yours?
Offline
Re: [resolved] Local dump file from remote source: the harrowing journey
Actually, I’m not sure I had the character problem to the extent I first thought. The first try was from a phpMyAdmin dump file. When first loaded, I had character problems in the UI that went away after updating the language files. But I still had gibberish after that in some article titles that had accented characters. This is what made me think there was a problem. There’s still a problem, but not nearly as bad if it’s just titles.
Next I exported a DB file via command-line. That resulted in exactly the same thing, but this time I noticed that while the gibberish remained in article titles, it wasn’t present on accented characters in the Excerpt field.
But then a new discovery, where I’m still struggling… Many of the articles are missing their Body field content, which of course is bad. They exist in the source site, but not in the site receiving the DB dump. But what’s weird is that the word counter says the words are still there, but they’re not. Not all article are like this, just the newer ones. Bizarre. Never seen that before.
Offline
Re: [resolved] Local dump file from remote source: the harrowing journey
Ugh. Weird. In fact it’s not working. Sorry for this unfocused thread. Let me try and center on the problem…
I can export a dump file from phpMyAdmin, or successfully (now) export a DB from command line (no 3rd-party tool needed, thanks anyway) after tunneling into the host, using:
mysqldump -u USER -p --default-character-set=utf8 DB_NAME -r DB_NAME.sqlor just the typical:
mysqldump -u USER -p DB_NAME > DB_NAME.sqlAnd I can upload any of those files to the local database using:
mysql -u USER -p DB_NAME < DB_NAME.sqlThe --default-character-set=utf8 and -r parts in that first command were suggested on StackOverflow to help enforce the data as UTF-8. But it didn’t do any good, apparently, because no matter which file I upload (via GUI or command line), my article content having accented (French) characters — or even just anywhere I’ve used a glyph from the glyph palette — turns into those black diamond question marks (which don’t show up here so I’m using double-Qs in there place), or whatever they’re called:
H⁇lo⁇se’s
Not only is that annoying as a unicode issue (I guess), but it makes keeping a local version of the site futile.
And that’s not the only problem that’s manifesting. After importing the DB locally, many article bodies are not showing in the admin side, but they do show on the front-side, with the character problems. The word counts are also shown in the admin-side, but the counts are off. For example, I’m working on a longform piece that’s currently an unedited draft at 15681 words (15541 + 140), but the locally-imported count says 15466 words (15326 + 140). It’s just a hunch, but I’m guessing the difference is represented by all the gibberish accents/glyphs. Nevertheless, I can’t see a single word in the Body field on the admin-side, so I couldn’t touch/edit this article if I wanted to.
The only thing I can think of is that the different versions of MySQL are causing the problem…
- Web host: Ver 14.14 Distrib 5.6.13, for solaris11 (i386) using EditLine wrapper
- Local: Ver 14.14 Distrib 5.5.18, for osx10.6 (i386) using readline 5.1
But I don’t know how to account for that aside from try and update the local install. Ugh. Maybe I should do a clean install of Yosemite (I just did an update before). Ugh.
If nobody has ideas about either problem, then I won’t be duplicating this site locally, unfortunately.
Last edited by Destry (2015-03-25 10:00:23)
Offline
Re: [resolved] Local dump file from remote source: the harrowing journey
It’s not ideal, but is it worth you creating a solaris11 VM on your Mac with the same specs?
Offline
Re: [resolved] Local dump file from remote source: the harrowing journey
Thanks, Pete, but all I want to do is write, while creating a handy duplicate/backup place for those times when I’m offline, and for the occasional need to tinker with the presentation in a safe place too. The idea of installing more software (bloat) to get something fundamental working — that should be working — seems like a bad path to take (to me). Indeed, I refuse to do it. I’d rather troubleshoot the problem; fix it at the source. I’ll probably try upgrading MySQL locally and see if that changes anything. If that doesn’t work, then I won’t be duplicating that particular site locally. ;)
Offline