Re: How do I determine if versions of phpMyAdmin before 4.8.5 is SQL Injectable using sqlmap?

[shawn l.green]

Hello,

On 4/17/2019 10:29 AM, Turritopsis Dohrnii Teo En Ming wrote:

Subject/Topic: How do I determine if versions of phpMyAdmin before 4.8.5 is SQL 
Injectable using sqlmap?

Good evening from Singapore,

Our customer (company name is Confidential/not disclosed) reported that their 
MySQL database has been found missing or was deleted a few times.


While it is bad form to explain how to break into anyone's software 
(including our own), there are places you can look to get a better idea 
about what might have happened:


1 - the database may have been removed by a DROP DATABASE command.

General Query Log - this will show you which session issued the command 
and the command itself.


Audit log (only for commercial releases) - same thing

Binary Log - Should have a record of the command executing. But, 
depending on which account was used or if Binary Log filtering is in 
place, it may not. This presumes that the Binary Log is even enabled on 
this system.  Many people mistakenly believe it is only for Replication 
when its other primary use is for point-in-time recovery. If your 
customer has a recent backup and all of the Binary Log files created 
since that backup, they could return the system to the point it was at 
just before that database went missing, skip that DROP command, then 
continue rolling forward the changes to the other tables to return to a 
"current" state of their data.


2 - The database was "dropped" by either changing privileges to the 
folder or by removing it from disk or some other file-level or 
system-level operation. Either of those would cause errors to start 
appearing in the MySQL Error Log because a resource that mysqld thinks 
should exist is no longer available.   While the Error Log can't tell 
you which operation made those files "no longer available" it will have 
a fingerprint that such an action happened outside of mysqld.



Have you determined which method was used to make that database/schema 
disappear?


A normal DROP command (which could happen through an SQL injection 
attack) would not leave messages in the Error Log about "unable to 
access ..." or something similar. The server (mysqld) would know that 
the database was gone (because it removed it) and it wouldn't be trying 
to find it or the tables within it for your clients to use it.





... snip ...
No matter how many commands I try, sqlmap always report that phpMyAdmin 4.8.4 
is *NOT* SQL injectable. Perhaps I was using the wrong sqlmap commands all the 
time? The following is one of the many sqlmap commands I have used.

$ python sqlmap.py -u "https://www.EXAMPLE.com/phymyadmin/index.php?id=1"; --level=1 
--dbms=mysql --sql-query="drop database"



Privately asking phpMyAdmin may be a better source of information about 
how to hack their system to do things it was not intended to do. This 
list is not about phpMyAdmin and it is very public.  They may also have 
a way of showing you some kind of trace or log that serves as a 
fingerprint for that happening.



--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

How do I determine if versions of phpMyAdmin before 4.8.5 is SQL Injectable using sqlmap?

[Turritopsis Dohrnii Teo En Ming]

Subject/Topic: How do I determine if versions of phpMyAdmin before 4.8.5 is SQL 
Injectable using sqlmap?

Good evening from Singapore,

Our customer (company name is Confidential/not disclosed) reported that their 
MySQL database has been found missing or was deleted a few times. They are 
using Ubuntu 16.04 LTS Linux server with Apache2 Web Server, MySQL and PHP 
(LAMP).

We responded to these security incidents by changing the passwords of the 
regular user, root user, and MySQL database user root. We have also examined 
/var/log/auth.log and think that the hacker could not have come in through ssh 
or sftp over ssh. From /var/log/mysql/error.log, we can ascertain that the 
MySQL database has been deleted at certain timings. We have also found nothing 
abnormal after examining /var/log/apache2/access.log.

Even though we have secured the Ubuntu Linux server by changing passwords, the 
hacker was still able to delete our customer's MySQL database again and again. 
I have already proposed to install ModSecurity Open Source Web Application 
Firewall (WAF) to defend against web application attacks but my boss has told 
me to put that on hold at the moment. In fact, I have already deployed 
ModSecurity 2.9.0 on a Ubuntu 16.04 LTS *Testing* server and found that it 
actively detects and logs Nessus and sqlmap vulnerability scans in blocking 
mode.

Since we did not find any evidence that the hacker had breached our customer's 
Ubuntu 16.04 LTS production server through ssh or Teamviewer, we suspect that 
the hacker could have achieved it by SQL injection. I took the initiative of 
downloading and installing Nessus Professional 8.3.1 Trial version for Windows 
64-bit. The vulnerability scan report generated by Nessus Web Application Tests 
shows that our customer is using a version of phpMyAdmin prior to 4.8.5 which 
could be vulnerable to SQL injection using the designer feature.

Further research shows that I can use sqlmap to determine if phpMyAdmin is SQL 
injectable. I already have a Testing Ubuntu 16.04 LTS Linux server with a 
Testing MySQL database and a Testing phpMyAdmin 4.8.4. I have purposely 
installed phpMyAdmin 4.8.4 because this version was reported to be vulnerable 
to SQL injection using the designer feature, and our customer is using a 
vulnerable version, according to CVE-2019-6798 ( 
https://nvd.nist.gov/vuln/detail/CVE-2019-6798 ). Then I proceeded to download 
and execute sqlmap on our Ubuntu Linux desktop against our Testing server.

No matter how many commands I try, sqlmap always report that phpMyAdmin 4.8.4 
is *NOT* SQL injectable. Perhaps I was using the wrong sqlmap commands all the 
time? The following is one of the many sqlmap commands I have used.

$ python sqlmap.py -u "https://www.EXAMPLE.com/phymyadmin/index.php?id=1"; 
--level=1 --dbms=mysql --sql-query="drop database"

Replace database by database name.

May I know what is the correct sqlmap command that I should use to determine 
that my Testing phpMyAdmin 4.8.4 is SQL injectable? I would like to know if I 
can successfully drop/delete the Testing database on our Testing server. If I 
can successfully drop/delete the Testing MySQL database using sqlmap, I would 
be able to conclude that the hacker must have carried out SQL injection to 
drop/delete the customer's database. I have already turned off the Testing 
ModSecurity Web Application Firewall on our Testing server to allow sqlmap to 
go through.

Please point me to any good tutorial on SQL injection using sqlmap. Maybe I do 
not understand SQL injection well enough. Our customer is also using a 
customised in-house inventory management system that relies on PHP application 
and MySQL database.

Would open source Snort Intrusion Detection System (IDS) and Intrusion 
Prevention System (IPS) be able to detect and block SQL injection as well?

Please advise.

Thank you very much.

-BEGIN EMAIL SIGNATURE-

The Gospel for all Targeted Individuals (TIs):

[The New York Times] Microwave Weapons Are Prime Suspect in Ills of
U.S. Embassy Workers

Link: 
https://www.nytimes.com/2018/09/01/science/sonic-attack-cuba-microwave.html



Singaporean Mr. Turritopsis Dohrnii Teo En Ming's Academic
Qualifications as at 14 Feb 2019

[1] https://tdtemcerts.wordpress.com/

[2] https://tdtemcerts.blogspot.sg/

[3] https://www.scribd.com/user/270125049/Teo-En-Ming

-END EMAIL SIGNATURE-


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: how to select the record with one sql statement?

[shawn l.green]

Hello sea,

On 8/13/2018 7:01 PM, sea wrote:

helle,
I have a table, like this:
  pigId  dayweigt
   pig1  2018-1-121
   pig2  2018-1-131
   pig3  2018-1-141
   pig1  2018-1-222
   pig2  2018-1-231
   pig3  2018-1-240
   pig1  2018-1-323  pig2  2018-1-330
   pig3  2018-1-341
   .

only the pig1'weight increase continuously for 3 days.   Giving the input: 
num_of_day(weight increasing continuously for num_of_day);   expecting the 
output: certain_day, pigId;from certain_day,  pigId'weight increasing 
continuously for num_of_day.  How to select the records in one sql 
statement?
 thanks



I've thought about this a bit (since your question appeared on the list) 
and I break down the tasks you need to perform in my head like this. 
(Others on the list may have different ways to approach the same problem)


task 1 - For each bucket, a pigId value, assemble an ordered list (not a 
set) of each weight sorted by time.  (not hard)


task 2 - Within each ordered list, compare the values of every 
consecutive pair.  (several ways to do this)


task 3 - Iterate over those "consecutive value differences" generated in 
task 2 looking for the longest sequence of positive non-zero values for 
each pigId.  (this is not really a set-oriented process so normal SELECT 
or GROUP BY command patterns will not handle it with any efficency)


I'm afraid that attempting all of that sequencing and iteration using 
just a single set-based SQL command is not going to be practical. Using 
one or more cursors within a stored procedure is your best bet for this 
type of sequential trend analysis.


I could easily imagine the first step as a INSERT...SELECT...ORDER BY... 
command going to a new table with an autoincrement column on it (to 
provide a global sequence number across all of your individual pigId 
values) . The second step could do a self join to that table where the 
ON clause could look like

  a.pigId = b.pigID AND a.seq-1 = b.seq

But at that point, counting the length of sequences (and remembering 
when each trend became positive) needs a loop. That's where even 
complicated set-wise SQL fails you and you need to shift into using the 
SQL of stored programs.



Regards,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

how to select the record with one sql statement?

[sea]

helle,
   I have a table, like this:
 pigId  dayweigt
  pig1  2018-1-121
  pig2  2018-1-131
  pig3  2018-1-141
  pig1  2018-1-222
  pig2  2018-1-231
  pig3  2018-1-240
  pig1  2018-1-323  pig2  2018-1-330
  pig3  2018-1-341
  .

   only the pig1'weight increase continuously for 3 days.   Giving the input: 
num_of_day(weight increasing continuously for num_of_day);   expecting the 
output: certain_day, pigId;from certain_day,  pigId'weight increasing 
continuously for num_of_day.  How to select the records in one sql 
statement?
thanks

Re: Automated SQL checker?

[SSC_perl]

> On Apr 12, 2017, at 3:38 PM, Ronan McGlue  wrote:
> 
> Enable the slow log on the DB.

Thanks Ronan.  That sounds like it would be beneficial.  I take it 
you’re referring to a setting on the server, though.  If that’s the case, I 
don’t think I can do that as I’m on shared hosting (unless I’m misunderstanding 
you).  When I get some free time (hah!) maybe I’ll set up MySQL on my laptop.  
Unfortunately, installing DBI on OS X can be a nightmare, so I don’t know 
if/when that will happen.

Thanks again,
Frank
https://www.surfshopcart.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Automated SQL checker?

[Ronan McGlue]

Hi Frank,

Enable the slow log on the DB. Any queries which are above the threshold 
variable ( |long_query_time| 
 
) will be logged .


Default is 10s, so I assume you'd want to drop this down to 1-2 ( or 
less ) for a shopcart type response time?


You can then use mysqldumpslow command to aggregate this file after a 
period of  time to identify queries which are greater than long_query_time.


This is typically the first step in analyzing inefficient queries ( or 
more accurately, queries which are candidates for improvement ) in a 
running DB. eg


*|mysqldumpslow -s c -t 5 |*

https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html

https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

Regards


Ronan McGlue

MySQL Support



On 13/04/2017 01:06, SSC_perl wrote:

On Apr 11, 2017, at 1:26 PM, Michael Munger  wrote:

Use MySQL workbench.

Thanks, Michael.  I played with it some already and it looks like it 
will give me a lot to work with.

Will it also let me know if field types are wrong for the given 
information type, or is that asking for too much?

Frank

https://www.surfshopcart.com


--
Regards

Ronan McGlue
MySQL Support
Oracle Australia

Re: Automated SQL checker?

[SSC_perl]

> On Apr 11, 2017, at 1:26 PM, Michael Munger  
> wrote:
> 
> Use MySQL workbench.

Thanks, Michael.  I played with it some already and it looks like it 
will give me a lot to work with.

Will it also let me know if field types are wrong for the given 
information type, or is that asking for too much?

Frank

https://www.surfshopcart.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: Automated SQL checker?

[Michael Munger]

Use MySQL workbench.

Add query to the editor, execute, check results.

Then, use the "Execution plan" feature to see how things are executing and look 
for bad things (Cartesian products, stupid loops, etc...)

See also: Query stats.


Michael Munger, dCAP, MCPS, MCNPS, MBSS
High Powered Help, Inc.
Microsoft Certified Professional
Microsoft Certified Small Business Specialist
Digium Certified Asterisk Professional
mich...@highpoweredhelp.com


-Original Message-
From: SSC_perl [mailto:p...@surfshopcart.com] 
Sent: Tuesday, April 11, 2017 4:04 PM
To: mysql mailing list 
Subject: Automated SQL checker?

Is there a way, perhaps with a script or a service, that one can check 
MySQL code to see about making it more efficient?  I maintain an open source 
shopping cart written in Perl and it’s been awhile since the SQL has been 
worked on, so I want to see if it could use some updating.

Thanks,
Frank

http://www.surfshopcart.com
-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Automated SQL checker?

[SSC_perl]

Is there a way, perhaps with a script or a service, that one can check 
MySQL code to see about making it more efficient?  I maintain an open source 
shopping cart written in Perl and it’s been awhile since the SQL has been 
worked on, so I want to see if it could use some updating.

Thanks,
Frank

http://www.surfshopcart.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Unsubscribing my sql mailing list

[Reindl Harald]

why do you think we could do that for you and what do you think is that 
link in every mail for? YES i send this to the list hopefully to save a 
few of this "meh i can't handle my mail".


To unsubscribe:http://lists.mysql.com/mysql

Am 22.07.2015 um 18:08 schrieb Charles Turner:

Dear Sirs,

I'd like to unsubscribe my subscription to this mailing list.

Thank you so much.

Regards

Andrea Fanni




signature.asc
Description: OpenPGP digital signature

Unsubscribing my sql mailing list

[Charles Turner]

Dear Sirs,

I'd like to unsubscribe my subscription to this mailing list.

Thank you so much.

Regards

Andrea Fanni

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

ANN: SQL Maestro for MySQL 15.5 released

[SQL Maestro Team]

Hi!

SQL Maestro Group announces the release of SQL Maestro for MySQL 15.5,
a powerful Windows GUI solution for MySQL administration and database
development.

The new version is immediately available for download at
http://www.sqlmaestro.com/products/mysql/maestro/

Top 10 new features
===

 1. Support for generated columns.
 2. Support for InnoDB fulltext indexes.
 3. The "Set to current timestamp on update" option in Field Editor.
 4. Updated Backup Database tool.
 5. Executing custom SQL scripts in all database connections
established by the software.
 6. Support for a number of new encryption algorithms in data grids.
 7. Colored tabs and more informative tab hints.
 8. Updated Data Export and Data Import tools.
 9. Improved Data Input Form.
10. Some performance and usability improvements.

In addition to this, several bugs have been fixed and some other minor
improvements and corrections have been made. Full press-release (with
explaining screenshots) is available at:
http://www.sqlmaestro.com/news/company/sql_maestro_for_mysql_15_5_released/

Background information:
-------
SQL Maestro Group offers complete database admin, development and
management tools for MySQL, SQL Server, PostgreSQL, Oracle, DB2,
SQLite, SQL Anywhere, Firebird and MaxDB  providing the highest
performance, scalability and reliability to meet the requirements of
today's database applications.

Sincerely yours,
SQL Maestro Group
http://www.sqlmaestro.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Merging multiple SQL requests

[Johan De Meersman]

- Original Message -
> From: "Jay Ess" 
> Subject: Re: Merging multiple SQL requests

> "UNION is used to combine the result from multiple SELECT statements into a
> single result set."

Yes, but only if your queries return the same number of fields; and you get a 
single resultset out of it.

I was under the impression that OP wanted to simply batch multiple unrelated 
small resultsets in a single network packet.


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Merging multiple SQL requests

[Learner Study]

Thanks for the response!

Looks like the only way MySQL server would merge requests is based on
keywords used by client side, such as UNION etc. If client doesn't
specify such keyword, the server won't automatically merge multiple
requests.

I'm now profiling the MySQL code to identify the root-cause...

Thanks!


On Sat, Feb 21, 2015 at 5:33 PM, Jay Ess  wrote:
> On 2015-02-15 23:55, Learner Study wrote:
>> Hello experts,
>>
>> Is it possible for MySQL server to automatically merge responses for
>> different queries into a single response? Are there any kernel
>> parameters that may dictate that?
>
> "UNION is used to combine the result from multiple SELECT statements into a
> single result set."
> http://dev.mysql.com/doc/refman/5.0/en/union.html
>
>
> --
> 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: Merging multiple SQL requests

[Jay Ess]

On 2015-02-15 23:55, Learner Study wrote:
> Hello experts,
> 
> Is it possible for MySQL server to automatically merge responses for
> different queries into a single response? Are there any kernel
> parameters that may dictate that?

"UNION is used to combine the result from multiple SELECT statements into a
single result set."
http://dev.mysql.com/doc/refman/5.0/en/union.html


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: is a .sql file transactional

[Johan De Meersman]

- Original Message -
> From: "thufir" 
> Subject: is a .sql file transactional

> when you run a .sql file, which modifies a schema, is it transactional?
> Specifically, is it an all-or-nothing proposition?  Or, can some commands
> get executed, some fail?

A file is nothing more than just another input method, it has no impact on 
transactionality or any other parameters.

DDL (schema modification) cannot be done inside a transaction - mysql will 
automatically commit your open transaction before proceeding, if you have one.

