33

I am creating a db in mysql for a java program.My program works well in my friends system.But I have some problem with my mysql.

The query is below:

mysql> create database sampledb;

Query OK, 1 row affected (0.00 sec)

mysql> use sampledb;

Database changed

mysql> create user zebronics identified by 'zebra123';

ERROR 1146 (42S02): Table 'mysql.user' doesn't exist

I cant create any user for my db.Please help??

Namphibian
  • 12,046
  • 7
  • 46
  • 76
Abilash Ravichandran
  • 419
  • 1
  • 7
  • 15
  • You can have a look at this link first : http://dev.mysql.com/doc/refman/5.1/en/create-table.html – Suresh Kamrushi Jul 22 '13 at 05:44
  • 1
    In my case, everything was intact (table `mysql` had not been *corrupted* or *dropped*) I was trying `SELECT * FROM mysql.user` from **command line** while **MySQL-Workbench** was running with the `Users and Privilege` section opened (basically the mysql.user table had been *locked* by `MySQL-Workbench`). Closing Workbench resolved the issue. I observed that the converse was also true: if i logged in as `root` from `shell` and then try to navigate to `Users and Privilege` section on Workbench, it would fail. – y2k-shubham Jan 23 '18 at 12:12
  • This helped me: https://stackoverflow.com/a/53554024/3177115 – ESP32 Nov 30 '18 at 09:07

10 Answers10

55

My solution was to run 

mysql_upgrade -u root

Scenario: I updated the MySQL version on my Mac with 'homebrew upgrade'. Afterwards, some stuff worked, but other commands raised the error described in the question.

Markus
  • 2,412
  • 29
  • 28
  • I actually the same problem when trying to secure mysql with `mysql_secure_installation` and received a message `Table 'mysql.role_edges' doesn't exist` – kostia Aug 28 '18 at 13:17
  • I'm a homebrew user and running `mysql_upgrade` was seemingly necessary when installing mysql 8.0.12. I was previously running mysql 8.0.11, and though I had tried to completely remove any remnants of 8.0.11 I imagine there was something lingering? Otherwise I suspect 8.0.12 may require `mysql_upgrade` for a different reason that I can't fathom at the moment. – Mark Fox Oct 09 '18 at 20:20
  • This was exactly what I needed after restoring a database dump from a much older version. Thanks! – uckelman Jun 09 '21 at 01:01
16

Looks like something is messed up with your MySQL installation. The mysql.user table should definitely exist. Try running the command below on your server to create the tables in the database called mysql:

mysql_install_db

If that doesn't work, maybe the permissions on your MySQL data directory are messed up. Look at a "known good" installation as a reference for what the permissions should be.

You could also try re-installing MySQL completely.

jnrbsn
  • 2,498
  • 1
  • 18
  • 25
12

Same issue here as lxxxvi describes. Running 

    mysql_upgrade -u root

allowed me to then successfully enter a password that

    mysql_secure_installation

was waiting for.

Matthias Nott
  • 301
  • 2
  • 4
  • with the last version of mysql, I got an error: `The mysql_upgrade client is now deprecated. The actions executed by the upgrade client are now done by the server. To upgrade, please start the new MySQL binary with the older data directory. Repairing user tables is done automatically. Restart is not required after upgrade. The upgrade process automatically starts on running a new MySQL binary with an older data directory. To avoid accidental upgrades, please use the --upgrade=NONE option with the MySQL binary. The option --upgrade=FORCE is also provided to run the server upgrade .......` – smerllo Dec 20 '20 at 14:25
9

Your database may be corrupt. Try to check if mysql.user exists:

use mysql;
select * from user;

If these are missing you can try recreating the tables by using

mysql_install_db

or you may have to clean (completely remove it) and reinstall MySQL.

Mark M
  • 1,580
  • 10
  • 22
  • After so many years problem still exists. I have the same problem and in my case `select * from user` does give you users but `mysql.user` is actually a view. I don't know how that was possible. `table_type:View, Engine: null, version: null...` this view was created from `global_priv` table. – temo Dec 04 '19 at 09:51
  • @temo That’s a new thing in MariaDB 10.4. – Janus Bahs Jacquet Dec 29 '19 at 10:23
  • @JanusBahsJacquet To me it was a password mismatch. After transfering DB server wrong password was used and it gave this exact error. After fixing that everything worked. – temo Dec 30 '19 at 12:27
  • @temo I meant that `mysql.user` being a view rather than a table is a new thing in MariaDB 10.4, a change from previous versions. :-) – Janus Bahs Jacquet Dec 30 '19 at 12:29
  • @JanusBahsJacquet I never denied that. I thought that change was the issue but it wasn't. – temo Dec 30 '19 at 14:30
  • @temo Ah, sorry – I misread your first comment. I thought you meant you didn’t know how it was possible that `mysql.user` was a view. I see what you meant now. – Janus Bahs Jacquet Dec 30 '19 at 14:33
3
 show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| datapass_schema    |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.05 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables
    -> ;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
23 rows in set (0.00 sec)

mysql> create user m identified by 'm';
Query OK, 0 rows affected (0.02 sec)

check for the database mysql and table user as shown above if that dosent work, your mysql installation is not proper.

use the below command as mention in other post to install tables again 

mysql_install_db
ManMohan Vyas
  • 4,004
  • 4
  • 27
  • 40
2

You can run the following query to check for the existance of the user table.

SELECT * FROM information_schema.TABLES
WHERE TABLE_NAME LIKE '%user%'

See if you can find a row with the following values in

mysql   user    BASE TABLE  MyISAM

If you cant find this table look at the following link to rebuild the database How to recover/recreate mysql's default 'mysql' database

Community
  • 1
  • 1
Namphibian
  • 12,046
  • 7
  • 46
  • 76
1

Try run mysqladmin reload, which is located in /usr/loca/mysql/bin/ on mac.

ray6080
  • 873
  • 2
  • 10
  • 25
1
'Error Code: 1046'.

This error shows that the table does not exist may sometimes be caused by having selected a different database and running a query referring to another table. The results indicates 'No database selected Select the default DB to be used by double-clicking its name in the SCHEMAS list in the sidebar'. Will solve the issue and it worked for me very well.

JohnKibika
  • 55
  • 1
  • 8
0

It sometime happens when you run the grant/ privileges query on an empty database

Raj
  • 1
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jul 12 '22 at 00:47
0

As of MySQL 8.0.16, mysql_upgrade is deprecated, you should use:

  • either --upgrade=FORCE while starting mysqld manually
  • or add upgrade=FORCE under [mysqld] section in your my.cnf and run service mysql restart (don't forget to remove the line in my.cnf once the server has restarted, to avoid the upgrade at restart)
Yvan
  • 2,539
  • 26
  • 28