MYSQL :: Locking Of Table Through VB For Write/Update

Locking Required During Update
if a update query is executed with concurrent access to the database is table locking always recommended? i am thinking specifically about updates that do not take into account the value that is already stored in the DB.
let me give an example to explain. a person wants to update the salary of someone we could write:

Update table1 Set salary = "30" Where personid = 5;

in that case the user is not reading the salary value eg they are not doing salary = salary + 100 so is there a need to do any locking if multiple users do the same thing say using a thin client? i cant see the possibility of a lost update error occurring.

No Read Or Write Between INSERT And UPDATE
I am using mysql with the InnoDB engine. I wrote a perl script that
first selects something from a table, and then updates a second table
based on the select from the first table. I need to make sure that
there is no read or write to the tables while my script performs the
insert and update.

I looked at

http://dev.mysql.com/doc/refman/4.1/en/lock-tables.html

and it says this:

---------------------------------------------------------
If you are using a storage engine in MySQL that does not support
transactions, you must use LOCK TABLES if you want to ensure that no
other thread comes between a SELECT and an UPDATE. The example shown
here requires LOCK TABLES to execute safely:

LOCK TABLES trans READ, customer WRITE;
SELECT SUM(value) FROM trans WHERE customer_id=some_id;
UPDATE customer
SET total_value=sum_from_previous_statement
WHERE customer_id=some_id;
UNLOCK TABLES;

Without LOCK TABLES, it is possible that another thread might insert a
new row in the trans table between execution of the SELECT and UPDATE
statements.
---------------------------------------------------------

However, I am using InnoDB and it DOES support transaction. So, does
that mean that even if I don't lock my tables, it will still work? If
not, what do I need to do?

Locking Table?
I've got two tables that control my menu on my site (which is coded in ASP).
The first table (cam_menu) contains info about the sort order of the menu
and the second about my content. To insert a menuitem above a previous
menuitem I use these SQL-sentences: Code:

Table Locking
I just have a question about the table locking in 3.23.56. If I am inserting a
row into a table that is MyISAM type, is the entire table locked? And, if so,
what does this mean for concurrent selects?

About Table Locking
One thread insert data to a specific table continuously using 'LOAD DATA INFILE..' or 'INSERT INTO ...' query. Actually, this table maintains only for one hour's data, so the delete query is performed before every insert query.
At the same time, another thread issues select query on that table periodically.

What I want to know is, whether it is free from the table corruption or not. Table level locking is performed in MySQL server? Or, I have to do explicitly?

Cant Write Or Delete To Table
I cant get my php script to insert or delete records into a mysql
table. I can view them all fine but that's about it. I've checked
the user permissions on mysql and have set them all to 'Y' incase that
was the problem but still no joy. Can anyone suggest what the problem
may be? I'm tried the usual RTFM but cant spot the problem

What Are The BIG Reasons For Locking The TABLE?
What are the BIG reasons for locking the table? :)
Why do we need to lock the table?

Search And Write, Or Write And Recover?
The problem: I need to generate a 'unique string' for each row in a table. I already use auto_increment for system dependencies between tables.

What is the best approach one of these or another?

After generating a candidate 'unique string' the two strategies that came to mind are:

1. to then search the table's column to see if it is already assigned; locking the table for write while searching and writing the new row, or

2. set the column to UNIQUE when defining the table. Just go ahead and write the new row if you get a "non-unique" exception, generate another 'unique string' and try again.

I've tried both on a small XP laptop and get "lock timeout exceptions" rather quickly using #1. But replace those with lots of re-writes when there starts to get "collisions" of 'unique string's.

Table Locking When Using Left Join
I need someone to clarify something for me.
If I have a SELECT query on tableA that uses a left join to TableB, does TableB get locked for the duration of the entire select query? Suppose that the SELECT on tableA is slow, but the join to tableB is really fast, does tableB experience a lock for the duration of the query on tableA?

Is it better to just run a query on tableA, and after run fast indexed queries on tableB?

Table Locking & Multiple Jobs
I'm using mySQL 4.1 (will be upgrading to 5 soon), and I have a problem with table locking.

My table is MyISAM.

I have a job running every x seconds that loops round the following process 10 times.
Each time it loops round it locks the table, reads into memory the oldest record in the table, deletes this oldest record and then unlocks the table and does some further processing of the data in memory.

