texts: Incorrect query filter according to the requirement

texts:
My requirement wholly depends on the QUERY.

I am filtering my gridview on the basis of three parameters.

Project_name (Always required)
Date (Not always)
Vehicle_No (Not always)

So, what my requirement is,

  With project_name either any one can be used for filtering my gridview.

Currently my debugged query works exactly fine for any two parameters.

Below is my debugged query:-

SELECT DISTINCT sv.mkey, vehicle_no,
               TO_CHAR (date_in, 'dd-MM-yyyy')
            || ' & '
            || time_in vehicleindate_time,
               TO_CHAR (date_out, 'dd-MM-yyyy')
            || ' & '
            || time_out vehicleoutdate_time,
            gate_no_in || ' & ' || gate_no_out ingate_outgateno,
            gd.good_type goods_type, net_weight netweight,
               TO_CHAR (challan_date, 'dd-MM-yyyy')
            || ' & '
            || challan_no challandate_no,
            remark_in remarkin, NULL receipt_no, date_in
       FROM xxcus.xxgid_supinv sv,
            xxcus.xx_supinv_goodtype gd,
            xxcus.xxacl_xxgid_user_mst ms
      WHERE (gd.good_type_code(+) = sv.good_type)
        AND (       sv.project_id = '1368'
                AND TO_CHAR (date_in, 'dd-MM-yyyy') = '26-11-2015'
             OR vehicle_no = '0'
            )
        AND sv.delete_flag = 'N'
   ORDER BY date_in DESC, vehicle_no

The above query shows me the exact result for project_name and Date which I entered.

But when I add the third parameter say Vehicle_no it doesn't show the data for that exact vehicle_no it still shows me the same data which I looked for 2 parameters.

I am using Oracle for the database.

LATEST UPDATE WHICH IS NOT WORKING

I am confused as how to deal with the query, as Balazs' solution was working fine when I selected, Project_name, Date and then vehicle_no (not necessary). But it was not fetching proper result when I took first as Project_name, and Vehicle_no. Any guesses why?

  The issue  is when I only filter with Project_name and Vehicle_no the date_column value comes as 0 so it doesn't show me any row, which is incorrect as I have data for that Project_name and Vehicle_no.

I have used Balaz's second query for the solution which is below

WHERE (gd.good_type_code(+) = sv.good_type)
    AND (       sv.project_id = '1368'
            AND TO_CHAR (date_in, 'dd-MM-yyyy') = '26-11-2015'
         AND vehicle_no = decode(:vehicle_no, '0', vehicle_no, :vehicle_no)
        )


texts: PowerBi Line Graph: Tooltip has different decimal places

texts:
I have a line graph showing water level data.

The graph has a tool tip which displays the values when you hover over with the mouse. See below.

My problem is that the number of decimal places displayed for Recorded Flow is different. I want them all to display with 3 decimal places.

All three values are stored in an SQL using a float datatype. The Warning and cutoff level values are a single value (which could be 1, 2, or 3 decimal places but displayed to 3 decimals), where as Recorded flow is a variable time series stored to 8-10 decimal places, but only ever displays as 2 decimal places. (and I want 3.)

I can't see anywhere that the datatype is altered or that the values have been rounded. I have removed and re-added the time-series, and changed the order of appearance but it makes no difference. I've even rounded the data in SQL to see if PowerBi was sniffing the data to determine formatting.

All web search results I've found talk about the lack of formatting control, but not that they're inconsistent or appear differently.

What am I missing?


texts: Why do temporal tables log the begin time of the transaction?

texts:
When updating a row in a temporal table the old values for the row are stored in the history table with the transaction begin time as the SysEndTime. The new values in the current table will have the transaction begin time as the SysStartTime.

SysStartTime and SysEndTime are datetime2 columns used by temporal tables to record when a row was the current version. Transaction begin time is the time the transaction containing the updates started.

BOL says:

  The times recorded in the system datetime2 columns are based on the
  begin time of the transaction itself. For example, all rows inserted
  within a single transaction will have the same UTC time recorded in
  the column corresponding to the start of the SYSTEM_TIME period.

Example: I start updating all the rows in my Orders table at 20160707 11:00:00 and the transaction takes 5 minutes to run. This creates a row in the history table for each row with SysEndTime as 20160707  11:00:00. All the rows in the current table will have a SysStartTime of 20160707  11:00:00.

If someone were to execute a query at 20160707  11:01:00 (while the update is running) they would see the old values (assuming default read committed isolation level).

But if someone was to then use the AS OF syntax to query the temporal table as it was at 20160707 11:01:00 they would see the new values because the their SysStartTime would be 20160707  11:00:00.

To me this means it doesn't show those rows as they were at that time. If it used the transaction end time the problem wouldn't exist.

Questions: Is this by design? Am I missing something?

The only reason I can think it's using the transaction begin time is that it is the only 'known' when the transaction starts. It doesn't know when the transaction will end when it starts and it would take time to apply the end time at the end which would invalidate the end time it was applying. Does this make sense?

This should allow you to recreate the issue.


texts: Setting MySQLDump to keep running in the background after session times out

texts:
I'm tryng to set up a new Slave DB by copying another Slave DB. Normally I would simply use MySQLDump to dump the Slave and create a .sql file, then import that into the new database.

