Selecting Last Records from Table

I needed to select the last records in a table.  I could do a MySQL query with no limit on records, but that could be inefficient.  Instead you could and this to the end of your MySQL query:

order by id desc limit x

For example:

SELECT * FROM articles order by id desc limit 5

This will return the last five records in the table.

Importing CSV into MySQL table on Windows

After completing development of one of my apps, I had to load one of my MySQL tables with data.  After putting the data in CSV format, I wanted to upload it to my MySQL table.  I searched the web and found the syntax to use (I use MySQL workbench and from there I went into the database and created and executed a new query).  However, it took awhile for me to figure out how to make the file name work.

Here’s the syntax I used in MySQL Workbench for the query to execute (replace the text in colors with your data):

load data local infile ‘C://yourdirectory/yourdirectory2/filename.csv‘ into table your_table_name fields terminated by ‘,’
enclosed by ‘”‘
lines terminated by ‘\n’
(field1, field2, field3, field4, field5)

(for some reason the characters don’t copy correctly from this post, so you can copy similar syntax from here)

Error: mysql2 gem compiled for wrong mysql client library

I was receiving this error after switching to the MySQL2. It turns out the reason is because I needed to update my MySQL connector.

To do that I
(1) went to: http://www.mysql.com/downloads/connector/
(2) I selected Connector/C (libmysql)
(3) I downloaded the Windows (x86, 32-bit), ZIP Archive no-install
(4) I placed it in my ruby/bin directory, which for me was in: C:\RailsInstaller\Ruby1.9.3\bin

Importing a MySQL Database in Windows

I switched computers and had to migrate my dev database.  I was able to export my old database, but had trouble importing it using phpmyadmin (actually, I couldn’t get phpmyadmin to work) and MySQL Workbench.  So I had to go into the Windows command prompt to do it.  Here’s what I did after generating a dump (ie your_database_name.sql) which contained the SQL and data to rebuild the DB.  To make this work, the database needs to be created (ie rake db:create), but the tables must not exist yet (or you’ll get a SQL error telling you that the table already exists).

(1) saved the your_database_name.sql to: users/your_user_name on the PC

(2) started the command prompt (start menu >search for programs -> cmd)

(3) typed mysql -u USERNAME -p DATABASE < FILENAME.sql

Thanks to ghacks.net for teaching me this.  They also have the sql for exporting a *.sql dump if you need that.

Rails Queries not Finding Nil Values

I had some queries that were not working and I couldn’t figure out how to make them work.  Basically, I was looking for fields with nil values, so I tried:

Model.where(‘variable = ?’, nil)….and didn’t get any results

So just for the heck of it, I tried:

Model.where(‘variable != ?’, nil)…but still no results.  How is that possible?  It either has to be a value or it isn’t right?

Well, apparently you can’t use normal comparison operands with nil/null.  However, instead of =, you can compare for nil using <=>

So I ended up doing:

Model.where(‘variable <=> ?’, nil)

and it worked, finding the nil values.

Setting Field to Null in MySQL Workbench

I had some bad data in my DB where fields had a space or some other value I could not see, so I needed to set the field to null (my program looks for null).  To do this, I changed the value of the field, then clicked “Apply” and on the SQL preview screen, I changed the value to NULL (ie city = NULL where…).

MySQL Workbench

My latest tool is the MySQL Workbench – a free tool that allows you to manage your MySQL databases.  Previously I was using PHPMyAdmin, but MySQL Workbench seems to run a lot faster.  I’m using it for managing both my local databases, as well as my remote databases.

When I first downloaded the app, I was double clicking on the MySQL application which opens a shell and the closes it right away.  I didn’t see the MySQL Workbench application which was in the same zip folder – this is the one to launch the app.

I’ve found that PHPMyAdmin was initially easier to understand because all of the links are on the screen, however that meant more screens.  In MySQL workbench, you can get to the same screens from the main menu and right clicking on the table (or the submenus of the table in the menu).

If I need to manually add rows to a table:
PHPMyAdmin – Click on Insert for the table and fields for up to two rows appear, if I want to add more, I click the go button, the entries save, new page loads

