Finding rows where a column contains [ and ] characters I need to get results where the column contains a string that includes [ ]. When I use LIKE, it takes the [] as a wildcard sequence, and doesn't work as I need.

For example, I want to search through a column called Address and get any values where the string contains [Sydney]:

SELECT * FROM Location
WHERE Address LIKE '%[Sydney]%'

I can't use CONTAINS(), because this is not a full-text indexed column.

One option is to use ESCAPE:

DECLARE @x TABLE(s VARCHAR(32)); INSERT @x SELECT 'Hi [Sydney]!'

SELECT s FROM @x WHERE s LIKE '%\[Sydney\]%' ESCAPE '\';

Of course, you'll want to choose a character that is unlikely to appear in the data naturally, or this could mess up other searches.

Another option is to just escape the first character by including it in its own escape sequence, e.g. in this case the end ] is not considered a part of any wildcard sequence because the first one is eliminated:

DECLARE @x TABLE(s VARCHAR(32)); INSERT @x SELECT 'Hi [Sydney]!'

SELECT s FROM @x WHERE s LIKE '%[[]Sydney]%';

One more option is to not use patterns that support wildcards or wildcard sequences. For example, CHARINDEX:

DECLARE @x TABLE(s VARCHAR(32)); INSERT @x SELECT 'Hi [Sydney]!'

SELECT s FROM @x WHERE CHARINDEX('[Sydney]', s) > 0;


Allowing users to compile / run PLSQL and SQL As a DBA - assuming my users are knowledgeable with PLSQL and SQL - why would I not grant my users privileges to compile and run their own PLSQL code.
We have an MI database environment where the data has undergone ETL.
The tables contain a large numbers of rows but not necessarily large rows in size. The analysis - will be - I assume of calculus in nature.
The users are wanting to run their own code on the data for the purposes of ad-hoc analysis - within their own schemas.
They come from a SAS background which from my understanding has allowed them to write and run their own analysis of data. Albeit, SAS runs on its own dataset - extracted from Oracle or elsewhere.

In my opinion this is a dangerous set-up - but without answering my own question - what are the pitfalls?

I think as a DBA you will inevitably lose the fight to keep hands out of your database. Having said that I think we owe it to our customers to try and provide a product that they can use. There are dangers and pitfalls of even read-only access that any DBA should be aware of:

You admitted that you are working with large record counts in your tables. What is your game plan when a user inadvertently issues a Cartesian Join?
If you are talking about read/write access how are you going to hold those users accountable for their actions? If financial data is involved (SOX) then it is not enough to just prove you are auditing the modifications to sensitive data you have to show you have removed their ability entirely.
If this is a production environment have you reconfigured your DR/backup solution? If your backup solution involves taking the DB off-line then what is your solution if a user puts a procedure in DBMS_JOB to run during it?
Lastly, and probably most importantly, who is going to support their code? If your org has a dev team and they are not on board for support I can guarantee that as the DBA you are going to get a finger pointed at you as soon as something goes wrong. Make that expectation up front.

I think thats enough devils advocate but I guess in summary my answer would be:
 "Never in production and never in QA. If you want to run code you write it you support it and then you package it in a deployable format so I can push it out. If you want to act like a developer I'm going to treat you like one."


How to set correctly utf8 on oracle? I check my db,and seems to be UTF8 complaint

select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

PARAMETER        |VALUE
NLS_CHARACTERSET |AL32UTF8

But when i do a search on a VIEW

SQL> select titolo,anno,genere from generale where titolo like  '%Roma dr%' order by titolo;

TITOLO                                                      |ANNO   |GENERE
------------------------------------------------------------|-------|------------
Roma drogata: la polizia non pu?? intervenire               |1975   |Poliziesco

1 row selected.

Elapsed: 00:00:00.02

The two ?? tell me something is wrong.
Any solution to give a full utf8?Thanks

Based on your reply of

SELECT ASCIISTR(titolo) FROM generale WHERE titolo LIKE 'Roma drogata%;

TITOLO
----------------------------------------------------------------
Roma drogata: la polizia non pu\FFFD\FFFD intervenire

You get Roma drogata: la polizia non pu?? intervenire because it has been inserted wrong into your database, i.e. when the record was inserted the client had set NLS_LANG wrong.

Unicode character U+FFFD is this one: REPLACEMENT CHARACTER

Probably the SQL script was saved as UTF-8 but NLS_LANG was set to ISO-8859-1 or Windows-Cp1252 or similar.


Problem with SELECT CASE WHEN THEN, 0 result Need an advice in  SELECT CASE WHEN THEN. In main, removed all unimportant function, usual select i.fav_id FROM users; i see original table:

fav_id
1
2
6
7

but i need change some id's for result will be:

fav_id
1
3
6
8

i do:

SELECT i.fav_id =
        CASE
           WHEN 2 THEN 3
           WHEN 3 THEN 2
           WHEN 7 THEN 8
           WHEN 8 THEN 7
           ELSE i.fav_id
       END fav_id
FROM `favorites` AS i WHERE i.member = '123456'

i got:

fav_id
0
0
0
0

i'm new to select CASE selection but after searching alot manuals and examples, and did not fund how to solve my problem. thanx for your time !

used:
libmysql - mysqlnd 5.0.11-dev - 20120503
PHP: 5.6.28-1+deb.sury.org~xenial+1
phpMyAdmin  4.5.4.1deb2ubuntu2

found solution, sorry, my mistake !

SELECT i.fav_id,
        CASE
           WHEN i.fav_id = 2 THEN 3
           WHEN i.fav_id = 3 THEN 2
           WHEN i.fav_id = 7 THEN 8
           WHEN i.fav_id = 8 THEN 7
           ELSE i.fav_id
       END fav_id
FROM `favorites` AS i WHERE i.member = '123456'


Email notifications from OEM I am trying to generate email alerts from OEM to multiple email addresses. I have configured the SMTP server details from 'Setup' screen. And then I have added the email addresses under the 'Preferences' tab in OEM.
To check if the alerts are working fine, I have shutdown database and restarted 3/4 times. But only once the alerts notification came and that too only to one email address and not to all the email ids mentioned.

Please let me know why this is working in this way. Also, am I missing anything?? Do I need to add anything else to get the notification emails correctly for all the email ids??

Also, there is one screen for notification schedule. Do I have to define some schedule???

Yes, you have to fill in the notification schedule. This site defines to whom the mail will be sent on which time and day. If you shut down your database at a time, where no mail-address is set in the schedule, OEM will not send any mails.

You can fill in multiple mail addresses in the field for the schedule to have multiple persons receiving alert mails.

If you want the same addresses to be used every time, set the start and end so that 24 hours are covered and select all weekdays.

For testing the SMTP-Server, there is a link on the site, where you define one to send a test-mail.

The purpose of the notification schedule is e.g. for having different persons to be notified during working hours than at night.


how do you convert a non Oracle Managed files into an Oracle Managed file how do you convert a non Oracle Managed files into an Oracle Managed file

The following parameters enable the Oracle to use OMF(Oracle Managed File).
DB_CREATE_FILE_DEST- For Datafiles
DB_CREATE_ONLINE_LOG_DEST_n- For redo log files.
DB_RECOVERY_FILE_DEST- For controlfiles.

To convert non-OMF datafiles to OMF-

1) Set the db_create_file_dest parameter.

alter system set db_Create_file_dest='+DATA';

2) select file#, name from v$datafile;--to list all the datafiles and their file number.

3) alter database move datafile 1; --repeat this for all datafiles.

To convert non-OMF redo log files to OMF

1) set the DB_CREATE_ONLINE_LOG_DEST_n

2) Create new OMF redo log files.

 alter database add LOGFILE group 5 size 100M;

3) Drop the old redo log files.


Run query faster, what index to use I have a table with 85M rows :

CREATE TABLE emissions_totales (
"schema_source" varchar(150),
"snap" varchar(20),
"gid" varchar(255),
"polluant" varchar(50),
"emi" float8,
"numreg" varchar(3),
"unites" varchar(60),
"annee_ref" varchar(4),
"type_source_n1" varchar(255),
"type_source_n2" varchar(255),
"type_source_n3" varchar(255),
"napfue" varchar(15),
"naf_rev2" varchar(10),
"entite" varchar(255),
"id" int4)

but a query like this one:

SELECT
    numreg,
    annee_ref,
    type_source_n1,
    polluant,
    unites,
    sum(emi) as emi
FROM emissions_synthese.emissions_totales
WHERE schema_source='residentiel'
GROUP BY
    numreg,
    annee_ref,
    type_source_n1,
    polluant,
    unites;

takes more than 3 hours to perform. What is the best way to make indexes? Do I have to make unique index on schema_source, numreg, annee_ref, type_source_n1, polluant and unites or a multicolumn index?

I have modified the postgresql.conf file by reading various sites, do I have to change something?

shared_buffers = 956MB
work_mem = 5MB
maintenance_work_mem = 128MB
checkpoint_segments = 64
checkpoint_timeout = 5min
checkpoint_completion_target = 0.9
effective_cache_size = 2304MB

(postgresql 8.4 on Debian Squeeze, 3.0 Go RAM, 2 Intel Xeon E5345 CPU)

I think the main problem is in your table design (many-many redundant values which are prone to typos and other problems).  If you were referencing these values via foreign keys, the table rows would be much shorter - therefore the aggregation could be done on a much smaller dataset and the actual values be added via joins.  (The optimizer takes care of doing this well, you don't need additional magic.  The design change is your task, though :) - see also ypercube's comment above.)

Aside this, in your configuration the work_mem value seems to be very low.  If this is true or not depends on how many rows you get from this query - but as grouping and aggregating involves sorting and work_mem is the place where the data is being sorted, there is a chance that simply increasing this to a (much) bigger value would improve the performance a lot.

Furthermore, an index like

CREATE INDEX idx_emissions_totales_residentiel
ON emissions_totales (schema_source, numreg, annee_ref, type_source_n1, unites)
WHERE schema_source = 'residentiel';

will also help this particular query.  As edze suggested, going to PostgreSQL 9.2 and including 'emi' in the index (as the last column) could also help.


What does remote database have to do for DB link in Oracle In local database, it has to create DB LINK through CREATE DATABASE LINK.

What about remote database side, what does it have to do?
On fixed user link, local database need to know ID and password set.
Is the ID is a usual account with which someone can access to the remote database without DB LINK or the ID is only for DB LINK?

What about remote database side, what does it have to do?

There must have Oracle Net installed on both local and remote database servers.
And the user being used should have CREATE SESSION system privilege on the remote database.

  On fixed user link, local database need to know ID and password set. Is the ID is a usual account with which someone can access to the remote database without DB LINK or the ID is only for DB LINK?

You can have to options to connect to the remote database,  one is using current user and another is fixed user which can be defined explicitly after CONNECT TO clause. That user is a remote user which must be exist on the remote database server and must have CREATE SESSION privilege . If you omit this then currently logged in user is used to access tables on the remote server.

