MySQL Forums - MySQL Query Browser

auto increment by -1 (1 reply)

Jig Sheth — Tue, 24 Nov 2009 19:41:24 +0000

Hi Guys,

How do you auto increment by -1 in MySQL?

I'm trying to find MySql syntax of the following statement:

GENERATED BY DEFAULT AS IDENTITY(START WITH -1, INCREMENT BY -1)

I can do AUTO_INCREMENT=-1 in create statement, but how do I increment by -1. So the next number would be -2 instead of 0.

BTW, I'm using MyISAM engine.

Thanks,
Jig

MySQL Workbench 5.2.8 (BETA 1): Upgrade from MySQL Administrator, MySQL Query Browser (no replies)

Edwin DeSouza — Wed, 18 Nov 2009 19:47:23 +0000

WB 5.2.8 includes:
- Model (upgrade from DBDesigner)
- Query (upgrade from MySQL Query Browser)
- Admin (upgrade from MySQL Administrator)
- SSH-Tunnel (new)

Quick-Start Tutorial:
http://wb.mysql.com/?p=406

Download WB 5.2:
http://dev.mysql.com/downloads/workbench/

MySQL join and delete (3 replies)

David Hoffmann — Tue, 17 Nov 2009 12:34:21 +0000

Completely new - so apologise in advance if I am in the wrong forum list!

I have an issue where I have two tables and I need to delete all entries that are set to inactive (itStatus = 2) in one table and then delete its associating idProducts in another table (whether active or inactive). I have cobbled together something that I thought would at least select what I am after first.

SELECT FROM table1 MerchantProduct INNER JOIN table2 Product ON idProduct = idProduct
WHERE itStatus like 2

Tips and advice for a confused queryer would be appreciated.

Is Query Browser secure accross the Internet? (1 reply)

Gary Cornelisse — Sat, 07 Nov 2009 01:15:36 +0000

I can't find anything in the docs about the protocol Query Browser uses to communicate. I'd like to suggest it to hosting clients you want access to their databases, but only if its secure. At the very least, is the log-in encrypted?

need help in dynamic table query (no replies)

Palanisamy E — Thu, 05 Nov 2009 07:13:45 +0000

Hi there,
i need help in dynamic table query.
The situation is as follows,

There are two tables
1.product_details
2.attribute_set

"product_details" structure:
product_id
attribute_set_id
created_date
is_active

"attribute_set" structure:
id
name

Example records of "product_details":
product_id = 1
attribute_set_id = 1
created_date = "2009-10-30"
is_active = 1

Example records of "attribute_set" :
id = 1
name = 'attribute_default" // this was another table name

"attribute_default" is the another table name, the structure of the table is as follows,
product_id = 1
product_name = 'product1'
price = '200'

i want to fetch the records of product_id from the respective table (attribute_default) in attribute_set using attribute_set_id in main product_details table.

i want query like this.
SELECT p.* FROM `product_details` as p LEFT JOIN (select name from attribute_set as ats WHERE ats.attribute_set_id=1) as ctb ON ctb.product_id=p.product_id

i am stuck here..please anybody help me..

sorry for my bad english..

thanks,
Palanisamy

Stored procedure with strcmp (7 replies)

Shivapriya Nagaraj — Thu, 05 Nov 2009 03:04:54 +0000

create procedure priya(in pa varchar(30))
begin
if((STRCMP(pa,'Detailed')) = 0)) then
select product,weight from cg_production1 order by product;
else
select product,sum(weight) from cg_production1 group by product;
end if;
end

What's wrong with this stored procedure?? giving error in line 3

Thanks

Keeping layout of a view I wote (no replies)

Neil Sharkie — Wed, 04 Nov 2009 16:06:07 +0000

Hi there,

I am new to MySQl but have a few years experience using SQL Server. Basically I am wondering if there is a way to save the layout that I write a view in.
For example when I write:

