smallseo.info

How to find all the tables in MySQL with specific column names in them?

I have 2-3 different column names that I want to look up in the entire DB and list out all tables which have those columns. Any easy script?

Would you recommend PostgreSQL over MySQL? [closed]

We are currently working with JavaEE and MySQL 5 in our company, but we have some queries, especially delete queries that take > 10 min to execute. We are considering a switch to PostgreSQL.

What are the advantages of PostgreSQL over MySQL, if there are any? Do you have experience with both DBs and give me a roundup about whether this is a good idea or does this fully depend on the needs of our server?

INNER JOIN ON vs WHERE clause

For simplicity, assume all relevant fields are NOT NULL.

You can do:

SELECT
    table1.this, table2.that, table2.somethingelse
FROM
    table1, table2
WHERE
    table1.foreignkey = table2.primarykey
    AND (some other conditions)

Or else:

SELECT
    table1.this, table2.that, table2.somethingelse
FROM
    table1 INNER JOIN table2
    ON table1.foreignkey = table2.primarykey
WHERE
    (some other conditions)

Are those two worked on the same way by MySQL?

How do I specify unique constraint for multiple columns in MySQL?

I have a table:

table votes (
    id,
    user,
    email,
    address,
    primary key(id),
);

Now I want to make the columns user, email, address unique (together).

How do I do this in MySql?

• Of course the example is just... an example. So please don't worry about the semantics.

How to output MySQL query results in CSV format?

Is there an easy way to run a MySQL query from the Linux command line and output the results in CSV format?

Here's what I'm doing now:

mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/        /,/g' | tee list.csv
select id, concat("\"",name,"\"") as name
from students
EOQ

It gets messy when there are a lot of columns that need to be surrounded by quotes, or if there are quotes in the results that need to be escaped.

UTF-8 all the way through

I'm setting up a new server, and want to support UTF-8 fully in my web application. I have tried in the past on existing servers and always seem to end up having to fall back to ISO-8859-1.

Where exactly do I need to set the encoding/charsets? I'm aware that I need to configure Apache, MySQL and PHP to do this - is there some standard checklist I can follow, or perhaps troubleshoot where the mismatches occur?

This is for a new Linux server, running MySQL 5, PHP 5 and Apache 2.

How to reset AUTO_INCREMENT in MySQL?

How can I reset the auto-increment of a field? I want it to start counting from 1 again.

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.

Why shouldn't I use mysql_* functions in PHP?

What are the technical reasons why I shouldn't use mysql_* functions? (e.g. mysql_query(), mysql_connect() or mysql_real_escape_string())?

Why should I use something else even if they work on my site?

How can I prevent SQL-injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example:

$unsafe_variable = $_POST['user_input']; 

mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");

That's because the user can input something like value'); DROP TABLE table;--, and the query becomes:

INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')

What can be done to prevent this from happening?

What's the difference between utf8_general_ci and utf8_unicode_ci

Between utf8_general_ci and utf8_unicode_ci, are there any differences in terms of performance?

mysql_fetch_array() expects parameter 1 to be resource (or mysqli_result), boolean given

I am trying to select data from a MySQL table, but I get one of the following error messages:

mysql_fetch_array() expects parameter 1 to be resource, boolean given

or

mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given

This is my code:

$username = $_POST['username'];
$password = $_POST['password'];

$result = mysql_query('SELECT * FROM Users WHERE UserName LIKE $username');

while($row = mysql_fetch_array($result)) {
    echo $row['FirstName'];
}

The same applies to code like

