Problem: you wish to write table data to file, but you wish to do so on client side.
SELECT … INTO OUTFILE writes the file on server. What are your options on client?
1. mysql client
If you have direct access from your client machine to your DB server machine, and can connect via mysql client, you get a very customizable file write:
bash$ mysql -h db_host –execute “SELECT * FROM my_table” > /tmp/output.txt
The above writes fancy tables, so you probably want to:
bash$ mysql -h db_host –execute “SELECT * FROM my_table” –bat > /tmp/output.txt
Also try:
bash$ mysql -h db_host –execute “SELECT * FROM my_table” –silent –raw > /tmp/output.txt
To turn off headers.
More options include the –xml or –html formats.
I’m doing a lot of stored routine programming lately, working on common_schema. I’m in particular touching at the extremes of abilities. Some things just can’t be done from within a stored routine. Here’s a list of can’t be done:
- Cursor for SHOW statements: can’t be done – this is explicitly blocked from operating (it once used to work).
- Get detailed error information on exceptions: apparently 5.6 has support for this. 5.1 and 5.5 do not.
- Change binlog_format: this is obvious, if you think about it. binlog_format dictates how the routine itself is replicated in the first place.
- Set sql_log_bin. Again, this makes sense.
- Work out different results depending on current machine. For example, you can’t have a routine that returns with ‘master’ on the master and with ‘slave’ on the slave. That is, not under any condition. Consider: if Row Based Replication is used, you don’t actually have a routine executing on the slave. I’m happy to be proven wrong on this.
- Know what database was in use by calling code. The routine executes within the context of the database where it is defined. But you can’t tell what database was in use just a couple milliseconds before.
- Likewise, know what sql_mode was in use by calling code. Stored routines have their own sql_mode - the one they were created with. No way to check up on the calling stack.
 USE is a client command.
