MYSQL :: Trying Abort One Select Accessing One Table Locked By Lock Table.

List A Locked Table
I try to Lock and unlock certain tables from my database test using JConnector (mysql jdbc lattest version) and I found out that the unlock command is not actually works. Now my database is occupied by these locked tables. How do I see a list of Locked tables in mysql and How do I get rid of that so I can unlock the tables.

Access > Odbc > Mysql | Locked Table?
i have a mysql db and an odbc connection setup to it under user root. i need the odbc connection so i can have connectivity in access 2000. select queries work fine. i can view the tables in access etc.

however update queries won't work for me. i know last week it worked, and this evening after it not working i opened up another copy of the database and it worked on that one... but only once, i went back and it didn't work in that one either.

and for the life of me i cannot figure out what would be different with the database now.
the error i'm getting is just the generic: "can not update 3 records due to lock violations"

however, what is locking odbc/access out of the mysql db? is it mysql? is it odbc? or is access locking the records? im sure it isn't mysql, and i think it is odbc but i don't know where. i downloded new drivers for odbc. i thought about updating the access jet engine but couldn't find updates... Code:

How To Find Out If A Msql Table Is Locked From A Php Script
Is there any way with a php to find out if a table is locked before using a mysql_query on the table?

Accessing A Table
I've been using a simple table within a database for a year or so now without any problems. It has an index called 'id' (int(11)) which increments every time a new record is added, 10 columns of various types and probably around 3 million records. Recently this table has stopped responding.
I can:
describe the table
see the table using 'show tables'
perform all normal operations on all other tables in the database

I can't:
update the table
check the table (I think - it has been running for an hour with no response)
select any data from the table, even something simple like max(id)

Does anyone know what the problem could be?

Accessing A Table Line
I have a table in my databasee called tablebob. tablebob has three fields (idnumber, address, other). the table is organized by the idnumber. The problem is I don't know the idnumber.

Please Note: because of responce time problems, I can NOT have the php page search through the entire table, and check the contents of each row one by one. I need to submit a quary saying something to the affect of "select from tablebob a row where address = www.google.com"

Lock On Table
If I do:

1) mysql_query('LOCK TABLE tablename');

2) mysql_query('SELECT * from tablename where condition=0');

3) if (cond) {

4) mysql_query('UPDATE tablename SET condition=1 WHERE condition=0');

5) }

6) mysql_query('UNLOCK TABLE tablename');

Lock Table?
I run two perl scripting programms simultaneously to collect some
information on internet, and insert the collected data into the same mysql
table (just one table). i was wonder if i need to lock this table when one
scripting programm inserts data, or it really does not matter in this case.

Table Lock
I'm writing a backup script for my database. I use SHOW KEYS and SHOW FIELDS to have the information I need to write the INSERT and CREATE inctruction, but after using show intructions I have my tables as if they were locked. If I try to drop a table I have this message bacK: Error on delete of '.Borghicategorie.MYI' (Errcode: 13).

Error Accessing MS Access Table
In order to perform a hetrogeneous join (I think that's what they're called)
between MySQL and another database, I've created an Access database
containing linked tables from each datasource.

I create the linked tables without any problems, clicking on "save password"
before selecting the desired tables. I can then access both tables fine from
within the Access GUI.

The problem starts when I then try to access this data from my ASP.NET web
application - classic ASP works fine but .NET gives me...

ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] ODBC--connection to
'MyDSN' failed.

From then on, the Access database remains locked (.LDB file in the same
directory) and I get a similar error when I run my ASP page, which
previously worked.

Lock Table For Insertion
Here is a stored procedure to add a new customer to my customers table. I want to be sure that name doesn't already exists in customers table. If yes then I want to return an error code, if no then I want to insert the new customer.

My question is defined the following piece of code :

CREATE DEFINER=`root`@`localhost` PROCEDURE `spCustomerAdd`(IN NAME VARCHAR(50), IN PWD VARCHAR(45), OUT NEW_ID INT, OUT ERROR_CODE INT)
BEGIN

