MySQL Technology

I have 2 tables:

Users:
UserId: int, auto inc
IsTriggerable: bit

Events:
EventId: int, auto inc
UserId: FK to Users table
IsTriggered: bit

ASSERTION: At any given moment in time, if Users.IsTriggerable = 0, then all Events.IsTriggers must = 0 for that same user.

I have 2 processes:

1. "SET" Process
Checks users's IsTriggerable value, and if 1, could set Events.IsTriggered = 1 for a row

2. "RESET" process
Sets Users.IsTriggerable = 0 and Events.IsTriggered = 0 for all rows of same user.

My question: How can I arrange a transaction such that these processes do their job yet my assertion is always true? What kind of transaction is required (repeatable read, read committed, etc.) ?

My first thought is to do a transaction with 2 updates:
UPDATE Users SET IsTriggerable = 0 WHERE UserId = 2;
UPDATE Events SET IsTriggered = 0 WHERE UserId = 2;

But is it possible for the "SET" process to do:
UPDATE Events SET IsTriggered = 1 WHERE UserId = 2;
at the same time and the end result is my assertion is not true?

Any comments would be most appreciated.

Thanks,
...Matt

Hello,

I originally created a view by reading from a large table (18 million rows) and limiting the number of rows in the view to 100. I then created another view that read from a different larger table and joined to the first view,limiting the rows to 100 in the second view.

The two views were created in a couple of seconds but when I tried to do a SELECT statement from the second view it took several minutes after which I killed the process.

I then thought I'd create a table instead of the second view. That only took a couple of seconds but now that I want to delete that second table I can't do so. I killed that process after 5 minutes!

I then tried to truncate the table but same results.

How can I get rid of this table?!

One other thing - with views it appears to recreate the view each time you open it up, as I see "loading" before the data pops up. I'm pretty new to MySQL and thought that views operated like tables. This is an issue for me as some view take several seconds to load up.

Thanks,
Rita

We have master1 and slave1 replication schema. Our biggest table is around 40GB on file system. Is any chance to optimize this huge table without blackout of master1? It takes about 5h of optimization.

What about master1 and master2 replication schema, how it works? Can I do optimization on master2 and when it's done, is it transferred back to master1 in optimized form?

What steps do you recommend to be able optimize and without blackout... Thanks!

The MySQL Workbench shows a lot of connections listed as sleep. From what I understand our software creates a new connection each time it queries MySQL and after it is done with the query a short time later the connection goes to sleep. Consequently we have a lot of connections that are shown as sleep. From what I have learned setting up MySQL in interactive mode will allow connections to be reused. Is this correct and should we be using this mode or what else can we do to cut down on all those sleep connections? We are using the MYSQL ODBC driver for connections and for 5.1.019 I do see an option for Interactive client.

Find a flatshare or flatmate fast! 1000s of rooms for rent in flatshares across London,
Manchester, Birmingham, Bristol, Leeds, Edinburgh, Glasgow and the rest.
http://www.spareroom.co.uk

USMLE Step 2 Packages Rate without Shipment
---------------------------------------------------------------------------------------------
USMLE Step 2 CS Books - Complete Package
First Aid for IMGs - Step 2 CS
First Aid for the Match - Step 2 CS
Kaplan Step 2 Clinical Skills - CS 4th Edition
USMLE Step 2 CS For the IMG
Battle Price $ 65

To Order, Please visit usmleworldwide.com

Hello.

I have a rather frustrating issue.
I'm building up a dynamic SQL statement with a WHERE clause that includes a test against a datetime column. The user enters in a value in a test box for the ReviewDate (in the format ccyy-mm-dd) and I use that value when the SQL statement is built up.

Here's the shortened version:
INSERT INTO myTable (SELECT b.ID, b.Name, b.ReviewDate FROM bills b
WHERE b.deleted = 0 AND b.ReviewDate >= '2012-01');

