Tag Archives: MySQL

Bootstrapping is the Rage

Posted on by
Reply

Web startups boot strapping themselves are all the rage.  Every few weeks someone approaches me with a new idea.  The implementation plans for the venture are almost always identical.

  • web application
  • open source framework, usually Ruby on Rails, sometimes PHP
  • free database, almost always MySQL
  • cloud hosting
  • small group of guys, typically people who have made money at a previous venture
  • no salaries, just equity

Inspirations for these companies are places such 37signals, Wufoo, Plentyoffish and World of Goo.

I enjoy hearing the ideas and almost joined one venture.  But in the end none have been compelling enough for me to risk 6 to 12 months of income.  Hopefully I’ll think of one on my own. 🙂

‘Error in my_thread_global_end()’ when running rake db:migrate

Posted on by
2

When I run rake db:migrate I get this error at the end.

Error in my_thread_global_end(): 1 threads didn't exit

I pinned it down to this code in one of my migration files.

# create admin user
user = User.new
user.login = 'admin'
user.password = 'password'
user.save(false)

If I don’t run user.save(false) I don’t get the error. I am not sure why, the user does get saved properly to the database.

I saw this post on the MySQL forums that seemed to indicate it was an issue with libmySQL.dll. So I upgraded my MySQL instance from 5.0.27 to 5.0.51a. Of course this did not go smoothly, I got this error when trying to reconfigure the MySQL server instance “MySQL service could not be started error 0”. Fortunately another post on the MySQL forums, Could not start service : Error 2003, solved this problem for me. I just had to remove the following files from the mysql/data directory.

  • ib_logfile0
  • ib_logfile1
  • ibdata1

Unfortunately when I then did a rake db:migrate I saw this error.

Mysql::Error: Table 'prayer.schema_info' doesn't exist:
SELECT version FROM schema_info

After deleting and recreating the database I was finally able to run rake db:migrate. Unfortunately I still got the same error that inspired this post.

Googling some more I saw a MySQL bug report, MySQL Bugs: #25621: Error in my_thread_global_end(): 1 threads didn’t exit. Apparently this is a client side issue and I think I can safely ignore it though it is quite annoying.

Strictly speaking, this is not MySQL bug. This is a client bug – a client application (PHP, probably) linked with libmysqlclient calls my_thread_init() but does not call my_thread_end() as appropriate (doesn’t call at all or calls too late). MySQL client library detects this and issues a warning.

On the other hand, we can just remove the check and let buggy applications to fail some other way. Not calling my_thread_end() is a guaranteed memory leak. Calling it too late could easily crash the application.

Listing All Tables

Posted on by
Reply

With MySQL listing all the tables in a database is easy enough.

mysql> show tables;

With Oracle it’s a little less straight-forward.

> select table_name from user_tables;

OR

> select * from user_objects where object_type = 'TABLE';

OR

> select table_name from tabs;

OR

> select table_name from all_all_tables;

phpMyAdmin not starting

Posted on by
Reply

Today when I started up phpMyAdmin (version 2.10.0.2) by going to http://localhost/phpMyAdmin I saw this error screen.

phpMyAdmin – Error

I googled around but could not find any solutions.

So I went and tried to install the latest version of phpMyAdmin, 2.11.5. When I tried to run setup I saw this error screen.

phpMyAdmin – Error
Cannot start session without errors, please check errors given in your PHP and/or webserver log file and configure your PHP installation properly.

Now that I had an error message to work with I googled around and found this post, Xampp phpMyAdmin Problem, which helped me diagnose the problem.

It turns out during one of my cleaning sessions I wiped out the session directory in, “C:\DOCUME~1\fkim\LOCALS~1\Temp\php\session.” After restoring it I ran into my next problem.

phpMyAdmin – Error

 Cannot load mysql extension. Please check your PHP configuration. – Documentation

I upgraded to PHP 5.2.5 from 5.2.1 but that did not help. I installed the mbstring module but that did not help.

Finally I added the PHP extension directory to the path (I had already added the PHP directory to the path) and it finally worked! My path now includes the following two directories, E:\Program Files\PHP and E:\Program Files\PHP\ext.

View that Calculates using Previous Date

Posted on by
Reply

I have a table of data that tracks the mileage of my beloved 2005 Toyota Prius.