Every DDL statement individually is, however, all-or-nothing - the server 
(mostly) makes a copy of the table you're operating on, and only switches them 
out if the change was successful.


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

is a .sql file transactional

[thufir]

when you run a .sql file, which modifies a schema, is it transactional?  
Specifically, is it an all-or-nothing proposition?  Or, can some commands 
get executed, some fail?


thanks,

Thufir


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Merging multiple SQL requests

[Bob Eby]

Hi "Learner",

You might want to try reading about SQL JOIN and/or CLIENT_MULTI_STATEMENTS.


Using databases is all about conglomerating results, but it helps to know
the environment first.

Re: Merging multiple SQL requests

[Johan De Meersman]

...it sounds to me like you're trying to figure out the small things when 
there's probably an elephant standing around somewhere :-)

You can quickly see if your network is troublesome using:
 * traceroute
 * ping
 * network copy (ssh/nfs/samba/whatever) of a large file

Usually, however, it's not the network :-)

Open up a console on your server and run dstat, see what resources are being 
taxed. Turn on the slowlog at 1s (or even 0s) and use pt-query-digest to figure 
out what queries are slow. Profile your application to see which bit takes 
longest to execute.



- Original Message -
> From: "Learner Study" 
> To: "Stewart Smith" 
> Cc: "MySql" , "internals" 
> Sent: Monday, 16 February, 2015 02:07:45
> Subject: Re: Merging multiple SQL requests

> I meant that can MySQL server combine multiple responses for a client
> and send a single TCP packet back to the client.
> But based on your response, I don't think that is possible - please correct?
> 
> Are there are any gothas to debug/investigate MySQL latency? I have
> checked TCP tunables, kernel timer ticks etc. Is there anything else
> to watch out for..pointers would be appreciated.
> 
> Thanks

-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Merging multiple SQL requests

[Stewart Smith]

Learner Study  writes:
> I meant that can MySQL server combine multiple responses for a client
> and send a single TCP packet back to the client.

Unless there's a way to send multiple simultaneous requests, you won't
get multiple responses in one packet.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Merging multiple SQL requests

[Learner Study]

I meant that can MySQL server combine multiple responses for a client
and send a single TCP packet back to the client.
But based on your response, I don't think that is possible - please correct?

Are there are any gothas to debug/investigate MySQL latency? I have
checked TCP tunables, kernel timer ticks etc. Is there anything else
to watch out for..pointers would be appreciated.

Thanks

On Sun, Feb 15, 2015 at 4:10 PM, Stewart Smith
 wrote:
> Learner Study  writes:
>> Is it possible for MySQL server to automatically merge responses for
>> different queries into a single response? Are there any kernel
>> parameters that may dictate that?
>
> I'm not sure what you could be meaning here... In the client protocol?
> to send several responses at once? No, there isn't - the protocol is
> pretty much "one question, one answer. Rinse, repeat".
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Merging multiple SQL requests

[Stewart Smith]

Learner Study  writes:
> Is it possible for MySQL server to automatically merge responses for
> different queries into a single response? Are there any kernel
> parameters that may dictate that?

I'm not sure what you could be meaning here... In the client protocol?
to send several responses at once? No, there isn't - the protocol is
pretty much "one question, one answer. Rinse, repeat".


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Merging multiple SQL requests

[Learner Study]

Hello experts,

Is it possible for MySQL server to automatically merge responses for
different queries into a single response? Are there any kernel
parameters that may dictate that?

Thanks!

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: When will MySQL support the ANSI SQL MERGE command?

[shawn l.green]

Hello ccleve,

On 9/19/2014 3:06 PM, ccleve wrote:
I need to do upserts and I need cross-database compatibility. I'd hate 
to drop support for MySQL in my product. Does MySQL plan to support 
the ANSI-standard MERGE command for upserts?





You appear to be looking for one of these commands that MySQL does support.

INSERT... SELECT ... ON DUPLICATE KEY UPDATE ...
http://dev.mysql.com/doc/refman/5.6/en/insert.html
http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html

REPLACE... SELECT ...
http://dev.mysql.com/doc/refman/5.6/en/replace.html

You can file a feature request to add a new command (MERGE) to the 
parser, here.

http://bugs.mysql.com/

But the odds are low that we will add the predicate MERGE to our 
syntaxes simply because we already have a storage engine called MERGE.

http://dev.mysql.com/doc/refman/5.6/en/merge-storage-engine.html


--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

When will MySQL support the ANSI SQL MERGE command?

[ccleve]

I need to do upserts and I need cross-database compatibility. I'd hate 
to drop support for MySQL in my product. Does MySQL plan to support the 
ANSI-standard MERGE command for upserts?



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: sql syntax error

[Christophe]

Hi,

Le 08/08/2014 17:48, Johan De Meersman a écrit :
> 
> As your code is french, I'll assume you're on Azerty; the backtick is Alt-Gr 
> plus the rightmost key (right next to return) on the middle row. Enjoy 
> spraining your fingers :-p
> 
> /johan
> 

Alt-GR plus '7' for French keyboard layout ;)


Christophe.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: sql syntax error

[Johan De Meersman]

- Original Message -
> From: "florent larose" 
> Subject: sql syntax error
> 
> near ''membres2' WHERE
> [...]
>  FROM 'espace_membre2'.'membres2' WHERE