However, as our new server is a Linux (Debian 7) server (the slave I'm copying is on a Windows server), I figured I would try to pipe the dump straight into the new database. This seemed to go well, until my putty session expired and it all stopped.

I tried it again, this time using nohup .... &. But still the dump stopped as soon as my session expired.

Is there a way to make this work? My full dump command is:

nohup mysqldump -hxxx -P3306 -uxxx -pxxx --dump-slave --databases a b c d --ignore-table=a.xxx -F | mysql -h127.0.0.1 -P3306 -uxxx -pxxx &

All DB's are MySQL 5.5.


texts: Service broker not getting messages when enabled

texts:
Due to lack of my knowledge in service broker i am finding hard time in troubleshooting Service broker in one of the databases.

Initially the Service broker somehow got disabled on this database running ssis packages using conversations to process messages. Still wandering how it got disabled and not getting enough info to find. please suggest where to look
when i enabled it using ALTER DATABASE DBSERB set NEW_BROKER WITH ROLLBACK IMMEDIATE, it enabled SB but seems to have clear of the messages i see in sys.conversation_endpoints. But the issue is i dont see those messages coming or starting. so i am back to my original issue of service broker not working. How can i troubleshoot and get this working

Adding more info while troubleshooting

select * from sys.conversation_endpoints shows 0 rows while for similar db in diff env i get almost 5-6 rows. Not sure what does that mean


texts: Check Constraints on SQL Server Database advisable for number range & year & month?

texts:
I'm busy designing a website and I have a requirement for the following columns in my SQL Server database:

allocation (values from 0 to 100)
year (only)
month (only)

I have read about check constraints and I'm wondering if the following would be feasible / and if it would be the best solution for the above:

allocation  | tinyint | not null | check constraint: 0-100
year        | date    | not null | check constraint: year
month       | date    | not null | check constraint: month

I will be validating from my front end side as well but I heard it's good practice to also design your DB with these restrictions in place where appropriate.


texts: Does the hash join operator always pull from the build side?

texts:
Watching queries execute with Live Query Statistics I noticed that it seems SQL Server is lazily constructing a hash table from the build input of a hash join.

This is a meaningful difference in the case of 0 probe rows. It potentially saves the entire build side tree.

I always thought a hash ran like this:

Create build-side hash table.
Match all probe rows against it.

But is it the following instead?

Pull first probe row.
Complete the operation if no row available (short-circuit).
Match all probe rows against it.

I'm not sure how to conclusively test which way it is. I'm not sure the Live Query Statistics output can be trusted in this way. Does anyone know how this works?


texts:
I am facing the following issue. I want to execute a query and export all the results in a file. My query is saved as in varchar2 type and i can execute immediate on it and give me all the results. Now these results how can i dump them all in a file. I don't want to select specific column names from the query or anything. It is like a bulk collect of many results and dump them to a file. Here is some pseudocode of what i was working on:

This is 1 way that is not going to work

Declare
filehandle1 UTL_FILE.FILE_TYPE;
myquery     varchar2(4000) := 'Select column1'||'~'||'Column2'||'~'||' from mytable';
Begin
filehandle1 := UTL_FILE.FOPEN ('MYDIR','mydata','W');
    FOR emp IN (execute immediate (myquery)) LOOP --This part of course is not working
        UTL_FILE.PUT (filehandle1, emp.*);        --i dont want to write each column
    END LOOP;                                     --i have the columns and formatting ready of the query
    UTL_FILE.FCLOSE (filehandle1);
End;

This is another way again that will not work

 Declare
    filehandle1 UTL_FILE.FILE_TYPE;
    myquery     varchar2(4000) := 'Select column1'||'~'||'Column2'||'~'||' from mytable';
    Begin
    filehandle1 := UTL_FILE.FOPEN ('MYDIR','mydata','W');
    UTL_FILE.PUT (filehandle1, myquery); --i want just to execute the query and write
    UTL_FILE.FCLOSE (filehandle1);       --everything in the file
    End;

How can i just get everything and throw them in the file? Also i this has to be done in a plsql block therefore i cannot spool them
Thanks in advance


texts:
So, I have a task to produce design to store data retrieved from sensors, like temperature, pressure, roll etc.

First prototype was quite simple, I created TimeSync table consisting of 2 columns: ID increment, and Time.
Then for each value I would create table of 2 columns: foreign key to timesync id, and value as float.

Reading back data was quite easy, I would filter time sync table from date ranges I'm interested in, and join other tables which I want to read back. The issue was disk space usage. We are quite limited on disk space, and storing 12 parameters for a year at 1Hz logging used ~100gb on Sqlite.

So we made a decision to move to postgresql and apply a little more complicated logic. Thing is, that there is quite a lot of duplicated data, let's say temperature. It doesn't change every 1s, it might chance once a minute, so there is no need to store it so often. The perfect solution would be to store first value received, then on next value check if it has changed, and if so, only then write to database.

So, that means there can be different frequencies at which values are stored, let's say a roll changes every 1s, and temperature changes every 60s.
Now my issue is how to combined that data into single query.

My design so far is to store when device was online, and when it was offline. This will provide clues later on how to do proper filtering.

Next, to store each value in different table, consisting of 2 columns: Time and value itself.

So, some examples, activity table looks like:

ID  DevID   Online              Offline
1   1       2017-01-16 16:13:46 2017-01-16 16:24:38
13  1       2017-01-16 16:32:51 2017-01-16 16:42:16

and data tables:
Data Table1:

Time                Value
2017-01-16 16:13:59 18.9
2017-01-16 16:14:20 17.9
2017-01-16 16:15:08 19.9

Data Table2:

Time                Value
2017-01-16 16:13:57 348
2017-01-16 16:14:05 350
2017-01-16 16:14:17 353

I'm using generate_series from postgresql, and it looks like it is what I need:

select *
--This will generate a series for specified range at specified interval
from generate_series(
                 '2017-01-16 16:10' :: timestamp,
                 '2017-01-16 19:00' :: timestamp,
                 '1 second' :: interval) as date
    --Joining one data table
    left outer join
    (select
         date_trunc('second', data.device_2_p_3.timestamp) as val1time,
         avg(value)                                        as val1avg
     from data.device_2_p_3
     group by val1time) results on (date = results.val1time)
    --Joining other data table
    left outer join
    (select
         date_trunc('second', data.device_1_p_1.timestamp) as val2time,
         avg(value)                                        as val2avg
     from data.device_1_p_1
     group by val2time) results2 on (date = results2.val2time)

order by date asc;

And the readback is:

date    val1time    val1avg val2time    val2avg
14:00.0 null        null    null        null
14:01.0 null        null    14:01.0     349
14:02.0 14:02.0     18.8    null        null
14:03.0 null        null    14:03.0     349.5
14:04.0 null        null    null        null

The issue is that I'm not able to interpolate or repeat data from previous value if device was active at that point. Any clues how to solve this, or suggestions how to improve design would be highly appreciated.


texts: Does MySQL's MyISAM use cache before insert?

texts:
I want to test two different disks, one of them is HDD and another is SSD.

I use exactly the same information and app for both servers. They are exactly the same, just one of them is SSD and another is HARD.

When I run a simple script that inserts 1,000,000 rows into the database they take a similar time to run. Sometimes HARD is faster!

I want to know does MySQL's MyISAM use any caching, so I can get the same time to insert or I have same disk?

If they use cache before insert how can I disable that to test disk speed?


texts: Azure SQL Database Pricing: Standard vs Premium tier

texts:
I was checking pricing for Standard tier and Premium tier concerning Azure SQL Database and I am a bit confused:
Knowing Standard tier has 1-4 IOPS per DTU and Premium tier has 25 IOPS per DTU (doc), why should I choose a Standard 6 (S6) plan with 400 DTU instead of a Premium 1 (P1) with 125 DTU?
S6 costs 506 euro/month, while P1 costs 392 euro/month and doing calculations:
S6: 506 euro/month | 400 DTU * 4 IOPS = 1600 IOPS
P1: 392 euro/month | 125 DTU * 25 IOPS = 3125 IOPS
Moreover P1 has other interesting features such as Columnstore indexing, In memory OLTP and 2ms IO latency.
Apparently it seems that P1 is way better than S6, even though costing less.


texts: memory errors on startup in postgresql 9.6 log - MAP_HUGETLB failed

texts:
On postgresql startup, I see this in the log files

  2017-07-17 05:21:01.743 UTC  > LOCATION:  CreateAnonymousSegment, pg_shmem.c:484
  2017-07-17 05:21:01.868 UTC  > LOG:  00000: redirecting log output to logging collector process
  2017-07-17 05:21:01.868 UTC  > HINT:  Future log output will appear in directory "pg_log".
  2017-07-17 05:21:01.868 UTC  > LOCATION:  SysLogger_Start, syslogger.c:622
  2017-07-21 05:46:01.680 UTC  > DEBUG:  00000: logger shutting down
  2017-07-21 05:46:01.680 UTC  > LOCATION:  SysLoggerMain, syslogger.c:490
  2017-07-21 05:46:01.973 UTC  > DEBUG:  00000: mmap(8264876032) with MAP_HUGETLB failed, huge pages disabled: Cannot allocate memory

I have never used huge pages - turning on huge_pages in postgres.conf does not help, I get different errors and the server will not start. I am unfamiliar with these settings.

Please help point me in the right direction to explore why these errors are occurring and what I should do about them - adjustments to memory settings, logging (it is set very high) vacuuming, or what?

What parts of the PostgreSQL manual are relevant?

Stack:  postgresql 9.6 on aws ec2 generic linux

UPDATE:  This post was originally written about things I noticed on installing a backup utility, and how that appeared to be causing errors, then I noticed a server without the utility is logging the same messages.


texts: What data is causing an NVARCHAR/DATETIME conversion error?

texts:
Application is getting this issue :
java.sql.SQLException: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value."

Back end SQL Server 2014. How can i resolve this issue?


texts: Counting unique records across 2 columns and computing a score based on boolean values

texts:
I have a table that looks like this in Google Big Query:

custID  InteractionDate Purchased?  Sales
1       20150312           F         0
1       20150312           T         200
1       20150314           T         150
2       20150221           T         400
2       20150221           F         0
2       20150221           T         120

..that I need to transform to the following structure:

 custID InteractionDate Success Sales
 1     20150312          0.5    200
 1     20150314           1     150
 2     20150221         0.66    520

..where in this second table the success column is defined as

count(purchase=Yes)/(count(purchase=Yes) + count(purchase=No)),

and sales is just the sum.

I'm quite new to BQ and no-SQL languages. I simply don't know how to do this, I've searched and seen examples that suggest using partition over but I simply haven't seen any example that does just this. Thanks a lot in advance.


texts: Is there an efficient way to match multiples headers that have the same set of details?

texts:
Here is an example of what I'm asking:

Detail Table:

HeaderId | DetailId
    1          100
    1          101
    2          100
    2          101
    3          101
    3          102
    3          103

I'm looking for a query strategy that will get me all header ids with the same set of detail ids for each.

So in the example, I would want HeaderId 1 and 2 to join together because they have the same two detail records, but 103 would not match because it has a third item in the set.

The strategy I'm taking so far is to use the STUFF technique to create a comma separate string of detail values, checksum that string, then join on the checksum result. It's seems to be working, but I'm not sure how to optimize it. Over a set of about 7000 headers, it returns in about 6-7 seconds.

Here is the query:

with Details as
(
    select distinct t2.HeaderId,
    checksum(stuff((
        select
            ',' + convert(varchar(15), t2.DetailId)
        from
            DetailTable t2
        where
            t2.HeaderId = t2.HeaderId
        for xml path('')
    ),1,1,'')) as ChkSum
from
      DetailTable t1
)
select
    *
from
    Details t1
        join Details t2
            on t2.ChkSum = t1.ChkSum
            and t2.HeaderId <> t1.HeaderId -- To avoid matching the same record

So - is this the right approach? And if it is, how can I optimize? Query plan doesn't have anything jumping out at me. The most weight is given to a table spool. Also, I'm trying to make this a function or proc if that helps.

Edit: I began researching relational division, and I think that is relevant here, but perhaps not in the context I am thinking. To give this more context, here is the business case I am trying to solve.

I have a set of Promotions that can have any number of UPCs in them. I'm trying to find promotions that have exactly the same set of UPCs in them. A lot of the solutions I'm seeing rely on using count(*). So - just some context for anyone looking at this. Thanks!


texts:
I am very new to any type of tuning but have been learning more.. One of my specific databases one of the problems identified with slow queries are a number of Implicit conversions. It is my understand that when 2 non like column types are compared SQL Server has to convert one to the other, there is listing of priorities that tell it how to convert - Since the data type is converted, its possible that it will not use indexes correctly.. One of the first queries identified is a very basic one, and I would like to see if someone could help me identify what I'm seeing on my execution plan...

The basic query is:

(
@1 TINYINT
,@2 VARCHAR(8000)
,@3 NUMERIC(4, 0)
)
SELECT CONVERT([float], [low] / @3, 0)
FROM [master].[dbo].[spt_values]
WHERE [number] = @1
AND [type] = @2

The Execution plan shows:

Detail on the clustered index seek

spt_vaules.Type is Defined as nchar(3)

The Seek Predicates section is confusing me, exactly which part of this is it converting... My query says [low] is being converted from int to numeric but that doesn't seem to be what the cluster index seek details is showing, is it?

Is there a way to tell in this specific example how much "Better" it would be if I did the conversions Explicitly?  I would think I could just do a "cast" in the query and insert a few numbers where the variables are, is that correct?


texts: Add Constraint to check value exists in a non-unique composite key column

texts:
I have the following table, which has a composite primary key:

CREATE TABLE dbo.Seqs(
    SequenceKey nvarchar(10) NOT NULL,
    UsageSequence smallint NOT NULL,
    col1 bit NOT NULL,
    col2 nvarchar(30) NULL,
    CONSTRAINT PK_Seqs PRIMARY KEY CLUSTERED
    (
        SequenceKey ASC,
        UsageSequence ASC
    )
)

I'm looking to create the following table, with a 'SequenceKey' column that must only accept values that are present in the 'Seqs' table:

CREATE TABLE dbo.Sometbl (
    SomeID int IDENTITY(1,1) NOT NULL,
    Col1 int NOT NULL,
    Col2 nvarchar(100) NOT NULL,
    Col3 nvarchar(30) NULL,
    SequenceKey nvarchar(10) NOT NULL,
    CONSTRAINT PK_Sometbl PRIMARY KEY CLUSTERED (
        SomeID ASC
        )
    )

Given that the 'SequenceKey' in the 'Seqs' table is not unique (the primary key uniqueness is reliant on the 'UsageSequence' column), what is the best way to ensure data cannot be added or updated in 'Sometbl' with a 'SequenceKey' value that doesn't exist in the 'Seqs' table?


texts:
If I set the jdbc isolation level to read uncommitted in SQL Server, will SQL Server ever lock any tables, pages, rows etc?

Thanks.


texts: How can a key lookup with zero executions and zero actual rows take time?

texts:
I have a query that is taking about 10 seconds to run and I am trying to optimize it.

All the time is spent in the key lookup which is fine however in this case if I mouse over the key lookup in the actual execution plan it says

Expected Rows : 1
Estimated Executions : 3341
Actual Rows : 0
Executions : 0

I understand in this case the statistics are expecting the index seek to find rows which is fine but as no rows were returned in the Index Seek why would the key lookup take any time? or even be shown in the query plan at all?


texts: Checking vulnerabilities for Oracle Data Integrator version 12.2.1.3.0

texts:
I understand October 2020 Oracle patches included security updates for Oracle Data Integrator (ODI) version 12.2.1.3.0.
The application admin who is managing ODI for us is really new and used to be with Windows desktop support. He is willing to help but needs guidance on what to look for.

How can we determine if the Oracle Data Integrator version 12.2.1.3.0 we are running is vulnerable to CVE 2020-5398 or not? The application admin ran a Qualys scan and it did not detect vulnerability for CVE 2020-5398 but an auditor is going to be visiting us soon so we need another confirmation that we are not impacted for CVE 2020-5398. The application admin asked people he knew and did not hear that anyone was using Spring framework whose vulnerability is fixed in CVE 2020-5398 and did not hear anything but since he is new, he is not sure if CVE 2020-5398 affects us or not.

Is there any tool/script by Oracle by which we can definitively know what vulnerabilities affect us for the Oracle Data Integrator version 12.2.1.3.0?

3.How can we ensure that we are patched for Oracle released updates and did not miss anything? We use Qualys application to scan our systems for vulnerabilities but our auditor needs a second confirmation.


texts: PostgreSQL 9.2 number of days in a tstzrange?

texts:
How can the number of days contained within a range be found?

For example, with these timestamp ranges, get these (integer) number of days:

tstzrange('2013-10-01 07:00', '2013-10-01 07:15') | 1 (day)
tstzrange('2013-10-01 07:00', '2013-10-01 23:45') | 1 (day)
tstzrange('2013-10-01 02:00', '2013-10-02 23:45') | 2 (days)
tstzrange('2013-10-01 07:00', '2013-10-03 01:00') | 2 (days)
tstzrange('2013-10-01 01:00', '2013-10-03 23:00') | 3 (days)
tstzrange('2013-10-01 23:00', '2013-10-04 01:00') | 4 (days)

In my digging about, I've not found a built in function for this.

Is is necessary pull out the lower() and upper() timestamp elements, then get the date interval between them?


texts: What is the y-axis of this Oracle ASH Viewer?

texts:
I am investigating different monitoring software for my database, and I stumbled upon this open source ASH Viewer. I noticed in the main graph on the y-axis for resource usage, it's labeled as "Active Sessions" with an axis of varying integers

Link: https://github.com/akardapolov/ASH-Viewer#how-it-works

I don't understand what "Active Sessions" is supposed to mean. Is this the CPU % utilization of the database?


texts: T-SQL Create Query - Compare and Calculate

texts:
I got a mini fantasy league (NBA) and would like some help with a query that turns averages into "ranking points".

Basically I got two tables.

Source table:
This is a table with statistical per game averages for a category (category = column).

Destination table or result set:
This is a points table. If I have the highest average in a category I get maximum points which is same as number of teams.
So if I have 10 teams and I have the highest average in 3PM category I get 10 points, and so on down to 1 point for the worst team in the category.
Last column is a total of all points per team.

The help I need:
I would like to create a query that compares every teams value in a given column/category in the source table, ranks the results and gives the right amount of points. When finished it adds up the points per team to the Total PTS table.

Appreciate it if someone would help me or show me the path to fantasy glory.


texts: Broken mysql replication on slave server (error 1236)

texts:
My question is: how to fix broken mysql replication on slave server (error 1236)?

server 1 (192.168.1.105)
server 2 (192.168.1.106)

I ran these steps to re-sync:

At the master - server 1 (192.168.1.105):

mysql -u root -p

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
Note > File: mysql-bin.000001 and Position: 107
mysqldump -uroot -p --events --ignore-table=mysql.events --all-databases > /var/backups/mysqldump.sql
UNLOCK TABLES;
Copy mysql dump file to server 2
(scp -p /var/backups/mysqldump.sql root@192.168.1.106:/tmp)

At the slave server 1 (192.168.1.106):

STOP SLAVE;
mysql -uroot -p < /tmp/mysqldump.sql
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST='192.168.1.105', MASTER_USER='slaveuser', MASTER_PASSWORD='mypassword';
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
START SLAVE;

I still got this error on server 1

  Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'


texts: Change 'root' Password forMySQL but Change Lost after Reboot

texts:
One of my people was asked to install a standalone MySQL on his laptop. After doing this with the Synaptic Package Manager he was unable to login to MySQL. He then claimed he was not offered the option to set a root password during the install (not sure whether to believe this).

After I reset the password everything worked fine until he rebooted. It then seemed to reverted to the original(unknown) password or something else. I have been through this loop three times and cannot understand why this is. After resetting the password I have executed 'flush privileges;' before stopping and starting the server. Everything then works fine until it is rebooted! I am at a loss what to try next bar a complete reinstall...

The laptop is running Mint 17.3 64-bit.


texts: Do repeated columns affect normalization?

texts:
For example these tables contain many repeated columns.

Does this affect normalization or this is a common practice? Would it be better to create a separate 1 to 1 table like this.

This is 1 to 0..1 relationship, not 1 to many.


texts:
I have a master/master setup with one DB in each. DB name and tables in one server are different from the other, and few 'user' tables are being replicated with the option replicate-do-table. (It's amazing how devs chose to sync the tables this way)

Is there a way to check what type of replication options were used? In specific I want to know if replicate-rewrite-db option was used. I checked all my.cnf file and I don't see it, and this info is not in global variables.


texts: Can Select but not Insert from SQL Server to MySQL

texts:
I'm having a problem with inserting data from SQL Server to MySQL. I saw this syntax that helped others:
EXEC master.dbo.sp_addlinkedserver @server='MYSQL', @srvproduct='MySQL',
@provider='MSDASQL', @provstr='DRIVER={MySQL ODBC 5.1
Driver};SERVER=HOST;Port=3306;USER=uid;PASSWORD=pw;OPTION=3;DATABASE=mydb;

but the error is still:

OLE DB provider "MSDASQL" for linked server "MYSQL1" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.5.44-MariaDB-log]Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.".
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "MSDASQL" for linked server "MYSQL1" could not INSERT INTO table "[MSDASQL]".