CREATE VIEW currentresources_erm
AS
SELECT r.TGI, r.Forename, r.Surname, r.StartDate, o.OrganisationName AS 'Organisation', d.Discipline, l.Location, t.ResourceType, r.EmployeeID
FROM resources r INNER JOIN organisation o ON
r.Organisation = o.OrgUId INNER JOIN disciplines d ON
r.Discipline = d.DisciplineID INNER JOIN resourcelocation l ON
r.Location = l.LocId INNER JOIN resourcetype t ON
r.ResourceType = t.TypeID
WHERE r.ERMUser = 'Y' AND r.CurrentEmployee = 'Y'

This is accepted fine but if I go to edit this view it is shown as just one long line which has also added a lot of brackets which makes the query very hard to read. (see below)
Surely this is possible because when I write a stored procedure it saves the layout I write it in. Does anyone know if this is possible?

Thanks in Advance
Neil

This is the one line that it is turned into:
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=``@`localhost` SQL SECURITY DEFINER VIEW `currentresources_erm` AS select `r`.`TGI` AS `TGI`,`r`.`Forename` AS `Forename`,`r`.`Surname` AS `Surname`,`r`.`StartDate` AS `StartDate`,`o`.`OrganisationName` AS `Organisation`,`d`.`Discipline` AS `Discipline`,`l`.`Location` AS `Location`,`t`.`ResourceType` AS `ResourceType`,`r`.`EmployeeId` AS `EmployeeID` from ((((`resources` `r` join `organisation` `o` on((`r`.`Organisation` = `o`.`OrgUId`))) join `disciplines` `d` on((`r`.`Discipline` = `d`.`DisciplineID`))) join `resourcelocation` `l` on((`r`.`Location` = `l`.`LocId`))) join `resourcetype` `t` on((`r`.`ResourceType` = `t`.`TypeID`))) where ((`r`.`ERMUser` = 'Y') and (`r`.`CurrentEmployee` = 'Y'));

advanced join if posible (no replies)

Maris Radu — Mon, 02 Nov 2009 10:15:11 +0000

Hello all

I have a strange question, i don't think it could be done as far as i found googling...but it is worth asking :) .

Can you join 2 tables located in 2 difrent databases, that are located on 2 diferent servers (like Oracle "database link" concept http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_5005.htm), not using replication.

Thank you

Update on Char(1) is causing problem (1 reply)

Subhash Gander — Sat, 31 Oct 2009 21:02:32 +0000

Hi All,

I have one table with few cols along with one ACTIVE_FLAG CHAR(1) NOT NULL.
And I am trying to update above table as per below query.

UPDATE TEST
SET ACTIVE_FLAG = 'P'
AND LAST_UPDATED_DT = CURRENT_TIMESTAMP
WHERE (PRODUCT_ID,SEQ_NO,USER_ID)
NOT IN (SELECT DISTINCT PRODUCT_ID,SEQ_NO,USER_ID FROM TEST_FCT WHERE ACTIVE_FLAG = 'Y')
AND TEST.AUTO_SEQ_NO IN (SELECT AUTO_SEQ_NO FROM TEMP GROUP BY PRODUCT_ID,SEQ_NO,USER_ID)
AND ACTIVE_FLAG = 'Y'

As per query active_flag should be 'P' if it satisfy my where clause.
But this update is making ACTIVE_FLAG = '0'.

Please suggest me that where I am doing wrong.

And yes I am doing this from PHP Admin.

Regards,
Subhash

Export table data to another DB table using SP (no replies)

sonny singh — Wed, 28 Oct 2009 18:58:37 +0000

Hi All

I need to export table data from on one DB Server (mysql on Linux) using SP to the destination DB Server (MySQL on Linux). This will run once in a day using cron job. Of course databse connectivity require with this set up.

I am new for this and i need to do this using scripting rather any third party or GUI tool.

Please help...Thanks in advance.

sonny

Query Browser Shaded Input Box (2 replies)

Frank Fox — Wed, 28 Oct 2009 17:06:56 +0000

