By Colleen, on August 4th, 2009%
In a previous post I discussed the stumbling blocks and security concerns that the mysql documentation doesn’t tell you about having a mysql user create csv files on the filesystem.
I ran into yet another issue. In order to do what I want with these csv’s, they have to have column headers. I thought surely by now MySQL must have come up with some syntax in the query to do this, but Paul DuBois assured us all in a 2006 forum post, that there is no such thing. I thought maybe Zend Framework might have such a thing in it’s DB class, but I didn’t locate one. Correct me if I’m wrong–Zend Framework is huge, and I might have missed it.
So, what to do? We have several options:
Continue reading MySQL CSV’s with column headings — Part 1
By Colleen, on July 23rd, 2009%
Don’t you hate it when you know you solved a problem three years ago but you can’t remember what the solution was? I ran into that recently. I was trying to make mysql generate a csv directly from query results. I remembered that you can do it, and I had an example of the correct syntax to use in my old code, and I had a query that did it just fine when running in the mysql CLI as root. I had to piece together the answer from several different sources; and none of them told the whole story. So here is the full story, along with all the stumbling blocks that I know of.
Continue reading How to make MySQL spit out CSV’s
By Colleen, on February 20th, 2009%
I was getting the nitpicky error 150 when trying to create an InnoDB table with foreign keys. I had very carefully checked several times that all the key types and names matched exactly including UNSIGNED (which is what gets most people), and that I had an index on every foreign key as required by my version of mySQL. That covers the majority of Error 150 situations but not mine. I was failing the “First in Composite KEY” constraint, which is easy to overlook.
Continue reading MySQL InnoDB Create Table composite key problem and solution
By Colleen, on January 12th, 2009%
A while back I was lamenting that table comments in MySQL get overlooked, and did a post on it, both how to create them, how to update them, and how to view them. The fact that my table comment post remains quite popular indicates that mySQL hasn’t SEO’d their pages on it thoroughly enough, and people are obviously looking for information on it. So… I thought I should follow with a column comment post. As a matter of fact in mySQL you can also comment at the column level, and probably should whenever the function of a field is not obvious.
Continue reading Column Comments in MySQL
Column Comments in MySQL
Continue reading Column Comments in MySQL