RE: converting numeric to date-time?
SET @tz=@@session.time_zone ; SET SESSION time_zone = '+0:00' ; SELECT DATE_FORMAT( FROM_UNIXTIME(1409304102.153) /*your epoch column here*/ ,'%Y-%m-%d %a %H:%i:%s.%f GMT'); SET SESSION time_zone = @tz ; 2014-08-29 Fri 09:21:42.153000 GMT (or) SELECT DATE_FORMAT( FROM_UNIXTIME(1409304102.153) - INTERVAL ( TIMESTAMPDIFF(SECOND,UTC_TIMESTAMP(),NOW()) ) SECOND ,'%Y-%m-%d %a %H:%i:%s.%f GMT') ; 2014-08-29 Fri 09:21:42.153000 GMT -Original Message- From: Philip Amadeo Saeli [mailto:psa...@zorodyne.com] Sent: Monday, September 01, 2014 5:51 PM To: Rajeev Prasad Cc: MYSQL General List Subject: Re: converting numeric to date-time? * Rajeev Prasad rp.ne...@yahoo.com [2014-09-01 17:55]: I have a column in a table which is epoch time including milliseconds. e.g. = 1409304102153 now i want to display all fields in the table but this field as: 2014-8-29 Fri 09:21:42: GMT (whatever comes in ) and i am not finding anything on web about how to do that. can anyone help please. ty. Rajeev I do not know how to do it directly in MySQL, but if you can dump the table and post-process, this may be helpful on Linux: The date(1) cmd can translate between formats, e.g. (taking the above value), date -d @1409304102.153 +%Y-%m-%d %a %H:%M:%S.%N 2014-08-29 Fri 04:21:42.15300 --Phil -- Philip Amadeo Saeli openSUSE, CentOS, RHEL psa...@zorodyne.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
replication newbie questions
Mysql newbie here, looking for some help configuring 5.0.45 master-slave replication. Here's my scenario... We have a heavily loaded 30gb 5.0.45 DB we need to replicate via master-slave configuration to a new, beefier server running same mysql 5.0.45, and then cutover to the new server. Due to extreme SAN congestion and a grossly overloaded master server, our DB dumps take 5.5 hours. But we cannot afford that much downtime or locking during the replication transition; we can manage 10-15 minutes, but more is very problematic. I understand that FLUSH TABLES WITH READ LOCK will lock the tables for the duration of the 5.5 hour dump. Is this true? If so, we'd like to dump/initialize/sync slave WITHOUT any locking anything the master for more than a few seconds if at all possible. Will this give us the dump we need? mysqldump --single-transaction --master-data --all-databases Thank you in advance for any help. Ed
Re: replication newbie questions
On 8/28/13 2:00 PM, Ananda Kumar wrote: Why don't u try snapshot backups, where the lock held for less duration. Or can't u take mysql dumps during Night time when there is less bd activity I neglected to mention these systems are both CentOS linux systems. Unfortunately, the 5.5 hour dumps are already done during the least busy times. Regarding snapshots, how long are snapshot locks held? These are ext4 filesystems. Assuming the lock is not held for long, what's the recommended way to do snapshots on ext4? Thanks, Ed On Thursday, August 29, 2013, Ed L. mysql@bluepolka.net mailto:mysql@bluepolka.net wrote: Mysql newbie here, looking for some help configuring 5.0.45 master-slave replication. Here's my scenario... We have a heavily loaded 30gb 5.0.45 DB we need to replicate via master-slave configuration to a new, beefier server running same mysql 5.0.45, and then cutover to the new server. Due to extreme SAN congestion and a grossly overloaded master server, our DB dumps take 5.5 hours. But we cannot afford that much downtime or locking during the replication transition; we can manage 10-15 minutes, but more is very problematic. I understand that FLUSH TABLES WITH READ LOCK will lock the tables for the duration of the 5.5 hour dump. Is this true? If so, we'd like to dump/initialize/sync slave WITHOUT any locking anything the master for more than a few seconds if at all possible. Will this give us the dump we need? mysqldump --single-transaction --master-data --all-databases Thank you in advance for any help. Ed
connection issue
This mysql newbie is having trouble connecting to a mysqld instance, hoping someone can offer a clue on troubleshooting. I have 2 mysql 5.0.45 installations on one RHEL server. One live mysqld is setup in what appears to be a relatively standard installation, port 3306, user 'mysql', etc. I've set up the other mysqld to run tests on a non-standard port 5045, user 'testsql', different data root, config, logs, etc. When I attempt to connect to the mysqld running on port 5045 from the command-line mysql client on the same host as follows ... # mysql -P 5045 ... it seems I'm actually connecting to the live server on 3306 because 'show databases' shows the live databases. How can I troubleshoot this best? Thanks.
How do I use a dynamic filename for an into outfile statement
Be kind, I am by no means any type of DB expert.
I would like to eventually move this to a stored procedure but for now I
am using the \. to fire it off.
Here is what I have so far (generating a list of machines missing software)
select last_logon.host_name_short
from last_logon
left join mcafee on last_logon.host_name_short = last_logon.host_name_short
where mcafee.host_name_short is null
and last_logon.host_name_short like 'w%'
-- the above works
into outfile (select
concat('Missing-',date_format(now(),'%Y%m%d%H%i'),'txt');
-- this line breaks it
The select concat() works from the command line
I can manually add the file name but would like to automate the process
Lastly, if it makes a difference, I don't use any graphical tools for DB
stuff. Inevitably someone says 'click here' :-)
Thanks for any help
Ed
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: how to RE-add innoDB storage? kinda kludgy fix
On 03/04/2011 09:24 PM, ed wrote: On 03/04/2011 10:46 AM, Jerry Schwartz wrote: -Original Message- From: ed [mailto:eth...@earthlink.net] [JS]snip I guess wordwrap is going to mess this up; mysql show engines ; [JS] Next time, try SHOW ENGINES\G Regards, I see no differences. thanks for the reply my fix wound up being use the gui in mcc (drakurpmi) to remove mysql and then to move any my* file from /etc and /usr/share, /usr/lib64/ and /usr/sbin/ and then reinstall with the gui in mcc (drakurpmi) to put back mysql functions I had removed. thanks for the help
how to RE-add innoDB storage?
I have recently noticed I can not access a PHP application's data files, and the errors I am getting seem to show that a recent update of the distro (mandriva) no longer supports innodb, and this may be a reason (if this is not some sort of catchall error). I would like to know how to add innodb plugin back into my mysql, or to convert the data to a usable format. I would be pleased with a pointer to the correct rtfd location. I guess wordwrap is going to mess this up; mysql show engines ; ++-+---+--+--++ | Engine | Support | Comment | Transactions | XA | Savepoints | ++-+---+--+--++ | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | CSV | YES | CSV storage engine| NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance| NO | NO | NO | ++-+---+--+--++ show plugins; ++++-+-+ | Name | Status | Type | Library | License | ++++-+-+ | binlog | ACTIVE | STORAGE ENGINE | NULL| GPL| | partition | ACTIVE | STORAGE ENGINE | NULL| GPL | | CSV| ACTIVE | STORAGE ENGINE | NULL| GPL| | MEMORY | ACTIVE | STORAGE ENGINE|NULL |GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL| GPL| | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL| GPL | check table vtiger_vendor; ++---+--+---+ | Table | Op| Msg_type | Msg_text | ++---+--+---+ | vtigercrm520.vtiger_vendor | check | Error| Unknown table engine 'InnoDB' | | vtigercrm520.vtiger_vendor | check | error| Corrupt | ++---+--+---+
Re: how to RE-add innoDB storage?
On 03/04/2011 10:46 AM, Jerry Schwartz wrote: -Original Message- From: ed [mailto:eth...@earthlink.net] [JS]snip I guess wordwrap is going to mess this up; mysql show engines ; [JS] Next time, try SHOW ENGINES\G Regards, I see no differences. thanks for the reply -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
find records with only numbers
I hope someone can give me a suggestion on this.
I'd like to find records in a table where a specific field only contains a
number.
For example,
Select Field1 as f
from table1 as t
where lcase(t.f) not like in
('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z')
This obviously doesn't work or I wouldn't be asking the question. In this
example field1 is a varchar(25) field and it is normally appropriate for it to
contain data that has numeric characters and alpha characters. Examples of
valid data in this field are '456987','142154','200145C1','954xxx','H 1231','My
Test', ..etc. In my query above I'm trying to find the records where there
is only a six digit numeric value and no others.
Thanks for any advice
Re: Timestamps replicating inconsistently depending on local timezone of server?
Rob Wultsch wrote: On Tue, May 13, 2008 at 2:07 PM, Ed W [EMAIL PROTECTED] wrote: I had naively assumed that dates would always be stored in UTC in the database and the only effect of localtime would be for display purposes? Can anyone shed some light on what's happening here please? TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. (This occurs only for the TIMESTAMP data type, not for other types such as DATETIME.) http://dev.mysql.com/doc/refman/5.1/en/timestamp.html Sure - but I'm observing the opposite. My datetime is correct in UTC, but the timestamp col has definitely jumped forward one hour. Orig server: created_at: 2008-05-13 17:52:53 updated_at: 2008-05-13 17:52:53 New server where the localtime variable has been changed: created_at: 2008-05-13 17:52:53 updated_at: 2008-05-13 18:52:53 Using default mysql client settings on each server to examine the data, so possibly problem is related to client incorrectly adjusting values for display? I then changed the second servers localtime option, restored the same database as before and again replicated the same data across to catch up and this time they show the same values. So basically the value retrieved from the second database is influenced by the localtime options being different on each server *at the time replication occurs* Anyone shed some light on this? Ed W
Re: Timestamps replicating inconsistently depending on local timezone of server?
Rob Wultsch wrote: This sounds like expected behavior to me. If you set the timezone one hour forward a timestamp will be one hour forward. The data stored on the server is the same, and will display the same if you change the timezone. The timezone setting when the insert occurred should have no effect. OK, your example is clearly demonstrating the effect I am seeing - however, by changing the server localtime option I appear to be influencing the default mysql time offset. I still don't understand the reality of what is happening here - your example appears to show that datetime fields are correctly stored as GMT and adjusted as desired, but that a timestamp is a function of localtime? Either way they appear inconsistent... The end result needs to be that I can get these dates out of the database and correctly adjust them for the desired users localtime. What you are demonstrating here is that I either need to ditch all my timestamp columns (inconvenient) or switch the server to only run in UTC (inconvenient in that I need to mentally adjust in order to make sense of the log files). It would appear that if I run the server with a correct localtime then I have a bag of trouble when I want to figure out the time something happened (as you can see c1 and c2 should be the same in all cases, but not in your example) Can anyone shed some light on the best approach? Thanks Ed W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Timestamps replicating inconsistently depending on local timezone of server?
Hi, can anyone explain mysql 5.0.54 handling of replication between two servers with inconstant /etc/localtime (but the same real clock time for UTC) On one server I inserted a new row with created_at and updated_at as the same time. Server localtime is GMT+1, created col is a date, updated_at col is a timestamp When this replicated to the other server which had localtime set to GMT, and then after it replicated I changed localtime to GMT+1 I find that created_at is no longer the same as updated_at - now updated_at is 1 hour later... I had naively assumed that dates would always be stored in UTC in the database and the only effect of localtime would be for display purposes? Can anyone shed some light on what's happening here please? Thanks Ed W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recent change in behaviour when inserting into NOT NULL fields??
Baron Schwartz wrote: Hi Ed, On Thu, Apr 3, 2008 at 3:32 PM, Ed W [EMAIL PROTECTED] wrote: Hi Up until version 5.0.44 (on linux) it appeared that you could do stuff like deliberately insert a NULL into a NOT NULL varchar field and it would be silently converted to an empty string. Similarly if you didn't specify a value it appeared to use what is describe in the docs as the DEFAULT() function to enter an empty string in the column However, I just upgraded from 5.0.44 to 5.0.54 and now the behaviour has changed so that this errors noisily. The docs on SQL Modes http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html - suggests that this behaviour can be controlled, but as far as I can see I don't have the |STRICT_ALL_TABLES or ||STRICT_TRANS_TABLES options enabled anyway..? Check the changelogs in the manual, but I don't think this behavior has changed. That would break backwards compatibility in a way I don't think has happened. You should check by examining your SQL mode on the running server: SELECT @@SQL_MODE; This is more reliable than looking at the configuration file, because you might be looking at the wrong file, or the wrong section, or someone might have updated the SQL mode by using SET GLOBAL SQL_MODE -- who knows. It's blank in both cases. Additionally if I compile in support for perl (something like --use-perl configure flag) then the old behaviour returns... Any thoughts? Ed W
Lost my defaults on varchar columns?
It appears that since updating from mysql 5.0.44 to 5.0.54 I have also lost my DEFAULT option on all my varchar columns. For colums which are also NOT NULL this is causing a bunch of problems in my existing app (trying to insert NULLS) Anyone can explain what happened and why this has changed? Innodb if this makes a difference.. Thanks Ed W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Recent change in behaviour when inserting into NOT NULL fields??
Hi Up until version 5.0.44 (on linux) it appeared that you could do stuff like deliberately insert a NULL into a NOT NULL varchar field and it would be silently converted to an empty string. Similarly if you didn't specify a value it appeared to use what is describe in the docs as the DEFAULT() function to enter an empty string in the column However, I just upgraded from 5.0.44 to 5.0.54 and now the behaviour has changed so that this errors noisily. The docs on SQL Modes http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html - suggests that this behaviour can be controlled, but as far as I can see I don't have the |STRICT_ALL_TABLES or ||STRICT_TRANS_TABLES options enabled anyway..? How can I return to the old behaviour (at least until I update my app)? Is this change in behaviour defined anywhere? Thanks Ed W | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Migrating form 3.23.49 to 5.0.32
I'm doing the above migration as mentioned in the subject line. I figured I would use the MySQL Migration Toolkit to help it along but it won't let me connect to my 3.23.49 server. Is there any other way to migrate all my data easily. Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
before insert trigger
Is it possible for a trigger to test the data to be inserted and if it doesn't meet specific criteria not insert the data at all? I have an application that writes a lot of data to my table. I don't have control over this application and it writes a lot more data then I need. So I'd like to have a trigger that doesn't allow certain records to be written at all. I can't seem to find anything in the documentation that describes what I'm looking for. Thanks
ODBC 3.51.22 problem - please help
I've found a glaring problem with the latest ODBC connector. Data types have been changed and data is no longer being read correctly. I'm running MySQL 5.1.16 on Netware. My apps are VB6 and VBA using ADO. The following query produces different data types depending on the version of the ODBC driver. SELECT ConCat(21000,'-','a') In 3.51.19 it is a VarChar and in 3.51.22 it's a VarBinary. Concat is supposed to return a string. In C a byte array may be fine but in VB a string should be a VarChar. Is this a bug or is there a server or OBDC setting that can be changed to make sure that it always returns a VarChar Thanks for the help
I hope I won't be thrown off the list for this
I know it's not 'Mysql help' related, but I hope this gets a smile, and I figure this crowd would appreciate it as much as any crowd; http://xkcd.com/327/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access denied for GRANT using root yet manual user insertion is fine
The grant flag is enabled on the root account: mysql SELECT Grant_priv FROM user WHERE User='root' AND Host='localhost'; ++ | Grant_priv | ++ | Y | ++ 1 row in set (0.01 sec) mysql SHOW GRANTS FOR 'root'@'localhost'; Grants for [EMAIL PROTECTED] GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 'MYSQL5_PASSWORD_HASH' WITH GRANT OPTION | 1 row in set (0.00 sec) When I execute the GRANT on _ANY_ database, I receive the following error: mysql GRANT ALL PRIVILEGES ON `foo`.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 'MYSQL5_PASSWORD_HASH'; ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'foo' I have tried revoking the grant (with --init-file), then reissuing it. This has not remedied it. It was recently upgraded from MySQL 4, it is currently running on mysql Ver 14.12 Distrib 5.0.27. I have ran 'mysql_fix_privilege_tables'. I have also updated the password to use the updated PASSWORD() hash in the mysql.user table, but the issue persists. The root user is fully operational, users can be inserted via the mysql.user table, This works without a problem! Contents of the my.cnf: # cat /etc/my.cnf [mysqld] set-variable = max_connections=500 safe-show-database log=/var/log/mysql.log old_passwords=0 The server runs on CentOS release 4.5 (Final). Any assistance to rectifying this will be greatly appreciated. Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access denied for GRANT using root yet manual user insertion is fine
The grant flag is enabled on the root account: mysql SELECT Grant_priv FROM user WHERE User='root' AND Host='localhost'; ++ | Grant_priv | ++ | Y | ++ 1 row in set (0.01 sec) mysql SHOW GRANTS FOR 'root'@'localhost'; Grants for [EMAIL PROTECTED] GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 'MYSQL5_PASSWORD_HASH' WITH GRANT OPTION | 1 row in set (0.00 sec) When I execute the GRANT on _ANY_ database, I receive the following error: mysql GRANT ALL PRIVILEGES ON `foo`.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 'MYSQL5_PASSWORD_HASH'; ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'foo' I have tried revoking the grant (with --init-file), then reissuing it. This has not remedied it. It was recently upgraded from MySQL 4, it is currently running on mysql Ver 14.12 Distrib 5.0.27. I have ran 'mysql_fix_privilege_tables'. I have also updated the password to use the updated PASSWORD() hash in the mysql.user table, but the issue persists. The root user is fully operational, users can be inserted via the mysql.user table, This works without a problem! Contents of the my.cnf: # cat /etc/my.cnf [mysqld] set-variable = max_connections=500 safe-show-database log=/var/log/mysql.log old_passwords=0 The server runs on CentOS release 4.5 (Final). Any assistance to rectifying this will be greatly appreciated. Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access denied for GRANT using root yet manual user insertion is fine
With assistance from a friend this issue has been rectified. The current GRANT was not sufficient. After executing the new GRANT with 'ALL PRIVILEGES' (using the --init-file switch) the root user can now issue GRANTS. Additionally I apologise for duplicate posts, It was not intentional. On 9/2/07, Ed Cradock [EMAIL PROTECTED] wrote: The grant flag is enabled on the root account: mysql SELECT Grant_priv FROM user WHERE User='root' AND Host='localhost'; ++ | Grant_priv | ++ | Y | ++ 1 row in set (0.01 sec) mysql SHOW GRANTS FOR 'root'@'localhost'; Grants for [EMAIL PROTECTED] GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 'MYSQL5_PASSWORD_HASH' WITH GRANT OPTION | 1 row in set (0.00 sec) When I execute the GRANT on _ANY_ database, I receive the following error: mysql GRANT ALL PRIVILEGES ON `foo`.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 'MYSQL5_PASSWORD_HASH'; ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'foo' I have tried revoking the grant (with --init-file), then reissuing it. This has not remedied it. It was recently upgraded from MySQL 4, it is currently running on mysql Ver 14.12 Distrib 5.0.27. I have ran 'mysql_fix_privilege_tables'. I have also updated the password to use the updated PASSWORD() hash in the mysql.user table, but the issue persists. The root user is fully operational, users can be inserted via the mysql.user table, This works without a problem! Contents of the my.cnf: # cat /etc/my.cnf [mysqld] set-variable = max_connections=500 safe-show-database log=/var/log/mysql.log old_passwords=0 The server runs on CentOS release 4.5 (Final). Any assistance to rectifying this will be greatly appreciated. Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What's up with 5.1x beta
Does anyone have an explanation as to why it's taking so long for an official release of version 5.1? Is it me or does it seem like 5.1 is taking a lot longer to be released. I don't remember previous versions having 20 beta revs before an official release. Is there a real date available for an official release?
Insert Select query problem
Hi All,
I have an issue that I need to resolve that is difficult to explain. I
hope that someone can understand what I*m trying to do and shed some
light on a solution. Here goes.
I have three tables, inventory, which is a list of transactions with
positive and negative values; request, which essentially is a temporary
table that gets deleted after it*s used here; and purchase, which holds
the solution as to whether an item is to be purchased or removed from
inventory,
CREATE TABLE `inventory` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Item` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
CREATE TABLE `purchase` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Source` int(11) DEFAULT NULL,
`Item` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `request` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Required` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
My Inventory and Request tables have data in them like this,
Insert Into `inventory` (Item, Qty)
Values
('Apples',5),
('Bananas',4),
('Cherries',6),
('Apples',-1),
('Bananas',1),
('Cherries',-2),
('Apples',3),
('Bananas',-7),
('Cherries',19),
('Apples',-5),
('Bananas',88),
('Cherries',6);
Insert Into `request` (Required, Qty)
Values
('Apples', 12),
('Bananas', 112),
('Cherries', 5);
Now what I*d like to do is create a single Insert Select query that
creates a record in my purchase table for each of the items in my
request table based on the number of items available in my inventory.
But, if there aren't enough items in the inventory to cover the amount
requested, I need to have a second record for that item in the purchase
table with the qty difference to another source. So based on the data in
the inventory my current totals are,
+--+--+
| Item | Sum(Qty) |
+--+--+
| Apples | 2|
| Bananas | 86 |
| Cherries | 29 |
+--+--+
and based on the qty of items in my request I would like to have a
purchase table that looks like this,
++--+-+
| Source | Item | Qty |
++--+-+
| 1 | Apples | 2 |
| 0 | Apples | 10 |
| 1 | Bananas | 86 |
| 0 | Bananas | 26 |
| 1 | Cherries | 5 |
++--+-+
with a source of 1 meaning pull the items from inventory and a source
of 0 means purchase them from somewhere else.
Can anyone help me with this?
Thanks
Re: Insert Select query problem
Thanks Jay,
I had to make a change to the first part of the query to get the results that I
wanted but your suggestion was definitely what I needed to get to the solution.
Thanks again.
For those that are interested, here's the final solution,
INSERT INTO purchase (Source, Item, Qty)
SELECT 1, totals.Item,if((totals.TotQty -r.Qty)0,r.qty, totals.TotQty)
FROM request r
JOIN
(SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item) AS totals
ON r.Required = totals.Item
UNION All
SELECT 0, totals.Item, (r.Qty - totals.TotQty)
FROM request r
JOIN
(SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item) AS totals
ON r.Required = totals.Item
WHERE r.Qty totals.TotQty;
On 8/10/07 at 12:33 PM, in message [EMAIL PROTECTED], Jay Pipes [EMAIL
PROTECTED] wrote:
Ed Reed wrote:
Hi All,
I have an issue that I need to resolve that is difficult to explain. I
hope that someone can understand what I*m trying to do and shed some
light on a solution. Here goes.
I have three tables, inventory, which is a list of transactions with
positive and negative values; request, which essentially is a temporary
table that gets deleted after it*s used here; and purchase, which holds
the solution as to whether an item is to be purchased or removed from
inventory,
CREATE TABLE `inventory` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Item` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
CREATE TABLE `purchase` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Source` int(11) DEFAULT NULL,
`Item` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `request` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Required` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
My Inventory and Request tables have data in them like this,
Insert Into `inventory` (Item, Qty)
Values
('Apples',5),
('Bananas',4),
('Cherries',6),
('Apples',-1),
('Bananas',1),
('Cherries',-2),
('Apples',3),
('Bananas',-7),
('Cherries',19),
('Apples',-5),
('Bananas',88),
('Cherries',6);
Insert Into `request` (Required, Qty)
Values
('Apples', 12),
('Bananas', 112),
('Cherries', 5);
Now what I*d like to do is create a single Insert Select query that
creates a record in my purchase table for each of the items in my
request table based on the number of items available in my inventory.
But, if there aren't enough items in the inventory to cover the amount
requested, I need to have a second record for that item in the purchase
table with the qty difference to another source. So based on the data in
the inventory my current totals are,
+--+--+
| Item | Sum(Qty) |
+--+--+
| Apples | 2|
| Bananas | 86 |
| Cherries | 29 |
+--+--+
and based on the qty of items in my request I would like to have a
purchase table that looks like this,
++--+-+
| Source | Item | Qty |
++--+-+
| 1 | Apples | 2 |
| 0 | Apples | 10 |
| 1 | Bananas | 86 |
| 0 | Bananas | 26 |
| 1 | Cherries | 5 |
++--+-+
with a source of 1 meaning pull the items from inventory and a source
of 0 means purchase them from somewhere else.
Can anyone help me with this?
Try this:
INSERT INTO purchase (Source, Item, Qty)
SELECT
1, totals.Item, r.Qty
FROM request r
JOIN (
SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item
) AS totals
ON r.Required = totals.Item
WHERE r.Qty = totals.TotQty
UNION ALL
SELECT
0, totals.Item, (r.Qty - totals.TotQty)
FROM request r
JOIN (
SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item
) AS totals
ON r.Required = totals.Item
WHERE r.Qty totals.TotQty;
cheers,
Jay
RE: database cache /
Thanks for the leads. I'll double check my indices and check out the following links. http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
database cache /
I have a 400mb database. The first query to tables takes about 90 seconds. Additional queries take about 5 seconds. I wait a while and run a query again; it takes about 90 seconds for the first one and the rest go quickly. I'm guessing data is being loaded into memory which is why things speed up. Does this sound right? Is there a way to keep the table in memory? Nothing is changing in the data. Thanks, -Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select statement with variable for table_reference?
Hi Octavian, First, thanks for helping out. I really appreciate it. Thanks to you also Randall. I am not sure I understand what you want. If you want to search for all cds, and books, and dvds based on a certain criteria, you can use that method I've told you about. Randall said it best. I have one table that has information about what other tables to search in. One table serves as an index of what's in a user's inventory while the actual product information resides in other tables. The user inventory table has fields for user_id, database_id, table_id, and record_id. There are also two other helper tables. One table (inventory_databases) contains a list of databases with their id and name. Another table (inventory_tables) contains a list of tables with their id and name. Both of these tables help map from the user's inventory to where product information resides. An example record from the inventory table would have data like this: user_id 33 database_id 1 table_id1 record_id 234234 I can look up the name of the database using database_id in the inventory_databases table. I can look up the name of the table using table_id in the inventory_tables table. All of the other product tables have an id field that corresponds to the record_id. Back to the example above, database_id 1 is the products1 database and table_id 1 is the books table. That means user id 33 has the book id 234234 in products1.books. Ok, that describes what I'm working with. As for what I'm trying to accomplish, I'm trying to reduce the number of queries required for pulling together basic information about the user's inventory. Why is that? Well... Right now I run one query the inventory table for all information specific to the user. Next, I use this information and run additional queries to get the actual product information. If the user has 1000 items in their inventory, I end up having to run 1 + 1000 queries. This can't be helped in situations where I refer to fields that are unique to each type of item (aka table). It seems like I should be able to avoid this though when dealing with a common field like title. I'm just not sure how to go about it. Using UNIONS is the only single query to work so far. Like I mentioned though, this requires a UNION for every table that product information is being stored in. If the first table can tell us where the data resides, it seems like we can use it to reduce the number of UNIONS required. Randall, your use of prepared statements and stored procedures seems like a good approach. It might provide a way to dynamically generate the product query. When I look at this: SET @strOtherTable = (SELECT other_table_name FROM first_table WHERE ...); SET @strSQL = CONCAT(SELECT ... FROM , @strOtherTable, WHERE...); The first thing I automatically think of is that the first select will very likely have more than one record in the result set. Is there a way to loop through the results to concatenate everything into the set of UNIONS? Ed May you want sometimes to search only in fewer tables that you know before making the query? If yes, then you can create more separate queries that search only in those tables. If you want to search only in the tables that have data about you want to search, you can't do it, because you don't know if those tables contain what you want before searching in them. But if those tables have well defined indexes on the keys you are searching for, that search will be very fast, especially if some of the tables don't contain records that match your criteria. I hope I understood correctly what you want. If you just want to specify a search criteria for each table separately, you can do it for each table in the (select ... where ...) and if you want to specify a search criteria for all the records of those unions, you can do it in a final where ... that's outside of those params. Octavian - Original Message - From: Ed Lazor [EMAIL PROTECTED] To: 'Octavian Rasnita' [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, June 26, 2007 11:37 PM Subject: RE: select statement with variable for table_reference? Ok, I used your approach like this: -- select i.scanned_barcode, v.title from inventory as i left join version as v on i.record_id = v.id where i.database_id = '1' AND i.table_id = '1' AND i.user_id = '33' and category_id = '766') UNION (select i.scanned_barcode, v.title from inventory as i left join amg.dvd as v on i.record_id = v.id where i.database_id = '2' AND i.table_id = '3' AND i.user_id = '33' and category_id = '766') order by title DESC -- It works like you're suggesting. I have to add a union for every one of the tables data is being stored in. That means I end up selecting something
select statement with variable for table_reference?
Is there a way to get something like this to work? Set @tname=mytable; Select * from @tname; Here's what I'm trying to really accomplish in case there is yet another way to approach this... I have to work with product data from multiple databases and multiple tables. For example, one database has a books table, another database has a dvds table and an albums table. One table in my primary database maps between these other databases and tables. I call this one table inventory and it has fields for user_id, database_id, table_id, record_id. select * from inventory where user_id = 'xxx' I'd like to create one query that selects all of the data for the user's inventory, independent of which database and table it resides in. I have some additional tables I can use to help: inventory_databases with id, name inventory_tables with id, name That allows me to get the actual name of the database or table. And that leads me why I'm trying to find another way to handle the table_reference. I figure there's a way to specify the value of one table's field as the name of the table when doing a join or something. Any ideas? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select statement with variable for table_reference?
Ok, I used your approach like this: -- select i.scanned_barcode, v.title from inventory as i left join version as v on i.record_id = v.id where i.database_id = '1' AND i.table_id = '1' AND i.user_id = '33' and category_id = '766') UNION (select i.scanned_barcode, v.title from inventory as i left join amg.dvd as v on i.record_id = v.id where i.database_id = '2' AND i.table_id = '3' AND i.user_id = '33' and category_id = '766') order by title DESC -- It works like you're suggesting. I have to add a union for every one of the tables data is being stored in. That means I end up selecting something from every product table, regardless of whether the user actually has something in there or not. Improving on this idea would be finding a way to just query the relevant tables... some sort of conditional union. Any ideas? -Ed -Original Message- From: Octavian Rasnita [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 26, 2007 1:02 PM To: Ed Lazor; mysql@lists.mysql.com Subject: Re: select statement with variable for table_reference? I am using the following method for doing this, but I am sure it is not the best one: (select id, title, author, 'book' as type from books) union (select id, title, author, 'cd' as type from cds) union (select id, title, author, 'dvd' as type from dvds) where ... order by ... limit ...; Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select statement with variable for table_reference?
Thanks for the info Jerry. =) -Original Message- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 26, 2007 1:59 PM To: 'Ed Lazor'; 'Octavian Rasnita'; mysql@lists.mysql.com Subject: RE: select statement with variable for table_reference? Getting back to your original question, I don't know of any way you can use a variable as a table name directly. You can, however, pull off something like this: mysql set @table = stage; Query OK, 0 rows affected (0.06 sec) mysql set @stmt = CONCAT(SELECT * FROM , @table); /* Arbitrarily complex stuff here */ Query OK, 0 rows affected (0.05 sec) mysql prepare foo from @stmt; Query OK, 0 rows affected (0.03 sec) Statement prepared mysql execute foo; +--++ | stage_id | stage_name | +--++ |1 | Imminent | |2 | Incoming | |3 | Follow Up | |4 | Eventual | |5 | Interested | |6 | Ongoing| |7 | Accts | |8 | Dump | |9 | Purchased | +--++ 9 rows in set (0.05 sec) Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select statement with variable for table_reference?
I'm honestly not sure. How would that work when the first table tells you what other tables to pull additional information from? -Original Message- What about using PREPARED STATEMENTS in a stored procedure? Something like: CREATE PROCEDURE `GetInventory`( IN strTableName VARCHAR(50), ...) BEGIN SET @strSQL = CONCAT(SELECT * FROM , strTableName); ... ... PREPARE Statement FROM @strSQL; EXECUTE Statement; DEALLOCATE PREPARE Statement; END Of course, you can build any type of statement to execute using this technique. One thing to know is that the prepared statements don't get put into the query cache. Hope this helps... Randall Price Secure Enterprise Technology Initiatives Microsoft Implementation Group Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 Email: [EMAIL PROTECTED] Phone: (540) 231-4396 -Original Message- From: Ed Lazor [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 26, 2007 4:37 PM To: 'Octavian Rasnita'; mysql@lists.mysql.com Subject: RE: select statement with variable for table_reference? Ok, I used your approach like this: -- select i.scanned_barcode, v.title from inventory as i left join version as v on i.record_id = v.id where i.database_id = '1' AND i.table_id = '1' AND i.user_id = '33' and category_id = '766') UNION (select i.scanned_barcode, v.title from inventory as i left join amg.dvd as v on i.record_id = v.id where i.database_id = '2' AND i.table_id = '3' AND i.user_id = '33' and category_id = '766') order by title DESC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Outer Join with Criteria
Hello, I'm wondering if this is the most effective way of doing an
outer join with 'extra criteria' (I don't feel like it's the best way):
SELECT e.EventID, ue.Contact, ut.Discount
FROM Event e
LEFT OUTER JOIN
(SELECT EventID, Contact FROM UserEvent WHERE UserId = 10) ue
using (EventID)
LEFT OUTER JOIN
(SELECT EventID, Discount FROM UserEventType WHERE UserID = 10) ut
using (EventTypeID)
WHERE e.FromDate = '2007-05-15'
Essentially, I'm trying to refactor some legacy php which looks like:
$q1 = mysql_query(SELECT EventID, EventTypeID FROM Event WHERE
FromDate = '2007-05-15');
while ($r1 = mysql_fetch($q1)) {
$q2 = mysql_query(SELECT Contact FROM UserEvent WHERE UserId = 10
AND EventID = {$r1['EventID']});
$q2 = mysql_query(SELECT Discount FROM UserEventType WHERE UserId
= 10
AND EventID = {$r1['EventTypeID']});
// ...
}
This performs with N+1 queries, and I'm sure there is a way to do it
with only 1.
Thanks for any help.
--Ed
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with Query
I need to get some duplicate record information from a table and I haven't found any way to do it yet. I figured there might be some type of query I could do using a for each type command. What I have is a table with names and companies. Some people have multiple entries for different companies. What I need to get is the name that has multiple entries along with the company names. Name| Company Joe BlowCompany 1 Joe BlowCompany 2 Joe G. Blow Company 1 Running the query should only return Joe Blow with Company 1 and Company 2. I can find out how many records Joe Blow has or list out each Company record grouped by Name but I only want Names with multiple entries shown. Can anyone help? I'm sure this also makes a difference but I'm stuck using MySQL 3.23. Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
binary into blob
Hi All, I'm trying to figure out how to put a pdf file into a blob field. I guess a pdf file is a binnary file and it will contain characters that will mess things up, so my question is: can it be done? Or better, how can it be done? ;) Any pointers to documentation are a bonus! Thanks all, -Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: binary into blob
On Wednesday 07 March 2007 19:28, Jay Pipes wrote: Ed wrote: I guess a pdf file is a binnary file and it will contain characters that will mess things up, so my question is: Hi, sorry for the late answer. The reason, until I come up with a better one, is that I'm doing my own basic accounting db and I'd like my bills to be available. So that a specific bill can easily be retrieved. Your thoughts ;) -Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: binary into blob
On Wednesday 07 March 2007 19:28, Jay Pipes wrote: Is there a specific reason you want to store this in a database? Why not use the local (or networked) file system and simply store the metadata about the PDF in the database? Cheers, Jay Hi Jay, Could you explain what you mean by metadata and how you would do it? Thanks -Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: binary into blob
On Wednesday 07 March 2007 21:26, Alexander Lind wrote: I would put the pdf as a regular file on the hd, and store the path to it in the db. Meta data could be things like the size of the pdf, author, owner, number of pages etc. Storing binary data from pdf:s or images or any other common binary format is generally not a good idea. Alec Thank you all for your help. I get the gist of things... don't store the binnary, store the path to it and details of it. Thank you all for your quick response. -Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: binary into blob
hehe, well said and sorry for the top-posting. I can only agree, both methods have their merits! :) Alec Seems I started quite a debate ;) I wanted to thank you all again for your opinion and for planting a new seed of doubt on which direction I'll go ;) I setup the db as proposed earlier using paths and file names to find the pdf, but I might read the article posted by 'colbey' and give it a go for technical curiosity. Thank you all again for your responses. -Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT IGNORE BUG?
I am importing a file into a table in which I want it to ignore duplicate rows. When I specify --ignore (this also happens if I do a SELECT IGNORE INTO from the client also) I get a duplicate key error. If I run the command again it skips the first such instance of a duplicate key and gives me an error (and exits) for the next instance of a duplicate key. If I run the command over and over it finally goes through the whole file. Then the fun starts over. The primary key is on 9 columns but the index shown in the error only has 6 of the columns listed. Is this a bug? I am running ver. 5.0.27 on Linux. -- Ed Pauley II [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General MySQL Question: Ed Reed (CA, United States of America) Medium
I just didn't enjoy the location. When it was in Orlando a few years ago, it was great. There were plenty of things to do and see; different places to eat every night. I had a really good time. Then the following year I went to Santa Clara and there was nothing to do. I went to the movies one night. Had dinner at Sizzler three times. I drove 45 minutes to find a place I could buy a shirt. The rest of the time I stayed in the hotel and watched crappy tv. If you folks want me to be away from my family and friends for five days you've gotta give me a reason to wanna go besides the great technical information. I didn't go to last years, I'm not going to this years and I probably won't go to any future ones held in Santa Clara. When I came home from the last one, my kids asked what I got them from my trip and I had nothing for them. It was just a boring place to go and I don't wanna go back. I'd just like to see it held someplace new every year. Thanks for replying to my comment Lenz Grimmer [EMAIL PROTECTED] 1/25/07 2:21 AM -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Ed, thank you for your message! On Thu, 18 Jan 2007, Ed Reed has filled out the MySQL Contact Form with a General MySQL Question: Can someone tell me you folks are going to hold all future User Conferences in Santa Clara? I would like to go this year but I didn't enjoy myself at the last one I went to in Santa Clara in '05 and I don't wanna go back. If you folks plan to hold all future conferences there I'd like to know so I can resign myself to the fact that it's never gonna change instead of hoping that it's gonna be somewhere else every year. To be honest, it's not entirely clear yet if we will change the location for upcoming events. But could you explain why you didn't enjoy yourself at the last one? How can we improve? Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Community Relations Manager, EMEA MySQL GmbH, http://www.mysql.de/, Hamburg, Germany Visit the MySQL Forge at http://forge.mysql.com/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQFFuISeSVDhKrJykfIRAsJxAJ9hLkjhA10B68OHpw97x6QHbSqjLACePQym m9lEE93nxdC+K//cQsL658Q= =W/8N -END PGP SIGNATURE-
Re: General MySQL Question: Ed Reed (CA, United States ofAmerica) Medium
I understand that. I go there for the same thing. My point was that, the conference needs the attendees as much we want the conference. When I go I'm going for the conference stuff but I have a life and they're asking us to give up that life for maybe 6 days. After a the second day things start to get a little monotonous and we need a break. After the third or fourth day the brain is fried. There needs to be some kind of other activity that takes your mind off of the same hotel walls. Let's put it another way. When you go to work do you stay there for 5 days straight? Probably not. You go home, you go out to dinner. You see friends. You do other things to take your mind away from work because you have to recharge your batteries. It's the same thing at the conferences. Unless you're a robot you have to get out and see the sights and be entertained so that you can go back the next day and absorb the next days meetings. I just liked it better when it felt like it was gonna be held at a different location every year. I made this same complaint last year and it never got this much attention. - cheers Joshua J. Kugler [EMAIL PROTECTED] 1/29/07 4:36 PM That's funny...when I go to a technical conference, I usually go for the conference, and couldn't care less if there is other stuff to do in the area during the off hours. j -- Joshua Kugler Lead System Admin -- Senior Programmer http://www.eeinternet.com ( http://www.eeinternet.com/ ) PGP Key: http://pgp.mit.edu/ ID 0xDB26D7CE PO Box 80086 -- Fairbanks, AK 99708 -- Ph: 907-456-5581 Fax: 907-456-3111 Ed Reed [EMAIL PROTECTED] 1/29/07 1:57 PM I just didn't enjoy the location. When it was in Orlando a few years ago, it was great. There were plenty of things to do and see; different places to eat every night. I had a really good time. Then the following year I went to Santa Clara and there was nothing to do. I went to the movies one night. Had dinner at Sizzler three times. I drove 45 minutes to find a place I could buy a shirt. The rest of the time I stayed in the hotel and watched crappy tv. If you folks want me to be away from my family and friends for five days you've gotta give me a reason to wanna go besides the great technical information. I didn't go to last years, I'm not going to this years and I probably won't go to any future ones held in Santa Clara. When I came home from the last one, my kids asked what I got them from my trip and I had nothing for them. It was just a boring place to go and I don't wanna go back. I'd just like to see it held someplace new every year. Thanks for replying to my comment Lenz Grimmer [EMAIL PROTECTED] 1/25/07 2:21 AM -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Ed, thank you for your message! On Thu, 18 Jan 2007, Ed Reed has filled out the MySQL Contact Form with a General MySQL Question: Can someone tell me you folks are going to hold all future User Conferences in Santa Clara? I would like to go this year but I didn't enjoy myself at the last one I went to in Santa Clara in '05 and I don't wanna go back. If you folks plan to hold all future conferences there I'd like to know so I can resign myself to the fact that it's never gonna change instead of hoping that it's gonna be somewhere else every year. To be honest, it's not entirely clear yet if we will change the location for upcoming events. But could you explain why you didn't enjoy yourself at the last one? How can we improve? Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Community Relations Manager, EMEA MySQL GmbH, http://www.mysql.de/, Hamburg, Germany Visit the MySQL Forge at http://forge.mysql.com/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQFFuISeSVDhKrJykfIRAsJxAJ9hLkjhA10B68OHpw97x6QHbSqjLACePQym m9lEE93nxdC+K//cQsL658Q= =W/8N -END PGP SIGNATURE-
Re: New DBManager Released
Yes it is true. I downloaded it and installed it to try and manage my user accounts. In the feature list it says Object Maintenance and User Objects are listed. But when you try to manage a user account I get a message saying that the feature is not available in this version. - enjoy COS [EMAIL PROTECTED] 1/24/07 4:08 AM Hi, Not true, please read http://www.dbtools.com.br/EN/dbmanagerpro/features.php (present in my original post) for details. Best Regards, DBTools Software http://www.dbtools.com.br ( http://www.dbtools.com.br/ ) - Original Message - From: Ed Reed [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com; PostgreSQL Announce List pgsql-announce@postgresql.org; SQlite Users Group sqlite-users@sqlite.org; Firebird Tools [EMAIL PROTECTED] Sent: Tuesday, January 23, 2007 3:20 PM Subject: Re: New DBManager Released The free version is crippled. You don't get all the capabilities they say. COS [EMAIL PROTECTED] 1/23/07 8:16 AM Hi, DBTools Software is proud to announce the availability of the new DBManager Professional Enterprise Edition 3.4.2 and DBManager Standard Edition 3.2.2. For a complete list of changes please check our website at http://www.dbtools.com.br/EN. The new versions are available in our downloads center at http://www.dbtools.com.br/EN/downloads. What is DBTools Manager? DBTools manager is an application for database managementm supporting MySQL, PostgreSQL, Interbase, Firebird, SQlite, DBF Tables, MSAccess, MSSQL Server, Sybase, Oracle and ODBC Datasources. This is a simple list of its features: - Full object management - Query Builder with planning, debugging capabilities - Diagram Designer - Report and Form Builders - Lots of Wizards to import and export data/structure to/from a variety of sources - Database Documenter, Comparer and Migration Wizards - View, Procedure and Function Builder - English and Portuguese BR language resources available - and much more Available in two editions: Enteprise and Standard. The latest can be used for free for personal use. For a complete list of its features check http://www.dbtools.com.br/EN/dbmanagerpro/features.php. Best Regards, DBTools Software http://www.dbtools.com.br ( http://www.dbtools.com.br/ ) ( http://www.dbtools.com.br/ ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: New DBManager Released
The free version is crippled. You don't get all the capabilities they say. COS [EMAIL PROTECTED] 1/23/07 8:16 AM Hi, DBTools Software is proud to announce the availability of the new DBManager Professional Enterprise Edition 3.4.2 and DBManager Standard Edition 3.2.2. For a complete list of changes please check our website at http://www.dbtools.com.br/EN. The new versions are available in our downloads center at http://www.dbtools.com.br/EN/downloads. What is DBTools Manager? DBTools manager is an application for database managementm supporting MySQL, PostgreSQL, Interbase, Firebird, SQlite, DBF Tables, MSAccess, MSSQL Server, Sybase, Oracle and ODBC Datasources. This is a simple list of its features: - Full object management - Query Builder with planning, debugging capabilities - Diagram Designer - Report and Form Builders - Lots of Wizards to import and export data/structure to/from a variety of sources - Database Documenter, Comparer and Migration Wizards - View, Procedure and Function Builder - English and Portuguese BR language resources available - and much more Available in two editions: Enteprise and Standard. The latest can be used for free for personal use. For a complete list of its features check http://www.dbtools.com.br/EN/dbmanagerpro/features.php. Best Regards, DBTools Software http://www.dbtools.com.br ( http://www.dbtools.com.br/ ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Administrator problem
Thanks for the reply Daniel, This is not on a Mac. I'm running MySQL Administrator 1.2.8 on a XP/SP2 machine and I'm connecting to a MySQL 5.1.9 server running on Netware6.5. - Still looking for help. Daniel Culver [EMAIL PROTECTED] 1/19/07 9:52 AM Are you working on a Mac? If so, logging in as root is not good enough. You must have set up and log into the root user account of your Mac computer or server. This, if anything is a Apple problem and advantage. The Administrator is opening to the account you are in, the login in window will accept any correct password combination under ShadowHash or other authorising protocol, but you will still be working in the account you are in with those privileges. HTH Daniel Culver [EMAIL PROTECTED] On Jan 17, 2007, at 4:49 PM, Ed Reed wrote: Unable to manage user accounts through MySQL Administrator 1.2.8. I've found that I'm not the only one with this problem but no one seems to have an answer for it. The problem is that when you select a user in the user list of the prog the user's detail don't appear on the right side so that they can be edited. If I right click on the user in the list then the only enabled options are Add, Refresh and Show Hosts. If I add a user, there's nothing else I can do it. The new user shows up in the list but I can't modify it or delete it. I'm logged in a root. I don't know what else to do.
MySQL Administrator problem
Unable to manage user accounts through MySQL Administrator 1.2.8. I've found that I'm not the only one with this problem but no one seems to have an answer for it. The problem is that when you select a user in the user list of the prog the user's detail don't appear on the right side so that they can be edited. If I right click on the user in the list then the only enabled options are Add, Refresh and Show Hosts. If I add a user, there's nothing else I can do it. The new user shows up in the list but I can't modify it or delete it. I'm logged in a root. I don't know what else to do. Any thoughts?
Re: MySQL Administrator problem
There was already a bug report submitted. That's where I found other users that have the same problem. Colin Charles [EMAIL PROTECTED] 1/17/07 6:05 PM Ed Reed wrote: Unable to manage user accounts through MySQL Administrator 1.2.8. I've found that I'm not the only one with this problem but no one seems to have an answer for it. Have you reported a bug to bugs.mysql.com ? kind regards -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Mobile: +614 12 593 292 / Skype: colincharles Web: http://www.bytebot.net/blog/ MySQL Forge: http://forge.mysql.com/
Re: MySQL Administrator problem
I don't have any way of selecting a host. I only have one host on my system and I don't see anything in the program that explicitly shows me where to select the host. Igor Alexandrov [EMAIL PROTECTED] 1/17/07 9:30 PM -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ed Reed wrote: Unable to manage user accounts through MySQL Administrator 1.2.8. I've found that I'm not the only one with this problem but no one seems to have an answer for it. The problem is that when you select a user in the user list of the prog the user's detail don't appear on the right side so that they can be edited. If I right click on the user in the list then the only enabled options are Add, Refresh and Show Hosts. If I add a user, there's nothing else I can do it. The new user shows up in the list but I can't modify it or delete it. I'm logged in a root. I don't know what else to do. Any thoughts? Hello! I think, that you haven't chosen a host for user. Choose it and user options will be enabled to edit. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org ( http://enigmail.mozdev.org/ ) iD8DBQFFrwYIn6e/N0YuRmcRAmJuAJ4u50WeNgr+fuTudhwXNk0mvi7VsACgpk1b sNzrOlk/rpVfD8ssw6hukUo= =Z2sQ -END PGP SIGNATURE-
Re: Insert ... Select troubles
Thanks for the Brent, What do you think about trying to make this work by using a stored procedure? A colleague mentioned it to me but I can't seem to get my head wrapped around it yet. - Thanks Brent Baisley [EMAIL PROTECTED] 1/15/07 7:45 AM When you said multiple field unique key, I assumed those two field were your primary key. The way I described in the easiest way to implement it. Especially since you can do future insert/select without having to worry about figureing out sequence numbers for each group. You may want to think if you need two unique indexes. Obviously if you already have related information in place based on the RecID value it would be difficult to switch. You would need to store the value of both fields to setup a relation. I don't think what you want to do can be done in a single query while also being able to add data in a similar manner in the future. Future insert/selects would require first determining the highest number for each group, then incrementing from there. You could create a middle table in structure I described and then run two insert/selects. The first to generate you sequence, then second to populate the main table. - Original Message - From: Ed Reed [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, January 12, 2007 8:22 PM Subject: Re: Insert ... Select troubles Thanks for the suggestion Brent. The auto_increment won't work in my situation though. My t1 table has a RecID field that already is my primary key and set to auto_increment. Is there another way that this can be done? Thanks Brent Baisley [EMAIL PROTECTED] 1/12/07 1:10 PM Sine ItemCount is part of your unique key, you should just make ItemCount an auto_increment field. Mysql will then handle providing sequential numbering within the group. For example: CREATE TABLE t1 ( GroupCount int, ItemCount int auto_increment, PRIMARY KEY (GroupCount,ItemCount) ) When you do an insert, leave out the ItemCount. insert into t1 (GroupCount) values (1),(1),(2),(2) The t1 table then looks like this; GroupCount ItemCount 11 12 21 22 The compound primary key causes a difference auto increment sequence to be made for each record group. - Original Message - From: Ed Reed [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, January 12, 2007 12:42 PM Subject: Re: Insert ... Select troubles ItemCount is essentially a counter of the records from the select statement. So, every new INSERT ... SELECT statement gets a new GroupCount (the next number up) and ItemCount represents the ID of the items in that new group. Does that make sense? - Thanks Michael Dykman [EMAIL PROTECTED] 1/12/07 8:13 AM On 1/11/07, Ed Reed [EMAIL PROTECTED] wrote: I need some help creating an INSERT * SELECT statement that supplies a record counter in the returned items. Here's the task, I have t1 and t2. t1 is GroupCount, ItemCount, Field1, Field2, Field3. t2 is Field1, Field2, Field3. t1 has a multiple field unique key called Request that has GroupCount and ItemCount. I would like to create an INSERT * SELECT statement that takes the three fields from t2 and puts them into t1 and at the same time automatically fills the GroupCount and ItemCount field. My data in t1 should look like this. +--+---+--+--+--+ |GroupCount| ItemCount | Field1 |Field2| Field3 | +--+---+--+--+--+ |1 | 1 | data from t2 | data from t2 | data from t2 | |1 | 2 | data from t2 | data from t2 | data from t2 | |1 | 3 | data from t2 | data from t2 | data from t2 | |2 | 1 | data from t2 | data from t2 | data from t2 | |2 | 2 | data from t2 | data from t2 | data from t2 | |3 | 1 | data from t2 | data from t2 | data from t2 | |3 | 2 | data from t2 | data from t2 | data from t2 | |3 | 3 | data from t2 | data from t2 | data from t2 | |3 | 4 | data from t2 | data from t2 | data from t2 | |3 | 5 | data from t2 | data from t2 | data from t2 | |3 | 6 | data from t2 | data from t2 | data from t2 | |4 | 1 | data from t2 | data from t2 | data from t2 | |4 | 2 | data from t2 | data from t2 | data from t2 | |4 | 3 | data from t2 | data from t2 | data from t2 | |4 | 4 | data from t2 | data from t2 | data from t2 | |4 | 5 | data from t2 | data from t2 | data from t2 | |4 | 6 | data from t2 | data from t2 | data from t2 | |4 | 7 | data from t2 | data from t2 | data from t2 | |5 | 1 | data from t2 | data from t2 | data from t2 | |5 | 2 | data from t2 | data from t2 | data
Re: Insert ... Select troubles
ItemCount is essentially a counter of the records from the select statement. So, every new INSERT ... SELECT statement gets a new GroupCount (the next number up) and ItemCount represents the ID of the items in that new group. Does that make sense? - Thanks Michael Dykman [EMAIL PROTECTED] 1/12/07 8:13 AM On 1/11/07, Ed Reed [EMAIL PROTECTED] wrote: I need some help creating an INSERT * SELECT statement that supplies a record counter in the returned items. Here's the task, I have t1 and t2. t1 is GroupCount, ItemCount, Field1, Field2, Field3. t2 is Field1, Field2, Field3. t1 has a multiple field unique key called Request that has GroupCount and ItemCount. I would like to create an INSERT * SELECT statement that takes the three fields from t2 and puts them into t1 and at the same time automatically fills the GroupCount and ItemCount field. My data in t1 should look like this. +--+---+--+--+--+ |GroupCount| ItemCount | Field1 |Field2| Field3 | +--+---+--+--+--+ |1 | 1 | data from t2 | data from t2 | data from t2 | |1 | 2 | data from t2 | data from t2 | data from t2 | |1 | 3 | data from t2 | data from t2 | data from t2 | |2 | 1 | data from t2 | data from t2 | data from t2 | |2 | 2 | data from t2 | data from t2 | data from t2 | |3 | 1 | data from t2 | data from t2 | data from t2 | |3 | 2 | data from t2 | data from t2 | data from t2 | |3 | 3 | data from t2 | data from t2 | data from t2 | |3 | 4 | data from t2 | data from t2 | data from t2 | |3 | 5 | data from t2 | data from t2 | data from t2 | |3 | 6 | data from t2 | data from t2 | data from t2 | |4 | 1 | data from t2 | data from t2 | data from t2 | |4 | 2 | data from t2 | data from t2 | data from t2 | |4 | 3 | data from t2 | data from t2 | data from t2 | |4 | 4 | data from t2 | data from t2 | data from t2 | |4 | 5 | data from t2 | data from t2 | data from t2 | |4 | 6 | data from t2 | data from t2 | data from t2 | |4 | 7 | data from t2 | data from t2 | data from t2 | |5 | 1 | data from t2 | data from t2 | data from t2 | |5 | 2 | data from t2 | data from t2 | data from t2 | |6 | 1 | data from t2 | data from t2 | data from t2 | |6 | 2 | data from t2 | data from t2 | data from t2 | |6 | 3 | data from t2 | data from t2 | data from t2 | +--+---+--+--+--+ I can figure most of it out with the exception of the ItemCount value. What I have so far is this, INSERT INTO t1 (GroupCount, ItemCount, Field1, Field2, Field3) SELECT (SELECT Max(GroupCount)+1 From t1), ?, Field1, Field2, Field3 FROM t2; The part with the ? is what I can't figure out. Any ideas? I'm not very clear what ItemCount is supposed to represent.. if you restate it, perhaps I can help -- - michael dykman - [EMAIL PROTECTED]
Re: Insert ... Select troubles
Thanks for the suggestion Brent. The auto_increment won't work in my situation though. My t1 table has a RecID field that already is my primary key and set to auto_increment. Is there another way that this can be done? Thanks Brent Baisley [EMAIL PROTECTED] 1/12/07 1:10 PM Sine ItemCount is part of your unique key, you should just make ItemCount an auto_increment field. Mysql will then handle providing sequential numbering within the group. For example: CREATE TABLE t1 ( GroupCount int, ItemCount int auto_increment, PRIMARY KEY (GroupCount,ItemCount) ) When you do an insert, leave out the ItemCount. insert into t1 (GroupCount) values (1),(1),(2),(2) The t1 table then looks like this; GroupCount ItemCount 11 12 21 22 The compound primary key causes a difference auto increment sequence to be made for each record group. - Original Message - From: Ed Reed [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, January 12, 2007 12:42 PM Subject: Re: Insert ... Select troubles ItemCount is essentially a counter of the records from the select statement. So, every new INSERT ... SELECT statement gets a new GroupCount (the next number up) and ItemCount represents the ID of the items in that new group. Does that make sense? - Thanks Michael Dykman [EMAIL PROTECTED] 1/12/07 8:13 AM On 1/11/07, Ed Reed [EMAIL PROTECTED] wrote: I need some help creating an INSERT * SELECT statement that supplies a record counter in the returned items. Here's the task, I have t1 and t2. t1 is GroupCount, ItemCount, Field1, Field2, Field3. t2 is Field1, Field2, Field3. t1 has a multiple field unique key called Request that has GroupCount and ItemCount. I would like to create an INSERT * SELECT statement that takes the three fields from t2 and puts them into t1 and at the same time automatically fills the GroupCount and ItemCount field. My data in t1 should look like this. +--+---+--+--+--+ |GroupCount| ItemCount | Field1 |Field2| Field3 | +--+---+--+--+--+ |1 | 1 | data from t2 | data from t2 | data from t2 | |1 | 2 | data from t2 | data from t2 | data from t2 | |1 | 3 | data from t2 | data from t2 | data from t2 | |2 | 1 | data from t2 | data from t2 | data from t2 | |2 | 2 | data from t2 | data from t2 | data from t2 | |3 | 1 | data from t2 | data from t2 | data from t2 | |3 | 2 | data from t2 | data from t2 | data from t2 | |3 | 3 | data from t2 | data from t2 | data from t2 | |3 | 4 | data from t2 | data from t2 | data from t2 | |3 | 5 | data from t2 | data from t2 | data from t2 | |3 | 6 | data from t2 | data from t2 | data from t2 | |4 | 1 | data from t2 | data from t2 | data from t2 | |4 | 2 | data from t2 | data from t2 | data from t2 | |4 | 3 | data from t2 | data from t2 | data from t2 | |4 | 4 | data from t2 | data from t2 | data from t2 | |4 | 5 | data from t2 | data from t2 | data from t2 | |4 | 6 | data from t2 | data from t2 | data from t2 | |4 | 7 | data from t2 | data from t2 | data from t2 | |5 | 1 | data from t2 | data from t2 | data from t2 | |5 | 2 | data from t2 | data from t2 | data from t2 | |6 | 1 | data from t2 | data from t2 | data from t2 | |6 | 2 | data from t2 | data from t2 | data from t2 | |6 | 3 | data from t2 | data from t2 | data from t2 | +--+---+--+--+--+ I can figure most of it out with the exception of the ItemCount value. What I have so far is this, INSERT INTO t1 (GroupCount, ItemCount, Field1, Field2, Field3) SELECT (SELECT Max(GroupCount)+1 From t1), ?, Field1, Field2, Field3 FROM t2; The part with the ? is what I can't figure out. Any ideas? I'm not very clear what ItemCount is supposed to represent.. if you restate it, perhaps I can help -- - michael dykman - [EMAIL PROTECTED]
Insert ... Select troubles
I need some help creating an INSERT * SELECT statement that supplies a record counter in the returned items. Here's the task, I have t1 and t2. t1 is GroupCount, ItemCount, Field1, Field2, Field3. t2 is Field1, Field2, Field3. t1 has a multiple field unique key called Request that has GroupCount and ItemCount. I would like to create an INSERT * SELECT statement that takes the three fields from t2 and puts them into t1 and at the same time automatically fills the GroupCount and ItemCount field. My data in t1 should look like this. +--+---+--+--+--+ |GroupCount| ItemCount | Field1 |Field2| Field3 | +--+---+--+--+--+ |1 | 1 | data from t2 | data from t2 | data from t2 | |1 | 2 | data from t2 | data from t2 | data from t2 | |1 | 3 | data from t2 | data from t2 | data from t2 | |2 | 1 | data from t2 | data from t2 | data from t2 | |2 | 2 | data from t2 | data from t2 | data from t2 | |3 | 1 | data from t2 | data from t2 | data from t2 | |3 | 2 | data from t2 | data from t2 | data from t2 | |3 | 3 | data from t2 | data from t2 | data from t2 | |3 | 4 | data from t2 | data from t2 | data from t2 | |3 | 5 | data from t2 | data from t2 | data from t2 | |3 | 6 | data from t2 | data from t2 | data from t2 | |4 | 1 | data from t2 | data from t2 | data from t2 | |4 | 2 | data from t2 | data from t2 | data from t2 | |4 | 3 | data from t2 | data from t2 | data from t2 | |4 | 4 | data from t2 | data from t2 | data from t2 | |4 | 5 | data from t2 | data from t2 | data from t2 | |4 | 6 | data from t2 | data from t2 | data from t2 | |4 | 7 | data from t2 | data from t2 | data from t2 | |5 | 1 | data from t2 | data from t2 | data from t2 | |5 | 2 | data from t2 | data from t2 | data from t2 | |6 | 1 | data from t2 | data from t2 | data from t2 | |6 | 2 | data from t2 | data from t2 | data from t2 | |6 | 3 | data from t2 | data from t2 | data from t2 | +--+---+--+--+--+ I can figure most of it out with the exception of the ItemCount value. What I have so far is this, INSERT INTO t1 (GroupCount, ItemCount, Field1, Field2, Field3) SELECT (SELECT Max(GroupCount)+1 From t1), ?, Field1, Field2, Field3 FROM t2; The part with the ? is what I can't figure out. Any ideas? Thanks
On Duplicate Key Update question
I use On Duplicate Key Update a lot and I usually use it like this, Insert Into tablename (myID, Qty) Values (1034,15), (6,13), (5001,1), (145,20) On Duplicate Key Update Qty=Values(Qty); This works very well but now I'd like to do something a little different. I'd like to have a query like this but instead of replacing the value of the previous Qty I'd like it to take the old Qty and the new Qty and store the sum of the two values in the Qty field. Is this possible and can anyone tell me how? Thanks
Re: On Duplicate Key Update question
Sorry for the premature question. I think I figured it out. On Duplicate Key Update Qty=Qty+Values(Qty); I haven't tested it yet but it makes sense that it'll work. Ed Reed [EMAIL PROTECTED] 1/5/07 2:40 PM I use On Duplicate Key Update a lot and I usually use it like this, Insert Into tablename (myID, Qty) Values (1034,15), (6,13), (5001,1), (145,20) On Duplicate Key Update Qty=Values(Qty); This works very well but now I'd like to do something a little different. I'd like to have a query like this but instead of replacing the value of the previous Qty I'd like it to take the old Qty and the new Qty and store the sum of the two values in the Qty field. Is this possible and can anyone tell me how? Thanks
Re: Replication Problem? - SOLVED
It turns out that I have a binlog-ignore-db option configured for
another database. If you have even one of these you must make all
updates in the default database if you want them to be replicated. This
is a change in behavior from 4.0 to 4.1. We upgraded from 4.0 to 4.1 to
5.0 the same day which explains why we did not see this behavior sooner.
I removed the option from the config file and just used
replicate-ignore-db instead and all is right in the world again. Hope
this helps someone else down the road. Thanks for all of your helpful
information and suggestions.
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
-Ed
Logan, David (SST - Adelaide) wrote:
Hi Ed,
You may like to look at this, especially the last statement. If you are
not using these options however, I would suspect a bug.
Regards
--replicate-do-db=db_name
Tell the slave to restrict replication to statements where the default
database (that is, the one selected by USE) is db_name. To specify more
than one database, use this option multiple times, once for each
database. Note that this does not replicate cross-database statements
such as UPDATE some_db.some_table SET foo='bar' while having selected a
different database or no database.
An example of what does not work as you might expect: If the slave is
started with --replicate-do-db=sales and you issue the following
statements on the master, the UPDATE statement is not replicated:
USE prices;
UPDATE sales.january SET amount=amount+1000;
The main reason for this just check the default database behavior is
that it is difficult from the statement alone to know whether it should
be replicated (for example, if you are using multiple-table DELETE
statements or multiple-table UPDATE statements that act across multiple
databases). It is also faster to check only the default database rather
than all databases if there is no need.
If you need cross-database updates to work, use
--replicate-wild-do-table=db_name.% instead. See Section 6.9, How
Servers Evaluate Replication Rules.
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +61 8 8408 4273
_/ _/ _/_/_/ Mobile: +61 417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Atle Veka [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 13 December 2006 10:55 AM
To: Ed Pauley II
Cc: mysql@lists.mysql.com
Subject: Re: Replication Problem?
Ed, this is unfortunately by design. Personally I don't get why this
choice was made...
Reference:
http://lists.mysql.com/mysql/190869
Regards,
Atle
FCI, Inc. - Unix Systems Administrator
On Tue, 12 Dec 2006, Ed Pauley II wrote:
We recently upgraded to MySQL 5.0. Since upgrading I have noticed that
queries of the form INSERT INTO test.test_table VALUES('test','1')
no
longer replicate. If you connect to or change to the test database and
then execute INSERT INTO test_table VALUES('test','1') the query
replicates. Is this normal behavior? Is there a configuration setting
that I can change to make replication accept explicit database naming
in
the query?
Any help would be greatly appreciated!
--
Ed Pauley II
[EMAIL PROTECTED]
--
Ed Pauley II
[EMAIL PROTECTED]
http://www.brisnet.com
http://www.brisbet.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Problem? - SOLVED
It turns out that I have a binlog-ignore-db option configured for
another database. If you have even one of these you must make all
updates in the default database if you want them to be replicated. This
is a change in behavior from 4.0 to 4.1. We upgraded from 4.0 to 4.1 to
5.0 the same day which explains why we did not see this behavior sooner.
I removed the option from the config file and just used
replicate-ignore-db instead and all is right in the world again. Hope
this helps someone else down the road. Thanks for all of your helpful
information and suggestions.
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
-Ed
Logan, David (SST - Adelaide) wrote:
Hi Ed,
You may like to look at this, especially the last statement. If you are
not using these options however, I would suspect a bug.
Regards
--replicate-do-db=db_name
Tell the slave to restrict replication to statements where the default
database (that is, the one selected by USE) is db_name. To specify more
than one database, use this option multiple times, once for each
database. Note that this does not replicate cross-database statements
such as UPDATE some_db.some_table SET foo='bar' while having selected a
different database or no database.
An example of what does not work as you might expect: If the slave is
started with --replicate-do-db=sales and you issue the following
statements on the master, the UPDATE statement is not replicated:
USE prices;
UPDATE sales.january SET amount=amount+1000;
The main reason for this just check the default database behavior is
that it is difficult from the statement alone to know whether it should
be replicated (for example, if you are using multiple-table DELETE
statements or multiple-table UPDATE statements that act across multiple
databases). It is also faster to check only the default database rather
than all databases if there is no need.
If you need cross-database updates to work, use
--replicate-wild-do-table=db_name.% instead. See Section 6.9, How
Servers Evaluate Replication Rules.
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +61 8 8408 4273
_/ _/ _/_/_/ Mobile: +61 417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Atle Veka [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 13 December 2006 10:55 AM
To: Ed Pauley II
Cc: mysql@lists.mysql.com
Subject: Re: Replication Problem?
Ed, this is unfortunately by design. Personally I don't get why this
choice was made...
Reference:
http://lists.mysql.com/mysql/190869
Regards,
Atle
FCI, Inc. - Unix Systems Administrator
On Tue, 12 Dec 2006, Ed Pauley II wrote:
We recently upgraded to MySQL 5.0. Since upgrading I have noticed that
queries of the form INSERT INTO test.test_table VALUES('test','1')
no
longer replicate. If you connect to or change to the test database and
then execute INSERT INTO test_table VALUES('test','1') the query
replicates. Is this normal behavior? Is there a configuration setting
that I can change to make replication accept explicit database naming
in
the query?
Any help would be greatly appreciated!
--
Ed Pauley II
[EMAIL PROTECTED]
--
Ed Pauley II
Internet Software Developer
Bloodstock Research Information Services
859.223.
800.354.9206 ext. 297
[EMAIL PROTECTED]
http://www.brisnet.com
http://www.brisbet.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Problem?
We recently upgraded to MySQL 5.0. Since upgrading I have noticed that
queries of the form INSERT INTO test.test_table VALUES('test','1') no
longer replicate. If you connect to or change to the test database and
then execute INSERT INTO test_table VALUES('test','1') the query
replicates. Is this normal behavior? Is there a configuration setting
that I can change to make replication accept explicit database naming in
the query?
Any help would be greatly appreciated!
--
Ed Pauley II
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Netware 5.1 beta binaries
Can someone tell me what happen to the MySQL 5.1.x-beta for Netware binaries? They aren't in the downloads page anymore. Thanks
Re: Number extraction from a string
Is there anyway to use RegExp in a field parameter? What would be great
is if I could do this,
SELECT Trim(Both RegExp '[a-z]' From value) as value_num, value
FROM num_test
WHERE value REGEXP 'N[1-999]';
The biggest problem is trying to find the position of where the number
starts in the string so that the number by itself can be shown as
returned field.
Chris White [EMAIL PROTECTED] 12/8/06 8:08 AM
On Friday 08 December 2006 01:57, Philip Mather wrote:
You'll need a regex, see these...
http://dev.mysql.com/doc/refman/4.1/en/pattern-matching.html
http://dev.mysql.com/doc/refman/4.1/en/string-comparison-functions.html
http://dev.mysql.com/doc/refman/4.1/en/regexp.html
Yes, you could use a regex and it would work, but if the format of N###
is
persistant and there are no false positives than I'd rather use that
instead
of regexes, which can an intensive operation. Also you might want to
try:
SUBSTR(value,2) instead of LIKE 'N%' just to see how they compare. If
a regex
is required, you could have something like:
SELECT SUBSTRING(value,2) as value_num, value FROM num_test WHERE value
x;
where x is one of the following depending on the situation:
REGEX('N[0-9]+$')
REGEX('N[0-9]+')
REGEX('N[0-9]{3}$')
depends on how specific you want to get really.
--
Chris White
PHP Programmer
Interfuel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
character set for French... confused :\
Hi all, I need a database that is able to handle french characters. I read the Mysql Reference Manual and have done the following in a table to test the different results but I wasn't successful which leads me to believe I am doing something wrong... and yes, I'm a noob. ;) CREATE TABLE companies ( c1 VARCHAR(30) CHARACTER SET utf8, c2 VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci, c3 VARCHAR(30) CHARACTER SET latin1, c4 VARCHAR(30) CHARACTER SET latin1 COLLATE latin1_general_ci, c5 VARCHAR(30) CHARACTER SET dec8, c6 VARCHAR(30) CHARACTER SET dec8 COLLATE dec8_bin, c7 VARCHAR(30) CHARACTER SET hp8, c8 VARCHAR(30) CHARACTER SET hp8 COLLATE hp8_bin, c9 VARCHAR(30) CHARACTER SET cp850, c10 CHAR(30)NOT NULL, c11 TEXTCHARACTER SET latin1 COLLATE latin1_general_ci ); When I insert 'Récré, Vive La Fête! the best I get is R?cr?, Vive La F?te! from c11. Can anyone advise me on what I need done to get these accents show up? Pointers to more documentation are also welcome. Many thanks, -Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: character set for French... confused :\
On Thursday 07 December 2006 10:09, Ed wrote: Hi all, I need a database that is able to handle french characters. Sorry about that, it's probably due to my OS rather than MySQL. $ echo Fête Fête $ touch Fête $ ls -l -rw-r--r-- 1 me me 0 Dec 7 14:20 F?te $ rm Fête Hmmm, back to OS ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Number extraction from a string
Can someone help me with this? I have a text field that sometimes contains a number preceded with the letter N. So it might look like this A test N60 or N45 someother text or This happened. N122, Then there was this. I need to come up with a Select statement that can show me all the instances where there is a N### value and return the number ### as a separate field. Anyone have a quick and easy solution? Thanks
Re: Number extraction from a string
Thanks for the quick reply Chris.
It's close but it's a little off. Your example also returns all
instances that where the letter N exists in another words as well
Do you know of a way to prevent that?
Thanks again
Chris White [EMAIL PROTECTED] 12/7/06 3:11 PM
On Thursday 07 December 2006 14:57, Ed Reed wrote:
Can someone help me with this?
quick hack, but it works from what you've stated:
mysql create table num_test (id SERIAL PRIMARY KEY, value
VARCHAR(256));
Query OK, 0 rows affected (0.07 sec)
mysql insert into num_test (value) VALUES
('N400'),('400'),('300'),('N500');
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql SELECT REPLACE(value,'N','') as value_num, value FROM num_test
WHERE
POSITION('N' IN value);
+---+---+
| value_num | value |
+---+---+
| 400 | N400 |
| 500 | N500 |
+---+---+
2 rows in set (0.00 sec)
--
Chris White
PHP Programmer
Interfuel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
Create stored procedures with Query Browser
Can someone tell me if it is possible to create stored procedures with Query Browser and if it is can you provide an example the works? Thanks
Re: Stored procedure parameters problem
Hi Paul, I've spent the past two days trying to reproduce my problem and I think I may have brought this on myself. I've had been trying to create a procedure with the Query Browser GUI and was unable to do so. So I tried to create them with MySQL-Front and MySQL Control Center and somehow I was able to get the procedure added to the proc table. Once the procedure was created I went into the mysql.proc table with MySQL-Front and modified the values of param_list and body fields to the values I had. So I probably put the strVal VarChar in the param_list without ever going through a Create or Alter Procedure statement. My bad. I'm still not able to create a procedure with Query Browser, which confuses me. I can create one using the command line client but the same commands do not work in Query Browser. That's a problem for another post. Paul DuBois [EMAIL PROTECTED] 12/2/06 10:38 AM At 11:34 AM -0800 12/1/06, Chris White wrote: On Friday 01 December 2006 11:22, Ed Reed wrote: I have a problem/question I'd like to find someone else to verify/answer for me. I'm using MySQL 5.1.09 running on Netware. I've created a stored procedure that has one parameter, Create Procedure MyTest(strVal VarChar) Begin Select Field1, Field2, Field3 From MyTable Where Field3=strVal; End varchar is meant to be variable, so it MUST have a length supplied. If you want the same flexibility without specifying a specific length, use TEXT instead. Hmm ... I don't know. CHAR is equivalent to CHAR(1), but for VARCHAR the length is not optional. For example, if you try to create a table using a length-less VARCHAR, this happens: mysql create table t (c varchar); ERROR 1064 (42000): 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 1 If you are able to create the procedure when no VARCHAR length is given, I think that might be considered a bug. And it's certainly a problem that you get proc table is missing, corrupt, or contains bad data. when attempting to invoke the procedure. Ed, could you file a bug report about this issue at bugs.mysql.com? That way, one of the developers can figure out what's going on and resolve the issue. Thanks. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored procedure parameters problem
I have a problem/question I'd like to find someone else to verify/answer
for me.
I'm using MySQL 5.1.09 running on Netware. I've created a stored
procedure that has one parameter,
Create Procedure MyTest(strVal VarChar)
Begin
Select Field1, Field2, Field3 From MyTable
Where Field3=strVal;
End
When I call the procedure like this,
Call MyTest('test');
Its suppose to return a result set but it fails with an error that the
proc table is missing, corrupt, or contains bad data. But if I create
the procedure like this,
Create Procedure MyTest(strVal VarChar(25))
Begin
Select Field1, Field2, Field3 From MyTable
Where Field3=strVal;
End
and call it the same as before I get the expected result set.
So now the question, should the fact that I specified a VarChar as the
data type indicate that this is supposed to be a variable-length-string
variable with no size limit other than the max size of the data type?
Thanks in advance.
Re: Stored procedure parameters problem
Got it. Thanks Chris White [EMAIL PROTECTED] 12/1/06 11:34 AM On Friday 01 December 2006 11:22, Ed Reed wrote: I have a problem/question I'd like to find someone else to verify/answer for me. I'm using MySQL 5.1.09 running on Netware. I've created a stored procedure that has one parameter, Create Procedure MyTest(strVal VarChar) Begin Select Field1, Field2, Field3 From MyTable Where Field3=strVal; End varchar is meant to be variable, so it MUST have a length supplied. If you want the same flexibility without specifying a specific length, use TEXT instead. -- Chris White PHP Programmer Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
date_add function
I'm having some trouble setting a future date within a table. I have one column 'this_date' which is a DATE field and I'm trying to add 90 days to it and set a column named 'future_date', also a DATE field. I don't know if the problem is that I'm trying to write the value into the 'this_date' and 'future_date' fields in the same query. UPDATE this_table SET this_date = $this_date, future_date = (DATE_ADD(this_date) INTERVAL 90 DAY) Would this work? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date_add function
On Wed, 11 Oct 2006, Mark Leith wrote: Ed Curtis wrote: I'm having some trouble setting a future date within a table. I have one column 'this_date' which is a DATE field and I'm trying to add 90 days to it and set a column named 'future_date', also a DATE field. I don't know if the problem is that I'm trying to write the value into the 'this_date' and 'future_date' fields in the same query. UPDATE this_table SET this_date = $this_date, future_date = (DATE_ADD(this_date) INTERVAL 90 DAY) Would this work? Thanks, Ed UPDATE this_table SET this_date = NOW(), future_date = NOW() + INTERVAL 90 DAY; This is probably along the lines of what you want.. Actually I'm setting the DATE via drop down menus using PHP and creating the date by hand via variables. NOW() won't work in this instance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date_add function
On Wed, 11 Oct 2006, Rolando Edwards wrote: Please check your syntax. It should look like this: UPDATE this_table SET this_date = $this_date, future_date = DATE_ADD($this_date,INTERVAL 90 DAY); Don't forget your WHERE clause or else you populate every row. Tried it, this is what I get back. You have an error in your SQL syntax near 'future_date = date_add(2008-10-20, INTERVAL 90 DAY) WHERE id =' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date_add function
On Wed, 11 Oct 2006, Rolando Edwards wrote:
Oops, also the $this_date
UPDATE this_table SET
this_date = '$this_date',
future_date = DATE_ADD('$this_date',INTERVAL 90 DAY);
Got it going guys, thanks again
Ed
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with query
I'm trying to do a keyword search within a phrase saved in a table. Here's the query: SELECT * from closedtickets WHERE keyphrase LIKE '%$keyword1%' OR keyphrase LIKE '%$keyword2%' OR keyphrase LIKE '%$keyword3%' The problem I'm having is that the query is returning every record in the table. I only want it to return the records where the keywords (any combination) are contained within 'keyphrase' Any ideas? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
More than 4 CPUs?
It seems like I once read that you don't get any performance gains in MySQL when you go above 4 CPUs per server. Is this correct? I was considering a 4 dual-core CPU machine. Should I go with a 2 dual-core machine instead? Thanks! -- Ed Pauley II -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing
Renato Golin wrote: Ed Pauley II wrote: Continuent's m/cluster will not work for me as it does not allow replication across a WAN. Yeah, known problem... We have an offsite backup that needs to be in the replication (2-way to make switching back and forth easy) chain. Why do you need a backup site to write things to your master ? Or did I got wrong ? This is another geographical location with automatic failover if there is a problem, network, hardware etc. with the primary location. When the problem is corrected, or corrects itself the traffic is automatically sent back to the primary location. Without 2-way replication data would be lost. We have been doing this for since MySQL 4.0 was released. I am thinking of a master, slave setup at each location where the masters continue to replicate both ways and then each replicates to it's own slaves. I would like to load balance these slaves on each end. Let me see if I got it right: you have two sites, one master on each, one slave on each., and you want both master to replicate to the other and both slaves to receive data from them as well. Right ? If so, MySQL does not support multi-master setup. It is not a multi-master setup. The master at each location is both master and slave to each other. The slaves are only slaves to the master in their respective locations. My problem is really with how to load balance the slaves at each location. As far as I've heard there is no direct move into that direction from MySQL developers, although a voting system for a new master among one of the slaves can happen in the near future. What may work is to have only one master on one of your sites and both sites update the same master (reducing speed for the slave site) and in the case of failure you switch them (manually or with some tool). hope that helps, --renato -- Ed Pauley II Internet Software Developer Bloodstock Research Information Services 859.223. 800.354.9206 ext. 297 [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing
Peter Zaitsev wrote: On Fri, 2006-08-04 at 15:54 -0400, Ed Pauley II wrote: I am looking into a scale-out solution for MySQL. I have read white papers and searched the web but I can't find a load balancer that claims to work well for MySQL. MySQL's white paper shows NetScaler in the scale-out stack but nothing on Citrix.com mentions MySQL. I also read that friendster wrote a custom script for NetScaler to work in a MySQL environment. I would rather not have to do that. Is there an out-of-box solution for load balancing MySQL. My understanding is that MySQL is a little more complicated than HTTP load balancing, which we already do with Coyote Point Equalizers. I have thought about LVS. Has anyone had any experience with load balancing MySQL? Any recommendations? Thanks in advance. As some people mentioned there is Continuent solution, this is what was Emic networks previously. If you're building solution on your own such as master and number of slaves there are plenty of things to think, regarding load balancing, for example if replication breaks for any reason of falls behind on one of the slaves you might want to kick it up. For very basic setup you even can use DNS for load balancing, which does not solve many of the problems describe. The same kind of simple load balancing is build in MySQL JDBC Driver. In general everyone seems to implement something on their own, working well for their application. Continuent's m/cluster will not work for me as it does not allow replication across a WAN. We have an offsite backup that needs to be in the replication (2-way to make switching back and forth easy) chain. I am thinking of a master, slave setup at each location where the masters continue to replicate both ways and then each replicates to it's own slaves. I would like to load balance these slaves on each end. I have not been able to find an appliance that will balance the reads for me. I have looked into possible solutions such as Sequoia, which I know would require a different setup. Is anyone actually using Sequoia? Does anyone use an appliance for load balancing MySQL? LVS seems like a good possibility but I would rather have an out-of-box solution since I will be repeating this setup at a remote location. -- Ed Pauley II [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Load Balancing
I am looking into a scale-out solution for MySQL. I have read white papers and searched the web but I can't find a load balancer that claims to work well for MySQL. MySQL's white paper shows NetScaler in the scale-out stack but nothing on Citrix.com mentions MySQL. I also read that friendster wrote a custom script for NetScaler to work in a MySQL environment. I would rather not have to do that. Is there an out-of-box solution for load balancing MySQL. My understanding is that MySQL is a little more complicated than HTTP load balancing, which we already do with Coyote Point Equalizers. I have thought about LVS. Has anyone had any experience with load balancing MySQL? Any recommendations? Thanks in advance. -Ed -- Ed Pauley II [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Show tables replacement
Anyone have an idea on this? Thanks Ed Reed [EMAIL PROTECTED] 7/5/06 1:54:25 PM Anyone have an idea on this? Thanks Ed Reed [EMAIL PROTECTED] 6/30/06 2:51:44 PM Opps! Thanks for the quick reply, Dan. But I forgot to mention I'm running MySQL 4.1.11 Any other suggestions? Thanks again. Dan Nelson [EMAIL PROTECTED] 6/30/06 2:48:57 PM In the last episode (Jun 30), Ed Reed said: Is there a Select statement I can use to get table names so that I could use other Select statement syntax on the results? What I'd like to do is this, SHOW Replace(TABLES, 'tbl','') Like 'tbl%'; But this doesn't work so I need a Select statement that can do the same thing. SELECT table_name FROM information_schema.tables; -- Dan Nelson [EMAIL PROTECTED]
Re: Show tables replacement
Thanks for the helpful words. The fact of the matter is that I would love to upgrade but since 5.0x doesn't have all the capabilities of 4.1x, an upgrade is out of the question at this time. So I'm left to looking here for help. Thanks for your time. Anyone else have an idea on this? Jochem van Dieten [EMAIL PROTECTED] 7/6/06 9:47:26 AM On 7/6/06, Ed Reed [EMAIL PROTECTED] wrote: Anyone have an idea on this? Upgrade. Or at least stop repeating the question. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Show tables replacement
Anyone have an idea on this? Thanks Ed Reed [EMAIL PROTECTED] 6/30/06 2:51:44 PM Opps! Thanks for the quick reply, Dan. But I forgot to mention I'm running MySQL 4.1.11 Any other suggestions? Thanks again. Dan Nelson [EMAIL PROTECTED] 6/30/06 2:48:57 PM In the last episode (Jun 30), Ed Reed said: Is there a Select statement I can use to get table names so that I could use other Select statement syntax on the results? What I'd like to do is this, SHOW Replace(TABLES, 'tbl','') Like 'tbl%'; But this doesn't work so I need a Select statement that can do the same thing. SELECT table_name FROM information_schema.tables; -- Dan Nelson [EMAIL PROTECTED]
Show tables replacement
Is there a Select statement I can use to get table names so that I could use other Select statement syntax on the results? What I'd like to do is this, SHOW Replace(TABLES, 'tbl','') Like 'tbl%'; But this doesn't work so I need a Select statement that can do the same thing. Thanks in advance
Re: Show tables replacement
Opps! Thanks for the quick reply, Dan. But I forgot to mention I'm running MySQL 4.1.11 Any other suggestions? Thanks again. Dan Nelson [EMAIL PROTECTED] 6/30/06 2:48:57 PM In the last episode (Jun 30), Ed Reed said: Is there a Select statement I can use to get table names so that I could use other Select statement syntax on the results? What I'd like to do is this, SHOW Replace(TABLES, 'tbl','') Like 'tbl%'; But this doesn't work so I need a Select statement that can do the same thing. SELECT table_name FROM information_schema.tables; -- Dan Nelson [EMAIL PROTECTED]
Replacing A Value
I have a column in a table I need to replace a value of certain records in. The current value is /realtors/Value/. I need to change them to /realtors/This_Value/. Is there an easy way to do this. There are way too many records to do it one record at a time. Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Return virtual records
Thanks for the response Shawn but there's nothing covert here. I really need a list of partnumbers based on the Sum of that part in the table. My users will be marking off the parts in the list and if there a more than one of a partnumber then it needs to show up in the list more than once. - Thanks again Shawn Green [EMAIL PROTECTED] 4/12/06 12:15:56 PM --- Ed Reed [EMAIL PROTECTED] wrote: Thanks for the response but neither one of the responses I've received does exactly what I need since they don't return multiple rows. Are there any other ideas out there? Thanks Jay Blanchard [EMAIL PROTECTED] 4/7/06 12:37:32 PM [snip] Anyone have an idea on this? Can anyone explain how I might be able to return a numbers of records based on the sum of a quantity in a field in the same table? (After I read that it sounds even confusing to me). Let me explain. I have records like this, Part# Qty 1254 5 1414 2 14758 1 1254 6 1024 3 1254 1 Now if I did a query like this Select Part#, Sum(Qty) From table1 Group By Part# Where Part#=1254 I would expect my results to look like this Part# Sum(Qty) 1254 12 But what I really want is this Part# 1254 1254 1254 1254 1254 12541254 1254 12541254 1254 1254 So 12 virtual records for the count of the records returned from the Sum() [/snip] Please do not hijack threads, open a new e-mail and send it to the list address. SELECT REPEAT(Part#, count(Part#)) FROM table WHERE Part# = '1254' What you are asking MySQL to do is not a normal request. There are probably better ways to solve your issue than by creating fake or virtual data. As was posted before, what is the real reason you want to auto-generate separate rows of data? If we understood your REAL problem (not your request, we understand that) we could probably help you find a faster, more robust solution than the one you are proposing. Shawn Green Database Administrator Unimin Corporation - Spruce Pine __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Multi Threaded on RedHat ES 4
I recently changed from SuSE ES 8 to RedHat ES 4 running MySQL 4.0.26 (I will be upgrading to at least 4.1 after busy season). I compiled my own version this time and when I start the server I see only one mysqld process. I am used to seeing hundreds as my max connect is set to 1000. This is for a very busy website. Did I miss a compile option? Is this an OS thing? I am putting this thing live and we have a couple of very busy weekends coming up. Any help would be appreciated. Thanks in adv! Ed -- Ed Pauley II Internet Software Developer Bloodstock Research Information Services 859.223. 800.354.9206 ext. 297 [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi Threaded on RedHat ES 4
I think this article explains what I am seeing but I don't know how to determine which threading I am using. I just know it is not user threads in a separate process. http://dev.mysql.com/doc/refman/4.1/en/thread-packages.html Ed Pauley II wrote: I recently changed from SuSE ES 8 to RedHat ES 4 running MySQL 4.0.26 (I will be upgrading to at least 4.1 after busy season). I compiled my own version this time and when I start the server I see only one mysqld process. I am used to seeing hundreds as my max connect is set to 1000. This is for a very busy website. Did I miss a compile option? Is this an OS thing? I am putting this thing live and we have a couple of very busy weekends coming up. Any help would be appreciated. Thanks in adv! Ed -- Ed Pauley II Internet Software Developer Bloodstock Research Information Services 859.223. 800.354.9206 ext. 297 [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: Re: mysqldumps from java program]
-- Ed Pauley II Internet Software Developer Bloodstock Research Information Services 859.223. 800.354.9206 ext. 297 [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com ---BeginMessage--- If I am not mistaken deleting from the production server would delete the data on the slave in a replication environment. William Fong wrote: Is it possible to setup replication so you would have another server to do backups on? Replicate the data, do whatever you want to the spare, and then delete the data from the production server. On 4/19/06, balaraju mandala [EMAIL PROTECTED] wrote: Hi Everybody, I need a suggestion regarding mysqldump. My problem is my application is creatiing around 500Mb of data per day. As i want my application run 24*7*365. I need a mechanisem where i can move, previous day's data to another location(i.e) at any given time i just want to store one or two days data only in my current DB. So i planned to make this by using mysqldump, as u know it will create files which we can upload where ever we need. Is this is a good idea, or we have another better mechanisem? I am planning to automate this using Java. Is we have any prebiuild tools for this? -- Ed Pauley II Internet Software Developer Bloodstock Research Information Services 859.223. 800.354.9206 ext. 297 [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com ---End Message--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Fwd: Re: mysqldumps from java program]
out of curiosity, how do you do that? Daniel da Veiga wrote: If I am not mistaken deleting from the production server would delete the data on the slave in a replication environment. Not if you disable the binlog of the query that will delete data... -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- Ed Pauley II Internet Software Developer Bloodstock Research Information Services 859.223. 800.354.9206 ext. 297 [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Fwd: Re: mysqldumps from java program]
Daniel da Veiga wrote:
On 4/19/06, Ed Pauley II [EMAIL PROTECTED] wrote:
out of curiosity, how do you do that?
Daniel da Veiga wrote:
If I am not mistaken deleting from the production server would delete
the data on the slave in a replication environment.
Don't get me wrong, try not to top-post, it makes the message harder to read...
SET SQL_LOG_BIN = {0|1}
Disables or enables binary logging for the current connection
(SQL_LOG_BIN is a session variable) if the client connects using an
account that has the SUPER privilege. The statement is ignored if the
client does not have that privilege.
So, if you DELETE data with an account that has the SUPER privilege,
you just issue this SET command before any statment and it won't log
your subsequent queries.
BTW, it was quoted fromt he MySQL Manual.
--
Daniel da Veiga
Computer Operator - RS - Brazil
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
--END GEEK CODE BLOCK--
I just found it myself. Thanks for the detailed reply.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Return virtual records
Thanks for the response but neither one of the responses I've received does exactly what I need since they don't return multiple rows. Are there any other ideas out there? Thanks Jay Blanchard [EMAIL PROTECTED] 4/7/06 12:37:32 PM [snip] Anyone have an idea on this? Can anyone explain how I might be able to return a numbers of records based on the sum of a quantity in a field in the same table? (After I read that it sounds even confusing to me). Let me explain. I have records like this, Part# Qty 1254 5 1414 2 14758 1 1254 6 1024 3 1254 1 Now if I did a query like this Select Part#, Sum(Qty) From table1 Group By Part# Where Part#=1254 I would expect my results to look like this Part# Sum(Qty) 1254 12 But what I really want is this Part# 1254 1254 1254 1254 1254 12541254 1254 12541254 1254 1254 So 12 virtual records for the count of the records returned from the Sum() [/snip] Please do not hijack threads, open a new e-mail and send it to the list address. SELECT REPEAT(Part#, count(Part#)) FROM table WHERE Part# = '1254' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Return virtual records
Anyone have an idea on this? Can anyone explain how I might be able to return a numbers of records based on the sum of a quantity in a field in the same table? (After I read that it sounds even confusing to me). Let me explain. I have records like this, Part#Qty 1254 5 1414 2 147581 1254 6 1024 3 1254 1 Now if I did a query like this Select Part#, Sum(Qty) From table1 Group By Part# Where Part#=1254 I would expect my results to look like this Part# Sum(Qty) 1254 12 But what I really want is this Part# 1254 1254 1254 1254 1254 12541254 1254 12541254 1254 1254 So 12 virtual records for the count of the records returned from the Sum() Can someone help me with this? - Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very long query for such a simple result
That's not really what I meant. I know what the function SUM() does. But Sum()
takes an expression and '1' doesn't seem like much of an expression to me. So
what is that 1 equates to and where in the MySQL documentation can I find this
explained.
Thanks again.
Peter Brawley [EMAIL PROTECTED] 4/5/06 10:28 AM
Hi Ed,
Count(1) works just as well. Sum(1) just adds 1 for each row so it's logically
equivalent.
PB
-
Ed Reed wrote: WOW!!! THAT WAS AWESOME!!! Thanks a lot Peter. Ok, so what is
SUM(1)? How is it able to do this? And where can I learn more about it? Thanks
again. Peter Brawley [EMAIL PROTECTED] 4/4/06 10:13:00 PM Ed,e:
Thanks for the quick reply Peter. Unfortunately, this is all legacy stuff that
I have to make work together and the problemreport table does not reference the
employeeid in the employees table. It was all create about ten years ago and
the data has just always been migrated to the db du jour. I'm currently using
MySQL 4.1x but most importantly I must be able to run the entire query in a
single call. Thanks for the help. OK, that's doable in a subquery, and you can
get the total from SUM(1), so something like ...SELECT IF( SUM(1) = 0,
'', CONCAT( 'You have ', SUM(1), ' Problem
Report', IF(SUM(1) = 1,'','s'), ': Priorities(High=',
SUM(IF(Priority='High',1,0)), ',Med=',
SUM(IF(Priority='Med',1,0)), ',Low=',
SUM(IF(Priority='Low' ,1,0)), ')'))FROM ( SELECT
Priority FROM ProblemReports, Employees WHERE ProblemReports.Status='Open'
AND ProblemReports.Responsible=CONCAT(Employees.FirstName, '
',Employees.LastName) AND Employees.DateTerminated IS NULL AND
Employees.UserName='User1') AS priorities;PB- Peter Brawley [EMAIL
PROTECTED] 4/4/06 2:35:49 PM Ed,The big slowdown in your query is
likely the join on ProblemReports.Responsible = CONCAT(Employees.FirstName, '
',Employees.LastName)) Eek. Does the employees table not have a primary key,
and does the problemreports table not reference that key? If not, I would
think a first priority would be to fix that.Meanwhile, you can simplify your
monster query by writing the user's problem priorities to a temp table, then
doing a simple pivot on priority to generate your sentence. Something like
...DROP TEMPORARY TABLE IF EXISTS ProbSum;CREATE TEMPORARY TABLE ProbSumSELECT
Priority FROM ProblemReports, Employees WHERE ProblemReports.Status='Open' AND
ProblemReports.Responsible=CONCAT(Employees.FirstName, ' ',Employees.LastName)
AND Employees.DateTerminated IS NULL AND Employees.UserName='User1'SELECT
COUNT(*) INTO @n FROM ProbSum;SELECT IF( @n = 0, '', CONCAT( 'You
have', @n, 'Problem Report',
IF(@n=1,'','s'), ': Priorities(High=',
SUM(IF(Priority='High',1,0)), ',Med=',
SUM(IF(Priority='Med',1,0)), ',Low=',
SUM(IF(Priority='Low' ,1,0)), ')'))FROM
probsum;DROP TEMPORARY TABLE probsum;All this would be easier in a stored
procedure, if you have MySql 5.PB -Ed Reed wrote: Can someone help me
simplify this query please? It's meant to return a single string result that
looks something like this, You have 12 open Problem Reports:
Priorities(High=5, Med=6, Low=1) The relavent columns from the two tables are
Table: ProblemReportsFields: PRNo, Status, Priority, Responsible Table:
EmployeesFields: Firstname, Lastname, DateTerminated, UserName Here's the query
Select IF((@a:=(SELECT Count(PRNo) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1')))0, If (@a=1, ConCat('You have one open Problem
Report: Prioritiy = ', (Select Priority From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1'))), Concat('You have ', @a, ' open Problem
Reports: Priorities(', (Select ConCat('High=',Count(Priority)) From
ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='High')),', ',(Select
ConCat('Med=',Count(Priority)) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='Med')),', ',(Select
ConCat('Low=',Count(Priority)) From ProblemReports, Employees WHERE
Return virtual records
Can anyone explain how I might be able to return a numbers of records based on the sum of a quantity in a field in the same table? (After I read that it sounds even confusing to me). Let me explain. I have records like this, Part#Qty 1254 5 1414 2 147581 1254 6 1024 3 1254 1 Now if I did a query like this Select Part#, Sum(Qty) From table1 Group By Part# Where Part#=1254 I would expect my results to look like this Part# Sum(Qty) 1254 12 But what I really want is this Part# 1254 1254 1254 1254 1254 12541254 1254 12541254 1254 1254 So 12 virtual records for the count of the records returned from the Sum() Can someone help me with this? - Thanks
Re: very long query for such a simple result
WOW!!! THAT WAS AWESOME!!!
Thanks a lot Peter. Ok, so what is SUM(1)? How is it able to do this? And where
can I learn more about it?
Thanks again.
Peter Brawley [EMAIL PROTECTED] 4/4/06 10:13:00 PM
Ed,e: Thanks for the quick reply Peter. Unfortunately, this is all legacy stuff
that I have to make work together and the problemreport table does not
reference the employeeid in the employees table. It was all create about ten
years ago and the data has just always been migrated to the db du jour. I'm
currently using MySQL 4.1x but most importantly I must be able to run the
entire query in a single call. Thanks for the help. OK, that's doable in a
subquery, and you can get the total from SUM(1), so something like ...
SELECT
IF( SUM(1) = 0,
'',
CONCAT( 'You have ',
SUM(1),
' Problem Report',
IF(SUM(1) = 1,'','s'),
': Priorities(High=',
SUM(IF(Priority='High',1,0)),
',Med=',
SUM(IF(Priority='Med',1,0)),
',Low=',
SUM(IF(Priority='Low' ,1,0)),
')'
)
)
FROM (
SELECT Priority
FROM ProblemReports, Employees
WHERE ProblemReports.Status='Open'
AND ProblemReports.Responsible=CONCAT(Employees.FirstName, '
',Employees.LastName)
AND Employees.DateTerminated IS NULL
AND Employees.UserName='User1'
) AS priorities;
PB
-
Peter Brawley [EMAIL PROTECTED] 4/4/06 2:35:49 PM Ed,The big
slowdown in your query is likely the join on ProblemReports.Responsible =
CONCAT(Employees.FirstName, ' ',Employees.LastName)) Eek. Does the employees
table not have a primary key, and does the problemreports table not reference
that key? If not, I would think a first priority would be to fix
that.Meanwhile, you can simplify your monster query by writing the user's
problem priorities to a temp table, then doing a simple pivot on priority to
generate your sentence. Something like ...DROP TEMPORARY TABLE IF EXISTS
ProbSum;CREATE TEMPORARY TABLE ProbSumSELECT Priority FROM ProblemReports,
Employees WHERE ProblemReports.Status='Open' AND
ProblemReports.Responsible=CONCAT(Employees.FirstName, ' ',Employees.LastName)
AND Employees.DateTerminated IS NULL AND Employees.UserName='User1'SELECT
COUNT(*) INTO @n FROM ProbSum;SELECT IF( @n = 0, '', CONCAT( 'You
have', @n, 'Problem Report',
IF(@n=1,'','s'), ': Priorities(High=',
SUM(IF(Priority='High',1,0)), ',Med=',
SUM(IF(Priority='Med',1,0)), ',Low=',
SUM(IF(Priority='Low' ,1,0)), ')'))FROM
probsum;DROP TEMPORARY TABLE probsum;All this would be easier in a stored
procedure, if you have MySql 5.PB -Ed Reed wrote: Can someone help me
simplify this query please? It's meant to return a single string result that
looks something like this, You have 12 open Problem Reports:
Priorities(High=5, Med=6, Low=1) The relavent columns from the two tables are
Table: ProblemReportsFields: PRNo, Status, Priority, Responsible Table:
EmployeesFields: Firstname, Lastname, DateTerminated, UserName Here's the query
Select IF((@a:=(SELECT Count(PRNo) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1')))0, If (@a=1, ConCat('You have one open Problem
Report: Prioritiy = ', (Select Priority From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1'))), Concat('You have ', @a, ' open Problem
Reports: Priorities(', (Select ConCat('High=',Count(Priority)) From
ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='High')),', ',(Select
ConCat('Med=',Count(Priority)) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='Med')),', ',(Select
ConCat('Low=',Count(Priority)) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='Low')),')')),''); No virus found
in this incoming message.Checked by AVG Free Edition.Version: 7.1.385 / Virus
Database: 268.3.5/300 - Release Date: 4/3/2006No virus found in this
incoming message.Checked by AVG
very long query for such a simple result
Can someone help me simplify this query please? It's meant to return a single
string result that looks something like this,
You have 12 open Problem Reports: Priorities(High=5, Med=6, Low=1)
The relavent columns from the two tables are
Table: ProblemReports
Fields: PRNo, Status, Priority, Responsible
Table: Employees
Fields: Firstname, Lastname, DateTerminated, UserName
Here's the query
Select IF((@a:=(SELECT Count(PRNo) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1')))0,
If (@a=1, ConCat('You have one open Problem Report: Prioritiy = ',
(Select Priority From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1'))),
Concat('You have ', @a, ' open Problem Reports: Priorities(',
(Select ConCat('High=',Count(Priority)) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And
(Priority='High')),', ',
(Select ConCat('Med=',Count(Priority)) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And
(Priority='Med')),', ',
(Select ConCat('Low=',Count(Priority)) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And
(Priority='Low')),')')),'');
Re: very long query for such a simple result
Sorry, here's the results. BTW the query works it just seems overly complex and I'd like to streamline it. CREATE TABLE `employees` ( `EmployeeID` int(11) NOT NULL auto_increment, `FirstName` varchar(50) default NULL, `LastName` varchar(50) default NULL, `DateTerminated` datetime default NULL, `UserName` varchar(15) default NULL, PRIMARY KEY (`EmployeeID`) UNIQUE KEY `EmployeeID` (`EmployeeID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE `problemreports` ( `PRNo` double NOT NULL auto_increment, `Status` varchar(10) default NULL, `Responsible` varchar(20) default NULL, `Priority` varchar(10) default NULL, PRIMARY KEY (`PRNo`), UNIQUE KEY `PRNo` (`PRNo`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 [EMAIL PROTECTED] 4/4/06 1:56:10 PM You are new at this, aren't you? First thing you need to do is to let us see exactly what we need to deal with. Please respond with the results of the following two commands SHOW CREATE TABLE Employees\G SHOW CREATE TABLE ProblemReports\G We (the list members) will be able to help straighten you out from there (there are lots of things we need to talk about but I think that we should get you working first, ok?) Always CC the list (or hit the REPLY TO ALL button or whatever you have in your email client). That way everyone on the list stays informed of the progress of this issue. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: very long query for such a simple result
Thanks for the quick reply Peter. Unfortunately, this is all legacy stuff that
I have to make work together and the problemreport table does not reference the
employeeid in the employees table. It was all create about ten years ago and
the data has just always been migrated to the db du jour. I'm currently using
MySQL 4.1x but most importantly I must be able to run the entire query in a
single call.
Thanks for the help.
Peter Brawley [EMAIL PROTECTED] 4/4/06 2:35:49 PM
Ed,
The big slowdown in your query is likely the join on
ProblemReports.Responsible = CONCAT(Employees.FirstName, '
',Employees.LastName))
Eek. Does the employees table not have a primary key, and does the
problemreports table not reference that key? If not, I would think a first
priority would be to fix that.
Meanwhile, you can simplify your monster query by writing the user's problem
priorities to a temp table, then doing a simple pivot on priority to generate
your sentence. Something like ...
DROP TEMPORARY TABLE IF EXISTS ProbSum;
CREATE TEMPORARY TABLE ProbSum
SELECT Priority
FROM ProblemReports, Employees
WHERE ProblemReports.Status='Open'
AND ProblemReports.Responsible=CONCAT(Employees.FirstName, '
',Employees.LastName)
AND Employees.DateTerminated IS NULL
AND Employees.UserName='User1'
SELECT COUNT(*) INTO @n FROM ProbSum;
SELECT
IF( @n = 0,
'',
CONCAT( 'You have',
@n,
'Problem Report',
IF(@n=1,'','s'),
': Priorities(High=',
SUM(IF(Priority='High',1,0)),
',Med=',
SUM(IF(Priority='Med',1,0)),
',Low=',
SUM(IF(Priority='Low' ,1,0)),
')'
)
)
FROM probsum;
DROP TEMPORARY TABLE probsum;
All this would be easier in a stored procedure, if you have MySql 5.
PB
-
Ed Reed wrote:
Can someone help me simplify this query please? It's meant to return a single
string result that looks something like this, You have 12 open Problem
Reports: Priorities(High=5, Med=6, Low=1) The relavent columns from the two
tables are Table: ProblemReportsFields: PRNo, Status, Priority, Responsible
Table: EmployeesFields: Firstname, Lastname, DateTerminated, UserName Here's
the query Select IF((@a:=(SELECT Count(PRNo) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1')))0, If (@a=1, ConCat('You have one open Problem
Report: Prioritiy = ', (Select Priority From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1'))), Concat('You have ', @a, ' open Problem
Reports: Priorities(', (Select ConCat('High=',Count(Priority)) From
ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='High')),', ',(Select
ConCat('Med=',Count(Priority)) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='Med')),', ',(Select
ConCat('Low=',Count(Priority)) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='Low')),')')),''); No virus found
in this incoming message.Checked by AVG Free Edition.Version: 7.1.385 / Virus
Database: 268.3.5/300 - Release Date: 4/3/2006
Re: Migration toolkit
YIKES! :) - Thanks Shawn [EMAIL PROTECTED] 2/14/06 10:08:14 AM I haven't tried the migration toolkit so I don't know what it can or cannot do. I am assuming that you have hand-transferred a few users from your old system to the new one. What I can suggest is that you generate two sets of data. The first is a list of your users, their hostnames, and their password hashes from your old server: CREATE TABLE oldUsers SELECT user, host, password FROM mysql.user; You can use the utility mysqldump to convert oldUsers table to a SQL script. Save this dump into a text file (oldusers.sql) for later. Next you need to run a bunch of SHOW GRANTS for statements. A script (pick your favorite scripting language for this) can crawl through oldUsers (just created) and capture the results of SHOW GRANTS FOR 'user'@'host' for every user in the oldUsers table. Save the results into another text file (oldgrants.sql). These will be the SQL statements you will need to restore permissions to your current users in your new database. Now comes the fun part: I prefer to enter the CLI and navigate to the correct database by hand before executing scripts like these (I have seen many people just do this from the command line but I would rather be sure). On your new server, start your mysql CLI and navigate to the mysql database. Once there, execute the script that generates the oldUsers table. The sequence should look something like this: mysql -u yourlogin -p mysql provide your password mysql source full_path_to_oldusers.sql That should create a table of all of your user accounts in the table oldUsers in the mysql database of your new server. Bulk insert them into your users table like this INSERT IGNORE user (user, host, password) SELECT user, host, password FROM oldUsers; And refresh the permissions cache: FLUSH PRIVELEGES; Now you are ready to re-apply privileges. Assuming that you correctly captured the GRANT statements from your SHOW GRANTS for script, you should be able to say. mysql source full_path_to_oldgrants.sql and do one last FLUSH PRIVILEGES; Your old accounts should now exist on your new server with their old permissions restored. Sorry but you asked for any ideas... ;-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ed Reed [EMAIL PROTECTED] wrote on 02/14/2006 12:11:05 PM: Does anyone have any idea on this one? - Thanks Ed Reed [EMAIL PROTECTED] 2/10/06 3:09 PM I'm trying to use the Migration Toolkit to migrate a 4.1 system to a 5.0 system. It doesn't appear that it can successfully migrate my usernames and privileges. Is it supposed to be able to and is there anything special I need to do to make it happen? - Thanks
Re: Migration toolkit
Does anyone have any idea on this one? - Thanks Ed Reed [EMAIL PROTECTED] 2/10/06 3:09 PM I'm trying to use the Migration Toolkit to migrate a 4.1 system to a 5.0 system. It doesn't appear that it can successfully migrate my usernames and privileges. Is it supposed to be able to and is there anything special I need to do to make it happen? - Thanks
Migration toolkit
I'm trying to use the Migration Toolkit to migrate a 4.1 system to a 5.0 system. It doesn't appear that it can successfully migrate my usernames and privileges. Is it supposed to be able to and is there anything special I need to do to make it happen? - Thanks
Re: Converting decimal to binary
Can you (or anyone else) explain to me how, or point me somewhere that I can learn how this works? I'd really like to know more about how bitwise arithmetic works. Thanks Francesco Riosa [EMAIL PROTECTED] 1/10/06 4:58:47 PM Francesco Riosa wrote: And another one is (in inverse order for laziness): select (8 1) AS `0` , (8 2 1) AS `1` , (8 4 1) AS `2` , (8 8 1) AS `3` , (8 16 1) AS `4` , (8 32 1) AS `5` , (8 64 1) AS `6` , (8 128 1) AS `7` ; but this one looks better: select (8 1) AS `0` , (8 1 1) AS `1` , (8 2 1) AS `2` , (8 3 1) AS `3` , (8 4 1) AS `4` , (8 5 1) AS `5` , (8 6 1) AS `6` , (8 7 1) AS `7` ; http://dev.mysql.com/doc/refman/4.1/en/bit-functions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