Problem is that I need to have two or more of these jobs running at once, and when this happens, occasionally each of the two jobs will process the same record in the table ... how can this be????? I'm doing a table lock which I thought would prevent two seperate jobs accessing the same record?!?!

Table Locking When Updating From DataSet?
I have a bunch of code that updates tables from a VB.Net DataSets.

The original code updated Access2000 tables, I migrated the MDBs to MySql and have converted the data adaptors and command builders to MySqlClient statements.

Does anyone have any code examples to to the table locking using DataSets???

Locking A Table/Record/Field
Is there a way to actually insert data into the table and then lock it, disallowing all write attempts to the table/record/field?

Write/Move/Save Pdf To MySQL Table?
Is upload the only way to pass a file to a table?

I have a contract.pdf which is generated via php (FPDF) and saved in a temporary file. I need that file then moved /written to the appropriate customer table in our database (MySQL ) so that the temporary file may then be overwritten by the next Customer/Order.

While I am able to upload the file via <input type="file" name="uploadpdf> and related uploader.php, asking our web-users to “browse” for the file does not suit our needs, and of course adding a default value

<input type="file" name="uploadpdf" value="http://localhost/crm/modules/Orders/contract.pdf"> doesn’t work either.

As the file is always the same (modules/Orders/contract.pdf) how may I manage that move automatically, (say with a ‘Confirm Order’ button) to the database?

Denormalized Tally, Table Locking Issues?
Say I have a `news` table (MyISAM). People can vote an article up/down. User votes are recorded in `news_votes`. To alleviate the need for performing expensive AGGREGATE functions with a join between the two, I was thinking of keeping a running tally in the news table (`tally`, mediumint).

Assume a few hundred news stories per day each with hundreds of votes per day. I'm afraid that the table-level locking of MyISAM on the news table will result in performance degradation due to the fact that SELECTS can't run concurrently with UPDATES.

What are you thoughts about this method of slight denormalization? I've seen it on lower concurrency system archetecture like Wordpress blogs.

InnoDB: Right For Write-heavy Table? Server Tuning?
I've decided to merge 4500 identical tables into one. They were previously partitioned with one table per user of the system, but I'm imagining I may not need to do this anymore for this particular table. I'd like to do so to reduce the number of files in this database's directory (currently over 15,000), to reduce backup complexity, and maybe improve performance. That's where I'm still unsure.

This is the summary table for W3Counter with fields website_id, date, unique_visits, return_visits, page_views. The website_id and date uniquely identify each row.

It is a write-heavy table (probably more than 99% INSERT/UPDATE compared to SELECT). I may be making things up, but I believe I remember InnoDB showing superior performance for this type of table. I'd imagine row-level locking would decrease lock contention when being hit by dozens of threads concurrently -- queries trying to update different rows would be allowed since that row isn't locked, unlike MyISAM where the entire table is locked by each query.

Merging all the tables into this one would mean starting with about 1.5 million rows and growing about 4500 per day.

So what do you think? Is InnoDB the right way to go for this table? Should I expect better performance than MyISAM for the inserts/updates? Should I expect the same or better performance on SELECTs that SUM() those counts over various date ranges, and sometimes group by WEEK(), MONTH() or YEAR() on the date column? I'm not sure whether the primary key is sufficient for that type of query, if a key on just the website_id would help those, or what. Any opinions?

My other reason for this thread is to get some pointers on tuning the server variables for InnoDB tables. There's much less written about this than those mainly applicable to MyISAM tables. Which are most important for a table like this? Right now no other tables will be InnoDB as the rest benefit greatly from MyISAM-specific features (like prefix compression on indexes and maintenance of row counts).

Update Column In Table A With The Count Of Another Table
Having my SQL-background in Oracle I'm looking for the MySQL equivalent for:

update tableA
set column1=(select count(*) from tableB where tableA.key= tableB.key)

cannot find anything similar in the manual, but probably overlooking
something.

Update One Table Value With Values From Another Table
I am trying to update one table value with values from another table, and I cannot get it to work. What am I doing wrong?

This is my SQL-command:
UPDATE tabel1 SET tabel1.name=tabel2.name WHERE tabel1.ID=tabel2.ID