# LOCK Customer table ??? Code:

File Or Table Lock
I'm developing a web application that uses mysql for it's data source. Eventually, there will be over 2 million records. The web site visitors will be almost always reading from the tables via predefined queries written in PERL. What type of locking mechanisms do I need, if any? Row locks, table locks?

Accessing Floating Point Data From Table...
I have a table and from a column in the table i'm accessing values. I found that only floating point data is not accessible. For example if i issue this below query it gives empty set.

select * from audio where audio_sr=44.1;

whereas when given as below,

select * from audio;

i can see rows having 44.1 as data value in audio_sr column.

Whats the problem please help.. I'm using mysql version 5.0.18 software.

Error Accessing MS Access Table Linked To MySQL
I create the linked tables without any problems, clicking on "save password"
before selecting the desired tables. I can then access both tables fine from
within the Access GUI.

The problem starts when I then try to access this data from my ASP.NET web
application - classic ASP works fine but .NET gives me...

ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] ODBC--connection to
'MyDSN' failed.

From then on, the Access database remains locked (.LDB file in the same
directory) and I get a similar error when I run my ASP page, which
previously worked. Code:

Lock InnoDB Table For Reading Having AUTOCOMMIT=1
I'm using MySQL 4.1.7 and InnoDB tables. I'd like to lock some tables for (write and) read of other users during a transaction, like this:

START TRANSACTION;
LOCK TABLES T1, T2;
...
...
UNLOCK TABLES T1, T2;
COMMIT;

Since I have AUTOCOMMIT=1, this does not work: "You should not have AUTOCOMMIT = 1, because then InnoDB releases its table lock immediately after the call of LOCK TABLES" (according to http://dev.mysql.com/doc/mysql/en/lock-tables.html).

Do I have an alternative, having AUTOCOMMIT=1, to lock tables for read access during a transaction? I considered SELECT * FROM table FOR UPDATE, but this does not seem to perform a read lock.

Grant Lock Tables On Single Table Gives ERROR 1144
I can't understand why mysql allows me to grant lock tables to all
tables in a particular database, while it fails to accept the same
command applied to a single table only. I've tested on MySQL versions
4.1.20 and 5.0.33 (both running CentOS 4.4 linux on Intel-32) with
exact same results.

Whats wrong with grant lock tables on a single table?

mysqlSELECT VERSION()G
*************************** 1. row ***************************
VERSION(): 4.1.20
1 row in set (0.00 sec)

mysqlSELECT CURRENT_USER()G
*************************** 1. row ***************************
CURRENT_USER(): root@localhost
1 row in set (0.00 sec)

mysqlCREATE DATABASE grant_test;
Query OK, 1 row affected (0.00 sec)

mysqlUSE grant_test;
Database changed
mysqlCREATE TABLE t (i INTEGER PRIMARY KEY NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysqlGRANT SELECT ON grant_test.t TO grant_test_user IDENTIFIED BY
'Ahelluwapassw0rd';
Query OK, 0 rows affected (0.00 sec)

mysqlGRANT LOCK TABLES ON grant_test.t TO grant_test_user IDENTIFIED
BY 'Ahelluwapassw0rd';
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the
manual to see which privileges can be used
mysqlGRANT LOCK TABLES ON grant_test.* TO grant_test_user IDENTIFIED
BY 'Ahelluwapassw0rd';
Query OK, 0 rows affected (0.00 sec)

The Total Number Of Locks Exceeds The Lock Table Size
I have a quite large table (about 70 mln rows, MySQL 5.0.18 on a linux box) and when i try to delete some records from it I started getting this message:
Quote:#HY000The total number of locks exceeds the lock table size

Grant Lock Tables On Single Table Gives ERROR 1144
Tested on MySQL 4.1.20 and 5.0.33 with similar results. I don't
understand why I cannot grant LOCK TABLES on a single table, while
granting LOCK TABLES on all tables work fine.

mysqlSELECT VERSION()G
*************************** 1. row ***************************
VERSION(): 4.1.20
1 row in set (0.00 sec)

mysqlSELECT CURRENT_USER()G
*************************** 1. row ***************************
CURRENT_USER(): root@localhost
1 row in set (0.02 sec)

mysqlCREATE DATABASE grant_test;
Query OK, 1 row affected (0.04 sec)

mysqlUSE grant_test;
Database changed
mysqlCREATE TABLE t (i INTEGER PRIMARY KEY NOT NULL);
Query OK, 0 rows affected (0.02 sec)

mysqlGRANT SELECT ON grant_test.t TO grant_test_user IDENTIFIED BY
'foobar';
Query OK, 0 rows affected (0.12 sec)

mysqlGRANT LOCK TABLES ON grant_test.t TO grant_test_user IDENTIFIED
BY 'foobar';

ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the
manual to see which privileges can be used

mysqlGRANT LOCK TABLES ON grant_test.* TO grant_test_user IDENTIFIED
BY 'foobar';

Query OK, 0 rows affected (0.03 sec)

Rights To Create Table, Select, Then Drop Table..
I have a need to get data from the db that requires me to=20

1) do a select and create a new table with the results=20
2) run a query against that new table=20
3) drop the new table=20

