Trying to update the schema or truncating the table or something else and got this error?
Turn the constraint temporarily and the issue shall be fixed:
SET FOREIGN_KEY_CHECKES=0; TRUNCATE TABLE somepoortable; . . SET FOREIGN_KEY_CHECKS=1;
That is it!
Are you having those ???? things on your webpage when something you entered on Mr mysql is unicode character?
Here is a fix for that
Assuming you are using PDO object in your Db Class..
$db = array containing information ....
$this->pdo = new PDO("mysql:host={$db['host']};dbname={$db['database']};charset=utf8", $db['username'], $db['password']);
Now the next task to to tell Mysql to honor unicodes:
$this->query('SET NAMES utf8');
$this->query('SET CHARACTER SET utf8');
$this->query('SET COLLATION_CONNECTION="utf8_general_ci"');
where your query function could look like
public function query($query, $params=array())
{
$results=array();
if (!empty($this->pdo))
{
try
{
$stmt=$this->pdo->prepare($query);
$stmt->execute($params);
$results=$stmt->fetchAll(PDO::FETCH_ASSOC);
}
catch(PDOException $exception)
{
echo "error occured";
//logit or whatever
die();
}
}
return $results;
}
Yea this would take care of the problem. There are certain things you have to do on the mysql database itself to handle unicode but I will not discuss those here
Hope it would help somebody..
From time to time I have been changing the column datatype for different reasons. Majorly being for performance and efficiency and some other time based on how the mysql engines would be strong on some types vs the other
here is the simple command i use for changing the column datatype
alter table table_name modify column column_name datatypegoeshere eg. ALTER TABLE members MODIFY COLUMN date_created TIMESTAMP;
It is customary sometimes to share the part of the database from sandbox to a fellow developer or to access it from the other machine – umm.. is it not how the servers are doing it?? what are you talking about đ
Anyway here is a simple note to make it happen.1. We need to bind the machine name for the server. to do that
open your my.cnf – inside the /etc/mysql/my.cnf for debianish machines and search for the
[mysqld] and add the following
bind-address = your ip address goes here
you can find your ip address from
ifconfig
then save the file and restart your mysql
sudo /etc/init.d/mysql restart
There are ways to assign for a specified user from the specified ip addresses as well.
Like if you have database db1 and user1 from ip address 168.192.3.4 then
grant select, insert, update on db1.* to 'user1'@'168.192.3.4'
I was reinstalling my mac machine and wanted to install my goodies [PHP, Apache, MySQL] on it and I come up with the newer version error when i try to install mysql.
here is what I gather from different sites and blogs:
Go to the terminal and issue these commands
sudo rm /usr/local/mysql*
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm /etc/my.cnf
sudo rm -rf /var/db/receipts/com.mysql.*
This has taken care of the error for me
The thing is, you would have sql that have LIMIT appended at the end like
select * from sometable limit 25
but what if you wanted to know how many rows would have been returned if it was not limited?
the trivial approach is
select count(*) from sometable
and then issuing query with limit on it.
This is slower solution.
here is the way to do it in a better way
select sql_calc_found_rows * from sometable limit 25; select found_rows();
Right after execution the select with sql_calc_found_rows, we would get the ‘would have been’ value on the second query.
Mysql comes with a handy tool for dumping database, like from remote server to local machine or vice versa.
Sometimes you might want to dump some tables but not the other here you go!
mysqldump -h[HOST] -u[USERNAME] --port=[PORT] -p --ignore [ignoretablename] databasename > databasename.sql
The database.sql would contain all the schema + data.
Then import it as
mysql -h[host] -u[username] -p databasename < databasename.sql
You can provide -p[password] or if you leave it as -p you would be prompted for password later which is a cool way to proceed.
ENJomYsql!
Unicode is ruling, it would come handy when working with non ASCII characters like when dealing with Chinese or Ethiopian alphabet amharic characters.
With internalization being the main concern in softwares, bit it website or mobile application, there is a chance you will need unicode in your application.
Hibernate is an ORM, object relational mapper, that is being used with Java and .net. It will allow to abstract all the database related stuffs with simple interface. In this unicode hibernate tutorial, I will show how you can insert, select update and delete records with unicode.
Why do I get No provider for Entity manager Error and how should I fix it
I will show the fix from the hibernate side for MySQL. But keep in mind that unicode characters have to be enabled on the database side as well with proper encoding.
See how you can avoid could not open hibernate session error
The solution for having unicode characters to be recognized as they are passing though the hibernate world would be done on the config file.
Here are the steps to follow:
1. go to your hibernate.cfg.xml file
2. make your the connection url something like: jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=UTF-8 – make sure the the ampersand is the html-encoded ampersand.
Have you wondered how to update/insert NULL as value to the existing row in mysql? Too simple:
UPDATE table_name SET field_name = NULL WHERE some_criteria
Watch! there is no any quote on the NULL part, if you do so, it would be inserted/updated as string with value “NULL”
Using if / else on the mysql would be much easier to express with simple query. Her we go:
Assume you have the following table:
+--------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+----------------+ | file_id | int(11) | NO | PRI | NULL | auto_increment | | file_path | text | NO | | NULL | | | file_name | varchar(120) | NO | MUL | NULL | | | file_size | bigint(20) | YES | | NULL | | | file_content | text | NO | MUL | NULL | | +--------------+--------------+------+-----+---------+----------------+
Then you want to collect records either by file_id or by file_size based on the file_size, if the file size above some value, check the name of the file to contain xls, otherwise, consider only files that have file id more than y
select * from file where if (file_size > x, file_name like '%.xls', file_id > y)
YUP!