Date: 06/25/08 (MySQL Communtiy)    Keywords: mysql, sql

"mydate" is a DATETIME field. I need to search this field with DATE input and ran into this:

mysql> SELECT COUNT(*) FROM program WHERE mydate LIKE '2008-04-22%';
+-------------------+
| COUNT(*) |
+-------------------+
| 538 |
+-------------------+
1 row in set (0.08 sec)

mysql> SELECT COUNT(*) FROM program WHERE DATE(mydate) = '2008-04-22';
+-------------------+
| COUNT(*) |
+-------------------+
| 0 |
+-------------------+
1 row in set (0.06 sec)

Source: http://community.livejournal.com/mysql/129354.html

Date: 06/17/08 (MySQL Communtiy)    Keywords: mysql, database, sql, linux

Hello all. I'm so bored to kill this error ... can anybody help me?

I have one master and about 10 slave servers. Time to time one (absolutely random) slave server lost replication connection. When i type slave stop; slave start; i see "ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log"

When i look at /var/log/mysql.log, i see

[ERROR] Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.000759' (relay_log_pos 235)
080617 12:25:45 [ERROR] Could not find target log during relay log initialization

OK, "slave reset", drop databases , set MASTER etc, slave start - and replication does just fine. without any errors. Until the next time.

I add to my.cnf next lines:

max-relay-log-size=1M
max-binlog-size=2M
relay-log=/var/run/mysqld/mysqld-relay-bin

But without any effect: replication may work fine long time or break at next hour.

Question: how can set replication, that always work ? :)

Platform: Linux Fedora 6 & 7, mysql 5.0.45, no any errors about disk or memory. Size of replication tables - about 1-2Mb

Source: http://community.livejournal.com/mysql/129177.html

Date: 06/05/08 (MySQL Communtiy)    Keywords: mysql, sql

Two MySQL community members, Keith Murphy and Mark Schoonover collaborated to bring you a survey. Results will be published in the summer issue of the [free] MySQL magazine.

This is a very quick survey, and nicely done in my opinion.

Survey

MySQL Magazine

MySQL Magazine latest issue (spring 2008, issue 4)

Source: http://community.livejournal.com/mysql/128924.html

Date: 06/04/08 (MySQL Communtiy)    Keywords: mysql, database, sql

Is there a way to bind mysql users to a specific server IP?

I have two databases running from the same server. I need to ensure that database ABC only allows activity from server IP 123.000.000.111. A second database (XYZ) should only allow activity from server IP 123.000.000.222. Both IPs and databases are on the same box (multiple nics).


If I've explained this poorly please let me know so I can clarify.

Source: http://community.livejournal.com/mysql/128663.html

Date: 05/21/08 (MySQL Communtiy)    Keywords: mysql, sql, java, security, web

I'm wondering if anyone has any thoughts or pointers about this.

I am setting up a Java Web Start program that accesses a mySQL DB. The program was written a good while ago and was never meant to go outside the office. But now, of course, we want it to. :-)

The issue is...wait for it...security! mySQL, by default, does not have secure communications, although I am seeing that it can be set up to do SSL communications. Save for having to set up our mySQL server to handle that (Certificate administrators, etc.) and having to re-write sections of the Java program to handle the secure communications, that's all theoretically good.

However, I don't really need something that elaborate. Indeed, the data itself is public and does not need to be secured. What we are worried about is someone sniffing out the credentials to the mySQL DB. How can that be stopped?

In my research into this I came across an invokation flag to mySQL called --compress.

The Basic SSL Concepts section of the mySQL 5.0 Reference Manual says, "To improve security a little, you can compress client/server traffic by using the --compress option when invoking client programs. However, this does not foil a determined attacker."

This sounds perfect for me if such an invokation compresses (or otherwise hides) the credentials being used to access the DB? Does anyone know if it does?

So, in a larger sense, I guess I'm asking how you folks secure access to your mySQL DBs in any sort of public setting? Am I forced to set up the SSL solution and rewrite the Java program, or is there a more non-invasive way of doing this?

Thanks!

Source: http://community.livejournal.com/mysql/128489.html

Date: 04/28/08 (MySQL Communtiy)    Keywords: mysql, database, sql, web

I'm working on setting a mirror copy of some websites. The part I'm not sure about the best way to handle things is the MySQL database (I have rsync for the files)