$result = mysqli_query($mysqli, 'SLECT ...');
// mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given
while( $row=mysqli_fetch_array($result) ) {
    ...

and

$result = $mysqli->query($mysqli, 'SELCT ...');
// Call to a member function fetch_assoc() on a non-object
while( $row=$result->fetch_assoc($result) ) {
    ...

and

$result = $pdo->query('SLECT ...', PDO::FETCH_ASSOC);
// Invalid argument supplied for foreach()
foreach( $result as $row ) {
    ...

and

$stmt = $mysqli->prepare('SLECT ...');
// Call to a member function bind_param() on a non-object
$stmt->bind_param(...)

and

$stmt = $pdo->prepare('SLECT ...');
// Call to a member function bindParam() on a non-object
$stmt->bindParam(...)

Which MySQL Datatype to use for storing boolean values?

Since MySQL doesn't seem to have any 'boolean' datatype, which datatype do you 'abuse' for storing true/false information in MySQL? Especially in the context of writing and reading from/to a PHP-Script.

Over time I have used and seen several approaches:

• tinyint, varchar fields containing the values 0/1,
• varchar fields containing the strings '0'/'1' or 'true'/'false'
• and finally enum Fields containing the two options 'true'/'false'.

None of the above seems optimal, I tend to prefer the tinyint 0/1 variant, since automatic type conversion in PHP gives me boolean values rather simply.

So which datatype do you use, is there a type designed for boolean values which I have overlooked? Do you see any advantages/disadvantages by using one type or another?

How should I ethically approach user password storage for later plaintext retrieval?

As I continue to build more and more websites and web applications I am often asked to store user's passwords in a way that they can be retrieved if/when the user has an issue (either to email a forgotten password link, walk them through over the phone, etc.) When I can I fight bitterly against this practice and I do a lot of ‘extra’ programming to make password resets and administrative assistance possible without storing their actual password.

When I can’t fight it (or can’t win) then I always encode the password in some way so that it at least isn’t stored as plaintext in the database—though I am aware that if my DB gets hacked that it won’t take much for the culprit to crack the passwords as well—so that makes me uncomfortable.

In a perfect world folks would update passwords frequently and not duplicate them across many different sites—unfortunately I know MANY people that have the same work/home/email/bank password, and have even freely given it to me when they need assistance. I don’t want to be the one responsible for their financial demise if my DB security procedures fail for some reason.

Morally and ethically I feel responsible for protecting what can be, for some users, their livelihood even if they are treating it with much less respect. I am certain that there are many avenues to approach and arguments to be made for salting hashes and different encoding options, but is there a single ‘best practice’ when you have to store them? In almost all cases I am using PHP and MySQL if that makes any difference in the way I should handle the specifics.

Additional Information for Bounty

I want to clarify that I know this is not something you want to have to do and that in most cases refusal to do so is best. I am, however, not looking for a lecture on the merits of taking this approach I am looking for the best steps to take if you do take this approach.

In a note below I made the point that websites geared largely toward the elderly, mentally challenged, or very young can become confusing for people when they are asked to perform a secure password recovery routine. Though we may find it simple and mundane in those cases some users need the extra assistance of either having a service tech help them into the system or having it emailed/displayed directly to them.

In such systems the attrition rate from these demographics could hobble the application if users were not given this level of access assistance, so please answer with such a setup in mind.

Thanks to Everyone

This has been a fun questions with lots of debate and I have enjoyed it. In the end I selected an answer that both retains password security (I will not have to keep plain text or recoverable passwords), but also makes it possible for the user base I specified to log into a system without the major drawbacks I have found from normal password recovery.

As always there were about 5 answers that I would like to have marked correct for different reasons, but I had to choose the best one--all the rest got a +1. Thanks everyone!

Also, thanks to everyone in the Stack community who voted for this question and/or marked it as a favorite. I take hitting 100 up votes as a compliment and hope that this discussion has helped someone else with the same concern that I had.

MyISAM versus InnoDB

I'm working on a projects which involves a lot of database writes, I'd say (70% inserts and 30% reads). This ratio would also include updates which I consider to be one read and one write. The reads can be dirty (e.g. I don't need 100% accurate information at the time of read).
The task in question will be doing over 1 million database transactions an hour.

I've read a bunch of stuff on the web about the differences between MyISAM and InnoDB, and MyISAM seems like the obvious choice to me for the particular database/tables that I'll be using for this task. From what I seem to be reading, InnoDB is good if transactions are needed since row level locking is supported.

Does anybody have any experience with this type of load (or higher)? Is MyISAM the way to go?