Trying to run a query and the input box becomes shaded after pasting script. Why is this and how can I get rid of the shading? I know this is ridiculous but I can't figure it out. Somebody please help! I can't do a damn thing until this is cleared up.

Non-committing commit! (2 replies)

Ian Jennings — Wed, 28 Oct 2009 12:37:54 +0000

Hi

I'm hoping one of the gurus could please give me a free clue as to what I'm doing wrong.

While I am new to MySQL I am not new to databases (dBIV -> Oracle) but this is one of the oddest things I've seen. In fact, there's two of us here who are equally puzzled.

Using Query Browser I am executing the following query:

update bp_test.contacts c set company =
(select l.businessname from survey.leads l
where c.data_source_id = 3 and c.old_id = l.id);

Nice simple update statement. I start a transaction, execute, then commit. If I run a query to view the changes I find that all has worked. I then change the c.data_source_id value to 7, start a transaction, run the update and commit. If I now view the table I find that the values for 7 have changed as expected, but the values for 3 have gone to 'null'.

If I now change c.data_source_id back to 3 and execute again, then all the values for 7 have gone to null.

I know I'm doing *something* wrong, but I can't for the life of me figure out what it is.

All advice will be most gratefully received.

TIA

Ian

MySQL Workbench 5.2.6 (ALPHA): SSH-Tunnel, Administrator, Query Browser (no replies)

Edwin DeSouza — Wed, 21 Oct 2009 21:25:10 +0000

WB 5.2.6 now includes:
- MySQL Query Browser (Feature Complete)
- SSH-Tunnel (Feature Complete)
- MySQL Administrator (Not Complete yet)

We need your help, testing & feedback!

Download WB 5.2:
http://dev.mysql.com/downloads/workbench/

MySQLWorkbench: SSH-Tunnel
http://wb.mysql.com/?p=370

MySQL Workbench: Administrator
http://wb.mysql.com/?p=374

MySQL Workbench 5.2: Features of MySQL Query Browser
http://dev.mysql.com/workbench/?p=229

MySQL Workbench 5.2: QuickTour of Query Browser
http://dev.mysql.com/workbench/?p=230

error in create table command (3 replies)

Gunwant walbe — Thu, 29 Oct 2009 12:27:53 +0000

Hi all

i am new to mysql ans linux .

I am using mysql server on red hat 5.
I want to create a table, and i am using mysql query browser 1.2.12 on red hat 5 OS.

the query generated from query browse is

CREATE TABLE `newdatabase`.`users` (
`id` VARCHAR NOT NULL,
`userid` VARCHAR NOT NULL DEFAULT NULL,
`password` VARCHAR NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE = MyISAM;

when i tried to exucute this mmand i got the following error message

Error executing SQL commands to create table.
MySQL Error Nr. 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 'NOT NULL,
`userid` VARCHAR NOT NULL DEFAULT NULL,
`password` VARCHAR NOT N' at line 2

plese help me ,

i don't know what is problem

Thanks

Mysql Query (no replies)

Thomas Alexander — Fri, 16 Oct 2009 10:05:04 +0000

Im bit confused with a query

I have a database table as mentioned below

name stat date
1) tom 1 2009-10-16 11:58:31
2) tom 1 2009-10-16 11:59:31
3 )tom 1 2009-10-16 13:05:11
4) tom 0 2009-10-16 13:06:11
5) tom 0 2009-10-16 13:07:11
6) tom 2 2009-10-16 13:08:11
7) tom 1 2009-10-16 13:09:11
8) tom 0 2009-10-16 13:10:11
9) tom 0 2009-10-16 13:11:11
10) tom 1 2009-10-16 13:12:11
11) tom 1 2009-10-16 13:13:11

i like to obtain the rows like 1),4),6),7),8),10) in single query

Result Should be

1) tom 1 2009-10-16 11:58:31
4) tom 0 2009-10-16 13:06:11
6) tom 2 2009-10-16 13:08:11
7) tom 1 2009-10-16 13:09:11
8) tom 0 2009-10-16 13:10:11
10) tom 1 2009-10-16 13:12:11

