JDBC Connection Strings for Coldfusion 6.1 / CFMX April 14, 2009
Posted by scoopseven in ColdFusion, Database, MySQL.add a comment
If you’re trying to setup ColdFusion MX 6 with MySQL here’s how you setup the MySQL datasources. In CF Administrator go to Datasources and Add a New datasource of type “Other”. Enter your datasource name, username and password and the following fields like this:
JDBC URL: jdbc:mysql://192.168.1.100/DatabaseName
Driver Class: com.mysql.jdbc.Driver
Driver Name:MySQL Connector/J
Make sure you have the latest MySQL drivers installed too.
How to Dump and Import a Database or Table using the CLI on Linux July 8, 2008
Posted by scoopseven in Linux, MySQL.1 comment so far
Log onto your server from the console or with a ssh client. I use Putty. Use the following command to dump a table.
myserver# mysqldump -u yourUserName -p yourDatabaseName yourTableName > yourDestinationFileName.sql
Use this command to dump a whole database (this may take a while). I’m using putting the dump file into a directory below too. Just to add to the example.
myserver# mysqldump -u yourUserName -p yourDatabaseName > /yourDestinationDirectory/yourDestinationFileName.sql
Because I’m moving this to another Linux DB server, I’m going to use scp to copy the file over
myserver# scp theFileIDumpedAbove.sql yourUserName@yourDestinationServer:/yourDestinationDir/yourDestinationFile.sql
Finally, on my destination server, where I just scp’ed the file to, I import my dump file into my database. If you’re replacing tables, make sure you drop those tables from the DB before you try to import them.
myserver# mysql -p yourDestinationDatabase < /yourSourceDirectory/yourSourceSqlFile.sql
MySQL Reusing/Cached Connection Problem June 16, 2008
Posted by scoopseven in Database, MySQL.add a comment
Problem with connecting to multiple databases within the same server is that every time you do:
mysql_connect(host, username, passwd);
it will reuse 'Resource id' for every connection, which means you will end with only one connection reference to avoid that do:
mysql_connect(host, username, passwd, true);
keeps all connections separate.
Finding Parsing Domain from Email in MySQL March 21, 2008
Posted by scoopseven in MySQL.add a comment
SELECT SUBSTRING_INDEX(Email, ‘@’, -1), COUNT(SUBSTRING_INDEX(Email, ‘@’, -1)) AS numDomain
FROM TableName
GROUP BY SUBSTRING_INDEX(Email, ‘@’, -1)
ORDER BY numDomain DESC, SUBSTRING_INDEX(Email, ‘@’, -1)
MySQL Duplicates January 18, 2008
Posted by scoopseven in MySQL.add a comment
#find duplicates
select columnlist, count( duplicate_column)
from table
group by duplicate_column
having count( duplicate_column) > 1
#find duplicates – show both records for comparison
SELECT table.* FROM table inner join
(select * from table group by duplicate_column having count(*)>1) as temp
on table.duplicate_column = temp.duplicate_column
From ODBC To JDBC November 7, 2007
Posted by scoopseven in MySQL.add a comment
Dear JDBC,
Although you may crash less, when connecting mySQL to Coldfusion, it has come to my attention that you cause some unexpected errors, which didn’t seem to bother me much.
I (ODBC) didn’t mind datetime fields set to 00:00:00. If you try to run a CF query that returns a 00:00:00 date, you (JDBC) throw an exception.
I (ODBC) also allow a user to update a date field to ” in Coldfusion. So [set datetimefield = ''] was ok. You (JDBC) throw an exception on that one too.
Your old friend, ODBC.
mySQL caching October 31, 2007
Posted by scoopseven in MySQL.1 comment so far
Here are a couple of sql statements to view your cache settings on a mySQL server. More on what they mean later (or at least a link).
show variables like ‘%query_cache%’;
show status like ‘%qcache%’;
SQL Fun October 19, 2006
Posted by scoopseven in Database, MySQL.add a comment
select a.*, ba.column1, bb.column1
from tablea a
join tableb ba on a.column1 = ba.column1 and ba.column2 = ’string’
join tableb bb on a.column1 = bb.column1 and bb.column2 = ’string’ and a.column2 = bb.column3
where a.column3 = ’string’
A couple of interesting things about this query. We’re matching up 1 row in table a with 2 rows in table b by aliasing table b in two different ways. We’re also using the “and” statement in our join, which is a neat trick.
Incorrect key file for table October 10, 2006
Posted by scoopseven in MySQL.2 comments
Incorrect key file for table ‘/tmp/#sql_18ed_O.MYI’ ; try to repair it.
This error was a result of a 95% full disk on a MySQL 5 server. Cleared up some space, error went away.
Connecting MySQL ODBC 3.51 to CFMX October 10, 2006
Posted by scoopseven in ColdFusion, MySQL.add a comment
The MySQL ODBC 3.51 driver doesn’t like CFMX because CFMX uses the odl MySQL password convention. So, to setup a datasource in CFMX that uses the MySQL 3.51 driver you have to setup a user in MySQL for ColdFusion, then run the following commands (I do it in MySQL Front).
SET PASSWORD FOR ‘cfuser’@'localhost’=OLD_PASSWORD(‘password’);
SET PASSWORD FOR ‘cfuser’@'%’=OLD_PASSWORD(‘password’);
After I do this, because I’m using MySQL Front, I can go look at the this user by clicking on USERS and then click on the Source tab. Copy and paste whatever is in the source tab and modify it to your liking. Here’s what I did to give my cfuser complete access to MySQL.
GRANT ALL PRIVILEGES ON *.* TO ‘cfuser’@'%’ IDENTIFIED BY PASSWORD ‘067747ad23f28fef’ WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO ‘cfuser’@'localhost’ IDENTIFIED BY PASSWORD ‘067747ad23f28fef’ WITH GRANT OPTION;