+----+------------+---------+---------+-------+ 
| id | date       | mileage | gallons | cost  | 
+----+------------+---------+---------+-------+ 
|  1 | 2005-02-14 |     280 |   8.615 | 16.03 | 
|  2 | 2005-02-27 |     480 |   4.775 |  8.59 | 
|  3 | 2005-03-19 |     713 |   7.213 | 14.27 | 
|  4 | 2005-04-09 |     999 |    7.86 | 16.81 | 
|  5 | 2005-04-11 |    1172 |   3.174 |  6.63 | 
|  6 | 2005-05-05 |    1560 |   8.889 | 18.66 | 
|  7 | 2005-06-07 |    1985 |   9.815 | 20.01 | 
|  8 | 2005-07-03 |    2444 |   9.868 |  21.7 | 
|  9 | 2005-08-13 |    2762 |   7.728 |    20 | 
| 10 | 2005-09-11 |    3271 |  10.072 | 30.11 | 
| 11 | 2005-10-24 |    3646 |   8.953 | 22.19 | 
| 12 | 2005-10-30 |    3959 |   6.583 | 17.11 | 
| 13 | 2005-11-04 |    4184 |   4.538 | 11.25 | 
| 14 | 2005-11-21 |    4631 |   8.742 |    18 | 
| 15 | 2005-12-31 |    4897 |   8.511 | 18.64 | 
| 16 | 2006-02-02 |    5609 |    7.83 |    18 | 
| 17 | 2006-03-01 |    6064 |    3.38 |  7.16 | 
| 18 | 2006-03-13 |    6400 |   7.171 |  16.2 | 
| 19 | 2006-03-22 |    6605 |   5.399 | 13.17 | 
| 22 | 2006-02-16 |    5944 |   7.957 |  17.5 | 
| 20 | 2006-04-06 |    6974 |   7.774 | 19.43 | 
| 21 | 2006-04-23 |    7316 |   7.102 | 20.45 | 
+----+------------+---------+---------+-------+

I wanted a way to calculate how many miles were driven for each trip, i.e. between consecutive dates, and what was the miles per gallon (MPG) for that trip.  To do this I modified what I learned in this post, unique ID field, getting next and previous existing ID from table to create a view that does all these calculations.  The advantage of this view is that it does not matter what order I enter the data, it will always calculate correctly.

CREATE VIEW v_prius_mpg AS 
  SELECT id, date, mileage, gallons, cost, mileage as trip_mileage,
         mileage / gallons AS mpg, cost / gallons AS price_per_gallon 
    FROM prius_mpg 
    WHERE id = 1 
  UNION 
  SELECT New.id AS id, New.date, New.mileage, New.gallons, New.cost,
        (New.mileage - Old.mileage) AS trip_mileage,
        (New.mileage - Old.mileage)/ New.gallons AS mpg,
        (New.cost / New.gallons) AS price_per_gallon 
    FROM prius_mpg New, prius_mpg Old 
    WHERE New.id > 1 
      AND Old.id = 
        (SELECT id 
           FROM prius_mpg 
           WHERE date < New.date 
           ORDER BY date DESC 
           LIMIT 1);

And here are the results of the view.  Note how row 20 is out of order but it still calculates correctly the trip mileage and MPG.

