Re: view query is slow
I think I'll just make a nightly process run that drops and then recreates the
table, unless someone has a workable idea of how to make this view query-able.
Thanks!
Jim McNeely
On Aug 23, 2012, at 2:06 PM, James W. McNeely wrote:
> This didn't help, but good try!
>
> Jim McNeely
>
> On Aug 23, 2012, at 12:27 PM, Martin Gainty wrote:
>
>>
>> If memory serves predicates convert strings to column-data-type (in your
>> case DATE) this *should* help
>> WHERE dateexam = STR_TO_DATE('2012-08-13','%Y-%m-%d')
>> does this help?
>> Martin
>> __
>> Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
>>
>> Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
>> Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte
>> Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht
>> dient lediglich dem Austausch von Informationen und entfaltet keine
>> rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
>> E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
>> Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
>> destinataire prévu, nous te demandons avec bonté que pour satisfaire
>> informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie
>> de ceci est interdite. Ce message sert à l'information seulement et n'aura
>> pas n'importe quel effet légalement obligatoire. Étant donné que les email
>> peuvent facilement être sujets à la manipulation, nous ne pouvons accepter
>> aucune responsabilité pour le contenu fourni.
>>
>>
>>> From: j...@newcenturydata.com
>>> Subject: view query is slow
>>> Date: Thu, 23 Aug 2012 11:30:17 -0700
>>> To: mysql@lists.mysql.com
>>>
>>> I am working on a view based on this query:
>>>
>>> ===
>>> SELECT
>>> -- Patient Info
>>> p.IdPatient,
>>> p.IdLastword MRN,
>>> p.NameLast,
>>> p.NameFirst,
>>> p.Addr1,
>>> p.Addr2,
>>> p.AddrCity,
>>> p.AddrState,
>>> p.AddrZip,
>>> p.Gender,
>>> p.DateOfBirth,
>>> -- Provider Info
>>> af.IdAffil,
>>> af.PractName,
>>> af.OfficeName,
>>> -- Exam Info
>>> e.IdExam,
>>> e.dateexam,
>>> a.WorkArea dept,
>>> a.Room location,
>>> e.ProcModeCode,
>>> e.ProcName,
>>> e.IdRefSite,
>>> ec.IdCPT,
>>> e.zzk exam_zzk,
>>> ec.zzk examcpt_zzk
>>> FROM patient_ p
>>> LEFT JOIN exams e ON e.IdPatient = p.IdPatient
>>> LEFT JOIN Examcpt_ ec ON (e.IdExam = ec.IdExam AND '1' = Quantity)
>>> LEFT JOIN Copy_ c ON CONCAT(e.IdAppt , '0') = c.IdApptType
>>> LEFT JOIN Appt_ a ON e.IdAppt = a.IdAppt
>>> LEFT JOIN Affil_ af ON c.IdPractAffil = af.IdAffil
>>> WHERE
>>> p.AddrState = 'WA'
>>> AND e.statusnumber = '4'
>>> AND e.IdRefSite <> 'S50'
>>> AND e.IdRefSite <> 'S51'
>>> AND e.IdREfSite <> 'S63'
>>> AND p.DateOfBirth < DATE_ADD(CURDATE(), INTERVAL '-2' MONTH)
>>> AND a.zzk IS NOT NULL
>>>
>>>
>>> If I run this query itself (not in the view), and add this:
>>>
>>> AND e.dateexam = '2012-08-13'
>>>
>>> it runs like lightning, super fast. But if I run the query against the
>>> view, for example "SELECT * FROM exam_view WHERE dateexam = '2012-08-13'
>>>
>>> It is so glacially slow that I end up having to kill the query. What is
>>> going on, and how can I fix this?
>>>
>>> Jim McNeely
>>> Northwest Radiologists
>>> Senior Database Programmer
>>> 360-788-9022 desk
>>> 360-303-3332 mobile
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:http://lists.mysql.com/mysql
>>>
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: view query is slow
This didn't help, but good try!
Jim McNeely
On Aug 23, 2012, at 12:27 PM, Martin Gainty wrote:
>
> If memory serves predicates convert strings to column-data-type (in your case
> DATE) this *should* help
> WHERE dateexam = STR_TO_DATE('2012-08-13','%Y-%m-%d')
> does this help?
> Martin
> __
> Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
>
> Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
> sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
> oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich
> dem Austausch von Informationen und entfaltet keine rechtliche
> Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen
> wir keine Haftung fuer den Inhalt uebernehmen.
> Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
> destinataire prévu, nous te demandons avec bonté que pour satisfaire informez
> l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci
> est interdite. Ce message sert à l'information seulement et n'aura pas
> n'importe quel effet légalement obligatoire. Étant donné que les email
> peuvent facilement être sujets à la manipulation, nous ne pouvons accepter
> aucune responsabilité pour le contenu fourni.
>
>
>> From: j...@newcenturydata.com
>> Subject: view query is slow
>> Date: Thu, 23 Aug 2012 11:30:17 -0700
>> To: mysql@lists.mysql.com
>>
>> I am working on a view based on this query:
>>
>> ===
>> SELECT
>> -- Patient Info
>> p.IdPatient,
>> p.IdLastword MRN,
>> p.NameLast,
>> p.NameFirst,
>> p.Addr1,
>> p.Addr2,
>> p.AddrCity,
>> p.AddrState,
>> p.AddrZip,
>> p.Gender,
>> p.DateOfBirth,
>> -- Provider Info
>> af.IdAffil,
>> af.PractName,
>> af.OfficeName,
>> -- Exam Info
>> e.IdExam,
>> e.dateexam,
>> a.WorkArea dept,
>> a.Room location,
>> e.ProcModeCode,
>> e.ProcName,
>> e.IdRefSite,
>> ec.IdCPT,
>> e.zzk exam_zzk,
>> ec.zzk examcpt_zzk
>> FROM patient_ p
>> LEFT JOIN exams e ON e.IdPatient = p.IdPatient
>> LEFT JOIN Examcpt_ ec ON (e.IdExam = ec.IdExam AND '1' = Quantity)
>> LEFT JOIN Copy_ c ON CONCAT(e.IdAppt , '0') = c.IdApptType
>> LEFT JOIN Appt_ a ON e.IdAppt = a.IdAppt
>> LEFT JOIN Affil_ af ON c.IdPractAffil = af.IdAffil
>> WHERE
>> p.AddrState = 'WA'
>> AND e.statusnumber = '4'
>> AND e.IdRefSite <> 'S50'
>> AND e.IdRefSite <> 'S51'
>> AND e.IdREfSite <> 'S63'
>> AND p.DateOfBirth < DATE_ADD(CURDATE(), INTERVAL '-2' MONTH)
>> AND a.zzk IS NOT NULL
>>
>>
>> If I run this query itself (not in the view), and add this:
>>
>> AND e.dateexam = '2012-08-13'
>>
>> it runs like lightning, super fast. But if I run the query against the view,
>> for example "SELECT * FROM exam_view WHERE dateexam = '2012-08-13'
>>
>> It is so glacially slow that I end up having to kill the query. What is
>> going on, and how can I fix this?
>>
>> Jim McNeely
>> Northwest Radiologists
>> Senior Database Programmer
>> 360-788-9022 desk
>> 360-303-3332 mobile
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:http://lists.mysql.com/mysql
>>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: view query is slow
Thanks for the responses to everyone! Here is the result for the explains. view query= explain select * from admin_exam_view where dateexam = '2012-08-13'; ++-+---+--+-++-+++-+ | id | select_type | table | type | possible_keys | key| key_len | ref| rows | Extra | ++-+---+--+-++-+++-+ | 1 | SIMPLE | p | ALL | DOB | NULL | NULL| NULL | 281123 | Using where | | 1 | SIMPLE | e | ref | IdPatient | IdPatient | 99 | RIS_archive.p.IdPatient| 6 | Using where | | 1 | SIMPLE | ec| ref | Quantity,IdExam | IdExam | 138 | RIS_archive.e.IdExam | 3 | | | 1 | SIMPLE | c | ref | IdApptType | IdApptType | 51 | func | 1 | | | 1 | SIMPLE | a | ref | IdAppt | IdAppt | 99 | RIS_archive.e.IdAppt | 1 | Using where | | 1 | SIMPLE | af| ref | IdAffil | IdAffil| 93 | RIS_archive.c.IdPractAffil | 1 | | ++-+---+--+-++-+++-+ =direct query= ++-+---+--+++-+++-+ | id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra | ++-+---+--+++-+++-+ | 1 | SIMPLE | p | ALL | IdPatient_UNIQUE,IdPatient,DOB | NULL | NULL| NULL | 281123 | Using where | | 1 | SIMPLE | e | ref | IdPatient,statusnumber,IdAppt | IdPatient | 99 | RIS_archive.p.IdPatient| 6 | Using where | | 1 | SIMPLE | a | ref | PRIMARY,zzk,IdAppt | IdAppt | 99 | RIS_archive.e.IdAppt | 1 | Using where | | 1 | SIMPLE | ec| ref | Quantity,IdExam| IdExam | 138 | RIS_archive.e.IdExam | 3 | | | 1 | SIMPLE | c | ref | IdApptType | IdApptType | 51 | func | 1 | | | 1 | SIMPLE | af| ref | IdAffil| IdAffil | 93 | RIS_archive.c.IdPractAffil | 1 | | ++-+---+--+++-+++-+ I can't tell any practical difference between the two. Jim McNeely On Aug 23, 2012, at 12:39 PM, Shawn Green wrote: > On 8/23/2012 2:30 PM, James W. McNeely wrote: >> I am working on a view based on this query: >> >> === >> SELECT >> -- Patient Info >> p.IdPatient, >> p.IdLastword MRN, >> p.NameLast, >> p.NameFirst, >> p.Addr1, >> p.Addr2, >> p.AddrCity, >> p.AddrState, >> p.AddrZip, >> p.Gender, >> p.DateOfBirth, >> -- Provider Info >> af.IdAffil, >> af.PractName, >> af.OfficeName, >> -- Exam Info >> e.IdExam, >> e.dateexam, >> a.WorkArea dept, >> a.Room location, >> e.ProcModeCode, >> e.ProcName, >> e.IdRefSite, >> ec.IdCPT, >> e.zzk exam_zzk, >> ec.zzk examcpt_zzk >> FROM patient_ p >> LEFT JOIN exams e ON e.IdPatient = p.IdPatient >> LEFT JOIN Examcpt_ ec ON (e.IdExam = ec.IdExam AND '1' = Quantity) >> LEFT JOIN Copy_ c ON CONCAT(e.IdAppt , '0') = c.IdApptType >> LEFT JOIN Appt_ a ON e.IdAppt = a.IdAppt >> LEFT JOIN Affil_ af ON c.IdPractAffil = af.IdAffil >> WHERE >> p.AddrState = 'WA' >> AND e.statusnumber = '4' >> AND e.IdRefSite <> 'S50' >> AND e.IdRefSite <> 'S51' >> AND e.IdREfSite <> 'S63' >> AND p.DateOfBirth < DATE_ADD(CURDATE(), INTERVAL '-2' MONTH) >> AND a.zzk IS NOT NULL >> >> >> If I run this query itself (not in the view), and add this: >> >> AND e.dateexam = '2012-08-13' >> >> it runs like lightning, super fast. But if I run the query against the view, >> for example "SELECT * FROM exam_view WHERE dateexam = '2012-08-13' >> >> It is so glacially slow that I end up having to kill the query. What is >> going on, and how can I fix this? >> > > Look at the two EXPLAINs. I believe that when you run the query directly, you > get to optimize that term into the execution of the view. When you run it > through the view, the ALGORITHM is set to force the view to materialize all > of the rows in the query, then scan those to find the rows that match your > condition. > > When you execute the
Re: view query is slow
On Thu, Aug 23, 2012 at 11:30:17AM -0700, James W. McNeely wrote: > I am working on a view based on this query: > > === > SELECT > -- Patient Info > p.IdPatient, > p.IdLastword MRN, > p.NameLast, > p.NameFirst, > p.Addr1, > p.Addr2, > p.AddrCity, > p.AddrState, > p.AddrZip, > p.Gender, > p.DateOfBirth, > -- Provider Info > af.IdAffil, > af.PractName, > af.OfficeName, > -- Exam Info > e.IdExam, > e.dateexam, > a.WorkArea dept, > a.Room location, > e.ProcModeCode, > e.ProcName, > e.IdRefSite, > ec.IdCPT, > e.zzk exam_zzk, > ec.zzk examcpt_zzk > FROM patient_ p > LEFT JOIN exams e ON e.IdPatient = p.IdPatient > LEFT JOIN Examcpt_ ec ON (e.IdExam = ec.IdExam AND '1' = Quantity) > LEFT JOIN Copy_ c ON CONCAT(e.IdAppt , '0') = c.IdApptType > LEFT JOIN Appt_ a ON e.IdAppt = a.IdAppt > LEFT JOIN Affil_ af ON c.IdPractAffil = af.IdAffil > WHERE > p.AddrState = 'WA' > AND e.statusnumber = '4' > AND e.IdRefSite <> 'S50' > AND e.IdRefSite <> 'S51' > AND e.IdREfSite <> 'S63' > AND p.DateOfBirth < DATE_ADD(CURDATE(), INTERVAL '-2' MONTH) > AND a.zzk IS NOT NULL > > > If I run this query itself (not in the view), and add this: > > AND e.dateexam = '2012-08-13' > > it runs like lightning, super fast. But if I run the query against the view, > for example "SELECT * FROM exam_view WHERE dateexam = '2012-08-13' > > It is so glacially slow that I end up having to kill the query. What is going > on, and how can I fix this? What does EXPLAIN show, for both queries? Are they different? I don't see any obvious reason why query with the VIEW should be slower. One possible reason why queries through a VIEW are slow is that the view is materialized into a temporary table (look for 'DERIVED' in EXPLAIN output) and condition e.dateexam = '2012-08-13' is only applied when reading from the temporary table. However, I don't see a reason why a view for the above query would be materialized. It should be merged. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: view query is slow
That is an example of where VIEWs screw up optimizations. Rumor has it that 5.6.6 might have improvements. Probably inefficient: ON CONCAT(e.IdAppt , '0') = c.IdApptType p might benefit from INDEX(AddrState, DateOfBirth) SHOW CREATE TABLE (for each table) EXPLAIN SELECT (with and without VIEW) We might have more comments/suggestions. > -Original Message- > From: James W. McNeely [mailto:j...@newcenturydata.com] > Sent: Thursday, August 23, 2012 11:30 AM > To: mysql@lists.mysql.com > Subject: view query is slow > > I am working on a view based on this query: > > === > SELECT > -- Patient Info > p.IdPatient, > p.IdLastword MRN, > p.NameLast, > p.NameFirst, > p.Addr1, > p.Addr2, > p.AddrCity, > p.AddrState, > p.AddrZip, > p.Gender, > p.DateOfBirth, > -- Provider Info > af.IdAffil, > af.PractName, > af.OfficeName, > -- Exam Info > e.IdExam, > e.dateexam, > a.WorkArea dept, > a.Room location, > e.ProcModeCode, > e.ProcName, > e.IdRefSite, > ec.IdCPT, > e.zzk exam_zzk, > ec.zzk examcpt_zzk > FROM patient_ p > LEFT JOIN exams e ON e.IdPatient = p.IdPatient LEFT JOIN Examcpt_ ec ON > (e.IdExam = ec.IdExam AND '1' = Quantity) LEFT JOIN Copy_ c ON > CONCAT(e.IdAppt , '0') = c.IdApptType LEFT JOIN Appt_ a ON e.IdAppt = > a.IdAppt LEFT JOIN Affil_ af ON c.IdPractAffil = af.IdAffil WHERE > p.AddrState = 'WA' > AND e.statusnumber = '4' > AND e.IdRefSite <> 'S50' > AND e.IdRefSite <> 'S51' > AND e.IdREfSite <> 'S63' > AND p.DateOfBirth < DATE_ADD(CURDATE(), INTERVAL '-2' MONTH) AND a.zzk > IS NOT NULL > > If I run this query itself (not in the view), and add this: > > AND e.dateexam = '2012-08-13' > > it runs like lightning, super fast. But if I run the query against the > view, for example "SELECT * FROM exam_view WHERE dateexam = '2012-08- > 13' > > It is so glacially slow that I end up having to kill the query. What is > going on, and how can I fix this? > > Jim McNeely > Northwest Radiologists > Senior Database Programmer > 360-788-9022 desk > 360-303-3332 mobile > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: view query is slow
On 8/23/2012 2:30 PM, James W. McNeely wrote: I am working on a view based on this query: === SELECT -- Patient Info p.IdPatient, p.IdLastword MRN, p.NameLast, p.NameFirst, p.Addr1, p.Addr2, p.AddrCity, p.AddrState, p.AddrZip, p.Gender, p.DateOfBirth, -- Provider Info af.IdAffil, af.PractName, af.OfficeName, -- Exam Info e.IdExam, e.dateexam, a.WorkArea dept, a.Room location, e.ProcModeCode, e.ProcName, e.IdRefSite, ec.IdCPT, e.zzk exam_zzk, ec.zzk examcpt_zzk FROM patient_ p LEFT JOIN exams e ON e.IdPatient = p.IdPatient LEFT JOIN Examcpt_ ec ON (e.IdExam = ec.IdExam AND '1' = Quantity) LEFT JOIN Copy_ c ON CONCAT(e.IdAppt , '0') = c.IdApptType LEFT JOIN Appt_ a ON e.IdAppt = a.IdAppt LEFT JOIN Affil_ af ON c.IdPractAffil = af.IdAffil WHERE p.AddrState = 'WA' AND e.statusnumber = '4' AND e.IdRefSite <> 'S50' AND e.IdRefSite <> 'S51' AND e.IdREfSite <> 'S63' AND p.DateOfBirth < DATE_ADD(CURDATE(), INTERVAL '-2' MONTH) AND a.zzk IS NOT NULL If I run this query itself (not in the view), and add this: AND e.dateexam = '2012-08-13' it runs like lightning, super fast. But if I run the query against the view, for example "SELECT * FROM exam_view WHERE dateexam = '2012-08-13' It is so glacially slow that I end up having to kill the query. What is going on, and how can I fix this? Look at the two EXPLAINs. I believe that when you run the query directly, you get to optimize that term into the execution of the view. When you run it through the view, the ALGORITHM is set to force the view to materialize all of the rows in the query, then scan those to find the rows that match your condition. When you execute the query manually, you are getting the benefits of peformance as you would have for ALGORITHM=MERGE in the VIEW. However since you are not getting those benefits, it looks like you are in an ALGORITHM=TEMPTABLE situation. http://dev.mysql.com/doc/refman/5.5/en/view-algorithms.html The explain plans will clearly show which situation you are in. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: view query is slow
If memory serves predicates convert strings to column-data-type (in your case
DATE) this *should* help
WHERE dateexam = STR_TO_DATE('2012-08-13','%Y-%m-%d')
does this help?
Martin
__
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est
interdite. Ce message sert à l'information seulement et n'aura pas n'importe
quel effet légalement obligatoire. Étant donné que les email peuvent facilement
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité
pour le contenu fourni.
> From: j...@newcenturydata.com
> Subject: view query is slow
> Date: Thu, 23 Aug 2012 11:30:17 -0700
> To: mysql@lists.mysql.com
>
> I am working on a view based on this query:
>
> ===
> SELECT
> -- Patient Info
> p.IdPatient,
> p.IdLastword MRN,
> p.NameLast,
> p.NameFirst,
> p.Addr1,
> p.Addr2,
> p.AddrCity,
> p.AddrState,
> p.AddrZip,
> p.Gender,
> p.DateOfBirth,
> -- Provider Info
> af.IdAffil,
> af.PractName,
> af.OfficeName,
> -- Exam Info
> e.IdExam,
> e.dateexam,
> a.WorkArea dept,
> a.Room location,
> e.ProcModeCode,
> e.ProcName,
> e.IdRefSite,
> ec.IdCPT,
> e.zzk exam_zzk,
> ec.zzk examcpt_zzk
> FROM patient_ p
> LEFT JOIN exams e ON e.IdPatient = p.IdPatient
> LEFT JOIN Examcpt_ ec ON (e.IdExam = ec.IdExam AND '1' = Quantity)
> LEFT JOIN Copy_ c ON CONCAT(e.IdAppt , '0') = c.IdApptType
> LEFT JOIN Appt_ a ON e.IdAppt = a.IdAppt
> LEFT JOIN Affil_ af ON c.IdPractAffil = af.IdAffil
> WHERE
> p.AddrState = 'WA'
> AND e.statusnumber = '4'
> AND e.IdRefSite <> 'S50'
> AND e.IdRefSite <> 'S51'
> AND e.IdREfSite <> 'S63'
> AND p.DateOfBirth < DATE_ADD(CURDATE(), INTERVAL '-2' MONTH)
> AND a.zzk IS NOT NULL
>
>
> If I run this query itself (not in the view), and add this:
>
> AND e.dateexam = '2012-08-13'
>
> it runs like lightning, super fast. But if I run the query against the view,
> for example "SELECT * FROM exam_view WHERE dateexam = '2012-08-13'
>
> It is so glacially slow that I end up having to kill the query. What is going
> on, and how can I fix this?
>
> Jim McNeely
> Northwest Radiologists
> Senior Database Programmer
> 360-788-9022 desk
> 360-303-3332 mobile
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
Re: view doesn't refresh inside transaction
Ingo, On Wed, Mar 4, 2009 at 8:49 AM, Ingo Weiss wrote: > Hi all, > > I have a view that is joining two base tables. I can update through > the view, but insert only through the base tables. Now I am having the > problem that seems to boil down to the following: When I insert into > the base tables inside a transaction, the view doesn't seem to update. > Only after the transaction is committed does the row appear in the > view. Now I would like to avoid having to commit the transaction at > that point. Is there any way to force a view to refresh inside a > transaction? Are you accessing the view and doing the inserts in separate transactions? -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: view irregularities
Shawn, On Feb 13, 2008, at 9:12 AM, Shawn Green wrote: Lev Lvovsky wrote: I'm running into a difficult to reproduce problem with a view which is similar to the following: CREATE TABLE Common ( COMMON_ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, COMMON_NAME VARCHAR(50), UNIQUE(COMMON_NAME) ) ENGINE = InnoDB; CREATE TABLE Parent ( PARENT_ID VARCHAR(50) NOT NULL, PARENT_NAME VARCHAR(50) NOT NULL, PARENT_COMMON_ID INT UNSIGNED, PRIMARY KEY (PARENT_ID, PARENT_COMMON_ID), FOREIGN KEY (PARENT_COMMON_ID) REFERENCES Common(COMMON_ID) ) ENGINE = InnoDB; CREATE TABLE Child ( CHILD_IDBINARY(20) NOT NULL PRIMARY KEY, PARENT_ID VARCHAR(50) NOT NULL, CHILD_NAME VARCHAR(50), CHILD_COMMON_ID INT UNSIGNED, FOREIGN KEY (PARENT_ID) REFERENCES Parent(PARENT_ID), FOREIGN KEY (CHILD_COMMON_ID) REFERENCES Common(COMMON_ID) ) ENGINE = InnoDB; DROP VIEW IF EXISTS BrokenView; CREATE VIEW BrokenView AS SELECT Child.* FROM Child LEFT JOIN Parent USING(PARENT_ID) WHERE Child.CHILD_COMMON_ID = Parent.PARENT_COMMON_ID; DROP VIEW IF EXISTS WorkingView; CREATE VIEW WorkingView AS SELECT Child.*, Parent.PARENT_NAME, Parent.PARENT_COMMON_ID FROM Child LEFT JOIN Parent ON (Child.PARENT_ID = Parent.PARENT_ID AND Child.CHILD_COMMON_ID = Parent.PARENT_COMMON_ID); Though the example cited above does not cause the problems that I'm running into, the table structure is similar. Specifically the fact that I'm doing a "WHERE ..." in the BrokenView vs WorkingView is seemingly the difference between getting rows returned and not. And this is exactly the cause of your problems. When you build a query that optionally includes a table, you get your results back in stages. The one stage of the query takes your LEFT JOIN and builds a list of matching rows. A later stage evaluates the terms in your WHERE clause. In this case in order to evaluate the WHERE clause, you force the existence of the rows from the `Parent` table just as if you had written an INNER JOIN. If there were no rows (no values) from the `Parent` table then the WHERE clause will evaluate as FALSE and those rows will not be returned. By putting both terms into the ON clause of your LEFT JOIN, you make it possible to have non-matched rows in your final result. Below is SQL which demonstrates the bug. Apparently this is an issue with the optimizer, and the temporary fix is the "ALGORITHM=TEMPTABLE" as I'd mentioned in my previous email. If this is not a bug, I would think that the behavior would be consistent before and after a 'flush table ...' command. -lev DROP DATABASE TestDB; CREATE DATABASE TestDB; USE TestDB; CREATE TABLE Common ( COMMON_ID INT UNSIGNED NOT NULL PRIMARY KEY ) ENGINE=InnoDB; CREATE TABLE Parent1 ( PARENT_ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, EXT_PARENT_ID INT UNSIGNED NOT NULL, PARENT_NAME INT UNSIGNED NOT NULL, COMMON_ID INT UNSIGNED NOT NULL, FOREIGN KEY (COMMON_ID) REFERENCES Common(COMMON_ID) ON DELETE RESTRICT ) ENGINE=InnoDB; CREATE TABLE Child1 ( CHILD_ID INT UNSIGNED NOT NULL PRIMARY KEY, PARENT_ID INT UNSIGNED NOT NULL, COMMON_ID INT UNSIGNED NOT NULL, FOREIGN KEY (PARENT_ID) REFERENCES Parent1(PARENT_ID) ON DELETE CASCADE, FOREIGN KEY (COMMON_ID) REFERENCES Common(COMMON_ID) ON DELETE RESTRICT ) ENGINE=InnoDB; CREATE TABLE Parent2 ( PARENT_ID INT UNSIGNED NOT NULL, PARENT_NAME INT UNSIGNED NOT NULL, COMMON_ID INT UNSIGNED NOT NULL, PRIMARY KEY (PARENT_ID, COMMON_ID), FOREIGN KEY (COMMON_ID) REFERENCES Common(COMMON_ID) ON DELETE RESTRICT ) ENGINE=InnoDB; CREATE TABLE Child2 ( CHILD_ID INT UNSIGNED NOT NULL PRIMARY KEY, PARENT_ID INT UNSIGNED NOT NULL, COMMON_ID INT UNSIGNED NOT NULL, FOREIGN KEY (PARENT_ID, COMMON_ID) REFERENCES Parent2(PARENT_ID, COMMON_ID) ON DELETE CASCADE, FOREIGN KEY (COMMON_ID) REFERENCES Common(COMMON_ID) ON DELETE RESTRICT ) ENGINE=InnoDB; CREATE VIEW ViewParent2 AS SELECT Parent2.* FROM Parent2; CREATE VIEW ViewChild2 AS SELECT Child2.* FROM Child2 LEFT JOIN ViewParent2 USING(PARENT_ID) WHERE Child2.COMMON_ID = ViewParent2.COMMON_ID; INSERT INTO Common SET COMMON_ID = 1; INSERT INTO Parent1 SET COMMON_ID = 1, EXT_PARENT_ID = 1, PARENT_NAME = 1; SELECT LAST_INSERT_ID() INTO @_parent_id; INSERT INTO Child1 SET COMMON_ID = 1, PARENT_ID = @_parent_id, CHILD_ID = 1; SELECT 1 FROM Child2 LEFT JOIN Child1 ON Child1.CHILD_ID = Child2.CHILD_ID AND Child1.COMMON_ID = Child2.COMMON_ID LEFT JOIN Parent2 ON Parent2.PARENT_ID = Child2.PARENT_ID AND Parent2.COMMON_ID = Child2.COMMON_ID WHERE ( Child1.CHILD_ID IS NOT NULL ) AND ( Child2.CHILD_ID = 1 ); INSERT INTO Parent2 SET PARENT_ID = 1, COMMON_ID = 1, PARENT_NAME = 1; INSERT INTO Child2 SET CHILD_ID = 1, PARENT_ID = 1, COMMON_ID = 1; -- Technically Child2, and ViewChild2 have the same data in them. However in selecting from ViewChild2 -- on the pkey returns 0 rows, whereas the base table returns the correct
Re: view irregularities
Lev Lvovsky wrote: I'm running into a difficult to reproduce problem with a view which is similar to the following: CREATE TABLE Common ( COMMON_ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, COMMON_NAME VARCHAR(50), UNIQUE(COMMON_NAME) ) ENGINE = InnoDB; CREATE TABLE Parent ( PARENT_ID VARCHAR(50) NOT NULL, PARENT_NAME VARCHAR(50) NOT NULL, PARENT_COMMON_ID INT UNSIGNED, PRIMARY KEY (PARENT_ID, PARENT_COMMON_ID), FOREIGN KEY (PARENT_COMMON_ID) REFERENCES Common(COMMON_ID) ) ENGINE = InnoDB; CREATE TABLE Child ( CHILD_IDBINARY(20) NOT NULL PRIMARY KEY, PARENT_ID VARCHAR(50) NOT NULL, CHILD_NAME VARCHAR(50), CHILD_COMMON_ID INT UNSIGNED, FOREIGN KEY (PARENT_ID) REFERENCES Parent(PARENT_ID), FOREIGN KEY (CHILD_COMMON_ID) REFERENCES Common(COMMON_ID) ) ENGINE = InnoDB; DROP VIEW IF EXISTS BrokenView; CREATE VIEW BrokenView AS SELECT Child.* FROM Child LEFT JOIN Parent USING(PARENT_ID) WHERE Child.CHILD_COMMON_ID = Parent.PARENT_COMMON_ID; DROP VIEW IF EXISTS WorkingView; CREATE VIEW WorkingView AS SELECT Child.*, Parent.PARENT_NAME, Parent.PARENT_COMMON_ID FROM Child LEFT JOIN Parent ON (Child.PARENT_ID = Parent.PARENT_ID AND Child.CHILD_COMMON_ID = Parent.PARENT_COMMON_ID); Though the example cited above does not cause the problems that I'm running into, the table structure is similar. Specifically the fact that I'm doing a "WHERE ..." in the BrokenView vs WorkingView is seemingly the difference between getting rows returned and not. And this is exactly the cause of your problems. When you build a query that optionally includes a table, you get your results back in stages. The one stage of the query takes your LEFT JOIN and builds a list of matching rows. A later stage evaluates the terms in your WHERE clause. In this case in order to evaluate the WHERE clause, you force the existence of the rows from the `Parent` table just as if you had written an INNER JOIN. If there were no rows (no values) from the `Parent` table then the WHERE clause will evaluate as FALSE and those rows will not be returned. By putting both terms into the ON clause of your LEFT JOIN, you make it possible to have non-matched rows in your final result. -- Shawn Green, Support Engineer MySQL Inc., USA, www.mysql.com Office: Blountville, TN __ ___ ___ __ / |/ /_ __/ __/ __ \/ / / /|_/ / // /\ \/ /_/ / /__ /_/ /_/\_, /___/\___\_\___/ <___/ Join the Quality Contribution Program Today! http://dev.mysql.com/qualitycontribution.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: VIEW and ROW_NUMBER
I always use stored procedure when I meet such demand.
On Jan 3, 2008 11:09 PM, Baron Schwartz <[EMAIL PROTECTED]> wrote:
> Hi,
>
> On Jan 3, 2008 9:28 AM, GF <[EMAIL PROTECTED]> wrote:
> > I need to create a view, about a ranking.
> > The select from which I generate the view has a "ORDER BY" and I need
> > to have a column in that select that shows the position of the object
> > in that ranking.
> >
> > I have searched on google, and I have found that it's possibile to do
> > it using the SET command and using variables.. but I don't think in a
> > VIEW I can use variables and SET.
>
> Correct. You can use ordinary SQL, like this:
>
> create table fruits (
>type varchar(10) not null,
>variety varchar(20) not null,
>primary key(type, variety));
>
> insert into fruits values
> ('apple', 'gala'),
> ('apple', 'fuji'),
> ('apple', 'limbertwig'),
> ('orange', 'valencia'),
> ('orange', 'navel'),
> ('pear', 'bradford'),
> ('pear', 'bartlett'),
> ('cherry', 'bing'),
> ('cherry', 'chelan');
>
> select l.type, l.variety, count(*) as num
> from fruits as l
> left outer join fruits as r
>on l.type = r.type
>and l.variety >= r.variety
> group by l.type, l.variety;
>
> +++-+
> | type | variety| num |
> +++-+
> | apple | fuji | 1 |
> | apple | gala | 2 |
> | apple | limbertwig | 3 |
> | cherry | bing | 1 |
> | cherry | chelan | 2 |
> | orange | navel | 1 |
> | orange | valencia | 2 |
> | pear | bartlett | 1 |
> | pear | bradford | 2 |
> +++-+
>
> It is not very efficient on large data sets, though. What about a
> stored procedure, or a UDF (a C UDF, not a SQL stored function)? Can
> you use either of those?
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
I'm a mysql DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn
Re: VIEW and ROW_NUMBER
Hi,
On Jan 3, 2008 9:28 AM, GF <[EMAIL PROTECTED]> wrote:
> I need to create a view, about a ranking.
> The select from which I generate the view has a "ORDER BY" and I need
> to have a column in that select that shows the position of the object
> in that ranking.
>
> I have searched on google, and I have found that it's possibile to do
> it using the SET command and using variables.. but I don't think in a
> VIEW I can use variables and SET.
Correct. You can use ordinary SQL, like this:
create table fruits (
type varchar(10) not null,
variety varchar(20) not null,
primary key(type, variety));
insert into fruits values
('apple', 'gala'),
('apple', 'fuji'),
('apple', 'limbertwig'),
('orange', 'valencia'),
('orange', 'navel'),
('pear', 'bradford'),
('pear', 'bartlett'),
('cherry', 'bing'),
('cherry', 'chelan');
select l.type, l.variety, count(*) as num
from fruits as l
left outer join fruits as r
on l.type = r.type
and l.variety >= r.variety
group by l.type, l.variety;
+++-+
| type | variety| num |
+++-+
| apple | fuji | 1 |
| apple | gala | 2 |
| apple | limbertwig | 3 |
| cherry | bing | 1 |
| cherry | chelan | 2 |
| orange | navel | 1 |
| orange | valencia | 2 |
| pear | bartlett | 1 |
| pear | bradford | 2 |
+++-+
It is not very efficient on large data sets, though. What about a
stored procedure, or a UDF (a C UDF, not a SQL stored function)? Can
you use either of those?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: View pocedures/backup procedures
In the last episode (Jul 10), Andrey Dmitriev said: > How do I view and backup my procedural code? > > In oracle it would be something like > Select text from user_source where name='MY_PROCEDURE' order by line; > > I did mysqldump, and didn't see any of the functions or procedures > created. > > In Oracle they'd be backed up either via RMAN or EXP. You need to add the --routines flag to mysqldump. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: View select results
>One last thing: you set, at first, a parameter called @prev with Null
>(' ') value: right?
No, I set it to a string containing one space char. Use anything that
does not occur as data in the column.
>And, after, you use, instead IF ELSE statement, another syntax: is it
>trinary operator? if yes, why it not is in the online MySQL manual?
I used the IF() function, see 'Control Flow Functions' under 'Functions
and Operators' in the manual
PB
--
spacemarc wrote:
2007/5/2, Peter Brawley <[EMAIL PROTECTED]>:
Right, give the computed column an alias differeing from the column
name, eg
SET @prev='';
SELECT
IF(area = @prev, '', @prev := area) AS AreaHdr,
text,amount
FROM products
ORDER BY area DESC;
ok, now it works! thanks!
One last thing: you set, at first, a parameter called @prev with Null
(' ') value: right?
And, after, you use, instead IF ELSE statement, another syntax: is it
trinary operator? if yes, why it not is in the online MySQL manual?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: View select results
2007/5/2, Peter Brawley <[EMAIL PROTECTED]>:
Right, give the computed column an alias differeing from the column name, eg
SET @prev='';
SELECT
IF(area = @prev, '', @prev := area) AS AreaHdr,
text,amount
FROM products
ORDER BY area DESC;
ok, now it works! thanks!
One last thing: you set, at first, a parameter called @prev with Null
(' ') value: right?
And, after, you use, instead IF ELSE statement, another syntax: is it
trinary operator? if yes, why it not is in the online MySQL manual?
--
http://www.spacemarc.it
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: View select results
Right, give the computed column an alias differeing from the column name, eg
SET @prev='';
SELECT
IF(area = @prev, '', @prev := area) AS AreaHdr,
text,amount
FROM products
ORDER BY area DESC;
PB
spacemarc wrote:
2007/5/2, Peter Brawley <[EMAIL PROTECTED]>:
Works for me. Please post a CREATE TABLE stmt & enough INSERTs to
demonstrate the problem.
This is the dump (MySQL: 5.0.38): the table is not final version, just
to test the query.
CREATE TABLE `products` (
`area` varchar(25) NOT NULL,
`text` varchar(25) NOT NULL,
`amount` int(3) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `products` (`area`, `text`, `amount`) VALUES
('area1', 'some text', 12),
('area1', 'other text here', 13),
('area3', 'example...', 22),
('area2', 'things', 123),
('area1', 'bla bla...', 24),
('area2', 'others again', 231),
('area1', 'english language..', 44),
('area1', 'server database', 53),
('area3', 'php language...', 22),
('area2', 'linux box', 951),
('area1', 'developer tools', 4),
('area2', 'others words', 1);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: View select results
2007/5/2, Peter Brawley <[EMAIL PROTECTED]>:
Works for me. Please post a CREATE TABLE stmt & enough INSERTs to
demonstrate the problem.
This is the dump (MySQL: 5.0.38): the table is not final version, just
to test the query.
CREATE TABLE `products` (
`area` varchar(25) NOT NULL,
`text` varchar(25) NOT NULL,
`amount` int(3) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `products` (`area`, `text`, `amount`) VALUES
('area1', 'some text', 12),
('area1', 'other text here', 13),
('area3', 'example...', 22),
('area2', 'things', 123),
('area1', 'bla bla...', 24),
('area2', 'others again', 231),
('area1', 'english language..', 44),
('area1', 'server database', 53),
('area3', 'php language...', 22),
('area2', 'linux box', 951),
('area1', 'developer tools', 4),
('area2', 'others words', 1);
--
http://www.spacemarc.it
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: View select results
>can I obtain a recordset like this? >area1, value_one, thing_one > //, value_two, thing_32 > //, value_three, thing_ dd >area2, value_ten, thing_6w > //, value_ff, thing_l SET @prev=''; SELECT IF(area = @prev, '', @prev := area) AS area, ... other columns ... FROM &c ... PB spacemarc wrote: Hi my table have three fields that, if selected, are shown like: area1, value_one, thing_one area1, value_two, thing_32 area1, value_three, thing_ dd area2, value_ten, thing_6w area2, value_ff, thing_l can I obtain a recordset like this? area1, value_one, thing_one //, value_two, thing_32 //, value_three, thing_ dd area2, value_ten, thing_6w //, value_ff, thing_l So, do not repeat more times the value of the first column (area1, area2...) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: View select results
Hi, spacemarc wrote: Hi my table have three fields that, if selected, are shown like: area1, value_one, thing_one area1, value_two, thing_32 area1, value_three, thing_ dd area2, value_ten, thing_6w area2, value_ff, thing_l can I obtain a recordset like this? area1, value_one, thing_one //, value_two, thing_32 //, value_three, thing_ dd area2, value_ten, thing_6w //, value_ff, thing_l So, do not repeat more times the value of the first column (area1, area2...) Giuseppe Maxia wrote a great article on this some time ago. The technique is called cross-tabulation or pivot tables. Here is a link: http://www.onlamp.com/pub/a/onlamp/2003/12/04/crosstabs.html Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: View with Subselect for User ID
Thank you for the clarification! For some reason I believed the WHERE belonged to the specific JOIN clause. I came up with a clause, but I removed the Group part, for I didn't know how to do that. I will work on that later. Would this statement be good SQL practice? --- CREATE OR REPLACE ALGORITHM=UNDEFINED [EMAIL PROTECTED] SQL SECURITY DEFINER VIEW `shared_v` AS select `shared`.`ID` AS `ID`,`shared`.`RawID` AS `RawID`,`shared`.`Added` AS `Added`, `shared`.`Keywords` AS `Keywords`,`shared`.`OwnerID` AS `OwnerID`,`shared`.`UserID` AS `UserID` FROM (`shared` LEFT JOIN `users` on((`shared`.`UserID` = `users`.`ID`))) WHERE (`users`.`Name` = convert(substring_index(user(),_utf8'@',1) using latin1)) OR (`Shared`.`OwnerID` = (SELECT ID FROM `Users` WHERE `Name` = convert(substring_index(user(),_utf8'@',1) using latin1))); --- -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 24, 2007 12:09 PM To: Andreas Iwanowski Cc: mysql@lists.mysql.com Subject: Re: View with Subselect for User ID Hello Andreas, >I tried the following statement: > >CREATE OR REPLACE ALGORITHM=UNDEFINED [EMAIL PROTECTED] SQL >SECURITY DEFINER VIEW `shared_v` AS SELECT `shared`.`ID` AS >`ID`,`shared`.`RawID` AS `RawID`,`shared`.`OwnerID` AS >`OwnerID`,`shared`.`UserID` AS `UserID`,`shared`.`GroupID` AS `GroupID` >from `shared` JOIN Users ON (Shared.UserID = Users.ID) WHERE Users.Name >= (convert(substring_index(user(),_utf8'@', 1) using latin1)) AND JOIN >Groups ON (Shared.GroupID = Groups.ID) WHERE Groups.ID = Users.GroupID) >WHERE Users.ID = Shared.OwnerID; > > >That failed with multiple errors, the first one occuring at the AND >JOIN. Apparently I cannot have multiple JOINS in one statement? Sure you can, but you might want to read up on your SQL. http://www.w3schools.com/sql/default.asp >Please apologize my limited knowledge of JOINS. If you take a look at the MySQL documentation, you can see there's a clear way of creating SQL statements. Basically: [select clause] [from clause] [where clause] [group by clause] [order by clause] ( I'm not using the correct notation here, but some of these are optional ) Now, a FROM clause can consist of multiple tables, including multiple JOINs, each JOIN is following by a join-match-clause (which is the ON part of the JOIN). FROM myfirsttable t1 JOIN mysecondtable t2 ON t1.id = t2.foreignid JOIN mythirdtable t3 ON t1.id = t3.id etc... This will establish how these tables relate to eachother. In the WHERE clause, you will write your row filtering items, eg: WHERE t1.myuserid = 5 AND t2.mystatus = 'CONFIRMED' Now, try and figure out your own SQL statement :-) Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: View with Subselect for User ID
Hello Andreas, >I tried the following statement: > >CREATE OR REPLACE ALGORITHM=UNDEFINED [EMAIL PROTECTED] SQL >SECURITY DEFINER VIEW `shared_v` AS SELECT `shared`.`ID` AS >`ID`,`shared`.`RawID` AS `RawID`,`shared`.`OwnerID` AS >`OwnerID`,`shared`.`UserID` AS `UserID`,`shared`.`GroupID` AS `GroupID` >from `shared` JOIN Users ON (Shared.UserID = Users.ID) WHERE Users.Name >= (convert(substring_index(user(),_utf8'@', 1) using latin1)) AND JOIN >Groups ON (Shared.GroupID = Groups.ID) WHERE Groups.ID = Users.GroupID) >WHERE Users.ID = Shared.OwnerID; > > >That failed with multiple errors, the first one occuring at the AND >JOIN. Apparently I cannot have multiple JOINS in one statement? Sure you can, but you might want to read up on your SQL. http://www.w3schools.com/sql/default.asp >Please apologize my limited knowledge of JOINS. If you take a look at the MySQL documentation, you can see there's a clear way of creating SQL statements. Basically: [select clause] [from clause] [where clause] [group by clause] [order by clause] ( I'm not using the correct notation here, but some of these are optional ) Now, a FROM clause can consist of multiple tables, including multiple JOINs, each JOIN is following by a join-match-clause (which is the ON part of the JOIN). FROM myfirsttable t1 JOIN mysecondtable t2 ON t1.id = t2.foreignid JOIN mythirdtable t3 ON t1.id = t3.id etc... This will establish how these tables relate to eachother. In the WHERE clause, you will write your row filtering items, eg: WHERE t1.myuserid = 5 AND t2.mystatus = 'CONFIRMED' Now, try and figure out your own SQL statement :-) Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: View with Subselect for User ID
Hello Christian, Thank you very much for this reply. It was very helpful, and the user matching part works as it should. Is there any way to JOIN on two tables, so I can match the Users.GroupID field against a JOIN on Groups.ID? Also, I tried adding a WHERE clause after the join to compare Users.UserID to Shared.OwnerID, but that produced an error. Maybe that is confusing, but basically I wish have the user only see data where: 1. His ID is in the UserID field of the row OR 2. His ID is in the OwnerID field of the row OR 3. His data from the Users table references an ID in the Groups table that is is the Shared table's GroupID field I tried the following statement: CREATE OR REPLACE ALGORITHM=UNDEFINED [EMAIL PROTECTED] SQL SECURITY DEFINER VIEW `shared_v` AS SELECT `shared`.`ID` AS `ID`,`shared`.`RawID` AS `RawID`,`shared`.`OwnerID` AS `OwnerID`,`shared`.`UserID` AS `UserID`,`shared`.`GroupID` AS `GroupID` from `shared` JOIN Users ON (Shared.UserID = Users.ID) WHERE Users.Name = (convert(substring_index(user(),_utf8'@', 1) using latin1)) AND JOIN Groups ON (Shared.GroupID = Groups.ID) WHERE Groups.ID = Users.GroupID) WHERE Users.ID = Shared.OwnerID; That failed with multiple errors, the first one occuring at the AND JOIN. Apparently I cannot have multiple JOINS in one statement? Please apologize my limited knowledge of JOINS. I would appreciate if you could take the time to have a look at this. Thank you very much!, Andy >>From what i can tell from your create statement I think a join will get you what you want. Try this-- SELECT Shared.ID, >>Shared.RawID FROM Shared JOIN Users ON (Shared.ID = Users.UserID) WHERE Users.Login = convert(substring_index(user(), >>_utf8'@', 1) using latin1); >>If that doesn't get you what you are looking for than i have misunderstood your requirements >>CJ > Hello MySQL experts, > > I am trying to create a view whose access is based on a User ID that > need to be looked up in a different table. > Here is an example of what I'm trying to do: > > CREATE OR REPLACE ALGORITHM=UNDEFINED [EMAIL PROTECTED] SQL > SECURITY DEFINER VIEW `shared_v` AS select `Shared`.`ID` AS > `ID`,`Shared`.`RawID` AS `RawID` FROM `Shared` WHERE > (`Shared`.`UserID` = (SELECT UserID FROM Users WHERE Login = > convert(substring_index(user(),_utf8'@',1) using latin1)); > > However, MySQL doesn't gulp the subquery for the ID. > I don't want to have a VARCHAR column with the user name in this > table, because it can easily grow very large. > Would JOINS be the way to go? > If so, could anyone please give me an example of how to accomplish > this with joins? > > I would appreciate any tip. > > Thank you in advance, > -Andy > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: View with Subselect for User ID
On 4/23/07, Andreas Iwanowski <[EMAIL PROTECTED]> wrote: Hello MySQL experts, I am trying to create a view whose access is based on a User ID that need to be looked up in a different table. Here is an example of what I'm trying to do: CREATE OR REPLACE ALGORITHM=UNDEFINED [EMAIL PROTECTED] SQL SECURITY DEFINER VIEW `shared_v` AS select `Shared`.`ID` AS `ID`,`Shared`.`RawID` AS `RawID` FROM `Shared` WHERE (`Shared`.`UserID` = (SELECT UserID FROM Users WHERE Login = convert(substring_index(user(),_utf8'@',1) using latin1)); However, MySQL doesn't gulp the subquery for the ID. I don't want to have a VARCHAR column with the user name in this table, because it can easily grow very large. Would JOINS be the way to go? If so, could anyone please give me an example of how to accomplish this with joins? I would appreciate any tip. Thank you in advance, -Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] From what i can tell from your create statement I think a join will get you what you want. Try this-- SELECT Shared.ID, Shared.RawID FROM Shared JOIN Users ON (Shared.ID = Users.UserID) WHERE Users.Login = convert(substring_index(user(),_utf8'@', 1) using latin1); If that doesn't get you what you are looking for than i have misunderstood your requirements CJ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: View hidden temporary files
Thanks. That works for me. -Mensaje original- De: Dan Nelson [mailto:[EMAIL PROTECTED] Enviado el: jueves, 07 de septiembre de 2006 19:05 Para: [EMAIL PROTECTED] CC: MySql Mail List Asunto: Re: View hidden temporary files In the last episode (Sep 07), [EMAIL PROTECTED] said: > In http://dev.mysql.com/doc/refman/4.1/en/temporary-files.html says > > "MySQL creates all temporary files as hidden files. This ensures that > the temporary files are removed if mysqld is terminated. The > disadvantage of using hidden files is that you do not see a big > temporary file that fills up the filesystem in which the temporary > file directory is located." > > Is there any form of see the length of the temporary files created by > MySQL? If you install the lsof program, you can ask it to print all filehandles opened by mysql with a link count less than one (i.e. deleted but still-open files): $ lsof -c mysqld -a +L1 mysqld 70195 mysql 45u VREG 0,120 463317867 0 12500 /usr (/dev/da0s1f) mysqld 70195 mysql 46u VREG 0,120 132005329 0 12520 /usr (/dev/da0s1f) -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: View hidden temporary files
Hi, MySQL creates temporary table to complete the query. However, it doesn't tell you whether that temporary table will be in memory or on disk. MySQL's tmp_table_size variable will control the temporary table size.The default tmp_table_size size is 32 MB Temporary tables can either be in the Disk or in Memory. If the space required to build the temporary table is less than or equal to tmp_table_size, MySQL keeps it in memory rather than incur the overhead and time required to write the data to disk and read it again. However, if the space required exceeds tmp_table_size, MySQL creates a disk-based table in its tmpdir directory (often /tmp on Unix systems.) . mysql> SHOW STATUS LIKE 'Created_tmp_%'; To find out how often that happens, compare the relative sizes of the Created_tmp_tables and Created_tmp_disk_tables counters. Thanks, ViSolve DB Team. - Original Message - From: <[EMAIL PROTECTED]> To: "MySql Mail List" Sent: Thursday, September 07, 2006 8:00 PM Subject: View hidden temporary files Hi. In http://dev.mysql.com/doc/refman/4.1/en/temporary-files.html says "MySQL creates all temporary files as hidden files. This ensures that the temporary files are removed if mysqld is terminated. The disadvantage of using hidden files is that you do not see a big temporary file that fills up the filesystem in which the temporary file directory is located." Is there any form of see the length of the temporary files created by MySQL? Iago.
Re: View hidden temporary files
In the last episode (Sep 07), [EMAIL PROTECTED] said: > In http://dev.mysql.com/doc/refman/4.1/en/temporary-files.html says > > "MySQL creates all temporary files as hidden files. This ensures that > the temporary files are removed if mysqld is terminated. The > disadvantage of using hidden files is that you do not see a big > temporary file that fills up the filesystem in which the temporary > file directory is located." > > Is there any form of see the length of the temporary files created by > MySQL? If you install the lsof program, you can ask it to print all filehandles opened by mysql with a link count less than one (i.e. deleted but still-open files): $ lsof -c mysqld -a +L1 mysqld 70195 mysql 45u VREG 0,120 463317867 0 12500 /usr (/dev/da0s1f) mysqld 70195 mysql 46u VREG 0,120 132005329 0 12520 /usr (/dev/da0s1f) -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: VIEW not working with myODBC in XP and Access 2003
This is an Access problem; you'll need to find folks who know access to fix it. Try: http://www.tek-tips.com/viewthread.cfm?qid=1146857&page=1 ?? -Sheeri On 4/26/06, Daevid Vincent <[EMAIL PROTECTED]> wrote: I have a critical problem that I hope there is a simple solution for. I've just spent a couple days converting a very messy "hack" to populate a table using a much more elegant VIEW solution now. Everything is going great, except now the whole point of this VIEW is so that people using MS Access (or other ODBC) can use the VIEW. It doesn't work!?! I've followed all of this: http://dev.mysql.com/doc/refman/5.0/en/msaccess-setup.html I can import/link any other non-VIEW table. I've given FULL permissions to my ODBC user in mysql.mysql.user (and other appropriate) places just in case. Access pops up an error box that says "Could not execute query; could not find linked table" I'm using these versions: Windows XP http://dev.mysql.com/downloads/connector/odbc/3.51.html [EMAIL PROTECTED]:/lockdown# mysql --version mysql Ver 14.12 Distrib 5.0.15, for pc-linux-gnu (i686) using readline 4.3 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: View onto tables w/ identical key names
> >When creating the view, use > >CREATE VIEW viewname ( column names ) > >AS > >select [fields] > > > >Instead of "*". This way, you can only return a single "mat" column instead > >of having that column twice in the view. > > > > > i'll try that programmatically, no way am i typing close to 1000 field > names into the query browser ;-) Get yourself a GUI tool with which you can simply drag if a list of fields :-) I know one ... Database Workbench - www.upscene.com ;-) With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: View onto tables w/ identical key names
Martijn Tonies wrote: When creating the view, use CREATE VIEW viewname ( column names ) AS select [fields] Instead of "*". This way, you can only return a single "mat" column instead of having that column twice in the view. i'll try that programmatically, no way am i typing close to 1000 field names into the query browser ;-) thanks les schaffer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: View onto tables w/ identical key names
Les, > i am working on a database system where a number of tables (5-10) each > with possibly hundreds of columns share an identical primary key name. > the truth is if it weren't for a limitation in the number of columns in > M$ Access (long story: we're creating a client server scheme so they > can get their Access DB for data analysis purposes post data > collection)) these would be all one table. > > since i need to use MySQL tables with a similar structure (long story), > i would like to join these tables at the hip, so to speak, with a VIEW. > however when i try to create said VIEW from a select/join, MySQL balks > because the primary key name is the same in each table: > > select * from id left join dia USING (mat) left join msa USING (mat) > left join sq USING (mat) When creating the view, use CREATE VIEW viewname ( column names ) AS select [fields] Instead of "*". This way, you can only return a single "mat" column instead of having that column twice in the view. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: View
Views are supported, however, starting with MySQL version 5.0.1. See this page of the on-line documentation to read more about it: http://dev.mysql.com/doc/mysql/en/views.html On Thu, 2005-06-02 at 14:31 -0400, Jerry Swanson wrote: > Does Mysql 4 supports "views"? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: View
NO Selon Jerry Swanson <[EMAIL PROTECTED]>: > Does Mysql 4 supports "views"? > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: View
Jerry Swanson <[EMAIL PROTECTED]> wrote on 06/02/2005 02:31:06 PM: > Does Mysql 4 supports "views"? > There were at least two other ways you could have found this information: RTFM: http://dev.mysql.com/doc/mysql/en/ansi-diff-views.html Search this list's archives: http://lists.mysql.com/mysql This is usually a very friendly list but I suspect you are going to get some rather "short" answers to your question. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: View
I believe 5.0 does. -Original Message- From: Andreas Ahlenstorf [mailto:[EMAIL PROTECTED] Sent: Thursday, June 02, 2005 2:55 PM To: Jerry Swanson Cc: mysql@lists.mysql.com Subject: Re: View Am 02.06.2005 um 20:31 schrieb Jerry Swanson: > Does Mysql 4 supports "views"? No. Regards, A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: View
Am 02.06.2005 um 20:31 schrieb Jerry Swanson: Does Mysql 4 supports "views"? No. Regards, A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: VIEW
Hi Steven, > I've been looking at the post in the website, but cannot find a clear answehr > about views. > Once is stated that it will be available in 5.0, and once that it's allready > in 4.1. > In the 5.0 features list etc. nothing is mentioned. > I like to know: when and how (docs) can I use views with mysql ? Not for a long time... With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: view warnings?
Bryan Harris <[EMAIL PROTECTED]> wrote: >>> I'm pretty new to MySQL, but I already like it. Kudos to the developers! >>> >>> I recently did a mysqlimport on a few data files, and it came back with over >>> 4000 warnings. How can I actually see what the warnings were? I'm still >>> not sure what it was upset about... >> >> You can't. >> From 4.1.1 you can see warning if you use LOAD DATA INFILE statement: >> http://www.mysql.com/doc/en/SHOW_WARNINGS.html > > I used mysqlimport because I could import a bunch of files all at once. > Does LOAD DATA have this capability? No. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: view warnings?
>> I'm pretty new to MySQL, but I already like it. Kudos to the developers! >> >> I recently did a mysqlimport on a few data files, and it came back with over >> 4000 warnings. How can I actually see what the warnings were? I'm still >> not sure what it was upset about... > > You can't. > From 4.1.1 you can see warning if you use LOAD DATA INFILE statement: > http://www.mysql.com/doc/en/SHOW_WARNINGS.html I used mysqlimport because I could import a bunch of files all at once. Does LOAD DATA have this capability? TIA. - B -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: view warnings?
Bryan Harris <[EMAIL PROTECTED]> wrote: > > I'm pretty new to MySQL, but I already like it. Kudos to the developers! > > I recently did a mysqlimport on a few data files, and it came back with over > 4000 warnings. How can I actually see what the warnings were? I'm still > not sure what it was upset about... You can't. >From 4.1.1 you can see warning if you use LOAD DATA INFILE statement: http://www.mysql.com/doc/en/SHOW_WARNINGS.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: View Warnings
On Wednesday 26 February 2003 02:40, Robert Mark Bram wrote: > mysql>LOAD DATA LOCAL > ->INFILE > "/Rob/architecturalScreens/prototype/v1/database/insertPart.dat" > ->INTO TABLE part; > Query OK, 496 rows affected (0.08 sec) > Records: 496 Deleted: 0 Skipped: 0 Warnings: 1494 > > I am glad it loaded my file (it took me ages to build it)... but how do I > view the warnings? You can view warnings since 4.1: http://www.mysql.com/doc/en/SHOW_WARNINGS.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Re: view privileges
On Thursday 16 January 2003 09:15, Addison Ellis wrote: > how can i view privileges for a table in a db? There is no command to see privileges on the certain table/db etc. If you have privileges on database 'mysql' you can search through tables to see privileges. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: view privileges
SELECT * from mysql.tables_priv where Table_name = 'myTable'; You will probably have to log on as 'root' unless you have created another user with the same privileges. The mysql GRANTs system is used to control USER privileges. This means that for a given table you can view which users can perform what operations on that table. The same holds true of databases and columns within tables. If you have never issued table-specific GRANT statements, then there will be no entries in the mysql.tables_priv table. That is because you have been granting database-wide access to all table functions (Select, Insert, Update, Delete, Create, Drop, Grant, References, Index, Alter) for all users. This is not a good idea. If you want to find out to which users you have been granting all these table-wide privileges, look at the mysql.db table, SELECT * from mysql.db where db='mydb'; The above is a very superficial description of the mysql Access Privilege system. You need to read a lot more in the manual in section 4.2, "General Security Issues and the MySQL Access Privilege System." Doug On Thu, 16 Jan 2003 01:15:03 -0600, Addison Ellis wrote: >hello, > how can i view privileges for a table in a db? thank you, addison >sql,query,queries,smallint >-- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: view privileges
hello, how can i view privileges for a table in a db? thank you, addison sql,query,queries,smallint -- Addison Ellis small independent publishing co. 114 B 29th Avenue North Nashville, TN 37203 (615) 321-1791 [EMAIL PROTECTED] [EMAIL PROTECTED] subsidiaries of small independent publishing co. [EMAIL PROTECTED] [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: View image from browser
What language are you using to pull the data? ASP? PHP? VB? At 11:23 PM 12/26/2002 -0800, tan tan wrote: Hi, Is anyone know how to view image that is stored in mysql as binary format to a browser ? Thank you. -- Michael She : [EMAIL PROTECTED] Mobile : (519) 589-7309 WWW Homepage : http://www.binaryio.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: View image from browser
Hi, Sorry I didn't check in the pdf. Thanks guys. By the way, I found there is an article from www.onlamp.com/lpt/a/a370 it uses the php to do the same thing. Thanks once again. Tan Tan --- Paul DuBois <[EMAIL PROTECTED]> wrote: > At 0:43 -0800 12/27/02, tan tan wrote: > >Hi Paul > >I went to the site and I can't look for the page. > >If you dont' mind to get the the exact url. > >Thanks. > > Odd. Anyone else out there have trouble with that > URL? > > http://www.kitebird.com/mysql-perl/downloads.php > http://www.kitebird.com/mysql-perl/webdb/webdb.tar.gz > http://www.kitebird.com/mysql-perl/webdb/webdb.zip > > > > >--- Paul DuBois <[EMAIL PROTECTED]> wrote: > >> At 23:23 -0800 12/26/02, tan tan wrote: > >> >Hi, > >> > > >> >Is anyone know how to view image that is stored > in > >> >mysql as binary format to > >> >a browser ? > >> > > >> >Thank you. > >> > >> Once you pull your image data from MySQL (I > assume > >> you're using some > >> kind of script for this), then you do this the > same > >> way you'd do it > >> if MySQL weren't involved. Send your HTTP > headers > >> to indicate the > >> MIME type and the length, a blank line, and the > >> image data. > >> > >> If you want some example code in Perl, get the > webdb > >> distribution at > > > http://www.kitebird.com/mysql-perl/ and look in > the > >> distribution's > >> image directory. > >> > >> > > > sql, query > __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: View image from browser
On Fri, 27 Dec 2002 07:15:01 -0600, Paul DuBois wrote: >At 0:43 -0800 12/27/02, tan tan wrote: >>Hi Paul >>I went to the site and I can't look for the page. >>If you dont' mind to get the the exact url. >>Thanks. > >Odd. Anyone else out there have trouble with that URL? > >http://www.kitebird.com/mysql-perl/downloads.php >http://www.kitebird.com/mysql-perl/webdb/webdb.tar.gz >http://www.kitebird.com/mysql-perl/webdb/webdb.zip > They work fine from here. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: View image from browser
At 0:43 -0800 12/27/02, tan tan wrote: Hi Paul I went to the site and I can't look for the page. If you dont' mind to get the the exact url. Thanks. Odd. Anyone else out there have trouble with that URL? http://www.kitebird.com/mysql-perl/downloads.php http://www.kitebird.com/mysql-perl/webdb/webdb.tar.gz http://www.kitebird.com/mysql-perl/webdb/webdb.zip --- Paul DuBois <[EMAIL PROTECTED]> wrote: At 23:23 -0800 12/26/02, tan tan wrote: >Hi, > >Is anyone know how to view image that is stored in >mysql as binary format to >a browser ? > >Thank you. Once you pull your image data from MySQL (I assume you're using some kind of script for this), then you do this the same way you'd do it if MySQL weren't involved. Send your HTTP headers to indicate the MIME type and the length, a blank line, and the image data. If you want some example code in Perl, get the webdb distribution at > http://www.kitebird.com/mysql-perl/ and look in the distribution's image directory. > sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: View image from browser
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello tan, Friday, December 27, 2002, 8:43:25 AM, you wrote: > Hi Paul > I went to the site and I can't look for the page. > If you dont' mind to get the the exact url. > Thanks. you don't find it because it's an pdf file http://www.kitebird.com/mysql-perl/ch05.pdf and do a search in the pdf file for the string 'Storing and Retrieving Images' > --- Paul DuBois <[EMAIL PROTECTED]> wrote: >> At 23:23 -0800 12/26/02, tan tan wrote: >> >Hi, >> > >> >Is anyone know how to view image that is stored in >> >mysql as binary format to >> >a browser ? >> > >> >Thank you. >> >> Once you pull your image data from MySQL (I assume >> you're using some >> kind of script for this), then you do this the same >> way you'd do it >> if MySQL weren't involved. Send your HTTP headers >> to indicate the >> MIME type and the length, a blank line, and the >> image data. >> >> If you want some example code in Perl, get the webdb >> distribution at >> http://www.kitebird.com/mysql-perl/ and look in the >> distribution's >> image directory. >> >> >> sql, query - -- Best regards, Solidmailto:[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) iD8DBQE+DEE02PEgI0nAJngRAla8AJ9qbLE2qAgOdTHYmrvP2liTYNUpnwCgr47s H/O5+Zh87YH1VbJ1Fst6R0Q= =LEV7 -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: View image from browser
Hi Paul I went to the site and I can't look for the page. If you dont' mind to get the the exact url. Thanks. --- Paul DuBois <[EMAIL PROTECTED]> wrote: > At 23:23 -0800 12/26/02, tan tan wrote: > >Hi, > > > >Is anyone know how to view image that is stored in > >mysql as binary format to > >a browser ? > > > >Thank you. > > Once you pull your image data from MySQL (I assume > you're using some > kind of script for this), then you do this the same > way you'd do it > if MySQL weren't involved. Send your HTTP headers > to indicate the > MIME type and the length, a blank line, and the > image data. > > If you want some example code in Perl, get the webdb > distribution at > http://www.kitebird.com/mysql-perl/ and look in the > distribution's > image directory. > > > sql, query __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: View image from browser
At 23:23 -0800 12/26/02, tan tan wrote: Hi, Is anyone know how to view image that is stored in mysql as binary format to a browser ? Thank you. Once you pull your image data from MySQL (I assume you're using some kind of script for this), then you do this the same way you'd do it if MySQL weren't involved. Send your HTTP headers to indicate the MIME type and the length, a blank line, and the image data. If you want some example code in Perl, get the webdb distribution at http://www.kitebird.com/mysql-perl/ and look in the distribution's image directory. sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: View current connections
Is the MySQL serving a web site? If yes, wrap show processlist in perl or php and have it generate an auto refreshing page. On Thu, 15 Aug 2002, Tab Alleman wrote: > Date: Thu, 15 Aug 2002 11:01:44 -0400 > From: Tab Alleman <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: RE: View current connections > > Thanks for the reply Egor.. This helps, but what I'm really hoping to > find is a GUI that updates itself on the fly, kind of like the Win2k > Task Manager processes tab. Anybody got anything like this? > > -Original Message- > From: Egor Egorov [mailto:[EMAIL PROTECTED]] > Sent: Thursday, August 15, 2002 7:08 AM > To: [EMAIL PROTECTED] > Subject: Re: View current connections > > > Tab, > Wednesday, August 14, 2002, 11:06:36 PM, you wrote: > > TA> Is there a tool out there for Windows that will let me monitor how > TA> many connections to my MySQL database are currently open at any > TA> given moment? > > Use SHOW PROCESSLIST command: > http://www.mysql.com/doc/en/SHOW_PROCESSLIST.html > > > > > > > -- > For technical support contracts, goto > https://order.mysql.com/?ref=ensita > This email is sponsored by Ensita.net http://www.ensita.net/ >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Egor Egorov > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ><___/ www.mysql.com > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > Sincerely, William Mussatto, Senior Systems Engineer CyberStrategies, Inc ph. 909-920-9154 ext. 27 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: View current connections
> ...I'm really hoping to > find is a GUI that updates itself on the fly, kind of like the Win2k > Task Manager processes tab. Anybody got anything like this? I am really happy with this one http://www.anse.de/mysqlfront/ It can do that and a lot of other nice features. Best regards, Mikhail. - Original Message - From: "Tab Alleman" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, August 15, 2002 5:01 PM Subject: RE: View current connections Thanks for the reply Egor.. This helps, but what I'm really hoping to find is a GUI that updates itself on the fly, kind of like the Win2k Task Manager processes tab. Anybody got anything like this? -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 15, 2002 7:08 AM To: [EMAIL PROTECTED] Subject: Re: View current connections Tab, Wednesday, August 14, 2002, 11:06:36 PM, you wrote: TA> Is there a tool out there for Windows that will let me monitor how TA> many connections to my MySQL database are currently open at any TA> given moment? Use SHOW PROCESSLIST command: http://www.mysql.com/doc/en/SHOW_PROCESSLIST.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: View current connections
Thanks for the reply Egor.. This helps, but what I'm really hoping to find is a GUI that updates itself on the fly, kind of like the Win2k Task Manager processes tab. Anybody got anything like this? -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 15, 2002 7:08 AM To: [EMAIL PROTECTED] Subject: Re: View current connections Tab, Wednesday, August 14, 2002, 11:06:36 PM, you wrote: TA> Is there a tool out there for Windows that will let me monitor how TA> many connections to my MySQL database are currently open at any TA> given moment? Use SHOW PROCESSLIST command: http://www.mysql.com/doc/en/SHOW_PROCESSLIST.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: View current connections
Tab, Wednesday, August 14, 2002, 11:06:36 PM, you wrote: TA> Is there a tool out there for Windows that will let me monitor how many TA> connections to my MySQL database are currently open at any given moment? Use SHOW PROCESSLIST command: http://www.mysql.com/doc/en/SHOW_PROCESSLIST.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: view support
On Wed, Sep 19, 2001 at 12:37:04PM -0400, Alexander Pichtchikov wrote: > Gents, > > When version 4.1 of MySQL with view supporting will publish? Sometime after 4.0. Seriously, though, I don't think anyone knows with much certainty. The 4.0 release has to hit alpha/beta/gamma testing and then be released. That should be starting soon, based on what we've heard here before. You can, of course, offer to sponsor the work on views and it might speed things up... The MySQL folks always like that. :-) Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 14 days, processed 255,706,181 queries (210/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: View a return of Select
> I have to check to MySQL is able to support our requirements >and I would >like to view a return of n columns like only one register, thats is posible >with MySQL? > For example i have 2 tables , 1 table have products and this >table habe 3 >colums ID , name, Id_price and the other table (prices) have 2 columns >Id_price , price , can i return for the 'x' product their prices like >price1,price2,...priceN > > >__ >Un Saludo >Jaume Rubio >Depto. Informática >[EMAIL PROTECTED] >http://www.epel-ind.com >__ Yes sir, you can return the data in that format, but whether it's a good idea is another question. SQL was intended to return data in the format x, price1 x, price2 x, price3 With most RDBMSs, including MySQL, it is possible to force the data into your format, but it is complicated and slows things down. You would be better off having your query return data in the usual format and then have a client program parse the output and reformat it. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: View a return of Select
Not without some procedural code in a different language or creating temp tables. (Both are bad ideas.) Using straight SQL, the best you can get it 3 records, each with all the info from table 1 and a different row from table 2. HTH, Cal http://www.calevans.com -Original Message- From: Jaime Rubio [mailto:[EMAIL PROTECTED]] Sent: Friday, March 23, 2001 6:06 AM To: [EMAIL PROTECTED] Subject: View a return of Select I have to check to MySQL is able to support our requirements and I would like to view a return of n columns like only one register, thats is posible with MySQL? For example i have 2 tables , 1 table have products and this table habe 3 colums ID , name, Id_price and the other table (prices) have 2 columns Id_price , price , can i return for the 'x' product their prices like price1,price2,...priceN __ Un Saludo Jaume Rubio Depto. Informática [EMAIL PROTECTED] http://www.epel-ind.com __ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
