1

I'm trying to do some queries, but I don't have any privilege whatsoever. I can't understand why - it seemed to be working fine just a few weeks ago.

When I log in as 'root' with superuser (sudo mysql -u root -p) I still don't have any privileges and get error 1227 while trying to create an "admin" user.

When I login normally I end up with an anonymous user ''@'localhost', and don't have any privileges also.

I only have the Information_Schema database (and test), and using Sequel Pro I can see the USER_PRIVILEGES table, with only the anonymous user and with USAGE privileges only.

I'm basically stuck I can't do anything with my databases. I've read thousands of posts, but just can't seem to find an answer.

Tried the SELECT USER(); and then GRANT ALL *.* but I also get access denied.

Basically, I've been getting error 1044 and 1227 constantly.

Any idea on how to make a admin user with all privileges possible? MariaDB is installed on my personal computer (MacOSX), so I would like to have all possible accesses.

Thanks a lot

EDIT: When stopping server with mysql.server stop and trying to skip tables with: mysqld --skip-grant-tables I go into an infinite loop of: 

[ERROR] InnoDB: Unable to lock ./ibdata1 error: 35
[Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files
lowercase00
  • 1,917
  • 3
  • 16
  • 36
  • Apparently fixed by reinstalling using brew. See t[his comment](https://stackoverflow.com/questions/47522777/mariadb-both-root-and-without-privileges-access-denied-1044-1227#comment82003007_47523027). – robinCTS Nov 28 '17 at 08:07

2 Answers2

0

Try stopping mariadb then starting with option to skip the grant tables; see:

How to start MySQL with --skip-grant-tables?

morissette
  • 1,071
  • 1
  • 8
  • 29
  • Editing my question, I tried that but go into an infinite loop of: " 2017-11-28 0:19:25 140737214337984 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 35 2017-11-28 0:19:25 140737214337984 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files – lowercase00 Nov 28 '17 at 02:19
  • What does SHOW GRANTS look like? – morissette Nov 28 '17 at 02:20
  • +--------------------------------------+ | Grants for @localhost | +--------------------------------------+ | GRANT USAGE ON *.* TO ''@'localhost' | +--------------------------------------+ 1 row in set (0.00 sec) – lowercase00 Nov 28 '17 at 02:22
  • Sounds like you have multiple databases using the same innodb files; do a ps auwxf | grep -i sql after you stop the server. If nothing is shown then do a lsof | grep ibdata and see if anything is using those files – morissette Nov 28 '17 at 02:26
  • Note: I am on Linux so OSX may vary in implementation – morissette Nov 28 '17 at 02:26
  • At which point you should just dump your dbs run docker and docker pull mariadb and call it a day :) – morissette Nov 28 '17 at 02:27
  • `ps auwx | grep -i sql` I get: Carvalho 4993 0.0 0.9 2932892 76660 ?? S 12:17AM 0:00.59 mysqld --skip-grant-tables Carvalho 5152 0.0 0.0 2442612 552 s000 R+ 12:28AM 0:00.00 grep -i sql – lowercase00 Nov 28 '17 at 02:29
  • So probably the best thing would be to just try to uninstall and install again, right? I suppose I could use Homebrew installation instead of a Docker container? (never used Docker before) – lowercase00 Nov 28 '17 at 02:31
  • Docker would be way easier; same with homebrew – morissette Nov 28 '17 at 02:32
0

But you could kill -15 that sql process then restart with skip grants

Totally meant to make this a comment - sorry

morissette
  • 1,071
  • 1
  • 8
  • 29
  • I guess I just deleted everything using this link (https://gist.github.com/vitorbritto/0555879fe4414d18569d)... I'll restart my pc and see how Docker works.... thanks a lot! – lowercase00 Nov 28 '17 at 02:35
  • just for reference, reinstalled using brew, and it turned out that a table called MySQL appeared... I'm guessing that was what was causing my privileges problems. Just can't figure out how it was deleted. anyway, is working, thanks a lot! – lowercase00 Nov 28 '17 at 03:16