It says here that the BINLOG_FORMAT is equal to STATEMENT but when I checked it, it is set to MIXED.
Found out a solution for these, they said that the BINLOG_FORMAT must be ROW but then I'm having a doubt or difficulty to edit in my.cnf because I don't know what will happen if I changed the binlog format from mixed to row.
How can I resolve this problem?


texts: WITH position unexpected position using Mysql 8.0 Date Generation

texts:
I'm attempting to use the Date Generation Example to generate my own date range but I can't get the query to run.  The query fails and Workbench shows an error indicating my syntax is invalid.

Am I overlooking/misunderstanding something about how this can be used?  I can't use stored procedures so I hope this isn't limited to being used in that context.

use mydb;

WITH RECURSIVE dates (date) AS
(
  SELECT date + INTERVAL 1 DAY FROM dates
  WHERE DAYOFWEEK(date) = 1 AND
    date >= DATE_SUB(NOW(), INTERVAL 1 YEAR);
)
SELECT * FROM dates;


texts: Having Non-Numeric values as measures in Fact table

texts:
According to my research regarding having non-numeric value as measures, I found that having non-numeric won't be an optimal one.

So I planned to maintain numeric values. My scenario was
"Employee's Attendance" and the measure may be "Casual Leave", "Sick Leave", "UnInformed Leave", "Comp off".

Is that I need to maintain column for all of these (4 measures) and will be updating based on the corresponding user's status. If I maintain like that means if a new one added means it will be like structure of the cube getting changed.

Is that will be a good one or any optimal solution available?


texts:
I've got a simple theoretical question...

If we discuss the principles, elements, design and implementation of a typical ETL flow for a dimension table then, in my understanding, (in short) I'd say something like : first data is extracted from source system, then transformation is applied (so the data maps the Dimensional Model) and then it is loaded into the dimension entities. With a Type-2 change method, each entry in the dimension is simply the data (description) of the entity.

Now, how can you describe the very same things for the Fact tables? I am confused in here... I know that the Fact tables contain Partial Primary Keys of the dimensions connected to it, and also some attributes that summarize some data. But what do the Fact tables contain as a general understanding? And how can you explain the ETL flow for a Fact table. Please, provide me with some simplified explanation, perhaps your own words, of how you understand it...


texts: why do I have three copies of data/log files per database on same server instance

texts:
OS: Windows 7 Professional

Database: SQL Server 2008 R2 Standard Edition

Just detected by SpaceMonger that, each of my databases on test instance has three copies of data files (.mdf) and log files (.ldf).

they are located at three folders:

..\DataFiles\Test
..\LogFiles\Test
..\Microsoft SQL Server\Mssql10_50.test\Mssql\Data

and they have exactly same file size and timestamp.

I confirmed that I have no mirroring setup for any of my databases, neither do I have any special setup to enforce the database to have files splitted.

The only suspicious setting I can find is:

the first two locations are the server level database default location (right click server in the MSSMS -> Properties -> Database Settings -> Database default locations): the first is for Data while the second is for Log,
and the last is each database's file location appearing in the MSSMS (right click a database -> Properties -> Files -> Database Files)

This annoying problem has made my server available disk space very low and forced me to do housekeeping very often.

any idea why does this happen?

any methodology i can follow to find out what has been wrong?

-- EDIT ---

Thank you all for the comments and reply.

We have identified the problem - not related to the sql database setting, but the folders themselves: the three folders have a setup to mirror each other. we have got our network team to investigate why it's so.


texts: Redshift table not showing up in tables for schema?

texts:
On Redshift, why doesn't my table show up in the following query? It definitely exists, as shown by the next query I run. I want a way to list all tables for a schema:

mydb=# select distinct(tablename) from pg_table_def where schemaname = 'db';
 tablename
-----------
(0 rows)

mydb=# \d db.some_table
                    Table "db.some_table"
     Column      |            Type             | Modifiers
-----------------+-----------------------------+-----------
...correct info shows up here...
...but nothing showed up above?


texts: sort a large table by its primary key and save it back

texts:
So I have a large table (>50 million rows) with an auto incremented primary key column, which is filled by a script running on multiple cores. Now, when I do a SELECT, entries are returned 'out of order', which, I guess, is the result of the table being filled by several processes in parallel.

I could do a SELECT ... ORDER BY <primary key>, but this select takes much more time than the simple SELECT.

Is there a way to sort the table by its primary key and save it back, without loading all rows into memory? I found a similar question answered here, but I cannot apply it to my case without adding another column, which, given the size of the table, I'm reluctant to.

I'm Running Firebird Superserver

Any advice appreciated.


texts: stored procedures location influence their performance?

texts:
I have a procedure SP_MYPRO that updates data in MYDB1.

1) the performance of SP_MYPRO would be different if I place it in MYDB2 instead of MYDB1?

2) what if the procedure SP_MYPRO only read data, does not update anything?


texts: Find out which columns must be specified in insert-statement

texts:
Sometimes when inserting data in a table with many columns it could be useful to know which columns must be specified if the insert-statement shouldn't fail.

I wrote this query to find out which columns are not nullable, identity, computed, timestamp and have no default value.

select  *
from    sys.columns
where   object_id = object_id('<table>') and
        is_nullable = 0 and
        is_identity = 0 and
        is_computed = 0 and
        default_object_id = 0 and
        type_name(system_type_id) <> 'timestamp'

Are there any other properties that should be included in this check?


texts: Issue with CAST using linked servers (possible bug?)

texts:
So I've got a some SQL Server 2008 DBs arranged in a star schema.  Server A is the one users submit queries to.  On Server A there is a DPV called Detection that's basically a UNION of table Detection on servers X, Y and Z.

One of the columns in Detection is a BIGINT called infoFlag that is basically a bitmask.  Users need to use bitwise operators to filter their queries on this mask.  This works fine in situation such as this:

    SELECT TOP 500 colA colB from Detection
    WHERE (infoFlag & 16 <> 0)