Currently, a script would run from the mirror to collect the MySQL data from the site with the live data (using SSH to run a mysqldump command, then SCP the dump file to itself) and overwrite the data on it's local copy.

I'm just wondering if there is something better (and no, I don't have root access at all. I only have access to some databases VIA the MySQL client)

Source: http://community.livejournal.com/mysql/128050.html

Date: 04/28/08 (MySQL Communtiy)    Keywords: mysql, database, sql

Greetings,

We will be upgrading to MySQL 5.0 (either 5.0.58 or 5.0.60 when it becomes available) 4.1 this summer and we are considering using DRDB. My concerns, as the dba are:

1. Failover time. We are currently running a single, monolithic InnoDB database that has grown to 342G. When I shutdown the server it has taken as long as 40 minutes for InnoDB to flush all data to disk. My concern is that with DRDB, if we have a hardware failure on the DRDB master, with DRDB block-level replication, the DRDB slave's datadir and logdir files will be in the same state as the master when it failed and startup of MySQL could take a long time. Currently we are handling failover with replication and scripts to redirect who the master is.

2. Table Maintenance. Some of our tables are quite large. One is at 90GB. To rebuild indexes and defrag tables, I would run ALTER TABLE blah ENGINE=InnoDB on the slave and then failover to it. With DRDB, if I understand it correctly, this would not be possible.

My concerns are that due to the size of our database DRDB may not be the optimal choice. I would appreciate anybody's comments on this.

Source: http://community.livejournal.com/mysql/127896.html

Date: 04/15/08 (MySQL Communtiy)    Keywords: mysql, sql

   I'm getting ready to upgrade one of our core DB's to 5.0.54 from 5.022, but I figure I am still a week away from that time and in the interim, the machine in question is somewhat spooking/scaring me.   It's been setup to use no more then 6GB of memory and verified to be this way by a couple different "tuning" scripts... yet top is showing the process taking 5.7GB of resident ram and another 2.3GB of swap.  So far there hasn't been any performance issues besides it killing the machine this last sunday from a complete lack of free memory (resident or swap).    

   Has anyone else experienced a similar situation where a MySQL setup using more memory then its been allocated or am I missing something here?     I know one of the BIG problems we have is that in development I had planned to use triggers to handle keeping a series of "materialized" view tables syncronized.  After Insert/Update abbreviated copies of records would be inserted/updated to three sub-tables ( pending 5.1 going stable and the availability of table partitioning)... so in the interim there is a cronjob of doom that updates the other tables based on a record time-stamp field.  Also another department used to have a 2.3GB pdf InnoDB table, but I had them downgrade that to a MyISAM table as they really didn't need transactions for what they were using it for. 



skip-bdb

#old_passwords=1
skip-locking #DJW - Why/who the hell set this?  This is set by default last I checked.

key_buffer = 64M #20080325 - Benchmark for 1 month
max_allowed_packet = 64M
table_cache = 2048
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M

thread_cache_size = 8
thread_concurrency = 8

#DJW - changed from 32
query_cache_size = 64M #Decrement to 48MB by 20080416
query_cache_limit = 3M

tmp_table_size=64M
max_heap_table_size=64M

back_log = 100
max_connect_errors = 10000
join_buffer_size=1M
open-files = 20000

interactive_timeout = 600
wait_timeout = 600

#DJW - 20080324 from 100
#DJW - 20080413 FROM 210 - DO NOT TAKE THIS HIGHER, RHEL5 doesn't appear to handle out of memory events ( normally would kill processes) gracefully
max_connections=180

################################################################################################
####INNODB flags
################################################################################################
innodb_buffer_pool_size = 4500MB
innodb_log_file_size = 500MB
innodb_log_buffer_size = 32MB
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=12
innodb_flush_method=O_DIRECT
#DJW - todo research this for usefulness
#transaction-isolation=READ-COMITTED
innodb_file_per_table 

         

     

Source: http://community.livejournal.com/mysql/127486.html

Date: 03/10/09 (PHP Community)    Keywords: php, mysql, database, sql, web

(x-posted to php_dev)

Hey guys,

I'm coding an image gallery to use on my PHP site and need a bit of help with a couple of features I want to code into it. I was hoping someone here could either give me some pointers or send me to a website that will help with either explainations or code snippets :)