[oracle@localhost admin]$ sqlplus scott/scott

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 30 16:39:08 2016

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create database link dblink1 using 'account';

Database link created.

SQL> create database link dblink2 connect to jay identified by jay using 'account';

Database link created.

SQL> select * from emp@dblink1;
select * from emp@dblink1
                  *
ERROR at line 1:
ORA-28000: the account is locked
ORA-02063: preceding line from DBLINK1

SQL>

Look at the above example. I have connected to the local database as SCOTT user and create two dblinks to connect to the remote server called 'account' using 'account' TNSNAME.

First dblink dblink1 uses current user which is SCOTT in our case is used in order to select * from emp at the remote server 'account'. In the example the select statement has returned an error stating that the SCOTT user at the remote site is locked.

The second dblink dblink1 uses the fixed user called jay hence this dblink can be used to access tables owned by Jay schema as well as other tables according to the privileges the user has got.

For details please refer to the Oracle Documentation.
CREATE DATABASE LINK


Unable to show binary logs how to remove logs from mysql? Server version: 5.1.50-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show binary logs ;

PROBLEM

Data directory is full
I want to remove space
Purge is not making more space.

Any idea what to do to clear relay logs and Bin file .

When the folder with binary logs is full, mysqld will freeze everything that does a write to a MyISAM table (even temporary MyISAM tables). See my post "Site Offline" MySQL server failing to start and stop as to why. Anyway, mysqld will wait for free diskspace before continuing whatever operation it was doing. In this particular instance, you must go to the OS and delete the binary logs, but not all of them.

For example, if you defined

[mysqld]
datadir=/var/lib/mysql
log-bin=mysql-bin
log-error=error-log.log

go to the OS and run this

cd /var/lib/mysql
ls -l mysql-bin.*

You will see all the binary logs. Delete just the first 2 or 3 binary logs.

Log back into mysql and run SHOW BINARY LOGS;

It will eventually kick in when the free space is acknowledged by mysqld.

Then, you can run PURGE BINARY TO 'mysql-bin.xxxxxx';

You may also have to look into the error log or slow log. If they are big, truncate them like this:

cd /var/lib/mysql
echo -n > error-log.log

GIVE IT A TRY !!!


Best approach to delete large table with foreign key constraints How to purge a table with a billion records that has foreign key to another table? Should one partition the table and the child table and use partition swap?

Try this:

Insert 3 month data in new table with similar structure:

declare @CurDate datetime
select *
into NewTable
from ExistingTable
where datecol >= @CurDate and datecol <= dateadd(month,3,@CurDate)

Rename old table to anything unique or drop table as per requirement
Rename new table to existintg table
Insert into NewChildTable with similar structure which match New parent table of Step 3
Rename or drop existing child table
Rename new child table to existing child table


Conversion failed when converting from a character string to uniqueidentifier I am creating a Dynamic CRM 2011 Report in SSRS.
I use several joins, And use GUID in joins.
For ex :

Quote.OpportunityId=Opprtunity.OpportunityId

The problem comes from parameters.
I have a parameter which its label is a name and the value is GUID of a record.
I retrieve them from a Dataset.
The query of the this Dataset(named siteDataset) is this :

select site.SiteId, site.Name from Site;

the siteId goes to the value of the parameter and site.name goes to the label of the parameter, the Parameter is named siteId itself.

But when I try to do this in another DataSet :

siteGUIDInAnotherEntity='@siteId'

Give me the subject Error.
I even tried this :

CONVERT(VARCHAR(50), site.SiteId)

in siteId DataSet; But no success.

Any help will appreciated.

This may be a typo in your question, but just to be safe I'll post this as a possible answer - you shouldn't have those single quotes around @siteId, it should be like this:

siteGUIDInAnotherEntity=@siteId

The text string '@siteId' can't be converted into a uniqueidentifier for that join, and will result in the error you mentioned:

SELECT CAST('@siteId' AS uniqueidentifier);

  Msg 8169, Level 16, State 2, Line 1
  Conversion failed when converting from a character string to uniqueidentifier.


Group by month in result columns? I'm using this DBMS: Caché

I've never used a pivot table in SQL and I'm not even good at it in Excel, but I assume that's what I need.  Given the following sample data:

ID    Name    Result    Date
01    Steve   Win       2015-01-06 00:00:00
02    James   Win       2015-05-12 00:00:00
03    Bob     Lose      2015-06-21 00:00:00
04    Bob     Win       2015-05-23 00:00:00
05    James   Win       2015-04-04 00:00:00
06    James   Lose      2015-01-06 00:00:00
...

How can I get results like this? (numbers don't match sample data)

Wins By Month

        Jan    Feb    Mar
Steve   4      0      1
James   1      3      1
Bob     3      1      1
Steve   2      3      2

A PIVOT that will work in most DBMS is:

select name
     , count(case when Result = 'Win' and Month(dt) = 1 then 1 end) as Jan
     , count(case when Result = 'Win' and Month(dt) = 2 then 1 end) as Feb
     , count(case when Result = 'Win' and Month(dt) = 3 then 1 end) as Mar
from t
group by name

I used dt as name for your date column since date is a reserved word. As others have mentioned, there may be better solutions for a specific vendor so you should always tag your question with that, unless you are looking for a general solution.


Is MySQL Replication Affected by a High-Latency Interconnect? We've got a vanilla master and slave MySQL setup that reside in different data centers, and another slave in the same datacenter as the master.

The bandwidth between the datacenter is pretty high (in network benchmarks we've done we can reach 15MB/second), but latency exists, it is around 28ms. It's not high by any means, but it is much higher than the sub-second latency in the same datacenter.

Occasionally, we experience serious lags (2000 seconds and more) with the remove slave, while the local slave stays up to date. When looking at the lagging remote slave, the SQL thread usually spends the time waiting for the IO thread to update the relay log. The master shows "waiting for net" or something of the sort at the same time.

So it means it's network, but we still have free bandwidth at the time this happens.

My question is: can the latency between the datacenters affect the performance of replication?
Does the slave io thread just stream the events until the master stops sending them, or is it pooling the master somehow between events?

The direct answer to your question is Yes, but it depends on the version of MySQL you are running. Before MySQL 5.5, replication would operate as follows:

Master Executes SQL
Master Records SQL Event in its Binary Logs
Slave Reads SQL Event from Master Binary Logs
Slave Stores SQL Event in its Relay Logs via I/O Thread
Slave Reads Next SQL Event From Relay Log via SQL Thread
Slave Executes SQL
Slave Acknowledges Master of the Complete Execution of the SQL Event

As of MySQL 5.5, using Semisynchronous Replication, now replication would operate as follows:

Master Executes SQL
Master Records SQL Event in its Binary Logs
Slave Reads SQL Event from Master Binary Logs
Slave Acknowledges Master of the Receipt of the SQL Event
Slave Stores SQL Event in its Relay Logs via I/O Thread
Slave Reads Next SQL Event From Relay Log via SQL Thread
Slave Executes SQL
Slave Acknowledges Master of the Complete Execution of the SQL Event

This new paradigm will permit a Slave to be closer sync'd to its Master.

Notwithstanding, latency within the network could hamper MySQL Semisync Replication to the point where it reverts back to the old-style asynchronous replication. Why ? If a timeout occurs without any slave having acknowledged the transaction, the master reverts to asynchronous replication. When at least one semisynchronous slave catches up, the master returns to semisynchronous replication.

UPDATE 2011-08-08 14:22 EDT

The configuration of MySQL 5.5 Semisynchronous Replication is straightforward

Step 1) Add these four(4) lines to /etc/my.cnf

[mysqld]
plugin-dir=/usr/lib64/mysql/plugin
#rpl_semi_sync_master_enabled
#rpl_semi_sync_master_timeout=5000
#rpl_semi_sync_slave_enabled

Step 2) Restart MySQL

service mysql restart

Step 3) Run these commands in the MySQL client

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave  SONAME 'semisync_slave.so';

Step 4) Uncomment the three rpm_semi_sync options after the plugin-dir option

[mysqld]
plugin-dir=/usr/lib64/mysql/plugin
rpl_semi_sync_master_enabled
rpl_semi_sync_master_timeout=5000
rpl_semi_sync_slave_enabled

Step 5) Restart MySQL

service mysql restart

All Done !!! Now just setup MySQL Replication as usual.


postgres_fdw performance is slow The following query on a foreign takes about 5 seconds to execute on 3.2 million rows:

SELECT x."IncidentTypeCode", COUNT(x."IncidentTypeCode")
FROM "IntterraNearRealTimeUnitReflexes300sForeign" x
WHERE x."IncidentDateTime" >= '05/01/2016'
GROUP BY x."IncidentTypeCode"
ORDER BY 1;

When I execute the same query on normal table, it returns in .6 seconds. The execution plans are quite different:

Normal Table

Sort  (cost=226861.20..226861.21 rows=4 width=4) (actual time=646.447..646.448 rows=7 loops=1)
  Sort Key: "IncidentTypeCode"
  Sort Method: quicksort  Memory: 25kB
  -> HashAggregate (cost=226861.12..226861.16 rows=4 width=4) (actual  time=646.433..646.434 rows=7 loops=1)
     Group Key: "IncidentTypeCode"
     -> Bitmap Heap Scan on "IntterraNearRealTimeUnitReflexes300s" x  (cost=10597.63..223318.41 rows=708542 width=4) (actual time=74.593..342.110 rows=709376 loops=1)
        Recheck Cond: ("IncidentDateTime" >= '2016-05-01 00:00:00'::timestamp without time zone)
        Rows Removed by Index Recheck: 12259
        Heap Blocks: exact=27052 lossy=26888
        -> Bitmap Index Scan on idx_incident_date_time_300  (cost=0.00..10420.49 rows=708542 width=0) (actual time=69.722..69.722 rows=709376 loops=1)
           Index Cond: ("IncidentDateTime" >= '2016-05-01 00:00:00'::timestamp without time zone)

Planning time: 0.165 ms
Execution time: 646.512 ms

Foreign Table

Sort  (cost=241132.04..241132.05 rows=4 width=4) (actual time=4782.110..4782.112 rows=7 loops=1)
  Sort Key: "IncidentTypeCode"
  Sort Method: quicksort  Memory: 25kB
  -> HashAggregate  (cost=241131.96..241132.00 rows=4 width=4) (actual time=4782.097..4782.100 rows=7 loops=1)
     Group Key: "IncidentTypeCode"
     -> Foreign Scan on "IntterraNearRealTimeUnitReflexes300sForeign" x  (cost=10697.63..237589.25 rows=708542 width=4) (actual time=1.916..4476.946 rows=709376 loops=1)

Planning time: 1.413 ms
Execution time: 4782.660 ms

I think I'm paying a high price for the GROUP BY clause, which isn't passed to the foreign server when I EXPLAIN VERBOSE:

SELECT
    "IncidentTypeCode"