But if you use a mask that is too big for an INT, for example:

    SELECT TOP 500 colA, colB
    FROM Detection
    WHERE (infoFlag & 3758333096 <> 0)

we get an error:

    System.Exception: The data types bigint and numeric are incompatible in the '&' operator.

Fair enough.  A simple cast should fix the problem, right?  It should, but it doesn't.

    SELECT TOP 500 colA, colB
    FROM Detection
    WHERE (infoFlag & CAST(3758333096 as BIGINT) <> 0)

gives the same error.

While troubleshooting this, I ran the query directly on the Detection table on Server X and it worked fine (with the cast.) So I figured that the problem had something to do with the linked servers.

So I fired up profiler on Server X and submitted the query against the Detection DPV on Server A.  Here is what came through:

    SELECT TOP (5000) "Tbl1002"."colA" "Col1008","Tbl1002"."colB" "Col1009"
    FROM "myDbName"."dbo"."Detection" "Tbl1002"
    WHERE ("Tbl1002"."infoFlag"&(3758333096))<>(0)

It seems that the CAST statement is not getting propogated to the remote server!

I did figure out a workaround, which is to do this:

    declare @a bigint
    set @a = 3758333096

    SELECT TOP 500 colA, colB
    FROM Detection
    WHERE (infoFlag & @a <>  0)

However I am concerned that this might be a bug.  Or is there something I'm missing?

thank you!

--suz (@capnsue)

Version: Microsoft SQL Server 2008 (SP1) - 10.0.2710.0 (X64)   Apr  7 2009 20:36:04   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

EDIT: Here is the definition of the Detection table.   http://pastebin.com/ErGJ9fSG


texts: Return all rows with a certain value in jsonb dictionary in Postgres

texts:
Let's say I've got a table like this where name is just a string and dicts is jsonb.

id  | name  |                         dicts
----+------+-----------------------------------------------------------
  1 | "bob"| {"food": "pasta", "exercise": "walking"}
  2 | "joe"| {"vehicle": "car"}
  3 |"john"| {"hobbies": "walking"}

And I want to return all rows that contain "walking" as a value within the key:value pairs in the dicts column. So in this case, I'd want to return the "bob" and "john" rows since "walking" appears as values in both rows. The number of key:value pairs in each jsonb dict can vary and the keys can also vary.

There's this command

select jsonb_object_keys(column) from table;

that at least gets me all the possible keys represented in a particular column, but that's the only lead I've got so far.


texts:
I am doing an application called as Invoice Application.For that I have my database is like this.

Now my problem comes when I am taking the tax part in my database.As I have made tax as an option where user can add one or two different taxes in an invoice form.Also I have taken total in invoice_items table to do calculations.So is this database is fine for doing all the calculation or I have to take another extra table for doing calculation and all?Any help regarding to make the database design simpler will be more appreciable.

Update

Here is the link for another question little bit similar.So can this question be solved like this?


texts: I'm trying to implement Change Data Capture in MySQL. I have seen that using Maxwell I can implement CDC but I'm confused where to start. Please help me with the detailed steps to implement it. And also tell me the prerequisites.


texts: Best Practices for Testing Application Dashboard Data

texts:
We have a dashboard that contains metrics based on a 6 month reporting period.  Users log in and see their metrics based on the current 6-month reporting period.

Every 6 months, we load new metrics in our DEV/QA environment and configure them for the upcoming reporting period so we can test. But, our application and ETL use GETDATE(), so for the ETL to calculate properly, we have been setting the server's DATE in the future so everything will calculate and display as if we were in the future.

  Our DBA group is now telling us that this should not be done and no
  one else in the industry does this.  Can someone give me some examples
  of what they have done for testing in this situation?  Should we be
  looking to re-design our ETL and application and what are some ideas
  for best practices?

We have unit testing for the application, so it will work.  We are more worried that the data from the ETL will not be correct on Day 1 of the new period.  It is very important that the metrics are displaying properly and the data is correct on Day 1.


texts: Using mariabackup/xtrabackup vs block level snapshots

texts:
I am running a mariadb 10.1 instance with all innodb tables and a replication slave in a k8s cluster on Google Cloud's GKE. I have a scheduled mysqldump that runs every night. But for additional security, I am working on a mariabackup implementation that will run on the slave pod. Ideally this would create an incremental backup on an hourly basis. The database is not that large, but growing, around 20GB.

But as I work out the logistics of exporting the backup, whether to external object store or just a separate disk, I am wondering if GCP's own snapshot feature would not be an easier and cheaper solution than using mariabackup at all.

My understanding of mariabackup is that it makes an un-modified copy of the underlying database files. The tables are not necessarily consistent, so upon restore, you must --prepare the files to make them consistent. You can then copy them over to the live folder and restart the instance. - https://mariadb.com/kb/en/library/how-mariabackup-works/

This leaves me with base copies and separate folders for incremental copies that I supposed I could send directly to object storage. Then of course write a restore script that can pull in and prepare the base backup and each incremental backup individually up to the point I need to restore.

The other option is simply to schedule an hourly snapshot in GCP. It takes an incremental snapshot and can be restored to a disk at any given point.

Am I missing anything? GCP seems to indicate that their snapshots are close to consistent, but in a write heavy environment, they won't be perfect. Would this be likely to cause problems? Can innodb recover from small inconsistencies? And am I missing out on anything else by using snapshots instead of something like mariabackup?


texts: Cannot set MEMORY_TARGET for Oracle 11.2

texts:
I'm trying to configure an Oracle installation on a developer machine (Windows 7 Professional 64bit).

The machine has 16GB of RAM and I'd like to configure MEMORY_MAX_TARGET to 8g but have MEMORY_TARGET set to 4g by default because that is enough for most of the work done on that machine. But in case I need more memory I can dynamically resize it.

However Oracle won't let  me set MEMORY_TARGET to 4G:

Here is my current configuration:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 8G
memory_target                        big integer 0
parallel_servers_target              integer     64
pga_aggregate_target                 big integer 250M
sga_target                           big integer 0

SQL> show parameter policy

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy               string      MANUAL
workarea_size_policy                 string      AUTO

Now when I try to run

SQL> ALTER SYSTEM SET MEMORY_TARGET=4G scope=both;
ALTER SYSTEM SET MEMORY_TARGET=4G scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least
6432M

I don't understand why MEMORY_TARGET can't be set to 4G? From all I know, it cannot exceed memory_max_target, but 4G is clearly less than 8G (and where do the 6432M come from?)

According to the manual this should be possible. Additionally I wasn't able to set PGA_AGGREGATE_TARGET to zero though - even if the manual claims that this is possible.

What am I missing here?


texts: Find the median of a calculated field in SSRS 2012

texts:
I have a start date and an end date, and am calculating the weekdays in between with the following:

I created a calcuated field called CountWeekDays, and it equals: Code.getBusinessDaysCount(Fields!date_created.Value,Fields!date_closed.Value)

I can get an Average of that like this: =Avg(Fields!CountWeekDays.Value)

However, I cannot get the Median the same way. How can I get the median number of something that is calculated?

The code I am using to get the weekday count is as follows:

Function getBusinessDaysCount(ByVal tFrom As Date, ByVal tTo As Date) As Integer
Dim tCount As Integer
Dim tProcessDate As Date = tFrom
For x as Integer= 1 To DateDiff(DateInterval.Day, tFrom, tTo) + 1
  If Not (tProcessDate.DayOfWeek = DayOfWeek.Saturday Or tProcessDate.DayOfWeek = DayOfWeek.Sunday) Then
    tCount = tCount + 1
  End If
  tProcessDate = DateAdd(DateInterval.Day, 1, tProcessDate)
Next
Return tCount

End Function


texts: Creating SQL Server Job on each replica in availability group

texts:
I have an availability group with 2 nodes (one primary and another secondary). The availability database has a stored procedure which is used to create a SQL Server job dynamically, based on user input. With this the jobs are always created only on the primary server. How can I create the same job on each replica in the availability group?

The listener redirects to primary server and the stored procedure on the primary server creates the job on the primary server only. I want to know if there is a way to modify the procedure on the primary server so that it can create jobs on other replicas in the availability group? While running the job, I will put a simple check, so that the job will run only on the current primary server only.


texts: Using MAX text or more specific, smaller type

texts:
Someone was reviewing my DDL code for creating tables and suggested, when they saw I saw using VARCHAR(256) fields for text I expect to be pretty small, like a first name or whatever, that I should always just use VARCHAR(MAX) and linked Why use anything but varchar(max). I read it but it seemed dated, as it was focusing on 2005, and didn't seem to offer any real justification to allocate potentially up to 2 GB per row on all text fields.

From a performance, storage, etc. standpoint, how should one go about deciding whether to use VARCHAR(MAX) or a smaller more specific type for modern versions of SQL Server? (e.g., 2008, 2012, 2014)


texts: Huge size on mongodb's gridfs. Should I compact?

texts:
I’m running MongoDB and grid.fs to store small files (less than 20mbs). These are part of a replica set. I currently have more than 350000 files stored.

I’ve noticed that the chunks collection takes around 700GB of preallocated space where the actual chunks consist of ~40GB. Even though there are 700GB of data preallocated, this keeps expanding over time.

Keep in mind that every 15 minutes or so I delete files older than 5 days. So in theory my fs.chunks and fs.files size should remain around the same over time.

Here's my fs.chunks stats

rs0:PRIMARY> db.fs.chunks.stats()
{
    "ns" : "collection.fs.chunks",
    "count" : 470388,
    "size" : 43295062144,
    "avgObjSize" : 92041.17057407927,
    "storageSize" : 757794040352,
    "numExtents" : 373,
    "nindexes" : 2,
    "lastExtentSize" : 2146426864,
    "paddingFactor" : 1,
    "systemFlags" : 1,
    "userFlags" : 0,
    "totalIndexSize" : 40356736,
    "indexSizes" : {
        "_id_" : 17431232,
        "files_id_1_n_1" : 22925504
    },
    "ok" : 1
}

Is this behaviour normal? Can I compact (defrag?) the chunks collection or even claim that preallocated space ? If I cannot reclaim that space (which I’m 99.9% sure I can’t) is there a way to ensure that the preallocated space will be used eventually rather than keeps expanding?


texts: Distinct in one column where another column has same values

texts:
I have a table sample

  id   |  name  | type
-------+--------+------
 PR001 | DC001  |    a
 PR002 | DC001  |    a
 PR003 | DC001  |    b
 PR004 | DC002  |    a
 PR005 | DC003  |    a
 PR006 | DC003  |    a
 PR007 | DC004  |    b
 PR008 | DC005  |    c

Is there a way to display distinct for column name only if where column type should be all the same value ('a')

output I want to achieve

 name   | type
---------+------
 DC002  |    a
 DC003  |    a

Any help would be greatly appreciated


texts: What means execute S_2, S_3 and S_4: COMMIT in Postgresql logs?

texts:
I'd noticed different commit types in Postgres log:

2019-12-20 12:00:00  [99999]: [12-1] host=...,user=...,db=... LOG:  duration: 1000.000 ms execute S_2: COMMIT
2019-12-20 12:00:00  [99999]: [12-1] host=...,user=...,db=... LOG:  duration: 1000.000 ms execute S_3: COMMIT
2019-12-20 12:00:00  [99999]: [12-1] host=...,user=...,db=... LOG:  duration: 1000.000 ms execute S_4: COMMIT

Note: I changed some information to omit sensitive data.

What can I conclude when I see these different types of commit in the log?


texts: sp_start_job only running 20 times concurrently - can I adjust?

texts:
I have a procedure that has a loop that calls sp_start_job to run a query against multiple linked servers.

Each run of the loop calls a job that queries a specific linked server. (An openquery that inserts the results into a table on the local server).

However when running the procedure and looking at sp_who2, it appears it's only running the procedure against 20 remote server at a time.

Where can I control how many servers it attempts to connect to at any give time? I'd like to tweak this to get maximum speed without over loading the server.

I have max worker threads set to 300 and max number of concurrent connections set to unlimited.

The procedure that runs:

 CREATE PROCEDURE [dbo].[RunStoreQuery]
  @name AS NVARCHAR(40)
AS
  DECLARE @row AS INT,
    @maxrow AS    INT,
    @store AS     NVARCHAR(8),
    @sj           NVARCHAR(40),
    @jobs         NVARCHAR(40)
  SELECT @maxrow = Max(row#)
  FROM   (
                  SELECT   row_number()OVER(ORDER BY try_cast(s.NAME as int) ASC) AS row#
                  FROM     sys.servers s
                  WHERE    LEFT(s.data_source,2) = '10') s
  SET @row =1
  WHILE @row <= @maxrow
  BEGIN
    SET @jobs = @name
    SET @store =
    (
           SELECT s.named
           FROM   (
                         SELECT named
                         FROM   (
                                         SELECT   row_number()OVER(ORDER BY try_cast(s.NAME as int) ASC) AS row#,
                                                  ('['+ s.NAME +']') AS                                     named,
                                                  try_cast(s.NAME AS int) AS                                intnum
                                         FROM     sys.servers s
                                         WHERE    LEFT(s.data_source,2) = '10') t
                         WHERE  t.row# = @row) s)
    SET @jobs= @jobs+@store
    SET @store=N''''+@store+N''''
    EXEC msdb.dbo.sp_start_job
      @jobs
    SET @row = @row + 1
  END


texts: DB2 - Archive Mode - on what values to set LOGPRIMARY,LOGSECOND,LOGFILSIZ?

texts:
we must set our database from CIRCULAR to ARCHIVE mode in order to enable ONLINE backups.
Backups will be done every 1 day.
Database is now almost 30GB.
Database grows approximately  1GB per month.
For online backup we will use Veritas NetBackup so we use its library file in the LOGARCHMETH1 parameter for the VENDOR option.
Time to restore is not so important parameter, it is important to everything restore from previous day when everything was OK.

Can you please assist me to set those three parameters on appropriate value? This is production database with important data.

LOGPRIMARY and LOGSECOND are set to values 13 and 15 by default but I think it is not enough? Is it to large if I set them to 50 for example? What can be negative effect?

Current values are: 13, 15, 4096.
I run Configuration Advisor and it gives me those values: 27, 7, 1024.
But for many wizard questions I used default values because I do not know exact answers (for example Transactions per minute,or Average number of connected remote applications..)
Why it put the lower value for LOGFILSIZ?

Can someone additionally suggest me are those good values or not ?
Thank you!!!!


texts: intel p3600 SSD no increase in read speed - postgres rdbms vs hdd

texts:
We run a custom application that produces analyzable data-sets for medical research.

the amount of data we have is about 600GB in form 3Billion rows distributed in 600+ tables it is hosted in postgres RDBMS and data is indexed and clustered.

Our application is read intensive(read only).

Usually it took about 7-8 hours on a normal computer running on a HDD to go through all this data and produce a dataset.

Recently we brought a Highend machine (intel i5 5960x, 32GB ram with an intel p3600 2TB SSD) and set up the database and our application.

I was amazed to find out it took almost the same amount of time that it used to take on the old machine to extract a dataset.

EDIT 1 : How the program works

The program is a Java Application

There are about 13Million patient's records in the database.
and each patient has 10000+ entries about their health/diagnosis/prescriptions etc. Hence the massive size.

What we do is look for patients with particular medical conditions(defined in a particular table) and pull out their medical history and other events(from different tables) from the database and do research on it.

The program is not CPU bound as we have very simple logic behind the program

I create a lot of java objects(at least 1 object per patient, which in turn is made up of lot of strings which hold values I pull out of database), hence we can say it is a bit memory intensive

Yes it is harddrive intensive as we read Billions of records, and that is why I though if i have a really fast harddrive I can get this running very fast. And hence brought a Intel P3600 2TB SSD

SAMPLE QUERY

These kind of queries are run on tables with 100,000+ of records for each patient 4-5 times per program execution.

SELECT Field1,Date
FROM schema.table1
WHERE Field1 IN ('aaaaaaa','bbbbbbb','ccccccc',...,'nnnnnnn' )
patientid = 'abcdefg'
AND Date <= 'xxxx-xx-xx'
order by Field1,Date;

Note: Field1,Date,patientID is indexed

EDIT 2:

**Explain Analyze Output:**

"GroupAggregate  (cost=51.75..51.78 rows=1 width=12) (actual time=1.112..1.112 rows=1 loops=1)"
"  Output: Field1, min(Date), Date"
"  Group Key: medical.Date, medical.Field1"
"  Buffers: shared hit=102 read=4 written=4"
"  ->  Sort  (cost=51.75..51.76 rows=2 width=12) (actual time=1.107..1.108 rows=1 loops=1)"
"        Output: Field1, Date"
"        Sort Key: medical.Date, medical.Field1"
"        Sort Method: quicksort  Memory: 25kB"
"        Buffers: shared hit=102 read=4 written=4"
"        ->  Index Scan using patmedmed_index on a6658.medical  (cost=0.42..51.74 rows=2 width=12) (actual time=1.016..1.089 rows=1 loops=1)"
"              Output: Field1, Date"
"              Index Cond: ((medical.patid = '00Nb'::bpchar) AND (medical.Field1 = ANY ('{C100111,C100112,C100z00,C101.00,C101000,C101100,C101y00,C101z00,C102.00,C102000,C102100,C102z00,C103.00,C103000,C103100,C103y00,C103z00,C104.00,C104.11,C104000,C10410 (...)"
"              Filter: (medical.Date <= '2015-01-01'::date)"
"              Buffers: shared hit=102 read=4 written=4"
"Planning time: 0.844 ms"
"Execution time: 1.248 ms"

I really need help


texts: Find records that do not have matching closing value

texts:
Sorry for unclear title, i am having trouble formulating the right question subject.
Hopefully the details will help you understand more clearly the issue i am facing.

I have a table with the following schema:

+----+----------+-----+
| ID |  Status  | PID | Time ...
+----+----------+-----+---
|  1 |  STARTED |  10 |
|  2 |  STARTED |  12 |
|  3 |  ENDED   |  10 |
|  4 |  STARTED |  18 |
|  5 |  STARTED |  21 |
|  6 |  ENDED   |  12 |
+----+----------+-----+

Every time a process starts, an entry containing its PID set to 'STARTED' Status in database.
Every time a process finishes its job, another entry containing its PID gets written to the database with the Status value of 'ENDED'.
A process with same 'PID' can start once again after finish.

I would appreciate if someone can guide me in the right direction to a query which will return all the PID's that have started but not (yet) completed.

Desired output:

+----+----------+-----+
| ID |  Status  | PID | Time ...
+----+----------+-----+---
|  4 |  STARTED |  18 |
|  5 |  STARTED |  21 |
+----+----------+-----+


texts:
SQL Server 2005, I'm seeing about 100 'jobs' in the SQL Agent>jobs node in SSMS. They appear as hexadecimal strings not with their real names:

If I query msdb..sysjobs, I see a much smaller list of jobs and their corresponding JobName.

So what are these jobs and how can I make them appear as their real job name as opposed to the JobID?

Thanks,
Peter


texts: Adding a sequence number to existing rows

texts:
I have a table with an OrderSequence column. The schema looks roughly like this:

id
Name
OrderSequence

I have an array of ids ["1", "2", "3", "4"] from an application, and I want to set the OrderSequence for sorting purposes. I don't want to loop through the rows from the application, executing 'n' SQL queries one at a time. How can I do this using a stored procedure?

Sample data

id Name OrderSequence
1  A    0
2  B    0
3  C    0

Desired result

id Name OrderSequence
1  A    1
2  B    2
3  C    3


texts:
I'm trying to get rid of using cursors in my project (SQL Server 2012). And I need to prepare query which calculates reverse (with subtraction) running total.

Let me explain on example.

I have the following data set:

Id  AppId   CaseId  Amount  Balance
1   2       123     16.48   43.29
2   2       123     5.01    43.29
3   2       123     2.25    43.29
4   4       123     16.48   43.29
5   4       123     5.01    43.29
6   4       123     2.25    43.29
7   10      789     2       10
8   11      789     4       10

I need to get following data set:

Id  AppId   CaseId  Amount  Balance Total
1   2       123     16.48   43.29   25.81
2   2       123     5.01    43.29   20.8
3   2       123     2.25    43.29   18.55
4   4       123     16.48   43.29   2.07
5   4       123     5.01    43.29   -2.94
6   4       123     2.25    43.29   -5.19
7   10      789     2       10      8
8   11      789     4       10      4

I've tried to use LAG and SUM() OVER() but I can't understand how to use it correctly.

Could you please help me with it?


texts: I have been tasked with Mysql Master-Master replication?

texts: I have done some study and i came to know that Mysql master-master replication violates the ACID properties and there has been problem with primary key collision.Please Let me Know,Apart from these are there any disadvantages?


texts: Is it possible to return empty response if there is no records in DB

texts:
Is it possible to return empty objects if there is no records in db?

db.query(' SELECT * from users where type = $1 and category = $2 limit 8', [admin, filltime], function(err, result) {
            if (err) return next(err);
            return res.status(200).send({
                status: "Successfully displaying",
                data: result.rows
            })
        })

i have only 2 records in PostgreSQL db for this type and category but i requested for 8 records. Am expecting that 2 records along with 6 empty object so how can i achieve? is there any query for that ? please guide me

expected o/p:

[{
record 1
},
{
record 2
},
{},{},{},{},{},{}]


texts: How to determine/set the Java classpath for an Oracle DB?

texts:
l am trying to make use of some proprietary Oracle Java classes/methods using an Oracle provided virtual machine (E-Business Suite Vision, Oracle 12.2.28)

On my test server I define a function:

[oracle@apps java_test]$ sqlplus USER/PASS@SID

SQL*Plus: Release 10.1.0.5.0 - Production on Tue May 21 10:21:24 2019

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create or replace function methodname(foo in varchar2, bar in varchar2)
return varchar2 as
language java
name 'path.to.classname.methodname(java.lang.String,java.lang.String) return java.lang.String';
/  2    3    4    5

Function created.

Which seems to compile. However when I attempt to call it, I get the following error.

SQL> select methodname('foo', 'bar') FROM DUAL;
select methodname('foo', 'bar') FROM DUAL
                                                                                *
ERROR at line 1:
ORA-29540: class path/to/classname does not exist

l know for a fact that this class exists at /u01/install/APPS/fs1/EBSapps/comn/java/classes/path/to/classname.class. The question is have is, how do I set the classpath for the database (either permanently or for the session) so that it can access this method? Alternatively, is there a query to determine what the classpath is so that I can copy the classes to the proper location in the operating system?


texts: SQL Server - NOLOCK vs. READONLY connections

texts: For SQL Server 2008 and above, should (NOLOCK) be used on each table of a SELECT statement even if the connection is configured as READONLY?


texts: Does SQL Server log record uncommited operations?

texts:
I often see statements like sql server log records every transction and opeation.

But I am confused about what happens when a transaction is eventually rolled back.

Say an explicit transaction has 3 statements: statement A, statement B, statement C, and finally a rollback statement D.

Now say when the execution has not reached the rollback statement D, will the modifications resulting from statements A through C get recorded to sql server log?

Understanding 1:

Statements A through D all get recorded. SQL Server records everything, no matter what.

Understanding 2:
Modifications are only stored somewhere in memory, and only recorded to log when SQL Server see a commit statement. If it turns out to be a rollback statement, SQL Server simply ignore the transacrion, no writing to log happens because it serves no purpose. In other words, SQL Server logs when there is a net result before and after the transactions.

Both seems logical, at least to me but they can't both be right. Thanks for any help.


texts: big db for managing user files and settings

texts:
I'm about to start a project that will hopefully eventually hit milion+ users. It is some kind of a site managment. Problem is how to set the db in MySQL? Some people suggest way 1 and others way 2. I hope you can help me decide the right path to start.

needs to be stored:

all users info:
user | pass | account status
user files:
path to compiled htm(in user folder with uid) | http
request path | file seg1 | file seg2 | file seg3
user settings:
personal settings (email..) | site settings (colors..) |

my ideas to store the data are:

path 1:
have main db with table for usernames and passwords.
create each user with db which inside has a table for user files and a table for user settings.
path 2:
have main db for system which inside has:
table for users and passwords and 2 more tables for each user: user_files, user_settings.

edit, add another path:

path 3:
3 DB.. one for users\passwords second for user_files and third for user_settings. This way it would separate the rows' weight evenly between the user_files and user_settings. Is that true?

And would a flat file system without the DB (beside the user password) be a better option here?

thank you.


texts: Seeing AWS PostgreSQL-on-Aurora snapshot sizes of 0 GB - is this normal for a db of ~800mb?

texts:
While working on some code to manage our manual snapshots, I noticed that a number of recent AWS PostgreSQL-on-Aurora snapshots show a storage size of 0 GB (older ones are at 1 gb). I thought it might be rounding in the wrong direction (the database is ~800 meg so far), but I wasnt sure if that was the reason for the 0 size.

I restored one of the snapshots with the intent of confirming that there's usable data in them. I am unable to connect to cluster restored from one of these snapshots despite using the same incoming IP and security group as the production cluster. In fact, I was unable to connect to it even after explicitly adding a SG that contained my IP. I've been using RDS (on "regular" postgres and MSSQL) for many years, so I doubt there is a "noob RDS" issue here - but I wouldnt discount a noob Aurora issue. Apparently I am missing some difference between Aurora (as PostgreSQL) and these other databases - or there's a cluster / instance / database problem that isn't yet revealing itself.

What am I missing?


texts: MySQL Grouping Data Together From Same User

texts:
I need a database guru. I'm trying to solve a problem that I'm pretty sure can be solved with a simple query I cant figure out. First, this is the table ve_messages:

MessageID | RecieverID | SenderID | Message | SentOn

I use a while statement to display the messages inside dynamic bootstrap panels but the query below will generate one panel every time the user receives a message which is good. But I need something more specific. I'm trying to gather all the messages from the same user inside one panel instead of creating a new one everytime so that the users can view all the messages that were sent so that they can follow the conversation. How can I do that? I tried GROUP BY SenderID but then I cannot access all the messages...

       <?php
       $i=1;
        $inboxmsgQ="SELECT * FROM ve_messages m JOIN ve_users u ON m.SenderID=u.UserID WHERE m.RecieverID= '$loginid_session' ";
        $inboxmsgresult=mysqli_query($db,$inboxmsgQ);
        while($inbox=mysqli_fetch_array($inboxmsgresult)){$i++?>
      <?php
      $checkavatareview=mysqli_query($db,"SELECT * FROM ve_photos p WHERE p.UserID={$inbox['SenderID']} AND IsPrimaryPhoto='1' ORDER BY InsertDate LIMIT 1");
       $rowviewe = mysqli_fetch_array($checkavatareview,MYSQLI_ASSOC);
      ?>

          <a data-toggle="collapse" data-parent="#accordion" href="#collapseM<?=$i;?>"><?=$inbox['ProfileName'];?> | <?=$inbox['SentOn'];?>

      <div id="collapseM<?=$i;?>" class="panel-collapse collapse">

               <a href="profile.php?viewuserprofile=<?=$inbox['SenderID'];?>" >
              <?php if(mysqli_num_rows( $checkavatareview)==0):?>

              <?php elseif(mysqli_num_rows( $checkavatareview)>0):?>
           <img style="width: 15% !important;" class="tableimg" src="core/<?=$rowviewe['PhotoPath'];?>" alt="profilename">
           <?php endif;?>

   <?=$inbox['Message'];?>

    <?php };?>


texts:
I am working on e-commerce website with wide range of categories (TV, mobile phones, furniture, etc). Since there is such large amount of attributes I decided to use EAV model to store attributes and values. Here is my tables structure:

CREATE TABLE public.product_attribute
(
    name character varying(255) COLLATE pg_catalog."default",
    attribute_type character varying(255) COLLATE pg_catalog."default",
    id integer NOT NULL,
    CONSTRAINT product_attribute_pkey PRIMARY KEY (id)
)

CREATE TABLE public.product_attribute_value
(
    value_text text COLLATE pg_catalog."default",
    value_integer integer,
    value_boolean boolean,
    value_float double precision,
    value_richtext text COLLATE pg_catalog."default",
    value_date date,
    value_file character varying(255) COLLATE pg_catalog."default",
    value_image character varying(255) COLLATE pg_catalog."default",
    entity_object_id integer,
    attribute_id integer,
    product_id integer,
    id integer NOT NULL,
    CONSTRAINT product_attribute_value_pkey PRIMARY KEY (id),
    CONSTRAINT "product_attr_id" FOREIGN KEY (attribute_id)
        REFERENCES public.product_attribute (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

Let's assume that I am adding a new TV.

Product
---------
id: 1
name: Sony TV

Product Attribute
-----------------
id: 1
name: Color

Product Attribute Value
-----------------------
id: 1
attribute_id: 1
value_text: white

The problem I am facing is that for some products (such as furniture) there are more than one attribute with same name (Color for example), not only that, I am also required to store the image photo url.

Product
---------
id: 2
name: Table

Product Attribute Value
-----------------------
id: 3
attribute_id: 1
value_text: Pattern 1
value_image: 01/a0/01a0sdadasfsdfsdfasdfasfdfsafs.jpg
=======================
id: 4
attribute_id: 1
value_text: Pattern 2
value_image: 01/a2/01a234234234234234324324234234.jpg

What is the best way to fetch products with all attributes? I am not worried a lot about query performance for filtering by attribute value (since all data synced to elasticsearch and fetched from there). But there are few millions of products. Is there any way to fetch each  product with all attributes with one query (one product -> one row)?

I think JSONB is not the best idea to store this attributes, since they are updated frequently and I don't want to search and update millions of records to rename attribute name.


texts:
I am fairly new to PostgreSQL and would appreciate any help and recommendations.

I'm running the following function to update a table:

$BODY$
  DECLARE
    commercial_period date := (SELECT periods.period FROM common.periods WHERE periods.type = 'commercial');
   --period text := concat('_',right((date_part('year',residential_period))::text,2),'_',right((date_part('year',residential_period))::text,2)::integer+1);
  BEGIN
-------------------------------------------------------------------------------------------------------------
    --copies alternative data from temporal table to common.status
    UPDATE
      common.status
    SET
      implementation_status = A.implementation_status,
      latest_version = A.latest_version
    FROM
      monitoring_commercial_temporal.possible_alternatives AS A
    WHERE
      status.reference = A.reference
      AND (status.update_period = A.update_period
      OR status.update_period IS NULL);
-------------------------------------------------------------------------------------------------------------
    --sets as lastest version any site, not in the temporal list of alternatives
    UPDATE
      common.status
    SET
      latest_version = 'yes'
    WHERE
      (reference, update_period) NOT IN (SELECT reference, update_period FROM monitoring_commercial_temporal.possible_alternatives)
      AND update_period = commercial_period + interval '1 year'
      AND implementation_status <> 'non-preferred alternative'
      AND latest_version IS NULL;
-------------------------------------------------------------------------------------------------------------
   END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.monitoring_update_alternatives_commercial()
  OWNER TO postgres;

but I get the following error:

  ERROR:  stack depth limit exceeded HINT:  Increase the configuration
  parameter "max_stack_depth" (currently 2048kB), after ensuring the
  platform's stack depth limit is adequate.

> CONTEXT:  SQL function "st_intersects" during inlining SQL statement
> "SELECT (SELECT
>                                     array_agg(status.reference)
>                                   FROM
>                                     common.geometries AS A,
>                                     common.geometries AS B,
>                                     common.status,
>                                     common.permission_details
>                                   WHERE
>                                     NEW.reference = A.reference
>                                     AND status.reference = B.reference
>                                     AND status.reference = permission_details.app
>                                     AND NEW.reference <> status.reference
>                                     AND ST_Intersects(B.geom, A.geom)
>                                     AND (NEW.update_period = residential_period + INTERVAL '1 YEAR' OR NEW.update_period IS NULL)
>                                     AND status.update_period = residential_period + INTERVAL '1 YEAR'
>                                     AND permission_details.monitoring_type <> 'commercial'
>                                     AND (((ST_Area(ST_Intersection(B.geom, A.geom))+0.01) /
> (ST_Area(B.geom)+0.01) >= 0.2)
>                                     OR ((ST_Area(ST_Intersection(B.geom, A.geom))+0.01) /
> (ST_Area(A.geom)+0.01) >= 0.2)))" PL/pgSQL function
> monitoring_update_status_before() line 72 at assignment SQL statement
> "UPDATE
>     common.status AS A   SET
>     trigger = TRUE   WHERE
>     A.latest_version = NEW.reference
>     AND A.update_period = NEW.update_period" PL/pgSQL function monitoring_update_status_after() line 5 at SQL statement

Then it keeps repeating the last 6 lines from the UPDATE


texts: visual studio database project - publish did not include drops but schema compare did

texts:
I just observed a difference in the result of publish vs schema compare.  I made changes to visual studio database project - some of which involved deleting tables.

Pushed changes to VSTS, build successful, release successful to Azure DB
Inspected said Azure DB.  Tables were not dropped that were deleted in the project.  Double checked the publish script - this didn't include any of the drops.
Ran SchemaCompare - this exposed all of the missing drops, and when executed gave me the result I expected (the applicable tables were dropped).

The same results were observed on local DB when manually publishing vs schema compare.  What am I doing incorrectly?  I thought publish would drop the tables.


texts:
I set streaming replication. Replication works just fine but it doesn't care about trigger_file. I have nothing fancy there except all what many of tutorials advices:

recovery.conf:

standby_mode = on
primary_conninfo = 'host=69.69.69.69 port=5432 user=repl password=some_pass_here'
trigger_file = '/var/lib/postgresql/9.3/main/failover_trigger'

I've checked that the postgres user has access to this file, I've tried to change the location to /tmp, set file owner as postgres or root and nothing helped.

Any ideas ? Thanks in advance.


texts: Postgresql - multi-tenant database - backup/restore single database

texts:
I am thinking about development of simple CRM for florists. Florists (company with 1-5 employees) will have local installed app on their PCs (call it "Floppa"). Floppa will be communicating to Windows Communication Foundation (WCF) service hosted on my remote server. For each of customer there will be running one instance of WCF service. Business layer under WCF service will be communicating to Postgresql database dedicated to specific customer.

I would like to use continuous archiving. If I understand well WALs will contain logs for every change in every database in cluster.

If one of customers will accidentally deletes/change some data and will ask me for recovery "today's morning state of data" how could I quickly comply his request? According my current knowledge I would need to do:

Stop WCF service (to prevent work on db which will be replace later)
Restore whole cluster to "today's morning state" on another computer
Dump customer's morning state database
Replace customer's database by dumped one
Start WCF service

Is that correct? Or is there any better solution to this backup/restore issue? What happen if I will create one database per cluster? What will be the drawbacks? I guess that one of advantages will be much more faster recovery.

What will differ if count of customers will be 10, 50, 100, 250? How Postgresql handles hundreds of clusters?

Thanks.


texts:
I have a large project tracking database used to track PMs, status, help needed, action items, reports etc. I keep the project schedule in a large MS-Project file. I've exhausted all efforts to have my organization invest in an MS-Project server.
How can I use the data found in my project file: Task Name, Resource, Start Date, End Date... etc as source data into my access database. Can these files be linked somehow if I put the project ID# into my project file as a foreign key? I just need to make the data available to people other than the PM. Ideas?


texts: Why does SQL Server need permissions to access data and log files?

texts:
When you want to attach a database, you can open them up if you put those mdf files in a folder such as:

  C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\

But SQL Server will not attach them if they are located on your desktop. If you go to your desktop path, e.g.:

  C:\users\username\desktop\

It states that it cannot find the object because it does not exist or you do not have permissions. I checked the security of the folder that is located on my desktop and I have full access. Can anyone explain this?


texts: Combine individual sensor values into an overall value with correction factors

texts:
For a school project, we are trying to calculate (corrected) Ozone values based on a combination of 5 sensors (3x O3, 1x temp, 1x humidity).

We are using MySQL and PHP for the rest of the project.

The table Measurements has this structure:

id (int(11))
time (datetime)
value (float)
measured_value (float)
sensor (tinytext)
unit (tinytext)
measurement_short_type (tinytext)
stream_id int(11)

so an example row looks like this:

ID    Time                 value  measured_value  sensor  unit   measurement_short_type  stream_id
----  -------------------  -----  --------------  ------  -----  ----------------------  ---------
3324  2016-05-21 11:00:34  0      193             O3r     KOhms  O3                      6511

As you can see, we have 2 columns, value (float) and measured_value (float).

In order to calculate the correct, final value based on the individual sensor data (stored in measured_value), we need to apply a formula similar to this for every datapoint:

Corrected value[datetime] = ("6511".measured_value[datetime] * -0.106830613)
                          + ("6512".measured_value[datetime] * 0.065201457)
                          + ("6513".measured_value[datetime] * 0.335456161)
                          + ("6514".measured_value[datetime] * -0.248569888)
                          + ("6515".measured_value[datetime] * 1.423460009) + -2.113591555

where 6511, 6512, ..., 6515 are the IDs for the 5 sensors (3x O3, 1x temp, 1x humidity). The values for all sensors are stored in the same table.

The notation "6514". measured_value[datetime] means: use the measured_value (not value!) of the row that has approximately the same datetime stamp, and the stream ID 6514.

All sensors report their value approximately at the same time, every 30 minutes (i.e. x:00 and x:30), but not exactly: one sensor could have 10:59:59 as timestamp, another 11:00:34.

As far as I know, there are 2 ingredients to solving this:

How can we apply the correction per the formula above in MySQL to get an exact value, based on the 5 sensor input values that are generated roughly every 30 minutes?
Since the timestamps for the 5 individual sensors are not always exactly the same, is there a way to have a time "fudge factor", since the corresponding sensor input values are never reported at exactly the same timestamp?

If this is not possible, can somebody advise on how to achieve this? We cannot influence the timing of the sensors, but we could change the DB/table structure if that facilitates the calculation.


texts: "CREATE INDEX" Background process in SQL Server

texts:
While analyzing our environment, I've noticed a recurrent blocking that usually happens around the same time, our monitoring tool says the blocked process is a "CREATE INDEX", however, the index maintenance is not in-progress when the issue arises. Looking at the details, the command seems to be running in the background.

Has anyone seen this background task before? I'd like to understand how/why this happens. I've looked it up and could not find much information.

I don't have automatic tuning enabled, also this is a small data mart, the ETL process doesn't include any index create statement. When I look at the blocking history, I can't really see what was the complete statement, it doesn't show plan handle or anything. I'm guessing it's an internal process as the SPID is below 50.

There is a similar thread here:
https://social.technet.microsoft.com/Forums/windowsserver/en-US/65283481-f4e7-422f-99bc-2677699ba70b/background-process-create-index?forum=sqldatabaseengine


texts:
I have started to design a database for a company which I work for. I am curious how the professionals would design such a database and what advice you might carry.

Information about the database:

The database is a reflection of activities which are all completely different. Basically there are around seven activities which each carry their own set of information. This means that it is hard to standardize the database and simply make a table called 'activities' which suffices for the entire database.

I was thinking of using a seperate table for each activity and link those to a couple of tables with basic information like the geographic information and/or the employee who was/is involved.

If this is the case then I at least know that I am on the right path and if I am not right, then I have learned something important!

If the information provided is not complete, then feel free to ask for more!


texts: Fastest way to change a 600GB table indexed key datatype from INT to BIGINT

texts:
I need to change a datatype from INT to BIGINT in a 600GB MySQL table. The column has a unique index. I might be good with unsigned INT, but I assume changing to that or BIGINT will be pretty much same pain.  The table's engine is InnoDB. What would be easier:

ALTER TABLE
Copying structure and INSERT INTO (SELECT *)
Dumping table and changing dump file table definitions
Anything else?

UPDATE:
As requested, MySQL ver 5.5.15, no foreign  keys and create table:

 CREATE TABLE `tbl` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) NOT NULL,
    `created_at` datetime NOT NULL,
    `tid` bigint(20) NOT NULL,
    `t` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `f` tinyint(1) NOT NULL,
    `i_id` bigint(20) NOT NULL,
    `ir_id` int(11) NOT NULL,
    `r_c` int(11) NOT NULL,
    `r` tinyint(1) NOT NULL,
    `e` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `t` varchar(5) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `user` (`user_id`,`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=1657146169 DEFAULT CHARSET=utf8


texts: Problem in MySQL cluster when using two clusters

texts:
I have a master-slave MySQL Cluster 1 which is two way. It is working fine.
I have another MySQL master-slave Cluster 2 where data is coming from Cluster 1 master to Cluster 2 master. It is also working fine except the part where data is not replicated to slave in Cluster 2.

This setup is shown in diagram below. The problem part is shown in RED color i.e. it is not working.

Is this kind of setup even possible and if yes then how can I make it work?


texts: How to Create Trigger Insert with a SELECT LEFT OUTER JOIN

texts:
I want this trigger to be fired after an insert is made with the text, this is what I have so far

ALTER TRIGGER [xxx]
   ON  [dbo].[Main]
   AFTER INSERT
AS
BEGIN

    SET NOCOUNT ON;

    INSERT INTO dtsx.dbo.main(refno,subject,recdate,dateind,officefrom,author,status)
    SELECT refno,subject,datereceived,'REC','TEST',author,StatDesc
    FROM dbo.Main LEFT OUTER JOIN
        dbo.DocStat ON dbo.Main.Status = dbo.DocStat.StatID

END

I just want the last record i created to be inserted but instead all the records in the source table (dbo.main) are inserted to the target table (dtsx.dbo.main). Where do place the INSERTED clause, if any? TIA


texts: How to efficently handle large amounts of insert statements

texts: I have a file containing a large amount of insert statements roughly 30 GB worth of them. The insert statements themselves are rather simple and contain 5 different values. I now have the requirement the insert the data from these statements into a SQL database on Azure. Which tool would be best to easily and efficiently insert the data? This is a one-time thing, so the solution does not need to be elegant or repeatable. The key priority is getting it done with as little time spent on the solution as possible. Thanks for reading this.


texts: How to insert and update bulk data From one DB to another

texts:
There are two DBs on different servers. I want to run a procedure once a day so that I can get updated records. If it already exists then it should update, else insert a new row.

So far I did it like this:

if not exists (Select emp_id  From DB1.dbo.Emp_Master where emp_code = (Select pk_emp_code From DB2)
    Begin
    INSERT INTO DB1.dbo.Emp_Master(emp_code,emp_name,emp_dept ,emp_desg ,emp_mob ,email_id,emp_dob,emp_doj)
    Select pk_emp_code,first_name+ '' +last_name,dept.dept_desc,
     desg.desig_desc,office_phone,email,REPLACE(CONVERT(NVARCHAR,CAST(dob AS DATETIME), 6), ' ', '-') ,
    REPLACE(CONVERT(NVARCHAR,CAST(doj AS DATETIME), 6), ' ', '-')
    From DB2
        Select 1
    END
    ELSE
    BEGIN

    UPDATE DB1.dbo.Emp_Master SET emp_name=first_name+ '' + last_name,emp_dept=dept.dept_desc,emp_desg=desg.desig_desc,
    email_id=email,emp_mob=office_phone,emp_dob=REPLACE(CONVERT(NVARCHAR,CAST(dob AS DATETIME), 6), ' ', '-'),
    emp_doj=REPLACE(CONVERT(NVARCHAR,CAST(doj AS DATETIME), 6), ' ', '-')
    FROM [DB2]
    where emp_code =pk_emp_code
    Select 2
    END

I am getting error

  SubQuery return more than one value

What's gone wrong?


texts: Postgres evaluates syntax error as boolean in Update statement

texts:
Recently I came across a bug in code, equivalent to this snippet:

create table testing(num int, dt date, istrue boolean);

insert into testing values (1, '2018-01-01', True);

UPDATE testing SET num = 2, istrue = null AND dt = '2018-01-01';

The UPDATE statement should have actually been:

UPDATE testing SET num = 2, istrue = null WHERE dt = '2018-01-01';

Yet postgresql happily accepted the UPDATE as valid and it evaluated the code:

null AND dt = '2018-01-01'

after the second equal sign as a boolean and therefore updated data based on bad logic.

I have tried this in other SQL flavors and it is not allowed, they want a WHERE.

I'm not a postgres person but it does seem like a very odd syntax quirk, or is it a bug?


texts: Oracle PDB broken after upgrade from 12.1 to 12.2

texts:
We upgraded our development environment Oracle 12.1 RDBMS to 12.2 version. The CDB contained 3 PDBs of which 2 upgraded nicely but 1 did not. The resulting situation is that during the upgrade SYSTEM and SYSAUX tablespaces ran out of space which caused all the packages etc also to fail. Now we cannot rebuild the internals because of the lack of space (ORA-65114: space usage in container is too high) and we cannot expand/add or frankly do any operations due to broken system data e.g:

SQL> alter pluggable database storage unlimited;
alter pluggable database storage unlimited
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'LBACSYS.LBAC$BEFORE_ALTER'
ORA-00604: error occurred at recursive SQL level 2
ORA-65114: space usage in container is too high
ORA-06508: PL/SQL: could not find program unit being called:
"LBACSYS.LBAC_EVENTS"
ORA-06512: at line 2

We have tried to create a new PDB from the failed PDB (did not work) and create a new PDB and move the old PDB's USERS tablespace files under the new PDB (using the same names, but also did not work due to mismatching checksums on PDB start).

The PDB seems to be usable by the end users but no management activities can be performed (expdp or even just looking at a table metadata in Sqldeveloper).

Is there any chance for recovery at all or should we start looking up backups?


texts: select items with one or more specified tags

texts:
I have two tables.
One table holds tags:

CREATE TABLE `tags` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(30) NOT NULL
) COMMENT='';
INSERT INTO `tags` (`id`, `name`) VALUES
(1, 'tag one'),
(2, 'tag two'),
(3, 'tag three'),
(4, 'tag four'),
(5, 'tag five');

Other table holds items, where each item has zero or more tags (their IDs) separated by comma:

CREATE TABLE `items` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(30) NOT NULL,
  `tags` text NOT NULL
) COMMENT='';
INSERT INTO `items` (`id`, `name`, `tags`) VALUES
(1, 'first item',   '2,4'),
(2, 'second item',  ''),
(3, 'third item',   '1'),
(4, 'fourth item',  '1,3,4');

How do I select only those items which have at least one of the wanted, say (2,3) tags?


texts:
We have a Postgres setup with multiple tables representing different parts and stages of a project, 4 of them at the moment but potentially more in the future. We want to make a Task table with a foreign key to any of these tables. Preferably like this:

CREATE TABLE stage1(
id SERIAL PRIMARY KEY,
description TEXT);

CREATE TABLE stage2 (
id SERIAL PRIMARY KEY,
description TEXT);

CREATE TABLE stage4 (
id SERIAL PRIMARY KEY,
description TEXT);

CREATE TABLE task (
id SERIAL PRIMARY KEY,
description TEXT,
foreignkey_table VARCHAR(20),
foreignkey_id INTEGER);

where the column named foreignkey_table can indicate the "parent" table and the foreignkey_id will indicate the "parent id".

These stages 1 to 3 are to make the example simple but they can in no way be joined as one. Think of them as "preparation stage", "execution stage" and "evaluation stage". Or as completely different tables like "users", "companies" and "activities"  each with its own specific not overlapping content.

How can I do this?

I have read some posts telling it is not possible but they were quite old so I'm hoping a solution is implemented after these posts. I also found a potential solution on Constraint for multiple foreign key yet it's mutually exclusive (PostgreSQL), and this is my current best fallback option.


texts: Drop constraints vs disable constraints during a data import

texts:
I am in a tough situation, and looking for advice on how to proceed.  I have to import some data from one environment to another.  80% of the records have been modified, so the plan is to simply delete all the existing records, and import the new ones.

Unfortunately this is a base table.  Several other tables have FK's defined, pointing at this table.  In order to delete the data, I need to either drop the FK's and recreate them when finished the import, or disable the constraints and re-enable them.

No records have been deleted.  A copy of prod was made, and developers have tweaked the data and added new records.

Which approach would you take?


texts:
I have a 1 TB database. I started restoring the database(full backup) with No recovery and replace .In between there is a windows msi installer which installed .net framework 4.6.1 needed a reboot of machine and server got rebooted. After reboot now the database is in restoring mode. But I feel restore is definitely not successful. So now my plan is :

Restore Database DBNAME with Recovery

does not work in this case.

Take the database offline.
Delete the database and re start the restore of full backup again with no recovery and replace.

Please advice if this approach is correct.Or what I am thinking is wrong.


texts: SQL Server 2012 (compability level 110) and SQL Server Native Client 10

texts:
I've upgraded my SQL Server 2008R2 to SQL 2012. I will set the compability level to 110 (2012) to all databases.

But I'm wondering if it will create some problems for clients that connect to this server with SQL Server Native Client 10.0 ?

I saw on multiple forums that they only won't be able to use new features of SQL 2012 but they never speak about level of compatibility of databases.

Can someone give me some clarification ?

Thanks a lot!


texts: NEO4J Constraint error `ConstraintCreationFailed`

texts:
Background
I am attempting to set up a constraint on neo4j using the browser.
I have first of all ran the following, to ensure there is no duplicates;
MATCH(m:uri), (b:uri)
WHERE m.value = b.value
AND id(m) <> id(b)
RETURN m, b
ORDER BY id(m)

For clarity, I also checked the following returned 0;
MATCH (b:uri)
WITH b.value AS value, COLLECT(b) AS branches
WHERE SIZE(branches) > 1
RETURN branches

What I tried
CREATE CONSTRAINT constraint_urivalue IF NOT EXISTS ON (u:uri) ASSERT u.value IS UNIQUE

AND
CREATE CONSTRAINT constraint_urivalue ON (u:uri) ASSERT u.value IS UNIQUE

The Error

Neo.DatabaseError.Schema.ConstraintCreationFailed
Unable to create Constraint( name='constraint_urivalue', type='UNIQUENESS', schema=(:uri {value}) ):

What else
I have managed to create other node/attribute-constraints using the same code as used above.
Also, I have a development environment where this exact "uri constraint" has been implemented without any issue.
Both are running the exact same version of neo4j.

Is there anyway to see more detail of the error?
Any queries worth running to re-check my data?

Thanks!


texts: mysql db backup and restore withought mysqldump. only tar?

texts:
on my linux system all databases reside in /var/lib/mysql.
Let's say my db is /var/lib/mysql/test1_db

Is it possible and/or advisable to just tar the db directry?
And then use the resulting .tar file to restore it.
Yes, I am stopping mysqld during the process.

If this is ill advised is there something like sql instant file init for linux?


texts: MongoDB C# driver fails to reconnect after stepDown

texts:
I have an Elastic Beanstalk environment in AWS that constantly verifies the DB is accessible by querying the mongos for one document. We only speak to the DB routers and not to specific instances.

During a stepDown, half the servers are immediately declared as unhealthy, whereas the other half, which uses the same connection string, works fine.

We use the official MongoDB C# driver v.1.10 (Mongo 2.6.1) and I have a feeling the driver doesn't recognize the re-election process but latches to the one node he got a hold of.

Is this a known phenomenon? What are the ways to bypass it?

Edit:
The connection string uses routers and no options, as follows:
mongodb://user:password@company.routerA.mongohost.com:9999,company.routerB.mongohost.com:9999,company.routerC.mongohost.com:9999/DatabaseName (names and ports altered)


texts: In a trigger, how to check if no fields have changed?

texts:
If we receive an update statement that does not check if the value has changed in the where clause, what are the different ways to ignore that update inside a trigger?

I know we can do a comparison of each individual field (handling the ISNULL side as well), but where it's a table that has 50+ fields, is there a faster/easier way to do it?

Note: This could be used to reduce IO/noise in audit records etc...

EXAMPLE

For a table of:

CREATE TABLE myTest
(ID int NOT NULL IDENTITY(1,1),
 Field1 varchar(10) NULL,
 Field2 varchar(20) NULL)

with an after updater trigger containing:

INSERT INTO myTestAudit (ID, Field1, Field2, DateTimeUpdate)
SELECT ID, Field1, Field2, getDate()
FROM inserted

with initial values:

INSERT INTO myTest (Field1, Field2)
SELECT 'a', 'b' UNION ALL
SELECT 'a', 'c'

Now run an update:

UPDATE myTest set Field2 = 'b' WHERE Field1 = 'a'


texts: Need help optimizing mysql on a busy server

texts:
I am managing a server for a very busy news website with lots of select queries (read-heavy), concurrent about 4000 users online at peak time. Around 1.4Mil pages views / day

The server has 42G RAM, running dual intel CPU 6 core 2.6G ( HT tech) = 2x6x2.6Ghz ( x2 in HT )
I have issues of the server overloading randomly up to +300 server load

Most of that time I see that SQL has taken all resources.. I'm trying to tweak it, but I'm out of my knowledge .

There are 3 databases, two just small 200mb but one main database for news is +4G in volume.
( web runs fast at load under 200 but it got sstuck, or error 502/504 if load increase )

Here is my.cnf

socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 768M
max_allowed_packet = 16M
max_connections = 2000
wait_timeout = 20
connect_timeout = 10
tmpdir = /dev/shm/mysqlcache
tmp_table_size = 512M
table_cache = 2948
sort_buffer_size = 256M
read_buffer_size = 16M
read_rnd_buffer_size = 256M
myisam_sort_buffer_size = 256M
thread_cache_size = 768
query_cache_type = 1
query_cache_size = 2048M
query_cache_limit = 16M

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 24

Any suggestions very well come..