Category Archives: Database

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.

Updating/Reading Dates

Posted on by
Reply

I always forget how to do this.

UPDATE foo SET bar_date = TO_DATE( '2005-09-29', 'YYYY-MM-DD') WHERE foo_id=51

SELECT TO_CHAR(bar_date, 'yyyy-mm-dd hh24:mi:ss') FROM foo;

SELECT * FROM foo WHERE bar_date > TO_DATE('08/16/2006', 'MM/DD/YYYY')

When you need a date in a pinch for inserting test data use sysdate.

INSERT INTO foo (bar_date) VALUE (sysdate)

A good article about this is Oracle, SQL, Dates and Timestamps.

Sequences on Oracle

Posted on by
Reply

Here is an example of how to create a sequence which you can use for creating sequential numbers, typically for ID’s.

CREATE SEQUENCE person_seq
    INCREMENT BY 1
    START WITH 1
    NOMAXVALUE
    NOCYCLE
    CACHE 10;

This is how you would then use the sequence.

INSERT INTO persons (id, name) VALUES (person_seq.NEXTVAL, 'Dylan')
INSERT INTO persons (id, name) VALUES (person_seq.NEXTVAL, 'Isaac')

If you want to select the next ID you would do it like this.

SELECT person_seq.NEXTVAL FROM Dual

To learn more, Managing Sequences and the Oracle DUAL table.