EzDevInfo.com

What does the information_schema database represent?

I have one database in mysql.

But when i log into phpMyAdmin , it shows another database called information_schema.

Is that database always present with one database?

I mean to say is there a copy of information_schema for every database present in mysql or is there one database called inforemation_schema per mysql server?

If i modify this information_schema database how will that affect my current database?

storing negative value in mysql

How can I store negative value in mysql decimal? I have data from DMS to Decimal having negative values, so it is decimal and negative. So what can I use to store such value?

MySQL OUTER JOIN Syntax error

maybe a facepalm for you guys but as a SQL query noob I'm having a syntax issue, anyone know what's wrong?

SELECT * FROM company C
OUTER JOIN company_address A ON C.company_id = A.company_id
WHERE A.company_id IS NULL

Giving the error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to use near 
'OUTER JOIN company_address A ON C.company_id = A.company_id WHERE A.address_id 
' at line 2

I thank you kindly for reviewing my little question :)

MySQL efficiently copy all records from one table to another

Is there a more-efficent, less laborious way of copying all records from one table to another that doing this:

INSERT INTO product_backup SELECT * FROM product

Typically, the product table will hold around 50,000 records. Both tables are identical in structure and have 31 columns in them. I'd like to point out this is not my database design, I have inherited a legacy system.

mysql5 - As 'root' can't create database or do anything (Access denied)

I'm on Mac OS X Lion and just installed mysql5 using MacPorts.

Then I successfully ran:

sudo /opt/local/lib/mysql5/bin/mysql_install_db --user=mysql

I'm able to start the server and connect as 'root' fine, but I can't create any databases.

$ mysql5 -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.61 Source distribution

mysql> create database dbname;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'dbname'

I've done a lot of Googling trying to figure this out, and it appears that the problem might have to do with file system permissions for /opt/local/var/db/mysql5, but I've tried changing these to no avail:

$ ls -l /opt/local/var/db/
total 0
drwxrwxrwx  8 _mysql  _mysql  272 Apr 12 11:55 mysql5

I've experimented with the owner being '_mysql', 'mysql', and 'root:wheel', but none of them have made a difference.

Handling Deletes/Inserts/Select in a huge table

I have a dating website. In this website I used to send daily 10 photo matches to a user and store them in the structure as

SENDER RECEIVER
11      1
12      1
13      1
14      1

I maintain two month log. User can also check them by logging to my website.

Which means there is parallel insert and select, which surely is not an issue.

Problem is when a user becomes inactive or deletes its id, I need to remove all the entries from the log where sender='inactive-id'.

Size of log is approx 60 million. So whenever a delete queries comes in this huge table, all selects get locked and my site is getting down.

Note my table is merge myisam as I need to store 2-3 month records and on 1st of every month I change the definition

Installing MySQL on Snow Leopard using MacPorts

I am trying to install MySQL5 on Snow Leopard, but I am having some trouble. Here is my terminal log:

users-MacBook-Pro:~ sam$ sudo port install mysql5Password:
--->  Computing dependencies for mysql5
--->  Fetching mysql5
--->  Verifying checksum(s) for mysql5
--->  Extracting mysql5
--->  Applying patches to mysql5
--->  Configuring mysql5
--->  Building mysql5
--->  Staging mysql5 into destroot
--->  Installing mysql5 @5.1.53_0
The MySQL client has been installed.
If you also want a MySQL server, install the mysql5-server port.
--->  Activating mysql5 @5.1.53_0
--->  Cleaning mysql5
users-MacBook-Pro:~ sam$ sudo port install mysql5-server
Password:
--->  Computing dependencies for mysql5-server
--->  Fetching mysql5-server
--->  Verifying checksum(s) for mysql5-server
--->  Extracting mysql5-server
--->  Configuring mysql5-server
--->  Building mysql5-server
--->  Staging mysql5-server into destroot
--->  Creating launchd control script
###########################################################
# A startup item has been generated that will aid in
# starting mysql5-server with launchd. It is disabled
# by default. Execute the following command to start it,
# and to cause it to launch at startup:
#
# sudo port load mysql5-server
###########################################################
--->  Installing mysql5-server @5.1.53_0
******************************************************
* In order to setup the database, you might want to run
* sudo -u _mysql mysql_install_db5
* if this is a new install
******************************************************
--->  Activating mysql5-server @5.1.53_0
--->  Cleaning mysql5-server
users-MacBook-Pro:~ sam$ sudo chmod -Rf 777 /opt/local/var/db/mysql
chmod: /opt/local/var/db/mysql: No such file or directory
users-MacBook-Pro:~ sam$ sudo chmod -Rf 777 /opt/local/var/db/mysql5
users-MacBook-Pro:~ sam$ sudo -u _mysql mysql_install_db5
Installing MySQL system tables...
ERROR: 1004  Can't create file '/var/tmp/#sqlf2ef_1_0.frm' (errno: 9)
110125 20:22:45 [ERROR] Aborting