+----+------------+---------+---------+--------------+------------------+ 
| id | date       | mileage | gallons | trip_mileage | mpg              | 
+----+------------+---------+---------+--------------+------------------+ 
|  1 | 2005-02-14 |     280 |   8.615 |          280 |  32.501450957632 | 
|  2 | 2005-02-27 |     480 |   4.775 |          200 | 41.8848167539267 | 
|  3 | 2005-03-19 |     713 |   7.213 |          233 | 32.3027866352419 | 
|  4 | 2005-04-09 |     999 |    7.86 |          286 | 36.3867684478371 | 
|  5 | 2005-04-11 |    1172 |   3.174 |          173 | 54.5053560176434 | 
|  6 | 2005-05-05 |    1560 |   8.889 |          388 | 43.6494543818202 | 
|  7 | 2005-06-07 |    1985 |   9.815 |          425 |  43.301069791136 | 
|  8 | 2005-07-03 |    2444 |   9.868 |          459 | 46.5139845966761 | 
|  9 | 2005-08-13 |    2762 |   7.728 |          318 | 41.1490683229814 | 
| 10 | 2005-09-11 |    3271 |  10.072 |          509 | 50.5361397934869 | 
| 11 | 2005-10-24 |    3646 |   8.953 |          375 | 41.8854015413828 | 
| 12 | 2005-10-30 |    3959 |   6.583 |          313 | 47.5467112258849 | 
| 13 | 2005-11-04 |    4184 |   4.538 |          225 | 49.5813133539004 | 
| 14 | 2005-11-21 |    4631 |   8.742 |          447 | 51.1324639670556 | 
| 15 | 2005-12-31 |    4897 |   8.511 |          266 | 31.2536717189519 | 
| 16 | 2006-02-02 |    5609 |    7.83 |          712 | 90.9323116219668 | 
| 17 | 2006-03-01 |    6064 |    3.38 |          120 | 35.5029585798817 | 
| 18 | 2006-03-13 |    6400 |   7.171 |          336 | 46.8553897643285 | 
| 19 | 2006-03-22 |    6605 |   5.399 |          205 | 37.9699944434154 | 
| 20 | 2006-02-16 |    5944 |   7.957 |          335 | 42.1012944577102 | 
| 21 | 2006-04-06 |    6974 |   7.774 |          369 |  47.465912014407 | 
| 22 | 2006-04-23 |    7316 |   7.102 |          342 | 48.1554491692481 | 
+----+------------+---------+---------+--------------+------------------+

I always wanted to appear on every row how many gallons of gas had been consumed up to that day and how much had spent on gas up to date but I could not figure out how.  The only thing I could do was determine the sums up until now.

mysql> select sum(gallons) as gallons_to_date,
       sum(cost) as cost_to_date from prius_mpg where date <= current_date;
+-----------------+--------------+
| gallons_to_date | cost_to_date |
+-----------------+--------------+
|         161.949 |       371.91 |
+-----------------+--------------+
1 row in set (0.01 sec)

mysqlimport

Posted on by
2

MySQL provides a utility for importing text files into a database called mysqlimport. It has some nice features including:

  1. If you don’t include the ID in the data of the text file and your table is set up to auto-increment the ID then it will create the ID for you when importing the data.
  2. It recognizes different delimiters for the data, the common delimiters being tabs and commas.
  3. It will delete previous data.

An example of how to use it is:
mysqlimport --user=admin --password=admin --local --delete --verbose -c name,pos,age,b,tm,rel,dl,ab,r,h,d,t,hr,rbi,bb,k,sb,cs,'$$' --fields -terminated-by='\t' roto batters.txt

Note how in this example that the tab delimiter is specified by ‘\t’. Also special column names must be enclosed within quotes like ‘$$’. And finally the name of the text file you are using must be the same as the name of the table in which you are importing.

One frustrating thing about mysqlimport is that if there are warnings or errors during import it does not actually tell you what the errors are. mysqlimport does not have a problem with no data being present where it expects there to be some, for example in a comma delimited file if it sees ,, it just assumes that column should be blank or 0, depending on the data type.

However mysqlimport will count as a warning if it sees more data fields then you specified in the number of columns. This could happen because you forgot to specify a column or it could happen because your data has empty spaces at the end of the line.

To debug these warnings or errors what I do is import smaller sets of the data at a time to try to discover what is causing the problem. Usually the same pattern is causing the warning so once you discover what is the problem you can eliminate it from throughout the text file.

Problem connecting to MySQL 4.1 via PHP

Posted on by
Reply

After installing MySQL 4.1 I was unable to connect to the MySQL database via PHP. It turns out this was because I set the root password. I tried instructing MySQL to use the old password encryption method by adding these two lines to my.ini but that did not work.

#Use old password encryption method (needed for 4.0 and older clients).
old_passwords

Finally I just uninstalled MySQL 4.1 and reinstalled it without the root password. After doing that everything was fine.

Also I noticed when I uninstalled MySQL 4.0 it did not remove the service. When I installed MySQL 4.1 it complained about the Windows service”MySQL” already being there so I had to install the Windows service “MySQL41”. Then I uninstalled MySQL 4.1 due to the above issue and it removed the “MySQL41” service.

When I installed the second time it thought I was trying to reconfigure the “MySQL” service. I instructed the installer to remove the “MySQL” service. Then I uninstalled and reinstalled a third time and this time I was able to install the Windows service “MySQL41” properly.