if you look the rows i mentioned those are first row before stat is changed.

i want to get the first occurence of datetime field when a stat is changed
Can any one help me.

how to show consecutive numbers from single query (5 replies)

Bhavinder Singh — Fri, 16 Oct 2009 10:51:30 +0000

Can some one explain me how to write MYSQL Query to display Consecutive Number.

My table has the field "id", "Chap", "date" which contain data like:

ID Chap Date
1 1 01-sep-2009
2 2 01-sep-2009
3 3 01-sep-2009
4 4 01-sep-2009
5 7 01-sep-2009
6 8 01-sep-2009
7 10 02-sep-2009
8 11 02-sep-2009
9 12 02-sep-2009

Desire output:
1-4, 7, 8 01-sep-2009
10-12 02-sep-2009

Hyphen and comma is more important to generate.

Thanks in Advance

GRANT acting up with stored routines (no replies)

András Lukács — Tue, 13 Oct 2009 16:19:43 +0000

Hi, All,

It seems I have a problem either with QB or GRANT, so I'm not sure I'm posting in the right forum, but didn't find a more fitting place...

I have a database with stored routines, which I created using 'root'@'localhost'. Then I decided to create a dedicated admin user for the db. I used CREATE USER to create 'db_admin'@'localhost', then granted it permission in the db using GRANT ALL ON db.* TO 'db_admin'@'localhost'.

At first, everything seemed to be fine. I was able to access tables and use stored routines. However, I noticed that when I try to open a stored routine for editing from the Schemata column of QB, it will always open up an empty routine shell, which contains only this:

DELIMITER $$

DROP PROCEDURE IF EXISTS `dbname`.`RoutineName` $$
$$

DELIMITER ;

So the routine is not available. QB also fails to display routine parameters and return types in the Schemata column. It seems as if db_admin only had execute rights on routines.

Any ideas how I can give it all rights? Isn't GRANT ALL supposed to grant those rights?

Query Browser slow after upgrading to 5.1 (no replies)

Roland Jonsson — Mon, 12 Oct 2009 15:59:27 +0000

We upgraded one of our servers from 5.0.67 to 5.1.37 back in August and after that we have noticed that queries using the Query Browser are extremely slow if you run queries with more than just a few rows in result when running it on my workstation. Running on the server though, results are returned in normal speed. Have updated to 5.1.39 now but it didn't make any difference.

The server is running Windows 2000 and I am using Windows Vista Home Premium 64-bit on my workstation, running over a 5 MBit Internet connection. (We have another server running Windows 2003 and 5.0.67 and I still get the normal response times from that one.)

Any tips for what to do would be appreciated!

Problem calculating query for team standings (3 replies)

Tom Gibson — Fri, 09 Oct 2009 22:40:44 +0000

I have a simple mySQL database for a pool league:
TABLE name='standings'
FIELD names='Team', 'Wins', 'Losses'
There are 4 players on each team. Round robin play of 16 games. The scores are entered each week into a form on our website which posts the results to the database. So each team is listed in the database for every week the form is submitted. With 20 weeks of play, each Team and their Wins & Losses will be in the database 20 times. That's the reason for the SUM's on the Wins & Losses.
The phrase games behind or games back (often abbreviated as GB in tables), is a common way to reflect the gap between a leading team and another team in a sports league, conference, or division. It is determined by adding the absolute difference in wins between a leading team and the team of interest to the absolute difference in losses between the two teams, and dividing by two. The leading team itself is always zero games behind. Usually this is indicated in tables by a dash rather than a zero.

