mysql

MySql Entity Framework Select

I am having trouble with an MySql query. string strSql = "select SQL_CALC_FOUND_ROWS *, pv.* from products pv WHERE pv.name = 'Teddy Bear';"; strSql += "SET @resultCount = FOUND_ROWS();" MySqlParameter parm = new MySqlParameter("@resultCount",MySqlDbType.Int32) parm.Direction = ParameterDirection.Output; var result = ObjectContext.Ex...

JDBC Driver class not found in Hibernate program

I am making a simple mapping program in hibernate. I am facing an error: JDBC Driver class not found: com.mysql.jdbc.Driver java.lang.ClassNotFoundException: com.mysql.jdbc.Driver It gives an error on this line: Session session = HibernateUtil.getSessionFactory().openSession(); hibernate.cfg.xml is correct. In the same configratio...

Using single query to retrieve multivalued attribute by joining two tables without causing duplication of fields in mysql

Table 1 : QUERY: Create table client ( applicationNo int primary key, name varchar(20) ); Insert statement: Insert into client values (1,'XYZ'),(1,'ABC'),(1,'DEF'); applicationNo | name 1 | XYZ 2 | ABC 3 | DEF Table 2: Query : Create table client ( applicationNo int, ...

Maintainable Web Widgets

How do you create maintainable web widgets? Right now, I allow my web users to create web widgets via a simple interface, and all the javascript, css, and html needed for that widget gets saved in a database table cell, which is very unmaintainable. Is there are better way of allowing people to create widgets, yet make them maintainabl...

mysql_escape_string question

Hi, Just a quick query really, In my PHP file, I have variables coming from my HTML form, like so: $companyName = mysql_escape_string($_POST['compName']); $AddLine1 = mysql_escape_string($_POST['add']); $AddLine2 = mysql_escape_string($_POST['add1']); $AddLine3 = mysql_escape_string($_POST['add2']); Throughout this scrip...

How to make my MySQL databases available at all times? Some expert DB advice needed!

I've been doing a lot of research, reading on replication, etc but just not sure as to what mysql solution would work. This is what I'm looking at: when my mysql fails for some reason or there are certain queries that are taking really long to execute and locking some tables, I want the other insert/update/select queries to still func...

Intermittent MySQL error under high load: "Unknown MySQL server host 'XXXX'"

Hi, I have a MySQL server under heavy load (~960 queries per second) with ~ 400 clients continuously running queries against it. It is on a powerful machine (8 cores, Xeon, 3.3 GHz) and it looks like it can keep up with the load, no problem. Occasionally (once per week), all client processes will error at the same time with the message...

Stop MySQL Reusing AUTO_INCREMENT IDs

I have a table with an AUTO_INCREMENT primary key. If the last row in the table is deleted, the next-inserted row will take the same ID. Is there a way of getting MySQL to behave like t-SQL, and not reuse the ID? Then if the deleted row is erroneously referenced from something external to the database, no rows will be returned, highligh...

php script to log the raw data of POST

Hi, I am sending data using HTTP POST to my server. But in the server, I am not receiving the data. And somehow I don't have any way to check the data (or debug script) on client side. But on client side I am getting HTTP 200, means data is sent. Also I can see the connection and data sending was successful. However log in the server doe...

stored procedure mysql

I have a database call that i am not sure if i am doing it the most efficient way. Basically the call queries a table of events with zip codes and then joins a zip code database that gives the lat/lon of that events zip. Then it joins the logged in user to the query and that user has a lat/lon of upon logging in. So the whole query pu...

Get friends data in mysql

Hey, I'm using the following query to get the id and the username of a friend in mysql: SELECT DISTINCT members.id AS userid, members.gebruikersnaam AS username, members.pasfoto AS avatar, members.id AS link, FROM friends JOIN members ON friends.friend_out = me...

DB Error on create - Errno 150

When running the follow query i receive the error below, Im just trying to create this simple table with no FK or anything and still receive this error. What could be the cause? SQL query: CREATE TABLE `xauction`.`orders` ( `id` INT( 11 ) NOT NULL AUTO_INCREMENT , `type` VARCHAR( 1 ) NOT NULL , PRIMARY KEY ( `id` ) ) ENGINE = InnoDB; ...

.NET and MySql paramters, AddWithValue NULL variables, how to avoid to check for nulls

Let's say I have a MySql stored procedure that inserts a record with some nullable CHAR fields. In VB.NET if I don't check for Nothing (or Null in other languages), I get an exception from the db driver, so I write: command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("_name", if(name Is Nothing, "", name)...

Optimize sql query

Is it possable to optimize this query? SELECT count(locId) AS antal , locId FROM `geolitecity_block` WHERE (1835880985>= startIpNum AND 1835880985 <= endIpNum) OR (1836875969>= startIpNum AND 1836875969 <= endIpNum) OR (1836878754>= startIpNum AND 1836878754 <= endIpNum) ... ... OR (1843488110>= startIpNum AND 1843488110 ...

Backup all MySQL databases that a mysql user has access to

Hi, I need to backup all MySQL databases available to a partiular user and store them *databasename_date.sql* Is there any way to use mysqldump to dump all databases instead of telling it each database name it needs to backup? Thanks ...

Unable to connect to MySQL from PHP : "mysql_connect(): No such file or directory"

Hi, I'm not able connect to mysql via php. It produces [error] [client 127.0.0.1] PHP Warning: mysql_connect(): No such file or directory error message in apache error log when executing mysql_connect() function in PHP. Please find the warning messages when running php -m command: $ php -m | grep mysql PHP Warning: PHP Startup: Una...

MySQL - Select un-matching data in a one to many relationship

Hi All, First of all, this question is in regards to PHP and MySQL I have two database tables: The People table: person_id | field_1 | field_2 | other_fields... And the Notes table: note_id | person_id | created_timestamp | other_fields... The People table has a one to many relationship with the Notes table... E...

mysql query WHERE in one table but not the other

EDIT:This did it: SELECT DISTINCT profileid FROM profilesrelevation WHERE profileid NOT IN ( SELECT profileid FROM profiles ) I need to get the profileid values that exist in profilesrelevation table but not in profiles table profiles table have 8107 diffrent "profileid" values while profilesrelevation table has 8380 diffrent "...

MySQL trigger + notify a long-polling Apache/PHP connection

I know there are Comet server technologies that do this but I want to write something simple and home-grown. When a record is inserted into a MySQL table, I want it to somehow communicate this data to a series of long-polled Apache connections using PHP (or whatever). So multiple people are "listening" through their browser and the sec...

GROUP BY query not ordering as expected

I don't know why I can't make this query work the way I want, I hope you can enlighten me. SELECT e.id, e.num, e.name, s.name as s_name, t.num as t_num, e.show_id FROM s_episodes e LEFT JOIN shows s ON s.id = e.show_id LEFT JOIN s_seasons t ON t.id = e.season_id GROUP BY...