Re: logging of BAD queries
Am 09.02.2010 16:27, schrieb andy knasinski: I've used the general and slow query log in the past, but I am trying to track down some queries from a compiled app that never seem to be hitting the DB server. My guess is that the SQL syntax is bad and never get executed, but I don't see any related queries in the general query log. Does the general log include invalid SQL? I've also tried to use the driver logging, but on Windows it overwrites with the last SQL command so I cannot get a good capture as requests are sent to the DB. DB is MySQL 5.0.x you can try MySQL proxy -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Upgrade from 4.0.26 to 5.0.67
Nanu Kalmanovitz schrieb: Hi! I wish to upgrade the MySQL on a web server (Novell 6.5 sp6 - Apache 2, MySQL ver. 4.0.26, PHP 5.2.3) to 4.1.2 or 5.0.67. Is there any possibility to upgrade directly from MySQL 4.0.26 to 5.0.67, without upgrading first to the intermediate versions? yes, but don't forget to run mysql_convert_table_format after upgrade (beside mysql_fix_privilege_tables) -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Install MySQL on Windows XP
yuan edit schrieb: Hi all. I am trying install mysql-essential-5.0.67-win32.msi on windows xp. But i can not configure the mysql server successful. I stoped the firewall and anti-virus programs when i install mysql server. there is mysql message: Could not start the service MySQL5.Error:0 Now i do not know what to do,Can someone help me? My english is not well.Do i speak clearly? take a look into your mysql error log, windows event viewer most common reason for not starting mysql is missconfigured InnoDB -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[phpMyAdmin] please vote or suggest features
Hi, for all you people out there loving phpMyAdmin ;-) please visit http://hackontest.org and vote for or suggest your favorite feature you would like to see in phpMyAdmin and that can be implemented within 24 hours by a team of three Thank you very much! -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Do I need to use GROUP BY to do this?
Re: Do I need to use GROUP BY to do this? yes Grant Giddens schrieb: Hi, nbsp; I have a table where I keep sales transactions, so I'm trying to do a query that will count the number of transactions per day. My test data looks like: What type of query do I need to get that information? SELECT `sales_date`, COUNT(*) FROM `sales_activity` WHERE `sales_type` = 1 GROUP BY `sales_date` -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: delete a data from 3 tables
Chandra N schrieb: hi, I would like to know how to delete a data from 3 table which is interconnected with each other by foreign key. i.e 1st table is connected with 2nd table by foreign key and 2nd table is connected with 3rd table with foreign key. please help to solve this problem. What exactly is the problem? What exactly did you tried? What was the error? How are the foreign keys defined? -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Still Not Working
Jesse schrieb:
Sorry for posting this again, but I got only one response last time, and
I'm still having the problem. I spent HOURS the other day manually
going through the data and Properizing these things by hand. I don't
want to do that again if I can avoid it. If anyone has any clues on
this one, I would appreciate it.
The only difference in this and what I have now is that someone
suggested changing it to Deterministic, which I did, and that didn't
change the output. I also changed SQL SECURITY DEFINER to SQL
SECURITY INVOKER, and that didn't make a difference either.
I have the following function on two servers:
CREATE FUNCTION `ProperCase`(cInput TEXT)
RETURNS text
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
Declare cReturn Text;
Set cReturn = CONCAT(UPPER(SUBSTRING(cInput,1,1)),LOWER(SUBSTRING(cInput
FROM 2)));
RETURN cReturn;
END;
It's a very simple function used to properize a string sent to it. When
I do
a simple SELECT ProperCase('JESSE'); it returns JESSE on our server that
is running 5.0.17-nt-log. On another server that I've got, running
5.0.51a-community-nt, this function returns Jesse as it should.
does it work outside the function?
did you tried SUBSTRING(cInput, 2)?
did you tried with converting?
from the manual: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
LOWER() (and UPPER()) are ineffective when applied to binary strings
(BINARY, VARBINARY, BLOB). To perform lettercase conversion, convert the
string to a non-binary string:
mysql SET @str = BINARY 'New York';
mysql SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: improve performance on FULLTEXT search.
Ananda Kumar schrieb:
Hi All,
We have table with 99 Million records, with fulltext index.
But when there is not load the sql's performance in just 6 sec, but when
anyother jobs like Index creation or data load is happening its take close
to 3 min for the same query to execute, any ways to improve the performance
of this query.
I have set innodb_buffer=11GB, key_buffer=3GB, 8 CPU, total 16 GB RAM
mysql explain select
-
-
ITEM_ID,LEAF_CATEG_ID,SLR_ID,ITEM_TITL,CURNT_PRICE_USD,START_PRICE_USD,RSRV_PRICE_USD,BIN_PRICE_USD,QTY_AVAIL,QTY_SOLD,
-
BIDCOUNT,AUCT_DURTN_DAYS,AUCT_TYPE_CODE,SUCCESS_YN,BIN_SOLD_FLAG,BOLD_FEE_FLAG,HIGHLIGHT_FEE_FLAG,GALLERY_FEE_FLAG,
-
GALLERY_FEATURED_FEE_FLAG,FEATURED_FEE_FLAG,GIFT_FEE_FLAG,
are this fields in same order as in table?
DATE_FORMAT(AUCT_START_DATE,'%Y-%m-%d
' %H:%i:%s'),DATE_FORMAT(AUCT_END_DATE,'%Y-%m-%d
AUCT_START_DATE, AUCT_END_DATE
why DATE_FORMAT? doesn't MySQL return datetime fields already as Y-m-d H:i:s ?
%H:%i:%s'),GMS_USD,RSRV_FLAG from amc_rch where match(ITEM_TITL)
- AGAINST('BOOKS') 0 and
why 0 ?
CURNT_PRICE_USD between ifnull(null,0) and
ifnull(null,) limit 1000;
CURNT_PRICE_USD between 0 and
why ifnull(null, ...) ?
++-+---+--+--+--+-+--+--+-+
| id | select_type | table | type | possible_keys|
key | key_len | ref | rows | Extra |
++-+---+--+--+--+-+--+--+-+
| 1 | SIMPLE | amc_rch | fulltext | ER_IT_CTX_IDX_0805201045 |
ER_IT_CTX_IDX_0805201045 | 0 | |1 | Using where |
++-+---+--+--+--+-+--+--+-+
1 row in set (0.05 sec)
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: batch in a read loop
Baumann, Michael schrieb: Hi there, I got a quite weird problem. Trying to update a table via a batch shell script that looks like this snip while read AAA BBB do mysql -u $DBUSER --password=$DBPASS --batch --execute=update ^ i guess your quotes are wrong, did you tried mysql -u $DBUSER --password=$DBPASS --batch --execute=update -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index/Range Problem?
Dave schrieb: Hi all, I've been trying to optimize some of our queries against a large database and come up against an index problem I haven't been able to find any documentation on. I've cut the query down to the bare minimum, and found the following -- explain Select iname,domain,serv,time from log where date between '2008-05-10' and '2008-05-30'; ++-+---+---+---+--+-+--+ ---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+--+-+--+ ---+-+ | 1 | SIMPLE | log | range | date | date | 4 | NULL | 45178 | Using where | ++-+---+---+---+--+-+--+ ---+-+ As you can see in the above query, it uses type range and the key date is used. If I change it to -05-01 to -05-30 though it does not : explain Select iname,domain,serv,time from log where date between '2008-05-01' and '2008-05-30'; ++-+---+--+---+--+-+--+- ---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+--+- ---+-+ | 1 | SIMPLE | log | ALL | date | NULL | NULL| NULL | 353558 | Using where | ++-+---+--+---+--+-+--+- ---+-+ As you can see the type is now ALL and it doesn't work. as Ananda already wrote, it seems rows valid for '2008-05-01' to '2008-05-30' are exceed the threshold when MySQL thinks it is faster to scan the table instead of scan the index and than read the table What could be the cause of this? It seems like its limited to a specific number of rows? The rows in explain appear to be wrong... mysql Select count(id) from log where date between '2008-05-01' and '2008-05-30'; +---+ | count(id) | +---+ | 85232 | +---+ 1 row in set (0.97 sec) rows is how many rows MySQL thinks it must examine to execute the query, not the number of rows possible returned http://dev.mysql.com/doc/refman/5.1/en/using-explain.html -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query, min, max with where conditions
CRISTEA, Adrian schrieb: Hello there, What is the corect syntax for selecting something like: select a, b, (min(q) where date100), (max(q) where date100) from a left join b left join c group by a.p i need min() max() values each of them with other WHERE clause. How can I do that? sub select or JOIN with condition, g.e.: SELECT MAX(copy_high.q), MIN(copy_low.q) FROM tab_with_date LEFT JOIN tab_with_date AS copy_high ON tab_with_date.pk = copy_high.pk AND copy_high.date 100 LEFT JOIN tab_with_date AS copy_low ON tab_with_date.pk = copy_low.pk AND copy_low.date 100 -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Serializing mySQLi result resource in PHP
Andrew Martin schrieb: Hello, Apologies for a slightly off topic PHP related post, the php-db mailing list has not been able to help with this so far. Is it possible to serialize a MySQL(i) result resource (specifically using PHP 5)? no I am looking to insert query results into the eAccelerator cache but the resource returned by eA does not appear to be recognised by mysqli_fetch_assoc. than you need to fetch the result, and store this result like any other array, but not the resource -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Matching subtables
Werner Van Belle schrieb: Hello, You might find the following challenging -or- you might now the answer :-) Table Q Subtable, field, val, ID A 1 a42 A 2 b42 B 1 a78 B 2 t78 B 3 o78 C 1 u23 Table R Subtableid, field, val A 1 a A 2 b Table S Subtableid, field, val B 1 a B 2 t Table T Subtableid, field, val C 1 u A 1 a A 2 b We now want to check whether table R is fully contained in table Q and what the ID is. In this case the answer should be 42. However if we would use table S and mathc it against table Q, then we should not get 78 back since field 3 is missing in table S. Also, we might want to perform this operation in batch mode, where we provide a table such as T for which we then should get the return value Subtable, field, val, ID A 1 a42 A 2 b42 C 1 u23 Is there anybody that bumped into a similar query and was able to solve it satisfactory ? you can do a OUTER JOIN on subtableid, and than check for NULL values (with HAVING), which means that at least one field is missing in one of the tables with sub selects: untested: SELECT Subtable, ID FROM `T` WHERE ID NOT IN ( SELECT ID FROM T OUTER JOIN Q ON T.Subtable = Q.Subtable AND T.field = Q.field AND T.val = Q.val HAVING ISNULL(Q.ID) OR ISNULL(T.ID) ) -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dump problem
נור דאוד schrieb: Hello list, I have a problem dumping a database. The problem is that the database uses the swedish charset (historical, hosting provider didn't have all sets). The data itself is Arabic (windows-1256), and although I have no idea how it is stored inside the database's files, the website's output is Arabic windows-1256. if you have stored another charsets in a filed than the field is declared as, than you need to change the charsets of this field without converting the content http://dev.mysql.com/doc/refman/5.1/en/charset-conversion.html Now I am ready to move away from that provider, so I want to take a dump of the data. I've tried many mysqldump options, but I always get a file full of giberish. Using iconv on the file doesn't even work... So my question is: How do you take a dump of a swedish-based database, and end up with a windows-1256 dump file?? with SET NAMES, you tell MySQL which charset it should use to return content to you, or which charset has the content you send to the server http://dev.mysql.com/doc/refman/5.1/en/charset-connection.html -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Learning best methods
[EMAIL PROTECTED] schrieb: I have the following table: explain domain_payments; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | Invid | int(11) | | | 0 | | | Custid | int(11) | | | 0 | | | Date| date | YES | | NULL| | | Description | varchar(80) | YES | | NULL| | | Domain | varchar(150) | YES | | NULL| | | UnitPrice | double | YES | | NULL| | | Quantity| int(11) | | | 0 | | | Amount | double | YES | | NULL| | +-+--+--+-+-+---+ My goal is to create a table with the latest payment date and invoice id for each domain. This is what I did: create table t select domain,max(invid) as invid from domain_payments group by domain; alter table t add date_paid date; update t,domain_payments as tr set date_paid=date where t.invid=tr.invid; The above worked fine for the number of records in my dataset. In learning [my]sql I am trying to see how this would best be done with a large dataset as well more efficiently in general. Thanks for any pointers. CREATE TABLE `t` SELECT `Domain`, `Invid`, `Date` FROM `domain_payments` WHERE `domain_payments`.`Invid` IN ( SELECT MAX(`Invid`) FROM `domain_payments` GROUP BY `Domain`) you can also save this as a VIEW btw. `Domain` should have an index, and i think `Invid` and `Custid` too -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld-nt Windows service: delay btwn svc running and accepting conx
Les Schaffer schrieb: We are having a small technical glitch about which we would like to have some insight: our application needs to start mysqld-nt as a Windows service after which we fairly quickly try to make connections to the Server. we are using the python wrappers, MySQLdb, and we successfully bring up the service 99.9% of the time. this means we get win32service.SERVICE_RUNNING before trying to make connections. and most other times, we have no problem making connections immediately after SERVICE_RUNNING is achieved. however, if we try to start the app right after Windows XP boots, or if XP has been sitting idle for a long while after the service had been brought up and down, we get OperationalError 2003, can't connect to server. when i check the server logs, i see that Windows ServiceManager tells us that we are SERVICE_RUNNING several seconds before the log says ... mysqld-nt.exe: ready for connections . we could put a 4-6 seconds on the connect_timeout, but we would like to at least understand what we are seeing. are there other states the ServiceManager would report for mysqld-nt, or are we limited to STOPPED/STARTING/RUNNING/STOPPING? more to the point, what determines the time delay between SERVICE_RUNNING and ... mysqld-nt.exe: ready for connections and is there a tried and true way to detect ready for connections without simply making a connection attempt??? did you take a look add the MySQL log? there you can see what MySQL is doing, with times you could also prioritize background processes instead of desktop processes to speedup MySQL start -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ordered list of titles, with fallback if title is only available in another language
Jack Bates schrieb: Given columns a, b, and c, where I GROUP BY a, how do I get the value of column b in each group which corresponds to the maximum value of column c? http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
inserting client time instead of server time
Hi, is there a way or a function like NOW() except it returns the client time and not the server time? -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connectors: per session persistent connection (PHP)
Paul DuBois schrieb: On May 7, 2008, at 4:36 AM, Sebastian Mendel wrote: Hi, wouldn't it be very helpful if mysql connectors support some sort of per session persistent connection? this would save a lot of queries in many apps, for example SET NAMES, setting variables, creating temporary tables How would a persistent connection save any of that? Suppose the script that previously used the connection reset any or all of those things? i do not fully understand, why should the script reset these things? i do not talk of a global persistent connection, every session should have it's own persistent connection (if requested). -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
connectors: per session persistent connection (PHP)
Hi, wouldn't it be very helpful if mysql connectors support some sort of per session persistent connection? this would save a lot of queries in many apps, for example SET NAMES, setting variables, creating temporary tables or are there any other methods i am not aware of to achieve this? -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connectors: per session persistent connection (PHP)
isn't this the general mysql list? isn't mysqlnd maintained by mysql? Michael Dykman schrieb: This is a little off-topic for this list (recent PHP tutorials nowithstanding)... replying offline On Wed, May 7, 2008 at 5:36 AM, Sebastian Mendel [EMAIL PROTECTED] wrote: Hi, wouldn't it be very helpful if mysql connectors support some sort of per session persistent connection? this would save a lot of queries in many apps, for example SET NAMES, setting variables, creating temporary tables or are there any other methods i am not aware of to achieve this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing table (shall I create a primary field?)
Charles Lambach schrieb:
Hi.
My hosting provider recommended me to optimize my 200,000 record table in
order to save resources.
I do _always_ this query:
SELECT * FROM books WHERE isbn='foo' LIMIT 1
The primary key of this table was 'id', and 'isbn' was and INDEX field.
I've modified this:
ALTER TABLE books DROP PRIMARY KEY, ADD INDEX ('isbn')
ALTER TABLE books ADD PRIMARY KEY ('isbn')
Is this a good change? Am I going to waste less resources with 'isbn' field
as primary key?
IMO not, but this depends on your app,
the Primary Key should be a value that never changes in lifetime of a row,
and should never be re-used once deleted
if you ever happen to change your ISBN cause by a typo or something, than
your references to other tables need to be updated too
having `id` as primary key is good
and leave the ISBN unique
you can cut down the index length by half the ISBN length, this should be
more than enough
according to http://en.wikipedia.org/wiki/International_Standard_Book_Number
you can use a fixed width unsigned INT field with a length of 13 for your ISBN
but you will loose formating ...
or you use two fields, one with formated ISBN and one indexed with numeric ISBN
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql variables
Krishna Chandra Prajapati schrieb: Hi Dan, I am worried about Key_blocks_unused. when Key_blocks_unused reach to 0. There will be no free blocks then how insert query will work. it is like any other cache system, if the cache is not usable (full, not accessible or whatever) the cache will be omitted, and writes/reads go directly to the disk and not the RAM (cache) you do not need to worry about loosing data caused by a full cache -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: running optimize/analyze command
Ananda Kumar schrieb: Hi All, I am using mysql 5.0.41 on debain. I have 8 processor, 8 GB RAM. I have atable with 95 Million records, each day there will be about 1.5 Million records deleted, and around 3.5 Million records added using LOAD FILE script. Since there would daily deletes happening, there would be lot of fragmention, so i used the analyze table table_name; command to defragment it, it took close to 12 hrs. Is there any better and faster way to analyze or optimize the table to defragmentation. having a fixed row length will prevent tale fragmentation -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Design
Krishna Chandra Prajapati schrieb: Hi All, Below is the table design on mysql server. CREATE TABLE `coupon_per_course` ( `coupon_id` int(10) unsigned NOT NULL default '0', `course_id` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`coupon_id`,`course_id`), KEY `idx_coupon_per_course` (`coupon_id`), KEY `idx_coupon_per_course_1` (`course_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; In my view index idx_coupon_per_course should not be there. Since coupon_id is a primary key. so it will be utilized for searching. Before removing index idx_coupon_per_course mysql do benchmark(100,(select sql_no_cache ac.plan from affiliate_coupon ac, coupon_per_course cpc where ac.coupon_code='TST0G0' and ac.coupon_id = cpc.coupon_id and cpc.course_id = 213336)); Query OK, 0 rows affected (0.06 sec) After removing index idx_coupon_per_course mysql do benchmark(100,(select sql_no_cache ac.plan from affiliate_coupon ac, coupon_per_course cpc where ac.coupon_code='TST0G0' and ac.coupon_id = cpc.coupon_id and cpc.course_id = 213336)); Query OK, 0 rows affected (0.07 sec) I am not able to understand why after removing the index idx_coupon_per_course, it is taking more time. As it must take less time. Some other statistics are mysql select count(*) from coupon_per_course; +--+ | count(*) | +--+ | 296218 | +--+ mysql select count(distinct coupon_id) from coupon_per_course; +---+ | count(distinct coupon_id) | +---+ |211519 | +---+ as you can see above, is the PRIMARY KEY(`coupon_id`,`course_id`) not only larger caused by having two fields indexed, also by having more index entries so it seems not unusual to me that it takes more time to search this index ... -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Aggregation question
Gary Greenberg schrieb: I have a table that stores performed transactions and I need to build a histogram of a number of transactions per day in the requested period. So, I made a simple query with the group by clause which returns me what I need: 2008-04-1665456204 2008-04-17190838546 2008-04-188909047 2008-04-199085084 2008-04-2118221038 2008-04-2218246184 except that there is no entry for April 20th as there were no transactions at that day. I need a query to return me zero for that day. I.e. I need uninterrupted sequence of dates. I am beating my head at this problem for the whole day and did not make much of a progress. If someone has any idea how to resolve this problem, I'll appreciate a tip greatly. a similar question was just answered on this list about 15 hours before your question ... http://lists.mysql.com/mysql/212457 -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TO_DAYS Date Range Question
David Perron schrieb: Hello Users- I think I have an interesting question with regards to applying a function to date range, I think half of problem solving is explaining it to an audience so please, bear with me. There is a table Orders that has two DATE columns, StartDate and EndDate. The range of dates can vary from 1 week to years. My goal is to get a count of days that each row in Orders spans over the current financial quarter. Example rows and desired result: OrderId = 1 StartDate '2008-01-01' End Date '2008-06-01' Days in Q2 = 61 OrderId = 2 StartDate '2008-03-01' EndDate '2008-10-01' Days in Q2 = 91 Etc. I can use the TO_DAYS() function to get the absolute count of days difference between Start End, but is there any function that I could apply to limit it to return the days between a range of dates. a snapshot: MIN(TO_DAYS([date_end]), TO_DAYS([Q_end])) - MAX(TO_DAYS([Q_start]), TO_DAYS([date_start])) -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Queries
Perrin Harkins schrieb: On Wed, Apr 23, 2008 at 9:22 PM, D Hill [EMAIL PROTECTED] wrote: Can anyone shed some light if I should index wite_desc to speed things up? No, since you don't use that column at all. If you're not on MySQL 5, upgrading to MySQL 5 will help. Otherwise, you're best bet is to rewrite the query as UNION clauses with one of your WHERE conditions in each. I know it sounds crazy, but before MySQL 5 the use of indexes with OR queries was not very good. IMHO not in this case, cause it is just a simple WHERE field IN () -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Queries
D Hill schrieb:
I have something I am trying to resolve with an over abundant number of
slow queries. Perhaps it is because of some additional indexes needed.
As soon as I enabled the option 'log_queries_not_using_indexes = 1' in
the configuration file, I started getting messages relating to the
select query:
SELECT wite_what, wite_desc FROM witelist
WHERE
wite_what = '$oct1' OR
wite_what = '$oct1.$oct2' OR
wite_what = '$oct1.$oct2.$oct3' OR
wite_what = '$oct1.$oct2.$oct3.$oct4' OR
wite_what = '[EMAIL PROTECTED]' OR
wite_what = '[EMAIL PROTECTED]' OR
wite_what = '$from_dom' OR
wite_what = '$rcpt_dom';
did you tried (result depending on your MySQL version):
WHERE wite_what IN ('$oct1', '$oct1.$oct2', '$oct1.$oct2.$oct3',
'$oct1.$oct2.$oct3.$oct4', '[EMAIL PROTECTED]',
'[EMAIL PROTECTED]', '$from_dom', '$rcpt_dom');
you could also vary with thee index length if wite_what.
and what indexes do you have currently exactly?
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Php-mssql connection problems on Windows XP
Padiyath Sreekumaran schrieb:
?php
$dsn=asi_qms;
$username=asi_qms_2006;
$password=something;
$server=xxx;
if(!$handle = odbc_connect($dsn, '$username', '$password')) die('Keine
Verbindung möglich!');
?
I got the following error when I execute the previous script:
Warning: odbc_connect() [function.odbc-connect]: SQL error: [Microsoft][ODBC
SQL Server Driver][SQL Server]Login failed for user '$username'., SQL state
28000 in SQLConnect in C:\xampplite\htdocs\script.php on line 6
Keine Verbindung möglich!
What is Iam missing? Any help is appreciated.
Please send a copy of the answer in my personal E-mail address also.
$username instead of '$username'
same for '$password'
but what has this to do with MySQL???
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Php-mssql connection problems on Windows XP
Padiyath Sreekumaran schrieb: Hello Sebastian, Thanks for your mail. But I donot see any difference in my $username and yours except '(). what surprise, yes, thats it! you have to use no quotes at all (or doublequotes) around variables, RTMF is this case the one from PHP but what has this to do with MySQL??? If the above works I want to use mssql_connect command. mssql extension for PHP has absolutely nothing to do with MySQL, or? -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Queries
D Hill schrieb:
On Thu, 24 Apr 2008 at 08:58 +0200, [EMAIL PROTECTED] confabulated:
D Hill schrieb:
I have something I am trying to resolve with an over abundant number
of slow queries. Perhaps it is because of some additional indexes
needed. As soon as I enabled the option
'log_queries_not_using_indexes = 1' in the configuration file, I
started getting messages relating to the select query:
SELECT wite_what, wite_desc FROM witelist
WHERE
wite_what = '$oct1' OR
wite_what = '$oct1.$oct2' OR
wite_what = '$oct1.$oct2.$oct3' OR
wite_what = '$oct1.$oct2.$oct3.$oct4' OR
wite_what = '[EMAIL PROTECTED]' OR
wite_what = '[EMAIL PROTECTED]' OR
wite_what = '$from_dom' OR
wite_what = '$rcpt_dom';
did you tried (result depending on your MySQL version):
WHERE wite_what IN ('$oct1', '$oct1.$oct2', '$oct1.$oct2.$oct3',
'$oct1.$oct2.$oct3.$oct4', '[EMAIL PROTECTED]',
'[EMAIL PROTECTED]', '$from_dom', '$rcpt_dom');
MySQL version is 5.0.51. Sorry I forgot to mention that. I did change
the query to what you have shown. I'll have to wait till the server
comes under a load to tell. I have noted when the last slow query was
logged for this and will see.
you could also vary with thee index length if wite_what.
Right now the index is for the full length of the field (128). I just
ran a query for the length of wite_what and the maximum length so far is
34. So, I will cut the index length down to 64.
and what indexes do you have currently exactly?
id - is the primary and has an index type btree
wite_what - is a unique and has an index type of btree
so this looks all ok, i am not sure if the query time includes the time if
the query needs to wait for locked tables ...
--
Sebastian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TO_DAYS Date Range Question
David Perron schrieb: Hi Sebastian- Wanted to follow up on this. I figured out the problem. You actually have to use the LEAST GREATEST operators when comparing multiple values, this statement works perfectly. LEAST(EndDays,Q2EndDays) - GREATEST(Q2StartDays,StartDays) as DaysInQ2, Thanks again for the tip! Have a great day. oh, yes, for sure, sorry, my mistake! :-) -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a strange problem
liaojian_163 schrieb: hi,all. In my mysql server,I have a strange problem. can someone help me? Thank you. mysql select id,classid,newstime from phome_ecms_zhichang where classid=41 and id 2500 order by id desc limit 10; +--+-+-+ | id | classid | newstime| +--+-+-+ | 2543 | 41 | 2008-04-22 21:55:22 | [...] 10 rows in set (0.00 sec) mysql select id,classid,newstime from phome_ecms_zhichang where classid=41 order by id desc limit 10; +--+-+-+ | id | classid | newstime| +--+-+-+ | 2540 | 41 | 2008-04-19 12:29:30 | [...] mysql select id,classid,newstime from phome_ecms_zhichang where classid=41 and id 0 order by id desc limit 10; Empty set (0.00 sec) did you tried to repair the table and/or rebuild the indexes? -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird result on max compared to order by
[EMAIL PROTECTED] schrieb: Hi, I did a select on a primary key.. Select max(account_id) from mytable; -- it gave me a value X I did a select with order by Select account_id from mytable order by account_id desc limit 3 -- it gave me a value of Y ( Y is the right value ) I was wondering why it didn't gave me the same value and after some time doing a select max gave me the right value Y seems your index was corrupted -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question: find items tagged with specific tags
Ingo Weiss schrieb:
Thanks, Sebastian!
I have tried this one before. The problem is that it finds all items the
tags of which include EITHER 'blue' OR 'red', not 'blue' AND 'red':
oh ... and ..., i missred
SELECT DISTINCT items.*
FROM items
INNER JOIN taggings
ON items.id = taggings.item_id
INNER JOIN tags
ON tags.id = taggings.tag_id
AND tags.name = 'blue'
AND tags.name = 'red';
or
SELECT DISTINCT items.*
COUNT(items.id)
FROM [your join above]
WHERE tags.name IN ('blue', 'red')
HAVING COUNT(items.id) = 2;
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a strange problem
liaojian_163 schrieb: thank you Sebastian! I have re-created the table.there are not any problems in the table. if the table is new,need to rebuild de indexes? no -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Symlink InnoDB tables without stoping MySQL
Dobromir Velev schrieb: Hi, What I'm trying to do is to create a new InnoDB table on a different disk and symlink it to an existing database. I have innodb_file_per_table turned on and here is how I tried to do it mysql \u test mysql create table test (...) ENGINE = 'InnoDB'; mysql\q move the test.ibd file to the other disk create a simlink in the database directory flush tables; This works as expected but there is something that bothers me - I inserted about 60K rows in the new table and all queries I tried are working including selects, inserts and updates. The SHOW TABLE STATUS command displays relevant results and still the test.ibd file to which the symlink points hasn't been changed or accessed at all. Any ideas are welcome you need to setup per-table tablespace, did you? Section 13.2.3.1, “Using Per-Table Tablespaces”. http://dev.mysql.com/doc/refman/5.0/en/innodb-init.html -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rewriting query to avoid inline view
Baron Schwartz schrieb:
Hi,
On Wed, Apr 23, 2008 at 8:42 AM, Morten Primdahl [EMAIL PROTECTED] wrote:
Hi,
A user enters a date range (ie. 2 dates, '2008-04-01' and
'2008-04-03'), the problem is to determine how many open events exist
on each day in this interval.
Assume that the events table has a start_date and an end_date.
One way to solve this problem, is to create an inline view in the
query, eg.:
SELECT virtual_date_range.index_date AS index_date, COUNT(*) AS
matches
FROM events, (
SELECT DATE('2008-04-01') AS index_date FROM DUAL UNION ALL
SELECT DATE('2008-04-02') FROM DUAL UNION ALL
SELECT DATE('2008-04-03') FROM DUAL UNION ALL
) AS virtual_date_range
WHERE virtual_date_range.index_date = events.start_date
AND virtual_date_range.index_date = events.end_date
GROUP BY index_date;
This works. But I'm wondering if there's a more elegant way of
expressing the same using pure DML, such that I don't need to build a
huge inline view in case the range is multiple years. Anyone?
A solution that doesn't return any rows for the dates that do not have
an event would work.
Example of the events table and the above query in action:
http://www.pastie.org/185419
You can generate the values with the integers table.
http://www.xaprb.com/blog/2005/12/07/the-integers-table/
i knew that you would answer this ... ;-)
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment
Hiep Nguyen schrieb:
hi list,
reading manual on mysql regarding auto_increment with multiple-column
index:
CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
);
INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;
+++-+
| grp| id | name|
+++-+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+++-+
my question is what id would be if i:
UPDATE `animals` SET `grp`='mammal' WHERE `grp`='bird' AND `id`='2' AND
`name`='ostrich' LIMIT 1;
you do not need `name`='ostrich' in WHERE, cause grp,id are already UNIQUE
your key is grp,id (bird,2)
but your query will fail, because there is already grp,id (mammal,2) and
therre can not be two identical UNIQUE (PRIMARY) keys
--
Sebastian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment
Sebastian Mendel schrieb:
Hiep Nguyen schrieb:
hi list,
reading manual on mysql regarding auto_increment with multiple-column
index:
CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
);
INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;
+++-+
| grp| id | name|
+++-+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+++-+
my question is what id would be if i:
UPDATE `animals` SET `grp`='mammal' WHERE `grp`='bird' AND `id`='2'
AND `name`='ostrich' LIMIT 1;
you do not need `name`='ostrich' in WHERE, cause grp,id are already UNIQUE
your key is grp,id (bird,2)
but your query will fail, because there is already grp,id (mammal,2) and
therre can not be two identical UNIQUE (PRIMARY) keys
auto_increment comes only in effect when inserting NULL (or 0 in some SQL
mode) or nothing (with default NULL, 0 what should be always the case for
auto_increment fields)
your query should look like this:
UPDATE `animals`
SET `grp` = 'mammal',
`id` = NULL
WHERE `grp` = 'bird'
AND `id` = '2'
LIMIT 1;
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How hard is it to move from on server to another?
B. Keith Murphy schrieb: A simple rsync should do the trick. How long will depend on how much data you have. I would just shut down the server, copy over the data directory and start the new server up. Should be a piece of cake. Keith David Ruggles wrote: I have a MySQL 5.x box and I am thinking about moving it to another more powerful server. I would be able to schedule some downtime so that's not an issue. How complicated a process would this be? I don't want to upgrade the software or anything, just move the existing tables, users and permissions to another physical server. It would even have the same IP address. additionally, depending on data size it could much more easier to just install our old HDD into the new server ... whether copy the data to the new HDD or sue the old ones ... -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment
Ben Clewett schrieb: Are you sure, I just get: CREATE TABLE ... ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key the mentioned CREATE TABLE is fine and works On version 5.0.41. What version are you using? this works on all versions, and the example is from the MySQL manual http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Working with Images
Victor Subervi schrieb:
Hi;
The python code works properly, so I assume this is a strictly MySQL
question now :)
If I grab an image in the database thus:
sql = select pic1 from products where id=' + str(id) + ';
cursor.execute(sql)
pic1 = cursor.fetchall()[0][0].tostring()
# pic1 = cursor.fetchall()[0][0] // either this or the above line
and try and re-insert it thus:
cursor.execute('update products set pic1=%s where id=%s, ;',
(pic1, id))
i am not familiar with this python db abstraction class, but
the last comma seems to be wrong
try
'update products set pic1=%s where id=%s;'
instead of
'update products set pic1=%s where id=%s, ;'
or even without ';' too
and is this some sort of prepared statement, or are the parameters escaped
somewhere else?
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question: find items tagged with specific tags
Ingo Weiss schrieb:
Hi all,
I have an application where items can be tagged. There are three tables
'items', 'taggings' and 'tags' joined together like this:
items inner join taggings on (items.id = taggings.item_id) inner join
tags on (tags.id = taggings.tag_id)
Now I have been struggling for some time now with coming up with the SQL
to find the items the tags of which include a specified list of tag
names. Example:
I am looking for items tagged with 'blue' and 'red'. This should find me:
- items tagged with 'blue' and 'red'
- items tagged with 'blue', 'red' and 'green'
SELECT DISTINCT items.*
FROM [your join above]
WHERE tags.name IN ('blue', 'red');
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't get a login shell for some databases
Pam Astor schrieb: Hi, I have about a half dozen small databases associated with a couple of small shopping carts, discussion forums, etc. All six databases have usernames and passwords associated with them, and all the databases are connected to the php based forums and shopping carts, no problems with the php applications reading and writing data to them. I set the databases up a few weeks ago, the first one I set up, I can’t remember exactly how I set it up. Here is what I don’t understand. I’m able to login as root, and also the very first database I set up, I am able to log in to MySQL via shell using the username and password associated with that user. However for all the other databases, I am not able to log in to a MySQL shell using the other usernames associated with their databases – even though the php applications are configured to use the usernames, passwords and database names for those users which I can not log in to get a shell MySQL session. How is it that my php applications can log in to MySQL and I can’t get a terminal connection to them? I’m sure it’s something about granting a login shell but how would I do that? connectiong from shell means connecting as localhost by default, connecting from PHP can be some different server and/or PHP uses the full IP address/hostname of the server check/compare the privileges for your users for 'localhost' and '%' -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't get a login shell for some databases
Pam Astor schrieb: connectiong from shell means connecting as localhost by default, connecting from PHP can be some different server and/or PHP uses the full IP address/hostname of the server check/compare the privileges for your users for 'localhost' and '%' [...] The seventh line shows the second non root user I created – it has just one line and shows localhost as the host. All the rest of the users I created show the % character in the host column. would be much more easier if you would send this output here (with faked names, passwords and hosts ...) -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: grant user privileges
Sebastian Mendel schrieb: Hiep Nguyen schrieb: hi all, i have an existing database (internal) with a user named 'admin', everything works fine as far as privileges concern. i just created a new database (test) and want to grant admin's privileges on test as same as internal. how do i do this??? i tried (as root): grant all on test.* to 'admin'@'localhost'; grant all on test.* to 'admin'@'10.0.0.%'; but it seems not right. grant access to admin from 'foreign' hosts? 10.0.0.0.% is not the host admin connects from but _TO_! this must be the host of the MySQL server, the host that is specified when connecting _TO_ the database. sorry, bulls***, i was totally wrong, of course ... -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why doesn't mysql select the correnct index?
Changying Li schrieb: why does mysql use group_id index ? because in this case group_id would be faster than user_id but in fact group_id is very slow (51.21 sec), user_id is (0.00 sec) ok, at least MySQL does think so I know, but I what I really want to know is how does mysql think so ? because rows is smaller for this index, when deciding what index to use it seems MySQL does not take into account what other/later steps needed (on joined tables) to get the final result ... how to let mysql choose user_id as an index ? what's the mean of 'rows' ? valid rows after applying the WHERE to this index the result is empty set, if what you said is true, then the rows must be 0 ? no, not the final result, only for this index read about EXPLAIN in the MySQL manual http://dev.mysql.com/doc/refman/5.0/en/using-explain.html I has read it , and it described like what you said, I don't really know what is the mean of 'only for this index', I tried 'select count(*) from photo where group_id=0 and album_id!=0,' ant it get a huge number, but not the value of rows. value of rows for EXPLAIN select count(*) from photo where group_id=0 and album_id!=0 ? beside the fact, MySQL should not need to investigate any row at all for this query, it should satisfy this query from the index, without looking up any row so this is a bad example -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incorrect results from sum
Jonathan Mangin schrieb: I'm trying to total certain nutrients consumed on a given date (though I've removed date temporarily). You'll see I have three items (in two meals) in itemized, and two meal totals in simple. mysql select id, item, carb from my_menu where id in (10, 11, 22); ++-+---+ | id | item| carb | ++-+---+ | 10 | apples, w/skin, raw | 0.138 | | 11 | bananas, raw| 0.228 | | 22 | bread, Arnold Natural Wheat | 0.500 | ++-+---+ 3 rows in set (0.00 sec) mysql select * from itemized; +++-+-+-+--+ | id | date | time_of_day | uid | personal_id | units| +++-+-+-+--+ | 3 | 2008-04-01 | 06:15:00| jmangin | 10 | 167. | | 7 | 2008-04-01 | 12:30:00| jmangin | 11 | 52. | | 6 | 2008-04-01 | 12:30:00| jmangin | 22 | 36. | +++-+-+-+--+ 3 rows in set (0.01 sec) mysql select * from simple; +++-+-+--+-+--+ | id | date | time_of_day | uid | carb | protein | fat | +++-+-+--+-+--+ | 1 | 2008-04-01 | 12:05:00| jmangin | 85.0 |10.0 | 2.3 | | 2 | 2008-04-01 | 18:30:00| jmangin | 80.4 |10.0 | 10.0 | +++-+-+--+-+--+ 2 rows in set (0.01 sec) mysql select sum(my_menu.carb*units) from itemized left join my_menu on personal_id=my_menu.id; +-+ | sum(my_menu.carb*units) | +-+ | 52.9020 | +-+ 1 row in set (0.00 sec) mysql select sum(carb) from simple; ++ | sum(carb) | ++ | 165.4 | ++ 1 row in set (0.01 sec) select round(sum(my_menu.carb * units) + sum(simple.carb),2) from itemized inner join simple using (uid) left join my_menu on itemized.personal_id = my_menu.id; Instead of 218.3 this returns 602, which is (52.9 * 2 items in simple) + (165.4 * 3 items in itemized). Is it possible to get correct totals some other way with this table structure? Or explain why this is wrong? use UNION http://dev.mysql.com/doc/refman/5.0/en/union.html -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: changing name of tables into UPPER CASE
perl pra schrieb: Hi ALL,, I am new to perl, I have a database which works fine in windows, Now i need to change table names to uppercase in Linux. So I have done the following: I copied all the from files into /var/lib/mysql/new_db from windows to linux. Then changed the names to caps; and changed the permissions to 660 (with user as mysql) restarted the mysql server. I can see all the tables but when i fire following query select * from table_name i get the following error *./new_db/table_name.frm not found* ** ** can anybody help me in changing the table names to upper case when table names are in uppercase you have also write this in your queries: select * from TABLE_NAME or read: http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incorrect results from sum
Perrin Harkins schrieb: On Tue, Apr 15, 2008 at 4:21 AM, Sebastian Mendel [EMAIL PROTECTED] wrote: use UNION You can't use UNION to add the results of two queries. It would return two rows. of course! you need to use this UNION as subquery sorry for being not imprecise -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update select question
Chris W schrieb: I have the following query... SELECT c.NLCID, n.publishdate FROM newsletter n JOIN newslettersection s using (NLID) JOIN newslettercontent c using(NLCID) WHERE contenttype = 1 AND n.publishdate AND c.`timestamp` = '-00-00 00:00:00' I want to run an update on newslettercontent and set its timestamp column to be the publishdate from the newsletter table using the join rules in that query. Is there a way to do that in a query? yes, you can, see multi-table update http://dev.mysql.com/doc/refman/5.0/en/update.html -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: grant user privileges
Hiep Nguyen schrieb: hi all, i have an existing database (internal) with a user named 'admin', everything works fine as far as privileges concern. i just created a new database (test) and want to grant admin's privileges on test as same as internal. how do i do this??? i tried (as root): grant all on test.* to 'admin'@'localhost'; grant all on test.* to 'admin'@'10.0.0.%'; but it seems not right. grant access to admin from 'foreign' hosts? 10.0.0.0.% is not the host admin connects from but _TO_! this must be the host of the MySQL server, the host that is specified when connecting _TO_ the database. -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Two MySql servers, but very different performances for a SELECT JOIN
Tristan Marly schrieb: First, thanks for all your suggestions and for beeing so reactive. @Martin: the explain result was in attachment, but you will have more results in this current mail. @Rob: you are right, the 'show index' shows strange things, cf. below. @Rodolphe: indeed the STRAIGHT_JOIN has been very helpfull, cf. below. @Brent: thanks for this very precise and technical answer. too bad, that all the other list members could not read what was so helpful ... reply to the author instead of to the list is very useful for the audience ... or was it only me who missed their replies (except from Rub Wulsch)? -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why doesn't mysql select the correnct index?
Changying Li schrieb: Hi. there is a table photo and two queries: mysql show index from photo; mysql show index from photo; +---+++--++---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name| Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---+++--++---+-+--++--++-+ | photo | 1 | user_id|1 | user_id| A | 1372007 | NULL | NULL | | BTREE | NULL| | photo | 1 | user_id|2 | banned | A | 1621463 | NULL | NULL | | BTREE | NULL| | photo | 1 | group_id |1 | group_id | A | 12403 | NULL | NULL | | BTREE | NULL| | photo | 1 | group_id |2 | album_id | A | 575358 | NULL | NULL | | BTREE | NULL| +---+++--++---+-+--++--++-+ 14 rows in set (0.00 sec) explain select * FROM photo WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; ++-+---+--+---+--+-+---+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+---+--+-+ | 1 | SIMPLE | photo | ref | PRIMARY,user_id,album_id,album_id_random,group_id | group_id | 3 | const | 1438 | Using where; Using filesort | ++-+---+--+---+--+-+---+--+-+ 1 row in set (0.00 sec) mysql select * FROM photo WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; Empty set (51.21 sec) mysql explain select * FROM photo use index (user_id) WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; ++-+---+--+---+-+-+-+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+-+-+-+--+-+ | 1 | SIMPLE | photo | ref | user_id | user_id | 4 | const,const | 1694 | Using where; Using filesort | ++-+---+--+---+-+-+-+--+-+ 1 row in set (0.00 sec) mysql select * FROM photo use index (user_id) WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; Empty set (0.00 sec) why does mysql use group_id index ? because in this case group_id would be faster than user_id how to let mysql choose user_id as an index ? what's the mean of 'rows' ? valid rows after applying the WHERE to this index how doese mysql get value of 'rows'? count returned values from index with valid WEHERE I really dont wnat to use 'force index' because I'm using DBIx::Class in perl catalyst framework. why do you want to FORCE INDEX? did you tried an index(user_id, group_id)? -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why doesn't mysql select the correnct index?
Changying Li schrieb: Sebastian Mendel [EMAIL PROTECTED] writes: Changying Li schrieb: Hi. there is a table photo and two queries: mysql show index from photo; mysql show index from photo; +---+++--++---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name| Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---+++--++---+-+--++--++-+ | photo | 1 | user_id|1 | user_id| A | 1372007 | NULL | NULL | | BTREE | NULL| | photo | 1 | user_id|2 | banned | A | 1621463 | NULL | NULL | | BTREE | NULL| | photo | 1 | group_id |1 | group_id | A | 12403 | NULL | NULL | | BTREE | NULL| | photo | 1 | group_id |2 | album_id | A | 575358 | NULL | NULL | | BTREE | NULL| +---+++--++---+-+--++--++-+ 14 rows in set (0.00 sec) explain select * FROM photo WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; ++-+---+--+---+--+-+---+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+---+--+-+ | 1 | SIMPLE | photo | ref | PRIMARY,user_id,album_id,album_id_random,group_id | group_id | 3 | const | 1438 | Using where; Using filesort | ++-+---+--+---+--+-+---+--+-+ 1 row in set (0.00 sec) mysql select * FROM photo WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; Empty set (51.21 sec) mysql explain select * FROM photo use index (user_id) WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; ++-+---+--+---+-+-+-+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+-+-+-+--+-+ | 1 | SIMPLE | photo | ref | user_id | user_id | 4 | const,const | 1694 | Using where; Using filesort | ++-+---+--+---+-+-+-+--+-+ 1 row in set (0.00 sec) mysql select * FROM photo use index (user_id) WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; Empty set (0.00 sec) why does mysql use group_id index ? because in this case group_id would be faster than user_id but in fact group_id is very slow (51.21 sec), user_id is (0.00 sec) ok, at least MySQL does think so how to let mysql choose user_id as an index ? what's the mean of 'rows' ? valid rows after applying the WHERE to this index the result is empty set, if what you said is true, then the rows must be 0 ? no, not the final result, only for this index read about EXPLAIN in the MySQL manual http://dev.mysql.com/doc/refman/5.0/en/using-explain.html -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY calculated field
Neil Tompkins schrieb: Hi, How do I achieve a SQL statement to order my results based on two calculated fields for example : what two calculated fields? SELECT COUNT(ProductsPurchases.ProductID) as varProductCount, Products.Name, Products.ProductReview FROM ProductsPurchasesINNER JOIN Products ON Products.ProductID = ProductsPurchases.ProductIDGROUP BY Products.ProductID ORDER BY varProductCount+Products.ProductReviewDESC ORDER BY COUNT(ProductsPurchases.ProductID) -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY calculated field
Neil Tompkins schrieb: Hi I want to order by the totalled fields varProductCount and Products.ProductReviewDESC just put them together, separated with comma, like it is written in the manual ORDER BY varProductCount + Products.ProductReviewDESC, COUNT(ProductsPurchases.ProductID) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY calculated field
Sebastian Mendel schrieb: Neil Tompkins schrieb: Hi I want to order by the totalled fields varProductCount and Products.ProductReviewDESC just put them together, separated with comma, like it is written in the manual ORDER BY varProductCount + Products.ProductReviewDESC, COUNT(ProductsPurchases.ProductID) sorry: ORDER BY COUNT(ProductsPurchases.ProductID) + Products.ProductReviewDESC -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY calculated field
Neil Tompkins schrieb: Thanks Sebastian, but I now get the error message [MySQL][ODBC 3.51 Driver][mysqld-3.23.58]Invalid use of group function i am not familiar with ODBC or MySQL 3.x but possible just GROUP BY is missing check the manual for your mysql version for the exact syntax if this is not working at all you have to use HAVING -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql privileges
Malka Cymbalista schrieb: Thanks for your reply. When I do show grants, I get back GRANT SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'super' @ 'localhost' ( mailto:super'@ 'localhost' ) identified by password... So it looks like super doesn't have rights to select from the hr table. But why not? According to the tables_priv table, super should have right to select. did you export/import your data, or just copied the data files from your old to the new MySQL? did you reload privileges after changes? FLUSH PRIVILEGES; -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Information schema question.
Carlos Savoretti schrieb: Hi all! Well, question is how could I to retrieve information about types supported. IMHO, no -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql privileges
Brown, Charles schrieb: Here is a follow-up question: Using mysqldump, I'm about to dump all databases and import to another instance - new . My question is do I need to define all security and users in the new mysql or the security definitions and privileges will be included in the dump file created by mysqldump. i am not sure if mysqldump does include `mysql` database, but you will see if you look into it, you should run mysql_fix_privilege_tables after importing `mysql` database and FLUSH PRIVILEGES; -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: relational tables
John Taylor-Johnston schrieb: I want to make a relational link from `data` to `shopping` so when I insert a new record in `shopping`, I will see the contents of `data`.`name` and `data`.`email` as drop-down menus in `shopping`. This is InnoDB so I should be able to do this by SQL, right? where do you want to see this drop-downs? MySQL is an database server, and i m not aware of any place where it would display any drop-downs, possible you speak of some sort of GUI, like MySQL Admin or phpMyAdmin? -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to use index with order by here
Rob Wultsch schrieb: On Mon, Mar 17, 2008 at 4:36 AM, Arthur Fuller [EMAIL PROTECTED] wrote: I love when this happens. I woke in the middle of the night with an idea for you. It now occurs to me that the query you want is dead simple. It just took me a while to see: SELECT * FROM messages WHERE id_from = 1 AND id_to = 2 UNION SELECT * FROM messages WHERE id_from = 2 AND id_to = 1 ORDER BY time Assuming an index on id_from (or id_from, id_to), it will be used. This will be very quick. I bench'ed the union before sending in my original response. For the generic data set I created as an example his original query is faster (not by much) and simpler. If it were me writing the query I would use a union, probably. IN and OR never end well ;) this will not prevent filesort, because the results still needs to be sorted for ORDER BY time, or? Yes. Adding the extra column to the index will not result in losing the filesort. hu? ... i am pretty sure i had a similar problem, i solved this by adding the column with the order to the index, which 'solved' the filesort (except the ORDER is in reverse) The filesort will not be any sort of a problem unless the result is large. yes. of course, size matters, if your whole DB is small enough you will not even get any performance impacts without any index ... ;-) but it requires more resources, or? -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [ANN] PBXT storage engine version 1.0-Alpha released
Paul McCullagh schrieb: Hi All, I have just released the first fully durable version of PBXT. Because of the amount of new code I have reverted PBXT to Alpha status. This version, 1.0-alpha, can be downloaded from: http://www.primebase.org/download. will there be any Windows builds available sooner or later? -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to use index with order by here
Rob Wultsch schrieb: On Sat, Mar 15, 2008 at 2:42 PM, Nacho Garcia [EMAIL PROTECTED] wrote: Hi, im having troubles with one query, hope someone can help. on this table: messages: id_fromint(10) id_toint(10) textvarchar(1000) time with index on id_form and id_to i want to get messages sent from one user to another and vice versa order by time. let say we want to search messages between user 1 and 2: i'm doing: SELECT * FROM messages WHERE id_from in (1,2) AND id_to in (1,2) ORDER BY time but that gives me a filesort in all rows matching the where clause, and thats not good. I tried by indexing id_from, id_to, time but thats not working of course. any help would be really appreciate. Short answer: The filesort is not necessary not your problem. Add a composite key on id_from,id_to. ALTER TABLE `messages` ADD INDEX ( `id_from` , `id_to` ) this will not prevent filesort, because the results still needs to be sorted for ORDER BY time, or? i would try INDEX(`id_from`, `id_to`, `time`) but i am not sure if this will help, cause of this two IN() -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: db design
Brett Harvey schrieb: which method is better to do. I have 5 tables. They represent sections/parts of a companies standards. There are 13 main categories, each of those categories has subsections (some with 3, some with 10 or more), those subsections have subsections, etc. Which table design is better to do. search for: nested sets or something similar -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Statement
Velen schrieb: Hi, I need to write up a select statement something like: Select a.supcode,a.code,b.desc,sum(c.qty),c.dept where a.supcode=b.supcode and a.code=c.code and a.code=b.code and c.dept between $tring1 and $tring2. group by supcode This is fine but the problem is that there is duplicate supcode in a. When running this query I often have c values which does not relate to supcode. yes, because c is JOINED by `code` and not by `subcode` with a and a.code=c.code -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Updating rows in a table with the information from the same table
MariSok schrieb: I have a products table with historical price information. Some records are missing price information. I added another field - closest_price, to be populated for records with 0 price. This would be price values from the same table, same product with non-zero price with earliest date. So my update statement looks like this: update t1 a, (select price_date, product_id, price from t1 group by product_id having price_date = min(price_date) and price != 0 ) b set a.closest_price = b.price where a.product_id = b.product_id and a.price = 0; This statement doesn't work. I don't get error - just 0 rows updated. I do get results from b if I ran it on its own. Appreciate any help try: UPDATE t1 a SET a.closest_price = ( SELECT b.price FROM t1 b WHERE b.price != 0 AND b.product_id = a.product_id ORDER BY b.price_date DESC LIMIT 1 ) WHERE a.price = 0; -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL FUNCTIONS
Krishna Chandra Prajapati schrieb: Hi All, While i was going through mysql reference manual. I saw that A query cannot be cached if it contains any of the functions shown below BENCHMARK() CONNECTION_ID() CONVERT_TZ() CURDATE() CURRENT_DATE() CURRENT_TIME() CURRENT_TIMESTAMP() CURTIME()DATABASE() ENCRYPT() with one parameter FOUND_ROWS() GET_LOCK() LAST_INSERT_ID() LOAD_FILE() MASTER_POS_WAIT() NOW() RAND() RELEASE_LOCK() UNIX_TIMESTAMP() with no paramet- SLEEP() SYSDATE() USER() On my production server, the following query is being used. select * from student where regis_date=now(); Then what should i do so that the query get cached. this would be like a time service would record once the current time, and than always just send this recorded time ... wired, not? -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Statement
Velen schrieb: Hi, I need to write up a select statement something like: Select a.supcode,a.code,b.desc,sum(c.qty),c.dept where a.supcode=b.supcode and a.code=c.code and a.code=b.code and c.dept between $tring1 and $tring2. group by supcode This is fine but the problem is that there is duplicate supcode in a. When running this query I often have c values which does not relate to supcode. yes, because c is JOINED by `code` and not by `subcode` with a and a.code=c.code -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reverse index
Phil schrieb: In my never ending quest for speed ups I've been trying the following.. I pull in xml data for roughly (at the peak) 1.8M hosts from the BOINC [EMAIL PROTECTED] hosts files. Each host will have a unique id, a score, createdate and possibly a country team (as well as a number of other characteristics) These have to be ranked in multiple ways. A basic ranking is just by the score which I hold as a double, I index this along with the id of the host computer. index(id, rank) is useless, cause id should have already an index, used when querieng for specific id but if you query for rank, or order by rank, this index is not used, because rank needs to come first to be used index(id, rank) will only be usefull if you do something like SELECT ... WHERE `id` IN(1,2,3,...) ORDER BY `rank` A more complex ranking is for score within teams. I use some sql as follows for this, fastest I've found to date set @rank = 0,@pos = 0,@team:=null,@score:=null;; update host_table set teamrank= greatest( @rank:= if(@team = team and @score = rev_score, @rank, if(@team team,1, @rank+1)), least(0,@pos := if(@team = team, @pos+1,1)), least(0,@team := team)) order by team,rev_score,id possiblke some sort of multi table update, which includes your team table could be faster, and less complex, but i am not sure without knowing your whole schema (structure of db, tables) Now note that the column is rev_score. Because mysql does not support descending indexes, I added a column for which I subtract the score from 1,000,000,000 and use that as an index. (score is unlikely to get above that anytime soon) how about just negate the score (score * -1)? 1.234 becomes -1.234 ? My question is, is this worth it? It certainly seems to be faster to me, but not as much as I expected. I did try originally subtracting from 0, but that caused the rankings to be incorrect.. -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to select total votes for each comment?
Patrick Aljord schrieb: Hey all, I have comments(id,content) and votes(comment_id,vote). vote is a tinyint. I would like to select total votes for each comment, I tried: select content, sum(v.votes) from comments c left join votes v on c.id=v.comment_id but it only returns first result obviously, any idea how I could do this? did you tried in your mysql console? please add the output here and add GROUP BY - this is required by SQL standard SELECT comments.content, SUM(votes.votes) FROM comments LEFT JOIN votes ON comments.id = votes.comment_id GROUP BY comments.id -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing and exporting from MySQL, escape slash problem
Dave M G schrieb: PHP List, MySQL List In my PHP environment, I have Magic Quotes turned off, and I use the mysql_real_escape_string() function clean strings of SQL syntax before inserting them into my database. So the data stored in my database does not have escape characters in it. Particularly, double and single quotes don't have slashes in front of them. This seems to work fine so long as I'm reading data into and out of the database from within my scripts. However, when I backup and import databases - I use the phpMyAdmin interface - they have escape slashes in front of every double and single quote characters. I'm not sure if it's on the export or import where they get added in. what version of phpMyAdmin? I've looked through the phpMyAdmin online documentation, and I can't see any option to control the presence of escape slashes. It seems to me that if it adds them in when exporting, it should take them out when importing. Or vice versa, but in either case be consistent. I just want my database to be exactly as it is before any export or import options. I'm a little muddled as to where I'm making the mistake. Can anyone advice on the best practice for preserving my database as is when backing up and restoring? this 'bug' is unknown to me, did you tried to reproduce on phpMyAdmin demo servers? http://pma.cihar.com/ http://wiki.cihar.com/pma/Getting_Help -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: /tmp/mysql.sock dissapears
Ian schrieb: Hi, I am running mysql 5.0.45 on freebsd 4.11 and for some strange reason /tmp/mysql.sock keeps on disappearing and we are forced to kill -9 mysql and restart it causing db corruptions as there is no other way of telling it to stop once that file has gone. I have tried to find any reason why this happens and there are no errors, no core files, nothing - the file just disappears. why do you need to kill if the socket is missing? does $/etc/init.d/mysqld stop|restart not work without a socket? -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: /tmp/mysql.sock dissapears
Ian schrieb: Hi, I wouldnt have thought so but whenever that file is missing and try the stop/restart it just sits at the waiting for pids part of the stop loop (where it lists the pids), and it never stops the server - i have left it for over an hour and it never stops - just keeps on in the stop loop. does MySQL still respond on network connections when the socket is 'gone'? -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing data types in mysql!
Lenin Lakshminarayanan schrieb: Hello, I was pulling data from one datasource [ oracle ] earlier which had a couple of fields as integer. Now i am moving to a newer data source and the same fields are now varchar's in the newer oracle database. I am planning to change the data types of those fields from integer to varchar's as am not doing any arithmetic with those fields. Does anyone see any issues with this approach ? Will this break any of the existing functionality when i move from INT to varchar's ? it will require more space sorting will change -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: adding then removing index produces different query results
mysql mysql schrieb: Can anyone explain the following? I encountered the following very strange behaviour while attempting to optimize a query (more details are provided later on for those interested): 1) execute query takes 2 minutes 2) add index 3) execute same query takes 11 seconds 4) drop index 5) execute same query takes 0.2 seconds and uses a different method of returning results from the original query in 1) 6) restart mysql 7) execute query takes 2 minutes query cache, os cache, or some other cache -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Packing list sort
David Ruggles schrieb: I have googled, read the mysql documentation and searched the list archive. I don't know if I just don't know the correct term to use or if I have some other problem. In a nutshell I generate packing lists where the items are normally sorted alphabetically. However, there is one type of item that always gets loaded first. I would like for any of these items to always be at the top of the list. Here's the classic animal example: Given: +--+-++ | name | species | birth | +--+-++ | Chirpy | bird| 1998-09-11 | | Whistler | bird| 1997-12-09 | | Claws| cat | 1994-03-17 | | Fluffy | cat | 1993-02-04 | | Fang | dog | 1990-08-27 | | Bowser | dog | 1989-08-31 | | Buffy| dog | 1989-05-13 | | Puffball | hamster | 1999-03-30 | | Slim | snake | 1996-04-29 | +--+-++ I want hamsters to always sort out first, but still have everything else in alphabetical order Like this: +--+-++ | name | species | birth | +--+-++ | Puffball | hamster | 1999-03-30 | | Chirpy | bird| 1998-09-11 | | Whistler | bird| 1997-12-09 | | Claws| cat | 1994-03-17 | | Fluffy | cat | 1993-02-04 | | Fang | dog | 1990-08-27 | | Bowser | dog | 1989-08-31 | | Buffy| dog | 1989-05-13 | | Slim | snake | 1996-04-29 | +--+-++ you need to add an additional field like 'priority' and do ORDER BY priority DESC, species ASC -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: A single number
Mário Gamito schrieb: Hi, I'm trying to get a single number out of a SELECT statement: SELECT comment_approved, COUNT(comment_agent) from wp_comments WHERE comment_agent LIKE '%Linux%' OR comment_approved=0 GROUP by comment_approved But instead i get two rows: |-- |comment_approved | COUNT (comment_agent) | |-- |0| 1 | |-- |1|47 | |-| I've Google about it but found no answer (my bad, probably). What I want is to have just 47 as a result of the SELECT. Any help would be appreciated. just include only the things you want in the SELECT also it seems you require only comment_agent LIKE '%Linux%' and NOT OR comment_approved=0 if you only need the '47' SELECT COUNT(comment_agent) from wp_comments WHERE comment_agent LIKE '%Linux%' GROUP by comment_approved -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query optimization
Joris Kinable schrieb: Optimize query I've got one query, which I would like to improve a lot since it takes very long (24 hours) to execute. Here is the idea: 1. Take the table ipv4_srcipv4_dstport_dst (other rows in this table are not mentioned for clearity) and remove all duplicate tuple's. This is done by subquery 'filter'. 2. The same query is performed by the boxplot query, but this time an aditional group by command is executed, therby calculating a User Defined Function boxplot(row,type) which returns a double value. 3. Finally the results of the query in step 2 are used to select a subset of results from the 'filter' table. 4. As you can see, the subquery 'pF' used in step 2 is identical to the query 'filter'. It's an extreme waste to calculate the same table twice. I've tried to create a temporary table from filter, but unfortunately Mysql doesn't allow you to access a temporary table twice in the same query. I prefer a 1 query answer, instead of creating views, or temporary tables. Is there a way to improve this query, therby improving the execution time? Query: SELECT filter.ipv4_src, filter.ipv4_dst, filter.port_dst, filter.octets FROM ( SELECT pF.ipv4_src, pF.ipv4_dst, boxplot(pF.port_dst,LOW) AS low,boxplot(pF.port_dst,HIGH) AS high FROM ( SELECT ipv4_src, ipv4_dst, port_dst, octets FROM mytable WHERE prot=6 GROUP BY ipv4_src, ipv4_dst, port_dst ORDER BY ipv4_src,ipv4_dst,port_dst ASC ) pF GROUP BY pF.ipv4_src, pF.ipv4_dst HAVING COUNT(filter.port_dst)10 ) boxplot, ( SELECT ipv4_src, ipv4_dst, port_dst, octets FROM mytable WHERE prot=6 GROUP BY ipv4_src, ipv4_dst, port_dst ORDER BY ipv4_src,ipv4_dst,port_dst ASC ) filter WHERE filter.ipv4_src=boxplot.ipv4_src AND filter.ipv4_dst=boxplot.ipv4_dst AND filter.port_dst=boxplot.low AND filter.port_dst=boxplot.low what you are trying to do? and how about formating your query in a human readable way? did you tried EXPLAIN? what type of syntax is this: boxplot(pF.port_dst,LOW) ? -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Handling Special Characters
Jerry Schwartz schrieb: I am having trouble inserting special characters into a table. I am using the MySQL client. I put the following commands into a text file (I'm on WinXP, using Notepad), copy them, and paste them into the MySQL command line client. SET NAMES utf8; CREATE TEMPORARY TABLE `giiexpr_db`.`eo_name_table` ( `eo_name` VARCHAR( 255 ) NOT NULL ) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO eo_name_table (eo_name) VALUES (Associated British Foods Plc (Abf) - Hot Drinks - World); SELECT * FROM eo_name_table; +-+ | eo_name | +-+ | Associated British Foods Plc (Abf) - Hot Drinks - World | +-+ 1 row in set (0.04 sec) That symbol before World is an N-dash, 0x96. This works perfectly. Here's where things go wrong. If instead of pasting these commands into the client, I source the exact same file, I get this: you need to take care of the used charset with the text-file [...] Anyone have any ideas? I was trying to avoid having to write a program to do this. use UTF-8 for text files use SET NAMEs to correctly MySQL what charset you are using -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is there any determined date for mysql 6 release?
legolas schrieb: Hi Thank you for reading my post Is there any scheduled date mysql 6 release? don't know I heard that it is based on falcon and can perform better... based on in the wrong term, MyISAM will still be the default storage engine, it just adds Falcon as a new storage engine http://dev.mysql.com/doc/refman/6.0/en/storage-engine-overview.html -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: generating numbers from other fields
Brian E Boothe schrieb: how can i join three fields Values into one field? so in three select boxes i have date : Projects type 1 - 7 and project type 1 - 6 so the third Filed would be 116200824 generated by the other three fields? please clarify! or did you mean CONCAT() or CONCAT_WS() or as operator: | -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select
Hiep Nguyen schrieb: hi all, i have a table looks like this: ID sDate 1 1997-03-21 2 1997-04-30 3 1997-05-30 4 1998-01-29 5 1998-02-24 6 1998-03-21 7 1999-05-10 8 1999-07-12 9 1999-10-20 10 2000-01-01 11 2000-02-15 12 2000-03-20 13 2000-05-18 how do i construct my select statement so that i only get distinct year? so the above data will return something like this: sDate 2000 1999 1998 1997 did your tried: SELECT DISTINCT YEAR(`sDate`); -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: creating temp file, modifying data and putting into other table
Kerry Frater schrieb: Can someone please advise. I am looking to create a multiuser friendly way of getting a subset number of rows from a table into another whilst making a modification. I thought that this could be done using a temporary table in a batch script that is unique to that session e.g. create temporary table Ttable1 (select * from masterlist where ref='ABCDE'); update Ttable1 set ref='SMI0C001'; insert into sublist select * from Ttable1; drop Ttable1; I know the above syntax doesn't work but it shows the steps I am looking to take. Hope this makes enough sense to be able to answer. did you tried with lowercase table names (ttable1) too? -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: performances and optimization in multiple join queries
[EMAIL PROTECTED] schrieb: Hello everybody! I have a huge query (something similar to a search engine), full of left joins and my testing server takes several minutes each time to output the recordset. I was looking for some (right) way to optimize the search. there were some solutions proposed around, like split in n simpler queries and then merge the results, or create some temporary tables containing the filtered data to let the main query execute without too many iterations... I was wondering if there was anybody here, to suggest the best approach to this kind of performance issues. Thanks, Stefano. p.s.: this is a sample of the query we are talking about.. each sub_table has 2 fields: cs_AN (indexed) and a data field (usually text, not indexed) SELECT field_1 FROM table_A LEFT JOIN sub_table_AUON table_A.mt_AN = sub_table_AU.cs_AN LEFT JOIN sub_table_BNON table_A.mt_AN = sub_table_BN.cs_AN LEFT JOIN sub_table_CNON table_A.mt_AN = sub_table_CN.cs_AN LEFT JOIN sub_table_CSON table_A.mt_AN = sub_table_CS.cs_AN LEFT JOIN sub_table_DTON table_A.mt_AN = sub_table_DT.cs_AN LEFT JOIN sub_table_EMON table_A.mt_AN = sub_table_EM.cs_AN LEFT JOIN sub_table_GNON table_A.mt_AN = sub_table_GN.cs_AN LEFT JOIN sub_table_IDON table_A.mt_AN = sub_table_ID.cs_AN LEFT JOIN sub_table_LAON table_A.mt_AN = sub_table_LA.cs_AN LEFT JOIN sub_table_OION table_A.mt_AN = sub_table_OI.cs_AN LEFT JOIN sub_table_ODON table_A.mt_AN = sub_table_OD.cs_AN LEFT JOIN sub_table_RNON table_A.mt_AN = sub_table_RN.cs_AN LEFT JOIN sub_table_KWON table_A.mt_AN = sub_table_KW.cs_AN WHERE ([...] create a merge table over your sub_tables, search this merge and join table_A to this, and not otherwise -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: does 'disable networking' make mySQL faster?
Daevid Vincent schrieb: Huh? This doesn't make any sense to me. If I'm running a LAMP box (all services on the same box), and my PHP calls out to the mySQL database, I would expect it to use the fastest method possible (since it's LOCAL). If that's sockets, then that's what it should use. I would think this to be a horrible design flaw if the mere fact that I have networking enabled (for the times I want to connect SQLYog to the RDBMS to manually run SQL commands) should NOT slow down or hurt my PHP applications performance in any way, shape or form!? I seriously hope that by turning on networking, mySQL isn't stupid enough to then start trying to connect my PHP application to localhost via 127.0.0.1 or something equally assinine. Please tell me this isn't the case. PHP mysql functions do it like you tell them: 'localhost' uses sockets, 127.0.0.1 uses networking host Can be either a host name or an IP address. Passing the NULL value or the string localhost to this parameter, the local host is assumed. When possible, pipes will be used instead of the TCP/IP protocol. http://php.net/mysqli_connect -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SPAM] - Re: OT: Sun to buy Mysql - Email found in subject
Richard Heyes schrieb: Will this bring good things to MySQL? $800,000,000 tends to bring good things. Hopefully. :-) ??? why, this payment goes to current owners and investors, not to MySQL itself ... but anyway, using Suns structures and power will help of course ... -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ghost Tables
OldManRiver schrieb: I uploaded the mysql console screenshot at: http://www.sitepoint.com/forums/showthread.php?t=525160 this requires a login ... why not just post (copy and paste) the console output, with all the commands and results your where trying to execute? -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: does 'disable networking' make mySQL faster?
Moon's Father schrieb: Because client's submit speed also affects servers's data. please read more carefully, and make no ToFu why should disabling networking speedup clients submit speed? (i am not talking about using sockets, possible this is faster than using networking, but why should disable networking speedup MySQL?) On Jan 14, 2008 6:54 PM, Sebastian Mendel [EMAIL PROTECTED] wrote: Moon's Father schrieb: On Jan 14, 2008 7:00 AM, Daevid Vincent [EMAIL PROTECTED] wrote: I saw this on the PHP list and was wondering if there is any merit to it?! I would be surprised if disabling networking made a difference. I'm only concerned about the added speed. I get the security benefit of course. From: Manuel Lemos [mailto:[EMAIL PROTECTED] Sent: Saturday, January 12, 2008 12:57 PM To: [EMAIL PROTECTED] Subject: Re: [PHP] Re: SMTP vs mail() Every time I install MySQL on the same machine as the Web server, I disable networking to make it use Unix domain sockets, for either greater speed and security. Of course faster than before. Why? Any Source? using sockets is possible faster than using networking, but why should disable networking speedup MySQL? -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: does 'disable networking' make mySQL faster?
Moon's Father schrieb: On Jan 14, 2008 7:00 AM, Daevid Vincent [EMAIL PROTECTED] wrote: I saw this on the PHP list and was wondering if there is any merit to it?! I would be surprised if disabling networking made a difference. I'm only concerned about the added speed. I get the security benefit of course. From: Manuel Lemos [mailto:[EMAIL PROTECTED] Sent: Saturday, January 12, 2008 12:57 PM To: [EMAIL PROTECTED] Subject: Re: [PHP] Re: SMTP vs mail() Every time I install MySQL on the same machine as the Web server, I disable networking to make it use Unix domain sockets, for either greater speed and security. Of course faster than before. Why? Any Source? using sockets is possible faster than using networking, but why should disable networking speedup MySQL? -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Single Column Indexes Vs. Multi Column
C.R.Vegelin schrieb: Sebastian, MySQL uses only one index for searching. As far as I know this applies to all MySQL versions, right ? IMHO, but i heard elselike too, but cannot find any proof in the manual ... -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Single Column Indexes Vs. Multi Column
Anup Shukla schrieb: C.R.Vegelin wrote: Sebastian, MySQL uses only one index for searching. As far as I know this applies to all MySQL versions, right ? AFAIK, MySQL (atleast from version 5.0 onwards) is capable of using multiple indexes. i have heard of something similar too, but cannot find any proof in the manual only counterpart, f.e.: http://dev.mysql.com/doc/refman/6.0/en/order-by-optimization.html In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following: [...] The key used to fetch the rows is not the same as the one used in the ORDER BY: -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Single Column Indexes Vs. Multi Column
Perrin Harkins schrieb: On Jan 11, 2008 7:22 AM, Sebastian Mendel [EMAIL PROTECTED] wrote: i can only find one source in the manual, where MySQL is using more than on index: http://dev.mysql.com/doc/refman/6.0/en/index-merge-optimization.html Uh, how many sources do you need? 1? It uses multiple indexes, just like it says. This has been true since 5.0. yes, i can read ... ;-) -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Single Column Indexes Vs. Multi Column
Sebastian Mendel schrieb: Anup Shukla schrieb: C.R.Vegelin wrote: Sebastian, MySQL uses only one index for searching. As far as I know this applies to all MySQL versions, right ? AFAIK, MySQL (atleast from version 5.0 onwards) is capable of using multiple indexes. i have heard of something similar too, but cannot find any proof in the manual only counterpart, f.e.: http://dev.mysql.com/doc/refman/6.0/en/order-by-optimization.html In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following: [...] The key used to fetch the rows is not the same as the one used in the ORDER BY: i can only find one source in the manual, where MySQL is using more than on index: http://dev.mysql.com/doc/refman/6.0/en/index-merge-optimization.html -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why in procedure truncate table do not reset auto_increment?
x schrieb: thanks may you point out which chapter says? From manual I get the following answer agaist to my result(my server version 5.0.45), For |InnoDB| before version 5.0.3, |TRUNCATE TABLE| is mapped to |DELETE|, so there is no difference. Starting with MySQL 5.0.3, fast |TRUNCATE TABLE| is available. However, the operation is still mapped to |DELETE| if there are foreign key constraints that reference the table. (When fast truncate is used, it resets any |AUTO_INCREMENT| counter. From MySQL 5.0.13 on, the |AUTO_INCREMENT| counter is reset by |TRUNCATE TABLE|, regardless of whether there is a foreign key constraint.) The table handler does not remember the last used |AUTO_INCREMENT| value, but starts counting from the beginning. This is true even for |MyISAM| and |InnoDB|, which normally do not reuse sequence values. See: http://dev.mysql.com/doc/refman/5.0/en/truncate.html http://dev.mysql.com/doc/refman/5.1/en/truncate.html oh sorry, yes, i was confused, somehow i mixed some old behavior and InnoDB behavior ... Martijn Tonies, sorry too -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Search for column value in a string variable?
Barry Newton schrieb:
OK, never mind. I finally found the 'locate' function. I knew it had
to be there somewhere!
or just:
... `column` IN ('name1', 'name2', 'name2', ...)
--
Sebastian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