FROM
    PUBLIC ."IntterraNearRealTimeUnitReflexes300s"
WHERE
    (
        (
            "IncidentDateTime" >= '2016-05-01 00:00:00' :: TIMESTAMP WITHOUT TIME ZONE
        )
    )

This returns 700k rows. Is there a way around this?

I spent a lot of time reading this documentation page yesterday, and thought I had found my answer with setting use_remote_estimate to true, but it had no effect.

I do have access to the foreign server to create objects if necessary. The timestamp value in the WHERE clause can be anything; it does not come from a list of predefined values.

If you use use_remote_estimate be sure to run ANALYZE the foreign table  (I see estimations pretty close with the returned, you'd probably did it). Also, the pushdown improvements are not available in <9.5 version. I also assume that you have the same table structure on the remote server either (including indexes). If a bitmap is needed due to the low cardinality, it won't use the index due to the limitations on the pushdown mechanism. You may want to reduce the amount of returned rows to force a BTREE index scan (timestamp ranges). Unfortunately, there is no clean way to avoid the SeqScan on the remote server if the filter returns +10% of the rows of the table (may vary this percentage if the planner considers that scanning the whole table is cheaper than seek reads). If you are using SSD, you will probably find useful to tweak random_page_cost).

You can use CTE to isolate the GROUP BY behavior:

WITH atable AS (
    SELECT "IncidentTypeCode"
    FROM PUBLIC ."IntterraNearRealTimeUnitReflexes300s"
    WHERE
       ("IncidentDateTime"
              BETWEEN '2016-05-01 00:00:00'::TIMESTAMP WITHOUT TIME ZONE
                  AND '2016-05-02 00:00:00'::TIMESTAMP WITHOUT TIME ZONE)
)
SELECT atable."IncidentTypeCode", COUNT(atable.IncidentTypeCode)
FROM atable
GROUP BY atable."IncidentTypeCode"
ORDER BY atable."IncidentTypeCode";


Facing error while trying to run dynamic pl/sql Hope you're doing well
I'm new to PL/SQL and I'm trying to write a dynamic PL/SQL.
What I've written so far is :

declare

   sql_stmt     varchar2(100);
   v_student_id number := 1;
   v_new_stname varchar2(50);

begin

  sql_stmt := 'update student
               set student_name = ''pantea'' where student_id :1 ' ||
              'returning student_name into :2';
  execute immediate sql_stmt
  using v_student_id
  returning into v_new_stname;

     dbms_output.put_line('New student name is :' || v_new_stname);

end;

when I want to run the code I see this error :

ORA-06502:Numeric or value error:Character string buffer too small.

I'm 80 percent sure that the problem might be in this part of the code

set student_name = ''pantea''

but I don't know how to solve it and I could not also find it on oracle books
I know how to handle this in T/SQL but have no idea how to handle this in PL/SQL.

You forgot the comparison operator for the WHERE clause

If you really want to use dynamic SQL (which is not needed for a simple UPDATE statement like that), then you should also pass all values as parameters, not just the student ID.

declare
   sql_stmt     varchar2(100);
   v_student_id number := 1;
   v_name varchar2(20) := 'pantea';
   v_new_stname varchar2(50);
begin
  sql_stmt := 'update student set student_name = :1 '||
              'where student_id = :2 '||
              'returning student_name into :3';

  execute immediate sql_stmt
    using v_name, v_student_id
    returning into v_new_stname;

  dbms_output.put_line('New student name is: ' || v_new_stname);
end;
/

The "string buffer too small" seems to happen when you are continuing a string literal (constant) over multiple lines. So it's better to open and close the string constants on each line and concatenate them using || rather than embedding a new line in the string as you did.


How does MS SQL Server authenticate MSDTC connections when using SSPI? I'm trying to get SSPI authentication to play well with two-phase transactions via MSDTC, using PostgreSQL with psqlODBC. I can't figure out how it can work securely - so I'm hoping someone can explain how it works with MS SQL Server.

I'm coming from a PostgreSQL background, so please forgive any major misunderstandings of MS SQL.

The TL;DR is: To complete prepared transaction recovery after an app exits or the server crashes, MSDTC.exe running as NETWORKSERVICE has to be able to connect to the database and commit prepared transactions on behalf of the original user. When authentication was done over SSPI originally I don't understand how that's possible.

Background: SSPI, DTC, ODBC and XA

A user establishes one or more psqlODBC connections to PostgreSQL, and authenticates to PostgreSQL with SSPI. Windows confirms to PostgreSQL that the requested PostgreSQL username is the same as the current Windows username, and access is granted.

The user binds the connections into a DTC session with the MSDTC transaction manager and begins a distributed transaction (which begins a tx on the connections being managed by MSDTC). When they're done with their work on the connections they request a distributed commit on the DTC transaction. This does a two-phase commit - in PostgreSQL, calling PREPARE TRANSACTION on both as phase 1, then when phase 1 confirms success calling COMMIT PREPARED on each as phase 2.

Standard-ish stuff so far, just Microsoft's version of XA.

XA recovery

Part of the point of XA and MSDTC is that if the client crashes after phase 1 commit confirms, the transaction is guaranteed to get committed.

For that to happen something must keep track of outstanding prepared transactions and commit them. That's the job of the MSDTC.exe, the microsoft distributed transaction co-ordinator daemon.

At least with psqlODBC, when an abandoned prepared transaction is recovered, MSDTC loads the XA DLL for psqlODBC and calls xa_recover, which makes a connection to PostgreSQL using the credentials of the user who began the DTC transaction. It then calls COMMIT PREPARED and closes the connection.

MSDTC.exe runs as NETWORKSERVICE. That's the where the problem starts, because those stored credentials are not valid for NETWORKSERVICE if the original connection wasn't also by the user NETWORKSERVICE. SSPI will reject the connection because of a username mismatch.

How does MS SQL handle this?

MS SQL Server is heavily used with SSPI, and MSDTC is designed in part around the needs of MS SQL Server. So presumably, MS SQL Server has a secure way to complete distributed transaction recovery for a connection that was made using SSPI by a user other than NETWORKSERVICE even though msdtc.exe runs as NETWORKSERVICE.

I don't understand how it could, though.

The obvious options are all insecure, like:

Configure and store credentials in the registry, accessible by NETWORKSERVICE, so msdtc.exe can commit/rollback any user's prepared transactions;
Trust NETWORKSERVICE to connect to MS-SQL without credentials for the purpose of prepared transaction management;

and in the end, I don't see how MS SQL can prevent other services running as NETWORKSERVICE from messing with its prepared transactions.

Is this just one of those things where people accept somewhat weaker security? You're expected to reasonably trust everything running as NETWORKSERVICE and MS SQL just allows MSDTC to connect without additional checks?

Or is there something I'm missing?

The only way I can think of to do this fairly securely (allowing for the inherent limitations of running as NETWORKSERVICE) is to generate a one-time cookie with each prepared transaction, which cannot be discovered by listing the transactions. Then allow anyone in NETWORKSERVICE to make a database connection that can do nothing except commit/rollback prepared transactions, and only if it knows the transaction's cookie. That seems awfully fiddly though.

Does MS-SQL just assume that NETWORKSERVICE is trusted?

There are two Distributed Transaction Standards Supported by the DTC. XA Transactions and OLE Transactions and recovery is implemented differently.

XA Transaction Recovery Process use xa_recover_entry of GetXaSwitch to get a list of xid from the transaction resource manager (SQL Server is a resource manager) that it then uses to rollback or commit the prepared transactions. I assume that running as NETWORKSERVICE is enough to do that based on what is documented in Disabling TIP, LU and XA Transactions

  In Windows XP , Windows Server 2003, and subsequent versions you can
  disable XA transactions to help prevent the security risk that arises
  when a user-specified DLL, used by the DTC to communicate with the XA
  partner's transaction manager, is loaded directly into the DTC
  process. This situation exposes a resource manager's databases to
  serious data corruption and can cause Denial of Service (DOS) attacks.
  The disabling of XA transactions helps to protect the DTC from this
  DLL attack.

The recovery process for OLE is implemented a bit differently. The resource manager has a list of prepared transactions and is responsible for reenlisting them in the DTC. Have a look at the Performing Recovery section of Implementing DTC Interfaces.

Not sure I actually provided a definitive answer for you here but I thought this was too much for a comment.


SQL server 2000 transactions with the same time I have a BIG problem here.
I'm creating a program that can be looping if the connection to the server is broken.
But when the program is running for a few months, I found a bug on SQL server 2000.

I found some data that had more than one transaction with the same date and time.

Is it possible that sql2k server has a transaction with the same date and time in one table?

PS : I use getdate to fill that field.

Hope there's explanation and solution for my problem.

It won't be a bug: it's a flaw in your logic.

If you're looping, then it's possible that you are opening transactions on top of another transactions. That is, @@TRANCOUNT keeps incrementing. You may be incorrectly looping on say, a client timeout, that doesn't actually disconnect.

In your loop, in client code, you should test for an open transaction and rollback. Or use SET XACT_ABORT ON to force full rollbacks. See https://stackoverflow.com/questions/917773/do-i-really-need-to-use-set-xact-abort-on/919279#919279 for more

It's hard to be more precise at this stage.


How to design database for tree view with an infinite depth I am trying to combine C# and SQL2005 for an application which need to have tree view control.

One of user requests is to that tree view can user friendly grow into infinite dept of child nodes. That data should be stored into database and at user request i need to draw again tree view whit parent and child nodes as it user was edited and arranged before.

How I should design my table(s) that can easily grow at two axes of tree view.

What we have always done is do it as a self join, with a parent_id which points to the parent.  This has the advantage of being simple, easy to work with, etc.  Of course it also requires recursion and WITH RECURSIVE CTE's.  It is easy to work with and as long as you are not working at huge depths and lots of records it performs well (note you can generate to a set depth and then fetch more records when a user expands if you want).


Passively load a monitoring shared object into an Oracle process I'm trying to load my own code, which should add some security features to my environment, into each oracle BEQ dedicated process.
The reason I'm posting this on this platform, is that I hope there is some DBA hack to trick Oracle to load my shared object when a new instance of oracle starts to run.
I have found out about external_libraries that in first glance seemed to fit, but after investigating how it works, I'm have found out that the library is being loaded into another external process and the loading will only happen on triggering a specific external function.
I know DB2 has an option to load external plugins to add functionality to the database (e.g 1, 2 and 3) - this would fulfill my needs. - Again, I'm not intrested in the actual logic behind the plugin, all I want is the oracle to pull/load me passively into it's memory space when it starts.
Can you think of anything?
Thanks

Using LD_PRELOAD to implement a hidden trojan in an oracle database
You can also follow the links there. Like this one: https://yurichev.com/writings/oracle.pdf


Database Design - hierarchical structure We are trying to redesign a database that currently holds thousands of parameters for different datatypes and currently has a flat data structure.

We generate XML files from the database, which our software application uses to load and display the required data. As the complexity of the data used by the application increases, predominantly becoming more hierarchical and using more arrays, the current database structure is obviously not suitable for purpose.

We have discussed the possibility of the using a parent-child relationship for parameters that have an array of values, using an index to order the data. However, this does not solve the problem of storing data structures and arrays of data structures within a data structure within a database.

A typical data structure used within the application looks like the following:

class A {
    int MyInt;
    List<B> MyBs;
    C MyC; }

class B {
    string MyString;
    double[] MyNumbers; }

class C {
    string MyString;
    double MyDouble;
    B MyB;
    List<B> MyBs; }

Can anyone help advise us on how we might solve this issue?

This could be a possible model, depending on the length of MyString, you could replace surrogated keys on B and C by this column.

CREATE TABLE B
(
    [ID] int IDENTITY(1,1) NOT NULL,
    [MyString] nvarchar(500),

    CONSTRAINT [PK_B] PRIMARY KEY ([ID])
);

CREATE TABLE C
(
    [ID] int IDENTITY(1,1) NOT NULL,
    [MyString] nvarchar(500),
    [MyDouble] decimal(18,2) DEFAULT(0),
    [B_ID] int NOT NULL,

    CONSTRAINT [PK_C] PRIMARY KEY ([ID]),
    CONSTRAINT [FK_C_B_ID] FOREIGN KEY ([B_ID]) REFERENCES B([ID])
);

CREATE TABLE A
(
    [ID] int NOT NULL,
    [C_ID] int NOT NULL,

    CONSTRAINT [PK_A] PRIMARY KEY ([ID]),
    CONSTRAINT [FK_A_C_ID] FOREIGN KEY ([C_ID]) REFERENCES C ([ID])
);

LIST/ARRAYS

CREATE TABLE A_B_LIST
(
    [A_ID] int NOT NULL,
    [Index] int NOT NULL, /* Index of array */
    [B_ID] int NOT NULL,

    CONSTRAINT [PK_A_B_LIST] PRIMARY KEY ([A_ID], [Index]),
    CONSTRAINT [FK_A_B_LIST_A_ID] FOREIGN KEY ([A_ID]) REFERENCES A ([ID]),
    CONSTRAINT [FK_A_B_LIST_B_ID] FOREIGN KEY ([B_ID]) REFERENCES B ([ID]),
);

CREATE TABLE B_NUM
(
    [B_ID] int NOT NULL,
    [Index] int NOT NULL,
    [MyNumber] decimal(18,2),

    CONSTRAINT [PK_B_NUM] PRIMARY KEY ([B_ID], [Index]),
    CONSTRAINT [FK_B_NUM_B_ID] FOREIGN KEY ([B_ID]) REFERENCES B([ID])
);

CREATE TABLE C_B_LIST
(
    [C_ID] int NOT NULL,
    [Index] int NOT NULL,
    [B_ID] int NOT NULL,

    CONSTRAINT [PK_C_B_LIST] PRIMARY KEY ([C_ID], [Index]),
    CONSTRAINT [FK_C_B_LIST_C_ID] FOREIGN KEY ([C_ID]) REFERENCES C ([ID]),
    CONSTRAINT [FK_C_B_LIST_B_ID] FOREIGN KEY ([B_ID]) REFERENCES B ([ID])
);

db<>fiddle here


MySQL import LOCK doesn't prevent PRIMARY key issues I am using Django and only want to backup mysql data (not it's structure; Django will do that, and it will otherwise mess up Django-South's migration checks).

Some info;

my tables are almost all intertwined with each other by Foreign Key bindings.
I'm currently having over 100 tables that hold all my data.

What I'm trying to achieve is some mechanism with mysqldump to dump my data, like so;

imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysqldump -u root -p my_database --no-create-info > my_database_data.sql

..and restore it like so;

imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysql -u root -p my_database < my_database_data.sql

..but then with the ability to store the following on the dump (and thus restore it that way on import);

When doing mysqldump

Store the PRIMARY key counters

When importing my_database_data.sql

LOCK the tables fully (even ignoring PRIMARY key counter checks)
INSERT all data (incl. primary keys)
Set the PRIMARY key counter back to what was saved
UNLOCK the tables again

Other issue

My first thought was to just ignore the PRIMARY keys somehow and just look-increment them again on insertion. The problem is that I can't do this due to their bindings with Foreign Keys, that would then thus break.

Whenever you dump a mysql table that has an AUTO_INCREMENT column, the next value is always attached to the definition of the table. You should see something like:
) ENGINE=InnoDB AUTO_INCREMENT=<some-number> ...

You may want to consider one of three(3) things
SUGGESTION #1 : mysqldump the database with table structure
imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysqldump -u root -p my_database > my_database_data.sql

That way, the auto_increment is attached to the table definition
SUGGESTION #2 : mysqldump the data and table structure separately
imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysqldump -u root -p my_database --no-create-info > my_database_data.sql
imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysqldump -u root -p my_database --no-data > my_database_schema.sql

You would load my_database_schema.sql first, then my_database_data.sql
SUGGESTION #3 : Read the next auto_increment of every table into a script
You can use a query like this to make the SQL script:
SELECT CONCAT('TRUNCATE TABLE ',db,'.',tb,'; ALTER TABLE ',db,'.',tb,' AUTO_INCREMENT=',autoinc,';')
FROM (SELECT table_schema db,table_name tb ,auto_increment autoinc
FROM information_schema.tables WHERE table_schema='my_database') A;

In the OS, you can do it like this:
SQLSTMT="SELECT CONCAT('TRUNCATE TABLE ',db,'.',tb,'; ALTER TABLE ',db,'.',tb,'AUTO_INCREMENT=',autoinc,';')"
SQLSTMT="${SQLSTMT} FROM (SELECT table_schema db,table_name tb ,auto_increment autoinc"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE table_schema='my_database') A;"
mysql -uroot -p -Ane"${SQLSTMT}" > autoinc.sql

Simply run autoinc.sql before my_database_data.sql
Give it a Try !!!


Database Tuning Advisor user permissions I was asked to create an account to use Database Tuning Advisor.
I red on technet but I don't understand which permission do I have to give.

I tried to login with this new user, but trying to list tables, I always get warning about there are no user tables available.
If I give db_owner to the user the tables are listed.

Thanks for help.

Bye

From BOL : Permissions Required to Run Database Engine Tuning Advisor

  After you install SQL Server, a user who is a member of the sysadmin fixed server role must launch Database Engine Tuning Advisor before anyone else. When the sysadmin user launches Database Engine Tuning Advisor that action initializes the application for use by others. After Database Engine Tuning Advisor is initialized, any user who is a member of the db_owner fixed database role can use it to tune tables on databases they own. For more information about initializing Database Engine Tuning Advisor.


Best design for adding a folder/file relationship I was given a database that has Projects and Media_files.  They were in a simple 1 to many relationship.

I now need to add Folders to Projects.  Media_files can be placed in Folders, and Folders can have Folders within them.

I am at a loss as to the best way to, first, have Folders belong to other Folders, and second, the best way to maintain the current relationship between Projects and Media_files, or if its best to break this relationship.

Projects is a simple table:

id,
title,
description,
owner_id.

Folders are an entirely new table, but my basic idea for them was:

id,
project_id,
parent_folder_id,
title,
description

I would to it either like this

       Projets
-----------------------
     project_id
       [....]

       Folder
-----------------------
     folder_id
     parent_id (-1 or the matching parent folder)
     project_id
      [....]

      Media
-----------------------
     media_id
     folder_id
     [....]

This way, you would need to break the relationship for it to work. IF you want to maintain the relationship, I would screw normalisation over, and add a project_id to the Media table as well with proper constraints/trigger so that moving media from one folder to another work without writing the explicit query.

or the other way would be:

       Projets
-----------------------
     project_id
       [....]

       Folder
-----------------------
     folder_id
     parent_id (-1 or the matching parent folder)
      [....]

      Media
-----------------------
     media_id
     folder_id
     project_id
      [....]

You maintain the original relationship and are good to go.


Push replicas from master to slave in postgresql I have a network topology where the master serves as a read/write database in one network, and has several slaves in different networks that are read-only for security reasons. Due to firewall restrictions, the slaves cannot establish a connection to the network the master resides in.

Is there a mechanism that allows the master to replicate itself to the slaves by only pushing out to the slaves? After reading a bit about replication in psql, it seems that the slaves need network access to the master.

No,there is no mechanism where a master will find out slaves waiting for replication.Slave to master connection is a necessary requirement as slave needs to authenticate to master.
If you are sure the connection is not getting established due to a restricting firewall then you need to configure your firewall to allow connection.Otherwise,you need to allow slaves' ip in pg_hba.conf on master server.


What to do when MySQL is not generating any logs at all on Debian? The problem I'm having is that MySQL (5.5.46-0+deb7u1) is not generating log files. This on Debian Wheezy.

