You need to restart mysql from the Linux command line like this:
service mysql restart --skip-grant-tables --skip-networking
If you are using Windows, add the two options in the mysqld section of my.ini:
[mysqld]
skip-grant-tables
skip-networking
and then run
net stop mysql (wait 10 seconds)
net start mysql
This will allow immedidate login without authentication and without any remoye connection sneaking in on you.
INSERT INTO mysql.user SET
Host='localhost',
User='mynewuser',
Password=PASSWORD('whateverpassword'),
Select_priv='Y',
Insert_priv='Y',
Update_priv='Y',
Delete_priv='Y',
Create_priv='Y',
Drop_priv='Y',
Reload_priv='Y',
Shutdown_priv='Y',
Process_priv='Y',
File_priv='Y',
Grant_priv='Y',
References_priv='Y',
Index_priv='Y',
Alter_priv='Y',
Show_db_priv='Y',
Super_priv='Y',
Create_tmp_table_priv='Y',
Lock_tables_priv='Y',
Execute_priv='Y',
Repl_slave_priv='Y',
Repl_client_priv='Y';
then
service mysql restart
At least you will have a localhost user named mynewuser with enough privileges to login.
You should then run this query:
select user,host,password from mysql.user;
and see if any new users were added by a hacker. If you see any remote users that should not be there, you can remove them with:
DELETE FROM mysql.user WHERE ...;
FLUSH PRIVILEGES;
If you are using Windows, please remember to remove skip-grants-tables and skip-networking from my.ini, then stop and start mysql service.
CAVEAT
Make a backup of /var/lib/mysql/mysql before you do anything.
UPDATE 2011-09-26 10:41 EDT
If the INSERT INTO mysql.user query does not work, it simply means the query is not compatible with the version of mysql I took this example from.
In order to know the correct INSERT query to user, please run this query:
desc mysql.user;
You will see all columns with the type ENUM('Y','N') for each privilege. Adjust the INSERT query to match all columns and then run that INSERT.
To get the exact columns to set to Y for all privileges, user this query:
SELECT CONCAT('SET ... ',GROUP_CONCAT(CONCAT(column_name,'=''Y'''),';'))
FROM information_schema.columns
WHERE table_schema='mysql'
AND table_name='user'
AND column_type='enum(''N'',''Y'')'
ORDER BY ORDINAL_POSITION\G
This will present to columns to set regardless of the version of MySQL you are using.
I follow the following rules for primary keys:
a) Should not have any business meaning - they should be totally independent of the application you are developing, therefore I go for numeric auto generated integers. However if you need additional columns to be unique then create unique indexes to support that
b) Should perform in joins - joining to varchars vs integers is about 2x to 3x slower as the length of the primary key grows, so you want to have your keys as integers. Since all computer systems are binary, I suspect its coz the string is changed to binary then compared with the others which is very slow
c) Use the smallest data type possible - if you expect your table to have very few columns say 52 US states, then use the smallest type possible maybe a CHAR(2) for the 2 digit code, but I would still go for a tinyint (128) for the column vs a big int which can go up to 2billion
Also you will have a challenge with cascading your changes from the primary keys to the other tables if for example the project name changes (which is not uncommon)
Go for sequential auto incrementing integers for your primary keys and gain the inbuilt efficiencies that database systems provide with support for changes in the future
Best Answer
Fixed it finally.
Was an issue with the I was using GROUP BY, ORDER BY and LIMIT, at least I think.
I changed it to the following and it's working smoothly now:
I ran into this issue before with my Database and forgot about it. There are some cards that are duplicates so I needed to remove them through the SQL code. In my original code it was hitting some duplicates after the 5th and breaking. Honestly not sure why (as the code worked in my back-end db), but it's working now.