When I just run the SELECT portion it works fine.
When I run the complete statement where it INSERTS after SELECTing, without the test for b.ReviewDate >= '2012-01', it works ok.

However, when I run the complete statement as described above with the test for the b.ReviewDate, I get the following error message:

[Err] 1292 - Incorrect datetime value: '2012-01' for column 'ReviewDate' at row 1

What am I missing?!

Any help on this will be greatly appreciated.

Thanks,
Rita

I’m trying to make a simple shopping cart application in PHP and am unsure of how to structure the database. I believe there are three tables that I need (but I could be wrong!):


1. products table

columns:

pn, name, base_price, category, images, tiered_prices, allowed_quantities, main_img, desc, weight, dimensions,qty_in_stock

column descriptions:

pn: the product part number
name: the product name
base_price: the base price of the item
category: the parent category for the item
images: serialized or json array of product images (probably between 2 and 5 images)
tiered_prices: serialized or json array. E.g. 10 items -> $1.00, 100 items -> $0.75, etc..
allowed_quantities: some items can only be purchased in certain quantities, such as 10, 100, ect.
main_img: the name of the main product image
desc: the product description
weight: the weight of the product
dimensions: serialized or json array containing width, height, depth
qty_in_stock: the number of these items in stock


2. cart table

columns:

id, pn, qty, user_id, date_added

column descriptions:

id: the primary key for the row (otherwise useless/unused)
pn: the part number of the product
qty: the quantity in the cart
user_id: the user id if logged in
date_added: the date the row was added. Rows older than, say, 30 days will be purged.

So, with this design, every item in the cart would have its own row in the the cart table. (The other method would be to serialize/encode the items/qtys in the cart and store them in one row).


3. orders

I’m really not sure how to do this table. I don’t think a row should exist for every item in the order, because that would be a TON of rows! Would it be better to serialize/json encode the pn’s & quantities of all the items purchased along with other relevant order information and store it all in a single row in the orders table?

Thanks for your advice!

nitroplanes has created developing and traveling by air gas rc air carriers a much simpler element to do than it ever has been in the last. All of our aircraft products are nearly 90% constructed. It only requires a few a chance to finish and you are prepared to fly! If you are new to gas aircraft, begin off with our very unique rc trainers! Our cost and promotions are unique by other sellers! We assurance the best cost on everything in our shop, in addition to we will not be beaten in our assistance and quality.

HI,
I am doing an ERP application using the mysql 5.5.18. here i need to calculate the employee in and out time difference by using the time (24-Hours format) only not using any date month and year. how to calculate this is there any function available.


example:
-- CALL sp_timediff('16:30','18:00');
-- CALL sp_timediff('09:05','10:05');
-- CALL sp_timediff('08:20','08:30');
-- CALL sp_timediff('16:20','16:30');
-- CALL sp_timediff('16:40','16:30');
-- CALL sp_timediff('23:50','00:30');
-- CALL sp_timediff('17:30','16:30');

Please help me...

I am a beginner and just downloaded mysql server and the workbench for Windows.

When I open "MySQL 5.5 command line client" it asks me only for th root password.
I created many users with diffrent passwords, but I don't know from where I could log in using those users?

I know I have to write (mysql --user=Username-- password=UserPassword UserDatabase) but exactly where I have to write those i don't know?

mysql command line doesn't ask me about the user I want to use to log in, it directly asks me about the root password!!

Hi,

did anyone encounter a statistic about the usage of different versions of MySQL servers among the internet or even in general?

I know this is even harder to determine than the webserver, but maybe there have been polls or whatever attempts to get a number.

Cheers
~Harry

Hello.

We have a web based application that allows the user to search for certain records that are contained in a MySQL database. The user can enter in search criterea in any one of fifty text boxes.

Six tables need to be joined to get the final results to the user.
Currently, the search MySQL statement is built up within java code whereby each text box is checked and if the value is not spaces, the text is appended to the WHERE clause.

