Id Starts From 1 After TRUNCATE
is it possible to let the id of a table to count from 1 afted it's been truncated?
View Complete Forum Thread with Replies
Related Forum Messages:
Starts And Ends
--Apple-Mail-2-720579055 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; format=flowed when I enter: cd /usr/local/mysql sudo chown -R mysql data/ sudo echo sudo ./bin/mysqld_safe & mysqld starts and ends. I am using a Mac with OSX 10.2.4 and have installed 4.0.4 Any assistance would be greatly appreciated! Helen --Apple-Mail-2-720579055--
View Replies !
Field Starts
How do you do a search for checking if the field starts with a certain value. What I am trying to do is search article titles for titles that start with "a" or "b" or "c" etc. Basically like on websites where you can find products by clicking on a list of letters. Do I need to grab all the articles and manually check this or can I do this in MySQL?
View Replies !
Starts With A Number
Is it possible in a query to select all the records that have a number (as opposed to a letter) as the first character in a particular field? If so, how would you word the query
View Replies !
Insert 'http://' Where Row Starts With Www.
I need to insert "[url]http://"[/url]; to the beginning of each row where a URL has been entered as www.anything.com (for example) to the row becomes (http://www.anything.com) I don't know how I can do this without over writting all the current URL's with out the prefix "[url]http://"[/url]; in a URL and whether it can be done.
View Replies !
Auto-increment Starts On Highest Value
When this has been created, the ID has been generated to 2147483647 and when I try and create another record it fails because nothing can go higher than 2147483647 (I think) The auto-increment has been started at 2147483647 no idea why, and from there when I atmysqlt to create another record it will fail because it ascends from that value.
View Replies !
Mysql Starts Multiple Php-instances
i have a little problem with a server, running mysql and php. I don't know which php-version i am working with, but the following is the answer for 'mysql --version' mysql Ver 11.18 Distrib 3.23.52, for suse-linux (i686) I know, that this version is not the newest one, but thats not my business. My problem is that i need to execute a script, which starts mysql with this command: mysql -hlocalhost -uuser -ppass db < $update > $update_log whenever this command is running and the 800k $update file is working on the db (a lot of delete and insert statements) there are several php-instances created. So many that they are slowing-down the server, that he is unusable.
View Replies !
Truncate-operator (*)
I want to be able to search for *load* and want my boolean search to return stuff like downloads, shareloads, bogload etc. The truncate-operator works only at the end of the word though. I can only think of, when the searchterm is submitted, using php to strip the *term* out of the rest, and then adding OR|AND|AND NOT (depending on *term, +*term*, -*term*) tablefield LIKE ''%term%' Is there a better way, without the use of LIKE?
View Replies !
Truncate Table
I'm writing a perl script that will crawl through a directory and insert into MySQL the filename, parent directory id, file size, and last modify time. I'm going to set the script to run on a weekly basis. The directory the script will be crawling through will be archived files that should not be changing much, but new directories will constantly be added tot e archive folder. My question is should I TRUNCATE the table every crawl and just insert the data from scratch every time? Or is there a better possibility?
View Replies !
TRUNCATE And AUTO_INCREMENT
Here is the situation: Recently i inserted a little over 530,000 rows into a table (InnoDB) where the primary id was on AUTO_INCREMENT. Obviosuly, COUNT(*) took a while. Then i truncated the table, and thought that this should get everything back. However, when i ran SELECT * on that table it took good 7 seconds to give me back my 0 rows. After this i did ALTER TABLE AUTO_INCREMENT = 1 and everything came back to normal - the SELECT * would take 0 seconds to return the fact that there are no rows. Unfortunately, i am on a kind of tight schedule so i didn't have a chance to repeat the experiment, but i was under impression that TRUNCATE would reset AUTO_INCREMENT. Why did it take so long to run SELECT * then and was fixed by ALTER TABLE? Or was it about the fact that i just ran TRUNCATE on that table and the first SELECT, for some reason unknown to me, runs longer on TRUNCATEd table?
View Replies !
Truncate Text
I'm selecting article brief from the "content" field of "article" table. The "content" field is mediumtext and I'm selecting this for many articles for the index page. So I'm saying to myself for sure there's a better solution than select the whole mediumtext field then use PHP substr function to just get the brief, 750 chars is not than enough.
View Replies !
Undo Table Truncate
im running phpmyadmin on my local server, with the mysql, by mistake i have truncated an unbacked-up table that contains valuable data, is there a way to undo or recover this data? does mysql put a copy of the truncated data somewhere?
View Replies !
Round/Truncate Functions
Truncate and round work as I expect them to and as the documentation says: TRUNCATE(100.999, 2) produces 100.99 However, it doesn't seem possible to use a database stored integer in place of the hard coded value for the decimal precision. For instance, if dec_precision is an INTEGER and quantity is a DECIMAL(65, 30): SELECT TRUNCATE(orders.quantity, orders.dec_precision) FROM orders performed on an order having the same values stated above will produce 100.989999999999994884092302527279 Round also creates similar strange results. Is it simply not possible to use round and truncate in such a manner?
View Replies !
Copy Records Before TRUNCATE
I need to copy the records in a table to another table before I truncate the records in example_table1. Lets say example_table1 needs to be copied to example_table2 before I truncate example_table1 The goal is to have example_table2 with ALL the records ever added to example_table1
View Replies !
Truncate Table Not Resetting Autoindex
Im in the process of doing some testing on my site. Our host uses Mysql 4.0.25-standard. When I want to empty out a certain table that has an autoindex field, it doesnt rest the count ....what the heck ? im looking at the table in phpmyadmin, and there is the " Row Statistics: " stating this: Next Autoindex 23 even after doing a truncate table. droping and recreating the table of course solves the problem, but is this a bug ? I dont seem to have this problem with ver 5.0 on my workstation.
View Replies !
Weird Problem With TRUNCATE I Use For Rounding
Let's say I want to round a number to 1 decimal digit in the most usual way, that is all numbers that have number 5 or greater after the first decimal digit are rounded up. I know that I can't rely on the ROUND() function in mysql 4.1 since its behaviour depends on the underlying C library and I want something consistent among servers. I have found a workaround in mysql documentation comments, and this is what I do - I have a table like this: CREATE TABLE `test2` ( `id` smallint(5) unsigned NOT NULL auto_increment, `seq` decimal(5,2) unsigned NOT NULL default Ɔ.00', PRIMARY KEY (`id`) ) ENGINE=MyISAM; INSERT INTO `test2` VALUES (1, 0.05); INSERT INTO `test2` VALUES (2, 0.15); INSERT INTO `test2` VALUES (3, 0.25); INSERT INTO `test2` VALUES (4, 0.35); INSERT INTO `test2` VALUES (5, 0.45); INSERT INTO `test2` VALUES (6, 0.55); INSERT INTO `test2` VALUES (7, 0.54); INSERT INTO `test2` VALUES (8, 0.56); And I run this query: SELECT seq, TRUNCATE((seq+0.05),1) AS rounded, seq+0.05 AS added FROM test2 and this is what I get: seq rounded added 0.05 0.1 0.10 0.15 0.2 0.20 0.25 0.3 0.30 0.35 0.3 0.40 0.45 0.5 0.50 0.55 0.6 0.60 0.54 0.5 0.59 0.56 0.6 0.61 Can you see the weird result at 0.35? I suppose this is because of lack of arithmetic precision - does mysql convert all numbers to floats before any arithmetics? Anyway, how to work around this problem?
View Replies !
Wrong Bytesec Error Truncate My Data!
I am getting quite desperate on this, since I've been trying to recover my diary of a whole year of travelling which was stored in a MyISAM table for a couples of weeks now! The MySQL server was running in a XP machine and suddenly the hard disk crashed starting my particular nightmare. I had to use recovery tools to scan the disk and I thought I was saved when I found the *.frm, *.MYI and *.MYD files and was able to recover them, but when I tried get the tables back into MySQL and backup their contents, I found a series of errors that have kept me down ever since! The first complain I got when I selected the table was: Didn't find any fields in table 'news' I then checked the table for errors, and got: mysql> check table news; +----------+-------+----------+-------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------+-------+----------+-------------------------------------------------+ | ver.news | check | error | Incorrect information in file: './ver/news.frm' | +----------+-------+----------+-------------------------------------------------+ 1 row in set (0.03 sec) I checked it with myisamchk and got: Curro:/usr/local/mysql/bin root# ./myisamchk ../data/ver/news myisamchk: error: '../data/ver/news' is not a MyISAM-table I was reading the manual and since I had the description of the original tables, I created new tables with the same structure and used the *.frm files. With myisamchk I got the same error, but with CHECK TABLE the error changed and got: mysql> check table news; +----------+-------+----------+------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------+-------+----------+------------------------------------------+ | ver.news | check | error | Can't open file: 'news.MYI' (errno: 130) | +----------+-------+----------+------------------------------------------+ 1 row in set (0.01 sec) Which meant I guess, the file is corrupted: MySQL error: 130 = Incorrect file format I then used the new *.MYI and got the following errors: Curro:/usr/local/mysql/bin root# ./myisamchk ../data/ver/news Checking MyISAM file: ../data/ver/news Data records: 0 Deleted blocks: 0 - check file-size myisamchk: warning: Size of datafile is: 848484 Should be: 0 - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 - check record links myisamchk: error: Wrong bytesec: 154-126-101 at linkstart: 0 MyISAM-table '../data/ver/news' is corrupted Fix it using switch "-r" or "-o" mysql> check table news; +----------+-------+----------+------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------+-------+----------+------------------------------------------------+ | ver.news | check | warning | Size of datafile is: 848484 Should be: 0 | | ver.news | check | error | Wrong bytesec: 154-126-101 at linkstart: 0 | | ver.news | check | error | Corrupt | +----------+-------+----------+------------------------------------------------+ 3 rows in set (0.10 sec) I searched the web to try to find a fix but I couldn't find any solved problem similar to this, with the Wrong bytesec at linkstart 0. I tried to repair the tables, and both the REPAIR TABLE and the myisamchk repairs using the newly created .frm and .MYI files resulted in an empty set, a truncated data file. I also tried with REPAIR TABLE .. USE_FRM with no luck, always complaining first of the 'Wrong bytesec' and then finding the blocks either that point outside data file or too small. Here is an extract of the result: mysql> repair table news USE_FRM; +----------+--------+----------+------------------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------+--------+----------+------------------------------------------------------------------------------+ | ver.news | repair | info | Wrong bytesec: 154-126-101 at 0; Skipped | | ver.news | repair | info | Found block that points outside data file at 488 | | ver.news | repair | info | Found block that points outside data file at 544 | | ver.news | repair | info | Found block with too small length at 896; Skipped | | ver.news | repair | info | Found block with too small length at 1120; Skipped | | ver.news | repair | info | Found link that points at -4170694140902777944 (outside data file) at 1148 | | ver.news | repair | info | Found block that points outside data file at 1232 | | ver.news | repair | info | Found block that points outside data file at 1716 | | ver.news | repair | info | Found block that points outside data file at 1944 | | ver.news | repair | info | Found block with too small length at 2044; Skipped | | ver.news | repair | info | Found block with too small length at 2068; Skipped | . . . | ver.news | repair | info | Found block that points outside data file at 847492 | | ver.news | repair | info | Found block with too small length at 847928; Skipped | | ver.news | repair | info | Found block that points outside data file at 848044 | | ver.news | repair | info | Found block that points outside data file at 848364 | | ver.news | repair | status | OK | +----------+--------+----------+------------------------------------------------------------------------------+ 3224 rows in set (1.50 sec) Please note that the different methods I used (REPAIR TABLE, myisamchk) all resulted as status OK, but the data is truncated. The tables were generated with MySQL version 3.23.58 under XP, and tried to fix them with the same version, with version 4.1.10 under XP and version 4.1.8 under Mac OSX. Could somebody please help me! I am getting quite desperate on this, I've tried to recover the files with 5 different programs and all recover the same files, so I think the .MYD file should not be corrupted.
View Replies !
Truncate Email Address In Username Field?
I imported a bunch of users using their email address as the username. I need to update every user who has an email address in the username field, to convert the username to just what is before the @ symbol. Example: user@host.com becomes user and user.name@host.com becomes user.name Can anybody help with this?
View Replies !
Truncate Tabel And Insert Data How To Update Indexes?
Once every day our financesystem updates data that I uses for my database. Some tables I truncate and reinsert data to get the latest changes in our customer and prospect database. Others I delete from a certain date to obtain best economical data and finally one database with 14M records I add records that has been added since the day before. What I find is a very slow system and the funny part (but I think understandable) is that if I do not update for lets say a week (because I test stuff) the queries are fast and smooth. That tells me that the problem is indexing. So if I truncate a table, how to update indexes or can I do that at all? The Optimize tabel doesn't help me here :( I use MyISam and not InnoDB would that make any changes? What about indexes are they needed (those I create)? or can MySQL handle that better?
View Replies !
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 !
|
TRACKER