MySQL Workbench – right click on the table, select “Edit Table Data”, skip to the last row and enter your data, then click enter and the next row appears – no pages reloads

Edit Record
PHPMyAdmin – go to table, next screen click browse, find your entry, click icon to edit, change field in page that loads, then save (and another page reload)

MySQL Workbench – right click on the table, select “Edit Table Data”, find your record and double click your data field, change field value, then click the Apply button when you’re done.

Those are just a couple of examples.  I think that a lot of the reason for speed differences is that MySQL Workbench is a standalone app, while PHPMyAdmin uses a browser and doesn’t incorporate as much Java for asynchronous changes (therefore the page reloads).  The other downer is that if you don’t have PHP installed on your PC, you need to do so t

PHPMyAdmin is also a great tool, but for now I’m going to try switching to MySQL Workbench.

Migrating from Heroku Shared DB to MySQL DB

I wanted to migrate from Heroku’s shared database to a MySQL database because:

  1. the shared DB has a limited level that you can scale to
  2. some of the Heroku add-on partners are a lot cheaper than Heroku’s DB offerings
  3. my development is being done with a MySQL DB, as opposed to Heroku’s PostGRESQL, and I did have one snafu where a query worked locally, but not on PostGRESQL
  4. it’s hard to see (and edit) data that’s in the Shared DB
  5. Heroku only allows read-access to the Shared DB, so it would’ve forced me to use their worker Dynos which are more expensive than something like Iron Worker workers for a lot of the tasks I wanted to do with workers

 

Lessons Importing CSV’s into PHPMYADMIN

I’ve been working on building dummy data into my local database.  Uploading a CSV that I filled with data using Excel, I populated my database.  Here are two lessons I learned.

Null Integers?
For one of the fields that I didn’t populate with data, imported a 0 because it was an integer field.  However, I needed the field null.  When I went into the record and took out the 0 and tried to save it, I got an error.  What I found was that when I clicked the checkbox for null for the record, it nullified the field.

Date Format
The date format was the European format in my database YYYY-MM-DD.  However, when I had the dates in the American format in my CSV MM-DD-YYYY. The records imported successfully in my database, but instead of erring out with the data, it set the date to 0000-00-00.  So I just had to delete the imported records, and then edit the date format in the CSV and re-import.

Setting up an Existing Rails App Locally

I had to download an existing Rails app and set it up.  To set-up a new Rails app, just follow the directions on the RoR site.

(1) download and install railsinstaller (railsinstaller.org)

(2) in the ruby command window, create a new app in the “sites” directory –

rails new appnamehere

(3) set-up git so you could download/upload to github and heroku (for version control).  follow these directions  and then these (ignore the parts about creating a new app and editing it, just need to set-up git).  when you add the remote, just use change the Hello-World to your app’s name

(4) add heroku.  follow these steps, (skip to step 3 since you set up git earlier)

(5) pull the latest version of your code.  In Git Bash go to (“cd …” for moving up in the directory, cd pathname (ie “cd sites/yourappname”) your app’s directory

(6) in git bash run: git pull heroku master git@heroku.com:yourappname.git

(7) go back to ruby command prompt to create db, run – rake db:create (i had an issue with this once, but this solved it)

(8) Create tables using schema – (i had connection issues, but this answer helped me)

rake db:schema:load

(9) run

rake db:migrate

(10) the tables should be set-up and you should be able to run “rails server” (or rails s for short).  when you go to localhost:3000 in your browser, you should see your app

(11) go to http://appservnetwork.com/ to download everything you need to run phpmyadmin, which will allow you to edit and view tables (structure and data).  i was having trouble the more recent versions so i had to use 2.4.9 (version) and that worked (set the host/domain name to localhost).  you’ll need to know what is the database login info for the development environment for the install of appserv (it will ask for it during the mysql install) – look in the sites/youappname/config/database.yml file for this info

(12) after install appserv, i could go to http://localhost/phpmyadmin/ in my browser to see my database and tables

(13) run “bundle install” in ruby command prompt (installs all gems specified in gemfile)