You were on the right path - mysql is wibbly about quotes. Either remove the 
quotes entirely ( espace_membre2.membres2 ) or try backticks ( ` ). They're 
MySQL's favourite quote, presumably because they were convenient to type on 
whatever abomination Monty used to type code :-)

As your code is french, I'll assume you're on Azerty; the backtick is Alt-Gr 
plus the rightmost key (right next to return) on the middle row. Enjoy 
spraining your fingers :-p

/johan

-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

sql syntax error

[florent larose]

hello, i am working on my personal website wih php 5.4.16 / mysql 5.6.12 (my 
system : windows 7 / wampserver 2).
i have a bug when i am running my connection to database webpage.
My error message is the following :
Erreur SQL : You have an error in your SQL syntax; check the manual that
 corresponds to your MySQL server version for the right syntax to use 
near ''membres2' WHERE
'pseudo_membre' = '\'Flarose59\'' GROUP BY 
'id_membre'' at line 1 
Ligne : 29.

this is my php code :
 $result = sqlquery("SELECT COUNT('id_membre') AS nbr, 'id_membre', 
'pseudo_membre', 'mdp_membre' FROM 'espace_membre2'.'membres2' WHERE 
'pseudo_membre' = '\'".mysql_real_escape_string($_POST['pseudo'])."\'' GROUP BY 
'id_membre'", 1);

I tried several delimitator for the query function (like ' \* ').


  

Re: Migration from SQL Server

[Carlos Proal]

Workbench provides some migration features and supports SQL Server

http://www.mysql.com/products/workbench/migrate/


On Mon, Jul 14, 2014 at 4:47 PM, Felipe Coutinho 
wrote:

> Hello,
>
> I'm a web developer and I'm developing a web application on Rails. I'm
> gonna use MySQL at production (I'm using Amazon RDS with MySQL) for this
> app. But this app already has a version of it (developed at ASP.NET) at
> production using SQL Server. Now I need to migrate the data from SQL Server
> to MySQL.
> The databases are very similar:
> - Few columns change the name.
> - One table was split in two
> - Few columns added
> - Few columns changed the type.
> Do you suggest any tool the helps with this migration?
>
> Thank you,
>
> Felipe.
>
> --
> Felipe Leal Coutinho
> Website <http://www.felipelc.com/> | Linkedin
> <http://www.linkedin.com/pub/felipe-coutinho/21/902/a22>  | Facebook
> <http://www.facebook.com/felipelcoutinho>
>

Migration from SQL Server

[Felipe Coutinho]

Hello,

I'm a web developer and I'm developing a web application on Rails. I'm
gonna use MySQL at production (I'm using Amazon RDS with MySQL) for this
app. But this app already has a version of it (developed at ASP.NET) at
production using SQL Server. Now I need to migrate the data from SQL Server
to MySQL.
The databases are very similar:
- Few columns change the name.
- One table was split in two
- Few columns added
- Few columns changed the type.
Do you suggest any tool the helps with this migration?

Thank you,

Felipe.

--
Felipe Leal Coutinho
Website <http://www.felipelc.com/> | Linkedin
<http://www.linkedin.com/pub/felipe-coutinho/21/902/a22>  | Facebook
<http://www.facebook.com/felipelcoutinho>

career advice - Excel Expert and MySQL SQL specialist

[Lukas Lehner]

Hi

I passed Oracle SQL Expert and working on MySQL 5.6 developer exam. I work
5% of my job with SQL but I am searching for a full time SQL job.

http://shop.oreilly.com/product/0790145363466.do

Is the Excel exam (Microsoft Office Specialist - MOS) useful for getting a
junior SQL pro job? What do you think?
Lukas

RE: LIKE sql optimization

[Zhigang Zhang]

Done.

 

Thand you very much!

 

Zhigang

 

  _  

From: Jesper Wisborg Krogh [mailto:my...@wisborg.dk] 
Sent: Wednesday, February 12, 2014 5:30 PM
To: Morgan Tocker; Zhigang Zhang
Cc: mysql@lists.mysql.com
Subject: Re: LIKE sql optimization

 

On 12/02/2014 13:16, Morgan Tocker wrote:

Hi Zhigang,
 
On Feb 11, 2014, at 8:48 PM, Zhigang Zhang  <mailto:zzgang2...@gmail.com>
 wrote:
 

I want to know the reason, in my opinion, to scan the smaller index data has
better performance than to scan the whole table data. 

 
I think I understand the question - you are asking why MySQL will not index
scan, find matching records, and then look them up rather than table scan?
 
I believe the answer is that there is no way of knowing if 1 row matches, or
all rows match.  In the worst case (where all rows match), it is much more
expensive to traverse between index and data rows for-each-record.
 
So a table scan is a “safe choice" / has less variance.


In addition to what Morgan writes, then with an index scan you will end up
doing a lot of random I/O: even if the index scan itself is one sequential
scan (which is not guaranteed) then for each match, it will be necessary to
look up the actual row. On the other hand a table scan will generally be
more of a sequential read as you already have all the data available for
each match. Random I/O is more expensive than sequential I/O - particularly
on spinning disks - so in general the optimizer will try to reduce the
amount of random I/O.

In some cases though, you may see the index scan be preferred. Assume you
have a query like:

SELECT val FROM table WHERE condition LIKE '%abcd';

and you have an index (condition, val) or (val, condition) then the whole
query can be satisfied from the index (it's called a covering index). In
that case the index scan is usually preferred over the table scan.


For the purpose of using an index to do index lookups to find the matching
rows rather than doing either a table or index scan for WHERE clauses like
"LIKE '%abcd'" you can do a couple of things:

*   Duplicate the column used in the WHERE clause, but reverse the
string. That way the above WHERE clause becomes: WHERE condition_revers LIKE
'dcba%'
This can use an index as it is a left prefix.
*   If you always look for around the same number of characters at the
end in your WHERE clause, you can create a column with just those last
characters, e.g. so the WHERE clause becomes: WHERE condition_suffix =
'abcd'
Do however be careful that you ensure you have enough selectivity that way.
If for example 90% of the rows ends in 'abcd' an index will not do you much
good (unless you are looking for the last 10% of the rows).


Best regards,
Jesper Krogh
MySQL Support

Re: LIKE sql optimization

[Jesper Wisborg Krogh]

On 12/02/2014 13:16, Morgan Tocker wrote:
> Hi Zhigang,
>
> On Feb 11, 2014, at 8:48 PM, Zhigang Zhang  wrote:
>
>> I want to know the reason, in my opinion, to scan the smaller index data has
>> better performance than to scan the whole table data. 
> I think I understand the question - you are asking why MySQL will not index 
> scan, find matching records, and then look them up rather than table scan?
>
> I believe the answer is that there is no way of knowing if 1 row matches, or 
> all rows match.  In the worst case (where all rows match), it is much more 
> expensive to traverse between index and data rows for-each-record.
>
> So a table scan is a “safe choice" / has less variance.

In addition to what Morgan writes, then with an index scan you will end
up doing a lot of random I/O: even if the index scan itself is one
sequential scan (which is not guaranteed) then for each match, it will
be necessary to look up the actual row. On the other hand a table scan
will generally be more of a sequential read as you already have all the
data available for each match. Random I/O is more expensive than
sequential I/O - particularly on spinning disks - so in general the
optimizer will try to reduce the amount of random I/O.

In some cases though, you may see the index scan be preferred. Assume
you have a query like:

SELECT val FROM table WHERE condition LIKE '%abcd';

and you have an index (condition, val) or (val, condition) then the
whole query can be satisfied from the index (it's called a covering
index). In that case the index scan is usually preferred over the table
scan.


For the purpose of using an index to do index lookups to find the
matching rows rather than doing either a table or index scan for WHERE
clauses like "LIKE '%abcd'" you can do a couple of things:

  * Duplicate the column used in the WHERE clause, but reverse the
string. That way the above WHERE clause becomes: WHERE
condition_revers LIKE 'dcba%'
This can use an index as it is a left prefix.
  * If you always look for around the same number of characters at the
end in your WHERE clause, you can create a column with just those
last characters, e.g. so the WHERE clause becomes: WHERE
condition_suffix = 'abcd'
Do however be careful that you ensure you have enough selectivity
that way. If for example 90% of the rows ends in 'abcd' an index
will not do you much good (unless you are looking for the last 10%
of the rows).


Best regards,
Jesper Krogh
MySQL Support

RE: LIKE sql optimization

[Zhigang Zhang]

Thank you very much!

 

Zhigang

 

 

-Original Message-
From: Morgan Tocker [mailto:morgan.toc...@oracle.com] 
Sent: Wednesday, February 12, 2014 10:16 AM
To: Zhigang Zhang
Cc: mysql@lists.mysql.com
Subject: Re: LIKE sql optimization

 

Hi Zhigang,

 

On Feb 11, 2014, at 8:48 PM, Zhigang Zhang  wrote:

 

> I want to know the reason, in my opinion, to scan the smaller index data
has

> better performance than to scan the whole table data. 

 

I think I understand the question - you are asking why MySQL will not index
scan, find matching records, and then look them up rather than table scan?

 

I believe the answer is that there is no way of knowing if 1 row matches, or
all rows match.  In the worst case (where all rows match), it is much more
expensive to traverse between index and data rows for-each-record.

 

So a table scan is a “safe choice" / has less variance.=

Re: LIKE sql optimization

[Morgan Tocker]

Hi Zhigang,

On Feb 11, 2014, at 8:48 PM, Zhigang Zhang  wrote:

> I want to know the reason, in my opinion, to scan the smaller index data has
> better performance than to scan the whole table data. 

I think I understand the question - you are asking why MySQL will not index 
scan, find matching records, and then look them up rather than table scan?

I believe the answer is that there is no way of knowing if 1 row matches, or 
all rows match.  In the worst case (where all rows match), it is much more 
expensive to traverse between index and data rows for-each-record.

So a table scan is a “safe choice" / has less variance.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: LIKE sql optimization

[Zhigang Zhang]

I checked a myisam table index, the index is a copy of the whole field.

 

 

Zhigang

 

 

-Original Message-
From: Reindl Harald [mailto:h.rei...@thelounge.net] 
Sent: Wednesday, February 12, 2014 10:02 AM
To: mysql@lists.mysql.com
Subject: Re: LIKE sql optimization

 

because a index is not just a dumb copy of the whole field

and you simply can't seek in the middle of it?

 

http://en.wikipedia.org/wiki/B-tree

http://mattfleming.com/node/192

 

Am 12.02.2014 02:48, schrieb Zhigang Zhang:

> I want to know the reason, in my opinion, to scan the smaller index data
has

> better performance than to scan the whole table data. 

> 

> From: Mathieu Desharnais [mailto:mdesharn...@diffusion.cc] 

> Sent: Wednesday, February 12, 2014 9:41 AM

> To: Zhigang Zhang; mysql@lists.mysql.com

> Subject: Re: LIKE sql optimization

> 

> Sql database doesn't use index in like statement if it starts with % .. 

> 

> like 'abcd%' would work though...   

> 

> To use an index you can store your value using reverse function and index
it

> .. then your like would use the index.

> 

> 2014-02-11 20:23 GMT-05:00 Zhigang Zhang :

> 

> For example:

> 

> Select * from T where col like ‘%abcd’;

> 

> The table T is myisam table and we created a index on col.

> 

> As we known, this like sql does not use the index created on col, it
confuse

> me, why?

> 

> I think in mysiam engine, the index data is smaller, it can use index link

> list to optimize it so as to reduce the disk scan than to the whole table

> scan.

 

Re: LIKE sql optimization

[Mathieu Desharnais]

Same reason as why composite index works only if you supply first field or
fields ..

example index on a,b,c

if you have a query :

select * from tbl
where a = 'whatever'
and b = 'something

it will use the index ..

but a query like this one :

select * from tbl
where b = 'something'
and c = 'something else'

won't use the index ..


-

Just like an index in a book ...




2014-02-11 21:03 GMT-05:00 louis liu :

> MySQL can't  use index when '%'  condition gives even oracle and you
> can try full-text search
>
>
> 2014-02-12 9:55 GMT+08:00 kitlenv :
>
>> *read how a index works technically*
>>
>>
>>
>> On Wed, Feb 12, 2014 at 12:48 PM, Zhigang Zhang > >wrote:
>>
>> > I want to know the reason, in my opinion, to scan the smaller index data
>> > has
>> > better performance than to scan the whole table data.
>> >
>> >
>> >
>> >
>> >
>> > zhigang
>> >
>> >
>> >
>> >   _
>> >
>> > From: Mathieu Desharnais [mailto:mdesharn...@diffusion.cc]
>> > Sent: Wednesday, February 12, 2014 9:41 AM
>> > To: Zhigang Zhang; mysql@lists.mysql.com
>> > Subject: Re: LIKE sql optimization
>> >
>> >
>> >
>> > Sql database doesn't use index in like statement if it starts with % ..
>> >
>> >
>> >
>> > like 'abcd%' would work though...
>> >
>> >
>> >
>> > To use an index you can store your value using reverse function and
>> index
>> > it
>> > .. then your like would use the index.
>> >
>> >
>> >
>> > 2014-02-11 20:23 GMT-05:00 Zhigang Zhang :
>> >
>> > For example:
>> >
>> >
>> >
>> > Select * from T where col like '%abcd';
>> >
>> >
>> >
>> > The table T is myisam table and we created a index on col.
>> >
>> >
>> >
>> > As we known, this like sql does not use the index created on col, it
>> > confuse
>> > me, why?
>> >
>> >
>> >
>> > I think in mysiam engine, the index data is smaller, it can use index
>> link
>> > list to optimize it so as to reduce the disk scan than to the whole
>> table
>> > scan.
>> >
>> >
>> >
>> > Thanks.
>> >
>> >
>> >
>> > Zhigang
>> >
>> >
>> >
>> >
>>
>
>
>
> --
> Phone: +86 1868061
> Email & Gtalk:  yloui...@gmail.com
> Personal Blog: http://www.vmcd.org
>

Re: LIKE sql optimization

[louis liu]

MySQL can't  use index when '%'  condition gives even oracle and you
can try full-text search


2014-02-12 9:55 GMT+08:00 kitlenv :

> *read how a index works technically*
>
>
> On Wed, Feb 12, 2014 at 12:48 PM, Zhigang Zhang  >wrote:
>
> > I want to know the reason, in my opinion, to scan the smaller index data
> > has
> > better performance than to scan the whole table data.
> >
> >
> >
> >
> >
> > zhigang
> >
> >
> >
> >   _
> >
> > From: Mathieu Desharnais [mailto:mdesharn...@diffusion.cc]
> > Sent: Wednesday, February 12, 2014 9:41 AM
> > To: Zhigang Zhang; mysql@lists.mysql.com
> > Subject: Re: LIKE sql optimization
> >
> >
> >
> > Sql database doesn't use index in like statement if it starts with % ..
> >
> >
> >
> > like 'abcd%' would work though...
> >
> >
> >
> > To use an index you can store your value using reverse function and index
> > it
> > .. then your like would use the index.
> >
> >
> >
> > 2014-02-11 20:23 GMT-05:00 Zhigang Zhang :
> >
> > For example:
> >
> >
> >
> > Select * from T where col like '%abcd';
> >
> >
> >
> > The table T is myisam table and we created a index on col.
> >
> >
> >
> > As we known, this like sql does not use the index created on col, it
> > confuse
> > me, why?
> >
> >
> >
> > I think in mysiam engine, the index data is smaller, it can use index
> link
> > list to optimize it so as to reduce the disk scan than to the whole table
> > scan.
> >
> >
> >
> > Thanks.
> >
> >
> >
> > Zhigang
> >
> >
> >
> >
>



-- 
Phone: +86 1868061
Email & Gtalk:  yloui...@gmail.com
Personal Blog: http://www.vmcd.org

Re: LIKE sql optimization

[Reindl Harald]

because a index is not just a dumb copy of the whole field
and you simply can't seek in the middle of it?

http://en.wikipedia.org/wiki/B-tree
http://mattfleming.com/node/192

Am 12.02.2014 02:48, schrieb Zhigang Zhang:
> I want to know the reason, in my opinion, to scan the smaller index data has
> better performance than to scan the whole table data. 
>
> From: Mathieu Desharnais [mailto:mdesharn...@diffusion.cc] 
> Sent: Wednesday, February 12, 2014 9:41 AM
> To: Zhigang Zhang; mysql@lists.mysql.com
> Subject: Re: LIKE sql optimization
> 
> Sql database doesn't use index in like statement if it starts with % .. 
> 
> like 'abcd%' would work though...   
> 
> To use an index you can store your value using reverse function and index it
> .. then your like would use the index.
> 
> 2014-02-11 20:23 GMT-05:00 Zhigang Zhang :
> 
> For example:
> 
> Select * from T where col like ‘%abcd’;
> 
> The table T is myisam table and we created a index on col.
>
> As we known, this like sql does not use the index created on col, it confuse
> me, why?
> 
> I think in mysiam engine, the index data is smaller, it can use index link
> list to optimize it so as to reduce the disk scan than to the whole table
> scan.



signature.asc
Description: OpenPGP digital signature

Re: LIKE sql optimization

[kitlenv]

*read how a index works technically*


On Wed, Feb 12, 2014 at 12:48 PM, Zhigang Zhang wrote:

> I want to know the reason, in my opinion, to scan the smaller index data
> has
> better performance than to scan the whole table data.
>
>
>
>
>
> zhigang
>
>
>
>   _
>
> From: Mathieu Desharnais [mailto:mdesharn...@diffusion.cc]
> Sent: Wednesday, February 12, 2014 9:41 AM
> To: Zhigang Zhang; mysql@lists.mysql.com
> Subject: Re: LIKE sql optimization
>
>
>
> Sql database doesn't use index in like statement if it starts with % ..
>
>
>
> like 'abcd%' would work though...
>
>
>
> To use an index you can store your value using reverse function and index
> it
> .. then your like would use the index.
>
>
>
> 2014-02-11 20:23 GMT-05:00 Zhigang Zhang :
>
> For example:
>
>
>
> Select * from T where col like '%abcd';
>
>
>
> The table T is myisam table and we created a index on col.
>
>
>
> As we known, this like sql does not use the index created on col, it
> confuse
> me, why?
>
>
>
> I think in mysiam engine, the index data is smaller, it can use index link
> list to optimize it so as to reduce the disk scan than to the whole table
> scan.
>
>
>
> Thanks.
>
>
>
> Zhigang
>
>
>
>

RE: LIKE sql optimization

[Zhigang Zhang]

I want to know the reason, in my opinion, to scan the smaller index data has
better performance than to scan the whole table data. 

 

 

zhigang

 

  _  

From: Mathieu Desharnais [mailto:mdesharn...@diffusion.cc] 
Sent: Wednesday, February 12, 2014 9:41 AM
To: Zhigang Zhang; mysql@lists.mysql.com
Subject: Re: LIKE sql optimization

 

Sql database doesn't use index in like statement if it starts with % .. 

 

like 'abcd%' would work though... 

 

To use an index you can store your value using reverse function and index it
.. then your like would use the index.

 

2014-02-11 20:23 GMT-05:00 Zhigang Zhang :

For example:



Select * from T where col like ‘%abcd’;



The table T is myisam table and we created a index on col.



As we known, this like sql does not use the index created on col, it confuse
me, why?



I think in mysiam engine, the index data is smaller, it can use index link
list to optimize it so as to reduce the disk scan than to the whole table
scan.



Thanks.



Zhigang

 

Re: LIKE sql optimization

[Mathieu Desharnais]

Sql database doesn't use index in like statement if it starts with % ..

like 'abcd%' would work though...

To use an index you can store your value using reverse function and index
it .. then your like would use the index.


2014-02-11 20:23 GMT-05:00 Zhigang Zhang :

> For example:
>
>
>
> Select * from T where col like '%abcd';
>
>
>
> The table T is myisam table and we created a index on col.
>
>
>
> As we known, this like sql does not use the index created on col, it
> confuse
> me, why?
>
>
>
> I think in mysiam engine, the index data is smaller, it can use index link
> list to optimize it so as to reduce the disk scan than to the whole table
> scan.
>
>
>
> Thanks.
>
>
>
> Zhigang
>
>

Re: LIKE sql optimization

[Reindl Harald]

Am 12.02.2014 02:23, schrieb Zhigang Zhang:
> For example:
> 
> Select * from T where col like ‘%abcd’;
> 
> The table T is myisam table and we created a index on col.
> 
> As we known, this like sql does not use the index created on col, it confuse
> me, why?
> 
> I think in mysiam engine, the index data is smaller, it can use index link
> list to optimize it so as to reduce the disk scan than to the whole table
> scan

because ‘%abcd’ can't work in case of a index
how do you imagine that?
read how a index works technically

'abcd%' may work but '%abcd' is impossible
independent what engine, this don't work and won't ever work

you may have luck with fulltext search (and it's other drawbacks)
https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html



signature.asc
Description: OpenPGP digital signature

LIKE sql optimization

[Zhigang Zhang]

For example:

 

Select * from T where col like ‘%abcd’;

 

The table T is myisam table and we created a index on col.

 

As we known, this like sql does not use the index created on col, it confuse
me, why?

 

I think in mysiam engine, the index data is smaller, it can use index link
list to optimize it so as to reduce the disk scan than to the whole table
scan.

 

Thanks.

 

Zhigang

ANN: SQL Maestro for MySQL 14.2 released

[SQL Maestro Team]

Hi!

SQL Maestro Group announces the release of SQL Maestro for MySQL 14.2,
a powerful Windows GUI solution for MySQL administration and database
development.

The new version is immediately available for download at
http://www.sqlmaestro.com/products/mysql/maestro/

Top 10 new features
===

 1. Support for a number of MySQL 5.6 and 5.7 new features.
 2. A new implementation of HTTP tunnel based on the MySQLi PHP extension.
 3. Keyboard-interactive SSH authentication.
 4. New Nullable Column Checker tool.
 6. Improved support for the bit data type.
 7. Database default character set and collation management.
 8. Data import from several files to a single table.
 9. Enhanced Column Builder for text files with fixed column width.
 9. Full-featured data grids when working via HTTP tunneling.
10. Data export to the JSON format.

In addition to this, several bugs have been fixed and some other minor
improvements and corrections have been made. Full press-release (with
explaining screenshots) is available at:
http://www.sqlmaestro.com/news/company/sql_maestro_for_mysql_14_2_released/

Background information:
---
SQL Maestro Group offers complete database admin, development and
management tools for MySQL, SQL Server, PostgreSQL, Oracle, DB2,
SQLite, SQL Anywhere, Firebird and MaxDB  providing the highest
performance, scalability and reliability to meet the requirements of
today's database applications.

Sincerely yours,
SQL Maestro Group
http://www.sqlmaestro.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: MySQL 5.0.0 [2003] vs. MySQL 5.6 [2013] from a SQL and SQL/PSM developer viewpoint

[Reindl Harald]

Am 07.01.2014 13:48, schrieb Lukas Lehner:
> Are there big changes between MySQL 5.0.0 vs. MySQL 5.6? I am only
> interested in developer changes (not admin)
> Can I use development books for MySQL 5.0.0 [2003] and use the code an
> recent MariaDB and MySQL releases?

clearly yes

the *other direction* may be problematic in case of unsupported
features in old version - hence you can even use 3.0 books



signature.asc
Description: OpenPGP digital signature

MySQL 5.0.0 [2003] vs. MySQL 5.6 [2013] from a SQL and SQL/PSM developer viewpoint

[Lukas Lehner]

Hi

Are there big changes between MySQL 5.0.0 vs. MySQL 5.6? I am only
interested in developer changes (not admin)
Can I use development books for MySQL 5.0.0 [2003] and use the code an
recent MariaDB and MySQL releases?

Re: How do I mysqldump different database tables to the same .sql file?

[Johan De Meersman]

--databases, methinks.

- Original Message -
> From: "Daevid Vincent" 
> To: mysql@lists.mysql.com
> Sent: Thursday, 21 November, 2013 10:44:39 PM
> Subject: How do I mysqldump different database tables to the same .sql file?
> 
> I'm working on some code where I am trying to merge two customer accounts
> (we get people signing up under different usernames, emails, or just create
> a new account sometimes). I want to test it, and so I need a way to restore
> the data in the particular tables. Taking a dump of all the DBs and tables
> is not feasible as it's massive, and importing (with indexes) takes HOURS. I
> just want only the tables that are relevant. I can find all the tables that
> have `customer_id` in them with this magic incantation:
>  
> SELECT `TABLE_NAME`,`TABLE_SCHEMA` FROM `information_schema`.`COLUMNS` WHERE
> `COLUMN_NAME` = 'customer_id' ORDER BY `TABLE_SCHEMA`, `TABLE_NAME`
>  
> Then I crafted this, but it pukes on the db name portion. :-(
>  
> mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
> --complete-insert --insert-ignore --create-options --quick --force
> --set-charset --disable-keys --quote-names --comments --verbose --tables
> member_sessions.users_last_login support.tickets mydb1.clear_passwords
> mydb1.crak_subscriptions mydb1.customers mydb1.customers_free
> mydb1.customers_free_tracking mydb1.customers_log
> mydb1.customers_subscriptions mydb1.customers_transactions mydb1.players
> mydb1content.actors_comments mydb1content.actor_collections
> mydb1content.actor_likes_users mydb1content.collections
> mydb1content.dvd_likes_users mydb1content.free_videos
> mydb1content.genre_collections mydb1content.playlists
> mydb1content.poll_votes mydb1content.scenes_comments
> mydb1content.scenes_ratings_users_new2 mydb1content.scene_collections
> mydb1content.scene_likes_users mydb1content.videos_downloaded
> mydb1content.videos_viewed > merge_backup.sql
>  
> -- Connecting to localhost...
> mysqldump: Got error: 1049: Unknown database
> 'member_sessions.users_last_login' when selecting the database
> -- Disconnecting from localhost...
>  
> I searched a bit and found that it seems I have to split this into multiple
> statements and append like I'm back in 1980. *sigh*
>  
> mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
> --complete-insert --insert-ignore --create-options --quick --force
> --set-charset --disable-keys --quote-names --comments --verbose --database
> member_sessions --tables users_last_login >> merge_backup.sql
> mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
> --complete-insert --insert-ignore --create-options --quick --force
> --set-charset --disable-keys --quote-names --comments --verbose --database
> support --tables tickets >> merge_backup.sql
> mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
> --complete-insert --insert-ignore --create-options --quick --force
> --set-charset --disable-keys --quote-names --comments --verbose --database
> mydb1 --tables clear_passwords customers customers_free
> customers_free_tracking customers_log customers_subscriptions
> customers_transactions players >> merge_backup.sql
> mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
> --complete-insert --insert-ignore --create-options --quick --force
> --set-charset --disable-keys --quote-names --comments --verbose --database
> content --tables actors_comments actor_collections actor_likes_users
> collections dvd_likes_users free_videos genre_collections playlists
> poll_votes scenes_comments scenes_ratings_users_new2 scene_collections
> scene_likes_users videos_downloaded videos_viewed >> merge_backup.sql
>  
> The critical flaw here is that the mysqldump program does NOT put the
> necessary "USE DATABASE" statement in each of these dumps since there is
> only one DB after the -database apparently. UGH. Nor do I see a command line
> option to force it to output this seemingly obvious statement.
>  
> It's a pretty significant shortcoming of mysqldump if you ask me that I
> can't do it the way I had it in the first example since that's pretty much
> standard SQL convetion of db.table.column format. And even more baffling is
> why it wouldn't dump out the "USE" statement always even if there is only
> one DB. It's a few characters and would save a lot of headaches in case
> someone tried to dump their .sql file into the wrong DB on accident.
>  
> Plus it's not easy to edit a 2.6GB file to manually insert these "USE"
> lines.
>  
> Is there a way to do this with some command line option I'm not seeing in
> the man page?
> 

-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: How do I mysqldump different database tables to the same .sql file?

[Daevid Vincent]

Except that it outputs the "USE" statement if you have more than one
database, so your theory doesn't hold a lot of water IMHO. Not to mention
it's near the very top of the output so it's pretty easy to trim it off if
you REALLY needed to move the DB (which I presume is not as frequently as
simply wanting a backup/dump of a database to restore).

Thanks for the shell script suggestion, that is what I've done already to
work around this silliness.

> -Original Message-
> From: Michael Dykman [mailto:mdyk...@gmail.com]
> Sent: Thursday, November 21, 2013 1:59 PM
> To: MySql
> Subject: Re: How do I mysqldump different database tables to the same .sql
> file?
> 
> There is a good reason that the USE database is not output in those
dumps..
>  it would make the tool very difficult to use for moving data around.
> 
> If I might suggest, a simple workaround is to create a shell script along
> these lines..  you might to do something a little more sophisticated.
> 
> #
> #!/bin/sh
> 
> echo  "USE `database1`;" > outflfile.sql
> mysqldump -(firstsetofoptions) >> outfile.sql
> echo  "USE `database2`;" >> outflfile.sql
> mysqldump -(secondsetofoptions) >> outfile.sql
> 
> 
> 
> 
> On Thu, Nov 21, 2013 at 4:44 PM, Daevid Vincent  wrote:
> 
> > I'm working on some code where I am trying to merge two customer
accounts
> > (we get people signing up under different usernames, emails, or just
> create
> > a new account sometimes). I want to test it, and so I need a way to
> restore
> > the data in the particular tables. Taking a dump of all the DBs and
tables
> > is not feasible as it's massive, and importing (with indexes) takes
HOURS.
> > I
> > just want only the tables that are relevant. I can find all the tables
> that
> > have `customer_id` in them with this magic incantation:
> >
> > SELECT `TABLE_NAME`,`TABLE_SCHEMA` FROM `information_schema`.`COLUMNS`
> > WHERE
> > `COLUMN_NAME` = 'customer_id' ORDER BY `TABLE_SCHEMA`, `TABLE_NAME`
> >
> > Then I crafted this, but it pukes on the db name portion. :-(
> >
> > mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
> > --complete-insert --insert-ignore --create-options --quick --force
> > --set-charset --disable-keys --quote-names --comments --verbose --tables
> > member_sessions.users_last_login support.tickets mydb1.clear_passwords
> > mydb1.crak_subscriptions mydb1.customers mydb1.customers_free
> > mydb1.customers_free_tracking mydb1.customers_log
> > mydb1.customers_subscriptions mydb1.customers_transactions mydb1.players
> > mydb1content.actors_comments mydb1content.actor_collections
> > mydb1content.actor_likes_users mydb1content.collections
> > mydb1content.dvd_likes_users mydb1content.free_videos
> > mydb1content.genre_collections mydb1content.playlists
> > mydb1content.poll_votes mydb1content.scenes_comments
> > mydb1content.scenes_ratings_users_new2 mydb1content.scene_collections
> > mydb1content.scene_likes_users mydb1content.videos_downloaded
> > mydb1content.videos_viewed > merge_backup.sql
> >
> > -- Connecting to localhost...
> > mysqldump: Got error: 1049: Unknown database
> > 'member_sessions.users_last_login' when selecting the database
> > -- Disconnecting from localhost...
> >
> > I searched a bit and found that it seems I have to split this into
> multiple
> > statements and append like I'm back in 1980. *sigh*
> >
> > mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
> > --complete-insert --insert-ignore --create-options --quick --force
> > --set-charset --disable-keys --quote-names --comments --verbose
--database
> > member_sessions --tables users_last_login >> merge_backup.sql
> > mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
> > --complete-insert --insert-ignore --create-options --quick --force
> > --set-charset --disable-keys --quote-names --comments --verbose
--database
> > support --tables tickets >> merge_backup.sql
> > mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
> > --complete-insert --insert-ignore --create-options --quick --force
> > --set-charset --disable-keys --quote-names --comments --verbose
--database
> > mydb1 --tables clear_passwords customers customers_free
> > customers_free_tracking customers_log customers_subscriptions
> > customers_transactions players >> merge_backup.sql
> > mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
> > --complete-i

Re: How do I mysqldump different database tables to the same .sql file?

[Michael Dykman]

There is a good reason that the USE database is not output in those dumps..
 it would make the tool very difficult to use for moving data around.

If I might suggest, a simple workaround is to create a shell script along
these lines..  you might to do something a little more sophisticated.

#
#!/bin/sh

echo  "USE `database1`;" > outflfile.sql
mysqldump -(firstsetofoptions) >> outfile.sql
echo  "USE `database2`;" >> outflfile.sql
mysqldump -(secondsetofoptions) >> outfile.sql




On Thu, Nov 21, 2013 at 4:44 PM, Daevid Vincent  wrote:

> I'm working on some code where I am trying to merge two customer accounts
> (we get people signing up under different usernames, emails, or just create
> a new account sometimes). I want to test it, and so I need a way to restore
> the data in the particular tables. Taking a dump of all the DBs and tables
> is not feasible as it's massive, and importing (with indexes) takes HOURS.
> I
> just want only the tables that are relevant. I can find all the tables that
> have `customer_id` in them with this magic incantation:
>
> SELECT `TABLE_NAME`,`TABLE_SCHEMA` FROM `information_schema`.`COLUMNS`
> WHERE
> `COLUMN_NAME` = 'customer_id' ORDER BY `TABLE_SCHEMA`, `TABLE_NAME`
>
> Then I crafted this, but it pukes on the db name portion. :-(
>
> mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
> --complete-insert --insert-ignore --create-options --quick --force
> --set-charset --disable-keys --quote-names --comments --verbose --tables
> member_sessions.users_last_login support.tickets mydb1.clear_passwords
> mydb1.crak_subscriptions mydb1.customers mydb1.customers_free
> mydb1.customers_free_tracking mydb1.customers_log
> mydb1.customers_subscriptions mydb1.customers_transactions mydb1.players
> mydb1content.actors_comments mydb1content.actor_collections
> mydb1content.actor_likes_users mydb1content.collections
> mydb1content.dvd_likes_users mydb1content.free_videos
> mydb1content.genre_collections mydb1content.playlists
> mydb1content.poll_votes mydb1content.scenes_comments
> mydb1content.scenes_ratings_users_new2 mydb1content.scene_collections
> mydb1content.scene_likes_users mydb1content.videos_downloaded
> mydb1content.videos_viewed > merge_backup.sql
>
> -- Connecting to localhost...
> mysqldump: Got error: 1049: Unknown database
> 'member_sessions.users_last_login' when selecting the database
> -- Disconnecting from localhost...
>
> I searched a bit and found that it seems I have to split this into multiple
> statements and append like I'm back in 1980. *sigh*
>
> mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
> --complete-insert --insert-ignore --create-options --quick --force
> --set-charset --disable-keys --quote-names --comments --verbose --database
> member_sessions --tables users_last_login >> merge_backup.sql
> mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
> --complete-insert --insert-ignore --create-options --quick --force
> --set-charset --disable-keys --quote-names --comments --verbose --database
> support --tables tickets >> merge_backup.sql
> mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
> --complete-insert --insert-ignore --create-options --quick --force
> --set-charset --disable-keys --quote-names --comments --verbose --database
> mydb1 --tables clear_passwords customers customers_free
> customers_free_tracking customers_log customers_subscriptions
> customers_transactions players >> merge_backup.sql
> mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
> --complete-insert --insert-ignore --create-options --quick --force
> --set-charset --disable-keys --quote-names --comments --verbose --database
> content --tables actors_comments actor_collections actor_likes_users
> collections dvd_likes_users free_videos genre_collections playlists
> poll_votes scenes_comments scenes_ratings_users_new2 scene_collections
> scene_likes_users videos_downloaded videos_viewed >> merge_backup.sql
>
> The critical flaw here is that the mysqldump program does NOT put the
> necessary "USE DATABASE" statement in each of these dumps since there is
> only one DB after the -database apparently. UGH. Nor do I see a command
> line
> option to force it to output this seemingly obvious statement.
>
> It's a pretty significant shortcoming of mysqldump if you ask me that I
> can't do it the way I had it in the first example since that's pretty much
> standard SQL convetion of db.table.column format. And even more baffling is
> why it wouldn't dump out the "USE" statement always even if there is only
&

How do I mysqldump different database tables to the same .sql file?

[Daevid Vincent]

I'm working on some code where I am trying to merge two customer accounts
(we get people signing up under different usernames, emails, or just create
a new account sometimes). I want to test it, and so I need a way to restore
the data in the particular tables. Taking a dump of all the DBs and tables
is not feasible as it's massive, and importing (with indexes) takes HOURS. I
just want only the tables that are relevant. I can find all the tables that
have `customer_id` in them with this magic incantation:
 
SELECT `TABLE_NAME`,`TABLE_SCHEMA` FROM `information_schema`.`COLUMNS` WHERE
`COLUMN_NAME` = 'customer_id' ORDER BY `TABLE_SCHEMA`, `TABLE_NAME`
 
Then I crafted this, but it pukes on the db name portion. :-(
 
mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
--complete-insert --insert-ignore --create-options --quick --force
--set-charset --disable-keys --quote-names --comments --verbose --tables
member_sessions.users_last_login support.tickets mydb1.clear_passwords
mydb1.crak_subscriptions mydb1.customers mydb1.customers_free
mydb1.customers_free_tracking mydb1.customers_log
mydb1.customers_subscriptions mydb1.customers_transactions mydb1.players
mydb1content.actors_comments mydb1content.actor_collections
mydb1content.actor_likes_users mydb1content.collections
mydb1content.dvd_likes_users mydb1content.free_videos
mydb1content.genre_collections mydb1content.playlists
mydb1content.poll_votes mydb1content.scenes_comments
mydb1content.scenes_ratings_users_new2 mydb1content.scene_collections
mydb1content.scene_likes_users mydb1content.videos_downloaded
mydb1content.videos_viewed > merge_backup.sql
 
-- Connecting to localhost...
mysqldump: Got error: 1049: Unknown database
'member_sessions.users_last_login' when selecting the database
-- Disconnecting from localhost...
 
I searched a bit and found that it seems I have to split this into multiple
statements and append like I'm back in 1980. *sigh*
 
mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
--complete-insert --insert-ignore --create-options --quick --force
--set-charset --disable-keys --quote-names --comments --verbose --database
member_sessions --tables users_last_login >> merge_backup.sql
mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
--complete-insert --insert-ignore --create-options --quick --force
--set-charset --disable-keys --quote-names --comments --verbose --database
support --tables tickets >> merge_backup.sql
mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
--complete-insert --insert-ignore --create-options --quick --force
--set-charset --disable-keys --quote-names --comments --verbose --database
mydb1 --tables clear_passwords customers customers_free
customers_free_tracking customers_log customers_subscriptions
customers_transactions players >> merge_backup.sql
mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
--complete-insert --insert-ignore --create-options --quick --force
--set-charset --disable-keys --quote-names --comments --verbose --database
content --tables actors_comments actor_collections actor_likes_users
collections dvd_likes_users free_videos genre_collections playlists
poll_votes scenes_comments scenes_ratings_users_new2 scene_collections
scene_likes_users videos_downloaded videos_viewed >> merge_backup.sql
 
The critical flaw here is that the mysqldump program does NOT put the
necessary "USE DATABASE" statement in each of these dumps since there is
only one DB after the -database apparently. UGH. Nor do I see a command line
option to force it to output this seemingly obvious statement.
 
It's a pretty significant shortcoming of mysqldump if you ask me that I
can't do it the way I had it in the first example since that's pretty much
standard SQL convetion of db.table.column format. And even more baffling is
why it wouldn't dump out the "USE" statement always even if there is only
one DB. It's a few characters and would save a lot of headaches in case
someone tried to dump their .sql file into the wrong DB on accident.
 
Plus it's not easy to edit a 2.6GB file to manually insert these "USE"
lines.
 
Is there a way to do this with some command line option I'm not seeing in
the man page?

Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?

[Reindl Harald]

that is what we all know

but how to get rid of them?
but *why* they are not cleaned up?

* the global tablespace knows about them
* nothing is using them really
* so why can mysqld not cleanup this mess?

if you delete them all works fine but each start
the error-log is cluttered

Am 25.06.2013 17:46, schrieb Rick James:
> If a crash occurs in the middle of an ALTER, the files may not get cleaned up.
> 
>> -Original Message-
>> From: Reindl Harald [mailto:h.rei...@thelounge.net]
>> Sent: Thursday, June 20, 2013 12:57 PM
>> To: mysql@lists.mysql.com
>> Subject: Re: How can I drop a table that is named “logs/#sql-ib203” and
>> appeared after MySQL crash?
>>
>> i know what happened but how get rid of these two bullshit files after
>> *three years* not touched and used by mysqld
>>
>> Am 20.06.2013 21:28, schrieb Rick James:
>>> #sql files are temp tables that vanish when the ALTER (or whatever)
>> finishes.  If you find one sitting around, it sounds like a crash happened
>> in the middle of the ALTER.
>>>
>>>> -Original Message-
>>>> From: Reindl Harald [mailto:h.rei...@thelounge.net]
>>>> Sent: Wednesday, June 19, 2013 12:19 PM
>>>> To: mysql@lists.mysql.com
>>>> Subject: Re: How can I drop a table that is named “logs/#sql-ib203”
>>>> and appeared after MySQL crash?
>>>>
>>>>
>>>>
>>>> Am 19.06.2013 21:00, schrieb Franck Dernoncourt:
>>>>> `USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`,
>>>>> but when trying to `ALTER` the table that was being changed during
>>>>> the crash MySQL complains about the existence of the table
>>>>> `logs/#sql-
>>>> ib203`:
>>>>>
>>>>>> ERROR 1050: Table 'logs/#sql-ib203' already exists
>>>>>>
>>>>>> SQL Statement:
>>>>>>
>>>>>> ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN
>>>>>> `agent` , DROP COLUMN `ip` , DROP COLUMN `event_source`
>>>>>
>>>>> DROP TABLE `logs/#sql-ib203`; does not work, neither do some name
>>>>> variants `/#sql-ib203`, `#sql-ib203`, `sql-ib203`. (respectively
>>>>> Error
>>>> Code: 1051.
>>>>> Unknown table 'logs.logs/#sql-ib203',  Error Code: 1051. Unknown
>>>>> table
>>>>>
>>>>> I use innodb_file_per_table. There was a "mysql_datadir/logs/#sql-
>>>> ib203.ibd"
>>>>> file (or maybe .frm, sorry I forgot) that I deleted.
>>>>>
>>>>> Any idea how to get rid of this ghostly table `logs/#sql-ib203`?
>>>>
>>>> interesting question, i have identical crap since 2009 also after a
>>>> crash und these "blind table" has the same structure as a used
>>>> existing one
>>>>
>>>> if i delete the #-files mysql whines every startup while they are
>>>> never used and it is ridiculous that there are references in the
>>>> table space to this useless crap and no mysql version from 5.1.8 to
>>>> 5.5.32 is fixing this
>>>>
>>>> -rw-rw 1 mysql mysql 8,4K 2011-07-24 11:49 #sql2-704-271.frm
>>>> -rw-rw 1 mysql mysql  64K 2011-07-24 11:49 #sql2-704-271.ibd
> 

-- 

Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / CISO / Software-Development
m: +43 (676) 40 221 40, p: +43 (1) 595 3999 33
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm



signature.asc
Description: OpenPGP digital signature

RE: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?

[Rick James]

If a crash occurs in the middle of an ALTER, the files may not get cleaned up.

> -Original Message-
> From: Reindl Harald [mailto:h.rei...@thelounge.net]
> Sent: Thursday, June 20, 2013 12:57 PM
> To: mysql@lists.mysql.com
> Subject: Re: How can I drop a table that is named “logs/#sql-ib203” and
> appeared after MySQL crash?
> 
> i know what happened but how get rid of these two bullshit files after
> *three years* not touched and used by mysqld
> 
> Am 20.06.2013 21:28, schrieb Rick James:
> > #sql files are temp tables that vanish when the ALTER (or whatever)
> finishes.  If you find one sitting around, it sounds like a crash happened
> in the middle of the ALTER.
> >
> >> -Original Message-
> >> From: Reindl Harald [mailto:h.rei...@thelounge.net]
> >> Sent: Wednesday, June 19, 2013 12:19 PM
> >> To: mysql@lists.mysql.com
> >> Subject: Re: How can I drop a table that is named “logs/#sql-ib203”
> >> and appeared after MySQL crash?
> >>
> >>
> >>
> >> Am 19.06.2013 21:00, schrieb Franck Dernoncourt:
> >>> `USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`,
> >>> but when trying to `ALTER` the table that was being changed during
> >>> the crash MySQL complains about the existence of the table
> >>> `logs/#sql-
> >> ib203`:
> >>>
> >>>> ERROR 1050: Table 'logs/#sql-ib203' already exists
> >>>>
> >>>> SQL Statement:
> >>>>
> >>>> ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN
> >>>> `agent` , DROP COLUMN `ip` , DROP COLUMN `event_source`
> >>>
> >>> DROP TABLE `logs/#sql-ib203`; does not work, neither do some name
> >>> variants `/#sql-ib203`, `#sql-ib203`, `sql-ib203`. (respectively
> >>> Error
> >> Code: 1051.
> >>> Unknown table 'logs.logs/#sql-ib203',  Error Code: 1051. Unknown
> >>> table
> >>>
> >>> I use innodb_file_per_table. There was a "mysql_datadir/logs/#sql-
> >> ib203.ibd"
> >>> file (or maybe .frm, sorry I forgot) that I deleted.
> >>>
> >>> Any idea how to get rid of this ghostly table `logs/#sql-ib203`?
> >>
> >> interesting question, i have identical crap since 2009 also after a
> >> crash und these "blind table" has the same structure as a used
> >> existing one
> >>
> >> if i delete the #-files mysql whines every startup while they are
> >> never used and it is ridiculous that there are references in the
> >> table space to this useless crap and no mysql version from 5.1.8 to
> >> 5.5.32 is fixing this
> >>
> >> -rw-rw 1 mysql mysql 8,4K 2011-07-24 11:49 #sql2-704-271.frm
> >> -rw-rw 1 mysql mysql  64K 2011-07-24 11:49 #sql2-704-271.ibd


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?

[Jesper Wisborg Krogh]

Hi Frank,

On 20/06/2013 05:00, Franck Dernoncourt wrote:

Hi all,

A table `logs/#sql-ib203` appeared after a MySQL crash due to disk space
shortage while deleting some attributes in a table in the `logs` database
and adding an index.

`USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`, but
when trying to `ALTER` the table that was being changed during the crash
MySQL complains about the existence of the table `logs/#sql-ib203`:


It's a bit of a workaround, but you should be able to get rid of the 
file using the steps below. I'm using an example where I killed mysqld 
while it was dropping the to_date column from the salaries table in the 
employees sample database:


   mysql> SHOW CREATE TABLE salaries\G
   *** 1. row ***
   Table: salaries
   Create Table: CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`),
  KEY `emp_no` (`emp_no`),
  CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES
   `employees` (`emp_no`) ON DELETE CASCADE
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1
   1 row in set (0.00 sec)

   mysql> ALTER TABLE salaries DROP COLUMN to_date;
   ERROR 2013 (HY000): Lost connection to MySQL server during query

   mysql$ ls -1 employees/#*
   employees/#sql-36ab_2.frm
   employees/#sql-ib30.ibd

1. Create a temporary table with the same structure as the salaries
   table would have looked after the ALTER that failed:

   mysql> CREATE TABLE tmp LIKE salaries; ALTER TABLE tmp DROP COLUMN
   to_date;

2. Shutdown MySQL.
3. Copy the .frm file from the tmp table to have the same name as the
   #sql-*.ibd file:

   mysql$ cp employees/tmp.frm employees/#sql-ib30.frm

4. Start MySQL again.
5. Drop the #sql-ib30.frm table:

   mysql> DROP TABLE `#mysql50##sql-ib30`;
   Query OK, 0 rows affected (0.01 sec)

6. Do the same for the #sql*.frm file (it'll get removed even though
   you get an error):

   mysql> DROP TABLE `#mysql50##sql-36ab_2`;
   ERROR 1051 (42S02): Unknown table 'employees.#mysql50##sql-36ab_2'

I know it's not very elegant, but should work. The #mysql50# prefix 
tells MySQL to not encode the table name when mapping to the file system 
(https://dev.mysql.com/doc/refman/5.6/en/identifier-mapping.html).


Best regards,
Jesper Krogh
MySQL Support

RE: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?

[Rick James]

#sql files are temp tables that vanish when the ALTER (or whatever) finishes.  
If you find one sitting around, it sounds like a crash happened in the middle 
of the ALTER.

> -Original Message-
> From: Reindl Harald [mailto:h.rei...@thelounge.net]
> Sent: Wednesday, June 19, 2013 12:19 PM
> To: mysql@lists.mysql.com
> Subject: Re: How can I drop a table that is named “logs/#sql-ib203” and
> appeared after MySQL crash?
> 
> 
> 
> Am 19.06.2013 21:00, schrieb Franck Dernoncourt:
> > `USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`,
> > but when trying to `ALTER` the table that was being changed during the
> > crash MySQL complains about the existence of the table `logs/#sql-
> ib203`:
> >
> >> ERROR 1050: Table 'logs/#sql-ib203' already exists
> >>
> >> SQL Statement:
> >>
> >> ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN
> >> `agent` , DROP COLUMN `ip` , DROP COLUMN `event_source`
> >
> > DROP TABLE `logs/#sql-ib203`; does not work, neither do some name
> > variants `/#sql-ib203`, `#sql-ib203`, `sql-ib203`. (respectively Error
> Code: 1051.
> > Unknown table 'logs.logs/#sql-ib203',  Error Code: 1051. Unknown table
> >
> > I use innodb_file_per_table. There was a "mysql_datadir/logs/#sql-
> ib203.ibd"
> > file (or maybe .frm, sorry I forgot) that I deleted.
> >
> > Any idea how to get rid of this ghostly table `logs/#sql-ib203`?
> 
> interesting question, i have identical crap since 2009 also after a crash
> und these "blind table" has the same structure as a used existing one
> 
> if i delete the #-files mysql whines every startup while they are never
> used and it is ridiculous that there are references in the table space to
> this useless crap and no mysql version from 5.1.8 to 5.5.32 is fixing this
> 
> -rw-rw 1 mysql mysql 8,4K 2011-07-24 11:49 #sql2-704-271.frm
> -rw-rw 1 mysql mysql  64K 2011-07-24 11:49 #sql2-704-271.ibd
> 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?

[Reindl Harald]

i know what happened but how get rid of these two bullshit files
after *three years* not touched and used by mysqld

Am 20.06.2013 21:28, schrieb Rick James:
> #sql files are temp tables that vanish when the ALTER (or whatever) finishes. 
>  If you find one sitting around, it sounds like a crash happened in the 
> middle of the ALTER.
> 
>> -Original Message-
>> From: Reindl Harald [mailto:h.rei...@thelounge.net]
>> Sent: Wednesday, June 19, 2013 12:19 PM
>> To: mysql@lists.mysql.com
>> Subject: Re: How can I drop a table that is named “logs/#sql-ib203” and
>> appeared after MySQL crash?
>>
>>
>>
>> Am 19.06.2013 21:00, schrieb Franck Dernoncourt:
>>> `USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`,
>>> but when trying to `ALTER` the table that was being changed during the
>>> crash MySQL complains about the existence of the table `logs/#sql-
>> ib203`:
>>>
>>>> ERROR 1050: Table 'logs/#sql-ib203' already exists
>>>>
>>>> SQL Statement:
>>>>
>>>> ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN
>>>> `agent` , DROP COLUMN `ip` , DROP COLUMN `event_source`
>>>
>>> DROP TABLE `logs/#sql-ib203`; does not work, neither do some name
>>> variants `/#sql-ib203`, `#sql-ib203`, `sql-ib203`. (respectively Error
>> Code: 1051.
>>> Unknown table 'logs.logs/#sql-ib203',  Error Code: 1051. Unknown table
>>>
>>> I use innodb_file_per_table. There was a "mysql_datadir/logs/#sql-
>> ib203.ibd"
>>> file (or maybe .frm, sorry I forgot) that I deleted.
>>>
>>> Any idea how to get rid of this ghostly table `logs/#sql-ib203`?
>>
>> interesting question, i have identical crap since 2009 also after a crash
>> und these "blind table" has the same structure as a used existing one
>>
>> if i delete the #-files mysql whines every startup while they are never
>> used and it is ridiculous that there are references in the table space to
>> this useless crap and no mysql version from 5.1.8 to 5.5.32 is fixing this
>>
>> -rw-rw 1 mysql mysql 8,4K 2011-07-24 11:49 #sql2-704-271.frm
>> -rw-rw 1 mysql mysql  64K 2011-07-24 11:49 #sql2-704-271.ibd



signature.asc
Description: OpenPGP digital signature

Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?

[Franck Dernoncourt]

On Thu, Jun 20, 2013 at 3:28 PM, Rick James  wrote:

> #sql files are temp tables that vanish when the ALTER (or whatever)
> finishes.  If you find one sitting around, it sounds like a crash happened
> in the middle of the ALTER.


Yes the crash happened during an ALTER: the table `logs/#sql-ib203`
appeared after a MySQL crash due to disk space shortage while executing the
following query:

ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN `agent` ,
DROP COLUMN `ip` , DROP COLUMN `event_source`
, ADD INDEX `username_event_type_idx` (`username` ASC, `event_type` ASC) ;

Shouldn't the recovery take care of cleaning the temporary tables created
during the query running at the time of the crash? Or at least, if not,
shouldn't the DROP be working on this temporary table?

Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?

[Franck Dernoncourt]

On Thu, Jun 20, 2013 at 7:13 AM, Denis Jedig  wrote:
>
> If you already tried enclosing the table name in backticks (DROP TABLE
> `#sql-ib203`) and using the DROP TEMPORARY TABLE syntax without success,


Yep, no success with:
DROP TEMPORARY TABLE `logs/#sql-ib203`;
DROP TEMPORARY TABLE `/#sql-ib203`;
DROP TEMPORARY TABLE `#sql-ib203`;
DROP TEMPORARY TABLE `sql-ib203`;
USE logs; DROP TEMPORARY TABLE `logs/#sql-ib203`;
USE logs; DROP TEMPORARY TABLE `/#sql-ib203`;
USE logs; DROP TEMPORARY TABLE `#sql-ib203`;
USE logs; DROP TEMPORARY TABLE `sql-ib203`;


>  copying the table along with all its data and dropping the original table
> afterwards or running `mysqldump database tablename > dump.sql && mysql <
> dump.sql` for a backup/restore operation at least will help the problem of
> being unable to run ALTER TABLE commands for the affected main table.


I copied the data to a new table with a different name, but I wish there
were a more subtle way to solve the issue :)
I haven't dropped the original table yet, so I cannot confirm this will
solve the issue but hopefully it will.


On Thu, Jun 20, 2013 at 1:32 PM, Carsten Pedersen wrote:

> This may be a naive question, but I'm not sure I can see you've covered
> this: Have you tried "USE logs" before DROP TABLE `#sql-ib203` (without the
> "logs/" bit)?
>

Thanks, I tried the following, none of them worked:

DROP TABLE `logs/#sql-ib203`;
DROP TABLE `/#sql-ib203`;
DROP TABLE `#sql-ib203`;
DROP TABLE `sql-ib203`;
USE logs; DROP TABLE `logs/#sql-ib203`;
USE logs; DROP TABLE `/#sql-ib203`;
USE logs; DROP TABLE `#sql-ib203`;
USE logs; DROP TABLE `sql-ib203`;

Also, I can create tables with that name:
USE logs; CREATE TABLE `logs/#sql-ib203` (id int);
USE logs; CREATE TABLE `/#sql-ib203` (id int);
USE logs; CREATE TABLE `#sql-ib203` (id int);
USE logs; CREATE TABLE `sql-ib203` (id int);

It does not conflict with any existing tables.

Here is an example where I CREATE and DROP `logs/#sql-ib203`:
step 1 14:47:48 USE logs 0 row(s) affected 0.000 sec
step 2  14:47:48 CREATE TABLE `logs/#sql-ib203` (id int) 0 row(s)
affected 0.047
sec
step 3 14:47:53 CREATE TABLE `logs/#sql-ib203` (id int) Error Code: 1050.
Table 'logs/#sql-ib203' already exists 0.000 sec
step 4 14:48:01 DROP TABLE `logs/#sql-ib203` 0 row(s) affected 0.047 sec
step 5 14:48:05 DROP TABLE `logs/#sql-ib203` Error Code: 1051. Unknown
table 'logs.logs/#sql-ib203' 0.000 sec
step 6 14:48:30 ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` Error
Code: 1050. Table 'logs/#sql-ib203' already exists 0.016 sec

DROP TABLE `logs/#sql-ib203` at step 4 works, which would allow me to do
step 2 CREATE TABLE `logs/#sql-ib203` (id int) again, but step 6 ALTER
TABLE still complains about the existence of 'logs/#sql-ib203'.

One last remark: the main file `ibdata1` contains references to
`logs/#sql-ib203`, which is not surprising given the error message I have
when trying to ALTER the original table. Is there any way to clean the file
`ibdata1` so that it only contains references to tables having an actual
data file? I use InnoDB with innodb_file_per_table

Thanks for your help,


Franck Dernoncourt
fran...@mit.edu
http://francky.me

Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?

[Carsten Pedersen]

This may be a naive question, but I'm not sure I can see you've covered 
this: Have you tried "USE logs" before DROP TABLE `#sql-ib203` (without 
the "logs/" bit)?


/ Carsten

On 19-06-2013 21:00, Franck Dernoncourt wrote:

Hi all,

A table `logs/#sql-ib203` appeared after a MySQL crash due to disk space
shortage while deleting some attributes in a table in the `logs` database
and adding an index.

`USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`, but
when trying to `ALTER` the table that was being changed during the crash
MySQL complains about the existence of the table `logs/#sql-ib203`:


ERROR 1050: Table 'logs/#sql-ib203' already exists

SQL Statement:

ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN
`agent` , DROP COLUMN `ip` , DROP COLUMN `event_source`


DROP TABLE `logs/#sql-ib203`; does not work, neither do some name variants
`/#sql-ib203`, `#sql-ib203`, `sql-ib203`. (respectively Error Code: 1051.
Unknown table 'logs.logs/#sql-ib203',  Error Code: 1051. Unknown table
'logs./#sql-ib203', Error Code: 1051. Unknown table 'logs.#sql-ib203' and
Error Code: 1051. Unknown table 'logs.sql-ib203'). Interestingly none of
these error messages display 'logs/#sql-ib203', which is the table name
MySQL complains it exists when I try to do ALTER.

I use innodb_file_per_table. There was a "mysql_datadir/logs/#sql-ib203.ibd"
file (or maybe .frm, sorry I forgot) that I deleted.

Any idea how to get rid of this ghostly table `logs/#sql-ib203`?

I use MySQL 5.6.12-winx64 and InnoDB.

Thanks,


Franck Dernoncourt
fran...@mit.edu
http://francky.me



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?

[Denis Jedig]

Franck,

Am 19.06.2013 21:00, schrieb Franck Dernoncourt:


A table `logs/#sql-ib203` appeared after a MySQL crash


the #sql-ib tables are temporarily created during an 
ALTER TABLE operation for recovery purposes. Apparently these 
temporary tables might stay in certain circumstances even after 
recovery is completed.


If you already tried enclosing the table name in backticks (DROP 
TABLE `#sql-ib203`) and using the DROP TEMPORARY TABLE syntax 
without success, copying the table along with all its data and 
dropping the original table afterwards or running `mysqldump 
database tablename > dump.sql && mysql < dump.sql` for a 
backup/restore operation at least will help the problem of being 
unable to run ALTER TABLE commands for the affected main table.


Regards,

Denis

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?

[Reindl Harald]

Am 19.06.2013 21:00, schrieb Franck Dernoncourt:
> `USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`, but
> when trying to `ALTER` the table that was being changed during the crash
> MySQL complains about the existence of the table `logs/#sql-ib203`:
> 
>> ERROR 1050: Table 'logs/#sql-ib203' already exists
>>
>> SQL Statement:
>>
>> ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN
>> `agent` , DROP COLUMN `ip` , DROP COLUMN `event_source`
> 
> DROP TABLE `logs/#sql-ib203`; does not work, neither do some name variants
> `/#sql-ib203`, `#sql-ib203`, `sql-ib203`. (respectively Error Code: 1051.
> Unknown table 'logs.logs/#sql-ib203',  Error Code: 1051. Unknown table
>
> I use innodb_file_per_table. There was a "mysql_datadir/logs/#sql-ib203.ibd"
> file (or maybe .frm, sorry I forgot) that I deleted.
> 
> Any idea how to get rid of this ghostly table `logs/#sql-ib203`?

interesting question, i have identical crap since 2009 also
after a crash und these "blind table" has the same structure
as a used existing one

if i delete the #-files mysql whines every startup while
they are never used and it is ridiculous that there are
references in the table space to this useless crap and
no mysql version from 5.1.8 to 5.5.32 is fixing this

-rw-rw 1 mysql mysql 8,4K 2011-07-24 11:49 #sql2-704-271.frm
-rw-rw 1 mysql mysql  64K 2011-07-24 11:49 #sql2-704-271.ibd




signature.asc
Description: OpenPGP digital signature

How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?

[Franck Dernoncourt]

Hi all,

A table `logs/#sql-ib203` appeared after a MySQL crash due to disk space
shortage while deleting some attributes in a table in the `logs` database
and adding an index.

`USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`, but
when trying to `ALTER` the table that was being changed during the crash
MySQL complains about the existence of the table `logs/#sql-ib203`:

> ERROR 1050: Table 'logs/#sql-ib203' already exists
>
> SQL Statement:
>
> ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN
> `agent` , DROP COLUMN `ip` , DROP COLUMN `event_source`

DROP TABLE `logs/#sql-ib203`; does not work, neither do some name variants
`/#sql-ib203`, `#sql-ib203`, `sql-ib203`. (respectively Error Code: 1051.
Unknown table 'logs.logs/#sql-ib203',  Error Code: 1051. Unknown table
'logs./#sql-ib203', Error Code: 1051. Unknown table 'logs.#sql-ib203' and
Error Code: 1051. Unknown table 'logs.sql-ib203'). Interestingly none of
these error messages display 'logs/#sql-ib203', which is the table name
MySQL complains it exists when I try to do ALTER.

I use innodb_file_per_table. There was a "mysql_datadir/logs/#sql-ib203.ibd"
file (or maybe .frm, sorry I forgot) that I deleted.

Any idea how to get rid of this ghostly table `logs/#sql-ib203`?

I use MySQL 5.6.12-winx64 and InnoDB.

Thanks,


Franck Dernoncourt
fran...@mit.edu
http://francky.me

Re: Importing SQL dumps into MySQL through Command line

[Manuel Arostegui]

2013/3/31 Norah Jones 

> Hi,
>
> To source sqldump i can use the source command, but if I need to do the
> same stuff using command line without going to the sqlpromt, can I achieve
> that.
>
>

Hello,

You mean cat sqldump.sql | mysql -uwhatever -pwhatever whatever_database ?

Manuel.

RE: Create a VIEW with nested SQL

[hsv]

>>>> 2012/09/10 15:49 -0700, Rick James >>>>
SELECT ... ORDER BY .. GROUP BY.. 
is syntactically incorrect.
<<<<<<<<
Yeap, my mistake.

>>>>>>>>
( SELECT ... ORDER BY .. ) GROUP BY ..
Is what I call the "group by trick".  It is an optimal way to SELECT all the 
fields corresponding to the MAX (or MIN) of one of the fields.  But it depends 
on the optimizer not screwing it up.  MariaDB decides that this construct can 
be optimized, and messes up the 'trick'.
<<<<<<<<
But this behavior is guaranteed nor in MySQL:


11.16.3. GROUP BY and HAVING with Hidden Columns

MySQL extends the use of GROUP BY so that you can use nonaggregated columns or 
calculations in the select list that do not appear in the GROUP BY clause. You 
can use this feature to get better performance by avoiding unnecessary column 
sorting and grouping. For example, you need not group on customer.name in the 
following query: 

SELECT order.custid, customer.name, MAX(payments)
  FROM order,customer
  WHERE order.custid = customer.custid
  GROUP BY order.custid;

In standard SQL, you would have to add customer.name to the GROUP BY clause. In 
MySQL, the name is redundant. 

When using this feature, all rows in each group should have the same values for 
the columns that are ommitted from the GROUP BY part. The server is free to 
return any value from the group, so the results are indeterminate unless all 
values are the same. 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: Create a VIEW with nested SQL

[Rick James]

SELECT ... ORDER BY .. GROUP BY.. 
is syntactically incorrect.

( SELECT ... ORDER BY .. ) GROUP BY ..
Is what I call the "group by trick".  It is an optimal way to SELECT all the 
fields corresponding to the MAX (or MIN) of one of the fields.  But it depends 
on the optimizer not screwing it up.  MariaDB decides that this construct can 
be optimized, and messes up the 'trick'.



> -Original Message-
> From: Mark Haney [mailto:ma...@abemblem.com]
> Sent: Friday, September 07, 2012 6:12 AM
> To: mysql@lists.mysql.com
> Subject: Re: Create a VIEW with nested SQL
> 
> On 09/06/2012 10:23 AM, h...@tbbs.net wrote:
> 
> > SELECT lights.*, machine.mach_name
> > FROM lights JOIN machine USING (mach_id)
> > /* ORDER BY date DESC */
> > GROUP BY mach_id
> >
> > ? With USING the fields "mach_id" from "lights" and "machine" become
> one unambiguous field "mach_id".
> >
> > Does "mach_id" really occur more times in "lights" or "machine"? If
> only once in both tables, GROUP BY at most orders it. It is not clear
> that ORDER BY does anything with GROUP BY outside it, unless "mach_id"
> is unique in both tables--I have found that GROUP BY not always orders
> the output, when everything is unique.
> >
> > And yes, MySQL balks at saving a view with a query for a table. One
> has to make them separate views.
> >
> >
> 
> The problem I encountered is that I can't find a way to just pull the
> most recent records for each machine without the GROUP BY statement.
> That's all I need.
> 
> Okay, so here's what I tried.  I created a view vLights from:
> 
> 
> > SELECT MAX(lights.date),machine.mach_name FROM lights JOIN machine
> > USING (mach_id) GROUP BY mach_name;
> 
> Now, as I use the query by itself I get this:
> 
> > mysql> SELECT MAX(lights.date),machine.mach_name FROM lights JOIN
> > mysql> machine USING (mach_id) GROUP BY mach_name;
> > +-+---+
> > | MAX(lights.date)| mach_name |
> > +-+---+
> > | 2012-09-07 09:03:10 | #10   |
> > | 2012-09-07 09:03:07 | #12   |
> > +-+---+
> > 2 rows in set (3.62 sec)
> 
> This is better than the 20s+ I was getting before, but still not
> acceptable for only 2 machines when I'll have 40+ at production time.
> 
> All I need is the most recent record for EACH machine ID, THEN to pull
> the machine name from the table that has the name in it.
> 
> Somehow I'm missing something incredibly obvious here.
> 
> --
> 
> Mark Haney
> Software Developer/Consultant
> AB Emblem
> ma...@abemblem.com
> Linux marius.homelinux.org 3.5.1-1.fc17.x86_64 GNU/Linux
> 
> --
> 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: Create a VIEW with nested SQL

[hsv]

 2012/09/07 09:11 -0400, Mark Haney 
All I need is the most recent record for EACH machine ID, THEN to pull the 
machine name from the table that has the name in it.

Somehow I'm missing something incredibly obvious here. 

That is not certain.

There is a fairly standard, fairly ugly means of getting all the latest records 
by some one field:

SELECT *
FROM (SELECT MAX(date) AS date, mach_id
FROM lights
GROUP BY mach_id) AS ll
JOIN lights USING (mach_id, date)

This works if each pair (mach_id, date) is unique--and if it is, better so to 
declare it and make an index of it, or maybe make it the primary key. To this 
is the name-table joined:

SELECT *
FROM (SELECT MAX(date) AS date, mach_id
FROM lights
GROUP BY mach_id) AS ll
JOIN lights USING (mach_id, date)
JOIN machine USING (mach_id)

As for performance, maybe the suggested index helps (almost every table is owed 
a primary key), or maybe to reverse the three tables (call "ll" a virtual 
table).

If you really want to make a view out of it, under MySQL it can be only two 
views, not one.

(I extensivly use views, but my tables are not big, and the traffic on them is 
very little, about ten transactions each week, all by my hand. One of the views 
is painfully slow.) 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: Create a VIEW with nested SQL

[Rick James]

VIEWs have never been optimized well.  Avoid them.

Please provide SHOW CREATE TABLE for each table.

Is `machine` used for anything other than the machine_name?

Are you expecting one row?

Here's another way:
SELECT  lights.*, 
( SELECT  mach_name
FROM  machine
WHERE  mach_id = lights.mach_id ) AS mach_name
FROM  lights
ORDER BY  lights.date DESC 
LIMIT 1;

lights would need INDEX(date)
machine would need INDEX(mach_id) -- Presumably it is the PRIMARY KEY, which 
suffices.




> -Original Message-
> From: Mark Haney [mailto:ma...@abemblem.com]
> Sent: Thursday, September 06, 2012 5:51 AM
> To: mysql@lists.mysql.com
> Subject: Create a VIEW with nested SQL
> 
> I have a bit of a performance/best practice question for those in the
> know.  I have a nested SQL statement that selects fields from a SELECT
> that has a JOIN in it. Here's the SQL:
> 
> > SELECT vLight.* FROM
> > (SELECT lights.*, machine.mach_name from lights JOIN machine ON
> > lights.mach_id = machine.mach_id ORDER BY date DESC) as vLight GROUP
> > BY mach_id
> 
> Now, it's been a while for me to craft a complex SQL statement, so if
> there is a better way, that's great. However, that's not really the
> issue.
> 
> I'm having a performance issue with this query because I'm using it to
> pull data from the DB (read only) every 5 seconds or so to display
> status lights from machines.
> 
> My thought was to make this a VIEW to see if that made a difference in
> speed, but when I went to create it mySQL choked with an error about
> the VIEW being built from a SELECT inside the SELECT.  I googled a
> couple of answers that moved the JOIN so it wouldn't be a nested SQL,
> and I thought maybe I could build the initial SELECT (the internal one)
> as a VIEW, than query that VIEW with the initial statement.
> 
> Then, of course, I realized that maybe none of this will give me the
> boost I need, so I decided, in my vicodin soaked brain (I have a torn
> rotator cuff and tendon in my shoulder) that I should hit the list
> before I go any farther.
> 
> So, what's the best way, or the most common way to deal with this
> issue.
> I'll be happy to clarify anything in here that doesn't make sense.
> 
> Thanks in advance.
> 
> 
> --
> 
> Mark Haney
> Software Developer/Consultant
> AB Emblem
> ma...@abemblem.com
> Linux marius.homelinux.org 3.5.1-1.fc17.x86_64 GNU/Linux
> 
> --
> 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: Create a VIEW with nested SQL

[Mark Haney]

On 09/06/2012 10:23 AM, h...@tbbs.net wrote:


SELECT lights.*, machine.mach_name
FROM lights JOIN machine USING (mach_id)
/* ORDER BY date DESC */
GROUP BY mach_id

? With USING the fields "mach_id" from "lights" and "machine" become one unambiguous 
field "mach_id".

Does "mach_id" really occur more times in "lights" or "machine"? If only once in both 
tables, GROUP BY at most orders it. It is not clear that ORDER BY does anything with GROUP BY outside it, unless 
"mach_id" is unique in both tables--I have found that GROUP BY not always orders the output, when everything 
is unique.

And yes, MySQL balks at saving a view with a query for a table. One has to make 
them separate views.




The problem I encountered is that I can't find a way to just pull the 
most recent records for each machine without the GROUP BY statement. 
That's all I need.


Okay, so here's what I tried.  I created a view vLights from:



SELECT MAX(lights.date),machine.mach_name FROM lights JOIN machine USING 
(mach_id) GROUP BY mach_name;


Now, as I use the query by itself I get this:


mysql> SELECT MAX(lights.date),machine.mach_name FROM lights JOIN machine USING 
(mach_id) GROUP BY mach_name;
+-+---+
| MAX(lights.date)| mach_name |
+-+---+
| 2012-09-07 09:03:10 | #10   |
| 2012-09-07 09:03:07 | #12   |
+-+---+
2 rows in set (3.62 sec)


This is better than the 20s+ I was getting before, but still not 
acceptable for only 2 machines when I'll have 40+ at production time.


All I need is the most recent record for EACH machine ID, THEN to pull 
the machine name from the table that has the name in it.


Somehow I'm missing something incredibly obvious here.

--

Mark Haney
Software Developer/Consultant
AB Emblem
ma...@abemblem.com
Linux marius.homelinux.org 3.5.1-1.fc17.x86_64 GNU/Linux

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Create a VIEW with nested SQL

[Mark Haney]

On 09/06/2012 10:23 AM, h...@tbbs.net wrote:

How about

SELECT lights.*, machine.mach_name
FROM lights JOIN machine USING (mach_id)
/* ORDER BY date DESC */
GROUP BY mach_id

? With USING the fields "mach_id" from "lights" and "machine" become one unambiguous 
field "mach_id".

Does "mach_id" really occur more times in "lights" or "machine"? If only once in both 
tables, GROUP BY at most orders it. It is not clear that ORDER BY does anything with GROUP BY outside it, unless 
"mach_id" is unique in both tables--I have found that GROUP BY not always orders the output, when everything 
is unique.

And yes, MySQL balks at saving a view with a query for a table. One has to make 
them separate views.




Now that's a syntax I've never seen before.  Then again, I haven't done 
any serious SQL in about 5 years, so I shouldn't be surprised.  If I 
read your question correctly about mach_id, the mach_id is precisely 
what it's name implies, the identifier of a particular machine.  It's 
unique to that machine, but it occurs many times in the lights table, as 
it is the table my monitoring system inserts data. I have them ORDERed 
BY date DESC to make sure I get the most recent status of EACH machine 
at the top of the list like this:


mach1,2010-09-01 10:00:00,running
mach2,2010-09-01 09:59:51,running
mach3

etc.

That's also what the GROUP BY is for, to group all the entries by 
machine ID, order them first, then group them.  Honestly, the ORDER BY 
may not be needed other than for getting them listed in 'numerical' 
order.  That's certainly not necessarily a requirement at the moment.


(And getting me to think about the grouping and ordering part of the 
query makes me stop and rethink the logic behind the query.  So thanks 
for that.  My SQL brain is still fuzzy, and combined with Vicodin, I'm 
no House.  I can't function 100% on pain meds.


I'll take a look at this, and if I need any help, I'll holler, but this 
looks really good at the moment.


Thanks.


--

Mark Haney
Software Developer/Consultant
AB Emblem
ma...@abemblem.com
Linux marius.homelinux.org 3.5.1-1.fc17.x86_64 GNU/Linux

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Create a VIEW with nested SQL

[hsv]

 2012/09/06 08:50 -0400, Mark Haney 
>SELECT vLight.* FROM
>(SELECT lights.*, machine.mach_name from lights
>JOIN machine ON lights.mach_id = machine.mach_id
>ORDER BY date DESC) as vLight
>GROUP BY mach_id

How about

SELECT lights.*, machine.mach_name
FROM lights JOIN machine USING (mach_id)
/* ORDER BY date DESC */
GROUP BY mach_id

? With USING the fields "mach_id" from "lights" and "machine" become one 
unambiguous field "mach_id".

Does "mach_id" really occur more times in "lights" or "machine"? If only once 
in both tables, GROUP BY at most orders it. It is not clear that ORDER BY does 
anything with GROUP BY outside it, unless "mach_id" is unique in both tables--I 
have found that GROUP BY not always orders the output, when everything is 
unique.

And yes, MySQL balks at saving a view with a query for a table. One has to make 
them separate views. 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Create a VIEW with nested SQL

[Mark Haney]

I have a bit of a performance/best practice question for those in the 
know.  I have a nested SQL statement that selects fields from a SELECT 
that has a JOIN in it. Here's the SQL:



SELECT vLight.* FROM
(SELECT lights.*, machine.mach_name from lights
JOIN machine ON lights.mach_id = machine.mach_id
ORDER BY date DESC) as vLight
GROUP BY mach_id


Now, it's been a while for me to craft a complex SQL statement, so if 
there is a better way, that's great. However, that's not really the issue.


I'm having a performance issue with this query because I'm using it to 
pull data from the DB (read only) every 5 seconds or so to display 
status lights from machines.


My thought was to make this a VIEW to see if that made a difference in 
speed, but when I went to create it mySQL choked with an error about the 
VIEW being built from a SELECT inside the SELECT.  I googled a couple of 
answers that moved the JOIN so it wouldn't be a nested SQL, and I 
thought maybe I could build the initial SELECT (the internal one) as a 
VIEW, than query that VIEW with the initial statement.


Then, of course, I realized that maybe none of this will give me the 
boost I need, so I decided, in my vicodin soaked brain (I have a torn 
rotator cuff and tendon in my shoulder) that I should hit the list 
before I go any farther.


So, what's the best way, or the most common way to deal with this issue. 
I'll be happy to clarify anything in here that doesn't make sense.


Thanks in advance.


--

Mark Haney
Software Developer/Consultant
AB Emblem
ma...@abemblem.com
Linux marius.homelinux.org 3.5.1-1.fc17.x86_64 GNU/Linux

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Having trouble with SQL query

[Shawn Green]

Hello Rich,

On 8/27/2012 12:19 PM, rich gray wrote:

Hi Nitin
Thanks - I tried that and got 0 rows...
I have spent more time on describing my problem -- see below hopefully
this will make the issue more clear...
Rich
... snip ...


There are many resources out there that can tell you how to build this 
type of data structure. However, my favorite and the one I think is most 
accessible is this:


http://www.sitepoint.com/hierarchical-data-database/

As you can see, his menu also has branches (fruit) and leaves (cherry, 
banana) just as your equipment menu does. I think this will be an 
excellent starting point for you to use to build the menu tree. From 
there, it should be easy to extend this to link your leaf nodes to any 
information records you may want.


Let us know if we can give any additional insights or suggestions.
--
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: Having trouble with SQL query

[rich gray]

Hi Nitin
Thanks - I tried that and got 0 rows...
I have spent more time on describing my problem -- see below hopefully 
this will make the issue more clear...

Rich
<

I have a MySQL database with a menu table and a product table.

- The products are linked to the menus in a one-to-many relationship 
i.e. each product can be linked to more than one menu

- The menus are nested in a parent child relationship
- Some menus may contain no products

The desire is that when a user clicks on a menu entry then all products 
linked to that menu - there may be none - will get displayed as well as 
all products linked to any child menus of the menu clicked on ...


So say we have a menu like this:-

Motor cycles -> Sports bikes -> Italian -> Ducati
Motor cycles -> Sports bikes -> Italian -> Moto Guzzi
Motor cycles -> Sports bikes -> British -> Triumph
Motor cycles -> Tourers -> British -> Triumph
Motor cycles -> Tourers -> American -> Harley-Davidson
.
etc etc

Clicking on 'Sports bikes' will show all products linked to 'Sports 
bikes' itself as well as all products linked to ALL menus below 'Sports 
bikes', clicking on 'Harley-Davidson' will just show products for that 
entry only.


Below are 'describe table' for the 2 main tables in question NB there is 
a 3rd table that holds product descriptions which I won't show as I 
don't think it is relevant here:-


CREATE TABLE `menu` (
 `menuid` int(11) unsigned NOT NULL AUTO_INCREMENT,
`parent_menuid` int(11) unsigned NOT NULL,
 `name` varchar(255) NOT NULL,
  PRIMARY KEY (`menuid`)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `menu_product` (
 `menuid` int(11) unsigned NOT NULL,
 `productid` int(11) unsigned NOT NULL,
PRIMARY KEY (`menuid`,`productid`),
 KEY `prodidx` (`productid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

For the sake of this question I will simplify it and say there is only 2 
levels of nesting i.e. root level and 1 level below that... this is the 
query I came up with:-


SELECT DISTINCT
p.productid,
pd.name
FROM menu_product as p
INNER JOIN menu as m ON (m.menuid = p.menuid)
INNER JOIN product_description as pd ON (pd.productid = p.productid)
LEFT JOIN menu as m2 ON (m2.parent_menuid = m.menuid) # User selected 
menu may itself be a child menu...
WHERE (m.name = '' OR p.productid IN 
(SELECT p2.productid from menu_product AS p2 WHERE p2.menuid = m2.menuid)


Anyway when I run the above query it returns far too many entries from 
menus that are totally unrelated...


I have been staring too hard at this for too long - I am sure it will be 
a forehead slapper!


I hope I have explained this sufficiently and I TYIA for any guidance

Rich




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Having trouble with SQL query

[Nitin Mehta]

I'm more of an hit and try guy and do good only with a data set available. 
Still I think making a little change might do the trick.
 
 
SELECT DISTINCT
    p.productid,
    pd.name
FROM menu_product as p
INNER JOIN menu as m ON (m.menuid = p.menuid AND m.name = '')
INNER JOIN product_description as pd ON (pd.productid = p.productid)
LEFT JOIN menu as m2 ON (m2.parent_menuid = m.menuid) # User selected menu may 
itself be a child menu...
WHERE p.productid IN (SELECT p2.productid from menu_product AS p2 WHERE 
p2.menuid = m2.menuid)


Hope that works.
 
 
Regards,
  


 From: rich gray 
To: mysql@lists.mysql.com 
Sent: Monday, August 27, 2012 2:46 AM
Subject: Having trouble with SQL query
  
I have a MySQL database with a menu table and a product table linked to the 
menus *(each product can be linked to more than menu row)* and the menus are 
nested.

The query is that when a user clicks on a menu entry then all products linked 
to that entry *(there may be none)* will get displayed as well as all products 
linked to child menus... below are describe tables for the 2 main tables in 
question (there is a 3rd table that holds product descriptions which I won't 
show as I don't think it is relevant)

CREATE TABLE `menu` (
`menuid` int(11) unsigned NOT NULL AUTO_INCREMENT,
`parent_menuid` int(11) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
  PRIMARY KEY (`menuid`)
) ENGINE=MyISAM AUTO_INCREMENT=225 DEFAULT CHARSET=utf8

CREATE TABLE `menu_product` (
`menuid` int(11) unsigned NOT NULL,
`productid` int(11) unsigned NOT NULL,
PRIMARY KEY (`menuid`,`productid`),
KEY `prodidx` (`productid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Anyway for the sake of this question lets say there is only 2 levels of nesting 
so a parent menu can only have children so no grandkids+ this is the query I 
came up with:-

SELECT DISTINCT
            p.productid,
            pd.name
FROM menu_product as p
INNER JOIN menu as m ON (m.menuid = p.menuid)
INNER JOIN product_description as pd ON (pd.productid = p.productid)
LEFT JOIN menu as m2 ON (m2.parent_menuid = m.menuid) # User selected menu may 
itself be a child menu...
WHERE (m.name = '' OR p.productid IN (SELECT 
p2.productid from menu_product AS p2 WHERE p2.menuid = m2.menuid)

Anyway the above query returns many many entries from menus that are totally 
unrelated... I have been staring too hard at this for too long - I am sure it 
will be a forehead slapper!

I hope I have explained this sufficiently and I TYIA for any guidance

Rich




-- MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql

Having trouble with SQL query

[rich gray]

I have a MySQL database with a menu table and a product table linked to 
the menus *(each product can be linked to more than menu row)* and the 
menus are nested.


The query is that when a user clicks on a menu entry then all products 
linked to that entry *(there may be none)* will get displayed as well as 
all products linked to child menus... below are describe tables for the 
2 main tables in question (there is a 3rd table that holds product 
descriptions which I won't show as I don't think it is relevant)


CREATE TABLE `menu` (
 `menuid` int(11) unsigned NOT NULL AUTO_INCREMENT,
`parent_menuid` int(11) unsigned NOT NULL,
 `name` varchar(255) NOT NULL,
  PRIMARY KEY (`menuid`)
) ENGINE=MyISAM AUTO_INCREMENT=225 DEFAULT CHARSET=utf8

CREATE TABLE `menu_product` (
 `menuid` int(11) unsigned NOT NULL,
 `productid` int(11) unsigned NOT NULL,
PRIMARY KEY (`menuid`,`productid`),
 KEY `prodidx` (`productid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Anyway for the sake of this question lets say there is only 2 levels of 
nesting so a parent menu can only have children so no grandkids+ this is 
the query I came up with:-


SELECT DISTINCT
p.productid,
pd.name
FROM menu_product as p
INNER JOIN menu as m ON (m.menuid = p.menuid)
INNER JOIN product_description as pd ON (pd.productid = p.productid)
LEFT JOIN menu as m2 ON (m2.parent_menuid = m.menuid) # User selected 
menu may itself be a child menu...
WHERE (m.name = '' OR p.productid IN 
(SELECT p2.productid from menu_product AS p2 WHERE p2.menuid = m2.menuid)


Anyway the above query returns many many entries from menus that are 
totally unrelated... I have been staring too hard at this for too long - 
I am sure it will be a forehead slapper!


I hope I have explained this sufficiently and I TYIA for any guidance

Rich




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: Hi, how did u do de-emphasis of sql statements?

[Rick James]

Let me change your example slightly...
select * from table where name>’A’
versus
select * from table where name>’Z’

Now, let's assume you have an INDEX starting with `name` and names are 
distributed in the typical way.

The will be perhaps 1% of the names satisfying >'Z', but 95% satisfying >'A'.  
The index would be very useful for Z, but a waste for A.

Hence, it is a "feature" that MySQL does not cache execution plans.

You will also find that MySQL's query analyzer is very fast (compared to the 
competition).  Hence, there is much less need for a cache than 'they' have.

> -Original Message-
> From: Johan De Meersman [mailto:vegiv...@tuxera.be]
> Sent: Monday, August 13, 2012 5:49 AM
> To: MID.night
> Cc: 673575760; mysql
> Subject: Re: Hi, how did u do de-emphasis of sql statements?
> 
> 
> 
> - Original Message -
> > From: "MID.night" <693893...@qq.com>
> >
> > Like select * from table where name>’A’ is just
> >
> > The same as select * from table where name>’B’.
> 
> The execution plan for both statements is indeed likely (but not
> guaranteed!) to be the same. As far as I'm aware, though, MySQL does
> not bother about that, though, as there is no execution plan cache.
> 
> The query result cache does not equate the statements - it works based
> off the EXACT query text, INCLUDING spaces and capitalization.
> 
> When analyzing various logs, the Maatkit/Aspersa/Percona toolset does
> transform SQL statements into their canonical form, though; so if
> you're looking for ways to do that you can have a look at how it's done
> there.
> 
> 
> --
> Linux Bier Wanderung 2012, now also available in Belgium!
> August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql

?????? Hi, how did u do de-emphasis of sql statements?

[MID.night]

As I consult the oracle 9i  reference , I found they actualy
 have values of  'similar' and 'force' to be set for cursor_shareing parameter, 
which allows
 oracle to treat hard-coded statements to bind-variable statements in order to 
use
 the same execution plans. I wonder how they do this?...>_;
 : 2012??8??13??(??) 8:48
 ??: "MID.night"<693893...@qq.com>; 
 : "673575760"<673575...@qq.com>; "mysql"; 
 : Re: Hi, how did u do de-emphasis of sql statements?

 



- Original Message -
> From: "MID.night" <693893...@qq.com>
> 
> Like select * from table where name>??A?? is just
>  
> The same as select * from table where name>??B??.

The execution plan for both statements is indeed likely (but not guaranteed!) 
to be the same. As far as I'm aware, though, MySQL does not bother about that, 
though, as there is no execution plan cache.

The query result cache does not equate the statements - it works based off the 
EXACT query text, INCLUDING spaces and capitalization.

When analyzing various logs, the Maatkit/Aspersa/Percona toolset does transform 
SQL statements into their canonical form, though; so if you're looking for ways 
to do that you can have a look at how it's done there.


-- 
Linux Bier Wanderung 2012, now also available in Belgium!
August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be

Re: Hi, how did u do de-emphasis of sql statements?

[Johan De Meersman]

- Original Message -
> From: "MID.night" <693893...@qq.com>
> 
> Like select * from table where name>’A’ is just
>  
> The same as select * from table where name>’B’.

The execution plan for both statements is indeed likely (but not guaranteed!) 
to be the same. As far as I'm aware, though, MySQL does not bother about that, 
though, as there is no execution plan cache.

The query result cache does not equate the statements - it works based off the 
EXACT query text, INCLUDING spaces and capitalization.

When analyzing various logs, the Maatkit/Aspersa/Percona toolset does transform 
SQL statements into their canonical form, though; so if you're looking for ways 
to do that you can have a look at how it's done there.


-- 
Linux Bier Wanderung 2012, now also available in Belgium!
August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Hi, how did u do de-emphasis of sql statements?

[MID.night]

Hey man, I would like to ask a question about de-emphasis of sql statements? I
 
Mean mysql engines have to be aware of which two statements are exactly the same
 
Despite of variables?? values at runtime. Because in slow log, the execution 
counter is
 
Maintained for each sql statement. De-emphasis is obviously behind the back. 
 
Like select * from table where name>??A?? is just
 
The same as select * from table where name>??B??. 
 
 How did u do this emphasis?

Re: Mysql starts to die at 27 SQL processes

[Andrés Tello]

Don't you have any message? For the number of max connections, I suppose
you are hitting the limit of file opened, there are no messages at error
log?

Check this:
http://www.geeksww.com/tutorials/database_management_systems/mysql/configuration/mysql_open_files_limit_openfileslimit_vs_openfileslimit_on_linux.php

and maybe you need to set files to ulimit...

 Which distribution and kernel would be usable...
uname -a
to get kernel information


On Thu, May 31, 2012 at 11:26 AM, J M  wrote:

> hi all,
>
> i have an issue with our db which hangs and the only way to
> recover from it is to restart mysql if you had console access or
> restart the box itself ( only if i can't wait )
>
> show processlist result can be found here:
> http://pastebin.com/BYvZ5ZFR
>
> listed below are info about the setup:
>
> ==
>  * Master-slave config...
> ==
>  * config..
>
> [client]
> port= 3306
> socket  = /var/lib/mysql/mysql.sock
>
>
> [mysqld]
> port= 3306
> socket  = /var/lib/mysql/mysql.sock
> skip-locking
> key_buffer_size = 256M
> max_allowed_packet = 1M
> sort_buffer_size = 2M
>
> read_buffer_size = 256K
> read_rnd_buffer_size = 256K
>
> # Testing Config
> #log = /tmp/sql.log
> # Testing Config End
>
> myisam_max_sort_file_size = 2G
> myisam_sort_buffer_size = 64M
> thread_cache_size = 8
>
> # Change 20110825 10:30AM
> query_cache_size  = 256M
>
> query_cache_limit = 1M
> thread_concurrency = 8
> max_connections = 600
> wait_timeout = 10
> interactive_timeout = 300
>
> table_open_cache = 750
> table_definition_cache=750
>
> # Change 20110825 10:30AM
> #max_heap_table_size = 64M
> #tmp_table_size = 64M
> max_heap_table_size = 256M
> tmp_table_size = 256M
>
>
> #max_write_lock_count = 1
> #low_priority_updates = 1
>
> #skip-networking
>
> log-bin=mysql-bin
> server-id   = 1
> binlog-ignore-db = mysql
> expire_logs_days = 7
>
> skip-innodb
>
> [mysqldump]
> quick
> max_allowed_packet = 16M
>
> [mysql]
> no-auto-rehash
>
> [myisamchk]
> key_buffer_size = 256M
> sort_buffer_size = 256M
> read_buffer = 2M
> write_buffer = 2M
>
> [mysqlhotcopy]
> interactive-timeout
>
> ==
>
>  use: website
>
> ==
>
>  I have a decent amount of resources:
>
> total   used   free sharedbuffers cached
> Mem:  1642587668835649542312  033784682384308
> -/+ buffers/cache:1120788   15305088
> Swap:  61448522086144644
>
>
>  16 core server ( DB is dedicated )
>
> ==
>
>  DB: v5.1.x
>
>
> ==
>
>  Storage: local disk @ Raid 10
>
> Notes:
>
>  assuming that application alteration is limited.. so i want to push
> the DB as far as it can go..
>
>  since i have 16G approximately @ current config i take 3G @600
> though the highest connection i get is 230 connection.
>
> tia,
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

RE: Mysql starts to die at 27 SQL processes

[Rick James]

Find the queries that are not Locked.  One of them is being naughty.  Perhaps 
it is this one:

| 20770707 | datadb | server1.domain.com:62606 | datadb | Query   |  36 | 
removing tmp table   | SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM 
wp_posts  INNER JOIN wp_term_relationships ON (wp_posts. |

However, it is truncated, so it is hard to analyze fully.  (SHOW FULL 
PROCESSLIST).

Also need to see SHOW TABLE STATUS and SHOW CREATE TABLE for all the tables in 
the naughty query.

Are the tables MyISAM?  How much RAM do you have?  Can WP be converted to 
InnoDB?

Probably some user has a zillion documents, and WP was not adequately designed 
for that kind of scaling.

Another question -- how many WP users do you have?  If I am not mistaken, WP 
creates about a dozen tables for each user.  Since MyISAM uses 3 files per 
table, a thousand users would bring the Operating System to its knees!  To see 
how bad it is, go to the WP directory and do
   time ls -l | wc
(if you are on unix)



> -Original Message-
> From: J M [mailto:jerom...@gmail.com]
> Sent: Thursday, May 31, 2012 11:17 AM
> To: Baron Schwartz
> Cc: MySql
> Subject: Re: Mysql starts to die at 27 SQL processes
> 
> Thank Baron,
> 
>i want to see if there is something i can do from the DB end.
> 
>   and BTW, i've been using the plugin already.
> 
> 
> On Thu, May 31, 2012 at 1:01 PM, Baron Schwartz 
> wrote:
> > In this case the solution is much easier outside of MySQL than
> inside.
> > http://ocaoimh.ie/wp-super-cache/ is an excellent option.
> >
> > --
> > 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: Mysql starts to die at 27 SQL processes

[J M]

Thank Baron,

   i want to see if there is something i can do from the DB end.

  and BTW, i've been using the plugin already.


On Thu, May 31, 2012 at 1:01 PM, Baron Schwartz  wrote:
> In this case the solution is much easier outside of MySQL than inside.
> http://ocaoimh.ie/wp-super-cache/ is an excellent option.
>
> --
> 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: Mysql starts to die at 27 SQL processes

[Baron Schwartz]

In this case the solution is much easier outside of MySQL than inside.
http://ocaoimh.ie/wp-super-cache/ is an excellent option.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Mysql starts to die at 27 SQL processes

[J M]

hi all,

 i have an issue with our db which hangs and the only way to
recover from it is to restart mysql if you had console access or
restart the box itself ( only if i can't wait )

 show processlist result can be found here: http://pastebin.com/BYvZ5ZFR

 listed below are info about the setup:

==
  * Master-slave config...
==
  * config..

[client]
port= 3306
socket  = /var/lib/mysql/mysql.sock


[mysqld]
port= 3306
socket  = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 256M
max_allowed_packet = 1M
sort_buffer_size = 2M

read_buffer_size = 256K
read_rnd_buffer_size = 256K

# Testing Config
#log = /tmp/sql.log
# Testing Config End

myisam_max_sort_file_size = 2G
myisam_sort_buffer_size = 64M
thread_cache_size = 8

# Change 20110825 10:30AM
query_cache_size  = 256M

query_cache_limit = 1M
thread_concurrency = 8
max_connections = 600
wait_timeout = 10
interactive_timeout = 300

table_open_cache = 750
table_definition_cache=750

# Change 20110825 10:30AM
#max_heap_table_size = 64M
#tmp_table_size = 64M
max_heap_table_size = 256M
tmp_table_size = 256M


#max_write_lock_count = 1
#low_priority_updates = 1

#skip-networking

log-bin=mysql-bin
server-id   = 1
binlog-ignore-db = mysql
expire_logs_days = 7

skip-innodb

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

==

  use: website

==

  I have a decent amount of resources:

 total   used   free sharedbuffers cached
Mem:  1642587668835649542312  033784682384308
-/+ buffers/cache:1120788   15305088
Swap:  61448522086144644


  16 core server ( DB is dedicated )

==

  DB: v5.1.x


==

  Storage: local disk @ Raid 10

Notes:

  assuming that application alteration is limited.. so i want to push
the DB as far as it can go..

  since i have 16G approximately @ current config i take 3G @600
though the highest connection i get is 230 connection.

tia,

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format

[Baron Schwartz]

I would work from the inside out. What you're doing is grouping scenes
by DVD and throwing away the ones that have no scenes. If you start
with DVDs and do a subquery for each row, you'll process DVDs without
scenes and then filter them out. If you start with a subquery that's
grouped by DVD ID, alias it with an AS clause, and then join from that
into the other tables, you can avoid that. It requires a little
backwards-thinking but it tends to work well in a lot of cases.  It
would look something like this. Here's the query against the scenes:

select dvd_id, count(*) as cnt from scenes_list group by dvd_id having
count(*) > 0;

Now you can put that into a subquery and join to it:

select ...
from (
  
) as s_sl
inner join dvds using (dvd_id)
;

I'm taking shortcuts because you said there is more to this query than
you've shown us, so I won't spend the time to make it a complete
query.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format

[Daevid Vincent]

There are a bunch of other columns in all these tables. A quick reason is
need the dvd.title too therefore the dvd table is needed. Another reason is
that the query is generated programmatically based upon parameters passed to
a method. But yes, I do she your point and maybe I can refactor some things
in this special case.

I haven't tried your query as I'm home and not at work right ATM, but I
think you need a DISTINCT dvd_id right? Otherwise I'll get a bunch of rows
all with the same dvd_id since multiple scene_ids will match.

d

-Original Message-
From: Mark Kelly [mailto:my...@wastedtimes.net] 
Sent: Saturday, May 19, 2012 3:34 PM
To: mysql@lists.mysql.com
Subject: Re: SQL query help. Retrieve all DVDs that have at least one scene
of a certain encoding format

Hi.

On Friday 18 May 2012 18:21:07 Daevid Vincent wrote:

> Actually, I may have figured it out. Is there a better way to do this?

I don't see why you need the dvds table when the dvd_id is in the scene
table:

SELECT a.dvd_id 
FROM scenes_list a, moviefiles b 
WHERE a.scene_id = b.scene_id 
AND b.format_id = '13';

or am I misunderstanding something?

Cheers,

Mark


-- 
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: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format

[Mark Kelly]

Hi.

On Friday 18 May 2012 18:21:07 Daevid Vincent wrote:

> Actually, I may have figured it out. Is there a better way to do this?

I don't see why you need the dvds table when the dvd_id is in the scene table:

SELECT a.dvd_id 
FROM scenes_list a, moviefiles b 
WHERE a.scene_id = b.scene_id 
AND b.format_id = '13';

or am I misunderstanding something?

Cheers,

Mark


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format

[Daevid Vincent]

> -Original Message-
> Sent: Friday, May 18, 2012 5:34 PM
> 
> I have a table of DVDs, another of scenes and a last one of encoding
> formats/files...
> 
> I want to find in one query all the dvd_id that have > 0 scene_id that's
> encoded in format_id = 13.
> In other words all DVDs that are format_id = 13 despite not having a
direct
> link.
> 
> CREATE TABLE `dvds` (
>   `dvd_id` smallint(6) unsigned NOT NULL auto_increment,
>   `dvd_title` varchar(64) NOT NULL default '',
>   `description` text NOT NULL,
>   PRIMARY KEY  (`dvd_id`),
> )
> 
> CREATE TABLE `scenes_list` (
>   `scene_id` int(11) NOT NULL auto_increment,
>   `dvd_id` int(11) NOT NULL default '0',
>   `description` text NOT NULL,
>   PRIMARY KEY  (`scene_id`),
> )
> 
> CREATE TABLE `moviefiles` (
>   `scene_id` int(11) NOT NULL default '0',
>   `format_id` int(3) NOT NULL default '0',
>   `filename` varchar(255),
>   `volume` smallint(6) NOT NULL default '0',
>   PRIMARY KEY  (`scene_id`,`format_id`),
> )

Actually, I may have figured it out. Is there a better way to do this?

SELECT DISTINCT 
d.`dvd_id` AS `id`,
(SELECT 
COUNT(s_sl.scene_id) AS s_tally 
FROM
scenes_list AS s_sl 
JOIN moviefiles AS s_mf USING (scene_id) 
WHERE s_sl.dvd_id = d.`dvd_id` 
AND s_mf.format_id = 13) AS s_tally 
FROM
`dvds` AS d 
WHEREd.`date_release` <= '2012-05-18' 
HAVING s_tally > 0 
ORDER BY d.`date_release` DESC;


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format

[Daevid Vincent]

I have a table of DVDs, another of scenes and a last one of encoding
formats/files...

I want to find in one query all the dvd_id that have > 0 scene_id that's
encoded in format_id = 13.
In other words all DVDs that are format_id = 13 despite not having a direct
link.

CREATE TABLE `dvds` (
  `dvd_id` smallint(6) unsigned NOT NULL auto_increment,
  `dvd_title` varchar(64) NOT NULL default '',
  `description` text NOT NULL,
  PRIMARY KEY  (`dvd_id`),
)

CREATE TABLE `scenes_list` (
  `scene_id` int(11) NOT NULL auto_increment,
  `dvd_id` int(11) NOT NULL default '0',
  `description` text NOT NULL,
  PRIMARY KEY  (`scene_id`),
)

CREATE TABLE `moviefiles` (
  `scene_id` int(11) NOT NULL default '0',
  `format_id` int(3) NOT NULL default '0',
  `filename` varchar(255),
  `volume` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`scene_id`,`format_id`),
)



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: The most elegant/efficient way to pull this SQL query

[Hal�sz S�ndor]

;>>> 2012/04/12 11:56 -0700, Haluk Karamete >>>>
My initial goal was to write a very convenient php function that display a 
table view based on arguments that are super simple to write - without 
requiring the developer to type-in ( or know ) the ins and outs of joins, 
natural joins etc.

Something like this 
function showtable($dbh,$table,$fields){
//get the $fields argument & parse it out to come up 
//with one of the SQL statements you guys are discussing.
SQL = BuildSQL($table,$fields)
//and then use this SQL to output the table
}

The magic would be happening in the $fields argument. 



What's the most straightforward way to achieve the following view with one SQL 
statement?


peopleID__stateID_
1_(joe)___1__(california)_
2_(bob)___4__(texas)__
3_(charlie)___5__(florida)



if the final table ( PeopleAndStates ) view I want were to be as follows;

peopleID__stateID_
1_1___
2_4___
3_5___

Then I would have called the function like this;

showtable($dbh,$myTable,"peopleID,stateID") 

But if I want to get, the following view instead;

peopleID__stateID_
1_(joe)___1___
2_(bob)___4___
3_(charlie)___5___

I would like to be able to call my function as follows; 

showtable($dbh,$PeopleAndStates,"peopleID(PeopleTable.PeopleID >> 
FName),stateID")  



To mean the following; 

When you are outputting the peopleID, provide the corresponding "Fname" field 
from the "PeopleTable" where peopleID there is equal to the peopleID you are 
outputting. 

What I was seeking from you guys was to find out to most simplistic SQL 
statement so that when I parse the area with (PeopleTable.PeopleID >> FName), I 
can extract the pieces and place it in the final SQL.

I'm not sure if you all get the idea of how such a function make debugging 
super easy. 

Once you write the parser, you can deploy it over many different cases such as 

ShowRecord($dbh,$table,$fields,"where peopleID<5","limit 100") and so on.

So, the simpler the SQL, the easier the transition from the starting slate 
which is really no different than 

SELECT peopleID(PeopleTable.PeopleID >> FName),stateID from PeopleAndStates
<<<<<<<<
(note that in MySQL '>>' is only right-shift.)

I fear that for this function in the end you will need 
information_schema.COLUMNS.

Peter Brawley already gave you a good answer for one of your examples (but I 
thus would write it):

select concat(peopleID, ' (',fname,')') AS peopleID, concat(stateID, ' 
(',state,')') AS stateID
from people
join PeopleAndStates USING(peopleID)
join state USING(stateID)

The middle example:

select peopleID, stateID from PeopleAndStates

The last:

select concat(peopleID, ' (',fname,')') AS peopleID, stateID
from people
join PeopleAndStates USING(peopleID)
join state USING(stateID)


I have assumed that you mean to join only on same-named fields with equality; 
if not, JOIN ... ON ... is needed. In any case, you have to keep track of it, 
whether in a result field the table name, too, is needed. If you use USING, for 
that field leave the table name out.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: The most elegant/efficient way to pull this SQL query

[Haluk Karamete]

My initial goal was to write a very convenient php function that display a
table view based on arguments that are super simple to write - without
requiring the developer to type-in ( or know ) the ins and outs of joins,
natural joins etc.

Something like this
function showtable($dbh,$table,$fields){
//get the $fields argument & parse it out to come up
//with one of the SQL statements you guys are discussing.
 SQL = BuildSQL($table,$fields)
//and then use this SQL to output the table
}

The magic would be happening in the $fields argument.

The example in my original question was like this.
I'm repeating it for convenience purposes.


I've got this relational mySQL table that ties peopleIDs from the people
table to the states IDs

peopleID___stateID
1__1
2__4
3__5


people table is like this;

___peopleID_FName_
___1joe
___2bob___
___3charlie_


and finally the state table goes like this;

___stateID_State___
___1___california
___2___new york
___3___washington__
___4___texas___
___5___florida__


What's the most straightforward way to achieve the following view with one
SQL statement?


peopleID__stateID_
1_(joe)___1__(california)_
2_(bob)___4__(texas)__
3_(charlie)___5__(florida)



if the final table ( PeopleAndStates ) view I want were to be as follows;

peopleID__stateID_
1_1___
2_4___
3_5___

Then I would have called the function like this;

showtable($dbh,$myTable,"peopleID,stateID")

But if I want to get, the following view instead;

peopleID__stateID_
1_(joe)___1___
2_(bob)___4___
3_(charlie)___5___

I would like to be able to call my function as follows;

showtable($dbh,$PeopleAndStates,"peopleID(PeopleTable.PeopleID >>
FName),stateID")



To mean the following;

When you are outputting the peopleID, provide the corresponding "Fname"
field from the "PeopleTable" where peopleID there is equal to the peopleID
you are outputting.

What I was seeking from you guys was to find out to most simplistic SQL
statement so that when I parse the area with (PeopleTable.PeopleID >>
FName), I can extract the pieces and place it in the final SQL.

I'm not sure if you all get the idea of how such a function make debugging
super easy.

Once you write the parser, you can deploy it over many different cases such
as

ShowRecord($dbh,$table,$fields,"where peopleID<5","limit 100") and so on.

So, the simpler the SQL, the easier the transition from the starting slate
which is really no different than

SELECT peopleID(PeopleTable.PeopleID >> FName),stateID from PeopleAndStates





2012/4/12 Halász Sándor 

> >>>> 2012/04/11 17:51 -0500, Peter Brawley >>>>
> select b.peopleID, concat('(',p.fname,,')'), b.stateID,
> concat('(',s.state,')')
> from bridge b
> join people p on b.peopleID=p.peopleID
> join state s on b.stateID=s.stateID;
> <<<<<<<<
> Since the names are the same in the tables, it works to use "USING", too,
> and you are relieved of the burden of an alias:
>
> from bridge
> join people USING(peopleID)
> join state USING(stateID)
>
> If the fields "peopleId" and "stateID" are the only field names in common,
> "NATURAL JOIN" also works.
>
> from bridge
> NATURAL join people
> NATURAL join state
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Re: The most elegant/efficient way to pull this SQL query

[Hal�sz S�ndor]

 2012/04/11 17:51 -0500, Peter Brawley 
select b.peopleID, concat('(',p.fname,,')'), b.stateID, concat('(',s.state,')')
from bridge b
join people p on b.peopleID=p.peopleID
join state s on b.stateID=s.stateID;

Since the names are the same in the tables, it works to use "USING", too, and 
you are relieved of the burden of an alias:

from bridge
join people USING(peopleID)
join state USING(stateID)

If the fields "peopleId" and "stateID" are the only field names in common, 
"NATURAL JOIN" also works.

from bridge
NATURAL join people
NATURAL join state


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: The most elegant/efficient way to pull this SQL query

[Hal�sz S�ndor]

;>>> 2012/04/11 11:30 -0700, Haluk Karamete >>>>
I've got this relational mySQL table that ties peopleIDs from the people
table to the states IDs

peopleID___stateID
1__1
2__4
3__5


people table is like this;

___peopleID_FName_
___1joe
___2bob___
___3charlie_


and finally the state table goes like this;

___stateID_State___
___1___california
___2___new york
___3___washington__
___4___texas___
___5___florida__


What's the most straightforward way to achieve the following view with one
SQL statement?


peopleID__stateID_
1_(joe)___1__(california)_
2_(bob)___4__(texas)__
3_(charlie)___5__(florida)
<<<<<<<<
Look at Stephen Tu s original post under the subject "forcing mysql to use 
batched key access (BKA) optimization for joins". That his query solves a 
problem very much like yours--but use explicit JOINing, not implicit. He also 
uses an unhappy style of making every field name in the database unique. 
Joining is easier if the fields to be joined on have same names.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: The most elegant/efficient way to pull this SQL query

[Peter Brawley]

On 4/11/2012 1:30 PM, Haluk Karamete wrote:

I've got this relational mySQL table that ties peopleIDs from the people
table to the states IDs

peopleID___stateID
1__1
2__4
3__5


people table is like this;

___peopleID_FName_
___1joe
___2bob___
___3charlie_


and finally the state table goes like this;

___stateID_State___
___1___california
___2___new york
___3___washington__
___4___texas___
___5___florida__


What's the most straightforward way to achieve the following view with one
SQL statement?


peopleID__stateID_
1_(joe)___1__(california)_
2_(bob)___4__(texas)__
3_(charlie)___5__(florida)


select b.peopleID, concat('(',p.fname,,')'), b.stateID, 
concat('(',s.state,')')

from bridge b
join people p on b.peopleID=p.peopleID
join state s on b.stateID=s.stateID;

PB

-





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Convert SQL Server Compact data to MySQL

[Hal�sz S�ndor]

>>>> 2012/04/05 02:15 -0700, sam >>>>
I have data file written in SQL Server Compact.I need to Convert it to  MySQL.

Does anyone know how to do that?
<<<<<<<<
If it stores in CSV files, you can use that for the data, although to make 
MySQL read it, you hav to fiddle with it. The SQL, for tables & procedures, you 
have to do apart.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Convert SQL Server Compact data to MySQL

[sam]

Unfortunately they don't have MySQL Migration Toolkit  for Ubuntu


--- On Thu, 4/5/12, Pothanaboyina Trimurthy  wrote:

From: Pothanaboyina Trimurthy 
Subject: Re: Convert SQL Server Compact data to MySQL
To: "sam" 
Date: Thursday, April 5, 2012, 4:56 AM

hello sam

you can convert that file into mysql using mysql migration toolkit.
you can download that one from mysql web site, dev.mysql.com
please try that one.

On 4/5/12, sam  wrote:
> Hello,
>
> I have data file written in SQL Server Compact.I need to Convert it to
> MySQL.
>
> Does anyone know how to do that?
>
> Thank you,
>
> Sam
>
>


-- 
3murthy

Convert SQL Server Compact data to MySQL

[sam]

Hello,

I have data file written in SQL Server Compact.I need to Convert it to  MySQL.

Does anyone know how to do that?

Thank you,

Sam

ANN: SQL Maestro for MySQL 12.3 released

[SQL Maestro Team]

Hi!

SQL Maestro Group announces the release of SQL Maestro for MySQL 12.3,
a powerful Windows GUI solution for MySQL administration and database
development.

The new version is immediately available for download at
http://www.sqlmaestro.com/products/mysql/maestro/

Please note that before April 15 you can purchase SQL Maestro for
MySQL and all other our products and bundles with a 20% discount.
Happy Easter!
http://www.sqlmaestro.com/purchase/

Top 10 new features
===

 1. Backup Database tool.
 2. SQL Editors: Unicode support, text change markers, parameter
hints, and improved code folding appearance.
 3. Enhanced performance of the SQL Script Editor tool.
 4. Improved Script Runner.
 5. Inline images in the data grid.
 6. Displaying BLOB data as PDF.
 7. Table Editor: quick index creation.
 8. Creation of views from Visual Query Builder.
 9. Updated Data Input form.
10. A Spanish translation.  

In addition to this, several bugs have been fixed and some other minor
improvements and corrections have been made. Full press-release (with
explaining screenshots) is available at:
http://www.sqlmaestro.com/news/company/sqlmaestro_for_mysql_12_3_released/

Background information:
---
SQL Maestro Group offers complete database admin, development and
management tools for MySQL, SQL Server, PostgreSQL, Oracle, DB2,
SQLite, SQL Anywhere, Firebird and MaxDB  providing the highest
performance, scalability and reliability to meet the requirements of
today's database applications.

Sincerely yours,
SQL Maestro Group
http://www.sqlmaestro.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: OT: SQL Question

[David Turner]

Jeff,

I would use a join table, teacher_flights.

create table teacher_flights(
teacher_id int(11) not null,
flight_id int(11) not null,
primary key(teacher_id, flight_id));

Dave




>
> From: Mark Phillips 
>To: Mysql List  
>Sent: Friday, March 23, 2012 7:28 PM
>Subject: OT: SQL Question
> 
>My question is not specific to MySQL, even though I am using a MySQL db for
>this project. I have a servlet/jsp/MySQL web site in production, and there
>are about 2,000 records in the flights table. One of the foreign keys is
>teacher_id. Up to this point, there is a one to many relationship between
>teacher_id and the data in the flights table. I need to change the data
>model to allow for a many to many relationship between teacher_id and the
>data in the flight table. What is the best way to do this?
>
>Thanks,
>
>Mark
>
>
>

Re: OT: SQL Question

[Michael Dykman]

A many-to-many is generally best accomplished with a third linking
table which contains the ids of the 2 records being linked ie.

create table tflink (
flightid int;
teacherid int;
);

On Fri, Mar 23, 2012 at 10:28 PM, Mark Phillips
 wrote:
> My question is not specific to MySQL, even though I am using a MySQL db for
> this project. I have a servlet/jsp/MySQL web site in production, and there
> are about 2,000 records in the flights table. One of the foreign keys is
> teacher_id. Up to this point, there is a one to many relationship between
> teacher_id and the data in the flights table. I need to change the data
> model to allow for a many to many relationship between teacher_id and the
> data in the flight table. What is the best way to do this?
>
> Thanks,
>
> Mark



-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql