mysql

MySQL Create table with indexes error

Ok, so I am creating tables in MySQL with indexes and foreign keys. I use MySQL Workbench to create the tables and then have it forward engineer a SQL create script (I do better in a visual DB environment than just writing out the SQL code by hand right away). The problem is many times when I import the sql script into mysql, I get the ...

apache2 and mysqldump causing massive load average spike

I've got a problem that's causing me some headaches and I could use some ideas on how to investigate/fix it. Setup: I'm running a Rails app (Apache2 w/ Passenger) on server A (Rails.A) I'm running the master DB on a dedicated DB server B (DB.B) I'm running a slave DB on server A (DB.A) with standard MySQL replication (just for backup ...

Why did my MySQL Index Cardinality get Zeroed out?

I have an older web application that uses a MySQL Database (MYISAM). I noticed recently that the performance of the application was drastically reduced. After checking on my indexes, I noticed that the cardinality for all of them was reporting zero. I was able to fix this by doing an ANALYZE TABLE on each table. But I'm curious, what ...

Python MYSQL update statement

Hi I'm trying to get this Python MYSQL update statement correct(With Variables): cursor.execute ("UPDATE tblTableName SET Year=%s" % Year ", Month=%s" % Month ", Day=%s" % Day ", Hour=%s" % Hour ", Minute=%s" Minute "WHERE Server=%s " % ServerID) Any ideas where I'm going wrong? ...

MySQL "set unique_checks", "set foreign_key_checks" vs. "alter table disable keys"

We're having a problem where a mysqldump script is spending 90% of it's time populating a small handful of the tables it deals with. Eliminating FK's and indexes eliminates the speed problem, but is not an acceptable solution. The dump script DOES have: /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET ...

Multiple mysql INSERT statements in one query php.

Is this legal? $string1= "INSERT INTO....;"; $string1 .= "INSERT INTO....;"; $string1 .= "INSERT INTO....;"; mysql_query($string1) or die(mysql_error()); ...

How can improve myself in web development?

I am not fully experienced web developer.I am more likely stand alone application guy experienced lots of languages like c,c++,java,c#.I have lots of ideas for web projects wtih knowladge of php and mysql but when i start to develop i give up when i come to design stage.ıt really frustrating , do you have any suggestion for that? ...

SQL Data Modelling/Mapping Software for 200+ Tables

I'm looking for what I think is called a data modeling program to map out all of our SQL Server tables (200+) into a large, poster-size image. We've put all of our legacy application tables into SQL Server 2005 and my boss is looking for a snazzy way of viewing the data... basically something to say "this is what we maintain". I've see...

Python - MYSQL - Select leading zeros

Hi When using Python and doing a Select statement to MYSQL to select 09 from a column the zero gets dropped and only the 9 gets printed. Is there any way to pull all of the number i.e. including the leading zero? ...

How to extract data from a PDF and store in MySQL

I have a PDF document that contains data that I would like to extract and store in a MySQL database. Could anyone give me some guidance or perhaps sample PHP code? ...

What additional steps are necessary to restore a mysql database from the physical files?

Referring to this question: http://stackoverflow.com/questions/484750 I would: 1) Shut down source server 2) Grab copy of all files in $SourceServer/data/$TheCatalog 3) Create empty "TheCatalog" on target server 4) Stop target server 5) Drop copied files into $TargetServer/data/TheCatalog 6) Start target server 7) ??? 8) Profit! Wha...

How do I form an SQL query using DATE_SUB to check for yesterday's data OR if it's Monday, to check for Friday's data?

Here's my query: SELECT * FROM daily_records AND date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) I use this to generate a report of everything that happened yesterday. This works great, Tuesday-Friday. However, on Mondays, I want it to be able to search back to Friday (the previous business day, eg INTERVAL 3 DAY). Is there a way to do t...

Grails/Spring and MySql Replication. And a deadlock...

After installing Grails 1.1.1 and adding Replication (org.mysql.jdbc.ReplicationDriver) it appears that my BackgroundQueue is clogging up. Question 1: How do I determine "why" my BackgroundQueue is clogged? (I define Clogged as having all available thread stuck, and not accepting any new "work"). Question 2: What is the "defined way" ...

MySQL - Different UPDATE else INSERT statement

I am busy doing an UPDATE/INSERT request, but here is the crux: table PLAYERS { player_id game_id team_id position number } What is supposed to happen is the following: I test if there is an entry where player_id = '$player_id' AND game_id = '$game_id' AND team_id = '$team_id'. If there is, then the following ha...

Mysql set LIMIT to string

Hi, Say I define an alias 'count' in my Select Query and I want to limit the amount returned to count / 5 (or 20% of the table). How can I do this? Mysql doesn't seem to take anything but integers, not functions. ...

Order By Sum of Two Fields

Let's say I have a table with karma_up and karma_down. Everytime someone votes up karma_up gets incremented and everytime someone votes down karma_down gets incremented one as well. How can I pull these a selection of rows and have them ordered by the sum of these new values? ORDER BY (karma_up - karma_down) does not seem to work how ...

Any tips on planning a large database

I usually fly by the seat of my pants when building my databases. However, my new project is going to need quite a bit of planning. I never went to school for database development so I have no formal training on the planning process. Is there any good software, methodologies for planning these things out? ...

How can I load a mysqldump .sql file to a MySQL database in Windows Vista

I'm trying to move a MySQL database from a Linux machine to one running Windows Vista. (I know, that seems the wrong direction, never mind why I want to do this.) Using mysqldump, I've obtained an .sql file, which I have moved to the Windows machine. What next? If I were moving to another Linux machine, one could do this sort of thin...

Simulating MySQL's ORDER BY FIELD() in Postgresql

Hello all, Just trying out Postgresql for the first time, coming from MySQL. In our Rails application we have a couple of locations with SQL like so: SELECT * FROM `currency_codes` ORDER BY FIELD(code, 'GBP', 'EUR', 'BBD', 'AUD', 'CAD', 'USD') DESC, name ASC It didn't take long to discover that this is not supported/allowed in Postg...

How to ORDER BY a SUM() in MySQL?

I have a table: "ID name c_counts f_counts " and I want to order all the record by sum(c_counts+f_counts) but this doesn't work: SELECT * FROM table ORDER BY sum(c_counts+f_counts) LIMIET 20; ...