MYSQL :: User Table And Version

User Privileges In Version 4.1.7(linux)
sql:
GRANT USAGE ON *.* TO 'shinestb'@'localhost' IDENTIFIED BY 'shinestb';
FLUSH PRIVIELGES;

an error return at login time:
./mysql -ushinestb -p
ERROR 1045 (28000): Access denied for user 'shinestb'@'localhost' (using password: NO

why? version < 4.1 is ok.

Unique Table Version Record
Unique table version record
Hello,
I am using a table record to store informations about database dump,I
use this table :

CREATE TABLE dump_version(
count BIGINT unsigned NOT NULL DEFAULT 0,
timestamp TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
DEFAULT 0
);
And I added a record (INSERT INTO dump_version () VALUES();) that I
modify each time I dump the database.
The only problem is that I can't ensure that this table will contain
only a single record, is that a way to ensure a king of 'UNIQUE' record
or something?

Syntax For Table Creation In Server Version 4.0.25
I was trying to install something in a php postnuke when I got a syntax error in the table creation of MySQL. The following is the error I recieved:

_PNC_DBCREATETABLEERROR - (users) - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMES

I was wondering if someone can take a look at the syntax I'm trying to use and offer a suggestion as to how to fix it. I have almost no knowledge of MySQL, The server version that my webhost is using is MySQL 4.0.25 standard, client version 4.1.10. Code:

Warning: Your Privilege Table Structure Seem To Be Older Than This MySQL Version!
I am using Yahoo's GeoCities Pro Hosting and when I get into "MyPHPadmin" to view my databases I get the following message.

Warning: Your privilege table structure seem to be older than this MySQL version!
Please run the script mysql_fix_privilege_tables that should be included in your MySQL server distribution to solve this problem!

How can I get to the script?? Can't find it.

Install New Version Of Mysql With Old Version Running
I would like to install the new versin of mysql but currently i have mysql version 3.23 running...

I need to have 2 version of mysql running at the moment in win2000 for different app.

what is the steps for me to install the new version of mysql without causing error and able to run concurrently.

Relating User Table With Content Table
I have a mysql table with the curriculum vitae (CV) information of about 50 researchers. I would like each researcher to be able to edit and update his/her information. In order to do this I need to create a membership system for the researchers to access their respective CV page.

Before I do anything I want to plan this out, hopefully with a little help of more experienced developers and programmers. So I am open to any ideas.

This is the thing that stumps me the most right now:

Right now I have a CV table (with all the curriuculum vitae information) and need to create a users table that will somehow link with the CV table. Eventually, what I would like is with a login and password (that they choose) they will automatically open their CV page. These are the questions I have:

What fields in the user table should I use to link the user table with the CV table?
Once that is established, how, by providing their login and password can I direct them to their own page to update? Should one of the fields in one of the tables contain the (already existing) address of their CV page?

Rolling Back A DB From Version 5 To Version 4
I've got a local MySQL DB with my MediaWiki data that was created in version 5.0.17, but I recently discovered that my webhost is running version 4.0.27, and when I tried to import the database as-is, I received an error.

I'm looking for suggestions as to how I could roll my database back to the older version of MySQL, in case my webhost decides not to let me upgrade. I've considered using an intermediary, such as converting to MDB or CSV, but so far I haven't had much luck with finding a tool to do so without spending a pile o' cash.

User Table In 5.0
I have a question about the "grande" entries in the 'user' table of
MySql 5.0.0-alpha.What is this?
Also, why are there two rows with "root" (one with localhost and one
with "grande")? Why two entries?

Master Master Replication, Truncate Table Fails, Version 5.0.22
I have set up a master master replication link between two databases, prototyping an idea. Anyhow, adding records and deleting works, both get updated, creating and dropping tables works, but truncating tables doesn't work.

My main questions are, why doesn't truncate work, and if it doesn't work, what else doesn't work across a master-master replication link?

Question About User Table In 5.0
I have a question about the "grande" entries in the 'user' table of
MySql 5.0.0-alpha.

What is this?

Also, why are there two rows with "root" (one with localhost and one
with "grande")? Why two entries?

Mysql User Table?
I have number of customer and they should have different access levels, some would access only few pages. I thought if i create a table:users in my database: maindata-> table: loginuser: will solve the problem, but something is not right when i saw the default mysql user table.

maindata->table loginuser: columns are
user id autoincrement
userfname varchar
userlname varchar
email varchar
userurl varchar
accessgroup (tinyint) = 1,2,3,4
Depending on this value they will access the specific url with.

However after reading code below, I am bit confused about storing all users, should i use default mysql user table cause it has got all privaleges by default ie., insert, del, modify etc. and sounds more secured cause if by mistake users can type admin.php url? I am new to all this and sort of confused.

PHP Code: ....

Mysql User Table For Authentication
What is the best way to store usernames/password information within a mysql Table? Is there a way to encrypt the columns that house this data?

Sorting A Table According To User Criteria.
I was looking for a some kind of simple solution to this issue. The application requires that certain tables should be showed ordered by the user criteria, for example categories. the user will insert a new category and then he could move up or down the item to the position he wants to show it.

In order to do this, I was thinking to add a column, let's call it 'sort_id' this column will save the position of the record. (this is the best i could come up with, I would love any suggestions). This might create some concurrency problems, but since this is an administration module, I don't think more than one would modify the same table at the same time. Anyway, I couldn't find an easy way to insert a new record in the table with the last position + 1.

Usernames And Password In User Table
I had multiple rows in 'User' table in mysql DB.

namely
% root <no password>
localhost root <no password>
% <null> <null> //anonymous user

Now i want to connect to the server through my application.

I deleted anonymous user, and entry for % root
and set password for localhost root.
Then i created a normal user
'% testuser testpwd' with all the previliges.

So that if i want to connect to the server from any other machine than server, i have to use 'testuser' account. In my application i am connecting to server using 'testuser'.

Database - User Table - Propagation
I've started a new website and I want to have a database that works with all my other user tables in my other databases. This is my idea.

Background. Example Setup Tree
--Main Page:
-->Forums
-->Mail
-->Collaboration
-->Bug Reporting
-->Etcetera
-->Etcetera
-->Etcetera

*NOTE: Each subsection has it's own database with their own user tables

-------------------------------------------------------------------------------

UserX visits my site. There's a register screen and UserX performs the standard Registration which is then added to the userdb in the usertable.

I want to run a query that will update all subsequent user tables of their own databases.

So, one query or multiple queries will register UserX's info into all those databases.

Once the person is logged into the site, the don't need to register nor login into the other portions of my site, they just click on 'said' link and have access to that portion of the site.

Creating A User-defined Table
I am wanting to create a user defined table in mySQL. The general idea is that they all would have an asset number (PK). After that, the items can be different.

For instance, I may be keeping track of a table, in paticular it's color, size, and cost. OR I might keep track of a computer system, the OS it has installed, who owns it, etc.

Specifying A User To Be Able To GRANT On A Specific Table
I tried giving said user GRANT power on a table, but it isn't working.

He currently has SELECT-only on this table, and nothing more.

the command: GRANT GRANT OPTION ON db.table TO username

Any idea why it isn't working?

Restore Default User Table
For some reason the mysql database does not contain any tables;

How do i restore the default user table?

I tryed to reinstall mysql:

yum remove mysql httpd php php-mysql mysql mysql-server

yum install mysql httpd php php-mysql mysql mysql-server

service mysqld start

[root@localhost] ~ : mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 30 to server version: 4.1.20

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> show databases;
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
2 rows in set (0.00 sec)

mysql> use mysql;
Database changed
mysql> show tables;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'mysql'
mysql>

But i still dont have permission to create a new database, cause mysql database have no tables.

Configure 2 Sites To Access One User Table
I have two databases on the same server with different domains. I would like both sites to access the same user database table. They have the same fields.

I thought that it might just be done with an mysql command, which will point one table to the other.

Create User Table With An Encrypted Variable
I'm trying to come up with a user database for logging onto my server where the passwords stored in the table under the column "password" are encrypted. I also need another script I have to understand when it's been 30 days since the password was changed, so I need to capture the date the password is changed. I've looked at a few reference manuals and tried the code:
CODEcreate database auth;
use auth;
create table auth (
name varchar(7) not null,
pass varchar(9) ENCRYPT not null,
fullname varchar(30) not null,
date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
deptnumber varchar(4),
phonenumber varchar(10),
cellnumber varchar(10),
primary key (name)
);
insert into auth values
('name', ENCRYPT 'pass', 'fullname', 'NOW()', 'deptnumber', 'phonenumber', 'cellnumber', );

insert into auth values
( 'someuser', ENCRYPT ('test12345'), 'Joe User', 'NOW()', '3421', '504 257-0000', 'none' );

Table Creation Based On User Login
I'm trying to build a website that will allow the addition of users (w/ username, password, etc.).

That seems simple enough.

But is it possible to set something up (in PHP, for example) that will create a new table in the database for each new user?

Ex. John Smith signs up, is verified, and a PHP script (or Java, or whatever) creates a table specifically for him.

I know I'll need a master table of usernames, passwords, etc. These new tables would store certain user-specific data, such as preferences and other data the user might want to restrict access to.

I know I could store all of this information in a master table, but I'm trying to think ahead. Worst case scenario, if there are millions of users (unlikely, but not impossible), I'd hate to have all that data in one table, but if I have to, I can.

Error 1036: Table 'user' Is Read Only
For some odd reason, the user table that contains all the priveleges and such is read only... I can't write to it... thus I can't change my password or permissions or anything of the sort.

I've tried reinstalling the databases, but no luck, same thing... i checked permissions on /var/lib/mysql/mysql/ and all the files under it and i gave write access 2 mysql.mysql... so i dont know whats going on.

Finding Field In Table That Matches User Supplied Input
I've come across match/against, field, and locate. I've been unable to use these to match against columns.

As an example, I have a table with two columns, an index number and a domain name. The user supplies a FQDN (fully qualified domain name). I would like to compare all of the domainname rows to see if they match against the FQDN and return a go/no-go value.

The match function is close, but works in reverse. I cannot see a way to get locate or field to work with fields.

Root User Denied Access To Fetch User List.
I can still log onto MySQL administrator or the command line program but I somehow appear to have lost all priveliges to alter or view the user list from this account. Even under the command line interface I can login but it tells me access is denied when I try to alter "root" permissions on a database.

I cannot seem to log onto any of the other accounts created but obviously if I could get into the user admin I could solve this.

Retrive Category Names Where Categoryid Is Different From User To User
I am trying to retrive category names where categoryid is different from user to user

I know LIKE works with one int(1), but I want it to work with more ints (1,3,5)
SELECT * FROM `categories` WHERE categoryid LIKE (1) Working

SELECT * FROM `categories` WHERE categoryid LIKE ( 1, 5 ) Not working

Copy User Privileges To A Newly Created User.
I have just created a user with specific permissions to certain tables
in different databases. Now I have to create serveral more users with
the same permission. Is there a command in MySQL that allows me to
create the new user by copying the privileges from a template user?

Deleted Root User From 'user' Database!
I so stupidly deleted the root user from my mysql > user database. Now I can't login to mysql. I uninstalled the rpm and reinstalled it but it still won't let me setup a new root user. How can I delete the mysql data files so that I start again, fresh? Where are these files located? I can't find them anywhere after removing the RPM but they are obviously there because upon reinstalling the RPM I'm still in the same position--I try to do

mysqladmin -u root password 'password'

but it says "Access denied for user root@localhost"

Deleted Root User From Mysql.user !!!
While trying to grant access to root user, i deleted all entries for root from mysql.user.

I've tried to use root% (anonimous built-in administrator user) but to no good).

Is there another admin user i could use to run mysql monitor and insert root records to mysql.user? I've read

Could I use a copy of the mysql.user table?

I'm using version 5.0.16-standard on Linux Fedora Core 3.

Before ruinin the root user, i made a backup of a databasefrom the same server (not mysql database); i was wondering if that backup contains the mysql.user information somehow?

User@localhost And User@domain.com
I have been following a tutorial for a mail server which uses mysql, mainly postfix

all through the tutorial i have been asked to put a user and password in the files for the database..

which i have done

root and pass1

in the log files the error shows as

warning access denied for user root@domain.com ( using password : yes )

when i log on from the shell, to mysql

and purpously get the password wrong, I get
access denied for user root@localhost ( using password : no )

i have checked the password is correct..

is the part after the @ the issue, and how can i tell mysql that root@domainname.com is a valid user???

Selecting User,password From User
When I do: select User,Password from user in MySQL, I get encrypted password. How can I see the real value?

And, what is the proper (secure) way to access MySQL database from PHP or other scripting languages?

User Problems... Only Root User "works".
i have been trying to figure this strange problem out for a week or so. here it is.

i have a set of users with various permissions for different reasons. about 2 weeks ago, my hardware suffered a fairly nasty failure. i recovered everything. things seemed to work out. i didnt think to check my phpBB forums or my blog (or other mysql based stuff). never had any problems with the DB... so, maybe i am missing something simple here. i hope so.

what my users can do:

they can log in via command line, a gui i use on mac os x and it does not matter if it is local, or remote. they work just fine.

what all but one users cannot do:

none of my users can run phpbb or the blog stuff.. except for the root user. any other user returns the error message written in the code of which ever script i try to access telling me that "could not connect to DB" etc..

root works fine.

now, i thought maybe it was a problem with the forums and other stuff. but i cant be. same thing happens with all things... even a game server i occasionally run that keeps account info in a DB.

all these users are set up properly and have worked just fine in the past. nothing new has been installed, no conf changes..im at a loss...

MYSQL User Information: Error While Storing The User Information
I have a proplem creating a new user and/or adding additional
information for root user in MySQL Administrator 1.0.19. When I do I
get this message: error while storing the user information.

Never the less, when I close the Admin program and open it again, the
changes I tryed to make are saved, (althoug the program asked me if I
wanted to save changes and I said no)

Is this a know bug, should I be alarmed, any solutions ?

I´m running
MySQL 4.3.11
Admin 1.0.19
Query 1.1.5
Apache2
php5

Version 3 And 4 On Same Box
I have a box that is currently using Version 3.23 of MySQL. I want to install a version 4 along with version 4. This will be running on a windows machine. Does anyone know if this is possible or will it cause problems for the already installed version 3?

Will any shared dlls be over written for example?

Version 5 To 4
We did some work on a mysql DB version 5. We now need to put it on the live server. However, the live server runs version 4.
IT does not want to put two versions on the server and will not upgrade to version 5. If I try to create a backup using compatability mode i get MySQL Error Nr.0
what is the best way for me to be able to get this database converted to a version 4 compatable instance.

Version Of PHP
suggest me that which latest versions are best and work properly with each other without having lots of problems.

Version
HOW TO FIND THE VERSION OF MYSQL IS BEING USED IN SYSTEM?
i AM USING "FEDORA CORE 3 " AS OS.

Version 5
I have a new application I'm working on and it would be *so* much simpler to have views available. Is anyone using 5.0 extensively and able to comment on its stability? I'd have to put some non-mission critical but still production databases into 5.0 and I don't want to have my data wandering away

Version
I tried to upgrad mysql v4.0 to v4.1 through cpanel/WHM and my user account cpanels are still showing the version as 4.0.25. How do I check which version is actually on there

Version 3.51. To 4
i just installed over the top, is this ok???? coz i have now 2 unistall options in windows install/unistall panel

Version
MySQL 4.0 or MySQL 4.1?
MySQL 4.1 supports subquery but it is still a beta version.

Version 5.0
I have MySQL version 5.0.17-nt installed in my localhost testing environment. I have run into a problem installing some software that uses MySQL. The software people have only tested their software with version 4.0.18 and nothing later.
Is it plausable that the software tested at version 4.0.18 has some incompatabilities with MySQL version 5.0.17?
If so, is there anything I can do (configuration, etc) with my version 5.0.17 server to smooth out these wrinkles?

Version 5.0
mysql version 4.1 has been in production status since october, 2004
if you're not on 4.1 yet, and you post a problem and wish to receive a solution involving sql, it is your responsibility to mention which version you're on
otherwise, you might get a solution using sql that works only in 4.1

Version 4
I have been trying to change the query below to work in version 4.0.x of MySQL (it works in version 5).

Code:

SELECT *
FROM Categories
WHERE Cat_ID NOT IN (SELECT DISTINCT Join_Cat.Cat_ID
FROM Join_Cat WHERE Join_Cat.Comp_ID = 1)

(The above returns all 'Categories' a 'Company' is not in)

New Version
I was using a previous version of MySQL before.(4.0.21) Then I downloaded version 5.1.2. I use the Windows Version for Windows 2003 server with ASP codes. What I was basicly doing is using forms to enter new info to the database and editing them by form again. On the editing page I was bringing the current values to the forms by giving the default values of the fields ( ors.Fields("propname").value ) But with this last version I have problem. It brings only the first word of the record to the form field. I could not understand why.

Changing User From One User To Another
Currently i have 2 Users in My MySQL(root,user1)...Whenever i am clicking command prompt Client its directly taking me to root user. If i want to change user (to user1) from there how i need to proceed...(Like connect in Oracle what we need to be use in mysql.

Version Problems
I'm getting the following error and I have 5.0.21 on my server.
CREATE TABLE cms_bbattachments_desc(
attach_id mediumint( 8 ) unsigned NOT NULL AUTO_INCREMENT ,
physical_filename varchar( 255 ) NOT NULL ,
real_filename varchar( 255 ) NOT NULL ,
download_count mediumint( 8 ) unsigned DEFAULT Ɔ' NOT NULL ,
COMMENT varchar( 255 ) ,
extension varchar( 100 ) ,
mimetype varchar( 100 ) ,
filesize int( 11 ) DEFAULT Ɔ' NOT NULL ,
filetime int( 11 ) DEFAULT Ɔ' NOT NULL ,
thumbnail tinyint( 1 ) DEFAULT Ɔ' NOT NULL ,
PRIMARY KEY ( attach_id ) ,
KEY filetime( filetime ) ,
KEY physical_filename( 0 ) ,
KEY filesize( filesize )
);
MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near Ɔ),
KEY filesize (filesize)
)' at line 14
The link to the whole sql file is
http://cmuench.com/sql.sql
It was version 4 and it won't let me insert it in a version 5.0.21

Using Mysqldump On Version 3.23
I need to upgrade my company's version of mysql. I'd like to backup the databases (20+) before attempting that, however when I try to use mysqldump nothing happens.
I've tried the following command line code to test out backing up one database, but nothing happens... no error or anything........

Version Conflict
I've created a stored proc using MySQL Administrator Ver 1.1.9 which runs fine in MySQL Query Browser 1.1.20. However, when I use it in a C++ program it gives me the following error:

[MySQL][ODBC 3.51 Driver][mysqld-5.0.22-community-nt]You have an error in your SQL syntax; check the manual that corresponds t
your MySQL server version for the right syntax to use near'{0= call 'GetAllJobDetails2'}at line 1.

The first line in the stored proc is:

CREATE DEFINER=`regan`@`localhost` PROCEDURE `GetAllJobDetails2`()

That is what the MySQL Administrator put in when I created it.