How to call stored procedure inside trigger? I have made stored procedure that will update product quantity as per product id.

That works fine for me.

Following is code :

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `updatequantity`(IN id int, IN qty int)
BEGIN
    update product_master
    set prod_qty = (prod_qty - qty)
    where prod_id = id;
END

it works greatly for me. Now I want to put trigger on product_master table that will first check that enough quantity is there or not in stock ?

Suppose order quantity is 100 and total available product quantity is 50 then procedure should not be called instead some error message should be displayed.
If enough quantity is there then only stored procedure should be called.

Anyone have idea that how to proceed for that ?

Thank You.

Instead of creating trigger you can use below code, It will also return you success or failure message.

DELIMITER ;
DROP FUNCTION IF EXISTS to_updatequantity;

DELIMITER //
CREATE FUNCTION to_updatequantity(id int, qty int) RETURNS CHAR(100) DETERMINISTIC
BEGIN

    SELECT prod_qty INTO @var_prod_qty FROM product_master WHERE prod_id = id;

    IF (@var_prod_qty > qty) THEN
        UPDATE product_master SET prod_qty = prod_qty - qty WHERE prod_id = id;
        SET @msg = 'prod_qty is updated successfully';
    ELSE
        SET @msg = 'prod_qty is less than or equal to ordered qty';
    END IF;

RETURN @msg;
END //
DELIMITER ;


SQL Server data, logs, backups files in mounted foldersin C:\ I know that is a good practice to have data, logs and backups files of SQL Server in different disk, for example

D:\ for Data files
E:\ for Logs files
F:\ for Backup files

If I want to use this disks mounted in folders in C:\is safe?
For example C:\MSSQL\Data, C:\MSSQL\Logs, C:\MSSQL\Backups respectively...

Well, you shouldn't encounter any errors from SQL Server, if that is what you are asking about.  SQL Server doesn't care where the files are located.  That being said, there are good reasons for having different file types on different drives.

Performance - Having different files on different drives means that there are different sets of drive heads reading the data.  Your throughput for simultaneous read/write operations goes up.  Especially if you are backing up to the same drive that your data files are on, this will suck.

Maintenance - What happens when your drive fills up because the part of your script that cleans out old backups has failed and now your drive is full?  If you had separate drives then just the one then your damage would be limited.

What happens to your backups when your primary drive fails?  Are they being taken off to tape somewhere else?

If this is a production server then I strongly encourage you to separate out to at least separate Data and Log drives and backup to a different machine.


Cannot create relationship: Error:Missing index on column(s) I am trying to make relation on 2 tables in database designer.

I am using relationship tool. Below are the steps followed:

I clicked on the ID in table lekar.
Then I click on lekar column in the next table.
Pressed OK.

It throws the below error, what is the problem ?

Error: Missing index on column(s).

I added ID column so it will have indexing, but it still does not work.

Ahoj Hynku,

zkus tam přidat CONSTRAINT id nebo/a FOREIGN KEYids referencí na druhou tabulku s 'Id' sloupcem.

Hi Hynek,

You can try add CONSTRAINT id or/and FOREIGN KEYid with refernece on second table with 'Id' column.


Return items not found in database in Oracle I've been given a list of employee names (several hundred), and I need to look them up, and return their employee number.

However, the names given are not necessarily exactly as they appear in the database.  I therefore also need to know which ones it does not find, so we can do some further manual research to locate them and find out their employee id - but I cannot figure out how to also return the employees not found.

Example:

select name, emplid
from employees
where name in ('Smith,John','Brown,Jill','Doe,Jane')

If one of the names is 'Doe,Jane April') in the db, then I don't find her.

I am using Oracle, and I do not have the access to create tables.  Is there any way to do this?

You can use this approach as it demonstrated in this SQL fiddle:

SQL Fiddle

Oracle 11g R2 Schema Setup:

create table cust (custname varchar2(20), custid int);
insert into cust values('Andrew', 1);
insert into cust values('John', 2);
insert into cust values('Mary', 3);

Query 1:

with names as (
select 'Andrew' AS search from dual
union all
select 'Mark' AS search from dual
union all
select 'Mary' AS search from dual
)
select n.search, NVL(c.custid, 0) custid
from names n left join cust c
on n.search = c.custname

Results:

| SEARCH | CUSTID |
|--------|--------|
| Andrew |      1 |
|   Mary |      3 |
|   Mark |      0 |

Query 2:

with names as (
select 'Andrew' AS search from dual
union all
select 'Mark' AS search from dual
union all
select 'Mary' AS search from dual
union all
select 'Jo' AS search from dual
)
select n.search, NVL(c.custid, 0) custid,
CASE WHEN c.custid IS NULL THEN 'not found'
 WHEN c.custname = n.search THEN 'exact match'
 ELSE 'partial match' END AS result
from names n left join cust c
on c.custname LIKE n.search || '%'

Results:

| SEARCH | CUSTID |        RESULT |
|--------|--------|---------------|
| Andrew |      1 |   exact match |
|   Mark |      0 |     not found |
|   Mary |      3 |   exact match |
|     Jo |      2 | partial match |

As you can see, you need to put the search values into the cte called names, and then left join it with your table. Using this approach will also allow you to use LIKE operator with a 'result' column, as demonstrated by the second query.

BTW, read this article on how to create a UDF that will convert the IN-list to a table


Is good idea to implement ProxySQL in production with Docker? The idea is to have solution with ProxySQL to give high availability solution with docker implementation? But this question maybe not have sense, I am to investigate alternative solution without VIP implementation.

HA implies that something will break.  But if all the pieces are in the same server, it seems like the most likely thing to break is the server -- taking down everything.

For HA to be really effective, you need separate servers geographically separated.

A blend would be to have such separate servers, then have dockers in each.  This could, for example, provide multiple HA systems sharing, say, 3 servers, where each system is split up for HA.

At that point, you should consider Galera (PXC or MariaDB) for its HA capabilities in 3 nodes in different locations.  Each node could be in a docker if desired.


Can't create diskgroup in Oracle 19c I've successfully installed Oracle 19c with this article.
Now I need to restore the database from existing backups. I've first restored the control file, then I started to restore the database:
rman> restore database;
...
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15374: invalid cluster configuration
...

Then I tried to create the diskgroup, and it failed:
SQL> create diskgroup DATA external redundancy disk '/dev/loop1' force;
create diskgroup DATA external redundancy disk '/dev/loop1' force
*
ERROR at line 1:
ORA-15000: command disallowed by current instance type

Is there a work around? I can either ignore the diskgroup or find a way to create one, I just don't how to fix it.

It seems you are in RDBMS instance
to check use the following query:
select value from v$parameter where upper(name) = 'INSTANCE_TYPE';

or
show parameters instance_type

If you get RDBMS value, go to operational system shell and check the SID of ASM instance in /etc/oratab
cat /etc/oratab

After cat the file, you will get something like it:
+ASM:/u01/app/19.0.0/grid:N

The rows are delimited by :. The first field is the SID (in my case +ASM), the second field is the GRID_HOME (also ORACLE_HOME)
You can use oraenv to set the variables needed to access the ASM instance or you can set the variables manually with export command.
With oraenv:
. oraenv

after issuing the . oraenv command, type your SID
or export the variables manually
export ORACLE_SID=+ASM
export ORACLE_HOME=/u01/app/19.0.0/grid
export PATH=$ORACLE_HOME/bin:$PATH

NOTE: to create asm diskgroup you need access the instance with sysasm role
sqlplus / as sysasm


Does the SQL Server Service Account have to have access to a network share to perform a backup? I've been provided a specific domain account to handle backups from my sysadmins. I am slightly confused as I've never been provided a separate domain account to handle backups in the past. I still want to use Agent to handle this, so I am thinking that they are thinking the below:

  If you are running the job under a proxy account, the proxy must be a
  domain account. Add the account to the sysadmin server role and grant
  it full control of the directory and the network share.

So I've done what I thought the above is describing, I added the domain account as a login, I created a credential & proxy, and I created the job step as CmdExec to run under the proxy, but I still get:

  Operating system error 5(Access is denied.).

It isn't until I give access to the Service Account that is running the SQL Server Service that I am able to backup to this network share. I'm also concerned about restores and I wanted to utilize Agent to handle cleanup tasks & moving files around to various directories as they age.

So I'm not sure what's going on here, but I think the service account at a very minimum needs write access? One sysadmin said he was aware of someone doing the backup process as a "separate service", which I don't quite understand, unless its something like ApexSQL backup or something...

If you're performing native SQL Server backups, the service account running the SQL Server database engine must have access to the backup location. The Agent job can run under a proxy to fire off the job itself, but the engine still performs the backup so still needs access.

The only way to have the backup write to disk as another user would be to use a third-party tool that receives the backup data through the SQL VDI interface and writes it to disk as the appropriate user.

From Docs:

  Ownership and permission problems on the backup device's physical file
  can interfere with a backup operation. SQL Server must be able to read
  and write to the device; the account under which the SQL Server
  service runs must have write permissions.

For managing the files after they've been backed up, you can use a SQL Server Agent proxy for this to ensure you're using the correct account for file system access.


Run multiple MySQL instances in Windows Which is a better way to install multiple instances of MySQL / MariaDB / etc on a single Windows box?

Manually setting different ports?
Docker?
Virtual Box?
Something else?  (Sandbox, mysql_multi, etc, seem to fail on Windows.)

Once they are installed, I intend to write a Perl script to iterate through the instances to get timings (etc) for comparison.  So, they need to be accessible in some manner from a single thread.

(I can figure out the settings, RAM, disk, etc; it's just the installs that I am looking for advice on.)

Easy, with MariaDB.

mysql_install_db.exe --service=service_name --datadir=datadir --port=port

Run this multiple times with  different parameters (service_name, datadir, port should b different for different instances). Start services with "net start " .
This is supported official tool. Documentation is here https://mariadb.com/kb/en/mariadb/mysql_install_dbexe/ . You do not need any mysql_multi, really :)


Invalid Column Name Error after ALTER and UPDATE I am trying to UPDATE a specific column within a table, but I get an error due to the fact that when SQL compiles, the column name IssueTimeUTC does not actually exist. Here is my sample code:

   WHILE  @startissuetime<='23:30'
        BEGIN
        ALTER TABLE Intraday_Forecast_temp
            ADD IssueTimeUTC SMALLDATETIME

            UPDATE Intraday_Forecast_temp
            SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
            WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)

            UPDATE Intraday_Forecast_temp
            SET IssueTimeUTC=Dateadd(d,-1,CAST(@tempdate AS SMALLDATETIME))
            WHERE

DateTimeUTC<=CAST(@tempdate AS SMALLDATETIME)
            END

I have read multiple similar posts, but I cannot make anything work therefore I am kindly asking you if you could help me.

The error I get is this:

  Msg 207, Level 16, State 1, Line 56 Invalid column name 'IssueTimeUTC'.

Update: So basically I was not able to find an exact solution to this specific problem but I just found a way to go ''around'' the problem instead. So this is the updated code I used.

