Posts by riahc3

Possibly, but I can't think how. Is this a problem for you? Have you been told that you're not allowed to use them?

The program Im using to make a MySQL query wont allow them. I have some code below (from exterior help) that might make it easier...

Did you search? Seems an issue after big MyISAM->InnoDB

OK, so I should backup, destroy and remake the table but this time with InnoDB (I plan to use InnoDB)?

Here is the new code:

I had to add a additional field (decimal) to the table called "Time_Stamp_Full" . It takes the Time_Stamp field (datetime) converts it to UNIX_TIME and then the Time_Stamp_ms (int) get added to it.

So the query is this:

SELECT z.*
FROM
(
    SELECT a.time_stamp_full, MAX(b.time_stamp_full) AS latest_prev_record
    FROM mytable a
    INNER JOIN mytable b
    ON a.time_stamp_full > b.time_stamp_full
    WHERE a.cycle = 1
    GROUP BY a.time_stamp_full
) Sub1
INNER JOIN mytable z
ON z.time_stamp_full = Sub1.latest_prev_record

Opinions? The problem is that the query seems to also take forever and last time I ran it, it gave me a lost connection to mysql server during query...

Um:

Error Code: 126. Incorrect key file for table '/tmp/#sql_141c_1.MYI'; try to repair it

What does this even mean?

'onerows' is just a name for the intermediate rownumbers equal to the row before the row where cycle=1, which ultimately show up in the 'i' column. Out of interest - do you need all the 'lead-up' data? Could you not just store the last record of each set? Should decrease your record numbers by about 10-fold possibly?

So "onerows" is a variable?

And yes, I need all the "lead-up" data.

Im gonna test this out now. Im still not completely sure if I can do it with that @i variable. Is it possible to do it without a variable?

Offtopic but changing a table to INNODB that had 2842501 records took 18 HOURS 38 minutes and 31.44 seconds. Biggest query Ive EVER done.

The "Times" table has a column named Time_Stamp and Time_Stamp_ms

Thats why Im not sure about "onerows" :S

BTW, Im kinda of confused as you change field and table names.

Im going to try to apply it.

Yeah, tried and it has confused me. Could you please change the names? Thank you

I would have to do two queries there and Im not sure I can (I can do two but Im not sure if that variable would be persistant)

Im doing a query and Im getting this:

Error Code: 3. Error writing file '/tmp/MYYQVeZr' (Errcode: 28)

Its not a space issue or a permission issue either:

Whats wrong?

Thank you. Seems like a very complicated query indeed....

You seem to be returning the last row of each cycle set is that right?

Each cycle ends with the row BEFORE the 1. 1 starts a cycle set.

I have a table called "mytable". The columns are

Time_Stamp (datetime) PK
Time_stamp_ms (int) PK
data1 (int)
data2 (int)
data3 (int)
data4 (int) 
data5 (int)
data6 (int)
cycle (int)
name (varstring)

I want to order by Time_Stamp and Time_stamp_ms and then each time cycle reaches 1, I want to get the Time_Stamp and Time_Stamp_ms from the previous row. Cycle is 1,2,3,4......n Means it will always increment by 1.

This table will problably have millions and millions of rows.

Also no PHP.

There is a sample of my table:

Time_Stamp              Time_Stamp_ms   d1      d2      d3      d4      d5      d6      cycle   name    

2014-04-24 09:09:37         765         5555    4444    3333    2222    1111    123     1       name
2014-04-24 09:09:37         845         5555    4444    3333    2222    1111    123     2       name
2014-04-24 09:09:37         925         5555    4444    3333    2222    1111    123     3       name
2014-04-24 09:09:38         5           5555    4444    3333    2222    1111    123     4       name
2014-04-24 09:09:38         85          5555    4444    3333    2222    1111    123     5       name
2014-04-24 09:09:38         165         5555    4444    3333    2222    1111    123     6       name
2014-04-24 09:09:38         245         5555    4444    3333    2222    1111    123     7       name
2014-04-24 09:09:38         325         5555    4444    3333    2222    1111    123     8       name
2014-04-24 09:09:38         405         5555    4444    3333    2222    1111    123     9       name
2014-04-24 09:09:38         485         5555    4444    3333    2222    1111    123     10      name
2014-04-24 09:09:38         565         5555    4444    3333    2222    1111    123     11      name
2014-04-24 09:09:38         645         5555    4444    3333    2222    1111    123     12      name
2014-04-24 09:09:38         725         5555    4444    3333    2222    1111    123     13      name
2014-04-24 09:09:38         805         5555    4444    3333 …

I pasted your code into MySQL and it returned 48:00:00 as you desired

I didnt test the code, I just saw that %H limits it from 0-23

You are right. It works like I desired.

Here is a quickie:

//declare another integer called x
while i <= Length(sEmail) do

  begin

    while x <= Length(ALLOWED) do
     begin

       if sEmail[i] = ALLOWED[x] then
         begin
               bResult := true;
               break;
         end;
       else
       begin
             bResult := false;
     // break;
         end;
           Inc(x) ;
     end;  
     Inc(i)

  end; 

