Global LOCK priveledges?

[robertoschler]

How do I use SQLYog to edit a user's permissions to include GLOBAL TABLE LOCK priveledges? I'm getting an “access denied” error after a successful connect, when attempting a LOCK TABLES operation.

I looked through the options on the User Manager->Manager Permissions dialog and I couldn't find anything that seemed to apply.

thx

[Ritesh]

I assume that you are using MySQL 4.x. You have to execute SQL queries manually.

SQLyog doesn't support NEW PRIVILEGES introduced in MySQL 4.x. This feature is planned for SQLyog Max.

HTH

[robertoschler]

Ritesh,

Would you mind giving me a sample of the MySQL query that would grant these priveledges? I am using SQL 4.0.13 and I can't seem to find the right syntax. I tried:

GRANT LOCK TABLES ON test_db TO test_db_user@[ip_address];

Where [ip_addresss] is the actual ip address (no square brackets).

And I got an ILLEGAL GRANT/REVOKE command error.

thx

[Shadow]

You need to issue the command as GRANT LOCK TABLES ON test_db.* TO test_db_user@[ip_address];

[stumbler]

Ritesh wrote on Sep 22 2003, 05:39 PM:

I assume that you are using MySQL 4.x. You have to execute SQL queries manually.

SQLyog doesn't support NEW PRIVILEGES introduced in MySQL 4.x. This feature is planned for SQLyog Max.

HTH

Quote:

Please clarify:

In the case of REORDER COLUMNS, does this mean that SQLyog won't

perform the actions (create temporary tables, lock tables),

or does it mean that I must set the user permissions outside of SQLyog?

I'm using mysql 4.0.14b.

[Ritesh]

SQLyog will always issue “CREATE TEMPORARY..” etc. commands. It does not check for privileges.

[stumbler]

Ritesh,

Thanks for your reply.

I'm getting this error when attempting to use REORDER COLUMNS:

Error No. 1005

Can't create table 'c:windowstempmysqltmp#sqlfff75a5f_1_0.frm' (errno 140)

I am able to set that path in mysql,

and I can send an .sql file there using mysqldump,

so I can't understand the reason for the error.

Any suggestions?

[Ritesh]

SQLyog logs all the queries in the History window.

Can you tell me which query is returning the error?

[stumbler]

Ritesh,

here's the last query:

[10:04:41 PM][ 36 ms] create temporary table if not exists `productdb`.`sqlyog_12850`

( `sku` varchar(64) NOT NULL DEFAULT '' ,

`description` varchar(128) NOT NULL DEFAULT '' ,

`title` varchar(128) NOT NULL DEFAULT 'NEW' ,

`comment` text NULL ,

`price` decimal(12,2) NOT NULL DEFAULT '0.00' ,

`category` enum('simulated stone','misc') NULL DEFAULT 'misc' ,

`prod_group` enum('metal','glass') NOT NULL DEFAULT 'metal' ,

`nontaxable` char(3) NULL ,

`weight` varchar(12) NOT NULL DEFAULT '0' ,

`size` varchar(96) NULL ,

`color` varchar(96) NULL ,

`unit_of_size` enum('mm','in','ft','yd','ounce','gram') NULL DEFAULT 'mm' ,

`unit_count` smallint(6) NOT NULL DEFAULT '1' ,

`material` varchar(128) NULL ,

`drilling` enum('top','side') NULL DEFAULT 'top' ,

`holesize` decimal(2,1) NULL ,

`prod_dateadded` datetime NULL DEFAULT '0000-00-00 00:00:00' ,

`vendor_id` smallint(5) unsigned NOT NULL DEFAULT '5' ,

`vendor_stock_no` varchar(50) NULL ,

`disc_codes` varchar(32) NULL DEFAULT 'null' ,

primary key ( sku ) ,

FULLTEXT KEY `idx_cat` ( `category` ),

KEY `title` ( `title` ),

KEY `price` ( `price` ),

KEY `prod_group` ( `prod_group` ))

The error is:

Error no 1005

Can't create table 'c:windowstempmysqltmp#sqlfff9b905_1_4.frm' (errno 140)

[stumbler]

This turns out to be a RTFM problem.

When I changed the Category field to NOT NULL, SQLyog did re-order the columns.

[Author]

Posts