mysql

MySql Partitioning

Hi Guys, I'm trying to create partitions in one big table, but I'm getting this error: 1505 - Partition management on a not partitioned table is not possible There is any command to convert the table? Or do I have to create a new one and import all data? There is any problem when using Partitioning with Replication? Thanks in advanc...

MySQL string match with multiple words

This page has a great example using REGEXP to do pattern matching. the problem with REGEXP won't match the following strings: "Mr John" "Dr. John" or even: "Mr. John Doe" with the string "John Doe" I would like to know how do I get positive matches for any of the given examples? Here is a sample code: Drop table Names; CREATE ...

Should I use MySQL blob field type?

I am struggling to decide if I should be using the MySQL blob field type in an upcoming project I have. My basic requirements are, there will be certain database records that can be viewed and have multiple files uploaded and "attached" to those records. Seeing said records can be limited to certain people on a case by case basis. Any...

The right way in SQL to deal with a difference between no value and a zero value in an INT datatype field

Hello, I was having issues with an INT field where there may be no value, a zero value, or an integer above zero and since SELECT foo FROM bar where foo = '' evaluates identically to SELECT foo FROM bar where foo = 0 I redefined foo like to foo INT(11) NULL so that rows where no value for foo is supplied do not get selected by e...

raise MysqlError; raise Mysql::Error both work, how did this happen?

Greetings, I was working on mysql exceptions and I came across this interesting issue, in which a raised exception is responding to two different exception names. How did this happen? -daniel #!/usr/bin/env ruby require 'rubygems' require 'mysql' require 'yaml' require 'pp' $config = YAML.load_file 'database.yml' class ExceptionPr...

Modelling country adjacency in SQL

I'm trying to model which countries border each other in MySQL. I have three tables: nodes ----- node_id MEDIUMINT countries --------- country_id MEDIUMINT (used as a foreign key for nodes.node_id) country CHAR(64) iso_code CHAR(2) node_adjacency -------------- node_id_1 MEDIUMINT (used as a foreign key for nodes.node_id) node_id_2 ME...

Self-Joins, Cross-Joins and Grouping

I've got a table of temperature samples over time from several sources and I want to find the minimum, maximum, and average temperatures across all sources at set time intervals. At first glance this is easily done like so: SELECT MIN(temp), MAX(temp), AVG(temp) FROM samples GROUP BY time; However, things become much more complicated ...

Show tables by engine in MySQL

How would I show all tables in MySQL with a given engine, e.g. InnoDB, MyISAM, FEDERATED? ...

mysql_num_rows does not work

this is my php code $sql="SELECT * FROM table_name WHERE FROM_UNIXTIME(date, '%Y') = $q order by id desc"; $q = mysql_query($sql) or die(mysql_error().$sql); $sql1="SELECT * FROM table_name WHERE FROM_UNIXTIME(date, '%Y') = $q"; $query=mysql_query($sql1); this gathers the results correctly (everything is correct) but when i use this...

How can I get dead lock in this situation?

In my client application I have a method like this (in practice it's more complex, but I've left the main part): public void btnUpdate_Click(...) { ... dataAdapter.Update(...); ... dataAdapter.Fill(...); // here I got exception one time } The exception I found in logs says "Deadlock found when trying to get lock; try restartin...

Entity Framework with MySQL - Timeout Expired while Generating Model

I've constructed a database in MySQL and I am attempting to map it out with Entity Framework, but I start running into "GenerateSSDLException"s whenever I try to add more than about 20 tables to the EF context. An exception of type 'Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine+GenerateSSDLExceptio...

MySQL count problem

I have a table course: CREATE TABLE course (COURSE_NO NUMERIC(8,0) NOT NULL ,DESCRIPTION VARCHAR(50) ,COST NUMERIC(9,2) ,PREREQUISITE NUMERIC(8,0) ,CREATED_BY VARCHAR(30) ,CREATED_DATE DATE ,MODIFIED_BY VARCHAR(30) ,MODIFIED_DATE DATE ,PRIMARY KEY (COURSE_NO) ,INDEX (PREREQUISITE) ) TYPE...

Help setting up separate MySQL server for rails

Maybe I am over complicating this in my head but I just don't know what to do next... I have a slice configured with MySQL, Rails & Passenger. I am setting up a new slice to separate the front-end from the database. The current server will become the database server and the new front-end will connect to it. I am using Ubuntu 8.10 on b...

MySQL Users and All Privileges

Ok, I'm trying to add a user to a MySQL database. This user should be able to add other users to the database it has privileges for. So I did this: GRANT ALL privileges ON thedbname.* TO 'topuser'@'%' IDENTIFIED BY 'pass'; However, I this user cannot add users. Is that because I only gave them 'all' for a single database? The permissi...

how can make my popupwindow always log in??

i create a popupwindow but link with mysql. When i click on it, it always show the page ask for log in first. But i already set it to the related page. How can solve this problem? This my code. <a HREF="http://192.168.1.3/ofcomcrm/index.php/products/add" onClick="return popup(this, 'notes')">Add Products</a></p> ...

MySQL Select Statement - Two Tables, Sort One Table by Count of Other Table

So I have built a voting system for a custom post system i wrote. I want to be able to sort by "most voted", "Most liked", etc. I have two tables. Entry: ID, Title, Post Vote: ID, EntryID, Result I want to be able to query the vote table for each entry and see how many vote's there are, and then sort the entry's by how many vote's e...

Can my code be more efficient?

I have to put my database's data in this format (values will differ, obviously), I think it's called an associative array (I'm horrible with terminology). $values=array( "Jan" => 110, "Feb" => 130, "Mar" => 215, "Apr" => 81, "May" => 310, "Jun" => 110, "Jul" => 190, ...

Create new user in MySQL and give it full access to one database

I'm new to MySQL administration. I want to create a new user in MySQL and give it full access only to 1 database, say dbTest, that I create. What would be the MySQL commands to do that? ...

checking user status (active, idle) on a web page

hello! i am checking on the user status - whether he is active or idle - in a web page (in a pop up window). in case he is idle for more than 30 minutes, the window will automatically close and reset some flags in the database. i set cookies containing the time-in (the time he opens the pop-up window) and check it against the current ti...

How can I auto renew data from MySQL without refresh?

I create dropdown fields to show the data from a MySQL database. Everytime when I key in new data, it must refresh to read the new data. I know Ajax can do it as well. But any other idea to solve this problem without refreshing to get the new data? Just when new data key in, it will auto renew without pressing F5 or refresh. This is my ...