I need to make this more efficient since three of the six tables have millions of rows of data. I'd like to call a Stored Procedure (SP) instead of using the inline MySQL statement. I'd have to pass in 50 parameters to this SP and then build up the WHERE clause depending on the parameters that are not empty.

I also need to make sure that each of the fifty fields that can be searched on have an index associated with it.

Is there anything else I can do to speed up the data search?

Any suggestions will be greatly appreciated!

Thanks,
Rita

Hi All,

We are in the process of initiating the development of a complex web application that will contain lots of user entered information. We need to perform full text searches on the database to get to the optimum results. An example scenario would be something similar to a classified ad entered by a user that is posted under some specific location and category. Now any user searching for this classified would enter some search term and the query would be able to filter results based on the locations and categories column and a full text search on the description column.

What things sholud be kept in mind for designing such db? Is it a good idea to have indexes on locations and category column? Should we use full text indexes ? Which is the ideal Storage engine for such operation, keeping in view that there will be multiple joins>

Hi, I have a table with names and states and I want to group the states with the names so the results would appear like this. If I right join on name it still comes out as one state per name instead of the below.

Name State
George AZ,WA
Carlton WA,HI
.. and so on

I think someone used collate that did the trick but don't recall the code.
Thanks!


CREATE TABLE `license` (
`firstname` VARCHAR (150),
`state` VARCHAR (2)
);


INSERT INTO `license` (`firstname`, `state`) VALUES('George','AZ');
INSERT INTO `license` (`firstname`, `state`) VALUES('George','WA');
INSERT INTO `license` (`firstname`, `state`) VALUES('Carlton','WA');
INSERT INTO `license` (`firstname`, `state`) VALUES('Carlton','HI');
INSERT INTO `license` (`firstname`, `state`) VALUES('Derek','WA');
INSERT INTO `license` (`firstname`, `state`) VALUES('Derek','OR');

Hi,

MySQL 4.1.4.

I'm experiencing some very strange behavior with Load Data Infile. Hopefully you can follow along and grasp what's happening...

Overnight, we push data to the server and load it with the above command.

Looking in the binlogs, it is successful. However, it is failing on the slave.

So, I tried to re-execute it on the master from the binlogs, with everything the way it was, and it now FAILS with the same error I see on the slave:

ERROR 1062 (23000) at line 4: Duplicate entry '1' for key 1

So, how can it run successfully when executed remotely, but fail when I run it again. I have double checked that the contents of the table are identical each time. And how, if it runs successfully on the master, does it fail on the slave? And, yes, I've checked they are in synch.

Binlogs snippet is below.

Can anyone help?!!!

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