I have a script on my server that does this using the root account that has all on *.* for the db. It works fine.=20

I now want to get these results on a web page.=20 I want to create a new db user for my .php web page to use to connect to the db that only has the needed priviledges on that specific db to get the job done.=20

what priviledges do I need to give that user?=20

currently I have the following but the user can't even log into the db from the command line..=20

mysql> show grants for user;=20
+-----------------------------------------------------------------------
---------+=20
| Grants for user@% |=20
+-----------------------------------------------------------------------
---------+=20
| GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD
'6fe4c0ab2cf30ae3' |=20
| GRANT SELECT, INSERT, UPDATE, CREATE, DROP ON `db1`.* TO 'user'@'%' |=20
+-----------------------------------------------------------------------
---------+=20
2 rows in set (0.00 sec)=20

when I do a "show grants for user", what should I see to allow what I want?

SELECT From A Table Depending On A Result From Another Table
This may sound a bit weird so i'll explain:

I have three tables:

tbl1foobar
------------------
ididid
type enum('foo', 'bar')infoinfo
typeid

Now I want to get the info from either foo or bar depending on tbl1.type
Is it possible to do this in one select statement?

Table Design Question? House Table, Owner Table, Code Violations Table - Best Way?
Given the tables:

HOUSE
house_ID
address

OWNER
owner_ID
name
telephone...

HOUSE_OWNER_JOIN
?

CODE_VIOLATION_HISTORY
house_ID
violation_ID
violationStatement
...

My goal is to be able to track code violations of the house PER owner.

For example, I need to display a page that shows the current house with it's coe violations and a link to show the HOUSE's history of violation regardless of owner, Like:

House 1009283
Address
Past history (link to the following)

House History
2001-01-04 Owner: John Smith Code Violation: Gutter issue
1999-06-01 Owner: John Smith Code Violation: Faulty Steps
1998-03-02 Owner: Sam Spade Code Violation: Driveway carcks
1990-01-12 Owner: Keith Sledge Code Violation: Grass untidy

For the design of the HOUSE_OWNER_JOIN table, I thought of two ways I could go on this and this is where I need your help.

Option 1:
Have the HOUSE_OWNER_JOIN table keep dates so I can track the ownership changes that way:

HOUSE_OWNER_JOIN
houseID
ownerID
dateOwnershipBegan
dateOwnershipEnded

then I could look up all code violations by date and associate them with their rightful owner.

==================================================
Option 2:
Have the HOUSE_OWNER_JOIN table be the primary keeper of identity data by adding a new primary key and changing the CODE_VIOLATION_HISTORY table to reference that table by chaning the referencing key from house_ID to house_owner_ID:

HOUSE_OWNER_JOIN
house_owner_ID
houseID
ownerID
dateOwnershipBegan
dateOwnershipEnded

CODE_VIOLATION_HISTORY
house_owner_ID
violationStatement
...

ERROR 1206 (HY000) At Line 41: "The Total Number Of Locks Exceeds The Lock Table Size"
I'm loading a large file into a table, and I get this error:

"ERROR 1206 (HY000) at line 41: The total number of locks exceeds the lock table size"

I already searched, and the only advice given was to do an explict full-table lock on that table before loading to avoid the row-by-row locks filling up the lock table. Well, I tried that, and it's not helping either.

Here's my log:

Select All Elements In One Table That Do Not Appear In Another Table
Say that I have a table A with column colA and table B with colB.

How do I select all elements of colA in table A that are not in colB of table B?

Select All Records From One Table That Does Not Appear In Another Table
I need to select all records from tableA that does not appear in tableB. I am using the following query that does work but is very very slow. Is there anything i can do to speed up the query?

select tableA.ID, tableA.Name, tableA.Surname
from tableA where tableA.ID != ALL (select tableB.ID FROM tableB where tableB.status = 'inserted' or tableB.status= 'edited' or tableB.status = 'deleted' );

I have as example 6000 records in tableA and 2000 records in tableB. tableB is used to track which records have been inserted, edited or deleted from another system so the query should return all records that do not exist in the other system yet.

This query runs for about 3mins and just gets slower the more data there is.
Can anyone suggest anything that could make this a sub second response?

How To Acquire Lock With Select
I have a project at hand. It need the application to acquire lock on a row while it is being read so that no two persons can read one row simultaneously.

Can mysql offer this locking mode? Or should I move this feature to the application level instead of the db level.

Accessing Multiple Tables With A Select Statement
I am writing a select statement, that says something to this effect. Select * from texas, california, oklahoma; Everything in each table is going to be the exact same. as far as city, name, and zip...they are the titles of my fields. But when I go and look at this file, it just shows everyone from oklahoma. If take out out oklahoma, then it shows everyone from california but not texas. What is the proper way to do this?

SELECT INNER JOIN Performance VS Single Table SELECT
I have designed my database using a somewhat oriented approach. Rows are objects, and different type of objects are in different tables, but since my objects share a common set of fields like ExpireDate, Archived, Draft, CreateDate, CreatorID, etc; I have an 'objects' table with these columns, and I have set up a foreign key in other tables where there is a need for a row-to-row integrity.

Now my question is, since I have to SELECT using INNER JOIN with object and the corresponding table, merely to filter out archived rows, I am wondering if I would be better off actually putting the common fields into each respective table and get rid of the 'objects' table altogether. I mean, is it more job for the server to actually JOIN the tables for each SELECT versus having a clean design with object oriented approach ?

How bad is my design, and what are the recommendations of experts who obviously were tempted to create an object oriented database design?

Select From First Table Where Id Is Not In Second Table
I have two tables. One is a backup of the other, but with slight changes in the fields.

I am trying to restore only the deleted rows of the table, by retreiving them from the backup.

I thought about something like:

Select * from backup_tbl Where id != ... then i don't know.

Two Table Query: Grab Rows From One Table Even If No Related Row In Other Table
PHP