The my.cfg file has been updated according to the required config for logging ALL queries (as per http://www.microhowto.info/howto/log_all_queries_to_a_mysql_server.html). It's not writing to the specified location, nor to the default location.

Mysql errors are also not being logged, as far as I can tell.

EDIT: my.cnf is here: http://pastie.org/private/q9vwgihslpenc94mxmyyiw

EDIT: Here's the output of "show variables like '%warn%';":

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings  | 1     |
| sql_warnings  | OFF   |
| warning_count | 0     |
+---------------+-------+

Thanks to commenters for their insights. The solutions was:

Add "log_errors = /var/log/mysql/mysql.err" to my.cnf
See permissions error in error log
chmod log files to "mysql:root"


Replicate RDS-MySQL to a non-amazon host? I have a fairly large MySQL (5.1) database running in Amazon as an RDS instance.  I'm working on migrating it out of Amazon.  To do that smoothly I'd like to set up the new hardware as a read-only replica of the RDS instance.

I know RDS supports replication within RDS.  Is there any way to configure it to replicate to a host which is not in RDS?

Looks like Amazon has chosen to forbid this for now:

https://forums.aws.amazon.com/thread.jspa?threadID=46377&tstart=0


transaction log after restore Can you let me know why a transaction log would be a lot smaller in size after restoring from a full backup.  Currently we have a massive log file on the live server, but after a restore to a test / development server the log file is considerably smaller.  Im guessing this is because the log was truncated as part of the backup however why would the original log file still be so huge.

A full database backup contains almost no transactions - just the transactions that happened while the FULL transaction log was being created.
The LDF file created when a database is restored using a full database backup is almost empty, regardless its size. It should be inflated (with no data but active portion of transaction log) to its backed up size when restoring.

If you want to restore the transaction log file too, the best way to do that is to restore the database using its LDF and LDF files (attach them), instead of using a full database backup.

There are many reasons why the original file is huge:

The database is in FULL recovery model and you don't create transaction log backups at all
The database is in FULL recovery model and you don't create transaction log backups often enough
there are large-scale index operations

For any of the possible causes, creating transaction log backups regularly should keep the size of the LDF file.


When using a master plus multiple slaves, how do I change over from one server to a brand new one with minimal downtime? In my particular case, I'm using Heroku Postgres (repackaged Amazon EC2 instances running PostgreSQL), but platform-agnostic answers are also good!

Heroku's instructions
describe the process for transfering over a single database (i.e. without slaves):

You have an existing server, A.
Create server B as a follower of A.

$ heroku addons:add heroku-postgresql:ronin --follow HEROKU_POSTGRESQL_AUBURN
... Attached as HEROKU_POSTGRESQL_BLUE

Wait for B to be up-to-date with A.
Disable the application so no new writes are made to A.
Wait for B to fully catch up.
Sever the connection between B and A, so B is an independent db.

$ heroku pg:unfollow HEROKU_POSTGRESQL_BLUE

Reconfigure the app to use B as your database.

$ heroku pg:promote HEROKU_POSTGRESQL_BLUE

Re-enable the application.
Destroy A.

$ heroku addons:remove HEROKU_POSTGRESQL_AUBURN

This all works fine if you have a single database, but what happens when you have slaves?  Heroku's docs only say that you'll have to re-create the slaves, without going into further depth.  (n.b. Once you've done the process above, you can't immediately create the new slaves.  The server needs a few hours before it can be forked/followed.)

Now, if your app can handle ditching the slaves entirely, then you can do the following:

disable the slaves
change over the master server
create new slaves
enable the new slaves

But if reducing your server farm to just a single server is going to cause the app to crash and burn, you need a way to keep your slaves up and running throughout this whole process!

My first thought was to create a server C, following B, even while B is still following A.  When switching over, B becomes the new master and C the new slave.  But, alas, Heroku (and presumably also PostgreSQL) doesn't support followers-following-followers.

QUESTION: How do you change over from one whole master/slave set to another set?  How do you change over your master without having to run without slaves for an extended period of time?

And just in case it's note clear: I'm not asking about failing over to an existing slave.  I want to upgrade all of the servers to a different-sized EC2 instance.  And I want to maintain my master/slave setup throughout the process.

I don't know how well this works on Heroku but I think it should work if one is using streaming replication internally.  I would recommend repmgr which is an open source replication cluster management tool from 2ndQuadrant.  This provides a general monitoring and management framework for replicated database clusters.  In 9.3, repmgr will be less necessary but still may be helpful for integration into heartbeat systems.  It provides a command line tool for failovers, preparing slaves, etc.

If you have to do something like this manually and repmgr wont work for you, basically, what you need to do is this:

turn off the master (shoot the other node in the head)
promote the slave to a master
re-base your other slaves and point them at the new master
turn your old master into a new slave when you can bring it up.

How you do this will depend to some extent on the replication approach.


How can I get the percentage of the table the groups represent? Given a query like this,

CREATE TABLE foo(grp)
AS VALUES ('a'),('a'),('b'),('b'),('b'),('c'),('c'),('c'),('c'),('e');

I can use GROUP BY to aggregate into a,b,c,e, but how can I return the different groups, and the percentage of the total table they represent?

a | 20%
b | 30%
c | 40%
e | 10%

First percentages are always in a range of [0-1) when stored on a computer. Rendering them in a range of a 0-100 is usually a function of the display (app). From wikipedia,

  For example, 45% (read as "forty-five percent") is equal to
  45 / 100, 45:100, or 0.45.

If you have for example a simple table.

CREATE TABLE foo(grp)
AS VALUES ('a'),('a'),('b'),('b'),('b'),('c'),('c'),('c'),('c'),('e');

All you must do is

SELECT foo.grp, count(*) / tablestat.total::float AS percent_total
FROM foo
CROSS JOIN (SELECT count(*) AS total FROM foo) AS tablestat
GROUP BY tablestat.total, foo.grp
ORDER BY grp;

 grp | percent_total
-----+---------------
 a   |           0.2
 b   |           0.3
 c   |           0.4
 e   |           0.1
(4 rows)

Now if you want percent to be displayed like a string, you can easily do that too. Because it's mere visual display, I suggest not to do this in a database but we can; a function like this is simple and hides the obscuring details of how you want the number rendered, from the math that calculates it:

CREATE FUNCTION display_percent(x double precision)
RETURNS text
AS $$
  SELECT (x*100)::text || '%'
$$ LANGUAGE sql
IMMUTABLE;

COMMENT ON FUNCTION display_percent
  IS $$Render a number in the range of [0-1) as a text-string (ex., 0.17 to '17%')$$;

And there we have it,

SELECT foo.grp, display_percent(count(*) / tablestat.total::float) AS percent_total
FROM foo
CROSS JOIN (SELECT count(*) AS total FROM foo) AS tablestat
GROUP BY tablestat.total, foo.grp
ORDER BY grp;
 grp | percent_total
-----+---------------
 a   | 20%
 b   | 30%
 c   | 40%
 e   | 10%
(4 rows)


Should a composite primary key out of 3-4 attributes or a unique artificial ID should be used for the situation below? For a small beginner project to start with databases and system development, I'm trying to develop a system that represents the classic case of an administration system for students and exams. This is based on the following diagram:

The two simple entities student and exam don't cause me any problems, but I'm struggling to find the best way to solve the many-to-many relationship between the two. I realize that I have to create an additional table for this, but I'm not sure if I should choose a composite key of 3-4 attributes or an artificial key with a unique ID. I have worked out both approaches a little, here are the results:

Composite Key:

With this solution, the idea is to identify a test result via the MatriculationNumber, ExamID and Date. In my opinion, the date must also be added to the key, since it is possible that exams that have not been passed must be taken again and managed in the system. Therefore, MatriculationNumber and ExamID would not be sufficient to uniquely determine the two completed exams. However, I also came up with an extreme case: What happens if an exam and the catch-up date (for whatever reason) take place on the same day and both have the same grade, say 5.0? Then it is not possible to distinguish these two things. The whole thing would become even more difficult if even a third attempt had to be taken into account.

Artificial ID:

In this case, a new, artificial ID is to be created in order to uniquely determine each result. However, there are no implicit advantages to the first solution, which, for example, allows only one entry for each exam and student and tag. However, I would like to be as flexible as possible with my solution, and not let the design of the database dictate my professionalism.

In general I would like to know from you experts which of the two solutions fits better for my application. It should be possible to obsolete several attempts of the same exam of a single student, even if they could fall on the same day. I don't want to let the database dictate or limit the subject. It should also be possible to build a REST-API based on what I find rather difficult in the case of the composite key, at least after what I have read on the net. In your opinion, what is the best practice for this problem?

EDIT
To describe the overall situation even better, a few additional details: For the implementation I would like to use a postgreSQL database. Because it is only a simple introduction example, where no billions of records are stored or time-critical operations are needed, the focus is not on performance at all. However, I find it more important to be able to expand the application and business logic later, for example by storing documents or other information. I don't want a feature to destroy the complete structure of my database or API. My main concern here is that it can happen to me that I have to make such rudimentary changes if I want additional information in the solution with the merged key. On the other hand, I still lack a clear statement or justification why I should introduce an artificial ID and what are the advantages of it

I agree with your analysis. Using (MatriculationNumber, ExamID, Date) as the key of TestResult will, indeed, allow each person to take the test once per day at most.

What to do about it depends on the problem domain - what is and is not allowed in real life. If a person is not permitted to resit an exam immediately your initial model is fine. You've indicated you would like to accommodate this, so further data must be captured to support it.

The easiest change would be to stop using DATE and start using DATETIME1. This will allow separate results down to the resolution of the clock. Inside the computer that's about microseconds. In real life, say, one minute. Can a student complete an exam twice inside sixty seconds? No - good; dust hands; job done.

Let's say we must retain the DATE column, because < reasons >. Then some other attribute must be added. I'll propose a new column called attempt_number. And because I must know this value to uniquely identify a row it becomes part of the table key. It now has a four-column composite key on (MatriculationNumber, ExamID, Date, attempt_number).

What values should my proposed column allow? One option is it resets each day. On Monday I have attempts 1, 2 and 3. On Tuesday I have attempts 1 through 7 (busy day). On Wednesday I start again at 1 and pass on attempt 4. Another option is that it resets per exam - I start with attempt 1 on Monday and I pass at attempt 14 on Wednesday. Yet another is it resets per student: this exam took attempts 43-45 and 47-57 because I have already had 42 goes at other exams before I started this one and I attempted a different exam on Monday afternoon.

There is no right or wrong option here. Each is valid. Each makes some queries easier and some harder (e.g. "how many times in total did Joe sit exam Alpha" versus "how many times did Joe sit exam Alpha on Tuesday"). The choice would be governed by the real-world practices you are modeling.

There is one further option for values of attempt_number - that they are global across all students, exams and dates. The first time anyone sits any exam it is given attempt number 1, the next is 2 and so on. attempt_number is, in fact, unique through the whole table. This is a good point to introduce some nomenclature.

A key is any column or set of columns whose values uniquely distinguish one row from all other rows in that table. Because multiple sets of columns can be unique each set is called a candidate key. In formal relational theory a relation must have at least one candidate key and can have more. Relational DBMSs (the physical implementation of relational theory) however typically allow tables (the implementation of relations) to exist without any key defined. Keys that consist of columns that occur naturally in day-to-day user conversation are termed natural keys. Every student will know her matriculation number. Every examiner will know the exam's ID. If you say "this is my 4th attempt today," that will be undestood. These could be used in natural keys. Sometimes we introduce new columns for performance or design considerations. If these new columns summarise and stand in for other candidate keys they are called surrogate keys. They are system-generated an internal. They are not well-know to people in the domain. No-one would understand if you exclaimed "I passed on ResultID 8720982!"

Thinking further about an attempt_number that is global across the whole TestResult table i.e. every row has a different value. Since knowing one value uniquely identifies a row it is now a candidate key. Since it stands in for the natural key (MatriculationNumber, ExamID, Date, < something else >) it is also a surrogate key. This is what I think you mean with your ResultID column. If we implement ResultID in this way, my proposed attempt_number is redundant and can be forgotten.

Surrogates have the advantage that they are shorter than the natural keys they represent. This saves space at the "child" end of a foreign key, for example. Sometimes it is just not possible to uniquely identify an entity by natural values. A person is the canonical example. They do have great flexibility as we as programers define them and can define their range and semantics to suit whatever purpose we choose. The drawback of surrogates is that UI input, report output and data transfers invariably need the "natural" values, so the foreign keys have to be traversed in these circumstances.

For the example given, I would implement ResultID as a surrogate ID. It's values will be global across the table. This will give flexibility to extend the schema easily in the future. I would also change DATE to DATETIME so a believable natural key also exists.

1 Don't ever name a column the same as a datatype. This column will be exam_start_time or somesuch.


MSSQL 2000 replication, incrementing an integer column on a single row using an update statement I have 2 web servers, both of which connect to separate mssql 2000 database servers, these two servers use merge replication in order to collate tracking data.

Now each web server runs an update statement to its database which goes something like this:

"update trackingdata set trackingvalue=trackingvalue+1 where id=@id"

Now I would have expected that when i go to each web server and action this that there would be a value on 1 in each table and then when the merge replication runs i would have a value of 2 in each table as there was 2 update statements but when the merge runs i am being left with a value of 1.

Can anyone explain why this is and or if I am doing something wrong ?

I need the value to increment on each server and for the new updates to add together on a merge.

Thanks in advance for any help you can provide guys!

Cheers.
Dylan.

Update: I think I should be using the 'Additive conflict resolver' for the article in question however I have supplied the column name in the text box below but I am receiving an error The specified conflict resolution column 'trackingvalue' could not be found., does anyone know how to get Additive conflict resolution working ?

You must either define identity ranges for each server (subscriber), but unsure if this was possible with SQL Server 2000. A more simple approach is to use GUID/uniqueidentifers for IDs, then you avoid conflicts.


Microsoft SQL Server, Error: 10054 The SQL Server Management Studio is working just fine when we RDP into the server and try to perform any sa privileges.However, when we use it from our local machines it is throwing the errors

Situation:

SSMS 2016 client

Version: Microsoft SQL Server 2014- Service Pack 1 -Standard Edition

TCP/IP is also enabled.

I can connect successfully to the database server using management studio but when I try to open database diagrams, I get the below error. I don't even see SQL server agent even if we login with sa privileges.

SQL Server agent service is running.

  TITLE: Microsoft SQL Server Management Studio
  ------------------------------
   An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.ConnectionInfo)
   ------------------------------
   ADDITIONAL INFORMATION:
   A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054)
   For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476
  ------------------------------
  An existing connection was forcibly closed by the remote host
  ------------------------------
  BUTTONS:
  OK

You say that by using RDP directly for logging onto and managing that server you obviously have enough rights to manage the server.

What is different is that you are now no longer using a direct connection?

If there are more than two computers involved, then probably Kerberos needs to be configured.
Are you sure that SQL Server Agent installed correctly.  Check for installation errors in the Error Log.
Have you manually tried starting or reconfiguring SQL Server Agent?  Or used the Services panel to start SQL Server?  Or run net start SQLSERVERAGENT.

If Kerberos is needed because of multiple hops between computers: Microsoft® Kerberos Configuration Manager for SQL Server® has some guidance on setting up and configuring Kerberos in your environment.


Throughput throttling on Azure VM We are just entering Azure world and will be migrating SQL server soon.I am trying to test throughput on Azure VM (DS13_V2) which is supposed to give 384MB/Sec as per documentation.

Here is the configuration :

a) No of cores : 8

b) Attached 2 disks (1TB each formatted with 64K block size) to the VM which          are P30 disks(200mb/sec).

c) I have striped 2 disks into one and called it F: drive with total 2TB which in theory is capable of giving 400mb/sec.

Test:
I ran SQLIO (with Test file size - 20GB on the striped drive F:) with multiple combinations and looks like throughput is throttled at 256mb/sec consistently.
I even ran diskspd which also gives me the same 256mb/sec throughput.

Question:
I am wondering where the bottleneck is or anything that i am missing as VM is capable of giving 384mb/sec and my striped disks are capable of giving 400mb/sec (200mb/sec each) wherein i should be seeing a throughput of 384mb/sec.

Please let me know if any suggestions would help me in doing a better storage test.

Those numbers in your performance tests are accurate. Theoretical throughput does not match my experience, after migrating an environment to more than ten DS13v2 instances. I get between 220MB/s and 256MB/s, depending on the type of writes, even with P30s.

To answer your question, the bottleneck is the network itself, which links the drives to the VM.


mysql query to get time difference I have query like this

SELECT user_ID, tanggal, kode_layanan, nomor_antrian, aksi_ID,jam
FROM `transaksi`
WHERE tanggal = '2018-10-11'
  AND kode_layanan = 'A'
  AND aksi_ID BETWEEN 4 AND 9

and the result is like the picture below

what I want to do is do calculation time difference "jam". I know there are similar question, but i think its difference condition. so how to solve query ? thanks

I think your column definition will be as shown in this query:

select
    user_ID,
    tanggal,
    kode_layanan,
    nomor_antrian,
    aksi_ID,
    jam,

    case aksi_ID
        when 4 then
            timediff (
                jam,
                (
                    select t2.jam
                    from transaki t2
                    where t2.aksi_ID = 9
                    and t2.nomor_antrian =
                        (
                            select max(t3.nomor_antrian)
                            from transaki t3
                            where t3.nomor_antrian < transaki.nomor_antrian
                        )
                )
            )
        else
            null
    end as idle_time

from transaki
order by jam;

The logic is: "if the current row has an aski_ID value of '4' then find the time difference between the current row's jam value, and the jam value of the row having an aski_ID value of 9 and a nomor_antrian value that is the maximum nomor_antrian value less than the current row's nomor_antrian value".

You can see this result working on db-fiddle.


Should I run a delete first before I execute index rebuild to a large table about 15 Million records I need help for a better approach on how to approach this.

Currently we have a table that is huge about 15 million records.  We plan to create an index on this table however due to large size, we decided to delete records first and then create the index.  Is this the right approach?

Badly needed an advise asap!

Really short answer: No.

Longer Answer:

A few thoughts to help you out here:

15 Million is a decent size - but it isn't impossibly huge.
I presume you are creating the index to better query it. So you care about the data inside of the table, right? So if you delete it you will have to just add the data again after the delete, now on a table with an additional index so that insert will be longer than it probably would have been.
Would you get rid of all of the data? If so, I wouldn't try and do a delete statement in one big fell swoop on a table of that size. It will all be logged and it will be slow and will impact your server's performance potentially worse than an index rebuild. If you are truly removing every row - a Truncate would be better IF you were to do a delete first.

So basically - my thought process is - no don't delete first.. There are always caveats, though.

Update

I answered like I thought you asked creating an index, not rebuilding. My answer doesn't change in fact it becomes simpler -  you still don't need to do this. In fact the point of an index rebuild is to reorganize the index and remove index fragmentation. If you remove all the rows in your table before hand, it sort of defeats the need for the rebuild in a sense, right? So you can make your index rebuild faster or reduce the impact.

If in Enterprise Edition you can do this as an online operation to reduce the impact to the users. Watch transaction log growth, though.

You can choose to Reorganize instead of Rebuild. There are caveats here though - you aren't fully taking care of all levels of fragmentation, it can actually take longer, but it can have less of an impact.

You can change the degree of parallelism for the actual rebuild operation to take advantage of parallelism.

You can use a script like this one from Ola Hallengren to actually see if you even need to rebuild first and then only rebuild if you need to.

But no.. You don't ever need to empty the table first - unless you plan to empty it afterwards anyway.


Database having only db_datareader shows invalid properties for a index If a SQL server database user has only public permission in server roles and for a particular database only db_datareader and public permission then while trying script index as CREATE To option in SSMS, the script generated will not be correct.

I mean if an index has filtered condition then that filtered condition is not displayed

So for a user who has owner permission script index as CREATE To will show

USE [test]
GO

/****** Object:  Index [filtered]    Script Date: 12/30/2013 18:54:19 ******/
CREATE NONCLUSTERED INDEX [filtered] ON [dbo].[Table_1]
(
    [b] ASC
)
WHERE ([Table_1].[b] IS NULL)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

For a user who only has db_datareader permission script index as CREATE To will show

USE [test]
GO

