Re: what is the rationale for not allowing LOCK TABLES in a stored procedure
Guys, I think I remember this from way back. You could ask for a lock, and get an OK if it is safe. Something like, if there is pending transactions, on your target tables, you would get a NO. But then again. I could be wrong, and Shawn is the authority on this. On 2018-08-18 23:59, shawn l.green wrote: Hello Jeff, On 8/13/2018 12:05 PM, j...@lxvi.net wrote: Hello, I have read through several pages of the reference manual, and I've seen several instances where it is stated that LOCK TABLES (and UNLOCK TABLES) is not allowed in a stored procedure, but so far, I haven't found an explanation as to *why* that is. Could someone please enlighten me? Thanks Normally, the list is more responsive than this. This is a pretty easy question and someone usually handles those before I need to step in as a backstop. The key why you cannot execute a LOCK TABLE command within a stored program is here: https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html ### LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables. ### Stored programs execute under the scope of the transaction in which they are started. That determines which sets of rows are "visible" to the routine and sets boundaries on what may be committed or rolled back should the need arise. (a simple example) * your session: START TRANSACTION * your session: ...other data activity ... * your session (INSERT ... ) * causes an INSERT trigger to fire * which calls a stored procedure If that stored procedure or that trigger called a LOCK TABLE command, it would forcibly COMMIT the existing transaction you had been working within until that moment. Your half-completed work would have become fully committed even if a later step had needed you to issue a ROLLBACK command. Note, even if you are not in a multi-statement transaction that any stored programs called by or executed within the scope of your user command are part of that little mini (auto-committed) transaction. Does that help? -- 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: Query Summary Help...
You need to GROUP BY those fields NOT in the aggregate function. Like: SELECT f.id,f.name,sum(p.price) FROM fruit f left join purchase p on f.id = p.fruit where p.price is not null group by f.id,f.name; 1, 'Apples', 2 2, 'Grapes', 6.5 4, 'Kiwis', 4 On 2015-10-23 04:15, Don Wieland wrote: Hi gang, I have a query: SELECT p.pk_ProductID, p.Description, i.Quantity FROM invoice_invoicelines_Product p JOIN invoice_InvoiceLines i ON p.pk_ProductID = i.fk_ProductID AND i.fk_InvoiceID IN (1,2,3) WHERE p.pk_ProductID IN (1,2,3); It produces a list like the following: 1,Banana,3 2,Orange,1 2,Orange,4 3,Melon,3 3,Melon,3 I want to SUM the i.Quantity per ProductID, but I am unable to get the scope/syntax correct. I was expecting the following would work: SELECT p.pk_ProductID, p.Description, SUM(i.Quantity) FROM invoice_invoicelines_Product p JOIN invoice_InvoiceLines i ON p.pk_ProductID = i.fk_ProductID AND i.fk_InvoiceID IN (1,2,3) WHERE p.pk_ProductID IN (1,2,3) GROUP BY i.fk_ProductID; but it is not working. Little help please. Thanks! Don Wieland d...@pointmade.net http://www.pointmade.net https://www.facebook.com/pointmade.band -- Mogens +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: When to create a new database
When I read the OP I was thinking: This is one for Reindl. And here we go. When dealing with data of this specific kind, you most definitely would want a date reference. A very small computer will be able to handle mane years of all kinds of weird sports statistics. You need to define the goal you are looking for, and then ask the question. On 2015-10-10 21:48, Reindl Harald wrote: Am 10.10.2015 um 16:28 schrieb Richard Reina: If I were keeping tract of high school sports statistics and thus designed the following tables: sports, rules, statistical definitions and players, teams, games Would it be a good or bad idea to put the first set of tables in a separate database called "library" since they are used for reference and rarely change? What would be the pros and cons of using two different databases? it makes zero sense since you can use different users for the same database down to table and even column permissions with default (crap) settings innodb anyways stores all in the same big file, and file_per_table is, well, per table -- Mogens +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Relational query question
Maybe not the most optimal, but (probably) the most simple: SELECT * FROM fruit where id not in (select fruit from purchase where customer=1); 1, 'Apples' 3, 'Oranges' On 2015-09-30 00:01, Richard Reina wrote: If I have three simple tables: mysql> select * from customer; +++ | ID | NAME | +++ | 1 | Joey | | 2 | Mike | | 3 | Kellie | +++ 3 rows in set (0.00 sec) mysql> select * from fruit; ++-+ | ID | NAME| ++-+ | 1 | Apples | | 2 | Grapes | | 3 | Oranges | | 4 | Kiwis | ++-+ 4 rows in set (0.00 sec) mysql> select * from purchases; ++-+--+ | ID | CUST_ID | FRUIT_ID | ++-+--+ | 2 | 3 | 2 | | 3 | 1 | 4 | | 4 | 1 | 2 | | 5 | 2 | 1 | ++-+--+ I am having trouble understanding a relational query. How can I select those fruits that Joey has not purchased? -- Mogens +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: checking for repetitive queries
https://www.thomas-krenn.com/en/wiki/MySQL_Performance_Tuning#Tuning_Tools On Mon, May 18, 2015 23:44, Reindl Harald wrote: Am 18.05.2015 um 23:37 schrieb Steve Quezadas: I want to make sure my caching system is working properly and I want to make sure my mysql server isnt being held up by repetitive queries (ie like the side products table that appears on every web page). I'm pretty sure I cached the site pretty well, but want to make sure that I didn't miss anything. Is there some sort of tool that allows me to check for repetitive queries? likely nobody knows what you are talking about http://www.catb.org/esr/faqs/smart-questions.html#beprecise -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Select one valuebut not the other
Right, Take a look at this one then: insert into test(item_number,data_value) values(1,1),(1,2),(1,3) ,(2,1),(2,3) ,(3,1),(3,2),(3,3) ,(4,1),(4,3); SELECT * FROM test WHERE item_number in (SELECT item_number FROM test where data_value=1) AND item_number not in (SELECT item_number FROM test where data_value = 2); Result: 2, 1 2, 3 4, 1 4, 3 On Wed, April 29, 2015 07:20, Olivier Nicole wrote: SELECT * FROM table WHERE item_number=1; Sorry if my question was not clear: what I am looking for is: SELECT * FROM table WHERE data_value=1 AND there is not any reccord with the same item_number and data_value=2 Olivier On Wed, April 29, 2015 06:30, Olivier Nicole wrote: Hi, I am sure that it is feasible with MySQl, and I am sure that's a newbie question, but my SQL skills are limited... I have a table where each record is made of one item_number and one data_value. If one item has several values, there are several records with the same item_number and a different data_value. What is the command to select all the records where an item_number has the data 1 but not the data 2? Thanks in advance, Olivier -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Select one valuebut not the other
SELECT * FROM table WHERE item_number=1; On Wed, April 29, 2015 06:30, Olivier Nicole wrote: Hi, I am sure that it is feasible with MySQl, and I am sure that's a newbie question, but my SQL skills are limited... I have a table where each record is made of one item_number and one data_value. If one item has several values, there are several records with the same item_number and a different data_value. What is the command to select all the records where an item_number has the data 1 but not the data 2? Thanks in advance, Olivier -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forum vs email
Just to pitch in, on this rather weird discussion. I've been on the MySQL pretty much from day one. I started on mSQL and transferred to MySQL when Monty took that corner. I'm probably not the only one, lurking in the shadows. On Sat, December 6, 2014 17:33, Reindl Harald wrote: Am 06.12.2014 um 16:53 schrieb h...@tbbs.net: 2014/12/06 12:51 +0100, Johan De Meersman I want: * The entire post, and as little notification-type content as possible, * headers and subjects so that mail clients that support threading will thread everything from a single forum topic in a mail thread and vice versa, * and, most importantly, the ability to also *reply* through mail and have it appear in the forum thread at the appropriate place in the conversation Those things are what would make it a proper mailing list integration, instead of just another notification tool. That is, this list, right? What does it lack (besides readers)? mail-clients using a readable quoting, your's do not :-) -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: INTO OUTFILE ERROR...
Maybe you need to grant access from localhost to the user? On Fri, September 19, 2014 16:59, Don Wieland wrote: On Sep 19, 2014, at 7:50 AM, Reindl Harald wrote: the target folder don't matter that is clearly a *MySQL error* that your *MYSQL user* lack the needed permissions Yes i figured that - so the question is, how do I give full permissions to that user? I did go to my cPanel and delete the user and recreated the user adding them to the DB with ALL PRIVILEGES. Obviously this is not enough. If this is not a simple thing that I can do myself, I am willing to compensate someone for support they can offer via GoToMeeting and SKYPE. I am spinning my wheels and I need to resolve this issue asap. Don Wieland d...@pointmade.net http://www.pointmade.net https://www.facebook.com/pointmade.band -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Stored Procedure help
Anders, I didn't see that at first, but now. I'd agree. Maybe I should read up on stored procedures. On Mon, July 14, 2014 16:25, Anders Karlsson wrote: The order makes quite a big difference, actually. In this case it ensures that the ordering of the values in the sort_id column is maintained, even though the numbers are different. Say this is your data (I have ignored the category thingy for now): SELECT id, sort_id FROM documents; +--+-+ | id | sort_id | +--+-+ |1 | 12 | |2 | 13 | |3 | 11 | +--+-+ Now if I run this the update without the order by: UPDATE documents SET sort_id = (@a := @a + 1) WHERE document_category = category; The result will be: SELECT id, sort_id FROM documents; +--+-+ | id | sort_id | +--+-+ |1 | 1 | |2 | 2 | |3 | 3 | +--+-+ Whereas with the order by UPDATE documents SET sort_id = (@a := @a + 1) WHERE document_category = category ORDER BY sort_id; the result would be: +--+-+ | id | sort_id | +--+-+ |1 | 2 | |2 | 3 | |3 | 1 | +--+-+ /Karlsson Keith Murphy skrev 2014-07-14 15:31: I would second what m. dykman says. There is no reason I can think of that you would even be doing the order by clause. keith On Sun, Jul 13, 2014 at 11:16 PM, yoku ts. yoku0...@gmail.com wrote: Would you try this? CREATE PROCEDURE `reset_sortid` (IN category INT(11)) BEGIN SET @a = 0; UPDATE documents SET sort_id = (@a := @a + 1) WHERE document_category = category ORDER BY sort_id; END // 2014-07-14 11:42 GMT+09:00 Don Wieland d...@pointmade.net: I am trying to create this stored procedure, but can't understand why my editor is chocking on it. Little help please: DELIMITER // CREATE PROCEDURE `reset_sortid` (IN category INT(11)) BEGIN DECLARE a INT; SET a = 0; UPDATE documents SET sort_id = (a := a + 1) WHERE document_category = category ORDER BY sort_id; END // Don Wieland d...@pointmade.net http://www.pointmade.net https://www.facebook.com/pointmade.band -- Anders Karlsson, Senior Sales Engineer SkySQL | t: +46 708-608-121 | Skype: drdatabase -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Precedence in WHERE clauses.
G'morning
A function in a where equals what ?
On Wed, March 19, 2014 15:05, Morgan Tocker wrote:
Hi Christophe,
Considering the following simple query :
SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
('PENDING', 'ACCEPTED') AND SubscribeDate DATE_SUB(NOW(), INTERVAL 24
HOUR);
Which of these filters are processed first ?
I'd like the first filter (DWProcessed / Lowest cardinality and indexed)
being processed first, but I can't really find any useful information
about this .
Is there any performance impact on query processing, about the order of
WHERE clauses ?
When a MySQL server receives a query, it goes through a process called
query optimization and tries to determine the best way to execute it
(based on availability of indexes etc). You can think of this as similar
to how GPS software picks the fastest route - it is very similar.
The order of the WHERE clause does not matter, and in fact more
complicated transformations happen in query optimization automatically.
For Example:
SELECT * FROM Status WHERE 1=1 AND DWProcessed = 0;
1=1 is detected as a tautology and removed.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
Mogens Melander
+66 8701 33224
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Getting distinct values in diff columns
Something like: select aname,max(adate) group by aname; On Thu, November 22, 2012 11:06, sagar bs wrote: Hi all, I have the table with two columns(account_name and order_date). In the account_name col, some account names are only once and few account names are twice and few others are 3/4/5 times and in col2 there is order_date. For same account name there may be 2/3/4/5 different order dates. Now I need to get distinct account names, and last order date, second last order date ans so on in different columns. Please help me out to solve the issue in mysql -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Basic SELECT help
Hmmm. OR, IN and HAVING pops up. On Thu, November 22, 2012 15:30, Neil Tompkins wrote: Hi, I'm struggling with what I think is a basic select but can't think how to do it : My data is id,type 1000,5 1001,5 1002,2 1001,2 1003,2 1005,2 1006,1 From this I what to get a distinct list of id where the type equals 2 and 5 Any ideas ? Neil -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Basic SELECT help
On Thu, November 22, 2012 15:45, Neil Tompkins wrote:
Basically I only what to return the IDs that have both types.
And that's exactly what below statement will return.
You forgot to include what platform you are on,
which version of MySQL you are running and
what class you are attending.
All necessary information to provide a sufficient help.
On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski
marek.gutow...@gmail.comwrote:
SELECT DISTINCT id FROM table WHERE type IN ('2','5')
should work
On 22 November 2012 14:30, Neil Tompkins
neil.tompk...@googlemail.comwrote:
Hi,
I'm struggling with what I think is a basic select but can't think how
to
do it : My data is
id,type
1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1
From this I what to get a distinct list of id where the type equals 2
and
5
Any ideas ?
Neil
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
Mogens Melander
+66 8701 33224
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Basic SELECT help
Ok, to make up for my bad joke, here's the answer
to the original question.
DROP TABLE IF EXISTS `test`.`atest`;
CREATE TABLE `test`.`atest` (
`id` int(10) unsigned NOT NULL,
`type` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
insert into atest(id,type)
values(1000,5)
,(1001,5)
,(1002,2)
,(1001,2)
,(1003,2)
,(1005,2)
,(1006,1);
SELECT DISTINCT id
FROM atest
WHERE `type` = 2 OR `type` = 5
GROUP BY id
HAVING count(DISTINCT `type`) = 2;
On Thu, November 22, 2012 22:16, Michael Dykman wrote:
Mogens,
Platform could not be less relevant to a question of MySql syntax.
The techniques we have been discussing have been available to every
version of MySql post v3.23 and the class/job function he is applying
it to is neither relevant to the problem nor any of our business,
unless he volunteers to share it. Excepting only the working
assumption that he is using a MySql version released in this century,
I don't know how this would have informed my analysis or response.
- michael dykman
On Thu, Nov 22, 2012 at 4:00 PM, Mogens Melander mog...@fumlersoft.dk
wrote:
On Thu, November 22, 2012 15:45, Neil Tompkins wrote:
Basically I only what to return the IDs that have both types.
And that's exactly what below statement will return.
You forgot to include what platform you are on,
which version of MySQL you are running and
what class you are attending.
All necessary information to provide a sufficient help.
On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski
marek.gutow...@gmail.comwrote:
SELECT DISTINCT id FROM table WHERE type IN ('2','5')
should work
On 22 November 2012 14:30, Neil Tompkins
neil.tompk...@googlemail.comwrote:
Hi,
I'm struggling with what I think is a basic select but can't think
how
to
do it : My data is
id,type
1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1
From this I what to get a distinct list of id where the type equals 2
and
5
Any ideas ?
Neil
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
Mogens Melander
+66 8701 33224
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
Mogens Melander
+66 8701 33224
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Dynamic crosstab got me lost.
On Mon, November 19, 2012 17:02, Shawn Green wrote: On 11/19/2012 9:02 AM, Mogens Melander wrote: On Mon, November 19, 2012 13:49, Jan Steinman wrote: From: Mogens Melander mog...@fumlersoft.dk So, I got a little further with my problem. I found an article on: http://stackoverflow.com/questions/3122424/dynamic-mysql-query-view-for-crosstab Describing how to do the dynamic generation of SQL statements. That's all good, kind of. The resulting SQL looks like this: SELECT main.code , IF(iconstandardrel.icon = 4,1,0) AS 'internationalt_produkt.eps' , IF(iconstandardrel.icon = 3,1,0) AS 'god_vaerdi.eps' , IF(iconstandardrel.icon = 2,1,0) AS 'for_miljoeets_skyld.eps' , IF(iconstandardrel.icon = 1,1,0) AS 'ergonomisk_produkt.eps' , IF(iconstandardrel.icon = 6,1,0) AS 'saml_selv.eps' , IF(iconstandardrel.icon = 12,1,0) AS 'brandfarlig.eps' FROM iconstandardrel JOIN main ON main.code = iconstandardrel.code JOIN iconstandard ON iconstandard.id = iconstandardrel.icon ORDER BY iconstandardrel.code; Which produces results like: 101577, 1, 0, 0, 0, 0, 0 101679, 0, 1, 0, 0, 0, 0 101679, 1, 0, 0, 0, 0, 0 101681, 1, 0, 0, 0, 0, 0 101748, 0, 1, 0, 0, 0, 0 101748, 1, 0, 0, 0, 0, 0 But I would like to have One line per code: 101577, 1, 0, 0, 0, 0, 0 101679, 1, 1, 0, 0, 0, 0 101681, 1, 0, 0, 0, 0, 0 101748, 1, 1, 0, 0, 0, 0 Is it possible to achieve this in pure SQL ? I think you need GROUP BY main.code. No, that ain't it either. I've tried that. But thanks anyway :) I can't figure out what to call this operation, to do a search. Someone out there must have done this before. You need both GROUP BY and either SUM or MAX, like this SELECT ... , SUM(IF(iconstandardrel.icon = 3,1,0)) AS 'god_vaerdi.eps' ... GROUP BY main.code; or SELECT ... , MAX(IF(iconstandardrel.icon = 3,1,0)) AS 'god_vaerdi.eps' ... GROUP BY main.code; That will combine (aggregate) your rows together. -- Shawn Green Super cool. The SUM() GROUP BY did the trick. Thanks a lot. -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Dynamic crosstab got me lost.
On Mon, November 19, 2012 13:49, Jan Steinman wrote: From: Mogens Melander mog...@fumlersoft.dk So, I got a little further with my problem. I found an article on: http://stackoverflow.com/questions/3122424/dynamic-mysql-query-view-for-crosstab Describing how to do the dynamic generation of SQL statements. That's all good, kind of. The resulting SQL looks like this: SELECT main.code , IF(iconstandardrel.icon = 4,1,0) AS 'internationalt_produkt.eps' , IF(iconstandardrel.icon = 3,1,0) AS 'god_vaerdi.eps' , IF(iconstandardrel.icon = 2,1,0) AS 'for_miljoeets_skyld.eps' , IF(iconstandardrel.icon = 1,1,0) AS 'ergonomisk_produkt.eps' , IF(iconstandardrel.icon = 6,1,0) AS 'saml_selv.eps' , IF(iconstandardrel.icon = 12,1,0) AS 'brandfarlig.eps' FROM iconstandardrel JOIN main ON main.code = iconstandardrel.code JOIN iconstandard ON iconstandard.id = iconstandardrel.icon ORDER BY iconstandardrel.code; Which produces results like: 101577, 1, 0, 0, 0, 0, 0 101679, 0, 1, 0, 0, 0, 0 101679, 1, 0, 0, 0, 0, 0 101681, 1, 0, 0, 0, 0, 0 101748, 0, 1, 0, 0, 0, 0 101748, 1, 0, 0, 0, 0, 0 But I would like to have One line per code: 101577, 1, 0, 0, 0, 0, 0 101679, 1, 1, 0, 0, 0, 0 101681, 1, 0, 0, 0, 0, 0 101748, 1, 1, 0, 0, 0, 0 Is it possible to achieve this in pure SQL ? I think you need GROUP BY main.code. No, that ain't it either. I've tried that. But thanks anyway :) I can't figure out what to call this operation, to do a search. Someone out there must have done this before. -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Dynamic crosstab got me lost.
On Wed, November 14, 2012 09:23, Mogens Melander wrote: Guru's I've been tasked with creating a report with information about which images are related to each active article. The images are descriptive icons visualizing different properties. Articles can be active 'on' or not '', and only active is to be included in the result. The number of available icons changes constantly, and are too many, to use anything static. What I got is: Table: main - code (int) active (on/off) - Table: icon - id (int) filename (char) - Table: iconrel - code (int) icon (int) - What I need is something like: code filename1 filename2 filename3 filenameN on off off on Hopefully somebody out there got more of a clue than I do. Any ideas, anybody? So, I got a little further with my problem. I found an article on: http://stackoverflow.com/questions/3122424/dynamic-mysql-query-view-for-crosstab Describing how to do the dynamic generation of SQL statements. That's all good, kind of. The resulting SQL looks like this: SELECT main.code , IF(iconstandardrel.icon = 4,1,0) AS 'internationalt_produkt.eps' , IF(iconstandardrel.icon = 3,1,0) AS 'god_vaerdi.eps' , IF(iconstandardrel.icon = 2,1,0) AS 'for_miljoeets_skyld.eps' , IF(iconstandardrel.icon = 1,1,0) AS 'ergonomisk_produkt.eps' , IF(iconstandardrel.icon = 6,1,0) AS 'saml_selv.eps' , IF(iconstandardrel.icon = 12,1,0) AS 'brandfarlig.eps' FROM iconstandardrel JOIN main ON main.code = iconstandardrel.code JOIN iconstandard ON iconstandard.id = iconstandardrel.icon ORDER BY iconstandardrel.code; Which produces results like: 101577, 1, 0, 0, 0, 0, 0 101679, 0, 1, 0, 0, 0, 0 101679, 1, 0, 0, 0, 0, 0 101681, 1, 0, 0, 0, 0, 0 101748, 0, 1, 0, 0, 0, 0 101748, 1, 0, 0, 0, 0, 0 But I would like to have One line per code: 101577, 1, 0, 0, 0, 0, 0 101679, 1, 1, 0, 0, 0, 0 101681, 1, 0, 0, 0, 0, 0 101748, 1, 1, 0, 0, 0, 0 Is it possible to achieve this in pure SQL ? -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Issue regarding the import of the date from csv file to the table in the database in mysql
Did you change the target column to varchar before import ?
On Wed, November 14, 2012 10:23, sagar bs wrote:
tried to import data as text, but its showing Operation failed with
exitcode 1
On Wed, Nov 14, 2012 at 1:12 PM, Mogens Melander
mog...@fumlersoft.dkwrote:
Or you could import the date as text and convert dates using:
mysql SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
- '2004-04-31'
On Wed, November 14, 2012 06:13, Larry Martell wrote:
On Tue, Nov 13, 2012 at 9:56 PM, sagar bs bs.sag...@gmail.com wrote:
Hi,
As i have the data with some 25 variables in csv file and i need to
import
to mysql.
The issue is that the date format in csv file is dd/mm/ and mysql
takes
the date format like /mm/dd.
The number of variables in the csv file are same in the table in
database
of mysql.
Please help me out.
Convert the data in your CSV file into the format you need. So many
ways to do that, e.g.: bring in into excel and change the column's
format, use sed, use python, use vi,
--
Mogens Melander
+66 8701 33224
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
Regards
SAGAR B S
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
Mogens Melander
+66 8701 33224
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
RE: Dynamic crosstab got me lost.
Thanks Rick Yes, I know it's not going the most (in lack of descriptive words) pretty piece of code. The variable number of columns is the key phrase here. I've already told them, this is not a SQL task, but having infinite trust in my peers out there I know it can be done. I just fail to do the logic myself. Believe me, I tried. On Wed, November 14, 2012 16:49, Rick James wrote: While it is possible to pivot a table like that, the code is ugly, and does not work well if you need a variable number of columns. SQL can do the JOIN to get a single resultset for the information, but the layout is best done in a application language, such as PHP. -Original Message- From: Mogens Melander [mailto:mog...@fumlersoft.dk] Sent: Wednesday, November 14, 2012 12:23 AM To: mysql@lists.mysql.com Subject: Dynamic crosstab got me lost. Guru's I've been tasked with creating a report with information about which images are related to each active article. The images are descriptive icons visualizing different properties. Articles can be active 'on' or not '', and only active is to be included in the result. The number of available icons changes constantly, and are too many, to use anything static. What I got is: Table: main - code (int) active (on/off) - Table: icon - id (int) filename (char) - Table: iconrel - code (int) icon (int) - What I need is something like: code filename1 filename2 filename3 filenameN on off off on Hopefully somebody out there got more of a clue than I do. Any ideas, anybody? -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Issue regarding the import of the date from csv file to the table in the database in mysql
Dude, which part of RTFM did yoy miss? http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html On Wed, November 14, 2012 13:57, sagar bs wrote: Hi, There are four columns in my table named like account_name, c1, c2 and c3. Account name is the primary key and c1, c2 contain two different dates and in the column c2 there are few fields showing /00/00, now i need to get the date different(in days) between the dates present in the c1 and c2. That days should be shown in the c3. please help me out. On Wed, Nov 14, 2012 at 3:46 PM, h...@tbbs.net wrote: 2012/11/14 10:26 +0530, sagar bs As i have the data with some 25 variables in csv file and i need to import to mysql. The issue is that the date format in csv file is dd/mm/ and mysql takes the date format like /mm/dd. The number of variables in the csv file are same in the table in database of mysql. Please help me out. Use LOAD DATA s feature of in the same SQL statement importing into a user variable and using it with SET, using the function STR_TO_DATE: load data ... (..., @dait, ...) ... SET Sins = STR_TO_DATE(@dait, '%d/%m/%Y') . It is not important how many decimal digits match each pattern, but it cannot match variation in the separators. It is required that NULLs in the file take the form '\N' or 'NULL', depending on escape-option. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Regards SAGAR B S -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Issue regarding the import of the date from csv file to the table in the database in mysql
I guess I'm sill learning. Does that mean that, if the last column in a load blabla. is a -00-00 terminated by ^n it might error ? Or are we talking ODBC ? On Wed, November 14, 2012 18:58, h...@tbbs.net wrote: 2012/11/14 18:27 +0530, sagar bs There are four columns in my table named like account_name, c1, c2 and c3. Account name is the primary key and c1, c2 contain two different dates and in the column c2 there are few fields showing /00/00, now i need to get the date different(in days) between the dates present in the c1 and c2. That days should be shown in the c3. please help me out. Try DATEDIFF. As for date /00/00, MySQL s treatment of NULLs in CSV files is peculiar: it wants the escape NULL or \N, separator right after separator is not NULL, but empty string. Consider those NULL. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Issue regarding the import of the date from csv file to the table in the database in mysql
6:40 AM: Sorry, didn't mean to be rude. It's in there. On Thu, November 15, 2012 00:23, Mogens Melander wrote: Dude, which part of RTFM did yoy miss? http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html On Wed, November 14, 2012 13:57, sagar bs wrote: Hi, There are four columns in my table named like account_name, c1, c2 and c3. Account name is the primary key and c1, c2 contain two different dates and in the column c2 there are few fields showing /00/00, now i need to get the date different(in days) between the dates present in the c1 and c2. That days should be shown in the c3. please help me out. On Wed, Nov 14, 2012 at 3:46 PM, h...@tbbs.net wrote: 2012/11/14 10:26 +0530, sagar bs As i have the data with some 25 variables in csv file and i need to import to mysql. The issue is that the date format in csv file is dd/mm/ and mysql takes the date format like /mm/dd. The number of variables in the csv file are same in the table in database of mysql. Please help me out. Use LOAD DATA s feature of in the same SQL statement importing into a user variable and using it with SET, using the function STR_TO_DATE: load data ... (..., @dait, ...) ... SET Sins = STR_TO_DATE(@dait, '%d/%m/%Y') . It is not important how many decimal digits match each pattern, but it cannot match variation in the separators. It is required that NULLs in the file take the form '\N' or 'NULL', depending on escape-option. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Regards SAGAR B S -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Issue regarding the import of the date from csv file to the table in the database in mysql
Or you could import the date as text and convert dates using:
mysql SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
- '2004-04-31'
On Wed, November 14, 2012 06:13, Larry Martell wrote:
On Tue, Nov 13, 2012 at 9:56 PM, sagar bs bs.sag...@gmail.com wrote:
Hi,
As i have the data with some 25 variables in csv file and i need to
import
to mysql.
The issue is that the date format in csv file is dd/mm/ and mysql
takes
the date format like /mm/dd.
The number of variables in the csv file are same in the table in
database
of mysql.
Please help me out.
Convert the data in your CSV file into the format you need. So many
ways to do that, e.g.: bring in into excel and change the column's
format, use sed, use python, use vi,
--
Mogens Melander
+66 8701 33224
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: optimize and tmpfiles
On Mon, October 29, 2012 18:11, walter harms wrote: Am 29.10.2012 14:55, schrieb Reindl Harald: Am 29.10.2012 14:54, schrieb walter harms: Am 29.10.2012 13:17, schrieb Reindl Harald: Am 29.10.2012 12:48, schrieb walter harms: hi list, does someone know under what circumstances ''optimize tables'' will create a tmpfile? under all if it is MyISAM and for select id from table order by rand(); too interessting, i was trying to force it (with optimize) but it did not work. Is there a way to restrict the maximum size of those tmpfiles? you do you imagine restrict them? actually i espected a no, but sometimes i have to think positv. they are as big as the table that is a problem with a large table Well, my friend. That's a hardware problem ;^) re, wh -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: update doesn't
On Sun, August 19, 2012 18:19, william drescher wrote: On 8/17/2012 12:13 PM, Rik Wasmus wrote: I get 1 row affected, but the status does not change when I look at the row. If I set it to 'X' it does change. To make it even more wacky, if I (using phpMyAdmin) change it to 'H' it will change and the row is shown change, but when I go to examine the row (using the pencil icon=Edit) it changes back to 'W'. Either there is something really strange or my mysql is possessed. I am using Server version: 5.1.63-0ubuntu0.10.04. Anyone have any thoughts about this or suggestions on how to debug it? 1) One thing that _could_ do this is a trigger. Does SHOW TRIGGERS; show any that could be doing this? 2) However, in 99.999% of cases, it is just a logic error in the application (be it your application or PHPMyAdmin), not anything in MySQL. Can you connect with the command line client, run the UPDATE statement, en then check what the SELECT shows? If it shows a correct result... the problem ain't in MySQL itself. mysql select status from tasks; ++ | status | ++ | W | ++ 1 row in set (0.00 sec) mysql update tasks set status= 'H'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed 1 Warnings: 0 mysql select status from tasks; ++ | status | ++ | W | ++ 1 row in set (0.00 sec) whoops bill Maybe a SHOW CREATE TABLE `tasks`\g could shed some light. -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: New Fast MySQL Compatible Server
Guys, really... Is this a binary release? Need to sudo to root? Not on my watch! On Tue, March 27, 2012 16:40, Baron Schwartz wrote: The license of this software is unclear. The binary is clearly derived from the MySQL server with InnoDB, but no source code is provided. Is the software GPL-licensed? If so, where can I get a copy of the source code? - Baron On Tue, Mar 27, 2012 at 10:26 AM, Hiromichi Watari hiromichiwat...@yahoo.com wrote: Hi, I created Parallel Universe which is a MySQL 5.5 compatible server with fast query execution. Speed is achieved by processing tables in parallel utilizing multi core/CPU server hardware. Pre-release field evaluation is being conducted and MySQL users are solicited to try out the new server for feedback. Please go to www.paralleluniverse-inc.com to download. Thank you, Hiromichi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- Baron Schwartz Win free MySQL conference tickets! http://goo.gl/mvZ4W -- Later Mogens Melander -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Wierd PHP/MySQL result
Hi guys I'm getting rather confused on this thing. Results returned from mysql console/query-browser is different from what's returned from same query in PHP. Maybe someone smarter than me could shed some light on what's going on. I'm clueless :( Environment is as follow: Mysql Server version: 5.1.22-rc-log Source distribution PHP 5.2.9 (cli) (built: Mar 31 2009 18:41:50) Apache/2.2.9 (Unix) mod_ssl/2.2.9 OpenSSL/0.9.8k DAV/2 PHP/5.2.9 The SQL: select c.id, c.owner, if(c.owner 0, s.name, 'Unknown') oname , c.fname, c.lname, c.addr1, c.addr2, c.zipcode, c.city , c.phone, c.mobile, c.email, c.web, c.saldo from customer c left join shop s on s.id=c.owner where c.id = 1; Result from PHP: id:1 ,owner:2 ,oname:null === Problem data ,fname:Jane ,lname:Doe ,addr1:Solvej 1 ,addr2: ,zipcode:1234 ,city:Sommerby ,phone:11 22 33 44 ,mobile: ,email: ,web: ,saldo:-7500.00 Result from MySQL console/query-browser: 1 , 2 , 'Long description' Correct data , 'Jane' , 'Doe' , 'Solvej 1' , '' , '1234' , 'Sommerby' , '11 22 33 44' , '' , '' , '' , -7500.00 -- Later Mogens Melander -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Wierd PHP/MySQL result
Well, no typos.
The relevant piece of code:
if (! $cust = mysql_fetch_assoc($res))
{
echo {success: false, errors: { reason: 'Fetch Customer failed: .
htmlspecialchars(mysql_error(),ENT_QUOTES) .
br . htmlspecialchars($sql,ENT_QUOTES) . '}};
}
else
{
echo {success: true, total: 1, results:[ . json_encode($cust) . ]};
}
On Tue, September 22, 2009 22:11, lists-mysql wrote:
You didn't show your php code, but I'd bet you have a typo, likely
in assigning the returned value to the variable, or in the name of
the variable you're printing.
- Rick
Original Message
Date: Tuesday, September 22, 2009 10:02:06 PM +0200
From: Mogens Melander mog...@fumlersoft.dk
To: mysql@lists.mysql.com
Subject: Wierd PHP/MySQL result
Hi guys
I'm getting rather confused on this thing.
Results returned from mysql console/query-browser is
different from what's returned from same query in PHP.
Maybe someone smarter than me could shed some light on
what's going on. I'm clueless :(
Environment is as follow:
Mysql Server version: 5.1.22-rc-log Source distribution
PHP 5.2.9 (cli) (built: Mar 31 2009 18:41:50)
Apache/2.2.9 (Unix) mod_ssl/2.2.9 OpenSSL/0.9.8k DAV/2 PHP/5.2.9
The SQL:
select c.id, c.owner, if(c.owner 0, s.name, 'Unknown') oname
, c.fname, c.lname, c.addr1, c.addr2, c.zipcode, c.city
, c.phone, c.mobile, c.email, c.web, c.saldo
from customer c
left join shop s on s.id=c.owner
where c.id = 1;
Result from PHP:
id:1
,owner:2
,oname:null === Problem data
,fname:Jane
,lname:Doe
,addr1:Solvej 1
,addr2:
,zipcode:1234
,city:Sommerby
,phone:11 22 33 44
,mobile:
,email:
,web:
,saldo:-7500.00
Result from MySQL console/query-browser:
1
, 2
, 'Long description' Correct data
, 'Jane'
, 'Doe'
, 'Solvej 1'
, ''
, '1234'
, 'Sommerby'
, '11 22 33 44'
, ''
, ''
, ''
, -7500.00
--
Later
Mogens Melander
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Wierd PHP/MySQL result (Solved)
Ok, i got it.
It's the json_encode($cust) that seems to have problems
with danish characters ( æ ø å Æ Ø Å ).
json_encode is supposed to work with utf8 only, and
my tables are all utf8 so that's wierd too :(
Seems like i get to do the json_encoding by hand.
On Tue, September 22, 2009 22:35, Mogens Melander wrote:
Well, no typos.
The relevant piece of code:
if (! $cust = mysql_fetch_assoc($res))
{
echo {success: false, errors: { reason: 'Fetch Customer failed: .
htmlspecialchars(mysql_error(),ENT_QUOTES) .
br . htmlspecialchars($sql,ENT_QUOTES) . '}};
}
else
{
echo {success: true, total: 1, results:[ . json_encode($cust) . ]};
}
On Tue, September 22, 2009 22:11, lists-mysql wrote:
You didn't show your php code, but I'd bet you have a typo, likely
in assigning the returned value to the variable, or in the name of
the variable you're printing.
- Rick
Original Message
Date: Tuesday, September 22, 2009 10:02:06 PM +0200
From: Mogens Melander mog...@fumlersoft.dk
To: mysql@lists.mysql.com
Subject: Wierd PHP/MySQL result
Hi guys
I'm getting rather confused on this thing.
Results returned from mysql console/query-browser is
different from what's returned from same query in PHP.
Maybe someone smarter than me could shed some light on
what's going on. I'm clueless :(
Environment is as follow:
Mysql Server version: 5.1.22-rc-log Source distribution
PHP 5.2.9 (cli) (built: Mar 31 2009 18:41:50)
Apache/2.2.9 (Unix) mod_ssl/2.2.9 OpenSSL/0.9.8k DAV/2 PHP/5.2.9
The SQL:
select c.id, c.owner, if(c.owner 0, s.name, 'Unknown') oname
, c.fname, c.lname, c.addr1, c.addr2, c.zipcode, c.city
, c.phone, c.mobile, c.email, c.web, c.saldo
from customer c
left join shop s on s.id=c.owner
where c.id = 1;
Result from PHP:
id:1
,owner:2
,oname:null === Problem data
,fname:Jane
,lname:Doe
,addr1:Solvej 1
,addr2:
,zipcode:1234
,city:Sommerby
,phone:11 22 33 44
,mobile:
,email:
,web:
,saldo:-7500.00
Result from MySQL console/query-browser:
1
, 2
, 'Long description' Correct data
, 'Jane'
, 'Doe'
, 'Solvej 1'
, ''
, '1234'
, 'Sommerby'
, '11 22 33 44'
, ''
, ''
, ''
, -7500.00
--
Later
Mogens Melander
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=mog...@fumlersoft.dk
--
Later
Mogens Melander
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Wierd PHP/MySQL result
Yes, you are right. It's the json_encode that fail.
It's strange though. The data that fail is Humlebæk.
Another query, in another form, returning Værksted don't
fail in json_encode.
echo $cust['oname'];give the correct value.
On Tue, September 22, 2009 22:56, Mark Goodge wrote:
Mogens Melander wrote:
Well, no typos.
The relevant piece of code:
if (! $cust = mysql_fetch_assoc($res))
{
echo {success: false, errors: { reason: 'Fetch Customer failed: .
htmlspecialchars(mysql_error(),ENT_QUOTES) .
br . htmlspecialchars($sql,ENT_QUOTES) . '}};
}
else
{
echo {success: true, total: 1, results:[ . json_encode($cust) . ]};
}
What happens if you simplify the PHP?
$cust = mysql_fetch_assoc($res);
print_r($cust);
That will tell you what PHP is seeing from MySQL. If the results have
the correct values, then the problem is elsewhere in the code.
I suspect that the problem is in the json_encode() function. According
to the PHP documentation this requires utf8-encoded data. If your values
in oname are not utf8, then that may explain why the function is
returning a 'null' where it should have a string.
Mark
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=mog...@fumlersoft.dk
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
Later
Mogens Melander
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: database design
Well, if you have a fixed number of article types, then maybe. If there is a chance of more types being added later, then no. Are you planning to hard code selection lists in your front end, or would you like to retrieve data from sql ??? Maybe a read up on Database Normalization is due: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html On Sat, September 12, 2009 07:19, AndrewJames wrote: thank you all, i think You probably wouldn't need Article_Type table if you're going to store Article_Type value directly. is my answer. -- From: Kyong Kim kykim...@gmail.com Sent: Saturday, September 12, 2009 8:22 AM To: Arthur Fuller fuller.art...@gmail.com Cc: Claudio Nanni claudio.na...@gmail.com; AndrewJames andrewhu...@gmail.com; mysql mysql@lists.mysql.com Subject: Re: database design A) You would probably want to populate the Article.Article_Type column with Article_Type.ID. You probably wouldn't need Article_Type table if you're going to store Article_Type value directly. I would also consider the use of natural primary key vs surrogate primary key. We've seen good results with primary key lookups on large tables (especially creating grouped subsets of data) If you imagine your data set growing fairly large, you should take a stab at projecting your workload to determine whether you would want to optimize access speed vs insert. For example, if you will be searching the article table by uid, you might want to cluster the data by uid so all related articles will be stored next to each other. Kyong On Fri, Sep 11, 2009 at 5:44 AM, Arthur Fuller fuller.art...@gmail.com wrote: I agree with Claudio. You have your design correct. The only other thing you need is the uid qualifier. Presumably you are using PHP or some other front end to present your data. Your front end would request the user's name and password, saving the uid in a variable and then issuing the select with a WHERE clause that passes the uid in: select * from articles A left joing article_types AT on A.article_type = AT.Arcticle_types_id WHERE A.uid = insert your variable here hth, Arthur On Fri, Sep 11, 2009 at 8:22 AM, Claudio Nanni claudio.na...@gmail.comwrote: A.J., It sounds good to me! You can be a little confused but you did it well, It seems you have all you need there. A) Yes B) select * from articles A left join article_types AT on A.article_type = AT.article_types_id Claudio 2009/9/11 AndrewJames andrewhu...@gmail.com This is a bit of a long shot, but i really need some help and or directed to the best reading resources. as i begun building my database (as i went along), i now realise i have to stop coding and sit back and design the database properly before i can go on. However i am still unable to wrap my head around what data to put into what tables, and which columns i need to link to make the relationships. so far, here is what i have. TABLES: users -uid(pk) -username -password articles -article_id(pk) -uid(fk) -article_type(fk) -article_subject -article_body article_types -article_types_id(pk) -article_type So i want the user to be able to login and add articles. I then want to be able to view all the articles the user has submitted. So in my understanding i need to link the users.uid(pk) to the articles.uid(fk) (so i know which user the article belongs to, please correct and update me if i am wrong) I am stuck at this point. A) Have i created the right tables and columns for each table, AND B) How do i link the articles.article_type to articles_type.type? (IF in fact that is even the correct linkage)?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mog...@fumlersoft.dk -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Later Mogens Melander -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Uptimize: join table on if()
Thanks for the suggestions.
My original query returned 5784 rows in 184.89 sec. Brent Baisley's variant
returned 5784 rows in 1.34 sec. and Peter Brawley's solution resulted in
5780 rows in 1.46 sec. I'm officially impressed :)
I can't figure out why the UNION solution is missing 4 rows. I'll include
the 3 complete statements, so maybe someone smarter than me can figure out
why there's a difference in the result.
===[ Brent Baisley ]
select m.code, m.parent,
if( m.parent 0, t.line1, t1.line1 ) AS line1,
if( m.parent 0, t.line2, t1.line2 ) AS line2,
if( m.parent 0, t.line3, t1.line3 ) AS line3,
if( m.parent 0, t.line4, t1.line4 ) AS line4,
if( m.parent 0, t.line5, t1.line5 ) AS line5,
if( m.parent 0, t.line6, t1.line6 ) AS line6,
if( m.parent 0, t.line7, t1.line7 ) AS line7,
if( m.parent 0, t.line8, t1.line8 ) AS line8,
if( m.parent 0, t.line9, t1.line9 ) AS line9,
if( m.parent 0, t.line10, t1.line10 ) AS line10,
u.unit, u.pack
from main m
left join olomulti t
on t.code = m.parent and t.country='dk'
left join olomulti t1
on t1.code=m.code and t1.country='dk'
left join unit u
on u.code=m.code and u.country='dk'
where m.activedk = 'on' and m.olomultidk = 'on'
order by m.code;
===[ Peter Brawley ]
select m.code, m.parent, t.line1, t.line2, t.line3, t.line4,
t.line5, t.line6, t.line7, t.line8, t.line9, t.line10, u.unit, u.pack
from main m
left join olomulti t
on t.code = m.parent and t.country='dk'
left join unit u
on u.code=m.code and u.country='dk'
where m.parent0
and m.activedk = 'on'
and m.olomultidk = 'on'
and t.line1 is not null
union
select m.code, m.parent, t.line1, t.line2, t.line3, t.line4,
t.line5, t.line6, t.line7, t.line8, t.line9, t.line10, u.unit, u.pack
from main m
left join olomulti t
on t.code = m.code and t.country='dk'
left join unit u
on u.code=m.code and u.country='dk'
where m.parent=0
and m.activedk = 'on'
and m.olomultidk = 'on'
and t.line1 is not null
order by code;
===[ My Self ]
select m.code, m.parent, concat('\'',lpad(m.code,18,'0'),'\'') lcode, t.line1,
t.line2,
t.line3, t.line4, t.line5, t.line6, t.line7, t.line8, t.line9, t.line10,
u.unit, u.pack
from main m
left join olomulti t
on ( if( m.parent 0, t.code = m.parent, t.code = m.code ) and t.country='dk'
)
left join unit u
on u.code=m.code and u.country='dk'
where m.activedk = 'on' and m.olomultidk = 'on'
order by m.code;
==
On Mon, December 8, 2008 22:48, Brent Baisley wrote:
On Mon, Dec 8, 2008 at 7:14 AM, Mogens Melander [EMAIL PROTECTED] wrote:
Hi list
I have this statement that really need optimizing. The result is
about 5500 rows, and it runs for about 3-5 minutes. If i do the
same in a PHP script (outer loop, inner loop) it run in 20 sec.
The idea is that data in tdata might be shared between 2 or more
records in main. The main.parent field is a pointer to main.code,
so if main.parent is positive, i need to retrieve data linked to parent.
Did i miss something?
select m.code, m.parent, t.data
from main m
left join tdata t
on ( if( m.parent 0, t.code = m.parent, t.code = m.code ) and
t.country='dk' )
where m.active = 'on' and m.tdataon = 'on'
order by m.code;
CREATE TABLE `main` (
`code` int(10) unsigned NOT NULL default '0',
`parent` int(10) unsigned NOT NULL default '0',
`active` varchar(2) NOT NULL,
`tdataon` varchar(2) NOT NULL default '',
PRIMARY KEY (`code`),
KEY `parent` (`parent`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `data` (
`code` int(10) unsigned NOT NULL default '0',
`country` varchar(2) NOT NULL default '',
`data` varchar(130) NOT NULL default '',
PRIMARY KEY (`code`,`country`),
KEY `code` (`code`),
KEY `country` (`country`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
What you are doing wrong is putting a condition on the JOIN. This
prevent MySQL from optimizing the query because it has to check every
record to determine the join. Put the condition in the field list you
pull. Alias the table you are joining on so you can join it twice, one
for each condition.
select m.code, m.parent,
if( m.parent 0, t.data, t1.data ) AS data
from main m
left join tdata t
on t.code = m.parent and t.country='dk'
left join tdata t1
on t1.code=m.code and t1.country='dk'
where m.active = 'on' and m.tdataon = 'on'
order by m.code;
That may not be completely correct. What you are doing is getting 2
copies of the data field and conditional adding the one you need to
the retrieved record.
Brent Baisley
--
Later
Mogens Melander
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http
Re: Uptimize: join table on if()
Hmmm, no. That's not it. The 2 queries using if() return the right number of rows. The union return too few. Anyway. I ended up with a query that runs in about 1 sec. compared to the original that ran about 3 min. I'm happy :) Thanks a bunch. On Tue, December 9, 2008 14:53, Thomas Pundt wrote: On Dienstag, 9. Dezember 2008, Mogens Melander wrote: | I can't figure out why the UNION solution is missing 4 rows. I'll include | the 3 complete statements, so maybe someone smarter than me can figure out | why there's a difference in the result. Without having studied your query, my guess would be: double rows. UNION eliminates those; if you need them, use UNION ALL. Just a guess though... Ciao, Thomas -- Thomas Pundt [EMAIL PROTECTED] http://rp-online.de/ -- Later Mogens Melander -- This message has been scanned for viruses and dangerous content by MailScanner, 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]
Uptimize: join table on if()
Hi list I have this statement that really need optimizing. The result is about 5500 rows, and it runs for about 3-5 minutes. If i do the same in a PHP script (outer loop, inner loop) it run in 20 sec. The idea is that data in tdata might be shared between 2 or more records in main. The main.parent field is a pointer to main.code, so if main.parent is positive, i need to retrieve data linked to parent. Did i miss something? select m.code, m.parent, t.data from main m left join tdata t on ( if( m.parent 0, t.code = m.parent, t.code = m.code ) and t.country='dk' ) where m.active = 'on' and m.tdataon = 'on' order by m.code; CREATE TABLE `main` ( `code` int(10) unsigned NOT NULL default '0', `parent` int(10) unsigned NOT NULL default '0', `active` varchar(2) NOT NULL, `tdataon` varchar(2) NOT NULL default '', PRIMARY KEY (`code`), KEY `parent` (`parent`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `data` ( `code` int(10) unsigned NOT NULL default '0', `country` varchar(2) NOT NULL default '', `data` varchar(130) NOT NULL default '', PRIMARY KEY (`code`,`country`), KEY `code` (`code`), KEY `country` (`country`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- Later Mogens Melander -- This message has been scanned for viruses and dangerous content by MailScanner, 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: Password storage
On Sat, August 18, 2007 15:53, C K wrote:
Thanks to all,
but the problem is that I am using external programs to insert data and I
can't use MySQL functions directly. Can I call/implement such type of
functions using MS Access 2003?
MD5() is not an encryption function. The MySQL manual states:
QUOTE
MD5(str)
Calculates an MD5 128-bit checksum for the string. The value
is returned as a binary string of 32 hex digits, or NULL if
the argument was NULL. The return value can, for example,
be used as a hash key.
mysql SELECT MD5('testing');
- 'ae2b1fca515949e5d54fb22b8ed95575'
This is the “RSA Data Security, Inc. MD5 Message-Digest Algorithm.”
/QUOTE
You might want to look at ENCODE() and DECODE() functions. Again from the
manual:
QUOTE
DECODE(crypt_str,pass_str)
Decrypts the encrypted string crypt_str using pass_str as
the password. crypt_str should be a string returned from ENCODE().
ENCODE(str,pass_str)
Encrypt str using pass_str as the password.
To decrypt the result, use DECODE().
The result is a binary string of the same length as str.
The strength of the encryption is based on how good the random
generator is. It should suffice for short strings.
/QUOTE
These are all functions you use in your sql statement, so yes. They can be
used in MS Access.
--
Later
Mogens Melander
+45 40 85 71 38
+66 870 133 224
--
This message has been scanned for viruses and
dangerous content by MailScanner, 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: Password storage
On Sat, August 18, 2007 20:17, Mike Aubury wrote: But you can use it for passwords (ask Unix)... You can't decode what the original password was, but you can encode the password they typed in and check the two hashes match - if they do - the chances are that the original passwords match (the odds against are huge!) Well, i got the impression that OP wanted to retrieve the cleartext string, but i could be wrong. On Saturday 18 August 2007 16:19, Mogens Melander wrote: MD5() is not an encryption function. The MySQL manual states: -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, 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: Seeing Table Structure
Try this: # mysqldump -d DBNAME tables.sql --no-data, -d Do not write any row information for the table. This is very useful if you want to dump only the CREATE TABLE statement for the table. On Mon, July 23, 2007 18:22, Olaf Stein wrote: I don't know of any way of doing this for all tables. I wrote a python script that creates a html file with information about tables (engine, fields,keys,indices) If you are interested in it I can email it Olaf On 7/23/07 11:34 AM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi; I need to see the table structure of all the tables of a database I have. I could also just dump all the data from all the tables in the database and copy the database, thus grabbing the table structures. I can´t find in the documentation how to do either of those. Please advise. TIA, Tony AOL now offers free email to everyone. Find out more about what's free from AOL at AOL.com. -- 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. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, 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: su-like functionality
On Mon, July 23, 2007 10:19, Carlo Sogono wrote: Is there a way for mysql to login as an administrator and su to a normal user? What I'd like to achieve is a way to log in to our clients' accounts (we are a web-hosting company) without having to use their passwords. Having to su keeps ownerships and stuff like that in check. Thanks in advance, Carlo ??? I'm having a hard time imagine what you want to do with that ??? You, being the administrator (root), you should be able to do whatever, on any DB on your server, without being recognzied as anybody. Please elaborate. -- This message has been scanned for viruses and dangerous content by MailScanner, 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: performance of extended insert vs. load data
Shure, load data is way faster than full inserts. I was thinking: while $warnings -lt 100% do dump ora-data | mysql database done swap IP-addr. On Mon, July 23, 2007 19:59, B. Keith Murphy wrote: I think you will find the load data infile will work faster. I am performing testing right now in preparation for a migration from 4.1 to 5.0 but I am confident that will be the case. Keith - Original Message - From: Sid Lane [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, July 23, 2007 1:44:53 PM (GMT-0500) America/New_York Subject: performance of extended insert vs. load data all, I need to migrate ~12GB of data from an Oracle 10 database to a MySQL 5.0one in as short a window as practically possible (throw tablespace in r/o, migrate data repoint web servers - every minute counts). the two approaches I am considering are: 1. write a program that outputs the Oracle data to a fifo pipe (mknod) and running a load data infile against it 2. write a program that dynamically builds extended insert statements up to length of max_allowed_packet (similar to mysqldump -e) is either one significantly faster than the other? I know I could benchmark it but I was hoping someone could save me writing #2 to find out if it's not the way to go... are there additional (faster) approaches I have not thought of? FWIW these are 95% innodb (5% myisam are static reference tables can be done in advance). thanks! -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, 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: mysql dump help!
mysqldump -u user -p -q --single-transaction --tab=/dest/dir database or mysql -u root -ppassword database output.sql On Tue, July 24, 2007 03:16, Red Hope wrote: Hey y'all, I've been fussing with the MySQL dump procedure to backup my stuff. I'm on MySQL 5.0.41 and I have tried so hard to get it to dump my files into .sql format but my syntax is wrong, wrong, wrong. lol I put below examples what I've been entering to get my databases backed up. I always get an error or I get locked into the - thing if I don't use the semicolons. Hmmm, try \qenter before typing mysqldump ;^) Any help would be awesome! Thank you, Lillian Tried Wrong mysqldump -u root -ppassword test test.sql mysqldump -u root -ppassword test test.sql; mysqldump -u root -ppassword -databases test test.sql; Yahoo! oneSearch: Finally, mobile search that gives answers, not web links. http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC -- 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. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, 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: mysql dump help!
I think we are daling with a windows user, who are not that familiar with a command prompt. Correct me if i'm wrong. How to get to a point where you would be able to execute a mysqldump, will depend on what OS you are running, and how you installed MySQL. On Tue, July 24, 2007 04:41, Carlos Proal wrote: Can you email us the complete command and the error ? Carlos Red Hope wrote: Well, that went over my head. :) I understand what you're telling me, how to get there, but not how to do it. bleh. When I start up MySQL Command Line Client, I'm always prompted at mysql. So I told it to switch from that prompt to shell prompt. It always starts up in mysql prompt. Once I'm in shell, I tried the dump procedure and it kept saying it couldn't connect. So I checked what databases it had, it shows them. I can't even switch to a database because of no connection. I'm not exactly sure why there's no 'connection' at all. Thanks for trying so hard, Carlos! Lillian -- 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. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, 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: su-like functionality
Ok, if memory serves me right, phpmyadmin use the credentials from mysql's grant/deny schema. So when loggin into PMA, you will have the priveleges on the server that was granted to that user. When installing/configuring PMA, it will insist on getting a root/privilleged users login/password to use for creating new databases/users ao. Not having the root/superuser's access will keep you from manipulating other users DB/tables. There is no way that you can simulate being another user like log in as A and change to B. You have to GRANT A priveleges to B's databases, to do stuff on them. It's probably too late for me to make sense, so i leave it here for now. On Tue, July 24, 2007 04:54, Carlo Sogono wrote: Mogens Melander wrote: On Mon, July 23, 2007 10:19, Carlo Sogono wrote: Is there a way for mysql to login as an administrator and su to a normal user? What I'd like to achieve is a way to log in to our clients' accounts (we are a web-hosting company) without having to use their passwords. Having to su keeps ownerships and stuff like that in check. Thanks in advance, Carlo ??? I'm having a hard time imagine what you want to do with that ??? You, being the administrator (root), you should be able to do whatever, on any DB on your server, without being recognzied as anybody. Please elaborate. Well first of all I'm a software developer so I do not know much about administering the database, and even as a programmer I know only general SQL commands and am not familiar with mysql-specific details. We're implementing the Single Sign-On feature of phpMyAdmin. Our control panel software logging our clients automatically into PMA by populating the PMA session with their login credentials...however this would mean that we have to store and retrieve their passwords (not ideal!). If I used an administrator's password, then they would be able to see and edit via PMA all the other databases in the system. Having an su-like feature allows us to login to their account using their own username without having to keep their passwords. Hope that was clear. Other suggestions on getting around this problem is also welcome. Regards, Carlo -- 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. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, 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: speeding imports
On Tue, July 17, 2007 13:31, Baron Schwartz wrote: Mogens Melander wrote: On Tue, July 17, 2007 04:29, Baron Schwartz wrote: B. Keith Murphy wrote: The problem is that I am realizing that this dump/import is going to take hours and in some cases days. I am looking for any way to speed this up. Any suggestions? The fastest way I've found is to do SELECT INTO OUTFILE on the master, which selects into a sort of tab-delimited format by default -- don't specify any options like field terminators or whatnot. This file can then be imported directly into LOAD DATA INFILE, again without options. I think this is faster than loading files full of SQL statements, which have to be parsed and query-planned etc. That method has proven very quick in the past. I thought mysqldump had an option to dump this way, but I can't see it now. I think you are looking for the --single-transaction option :) I found the option I meant: -T, --tab=name Creates tab separated textfile for each table to given path. (creates .sql and .txt files). NOTE: This only works if mysqldump is run on the same machine as the mysqld daemon. Yup, that was what i was trying to write 8^) using this one with the other. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, 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: speeding imports
On Tue, July 17, 2007 04:29, Baron Schwartz wrote: B. Keith Murphy wrote: Hey all. I have a bit of a problem. We are upgrading from ver 4.1.x to 5.0.45. That's not the problem :). The problem is that MySQL recommends dumping and importing your data when doing this upgrade to make sure that things go as smoothly as possible. We have quite a bit of data spread over multiple servers. We have slaves off each master server and the plan is upgrade the slaves and then make them the masters. The problem is that I am realizing that this dump/import is going to take hours and in some cases days. I am looking for any way to speed this up. Any suggestions? The fastest way I've found is to do SELECT INTO OUTFILE on the master, which selects into a sort of tab-delimited format by default -- don't specify any options like field terminators or whatnot. This file can then be imported directly into LOAD DATA INFILE, again without options. I think this is faster than loading files full of SQL statements, which have to be parsed and query-planned etc. I thought mysqldump had an option to dump this way, but I can't see it now. I think you are looking for the --single-transaction option :) I'd use mysqldump to just dump the structures, routines etc without data, and then load the data separately. Beware: replication from 4.1.x to 5.0.40 will fail. The MySQL changelog didn't list it as an incompatible change, but there is some bug that got fixed around 5.0.38 (sorry, I forget what it was and can't find it now). When the slave checks the master version, it throws an error and stops saying I refuse because of this bug. Baron -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, 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: New error to me...
On Sun, July 15, 2007 03:53, Steffan A. Cline wrote: I was trying to reduce a set of queries and ran into this: insert into forums (forum_reply_id, forum_dev_id, forum_subject, forum_message) values (0, 1, (select forum_subject from forums where forum_id=3 ), I figured this one needed a reply too.) Your subselect could return more than one row, and because of that, can't be used in a direct insert/update. yields: error: You can't specify target table from 'forums' for update in from clause I read somewhere online when they referenced 4.x that said you cannot do subqueries in an update. Is this true of 5.x ? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 Lasso Partner Alliance Member --- -- 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. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, 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: How to restore 1 database from mysqldump of all databases
A quick script solution: create a dump pr. table in db. #!/bin/bash for tbl in `echo use db;show tables;|mysql -s -u user -ppassword` do mysqldump -u user ppassword db $tbl $tbl.sql done and the other way: cat tbl.sql| mysql -u user -ppassword db On Tue, July 10, 2007 15:47, Rolando Edwards wrote: When the mysqldump ran against all databases, the USE db-name command should have appeared above each section of that databases dump. Try using Perl or the head or tail Unix command to hunt down the Database you are dumping. Read all lines until the next USE db-name command. Example: If you mysqldumped databases DB1, DB2 (copyright IBM), DB3, DB4, and DB5 and you want ot restore just DB3, locate the string USE DB3 (say its line 20) and USE DB4 (say its line 25). Copy lines 20 to 25 to another SQL script. Then run that new script. - Original Message - From: waldo tumanut [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, July 10, 2007 9:34:28 AM (GMT-0500) America/New_York Subject: How to restore 1 database from mysqldump of all databases Question: How do I restore 1 database from amysqldump of all databases? Obviously a newbie. I've searched the list but couldn't find the answer. Can someone help? Waldo Tumanut Database Analyst CONFIDENTIALITY NOTICE: This electronic mail transmission (including any accompanying attachments) is intended solely for its authorized recipient(s), and may contain confidential and/or legally privileged information. If you are not an intended recipient, or responsible for delivering some or all of this transmission to an intended recipient, be aware that any review, copying, printing, distribution, use or disclosure of the contents of this message is strictly prohibited. If you have received this electronic mail message in error, please contact us immediately by electronic mail at [EMAIL PROTECTED] and destroy the original and all copies of this transmission (including any attachments). Thank you. -- 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] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, 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: off-topic unsubscribe concern
On Fri, July 6, 2007 17:55, Michael Dykman wrote: Hi everyone. I have been on this list for a pretty long time but in the last few months I have started to receive random 'confirm unsubscribe' messages..They always seem to originate from a Roadrunner IP (I have not thoroughly tested that hypothesis). I have no accounts on or near roadrunner, so I doubt I am inadvertantly kicking these off, which was my first theory. Is anyone else suffering from this or is it just me? I've seen quite few lately. -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- 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. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, 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: database structure
On Mon, July 2, 2007 21:10, Hiep Nguyen wrote: take your advice, i looked in to JOIN and i got the idea. but i noticed that in order to use JOIN, don't you need to have the same column name in both tables? i just don't see it in your example here. is there something that i'm missing? Using the form: select t1.field1 data1, t2.field1 data2, t3.fieldn data3 from table_a t1 left join table_b t2 on ( t1.id=t2.t1_ref ) left join table_n t3 on ( t2.id=t3.t2_ref ); You can join on allmost anything. ?? Can typecasts be used in this scenario ?? can u give a select example with JOIN on three tables above? -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, 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]
Interresting update problem
Hi all, I'm trying to wrap my brain around folowing problem. table main(code, field_1, field_2, field_3, , , field_51) 111, 'X', '', 'X',,, 222, '', '', 'X',,, 333, '', 'X', '' ,,, 444, '', '', '' ,,, 555, 'X','X', '' ,,, table map(id, field) 1, 'field_1' 5, 'field_2' 9, 'field_3' 86, 'field_51' The exercise is: replace 'X' with map.id in main.map.field main.code and map.id are primary keys, all other are varchar. Hmm, did that make any sense? -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, 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: Selecting rows by DATE ranges
Looks like you have datetime fields makeing 2007-01-01 00:00:01 2007-01-01. Also using BETWEEN on date-ranges might help. On Sun, June 24, 2007 02:13, Miguel Cardenas wrote: Hello list I found a little problem with an application am developing, in particular creating reports by DATE ranges. Examples: select ... where date2007-01-01; returns all records where date is greater (and equal inclusive) to 2007-01-01 select ... where date=2007-01-01; returns all records where date is greater/equal to 2007-01-01 the and = have the same effect select ... where date2007-01-01 and date2007-01-20; returns all records where date is greater/equal to 2007-01-01 and less than 2007-01-20 *** DOES NOT RETURN RECORDS FROM DAY *20* select ... where date2007-01-01 and date=2007-01-20; returns all records where date is greater/equal to 2007-01-01 and less than 2007-01-20 although I'm using = *** DOES NOT RETURN RECORDS FROM DAY *20* My doubts are: 1. how can I retrieve rows with a date... NOT INCLUDING the day of the specified date, I mean apply a strict GREATHER THAN 2. how can I retrieve rows with date=... INCLUDING the day of the specified date. currently I have to do a date=date1 and date=date2+1day I need to retrieve rows in this way dateX date=X dateX date=X dateX and dateY date=X and dateY date=X and date=Y dateX and date=Y and so... didn't find a function to specify ranges of dates and the LESS THAN/EQUAL operator does not include the last day, so my reports with = are done by adding one day but don't like to use it this way since it could be confusing and generate errors on reports. Thanks for any comment, Miguel -- 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. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, 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: Storing a linked list
How about:
$id1 = 1;
$id2 = 0;
while ($id1) /** or ($id2 != 8) **/
{
$sql = select * from table where id1=$id1;
$row=query($sql);
$id1=$row-id1;
$id2=$row-id2;
do_stuff();
$id1 = $id2
}
On Tue, June 19, 2007 10:58, Matt Juszczak wrote:
Hi all,
I've got a table such as the following:
id1 char
id2 char
sample data looks like this:
id1 id2
1 3
2 4
3 5
5 6
6 8
And of course another table has something like:
idinfo1 info2 info3
1 blahblahblah
2 blahblahblah
I'd like to store paths to specific destinations...
In other words, the path from 1 to 8 is:
1,3,5,6,8
I was thinking of creating a table called relationships
start end path
1 8 {3,5,6}
This would allow me to easily display the path if I know the start and
end, but
what it doesn't allow me to do is reuse the data.
IE: say that I calculate the path from 1 to 8 as 1,3,5,6,8, and then I
want to
know the path from 3 to 6. even though this is already calculated, I have
to
recalculate it as another row... hence
start end path
1 8 {3,5,6}
3 6 {5}
I considered making another table, called hops, such as:
start end relationshipID
1 8 1
table hops:
relationshipIDstart end
1 1 3
1 3 5
1 5 6
1 6 8
Then I could almost reuse those hops somehow but not sure.
Can anyone recommend a good way to store this data?
Thanks!
-Matt
--
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.
--
Later
Mogens Melander
+45 40 85 71 38
+66 870 133 224
--
This message has been scanned for viruses and
dangerous content by MailScanner, 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: selecting everyting from 2 non-identical tables.
On Tue, June 19, 2007 23:42, Olexandr Melnyk wrote: 2007/6/19, [EMAIL PROTECTED] [EMAIL PROTECTED]: My frist post was not worded correctly. I cannot join two tables as all the rows are unique. What's wrong with my solution? 2007/6/19, Olexandr Melnyk [EMAIL PROTECTED]: select id, name, age, null as height from table1 union select id, name, null as age, height from table2 I believe that was the right answer to the question. ( there might be other answers ;^) -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, 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: Change in behaviour in version 5.0.41
On Fri, June 15, 2007 16:29, Ben Clewett wrote:
Dear MySql,
I have noticed a change in behaviour in MySql 5.0.41 from 5.0.26 with
date comparisons.
In 5.0.26:'2007-06-15' = '2007-06-15 00:00:00' is True.
In 5.0.41:'2007-06-15' = '2007-06-15 00:00:00' is False.
In 5.1.6-alpha: '2007-06-15' = '2007-06-15 00:00:00' is True.
On my servers:
5.0.21-log '2007-06-15' = '2007-06-15 00:00:00' is False.
5.1.17-beta-log '2007-06-15' = '2007-06-15 00:00:00' is False.
But on 5.0.21
SELECT COUNT(*) FROM t WHERE d = '2007-06-15 00:00:00'; = 1
SELECT COUNT(*) FROM t WHERE d = '2007-06-15'; = 1
and on 5.1.17
SELECT COUNT(*) FROM t WHERE d = '2007-06-15 00:00:00'; = 0
SELECT COUNT(*) FROM t WHERE d = '2007-06-15'; = 1
How does that make sense ???
This has caused us a few problems. Is this the way things should be,
because this change does not seem right?
I am also very worried that this behaviour revert when we role out 5.1?
Does any member know whether this is a bug, or just an anoying feature?
Regards,
Ben
To Replicate:
CREATE TABLE t (d DATE);
INSERT INTO t VALUES ('2007-06-15');
SELECT COUNT(*) FROM t WHERE d = '2007-06-15 00:00:00';
+--+
| COUNT(*) |
+--+
|0 |
+--+
SELECT COUNT(*) FROM t WHERE d = '2007-06-15';
+--+
| COUNT(*) |
+--+
|1 |
+--+
--
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.
--
Later
Mogens Melander
+45 40 85 71 38
+66 870 133 224
--
This message has been scanned for viruses and
dangerous content by MailScanner, 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: Request: Schema suggestion for items which change over time...
Hi, On Thu, June 14, 2007 18:16, Jake Peavy wrote: Hi all, Can someone suggest a good method or normalized schema for storing product information (id, description, price) which changes over time so that as a product is gradually discounted, an order will reflect the cost of that particular product at that particular time? One method could be to store product(id, description, price) and orderitem(id,orderid,productid,quantity,price) This way your orders will reflect the item-price at the time the order was created. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, 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: When will partitioning be included in the MySQl stable release ?
I've been running 5.1 for a while, seing no issues at all, on a production system. Respect! On Fri, June 8, 2007 19:51, Joerg Bruehe wrote: Hi all ! Michael Dykman wrote: It is my understanding that there is no intention of adding partioning into the 5.0 release. Right. Any GA release will not get features added, as this would change functional behavior and also introduce the risk of losing stability. It will be avilable when 5.1 hits relase status.. it is currently in beta and no official date has been set for that release. There are rumours of course but the MySQL team is keeping that date pretty close. This sounds like an assumption there were a fixed date already set, but kept secret. Any such assumption is wrong: 5.1 is currently in beta status, and we hope many users are using it for development and/or testing. In addition to our internal checks, we rely on these users giving feedback about any bugs they discovered, and about their experiences - the combined requirements of our users will always be more complete than any test suites. Any such feedback is highly appreciated ! When 5.1 has reached the required stability and completeness (this includes the fixing of any reported critical bugs), it will be designated rc (= release candidate, formerly called gamma). When in turn this one has not shown any severe problems, 5.1 will be called production level (aka GA). For these status changes there are no dates, and any individual guesses are based on assumptions which cannot be proven (like known rates of bug inflow and fixing). -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, 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: Stable mysql 4.xx
I'm haing a hard time figuring out why you want an old 4.0 server, when newer better versions are available. MySQL 4.0.27 can be downloaded from Softpedia: http://linux.softpedia.com/progDownload/MySQL-Download-3074.html On Mon, June 4, 2007 00:44, Baron Schwartz wrote: Only back to 4.1.x -- before that, they say it's no longer supported. I couldn't find a way to get it, anyway. Mogens Melander wrote: If i'm not mistaken, the mysql site, also has older versions. On Sun, June 3, 2007 18:31, Baron Schwartz wrote: Hi, Subasta.pl - Darmowe Aukcje Internetowe wrote: Hello, Where I can find the best stable version 4.xx? Some of the mirrors still have older versions, such as http://mysql.mirrors.pair.com/ Baron -- 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. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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: CSV import
Import your CSV-data into a temporary table, using mysqlimport, and throw a bit of SQL at it might do the trick, but then again, not knowing the layout of your table, nor the data you want to import, i'm only guessing. On Mon, May 28, 2007 16:57, Sharique uddin Ahmed Farooqui wrote: Both PhpMyAdmin and SQLYOG doesn't support customised mapping. My cvs data structure is diff from mysql table. I just want to import values for one field only. Previously I was using Mysql Front but it crashes. Also this s/w very old and discontinued. Why mysql doesn't implement it in MysqlAdmin? -- Sharique uddin Ahmed Farooqui (C++/C# Developer, IT Consultant) A revolution is about to begin. A world is about to change. And you and me are the initiator. On 5/28/07, Geoffroy Cogniaux [EMAIL PROTECTED] wrote: Hi, It can be done easily with PhpMyAdmin, but it is not in .net 2007/5/28, Sharique uddin Ahmed Farooqui [EMAIL PROTECTED]: I want to import data from a CSV file in a table. MySql admin doesn't support import from CSV files. Format of data is different from structure of table. Is there any app/snippet written for this task in .net , which I can modify according to my need. -- Sharique uddin Ahmed Farooqui (C++/C# Developer, IT Consultant) A revolution is about to begin. A world is about to change. And you and me are the initiator. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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: Padding result
How about: SELECT LPAD(id,5,'1'),user from table;
LPAD(str,len,padstr)
Returns the string str, left-padded with the string padstr to a length of
len characters. If str is longer than len, the return value is shortened
to len characters.
mysql SELECT LPAD('hi',4,'??');
- '??hi'
mysql SELECT LPAD('hi',1,'??');
- 'h'
On Fri, June 1, 2007 02:06, Ashley M. Kirchner wrote:
Is there a way to automatically pad a query result? For example:
select id, user from table
+-+--+
| id | user |
+-+--+
| 3 | Tinker Bell |
| 11 | Peter Pan|
| 7 | Dumbo|
| 121 | Mickey Mouse |
+-+--+
What I really want is:
+---+--+
|id | user |
+---+--+
| 10003 | Tinker Bell |
| 10011 | Peter Pan|
| 10007 | Dumbo|
| 10121 | Mickey Mouse |
+---+--+
--
W | It's not a bug - it's an undocumented feature.
+
Ashley M. Kirchner mailto:[EMAIL PROTECTED] . 303.442.6410 x130
IT Director / SysAdmin / Websmith . 800.441.3873 x130
Photo Craft Imaging . 3550 Arapahoe Ave. #6
http://www.pcraft.com . . .. Boulder, CO 80303, U.S.A.
--
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.
--
Later
Mogens Melander
+45 40 85 71 38
+66 870 133 224
--
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: string to timestamp conversion
Like:
str_to_date('Thu May 17 09:15:47 2007','%a %b %e %T %Y')
On Mon, May 21, 2007 21:10, [EMAIL PROTECTED] wrote:
Have you considered using the string to time function?
Sent via BlackBerry from T-Mobile
-Original Message-
From: Bryan Cantwell [EMAIL PROTECTED]
Date: Mon, 21 May 2007 12:08:11
To:MySQL General mysql@lists.mysql.com
Subject: string to timestamp conversion
I have a table with a varchar column that contains a timestamp like
this: 'Thu May 17 09:15:47 2007'
I need to grab this and include it in an insert sql that puts that value
in a table as a timestamp...
--
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]
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
--
Later
Mogens Melander
+45 40 85 71 38
+66 870 133 224
--
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: Problem with GRANT ... 'user'@'%'
On my servers i'm using the 'user'@'localhost' for PHP apps. running on the local web-server. Those users allocated for web-apps can only connect to their specific DB from localhost. On Tue, May 22, 2007 03:19, Miguel Cardenas wrote: Localhost is indeed a special value that isn't include in '%'. It's a feature not a bug ;) Regards, Bingo! That was the point! If i connect to the server ip or server name it works perfectly, but if I try to connect to localhost it fails unless I add a new user specific to localhost :D Thanks for your comments -- 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. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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: speedup mysql restore
--disable-keys, -K For each table, surround the INSERT statements with /*!4 ALTER TABLE tbl_name DISABLE KEYS */; and /*!4 ALTER TABLE tbl_name ENABLE KEYS */; statements. This makes loading the dump file faster because the indexes are created after all rows are inserted. This option is effective only for non-unique indexes of MyISAM tables. On Sun, May 20, 2007 16:28, Vitaliy Okulov wrote: Здравствуйте, mysql. How i can speedup restore of mysql DB from file created by mysqldump? -- С уважением, Vitaliy mailto:[EMAIL PROTECTED] -- 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. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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: Re[2]: speedup mysql restore
On Sun, May 20, 2007 17:34, Vitaliy Okulov wrote: Здравствуйте, Mogens. --disable-keys, -K On Sun, May 20, 2007 16:28, Vitaliy Okulov wrote: Здравствуйте, mysql. How i can speedup restore of mysql DB from file created by mysqldump? Ok, but for innodb? Well, you might be able to gain some speed, fiddeling with buffer sizes. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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: Problem with GRANT ... 'user'@'%'
You are still missing the GRANT for 'myuser'@'localhost' On Mon, May 21, 2007 03:06, Miguel Cardenas wrote: Remember to quote the user and host carefully. So, The quoting is okay, I tested again If you have doubts about what you actually granted, do this: SHOW GRANTS FOR 'user' I've sent this command: grant all on mydatabase.* to 'myuser'@'%' identified by 'mypass'; Then tested the show grants: show grants for 'myuser'; --- Grants for [EMAIL PROTECTED] : GRANT USAGE ON *.* TO 'myuser'@'%' IDENTIFIED BY PASSWORD '...' GRANT ALL PRIVILEGES ON `mydatabase`.* TO 'myuser'@'%' so it appears that user is added correctly, but when I try to connect get this error again: ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES) Oh, one more place to look is at the server configuration in /etc/my.cnf or equivalent, to be sure networking is configured right. You want to check the bind-address and skip-networking settings. You don't want skip-networking, and you want bind-address set to the machine's IP address. Now that I think of it, this is more likely to be the problem for you. - skip-networking is disabled - bind-address option is not present in /etc/my.cnf should I try to add a: bind-address = x.y.z.a to the configuration file :-? Thanks for any comment ** P.S. I've added the same user but using 'myuser'@'localhost' whithout deleting the first one created, and the new show grants outputs the *same* two lines of information Grants for [EMAIL PROTECTED] : GRANT USAGE ON *.* TO 'myuser'@'%' IDENTIFIED BY PASSWORD '...' GRANT ALL PRIVILEGES ON `mydatabase`.* TO 'myuser'@'%' but this time am able to connect. What can be wrong? or do I need to add both '%' and 'localhost' hosts? ** -- 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. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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: Data security - help required
On Tue, May 15, 2007 11:12, Chris wrote: Ratheesh K J wrote: Hello all, I have a requirement of maintaining some secret information in the database. And this information should not be visible/accessible to any other person but the owner of the data. Whilst I know that encryption/decryption is the solution for this, are there any other level of security that I can provide to this? Which is the best security technique used in MySQL to store seceret information. PS: Even the database admin should not be able to access anybody else's information Then you're stuffed - *someone* has to be able to see everything so you can do a mysqldump. *Someone* has to be able to see everything so you can grant permissions to the other users too :) Well, doing encryption in user-interface (PHP mcrypt) using a password not stored in app. or db would hide information pretty good. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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: Scheduled backups
On Mon, May 14, 2007 17:56, John Meyer wrote: Mike Blezien wrote: Hello, - Original Message - From: John Meyer [EMAIL PROTECTED] To: MySQL General mysql@lists.mysql.com Sent: Monday, May 14, 2007 10:26 AM Subject: Re: Scheduled backups J Trahair wrote: Hi Everyone I have set up a scheduled backup using MySQL Administrator. Stored connection, database, dates and time, even the Windows user password (in fact, blank). It doesn't start at the correct time, or indeed any time. Have I missed something? this is a nice MySQL B/U bash script we've been using for sometime and works quite nicely, on a LINUX system. MySQL Backup Script VER. 2.5 - http://sourceforge.net/projects/automysqlbackup/ Actually, he told me he was on Windows XP. One thing I have to wonder about, though; in terms of security, okay, maybe you don't want your password stored in a plain text file, but is there anyway around that other than setting the password as blank? How about creating a mysql account with only read / connect from localhost permissions on? Making a no-password account, or storing password in file, can't be that far apart, security wise :) -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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: finding next and prev record in mysql
On Fri, May 11, 2007 07:15, Richard Kurth wrote: How would I find the next id and the prev id in sql statement like the one below. The id number is not going to be in order so I can't do a or limit 1 on the search SELECT id FROM contacts WHERE category = '5' AND subcategory = '1' AND members_id= '8' ORDER BY lastname The PHP variant could look like (prev id): SELECT id FROM contacts WHERE category = '5' AND subcategory = '1' AND members_id= '8' AND lastname $_POST['lastname'] ORDER BY lastname LIMIT 1; -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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: problem with initdb script in lxr installation - newbie needs help
RELEASE is a reserved word, and if used as column-name, it must be quoted (back-tick) `release` char(255) . On Tue, May 8, 2007 12:09, Shahbaz Khan wrote: This is the exact error that mySQL reported: ERROR 1064 (42000): You have an error in you sql syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'release char(255) bunary not null, primary key (fileid,relea' at line 3 MySQL server version is 5.0.22. The script requires 4.x. If Fedora core 6 allows without dependency issues I have no problem with 4.x. Anyways I think it would be better if the script can be corrected because it will be useful for the majority newbies like me. On 5/7/07, Michael Dykman [EMAIL PROTECTED] wrote: perhaps you might share with us what error you are getting and what version of MyQSL you are using? The more context you can suply, the better. On 5/7/07, Shahbaz Khan [EMAIL PROTECTED] wrote: This portion of the script gives error. reate table lxr_releases (fileid int not null references lxr_files, release char(255) binary not null, primary key (fileid,release) The script is present as attachment if needed. I suspect this script is for mysql 4.x version and if so what needs to be done? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- 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. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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: how to create new username n password in mysql
GRANT CREATE,INSERT,DELETE,UPDATE,SELECT PRIVILEGES ON pauldb.*
TO [EMAIL PROTECTED] IDENTIFIED BY 'mysecretpassword';
On Tue, May 8, 2007 13:57, sunisundar wrote:
how to create new username n password in mysql using commands.
followed these steps::
mysql create database pauldb;
Query OK, 1 row affected (0.00 sec)
#
# Now we create the user paul and give him full
# permissions on the new database
mysql grant CREATE,INSERT,DELETE,UPDATE,SELECT on pauldb.* to
[EMAIL PROTECTED];
Query OK, 0 rows affected (0.00 sec)
#
# Next we set a password for this new user
#
mysql set password for paul = password('mysecretpassword');
Query OK, 0 rows affected (0.00 sec)
#
# Cleanup and ext
mysql flush privileges;
mysql exit;
when next time i login using this pwd not working.
Give me proper commands.
--
View this message in context:
http://www.nabble.com/how-to-create-new-username-n-password-in-mysql-tf3709178.html#a10374454
Sent from the MySQL - General mailing list archive at Nabble.com.
--
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.
--
Later
Mogens Melander
+45 40 85 71 38
+66 870 133 224
--
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: how to create new username n password in mysql
On Tue, May 8, 2007 14:58, sunisundar wrote:
no getting an error now like check the manual that corresponds to your
mysql version.
mine is mysql 5 version
And what does that error say?
Try quoteing [EMAIL PROTECTED] ala. 'poul'@'localhost'
From the manual:
To create the accounts with GRANT, use the following statements:
shell mysql --user=root mysql
mysql GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
- ON bankaccount.*
- TO 'custom'@'localhost'
- IDENTIFIED BY 'obscure';
mysql GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
- ON expenses.*
- TO 'custom'@'whitehouse.gov'
- IDENTIFIED BY 'obscure';
mysql GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
- ON customer.*
- TO 'custom'@'server.domain'
- IDENTIFIED BY 'obscure';
The three accounts can be used as follows:
The first account can access the bankaccount database, but only from the
local host.
The second account can access the expenses database, but only from the
host whitehouse.gov.
The third account can access the customer database, but only from the host
server.domain.
Mogens Melander wrote:
GRANT CREATE,INSERT,DELETE,UPDATE,SELECT PRIVILEGES ON pauldb.*
TO [EMAIL PROTECTED] IDENTIFIED BY 'mysecretpassword';
On Tue, May 8, 2007 13:57, sunisundar wrote:
how to create new username n password in mysql using commands.
followed these steps::
mysql create database pauldb;
Query OK, 1 row affected (0.00 sec)
#
# Now we create the user paul and give him full
# permissions on the new database
mysql grant CREATE,INSERT,DELETE,UPDATE,SELECT on pauldb.* to
[EMAIL PROTECTED];
Query OK, 0 rows affected (0.00 sec)
#
# Next we set a password for this new user
#
mysql set password for paul = password('mysecretpassword');
Query OK, 0 rows affected (0.00 sec)
#
# Cleanup and ext
mysql flush privileges;
mysql exit;
when next time i login using this pwd not working.
Give me proper commands.
--
View this message in context:
http://www.nabble.com/how-to-create-new-username-n-password-in-mysql-tf3709178.html#a10374454
Sent from the MySQL - General mailing list archive at Nabble.com.
--
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.
--
Later
Mogens Melander
+45 40 85 71 38
+66 870 133 224
--
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]
--
View this message in context:
http://www.nabble.com/how-to-create-new-username-n-password-in-mysql-tf3709178.html#a10375368
Sent from the MySQL - General mailing list archive at Nabble.com.
--
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.
--
Later
Mogens Melander
+45 40 85 71 38
+66 870 133 224
--
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: Installing 2nd instance on windows.
It should be possible to do a manual install from the non-installer download. Moreover, it should also be possible to run 2 instances on 2 different ip-adresses on one computer. It's been a while sincei ran MySQL on Windows, so my memory is not clear on this, but scan the docs, and your questions will be answered. On Tue, May 8, 2007 20:35, C K wrote: Dear friends, thank you for your response. but the problem is that when I try to install MySQL 5.0 from windows .msi installer on windows XP with MySQL 5.0 already installed, the installer does not shows any option regarding new installation. I can just rapair/remove the installation. Why? As I know we can install multiple instances of MySQL running for different ports, how to make it available on Windows? I need to run two different mysql servers on same machine at different ports(3306, 3307 etc) is it possible and how? Thanks again, CPK Keep your Environment clean and green. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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: Printing
On Wed, May 9, 2007 04:06, Stephen Cook wrote: I think that offering some sort of feedback other than result sets would be nice for debugging. And it makes perfect sence to get this debug information in postscript format :D Peter Brawley wrote: I hope this isn't a silly question, or something covered in a FAQ. . . but is there any reason to not have at least some primitive print formatting commands in MySQL? Or am I missing something blindingly obvious? I think the idea is that RDBMS ought to stick to what is necessary for RDBMS management and leave the rest to application programs. PB -- 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. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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: secure port 3306
On Mon, May 7, 2007 17:40, Steven Buehler wrote: The thing is...I need to securely do this. Here would be the setup Desktop - Secure connection to Server 1 - Secure connection to Server 2. So I am assuming that what I need to do is to have the Desktop SSH into Server 1 which will have the iptables setup to tunnel to Server 2 and then use a tunnel from Secure CRT (or putty) to tunnel all the way to Server 2 through Server 1? Server one can only be accessed with SSH from Server 1. The only reason for the need for ssh-tunnel would be to eliminate the risk of somebody sniffing between desktop - server-1. This iptables rule allow only access from one ip-address (desktop). On linux, one could do a port forward: EXTIF=eth0 # Or whatever the interface that faces internet is called. iptables -A FORWARD -i $EXTIF -p tcp -s client-ip --dport 3306 -j ACCEPT iptables -A PREROUTING -t nat -p tcp -s client-ip \ -d linux-fw-ip --dport 3306 -j DNAT --to internal-ip:3306 On Wed, May 2, 2007 17:03, Steven Buehler wrote: I have a client that needs to be able to remotely connect to port 3306 securely. I have tried to suggest an SSH Tunnel, but they do not want their clients to have SSH access. Another problem is that even if we do tunnel, it needs to go thru one server that is connected to the Internet and into the MySQL server which is NOT accessible from the Internet. Any suggestions? Thanks Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=1 -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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] -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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: secure port 3306
Well, i that case you can forward the tunnel (port 22) directly from server-1 to server-2, so when you tunnel from client to server-1, you get forwarded to server-2. On Mon, May 7, 2007 21:41, Steven Buehler wrote: But I also need to make sure that nobody is sniffing between Server-1 and Server-2. Steve -Original Message- From: Mogens Melander [mailto:[EMAIL PROTECTED] Sent: Monday, May 07, 2007 1:35 PM To: Steven Buehler Cc: mysql@lists.mysql.com Subject: RE: secure port 3306 On Mon, May 7, 2007 17:40, Steven Buehler wrote: The thing is...I need to securely do this. Here would be the setup Desktop - Secure connection to Server 1 - Secure connection to Server 2. So I am assuming that what I need to do is to have the Desktop SSH into Server 1 which will have the iptables setup to tunnel to Server 2 and then use a tunnel from Secure CRT (or putty) to tunnel all the way to Server 2 through Server 1? Server one can only be accessed with SSH from Server 1. The only reason for the need for ssh-tunnel would be to eliminate the risk of somebody sniffing between desktop - server-1. This iptables rule allow only access from one ip-address (desktop). On linux, one could do a port forward: EXTIF=eth0 # Or whatever the interface that faces internet is called. iptables -A FORWARD -i $EXTIF -p tcp -s client-ip --dport 3306 -j ACCEPT iptables -A PREROUTING -t nat -p tcp -s client-ip \ -d linux-fw-ip --dport 3306 -j DNAT --to internal-ip:3306 On Wed, May 2, 2007 17:03, Steven Buehler wrote: I have a client that needs to be able to remotely connect to port 3306 securely. I have tried to suggest an SSH Tunnel, but they do not want their clients to have SSH access. Another problem is that even if we do tunnel, it needs to go thru one server that is connected to the Internet and into the MySQL server which is NOT accessible from the Internet. Any suggestions? Thanks Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=1 -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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] -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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] -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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: How do I find products when a user types freeform strings like 'Sony 20 TV' or '20 Sony TV'?
On Fri, May 4, 2007 10:21, Daevid Vincent wrote:
I'm having trouble figuring out the logic/query I want.
I know that all those ORs are not right.
I'm doing this in PHP and mySQL (of course),
so if it can't be done with a single query, I can split it up.
Here's the challenge, given a text field search box, someone enters:
Sony 20 TV
How do I search for that, not knowing which fields are which?
For example, they could have also entered:
20 Sony TV
How about:
select soundex('Sony 20 TV' ),soundex('20 Sony TV');
'S531', 'S531'
--
Later
Mogens Melander
+45 40 85 71 38
+66 870 133 224
--
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: batch mode
On Tue, May 1, 2007 22:15, Brown, Charles wrote: Because I am running on batch mode therefore I'm trying to direct my session output to a file -- meaning stdout. But I'm having a problem. For instance this input: use test_db gave me no output but this input show tables gave me an output. What is missing, what's the trick. Help me Y'all. You could try something like: mysql -u xxx -pyyy -e 'select * from dbname;' mysql -u xxx -pyyy -e 'select * from dbname.tblname;' -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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: batch mode
On Wed, May 2, 2007 08:55, Mogens Melander wrote: On Tue, May 1, 2007 22:15, Brown, Charles wrote: Because I am running on batch mode therefore I'm trying to direct my session output to a file -- meaning stdout. But I'm having a problem. For instance this input: use test_db gave me no output but this input show tables gave me an output. What is missing, what's the trick. Help me Y'all. You could try something like: mysql -u xxx -pyyy -e 'select * from dbname;' Argh, that should have been: mysql -s -u xxx -pyyy -e 'show tables from dbname;' mysql -u xxx -pyyy -e 'select * from dbname.tblname;' And: mysql -s -u xxx -pyyy -e 'select * from dbname.tblname;' -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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] -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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: secure port 3306
On linux, one could do a port forward: EXTIF=eth0 # Or whatever the interface that faces internet is called. iptables -A FORWARD -i $EXTIF -p tcp -s client-ip --dport 3306 -j ACCEPT iptables -A PREROUTING -t nat -p tcp -s client-ip \ -d linux-fw-ip --dport 3306 -j DNAT --to internal-ip:3306 On Wed, May 2, 2007 17:03, Steven Buehler wrote: I have a client that needs to be able to remotely connect to port 3306 securely. I have tried to suggest an SSH Tunnel, but they do not want their clients to have SSH access. Another problem is that even if we do tunnel, it needs to go thru one server that is connected to the Internet and into the MySQL server which is NOT accessible from the Internet. Any suggestions? Thanks Steve -- 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. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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: show tables
On Tue, May 1, 2007 09:57, Stijn Verholen wrote: Octavian Rasnita wrote: mysql -u user -p database -e 'show tables;' file.txt That one works for me, with a litle change. -s (kill's columnnames a.o.). [EMAIL PROTECTED]:~# mysql -s -u rppt test -e 'show tables;' file.txt [EMAIL PROTECTED]:~# cat file.txt abc store t tablea trans Hmmm, there is no such user -u rppt but it works anyway ??? Maybe because it's the test db. But the result was the help file displayed by MySQL when a command is not correct. The SQL queries I gave are correct, because they work when I give them at the mysql prompt. What error message do you get ? -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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: MySQL Workbench
I've had a lot of fun using Toad on Oracle. Quest got a freeware for MySQL, that i never got around to try, but have a look at: http://www.quest.com/toad-for-mysql/ On Tue, May 1, 2007 21:36, Afan Pasalic wrote: Hi, I'm looking for database modeling tool form MySQL. Anybody used the MySQL Workbench? I know the Workbench is in Alpha production, though some hints? How about DB Designer? Thanks for any respond. -afan -- 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. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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: how to query this sql?
On Sun, April 29, 2007 05:28, Jeff Pang wrote: Hello list, I want to get the counter for db-items by each day,so I wrote this sql: select count(*) as dd from items group by updatetime; But sorry updatetime is datetime type,not date type.Then I can't get the correct result. How can I do this?Thanks. That would be select date(updatetime) ut, count(*) dd from items group by ut; -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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: Permissions and Into Outfile
Ok, on my linux (slackware 10) /var/www is owner by root.root with permissions rwxr-xr-x (755). Say you want mysql to put your data in a subdirectory under /var/www named data. As root, do: mkdir /var/www/data chgrp mysql /var/www/data chmod 775 /var/www/data Now mysql has write permissions to /var/www/data If you want a f.ex. PHP script to manipulate/change/delete files generated by mysql, your webserver need r/w permissions as well. I'm using apache2, so the daemon user need access. chown daemon.mysql /var/www/data or chown mysql.daemon /var/www/data That's it. On Sat, April 28, 2007 04:04, John Kebbel wrote: I experimented with a local /var/www folder. I assumed setting 2, 6, or 7 for the Other value would give mysql write privileges, but mysql would not settle for anything less than a 7 in that last slot. What was really curious to me was that the User and Group settings were inconsequential. I even set the folder for 007 and mysql could write to the folder. But as soon as I tried making that Other setting anything less than 7, mysql generated an error message. I've been using Linux for years, but more as a hobby than profession (though I do maintain an internal Mac OS X web server at my school). I am unfamiliar with the syntax you were using with the username in front of :mysql. I didn't know of any way other than chmod nnn to change permissions for UGO (user,group,other). Could you post one or two sample command line statements that illustrate this syntax in action. I did a brief web search, but it only turned up the stuff I was familiar with. Thanks to everyone who helped with this issue. -- 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. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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: Permissions and Into Outfile
Another variant would be, to create a directory under your www area, and give it your-user:mysql 775 permissions, or maybe web-server-user:mysql 775 permissions, depending on what you want to do with the csv file. On Fri, April 27, 2007 16:41, Kebbel, John wrote: cat /etc/passwd on my Macintosh 10.3 at work (I'm using Linux at home) shows me this for mysql ... mysql:*:74:74:MySQL Server:/var/empty:/usr/bin/false The home directory for mysql is /var/empty, which does exist. Should I write ~into file~ output to this folder or should I change the mysql home directory to some other folder? -- From:Gerald L. Clark Sent:Friday, April 27, 2007 10:09 AM To: Kebbel, John Cc: mysql@lists.mysql.com Subject: Re: Permissions and Into Outfile Kebbel, John wrote: I was trying to write the output of a select statement to a tab-delimited text file. I could not write the file to a folder inside /var/www or to my home file because of permission problems. After a moment's reflection, I realized /tmp had stuff written to it all the time, so its permissions must be wide open. I tried writing the file to /tmp and it worked fine. Since you cannot write to an existing file, you cannot create the file in advance and set its permissions. What is the trick to getting the file to write successfully anywhere you want it to be written? This is the query I was using... mysql select sched_students.id, firstName, lastName, grade, race, gender, dob, school, phone, program, hsMath, hsEnglish, hsScience, major, 9YBand, 9YChorus, 9YGifted, 9YTV, 9YROTC, 9YSpanishI, 9YSpanish2, 9YFrenchI, 9SAerobics, 9SArt, 9SBasketball, 9SDrama, 9SDriverEd1, 9SDriverEd2, 9SRecreation, 9STeamSports1, 9STeamSports2, 9SWeights into outfile '/tmp/srhs9.txt' from sched_students, SCHED_COURSES where (sched_students.id = SCHED_COURSES.id) and (grade = 8) order by lastname, firstname; The user 'mysql' must have write permission in the target directory. -- Gerald L. Clark Supplier Systems Corporation -- 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. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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: Interesting SQL Query - Total and Grouped Counts together?
On Thu, April 26, 2007 18:38, Baron Schwartz wrote: Hi, Imran Chaudhry wrote: I'm wondering if any of you can assist with an interesing SQL query. I have a single table within a database, the relevant fields of Try IF or CASE expressions: SELECT foo, count(*), sum(case when foo = 'bar' then 1 else 0 end) FROM tbl GROUP BY foo Baron Cool, it's actually working :) I've been looking for something like that before. SELECT * FROM tablea t order by domain,mime; domainmime -- 'google.com', 'image/gif' 'google.com', 'image/jpeg' 'google.com', 'image/png' 'google.com', 'text/html' 'google.com', 'text/html' 'google.com', 'text/html' 'teddybears.com', 'image/png' 'teddybears.com', 'text/html' SELECT domain, count(*) `all`, sum(case when mime = 'text/html' then 1 else 0 end) html, sum(case when mime like 'image/%' then 1 else 0 end) image FROM tablea GROUP BY domain; domain all html image - 'google.com', 6, 3, 3 'teddybears.com', 2, 1, 1 -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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: Update question
On Wed, April 25, 2007 23:10, Jørn Dahl-Stamnes wrote: Please, I nedd help!! I have two tabels: table1: id value table2: id value Both tables has a lot of records with identical IDs. I need to update the table1.value with the table2.value where the id are identical. update table1 t1, table2 t2 set t1.value=t2.value where t1.id=t2.id; But I cannot find any UPDATE query that can do this in a single operation. Anyone that can give me a suggestion? I'm using MySQL 4.1.8 -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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: Trying to open a big sql script
On Thu, April 19, 2007 12:48, Duncan Hill wrote: On Thursday 19 April 2007 11:43:34 molemenacer wrote: Hi all, I have backed up a database using mysqldump and have a .sql script that is over 2GB in size. I am trying to open this file to view it and make some changes. I have not been able to find a program that can open this file. Does anyone have any suggestions as to a program that can do this? You need an editor that will only load the current view of the file into memory. I'm not sure that such a beast exists, other than stream editors such as sed or perl. Can the changes you need to make be done with stream editing (simple changes like changing a word or two are very easy with stream editor)? Also, replace from your mysql-installation could be used,if you only need to replace a-string with b-string :) -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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: Trying to open a big sql script
Something like this should do the trick: ~#replace mthosp newname org-file.sql new-file.sql -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 On Thu, April 19, 2007 16:53, molemenacer wrote: I am trying to change all the names of the database from mthosp to another name, is this possible? Dan Buettner-2 wrote: That's a much larger file than most any text editor would work with, in my experience. I'd give BBEdit on the Mac a try if nothing else, but my expectations would not be too high. For examining and altering a file that large I'd try grep, awk, sed, perl, etc. Barring that, one thing you might do is use perl or another scripting language (or perhaps some utility software) to read the file in 100 MB or so chunks and write out to a series of smaller files. Edit the smaller files, then use shell command to cat them all back into one bigger file. HTH, Dan On 4/19/07, molemenacer [EMAIL PROTECTED] wrote: Hi all, I have backed up a database using mysqldump and have a .sql script that is over 2GB in size. I am trying to open this file to view it and make some changes. I have not been able to find a program that can open this file. Does anyone have any suggestions as to a program that can do this? Thanks in advance -- View this message in context: http://www.nabble.com/Trying-to-open-a-big-sql-script-tf3606302.html#a10075570 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/Trying-to-open-a-big-sql-script-tf3606302.html#a10078655 Sent from the MySQL - General mailing list archive at Nabble.com. -- 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: how to tell if something hasn't happened yet
Arg, come on, really. where t.created = date(now())); -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 On Mon, April 16, 2007 15:18, Jay Blanchard wrote: [snip] select s.* from store s where s.id not in (select t.storeid from trans t where t.created=date(now())); [/snip] This is close, but it does not exclude previous days. I only want to see those that have not logged in today. -- 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: how to tell if something hasn't happened yet
Argh, that was not the one i wanted to send :) This is the one: select s.* from store s where s.id not in (select t.storeid from trans t where t.created date(now()) - interval 1 day); -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 On Tue, April 17, 2007 09:20, Mogens Melander wrote: Arg, come on, really. where t.created = date(now())); -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 On Mon, April 16, 2007 15:18, Jay Blanchard wrote: [snip] select s.* from store s where s.id not in (select t.storeid from trans t where t.created=date(now())); [/snip] This is close, but it does not exclude previous days. I only want to see those that have not logged in today. -- 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] -- 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: Problem with mysqldump and local-infile
It looks to me that local-infile is a command-line parameter to mysql client mysql --local-infile -u user dbname I've not been able to find this option elsewhere. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 On Mon, April 16, 2007 12:14, Mark van Herpen wrote: Hi, I want to backup my databases with mysqldump, but mysqldump won't run because I use the 'local-infile=1' option in the my.cnf file: [client] port= 3306 socket = /tmp/mysql.sock local-infile= 1 This is because I want php and other clients to use local-infile. This works, by when I start mysqldump I got this error: ~ # mysqldump mysqldump: unknown variable 'local-infile=1' So, what is wrong? As far as I know local-infile is a valid option to put in the my.cnf. Is there a way to unset the local-infile option and start then start mysqldump or something? Or any other solution? Grtz, Mark van Herpen -- 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?
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: can't import
Hmmm, i got a somewhat different result :^) (BTW. I'm on Slackware 10.0. What are you on?) $ cat /tmp/abc.txt A1, B1, C1 A2, B2, C2 A3, B3, C3 $mysql test Server version: 5.0.24a-log mysql SHOW GRANTS FOR ''@localhost; +--+ | Grants for @localhost| +--+ | GRANT USAGE ON *.* TO ''@'localhost' | +--+ 1 row in set (0.00 sec) mysql show tables; ++ | Tables_in_test | ++ | abc| ++ 1 row in set (0.00 sec) mysql describe abc; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | a | text | NO | | | | | b | text | NO | | | | | c | text | NO | | | | +---+--+--+-+-+---+ 3 rows in set (0.00 sec) mysql LOAD DATA INFILE '/tmp/abc.txt' INTO TABLE test.abc - FIELDS TERMINATED BY ',' - LINES TERMINATED BY '\n'; Query OK, 3 rows affected (0.02 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql select * from abc; ++-+-+ | a | b | c | ++-+-+ | A1 | B1 | C1 | | A2 | B2 | C2 | | A3 | B3 | C3 | ++-+-+ 3 rows in set (0.00 sec) -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 On Fri, April 13, 2007 09:39, Thufir wrote: Mogens Melander mogens at fumlersoft.dk writes: Hmmm, your LINES TERMINATED BY '/r/n'; should be: LINES TERMINATED BY '\r\n'; if the abc.txt file was generated on windows. If it was made on *nix/linux, it should be: LINES TERMINATED BY '\n'; I think I fixed it, at least as best I could. still same result: [EMAIL PROTECTED] ~]$ [EMAIL PROTECTED] ~]$ [EMAIL PROTECTED] ~]$ su - mysql Password: -bash-3.1$ -bash-3.1$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18 to server version: 5.0.27 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql SHOW DATABASES; ++ | Database | ++ | information_schema | | alpha | | bravo | | charlie| | delta | | mysql | | test | ++ 7 rows in set (0.40 sec) mysql USE delta; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql SHOW TABLES; +-+ | Tables_in_delta | +-+ | abc | +-+ 1 row in set (0.00 sec) mysql DESCRIBE abc; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | a | text | NO | | | | | b | text | NO | | | | | c | text | NO | | | | +---+--+--+-+-+---+ 3 rows in set (0.37 sec) mysql LOAD DATA INFILE '/tmp/abc.txt' INTO TABLE delta.abc FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'; ERROR 13 (HY000): Can't get stat of '/tmp/abc.txt' (Errcode: 13) mysql ; ERROR: No query specified mysql quit Bye -bash-3.1$ exit logout [EMAIL PROTECTED] ~]$ [EMAIL PROTECTED] ~]$ cat /tmp/abc.txt -n 1 A1, B1, C1 2 A2, B2, C2 3 A3, B3, C3 [EMAIL PROTECTED] ~]$ [EMAIL PROTECTED] ~]$ cat /tmp/abc.txt A1, B1, C1 A2, B2, C2 A3, B3, C3 [EMAIL PROTECTED] ~]$ [EMAIL PROTECTED] ~]$ [EMAIL PROTECTED] ~]$ [EMAIL PROTECTED] ~]$ ll /tmp/abc.txt -rw-r--r-- 1 thufir thufir 33 Apr 12 06:59 /tmp/abc.txt [EMAIL PROTECTED] ~]$ [EMAIL PROTECTED] ~]$ [EMAIL PROTECTED] ~]$ date Fri Apr 13 08:33:44 BST 2007 [EMAIL PROTECTED] ~]$ [EMAIL PROTECTED] ~]$ [EMAIL PROTECTED] ~]$ thanks, Thufir -- 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: how to tell if something hasn't happened yet
How about this: select s.* from store s where s.id not in (select t.storeid from trans t where t.created=date(now())); -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 On Sat, April 14, 2007 00:22, Jay Blanchard wrote: Good day gurus and gurettes! I have a table; | transactionid | int(11) | NO | PRI | | auto_increment | | username | varchar(32) | NO | | || | storeid | varchar(6) | NO | | || | action| int(4) | NO | | || | code | int(2) | NO | | || | ipAddr| varchar(32) | NO | | || | created | datetime| NO | MUL | || | created_by| varchar(32) | NO | | || I used to have a query (I have misplaced it somehow) where I could tell which storied had not logged in (created) today yet. No matter how hard I try I cannot remember the query. What I need is a query that will tell me at any given point during the day which storeid is not online (created). I do have a sister table where all of the storeid's are, so the join happens there. I can test created for IS NULL but it does not limit the query to today. select store.storeid, store.stName from store left outer join transaction on(store.storeid = transaction.storeid) where transaction.created IS NULL and store.active = 'yes' group by store.storeid; How can I limit this to today only without having to hard code a date into the query? TVMIA! -- 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: a little user rights help?
Well, that look like a simple way to achieve your goal. connect to db as AdminTyp2, get usercredentials (select), reconnect with users AdminType. Normaly i handle stuff like that in the application. No need to display a delete button, if the action will fail anyway (with an ugly mysql error). -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 On Fri, April 13, 2007 22:18, Denise Wilson wrote: Hi. I'm brand new at this so I'm looking for a little help. I need to have two difference levels of access to a mysql database that I am developing for our librarians to use to maintain the various research resources we have available in our library. AdminType1 should have Delete, Insert, Select, and Update rights on all the tables in the Resources database. AdminType2 will have the same rights on some of the tables in the Resources database, but in other tables they should only have Select rights. At the moment, my plan is to have a separate user database that will contain a table with a row for each librarian and a column that will hold information about whether the librarian is AdminType1 or AdminType2. I plan to set up 2 users in the grant tables of the mysql database. AdminType1 will be granted the more comprehensive rights to all the tables in the Resources database and AdminType2 will be granted the Delete, Insert, and Update rights only on the appropriate tables. After the user has logged into my user database, they will be connected to the Resources database as either user AdminType1 or AdminType2. My Question: Is this a good way to approach this or am I WAY off base? Denise -- 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: can't import
Hmmm, your LINES TERMINATED BY '/r/n'; should be: LINES TERMINATED BY '\r\n'; if the abc.txt file was generated on windows. If it was made on *nix/linux, it should be: LINES TERMINATED BY '\n'; -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 On Thu, April 12, 2007 09:58, Thufir wrote: I'm at total loss. apparently, error 13 relates to file permissions? what could possibly be preventing the import? http://paste.husk.org/8073 for: [EMAIL PROTECTED] ~]$ [EMAIL PROTECTED] ~]$ ll /tmp/abc.txt -rw-r--r-- 1 thufir thufir 33 Apr 12 06:59 /tmp/abc.txt [EMAIL PROTECTED] ~]$ cat /tmp/abc.txt A1, B1, C1 A2, B2, C2 A3, B3, C3 [EMAIL PROTECTED] ~]$ mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 to server version: 5.0.27 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show databases; ++ | Database | ++ | information_schema | | alpha | | bravo | | charlie| | delta | | mysql | | test | ++ 7 rows in set (0.09 sec) mysql use delta; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql show tables; +-+ | Tables_in_delta | +-+ | abc | +-+ 1 row in set (0.00 sec) mysql describe abc; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | a | text | NO | | | | | b | text | NO | | | | | c | text | NO | | | | +---+--+--+-+-+---+ 3 rows in set (0.00 sec) mysql LOAD DATA INFILE '/tmp/abc.txt' INTO TABLE delta.abc FIELDS TERMINATED BY ',' LINES TERMINATED BY '/r/n'; ERROR 13 (HY000): Can't get stat of '/tmp/abc.txt' (Errcode: 13) mysql exit Bye [EMAIL PROTECTED] ~]$ date Thu Apr 12 08:53:37 BST 2007 [EMAIL PROTECTED] ~]$ [EMAIL PROTECTED] ~]$ (I've even tried it as root, same result. this implies to me that the source of the message is with mysql rather than linux per se.) thanks, Thufir -- 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: database backup problem, since database over 60mb
Hi, Maybe you will have better luck with a command like: mysqldump -u user -ppassword databasename backup_date.sql Regarding phpmyadmin, my guess would be, the script is longer than the permitted time to finish. Defaults in php.ini is something like: max_execution_time = 30 ; Maximum execution time of each script, ; in seconds max_input_time = 60 ; Maximum amount of time each script may spend ; parsing request data memory_limit = 8M; Maximum amount of memory a script may ; consume (8MB) -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 On Wed, April 11, 2007 21:11, Richard wrote: Hello, I've got a problem with mysql 5 on my debian server. I've got a forum on this server and untill the database reached about 60 Mo I could dump the database with either phpmyadmin or with the command : mysql -u user -p'password' databasename backup_date.sql My last backup that worked was about 56Mb, but now since I've gone over the 60mb my backup files with phpmyadmin and mysqldump are only around 46Mb and therefore don't contain everything and also when I do a mysql -u user -p'password' databasename backup_date.sql it never finishes, and even if I wait for two hours the bacup_date.sql file is 0Mb ... The forum runs well and I use no compression I save the file in simple .sql text format. Any ideas as to why it does this or how I can fix it would be great ! I've gone through my my.cnf file and I can't see any setting that seems to block this. If you need any further information please let me know 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 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: import fails: error 13
You need a LOCAL in your LOAD statement. LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number LINES] [(col_name_or_user_var,...)] [SET col_name = expr,...)] -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 On Thu, April 12, 2007 02:37, Thufir wrote: [EMAIL PROTECTED] ~]$ [EMAIL PROTECTED] ~]$ cat abc.txt -n 1 A1,B1,C1 2 A2,B2,C2 3 A3,B3,C3 [EMAIL PROTECTED] ~]$ [EMAIL PROTECTED] ~]$ mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 21 to server version: 5.0.27 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show databases; ++ | Database | ++ | information_schema | | alpha | | bravo | | charlie| | delta | | mysql | | test | ++ 7 rows in set (0.00 sec) mysql use delta; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql show tables; +-+ | Tables_in_delta | +-+ | abc | +-+ 1 row in set (0.00 sec) mysql describe abc; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | a | text | NO | | | | | b | text | NO | | | | | c | text | NO | | | | +---+--+--+-+-+---+ 3 rows in set (0.01 sec) mysql LOAD DATA INFILE '/home/thufir/abc.txt' INTO TABLE delta.abc; ERROR 13 (HY000): Can't get stat of '/home/thufir/abc.txt' (Errcode: 13) mysql mysql quit Bye [EMAIL PROTECTED] ~]$ [EMAIL PROTECTED] ~]$ date Thu Apr 12 01:35:36 BST 2007 [EMAIL PROTECTED] ~]$ -- 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: How can I do something like this in mySQL...
Well, maybe you want to read up on isnull() and case (..) in the manual.
It's in there, somewhere.
mysql SELECT CASE 1 WHEN 1 THEN 'one'
- WHEN 2 THEN 'two' ELSE 'more' END;
- 'one'
mysql SELECT CASE WHEN 10 THEN 'true' ELSE 'false' END;
- 'true'
mysql SELECT CASE BINARY 'B'
- WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
- NULL
IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 0 and expr1 NULL) then IF() returns expr2;
otherwise it returns expr3. IF() returns a numeric or string value,
depending on the context in which it is used.
mysql SELECT IF(12,2,3);
- 3
mysql SELECT IF(12,'yes','no');
- 'yes'
mysql SELECT IF(STRCMP('test','test1'),'no','yes');
- 'no'
IFNULL(expr1,expr2)
If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.
IFNULL() returns a numeric or string value, depending on the context in
which it is used.
mysql SELECT IFNULL(1,0);
- 1
mysql SELECT IFNULL(NULL,10);
- 10
mysql SELECT IFNULL(1/0,10);
- 10
mysql SELECT IFNULL(1/0,'yes');
- 'yes'
--
Later
Mogens Melander
+45 40 85 71 38
+66 870 133 224
On Sat, April 7, 2007 20:19, John Kopanas wrote:
I have a query that looks something like this:
SELECT (c_o_w_inst_rev - c_o_w_estcost)/c_o_w_inst_rev
FROM tmpGovernmentSummaries
The problem is that sometimes c_o_w_inst_rev is 0 and dividing by zero
returns a NULL.
If c_o_w_inst_rev == 0 how can I return 0 for the SELECT above instead
of NULL? Can I test in the SELECT if c_o_w_inst_rev is 0 and return 0
and if not do the math?
Insight would be greatly appreciated :-)
--
John Kopanas
[EMAIL PROTECTED]
http://www.kopanas.com
http://www.cusec.net
http://www.soen.info
--
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: question about Queries per second avg
My idea of this is : (Questions+Slow queries)/Uptime = Queries per second avg (118794 + 16) / 84751) = 1,4018713643496831895788840249673 Uptime: 84751 Threads: 2 Questions: 118794 Slow queries: 16 Opens: 478 Flush tables: 1 Open tables: 29 Queries per second avg: 1.402 -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 On Fri, April 6, 2007 10:16, Christophe Gregoir wrote: I have to admit, Google doesn't provide much information. Let's take a closer look and see if we can't figure out ourselves what that value means. Here is the output of `mysqladmin status` on my development box: Uptime: 7510 Threads: 3 Questions: 4123 Slow queries: 0 Opens: 713 Flush tables: 1 Open tables: 64 Queries per second avg: 0.549 The number of queries would be Questions + Slow queries. See if you can spot where mysqladmin gets its time count and use it to divide the previous sum. Your box isn't underperforming, it just isn't under a high load. Question answered ? grtz C.R.Vegelin wrote: Thanks Christophe, Question rephrased: how do you explain 0.05 q/s = 20 s/q with an immediate response ? Cor - Original Message - From: Christophe Gregoir [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, April 06, 2007 8:35 AM Subject: Re: question about Queries per second avg C.R.Vegelin wrote: ... How should I interpret Queries per second avg ? How about as 'queries per second on average' :) -- 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: mysql question.
Hmmm, using distinct email in a query where you want to count email won't work. select mail, count(mail) as mailcnt from guest where voted='yes' group by mail order by mailcnt; should do the trick. On Tue, April 3, 2007 12:51, Me2resh Lists wrote: hi i need help regarding a sql query in my php app. the query is : $SQL = SELECT DISTINCT(EMail) FROM mena_guests WHERE Voted = 'yes' LIMIT $startingID,$items_numbers_list; i want to sort this query by the number of the repeated EMail counts. can anyone help me with that please ? -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- 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]