WHILE  @startissuetime<='23:30'
    BEGIN
        ALTER TABLE Intraday_Forecast_temp
        DROP COLUMN IssueTimeUTC
        --------
        BULK INSERT.....
        --------
        ALTER TABLE Intraday_Forecast_temp
        ADD IssueTimeUTC SMALLDATETIME

        UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
        WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)

        UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=Dateadd(d,-1,CAST(@tempdate AS SMALLDATETIME))
        WHERE DateTimeUTC<=CAST(@tempdate AS SMALLDATETIME)
    END

I know that this is not probably the most elegant solution but I initially defined the Intraday_Forecast_temp table WITH the column IssueTimeUTC , then I drop it and add it again. This way, SQL stop complaining that the column does not exist upon compilation :)

You cannot add the column and use it in the same batch.

So if you execute your code in SSMS just add GO between your commands like this:

    ALTER TABLE Intraday_Forecast_temp
        ADD IssueTimeUTC SMALLDATETIME

go ----------------------------<<<<<-----------------------------

        UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
        WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)

        UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=Dateadd(d,-1,CAST(@tempdate AS SMALLDATETIME))
        WHERE DateTimeUTC<=CAST(@tempdate AS SMALLDATETIME)

If it's a part of stored procedure, wrap your update in EXEC:

exec ('UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
        WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)')

UPDATE

In your case you can use wrapping in exec this way:

declare @tempdate date = getdate(); -- or whatever it should be
declare @sql nvarchar(4000)= N'UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
        WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)';

exec sp_executesql @sql, N'@tempdate date', @tempdate = @tempdate;


Optimize Oracle 11g SQL*Plus connection for large remote SELECTs Which options should I set in SQL*Plus for a script that runs multiple SELECTs, (some are JOINs) to SPOOL the results as text (CSV) in order to optimize these SELECTs, specifically one resulting in a 50 MB file, so that they take shorter to run, while the connection runs over the Internet? There's only read-only access, so I'm wondering what the options may be.

Instead of the 11g client, you should use at least 12.2, because:

11g is outdated
12.2 has builtin support for generating CSV output (MARKUP), sqlplus -M "CSV ON" user/password@address
you can simply start 12.2 with the -fast option (-F[ast]), that sets (which you can set and tune further manually as well):

ARRAYSIZE = 100
LOBPREFETCH = 16384
PAGESIZE = 50000
ROWPREFETCH = 2
STATEMENTCACHE = 20


Computed Column Update when Table Value changes I have environmental sensors collecting some raw data from environmental stations. In addition to the sensors, there is a physical gauge measuring water height (staff gauge).

I'm trying to set up the staff gauge calculation, so that it will take the most recent offset reading from Table 2 and apply it to any new measurements coming in, without changing the previously calculated values.

Table 1

station_time
Air_temp_celcius
Rainfall_mm
pressure_mH20
water_temp_celcius
sensor_depth_mH20
staff_gauge_height_m (calculated, sensor_depth_mH20 + Offset from Table2)

Table 2

Observed_time
Offset_m
comments

My thinking was to have a function to pull the offset

SELECT TOP 1(Offset)
FROM Table2
ORDER BY Observed_time DESC

Is there a way to have it only update new values coming into Table 1 without updating previous values when a new offset is entered?  New offsets are entered into Table 2 very sporadically as field crews visit the site.

Additional information from comments:

There is no direct relationship between the two tables, other than the latest offset is used to calculate the staff gauge value. It should always use the most recent value.

I have specific stations and specific station offset tables. I generalized the table structure: table1_stream1 and table2_stream1_offset. As for the offset storage on the table, redundancy. Always was taught you normalize as much as possible and don't duplicate data in multiple tables if you can avoid it. I no longer work on coding and databases as much as I'd like, but some stuff sticks.

  Are you saying if you have a station called "Antartica" and a station called "Africa" then you have the following tables: Antartica_stream1, Antartica_stream1_offset, Africa_stream1, and Africa_stream1_offset, etc.?

Correct. Given stations are polling hourly and not all locations carry the exact same sensor package (or sensors are in different order). Also individual stations may go offline due to technical or environmental issues.

Given the additional information that Aaron Bertrand didn't have access to when he posted his answer I would suggest a different tack.

Instead of putting logic/business significance in table names I would have general table names and put the logic/business significance in attributes/data in the tables.  This should make it easier to expand functionality, and maintain your data.  Furthermore you can extract useful information much easier.

The following is a rough schema that captures the direction I recommend and will probably need to be adapted to your exact needs:

CREATE TABLE dbo.WeatherStation
(
    WeatherStationId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(50) NOT NULL -- This is where you put the name of the station instead of in the table.
)

CREATE TABLE dbo.SensorReading
(
    SensorReadingId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    WeatherStationId INT NOT NULL FOREIGN KEY REFERENCES dbo.WeatherStation(WeatherStationId), -- Match a reading to the station
    ReportedTime DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(), -- When the time was reported to the database
    <Other columns like temp, pressure, etc.>
)

CREATE TABLE dbo.SensorOffset
(
    SensorOffsetId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    WeatherStationId INT NOT NULL FOREIGN KEY REFERENCES dbo.WeatherStation(WeatherStationId), -- Match a reading to the station like you do now
    Offset DECIMAL(20, 10) NOT NULL -- Adjust precision/datatype as needed
    Comment NVARCHAR(500) NULL,
    Created DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME() -- This would need to be unique per weather station
)

Now you can add a new station without duplicating table schema, you can easily compare data from related stations, etc.

Even if you didn't want/can't change your schema, I would recommend putting the calculation in a view.  That is more obvious in my opinion than a trigger, and it would be easier to trouble shoot for me.  Something like the following should work with my schema above:

;WITH CurrentOffset_CTE AS
(
    SELECT
        WeatherStationId
        , MAX(Created) AS Created
    FROM dbo.SensorOffset
    GROUP BY
        WeatherStationId
)
SELECT
    WS.Name
    , SR.ReportedTime
    , CASE WHEN SR.<reading> IS NOT NULL THEN SR.<reading> + SO.Offset ELSE NULL END AS <reading>
    , <repeat same pattern as above for the various readings>
FROM dbo.WeatherStation WS
    INNER JOIN dbo.SensorReading SR ON SR.WeatherStationId = WS.WeatherStationId
    INNER JOIN CurrentOffset_CTE CO ON CO.WeatherStationId = WS.WeatherStationId
    INNER JOIN dbo.SensorOffset SO ON SO.WeatherStationId = CO.WeatherStationId AND SO.Created = CO.Created

This will be easy to troubleshoot, hard to miss, and obvious to future maintainers.  You could modify this code to work for your current schema too, but would have to duplicate it for each station.  In that case I would still recommend this approach for the above stated reasons.


Shrink table SYS.AUD$ DB:
Oracle RAC Database Release 11.2.0.4.0

I want to shrink SYS.AUD$ table. Some records were deleted but table still has about 25GB.
I read that it is necessary to move AUD$ segment to another tablespace using dbms_audit_mgmt package.
begin
dbms_audit_mgmt.set_audit_trail_location(
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
audit_trail_location_value => 'AUDTBS');
end;

Then:
alter table sys.aud$ enable row movement;

alter table sys.aud$ shrink space cascade;

alter table sys.aud$ disable row movement;

My questions are:

Is this "dbms_audit_mgmt.set_audit_trail_location" an ONLINE operation?
After that, should i move aud$ table back to SYSTEM tablespace?

Thank you guys

Where did you 'read' that the procedure you state is necessary to shrink the table?
Yes, you should use dbms_audit_mgmt to relocate your audit table out of the SYSTEM or SYSAUX table space.  Not to shrink the table, but because that is best practice.
Is it an online operation?  What does the doc say?

Usage Notes
The following usage notes apply:
This procedure involves data movement across tablespaces. This can be a resource intensive operation especially if your database audit

trail tables are already populated. Oracle recommends that you invoke
the procedure during non-peak hours.

Should you move it back to the SYSTEM TS?  No.
Also, when considering shrinking table you need to remember that space that is freed up by deleting rows is still allocated to the table and will be reused by future inserts.  Unless a delete is a one-time cleanup and permanent reduction in the number of rows, a shrink table is worse than wasted effort because future inserts will just incur additional overhead to re-acquire the needed space.
But remember, relocating the table into its own TS is just good management, regardless of shrinking the table.
Also, you state you are on 11.2.0.4. That is out of support and long past time to upgrade.


How can I prevent Pentaho DI/Spoon from deadlocking? I have a transformation with four steps:

Read a pipe-delimited text (a text file input step)
Write to a staging table, with truncation (import table)
Run a stored procedure against the table populated in #2
Run a stored procedure against the table modified in #3

These have hops between them: 1 to 2 to 3 to 4.

It consistently hangs (tested several times, waiting up to 1.6 hours); that is, Pentaho reports starting the job in the Logging window, then nothing more.  The GUI itself does not hang, in fact the Stop button disables after a moment, but the transformation does not proceed.

It appears to be deadlocking: checking for blocked sessions on the target database (PostgreSQL), I see two sessions, one for the stored procedure in step #3 (which shouldn't have started yet), which is blocked by a session which is trying to truncate the table in step #2.  The TRUNCATE never completes (though Postgres does not report it as blocked), so no records get written and step #3 cannot run.  If I kill either session, the transformation fails, with no records written; the sprocs in steps #3 or #4 sometimes run, but with no new data on which to operate.

If I disable the hop from 2 to 3, the job runs fine, and it takes just a couple of minutes.

Why is the sproc in step #3 trying to run when it has a dependency on step #2?  Does a Pentaho "hop" not mean "run A, wait for it to end, and then run B"?  What can I do to prevent the deadlock situation, or have I misread what's going on?

Noob error!  Pentaho "hops" are not sequential; they will all run simultaneously.

The correct way to handle this type of process - read data, write data, and then manipulate the data in SQL - is to put the read/write in a transformation by itself, and then put that transformation into a job with the SQL procedure(s) following the transformation.


Find rows that have the same value on a column in MySQL In an employee table, some rows have the same value for the reportsTo column.

employeeNumber | reportsTo
---------------|-----------------
1056           | 1002
1076           | 1002
1088           | 1056
1102           | 1056
1143           | 1056
...

I want to be able to show the employee numbers of employees who report to the same person as does a particular employee.

For e.g. I want to know the other employees who report to the same person as employeeNumber 1088

The output should be:

   | employeeNumber |
   |----------------|
   | 1088           |
   | 1102           |
   | 1143           |

What simple MySQL statement I can use to get this result?

One way by using a subquery that returns reportsTo of employeeNumber = 1088

select
from   employees
where  employee.reportsTo = (select reportsTo
                             from   employees
                             where  employeeNumber = 1088);


Postgres - Find index order ASC or DESC Im trying to list all the indexes in my Postgres along with their order like ASC or DESC. I found some great answers from this StackOverflow question. But unfortunately, the pg_am table got removed a lot more columns.
So Im  not able to find any alternate system table to check this. Can someone help me with that?