Code is as-is and untested. Also very dirty and there are 1000000s of better ways to do this.

BTW, pritaeas is right: With this you are just validating if it is a email, not a proper email.

if sEmail[i] in [ALLOWED[1] .. ALLOWED[Length(ALLOWED)]] then 

I cant find any documentation on a "if.......in......then" statement.

OK, from debugging I see your problem is in the while....

Debugging some more as I havent done Pascal in a LONG time.

Id take a hack at it (putting it on my PC and testing it) but is this Pascal or Delphi? Looks like Delphi...

Im thinking of proper ways to validate dates. Which months have 30 or 31, feb when it has 28, etc.

Since Im doing this in VBScript and there is no forum for that here, I thought of making this thread for a pseudocode method of doing it correctly universally.

No functions are allowed that it is done automatically and it should be able to be implemented in all languages.

Thanks for the tips and the ideas

I went ahead with REPLACE but thank you

And this isnt a PHP question.

Figured it out.

I have another question:

Would:

SELECT time_format(timediff('2014-04-25 09:11:00','2014-04-23 09:11:00'),'%H:%m:%S') as dif

Return

48:00:00

If not, how can I get it so it can return 48:00:00 ?

Thank you

This does work BTW:

SELECT time_format(timediff('2014-04-24 09:11:37','2014-04-24 09:11:47'),'%H:%m:%S') as dif

It returns -00:00:10 like it should

Simple timediff:

SELECT time_format(timediff('2014-04-24 09:11:37','2014-04-24 09:09:37'),'%H:%m:%s') as dif

Returns 00:00:00 . Im problably missing something stupid, my apoligies...

Solved it with this code:

 try 
            {
                dataGridView1.Columns["Time_Stamp"].DefaultCellStyle.Format = "yyyy-MM-dd hh:mm:ss";

            }
            catch (Exception x)
            {

                MessageBox.Show(x.Message);
            }

I have in a MySQL database with the column type being DateTime the following time:

Timestamp:
9:34:09
9:34:23
9:34:45
9:34:50
9:35:09
9:36:17

But a DataGridView in C# only shows me
9:34
9:34
9:34
9:34
9:35
9:36

Why?

It looks as though the data is just being placed at the beginning of the table in MySQL5.1 instead of the end. Unless specified, MySQL will add the inputted data to the end of the table, not the beginning. I don't recall this being different in MySQL 5.1. How are you calling the information from the database? I'm assuming you're using the same command to call from both like you have done when inserting data?

Your basic command:

select * from table;

I mean, I dont recall this being any different either. Like I said the code is the same except instead of connecting to one database (running on a 5.6 server) I connect to another (running 5.1)...

I insert something simple like

insert into table(pri1,pri2,value) values (1,1,1);
insert into table(pri1,pri2,value) values (1,2,1);

And in MySQL 5.6 they show up as:

pri1 pri2 value
1    1     1
1    2     1

BUT

In MySQL 5.1 I do the same thing and they show up as:

pri1 pri2 value
1    2     1
1    1     1

As in the first row is the last row inserted.

Why is this? Ive seen strange things but this is one of the strangest!

I figured it out:

insert into table(Time_Stamp,Time_Stamp_ms,p1,p2) values (time,timems,'1','2') on duplicate key update p1 = '1',p2='2'

That works for me. Did not know you could update more than one.

Hello

Sometimes I get a duplicate key which I want to ignore or discard.

This is a example and has nothing to do with the actual query. I simple want to state columns/values

insert into table(Time_Stamp,Time_Stamp_ms,p1) values (time,timems,'1')

Where the first two are my PK.

I see that

insert into table(Time_Stamp,Time_Stamp_ms,p1) values (time,timems,'1') on duplicate key

exists but it asks me for a UPDATE. How do I update on "Time_Stamp" and "Time_Stamp_ms"?

Im sorry for not explaining correctly my problem.

I have a bunch of insert querys running and some of them fail with a duplciate entry.

I want to see a log where it says "Error: Duplicate key" and that tells me the EXACT INSERT query that fails so I can see what keys are duplicated.

Thanks

I have a bunch of insert querys running and some of them fail with a duplciate entry.

I want to see a log where it says "Error: Duplicate key" and that tells me the EXACT INSERT query that fails so I can see what keys are duplicated.

Thanks

Even better:

select * from table limit 1,5;

Tried that but didn't work.

Got it to work :)

I did but accidiently made two threads: http://www.daniweb.com/web-development/databases/threads/476852/split-databasetables-across-various-files

Noone has any ideas? At all?

I have this table:

Time_Stamp Time_Stamp_ms
'2014-04-11 13:33:24', '879', '5555', '4444', '3333', '2222', '1111', '123', '1', 'text'
'2014-04-11 13:33:24', '899', '5555', '4444', '3333', '2222', '1111', '123', '2', 'text'
'2014-04-11 13:33:24', '919', '5555', '4444', '3333', '2222', '1111', '123', '3', 'text'
'2014-04-11 13:33:24', '939', '5555', '4444', '3333', '2222', '1111', '123', '4', 'text'
'2014-04-11 13:33:24', '959', '5555', '4444', '3333', '2222', '1111', '123', '5', 'text'
'2014-04-11 13:33:24', '979', '5555', '4444', '3333', '2222', '1111', '123', '6', 'text'
'2014-04-11 13:33:24', '999', '5555', '4444', '3333', '2222', '1111', '123', '7', 'text'
'2014-04-11 13:33:25', '19', '5555', '4444', '3333', '2222', '1111', '123', '8', 'text'
'2014-04-11 13:33:25', '39', '5555', '4444', '3333', '2222', '1111', '123', '9', 'text'
'2014-04-11 13:33:25', '59', '5555', '4444', '3333', '2222', '1111', '123', '10', 'text'
'2014-04-11 13:33:25', '79', '5555', '4444', '3333', '2222', '1111', '123', '11', 'text'
'2014-04-11 13:33:25', '99', '5555', '4444', '3333', '2222', '1111', '123', '12', 'text'

I want to get rows from one date, time and ms to another.

Now this would work great:

SELECT * FROM objects WHERE (date_field BETWEEN '2014-04-11 13:33:24' AND '2014-04-11 13:33:25')

But I need it to be more filtered out by ms....How could I do this? I tried between with the ms but it doesnt work.

I perfer something built into MySQL natively, not a third party solution which Im sure there are plenty out there.

I would like to be able to store data in various files instead of one huge one to make remote backups easier on MySQL. How can I set this up?

Thank you

For example DE07 is "2014" but it is also "32303134"

I have DE07. I need to convert it into "2014" (or 32303134 which I can convert to "2014).

How can I do this?

I have pairs of hex digits that I want to convert to char. What is the best way to do it?

Thank you

I want to get a string in hex (example: "07DE" is 2014) and convert that to a char. How can I do this in MySQL Server?

Sorry for the sloppy explanation

Thank you

Well how is the best way I can do that?

Unserealizing the BLOB byte data would be done in a program, probably just like you turned it into bytes in the first place. I don't think there is a way of doing this directly in the database, and if so, we would like to know what type of database you are using.

Well, if it couldnt be done in the database, using MySQL Workbench, why when I right click and click "Open Value in Editor" it perfectly shows me the hexadecimal numbers? It would just need to get those hexadecimal numbers shown in a select, convert it using unhex and I would have a (huge) string with the in a human readable format data I want.

Just not sure how to do it.

Im using MySQL 5.6 if that was your question.

Thank you for the reply.

I store "BYTE[]" type data into a BLOB. It stores it perfectly, the data being represented by hexadecimal pairs when I try to view it in MySQL. Now I want to convert that hexadecimal pairs into string. All the data is "simple" data such as strsing, intesger, floats, e the only thing that it is represented in hex. There is no complex data structures inside the BLOB.

What is the best way to do this?

Thank you

We have various databases on several offsite locations and we want to do a backup to here. Problem is that database is problably going to weigh GBs of data so it would be better to split it into several "files" so we can only get the updates in small amounts.

How can this be done in MySQL?

USE database;
DELIMITER $$
CREATE TRIGGER checkrow BEFORE INSERT ON table6 FOR EACH ROW
if inserted.sha1(column1) == select sha1(column1) from table6 order by id desc limit 1 then
delete inserted.column1;
end if

Best I got:

USE database;
DELIMITER $$
CREATE TRIGGER checkrow BEFORE INSERT ON table6 FOR EACH ROW
if inserted.column1 == select column1 from table6 order by id desc limit 1 then
delete inserted.column1

But after that...no clue what to do.

In MySQL server, I want to do a trigger that checks when a row inserted, if the row has the previous value in column A than the previous row, it should delete it.

Im a little forgetful on how to check this correctly so if someone could lend a hand, thank you.

I recently upgraded MySQL 5.5 to 5.6 because I needed some features but now Im not sure which my.cnf 5.6 loads...

I try editing the my.cnf in /etc with some lines such as "port=hello" but it still loads and starts correctly.

How can I see the exact route of the my.cnf it is loading? Thank you

Why does the DB server have to be on the factory floor where all the vibration is occurring? And if it is a requirement for some reason, there are ways of isolating the server by placing it on a base that will dampen the vibrations.

It has to be to comply to certain standards. And yes, if we go mechanical, there are ways to dampen the vibration like you mentioned.

I want to prepare MySQL Server 5.6 for thousands/millions of inserts. Ive found this:

•innodb_doublewrite = 0
•innodb_buffer_pool_size = 50%+ system memory
•innodb_log_file_size = 512M
•log-bin = 0
•innodb_support_xa = 0
•innodb_flush_log_at_trx_commit = 0

Is there anything else I can do?

Thank you

We plan to record/read data as fast as 10 ms (that's milliseconds) to a database. Now, this database will be in a factory so there is gonna be a lot of vibrations, temps, etc...

Logically the first choice to everyone is a SSD. The problem with the SSD is that writing/reading information THAT fast will completely destroy the SSD in no time. So that's why it better to go with a traditional one.

Ive rethought about it and I want to confirm this as being true: Writing/reading 10ms data to a database stored on a SSD would kill it in no time, right?