1. Firstly, thumbnails. I have created an admin panel where I upload the images and can set various things like the category, title etc, and all that is stored in a MySql database, and the file uploaded to a general directory. But i'd also like to create a thumbnail of the image as it uploads. I've found several scripts that make thumbnails from images already in a directory, but i'd like it to happen as I upload the image. Is that possible? Without having to use some all-in-one image gallery script heh. I'd also need it to resize the image proportionally so that images aren't just squashed into a 100x100 square shape.

2. Secondly, I'd like to "tag" images like with LJ entries, so that on the front end when a picture is being viewed, the list of tags is shown and can be clicked on to show other image thumbnails with the same tag. But i'm not really sure how to go about this... I was just going to have a table field called img_tags and store them all in that per image, but then is that efficient? searching for matches within a db field? any tips or suggestions?

3. oh and as an extra thought, how can i then take those tags and make a tag cloud maybe?

All and any help would be most appreciated! :)

paul
x

Source: http://community.livejournal.com/php/659077.html

Date: 02/11/09 (PHP Community)    Keywords: php, mysql, sql

I’m using PHP and MySQL to create a dynamic page that delivers files to users based on group settings.

The user is given a variety of files to download, based on their unique settings.
I want to track what files the user selects to download.

For the life of me…I can’t figure out how to do that without creating a button for each file.
(The button triggers an entry into the appropriate table and starts the download.)

Is there a more elegant solution??
(I’d rather do it within a hyperlink)

Appreciate any help!

Source: http://community.livejournal.com/php/657215.html

Date: 01/23/09 (PHP Community)    Keywords: php, mysql, sql

I'm pretty sure there's a term for my title ("Listing alphabetically omitting "the" or "a"") but I'm not sure what it is.

But that is essentially what I need...

I use MySQL to store entries with titles. In the PHP I can populate an alphabetical list, but I'd like to go a step up and try and omit the "the" and "a" etc. So something called: "A Pen" would appear under P.

How would I do this?

Am I correct in thinking this will require lots of brain power? :P

Thanks!

Source: http://community.livejournal.com/php/655446.html

Date: 01/15/09 (PHP Community)    Keywords: php, mysql, asp, sql

Hello all!

I hope it is a right community for my question.

I have to setup a new MediaWiki site using IIS.


The server does not have PHP and MySQL installed, so I must install it first.

The step 1 was to install PHP.
I've done it as described here:

http://learn.iis.net/page.aspx/247/using-fastcgi-to-host-php-applications-on-iis-60/

In this description the file php-cgi.exe is mentioned.

But I've got only the file php.exe after installation.

What could be my mistake?

Source: http://community.livejournal.com/php/653630.html

Date: 01/12/09 (PHP Community)    Keywords: mysql, database, sql

Hi everyone! Time for another one of my dumb questions!

The short version: Why do my try/catch blocks not seem to catch properly?