Update Table A For Every Instance In Table B
I have learned how to select records from two tables but now, I need to update every record in table A based on keys found in table B.

I have a KEY which I can match to records in table B and table B has a field which I can match to records in table A. I need to update table A and possibly table B.

How do I go about this?

Update Table With Info In Other Table
i have a case like this:

table table1 (key, accumulator)

table table2 (key, counter)

i want to, for each table1.key = table2.key, update accumulator with the info in counter, something like this:

update table1 set accumulator=accumulator+table2.counter where (? counter is the value related with the same key as the one in table1)

how do you write a sentence like this one?

Update Table From Temporary Table
I have an import process that creates a temporary table and then dumps a large amount of records into it. I need to update an existing table against this temporary table. So where the record exists I either want to update or replace (probably based on parameter but can be a separate query for each) it in the live table, and if it does not exist i want to add it.

The temporary table can contain a large number of records (100000-200000). How can I create a fast and efficient process for doing this? Is there a query I could write to do this or would I have to do this through through code (i'm working in a java environment). I think a query would be faster than processing that many records via jdbc.

Help Me Write This Join?
Hi there, been ages since I wrote any sql, and I'm a little stuck but I figure this is easy for you folks. I have this original query:

$posts = $wpdb->get_results("SELECT ID, post_title FROM " . $wpdb->posts . " WHERE post_status='publish' ORDER BY post_date DESC");

And this gives a lists of all posts. However, I want to only grab posts by a certain category now, say category #9. This information is kept in an intersection table called wp_post2cat. The columns in that table are:

rel_id
post_id
category_id

category_id is what I want, and post_id is the key pointing back to the original post. Posts can have multiple categories, but I just want the one listing where it's category 9, which is audio.

So pseudocode would be something like:

SELECT all posts WHERE post_status equals "publish" and the category is equal to audio (9).

How To Write This Select?
I'm having some troubles writing a select. I have the following tables:

theater
-------
- id
- name

movie
-----
- id
- name

showtime
---------
- id
- theater
- movie
- from
- to
- schedule

I would like to list all the theaters with all the available showtimes and movies. I tried to, but i didn't get the result i was expecting. Can somebody help?

Best Way To Write This Query
Query 1: obtains results in boolean mode from products table based on keywords

Query 2: Would like to grab all manufacturers names from manufacturers table based on mf_id in products table to then create a brand filter.

What is the best way to do this in the most efficient query possible.

I was thinking of building a php array from query 1 of mf id's and the putting them in query 2 and separating them using OR operators.

any ideas?

here is the query fyi:

PHP

$sql =  "SELECT products_id as prodid,
                    products_name as name,
                    manufacturers_id as manufacturers_id,
                    products_price as price,
                    products_msrp as msrp,
                    products_date_added as pda,
                    products_status as status,
                    products_sku as sku,
                    products_weight as weight,
                    categories_id as cat_id,
"
        .boolean_sql_select(
            boolean_inclusive_atoms($search_string),
            $fulltext_key)." as relevance
"
        ."FROM $table_name
"
        ."WHERE
"
        .boolean_sql_where($search_string,$fulltext_key)."
"
        ."HAVING relevance>0
";



        if (isset($HTTP_GET_VARS['sort'])) {
            if ($_GET['sort']=="brand") {
            $sql.="ORDER BY products_name ";
            } elseif ($_GET['sort']=="size") {
            $sql.="ORDER BY products_weight ";
            } elseif ($_GET['sort']=="price_low") {
            $sql.="ORDER BY products_price ASC ";
            } elseif ($_GET['sort']=="price_high") {
            $sql.="ORDER BY products_price DESC ";
            } elseif ($_GET['sort']=="fav") {
            $sql.="ORDER BY products_name ";
            }

        } else {
            $sql.="ORDER BY relevance DESC
";
        }

How To Write This In MySQL?
I Want to check if a certain value doesnt exist in a ceratain field in
my table as a condition.

"select id from employees inner join
translog on employees.emp_id=translog.empid
inner join outofofficedays on
employees.emp_id=outofofficedays.emp_id inner join holidays
where (here is my question: &#55614;&#57159;-04-02'
doesnt exist in the column translog.vtransdate)
and (&#55614;&#57159;-04-02' not between holidays.H_START and H_END)
order by translog.vTransDate,employees.emp_id limit 1"

How Do I Write This Trigger?
Iâ€™ve been searching all over for the answer to this one. If you have the answer, it would help me out a lot!

Using MySQL triggers, I want to sync content between two tables; I have table â€œuserâ€ and table â€œusersâ€, each have similar columns â€œlast_nameâ€ â€œfirst_nameâ€ â€œuser_idâ€ â€¦etc, and when a row gets written to table â€œusersâ€, I wanted that record to be replicated and inserted to â€œuserâ€ (with all the same field info).

How do I write this trigger? I know, itâ€™s anti-normalization, but it will really help me out with testing one of our site's authentication.

Write A Mysql Db Onto A Cd
how to write the db of a mysql on one system onto a cd so as to copy that db to the mysql on another system.
The 2 systems are not connected to each other.

Can't Write To File
Using MySQL 4.1.11-max on OS X 10.3. Using command line.
Can't successfully use "INTO OUTFILE". Getting the following:
mysql> select *
-> into outfile '/users/steves/desktop/test.txt'
-> fields terminated by ','
-> from industry;
ERROR 1 (HY000): Can't create/write to file '/users/steves/desktop/test.txt' (Errcode: 13)
At the shell, I get this:
steves2ndmac:~ steves$ perror 13
OS error code 13: Permission denied

Newbie in both Unix shell and MySQL. Can anyone tell me how to set permissions for Unix User mysql?

Cant Write Or Delete
I cant get my php script to insert or delete records into a mysql
table. I can view them all fine but that's about it. I've checked
the user permissions on mysql and have set them all to 'Y' incase that
was the problem but still no joy. Can anyone suggest what the problem
may be? I'm tried the usual RTFM but cant spot the problem

Write Data To Row With Via C API
I have an app written in C that sends data to text files. The files receive 8 variables and look like this:

May 19 20:42:09 2002
7
Services
3.23
4.34
0
None
2.31

Rather than writing data to a text file, I need that data to go to a MySQL 5.0.41 database row. I've already got the C API working and connecting to the "localhost" database, but now I need to write data with each transaction. Optimally, I'd like to send the data directly to the database row, but there doesn't seem to be an INSERT statement to do that. What's the best method to write the data to the database directly?

Locking
I'm wondering if there is a way to lock the database (or do something
else) to execute more than one query with assurance that they are
executed exactly one after another (figure if there are several threads
and I give it a couple of queries they may mix). Thereason is that
suppose we have two tables, one with an auto_increment key, and the
other that references that key:

table order (id ... primary key auto_increment ...
table transaction(order_id, ... foreign key refences order(id) ...

Now, I do something like:

insert into order ...
id = last_insert_id()
insert into transaction(id,0,0,...

Update Table With API
I'm trying to develope a getway between matlab and mysql. I would like to
write the result of matlab routine into mysql table without "UPDATE
.....SET..." statement, because I've have to write a different value for each
row and I have to write a lot of row. I would like to write table row by row
sequentially.

UPDATE Table SET
Does anyone know how to implement this into a php form CORRECTLY? I have all the proper syntax but then the server gives me a message that says the mysql version may not go along with the syntax. How do I work around this then? I need to update query strings and such and nothing will work.

Update From One Table To Another ????
I have two tables with linked fields, a one to many relationship. It is a item sold table to work order table where there are many items sold linked to a single workorder. I want to add up all the item taxes for the particluar workorder and instert it into the appropriate workorder. I have tried something like this:

update set workorder.tax (sum)woitem.tax where workorder.id=woitem.linkid

Can someone show me where I am going wrong?

Table Update
How do I relate such a way that I update a column of the main table, another secondary table's column related to the main table gets updated automatically?

UPDATE From One Table To Another
This should be so simple but I'm getting a very strange error??

I'm using MySQL version: 4.1.12-log

I've got an outdated country table that I'd like to update info from an ISO table. Pretty straight forward if you ask me.

Here's the query:
UPDATE country, isocountry SET country.un_numcode=isocountry.numcode
WHERE country.country_code=isocountry.iso;

I created a column called un_numcode and then I'd like to put the iso numcode info in my table where the country_code = iso code (same data, different field names in different tables).

It goes along really well for 38 rows out of 239 rows?? And then I start getting 127 in ALL the un_numcode fields even though that value does NOT exist in the country.numcode table!

Update Table
Im trying to update a table, but Im getting the following error...
Syntax error or access violation: 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 '' at line 5

Here's my code...

<cfquery>
UPDATE printers SET name= 
<cfif IsDefined("FORM.name") AND #FORM.name# NEQ "">
'#FORM.name#'
<cfelse>
NULL
</cfif>
WHERE printerid=#FORM.printerid#
</cfquery>

Update Table
What is the right syntax of UPDATE command, I want to use multiple (more than 5) WHERE clauses and update more than 2 fields.
UPDATE table SET UPDATE1 =â€™xyzâ€™ UPDATE2 = â€˜abcâ€™ WHERE 1=â€˜zxcâ€™ 2=â€™werâ€™ â€¦â€¦â€¦â€¦?

Update Table
I want to update a number in a column adding a 1 to the current number, however I don't want to select the table first.

Update Table
everytime it tried to update row in any table, it takes very longtime, it locks the table, then i run out of connections and mysql crashes.

2-table UPDATE
I am running the following query through PHP's mysql_query:

UPDATE hotel, hotel_brand
SET hotel.hotel_brand_id=0, hotel_brand.hotel_brand_parent_id=0
WHERE hotel.hotel_brand_id=6 AND hotel_brand.hotel_brand_parent_id=6

In actuality, it's two queries combined into 1. I am deleting a value that rows in these 2 tables reference, and want to set the values to 0.
Of course, the problem is the values aren't changing to 0.

Is splitting these up the best solution?

Update One Table
I have 2 tables one for the team standings one for the scores, this select statement will calculate win loss :

SELECT
SUM((homescore>awayscore and home=teamid)
OR (awayscore>homescore and away=teamid)
) as wins,
SUM((homescore>awayscore and away=teamid)
OR (awayscore>homescore and home=teamid)
) as losses
FROM scores, teams
order by teamID

UPDATE Table
Code:

UPDATE table SET field='select concat(field1,field2) from table'

This gives me a zero in my field
I can select concat separately without any problem

How To Write Like Comparison In Procedures?
this sample procedure takes in an username, searches for number of entries matching the pattern of the username.

delimiter $$
create procedure sampleDB.sampleProcedure( IN m_username varchar(30), OUT m_username_matched_count int )
deterministic
begin
if length(m_username) = 0 then
set m_username = 'jason' #set temporary name if empty
end if

set m_username_matched = 0; #finds number of matches for the given username

select
count(*) into m_username_matched #store into the variable to be returned
from
sampleDB.sampleTable
where
userName like '%m_username%' #this is the statement that does not work#

end$$

Using INSERT To Write To A Directory
I have a problem that I'm not sure if it can be done. I'm trying to
use the MySQL C API to be able to use a normal sql insert statement
that will send the data or file to a directory and NOT the database.
I'm not sure if there is anything out there that will allow this, but
I've searched everywhere, and I have found no solution.

C- Program To Write To Myi , Myd File
is it possible to create a myi, myd file for mysql using a C program. I
am using Stata that is written in C API, and I want to write the data
out to MYSQL database. Using ODBC and loading the data in MYSQL from
STATA takes forever. Can anyone please tell me if I can create the myd
files by using the data in memory thru C programming.

MySql Write Vs Read
Essentially I am faced with a design choice;

have an oft run (modifiable) query run every time any user wants to
run it, comparing criteria againt each and every user in the table and
delviering list OR saving that list (user/user/) so the next time all
that needs to be queried is the existence of a record in the table.

The basic tradeoff; version 1 there are many simultaneous queries of
the user db on complex search criteria; version 2 there are fewer but
their are as many far more simple queries to a much larger table, not
to mention many writes to same to keep the table current.

I know this is pretty vague, but I was wondering if in general there
is an area that mysql excels in vis a vis complex query smaller tables
simple query larger tables.

Question 2 is there anyway to guestiate the search/write times of a
user log in to both query and update? #Records, fields, etc? I'd like
to keep log in to < 10 seconds max.

Locking Of Table Through VB For Write/Update

Can anybody help me out by giving some hint on how to lock MySQL tables from VB for doing write or update?