#120129 2:00:23 server id 1 log_pos 71830438 Query thread_id=1804 exec_time=0 error_code=0
SET TIMESTAMP=1327820423;
SET ONE_SHOT CHARACTER_SET_CLIENT=33,COLLATION_CONNECTION=33,COLLATION_DATABASE=48,COLLATION_SERVER=48;
# at 71830571
#120129 2:00:23 server id 1 log_pos 71830571 Intvar
SET INSERT_ID=1;
# at 71830599
#120129 2:00:23 server id 1 log_pos 71830599 Query thread_id=1804 exec_time=0
# use sdi001;
# LOAD DATA INFILE 'F022A.TXT' REPLACE INTO TABLE `x0001` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' STARTING BY '' IGNORE 1 LINES (NAME22,ADDRESS12A,ADDRESS22A,CITY22A,STATE22A,ZIP22A,OWNER22A,PHONE22A,DEFLIC,DFLTINSFRM,DEFCOUNTY,DEFZIP,MESSAGEL1,MESSAGEL2,MINDAYS,SUSDAYS,HOLD_ROLL,ACRNTNOTIC,AFRSTNOTIC,ASCNDNOTIC,ATHRDNOTIC,AFINLNOTIC,BCRNTNOTIC,BFRSTNOTIC,BSCNDNOTIC,BTHRDNOTIC,BFINLNOTIC,VERSION,MAINTDATE,MCASUBMNUM,NUMOFLIC,DP36LABTYP,DP36PATLAB,DP36RUNLAB,PURGE_L_D,PURGE_F_D,DEF_COLLCT,FORM_ALIGN,LST_REF_DT,MASTERPASS,EMA_NET,SB_PQ_CODE,EFF_D_MCD,NEW_MENU,S_NR,E_NR,KIPRC_SDTE,KIPRC_LDTE,KIPRC_FREQ,record_id,CCVI,CCMC,CCDS,CCAX,CCMERCHID,WEBPAY,WGATEWAY);
# file_id: 1005 block_len: 1241
# at 71832508
#120129 2:00:23 server id 1 log_pos 71832508 Query thread_id=1804 exec_time=0 error_code=0
SET TIMESTAMP=1327820423;
SET ONE_SHOT CHARACTER_SET_CLIENT=33,COLLATION_CONNECTION=33,COLLATION_DATABASE=48,COLLATION_SERVER=48;
# at 71832641
#120129 2:00:23 server id 1 log_pos 71832641 Intvar
SET INSERT_ID=1;
# at 71832669
#120129 2:00:23 server id 1 log_pos 71832669
#Exec_load: file_id=1005
#120129 2:00:23 server id 1 log_pos 71830599 Query thread_id=1804 exec_time=0
use sdi001;
LOAD DATA LOCAL INFILE 'C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\1\F022A.TXT-3ed-0' REPLACE INTO TABLE `x0001` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' STARTING BY '' IGNORE 1 LINES (NAME22,ADDRESS12A,ADDRESS22A,CITY22A,STATE22A,ZIP22A,OWNER22A,PHONE22A,DEFLIC,DFLTINSFRM,DEFCOUNTY,DEFZIP,MESSAGEL1,MESSAGEL2,MINDAYS,SUSDAYS,HOLD_ROLL,ACRNTNOTIC,AFRSTNOTIC,ASCNDNOTIC,ATHRDNOTIC,AFINLNOTIC,BCRNTNOTIC,BFRSTNOTIC,BSCNDNOTIC,BTHRDNOTIC,BFINLNOTIC,VERSION,MAINTDATE,MCASUBMNUM,NUMOFLIC,DP36LABTYP,DP36PATLAB,DP36RUNLAB,PURGE_L_D,PURGE_F_D,DEF_COLLCT,FORM_ALIGN,LST_REF_DT,MASTERPASS,EMA_NET,SB_PQ_CODE,EFF_D_MCD,NEW_MENU,S_NR,E_NR,KIPRC_SDTE,KIPRC_LDTE,KIPRC_FREQ,record_id,CCVI,CCMC,CCDS,CCAX,CCMERCHID,WEBPAY,WGATEWAY);

Dear Friends,


I have date format like 11-11-2011
but i want to change whole dates in the date column
like '%m/%e/%Y' (11/11/2011)

Please give me a replay as early as possible


Thanks in Advance..............

Why does

select max(DateField)+C, max(DateField+C) from TableWithDateField;

[C an integer]

not return the same value in both columns?

select date(max(DateField))+C, max(DateField+C) from TableWithDateField;

does return the same value in both columns so does this mean max(DateField) doesn't return a date type?

I'm using MySQL (community) 5.1.

Hi All,

Here's what I want to do:

I need to check a field in a certain table once a day.

If this field reaches a certain value (say 15000) then I need to send off some emails (email addresses are stored in another table).

Can I do this with the mysql scheduler? How else can I do this?

Thanks!

qin__23

I'm looking for ways to work around this:

"As a general rule, you should never assign a value to a user variable and read the value within the same statement."