110125 20:22:45 [Note] /opt/local/libexec/mysqld: Shutdown complete


Installation of system tables failed!  Examine the logs in
/opt/local/var/db/mysql5 for more information.

You can try to start the mysqld daemon with:

    shell> /opt/local/libexec/mysqld --skip-grant &

and use the command line tool /opt/local/lib/mysql5/bin/mysql
to connect to the mysql database and look at the grant tables:

    shell> /opt/local/lib/mysql5/bin/mysql -u root mysql
    mysql> show tables

Try 'mysqld --help' if you have problems with paths.  Using --log
gives you a log in /opt/local/var/db/mysql5 that may be helpful.

Please consult the MySQL manual section
'Problems running mysql_install_db', and the manual section that
describes problems on your OS.  Another information source are the
MySQL email archives available at http://lists.mysql.com/.

Please check all of the above before mailing us!  And remember, if
you do mail us, you MUST use the /opt/local/lib/mysql5/bin/mysqlbug script!

I have tried repairing the my disks permissions but nothing seems to work :( Would appreciate any advice on how to get MySQL up and running!

Stop MySQL tolerating multiple NULLs in a UNIQUE constraint

My SQL schema is

CREATE TABLE Foo (
 `bar` INT NULL ,
 `name` VARCHAR (59) NOT NULL ,
 UNIQUE ( `name`, `bar` )
) ENGINE = INNODB;

MySQL is allowing the following statement to be repeated, resulting in duplicates.

INSERT INTO Foo (`bar`, `name`) VALUES (NULL, 'abc');

despite having

UNIQUE ( `name`, `bar` )

Why is this tolerated and how do I stop it?

JDBC Driver class not found: com.mysql.jdbc.Driver

I am developing a web application using maven spring and hibernate and I need to create schema using hibernate for which I had the following in my pom.xml to connect to MySQL 5.5 database.

    <!-- MySql 5.5 Connector -->   
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.15</version>
    </dependency>

This imported mysql-connector-java-5.1.15.jar in my Libraries under Maven Dependencies but when I try to connect to database it gives me Exception in thread "main" org.hibernate.HibernateException: JDBC Driver class not found: com.mysql.jdbc.Driver.

I have done this like gazillions of times, but I did it when I din't used to use Maven to manage my dependencies and build the project. I just used to have it the same jar file in the lib folder of a Dynamic Web Project in Eclipse IDE.

Could someone tell me what am I missing here and what else I need to have this jar in my build path?

Thanks.

How to Run PHP on IIS7.5 Express?

I have Win XP SP3 and have installed IIS7.5 Express and want to run PHP on it.

I am able to run simple HTML code on the server, I am able to start and stop the server by running iisservices.exe, but I am not able to run php scripts on it.

If I have the following PHP file:

<? php 
echo "hello world";
?>
<html>HI</html>

The output is HI but the PHP script doesn't run.

I have followed the steps described in this article to install PHP:

http://learn.iis.net/page.aspx/724/install-and-configure-php/

But can't proceed from step 10 onwards because IIS Express doesn't have an IIS Management Console MMC snap-in.

How do I configure IIS Express to run PHP?

Can't connect to MySQL server on 'localhost' (10061)

I recently installed MySQL 5 on Windows 2003 and tried configuring an instance. Everything worked fine until I got to "Applying Security settings", at which point it gave me the above error (Can't connect to MySQL server on 'localhost' (10061)).

I do have a port 3306 exception in my firewall for 'MySQL Server'.

Are table names in MySQL case sensitive?

Are table names in MySQL case sensitive? On my Windows dev machine the code I have is able to query my tables which appear to be all lowercase. When I deploy to the test server in our datacenter the table names appear to start with an uppercase letter.

The servers we use are all on Ubuntu.

How to get a list of MySQL user accounts

I'm using the MySQL command line utility and can navigate through a database. Now I need to see a list of user accounts. How can I do this?

I'm using MySQL version 5.4.1.

SQL multiple insert with common select-from

I have to insert data into a table with the following pattern

INSERT INTO tablename (a, b) VALUES (
    (123, (SELECT foo FROM someothertable WHERE some_condition)),
    (456, (SELECT foo FROM someothertable WHERE some_condition)),
    (789, (SELECT foo FROM someothertable WHERE some_condition)),
    ...

All inserted rows have the same value for the b column and I want to factor it out. I could manually perform the subselect and paste the value in, but that will break encapsulation in the set of scripts I'm writing.

Can I do this in pure SQL within the same query?

org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'

Hi When I try to connect to the datasource using JNDI, I am getting this error:

    [org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1452)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1371)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
    at org.apache.roller.weblogger.business.DatabaseProvider.getConnection(DatabaseProvider.java:179)
    at org.apache.roller.weblogger.business.DatabaseProvider.<init>(DatabaseProvider.java:141)
    at org.apache.roller.weblogger.business.startup.WebloggerStartup.prepare(WebloggerStartup.java:171)
    at org.apache.roller.weblogger.ui.core.RollerContext.contextInitialized(RollerContext.java:138)
    at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4205)
    at org.apache.catalina.core.StandardContext.start(StandardContext.java:4704)
    at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:799)
    at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:779)
    at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:601)
    at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1079)
    at org.apache.catalina.startup.HostConfig.deployDirectories(HostConfig.java:1002)
    at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:506)
    at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1315)
    at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:324)
    at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:142)
    at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1061)
    at org.apache.catalina.core.StandardHost.start(StandardHost.java:840)
    at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
    at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:463)
    at org.apache.catalina.core.StandardService.start(StandardService.java:525)
    at org.apache.catalina.core.StandardServer.start(StandardServer.java:754)
    at org.apache.catalina.startup.Catalina.start(Catalina.java:595)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:289)
    at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:414)
