Getting Statistic From Detail Table Into Query On Master
I have two tables: Students, and Attendance_Dates.
Students:
Student_ID [PK]
Last_Name
First_Name
Attendance_Dates:
ID [PK]
Student_ID
Attendance_Date
Attendance_Dates is a detail table containing all of the dates each student was in class.
I want to write a SELECT statement that gives me a list of ALL students by Last_Name, First_Name PLUS an additional column that contains the LAST date they were in class.
I tried this:
SELECT Last_Name, First_Name,
MAX(SELECT Attendance_Date FROM Attendance_Dates WHERE Students.Student_ID = Attendance_Dates.Student_ID) AS Latest_Date;
Didn't work. Synatax error.
View Complete Forum Thread with Replies
Related Forum Messages:
Master Master Replication, Truncate Table Fails, Version 5.0.22
I have set up a master master replication link between two databases, prototyping an idea. Anyhow, adding records and deleting works, both get updated, creating and dropping tables works, but truncating tables doesn't work. My main questions are, why doesn't truncate work, and if it doesn't work, what else doesn't work across a master-master replication link?
View Replies !
17 Separate Statistic Columns Or A Separate Table And A Pivot Table?
Okay, I have a table that lists items. These items can possibly have different values for 17 statistic fields, many of which will be blank for each item. do I setup all 17 of these as fields in the item table, or should I do as follows: Items: id name description stats: id statname item2stats: item_id stat_id stat_value What are the pros and cons of each? What will be easier to search through? I would want to retrieve these items along with all their stats, and all the stat fields could be possible search fields in an advanced search.
View Replies !
Error Detail
I have 2 tables "a" & "b". I have a foreign key field defined in "b" that points to a parent field in table "a". Foreign key constraint is configured to restrict deletions in "a" when a child record exists in "b". This works as expected and generates the following error if a deletion is attempted that violates the fk constraint: "Cannot delete or update a parent row: a foreign key constraint fails" My question: is there a way to have MySQL report the error in further detail (i.e. which record in table "a" caused the error)? I can determine this manually via selects and joins, but in my particular situation it would be more convienient to have the details reported in the error.
View Replies !
Inserting Multiple Items (# Unknown) Into A Table Using Primary Id From Master Table
In my database I have a master table for products using a auto-num id column. One product may have several purchase options (items) which have to be entered into another table. To enter a new product and items I use a form that gathers the information for the master product and one item. This information is then inserted into the database. For the items I use mysql_insert_id() to get the id into the items table. My challenge is getting this same id for additional items. Once the user has input the initial product and first item they hit a submit button to call a form for adding any additional items, and this is repeated until all items for that product are entered. The new items are inserted into the database okay, but always with an id of 0. The mysql_insert_id() no longer works. Is there a way to pass this id from the previous insertion to the new one by passing a variable? or echoing input type"hidden"? Or even better yet, Im wondering if I can ask the user at the time of entering information for the product how many items that particular product has and then bring up that many form fields to enter multiple items? Can this be done using something like an incremented loop to call form fields?
View Replies !
Backing Up A Circular Master-master Replication Setup
I have a running circular master - master replication running. I want to run a mysqldump on one of the servers but am worried what the impact of running a mysqldump will be on the replication. Will the transactions generated by the mysqldump get put into the binary log file? I see the option --delete-master-logs for mysqldump and am assuming that it deletes the previously inserted log items from the binary log file. If there is someone that can please just give me a little more clarification on how the --delete-master-logs option works.
View Replies !
MySQL And Statistic
I have to create a website that will use mySQL database for storing input data. I can using queries to obtain data from this database. I will use PHP for enter data or obtain data. My question is how I can mySQL database for analysing (chi squares, t-tests....) ?, how can I do that.
View Replies !
Optimizing Statistic Generation
I use a select statement to gather statistics for various time periods and would like to optimize it, if possible....this is quickly typed out for general knowledge @lengths = 1,2,3,4,5,6,7 foreach length select count(*) from (select * from actions WHERE datediff(current_timestamp,date) <= $length ORDER BY date DESC) AS actions GROUP BY ip; The problem is that if I make the @lenghts about 5 times longer, a real slow down is noticed. Right now, this table has about 5,000 rows and my site is very small. Basically, every page view adds a row to the table. Since I am VERY new to mysql (this being my first site ever with no true course on sql), is this a good idea or will I mess things up? Also, by limiting to date < the length, does this speed it up a lot because the majority of the rows might not apply once it gets a lot larger? After a year, only 7 out of 365 days will apply...
View Replies !
Select Statistic From 3 Tables
I need some help in my statistic,this is my bd tables groups +------+-------+ | descr | image | +------+-------+ users +------+ | groups | +------+ inquiry of 2 tables PHP SELECT descr, image, COUNT(users.groups) as num FROM groups LEFT JOIN users ON users.groups = groups.id GROUP BY descr ORDER BY num ; statistic example ---------+-----------------------+ | Group | Users in group | +--------+-----------------------+ | FunTV | 5 | +--------+-----------------------+ | M-Zone | 15 | +--------+-----------------------+ | Bt-Mov | 3 | +--------+-----------------------+ And now i want add new table torrents +------+ | grupa | +------+ That the statistics would be those +--------+-----------------------+-------------------+ | Group | Users in group | Torrents | +--------+-----------------------+-------------------+ | FunTV | 5 | 9 | +--------+-----------------------+-------------------+ | M-Zone | 15 | 18 | +--------+-----------------------+-------------------+ | Bt-Mov | 3 | 6 | +--------+-----------------------+-------------------+ Question in how me to make inquiry of 3 tables (users,groups,torrents) ? this PHP SELECT descr, image, COUNT(users.groups) as num FROM groups LEFT JOIN users ON users.groups = groups.id GROUP BY descr ORDER BY num ; + this PHP SELECT descr, image, COUNT(torrents.grupa) as grupa FROM groups LEFT JOIN torrents ON torrents.grupa = groups.id GROUP BY descr ORDER BY num ;
View Replies !
Temporary Table Or Append Master Table
I am using PHP to interface to MySQL. After PHP provides the user inputted data, MySQL will perform several queries using both the user data as well as several other non-user specific tables, and return the resultant values which PHP will return to the user. Multiple users will access the system simultaneously. I know of two possible solutions, but don’t know which is better. I can either create a table called T_USER_DATA (there will actually be 6 tables, some with one row per user and others with multiple rows) with a column for C_USER_ID, and additional columns for the user’s data. The user data rows will be added, and then filtered based on the USER_ID. Or, I can create a temporary table (maybe using a memory engine for faster speed?) specific for a given user on the fly when PHP passes the data. The queries are performed, data returned to PHP, and then the temporary table is deleted when the connection to MySQL is closed.
View Replies !
Query For Month Data Based On Occurance Against Master List
I have a query running nicely. Now I'm trying to expand it. It pulls the data from a table based on matching the id with another table and part of the grouping is by month and 2 other criteria. Now I'm trying to get the data pulled by month to pull only the info where the id matches the master file AND the FRANID's pulled are the same for each month. I'm trying to get a comparisson across times, but not every month has all FRANID's, depending on when they were entered and I ony want data that exists with matches over set months, starting for now with 12, perhaps also for 6 and 3 next time. I've simplified what I'm doing above, but it is detailed below.....
View Replies !
Master / Master Asynchronous Replication
Currently we have a web based application that is mostly reads (4:1 r/w). It is using a single MySQL database server. Is there any way to have two database servers in a master/master configuration such that writes to either database server are replicated to eachother. Basically even though we have a 4:1 ration of read/write the writes happen often enought that when the database goes down the app stops working. I know how to get this working in Oracle (insert big laugh here) but Oracle is cost prohibitive. Any pointers?
View Replies !
Master/Master Replication Problems
I was wondering whether someone could help me. I have inherited set of replicated servers from a guy I used to work with, basic set up is: Master1/Slave2 - Server ID 1 Master2/Slave1 - Server ID 2 When I looked at the logs it looks as though Server ID 2 is well out of step. Having looked at the slave status it appears that that Slave_SQL_Running state is set to No and a particular piece of SQL is causing the problem. Having ran the SQL on the particular server it has now problem at all. Now my understanding of this type of set up is that Server ID 1 writes directly to Server ID 2, then Server ID 1 is in turn replicated to. But for the life in me, I cannot understand why this piece of SQL is causing the problem. Can anyone offer me any ideas on how I can best get to the bottom of this, I'm really struggling with this. There was me thinking replication just worked! Is there an ability to get MySQL to log all queries it is running, so I can get to the bottom of all queries currently being executed?
View Replies !
Two Table Query: Grab Rows From One Table Even If No Related Row In Other Table
PHP $gettray = mysql_query("SELECT trailers.title, trailers.link, trailers.movie, movie.title AS mtitle FROM trailers,movie WHERE trailers.movie=movie.word ORDER BY trailerid DESC LIMIT 6",$connm); It works great, but there is one problem. It will not grab any rows from the 'trailers' table if a corresponding movie row does not exist in the 'movies' table. I want it to pull ALL rows from the 'trailers' table, even if the corresponding row in the 'movies' table does not exist yet. If the row does not exist in 'movies', the program than uses the entire trailer title like so PHP if($ttray['mtitle']) { $newttitle = explode("-",$ttray['title']); $newttitle = array_reverse($newttitle); $ttitle = $newttitle[0]; $ttitle = $ttray['mtitle'] ."- ". $ttitle; } else { $ttitle = $ttray['title']; } Thanks Ryan
View Replies !
Relay-bin On Master?
I am getting relay-bin logs on my master server. They are all empty except for one line that reads: þbin I have no slave settings in my my.cnf file. Any idea where these are coming from and how to turn them off?
View Replies !
Two Master Servers
I am using two master servers that are synchronized with each other so that if one of them gets disconnected, the other one could take over and no data is lost. Now my problem is doing backup on the servers. Say I am doing backup on Master 1 and after backup, I will delete the data. So, since Master 2 is synchronized with Master 1, will the deleted data be written back to Master 1? My batch file is something like this. echo Executing MYSQL BACKUP mysqldump -uroot -proot --result-file=%SAVPATH%%FILENAME%".sql" database1 echo Executing MYSQL DELETE mysql -uroot -proot datalog < "C:Program FilesMySQLMySQL Server 5.0inscript.sql" Script.sql will call the command "DELETE from table1" for me.
View Replies !
Clustering And Master Down
I want to create replication databases with multiple servers, but i need more informations about consequences when the MASTER database crash : what happens ? And, if one slave crash : what happens ? Is it like RAID 5, mysql just work in degraded mode and continue to work, or is it fatal ? Can we compare the replication system by MySQL with the Grid Computing of Oracle ? I don't think MySQL offers a clustering mode for calculating and searching rows, am i wrong ?
View Replies !
Change Master
My query is that is their any possibility that i can change running slave as a master server. I want to switch running slave as a master.
View Replies !
Replication From Master To Slave
I'm having quite a fuss with my two MySQL DB servers lately. I'm trying to establish replication between a MySQL 4.0.20 server (master) and a MySQL 4.1.11 server (slave). From what I read, replication from a 4.0.x server -> 4.1.x server will work, just not the other way around. However, that's not really my problem. After unsuccessfully completing the configuration myself, I used ....
View Replies !
Master-connect-retry
I installed MySQL 4.0.14 on a new Linux machine unde the form of two servers: one master and one slave. I met many unsynchronized events so it was appears conflicts from this late of the changes from master to slave. I put the master-connect-retry to 2. Now it seems to work clearly but I cannot see the master-connect-retry variable with "show variables" command. Is this bad? Shall I have another unsynchronized events in the future between master and slave? I manually repaired each conflict or I did RESET MASTER/SLAVE to escape from the errors' labirint.
View Replies !
MySQL Master/Slave
In the case of 2 3.23.X series mysql databases in a master/slave replication setup (myiasm tables), if a FLUSH TABLES WITH READ LOCK is put on the slave, does the master effectively lock too because it is waiting for the binlog update(s) to be confirmed from the slave, or does the master merrily carry on not caring in the least that the slave tables are locked?
View Replies !
Dual-master Replication
I would like to set up a dual-master replication setup for my MySQL database (running on RedHat 9). Could someone please point me to appropriate website that details this setup process and management as well (i.e. what should I do if one of the masters goes down?). Also, are there any solutions that allow auto-increment fields in this kind of setup?
View Replies !
Master/Slave Restoration
I have 2 servers (A and B) setup as Master and Slave respectively. Upon A's failure, all client's request will be directed to B. How can I set A to become master again after its been restored from its failure? In chapter 4.10 of mysql docs, it mentioned (vaguely) that A will become B's slave to sync all operations that had happened after the failure. Finally, upon full sync switch A to become Master again and B to slave. How can I setup A to be a slave of B? The problem I faced is that I can't find out what the bin log and position is from B since "show master status" is always empty. I guess B still thinks its a slave.
View Replies !
Replication :: Run RESET MASTER
If I run RESET MASTER on the primary MySQL server (the master) will it wait till the slaves are up to date before deleting the bin files or do you think my slaves will get screwed up?
View Replies !
Master Slave Replication
I am setting up a Master/Slave system so that the data will always be backup up safely when any changes are done. Now - I am not sure to what extent the replication extends to. I know it is fine for Data but what about these scenario's 1. Master table structure changes - are these replicated 2. Trigger/Stored Procedure added or changes - are these replicated 3. View or exisiting view creates/modified - are these replicated.
View Replies !
Master Child Reporting
Need to create a query that will pull a status field from a child table based on two seperate fields. One is date and the other is time. Can not use datetime field due to restictions in other legacy application. Want to select single related record with the highest date and time.
View Replies !
Master Servers Merge
I want to make query to merge table1 from master server A and table2 from master server B (ex: select * from table1, table2) because my site becomes heavy traffic and database gets bigger. It means I need to separate tables and create new database on other master server B. Do you know how to make "merge" from master server A and B?
View Replies !
Show Master Logs
When would SHOW MASTER LOGS be used? I've had mysql hang 4 times this week and we've done all we can to fix it - but it seems that what we have done has only reduced the backlog of queries that occur when it hangs - now it hung again this morning and the only thing of note in the mysqladmin process list was SHOW MASTER LOGS - I googled it and there seemed to be a few cases of crash related bugs with this particular command - now I know I didnt issue the command so what would have done
View Replies !
Different Errors On Master And Slave.
Replication stopped on slave servers when I executed the below query, and this query failed on master. mysql> UPDATE members set col1 = 1000, col2 = 10 where type = 2; ERROR 1030 (HY000): Got error 127 from storage engine Here is the error message on when I do SHOW SLAVE STATUSG Last_Errno: 0 Last_Error: Query caused different errors on master and slave. Error on master: 'Got error %d from storage engine' (1030), Error on slave: 'no error' (0). Default database: 'subscribers'. Query: 'UPDATE members set col1 = 1000, col2 = 10 where type = 2' members table contains 1485112 rows.
View Replies !
Multi Table Query, Grab More Than Single Row From Secondary Table
I have a query like so: PHP Code: $selectt = mysql_query("SELECT trailers.trailer_id, trailers.trailer_title, trailers.description, trailers.file_url, trailers.runtime, trailers.views, trailers.rating, trailers.tt, trailers.keyw, trailers.date, tags.snub, films.film_title, films.studio, films.keyw, films.image FROM films,tags,trailers WHERE trailers.trailer_id=tags.trailer_id AND trailers.film_id=films.film_id ORDER BY trailers.trailer_id DESC LIMIT 8",$dbh); I know this is pretty sloppy, I have multiple 'tags' per trailer ID, and I was hoping to grab all of them in a single query; maybe into an array? So that it shows the row, and then all the tags that belong to it. Possible?
View Replies !
Replication Master & Slave On The Same Machine
I have set up a master and a slave according to the manual. The master runs 4.0.12 and the slave runs 4.0.14, they are both actually on the same machine (it's a long story, but it's what I need). I did LOAD DATA FROM MASTER on the slave which completed successfully, and then I tried SLAVE START: ERROR 1200: The server is not configured as slave, fix in config file or with CHANGE MASTER TO but.. it is! on the slave: Code:
View Replies !
Master/slave Replication Error
I have set up master/slave replication between 2 servers (1 master, 1 slave). However, when I change a table on the master (e.g. by inserting a row), the same table on the slave is not updated. The strange thing is that if I enter SHOW SLAVE STATUS on the client, it gives me the same offset in the binary log on the master as the master gives when issuing SHOW MASTER STATUS (both entered after the update). Code:
View Replies !
Replication Master Crashed After 6 Hours
I recently setup replication, the master and slaves appears to work fine initially. After about 6 hours, the master mysqld process will start to consume 99% CPU and the load (avg. load per minute) will go to 3, 4, 6, 15, 24, 56, 312... in a matter of seconds! The master then stops responding and needs a reboot. This problem is reproducible, I have tested a few times. This is a screenshot of "top" command just before the master died: http://choonkeng.hopto.org/temp/replication-hang.gif There are plenty of memory and no disk swapping when the master goes 99%. Error and slow logs don't show any useful information.
View Replies !
Master Replication Automatic Failover
I am having one master and three slaves. If my master fails suddenly then one of my slave has to become master automatically on any platform (windows/linux). Can you please suggest the process to do this. Note: Manually i know how to do this task, but i need to be do this automatic.
View Replies !
PROBLEM WITH PURGE MASTER LOGS
I have a Server Linux with mysql 4.0.13 version with qmail server. I run the command purge master logs but he return a message of error i/o. The table "relay" on db vpopmail is corrupt and the command repair or restore not function.
View Replies !
Multi-Master Cross OS Replication
I normally run two Linux MySQL servers in multi-master replication and it has worked fine. Now I have had one of the machines die on me. The problem is that I want to now have that same replication configuration between my Linux and Windows machine which both run MySQL. The configuration on the linux machine I don't really have trouble with at all, but editing the my.ini file on windows to setup the master host causes the service to fail to start. So my question is what is the appropriate configuration of my.ini on windows to facilitate multi-master replication? Many hours of googling produced limited results/tutorials, none of which were helpful (most of which were for Linux which again I am familiar with already).
View Replies !
Master List Of Select Rows
I am creating a database as part of a service record for items. When an item is serviced, an entry is added to the database with the item serial number and some other info. That all works great. I also want to create a master list of all items. If I use the old "SELECT * FROM database WHERE 1" query, it will display all the rows in the database. Is there any way to retrieve the last row for each item number, instead of displaying all rows for all items? It seems I have worked myself into a corner with this table setup. If it can't be accomplished the way I have it set up, is there a better way to structure the table? I thought about creating another field "type" and have it be either "parent" or "child" and then only display the "parent" rows in the master list. That would involve two queries when updating an item's status (adding a new row and updating the "parent" row for that item), and seems to be less convenient. This seems like a common application so there has to be another way.
View Replies !
Privilege For Purge Master Logs
MySQL Version: 5.0.41 I have a user that has these privileges but is denied when trying to PURGE MASTER LOGS. GRANT SELECT, RELOAD, FILE, SHOW DATABASES, SUPER, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW ON *.*
View Replies !
Muti Master Server Replication
I need some help in setting up multi master server replication setup fpr my project. Till now I have set up single master replication, but had not been able to manage anythin on mulitimaster setup.
View Replies !
Master/slave Setup To Do Replication
using mysql 4.1.7, have a master/slave setup to do replication. Is there anyway to prevent the slavedb from accepting inserts other than the replication inserts? If rows do get inserted directly into the slave, there is an error because the two databases no longer match. Is there anyway to recover from this?
View Replies !
Dual Master Replication In MySQL 4.1
I have been charged with the task of setting up dual master replication with our company's MySQL servers. I know that their are inherent auto_increment problems with this set up, and the mechanisms to fix this problem weren't introduced until version 5.0. Can anyone give me some tips, pointers, or advice for safely setting up this replication topology in 4.1? Are there any steps I can take to make sure there are no auto_increment issues? Has anyone had experience setting this up?
View Replies !
Replication With Master And Several Slaves/Masters
At the company I'm working (a road survey centre) where about to start a new project. We're planning to create web-based application with php, javascript, maybe we're going to use AJAX but certainly combined with a MySQL database to store all of the data. Since users/researchers have to be able to work off-line on a laptop. Our idea was to install, via XAMPP or something like that, the complete package (i.e. Apache, PHP, MySQL) on each laptop. On a main server we have the same but probably a Linux system. Now, I know it is possible to replicate the DB on the laptops with the main DB. But is it also possible to update/replicate the main DB with results from the/a laptop after working a day off-line? So it becomes available on each laptop after they done a replication from the main DB? Maybe a long post for a small question, but I wanted to draw the "context" in wich it is going to be used. A question you are about going to ask me probably is: "why they have to be able to work off-line?" Quite simple: Not everywhere in the world you have wireless internet access + the onsite laptops need to communicate on site with serial devices GPS, Gyroscope, and such like. Wich is for a webbased application running on a server thousand miles rather difficult...
View Replies !
Replication :: Error Connecting To Master
Show master status on the master looks fine. When I start mysql on the slave, here's what I get: 071017 10:20:35 [Note] Slave SQL thread initialized, starting replication in log 'binary-log.000006' at position 3031, relay log './web1-relay-bin.000003' position: 98 071017 10:23:44 [ERROR] Slave I/O thread: error connecting to master 'replication@myotherserver': Error: 'Lost connection to MySQL server during query' errno: 2013 retry-time: 60 retries: 86400 Master my.cnf: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 log-bin=/var/db/repl/binary-log binlog-do-db=mydatabase binlog-ignore-db=abunchofotherdatabases server-id=1 [mysql.server] user=mysql #basedir=/var/lib [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Slave My.cnf: [mysqld]...........................
View Replies !
1 Simply Query Problem. Please Help With Insert Into 1 Table From Another Table
I have Table A that has some records already in and then I have Table B that does a few things and gets updated regularly. With Table A, I want to get it updated every 8 hours or so with new data that has been inserted into Table B. I will do this using the Cron. But what would be the best insert query to use so that it does the process really quick. The query that I have so far is: Quote: mysql_query("INSERT INTO table a (id, title, descrip) SELECT id, title, descrip FROM table b where app=1"); With the above query the id's match in both tables. But I only want the new records to be inserted into Table A where app=1 in Table B and the row is not already in Table A. How can I add to the query so that it does this.
View Replies !
|
TRACKER