I'm using MySQL to update a recipe database I'm creating (and have been creating for just about forever. It's one of those projects that you can only pick up in your very limited spare time) and just for grins, I wanted to test and see if the try/catch blocks I set up to catch MySQL errors in host connection, database connection, and query running worked.

So I changed the password to see if they would work. Let me tell you, it was a big pile of fail and MySQL errors.

In theory, if the connection to the host is unsuccessful, it should say "Problem connecting to the host" and give the info stored in mysql_error(). But it doesn't. It just plugs along and fails like the try/catch blocks weren't even there. And I'm not quite sure why.

So, what have I done to Eff up my Try/catch blocks?

Example code...

public function create_recipe()
{
  try
  {
    $create_connect = $this->_db_connect();
    try
    {
      $this->recipe_id = $this->_set_id($create_connect, "recipes");

      //Selects the database from the server.
      $db_select = mysql_select_db("recipes", $create_connect);
      try
      {
        //Query to create the recipe in the database.
        $query = "INSERT INTO recipes ";
        $query .= "VALUES(" . $this->recipe_id . ", '" . 
                  $this->recipe_info["recipeName"] . "', ";
        $query .= "'" . $this->recipe_info["numServings"] . "', '" . 
                  $this->recipe_info["prepTime"] . "', ";
        $query .= "'" . $this->recipe_info["cookTime"] . "', NULL, '" . 
                  $this->recipe_info["origAuthor"] . "', ";
        $query .= "'" . $this->recipe_info["origLocation"] . "', '" . 
                  $this->recipe_info["notes"] . "', NULL);";
        //Timestamp is automatically created in the database upon insert.
        
        //Runs $query on the database.  Stops the script if there is an error.
        $result = mysql_query($query, $create_connect);
        mysql_close($create_connect);
      } //end try $eee
      catch (Exception $eee)
      {
        echo "Problem running the query. " . mysql_error($create_connect);
        echo "

";
        mysql_close($create_connect);
      } //end catch $eee
    } //end try $ee
    catch (Exception $ee)
    {
      //Verifies that the database/table connection was successful.  If not, it kills the 
      //application.
      echo "Problem selecting the table. " . mysql_error($create_connect);
      echo "

";
      mysql_close($create_connect);
    } //end catch $ee
  } //end try $e
  catch (Exception $e)
  {
    //Verifies that the connection to the host was successful.  If not, it kills the 
    //application.
    echo "Problem connecting to the host. " . mysql_error();
    echo "

";
  } //end catch $e
} //end create_recipe()

private function _db_connect()
{
  $hostname = "********";
  $username = "********";
  $password = "********";
  return mysql_connect($hostname, $username, $password);
} //end db_connect()

Source: http://community.livejournal.com/php/652844.html

Date: 01/12/09 (PHP Community)    Keywords: php, mysql, css, html, technology, sql, web

I have had an html-only movie review website ( http://www.thoughtsonfilm.com/ ) for several years now and over time have grown quite tired of manually uploading articles, formatting them and the main page and manually indexing everything. I set my New Year's resolution to get the site updated to current technology both for ease of use and to add additional functionality. The problem is, I know next to nothing about MySQL, php and css. And that poses a problem.

I have installed the ArticleMS application and think that it can adequately handle the back-end of what I'm trying to do, but I don't know enough to make it functional nor how to set up the front end to look how I want.

So I pose the following question: Is there anyone out in internetland who can aid me in this task? The site isn't a big money maker, so I'm not going to be able to pay very much at all for the help, but if there's someone wishing to build their portfolio or who has an interest in reviewing movies, perhaps a creative compensation arrangement can be worked out.

Source: http://community.livejournal.com/php/652635.html

Date: 03/10/09 (PHP Development)    Keywords: php, mysql, database, sql, web

(x-posted to php)

Hey guys,

I'm coding an image gallery to use on my PHP site and need a bit of help with a couple of features I want to code into it. I was hoping someone here could either give me some pointers or send me to a website that will help with either explainations or code snippets :)

1. Firstly, thumbnails. I have created an admin panel where I upload the images and can set various things like the category, title etc, and all that is stored in a MySql database, and the file uploaded to a general directory. But i'd also like to create a thumbnail of the image as it uploads. I've found several scripts that make thumbnails from images already in a directory, but i'd like it to happen as I upload the image. Is that possible? Without having to use some all-in-one image gallery script heh. I'd also need it to resize the image proportionally so that images aren't just squashed into a 100x100 square shape.

2. Secondly, I'd like to "tag" images like with LJ entries, so that on the front end when a picture is being viewed, the list of tags is shown and can be clicked on to show other image thumbnails with the same tag. But i'm not really sure how to go about this... I was just going to have a table field called img_tags and store them all in that per image, but then is that efficient? searching for matches within a db field? any tips or suggestions?

3. oh and as an extra thought, how can i then take those tags and make a tag cloud maybe?

All and any help would be most appreciated! :)

paul
x

Source: http://community.livejournal.com/php_dev/81475.html

Date: 05/13/08 (PHP Development)    Keywords: php, mysql, sql, web, google

Leading web developers will gather to participate in the 7th annual international conference on PHP and web development.



- Marcus Börger "Introduction to object oriented PHP" (Google)

- Dmitry Stogov "PHP 5.3 & Zend Engine Inside " (Zend)

- Sveta Smirnova "MySQL Essential Questions" (MySQL)

- Alexander Rubin "Geo Distance Search with MySQL"

http://phpconf.ru/

Source: http://community.livejournal.com/php_dev/80478.html

Date: 08/01/10 (Apache)    Keywords: mysql, database, sql, jsp, web, linux

I'm involved in migrating an existing, functional JSP/Struts2 app from Windows to Linux.

The former Windows environment was a Tomcat/Struts/Eclipse setup. The new environment is a standalone installation of Tomcat (which is already configured and serving several other applications).

The app in question, "MyJSPWebsite", was copied to the Linux/Tomcat webapps folder and correct permissions assigned. The database (mysql) was also copied over with user permissions established.

The site now opens, but none of the struts enabled content is functioning. For example, a drop-down list of data is not being populated. I'm not seeing any error SQL messages in catalina.out, and the username/password & query work fine from command line.

Are there separate, core struts files that have to be installed outside of those already included in the webapps/MyJSPWebsite folder?

Source: http://apache.livejournal.com/44268.html

Date: 10/15/10 (MySQL Communtiy)    Keywords: mysql, database, sql

A database application I've written uses a table with around 600,000 rows. Each row has a text field 500-5000 characters long. I periodically need to find all the rows containing a particular phone number, name, or address, ie.'123-4567', 'john smith', '1950 Main St N'

I'm doing this using
SELECT * FROM `tb_archive` WHERE `text` LIKE '%john smith%' ORDER BY `date` DESC

The problem is that it is too slow. Most searches take 30-60 seconds. If multiple searches are done the server response begins to slow to a crawl for other users.

I've looked at mysql built in full-text indexing - but I'm not sure if it can work since I only need exact matches, don't care about relavence, and often search for numbers and short words.

Any suggestions as to how I can do this more efficiently?

Source: http://mysql.livejournal.com/138390.html

Date: 10/09/09 (MySQL Communtiy)    Keywords: mysql, database, sql

Anyone know how to store a MySQL database schema (and maybe data from some tables hopefully based on table structure) into SVN?

Source: http://mysql.livejournal.com/137943.html

Date: 09/22/09 (MySQL Communtiy)    Keywords: mysql, database, sql

I found an instruction set that said to list the first, and last name of all employees that had neither 'SON' nor 'DAUGHTER' listed in their dependency files. I came up with a query that gave me the results I wanted by assuming the database only takes SPOUSE, SON, and DAUGHTER, but assuming has always caused me trouble, so I would like to know: Is there any better way I could have gone about approaching this so it's not going by a general assumption of dep_relation = 1, and so forth?

mysql> SELECT * FROM dependent;
+-------------+------------+------------+-------------------+------------------+
| dep_emp_ssn | dep_name   | dep_gender | dep_date_of_birth | dep_relationship |
+-------------+------------+------------+-------------------+------------------+
| 999444444   | Jo Ellen   | F          | 1996-04-05        | DAUGHTER         | 
| 999444444   | Andrew     | M          | 1998-10-25        | SON              | 
| 999444444   | Susan      | F          | 1975-05-03        | SPOUSE           | 
| 999555555   | Allen      | M          | 1968-02-29        | SPOUSE           | 
| 999111111   | Jeffery    | M          | 1978-01-01        | SON              | 
| 999111111   | Deanna     | F          | 1978-12-31        | DAUGHTER         | 
| 999111111   | Mary Ellen | F          | 1957-05-05        | SPOUSE           | 
+-------------+------------+------------+-------------------+------------------+


mysql> SELECT * FROM employee;
+-----------+---------------+----------------+
| emp_ssn   | emp_last_name | emp_first_name |
+-----------+---------------+----------------+
| 999666666 | Bordoloi      | Bijoy          | 
| 999555555 | Joyner        | Suzanne        | 
| 999444444 | Zhu           | Waiman         | 
| 999887777 | Markis        | Marcia         | 
| 999222222 | Amin          | Hyder          | 
| 999111111 | Bock          | Douglas        | 
| 999333333 | Joshi         | Dinesh         | 
| 999888888 | Prescott      | Sherri         | 
+-----------+---------------+----------------+



What I generated was:

SELECT emp_first_name, emp_last_name 
FROM 
	(
	SELECT emp_first_name, emp_last_name,
	dep_relationship, COUNT(dep_relationship) AS count 
	FROM dependent 
	JOIN employee ON dep_emp_ssn = emp_ssn 
	GROUP BY emp_ssn
	) tempTable 
WHERE tempTable.count = 1 
AND tempTable.dep_relationship != 'SON' 
AND tempTable.dep_relationship != 'DAUGHTER';

To get:

+----------------+---------------+
| emp_first_name | emp_last_name |
+----------------+---------------+
| Suzanne        | Joyner        | 
+----------------+---------------+

Source: http://mysql.livejournal.com/137636.html