The code here creates what I want but with one problem; the GB (games behind) produces NULL for every row. What is wrong??
SELECT TEAM,
SUM(wins) AS W,
SUM(losses) AS L,
TRUNCATE (SUM(wins)/((SUM(wins)+SUM(losses))),3) AS PCT,
IF (@WL_diff - (wins)-(losses) = 0,'-',(@WL_diff - (wins)-(losses))) /2 AS GB
FROM standings
GROUP BY TEAM
ORDER BY SUM(wins)-SUM(losses) DESC, PCT DESC
LIMIT 0,500
-----------------------------------------------------------------------------------
TEAM......................W....L....PCT.....GB
Cedar Creek 2............24....8...0.750...NULL
Cedar Creek 1............21...11...0.656...NULL
Champs...................18...14...0.562...NULL
Fondas...................15...17...0.468...NULL
Sportsmans 2.............13...19...0.406...NULL
Hueys....................12...20...0.375...NULL
Sportsmans 1.............10...22...0.312...NULL
Bristol Ridge Expansion...9...23...0.281...NULL
Bristol Ridge 1...........6...26...0.187...NULL
Bristol Ridge Maynards....3...29...0.093...NULL

The GB column should look like this:

GB
-
3
6
9
11
12
14
15
18
21

A bit complex for me.. (1 reply)

John M — Tue, 13 Oct 2009 21:04:37 +0000

I have two tables in my db .

articles
--ARTICLE_ID
--TITLE
--DESCRIPTION
--CONTENT

galeries
--ARTICLE_ID
--GALLERY_ID
--CONTENT

I need to fulltext search these two tables. The ARTICLE_ID is the common key for both. The CONTENT in articles is empty when the article is a galery, so in that case for this ARTICLE_ID the content is stored in galeries table and can have multiple instances with the same ARTICLE_ID.
The search keyword may be contained in TITLE,DESCRIPTION and CONTENT fields.

I'm stack with this and I can't think a valid query.

Any help will be apreciated.
Thanks in advance.

MySQL Workbench 5.2.4 (ALPHA): SSH-Tunnel, Administrator, Query Browser. (no replies)

Edwin DeSouza — Thu, 08 Oct 2009 00:26:03 +0000

WB 5.2.4 now includes:
- MySQL Query Browser (Feature Complete)
- SSH-Tunnel (Feature Complete)
- MySQL Administrator (Not Complete yet)

We need your help, testing & feedback!

Download WB 5.2.4:
http://dev.mysql.com/downloads/workbench/

MySQLWorkbench: SSH-Tunnel
http://wb.mysql.com/?p=370

MySQL Workbench: Administrator
http://wb.mysql.com/?p=374

MySQL Workbench 5.2: Features of MySQL Query Browser
http://dev.mysql.com/workbench/?p=229

MySQL Workbench 5.2: QuickTour of Query Browser
http://dev.mysql.com/workbench/?p=230

How do I order items but place some always at the bottom? (2 replies)

Greg Quinn — Wed, 07 Oct 2009 17:03:00 +0000

I have the following items in my table...

Bananas
No fruit
Peaches
Pears
Please select a fruit
Watermelon

I would like to order the items so they appear as follows, i.e I want 'Please select a fruit' at the top, and 'No fruit' right at the bottom.

Please select a fruit
Bananas
Peaches
Pears
Watermelon
No fruit

How can I query my table so the order comes out in this order?

Thanks

Not desired results (1 reply)

Ryan Crisman — Tue, 06 Oct 2009 14:45:25 +0000

Okay I am trying to get a sum of numbers in one column but instead I am getting this.

State | ZIP | ITEM | SALES
'"MA"', '"01082"', '"588"', 4

I should be getting this:

State | ZIP | ITEM | SALES
'"MA"', '"01082"', '"588"', 5823.8

My MySQL Query

SELECT d.`state`, d.`zip`, l.`item`, SUM(s.`sales`) FROM demo d, line l, sales s
WHERE d.`invoice` = l.`invoice` AND
d.`invoice` = s.`invoice` AND
l.`item` LIKE '"588%' AND
l.`item` NOT LIKE '%-REC%' AND
l.`item` NOT LIKE '%-USED%'
GROUP BY d.`zip`;