I found a small workaround for this.
SELECT     tnsp.nspname                           AS schema_name,
           trel.relname                           AS table_name,
           irel.relname                           AS index_name,
           a.attname                              AS column_name,
           1 + Array_position(i.indkey, a.attnum) AS column_position,
           CASE o.OPTION
                                 & 1
                      WHEN 1 THEN 'DESC'
                      ELSE 'ASC'
           END      AS order
FROM       pg_index AS i
join       pg_class AS trel
ON         trel.oid = i.indrelid
join       pg_namespace AS tnsp
ON         trel.relnamespace = tnsp.oid
join       pg_class AS irel
ON         irel.oid = i.indexrelid
cross join lateral unnest (i.indkey) WITH ordinality    AS c (colnum, ordinality)
left join  lateral unnest (i.indoption) WITH ordinality AS o (OPTION, ordinality)
ON         c.ordinality = o.ordinality
join       pg_attribute AS a
ON         trel.oid = a.attrelid
AND        a.attnum = c.colnum
WHERE      tnsp.nspname='public' -- can be replaced
AND        trel.relname='test1' -- can be replaced
GROUP BY   tnsp.nspname,
           trel.relname,
           irel.relname,
           a.attname,
           array_position(i.indkey, a.attnum),
           o.OPTION ;


PLPGSQL function(multiple column output)--gives error create or replace function help_constraint(p_schemaname text, p_tablename text)
  returns table(constraint_type text, constraint_name text, delete_action text, update_action text, status_enabled boolean, status_for_replication text, constraint_keys text)
as
$body$
  SELECT
 case when substr(tc.constraint_name, 1, 3) = 'ck_' and tc.constraint_type = 'CHECK' then 'CHECK Table level '
when tc.constraint_type = 'CHECK' then 'CHECK on column ' || ccu.column_name
when tc.constraint_type = 'PRIMARY KEY' then 'PRIMARY KEY (clustered)'
 when tc.constraint_type = 'DEFAULT' then 'DEFAULT on column ' || ccu.column_name
else '' end AS constraint_type,
tc.constraint_name,null as delete_action,null as update_action,null as status_enabled,
null as status_for_replication,coalesce(cc.check_clause, ccu.column_name) as constraint_keys,null as data_compression,null as default_uid,null as partition_qty,ccu.column_name
from information_schema.table_constraints AS tc
left JOIN information_schema.constraint_column_usage ccu ON tc.constraint_catalog = ccu.constraint_catalog
 AND tc.constraint_schema = ccu.constraint_schema
 AND tc.constraint_name = ccu.constraint_name
left JOIN information_schema.check_constraints cc ON  tc.constraint_catalog = cc.constraint_catalog
 AND tc.constraint_schema = cc.constraint_schema
 AND tc.constraint_name = cc.constraint_name
WHERE lower(tc.constraint_type) in ('foreign key','primary key','check')
and lower(ccu.table_schema) = 'compare' and lower(ccu.table_name) = 'testpostgresql'
union all
SELECT 'DEFAULT on column ' || cc.column_name,
'DF_' || cc.column_name as constraint_name,null as delete_action,null as update_action,null as status_enabled,
null as status_for_replication,column_default as constraint_keys,null as data_compression,null as default_uid,null as partition_qty,cc.column_name
FROM information_schema.columns cc
WHERE (table_schema, table_name) = ('p_schemaname', 'p_tablename')
and column_default is not null;
$body$
language sql;

ERROR:  return type mismatch in function declared to return record
DETAIL:  Final statement returns text instead of boolean at column 5.
CONTEXT:  SQL function "help_constraint"

It seems you simply want to return the result of the query. To do that, the function needs to be declared as a set returning function. Your current function is a scalar function that returns a single character (char).

You also don't need dynamic SQL or even PL/pgSQL for that:

create or replace function help_constraint(p_schemaname text, p_tablename text)
  returns table(constraint_type text, constraint_name text, delete_action text, update_action text, status_enabled boolean, status_for_replication text, constraint_keys text)
as
$body$
  SELECT tc.constraint_type,
         tc.constraint_name,
         null::text as delete_action,
         null::text as update_action,
         null::boolean as status_enabled,
         null::text as status_for_replication,
         coalesce(cc.check_clause, ccu.column_name ) as constraint_keys
  from information_schema.table_constraints AS tc
    JOIN information_schema.constraint_column_usage ccu
           ON tc.constraint_catalog = ccu.constraint_catalog
          AND tc.constraint_schema = ccu.constraint_schema
          AND tc.constraint_name = ccu.constraint_name
    left JOIN information_schema.check_constraints cc
           ON tc.constraint_catalog = cc.constraint_catalog
          AND tc.constraint_schema = cc.constraint_schema
          AND tc.constraint_name = cc.constraint_name
  where tc.constraint_type IN ('FOREIGN KEY', 'PRIMARY KEY', 'CHECK')
    and ccu.table_schema = p_schemaname
    and ccu.table_name = p_tablename;
$body$
language sql;

Note that I change the first left join to an inner join as the where condition on ccu makes it an inner join  anyways.

To use that function use:

select *
from help_constraints('public', 'my_table');


Disallowing MariaDB query cache except from webserver (JDBC) I have an application server (Play Framework), webserver (Caddy), and database server (MariaDB with InnoDB) that are running on a single machine for now.

The database is used in two ways:

client Python code that makes highly specific SELECTs (over an SSH tunnel).
the web/application server, which performs occasional INSERTs and UPDATEs, but mostly SELECTs from large tables each time a page loads.

Is there a simple way to enable (and force) query caching for the webserver but not for the client code? My thought was to set query_cache_type=DEMAND and use SQL_CACHE on the webserver, but it's not ideal. Perhaps I can force JDBC to use SQL_CACHE somehow?

Clarification:
The unfortunate technical issue with adding SQL_CACHE to every query is that the web application queries using Slick and a JDBC backend. The integration with Slick is tight so I can't rewrite the application layer, and I don't know how to force JDBC to append SQL_CACHE.

If there is a lot of write activity, the Query Cache is virtually useless -- because all QC entries for a table are purged when any modification happens to the table.

If the QC is useful, using query_cache_type=DEMAND and manually saying SQL_CACHE or SQL_NO_CACHE on every SELECT may be optimal.  This keeps the rare, bulky, queries from cluttering the cache, while allowing the 'good' ones to make use of the QC.  Note:  This has nothing to do with the connector (JDBC, etc), only with the SELECT statements.

Yes, DEMAND without SQL_CACHE leads to not using the QC.  I agree that this is "not ideal" since it depends on un-obvious rules about the QC.  Still, it may be your best solution, other than...

If a query is slow slow that it matters, we should look critically at the query.  The speed-up may be as simple as adding a 'composite' index, or some seemingly minor tweak to the query such as not hiding an indexed column in a function call (eg, DATE(dt) = ...).


AWS KMS Encryption enable for SQL Server Columns? Is it possible MSSQL on EC2 with a key in AWS KMS to encrypt columns

As per sql server blog here On SQL Server side, it is supported to use a custom key store provider for Always Encrypted, but the implementation/support of the custom key store provider comes from the service provider itself, which in this case is the AWS KMS.

For further your ref here


getting the error message from another trigger in sql server i have a table 'Event' and it contains a trigger trEvent which updates table 'Position', and table 'Position' has a trigger trPosition which validates the Position records.
whenever trEvent is fired one record in Position table is updated and the trPosition trigger is fired to check the validation of position, if it is a valid position there is no problem but if the position is invalid how the trEvent should know there is something wrong with the Position which is updated.

in simple words how to access error message from trPosition trigger in trEvent trigger?

thanks in advance!

Based on the problem description, I tried to create a similar scenario including the solution. Hope this will help you.

Here I created tables and triggers.

Trigger trgEvents inserting the EventId into tbl_Position table if the EventId does not exists.

In the trigger trgPosition created on tbl_Position, I'm just validating whether column PositionId in tbl_Position is having a numeric value.

DROP TABLE IF EXISTS tbl_Events
GO
CREATE TABLE tbl_Events
(
EventId int PRIMARY KEY,
EventDescription varchar(512)
);
DROP TABLE IF EXISTS tbl_Position
GO
CREATE TABLE tbl_Position
(
EventId int REFERENCES tbl_Events(EventId),
PositionId varchar(8)
);
GO

DROP TRIGGER IF EXISTS trEvents
GO
CREATE TRIGGER trEvents
ON tbl_Events
FOR INSERT
AS
BEGIN
BEGIN TRY
IF EXISTS (select 'Exists ?' from tbl_Position P join inserted i on P.EventId=i.EventId)
    BEGIN
    UPDATE P SET PositionId=i.EventId+100 from tbl_Position P join inserted i on P.EventId=i.EventId;
    END
ELSE
    BEGIN
    INSERT INTO tbl_Position
    SELECt EventId,'NA' FROM inserted;
    END
END TRY
BEGIN CATCH
ROLLBACK;

DECLARE @Err NVARCHAR(4000) = ERROR_MESSAGE()
        RAISERROR(@Err, 16, 10);
END CATCH
END
GO

DROP TRIGGER IF EXISTS trPosition
GO
CREATE TRIGGER trPosition
ON tbl_Position
FOR UPDATE,INSERT
AS
BEGIN
IF (SELECT isnumeric(PositionId) from inserted)=1
    BEGIN
    Print 'PositionId has Numeric value'
    END
ELSE
    BEGIN
    RAISERROR ('PositionId has Non-Numeric value', 16 ,10)
    ROLLBACK;
    END
END

After I insert bellow execute the INSERT statement below.

INSERT INTO tbl_Events(EventId,EventDescription)
VALUES(1,'Dance compatition in New Delhi')

It fails and complete transaction gets rolled back.

(0 rows affected)
Msg 50000, Level 16, State 10, Procedure trEvents, Line 21 [Batch Start Line 62]
PositionId has Non-Numeric value
Msg 3609, Level 16, State 1, Line 63
The transaction ended in the trigger. The batch has been aborted.

Thanks!


sp_Blitz: Poison wait detected and too much free memory We have a website for SolarWinds Orion which utilizes SQL and we've had many reports of slowness issues with the website. I ran sp_Blitz on the SQL box and I'm seeing these errors:

Poison Wait Detected: RESOURCE_SEMAPHORE
98:07:56:03 of this wait have been recorded. This wait often indicates killer performance problems.

Poison Wait Detected: RESOURCE_SEMAPHORE_QUERY_COMPILE
33:06:03:08 of this wait have been recorded. This wait often indicates killer performance problems.

Too Much Free Memory
7GB of free memory inside SQL Server's buffer pool, which is 12GB. You would think lots of free memory would be good, but check out the URL for more information.

The server has been provisioned 16GB of RAM and SQL server is configured to use 13GB of maximum server memory, 1024KB per query. Is this server over provisioned? I feel like its a memory misconfiguration but any assistance or insight would be appreciated.

This is not an over-provisioning issue or even a memory misconfiguration.

RESOURCE_SEMAPHORE: "Occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts."

Depending on which version and build you are using of SQL Server, you may be able to query for large memory grants so that you know where to start on which queries are causing a problem. If you are on SQL 2012 SP3 or greater, SQL 2014 SP2 or greater or SQL 2016 RTM or greater, then you can query for this information. See the query at the bottom of New memory grant query hint MIN_GRANT_PERCENT came to rescue.

Check out Query Plans: Memory Grants and High Row Estimates for more information on memory grants, which is the link inside the link sp_Blitz directed you to.


how to query for rows by specifying the range of row numbers in postgresql I have an application where in the user can see the history of all questions he has posted. These are have to be displayed in the order newest to oldest. I am implementing this in Android.
I have two questions
1) I have not stored the time at which the question was asked. Does postgres, internally store the timestamp of when the row was inserted?

2) In a mobile device, only a few posts can be displayed at a time. So when the user scrolls, the new rows should be displayed. So in the database, is it possible to query like give me the top 10 rows, 10-20 rows and so on.

1) I have not stored the time at which the question was asked. Does postgres, internally store the timestamp of when the row was inserted?

No.  You can create one by using TIMESTAMP WITH TIME ZONE (timestamptz for short) and setting a default using an appropriate function.  (You can choose the timestamp of the start of database transaction, the start of execution of the INSERT statement, or the moment the row is processed -- whichever best suits your needs.)  Don't be tempted to use TIMESTAMP WITHOUT TIME ZONE (the default if you just say TIMESTAMP) -- you would regret that the first time you hit a daylight saving time boundary.

  2) In a mobile device, only a few posts can be displayed at a time. So when the user scrolls, the new rows should be displayed. So in the database, is it possible to query like give me the top 10 rows, 10-20 rows and so on.

This one is a bit tricky.

You can use non-transactional cursors, but that prevents database maintenance operations for as long as the cursor is open, so you want to make sure that the time is not unbounded.
You can use OFFSET and LIMIT, but that gets slow if you need a big OFFSET, since it has to read through all the preceding rows each time.  It also gets weird if the data is being modified while you are paging around.
You can save values for the ends of what is being displayed and run a query with LIMIT to get the rows just outside that.  This one performs well; the only real down side is programming complexity.
You can run the whole list to storage somewhere outside the database when they first request it, and do your scrolling from that.  It may even be possible to use a temporary table for this, depending on other details.

This is a problem that people are always sorting out, and I don't think that any one answer is the best for all situations.


Backup/Restore File Groups SQL Server I've got a database with 3 Filegroups and each filegroups own a single file, Filegroup names are Primary, SecondFG and ThirdFG. I want to backup ThirdFG separately each time without backing up the others. I've tried it in many ways but when I want to restore database filegroup backup I get the error for other Filegroups or files are not included in the backup file! but When I backup all FileGroups together there would be no problem, please guide me how to backup and restore single filegroup, by the way I've read all BOL and I couldn't solve my problem!

You can back up individual files and filegroups only when your database is using the full or bulk_logged recovery model, because you must apply log backups after you restore a file or filegroup; you can't make log backups of a database that's using the simple model.
Unlike full database and differential backups, a backup of a file or filegroup doesn't back up any portion of the transaction log. So, none of the transaction log is in a file or filegroup restore, and you still need to restore the log.
You can restore individual file or filegroup backups from a full database backup.
Immediately before restoring an individual file or filegroup, you must back up the transaction log. You must have an unbroken chain of log backups from the time the file or filegroup backup was made until the time the files or filegroups are restored.
After restoring a file or filegroup backup, you must restore all the transaction logs made between the time you backed up the file or filegroup and the time you restored it. Restoring these interim transaction logs guarantees that the restored files are synchronized with the rest of the database.


Alter mysql database engine doesn't work from cluster to other I got into this issue when I tried to alter my database table engine to InnoDB from ndbcluster, It did not work. But When I changed engine from InnoDB to ndbcluster it worked.

It did not throw any error but did not work. I just want to know why this did not work as I got it from MySQL site that it won't work. I just need to know the reason for the same.

For client requirement I may need to alter the engine on installed database without losing any data.

Any Ideas or help that anybody can provide to resolve issue.

Why you can't change ndbcluster to InnoDB?

Clustering with MySQL is supported only by the NDB storage engine. ndbcluster/ndb storage engine is meant for clustering if you specify a table with ENGINE=ndbcluster; this means table is being shared with all nodes and simply you can't change it to other ENGINE types, either you should take a backup of this table and then DROP table with ndbcluster option and import by changing ndbcluster to innodb

However, it is possible to create tables using other storage engines (such as InnoDB or MyISAM) on a MySQL server being used with a MySQL Cluster, but since these tables do not use NDB, they do not participate in clustering; each such table is strictly local to the individual MySQL server instance on which it is created.

Migrating from InnoDB to ndbcluster?

There are two ways to migrate InnoDB tables with foreign keys to NDB.

Dump the database and edit the script so each table specifies ENGINE=NDB before re-importing the script to a new database
Drop the constraints, alter the tables to use the NDB engine, and recreate the constraints

Dumping the database and editing the script is a straightforward use of mysqldump and a text editor.

Converting InnoDB Tables to MySQL Cluster


Find multiple rows that share one field value but have one unique value I have tables cards, decks, and a join table card_decks. I want to find the count of a unique set of pairs of cards where the a given attribute is true. I feel like I'm close but there is another step I am missing.

SELECT c.id card_id, d.id deck_id
FROM cards c
INNER JOIN card_decks cd
ON c.id = cd.card_id
INNER JOIN decks d on cd.deck_id = d.id
WHERE planeswalker = true OR signature_spell = true;

This gives me data that is a step closer.

 card_id | deck_id
---------+---------
    9137 |    2787
    9138 |    2787
    9182 |    2788
    9183 |    2788
    9207 |    2789
    9218 |    2789
    9233 |    2790
    9234 |    2790
    9285 |    2791
    9286 |    2791
    9360 |    2793
    9361 |    2793

What I don't understand is how to make 9317 and 9138 a unit that I can see if the pair exists in any other decks. I know there is a CONCAT function, but I'm not sure how to CONCAT two different rows like this.

Or more simply I want to find the count of all pairs of cards where a pair is one card with planeswalker true and one card with signature_spell true per deck.

EDIT 1:

Sorry about the poorly stated original question.

Here is the sample data:

create table cards (
    id INT,
    name VARCHAR(50),
    planeswalker BOOLEAN,
    signature_spell BOOLEAN
);
insert into cards (id, name, planeswalker, signature_spell) values
    (1, 'Norby', false, true),(2, 'Johanna', true, false),
    (3, 'Killian', false, true), (4, 'Madalyn', false, false),
    (5, 'Queenie', true, false),(6, 'Mort', true, false),
    (7, 'Nedda', false, true),(8, 'Pennie', true, false),
    (9, 'Fabien', false, true),(10, 'Fiann', true, false);

create table decks (
    id INT,
    name VARCHAR(50)
);
insert into decks (id, name) values
    (1, 'Buzzshare'),(2, 'Mymm'),(3, 'Pixope'),(4, 'Zazio'),
    (5, 'Shufflester'),(6, 'Youbridge'),(7, 'Avavee'),
    (8, 'Thoughtblab'),(9, 'Skiptube'),(10, 'Eare');

create table card_decks (
    id INT,
    card_id INT,
    deck_id INT
);
insert into card_decks (id, card_id, deck_id) values
    (1, 1, 1),(2, 2, 1),(6, 7, 2),(4, 6, 2),
    (3, 3, 3),(5, 5, 3),(7, 1, 4),(8, 2, 4);

The result I would like for the given sample data:

 pw_id   | ss_id   |  count
---------+---------+----------
    1    |    2    |    2
    6    |    7    |    1
    5    |    3    |    1

Where pw_id is a card id with planeswalker = true and ss_id is a card id with signature_spell = true, and the count is how many decks each pair of cards exist.

Let me know if I need to add anything else.

Join card_decks and cards twice.

SELECT pw.id AS pw_card_id,ss.id AS ss_card_id, d.id AS deck_id
FROM decks d
INNER JOIN card_decks ssd on ssd.deck_id = d.id
INNER JOIN card_decks pwd on pwd.deck_id = d.id
INNER JOIN cards pw ON pw.id = pwd.card_id AND pw.planeswalker
INNER JOIN cards ss ON ss.id = ssd.card_id AND ss.signature_spell ;

you can also group the result

SELECT pw.id AS pw_card_id,ss.id AS ss_card_id, count(*)
FROM decks d
INNER JOIN card_decks ssd on ssd.deck_id = d.id
INNER JOIN card_decks pwd on pwd.deck_id = d.id
INNER JOIN cards pw ON pw.id = pwd.card_id AND pw.planeswalker
INNER JOIN cards ss ON ss.id = ssd.card_id AND ss.signature_spell
GROUP BY 1,2 order by 3 desc, 2, 1;


ORA-02049 Timeout: Distributed Transaction Waiting for Lock Solution needed We are cleaning up the last year's transactions from our primary database. We use public database link to delete the records a day by day from another database. Each days records are around 30-40k, but somedays transaction peak to 1 million records. When the delete procedure faces such days, it locks the primary database and causing lock. What would be the ideal solution for clearing the records? We also tried to use alter table truncate partition solution. However, the lock situation is worse in this one. As we clear the database, the new records are being written to the database in real-time. So I cannot restart or increase the distributed_lock_timeout parameter. Any solution is appreciated.

First of all do not use database links for larger transactions. This can cause too many problems with blocking sessions. Another hint is to keep transactions in a good size (not to small, not to big).

If your table is not partitioned then write a piece of code to remove just 1000 rows, commit and delete the next 1000 rows.

You said something about alter table <table_name> truncate partition <partition_name>; Is this table partitioned?

Why do you want to delete data from a partitioned table on daily basis? Is the table partitioned the same way?

To archive an entire partition you best exchange it with an empty table of the same structure. Afterwards you can export/backup the table and then drop it. If your partition is in a dedicated tablespace you can also mark it as read only and skip it in the daily backup (enable backup optimization in RMAN).


Store Mysql Index in separate .Ibd file? Is there any option to store table index in separate .ibd file? something like file_per_table mode? Becuase my table is growing more than 200gb. Table .ibd file have index for that table also. so If index kept in another file, then table .idb file size will be reduced.

Thanks

In theory it's possible. What you call a table in InnoDB is a B+tree index named PRIMARY. The key in the PRIMARY index is the primary key field(s).

All secondary indexes are B+tree indexes, too. The key would be the secondary index field(s). The value is the primary key.
(See my slides to get more details http://www.slideshare.net/akuzminsky/indexes-in-my-sql-aleksandr-kuzminsky-https-twindbcom?ref=https://twindb.com/efficient-use-of-indexes-in-mysql/)

Although logically the PRIMARY and secondary indexes are separate they're still stored in one .ibd file (that's assuming innodb_file_per_table=ON). There is no way to store them in separate files.


Automated UML2 Class Diagram generation from SQL? How can I generate a class diagram from an SQL database or script?  I'll add the functions and set whether the attributes are public, private or virtual once the initial diagram is generated.

I've generated it using PowerDesigner, here is the .sql file: http://pastebin.com/S14JWjKF

Solution:

Generate SQL file in PowerDesigner (or any other tool capable of
generating SQL, or write it by hand)
Install Eclipse with JPA and Modelling Tools
Follow this guide
When the guide fails you (step 4), open up another tool (or you
could probably do it in Eclipse with a new project), for instance,
PowerDesigner; and reverse-engineer the Java code into a Class
diagram
Profit!

:D


Postgres cascades truncates to unrelated tables I've created a number of tables in my public schema.

Occasionally, I need to run a seeder to refresh the users table when I perform tests. To do this, I'm executing TRUNCATE TABLE users RESTART IDENTITY CASCADE.

However, I do not want any other tables but those related to the users table to be affected. The only foreign key referencing the users table is on the teams table which references users through owner_id. The teams table truncates as expected.

What isn't expected, is that the attributes table is also truncated by TRUNCATE TABLE users RESTART IDENTITY CASCADE.

The attributes table has one foreign key on the accounts table which references the users table. However, on the attributes table, ALL account_id values are NULL so there is no actual reference back on any of the records to a specific record in the accounts table. In fact, at the time of executing the truncate query, the entire accounts table is already empty.

How then is TRUNCATE TABLE users RESTART IDENTITY CASCADE causing my attributes table to be truncated based on NULL foreign keys?

I think TRUNCATE ... CASCADE doesn't do any reference checking on foreign key values, that would defeat the purpose. The mere presense of referential integrity constraints connecting attributes to users via accounts causes all three (and possibly more) tables to be truncated (which you would have noticed if accounts weren't empty).


Always return X rows with variable starting row My goal is to return X rows from a table, where the value of X is some value in the low 100's.  For the purposes of testing this query, I am experimenting with different X values.

This is a take on pagination for populating a grid in a user interface.  I tried using Offset / Fetch in this query, but I could not get it to function with my @Partial variable.

@Partial is a string that will identify the starting row number and all row numbers that follow the first match.  This is pretty simple until @Partial has returned the last row.  In that instance, I want to return the last row plus the previous X - 1 rows.

My query appears to be functioning, but I feel that I'm over thinking the problem.  I have done hours of testing and online research.  I have provided a full working query that will execute.

When @Partial is null, then the query abides by the @StartRow and @NumWindowRows variables.  Otherwise, @NumWindowRows will be returned, starting or ending with the matched string.

Thanks for any assistance on this query.  To recap, I am getting my intended results, but I am looking for guidance on a simpler approach or method to improve efficiency.

/***
Dynamic variables
******/
DECLARE
@StartRowNum INT = 1
,@NumWindowRows INT = 3
,@Partial NVARCHAR(10) = 'M';

/*****
Test table
*******/
DECLARE @Customers TABLE (
    CustomerID VARCHAR(10)
    ,[Name] VARCHAR(155)
    );
/***
Generate test data
*****/
INSERT INTO @Customers(CustomerID, [Name])
SELECT Char(number+65), 'Customer ' +Char(number+65)
  FROM master.dbo.spt_values
 WHERE name IS NULL AND
       number < 26

/***
Return @NumWindowRows
******/

;WITH FullResult AS (
        SELECT ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNum
        ,COUNT(*) OVER () AS FullCount
        ,CustomerID
        ,[Name]
        FROM @Customers),
PartialResult AS (SELECT *, COUNT(*) OVER () AS FullCount
        FROM
        (SELECT ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNum
            ,CustomerID
            ,[Name]
         FROM @Customers) c
         WHERE CustomerID >= @Partial)

SELECT f.*
FROM FullResult f
LEFT OUTER JOIN PartialResult p ON f.RowNum = p.RowNum
WHERE (@Partial IS NULL AND f.RowNum >= @StartRowNum
        AND f.RowNum <= @NumWindowRows + @StartRowNum -1)
OR (f.RowNum >= CASE
                WHEN (SELECT MAX(FullCount) FROM PartialResult) < @NumWindowRows
                THEN ((SELECT MIN(RowNum) FROM PartialResult) - (@NumWindowRows - (SELECT MAX(FullCount) FROM PartialResult)))
                ELSE (SELECT MIN(RowNum) FROM PartialResult)    END
        AND f.RowNum <= CASE
                WHEN (SELECT MAX(FullCount) FROM PartialResult) > @NumWindowRows
                THEN ((SELECT MAX(RowNum) FROM PartialResult) - ((SELECT MAX(FullCount) FROM PartialResult) - @NumWindowRows))
                WHEN (SELECT MAX(FullCount) FROM PartialResult) + @StartRowNum < @NumWindowRows
                THEN f.FullCount
                ELSE (SELECT MAX(RowNum) FROM PartialResult) END
            )

Okay, so we need to find out how many rows are in the table, and how many rows are left once we've moved to the marker defined by @Partial.

-- simple variable to hold the offset:
DECLARE @offset int;

;WITH x(o,c) AS
(
  SELECT
    -- how many rows are at or past the @Partial marker?
    SUM(CASE WHEN CustomerID >= @Partial THEN 1 END),
    -- how many rows total?
    COUNT(*)
  FROM @Customers
 )
 -- the magic: a conditional that determines if we're
 -- too close to the end, and subtracts the difference
 -- between how close we are and how close we want to be
 SELECT @offset = (c-o) - CASE
   WHEN @NumWindowRows > (c - (c-o)) THEN
       (@NumWindowRows - (c - (c-o)))
   ELSE 0 END
FROM x;

SELECT
  ROW_NUMBER() OVER (ORDER BY CustomerID),
  CustomerID,
  Name
FROM @Customers
ORDER BY CustomerID
OFFSET @offset ROWS
FETCH NEXT @NumWindowRows ROWS ONLY;

You could probably do this all inline instead of using the @offset variable, but it gets messy very fast, and I don't think it's possible to avoid the additional scan required to derive those counts anyway.

This is far simpler than your logic. But it assumes that you will always order by CustomerID and never add additional filters. If there are less than @NumWindowRows rows in the table, well...


ib_logfile opened with O_SYNC when innodb_flush_method=O_DSYNC mysql will open and flush data/log files as below when innodb_flush_method=O_DSYNC

Open log    Flush log   Open datafile   Flush data

O_SYNC       NONE           NONE         fsync ()

My First question is ,why do we need to Use O_SYNC to open log instead of O_DSYNC ?

With O_DSYNC, each transaction require one write, but with O_SYNC it requires two writes. I think that would make a big difference.

And I found another option in percona server  ALL_O_DIRECT: use O_DIRECT to open both data and log files, and use fsync() to flush the data files but not the log files.

Tested this option use sysbench OLTP , single thread

response time changes from 20.11ms to 8.3 ms

Test option O_DIRECT_NO_FSYNC on mysql 5.7, it still call fsync to flush log files

  strace -p `pgrep -x mysqld`  -ff -e trace=desc >strace.out 2>&1 &
  tail -f strace.out |grep "(8\|(9"

  [pid 12258] pwrite(9, "\200\10K\376\2\0\0N\0\0\0L.\0\4\0\2018\0300\2018\2\0\2018\0304\226\240\4\0"..., 1024, 26740736) = 1024
  [pid 12258] fsync(9)                    = 0
  [pid 12258] pwrite(9, "\200\10K\377\2\0\0\25\0\0\0L\10\215\247\3\4\0\0\0\1\247\f\240\37\0\6\0\1\200\4\200"..., 1024, 26741248) = 1024
  [pid 12258] fsync(9)                    = 0

Second Question ,beside use ALL_O_DIRECT, any other method could stop innodb from writing metadata of log file into storage device ?

Yes, this O_DSYNC (the option) is fundamentally useless because MySQL/InnoDB engineers saw that it was dangerous in certain cases (corruption), and disabled it in many cases, making it a synonym to O_SYNC. it also does an fsync() for the same reason.

The main thing you want to tune is O_DIRECT vs. default (flushed), and maybe ALL_O_DIRECT on Percona.

In your case, I suppose you have good latency to the filesystem (where O_DIRECT shines). Sometimes, having the transaction logs on the filesystem cache can be hurtful (although such a difference seem a bit too much for me - did you compare it with O_DIRECT, available on all versions of MySQL, which only avoids data file caching?).

Also be careful, because sysbench may not be a good representative of your real-world load. Response times can vary a lot depending on flush_log_at_trx_commit, I assume you have that as 1.

According to Dimitri, from Oracle-MySQL, the solution upstream comes in 5.7. I like it for now in Percona, as you can choose.


Party Relationship Model in Practice - Insertions I'm moving this question for StackOverflow in hopes to get perspective from a more database-oriented community.

Here is a basic representation of what I'm doing (in pseudo sql):

Parties
- ID, AUTO_INCREMENT
- Type
- Shared_Data
- Primary Key (ID, Type)

User_Sub
- ID
- Type, Default 'U'
- User_Data
- Primary Key (ID, Type)
- Foreign Key 'User-Party' (ID, Type) references (Parties.ID, Parties.Type)

Organization_Sub
- ID
- Type, Default 'O'
- Organization_Data
- Primary Key (ID, Type)
- Foreign Key 'Organization-Party' (ID, Type) references (Parties.ID, Parties.Type)

Because data is spread across multiple tables, I'd like to be able to read from and insert into the parent and child tables at once. Reading is fine, and can be done with a:

CREATE VIEW Users as
SELECT P.*, U.User_Data FROM Parties P
Inner Join Users_Sub U on P.id=U.id, P.type=U.type

Inserting, though, is more difficult.

I can do two consecutive queries: One into the parties table, then one into a sub table.
I can create a stored procedure in order to perform multiple queries in one transaction.
This answer mentioned moving columns into the super table and using a trigger after insert on the super table to insert into the sub table.

It seems to me that it might be more fitting to consolidate these sub-table columns back into the super-table and get rid of the sub-tables entirely. Since I'd probably need to join or union all of the subtables if I wanted to query values from the parties table anyway.

Is this generally how it is done in practice? There will be many unused columns if this is the case, and it will be difficult (though not impossible with triggers) to enforce 'not null' constraints. The case above shows only two sub table types - as the number of sub-types increase, the super table will become more bloated.

Any thoughts or suggestions on this would be much appreciated.

How about another answer?  Get rid of the Party table entirely.  I'm a passionate hater of the Party model as it causes vastly more problems than it solves.

However, since it's unlikely you're in the position to do such a thing:

  I can do two consecutive queries: One into the parties table, then one
  into a sub table.

There's nothing per-se wrong with this as long as you do your transaction control in your app.  It's not what I would suggest though.

  This answer mentioned moving columns into the super table and using a
  trigger after insert on the super table to insert into the sub table.

This is nasty from a model perspective.  If you go this route you have attributes that apply only to organizations intermingled with attributes that are only for users.  It becomes quite a mess and you just 'have to know' what is right and wrong.  It is a significant impediment to clarity.

  I can create a stored procedure in order to perform multiple queries
  in one transaction.

This is your best option.  It maintains atomicity while at the same time encapsulating and decoupling the data access from your app.  Do this.

UPDATED:

To clarify my point about the Party model.  The following are my major beefs with the approach:

There is no such thing as Party.  That is, there is no natural key that identifies a 'Party' in real life.  As such, it is a meaningless abstraction that leads to ...
Models inevitably base everything off Party.  Foreign keys always back to Party rather than the 'subclasses' (that aren't really subclasses).  The perception is that this makes matters simpler because it avoids n:n relationships along with intersection tables and so forth.  However, it ends up completely hiding what the model actually is.  Some things are related ONLY to User and some things are related ONLY to Organization.  Nothing is ever related to a 'Party', in reality.  So it is a physical 'optimization' that results in extreme logical confusion. It becomes completely impossible to determine the model by simply reading it.  You just simply have to know that certain things are allowed and certain things aren't.  Inevitably this ends up in model confusion, code mistakes/complexity, report inaccuracies, etc.  It also leads to other evils like not taking keys seriously.


SQL Server: Time Series Table Design I am creating a time series database that follows the table structure here: Storing intervals of time with no overlaps.  The difference with my design is that the FinishedAt column changes every time a user updates that particular record (you can actually see my question on his site at the bottom).

Basically, I have a FinishedAt column that has the current record with a date of 9999-12-31 until the next record with the same business key gets updated (in his example that key would be the SettingID). I run into the problem with his FK_IntegerSettings_SettingID_PreviousFinishedAt since I cannot make FinishedAt part of my primary key. I cannot make it part of my primary key because it is always changing....but is it okay to make FinishedAt part of my primary key, which would involve updating the primary key? Is this frowned upon even in a time series table?

I realize I can still use his other constraints, but I loose the very important foreign key constraint. This is my first encounter with this type of design so any direction will be appreciated. Thank you.

A foreign key can reference a unique constraint, not necessarily only a primary key, so if you want to be a purist, include FinishedAt (and whatever else you need) in the parent table's unique constraint and use that to define the foreign key.


Mysql: Insert performance INNODB vs MYISAM I am inserting into a simple small table with 5 attributes and 1000 rows.

I observed when the engine is INNODB, each insert is taking 0.03 - 0.05 seconds.
I changed the engine to MYISAM, then the inserts are faster. it is taking 0.001 - 0.003.

What is the problem. innodb_flush_log_trx_commit = 1 by default. I was this setting as it is.
Here are my innodb setting.

innodb_log_buffer_size : 1MB
innodb_log_file_size   : 5MB
innodb_buffer_pool_size: 8MB
innodb_flush_log_trx_commit = 1

mysql> desc table ;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(10) | YES  |     | NULL    |       |
| count | int(10) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

VARIABLES

mysql> show variables like 'innodb%' ;
+-----------------------------------------+------------------------+
| Variable_name                           | Value                  |
+-----------------------------------------+------------------------+
| innodb_adaptive_hash_index              | ON                     |
| innodb_additional_mem_pool_size         | 1048576                |
| innodb_autoextend_increment             | 8                      |
| innodb_autoinc_lock_mode                | 1                      |
| innodb_buffer_pool_size                 | 8388608                |
| innodb_checksums                        | ON                     |
| innodb_commit_concurrency               | 0                      |
| innodb_concurrency_tickets              | 500                    |
| innodb_data_file_path                   | ibdata1:10M:autoextend |
| innodb_data_home_dir                    |                        |
| innodb_doublewrite                      | ON                     |
| innodb_fast_shutdown                    | 1                      |
| innodb_file_io_threads                  | 4                      |
| innodb_file_per_table                   | OFF                    |
| innodb_flush_log_at_trx_commit          | 1                      |
| innodb_flush_method                     |                        |
| innodb_force_recovery                   | 0                      |
| innodb_lock_wait_timeout                | 50                     |
| innodb_locks_unsafe_for_binlog          | OFF                    |
| innodb_log_buffer_size                  | 2097152                |
| innodb_log_file_size                    | 5242880                |
| innodb_log_files_in_group               | 2                      |
| innodb_log_group_home_dir               | ./                     |
| innodb_max_dirty_pages_pct              | 90                     |
| innodb_max_purge_lag                    | 0                      |
| innodb_mirrored_log_groups              | 1                      |
| innodb_open_files                       | 300                    |
| innodb_rollback_on_timeout              | OFF                    |
| innodb_stats_method                     | nulls_equal            |
| innodb_stats_on_metadata                | ON                     |
| innodb_support_xa                       | ON                     |
| innodb_sync_spin_loops                  | 20                     |
| innodb_table_locks                      | ON                     |
| innodb_thread_concurrency               | 8                      |
| innodb_thread_sleep_delay               | 10000                  |
| innodb_use_legacy_cardinality_algorithm | ON                     |

+-----------------------------------------+------------------------+
36 rows in set (0.00 sec)

I could not able to figure out what went wrong with this.
    Thanks in advance.
    Regards,
    UDAY

The reason is very simple. When you insert a row into MyISAM, it just puts it into the server's memory and hopes that the server will flush it to disk at some point in the future. Good luck if the server crashes.

When you insert a row into InnoDB it syncs the transaction durably to disk, and that requires it to wait for the disk to spin.  Do the math on your system and see how long that takes.

You can improve this by relaxing innodb_flush_log_at_trx_commit or by batching rows within a transaction instead of doing one transaction per row.

I highly recommend reading High Performance MySQL 3rd Edition (I am the author).


UPDATE heap table -> Deadlocks on RID I'm setting up a test case to prove a certain deadlock scenario and require some insight on what is going on.
I have a heap table, conventiently called HeapTable. This table is updated by 2 transactions simulateously.

Transaction 1:

BEGIN TRAN

UPDATE HeapTable
SET FirstName = 'Dylan'
WHERE FirstName = 'Ovidiu';

WAITFOR DELAY '00:00:15';

UPDATE HeapTable
SET FirstName = 'Bob'
WHERE FirstName = 'Thierry';

ROLLBACK TRANSACTION

Transaction 2:

BEGIN TRAN

UPDATE HeapTable
SET FirstName = 'Pierre'
WHERE FirstName = 'Michael';

ROLLBACK TRAN

I fire off transaction 1 first, closely followed by transaction 2. As expected transaction 1 will claim some exclusive locks, together with some intent exclusive ones. Transaction 2 will come in and request an Update lock on the same RID:

spid dbid   ObjId       IndId   Type    Resource     Mode   Status
55    5     711673583   0       RID     1:24336:10   X      GRANT
57    5     711673583   0       RID     1:24336:10   U      WAIT

I was kind of surprised to see the second transaction ask for an Update lock on the same RID, since I thought this pointed to a single record & both update statements handle different data. I was somehow expecting a conflict on page level instead.

When the second update of transaction 1 kicks in transaction 2 will be seen as deadlock victim resulting in a rollback of transaction 2 & completion of transaction 1.

Can someone explain me why the second transaction would require an update lock on the same RID although updating a different record?

I know how to fix this (e.g. with an index). I'm not looking for a fix, I'm actually looking for an explanation to why 2 Updates handling different records in a heap would want to lock the same RID. I am using read committed isolation. There are no nonclustered indexes on the table.

Without an index on FirstName, SQL Server has to check every row to see if it qualifies for the UPDATE.

It takes an update U lock when reading each row to prevent a common deadlock scenario. It could take a shared S lock, but that would still be blocked by the exclusive X lock held by the first transaction.


MongoDB - The connection is not disconnected for a long time I am building a system that outputs information such as visit information and page transition of the user who visited the website to MongoDB, The MongoDB connection pool of the server of the web site became MAX and it ceased to MongoDB from the web site.

The MongoDB service is configured with the following configuration.

mongos server#1 service：mongos、config mongos server#2 service：mongos、config、rs01arbiter mongos server#3 service：mongos、config、rs02arbiter node#1 service：rs01(secondary)、rs02(secondary) node#2 service：rs01(primary)、rs02(primary)

※Originally the following state is set, but OS is restarted, primary has dropped to node # 2, but it is left as it is node#1 service：rs01(primary)、rs02(secondary) node#2 service：rs01(secondary)、rs02(primary)

From this state, we restarted the OS of node # 2. rs 01 and rs 02 of node # 1 were promoted to primary. rs01, rs02 of node # 2 demoted to secondary.I confirmed it from the log.

However, it happened that more connections would take more than 1 to 2 hours from OS reboot on node # 2 to expire. And the MongoDB connection pool of the server of the web site became MAX and it became not connected to MongoDB from the web site. Normally it will be out of connection in about 30 minutes at the longest. I thought that node # 1 works as Primary, so I thought it would work normally, but I do not know why the connection is not disconnected for a long time.

If you encounter similar events, please let me know if anyone knows the solution or workaround.

appendix
The version of MongoDB is "MongoDB 3.0.12". The application connected to MongoDB is built with .net c # and is running on Windows Server 2012 R2. TCP Keepalive remains the default setting. Also when connecting from application socketOptions.keepAlive has not changed. The connectionString of the application also does not set the option as follows.

From this state, we restarted the OS of node # 2. rs 01 and rs 02 of
  node # 1 were promoted to primary. rs01, rs02 of node # 2 demoted to
  secondary.I confirmed it from the log.

  However, it happened that more connections would take more than 1 to 2
  hours from OS reboot on node # 2 to expire. And the MongoDB connection
  pool of the server of the web site became MAX and it became not
  connected to MongoDB from the web site. Normally it will be out of
  connection in about 30 minutes at the longest. I thought that node # 1
  works as Primary, so I thought it would work normally, but I do not
  know why the connection is not disconnected for a long time.

As per MongoDB documentation here The priority settings of replica set members affect both the timing and the outcome of elections for primary. Higher-priority members are more likely to call elections, and are more likely to win. Use this setting to ensure that some members are more likely to become primary and that others can never become primary.

The value of the member’s priority setting determines the member’s priority in elections. The higher the number, the higher the priority.

  Note:
  As you said that the version 3.0, this version of the manual is no longer supported. Learn
  more about upgrading your version of MongoDB.

For further your ref here and here


How can I limit a hierarchical query to the point where there are multiple children? This question is related to my previous question about hierarchical queries.

I have a hierarchical query in Oracle 11gR2 that returns something like this:

- Level 1
  - Level 2
    - Level 3
      - Level 4
        - Level 5
          - Level 6
      - Level 4
        - Level 5
          - Level 6
            - Level 7

I'd like to design a query to return the rows for levels 1-3, on the basis that there are two siblings at level 4. Or, to put it another way, I'd like to return all the rows in the hierarchy, starting from a given point, until I reach a level with more than one sibling. I'd then like to stop there, excluding the level with multiple siblings.

I've tried putting count functions in various places in my query, but I can't work it out. This is what I have so far:

select   *
  from
    (select   level, count (*) over (partition by level) level_count, <columns>
      from <tables>
        start with <predicate>
        connect by prior <child> = <parent>
    )
  where level_count = 1;

This works for the case there the length of each branch is the same, but returns extra rows otherwise. In my example above, I want to return only the rows for levels 1-3 (i.e. 3 rows in total) and exclude level 7 because level 4 has two siblings. I hope that makes sense!

Simplified with window functions:

with hier as
    (select   level as the_level, <columns>
      from <tables>
        start with <predicate>
        connect by prior <child> = <parent>
    ), levels as
    (select hier.*, count(*) over (order by the_level) as count_items
      from hier
    )
  select levels.*
    from levels
    where the_level = count_items ;


Using mysqldump to initialize slave database I've facing an issue I don't really understand.

I wan't to synchronize a slave MariaDB database to a master one.

I try to use the mysqldump option --master-data=1 which is adding a "CHANGE MASTER TO" in the output file to set the bin log file and position for the slave. I'm assuming (as per the doc) than it should be enough for the slave to know where to start.
When I import the file to the slave and start slave, the slave is desynchronized

If I do a "FLUSH TABLES WITH READ LOCK;", SHOW MASTER STATUS etc then at the end manually do a "CHANGE MASTER TO", all if fine and synchronized

I would assume the --master-data=1 would generate the same bin log information as the manual way, but it is not.

Where am I going wrong ?

When setting up replication on a new slave for the first time, you have to explicitly give it all the information it needs:
Suppose you have

Master Host is 10.20.30.40
replication user is 'repuser'
replication password is 'reppass'

First, create the replication user on the Master like this:
CREATE USER 'repuser'@'%' IDENTIFIED BY 'reppass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

Next, enable replication on the Slave like this:
CHANGE MASTER TO
master_host='10.20.30.40',
master_port=3306,
master_user='repuser',
master_password='reppass',
master_log_file='dummy',
master_log_pos=1;

Now, load the mysqldump into the Slave.
Line 23-25 of the dump file has the CHANGE MASTER TO command, but it specifies log file and position only. This will set those values on the Slave.
Finally, run these on the Slave
START SLAVE;
DO SLEEP(10);
SHOW SLAVE STATUS\G

Replication should now be running. You can verify it as running when you see

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master:

Keep running SHOW SLAVE STATUS\G on the Slave until Seconds_Behind_Master is 0
YOUR ACTUAL QUESTION
You initially said

I try to use the mysqldump option --master-data=1 which is adding a "CHANGE MASTER TO" in the output file to set the bin log file and position for the slave. I'm assuming (as per the doc) than it should be enough for the slave to know where to start.

To reiterate what I said earlier

Line 23-25 of the dump file has the CHANGE MASTER TO command, but it specifies log file and position only. This will set those values on the Slave.

To clarify, when a Slave is initialized with all 6 options for the first time, then you can give it a CHANGE MASTER TO command with just master_log_file and master_log_pos. Using --master-data does not supply host, port, username and password. Even with MySQL 5.7, CHANGE MASTER TO supplies master_host and master_port. Still, username and password are not.


Assembly deployment with permission UNSAFE or EXTERNAL_ACCESS using asymmetric key When we are trying to deploy an assembly with PERMISSION_SET=UNSAFE or EXTERNAL_ACCESS, we are getting following error message by SQL Server:

 CREATE ASSEMBLY for assembly 'System.ServiceModel.Internals' failed because
 assembly 'System.ServiceModel.Internals' is not authorized for
 PERMISSION_SET = UNSAFE.  The assembly is authorized when either of the
 following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission
 and the database has the TRUSTWORTHY database property on; or the assembly
 is signed with a certificate or **an asymmetric key that has a
 corresponding login with UNSAFE ASSEMBLY permission.**

We are not setting database trustworthy=ON, instead we are creating asymmetric key using  ALGORITHM = RSA_2048. Following is the sample code:

USE master;
GO

IF NOT EXISTS (SELECT 1 FROM sys.asymmetric_keys WHERE [name]='SampleClrKey')
BEGIN
      CREATE ASYMMETRIC KEY [SampleClrKey] WITH ALGORITHM = RSA_2048
      CREATE LOGIN [SampleClrLogin] FROM ASYMMETRIC KEY [SampleClrKey];
      GRANT UNSAFE ASSEMBLY TO [SampleClrLogin];
 END
GO

But this error is not getting resolved. I even provided sysadmin access to SampleClrLogin login for my database and master database, but it did not work. Can you guys please provide some insight to solve this issue.

The problem is that you are creating a random new Asymmetric Key in SQL Server, which does nothing to associate the Login to the Assembly, which is what the Asymmetric Key (or Certificate) is used for.

You need to first sign the Assembly prior to loading it into SQL Server. Whether it is signed with a strong name key (which equates to an Asymmetric Key within SQL Server) or a Certificate, you then create that same Asymmetric Key or Certificate (only the public key is required) in master and then create the signature-based Login from that.

For examples on how to accomplish this (and in a way that can be automated via Visual Studio / SSDT), please see the series I am writing on working with SQLCLR (on SQL Server Central): Stairway to SQLCLR (Levels 7 and 8 in particular).

If you are working with SQL Server 2017 or newer, then there is an additional complication to account for which is not part of the method described in the SQL Server central articles. For that, please see the two options for the revised approach in my posts:

SQLCLR vs. SQL Server 2017, Part 2: "CLR strict security" - Solution 1
SQLCLR vs. SQL Server 2017, Part 3: "CLR strict security" - Solution 2

P.S. Thank you for spending the extra few minutes to not enable TRUSTWORTHY :-)

CLARIFICATION

Since you are trying to load an unsupported .NET Framework library, you won't be compiling that yourself so the steps I outlined above will not work here (though they are still relevant for someone simply misunderstanding and trying to create the Asymmetric Key initially within SQL Server as shown in the question).

But, given that:

You are attempting to load System.ServiceModel.Internals and not System.ServiceModel, and
You are not getting the error about a particular library being "malformed or not a pure .NET assembly"

then you can accomplish this (while keeping TRUSTWORTHY OFF!) by doing the following:

USE [master];
CREATE CERTIFICATE [UnsupportedFrameworkLibraryPermissions]
  FROM EXECUTABLE FILE =
  'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.ServiceModel.Internals.dll';

CREATE LOGIN [UnsupportedFrameworkLibraryPermissions]
  FROM CERTIFICATE [UnsupportedFrameworkLibraryPermissions];

GRANT UNSAFE ASSEMBLY TO [UnsupportedFrameworkLibraryPermissions];

And then:

USE [{your_DB_name_here}];

CREATE ASSEMBLY [System.ServiceModel.Internals]
  FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.ServiceModel.Internals.dll'
  WITH PERMISSION_SET = UNSAFE;

Just keep in mind that this technique does not work with System.ServiceModel.dll. That library went from being pure MSIL pre-.NET 4.0 to mixed in .NET 4.0, and only pure MSIL libraries can be loaded into SQL Server. Using ServiceModel worked in SQL Server 2005, 2008, and 2008 R2 because those versions use CLR 2.0 which is tied to .NET Framework versions 2.0, 3.0, and 3.5 in which ServiceModel was pure-MSIL.

But in general, David Browne is correct in that you should probably be using HttpWebRequest instead (depending on what you need System.ServiceModel.Internals for).


Creating a Persisted Computed Column with a function I am working with the programmers on a database solution.  They want to add a computed column to mimic the old keys for the older queries, procedures, and systems and index it.  The new keys will be GUIDS.

To do this, they want to create a function for the computed column that creates a value and persist it.  It will not let them persist the column.  I don't have any warm fuzzies about the idea and I also can not find any info on the web about the technique (is it a technique?).

I am thinking they need to add a trigger instead. Does anyone have any ideas?

The function will be run as this:

(SELECT [INT Identity field] FROM TABLE WHERE [GUID COLUMN] = @GUIDKEY

It returns an INT Identity field based on the GUID.

This will be run on ever insert into a related table.  SO  if Table One holds the primary key, the related table Two will update (using the GUID passed in) to get the key from the Table one and insert it into table two.

Still don't understand why this needs to be a column in a table, never mind a persisted one.

Why not just create a table-valued function that you cross apply when (and only when) the query actually needs it? Since the old key will never change it doesn't need to be computed or persisted anyway.

If you really want the old key to live in multiple places (and it sounds like people who shouldn't be making this kind of decision have already made this kind of decision), just do the lookup in a trigger and populate it at write time. Then it's just a static column in a table.

I still highly recommend a table-valued function to facilitate this, so that you can write the trigger in such a way that it handles multi-row operations... without having to write a loop, or call a scalar-valued function over and over again for every row.

Just to show how similar these things really are (and question your lead developer who "doesn't like it"):

-- bad, slow, painful row-by-row
CREATE FUNCTION dbo.GetIDByGUID
(
  @GuidKey uniqueidentifier
)
RETURNS int
AS
BEGIN
  RETURN (SELECT $IDENTITY FROM dbo.tablename WHERE guid_column = @GuidKey);
END
GO

-- in the trigger:
UPDATE r SET oldkey = dbo.GetIDByGUID(i.guid_column)
  FROM dbo.related AS r
  INNER JOIN inserted AS i
  ON r.guid_column = i.guid_column;

Now, if you have a table-valued function, the code is quite similar, but you'll find the performance is much better in multi-row operations, and close to identical for single-row operations.

-- ah, much better
ALTER FUNCTION dbo.GetIDByGUID_TVF
(
  @GuidKey uniqueidentifier
)
RETURNS TABLE
AS
  RETURN (SELECT id = $IDENTITY FROM dbo.tablename WHERE guid_column = @GuidKey);
GO

-- in the trigger:
UPDATE r SET oldkey = f.id
  FROM dbo.related AS r
  INNER JOIN inserted AS i
  ON r.guid_column = i.guid_column
  CROSS APPLY dbo.GetIDByGUID_TVF(i.guid_column) AS f;


Form saving error PHP MYSQL I have a form that saves 2 narratives and a select option.  If I place to many characters in the narrative box it will not save to the mysql table, but if I decrease the characters then it will save just fine.

I have set php.ini to allow longer post time var sizes so im not sure if I am missing anything else in the php.ini that anyone can think of.

Thank for any suggestions.

It sounds like your data is to large for the constraints placed on the table/columns that you are trying to populate.

Take a look at the Data Types of the columns you are adding to and the Limits of those Data Types, then compare that to your values that are producing the errors.


BULK INSERT syntax error While inserting bulk data in sqlserver with query

BULK INSERT Table_name
FROM 'FilePath'
WITH
( FIELDTERMINATOR ',',
ROWTERMINATOR '\n')
;

I'm getting error

  "Incorrect syntax near '('."

I don't see an error in the above statement - could someone please help?

Looking at the BULK INSERT documentation, it shows that the syntax should be:

BULK INSERT Table_name
FROM 'FilePath'
WITH
( FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n')
;

ie, with = signs between the option name and the value.


'Locking' will occur when I create more than 5-6 non-clustered indexes? When I create more than 5 to 6 non-clustered indexes on a single table in SQL Server it will cause a locking problem.

Is this right or wrong?  And if your answer is "right" and my  actual requirement is more than 5-6 non-clustered indexes then what shall I do?

It's hard to give you a complete answer without knowing what you mean by "locking problem".  All queries take locks of one type or another and this is normal and correct.  I'm going to assume you mean that queries are getting blocked?  Where you are running one query and another will have to wait for resources?

I can tell you the following:

While you create the indexes you might (depending on how you create it) see some blocking.
Every index you add creates overhead for write operations.  This means that your writes will take longer and could increase the amount of blocking that you see.

In and of themselves no, creating 5-6 indexes shouldn't be causing you a noticeable problem.  Now if you had 50-60 indexes we might expect one.  If you are seeing a problem you will have to give us quite a bit more detail (table structures, indexes, queries that are having a problem, and most importantly what the actual problem is) before we can give you a more specific answer.


Stop rollback of anonymous DO code block? I'm executing some code through this anonymous DO code feature. I am observing that whenever it fails, all changes made are discarded and the database is rolled back to a state as it was before it began. How can I stop this functionality?

You can commit inside the DO, as long as it is not already inside a transaction.

create table foo (z double precision);

do language PLPGSQL $$
declare i int;
begin
  for i in 1 .. 12 loop
    insert into foo values (sqrt(10-i));
    commit;
  end loop;
end;$$;

ERROR:  cannot take square root of a negative number
CONTEXT:  SQL statement "insert into foo values (sqrt(10-i))"
PL/pgSQL function inline_code_block line 1 at SQL statement

select count(*) from foo;

  10


How can I achieve clustering of rows without the exclusive lock and logging overhead of the `cluster` command? The CLUSTER command on a big table can take a long time, and blocks both reads and writes to the table while it runs.

I don't need the data in my table to be strictly sorted in index order, I just want rows that are commonly queried together to be more likely to be in the same database blocks than scattered evenly through the table (which is the distribution they naturally have due to the nature of the way date is inserted to the table).

It can make a big difference. In the example below, the only difference is that the insert has an additional order by mod(g,10) so that the test data is pre-clustered by host_id. Far fewer blocks need to be read when getting all the data for one particular host_id.

Is there some way of achieving this kind of clustering without the exclusive lock and logging overhead of the cluster command?

create schema stack;
set search_path=stack;
--
create table foo(host_id integer, bar text default repeat('a',400));
insert into foo(host_id) select mod(g,10) from generate_series(1,500000) g;
create index nu_foo on foo(host_id);
explain analyze select count(bar) from foo where host_id=1;
/*
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=30188.66..30188.67 rows=1 width=404) (actual time=1129.858..1129.859 rows=1 loops=1)
   ->  Bitmap Heap Scan on foo  (cost=919.27..30066.46 rows=48883 width=404) (actual time=253.149..1110.013 rows=50000 loops=1)
         Recheck Cond: (host_id = 1)
         Rows Removed by Index Recheck: 320257
         ->  Bitmap Index Scan on nu_foo  (cost=0.00..907.04 rows=48883 width=0) (actual time=251.863..251.863 rows=50000 loops=1)
               Index Cond: (host_id = 1)
 Total runtime: 1129.893 ms
*/
--
drop table foo;
--
create table foo(host_id integer, bar text default repeat('a',400));
insert into foo(host_id) select mod(g,10) from generate_series(1,500000) g order by mod(g,10);
create index nu_foo on foo(host_id);
explain analyze select count(bar) from foo where host_id=1;
/*
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=7550.20..7550.21 rows=1 width=32) (actual time=24.397..24.397 rows=1 loops=1)
   ->  Bitmap Heap Scan on foo  (cost=47.80..7543.95 rows=2500 width=32) (actual time=3.988..16.189 rows=50000 loops=1)
         Recheck Cond: (host_id = 1)
         ->  Bitmap Index Scan on nu_foo  (cost=0.00..47.17 rows=2500 width=0) (actual time=3.649..3.649 rows=50000 loops=1)
               Index Cond: (host_id = 1)
 Total runtime: 24.437 ms
*/
--
drop schema stack cascade;

You can do this without using the cluster command and having the table locked or generating WAL for the whole table. The cost is that you need to full-scan the table regularly.

The basic idea is:

turn off autovacuum for the table
check each block to determine the degree of clustering
delete and re-insert all the rows from blocks below a clustering threshold
manually vacuum to free those (complete) blocks
repeat steps 2-4 as regularly as necessary

test schema sample data initially 'part-clustered':

create schema stack;
set search_path=stack;
create type t_tid as (blkno bigint, rowno integer);
create table foo(host_id integer, bar text default repeat('a',400)) with (autovacuum_enabled=false);
insert into foo(host_id) select mod(g,10) from generate_series(1,500000) g order by mod(g,10);
insert into foo(host_id) select mod(g,10) from generate_series(1,500000) g;
create index nu_foo on foo(host_id);

initial clustering statistics:

select cn, count(*)
from ( select count(*) cn
       from (select distinct (ctid::text::t_tid).blkno, host_id from foo) z
       group by blkno ) z
group by cn
order by cn;
/*
 cn | count
----+-------
  1 | 27769  <---- half clustered
  2 |     8
  5 |     1
 10 | 27778  <---- half un-clustered
*/
select count(distinct (ctid::text::t_tid).blkno) from foo where host_id=1;
/*
 count
-------
 30558  <--------- lots of blocks to read for `host_id=1`
*/

initial analyze (2146.503 ms):

explain analyze select count(bar) from foo where host_id=1;
/*
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=15097.30..15097.31 rows=1 width=32) (actual time=2146.157..2146.158 rows=1 loops=1)
   ->  Bitmap Heap Scan on foo  (cost=95.17..15084.80 rows=5000 width=32) (actual time=21.586..2092.379 rows=100000 loops=1)
         Recheck Cond: (host_id = 1)
         Rows Removed by Index Recheck: 286610
         ->  Bitmap Index Scan on nu_foo  (cost=0.00..93.92 rows=5000 width=0) (actual time=19.232..19.232 rows=100000 loops=1)
               Index Cond: (host_id = 1)
 Total runtime: 2146.503 ms
*/

delete and re-insert the un-clustered rows:

with w as ( select blkno
            from (select distinct (ctid::text::t_tid).blkno, host_id from foo) z
            group by blkno
            having count(*)>2 )
   , d as ( delete from foo
            where (ctid::text::t_tid).blkno in (select blkno from w)
            returning * )
insert into foo(host_id,bar) select host_id,bar from d order by host_id;
--
vacuum foo;

new clustering statistics:

select cn, count(*)
from ( select count(*) cn
       from (select distinct (ctid::text::t_tid).blkno, host_id from foo) z
       group by blkno ) z
group by cn
order by cn;
/*
 cn | count
----+-------
  1 | 55541  <---- fully clustered
  2 |    16
*/
select count(distinct (ctid::text::t_tid).blkno) from foo where host_id=1;
/*
 count
-------
  5558  <--------- far fewer blocks to read for `host_id=1`
*/

new analyze (48.804 ms):

explain analyze select count(bar) from foo where host_id=1;
/*
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=16110.64..16110.65 rows=1 width=32) (actual time=48.760..48.761 rows=1 loops=1)
   ->  Bitmap Heap Scan on foo  (cost=131.18..16098.14 rows=5000 width=32) (actual time=8.402..32.439 rows=100000 loops=1)
         Recheck Cond: (host_id = 1)
         ->  Bitmap Index Scan on nu_foo  (cost=0.00..129.93 rows=5000 width=0) (actual time=7.636..7.636 rows=100000 loops=1)
               Index Cond: (host_id = 1)
 Total runtime: 48.804 ms
*/

clean up:

drop schema stack cascade;

The above is workable now, but is a bit quirky (needing to turn off auto-vacuum for the table) and requires regular full-scanning the table. I think something similar without the disadvantages could built into postgres. You'd need:

A space efficient index to cluster on (this is coming in 9.4 with GIN compression, or better still in 9.5 with the new BRIN index type)
A 'vacuum-like' process that would scan that index to detect which blocks need to be deleted/reinserted (this would ideally be able to reinsert the rows into fresh blocks so auto-vacuum can be left at default)


Oracle PL/SQL find nearest object There are two tables containing objects and their coordinates. One contains hotels, the other contains restaurants. How to build a table that for each hotel lists nearest restaurant name and the distance?

WITH restaurant AS (
           SELECT 'A' r_name, 0.0 x, 0.0 y FROM dual UNION ALL
           SELECT 'B' r_name, 0.0 x, 6.0 y FROM dual UNION ALL
           SELECT 'C' r_name, 9.5 x, 0.5 y FROM dual UNION ALL
           SELECT 'D' r_name, 8.2 x, 4.7 y FROM dual -- etc
           ),

     hotel AS (
           SELECT 'First_Hotel' h_name, 1.0 x, 6.0 y FROM dual UNION ALL
           SELECT 'Second_Hotel' h_name, -4.0 x, 3.0 y FROM dual -- etc
           )

     select h.h_name, r.r_name as nearest_restaurant, distance from restaurant r, hotel h  /* some query */

Should return

 hotel          nearest_restaurant    distance
 First_Hotel       B                    1     -- B is the nearest restaurant to First_Hotel. Distance is sqrt((1.0-0.0)^2+(6.0-6.0)^2)=1
 Second_Hotel      A                    5     --if both A and B on the same distance choose based on r_name

Edited: I also have geoloc for both tables with x and y coordinates. Can geoloc help to build the query?

This query worked for me. It uses GEOLOC from Oracle Spartial

    create table my_result_table as  (
    select n.nodeid, n.geoloc.sdo_point.y as node_y, n.geoloc.sdo_point.x as node_x,
        r.geoloc.sdo_point.y as sl_y, r.geoloc.sdo_point.x as sl_x,
        sdo_nn_distance(1)  as sld
       from  my_nodes n,  my_restaurants r
       where
       sdo_nn   ( r.GEOLOC, n.geoloc,  'Unit = MILE sdo_num_res = 1', 1) = 'TRUE'
        );

Note that first argument is restaurants, not nodes.


SELECT query returns legacy data My program does r/w operations on a MariaDB table. There are circumstances why it has to do it in concurrent processes. That's why it does it with multiple connections. After a write (UPDATE query) operation, the then triggered read operation receives legacy values.
Question is if there is any query or other feature which makes all connections refreshing their data. A reconnect of the reader is actually working, but honestly a bad workaround.

There is no cache activated. And even if, the description says that an UPDATE will clear the affected sections in the cache.
I'm pretty new to MariaDB, is there any part of the general concept that I miss here?

The general concept to consider with these issues is "transaction isolation level".

I believe the most plausible explanation with MariaDB would be that both writers and readers work in non-autocommit mode (either autocommit=0 in my.cnf or set explicitly where application opens new connection). In this case, you can give the reader sessions access to newly updated data by issuing their own local commit; command instead of full re-connect.