- Reconnect after failure (kind of obvious, isn’t it?)
- Connect to other servers (not so obvious to SQL Server DBAs). You can’t issue queries on other servers. Bummer.
- Shutdown the server
- Fork (you’re in a connection, you can’t issue a new connection from your own connection)
There is no reasonable way to catch and diagnose errors on server side. It is nearly impossible to know exactly what went wrong.
To illustrate, consider the following query:
INSERT INTO my_table (my_column) VALUES (300);
What could go wrong with this query?
- We might hit a UNIQUE KEY violation
- Or a FOREIGN KEY violation
- my_column could be TINYINT UNSIGNED, and with strict sql_mode this makes for out-of-range
- Or, similarly, it could be an ENUM (2,3,5,8)
Is that it? Not remotely:
- This could be a read-only MyISAM table
- We may have issued a LOCK TABLES my_table READ – this violates our lock
- Or this could be an InnoDB table, and this INSERT would make for a deadlock
- Or we have read_only=1 configuration
- Or the user is not allowed access to this table
- Or the table does not exist
- Or the column does not exist
Or… I’m pretty sure there could be many other issues.
Now, if I write a Java program, perhaps using Hibernate, I get the error nicely wrapped up in a SQLException object, with easy access to error code and error message.
But can I have the same on server side? No.
Take a look at the following code:
What is the difference between the following three syntaxes?
SELECT * FROM film JOIN film_actor ON (film.film_id = film_actor.film_id)
SELECT * FROM film JOIN film_actor USING (film_id)
SELECT * FROM film, film_actor WHERE film.film_id = film_actor.film_id
The difference is mostly syntactic sugar, but with a couple interesting notes.
To put names, the first two are called “ANSI-style” while the third is called “Theta-style”.
Theta style
On the FROM clause, tables are listed as if with Cartesian products, and the WHERE clause specifies how the join should take place.
This is considered to be the “old” style. It is somewhat confusing to read. Consider the following query:
MySQL’s Row Based Replication (RBR) succeeds (though not replaces) Statement Based Replication (SBR), as of version 5.1.
Anyone who is familiar with replication data drift – the unexplained growing data difference between master & slave – might wish to look into row based replication. On multiple servers I’m handling the change to RBR has eliminated (to the best of my findings) replication data drift.
This is easily explained, as RBR writes particular row IDs into the binary log. You no longer need to hope the statement
DELETE FROM my_table ORDER BY my_column LIMIT 100
acts deterministically on all servers (is my_column UNIQUE?). With row based replication the particular rows deleted on the master are then deleted on the slave.
Here are three notes on RBR:
Have just read INSERT, Don’t DELETE by Aaron Brown, and have some lengthy response, which is why I write this post instead of commenting on said post.
I wish to offer my counter thought and suggest that DELETEs are probably the better choice.
Aaron suggests that, when one wishes to purge rows from some table, a trick can be used: instead of DELETEing unwanted rows, one can INSERT “good” rows into a new table, then switch over with RENAME (but please read referenced post for complete details).
I respectfully disagree on several points discussed.
Lockdown
The fact one needs to block writes during the time of creation of new table is problematic: you need to essentially turn off parts of your application. The posts suggests one could use a slave - but this solution is far from being trivial as well. To switch over, you yet again need to turn off access to DB, even if for a short while.
We’re used to Cold, Warm and Hot backups. This scale of three temperatures does not quite reflect the impact of backups on your MySQL database.
In this post I offer a new backup temperature scale, and (somewhat seriously) compare it with showers. Call it the backup shower scale.
A database backup is like a shower: the colder it is, the less time you want to spend doing it.
Cold
A cold backup requires taking your database down (i.e. stop the service).
Example: file system copy
This can work well for replicating slaves, which may not be required for normal operation.  Just make sure its master has all the necessary binary logs.
A friend was staying at my place and was lecturing me on the benefits of cold showers; how it was good for your health. A couple hours later, preparing for bed, I hear him exclaiming from the bathroom: “Whaaaaa! There’s no hot water!”
Got “too many connections” this morning. New attempts continuously abort. Every once in a while some slipped through, but overall behavior was unacceptable.
max_connections is set to 500, well above normal requirements.
Immediate move: raise max_connections to 600, some urgent connections must take place. But, this is no solution: if 500 got hogged, so will the extra 100 I’ve just made available.
So, who’s to blame? SHOW PROCESSLIST is so unfriendly at that. Wait. Didn’t I create that view in common_schema, called processlist_per_userhost? I wonder what it says…
SELECT * FROM common_schema.processlist_per_userhost;
+————-+——————+—————–+——————+———————+
| user | host | count_processes | active_processes | average_active_time |
+————-+——————+—————–+——————+———————+
| maatkit | sqlhost02.myweb | 1 | 0 | NULL |
| rango | webhost04.myweb | 2 | 0 | NULL |
| rango | webhost07.myweb | 8 | 0 | NULL |
| rango | sqlhost02.myweb | 38 | 0 | NULL |
| rango | management.myweb | 35 | 0 | NULL |
| rango | webhost03.myweb | 10 | 0 | NULL |
| rango | local01.myweb | 8 | 0 | NULL |
| rango | analytic02.myweb | 11 | 0 | NULL |
| mytop | localhost | 2 | 0 | NULL |
| buttercup | sqlhost02.myweb | 451 | 5 | 0.0000 |
| replc_user | sqlhost00.myweb | 1 | 1 |<PHONE_NUMBER> |
| replc_user | sqlhost02.myweb | 1 | 1 | 38028.0000 |
| root | localhost | 2 | 0 | NULL |
| system user | | 2 | 2 |<PHONE_NUMBER> |
+————-+——————+—————–+——————+———————+
Ah! It’s buttercup connecting from sqlhost02.myweb who is making a fuss. I knew that view was created for a reason.
I very much enjoyed reading Overloading Procedures by Michael McLaughlin: good stuff!
I’m dealing with similar issues in common_schema/QueryScript, where I implement a whole new scripting language within MySQL, interpreted by stored routines. I am now finalizing the next version of common_schema/QueryScript, with a major addition to the scripting language to put yet even more power at the hands of the programmer/DBA using simple, clean syntax.
Still hush hush, the development of that feature touched at the very same issues described in Michael’s post. Present in current release, these issues are intensified by the use and complexity of the new development. Here are a few insights of mine:
Internal array implementation
Like Michael, I started by implementing arrays through tables. That is, create a (temporary, in my case) table, wrap it up with a lot of stored routine code, and simulate an array. This array is not yet provided to the user, but is used internally for QueryScript’s own code.
Well, disappointment here: during load tests on intense structures, such as a foreach loop, where each iteration of the loop requires the creation of an array, I found that the current solution does not hold well on busy servers.
Seemingly, there’s nothing wrong with the creation of a new table every once in a while – and in particular a temporary table. However, I quickly found out that a busy server thrashes the table cache with such intense rate of creation/dropping of tables. The competition over the table cache mutex becomes intolerable and hogs not only the script’s execution but the entire server’s.
There’s also the issue of the type of array values – no going around using textual columns, of course, but – how long? A VARCHAR(32767) should be enough for any reasonable implementation, but – how much memory would that consume? Both MEMORY and standard temporary tables (Percona Server has that partially resolved) use a fixed row format, which means a 32K text is actually allocated in memory even when your value is ‘x’.
I know about it, I knew about it all along, but… it’s so easy to fall for it; there’s just so much absurdity!
A CHAR type has a known number of characters. For example, the column:
CountryCode CHAR(3) CHARSET ascii NOT NULL
- is known to have exactly three characters. These could be ‘USA’, ‘FRA’, etc.
What happens with spaces? A SQL CHAR type ignores any trailing spaces; thus, the code ‘GB ‘ (the characters ‘G’, ‘B’, and the space ’ ‘) is interpreted as ‘GB’. Trailing spaces are not regarded as part of the text. Want to see some absurdity?
CREATE TABLE c_test
(
c
char(1) DEFAULT NULL
);
INSERT INTO c_test VALUES (‘a’);
INSERT INTO c_test VALUES (‘b’);
INSERT INTO c_test VALUES (’ ‘);
SELECT c, LENGTH©, c = ‘a’, c = ‘b’, c = ‘ ‘, LENGTH(’ ‘) FROM c_test;
+——+———–+———+———+———+————-+
| c | LENGTH© | c = ‘a’ | c = ‘b’ | c = ‘ ’ | LENGTH(’ ‘) |
+——+———–+———+———+———+————-+
| a | 1 | 1 | 0 | 0 | 1 |
| b | 1 | 0 | 1 | 0 | 1 |
| | 0 | 0 | 0 | 1 | 1 |
+——+———–+———+———+———+————-+