$gettray = mysql_query("SELECT trailers.title,
trailers.link,
trailers.movie,
movie.title AS mtitle
FROM trailers,movie
WHERE trailers.movie=movie.word
ORDER BY trailerid
DESC LIMIT 6",$connm);

It works great, but there is one problem. It will not grab any rows from the 'trailers' table if a corresponding movie row does not exist in the 'movies' table.

I want it to pull ALL rows from the 'trailers' table, even if the corresponding row in the 'movies' table does not exist yet.

If the row does not exist in 'movies', the program than uses the entire trailer title like so

PHP

if($ttray['mtitle']) {
$newttitle = explode("-",$ttray['title']);
$newttitle = array_reverse($newttitle);
$ttitle = $newttitle[0];
$ttitle = $ttray['mtitle'] ."- ". $ttitle;
} else {
$ttitle = $ttray['title'];
}

Thanks
Ryan

2 Table Select.
I have the following query and it erros out saying the select field name is ambigious. The tables are exactly the same. How would I go about fixing it?

I just want it to return the user_id if it returns a value for either table.

SELECT user_id FROM amt_users a, pro_users p WHERE `a.username` = '$username' AND `a.password` = '$password' OR `p.username` = '$username' AND `p.password` = '$password'

2-3 Table Select
I Have 3 tables
-----------------
person
ID
person_name

product
ID
product_name

person_review
ID
product_ID
person_ID
---------------

Basically I a person reviews a product, their ID and the products ID gets put into the person_review table. I want to be able to go through the person_review table, and see what products haven't been reviewed, and what products haven't been reviewed by 'x' user.

So far, this has gotten me, what products a specific person HAS reviewed, but I am unable to get which ones they haven't reviewed. Code:

Select What Is In One Table And Not In The Other
I have a dilema, I have two tables, the primary ID from the first is a foreign ID in the second. How can I structure a query to select only the records from the first table ONLY IF the primary ID of this table is not listed in the secondary table as a foreign key.

Select, Only If Id Is Not In Another Table.
I have two tables that look like this:

Objects(id, description)
Votes(<id>, ip)

Now I want to select rows from objects like this:
SELECT * FROM Objects WHERE id != (SELECT id FROM Votes WHERE ip = 'currentIP')

currentIP is filled in automaticly by PHP, but I'm able to convert a query from MySQL to PHP, so that's not important :)

Doe anyone have a good idea?

How Do I Select < 5 From All Of The Table?
I am trying to veiw all of the table past the first 5 rows....

SELECT * FROM table WHERE * < 5;

I know thats not even close, but does anyone know what im trying to do and have the solution?

Select From Table
Is it possible to do a "select * from table where id=value1 and id=value2"? I have tried this and the query returns no data despite the fact that the table has records that match the criteria in the select?

Select * From Table
Wanted to know if there was a quicker way of doing a search in the table instead of having to do something like:

"SELECT * from table where id LIKE '%$search%' OR thing LIKE '%$search%' etc. Instead, simply select * from the table where ANY column is LIKE $search