(from http://dev.mysql.com/doc/refman/5.0/en/user-variables.html)

Can some experts weigh in on different approaches, and whether they will work?

== Test Data ==

create temporary table test_table (
x int(10),
y int(10)
);

insert into test_table(x,y)
values
(1,1),
(1,2),
(1,3),
(2,1),
(2,2),
(2,3),
(2,4);

== Problem to Solve and Bad Solution ==

Let's say I want to order and rank the y values, but grouped by x value. This forces us to keep track of x and reset the rank value for y when x changes. In addition, let's also set the rank to null if it's more than 2. That will also force us to read the rank value. Here's what I want the query to return:

(x,y,first_two_rank)
(1,1,1)
(1,2,2)
(1,3,null)
(2,1,1)
(2,2,2)
(2,3,null)
(2,4,null)

Here's a solution that appears to work but is wrong. It's wrong because both @prev_x and @rank are written and read in the same statement.

select x, y, first_two_rank
from
(
select
x, y,
-- Reset @rank if x has changed
if(x != @prev_x, @rank := 0, @rank) as dummy_rank_reset,
-- Store x for the next row
@prev_x := x,
-- Increment the rank
@rank := @rank + 1 as dummy_rank_increment,
-- Grab the rank, unless it's greater than two, in which case it's null
if(@rank > 2, null, @rank) as first_two_rank
from
test_table
join (select @prev_x := null, @rank := 0) as init
order by x, y
) as x
order by x, y

== Assumptions ==

First, all the approaches below assume that:

- variable assignments in joined subqueries happen before variable assignments in the main query
- "order by" happens before the variables in the select are evaluated

So if we wanted to simply record the row number of the test_table this would work fine:

select
test_table.*,
@a := @a+1 as rownum
from
test_table
join (select @a := 0) as init
order by x, y;

Can someone confirm these assumptions?

== Approach #1: Arithmetic and Subqueries ==

This approach separates the computation of @rank from its usage by putting the computation into a subquery. It also uses arthimetic order of operations to update @prev_x while also using its previous value.

select
x, y,
if(rank > 2, null, rank) as first_two_rank
from (
select
x, y,
-- Reset @rank if x has changed, and increment it otherwise
if(x !=
-- Computes the previous x value while also updating @prev_x
(((@temp := @prev_x) * 0) * (@prev_x := x) * 0) + @temp,
@rank := 1,
@rank := @rank + 1
) as rank
from
test_table
join (select @prev_x := null, @rank := 0) as init
order by x, y
) as subquery
order by x, y

Can we assume that user variables embedded inside arthimetic operations will be evaluated in arthimetic order?

I couldn't think of a way to update and read @prev_x by separating them with a subquery. Assuming that subqueries are evaluated before main queries, it would be nice to replace the tricky arithmetic expression with some subquery.

== Approach #2: COALESCE() ==

This approach assumes that COALESCE(a,b) evaluates a then b. It forces the values to null to cause all the COALESCE terms to be evaluated.

select
x, y,
coalesce(
-- Save the previous value of @rank
if(@temp2 :=
-- Reset the rank if x has changed
coalesce(
-- Save the previous value of @prev_x
if(@temp := @prev_x,null,null),
-- Update @prev_x
if(@prev_x := x,null,null),
-- Now use the previous value of @prev_x
if(x != @temp, @rank := 1, @rank)
),null,null),
-- Update @rank
if(@rank := @rank + 1,null,null),
-- Now use the previous value of @temp2
if(@temp2 > 2, null, @temp2)
) as first_two_rank
from
(select @prev_x := null, @rank := 1) as init
join test_table
order by x, y;

Can we assume COALESCE() evaluates terms in order?

== Approach #3: User-Defined Function? ==

http://forums.mysql.com/read.php?102,511115,511290

I saw this post that showed using a function to solve this problem, but the queries have some oddities, and I'd have to think harder to apply this solution to the problem above. I thought I'd mention it in case people thought that despite its limitations it's a less ugly solution than the ones above. If so, I'll go off and work harder on a solution using that approach.