I recently upgraded a Drupal installation. In the process, the database has to be backed up. The DB backup seemed large to me so I wanted to check the size of the MySQL database. I found this tip on the MySQL forum from Prakash Babu.

SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;


It worked great and confirmed the size of the backup.

I recently purchased MySQL Workbench Standard Edition to help me design databases and to reverse engineer the databases of open source projects that I build on and support. I figured insight into the open source projects workings would allow me to extend and support them better.

So far I have only reversed engineered two open source ecommerce projects: Zen Cart and Magento Commerce. Workbench did a great job of identifying the tables, and in the case of Magento building the relations between tables. The layout or autolayout of the results left plenty of room for improvement. I tried to make Magento DB layout better by moving tables around, but Workbench could keep up with the task. When moved, the tables would disappear and then reappear when dropped. If your lucky, the table would be in the neighborhood of where you wanted it. If your not lucky, it would stay put.

All in all, Workbench seems to be coming along but it isn’t ready for serious design work. I look forward to the day that it is.

UPDATE: MySQL Workbench can crash randomly. Make sure you save often. When it crashes, a dialogue box will appear to offer you the option of saving. Save. Do not click cancel. Upon canceling, Workbench will only proceed to crash making you lose all your unsaved work. It just happened to me.

UPDATE: I found a little problem with the SQL CREATE export. At the very end of the CREATE syntax, the table type is specified. For example:

ENGINE = InnoDB;

Sometimes the line is not terminated with a semicolon (;). If the line in improperly terminated, importing the .sql file on the command line throws an error: ERROR 1064 (42000).

Security-hacks.com has compiled a list of the top fifteen free SQL injection scanners. SQL injections is a common technique to allow hackers access to your database. Depending on the quality of an applications “security” coding, access to your database could reveal proprietary business information, allow a hacker to modify or delete information, or simply scan your tables to find SSNs or CCNs. These scanners supposedly alert a would be good developer to a potential security breach in their program.

The syntax for modifying a column’s datatype in Oracle is

alter table table_name modify column_name new_datatype;