/****** Object:  Index [filtered]    Script Date: 12/30/2013 18:55:13 ******/
CREATE NONCLUSTERED INDEX [filtered] ON [dbo].[Table_1]
(
    [b] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

It would be OK if a user with db_datareader is not allowed to see the script of an index and shows access denied error. But it shows invalid script. Also if index properties is checked then only filtered condition is not shown and other properties of index are shown. Why this happens?

It's by design.

I can understand why it could be confusing to be able to see partial meta data. However, the idea behind the data role db_datareader is to grant a user read access to both user data as well as column information. (you wouldn't be able to create a SELECT statement if you didn't have access to the  column names as well.)

So with that in mind, showing only the part of the index definition that contains column info would be perfectly within the boundaries of the datareader permission.

The filtered index clause however is considered non column information so therefore you need more permission to be able to see this part of the definition as well.

You need VIEW DEFINITION permission on that object, or VIEW ANY DEFINITION to be able to see that extra bit of meta data.

I think it would be nice if they would give a warning when you generate a " incomplete"  CREATE script.

You can test it yourself:

--create a database
CREATE DATABASE [testdb]
go

--change context to new database
USE [testdb]
GO

--create table
CREATE TABLE [testtable] ( col1 int, col2 int)
GO

--create filtered index
CREATE INDEX [fix_testtable_col1] ON [testtable](col1) WHERE col1 is not null
GO

--create a login
CREATE LOGIN [testlogin] WITH   PASSWORD=N'test',
                                CHECK_EXPIRATION=OFF,
                                CHECK_POLICY=OFF

--create a user in your database
CREATE USER [testlogin] FOR LOGIN [testlogin]

--add the user to the data reader role
EXEC sp_addrolemember N'db_datareader', N'testlogin'

--Change your execution contect to the new user with just datareader rights.
EXECUTE AS LOGIN='testlogin'

--try and view the filter definition of the index. It will not show the definition.
SELECT  name,
        has_filter,
        filter_definition
FROM sys.indexes
WHERE name='fix_testtable_col1'

--go back to being you
REVERT

--try and view the filter definition of the index. It will succeed.
SELECT  name,
        has_filter,
        filter_definition
FROM sys.indexes
WHERE name='fix_testtable_col1'

--give the user VIEW DEFINITION rights
GRANT VIEW DEFINITION ON OBJECT::testTable to testlogin

--switch context once again
EXECUTE AS LOGIN='testlogin'

--try and view the filter definition of the index. this time it will work.
SELECT  name,
        has_filter,
        filter_definition
FROM sys.indexes
WHERE name='fix_testtable_col1'

--don't forget to switch back to being you.
REVERT


How to predict how much space a VACUUM FULL would reclaim? Is there a way to determine in advance how much disk space a VACUUM FULL on a particular table would return to the OS? Therefore you can decide whether it's worth the cost in doing so.
Bonus if there's a simple query to do this for every table in a database/server (rather than doing each one separately).

You need the pgstattuple extension to get the amount of free space.
So you could run
SELECT t.oid::regclass AS table_name,
       s.table_len AS size,
       dead_tuple_len + s.approx_free_space AS reclaimable
FROM pg_class AS t
   CROSS JOIN LATERAL pgstattuple_approx(t.oid) AS s
WHERE t.relkind = 'r'
ORDER BY (s.dead_tuple_len::float8 + s.approx_free_space::float8)
       / (s.table_len::float8 + 1.0) DESC;

where size and reclaimable are in bytes.
Be warned that this query is somewhat expensive.
That should give a pretty good estimate of the space that can be reclaimed, but in practice it will be slightly less, because

there is always some free space in each 8kB block that is too small to fit another row

some of the dead tuples might not be reclaimable if you have long running transactions


MySQL Replication - Issues with Triggers MySQL master has a table   Products that has triggers on update and delete.  Deleted/Updated records are stored in ProductsUpdatedDeleted tab.

When a record is deleted, the operation is replicated to the slave server.   The issue is that slave also executes the trigger thus adding a record in its ProductsUpdatedDeleted table.  Then a duplicate record in ProductsUpdatedDeleted received from the master server through replication.

What is the best way to stop the slave to execute triggers?  I can comment trigger code in the slave but I would prefer both servers in the same state to quick failover.

Basically, there are two methods
METHOD #1
Use row-based replication. It says so in Paragraphs 1-3 of "Replication and Triggers"

With statement-based replication, triggers executed on the master also execute on the slave. With row-based replication, triggers executed on the master do not execute on the slave. Instead, the row changes on the master resulting from trigger execution are replicated and applied on the slave.
This behavior is by design. If under row-based replication the slave applied the triggers as well as the row changes caused by them, the changes would in effect be applied twice on the slave, leading to different data on the master and the slave.
If you want triggers to execute on both the master and the slave—perhaps because you have different triggers on the master and slave—you must use statement-based replication. However, to enable slave-side triggers, it is not necessary to use statement-based replication exclusively. It is sufficient to switch to statement-based replication only for those statements where you want this effect, and to use row-based replication the rest of the time.

METHOD #2
Drop all the triggers on the Slave. No trigger present, no trigger to execute.
Do not do both method. Just select one method.


Is there any way to get the optimization time in MySQL? I'd like to see the time optimizer takes to optimize a query. Is there any way to get that time?


Socket recv() timeout on secondary nodes We started using MongoDB pretty recently. We are on version 2.6.3. We have a replica set of 4 nodes (1 Primary + 3 Secondary)

We are currently seeing some latency in replication. The Secondary nodes seem to be falling behind primary by a few minutes (2 mins to 20 minutes).

Looking at the log file of Secondary nodes, I do see socket timeouts.

  2014-10-01T08:42:03.485-0400 [rsBackgroundSync] Socket recv() timeout
  157.206.233.10:27017 2014-10-01T08:42:03.485-0400 [rsBackgroundSync] SocketException: remote: 157.206.233.10:27017 error: 9001 socket
  exception [RECV_TIMEOUT] server [157.206.233.10:27017]
  2014-10-01T08:42:03.485-0400 [rsBackgroundSync] DBClientCursor::init
  call() failed 2014-10-01T08:42:03.485-0400 [rsBackgroundSync] replSet
  sync source problem: 10276 DBClientBase::findN: transport error:
  XSNW10K832W:27017 ns: local.oplog.rs query: {}
  2014-10-01T08:42:03.485-0400 [rsBackgroundSync] replSet syncing to:
  XSNW10K832W:27017 2014-10-01T08:42:23.110-0400 [rsBackgroundSync]
  replset setting syncSourceFeedback to XSNW10K832W:27017

Here is what I see in the log file of Primary.

  2014-10-01T08:42:23.260-0400 [conn492807] getmore local.oplog.rs
  cursorid:95505391617 ntoreturn:0 keyUpdates:0 numYields:1
  locks(micros) r:8846 nreturned:1 reslen:1629780 108434ms
  2014-10-01T08:42:23.260-0400 [conn492807] end connection
  157.206.235.17:61991 (551 connections now open) 2014-10-01T08:42:23.276-0400 [conn496810] getmore local.oplog.rs
  cursorid:96066510233 ntoreturn:0 keyUpdates:0 numYields:2
  locks(micros) r:4403 nreturned:1 reslen:1629780 108438ms
  2014-10-01T08:42:23.276-0400 [conn496810] end connection
  157.206.235.16:58038 (550 connections now open) 2014-10-01T08:42:23.276-0400 [conn496812] getmore local.oplog.rs
  cursorid:95180580732 ntoreturn:0 keyUpdates:0 numYields:1
  locks(micros) r:2960 nreturned:1 reslen:1629780 108440ms
  2014-10-01T08:42:23.276-0400 [conn496812] end connection
  157.206.235.15:60989 (549 connections now open) 2014-10-01T08:42:23.291-0400 [conn496843] end connection
  157.206.235.17:55712 (548 connections now open)

Any insight on what is causing the socket timeouts and how to identify/fix the problem?

Just a shot in the dark. Could it be a problem with the primary servicing too many connectionss simultaneously?

This information comes from the mongodb 202 training.

If a primary can no longer service requests, there will be

socket exceptions
timeouts
the over servers can no longer ping the primary possibly causing an election
--maxConns limit connections to mongos can insulate mongods from getting into unpredicatble states

From MongoDB best practices

Connections MongoDB drivers implement connection pooling to facilitate efficient use of resources. Each connection consumes 1MB of RAM, so be careful to monitor the total number of connections so they do not over-whelm the available RAM and reduce the availablememory for the working set. This typically happenswhen client applicationsdo not properly close their connections, or with Java in particular, that relies ongarbage collection to close the connections.

If there are too many connections open on the primary at any one given time, secondaries, administrative, clients, etc, then your primary could also be running out of memory.

Here is an example layout, with connections coming from drivers, secondaries, to and from different processes. This can all quickly add up depending on the number of clients and machines in your infrastructure!


How can I select all of the parents of rows that exist in another table? I have two tables that share a many to many relationship. The relationships are stored in a third table.

CREATE TABLE Person
(
    Id int NOT NULL,
    Name varchar(255) NOT NULL,
    PRIMARY KEY (Id)
)

CREATE TABLE Filenodes
(
    Id hierarchyid NOT NULL,
    Name varchar(255) NOT NULL,
    PRIMARY KEY (Id)
)

CREATE TABLE PersonFilenodes
(
    FileId hierarchyid NOT NULL,
    PersonId int NOT NULL,
    PRIMARY KEY (FileId, PersonId)
)

I need to select all of the filenodes that belong to a person, as well as all of the files that are ancestors of those files.

I know how to select all of the Filenodes that belong to a given person:

SELECT Id, Name
FROM PersonFilenodes
JOIN Filenodes on Filenodes.Id = PersonFilenodes.FileId
WHERE Id = @personId

Thanks to marc_s, I also know how to select all ancestors of a given filenode:

SELECT Id, Name
FROM Filenodes
WHERE @filenodeId.IsDescendantOf(Id) = 1

I just can't figure out how to combine the two queries to achieve the result set that I need. How can I select all of the filenodes that belong to a person, as well as all of the files that are ancestors of those files?

I think you need:

SELECT f.Id AS FileId, f.Name
FROM Filenodes AS f
WHERE EXISTS
      ( SELECT *
        FROM PersonFilenodes AS pf
        WHERE pf.PersonId = @personId
          AND ( pf.FileId = f.Id
             OR pf.FileId.IsDescendantOf(f.id) = 1
              )
      ) ;

The inner query is a correlated subquery that takes two parameters: @personId, which is an actual parameter, and f.Id, which is the Filenode Id of the current row from the outer query.


Indexing a reference column Say I have a reference table like languages with only 20 records. Is it worth indexing a field like language_code (e.g. en, es) that's frequently used in queries? I'm wondering if an efficient server would effectively be indexing columns like this automatically.

This is specifically for MariaDB with InnoDB engine.

On the MySQL support page they write some about index optimization. (I assume MariaDB isn't deviating too much from this since they're somewhat similar.) Here they write that indexes are not so important on small tables.

  Indexes are less important for queries on small tables, or big tables
  where report queries process most or all of the rows. When a query
  needs to access most of the rows, reading sequentially is faster than
  working through an index. Sequential reads minimize disk seeks, even
  if not all the rows are needed for the query.

On the page about How to avoid full table scans they define the limit as "more than a few rows".

  For tables with more than a few rows, consider redesigning the query
  by adding an index for one or more of the columns tested in the WHERE
  clause.

I personally would add an index just because I always add index to the key on all tables. In your case I don't know if the index would contribute so much though.


Business Intelligence Development Studio installation for Visual studio 2010 problem I want to install Business Intelligence Development Studio for doing report development in Visual Studio 2010, however in my installation process, I cannot find BIDS in the feature selection.

What I have done is:

Install Visual Studio 2010
Upgrade Visual Studio 2010 to SP1
Installed SQL Server Data tools
Lastly, tried to install SQL server 2008 for BIDS feature. But it is not in the feature selection list

What is the matter for this problem? Is there anything that I need to install before I install SQL server 2008? If there is, can I have the link?

From your screenshot I judge that you are using the Express installer (It says: Select the express features to install), SSDT and Visual Studio 2010 have no influence on the options in this installer.

Make sure you install the Microsoft SQL Server2008 R2 RTM - Express with Advanced Services  as that is the only one including BIDS.

Have a look at Features Supported by the Editions of SQL Server 2008 R2 which clearly states that the regular express doesn't have BIDS and the Express with advanced services does have BIDS.

Installing BIDS has no prerequisite on Visual Studio or SSDT and SSDT has no influence whatsoever on the installer you are running.

Also, BIDS is by definition Visual Studio 2008 as explained here

  Business Intelligence Development Studio is Microsoft Visual Studio
  2008 with additional project types that are specific to SQL Server
  business intelligence.

If you're insisting on installing Visual Studio of a specific version you need to look into SQL Server data tools. SQL Server Data tools is not shown in the installation features but is a separate download and can not be reasonably used with SQL 2008


Connect to SQL Server 2012 running on Azure VM via local SSMS 2012 I have been testing VM on Azure. I have created a SQL VM running SQL 2012 on Windows 2012 and would like to connect to it via SSMS 2012 on my local instead of connecting via RDP through Azure Portal.

Thanks!

Yes, this is well documented.

Once you have configured the firewall, the ports, and your instance to use SQL authentication, you should be able to connect to the computer name (i.e. name.cloudapp.net)


Creating Archiving database for old data in MySQL I Have a Master and Various Slave Servers.

There is a huge amount of the data on all the servers and I wish to create an Archiving system for our Data.

Scenario is :
I want that only Last one year data to be on the servers and all other previous data to be move to archiving system.

Most of the tables are InnoDB and there may also be referential integrity between the tables. (When I purge/Delete the old data from the Servers, which i have moved to the Archive the referential integrity also may fail).

Since I have this data in the Production Servers, what steps should I take to accomplish this with no downtime?

I would suggest looking at the pt-archiver tool in Percona Toolkit.


100's of 1000's of report server jobs added - how to kill all? A colleague wanted to run about 70 SSRS reports using the EXEC ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='E3843E8F-632E-4F2F-BE7C-63D4CF10F2ED' command. (Changing @EventData)

Unfortunately he ran a cursor to loop through a list of them, but it seems to have just ran the first one around 500,000 times before it was cancelled. This seems to have added all of these to a queue of some sort that is constantly working them.

We have tried killing each process, restarting the ReportServer service, restarting the entire server, but each time new jobs continue to be created. How do I clear whatever queue these are sitting in?

This is kind of urgent, what do we need to do?

I am searching through the ReportServer database for a table to remove the rows from, the ones that stand out are Notifications and ExecutionLogStorage as these have enough data in them, but the likes of RunningJobs is empty.

I was wrong in my assumption that Notifications was not the table to be looking in. It was exactly the right one. Deleting all where the ReportID matched the one we were looking at fixed the problem.

DELETE FROM [ReportServer].[dbo].[Notifications] WHERE ReportID = '4837A901-7188-4FCB-B86A-7974D35D9F56'


Does Sql Server caches parameterized anonymous blocks? "Anonymous block" is a term from Oracle. But speaking of the thing for sql server
Declare
   ....
Begin
    begin trans
 . . .
    commit
End

If I send this SQL in parameterized form to the server via ADO.NET, will it be cached and reused or it will be recompiled every time?

From my brief reading on Anonymous Blocks in Oracle, this is essentially just an ad-hoc query in SQL Server. Yes, ad-hoc queries are cached in the query plan cache of SQL Server, and therefore on subsequent runs won't be recompiled, for as long as they stay in the cache. (The query plan cache has mechanisms to remove old and/or rarely used plans from the cache based on the busyness of the server.)


Index for a Postgres query with a sort and an equality This query is very slow:

EXPLAIN (ANALYZE, buffers) SELECT *
FROM
    "Follows" AS "Follow"
INNER JOIN "Users" AS "followee" ON "Follow"."followeeId" = "followee"."id"
WHERE
    "Follow"."followerId" = 169368
ORDER BY
    "Follow"."createdAt" DESC
LIMIT 1000;

Here is the explain:

Limit  (cost=0.86..2120.08 rows=141 width=814) (actual time=0.776..239.289 rows=262 loops=1)
  Buffers: shared hit=750 read=673 dirtied=1
  ->  Nested Loop  (cost=0.86..2120.08 rows=141 width=814) (actual time=0.774..239.148 rows=262 loops=1)
        Buffers: shared hit=750 read=673 dirtied=1
        ->  Index Scan using follows_followinglist_followerid_createdat_idx on "Follows" "Follow"  (cost=0.43..681.88 rows=170 width=41) (actual time=0.377..52.687 rows=262 loops=1)
              Index Cond: ("followerId" = 169368)
              Buffers: shared hit=149 read=115
        ->  Index Scan using "Users_pkey" on "Users" followee  (cost=0.43..8.45 rows=1 width=773) (actual time=0.559..0.709 rows=1 loops=262)
              Index Cond: (id = "Follow"."followeeId")
              Buffers: shared hit=601 read=558 dirtied=1
Total runtime: 239.545 ms

I have the following indexes (currently, it is using the first one):

CREATE INDEX follows_followinglist_followerid_createdat_idx ON "Follows"
  ("followerId", "createdAt" DESC)
CREATE INDEX follows_followinglist_followerid_createdat_idx2 ON "Follows"
  ("createdAt" DESC, "followerId")
CREATE INDEX follows_followerId_fk_index ON "Follows" ("followerId");
CREATE INDEX  "follows_createdat_index" ON "public"."Follows" USING btree("createdAt" DESC);

Before I added the first two, the cost was slightly lower (~2000) using follows_followerId_fk_index, but still too slow.

I'm wondering if there's a way to optimize this further.

Simplified query to make it readable:

SELECT *
FROM   "Follows" f
JOIN   "Users"   u ON f."followeeId" = u."id"
WHERE  f."followerId" = 169368
ORDER  BY f."createdAt" DESC
LIMIT  1000;

Your index follows_followinglist_followerid_createdat_idx looks good for the job. In Postgres 9.2+ it might get a bit faster if you append "followeeId" to the index - if and only if you can get an index-only scan out of this. Maybe not possible in your case.

CREATE INDEX follows_foo_idx ON "Follows"("followerId", "createdAt" DESC, "followeeId");

Generally, this query won't get lighning fast, since you have to fetch up to 1000 random rows from "Users". The key to performance will be:

Optimized table layout of "Users" to keep the table as small as possible, so fewer blocks have to be read and more of it can stay in cache.
Plenty of RAM and cache settings accordingly if your table "Users" is big.

It would also help to cluster the "Follows" table from time to time. Details:

Configuring PostgreSQL for read performance


Percona 5.7.24 : How to fix disk slave storage full I find that my slave disk is full, but I don't understand what makes it full.
Can anyone help me explain what this file is?
And how it is formed, so that it makes lots of files that make storage full.
Check in mysql error log I did not find anything
Some file types that make my disk full like this:

#sql files are temp tables generated by ALTER and other maintenance commands.  You will need to find what that command is.  At that point, you will probably find that it is in a tight loop, either spawning off process after process, or crashing and restarting.

Other clues:  The table name (for many of them) includes "ib916".  The table is about 7GB in size (disk footprint).


How to daily copy selected data from MySQL and PostgreSQL servers to another PostgreSQL server I have two source servers, a MySQL 5.7 and a PostgreSQL 9.6, respectively. I would like to daily update some tables from each source to another target PostgreSQL 11 server (via a cron-job).

On the target server I also have some views involving the migrating tables that should update regularly accordingly.

I used pgloader for the initial MySQL -> PostgreSQL data migration and pg_dump for the initial PostgreSQL-> PostgreSQL data migration. However, because of the aforementioned views, the pg_dump command (see below) refuses to alter the tables because of the dependencies. The specific pg_dump command was

sudo pg_dump -x --no-privileges --no-owner --dbname=postgresql://<source_user>:<source_pass>@<source_server>/<source_db> -t 'table_1'  -t 'table_2' -c | psql --dbname=postgresql://<target_user>:<target_pass>@<target_server>/<target_db>

How could I run the data update without running into the dependencies issues? Moreover, is there a unified way to achieve the above updates for both MySQL -> PostgreSQL and PostgreSQL -> PostgreSQL? Ideally, I would like to avoid having the views defined as part of the update process. I am running on Ubuntu-18.04-bionic.

What you are looking for is called ETL (extract transform load) tool
https://en.wikipedia.org/wiki/Extract%2C_transform%2C_load

There is no FREE/open source pre-built tool that exist to do what you want when moving data from different databases  that I am aware of.  I have used the paid for ETL tools they just speed up the process coding/typing time.  The computer generated scripts  always require tweaking to get to work and speed them up.

here is list  i found... (no idea how valid this review is,  i do not use such tools)
https://suyati.com/blog/top-10-etl-tools-salesforce-data-migration/

What you have to do is write scripts that update the tables in sequence you want.

This script can be python, perl, or some other script that runs and moves the data from one server to another.

Another option is use FDW on the PG 11 server to copy the data from the MySQL and PG 9.6 server.  Using FDW means it can be done in pure SQL using simple Updates and Inserts.
https://www.postgresql.org/docs/current/postgres-fdw.html
https://wiki.postgresql.org/wiki/Foreign_data_wrappers

To execute the  SQL script use PG Agent for Perl/Pytho/other script language use cron Job.


Is it possible to use part of a composite primary key as a foreign key to another table? In my attempt to model a one to many relationship, I am trying to achieve the following in SQL (MariaDB) given my basic DB knowledge:

Profiles Table:
Primary Key: profileName <-----
                              |
Repositories Table:           |
Composite Primary Keys: (profileName, repository_name)

Is this a possibility?

This is certainly possible and even very reasonable in many situations. Think for instance to the classical Order and OrderLine tables, in which the OrderLine has as primary key the order number and the line number, with order number foreign key for the Order table.


MySQL in Ubuntu Linux - I cannot connect to the server I am new in MySQL. I installed Workbench and server. When i try to connect to the database server i receive a message: Your connection attempt failed for user 'root' to the MySQL server. Ann AppArmor policy prevents this sender from sending this message to this recepient; type .
enter image description here
enter image description here

AppArmor is a Linux kernel security module that allows the system administrator to restrict programs' capabilities with per-program profiles. Have you tried to disable it and see if the connection works?
Check here on how to do that: help.ubuntu.com/community/AppArmor#Disable_AppArmor_framework
Another idea you can check with ss -tnl if you can see localhost:3306 on listening mode, if ss is not available use netstat -tnl
$ man ss
ss - another utility to investigate sockets
-t, --tcp
    Display TCP sockets.

-n, --numeric
    Do not try to resolve service names. Show exact bandwidth values, instead of human-readable.

l, --listening
    Display only listening sockets (these are omitted by default).

# Usually something like that means you have the service listening on
# localhost port 3306 in that case the port you configured your mysql to be running
State   Recv-Q   Send-Q     Local Address:Port      Peer Address:Port  Process
LISTEN  0        4096           127.0.0.1:3306          0.0.0.0:*


Using MSDTC in SQL Server 2008 I used Hyper-v to install one windows 2008 server machine, and copy the image to create another one and change the computer name, both of them are in the same domain, and I use a domain user as administrator to configure them.

After installing SQL server 2008, I tried T-SQL below:

SET XACT_ABORT ON
GO

BEGIN DISTRIBUTED TRANSACTION
EXEC CatalogSQLServer.Catalog.dbo.Hello
ROLLBACK

The first time I tried，I got the error information like:

  OLE DB provider 'SQLNCLI' for linked server '' returned message 'No transaction is active

I am 100% sure that I have configure DTC,Linked server and allow all inbound and outbound rule of firewall.  I configured MSDTC based on these instructions.

after that, I tried use command:

msdtc -uninstall
msdtc -install

...and then restart dtc to re-install msdtc on CatalogSQLServer.  But this time, when I execute the test script, SSMS will keep executing for a very long time but didn't get any result.  Does anybody know how to fix this issue?

First check that your computer name is not more than 15 characters as MSDTC relies on NetBIOS , if it is less than that follow the instructions from Microsoft on How to troubleshoot connectivity issues in MS DTC by using the DTCPing tool.


Google Cloud SQL lacks raster2pgsql Google Cloud SQL supports the PostGIS extension, but I haven't seen any documentation regarding how to work with raster data on it. To load raster data into a table, we have to use raster2pgsql driver.

In this sense, does Cloud SQL support it? Also, it would be really useful if it had out-of-db raster support considering we could store data in Cloud Storage and query it from there.

raster2pgsql

This a helper to make the job easy. It's not strictly speaking required.

  raster2pgsql  is a raster loader executable that loads GDAL supported raster formats into sql suitable for loading into a PostGIS raster table. It is capable of loading folders of raster files as well as creating overviews of rasters.

You can just redirect the output of raster2pgsql into a file

raster2pgsql > myFile.sql

Then run myFile.sql on Google Cloud.