mysql

How do I keep a mySQL database secure?

I'm going to be implementing a PHP/mySQL setup to store credit card information. It seems like AES_ENCRYPT/AES_DECRYPT is the way to go, but I'm still confused on one point: how do I keep the encryption key secure? Hardwiring it into my PHP scripts (which will live on the same server as the db) seems like a major security hole. What's th...

Find out where your PHP code is slowing down (Performance Issue)

Here's my first question at SO. I have a internal application for my company which I've been recently ask to maintain. The applications is built in PHP and its fairly well coded (OO, DB Abstraction, Smarty) nothing WTF-ish. The problem is the applications is very slow. How do I go about finding out what's slowing the application down...

SQL Recursion

i have the next tables. groups table which contains hierarchically ordered groups and *group_member* which stores to which groups a user belongs to. groups --------- id parent_id name group_member --------- id group_id user_id ID PARENT_ID NAME --------------------------- 1 NULL Cerebra 2 1 CATS 3 2 ...

In what order are ON DELETE CASCADE constraints processed?

Here is an example of what I've got going on: CREATE TABLE Parent (id BIGINT NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB; CREATE TABLE Child (id BIGINT NOT NULL, parentid BIGINT NOT NULL, PRIMARY KEY (id), KEY (parentid), CONSTRAINT fk_parent FOREIGN KEY (parentid) REFERENCES Parent (id) ON DELETE CASCADE) ENGINE=InnoDB; CREAT...

Best way to stop SQL Injection in PHP

So specifically in a mysql database. Take the following code and tell me what to do. // connect to the mysql database $unsafe_variable = $_POST["user-input"]; mysql_query("INSERT INTO table (column) VALUES ('" . $unsafe_variable . "')"); // disconnect from the mysql database ...

How to check if a value exists in SQL table

I've got a table of URL's and I don't want any duplicate URL's. How do I check to see if a given URL is already in the table using PHP/MySQL? ...

MySQL Interview Questions

Hi, I've been asked to screen some candidates for a MySQL DBA / Developer position for a role that requires an enterprise level skill set. I myself am a SQL Server person so I know what I would be looking for from that point of view with regards to scalability / design etc but is there anything specific I should be asking with regards ...

reload a .sql schema without restarting mysqld

Is it possible to reload a schema file without having to restart mysqld? I am working in just one db in a sea of many and would like to have my changes refreshed without doing a cold-restart. ...

MySQL statement that returns a SQL statement?

I need to do a dump of a table on a remote server, but I can't access the server directly. The only access I have is through PHP scripts. Is there some way in which MySQL will return an INSERT INTO `table_name` (`field1`, `field2`) VALUES ('a', 'b'), ('c', 'd') statement, like what mysqldump will return? I don't have access to phpM...

What databases do I have permissions on

How can I find what databases I have a minimum of read access to in either basic SQL, MySQL specific or in PHP? ...

How do I call MySQL stored procedures from Perl?

How do I call MySQL stored procedures from Perl? Stored procedure functionality is fairly new to MySQL and the MySQL modules for Perl don't seem to have caught up yet. ...

Warnings fatal on Windows

While working between a Windows MySQL server and a Debian MySQL server, I noticed that warnings were fatal on Windows, but silently ignored on Debian. I'd like to make the warnings fatal on both servers while I'm doing development, but I wasn't able to find a setting that effected this behavior. Anyone have any ideas? ...

MySQL: "lock wait timeout exceeded"

I am trying to delete several rows from a MySQL 5.0.45 database: delete from bundle_inclusions; The client works for a while and then returns the error: Lock wait timeout exceeded; try restarting transaction It's possible there is some uncommitted transaction out there that has a lock on this table, but I need this process to trump...

Is it possible to select from "show tables" in MySQL?

Well, yes, the title says it all. SELECT * FROM (SHOW TABLES) AS my_tables Something along these lines, though the above does not work (on 5.0.51a, at least). ...

Anyone using Lisp for a MySQL-backended web app?

I keep hearing that Lisp is a really productive language, and I'm enjoying SICP. Still, I'm missing something useful that would let me replace PHP for server-side database interaction in web applications. Is there something like PHP's PDO library for Lisp or Arc or Scheme or one of the dialects? ...

Best way to archive live MySQL database

We have a live MySQL database that is 99% INSERTs, around 100 per second. We want to archive the data each day so that we can run queries on it without affecting the main, live database. In addition, once the archive is completed, we want to clear the live database. What is the best way to do this without (if possible) locking INSERTs? ...

How do I quickly rename a mysql database (change schema name)?

Usually I just dump the database and reimport it with a new name. This is not an option for very big databases. Apparently RENAME {DATABASE | SCHEMA} db_name TO new_db_name; does bad things, exist only in a handful of versions, and is a bad idea overall. Oh, one more thing - this needs to work with InnoDB, which stores things very diff...

Speeding up mysql dumps and imports

Are there any tricks for speeding up mySQL dumps and imports? This would include my.cnf settings, using ramdisks, etc. ...

mod_python/MySQL error on INSERT with a lot of data: "OperationalError: (2006, 'MySQL server has gone away')"

When doing an INSERT with a lot of data, ie~ `INSERT INTO table (mediumtext_field) VALUES ('...lots of text here: about 2MB worth...')', MySQL returns "OperationalError: (2006, 'MySQL server has gone away')" This is happening within a minute of starting the script, so it is not a timeout issue. Also, mediumtext_field should be able to h...

Database design for a booking application e.g. hotel

I've built one, but I'm convinced it's wrong. I had a table for customer details, and another table with the each date staying (i.e. a week's holiday would have seven records). Is there a better way? Many thanks. I code in PHP with MySQL ...