Can connect to mysql on localhost but not on remote host (1 reply)

Jim McLaughlin — Thu, 08 Oct 2009 15:34:01 +0000

Hi all,

I can connect to mysql if it's on the same machine as where the app is running, but once I change the connection string to a remote machine, I get a "can't connect to mysql on xx.xx.xx.xx, is there a server runnning..." error. The IP, port, username and password are all correct and mysql is running on the remote machine. Using mysqladmin -h xx.xx.xx.xx -u -p version on the machine with the app, I get the reply perfectly from the remote machine.

Any ideas? Am at my wits end.

Browser don´t show the value of Bit (2 replies)

Dirk 191 — Wed, 28 Oct 2009 21:53:32 +0000

Hello,

the Query Browser normaly shows the value of a Bit field like b'1' or b'0' in a resultset.

My Browser only shows b without the value.
If i right click in a Bit fild and then click View Filed in Popup Editor it´s displayed the right way with value.

Wath´s wrong?

Some Infos:
MySQL: 5.1.28-rc-community Windows 2003
and 5.1.31-community Windows XP Prof (same on both Systems)
MySQl Query Browser 1.12.17

Thanks, Dirk

conditions in cursor (no replies)

shiva reddy — Wed, 30 Sep 2009 08:00:25 +0000

hi
hi,my cursors definition is look like in sp

DECLARE cur1 CURSOR FOR select distinct(catag) from d_cacntrl
where (termid, cycleid)=(SELECT max(termid), max(cycleid) FROM `d_cacntrl`);

DECLARE CUR2 CURSOR FOR SELECT MAX(TERMID) as v_termid,MAX(CYCLEID) as v_cycleid,
MAX(YRMTH) as v_yrmth,catag,SUM(GROSSBAL) as varGRSBAL,SUM(SECVAL) as varSECVAL
FROM `d_cawork` GROUP BY CATAG;

NOTE:- catag is a common(relational) field between two cursors.

Q:- i need to define link between CUR1 AND CUR2, how we can link ?

Regards

query to Calculate total (no replies)

SHUAHIL ALAM — Tue, 29 Sep 2009 18:21:09 +0000

if my database is designed as following:

----------------------|
product_table
----------------------|
item | cost
----------------------|
item1| 60
----------------------|
item2 | 52
----------------------|
=================================
----------------------|
item1_table
----------------------|
member | won
----------------------|
00235 | 5
----------------------|
00236 | 6
----------------------|

=================================

----------------------|
item2_table
----------------------|
member | won
----------------------|
00235 | 2
----------------------|
00236 | 6
----------------------|

I want to know the total cost of the items member(00235) won.

i can't connect to MySQl using a new user (no replies)

Grant Walker — Tue, 29 Sep 2009 07:25:17 +0000

I've just created a new user using the MySQLAdministrator tool. There were no errors when i created the user(giving him total control over only one database) but now I can't connect to MySQL Query Browser using the username and password that I entered. Can anyone suggest why this is happening, and a solution to my problem?

Thanks
Grant

Greek Encoding Problem (no replies)

elias elia — Mon, 21 Sep 2009 07:30:07 +0000

I have a website that has the following in the section:

The database encoding is utf-8.

In the website, the text appears fine. However, in MySQL Query Browser it is scrubbled.

What is wrong?

Thanks!

Hey, Please help! (no replies)

Tobbi opsahl — Sat, 19 Sep 2009 13:39:42 +0000

Hey,
I messed up my MySQL files (MySQL server 5.0 and MySQL tools for 5.0).
I fixed MySQL server but MySQL Query Browser is messed up totally!
I deleted the connections(in MySQL Query Browser) no idea why but it wasnt smart:(
So now i can only log in with host name: 127.0.0.1 or localhost.
An i need to log in with my WAN ip.
So how can i fix it? Or How can i remove MySQL so i can download MySQL like i never had it on my computer?

Hope you understand and Thanks for reading!

Note: I wont use recovery DVD and delete all files !