Welcome to my website. I am always posting links to photo albums, art, technology and other creations. Everything that you will see on my numerous personal sites is powered by the formVistaTM Website Management Engine.

icon.linkedin.jpgicon.twitter.jpg

  • Solution for MySQL ERROR 1396 (HY000): Operation CREATE USER failed for
    04/28/2016 11:56AM

    This indicates that the user already exists, or did exist but that all of the data for that user has not been deleted.

    As the mysql root user:

    REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'hostname';
    DROP USER 'user'@'hostname'


    Then re-try creating the user.

  • Executing Dynamically Generated SQL Queries in a Shell Script and Saving the Output to a Variable
    01/10/2014 10:19AM

    If you would like to, in a shell script, dynamically generate SQL queries for MySQL and save the output of those queries to a variable that you can then use in the script, here is an example:

    #!/bin/bash

    for i in `cat tables_list.txt`
    do

       # Build the query
       QUERY="SELECT count(*) FROM ${i}"

       # Run the query from the command-line and save the
       # output into the $ROW_COUNT variable
       ROW_COUNT=$(echo $QUERY | mysql -u${USER_NAME} -p${PASSWORD} -h ${HOST} -P ${PORT} --skip-column-names ${DBASE})

       # Do something with the var...
       echo -n -e "$ROW_COUNT\t" >> $OUT_FILE
       echo "$i" >> $OUT_FILE

    done;

  • Figuring out MySQL ERROR 1005 (HY000) Can't create table (errno: 150)
    01/03/2014 4:56PM

    So I'm dumping a database on a remote server to pull down to my local box to do some development.  When loading the mysqldump file I encountered the error:

    ERROR 1005 (HY000) at line 8680: Can't create table 'database.table' (errno: 150)

    After doing some searching online it seems that is one of the notoriously cryptic MySQL error messages that is woefully overloaded.

    I did manage to fix the error (which ended up being a foreign key reference from another table to the one that was erroring out whereby the column definitions were not exactly the same) and learned two important things:

    1. You can invoke the following mysql command immediately after the error to get a more verbose error message (which is what enabled me to solve the problem): SHOW INNODB STATUS
    2. Jason Hinkle has a very nice online reference with a number of possible culprits, and their solutions are listed.


  • Executing MySQL Queries and Commands from the Command Line to a Remote Server
    10/31/2013 10:56AM

    Following are a couple of ways to execute SQL on a remote MySQL server via the shell.

    Executing SQL directly from the command line:

    $ mysql -u uid -p -h remote.host database -e 'SQL query here;'

    Executing SQL from a file on the local host on the remote server:

    $ mysql -u uid -p -h remote.host database < file.sql

    You can also connect to the remote mysql server and from the command line execute SQL from files on the local drive as such (as long as the file is in the same directory from which the mysql connection is made

    mysql> \. file.sql

    Notice NO ';' at the end of the command.

    Moreover, you can use the following to run commands in your local shell

    mysql> \! ls

    Will give you the directly listing of the dir from which you invoked the mysql remote connection.

    mysql > \! vi file.sql

    Will allow you to edit the file on your local drive without having to close the mysql cli.

  • Executing Dynamically Generated SQL Queries from a Shell Script
    10/25/2013 1:03PM

    Following is how to generate dynamic SQL in a shell script and then execute those queries.

    Let's say, for instance, that you have a list of tables that you want to flush regularly during development and don't want to type in the SQL queries each time.  Moreover, you just want to maintain a list of the table names and add and remove from it when necessary and have your script dynamically generate and execute the delete statements.

    For the purposes of this tutorial we are running these commands as root to avoid the additional complication of authentication.

    The first thing you need is your text file with the list of your tables:

    table_list.txt:

    table_a
    table_b
    table_c

    delete_data.sh:

    #!/bin/bash

    for i in `cat tables_list.txt`
    do

       mysql -u root dbase_name << END_SQL
       delete from ${i};
    END_SQL
    #
    # It is imperitive that the END_SQL redirect identifier
    # be on its own line without any leading or trailing spaces
    #

    done

    The script will run, will make a connection to MySQL and then execute the command with the dynamically generated target table.

    This will work with other databases other than MySQL as well.

  • 1 2 >>
Advanced Search

Categories

Archives