Write A Script With Looping Constructs
I want to write a script to insert some rows in a database. The user of the script will login to mysql and paste the script at this point.
Is there a way to use looping constructs, variables, etc. Iam thinking of a PL/SQL equivalent.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Search And Write, Or Write And Recover?
The problem: I need to generate a 'unique string' for each row in a table. I already use auto_increment for system dependencies between tables. What is the best approach one of these or another? After generating a candidate 'unique string' the two strategies that came to mind are: 1. to then search the table's column to see if it is already assigned; locking the table for write while searching and writing the new row, or 2. set the column to UNIQUE when defining the table. Just go ahead and write the new row if you get a "non-unique" exception, generate another 'unique string' and try again. I've tried both on a small XP laptop and get "lock timeout exceptions" rather quickly using #1. But replace those with lots of re-writes when there starts to get "collisions" of 'unique string's.
SQL Looping Query
I am pulling my hair out trying to crack a bit of SQL to query some tables. I could easily sort it out if I could just do a simple subquery like this: SELECT DISTINCT(ArtistNameTable2.ArtistName) AS ANm, ArtistNameTable2.ID FROM SongTable2, ArtistNameTable2, SongPaidFor WHERE SongTable2.ArtID = ArtistNameTable2.ID AND SongPaidFor.SongID = SongTable2.ID AND SongPaidFor.CustID IN (SELECT DISTINCT(CustID) AS CID FROM SongPaidFor WHERE SongID = 205 AND CustID <> 0) But! I can't use subqueries on the version of MySQL I'm using. So, I have 3 tables: SongPaidFor ArtistNameTable2 SongTable2 When a song is paid for, the following type of data gets entered into the SongPaidFor table e.g let's assume we're looking at SongID 205: SongID..CustID 205.....128 205.....218 205.....388 205.....392 205.....396 205.....397 Fine so far -- what I am trying to do is to say, for songID 205, customers also bought songs from the following artists... So - to start with, we have the SongPaidFor table which shows which customers bought which song. I also have the following tables: ArtistNameTable2 - containing: ================ ID (ArtistID) ArtistName and also: SongTable2 - containing: ========== ID (SongID) SongName So these 2 tables can be accessed via a simple bit of SQL - e.g - to find the Artists customer 128 also purchased tracks from, I could do this: SELECT DISTINCT(ArtistNameTable2.ArtistName) AS ANm, ArtistNameTable2.ID FROM SongTable2, ArtistNameTable2, SongPaidFor WHERE SongTable2.ArtID = ArtistNameTable2.ID AND SongPaidFor.SongID = SongTable2.ID AND SongPaidFor.CustID = 128 But - how on earth can I loop through the output to generate a DISTINCT list of artists that the customers who bought track 205 also like? I have done a simple bit of SQL to loop through to work out the ArtistName and ArtistID that the customers also like: ID = request("ID") ''e.g.songID 205 SQL1 = "SELECT DISTINCT(CustID) AS CID FROM SongPaidFor WHERE SongID = "&ID&" AND CustID <> 0" SET RS1 = oConn.Execute(SQL1) DO WHILE NOT RS1.EOF CustID = RS1("CID") SQL2 = " SELECT DISTINCT(ArtistNameTable2.ArtistName), ArtistNameTable2.ID FROM SongTable2, ArtistNameTable2, SongPaidFor " SQL2 = SQL2 & " WHERE SongTable2.ArtID = ArtistNameTable2.ID " SQL2 = SQL2 & " AND SongPaidFor.SongID = SongTable2.ID " SQL2 = SQL2 & " AND SongPaidFor.CustID = "&CustID SET RS2 = oConn.Execute(SQL2) name_loop = "" DO WHILE NOT RS2.EOF thisname = RS2("ArtistName") name_loop = name_loop & thisname & "<br />" RS2.MoveNext Loop RS2.Close:set RS2=nothing total_loop = total_loop & name_loop RS1.MoveNext Loop RS1.Close:Set RS1=nothing But - it's still a completely crap approach, because at the end of it, I just get a big list of text, which cannot be manipulated in any way: e.g: The High Teas semble Gavin Molton (instrumental) Gavin Molton Ruin the Rain Charlies Horse The Blue Review Water Middle Men akimbo The Barnacles StrangeCloud Gavin Molton Flame Into Being Gavin Molton (instrumental) Ray Saunders Flame Into Being Gavin Molton Gavin Molton (instrumental) Au Revoir Simone Cut Copy Seamless Pink rinôçérôse Zap Mama Nada Surf DJ Shadowman Nermin feat. Ayaah Nomad Gavin Molton With this - I cannot strip out duplicates, or anything else - it's just a list of text. Any ideas? Can anyone else see a way out of this? Sorry this is so long and messy. If you've read this far, thanks! Jim
Looping Certain Database Entries
I need to determine how many links there are in a database table. Then loop the links to be displayed in the menu. Can someone please help me with this? Thanks in advance.
Query Help Looping Through Records
The below Query loops through two tables in mysql and outputs all records where a match_date in a reports table (re) is equal to a match_date in a fixtures table (f). Now this works well, but what I want to do is say to my query is find all of these dates BUT - As soon as the FIRST f.date is found that does not have a re.date matching it output that information and limit it to 1 .....
Looping Through Database Objects
I am trying to create a process that loops through all the databases on my server (75) and run the same set of sql statements on each database. Here is what I have so far... begin declare dbname char(15); SELECT schema_name INTO dbname FROM information_schema.schemata WHERE schema_name like "acc340%" LIMIT 1; while dbname IS not null do (this is what doesn't work) SELECT dbname; SELECT schema_name INTO dbname FROM information_schema.schemata WHERE schema_name > dbname AND schema_name like "acc340%" LIMIT 1; end while; end The loop works but since the select into never changes the value to null after it runs out of data, my loop continues forever. Is there a way to get around this annoying problem?
Looping Through Dates Without Table
Is there a way to have mysql simply output all the dates between 2007-10-10 and 2007-10-15 without using a single table? Resulting rows would be : 2007-10-10 2007-10-11 2007-10-12 2007-10-13 2007-10-14 2007-10-15
Looping Through Insert Statements Is Timing Out, Alternative?
I am looping through and creating about 600 inserts/executes in PHP and it's timing out. I have tried aggregating them into one string that ends up like: INSERT INTO table VALUES(val1, val2,...);INSERT INTO table VALUES(val3, val4,...);INSERT INTO table VALUES(val5, val6,...);... But is errors out. I am using PHP5 and MySQL 4.1. How can I get around that? Is there an alternative? If prepared statement is a way around it, any easy examples to look at?
How Do I Calculate A New Value From 2 Columns Whilst Looping A Table
I have a single table and i would like to calculate two columns (imp and clicks) while looping through the results. this is what the table would look like... campaign | imp| clicks A| 200000 | 100 B| 100000 | 40 C| 50000| 10 D| 300000 | 200 and here's what i would like to achieve with a mysql query and php. I would like to create a new column CTR% and calculate the CTR for each row by looping through the table... campaign | imp| clicks | CTR % A| 200000 | 100| 0.05 B| 100000 | 40| 0.04 C| 50000| 10| 0.02 D| 300000 | 200| 0.06 i've tried a few things that i've read up on by googling but none really worked (based on using a while loop). now i would like to wipe the slate clean and get your suggestions on what to do??
Help Me Write This Join?
Hi there, been ages since I wrote any sql, and I'm a little stuck but I figure this is easy for you folks. I have this original query: $posts = $wpdb->get_results("SELECT ID, post_title FROM " . $wpdb->posts . " WHERE post_status='publish' ORDER BY post_date DESC"); And this gives a lists of all posts. However, I want to only grab posts by a certain category now, say category #9. This information is kept in an intersection table called wp_post2cat. The columns in that table are: rel_id post_id category_id category_id is what I want, and post_id is the key pointing back to the original post. Posts can have multiple categories, but I just want the one listing where it's category 9, which is audio. So pseudocode would be something like: SELECT all posts WHERE post_status equals "publish" and the category is equal to audio (9).
How To Write This Select?
I'm having some troubles writing a select. I have the following tables: theater ------- - id - name movie ----- - id - name showtime --------- - id - theater - movie - from - to - schedule I would like to list all the theaters with all the available showtimes and movies. I tried to, but i didn't get the result i was expecting. Can somebody help?
Best Way To Write This Query
Query 1: obtains results in boolean mode from products table based on keywords Query 2: Would like to grab all manufacturers names from manufacturers table based on mf_id in products table to then create a brand filter. What is the best way to do this in the most efficient query possible. I was thinking of building a php array from query 1 of mf id's and the putting them in query 2 and separating them using OR operators. any ideas? here is the query fyi: PHP $sql ="SELECT products_id as prodid, products_name as name, manufacturers_id as manufacturers_id, products_price as price, products_msrp as msrp, products_date_added as pda, products_status as status, products_sku as sku, products_weight as weight, categories_id as cat_id, " .boolean_sql_select( boolean_inclusive_atoms($search_string), $fulltext_key)." as relevance " ."FROM $table_name " ."WHERE " .boolean_sql_where($search_string,$fulltext_key)." " ."HAVING relevance>0 "; if (isset($HTTP_GET_VARS['sort'])) { if ($_GET['sort']=="brand") { $sql.="ORDER BY products_name "; } elseif ($_GET['sort']=="size") { $sql.="ORDER BY products_weight "; } elseif ($_GET['sort']=="price_low") { $sql.="ORDER BY products_price ASC "; } elseif ($_GET['sort']=="price_high") { $sql.="ORDER BY products_price DESC "; } elseif ($_GET['sort']=="fav") { $sql.="ORDER BY products_name "; } } else { $sql.="ORDER BY relevance DESC "; }
How To Write This In MySQL?
I Want to check if a certain value doesnt exist in a ceratain field in my table as a condition. "select id from employees inner join translog on employees.emp_id=translog.empid inner join outofofficedays on employees.emp_id=outofofficedays.emp_id inner join holidays where (here is my question: ��-04-02' doesnt exist in the column translog.vtransdate) and (��-04-02' not between holidays.H_START and H_END) order by translog.vTransDate,employees.emp_id limit 1"
How Do I Write This Trigger?
I’ve been searching all over for the answer to this one. If you have the answer, it would help me out a lot! Using MySQL triggers, I want to sync content between two tables; I have table “user” and table “users”, each have similar columns “last_name” “first_name” “user_id” …etc, and when a row gets written to table “users”, I wanted that record to be replicated and inserted to “user” (with all the same field info). How do I write this trigger? I know, it’s anti-normalization, but it will really help me out with testing one of our site's authentication.
Write A Mysql Db Onto A Cd
how to write the db of a mysql on one system onto a cd so as to copy that db to the mysql on another system. The 2 systems are not connected to each other.
Can't Write To File
Using MySQL 4.1.11-max on OS X 10.3. Using command line. Can't successfully use "INTO OUTFILE". Getting the following: mysql> select * -> into outfile '/users/steves/desktop/test.txt' -> fields terminated by ',' -> from industry; ERROR 1 (HY000): Can't create/write to file '/users/steves/desktop/test.txt' (Errcode: 13) At the shell, I get this: steves2ndmac:~ steves$ perror 13 OS error code 13: Permission denied Newbie in both Unix shell and MySQL. Can anyone tell me how to set permissions for Unix User mysql?
Cant Write Or Delete
I cant get my php script to insert or delete records into a mysql table. I can view them all fine but that's about it. I've checked the user permissions on mysql and have set them all to 'Y' incase that was the problem but still no joy. Can anyone suggest what the problem may be? I'm tried the usual RTFM but cant spot the problem
Write Data To Row With Via C API
I have an app written in C that sends data to text files. The files receive 8 variables and look like this: May 19 20:42:09 2002 7 Services 3.23 4.34 0 None 2.31 Rather than writing data to a text file, I need that data to go to a MySQL 5.0.41 database row. I've already got the C API working and connecting to the "localhost" database, but now I need to write data with each transaction. Optimally, I'd like to send the data directly to the database row, but there doesn't seem to be an INSERT statement to do that. What's the best method to write the data to the database directly?
How To Write Like Comparison In Procedures?
this sample procedure takes in an username, searches for number of entries matching the pattern of the username. delimiter $$ create procedure sampleDB.sampleProcedure( IN m_username varchar(30), OUT m_username_matched_count int ) deterministic begin if length(m_username) = 0 then set m_username = 'jason' #set temporary name if empty end if set m_username_matched = 0; #finds number of matches for the given username select count(*) into m_username_matched #store into the variable to be returned from sampleDB.sampleTable where userName like '%m_username%' #this is the statement that does not work# end$$
Using INSERT To Write To A Directory
I have a problem that I'm not sure if it can be done. I'm trying to use the MySQL C API to be able to use a normal sql insert statement that will send the data or file to a directory and NOT the database. I'm not sure if there is anything out there that will allow this, but I've searched everywhere, and I have found no solution.
C- Program To Write To Myi , Myd File
is it possible to create a myi, myd file for mysql using a C program. I am using Stata that is written in C API, and I want to write the data out to MYSQL database. Using ODBC and loading the data in MYSQL from STATA takes forever. Can anyone please tell me if I can create the myd files by using the data in memory thru C programming.
Cant Write Or Delete To Table
I cant get my php script to insert or delete records into a mysql table. I can view them all fine but that's about it. I've checked the user permissions on mysql and have set them all to 'Y' incase that was the problem but still no joy. Can anyone suggest what the problem may be? I'm tried the usual RTFM but cant spot the problem
MySql Write Vs Read
Essentially I am faced with a design choice; have an oft run (modifiable) query run every time any user wants to run it, comparing criteria againt each and every user in the table and delviering list OR saving that list (user/user/) so the next time all that needs to be queried is the existence of a record in the table. The basic tradeoff; version 1 there are many simultaneous queries of the user db on complex search criteria; version 2 there are fewer but their are as many far more simple queries to a much larger table, not to mention many writes to same to keep the table current. I know this is pretty vague, but I was wondering if in general there is an area that mysql excels in vis a vis complex query smaller tables simple query larger tables. Question 2 is there anyway to guestiate the search/write times of a user log in to both query and update? #Records, fields, etc? I'd like to keep log in to < 10 seconds max.
Write Conflict Error
I have a database with access as front end and Mysql as back end. I am gettting the following 'Write Conflict' Error. when i am on my order form, This form does has a subform where i enter all the products that are ordered. Could any one let me know what could cause this problem. I am the only one using the database as its still in the build stage Code:
Slow Write Speeds
Here is what i have IBM x226 - 8GB ram - 2x 3.4 Ghz Xeon (single core) processors - RAID 5E (6x300GB disks) Suse Linux 9.0; MySQL 5.0.13 So, I am migrating from SQL server 2000 on a much smaller Dell PowerEdge (2gb ram, 2 Xeon 3.0 Ghz, Raid 5 over 5x74GB). I am exeriencing very slow write times. For instance, there is one table on both servers that has approx 9mil rows...here is a basic test: Update loan set test_col = 1; Platform Time IBM/Linux/MySQL 5 9:45 Dell/Win/SQL server 1:37 The tests were run during very light server activity and I tried them multiple times. Our IT department is fairly insistant that the IBM server is running per spec...obviously something is not right. I have changed some of the server variables with no improvement.
Write Once Read Many Field
I am designing a database and one of the requirements my client has is that once data is entered into certain fields it cannot, under any circumstances, be changed. Is it possible to create a write once read many (WORM) field in MySQL directly?
Re-write Text File
i'm creating a log file regarding database operations. My problem is i want to append a new record into the log file each time i update my database. Previously I've used the following method to create a file: SELECT * INTO OUTFILE 'c:/"+fileName[i]+" ' FIELDS TERMINATED BY '|' LINES TERMINATED BY ' ' FROM "+tableName[i]+"; MY problem is I cannot re-use the file created. Is there any way to call the same file for the purpose of re-use. Or maybe the OUTFILE do not allow file created to be re-use.
Create/write To The File
I am working on Solaris and seem to get the following error Cant create/write to the file '/home/dslab456/temp/tempoutput.lst' As in windows i cannot specify which drive it is or so ? could anyone help me out with this ? SELECT * INTO OUTFILE '/home/dslab456/temp/tempoutput.lst' FROM stocklist_vokus s,p_tmpr p WHERE s.STOCKLIST =p.stocklist_value ORDER BY p.rid Edited 1 time(s). Last edit at 02/12/2007 05:38AM by summer queen.
Vita And Write In MySQL
My web site use a odbc to a MySQL database. I create it on XP and it's worked but I'm now on vita and the data base on a 2003 server. All the reading access on the database are ok but all the writing access are denied. I haven't error message just error 500 on ie7.
Who Do I Write Select With Dates And Times
i have to cols, date and time. who can i do, SELECT between (date 1,time 1) to (date 2, time 2)? for example i want to get the recordes between 2007-01-01 23:00:00 to 2007-01-03 03:00:00 ?
How To Write Query To Compare Tables
I give in. I can't figure it out, and I know it's one of those things that once I see it I'll think, "OF COURSE!". I have 2 tables, same DB. Table 1 is named Policies Table 2 is named Assignments Both contain a PolicyID field. I want to find out which PolicyID entries are ONLY in Policies, and not in Assignments. So if "Select PolicyID from Assignments" returned the following: 1, 2, 3, 4, 5, 6 And "Select PolicyID from Policies" returned: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 What do I use to return this? 7, 8, 9, 10 Any nudge, help, clue, etc. would be appreciated. I've looked thru examples of Outer Joins, Inner Joins, Lefts, Rights, Unions, Intersects, etc. and can't figure out which to use, all for what seems to be a simple operation!
To Re-write Complex Query With JOINS
I have a problem with one of the JOIN query here. Below is a brief description of the problem. tablename : test RecordId EffectiveDateothertableidvalue -------- ------------- ------------ ----- 12004-01-10110 22004-01-20120 32004-01-20270 42004-01-10280 52004-01-15110 62004-01-25310 Output : RecordId EffectiveDateothertableidvalue -------- ------------- ------------ ----- 22004-01-20120 32004-01-20270 62004-01-25310 Now I want to use a single SQL query to find a result where there exist one record for each unique "othertableid" where the record selected for the "othertableid" should be the recent one with regard to "EffectiveDate". That is from the above records, I want to select Records with "RecordId" = 2 and 3 because they are the recent one for "othertableid" = 1 and 2 respectively. Please be sure that I want to retrive all fields including "RecordId". The result should not depend on any other fields but "EffectiveDate" only. I am using MySQL 4.0.12 and it does not support "SUBQUERIES" which is now given support in latest MySQL edition. But I have read in the manual of MySQL that any "SUBQUERY" SQL statement can be written with proper "JOINS".
Can't Create/write To File In C:windows
I installed the 3.51 ODBC driver on a new test machine that is re-imaged each night to a clean OS (Win XP Pro SP2). We place our developer daily builds on this machine and run some automation. In the automation process, MYSQL statements are ran. The issue I get is that sometimes it stops with this error: "Can't create/write to file 'C:WINDOWSTEMP#sql_1808_0.MYD' (Errcode: 13), #-2147217871" or it will run right away, or sometimes it takes 3 tries before it finally gets past whatever it is choking on. I do not have MYSQL installed on that machine, only the driver. I have given the "Everyone" account full permissions, but still no luck. Any suggestions? The environment variable on the machine is set to C:WindowsTemp and it appears to execute some statements before, just is stops on some.
No Read Or Write Between INSERT And UPDATE
I am using mysql with the InnoDB engine. I wrote a perl script that first selects something from a table, and then updates a second table based on the select from the first table. I need to make sure that there is no read or write to the tables while my script performs the insert and update. I looked at http://dev.mysql.com/doc/refman/4.1/en/lock-tables.html and it says this: --------------------------------------------------------- If you are using a storage engine in MySQL that does not support transactions, you must use LOCK TABLES if you want to ensure that no other thread comes between a SELECT and an UPDATE. The example shown here requires LOCK TABLES to execute safely: LOCK TABLES trans READ, customer WRITE; SELECT SUM(value) FROM trans WHERE customer_id=some_id; UPDATE customer SET total_value=sum_from_previous_statement WHERE customer_id=some_id; UNLOCK TABLES; Without LOCK TABLES, it is possible that another thread might insert a new row in the trans table between execution of the SELECT and UPDATE statements. --------------------------------------------------------- However, I am using InnoDB and it DOES support transaction. So, does that mean that even if I don't lock my tables, it will still work? If not, what do I need to do?
SQL, Field Name Alias For Write Access
I get an error on the adorec.update line - Unknown column 'MyName' in 'field list'. The code will work for read access but won't let me write to it.How do I update a field with an alias? I know i can lose the "AS" part but that would defeat the purpose of what I'm accually trying to do.
Can't Create/write To File /var/run/mysqlid/mysqld.pid
I have installed mysql(mysql-standard-4.1.14-unknown-linux-gnux86_64-glibc23.tar) on linux.but when i tried to start mysql i am getting an error can't create/write to file /var/run/mysqlid/mysqld.pid can't start server can't create PID file :No such file or directory.
Can't Create/write To Database And Temp Directories
I'm quite new to mySQL, and am having problems being able to create database tables. I'm on my [5th] setup of mySQL/apache/php, just after formatting windows, and everything else seems to be working, and working together, so I'm assuming the installation is ok. I can create databases, but when I try to create a table, I get this error message: ERROR 1 (HY000): Can't create/write to file '.customermidian.frm' (Errcode: 2) I'm assuming I haven't set up my temp folder correctly, but I don't understand how it works properly. After reading the mySQL maual repeatedly, I tried using --tmp=c: emp to set it, and it didn't seem to work, and I created a my.ini file with this in it: [mysqld] tmpdir=C:/temp is there anything else I can try? I am logging into mySQL with the root account, and a password.
Access ODBC 'write Conflict' SOLVED!
I had this problem where a user would attempt to change a record, and always get the 'write conflict', and that another user had changed the record... even though no other users were on the database. I searched high and low for a solution, to no avail. Yes, I had a primary key; I had a TIMESTAMP field. I played around with Access' settings for record locking, no record locking, etc.; I set the MyODBC setting to "return matching rows". None of these worked. Here is what I found: I had a TIMESTAMP field in the MySQL table, NULL = OK. I imported data from a text file into this table. This text file did not have the TIMESTAMP field, so all of the records in the MySQL table had a default timestamp of '00000000000000'. I wrote a SQL statement that changed this TIMESTAMP '00000000000000' value to an arbitrary value (for example, '20030910134340'). Everything worked fine.
How To Write :send Email And Clear Form In Php
I am a student and I am building a webpage. I need to build a formular to send emails. The fields to fill are the name and email of the sender and also the message to send. I would like to test it locally and also later on a remote server. Code:
Can Seqouia/c-jdbc Segregate Read & Write DBs ?
I haven't started using Seqouia/C-jdbc but I am probing the ability to do something like this: Controller CW has 2 controllers under it - C1 & C2. C1 is local and has a host HA with X.db's tables t1 & t2. C2 is remote and has a host HB also with X.db's tables t1 & t2. Controller CR has 2 controllers under it - C3 & C4. C3 is local and has a host HC with X.db's tables t1 & t2. C4 is remote and has a host HD also with X.db's tables t1 & t2. X.db is MySql type, from experience I've found that MySQL is better off doing either only writes or only reads. So I want to segregate my read and write stores. Hence, HA & HB are meant for writing into, while HC & HD are meant for reading. Now writes coming to CW should be treated like transactions which land up on local and remote X.db. These are my WRITE-ONLY-DBs. Likewise I want reads to goto CR for load balancing with higher priority to read from HC than HD. Questions: (a) Can c-jdbc help in replication of data between HA-HC and HB-HD ? Which component would help to set this up ? Or do I need to go for out-of-band MySQL replication ? (b) Is this configuration optimal for high scalability or availability ? Should I have added contollers horizontally instead of vertically ? (c) Also, looks like I'd have to write an app to direct writes to CW and read to CR. Or is there a way to obviate that with the controller settings itself ?
Write/Move/Save Pdf To MySQL Table?
Is upload the only way to pass a file to a table? I have a contract.pdf which is generated via php (FPDF) and saved in a temporary file. I need that file then moved /written to the appropriate customer table in our database (MySQL ) so that the temporary file may then be overwritten by the next Customer/Order. While I am able to upload the file via <input type="file" name="uploadpdf> and related uploader.php, asking our web-users to browse for the file does not suit our needs, and of course adding a default value <input type="file" name="uploadpdf" value="http://localhost/crm/modules/Orders/contract.pdf"> doesnt work either. As the file is always the same (modules/Orders/contract.pdf) how may I manage that move automatically, (say with a Confirm Order button) to the database?
How To Optimize Frequency Of Disc Write Operations
I need to optimize frequency of disc write operations for inserted or updated data on MyISAM tables. How can I tune MySQL for large records block disc flushing instead of record-by-record flushing by default? Some DBMS parameters have effect on disc flushing ("flush", "flush_time", "delay_key_write"), but any of these parameters does not allow to reduce intensity of disc write operations. MySQL 5.0 (Win32) is used.
Mysqldump: Got Errno 27 On Write. File Too Large
I have musql 4.1.7 on Solaris 9, 64 bits and I want to mysqldump a +-4 gigas db. The dump I want to do is from a myISAM tables databank. I used to do that dump wihtout any problem until recently while the size of the Db double (+-2 to +-4) Code:
Obtaining Read/Write Ratio -Replication
I am looking into a replication strategies to improve performance and redundancy for a MySQL 4.1 database running on Linux/Apache2/mod_python. I am curious as to the standard way of obtaining a Read/Write Ratio. What patterns of usage suit One-Way or Two-Way replication? In my situation I have a database that intermittent bursts of inserts through the day, a lot of batched deletes/inserts/updates over night and fairly constant selects. I'd expect the RW Ratio would fluctuate dramatically in a 24 hour period.
InnoDB: Right For Write-heavy Table? Server Tuning?
I've decided to merge 4500 identical tables into one. They were previously partitioned with one table per user of the system, but I'm imagining I may not need to do this anymore for this particular table. I'd like to do so to reduce the number of files in this database's directory (currently over 15,000), to reduce backup complexity, and maybe improve performance. That's where I'm still unsure. This is the summary table for W3Counter with fields website_id, date, unique_visits, return_visits, page_views. The website_id and date uniquely identify each row. It is a write-heavy table (probably more than 99% INSERT/UPDATE compared to SELECT). I may be making things up, but I believe I remember InnoDB showing superior performance for this type of table. I'd imagine row-level locking would decrease lock contention when being hit by dozens of threads concurrently -- queries trying to update different rows would be allowed since that row isn't locked, unlike MyISAM where the entire table is locked by each query. Merging all the tables into this one would mean starting with about 1.5 million rows and growing about 4500 per day. So what do you think? Is InnoDB the right way to go for this table? Should I expect better performance than MyISAM for the inserts/updates? Should I expect the same or better performance on SELECTs that SUM() those counts over various date ranges, and sometimes group by WEEK(), MONTH() or YEAR() on the date column? I'm not sure whether the primary key is sufficient for that type of query, if a key on just the website_id would help those, or what. Any opinions? My other reason for this thread is to get some pointers on tuning the server variables for InnoDB tables. There's much less written about this than those mainly applicable to MyISAM tables. Which are most important for a table like this? Right now no other tables will be InnoDB as the rest benefit greatly from MyISAM-specific features (like prefix compression on indexes and maintenance of row counts).
Mysql Client Dont Write Russian Characters
I installed MySQL 5.0.24 and have so problem.I can't type russian characters in mysql client. Database has data in koi8 encoding (from dump, from php form...) and this data correctly shown.But when I switch encoding to russian in mysql client cursor stops moving independent from my keys pressing,when switch it return to English and typing continued.For example,I enter: select fld from tbl where fld="russ",if "russ" in koi8 I cann't type this characters. It is possible type russian characters from OS command line: mysql -p -e "select fld from tbl where fld='russ'" dbname. In this case whole query text will be entered and correctly executed. If anybody know how possibly decide this problem write me please. My OS is ALTLINUX 3.0 : LANG=ru_RU.KOI8-R MySQL's variables: | character_set_client | koi8r | character_set_connection | koi8r | character_set_database | koi8r | character_set_filesystem | binary | character_set_results | koi8r | character_set_server | koi8r | character_set_system | utf8 | character_sets_dir | /usr/local/mysql-standard-5.0.24-linux-i686/share/mysql/charsets/ | | collation_connection | koi8r_general_ci | collation_database | koi8r_general_ci | collation_server | koi8r_general_ci
|
TRACKER