I wanted to know this in order to simply shorten the query (and learn if there's a way to do it) and also, so that if I search for two different strings, for example, one that states the date, whereas the other states a person, it would find the query, even though they're both in different columns.

Select From Table
I have a table of colours, one of which is assigned to each user.
I wantr to be able to show the colours that arent already being used by a user, so....
In the user table there is a field named use_colour (which i just save hex value into a varchar field). Colour table is a simple list of available colours.
So I have tried things like:
Code:

$sql = "SELECT * FROM colour,user
WHERE col_colour != use_colour
";
but to no avail.
I basically want to see a list of all the colours from the colour table, excpet those that are in use in the user table

Select From One Table
basically I need to select all records
from one table that don't exists in second table with status 1, but
they can exists in second table with status 0, to be more complicated
there can be in the same record in second table with status 0 and 1
(second table is something like log)

in oracle I can do:
select record from table_1 where record is not in (select record from
table_2 where status=1)

and I'm looking for something similar in mysql

SELECT Table
I am not sure if this is improper or not but here is the problem.
I would like to store along with other data the name of a table storing the extended data. First is this a bad or frowned upon idea. Secondly, is it possible to join the data so that I don't have multiple queries?

Select * From A Table Except
I want to select all the entries in a table except one. Is there something like select * from table EXCEPT x ?

Here's One For You (select From One Table On Cond From Another)
Working on selecting everything from (a condition in) table1 where the related row in table2 meets a certain condition.

table1:

+----+-----------+------------+------+
| id | table2_id | condition | data |
+----+-----------+------------+------+
| 1 | 1 | false | hi |
| 2 | 2 | true | foo |
| 3 | 2 | false | hows |
| 4 | 3 | true | bar |
| 5 | 3 | false | good |
+----+-----------+------------+------+
table2

+----+------------+------+
| id | condition | data |
+----+------------+------+
| 1 | false | abc |
| 2 | true | bca |
| 3 | true | cab |
+----+------------+------+

So I'd like to select * from table1 where condition equals false, if and only if the condition in table2 (where table1.table2_id = table2.id) is true.

desired resultset

+----+-----------+------------+------+
| id | table2_id | condition | data |
+----+-----------+------------+------+
| 3 | 2 | false | hows |
| 5 | 3 | false | good |
+----+-----------+------------+------+
This doesn't seem to work:

SELECT table1.id, table1.table2_id, table1.condition, table1.data FROM table1, table2 WHERE table1.table2_id = table2.id AND table1.condition = 'false' AND table2.condition = 'true'
I think it would be a more involved query than that. An example scenario is a database of computer programs (table2) and screenshots (table1) for approval. The screenshots are linked to the programs. When program condition is true, they have already been approved, as with screenshots. The query would retrieve a listing of unapproved screenshots for any computer program that is already approved. Does this make any sense?

SELECT Where There's No Record In Table
[products]
ID
name

[users]
ID
fname
lname

[products_users_lookup]
uID
pID

Ok, I need the following:

SELECT all products FROM these 3 tables WHERE there's no record in the lookup table.

So I need all products that are not attached to the user account.

Select From Table Where Col Begins With
I am writing a cms and have to correct the way "page names" are being written.

category should be just dog however when these went into the database it also wrote the sub categories such as dog-poodle and dog-beagle.

The feature to rename categories selects data from the category column where category = 'dog' which misses the breeds.

I am a sub on this project and told them to create only one page and play with the editor, well over the weekend they put in over 100 entries.

My question, is there any way to use regular expressions when selecting data.

something like WHERE category '(^dog)'

or do I need to select everything in the col and run a regex agaunst it?

perhaps the most obvious would be to select everything and explode by - and just put them all correctly back into the db.

The reason I ask is that I can see many uses to query the db with regex.

Select Every 3 Rows From A Table.
I have a bit of a problem, Iam doing a match results page with data from mysql.

My table Name: JuniorMatchResults

Data Colums: id, date, venue, position (1,2 or 3), weight.

What i wanto to do is output this information in my website such as you would have the 1st 3 positions outputted in a table, the the next set of 3 in a table and so on.

Select MAX Date From Table
I've got a table that's going to have records spanning a whole week. There's a date field that I'm going to use as a filter to determine which records to display (the most recent). How might I query to get the records with the most recent date only?

I'd prefer to have just one query, but as I'm running mySQL 4.0.17 that might not be possible. This means I can't perform subqueries.

so my records might be like this:

2006-05-31 creole 15
2006-05-31 andy 78
2006-05-30 creole 12
2006-05-30 andy 76

If I was adding in the data manually there would be no issue with what to grab, but the question is further complicated by the data import that I'll be performing once per day around 2am. This site is going to be used by people internationally so I can never be sure WHAT day the person will have.

So, if I suppose that I could do a query to get the MAX(date) from the table, then a second query upon that value. But I'd love to see if there's a better way first.

Select From Table Where Age Is One Week
I think the Subject line is slightly cryptic...

I have a simple query that outputs the records from a table that have dates 'entered' after 2008-04-01:

CODESELECT * FROM table_user w
WHERE w.`entered` >= '2008-04-01';

Select Row From Table, EASY
I'm drawing a blank and can't find it my Sams 24 hour book.

I've got a player name and stats in a DB so a row in the database would look like
Chris Brown 300 1200 5 40 325 2

I need to select(display PHP) Chris Brown and everything associated with him.

I had this all setup and haven't thought of it in years and now....geesh I just feel like an idiot as nothing I've tried works and I know it's connect to DB then one line of code, a select statement.