Caused by: java.lang.NullPointerException
    at sun.jdbc.odbc.JdbcOdbcDriver.getProtocol(JdbcOdbcDriver.java:507)
    at sun.jdbc.odbc.JdbcOdbcDriver.knownURL(JdbcOdbcDriver.java:476)
    at sun.jdbc.odbc.JdbcOdbcDriver.acceptsURL(JdbcOdbcDriver.java:307)
    at java.sql.DriverManager.getDriver(DriverManager.java:253)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1437)
    ... 30 more]

Context.xml

<Context path="/roller" docBase="c:\Program Files\Apache Software Foundation\Tomcat 6.0\webapps\roller" debug="0">
    <Resource name="jdbc/rollerdb" auth="Container"
        type="javax.sql.DataSource"
        driverClassName="com.mysql.jdbc.Driver"
        url="jdbc:mysql://localhost:3306/rollerdb?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=utf8-&amp;mysqlEncoding=utf8"
        username="roller_user"
        password="password"
        maxActive="20" maxIdle="3" removeAbandoned="true"
        maxWait="3000" 
    />
    <Resource name="mail/Session"
        auth="Container"
        type="javax.mail.Session"
        username="username@gmail.com"
        password="password"
        mail.debug="false"
        mail.user="username@gmail.com"
        mail.password="password"
        mail.smtp.from="username@gmail.com"
        mail.transport.protocol="smtp"
        mail.smtp.port="465"
        mail.smtp.host="smtp.gmail.com"
        mail.smtp.auth="true"
        mail.smtp.starttls.enable="true"
        mail.smtp.socketFactory.port="465"
        mail.smtp.socketFactory.class="javax.net.ssl.SSLSocketFactory"
        mail.smtp.socketFactory.fallback="false" 
    />
</Context>

web.xml

<web-app .. >
...
    <!-- jndi resources -->
    <resource-ref>
        <res-ref-name>jdbc/rollerdb</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>

    <resource-ref>
        <res-ref-name>mail/Session</res-ref-name>
        <res-type>javax.mail.Session</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>

</web-app>

roller-custom.properties

installation.type=auto
database.configurationType=jndi
database.jndi.name=jdbc/rollerdb
mail.configurationType=jndi
mail.jndi.name=mail/Session

I've got mysql-connector-java-5.1.15-bin.jar, activation.jar and mail.jar in tomcat's lib diectory and the web application roller in tomcat's webapps folder.

I've not been able to understand what I am doing wrong here. Could someone help me understand what am I missing here?

Thanks.

Thanks.