Friendly apps
No time to cook? We got you covered. With CrashMeal we offer a simple way to free your time.
Order meals through our user friendly app in four simple clicks. Just log in, choose your
restaurant, your meal and confirm your order and let us do the rest! Ordering meals made
easy.
Download the app:


Trevaughn Bynum is the consummate artist. She is multi-disciplined with a range that spans from fine art to design to writing and directing. Trevaughn studied Scenic Art and Stage Properties at The University of North Carolina School of the Arts.
After doing theater for several years she undertook a self-imposed challenge to write a full length play. It started with a poem on a post it note and eventually became a full-length spoken work play she named Waitin'.
She is currently the Program Coordinator for RAP-SI (Retention, Achievement, Professionalism, Success Institute) a CUNY Black Male Initiative project at The City College of New York. Her first book, Don't Let This Happen to You! is a half memoir half self-help work book for college students to aid in creating a completing college mindset.
In her copious amounts of free time, she is building a creative production company Level Six Productions and revamping her pet site ClioGivesLove.com
Carpentry, Scene Painting, Faux Finish Painting, Portraiture, Drawing,
Wood Working, Wood, Plaster or Foam Carving, Scale Model Creation, Display Building,
Furniture Restoration, Upholstering, Airbrush, Sculptural Installation, Basic Electrical
Wiring, Color Theory, Drafting, Sewing, Scene Design, Costume Design, Stop Motion Animation,
Puppet Making, Desktop Publishing, Photo Retouching, Photo Repair/Compositing,
Graphic Design, Website Design, Video Editing, Audio Editing
Crosslines: A Black Woman's Perspective on American Politics, Poetlitical Inc. 2011
M.Body, The Bennola Company, 2011
Waitin, 2009
The Get There, 2009
Crosslines: A Black Woman's Perspective on American Politics, Poetlitical Inc. 2011
M.Body, The Bennola Company, 2011
Paris Commune, The Civilians, NY, 2003
Gorilla Man, PS 122, 2005
Gone Missing, The Civilians, 2004, 2005
The Roaring Girlie, The Foundry Theatre, 2004
Gone Missing, The Civilians, NY, 2003, 2004
Square One, Wide-Awake Theatre Co., 2003-2004
Question Love, NY International Film Festival, 2004
CCNY Holiday Show, 2011, 2012, 2015
Creative Clash, 2004
www.art4love.com, 2003
Borough Art Supply, New York, NY Harlemade, New York, NY 2002-2003
Seattle Central Grind, Seattle, WA 2001
Bathhouse Theatre, Seattle, WA 1996
Uwjamaa Market, Greensboro, NC 1995
Art and the Garden for Triad Health Project, Greensboro, NC 1994
Triad Women's Weekend, Greensboro, NC 1994
Black Republicans for Community Empowerment, Greensboro, NC 1994


On the start screen of the App, real-time UV index, current geolocation coordinates and number of satellites are displayed. The app also collects GPS signal, Cell signal and Wifi signal in the backend with built-in sensors. All data are collectd once per minute.
The menu page allows the user to view UV diagram that shows daily, weekly and monthly UV exposure. User can also go to questionnaire to test their understanding of UV exposure.
When service of the app is stopped by the user, all data collected will be written to csv file and stored in the internal storage of the phone.


HOME
CODE
ABOUT
About This Project
This site is for use with CS 1114 at the Tandon School of Engineering.