Re: 5.1 to 5.6 upgrade: is it possible?
Hello Sabika, On 12/4/2013 2:56 PM, Sabika Makhdoom wrote: I would from 5.1.40 to 5.5.8 first and then to 5.6 Why 5.5.8? Our current release is 5.5.35. 5.5.8 is an ancient release of 5.5 and represents the very first GA release of 5.5 . There have been 27 additional releases (each with their own set of improvements and bug fixes) since then. If anyone cannot perform a stepwise binary upgrade (...->4.1->5.0->5.5->5.6->5.7-> ... ) , then dump your 5.1 data and restore it to an empty installation of your target version. (Note 5.7 is not yet GA but is available for testing and planning purposes). A binary upgrade is where you change out the installed package (the binaries and their support files) followed by an execution of the utility mysql_upgrade followed by performing any corrective actions that mysql_upgrade identifies. Warmest regards, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: 5.1 to 5.6 upgrade: is it possible?
I would from 5.1.40 to 5.5.8 first and then to 5.6 On Dec 4, 2013, at 11:33 AM, Ilya Kazakevich wrote: > Hello, > > Have anybody tried to upgrade 5.1 to 5.6? I believe running mysql_upgrade > should be enough, but does there are some caveats? > > Ilya Kazakevich. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: 5.1 to 5.6 upgrade: is it possible?
On 2013-12-04 1:33 PM, Ilya Kazakevich wrote: Hello, Have anybody tried to upgrade 5.1 to 5.6? I believe running mysql_upgrade should be enough, but does there are some caveats? Ilya Kazakevich. The MySQL recommendation is to upgrade one major version at a time, ie 5.1->5.5->5.6. There are so many 5.1->5.5 incompatibilites (eg see http://www.artfulsoftware.com/infotree/mysql_incompat.html), I think the advice is sound. PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
5.1 to 5.6 upgrade: is it possible?
Hello, Have anybody tried to upgrade 5.1 to 5.6? I believe running mysql_upgrade should be enough, but does there are some caveats? Ilya Kazakevich.
Re: Is it possible to make this more efficient?
- Original Message - > From: "Sergei Petrunia" > > Create index on (importance, company_id) (or > (company_id,importance), should > not matter). When that index is present, the query should use ref > access on it using both key parts (if by some crazy reason it doesn't, use > FORCE > INDEX to make it to). This way, you will be scanning as few rows as possible. Umm... It might just be a matter of cardinality? :-) While the optimizer can be wrong at times (and it's certainly worth checking that) it does usually pick the best choices. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is it possible to make this more efficient?
On Wed, Oct 19, 2011 at 05:09:13PM +1100, Wayne W wrote: > Hi, > > I asked this question over on stackoverflow - basically I have a query > and when using EXPLAIN I see that around 2400 rows are still being > scanned. I'd added various indexes but it cannot make it perform any > better. > > I would appreciate if anyone has any further ideas? > > http://stackoverflow.com/questions/7793393/what-can-i-do-to-make-this-sql-more-effecient-table-has-850k-rows > Create index on (importance, company_id) (or (company_id,importance), should not matter). When that index is present, the query should use ref access on it using both key parts (if by some crazy reason it doesn't, use FORCE INDEX to make it to). This way, you will be scanning as few rows as possible. Also, check if the EXPLAIN has "Using index". If it doesn't, add `id` to the index, i.e. use INDEX (importance, company_id, id) That way, the query will be only using index, which will give the best possible speed. BR Sergey -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Is it possible to make this more efficient?
Hi, I asked this question over on stackoverflow - basically I have a query and when using EXPLAIN I see that around 2400 rows are still being scanned. I'd added various indexes but it cannot make it perform any better. I would appreciate if anyone has any further ideas? http://stackoverflow.com/questions/7793393/what-can-i-do-to-make-this-sql-more-effecient-table-has-850k-rows Reply here of course thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: hi.. is it possible
MuraliKrishna wrote: Hi is it possible to have two instances of mysql over single windows xp. because I want to workout with those as server and client. please help me regarding this. Sure you can! Read this and get back to us with questions: http://dev.mysql.com/doc/refman/5.1/en/multiple-servers.html -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: hi.. is it possible
Yes you can, but port has to be different. Regards, Thiyaghu CK www.mafiree.com On Sat, Feb 6, 2010 at 2:50 PM, MuraliKrishna < murali_kris...@arthaoptions.com> wrote: > Hi is it possible to have two instances of mysql over single windows xp. > because I want to workout with those as server and client. please help me > regarding this. > >
hi.. is it possible
Hi is it possible to have two instances of mysql over single windows xp. because I want to workout with those as server and client. please help me regarding this.
Re: Is It Possible to create a Join with Multiple Tables
c...@hosting4days.com wrote: :Newbie: Short Question: Is it possible to create an inner join (or another join) with multiple tables? something like $query_list1 = "SELECT table1.id,table1.status, table2.id, table3.id (more here - etc.) FROM table1 INNER JOIN table2 ON table1.id = table2.id INNER JOIN table3 ON table2.id = table3.id WHERE table1.status = 'open'"; Q: Is something like this possible? The easiest way to find out if something might work is to try it. Did you actually try the statement you posted above? Assuming your typing was correct, it should have worked just fine. For future reference, here is the JOIN page in the MySQL manual: http://dev.mysql.com/doc/refman/5.1/en/join.html Suggestion -- If you have a problem: document what you did, what you did it to, and the error message you received. Include any alternatives you have tried and enough explanation that we can figure out what you are trying to accomplish. Normally this will get you correct and accurate responses very quickly. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is It Possible to create a Join with Multiple Tables
I don't see why not, but another choice is to create a view that encompasses all these tables and Select * From my_view. Arthur On Fri, Sep 25, 2009 at 11:28 PM, c...@hosting4days.com < c...@hosting4days.com> wrote: > :Newbie: > Short Question: Is it possible to create an inner join (or another join) > with multiple tables? > > something like > > $query_list1 = "SELECT table1.id,table1.status, table2.id, table3.id (more > here - etc.) FROM table1 > INNER JOIN table2 ON table1.id = table2.id > INNER JOIN table3 ON table2.id = table3.id > WHERE table1.status = 'open'"; > > > Q: Is something like this possible? > > > > Thanks, > c...@hosting4days.com > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=fuller.art...@gmail.com > >
Is It Possible to create a Join with Multiple Tables
:Newbie: Short Question: Is it possible to create an inner join (or another join) with multiple tables? something like $query_list1 = "SELECT table1.id,table1.status, table2.id, table3.id (more here - etc.) FROM table1 INNER JOIN table2 ON table1.id = table2.id INNER JOIN table3 ON table2.id = table3.id WHERE table1.status = 'open'"; Q: Is something like this possible? Thanks, c...@hosting4days.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is it possible to convert column data into rows
I have a column which contains a list of IDs separated by commas. How can I take the content of this column, and insert it into a temporary table so that each ID is on a separate row? Can this be done using SQL? Can be done in a stored procedure, or (more easily) in an application language. PB - Khai Doan wrote: I have a column which contains a list of IDs separated by commas. How can I take the content of this column, and insert it into a temporary table so that each ID is on a separate row? Can this be done using SQL? Thanks! Khai No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.339 / Virus Database: 270.12.37/2130 - Release Date: 05/23/09 07:00:00
Is it possible to convert column data into rows
I have a column which contains a list of IDs separated by commas. How can I take the content of this column, and insert it into a temporary table so that each ID is on a separate row? Can this be done using SQL? Thanks! Khai -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Richard, >1) If a user has never logged in he doesn't show the user in the list >2) It doesn't count if it is 0 it's not on the liste and if the user has logged >in more than once the result is 1 (because of the group by ...). Do you mean by #1 that you want to list all users whether they have logged in or not? #2 is less clear still; does it mean the query is to show a count of 0 for no logins and 1 for any positive number of logins? If so, try... SELECT a.username, a.first_name, a.last_name,COUNT(DISTINCT b.username) AS count FROM user_list a LEFT JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; PB Richard wrote: Hi, and thankyou for trying to help me out! I've tried this and it does not work. Here are the problems : 1) If a user has never logged in he doesn't show the user in the list 2) It doesn't count if it is 0 it's not on the liste and if the user has logged in more than once the result is 1 (because of the group by ...). Thankyou Peter Brawley a écrit : Richard, >Can I do something like this : >SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count >FROM login_table b WHERE a.username = b.username) FROM user_list a Try ... SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count FROM user_list a JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; PB - Richard wrote: Hello, This time I'm rearly not sure if this is possible to do. I've got two queries that I would like to bring together to make only one query ... I've got a list of users And also a login table I would like to list all users and show the number of times they have logged in. So to get the list of users I would do : SELECT username, first_name, last_name FROM user_list And to count the number of connections I would do SELECT COUNT(*) AS count FROM login_table WHERE username = $result['username'] Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a I know that the above query can not work but It's just to give a better idea about what I'm trying to do . :) If I do a join, I will the username repeated for each login. Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Try this one: SELECT a.username, a.first_name, a.last_name,COALESCE(COUNT(b.username), 0) AS count FROM user_list a LEFT JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; The LEFT JOIN will ensure you still get a result row even if there are no matching rows in `login_table`. And the COALESCE will give you a value of 0 instead of NULL for the count, in that case. On Feb 19, 2008, at 5:29 PM, Richard wrote: Sorry it's me again, I made a mistake, it counts the number of logins correctly, but does not show members with 0 logins ! Any idea how to do this? Thanks :) Peter Brawley a écrit : Richard, >Can I do something like this : >SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count >FROM login_table b WHERE a.username = b.username) FROM user_list a Try ... SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count FROM user_list a JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; PB - Richard wrote: Hello, This time I'm rearly not sure if this is possible to do. I've got two queries that I would like to bring together to make only one query ... I've got a list of users And also a login table I would like to list all users and show the number of times they have logged in. So to get the list of users I would do : SELECT username, first_name, last_name FROM user_list And to count the number of connections I would do SELECT COUNT(*) AS count FROM login_table WHERE username = $result['username'] Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a I know that the above query can not work but It's just to give a better idea about what I'm trying to do . :) If I do a join, I will the username repeated for each login. Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Actually, this works too: SELECT a.username, a.first_name, a.last_name, Count(b.username) as count FROM user_list a LEFT OUTER JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; __ Try SELECT a.username, a.first_name, a.last_name, SUM(case when b.username is null then 0 else 1 end) as count FROM user_list a LEFT OUTER JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; Donna Richard <[EMAIL PROTECTED]> 02/19/2008 05:29 PM To [EMAIL PROTECTED], mysql@lists.mysql.com cc Subject Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ? Sorry it's me again, I made a mistake, it counts the number of logins correctly, but does not show members with 0 logins ! Any idea how to do this? Thanks :) Peter Brawley a écrit : > Richard, > > >Can I do something like this : > >SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count > >FROM login_table b WHERE a.username = b.username) FROM user_list a > > Try ... > > SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count > FROM user_list a > JOIN login_table b ON a.username = b.username > GROUP BY a.username,a.first_name,a.lastname; > > PB > > - > > Richard wrote: >> Hello, >> >> This time I'm rearly not sure if this is possible to do. I've got two >> queries that I would like to bring together to make only one query ... >> >> I've got a list of users >> >> And also a login table >> >> I would like to list all users and show the number of times they have >> logged in. >> >> So to get the list of users I would do : >> >> SELECT username, first_name, last_name FROM user_list >> >> And to count the number of connections I would do >> >> SELECT COUNT(*) AS count FROM login_table WHERE username = >> $result['username'] >> >> Can I do something like this : >> >> SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS >> count FROM login_table b WHERE a.username = b.username) FROM user_list a >> >> I know that the above query can not work but It's just to give a >> better idea about what I'm trying to do . :) >> >> If I do a join, I will the username repeated for each login. >> >> Thanks in advance, >> >> Richard >> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Try SELECT a.username, a.first_name, a.last_name, SUM(case when b.username is null then 0 else 1 end) as count FROM user_list a LEFT OUTER JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; Donna Richard <[EMAIL PROTECTED]> 02/19/2008 05:29 PM To [EMAIL PROTECTED], mysql@lists.mysql.com cc Subject Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ? Sorry it's me again, I made a mistake, it counts the number of logins correctly, but does not show members with 0 logins ! Any idea how to do this? Thanks :) Peter Brawley a écrit : > Richard, > > >Can I do something like this : > >SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count > >FROM login_table b WHERE a.username = b.username) FROM user_list a > > Try ... > > SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count > FROM user_list a > JOIN login_table b ON a.username = b.username > GROUP BY a.username,a.first_name,a.lastname; > > PB > > - > > Richard wrote: >> Hello, >> >> This time I'm rearly not sure if this is possible to do. I've got two >> queries that I would like to bring together to make only one query ... >> >> I've got a list of users >> >> And also a login table >> >> I would like to list all users and show the number of times they have >> logged in. >> >> So to get the list of users I would do : >> >> SELECT username, first_name, last_name FROM user_list >> >> And to count the number of connections I would do >> >> SELECT COUNT(*) AS count FROM login_table WHERE username = >> $result['username'] >> >> Can I do something like this : >> >> SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS >> count FROM login_table b WHERE a.username = b.username) FROM user_list a >> >> I know that the above query can not work but It's just to give a >> better idea about what I'm trying to do . :) >> >> If I do a join, I will the username repeated for each login. >> >> Thanks in advance, >> >> Richard >> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Sorry it's me again, I made a mistake, it counts the number of logins correctly, but does not show members with 0 logins ! Any idea how to do this? Thanks :) Peter Brawley a écrit : Richard, >Can I do something like this : >SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count >FROM login_table b WHERE a.username = b.username) FROM user_list a Try ... SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count FROM user_list a JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; PB - Richard wrote: Hello, This time I'm rearly not sure if this is possible to do. I've got two queries that I would like to bring together to make only one query ... I've got a list of users And also a login table I would like to list all users and show the number of times they have logged in. So to get the list of users I would do : SELECT username, first_name, last_name FROM user_list And to count the number of connections I would do SELECT COUNT(*) AS count FROM login_table WHERE username = $result['username'] Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a I know that the above query can not work but It's just to give a better idea about what I'm trying to do . :) If I do a join, I will the username repeated for each login. Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Hi, and thankyou for trying to help me out! I've tried this and it does not work. Here are the problems : 1) If a user has never logged in he doesn't show the user in the list 2) It doesn't count if it is 0 it's not on the liste and if the user has logged in more than once the result is 1 (because of the group by ...). Thankyou Peter Brawley a écrit : Richard, >Can I do something like this : >SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count >FROM login_table b WHERE a.username = b.username) FROM user_list a Try ... SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count FROM user_list a JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; PB - Richard wrote: Hello, This time I'm rearly not sure if this is possible to do. I've got two queries that I would like to bring together to make only one query ... I've got a list of users And also a login table I would like to list all users and show the number of times they have logged in. So to get the list of users I would do : SELECT username, first_name, last_name FROM user_list And to count the number of connections I would do SELECT COUNT(*) AS count FROM login_table WHERE username = $result['username'] Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a I know that the above query can not work but It's just to give a better idea about what I'm trying to do . :) If I do a join, I will the username repeated for each login. Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Richard, >Can I do something like this : >SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count >FROM login_table b WHERE a.username = b.username) FROM user_list a Try ... SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count FROM user_list a JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; PB - Richard wrote: Hello, This time I'm rearly not sure if this is possible to do. I've got two queries that I would like to bring together to make only one query ... I've got a list of users And also a login table I would like to list all users and show the number of times they have logged in. So to get the list of users I would do : SELECT username, first_name, last_name FROM user_list And to count the number of connections I would do SELECT COUNT(*) AS count FROM login_table WHERE username = $result['username'] Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a I know that the above query can not work but It's just to give a better idea about what I'm trying to do . :) If I do a join, I will the username repeated for each login. Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Hello, This time I'm rearly not sure if this is possible to do. I've got two queries that I would like to bring together to make only one query ... I've got a list of users And also a login table I would like to list all users and show the number of times they have logged in. So to get the list of users I would do : SELECT username, first_name, last_name FROM user_list And to count the number of connections I would do SELECT COUNT(*) AS count FROM login_table WHERE username = $result['username'] Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a I know that the above query can not work but It's just to give a better idea about what I'm trying to do . :) If I do a join, I will the username repeated for each login. Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is it possible to either update or insert in a single query?
Thanks Mogens. I was aware of REPLACE but it was the non-standard ON
DUPLICATE KEY UPDATE that I was looking for.
Thanks,
Doug
-Original Message-
From: Mogens Melander [mailto:[EMAIL PROTECTED]
Sent: Friday, April 13, 2007 2:28 AM
To: Douglas Pearson
Cc: [EMAIL PROTECTED]
Subject: Re: Is it possible to either update or insert in a single query?
This part of the manual might be of use to you:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
Or:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
Or:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
SELECT ...
REPLACE works exactly like INSERT, except that if an old row in the table
has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old
row is deleted before the new row is inserted. See Section 13.2.4, "INSERT
Syntax".
REPLACE is a MySQL extension to the SQL standard. It either inserts, or
deletes and inserts. For another MySQL extension to standard SQL - that
either inserts or updates - see Section 13.2.4.3, "INSERT ... ON DUPLICATE
KEY UPDATE Syntax".
Note that unless the table has a PRIMARY KEY or UNIQUE index, using a
REPLACE statement makes no sense. It becomes equivalent to INSERT, because
there is no index to be used to determine whether a new row duplicates
another.
--
Later
Mogens Melander
+45 40 85 71 38
+66 870 133 224
On Fri, April 13, 2007 08:14, Douglas Pearson wrote:
> Apologies if this is a dumb question, but is it possible to write a
> single query that either updates certain columns in a row, or adds an
> entirely new row if there is none already?
>
> I seem to be running into this a lot, and so far I've solved it by:
> 1) run UPDATE table SET x,y WHERE some row
> 2) if rowsChanged == 0 then run the INSERT
>
> It just feels like there must be a way to do this more efficiently.
>
> Thanks,
>
> Doug
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
> --
> This message has been scanned for viruses and dangerous content by
> OpenProtect(http://www.openprotect.com), and is believed to be clean.
>
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is it possible to either update or insert in a single query?
You could try a stored procedure that either inserts a new row or updates an existing row: CREATE PROCEUDRE InsertOrUpdateRecord(IN NewID INT, ... other params ... ) BEGIN IF NOT EXISTS (SELECT ID FROM myTable WHERE ID = NewID) THEN BEGIN INSERT INTO myTable () END; ELSE BEGIN UPDATE myTable SET . END; END IF; END; Randall Price Secure Enterprise Technology Initiatives Microsoft Implementation Group Virginia Tech Information Technology -Original Message- From: Douglas Pearson [mailto:[EMAIL PROTECTED] Sent: Friday, April 13, 2007 2:14 AM To: [EMAIL PROTECTED] Subject: Is it possible to either update or insert in a single query? Apologies if this is a dumb question, but is it possible to write a single query that either updates certain columns in a row, or adds an entirely new row if there is none already? I seem to be running into this a lot, and so far I've solved it by: 1) run UPDATE table SET x,y WHERE some row 2) if rowsChanged == 0 then run the INSERT It just feels like there must be a way to do this more efficiently. Thanks, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is it possible to either update or insert in a single query?
> REPLACE is a MySQL extension to the SQL standard. It either inserts, or > deletes and inserts. For another MySQL extension to standard SQL — that > either inserts or updates — see Section 13.2.4.3, “INSERT ... ON DUPLICATE > KEY UPDATE Syntax”. Take note!! If you're using triggers, foreign key constraints or defaults on columns, it is better to use ON DUPLICATE cause it will update instead of deleting the row first. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is it possible to either update or insert in a single query?
This part of the manual might be of use to you:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
Or:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
Or:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
SELECT ...
REPLACE works exactly like INSERT, except that if an old row in the table
has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the
old row is deleted before the new row is inserted. See Section 13.2.4,
“INSERT Syntax”.
REPLACE is a MySQL extension to the SQL standard. It either inserts, or
deletes and inserts. For another MySQL extension to standard SQL — that
either inserts or updates — see Section 13.2.4.3, “INSERT ... ON DUPLICATE
KEY UPDATE Syntax”.
Note that unless the table has a PRIMARY KEY or UNIQUE index, using a
REPLACE statement makes no sense. It becomes equivalent to INSERT, because
there is no index to be used to determine whether a new row duplicates
another.
--
Later
Mogens Melander
+45 40 85 71 38
+66 870 133 224
On Fri, April 13, 2007 08:14, Douglas Pearson wrote:
> Apologies if this is a dumb question, but is it possible to write a single
> query that either updates certain columns in a row, or adds an entirely
> new
> row if there is none already?
>
> I seem to be running into this a lot, and so far I've solved it by:
> 1) run UPDATE table SET x,y WHERE some row
> 2) if rowsChanged == 0 then run the INSERT
>
> It just feels like there must be a way to do this more efficiently.
>
> Thanks,
>
> Doug
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
> --
> This message has been scanned for viruses and
> dangerous content by OpenProtect(http://www.openprotect.com), and is
> believed to be clean.
>
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is it possible to either update or insert in a single query?
Have you look at replace ? http://dev.mysql.com/doc/refman/5.0/en/replace.html Carlos Douglas Pearson wrote: Apologies if this is a dumb question, but is it possible to write a single query that either updates certain columns in a row, or adds an entirely new row if there is none already? I seem to be running into this a lot, and so far I've solved it by: 1) run UPDATE table SET x,y WHERE some row 2) if rowsChanged == 0 then run the INSERT It just feels like there must be a way to do this more efficiently. Thanks, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is it possible to either update or insert in a single query?
Apologies if this is a dumb question, but is it possible to write a single query that either updates certain columns in a row, or adds an entirely new row if there is none already? I seem to be running into this a lot, and so far I've solved it by: 1) run UPDATE table SET x,y WHERE some row 2) if rowsChanged == 0 then run the INSERT It just feels like there must be a way to do this more efficiently. Thanks, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is it possible to combine mysql with nis?
In the last episode (Jul 19), Cheng-Lin Yang said: > I'd like to know if it's possbile to make MySQL auth the account > through system account via NIS? I want the user in my system who can > automatically have their own mysql account by using the same password > on the system. Please kindly help me on this problem. Thank you in > advanced! :) Mysql can only authenticate to its own internal user table. See also http://bugs.mysql.com/bug.php?id=4703 -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is it possible to combine mysql with nis?
I'm far from an expert on the subject, but judging by a quick Google search for 'NIS mysql backend', people have been able to do this via OpenLDAP. I just thought I'd send you a starting point. On 7/19/06, Cheng-Lin Yang <[EMAIL PROTECTED]> wrote: Hi all, I'd like to know if it's possbile to make MySQL auth the account through system account via NIS? I want the user in my system who can automatically have their own mysql account by using the same password on the system. Please kindly help me on this problem. Thank you in advanced! :) Regards, Cheng-Lin Yang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is it possible to combine mysql with nis?
Hi all, I'd like to know if it's possbile to make MySQL auth the account through system account via NIS? I want the user in my system who can automatically have their own mysql account by using the same password on the system. Please kindly help me on this problem. Thank you in advanced! :) Regards, Cheng-Lin Yang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
is it possible to estimate a backup file size?
Hi all, I'm trying to figure out how much space would be necessary for the mysqldump with the -opt option.. The inodb tablespace is using roughly 130 G, plus maybe 5 G for various myisam files. Is it possible to get even a rough estimate? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[3]: Simple query? Is it possible? (hm.. solution?, maybe can be better?)
Hello Vaidas, Monday, May 9, 2005, 3:08:26 PM, you wrote: hm founded something intresting :) set @mynr:=0; Select @mynr as nr,table.id from table where @a:=IF((table.id=0),0,(@a+1)) having table.id=518 order by id strange IF hack :) main problem what this metod not fast. tested in table with 35024 rows, result i got only after 0.7s! what i can improve? VZ> Hello Roger, VZ> Monday, May 9, 2005, 2:56:18 PM, you wrote: VZ> Heh if i know page i would haven't problem. VZ> but i need to get also correct page with link VZ> doomain.con/items.php?showid=45 VZ> it can be anywhere! :) VZ> I making web application with data binding (IE stuff) data is loading VZ> very fast, can be 1000 and 100 of rows, it's not problem, and user VZ> interface with data loads very fast. VZ> if i use scroll, JS count's what possition is on row, and if needed VZ> loading data from server. VZ> But now i need to to simple trick, SelectRowWhereId(1525) VZ> script must now position of that row in database with same WHERE and VZ> ORDER. VZ> so i never know "page" number. VZ> :) RB>> Vaidas Zilionis wrote: RB>> [...] >>> Example items are displayed 100 in page, and i display 20 pages >>> numbers >>> 1 ... 4[5] 6 x >>> doomain.con/items.php?page=5 >>> >>> and i get all result here with limit 400,100 RB>> Yes, with PHP it would be something like this: RB>> $items_per_page = 100; RB>> $limit_clause = RB>> ((int)$page-1)*$items_per_page.','.$items_per_page; >>> If i use it >>> >>> doomain.con/items.php?showid=45 (example he is in 7 page) >>> >>> i want to see same table with page numbers and ect. And record must be >>> in 7 page. 1 ... 6 [7] 8 x RB>> Why not just create your links like this: RB>> doomain.con/items.php?showid=45&page=7 RB>> At this stage, when you create the link, you allready know what page you RB>> are on. Why waste time and try to calculate it again? VZ> -- VZ> -- VZ> Vaidas Žilionis VZ> Mobile: +370 616 91393 VZ> ICQ: 35174940 VZ> Website: www.zilionis.net VZ> Project: www.sitesupra-tools.net -- -- Vaidas Žilionis Mobile: +370 616 91393 ICQ: 35174940 Website: www.zilionis.net Project: www.sitesupra-tools.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Simple query? Is it possible?
Hello Roger, Monday, May 9, 2005, 2:56:18 PM, you wrote: Heh if i know page i would haven't problem. but i need to get also correct page with link doomain.con/items.php?showid=45 it can be anywhere! :) I making web application with data binding (IE stuff) data is loading very fast, can be 1000 and 100 of rows, it's not problem, and user interface with data loads very fast. if i use scroll, JS count's what possition is on row, and if needed loading data from server. But now i need to to simple trick, SelectRowWhereId(1525) script must now position of that row in database with same WHERE and ORDER. so i never know "page" number. :) RB> Vaidas Zilionis wrote: RB> [...] >> Example items are displayed 100 in page, and i display 20 pages >> numbers >> 1 ... 4[5] 6 x >> doomain.con/items.php?page=5 >> >> and i get all result here with limit 400,100 RB> Yes, with PHP it would be something like this: RB> $items_per_page = 100; RB> $limit_clause = RB> ((int)$page-1)*$items_per_page.','.$items_per_page; >> If i use it >> >> doomain.con/items.php?showid=45 (example he is in 7 page) >> >> i want to see same table with page numbers and ect. And record must be >> in 7 page. 1 ... 6 [7] 8 x RB> Why not just create your links like this: RB> doomain.con/items.php?showid=45&page=7 RB> At this stage, when you create the link, you allready know what page you RB> are on. Why waste time and try to calculate it again? -- -- Vaidas Žilionis Mobile: +370 616 91393 ICQ: 35174940 Website: www.zilionis.net Project: www.sitesupra-tools.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple query? Is it possible?
Vaidas Zilionis wrote: [...] Example items are displayed 100 in page, and i display 20 pages numbers 1 ... 4[5] 6 x doomain.con/items.php?page=5 and i get all result here with limit 400,100 Yes, with PHP it would be something like this: $items_per_page = 100; $limit_clause = ((int)$page-1)*$items_per_page.','.$items_per_page; If i use it doomain.con/items.php?showid=45 (example he is in 7 page) i want to see same table with page numbers and ect. And record must be in 7 page. 1 ... 6 [7] 8 x Why not just create your links like this: doomain.con/items.php?showid=45&page=7 At this stage, when you create the link, you allready know what page you are on. Why waste time and try to calculate it again? -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Simple query? Is it possible?
Hi all, let's figure we have one big database table ~1mln rows. I can easly to collect needed data from this table with query: SELECT id,title,cdate FROM bigtable WHERE active ORDER BY cdate ASC, id ASC LIMIT 300,100 So i get needed rows to display. i can make easly with page numbers to display all data. What i need? I know id of data in bigtable so i wanna know in what place will be record with query (SELECT id,title,cdate FROM bigtable WHERE active ORDER BY cdate ASC, id ASC) example result of query : id|title 00: 1 |name1 01: 12|name2 02: 6|name15 03: 42|name3 04: 25|nameX 05: 35|name4 ... I know id 42, and i want to get result 3, (if id=25, result would be 4) i can't download all data to php script, and find record after query. Because it's slow, and can take big count of RAM in server. Why I need that? Example items are displayed 100 in page, and i display 20 pages numbers 1 ... 4[5] 6 x doomain.con/items.php?page=5 and i get all result here with limit 400,100 If i use it doomain.con/items.php?showid=45 (example he is in 7 page) i want to see same table with page numbers and ect. And record must be in 7 page. 1 ... 6 [7] 8 x It's possible to do in mysql? Sorry for bad english. -- Vaidas Žilionis Mobile: +370 616 91393 ICQ: 35174940 Website: www.zilionis.net Project: www.sitesupra-tools.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIKE question - is it possible?
Not sure if your query works. But what should work is SELECT * FROM table WHERE c LIKE "%word1%" and c LIKE "%word2%" and c LIKE "%word3%"; But might be slow ... Am Thursday 14 April 2005 13:53 schrieb Micha Berdichevsky: > Hi group. > I have a table with a varchar(250) column in it (let's call it c) > I want to select values that contain a number of given words in them > (three or more), in any words order > I currently use > SELECT * FROM table WHERE c LIKE "%word1%word2%word3%"; > I was wandering if it is possible to use a query where the LIKE (or > anything else) searches for my given strings in any order. > I'm using MySQL 4.1.11 on windows XP, if it matters. > > Thanks. > Micha. -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LIKE question - is it possible?
I think that if you explode your words with AND it should work in any order: SELECT * FROM table WHERE c LIKE '%word1%' AND c LIKE '%word2%' AND c LIKE '%word3%'; but there's maybe something better to do! From: Micha Berdichevsky <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Subject: LIKE question - is it possible? Date: Thu, 14 Apr 2005 13:53:31 +0200 Hi group. I have a table with a varchar(250) column in it (let's call it c) I want to select values that contain a number of given words in them (three or more), in any words order I currently use SELECT * FROM table WHERE c LIKE "%word1%word2%word3%"; I was wandering if it is possible to use a query where the LIKE (or anything else) searches for my given strings in any order. I'm using MySQL 4.1.11 on windows XP, if it matters. Thanks. Micha. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIKE question - is it possible?
Micha Berdichevsky <[EMAIL PROTECTED]> wrote on 14/04/2005 12:53:31: > Hi group. > I have a table with a varchar(250) column in it (let's call it c) > I want to select values that contain a number of given words in them > (three or more), in any words order > I currently use > SELECT * FROM table WHERE c LIKE "%word1%word2%word3%"; > I was wandering if it is possible to use a query where the LIKE (or > anything else) searches for my given strings in any order. > I'm using MySQL 4.1.11 on windows XP, if it matters. I think you want to do a FULLTEXT search: see http://dev.mysql.com/doc/mysql/en/fulltext-search.html This requres using a FULLTEXT index on your column and using the MATCH command. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LIKE question - is it possible?
Hi group. I have a table with a varchar(250) column in it (let's call it c) I want to select values that contain a number of given words in them (three or more), in any words order I currently use SELECT * FROM table WHERE c LIKE "%word1%word2%word3%"; I was wandering if it is possible to use a query where the LIKE (or anything else) searches for my given strings in any order. I'm using MySQL 4.1.11 on windows XP, if it matters. Thanks. Micha. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is it possible to export column headings with data?
I am using SELECT statements to write some files via INTO OUTFILE. This has been working fine, but now I have the need to export the column headings from the MySQL tables along with the column contents. Is this possible? If so, how? Ex. SELECT stock, vin, color, mileage, price, cylinders, photo1 FROM cars WHERE owner = 'dealer1' INTO OUTFILE 'c:/stock.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
Re: Is it possible to have Undeletable Records?
zzapper <[EMAIL PROTECTED]> wrote: > Is it possible to have undeletable/unmodifiable records in a table of otherwise > modifiable & > deleteable & createable records? > > At present I do it at the Update/Delete level where I have clauses which prevent > certain records > being changed. I suppose I could also have an extra boolean field "Record Read > only", but is there > anyway to specify at the data level? No, no such feature. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is it possible to have Undeletable Records?
Wesley Furgiuele wrote: I don't think so. I think your current method of either storing it in the query or with a boolean field is your best bet. What I've seen done in the past is that records get marked with a user level and there is either one or a group of users who are allowed to delete or modify those otherwise permanent records. Wes On Fri, 03 Sep 2004 11:38:35 +0100, zzapper <[EMAIL PROTECTED]> wrote: Hi Y'All Is it possible to have undeletable/unmodifiable records in a table of otherwise modifiable & deleteable & createable records? At present I do it at the Update/Delete level where I have clauses which prevent certain records being changed. I suppose I could also have an extra boolean field "Record Read only", but is there anyway to specify at the data level? zzapper (vim, cygwin, wiki & zsh) -- vim -c ":%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg?" http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips Here is an off the wall idea. Create a MERGE table for the data. Put the undeletable data into one table then pack it. Put the other data into a normal myisam file. I have no idea if this will work. But I think it is worth a try. Good Luck -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is it possible to have Undeletable Records?
I don't think so. I think your current method of either storing it in the query or with a boolean field is your best bet. What I've seen done in the past is that records get marked with a user level and there is either one or a group of users who are allowed to delete or modify those otherwise permanent records. Wes On Fri, 03 Sep 2004 11:38:35 +0100, zzapper <[EMAIL PROTECTED]> wrote: > Hi Y'All > > Is it possible to have undeletable/unmodifiable records in a table of otherwise > modifiable & > deleteable & createable records? > > At present I do it at the Update/Delete level where I have clauses which prevent > certain records > being changed. I suppose I could also have an extra boolean field "Record Read > only", but is there > anyway to specify at the data level? > > zzapper (vim, cygwin, wiki & zsh) > -- > > vim -c ":%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg?" > > http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is it possible to have Undeletable Records?
Hi Y'All Is it possible to have undeletable/unmodifiable records in a table of otherwise modifiable & deleteable & createable records? At present I do it at the Update/Delete level where I have clauses which prevent certain records being changed. I suppose I could also have an extra boolean field "Record Read only", but is there anyway to specify at the data level? zzapper (vim, cygwin, wiki & zsh) -- vim -c ":%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg?" http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is it possible to get a timestamp from the server ?
Yea do a search on the mysql.com web site for "date functions" (or just click here http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html ) its loaded with many examples and descriptions of useful functions. Built in date calculations are one thing I really enjoy with mysql. Best, Yoed -Original Message- From: Greg Hosler [mailto:[EMAIL PROTECTED] Sent: Friday, April 23, 2004 7:08 PM To: [EMAIL PROTECTED] Subject: Is it possible to get a timestamp from the server ? Hi, I have a database application that I am porting to MySQL. one of the things that the application needs to do is to get a timestamp from the server, so that all instances of the application across a network can timestamp cewrtain records using a common source for the timestamp. e.g. the sql server. In the past, under Informix, we did the following sql: SELECT distinct current year to second FROM systables I've looked around, and I'm not seeing anything equivalent under MySQL. Have I hopefully missed something ? thx for any suggestions / hints / pointers. best rgds, -Greg Hosler +-+ You can release software that's good, software that's inexpensive, or software that's available on time. You can usually release software that has 2 of these 3 attributes -- but not all 3. | Greg Hosler [EMAIL PROTECTED]| +-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is it possible to get a timestamp from the server ?
To get the current year, use: SELECT DATE_FORMAT(NOW(), '%Y'); Basically you can use the DATE_FORMAT to show the present time as desired. --bmansell -Original Message- From: Greg Hosler [mailto:[EMAIL PROTECTED] Sent: Friday, April 23, 2004 5:08 PM To: [EMAIL PROTECTED] Subject: Is it possible to get a timestamp from the server ? Hi, I have a database application that I am porting to MySQL. one of the things that the application needs to do is to get a timestamp from the server, so that all instances of the application across a network can timestamp cewrtain records using a common source for the timestamp. e.g. the sql server. In the past, under Informix, we did the following sql: SELECT distinct current year to second FROM systables I've looked around, and I'm not seeing anything equivalent under MySQL. Have I hopefully missed something ? thx for any suggestions / hints / pointers. best rgds, -Greg Hosler +-+ You can release software that's good, software that's inexpensive, or software that's available on time. You can usually release software that has 2 of these 3 attributes -- but not all 3. | Greg Hosler [EMAIL PROTECTED]| +-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is it possible to get a timestamp from the server ?
Hi, I have a database application that I am porting to MySQL. one of the things that the application needs to do is to get a timestamp from the server, so that all instances of the application across a network can timestamp cewrtain records using a common source for the timestamp. e.g. the sql server. In the past, under Informix, we did the following sql: SELECT distinct current year to second FROM systables I've looked around, and I'm not seeing anything equivalent under MySQL. Have I hopefully missed something ? thx for any suggestions / hints / pointers. best rgds, -Greg Hosler +-+ You can release software that's good, software that's inexpensive, or software that's available on time. You can usually release software that has 2 of these 3 attributes -- but not all 3. | Greg Hosler [EMAIL PROTECTED]| +-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: is it possible...
Boy do I feel dumb. :^) -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Monday, February 09, 2004 4:54 PM To: [EMAIL PROTECTED] Subject: RE: is it possible... UPDATE EMAIL_TEST_LIST SET EMAIL_DATE = curdate(); -Original Message- From: Ligaya Turmelle [mailto:[EMAIL PROTECTED] Sent: Sunday, February 08, 2004 10:42 PM To: [EMAIL PROTECTED] Subject: is it possible... Is it possible to have an update statement that uses curdate()? Example: UPDATE EMAIL_TEST_LIST SET EMAIL_DATE = "(SELECT curdate())"; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: is it possible...
UPDATE EMAIL_TEST_LIST SET EMAIL_DATE = curdate(); -Original Message- From: Ligaya Turmelle [mailto:[EMAIL PROTECTED] Sent: Sunday, February 08, 2004 10:42 PM To: [EMAIL PROTECTED] Subject: is it possible... Is it possible to have an update statement that uses curdate()? Example: UPDATE EMAIL_TEST_LIST SET EMAIL_DATE = "(SELECT curdate())"; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
is it possible...
Is it possible to have an update statement that uses curdate()? Example: UPDATE EMAIL_TEST_LIST SET EMAIL_DATE = "(SELECT curdate())"; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is It Possible To Change the Value of A Particular Field Manually?
Carlonie, Use the UPDATE. I assume you mean by "database" here you mean the database table named "members"... if so, try the following query. UPDATE members SET user_name = 'john_doe' WHERE user_name = 'John Doe'; -- MikeBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Caroline Jen wrote: Hi, I am not a database person. I have a database called "members". One of the fields in this database is "user_name". There are a number of records in this database. Under the field user_name, I would like to make some changes manually; for example, I want to change John Doe to john_doe Is it possible to do it? How do I do it? Thank you very much in advance. __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is It Possible To Change the Value of A Particular Field Manually?
you can optionally use MySQL Front to view and edit data in GUI mode get it free of cost athttp://www.mysqlfront.de/ Enjoy Nitin - Original Message - From: "Michael Stassen" <[EMAIL PROTECTED]> To: "Caroline Jen" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, December 18, 2003 11:28 AM Subject: Re: Is It Possible To Change the Value of A Particular Field Manually? > > Caroline Jen wrote: > > Hi, I am not a database person. I have a database > > called "members". One of the fields in this database > > is "user_name". There are a number of records in > > this database. Under the field user_name, I would > > like to make some changes manually; for example, I > > want to change > > > > John Doe > > > > to > > > > john_doe > > > > Is it possible to do it? How do I do it? Thank you > > very much in advance. > > Yes. You can specify the records you want with a WHERE clause. First > try a SELECT to test your WHERE clause to make sure you get just the > record(s) you want: > > SELECT * FROM members WHERE user_name = 'John Doe'; > > As long as that looks right, then use UPDATE to modify the record(s): > > UPDATE members SET user_name='john_doe' WHERE user_name = 'John Doe'; > > See <http://www.mysql.com/doc/en/UPDATE.html> in the manual for more. > > Michael > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is It Possible To Change the Value of A Particular Field Manually?
Caroline Jen wrote: Hi, I am not a database person. I have a database called "members". One of the fields in this database is "user_name". There are a number of records in this database. Under the field user_name, I would like to make some changes manually; for example, I want to change John Doe to john_doe Is it possible to do it? How do I do it? Thank you very much in advance. Yes. You can specify the records you want with a WHERE clause. First try a SELECT to test your WHERE clause to make sure you get just the record(s) you want: SELECT * FROM members WHERE user_name = 'John Doe'; As long as that looks right, then use UPDATE to modify the record(s): UPDATE members SET user_name='john_doe' WHERE user_name = 'John Doe'; See <http://www.mysql.com/doc/en/UPDATE.html> in the manual for more. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is It Possible To Change the Value of A Particular Field Manually?
Hi, I am not a database person. I have a database called "members". One of the fields in this database is "user_name". There are a number of records in this database. Under the field user_name, I would like to make some changes manually; for example, I want to change John Doe to john_doe Is it possible to do it? How do I do it? Thank you very much in advance. __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: is it possible?
Eugene R. Miller wrote: This is kind of a silly question is there an easy way to ... SELECT Status, rating, COUNT(*) FROM song GROUP BY pldupldqd, rating ORDER BY Status, rating DESC This gives me all the information I need ... What I would like to do is something... like SELECT rating, count(WHERE pldupldqd = 0), count (WHERE pldupldqd = 1), COUNT (WHERE pldupldqd = 2) FROM song GROUP BY rating The idea is to get... 3 different columns counting what it equals. Yes, it's possible, the solution is very easy: SELECT rating, sum(CASE pldupldqd WHEN 0 THEN 1 ELSE 0 END), sum(CASE pldupldqd WHEN 1 THEN 1 ELSE 0 END), sum(CASE pldupldqd WHEN 2 THEN 1 ELSE 0 END), Alternatively, you case use function IF(), instead of CASE. Erm --- www.the-erm.com -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Barkov <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Izhevsk, Russia <___/ www.mysql.com +7-912-856-80-21 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
is it possible?
This is kind of a silly question is there an easy way to ... SELECT Status, rating, COUNT(*) FROM song GROUP BY pldupldqd, rating ORDER BY Status, rating DESC This gives me all the information I need ... What I would like to do is something... like SELECT rating, count(WHERE pldupldqd = 0), count (WHERE pldupldqd = 1), COUNT (WHERE pldupldqd = 2) FROM song GROUP BY rating The idea is to get... 3 different columns counting what it equals. Erm --- www.the-erm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Composit Key - Is it possible, and if so, what is the syntax?
Try taking out the identifier name for the primary key (pk_foo). The way I read the documentation, MySQL does not support identifiers or names for primary keys although it does for other index types. HTH Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ** Composit Key - Is it possible, and if so, what is the syntax?
I did the following (not much different): CREATE TABLE `foo` ( `pri_1` INT UNSIGNED NOT NULL, `pri_2` INT UNSIGNED NOT NULL, `pri_3` INT UNSIGNED NOT NULL, `test_data` VARCHAR(120) NOT NULL, PRIMARY KEY (`pri_1`, `pri_2`, `pri_3`) ) TYPE = InnoDB; Regards, Ritter -- Jason k Larson Fortuno, Adam wrote: All, I've got an InnoDb table that requires a composit key constraint. However, I'm not certain how to word the DDL syntax. Pretending for a sec this was SQL Server 2000 or Sybase ASE 12.5 I'd use the following. CREATE TABLE foo( pri_1 INTEGER NOT NULL, pri_2 INTEGER NOT NULL, pri_3 INTEGER NOT NULL, test_data VARCHAR(120) NULL CONSTRAINT pk_foo PRIMARY KEY (pri_1, pri_2, pri_3) ) Type = InnoDB; In MySQL, this would return an error. I'd really appreciate knowing if its possible and (if so) what the syntax is. Regards, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
** Composit Key - Is it possible, and if so, what is the syntax?
All, I've got an InnoDb table that requires a composit key constraint. However, I'm not certain how to word the DDL syntax. Pretending for a sec this was SQL Server 2000 or Sybase ASE 12.5 I'd use the following. CREATE TABLE foo( pri_1 INTEGER NOT NULL, pri_2 INTEGER NOT NULL, pri_3 INTEGER NOT NULL, test_data VARCHAR(120) NULL CONSTRAINT pk_foo PRIMARY KEY (pri_1, pri_2, pri_3) ) Type = InnoDB; In MySQL, this would return an error. I'd really appreciate knowing if its possible and (if so) what the syntax is. Regards, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is it possible to share a DB handle among threads?
KINS E-mailHi all, I plan to make SQL queries in different threads(pthread) maked with mysql C API. Can I use the same Connection object in all threads for the queries ? Or should I use a different Connection object for each thread ? p.s If it is possible, let me get some example code.
Re: Is it possible to pipe wildcard searches into mySQL?
At 21:26 -0400 9/3/03, Dan Anderson wrote: I'm writing a search engine to query a database to my site. I know how to use a WHERE product_name = "foo" if somebody enters the exact product name, but how could I do something like: WHERE product_name = "*foo*" so all results containing "foo" in product name would be returned and not just products named only "foo"? Thanks in Advance, Dan Anderson Sounds like you want to use LIKE or REGEXP, which are described here: http://www.mysql.com/doc/en/String_comparison_functions.html Either of these expressions should do it: WHERE product_name LIKE '%foo%' or WHERE product_name REGEXP 'foo' -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is it possible to pipe wildcard searches into mySQL?
I'm writing a search engine to query a database to my site. I know how to use a WHERE product_name = "foo" if somebody enters the exact product name, but how could I do something like: WHERE product_name = "*foo*" so all results containing "foo" in product name would be returned and not just products named only "foo"? Thanks in Advance, Dan Anderson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is it possible to protect MySQL database on a Win2k laptop?
I want to put a MySQL 4.1 database on a Win2k laptop but the problem is it contains confidential client information. It has to be Window because applications accessing the database are written in Windows. The problem is laptops do get stolen quite often, even when running on someone's unattended desk. Most security chains can be cut with a small pair of bolt cutters. I have to convince my boss that if the laptop disappears it won't put the company out of business, and me out of a job. Is it possible to secure the MySQL database on the laptop so if it does get stolen, the information is safe? The password security of Win2k can be easily bypassed by hacking tools so I can't rely on the security of the OS. I could encrypt some of the columns but how does this affect the indexes? I need to use "select custname, col2, col3 from table where custname like 'smith% order by custname'. How would encrypting individual columns affect the use of indexes and sort orders? Are the indexes bypassed? There would be millions of rows of data in the table so I still need to use indexes on these encrypted columns both for searching and for sorting. Does anyone have any advice on how to proceed? TIA. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is it possible to dump images into a database?
Use LOAD DATA INFILE if you want to import it from the filesystem or use INSERT/UPDATE if you do it from an application (e.g. perl, php). Make sure that the column which stores them is defined as BLOB and not as TEXT. Look up the BLOB definition for size limitation and variations on BLOBs. For getting them out of the DB use SELECT .. INTO DUMPFILE to get them onto the file system or normal SELECT for applications. HINT: normally it is MUCH better to store the image on the file system and only store the path to the image in the database. But this again depends on what you want to achieve. Cheers /rudy -Original Message- From: Dan Anderson [mailto:[EMAIL PROTECTED] Sent: woensdag 9 juli 2003 16:49 To: [EMAIL PROTECTED] Subject: Is it possible to dump images into a database? Can anyone point me to a reference on how to insert images into a column in a mySQL database -- or is that not possible? Thanks in advance, Dan Anderson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is it possible to dump images into a database?
Yes it is. If you are using VB look at www.vbmysql.com/articles/blobaccessvb.html Regards, Mike Hillyer www.vbmysql.com > -Original Message- > From: Dan Anderson [mailto:[EMAIL PROTECTED] > Sent: Wednesday, July 09, 2003 8:49 AM > To: [EMAIL PROTECTED] > Subject: Is it possible to dump images into a database? > > > Can anyone point me to a reference on how to insert images > into a column > in a mySQL database -- or is that not possible? > > Thanks in advance, > > Dan Anderson > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is it possible to dump images into a database?
take a look at: http://www.php4.com/forums/viewtopic.php?t=6 or search the mailing list archive.. there are plenty of threads talking about this: For list archives: http://lists.mysql.com/mysql On Wed, 9 Jul 2003, Dan Anderson wrote: > Can anyone point me to a reference on how to insert images into a column > in a mySQL database -- or is that not possible? > > Thanks in advance, > > Dan Anderson > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is it possible to dump images into a database?
Can anyone point me to a reference on how to insert images into a column in a mySQL database -- or is that not possible? Thanks in advance, Dan Anderson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: inserting images to mysql - is it possible?
check out http://www.phpbuilder.com thay have a lot of things and that is one off them. -- eschew obfuscation. -Original Message- From: Digital Directory USA [mailto:[EMAIL PROTECTED] Sent: 29 June 2003 19:28 To: [EMAIL PROTECTED] Subject: inserting images to mysql - is it possible? I am new to php and mysql, I have searched looked over and cannot find a way to do this... can it be done? I have set up the following in mysql. How do I upload the product images to the pPhoto column. Any suggestions? ds_produc Field Type Attributes Null Default Extra pID int(11)Noauto_increment catID int(11)No 0 pName varchar(70)Yes NULL pNum varchar(40)Yes NULL pTeaser textNo pDesc textYes NULL pPrice float(10,2)Yes NULL pSalesPrice float(10,2)No 0.00 pSale char(1)No pPhoto varchar(70)Yes NULL pPhotoWidth int(11)No 0 pPhotoHeight int(11)No 0 pSize varchar(35)Yes NULL pAvail char(1)No Y George Guebely Digital Directory USA, Inc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inserting images to mysql - is it possible?
Hi Gerald- I understand the pros and cons of db vs filesystem, and for images (static, rarely changing) it makes sense. I was pursuing any info about large chunk transfers or fragmented storage issues for large field tables. I'm working with a table containing historical information for 67,000 stocks and indexes. Each record has a data field (mediumtext) which contains the csv data for the stock (approx 30K). Each record needs to be updated each night (append that day's row of data to the mediumtext field) and need to be available ASAP to all web-servers (currently 4). The file is now about 1.5GB, and keeping/updating shadow copies of 70,000 files on 4+ nodes seemed like a problem which would not scale well. The read-side application will access less than 10% of the records in the database each day (but may access many records repeatedly). bob. At 07:50 AM 6/30/03 -0500, you wrote: Bob: This question comes up over and over on the list ... do a search for 'images' of the list archive and you can read the accumulated pros and cons. Gerald Jensen - Original Message - From: "Bob Ostermann" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, June 30, 2003 7:35 AM Subject: Re: inserting images to mysql - is it possible? > Armand- > > Why is it not recommended to store images in a database? I've not heard this... > > > bob. > > > > At 09:51 PM 6/29/03 +0200, you wrote: > >Hi, > >You have to insert a new blob field where you can store binary data. But > >it is not recommanded to store images in a database. I recommande you to > >store images on a server filesystem and only the reference to the images > >in a database. > > > >Armand > > > >Digital Directory USA wrote: > > > >>I am new to php and mysql, I have searched looked over and cannot find a > >>way to do this... can it be done? I have set up the following in mysql. > >>How do I upload the product images to the pPhoto column. Any suggestions? > >>ds_produc > >> Field Type Attributes Null Default Extra > >> pID int(11)Noauto_increment > >> catID int(11)No 0 > >> pName varchar(70)Yes NULL > >> pNum varchar(40)Yes NULL > >> pTeaser textNo > >> pDesc textYes NULL > >> pPrice float(10,2)Yes NULL > >> pSalesPrice float(10,2)No 0.00 > >> pSale char(1)No > >> pPhoto varchar(70)Yes NULL > >> pPhotoWidth int(11)No 0 > >> pPhotoHeight int(11)No 0 > >> pSize varchar(35)Yes NULL > >> pAvail char(1)No Y > >> > >> > >>George Guebely > >>Digital Directory USA, Inc > >> > >> > > > > > > > >-- > >MySQL General Mailing List > >For list archives: http://lists.mysql.com/mysql > >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > == > The RediNews Network http://www.redinews.com/ > Custom Financial Web Site Content > phone: (516) 997-4343 > == > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > == The RediNews Network http://www.redinews.com/ Custom Financial Web Site Content phone: (516) 997-4343 == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inserting images to mysql - is it possible?
Bob: This question comes up over and over on the list ... do a search for 'images' of the list archive and you can read the accumulated pros and cons. Gerald Jensen - Original Message - From: "Bob Ostermann" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, June 30, 2003 7:35 AM Subject: Re: inserting images to mysql - is it possible? > Armand- > > Why is it not recommended to store images in a database? I've not heard this... > > > bob. > > > > At 09:51 PM 6/29/03 +0200, you wrote: > >Hi, > >You have to insert a new blob field where you can store binary data. But > >it is not recommanded to store images in a database. I recommande you to > >store images on a server filesystem and only the reference to the images > >in a database. > > > >Armand > > > >Digital Directory USA wrote: > > > >>I am new to php and mysql, I have searched looked over and cannot find a > >>way to do this... can it be done? I have set up the following in mysql. > >>How do I upload the product images to the pPhoto column. Any suggestions? > >>ds_produc > >> Field Type Attributes Null Default Extra > >> pID int(11)Noauto_increment > >> catID int(11)No 0 > >> pName varchar(70)Yes NULL > >> pNum varchar(40)Yes NULL > >> pTeaser textNo > >> pDesc textYes NULL > >> pPrice float(10,2)Yes NULL > >> pSalesPrice float(10,2)No 0.00 > >> pSale char(1)No > >> pPhoto varchar(70)Yes NULL > >> pPhotoWidth int(11)No 0 > >> pPhotoHeight int(11)No 0 > >> pSize varchar(35)Yes NULL > >> pAvail char(1)No Y > >> > >> > >>George Guebely > >>Digital Directory USA, Inc > >> > >> > > > > > > > >-- > >MySQL General Mailing List > >For list archives: http://lists.mysql.com/mysql > >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > == > The RediNews Network http://www.redinews.com/ > Custom Financial Web Site Content > phone: (516) 997-4343 > == > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inserting images to mysql - is it possible?
Armand- Why is it not recommended to store images in a database? I've not heard this... bob. At 09:51 PM 6/29/03 +0200, you wrote: Hi, You have to insert a new blob field where you can store binary data. But it is not recommanded to store images in a database. I recommande you to store images on a server filesystem and only the reference to the images in a database. Armand Digital Directory USA wrote: I am new to php and mysql, I have searched looked over and cannot find a way to do this... can it be done? I have set up the following in mysql. How do I upload the product images to the pPhoto column. Any suggestions? ds_produc Field Type Attributes Null Default Extra pID int(11)Noauto_increment catID int(11)No 0 pName varchar(70)Yes NULL pNum varchar(40)Yes NULL pTeaser textNo pDesc textYes NULL pPrice float(10,2)Yes NULL pSalesPrice float(10,2)No 0.00 pSale char(1)No pPhoto varchar(70)Yes NULL pPhotoWidth int(11)No 0 pPhotoHeight int(11)No 0 pSize varchar(35)Yes NULL pAvail char(1)No Y George Guebely Digital Directory USA, Inc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] == The RediNews Network http://www.redinews.com/ Custom Financial Web Site Content phone: (516) 997-4343 == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inserting images to mysql - is it possible?
If you want to store images in the database, use a blob columntype.. And take a look at this example alot of people have based mysql binary storage off: http://www.php4.com/forums/viewtopic.php?t=6 good luck On Sun, 29 Jun 2003, Digital Directory USA wrote: > I am new to php and mysql, I have searched looked over and cannot find a way to do > this... can it be done? > I have set up the following in mysql. How do I upload the product images to the > pPhoto column. Any suggestions? > ds_produc > Field Type Attributes Null Default Extra > pID int(11)Noauto_increment > catID int(11)No 0 > pName varchar(70)Yes NULL > pNum varchar(40)Yes NULL > pTeaser textNo > pDesc textYes NULL > pPrice float(10,2)Yes NULL > pSalesPrice float(10,2)No 0.00 > pSale char(1)No > pPhoto varchar(70)Yes NULL > pPhotoWidth int(11)No 0 > pPhotoHeight int(11)No 0 > pSize varchar(35)Yes NULL > pAvail char(1)No Y > > > George Guebely > Digital Directory USA, Inc > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inserting images to mysql - is it possible?
Hi, You have to insert a new blob field where you can store binary data. But it is not recommanded to store images in a database. I recommande you to store images on a server filesystem and only the reference to the images in a database. Armand Digital Directory USA wrote: I am new to php and mysql, I have searched looked over and cannot find a way to do this... can it be done? I have set up the following in mysql. How do I upload the product images to the pPhoto column. Any suggestions? ds_produc Field Type Attributes Null Default Extra pID int(11)Noauto_increment catID int(11)No 0 pName varchar(70)Yes NULL pNum varchar(40)Yes NULL pTeaser textNo pDesc textYes NULL pPrice float(10,2)Yes NULL pSalesPrice float(10,2)No 0.00 pSale char(1)No pPhoto varchar(70)Yes NULL pPhotoWidth int(11)No 0 pPhotoHeight int(11)No 0 pSize varchar(35)Yes NULL pAvail char(1)No Y George Guebely Digital Directory USA, Inc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
inserting images to mysql - is it possible?
I am new to php and mysql, I have searched looked over and cannot find a way to do this... can it be done? I have set up the following in mysql. How do I upload the product images to the pPhoto column. Any suggestions? ds_produc Field Type Attributes Null Default Extra pID int(11)Noauto_increment catID int(11)No 0 pName varchar(70)Yes NULL pNum varchar(40)Yes NULL pTeaser textNo pDesc textYes NULL pPrice float(10,2)Yes NULL pSalesPrice float(10,2)No 0.00 pSale char(1)No pPhoto varchar(70)Yes NULL pPhotoWidth int(11)No 0 pPhotoHeight int(11)No 0 pSize varchar(35)Yes NULL pAvail char(1)No Y George Guebely Digital Directory USA, Inc
Re: Is it possible to refresh data in a mySQL data via a script?
On Fri, 2003-06-20 at 12:16, Barbara Andrew wrote: > I have three books on mySQL, they all talk about how to > construct SQL statements and I'm fine with that. What I can't figure out is > how to get those statements to the right place without having to do it > manually. This would be the domain of some other tool, such as a shell script, or a commandline php/python/perl etc script that could be run nightly using a scheduling tool ie cron, at etc. You could also have mysql do the import: use databasename; drop table if exists myTable; create table myTable ( Field1 varchar(50) not null, field2 varchar(25) not null, PRIMARY KEY (Field1), KEY names(field2) ) TYPE=MyISAM; load data infile '/tmp/dataFile' into table myTable FIELDS TERMINATED BY '|'; put the above (or some variation) in a file (filename.mysql), and then have mysql execute those statements nightly: by putting the following into either a one line bash script, or having cron run it directly: mysql filename.mysql Of course, you will probably want to run some checks to make sure the data is in the file before you start Dropping tables etc. Check these out for reference: http://www.mysql.com/doc/en/mysqlimport.html http://www.mysql.com/doc/en/LOAD_DATA.html hth, gabe. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is it possible to refresh data in a mySQL data via a script?
Hi there, I'm just getting started with mySQL and PHP. I've used phpMyAdmin to create tables and insert data into them from a delimited text file. From there I'm creating a web page to view the data. Ideally this table would be refreshed every night as it is a data dump from our production server. Could anyone point me in the direction of any documentation on how to automate the data insertion routine? I have three books on mySQL, they all talk about how to construct SQL statements and I'm fine with that. What I can't figure out is how to get those statements to the right place without having to do it manually. Thanks, Barbara -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: is it possible to get around 4 billion row limit
Hi Edward, Thanks for the info, but I tried setting the avg_row_length with no effect - I'm pretty sure you only need that for dynamic format tables (with varchars and text). My table is fixed format, and the table does claim to get around the 2/4GB limit. My problem is the maximum number of rows, not the final table size. Max_data_length: 47244640255 Create_options: max_rows=4294967295 I'm guessing that you can't have more than 4294967295 rows in a MyISAM table (or maybe the SQL parser can't handle numbers > 2^32 as the value for max_rows). I think I'll resubmit the question as "What is the maximum number of rows MySQL can store in a table". thanks, Martin On Tue, 2003-06-10 at 11:54, Becoming Digital wrote: > Sorry, I meant to include this link, too. > http://www.mysql.com/doc/en/Table_size.html > > Edward Dudlik > Becoming Digital > www.becomingdigital.com > > > - Original Message - > From: "Martin Waite" <[EMAIL PROTECTED]> > To: "MySQL List" <[EMAIL PROTECTED]> > Sent: Monday, 09 June, 2003 04:45 > Subject: is it possible to get around 4 billion row limit > > > Hi, > > I want to create a table with a lot (8 billion) > small fixed-length records. > > I thought setting MAX_ROWS in the create table > would do this for me, but it looks like it quietly > ignores values over 4.2 billion. > > Is this a hard-limit in MySQL ? > Does MySQL 4.0.x have the same limitation ? > > (MySQL v3.23.51) > > regards, > Martin > > eg. > > CREATE TABLE `txn_tag` ( > txn_id int unsigned not null, > `tag_id` smallint unsigned NOT NULL default '0', > `value_id` int(11) NOT NULL default '0', > unique KEY (txn_id, tag_id, value_id), > KEY `tag_id` (`tag_id`,`value_id`), > KEY `value_id` (`value_id`,`tag_id`) > ) max_rows=80; > > > show table status like 'txn_tag'\G > *** 1. row *** >Name: txn_tag >Type: MyISAM > Row_format: Fixed >Rows: 0 > Avg_row_length: 0 > Data_length: 0 > Max_data_length: 47244640255 >Index_length: 1024 > Data_free: 0 > Auto_increment: NULL > Create_time: 2003-06-09 09:40:57 > Update_time: 2003-06-09 09:40:57 > Check_time: NULL > Create_options: max_rows=4294967295 > Comment: > 1 row in set (0.00 sec) > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: is it possible to get around 4 billion row limit
Sorry, I meant to include this link, too. http://www.mysql.com/doc/en/Table_size.html Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: "Martin Waite" <[EMAIL PROTECTED]> To: "MySQL List" <[EMAIL PROTECTED]> Sent: Monday, 09 June, 2003 04:45 Subject: is it possible to get around 4 billion row limit Hi, I want to create a table with a lot (8 billion) small fixed-length records. I thought setting MAX_ROWS in the create table would do this for me, but it looks like it quietly ignores values over 4.2 billion. Is this a hard-limit in MySQL ? Does MySQL 4.0.x have the same limitation ? (MySQL v3.23.51) regards, Martin eg. CREATE TABLE `txn_tag` ( txn_id int unsigned not null, `tag_id` smallint unsigned NOT NULL default '0', `value_id` int(11) NOT NULL default '0', unique KEY (txn_id, tag_id, value_id), KEY `tag_id` (`tag_id`,`value_id`), KEY `value_id` (`value_id`,`tag_id`) ) max_rows=80; show table status like 'txn_tag'\G *** 1. row *** Name: txn_tag Type: MyISAM Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 47244640255 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2003-06-09 09:40:57 Update_time: 2003-06-09 09:40:57 Check_time: NULL Create_options: max_rows=4294967295 Comment: 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: is it possible to get around 4 billion row limit
Perhaps this has something to do with it: "When you use a MyISAM table, MySQL uses the product of max_rows * avg_row_length to decide how big the resulting table will be. If you don't specify any of the above options, the maximum size for a table will be 4G (or 2G if your operating systems only supports 2G tables)." http://www.mysql.com/doc/en/CREATE_TABLE.html Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: "Martin Waite" <[EMAIL PROTECTED]> To: "MySQL List" <[EMAIL PROTECTED]> Sent: Monday, 09 June, 2003 04:45 Subject: is it possible to get around 4 billion row limit Hi, I want to create a table with a lot (8 billion) small fixed-length records. I thought setting MAX_ROWS in the create table would do this for me, but it looks like it quietly ignores values over 4.2 billion. Is this a hard-limit in MySQL ? Does MySQL 4.0.x have the same limitation ? (MySQL v3.23.51) regards, Martin eg. CREATE TABLE `txn_tag` ( txn_id int unsigned not null, `tag_id` smallint unsigned NOT NULL default '0', `value_id` int(11) NOT NULL default '0', unique KEY (txn_id, tag_id, value_id), KEY `tag_id` (`tag_id`,`value_id`), KEY `value_id` (`value_id`,`tag_id`) ) max_rows=80; show table status like 'txn_tag'\G *** 1. row *** Name: txn_tag Type: MyISAM Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 47244640255 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2003-06-09 09:40:57 Update_time: 2003-06-09 09:40:57 Check_time: NULL Create_options: max_rows=4294967295 Comment: 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
is it possible to get around 4 billion row limit
Hi, I want to create a table with a lot (8 billion) small fixed-length records. I thought setting MAX_ROWS in the create table would do this for me, but it looks like it quietly ignores values over 4.2 billion. Is this a hard-limit in MySQL ? Does MySQL 4.0.x have the same limitation ? (MySQL v3.23.51) regards, Martin eg. CREATE TABLE `txn_tag` ( txn_id int unsigned not null, `tag_id` smallint unsigned NOT NULL default '0', `value_id` int(11) NOT NULL default '0', unique KEY (txn_id, tag_id, value_id), KEY `tag_id` (`tag_id`,`value_id`), KEY `value_id` (`value_id`,`tag_id`) ) max_rows=80; show table status like 'txn_tag'\G *** 1. row *** Name: txn_tag Type: MyISAM Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 47244640255 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2003-06-09 09:40:57 Update_time: 2003-06-09 09:40:57 Check_time: NULL Create_options: max_rows=4294967295 Comment: 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: self foreign keys and InnoDB is it possible ?
I don't have the answer to your question, but I find your question very interesting. One observation,and I could be wrong, is that "deleting 1 has to delete 1,2,4,5' doesn't seem to be legal as far as Foreign Key constraints go. As I read your table, 1/aa has no father (-1), 2/bb has father 1/aa, but 4/dd and 5/ee have father of 2/bb NOT 1/aa, 1/aa is a grandfather. While I see your logic (if 2/bb doesn't exist, then 4/dd and 5/ee can't exist either), but you are getting into recursion here I think, which I'm pretty sure is beyond the scope of SQL. You might have to break this into a routine in whatever language you're using, one that traverses your tree and deletes the nodes. I'm sure there are some efficient algorithms out there for this type of thing. > -Original Message- > From: alx [mailto:[EMAIL PROTECTED] > Sent: Thursday, April 03, 2003 5:10 PM > To: [EMAIL PROTECTED] > Subject: self foreign keys and InnoDB is it possible ? > > > hi all > > I'm creating a table like this > > CREATE TABLE test ( > id integer not null primary key auto_increment, > testchar(20), > father integer key default ='-1' not null, > FOREIGN KEY (id) RERERENCES test(padre) ON DELETE CASCADE > ) Type=InnoDB; > > But I made some mistakes on it, because i want it to act differently > from now > > I want obtain something like this > > table row example: > id test father > 1 'aa' -1 > 2 'bb' 1 > 3 'cc' -1 > 4 'dd' 2 > 5 'ee' 2 > 6 'ff' 3 > so relatives chains are > 1-2-4 >\-5 > > 3-6 > > and then I want to delete all child from a id that is their father. > > example > deleting 3 has to delete 3 and 6 > deleting 2 has to delete 2,4,5 > deleting 1 has to delete 1,2,4,5 > > I hope this could be enough to explain my SQL problem... > > TIA > ALx > -- > alx <[EMAIL PROTECTED]> > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
self foreign keys and InnoDB is it possible ?
hi all I'm creating a table like this CREATE TABLE test ( id integer not null primary key auto_increment, testchar(20), father integer key default ='-1' not null, FOREIGN KEY (id) RERERENCES test(padre) ON DELETE CASCADE ) Type=InnoDB; But I made some mistakes on it, because i want it to act differently from now I want obtain something like this table row example: id test father 1 'aa' -1 2 'bb' 1 3 'cc' -1 4 'dd' 2 5 'ee' 2 6 'ff' 3 so relatives chains are 1-2-4 \-5 3-6 and then I want to delete all child from a id that is their father. example deleting 3 has to delete 3 and 6 deleting 2 has to delete 2,4,5 deleting 1 has to delete 1,2,4,5 I hope this could be enough to explain my SQL problem... TIA ALx -- alx <[EMAIL PROTECTED]> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: auto generated column data: is it possible?
From: "Victoria Reznichenko" <[EMAIL PROTECTED]> > Why do you want to store the sum of columns? You can just sum them when > you retrieve data. The database has got about 1 million records, and it gorws day by day The query I need returns lots of records, and calculating it while retrieving data is too expensive >Even if you want to store sum, you can sum values in INSERT > statement, can't you? You are right... The thing is that the data is inserted by a Perl script I made. The type of those fields is DateTime, and when I made the script I didn't find an operation for Dates that could do that... but now I know UNIX_TIMESTAMP() that suites perfect for what I want. I was about to do the calcs in my Perl script, but you're right, I can easily do it that way in the INSERT statement... Thanks a lot!!! Juan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: auto generated column data: is it possible?
On Thursday 20 March 2003 21:10, Juan Nin wrote: > Imagine I have a table with 3 colums (column1, column2, column3), which are > of type INT. > > I want the data in column3 to be generated automatically from the one in > column1 and column2 when it is inserted > For example a sum, that the data in column3 be the sum of column1 and > column2. > If I insert a row that contains "2" for column1 and "3" for column2, the > column3 should be automatically filled with "5". > > I know that in other DBMs this can be done with triggers, but MySQL still > doesn't support them.. Why do you want to store the sum of columns? You can just sum them when you retrieve data. Even if you want to store sum, you can sum values in INSERT statement, can't you? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
auto generated column data: is it possible?
Hi, I got the following question: Imagine I have a table with 3 colums (column1, column2, column3), which are of type INT. I want the data in column3 to be generated automatically from the one in column1 and column2 when it is inserted For example a sum, that the data in column3 be the sum of column1 and column2. If I insert a row that contains "2" for column1 and "3" for column2, the column3 should be automatically filled with "5". I know that in other DBMs this can be done with triggers, but MySQL still doesn't support them.. Can it be done in any way?? Thanks in advance, Juan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Is it possible to prevent a user from dropping some tables butnot others?
Hi all, Sorry to bother the list, since I'm pretty sure the answer is no, but I'm trying to figure out if it's possible to prevent a user from dropping some tables in a database while allowing him/her to drop others. I've seen requests on the list for something similar, but not a solution. Specifically, for a class, I would like to have students practice SQL using a database I've created with some tables in it. The practice includes creating and dropping tables, but I would like to prevent students from dropping my tables (or each other's I suppose, but that's optional). This problem generalizes to other permissions, e.g., to allow a user to read any table in a database except a specified one. The permission system described in the manual indicates that the search from general to specific (user to db + host to table and column) stops if permission is granted. It doesn't seem to be possible to grant a general permission (you can drop tables), then take it back in a specific case (except not this one). So, ideas I've come up with: 1) tell them not to drop my tables. This will work for a lab, but it's not really a solution. Still, it's probably what I'll do. 2) make a list of the table names they're allowed to create (e.g., their last names), then explicitly grant permission to drop (and create) just those. This will work, but it doesn't generalize very well. 3) put my tables in a different database that is protected, then teach them how to write queries that span databases. I think this will work, but at the expense of a bit of complexity in class. I'm hoping that someone will have a better idea. -- Kevin Crowston Syracuse University Phone: +1 (315) 443-1676 School of Information Studies Fax:+1 (315) 443-5806 4-206 Centre for Science and Technology EMail: [EMAIL PROTECTED] Syracuse, NY 13244-4100 USA Web:http://crowston.syr.edu/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Is it possible to backup a corrupt database without being
> -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > I can FTP and telnet in to my site as "admin" or a user, but > all the files in > /var are owned by root so cannot be deleted, with the > exception of several > mysql files owned by mysql, which I also cannot move or > delete :( If I could > extract the info in the database I could delete that > remotely, then restore it > after I get root access back again, but as it is now marked > corrupt I can't > seem to do anything with it. Is there any way of fixing a > database with zero > room on the partition it is in? Just a thought: FTP the database files to another machine. Drop the affected databases to delete the files and free up enough disk space to log in. Log in as root and free up more disk space. FTP the database files back to the server. Repair the database. My apologies if you've already thought of this. I don't have any experience with the raq3, so maybe this is unworkable. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it possible to backup a corrupt database without being
I can FTP and telnet in to my site as "admin" or a user, but all the files in /var are owned by root so cannot be deleted, with the exception of several mysql files owned by mysql, which I also cannot move or delete :( If I could extract the info in the database I could delete that remotely, then restore it after I get root access back again, but as it is now marked corrupt I can't seem to do anything with it. Is there any way of fixing a database with zero room on the partition it is in? > > I`ve got myself into a bit of a mess by allowing my raq3's 200mb var > > partition > > to fill up. This means I can no longer login to my server as root, > > because a > > log file of my login cannot be created. Naturally only root can delete or > > move any files in /var to fix this problem :o Anyway, this caused my > > MySQL > > database which was also in /var have problems, and before realising that > > disk > > space was the cause, I tried to repair the database using the repair > > command. > > Now I have a corrupt database that I cannot export due to "Error: Cannot > > open > > database.MYD", and I cannot copy the actual data files on the server due > > to > > lack of permissions. Is there anyway I can get around this? If I delete > > the > > database I should have enough room to login as root again, but I`d really > > rather have a copy of the database before doing this. > > Just a offshot guess here, can you ftp in and delete a file? > > - > Scott HanedaTel: 415.898.2602 > http://www.newgeo.com Fax: 313.557.5052 > [EMAIL PROTECTED]Novato, CA U.S.A. > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it possible to backup a corrupt database without beingroot?
on 12/19/2002 5:00 PM, Paul Nolan at [EMAIL PROTECTED] wrote: > I`ve got myself into a bit of a mess by allowing my raq3's 200mb var partition > to fill up. This means I can no longer login to my server as root, because a > log file of my login cannot be created. Naturally only root can delete or > move any files in /var to fix this problem :o Anyway, this caused my MySQL > database which was also in /var have problems, and before realising that disk > space was the cause, I tried to repair the database using the repair command. > Now I have a corrupt database that I cannot export due to "Error: Cannot open > database.MYD", and I cannot copy the actual data files on the server due to > lack of permissions. Is there anyway I can get around this? If I delete the > database I should have enough room to login as root again, but I`d really > rather have a copy of the database before doing this. Just a offshot guess here, can you ftp in and delete a file? - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Is it possible to backup a corrupt database without being root?
Hello, I`ve got myself into a bit of a mess by allowing my raq3's 200mb var partition to fill up. This means I can no longer login to my server as root, because a log file of my login cannot be created. Naturally only root can delete or move any files in /var to fix this problem :o Anyway, this caused my MySQL database which was also in /var have problems, and before realising that disk space was the cause, I tried to repair the database using the repair command. Now I have a corrupt database that I cannot export due to "Error: Cannot open database.MYD", and I cannot copy the actual data files on the server due to lack of permissions. Is there anyway I can get around this? If I delete the database I should have enough room to login as root again, but I`d really rather have a copy of the database before doing this. Thanks, Paul Nolan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it possible to export data from access to MySQL?
Dear Steve, > Is it possible to export data from MS Access into MySQL? If you don't care to export every single table manually, this is what you do: 1. Install MyODBC 3.51.04 2. Configure a system DSN for your MySQL server. 3. In Access, right-click the table to be exported. 4. Choose Export... 5. Choose Data Type "ODBC". 6. Choose a name for the table (default: the Access name). 7. On Computer Data Source, choose the DSN you set up in step 2. The table will be created in the database you set up in step 2. The export will create the table definition (.frm) and import the data. Make sure _all_ your Access tables have primary keys, this makes things easier. You may run into trouble if your Access tables have strange column names (e.g., with spaces, umlauts etc.). Recently, I tried this procedure on a 500 MB Access table with 1.2 million records, and it took 4 minutes on a 2 GHz single processor Pentium machine running Win2K with Service Pack 2. Regards, -- Stefan Hinz <[EMAIL PROTECTED]> Geschäftsführer / CEO iConnect GmbH <http://iConnect.de> Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: "Steve Jackson" <[EMAIL PROTECTED]> To: "MySQL General Mailing list" <[EMAIL PROTECTED]> Sent: Tuesday, November 26, 2002 3:37 PM Subject: Is it possible to export data from access to MySQL? > Is it possible to export data from MS Access into MySQL? > If so how? > I have a huge amount of data that will take weeks to input and I've > convinced my boss to use MySQL rather than any other DB because of it's > compatibility with PHP. However I (probably hastily) *assumed* that > there would be a way to export the data. > Any ideas? > > Steve Jackson > Web Developer > Viola Systems Ltd. > http://www.violasystems.com > [EMAIL PROTECTED] > Mobile +358 50 343 5159 > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Is it possible to export data from access to MySQL?
I like that one. Steve Bradwell MIS Department. "If you give someone a program, you will frustrate them for a day. If you teach them how to program, you will frustrate them for a lifetime." -Original Message- From: John Ragan [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 26, 2002 12:50 PM To: MySQL General Mailing list; [EMAIL PROTECTED] Subject: Re: Is it possible to export data from access to MySQL? you're gonna get lots of neat high tech answers to your problem, so here's a low tech answer just because it's fun. construct the database in mysql. copy the access database to a working copy to be safe. in the working copy, link to the mysql tables. open the access table a and copy the data. open the mysql table a and paste the data. : ) the kid in me loves doing that. copy and paste is the most powerful concept in windows. the only reason that i don't use it in corereader is that it would permanently tie corereader into ms. windows. > Is it possible to export data from MS Access into MySQL? > If so how? > I have a huge amount of data that will take weeks to input and I've > convinced my boss to use MySQL rather than any other DB because of it's > compatibility with PHP. However I (probably hastily) *assumed* that > there would be a way to export the data. > Any ideas? > > Steve Jackson > Web Developer > Viola Systems Ltd. > http://www.violasystems.com > [EMAIL PROTECTED] > Mobile +358 50 343 5159 > > -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it possible to export data from access to MySQL?
you're gonna get lots of neat high tech answers to your problem, so here's a low tech answer just because it's fun. construct the database in mysql. copy the access database to a working copy to be safe. in the working copy, link to the mysql tables. open the access table a and copy the data. open the mysql table a and paste the data. : ) the kid in me loves doing that. copy and paste is the most powerful concept in windows. the only reason that i don't use it in corereader is that it would permanently tie corereader into ms. windows. > Is it possible to export data from MS Access into MySQL? > If so how? > I have a huge amount of data that will take weeks to input and I've > convinced my boss to use MySQL rather than any other DB because of it's > compatibility with PHP. However I (probably hastily) *assumed* that > there would be a way to export the data. > Any ideas? > > Steve Jackson > Web Developer > Viola Systems Ltd. > http://www.violasystems.com > [EMAIL PROTECTED] > Mobile +358 50 343 5159 > > -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it possible to export data from access to MySQL?
I used MyAccess to convert my 40mb .mdb file into MySQL and it works great. You can find it and a whole bunch of other conversion tools on the MySQL site in Downloads/Other. Here's the link, http://www.mysql.com/downloads/os-win32.html Good Luck >>> "Steve Jackson" <[EMAIL PROTECTED]> 11/26/02 6:37:45 AM >>> Is it possible to export data from MS Access into MySQL? If so how? I have a huge amount of data that will take weeks to input and I've convinced my boss to use MySQL rather than any other DB because of it's compatibility with PHP. However I (probably hastily) *assumed* that there would be a way to export the data. Any ideas? Steve Jackson Web Developer Viola Systems Ltd. http://www.violasystems.com [EMAIL PROTECTED] Mobile +358 50 343 5159 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it possible to export data from access to MySQL?
Hi! Yes, it's possible. Export your Access data to standard csv files, and then use mysqlimport with appropriate parameters to import the csvs into MySQL. The MySQL manual has excellent guidance on using mysqlimport or LOAD DATA INFILE statements (which can do the same as mysqlimport). Iikka On Tue, 26 Nov 2002, Steve Jackson wrote: > Is it possible to export data from MS Access into MySQL? > If so how? > I have a huge amount of data that will take weeks to input and I've > convinced my boss to use MySQL rather than any other DB because of it's > compatibility with PHP. However I (probably hastily) *assumed* that > there would be a way to export the data. > Any ideas? > > Steve Jackson > Web Developer > Viola Systems Ltd. > http://www.violasystems.com > [EMAIL PROTECTED] > Mobile +358 50 343 5159 > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail ><[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > Iikka Meriläinen Vaala, Finland E-mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Is it possible to export data from access to MySQL?
A quick way. If you don't have phpMyAdmin running set it up. It has a great import feature. Just dump the access table to a delimited text file. Then go to phpMyAdmin, from the main page - create a new database, or select an existing one. from there scroll down to create a table, enter the table name and amount of fields. Enter all field names and types. Save it, then scroll down to "Insert data from a textfile into a table" and follow the instructions. Its a piece of cake from there. Steve Bradwell MIS Department. "If you give someone a program, you will frustrate them for a day. If you teach them how to program, you will frustrate them for a lifetime." -Original Message- From: Steve Jackson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 26, 2002 9:38 AM To: MySQL General Mailing list Subject: Is it possible to export data from access to MySQL? Is it possible to export data from MS Access into MySQL? If so how? I have a huge amount of data that will take weeks to input and I've convinced my boss to use MySQL rather than any other DB because of it's compatibility with PHP. However I (probably hastily) *assumed* that there would be a way to export the data. Any ideas? Steve Jackson Web Developer Viola Systems Ltd. http://www.violasystems.com [EMAIL PROTECTED] Mobile +358 50 343 5159 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it possible to export data from access to MySQL?
MySQL-Front is quite good for this... - Original Message - From: "Steve Jackson" <[EMAIL PROTECTED]> To: "MySQL General Mailing list" <[EMAIL PROTECTED]> Sent: Tuesday, November 26, 2002 4:37 PM Subject: Is it possible to export data from access to MySQL? > Is it possible to export data from MS Access into MySQL? > If so how? > I have a huge amount of data that will take weeks to input and I've > convinced my boss to use MySQL rather than any other DB because of it's > compatibility with PHP. However I (probably hastily) *assumed* that > there would be a way to export the data. > Any ideas? > > Steve Jackson > Web Developer > Viola Systems Ltd. > http://www.violasystems.com > [EMAIL PROTECTED] > Mobile +358 50 343 5159 > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Is it possible to export data from access to MySQL?
Cheers. Sorted very quickly using MySQL front. Good thing is I can use that to get the SQL and then cut and paste it to any DB I like which is PDG! Steve Jackson Web Developer Viola Systems Ltd. http://www.violasystems.com [EMAIL PROTECTED] Mobile +358 50 343 5159 > -Original Message- > From: FlashGuy [mailto:[EMAIL PROTECTED]] > Sent: 26. marraskuuta 2002 16:42 > To: MySQL General Mailing list; [EMAIL PROTECTED] > Subject: Re: Is it possible to export data from access to MySQL? > > > > Download MySQL-Font v2.5 > > This is what I used when I switched from Access to MySQL. > > Once installed just load MySQL-Front and click on > "Im-/Export" on the toolbar. Select ODBC Import. > > Viola! > > > > On Tue, 26 Nov 2002 16:37:45 +0200, Steve Jackson wrote: > > > Is it possible to export data from MS Access into MySQL? > > If so how? > > I have a huge amount of data that will take weeks to input and I've > > convinced my boss to use MySQL rather than any other DB because of > > it's compatibility with PHP. However I (probably hastily) *assumed* > > that there would be a way to export the data. Any ideas? > > > > Steve Jackson > > Web Developer > > Viola Systems Ltd. > > http://www.violasystems.com > > [EMAIL PROTECTED] > > Mobile +358 50 343 5159 > > > > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php > > > > > > > --- > Colonel Nathan R. Jessop > Commanding Officer > Marine Ground Forces > Guatanamo Bay, Cuba > --- > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
