texts:
I have four databases each holding several hundred tables.

Every table in one database suddenly returns error code 1146 Table xxx does not exist. The other databases are fine. I can see all the .ibd and .frm files ok and their various file sizes suggests they are not empty. There is an ibdata1 as well.

From workbench I tried Analyse Table, which indicates InnoDB: Tablespace is missing for table '...' and going on to 'Doesn't exist'.

I created new test tables and they work fine.

OSX was updated the other day, but as the other databases are fine I can't see a connection.

I haven't manually moved/copied etc any files etc.

Something has gone awry, but what?


texts: Can I open a SQL Server 2008 R2 database in SQL Server 2008 R2 Express?

texts:
This question has probably been asked a dozen times but I couldn't find them here or at SO.

Can I attach/restore a SQL Server 2008 R2 database to my local SQL 2008 R2 Express instance?


texts: Upgrading from single server to new GTID-replicated master and slave MySQL 5.7

texts:
I'm going from a single, non-GTID MySQL 5.6 server to a master-slave replicated setup on 5.7 (could be 8.0 too) with GTID on CentOS 7.x. Some details: format = ROW, minimal logging, file_per_table InnoDB

What's the best path to make the switch? The original server is in production, and I can stop it for discrete moments without much disruption.

If I set the new master and slave with replication from scratch and then import the database dumps (plain, no GTID data here) to the master, everything will get replicated right away, right?

My plan is this:

Stop the app associated with a database (WordPress blogs mainly, front-end caching can keep downtime unnoticed).
mysqldump individual database
Import dump into new master (replication propagates data to the slave)
Redirect app to new master, restart app

The slave will act as an emergency replacement and backup source while the master will bear the brunt of reads and writes. If load justifies it, maybe I will use the slave for reads and the master for writes in the future.

Am I too off in my plan?


texts: Does anyone ever use database sequences that count down from 2^64-1?

texts:
Does anyone ever use database sequences that count down from 2^64-1, rather than up from 1, for assigning ids to rows?

The motivation for doing this would be to ensure that any application consuming these ids does not encounter an overflow bug at some bit width < 64, the most obvious being trying to use a 32-bit int to hold the id. If you start at the top, problems should arise sooner during development.


texts: Questions concerning performance/production resources where exporting / importing big data sets

texts:
I have a MS SQL Server database that contains data for many customers and I am working on a feature to export a single customers data.

I have basically created a script that generates inserts for all the customer records to run on another database. This is working great in my test environment but I am concerned about what impact this will have on production.

We could of used a product like RedGate but we decided to integrate this logic within our app itself.

Export Action

No transaction required
Need to be run on live database but will be off hours.
Right now I have a stored procedure that returns the insert statements.

Import Action

Transaction required.
Need to be run on live database but will be off hours.
I run the returned insert statements on the new database.

Questions (mostly regarding the production environment)

The export script touches virtually every table in the system (with no lock), should I be concerned about this action? I have had suggestions to break up this action into pieces, to not create one giant execution. Would there be an advantage to this?
The import script will potentially be running millions of inserts. With similar concerns to the export, should I be adding "GO"s every X inserts? Is that enough? Should this be broken into multiple executions? Is this a scenario where I need to use SQL Bulk Copy?

Thank you very much for your help.


texts: Find application password expiration date

texts:
I changed password of my application on one sql server?
when I am using Login property command I am getting last login date.
I want to know when my application password is going to expired in SQL server.
Please help me.
Thank You all.


texts:
I have two users in my database. When I execute following query under user ONE it successfully executes; however when I execute it under user TWO it generates a error message:

Query:

select cast ('2015-04-28 16:00:00.000' as datetime)

Error Msg:

  Msg 242, Level 16, State 3, Line 11
  The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

User ONE has English as default language and its a simple Server login
User TWO has British English as default language and is also used as Application User

DB Properties

Since in my real query columns are in Where clause to get result set for a specified date range:

    CAST(CONVERT(VARCHAR(10), Createdate, 101) AS DATETIME) >= CAST(CONVERT(VARCHAR(10), StartDate, 101) AS DATETIME)
    AND
    CAST(CONVERT(VARCHAR(10), Createdate, 101) AS DATETIME) <= CAST(CONVERT(VARCHAR(10), EndDate, 101) AS DATETIME)

I verified columns Createdate, StartDate and EndDate have a DateTime datatype in database.

What I have tried so far:

Removed casting cast(columnname as datetime) and its working fine.

CONVERT(VARCHAR(10), Createdate, 101)  >= CONVERT(VARCHAR(10), StartDate, 101)
AND
CONVERT(VARCHAR(10), Createdate, 101) <= CONVERT(VARCHAR(10), EndDate, 101)

This is not the solution there are more than 3 thousand SPs and we can't change all in a single click? is there any other solution?
I changed language of user 'Two' to English by following steps Security->logins->User'two'->Default Language to verify is this the only reason but query still failed on Casting

Any idea what settings are we missing? Do I have to verify date format for both users?

If removal of casting or use of T is the only solution then it require time for modifications and then QA, do we have any temporary solution so nothing effect with our deadline and we have enough time to change SPs according to best practices?


texts: How to troubleshoot enq: TX - row lock contention?

texts:
I have the following situation.

I have RAC. On both nodes there are the locks.

On the First Node

    SID EVENT                           USERNAME    BLOCKING_SESSION    ROW_WAIT_OBJ#   OBJECT_NAME LOCKWAIT            SQL_ID          STATUS
1   102 enq: TX - row lock contention   MYUSER      155                 136972          TABLE1V     0000000810EFA958    5f4bzdg49fdxq   ACTIVE
2   111 enq: TX - row lock contention   MYUSER      155                 136972          TABLE1V     0000000810EFAC98    5f4bzdg49fdxq   ACTIVE

Blocking session info

    SID EVENT                       USERNAME    ROW_WAIT_OBJ#   OBJECT_NAME LOCKWAIT    SQL_ID          STATUS
1   155 SQL*Net message from client MYUSER      136971          MyTABLEIMAGES_IDPK      4hw85z8absbjc   INACTIVE

On the Second Node

    SID EVENT                           USERNAME    BLOCKING_SESSION    ROW_WAIT_OBJ#   OBJECT_NAME   LOCKWAIT          SQL_ID          STATUS
1   65  enq: TX - row lock contention   MYUSER      155                 137033          FactTABLE1V   0000000810EF9B58  1mznc2z75ksdx   ACTIVE
2   111 enq: TX - row lock contention   MYUSER      155                 136972          TABLE1V       0000000810EF9818  5f4bzdg49fdxq   ACTIVE

Blocking session info

    SID EVENT                       USERNAME    ROW_WAIT_OBJ#   OBJECT_NAME  SQL_ID  STATUS
1   155 SQL*Net message from client MYUSER      127176          MYTableLOG           INACTIVE

Additional Info : Blocking session SQL_TEXT

create or replace procedure ACTIONProcedureDELETE
(
p_ID NUMBER
)
 is

 cursor oldval is select r.id,r.sessionstatus
  from MyTABLEIMAGES  r where r.idparent=p_ID;

begin
       update  actionmyTableblock r  set r.status='False' where  ID=p_ID;

   for oldvalItem in oldval loop

    if oldvalItem.Sessionstatus='True' then
      update MyTABLEIMAGES r set r.sessionstatus='False' where r.id=oldvalItem.Id;
    else
      update MyTABLEIMAGES r set r.sessionstatus='True' where r.id=oldvalItem.Id;
    end if;
  end loop;

end ACTIONProcedureDELETE;

How do I troubleshoot this ?

As you can see blocking session is INACTIVE but still locking.

If I select v$sql_bind_capture there is no value for VALUE_STRING for blocking session sql_id.

From where to start?

I can guess that there is missing commit/rollback but application developer says "I have everything ok, I have written commit where it is necessary"

Please help.


texts: IOPS needs of Root Volume vs Database/Log Files Volume

texts:
I'm setting up a database server via AWS EC2, using:

Root EBS Volume, General-purpose SSD
EBS Volume, Provisioned IOPS

And intending to configure PostgreSQL to have the database and log files on the PIOPS volume.

I'm wondering how much IOPS strain that the root volume will have, since the base install of the DB engine and instance will be there, hence still handling the flow of data for the networking and database logic.

For example, if I have 2000 IOPS on the DB volume and 3 IOPS on the root volume, that database server would certainly be bottlenecked by the root IOPS. What does a good ratio look like?


texts: dm_db_index_physical_stats results inconsistent with actual object properties - what may be the reasons?

texts:
SQL Server 2008 RTM (10.0.1600.22)

Running a typical index fragmentation report on a table:

SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
       i.name AS IndexName,
       s.index_type_desc,
       s.avg_fragmentation_in_percent,
       s.fragment_count,
       s.page_count,
       s.record_count
FROM
    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('RPT_XXX'), NULL, NULL, 'DETAILED') s
    INNER JOIN
    sys.indexes i ON i.OBJECT_ID = s.OBJECT_ID
                       AND i.index_id = s.index_id
                       and i.object_id = OBJECT_ID('RPT_XXX')
WHERE
    s.avg_fragmentation_in_percent > 25

tells me that one index on that table has 7 fragments, 7 pages, about 700 rows and over 28% fragmentation. However, when I look at the index storage properties in the Management Studio, I get nearly 9000 pages, over 12 million rows, and 0.01% fragmentation, which are the expected numbers because the table is big and I just did the index rebuild and updated statistics on the table.

What, apart from a possible bug in the unsupported RTM version of SQL Server, might be the reasons for this discrepancy?


texts: SQL Server detected 2 sockets with 4 cores per socket but only one NUMA node

texts:
We have a SQL Server 2012 SP 2 CU 8 instance in a VMWare host. The operating system is Windows Server 2012 R2.

Windows Task Manager says that we have two sockets and 8 virtual cores. This number corresponds with the actual hardware beneath the guest.

In Task Manager I also can't change the view to NUMA nodes.

In SQL Server still only one NUMA node to see

Also this snippet from Glen Berrys diagnostics queries gives me this information

EXEC sys.xp_readerrorlog 0, 1, N'detected', N'socket';

  SQL Server detected 2 sockets with 4 cores per socket and 4 logical
  processors per socket, 8 total logical processors; using 8 logical
  processors based on SQL Server licensing. This is an informational
  message; no user action is required.

From all this information i can suspect that we are running on only one NUMA node but across two sockets.

My Questions:

Is it right that we are running on only one NUMA node?
Can this hurt performance even when everything is located on one NUMA node?
What component exactly dictates the NUMA boundaries? E.g. if I have a
two socket mainboard with two Intel E5 processors does the mainboard
dictate how many NUMA nodes I have or the processor.

I think there is a lot of misinformation out there about NUMA nodes. If someone can answer my question or point to a useful resource that explains this concept in detail that would be really appreciated.


texts: SQL Server Processor affinity with trace flag 8002 ON

texts:
I have been going through an article that explains the unwanted side effects of Setting a Processor Affinity in SQL Server.

What I have come to know is that in most of the cases the default setting of SQL Server works best as when we are setting processor affinity, we are making the scheduler CPU bound. I have a couple of questions here:

When the default setting of Processor affinity works best, then why does the need arise to set Processor affinity explicitly?
The article also states:

  With the trace flag 8002 turned on, the scheduler is not bound to CPU anymore.

Is it justified to say that trace flag 8002 being ON is just like the default Processor affinity configuration with the exception of having less number of cores available for SQL Server?

For example, if we set the processor affinity to 16 cores for a 32 core processor (with trace flag 8002 as ON), then would the schedulers that have SQL threads associated with them, will have only 16 cores to toggle?
Setting a Processor affinity restricts the SQL Server Scheduler to use some cores. Does this setting also restricts the core usage for other CPU intensive processes, such as a .NET application?

What I am trying to say is, if for a 32 core CPU, the processor affinity is set to 16 core, then SQL Server scheduler has only 16 cores to toggle. If yes, then what about another CPU intensive application such as a .NET application? Does the .NET app have the liberty of switching between all 32 cores, since it will be dependent on Windows OS and not SQLOS?

It is just a hypothetical scenario I had imagined. Given a production scenario, I highly doubt people would go for SSMS and a CPU intensive .NET application on the same server. That will greatly hamper performance and scarce the CPU availability for any other process. Correct me if I am wrong here.


texts: PostgreSQL CREATE TYPE using multiple relations?

texts:
I'm becoming familiar with PostgreSQL's ability to define a data type https://www.postgresql.org/docs/current/sql-createtype.html using CREATE TYPE syntax.

It appears to me that this type does not allow to compose a type out of relations/tables. In other words, I can do this:

CREATE TYPE compfoo AS (f1 int, f2 text);

but I can't do

CREATE TYPE compfoo AS (f1 relation, f2 relation);

I was wondering if my understanding is correct, and if not, how one could achieve the latter?

I want to indicate that I don't have any particular application for it. I would actually be interested in starting a discussion on an application! I am just trying to expand my understanding of this feature which is often touted as a very powerful feature in the extensible DBMS world.


texts: How to transfer 70GB MySQL with no downtime?

texts:
I have a large amount (4000) of individual databases, 90% of which are small. They all include about 20 tables each, and in total, they use about 75GB of space. All tables are MyISAM.

I want to transfer them to a newer, more powerful server with an SSD for increased performance.

We do not need to continue using MyISAM - i.e. we're not using fulltext or anything - so whilst we could convert to InnoDB and then use Xtrabackup, the time it will actually take to convert to InnoDB concerns me.

Also, during the conversion, wouldn't the database be locked? I don't want any (or at least, less than 5-10 minutes) downtime on the master server.

I cannot use Xtrabackup because it will bring everything down while it copies as it's all MyISAM. I can't use rsync because mysqld has to be stopped during the copy. I can't convert to InnoDB and then use Xtrabackup because the time it will take to convert about 80000 tables (even if they are small) concerns me, and those tables will lock during the conversion process.

And I can't find any other solutions to get this data across! Help is hugely appreciated.


texts:
I have the given table:

mysql> describe streaming_innodb;
+---------------+------------------------+------+-----+---------------------+-----------------------------+
| Field         | Type                   | Null | Key | Default             | Extra                       |
+---------------+------------------------+------+-----+---------------------+-----------------------------+
| row_id        | bigint(20) unsigned    | NO   | PRI | NULL                | auto_increment              |
| create_time   | timestamp              | NO   | MUL | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| user_time     | timestamp              | NO   | MUL | 0000-00-00 00:00:00 |                             |
| uTINYINT      | tinyint(3) unsigned    | YES  |     | NULL                |                             |
| uSMALLINT     | smallint(5) unsigned   | YES  |     | NULL                |                             |
| uMEDIUMINT    | mediumint(8) unsigned  | YES  |     | NULL                |                             |
| uINTEGER      | int(10) unsigned       | YES  |     | NULL                |                             |
| uBIGINT       | bigint(20) unsigned    | YES  |     | NULL                |                             |
| uDecimal_10_5 | decimal(10,5) unsigned | YES  |     | NULL                |                             |
| uDecimal_10_0 | decimal(10,0) unsigned | YES  |     | NULL                |                             |
| uFloat_30_15  | float(30,15) unsigned  | YES  |     | NULL                |                             |
| uFloat_10_10  | float(10,10) unsigned  | YES  |     | NULL                |                             |
| sTINYINT      | tinyint(4)             | YES  |     | NULL                |                             |
| sSMALLINT     | smallint(6)            | YES  |     | NULL                |                             |
| sMEDIUMINT    | mediumint(9)           | YES  |     | NULL                |                             |
| sINTEGER      | int(11)                | YES  |     | NULL                |                             |
| sBIGINT       | bigint(20)             | YES  |     | NULL                |                             |
| sDecimal_4_1  | decimal(4,1)           | YES  |     | NULL                |                             |
| sDecimal_10_2 | decimal(10,2)          | YES  |     | NULL                |                             |
| sFloat_2_0    | float(2,0)             | YES  |     | NULL                |                             |
| sFloat_15_8   | float(15,8)            | YES  |     | NULL                |                             |
| rCHAR5        | char(5)                | YES  |     | NULL                |                             |
| rCHAR100      | varchar(100)           | YES  |     | NULL                |                             |
| rDATE         | datetime               | YES  |     | NULL                |                             |
| rTIME         | time                   | YES  |     | NULL                |                             |
| rDATETIME     | datetime               | YES  |     | NULL                |                             |
| rTIMESTAMP    | timestamp              | NO   |     | 0000-00-00 00:00:00 |                             |
+---------------+------------------------+------+-----+---------------------+-----------------------------+
27 rows in set (0.00 sec)

Assuming I want to find the value of a column, based on a value in another column, I can use the LIKE method

Example:

mysql> select user_time from streaming_scaledb where rTime like "04:44:21";
+---------------------+
| user_time           |
+---------------------+
| 2019-03-18 04:52:05 |
+---------------------+
1 row in set (0.02 sec)

However, for this problem I only know the value "04:44:21"; ie I need to find X and don't know the expected user_time.

mysql> select user_time from streaming_scaledb where X like "04:44:21";

Is there any query I can use such in order to find X?


texts: Is possible to bypass the 32 bit limitation in MongoDB?

texts: Can you help me please? I have got server for research purposes, where I have used MongoDB. But in this time size of my data exceed 2 GB limit. I do not any possibility of migrating to 64 bit. version. Is possible to bypass the 32 bit limitation in MongoDB some way please? Or it is definitely the end of my research on this system?


texts: Execute job every time database changes - execute job on each change-event

texts:
I have a transactional-replication running in my database, and I need to execute a service on my website that refreshes the cache every time the database changes.

So i guess i have two options to make it work.

Execute job when transactions occur
Execute a job on every database change

But i have no idea how to do either of those.
Is it even possible? I have been looking around and it seems that the "on-transaction" event, is not really possible because the replication agent is not starting and stopping, it's just running the whole time.

And executing a job on every database change should be possible, but again, never tried it before, but i really would bet money on it being possible.

Anyone know how this can be done?
Thanks in advance :)

EDIT:

I have two sets of servers, two webservers and two database servers.
The db-server has transactional replication to the other db.

Each webserver has it's own website running with some pretty deep caching.
I need to trigger a cacherefresh webservice on the webserver so that the replicated changes will be visible to users.

Yesterday after writing this question, i set up a scheduled job to refresh the cache, and if this works wothout any hicks, i would rather keep that solution going, but if not i will need to trigger the refresh some other way.

Thank you :)


texts: postgresql on windows won't install with data and executables on different disks

texts:
I am trying to install postgresql 9.4.4 on a windows 2008 rc2 machine that has 2 HDs.
The first HD is, in theory, used only for executables/code/etc while the second one, being faster, should be used for data.

When I try to install postgresql the only way in which I can get an installation is if I select to put postgresql's exec and data on the same disk (the exec one). If I decide, as I want, to install postgresql "split" (with the 'data' directory on the second disk) the installation tells me that the cluster could not be initialized.

How can I solve this? I think it's related to some setting in Windows 2008 but I don't know what or which.

Ideas or pointers or RTFMs? :)

(I have noticed other similar questions but they don't apply in this case....)

Thanks!


texts:
When I Execute
 SELECT * FROM FN_TRACE_GETTABLE(N'E:\Trace_Files\trace1.trc', 1)
It says "You do not have permission to run 'FN_TRACE_GETTABLE", What type of permission on server required to execute the above query ??


texts: What happens if one DC goes down in Cassandra multi DC setup?

texts:
We have a Cassandra multi DC setup with 3 nodes in each DC, enabled with NetworkTopologyStrategy and RF 3.

The java client interacts with Cassandra cluster using read and write consistency as LOCAL_QUORUM.

We have our service in each of these DC (same service, just config changes), so that LOCAL_QUORUM helps pick the co-ordinator in the same DC and responds back without waiting for other DC.

There is no interaction between the services running in these 2 DCs.

What would happen if one of the DC goes down completely and is not able to recover soon? Obviously, the service running in the same DC would be affected, but what would happen to the other DC which is still up? I guess the service won't be affected as it talks to local DC only, and think that the read/writes won't be affected even if they are not relayed to the other DC.


texts: Can't restart or stop Firebird 1.5 on linux

texts:
I have an ubuntu server running firebird 1.5. It was working fine, but recently it started hanging (firebird does not respond anymore). Even when trying to restart or stop the server, it shows an error:

>sudo service firebird1.5-super stop
 * Stopping Firebird 1.5 server manager...
Unable to complete network request to host "localhost".
-Failed to establish a connection.
-Connection reset by peer
can not attach to server

What can be happening?


texts: What's the difference between xp_fixeddrives and this query

texts:
when i run xp_fixeddrives command to the drive that contains my tempdb, I obtain 10mb of free space, but the following query return other thing (45000 mb free).

other query :

USE tempdb;
GO
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

this drive only contain my tempdb db.

what is my error?


texts: Backup TO URL WITH NOINIT fails because file already exists

texts:
There's a similar question here but I cannot work out the solution because the circumstances are slightly different, so please bear with me; I've been wresting with the official help docs all day.
My goal is to have one backup file per database per day, with 15 minute recovery objective.  I am scheduling agent jobs as follows:

Once a day at 00:00, create a full backup of each database specified
Every 15 minutes, create a transaction log backup of each database specified

Ignoring all the code to iterate through the required databases, this is the core logic of part 1 above:
    DECLARE @dbName nvarchar(50) = (SELECT DatabaseName FROM #DbsToBackup WHERE (Id = @current));
    DECLARE @dbNameAndDate nvarchar(200) = @dateString + '_' + @dbName;
    DECLARE @dbSpecificContainerUrl  nvarchar(MAX) = @containerUrl + @dbNameAndDate + '.bak';
    DECLARE @verifyError nvarchar(200) = N'Verify failed. Backup information for database ' + @dbName + ' not found.';

    BACKUP DATABASE @dbName
         TO URL = @dbSpecificContainerUrl
         WITH CREDENTIAL = @containerCredential
        ,NOINIT
        ,NAME = @dbNameAndDate
        ,COMPRESSION
        ,CHECKSUM
        ;

Then, the transaction log backup logic is as follows (same steps for grabbing the database name, date strings, etc.:
    BACKUP LOG @dbName
         TO URL = @dbSpecificContainerUrl
         WITH CREDENTIAL = @containerCredential
        ,NOINIT
        ,NAME = @dbNameAndDate
        ,SKIP
        ,COMPRESSION
        ,CHECKSUM
    ;

Part 1 works on the first attempt, but part 2 (and any subsequent re-run of part 1) immediately fails with this:

A nonrecoverable I/O error occurred on file "https://.....Test.bak:"
The file https://....Test.bak exists on the remote endpoint, and WITH
FORMAT was not specified.  Backup cannot proceed..  [SQLSTATE 42000]
(Error 3271)  BACKUP LOG is terminating abnormally.  The step failed.

I get that the .bak file is already there, but I thought that NOINIT forced an append which would add the transaction log data into the existing .bak file.
I'm probably missing something simple but can somebody please advise?


texts: Function accepts only certain passwords in postgreSQL

texts:
I have created a function in a postgreSQL(v10.10) database to create new users or update existing ones.
As parameters you pass the username, password, role memberships, first name and last name. In my case the function is called by an external program with connection to this database. Basically it works, but strangely only passwords are taken over which contain only lower case letters and do not start with a number. As soon as the password contains uppercase letters, special characters or the password begins with a number, the user is created, but the login data is supposed to be wrong when trying to connect afterwards.
I have already written the password in that function into a logging table. There the password is also identical with the given password parameter(e.g. uppercase, numbers, etc.), but a login still does not work.
Thats my function I am using:
DECLARE
v_role TEXT;
v_timestamp TIMESTAMP;
v_status TEXT;

BEGIN
    v_timestamp := NOW();
    --check ob Nutzer bereits angelegt, dann create oder alter
    IF NOT EXISTS (
      SELECT FROM pg_catalog.pg_roles
      WHERE  rolname = v_username)
      THEN --Neuanlage
        --lege Nutzer an
        EXECUTE FORMAT('CREATE USER %I WITH PASSWORD ''%I''', v_username, v_password);
        EXECUTE FORMAT(E'COMMENT ON ROLE %I IS ''%s %s \nAngelegt am:\n%s''', v_username, v_vorname, v_nachname, to_char(v_timestamp, 'DD.MM.YYYY HH24:MI:SS'));
        v_status := 'neuanlage';

      ELSE --Änderung
      --ändere bestehenden Nutzer
        EXECUTE FORMAT('ALTER USER %I WITH PASSWORD ''%I''', v_username, v_password);
        EXECUTE FORMAT(E'COMMENT ON ROLE %I IS ''%s %s \nUpdate am:\n%s''', v_username, v_vorname, v_nachname, to_char(v_timestamp, 'DD.MM.YYYY HH24:MI:SS'));
        --entferne Mitgliedschaft aus bestehenden Gruppen
        FOR v_role IN
        SELECT rolname FROM pg_roles WHERE pg_has_role( v_username, oid, 'member')
        LOOP
            IF v_role != v_username THEN
                EXECUTE(FORMAT('revoke %I from %I', v_role, v_username));
            END IF;
        END LOOP;
        v_status := 'update';
    END IF;
    --füge zu Gruppen hinzu
    FOREACH v_role IN ARRAY v_roles LOOP
        EXECUTE FORMAT('GRANT %I TO %I', v_role, v_username);
    END loop;

    --Protokollierung
    INSERT INTO verwaltung.roles_protokoll (roles, status, timestamp, username, password) VALUES (v_roles, v_status, v_timestamp, v_username, v_password);

    RETURN 1;
    -- Simple Exception
EXCEPTION
    WHEN others THEN
        RETURN 0;
END;

Do you have any ideas what's going wrong in there?


texts: How can I create a stored procedure of inheritance tables in SQL Server

texts:
I have three tables

create table CustomerTypes(
CustomerTypeID int primary key identity(1,1),
CustomerType varchar(11)
);

insert into CustomerTypes(CustomerTypeID,CustomerType)
select 1, 'Regular' union all
select 2, 'Business'

create table Customers(
ContactID int primary key identity(1,1),
CustomerTypeID int references CustomerTypes(CustomerTypeID),
FirstName varchar(25)not null,
LastName varchar(25)not null,
DOB date not null,
Telephone varchar(18),
Notes varchar(250),
AddDate date not null
constraint cust_AltPK unique (ContactID,CustomerTypeID)
);

create table Regular_Customer(
ContactID int primary key identity(1,1),
CustomerTypeID as 1 persisted, --regular
Specification varchar(45),
Date_Joined date,
foreign key (ContactID,CustomerTypeID) references Customers(ContactID,CustomerTypeID)
);

create table Business_Customer(
ContactID int primary key identity(1,1),
CustomerTypeID as 2 persisted, --Business
Business_Name varchar(30)not null,
Business_Type varchar(30),
foreign key (ContactID,CustomerTypeID) references Customers(ContactID,CustomerTypeID)
);

I want to create a stored procedure called CustRegistration to insert data into the various customer tables, by combining customerType table and Customer table.


texts: we have a DWH load job that sometimes hangs.  We have not been able to determine why this is happening it seems to be random and network/hardware related (which we have no control over).  We are ordering new hardware which will isolate our processes but this is weeks away.  Therefore when our job hangs, we would like to specify a timeout option if any of the steps reaches a certain amount of time and have it automatically restarted.  How can this be done?  Thanks!


texts: Downgrading from SQL Server 2008 to 2005

texts: Database files that are built using SQL 2008 are not compatable with 2005. Is there a work around?


texts: ETA: translate Oracle SQL queries to MySQL

texts:
task: replace Oracle 11g with MySQL

current setup:

Oracle 11g SE One
about 80 tables
stored procedures, PL/SQL are in place but out of scope for this question
client applications do not use a generic access technique like JDBC but OCI libs
applications have roughly 500 fixed SQL queries for Oracle

Management now wants to have outlined a migration draft. Don't bother about the schema or data migration.

I'm trying to figure out a rough estimate of time to spend for translating 500 queries. Naturally these queries range from very simple INSERTs/SELECTs to rather complex ones with subqueries, multiple JOINs and expressions like that:

TRUNC(EXTRACT(DAY FROM (afs.next - TO_TIMESTAMP_TZ('1970-01-01 00:00:00 UTC',
 'YYYY-MM-DD HH24:MI:SS TZR'))) * 86400 + EXTRACT(HOUR FROM (afs.next -
TO_TIMESTAMP_TZ('1970-01-01 00:00:00 UTC', 'YYYY-MM-DD HH24:MI:SS TZR')))
* 3600 + EXTRACT(MINUTE FROM (afs.next - TO_TIMESTAMP_TZ('1970-01-01 00:00:00 UTC',
 'YYYY-MM-DD HH24:MI:SS TZR'))) * 60 + EXTRACT(SECOND FROM (afs.next -
TO_TIMESTAMP_TZ('1970-01-01 00:00:00 UTC', 'YYYY-MM-DD HH24:MI:SS TZR')))) as \"next\"

Hint: sense and purpose of such expressions has to be read out of the existing queries because at creation time no one bothered about documenting details of the implementation.

I'm completely aware that there may be queries that cannot be translated as is because MySQL perhaps does not support some things.

Question: can you give a rough hint about the expected "throughput" you think an expert could be capable of? The expert will be a person skilled with MySQL, Oracle and the applications. What do you think how much time is required to "translate" a single average query and simply manually test it in the target database?

Please ignore the required testing of the whole application. Don't regard that we have to setup the target schema beforehand. Hints to and stories of failed migrations and disastrous problems are welcome.

Thanks!


texts: What should I set my table_definition_cache to?

texts:
I just discovered table_definition_cache and I am trying to decide what to set it to. I am messing with my config because of performance issues.

On one server I have 36599 tables and when I run SHOW GLOBAL STATUS the value for Opened tables is 930312. table_definition_cache is set to 20k.

On another server I have 45349 tables and when I run SHOW GLOBAL STATUS the value for Opened tables is 94383. table_definition_cache is set to 40k.

I am not sure what to set it to table_definition_cache value to because it seems like the server doing a lot of opening / swapping.

Both servers are CentOS 6 and running

Server version: 5.6.32-78.0 Percona Server (GPL), Release 78.0, Revision 8a8e016

Thank you for looking! I appreciate your feedback greatly.


texts: ms sql query multiple table colums as identical tag

texts:
I am using SQL Server Express 2017

My Table looks like this

+-------------+
| ID | Name   |
+-------------+
| 1  | Bert   |
| 2  | Agnes  |
| 3  | Diane  |
+-------------+

I'm looking for this kind of query result:

<MyRootElement>
  <MyTag ID='1' Name='Bert' />
  <MyTag ID='2' Name='Agnes' />
  <MyTag ID='3' Name='Diane' />
</MyRootElement

Can anyone help me, please?

Thanks in advance!


texts:
We are planning to use PostgreSQL in our application but are concerned about crash-safety and recovery.

I can't find any database crash recovery method or process in PostgreSQL. I know there must be something in PostgreSQL.

Advice? So if anyone can help me out to find  this then that would be great.


texts:
I am attempting to add schemabinding to a view in SQL Server 2008, but I am getting an error of

  Syntax 'Openrowset/Openquery/Opendatasource' is not allowed in schema-bound objects.

I am using Openquery because I am querying a linked server and this is the way I must query it.  Is there a workaround for this?

This was the syntax I tried with my view

Create View dbo.Test
With Schemabinding
As

  Select * FROM OPENQUERY(191.183.18.101, 'Select * from ls1prod.hrinfo')

Go


texts: Unique filtered index and surprising estimation

texts:
When I view the estimated execution plan for this query...

SELECT *
FROM tabela2  x
     LEFT JOIN dbo.tabela1 t with (nolock) on t.Kod = x.Kod

... the Estimated Number of Rows property for [table1] shows 10. Why is this? In the schema (copied below) I have a unique index AKF_tabela1_Kod. Shouldn't this unique index force the row estimation to be 1?

Estimated Execution Plan

Create Database, Tables and Index

-- create database
USE [master]
GO
DROP DATABASE IF EXISTS my_test;
CREATE DATABASE my_test;
GO
ALTER DATABASE my_test SET AUTO_CREATE_STATISTICS OFF;
ALTER AUTHORIZATION ON DATABASE ::my_test TO sa;
USE my_test
GO

-- create dbo.tabela1

CREATE TABLE dbo.[tabela1]
    (
    [Id] int NOT NULL identity(1,1),
    [kol1] varchar(20) NOT NULL,
    [Kod] int NULL,
    CONSTRAINT [PK_tabela1] PRIMARY KEY NONCLUSTERED([id] ASC) WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
    CONSTRAINT [BK_tabela1] UNIQUE CLUSTERED([Kol1] ASC) WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
    );
GO

-- create Index

CREATE UNIQUE NONCLUSTERED INDEX [AKF_tabela1_Kod]
    ON dbo.[tabela1]([Kod] ASC)
    INCLUDE ([id],[kol1])
    WHERE ([Kod] IS NOT NULL)
    WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, MAXDOP = 0);
GO

-- create dbo.tabela2

IF  OBJECT_ID('dbo.tabela2') IS NULL
  CREATE TABLE tabela2 (Kod int NOT NULL)
GO

Insert Data into Table

-- create data in tabela1
declare @i int=1
    while @i<=100
    begin
      INSERT tabela1 (kol1, kod)
      values (@i, cast(@i+100 as varchar))
      set @i += 1;
    end;
GO

UPDATE STATISTICS tabela1 WITH FULLSCAN;

Here the Execution plan


texts: Moving Database SQL Server 2012 to 2008/R2

texts: I'm planning to move a Large Database from SQL Server 2012 to 2008/R2. I tried the scripting thing, it works on a small-medium type of database but not on Large database w/c is about a hundred MB. Any tips on how can I make this? Or Is it recommended to do this for a Large Databases?


texts: What is the fastest method to migrate large tables from Oracle to SQL Server?

texts:
We have a table with 169 million rows in a Oracle 10g database, we need to move this table to a MS SQL Server 2012 database.

Describe on the table:

aaa NOT NULL NUMBER(38)
bbb NOT NULL NUMBER(38)
ccc NOT NULL DATE
ddd NOT NULL VARCHAR2(8)
eee NOT NULL DATE
fff NOT NULL NUMBER(3)
ggg NUMBER(38)
hhh DATE
iii VARCHAR2(255)
jjj VARCHAR2(255)
kkk VARCHAR2(4000)

We have tried SSMA (SQL Server Migration Assistant) - takes over 300 hours.
We have tried SSIS (SQL Server Integration Services)- takes over 52 hours.
These options are not practical

I was wondering if 'bcp' is a good option, if yes, what is the syntax for such a data pull? Can we directly pull the data from the Oracle database?


texts: How to "self audit" all of the SQL Server licenses at an organization?

texts:
So I have started a new job with a nice non-profit organization, which has been around for a few decades. They have a wide variety of SQL Server editions and versions (ex: 2005 Express to 2016 SP2 Enterprise) and across many Windows VMs (versions anywhere from 2003 to 2016).

The previous DBAs did not complete any documentation around the number of licenses the company owns, nor editions, versions. Effectively no useful records.

I have run the MAP toolkit to gather details on the SQL Servers. So at least I've got a starting point for what servers exist, and I've dug into which are still serving up production data.

I have also used the new ApexSQL Discover tool with mixed results (sorry guys I love your stuff but I can't wait 3 days for this tool to run when I need to add 1 server). And also dbatools find-dbainstance (not a huge fan of using PS to export large amounts of data). So by piecing this all together, I'm fairly certain at this point that I've discovered most or all of the instances that ever existed on this domain.

Of course, I've queried the licensing in a number of ways and thoroughly examined the completely useless extremely helpful MS SQL licensing guide of which nothing seems to provide any information on the licenses that the organization owns or how to acquire it.

The crux of the issue: Some of these licenses were purchased a decade (or two) ago. Some were surely paper invoices. Remember I mentioned this is a non-profit? On the plus side, licensing is cheap. On the other hand, licensing is cheap, so most or all of the licenses do not have SA because it's cheaper to buy new ones. edit: This was the reasoning at the time for not purchasing SA, I was not involved.

Should I just assume that since most likely none have SA--that the server is also likely to be licensed properly--and just buy new ones as needed? That seems like a horrible approach.

My question:

How the heck do I know what SQL Server licenses my new company owns without trying to dig up paper invoices from 2004? I've never been in this situation before. Is my only option to get audited or pay for a partner to audit? (and if so, Microsoft what is you doin' bb??).

Any insights that dba.se can provide here will probably add years to my life. Thanks all!


texts: MySQL query that works but extremely slow

texts:
I have a developer who has created a query joining 3 tables in a method that I have never seen before.  This query is working although extremely slow.  I am trying to understand how this query is functioning because it seems to be extremely poorly designed.  I have tried to create a working sample that will hopefully clearly show the issue.

SELECT A.Col1, C.Col3
    FROM TABLEA A
        INNER JOIN TABLEB B
            ON A.... = B....
        LEFT JOIN TABLEC C
            ON A.... = C....  AND A.... = C....
                AND B... = C.... AND B... = C.... AND B... = C... AND B... = C....

Notice how on the LEFT JOIN they have pulled in conditions from both TABLE A and TABLE B to join to C.  Is this creating some sort of derived or temporary table in the background?


texts: Build a select query that includes aggregated values from a different table


texts: SSIS deployment on databases part of AAG

texts:
Experts, I have two databases, one is SSISDB, other is user database. Both of them are a part of Always on availability group. I need to give one user a permission to be able to deploy a package. How should I go about doing this. He already has a login and has a data reader access to both the databases. We are talking about SQL server 2016 EE here.
Thanks.


texts: Parsing and binding variables for SELECT .. WHERE column IN ( .. ) queries

texts:
I'm looking to make sure that all queries in a php web application have proper use of bind variables to minimize parsing of the queries.

I'm wondering how Oracle parses queries that compares a column to a list of values. Will Oracle consider these statements to be the same, or must the list be inside bound variables?

select char from alphabet where char not in ('a', 'b');

select char from alphabet where char not in ('c', 'd');

If the contents of the list must be in a bind variable, can it be done with a single variable, or must one put each item on the list in a separate variable?

select char from alphabet where char not in (:list);

select char from alphabet where char not in (:c1, :c2);

If the latter is true, will queries with a different number of items in the list still be considered to have the same structure?

select char from alphabet where char not in (:c1, :c2);

select char from alphabet where char not in (:c1, :c2, :c3);


texts: Microsoft SQL Server says my login is disabled, even in single user/DAC mode

texts:
I'm locked out of a local database, and the single-user mode option that has worked for me in the past isn't working for me here. I could use some options to try.

It's saying the account is disabled, which I didn't think was even possible for single-user mode.

What I've tried already:

Restarting in single-user mode:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBNAME\MSSQL\Binn>net stop MSSQL$DBNAME
The SQL Server (DBNAME) service is stopping..
The SQL Server (DBNAME) service was stopped successfully.

C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBNAME\MSSQL\Binn>.\sqlservr -sDBNAME -m -c

[seems to come up]

The login attempt looks like this:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBNAME\MSSQL\Binn>sqlcmd -S .\DBNAME
Msg 18470, Level 14, State 1, Server MY-PC\DBNAME, Line 1
Login failed for user 'My-PC\sarahg'. Reason: The account is disabled.

Server output:

2012-05-29 15:55:25.11 Logon       Error: 18470, Severity: 14, State: 1.
2012-05-29 15:55:25.11 Logon       Login failed for user 'My-PC\sarahg'. Reason: The account is disabled. [CLIENT: <local machine>]

Restarting in DAC mode:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBNAME\MSSQL\Binn>.\sqlservr -sDBNAME -m -c -t 7806
... blah blah ...
2012-05-29 15:59:05.59 spid6s      Server name is 'MY-PC\DBNAME'. This is an informational message only. No user action is required.
2012-05-29 15:59:05.60 spid9s      Starting up database 'model'.
2012-05-29 15:59:05.60 spid6s      Informational: No full-text supported languages found.
2012-05-29 15:59:05.60 spid6s      Starting up database 'msdb'.
2012-05-29 15:59:05.72 Server      A self-generated certificate was successfully loaded for encryption.
2012-05-29 15:59:05.72 Server      Server is listening on [ 'any' <ipv6> 49201].
2012-05-29 15:59:05.74 Server      Server is listening on [ 'any' <ipv4> 49201].
2012-05-29 15:59:05.74 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\DBNAME ].
2012-05-29 15:59:05.74 Server      Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$DBNAME\sql\query ].

The login attempt looks like this:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBNAME\MSSQL\Binn>sqlcmd -A -S .\DBNAME
Msg 18470, Level 14, State 1, Server MY-PC\DBNAME, Line 1
Login failed for user 'Mys-PC\sarahg'. Reason: The account is disabled.

With server log:

2012-05-29 16:00:24.86 Logon       Error: 18470, Severity: 14, State: 1.
2012-05-29 16:00:24.86 Logon       Login failed for user 'Mys-PC\sarahg'. Reason: The account is disabled. [CLIENT: 127.0.0.1]

Any help is very welcome,

-- Sarah


texts: Mysql Master Slave replication performance is too low

texts:
I have implemented Master Slave Replication.

The slave can't keep up with progress.

How can I find out the problem and optimize the config?

  OS: CentOS 6.8 Mysql 5.6.35 Master => Slave 3-5 Mbps

           Slave_IO_State: Waiting for master to send event
              Master_Host: **.**.**.**
              Master_User: ********
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: binlog.000095
      Read_Master_Log_Pos: 297639485
           Relay_Log_File: mysqld-relay-bin.000050
            Relay_Log_Pos: 644337677
    Relay_Master_Log_File: binlog.000083
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
          Replicate_Do_DB:
      Replicate_Ignore_DB:
       Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:
               Last_Errno: 0
               Last_Error:
             Skip_Counter: 0
      Exec_Master_Log_Pos: 644337473
          Relay_Log_Space: 13252726053
          Until_Condition: None
           Until_Log_File:
            Until_Log_Pos: 0
       Master_SSL_Allowed: No
       Master_SSL_CA_File:
       Master_SSL_CA_Path:
          Master_SSL_Cert:
        Master_SSL_Cipher:
           Master_SSL_Key:
    Seconds_Behind_Master: 119951 Master_SSL_Verify_Server_Cert: No
            Last_IO_Errno: 0
            Last_IO_Error:
           Last_SQL_Errno: 0
           Last_SQL_Error:   Replicate_Ignore_Server_Ids:
         Master_Server_Id: 1
              Master_UUID: bada755f-d361-11e6-b53e-d4ae52b21cd5
         Master_Info_File: mysql.slave_master_info
                SQL_Delay: 0
      SQL_Remaining_Delay: NULL
  Slave_SQL_Running_State: System lock
       Master_Retry_Count: 86400
              Master_Bind:
  Last_IO_Error_Timestamp:
 Last_SQL_Error_Timestamp:
           Master_SSL_Crl:
       Master_SSL_Crlpath:
       Retrieved_Gtid_Set: bada755f-d361-11e6-b53e-d4ae52b21cd5:281172-14941959
        Executed_Gtid_Set: bada755f-d361-11e6-b53e-d4ae52b21cd5:1-11696260
            Auto_Position: 1


texts: Encrypt/Decrypt output & input for SSIS task

texts:
I use SSIS packages to output the data from one system as Comma Separated Values, and then import that same data into a second system.  I have a new requirement to encrypt the CSV text files that are generated by the SSIS package, which means I will also need to decrypt those text files when importing into the other system.

This encrypting/decrypting could be done using some sort of batch processing outside of SSIS, but it seems like it would be more natural to do this all within SSIS.  What's the best way to do this?


texts:
Is there a way to move an MDF and LDF file from C drive to D drive? I've tried to run

ALTER DATABASE myDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE myDb SET OFFLINE

ALTER DATABASE myDb MODIFY FILE ( Name = myDb, Filename = 'D:\SQL Data Files\myDb.MDF')

ALTER DATABASE myDb SET ONLINE

ALTER DATABASE myDb SET MULTI_USER

but I have to manually move files from C drive to D:\SQL Data Files which is ok but is there a way to move files by SQL query itself, I'm not trying to move any System Databases just the databases that I have on my server.


texts:
On an Oracle database, I need to calculate something like this - on column my

id yr    m    v1  v2 v3 st my
1  2015  10   1   0  0  0  0
2  2015  10   0   0  2  1  4
3  2015  10   2   0  0  1  0
4  2015  10   0   1  0  1  0

When st=1, calculate 10 (total from m row) minus total in rows v1+v2+v3 (6) = 4. So, in the second row I will put 4 and in the third row, the total of 10 is reached and I will put 0. The same in the 4th row. So I need to calculate a sum on rows until the current row. Can I do this?

The sum on v1+v2+v3 is something like this:

(sum(v1+v2+v3) over (partition by yr))

How can I find out the sum for the row I calculate my so I won't get over my maximum sum from m?


texts: What does "thread" mean in "thread checkpoints" context?

texts:
The following excerpt is from Oracle concepts. Maybe this is a stupid question, but I really need your help to understand it.

My question is:

What's does the thread mean in the context thread checkpoints?
Does it means redo log thread(all redo log files that belong to a specific instance)?
What does a certain target means?

Thanks all! English is not my mother tongue, and I feel a little difficult to fully understand this.

When Oracle Database Initiates Checkpoints

The checkpoint process (CKPT) is responsible for writing checkpoints to the data file
headers and control file. Checkpoints occur in a variety of situations. For example,
Oracle Database uses the following types of checkpoints:

■   Thread checkpoints
    ^^^^^^
    The database writes to disk all buffers modified by redo in a specific thread before
                                                             ^^^^^^^^^^^^^^^^^^^^
    a certain target. The set of thread checkpoints on all instances in a database is
    ^^^^^^^^^^^^^^^^^
    a database checkpoint. Thread checkpoints occur in the following situations:

    – Consistent database shutdown

    – ALTER SYSTEM CHECKPOINT statement

    – Online redo log switch

    – ALTER DATABASE BEGIN BACKUP statement

■   Tablespace and data file checkpoints

    The database writes to disk all buffers modified by redo before a specific target. A
    tablespace checkpoint is a set of data file checkpoints, one for each data file in the
    tablespace. These checkpoints occur in a variety of situations, including making a
    tablespace read-only or taking it offline normal, shrinking a data file, or executing
    ALTER TABLESPACE BEGIN BACKUP.

■   Incremental checkpoints

    An incremental checkpoint is a type of thread checkpoint partly intended to avoid
    writing large numbers of blocks at online redo log switches. DBWn checks at least
    every three seconds to determine whether it has work to do. When DBWn writes
    dirty buffers, it advances the checkpoint position, causing CKPT to write the
    checkpoint position to the control file, but not to the data file headers.

Other types of checkpoints include instance and media recovery checkpoints and
checkpoints when schema objects are dropped or truncated.


texts: SMO Copy database or remove all non-system database objects

texts:
I have c# SMO script which copy all database objects to existed empty database or to a new database.

But this approach has two problems

Existed database is not empty, manual intervention is needed.
New database will have different options like DB Owner, disk
management etc.

Because I need to Clone existed database from one instance to another and I am able to copy only database objects right now, therefore my question is

it is possible to create a new database according to settings of
existed one ?
it is possible to drop all objects, regardless on dependencies, from existed database ?

my script look like

Transfer transfer = new Transfer(databaseSource);
transfer.CopyAllObjects = true;
transfer.CopyAllUsers = true;
transfer.Options.WithDependencies = true;
transfer.DestinationServer = serverDestination.Name;
transfer.DestinationDatabase = databaseDestination.Name;
transfer.DestinationLoginSecure = true;
transfer.CopySchema = true;
transfer.CopyData = true;
transfer.Options.Permissions = true;
transfer.Options.ContinueScriptingOnError = true;
transfer.TransferData();


texts: What's the point of SQL joins if you have OLAP?

texts:
I want to understand the purpose of using SQL joins from a business analysis perspective.

Let's say I want to analyze customer data. Why would I want to pull data from multiple tables using join statements if I can use a cube or tabular model, which seem omnipresent, even excel has this functionality.  I can use point and click adhoc, slicers and other filtering and aggregation which if I understand correctly are a lot faster and if I want to do a very complex analys, I can write MDX queries or cubeformulas, in the excel example.

What can SQL joins can help me with?


texts: What happens if we put skip-grant-tables, skip-networking these two options in my.cnf file and restarted the server in production hours, will there be any loss of data. I removed them immediately and resarted the server and brought it to normal. Can anyone explain the consequences please?


texts: Is possible to reuse a already calculatesd column into a query in MySQL?

texts:
I have a query with complicated column, and I would like to use this column result in another column, for instance:

SELECT ( /* Complex query */ ) as myValue, if( myValue > 10, "OK" , "" ) from table;

but his query returns an error:

Error Code: 1054. Unknown column 'myValue' in 'field list

How can I reuse a already calculated field?


texts: SELECT i.c.w. a COUNT and INNER JOIN gives different results with string literals and numeric values when they are used in a WHERE clause

texts:
I have a MySQL query:

SELECT
    p.informationProvider as pGLN,
    p.productID,
    p.productStatus
FROM tblproducts AS p
WHERE
     -- informationProvider is of type varchar(14)
    p.informationProvider = '8718885110005'

Result of that is:

pGLN            productID productStatus
8718885110005   497905    closed
8718885110005   497906    closed
8718885110005   497907    closed
8718885110005   497908    closed
8718885110005   505972    closed
8718885110005   505973    closed
8718885110005   508261    received
8718885110005   508262    received
8718885110005   508263    received
8718885110005   513591    received
8718885110005   517529    received
8718885110005   537381    received

Now I have another query:

SELECT
    c.informationProvider as cGLN,
    c.companyName
FROM tblcompanies as c
WHERE
    -- informationProvider is of type varchar(14)
    c.informationProvider = 8718885110005

The Result of that is:

cGLN            companyName
8718885110005   Oliva BV

Now I'll combine those two queries.

SELECT
    c.informationProvider as cGLN,
    p.informationProvider as pGLN,
    COUNT(p.productID) AS count,
    p.productID,
    p.productStatus
FROM tblproducts AS p
INNER JOIN tblcompanies AS c ON (c.informationProvider = p.informationProvider)
WHERE       p.productStatus IN ('requested' , 'expected', 'open')  -- closed and received aren't in the list
AND c.informationProvider = '8718885110005'

The result:

cGLN           pGLN count productID productStatus
8718885110005  NULL  0     NULL      NULL

-------- the problem ------

Ok, now I'll change a little.
I'll change AND c.informationProvider = '8718885110005' to AND c.informationProvider = 8718885110005. Thus, the string will be presented as an integer.

The result of that is:

cGLN           pGLN count productID productStatus
NULL           NULL  0     NULL      NULL

This surprised me, why does the cGLN cell have a NULL?

My first idea was to check if the string value is equal to the integer. To my surprise they were.

SELECT 8718885110005 = '8718885110005'; -- this is 1

This surprised me even more.

My question:
Why is the cGLN NULL?


texts:
Does the Oracle database username exist for each Linux account?

The former employee left the company and can not find the details at this time.

I can log in to the Linux root account and run a DB query through sqlplus.
If I log in as the Linux oracle account, I can not log in with the username that was logged in as the root account.

I do not know which setting I made.

I have a few questions,

As the title suggests, does the Oracle database username exist for every Linux account?
I'm wondering if there is a database username that can only be logged in to the Linux root account.
I would like you to give me a general idea of what configuration should be changed to run as the root account in the Linux oracle account.


texts: MySQL replication working, but data is out of date

texts:
I'm trying to figure out why even though SHOW SLAVE STATUS isn't reporting any errors, nothing is replicating (see below):

both nodes are running:

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.1.73-log |
+------------+
1 row in set (0.00 sec)

mysql>

on

# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.5 (Santiago)
#

master:

mysql> SHOW MASTER STATUS\G;
*************************** 1. row ***************************
            File: mysql-bin.000107
        Position: 228679653
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

slave:

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: MASTER_HOST
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000107
          Read_Master_Log_Pos: 228681259
               Relay_Log_File: mysql:PR_DB-relay-bin.000150
                Relay_Log_Pos: 228398599
        Relay_Master_Log_File: mysql-bin.000107
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: DB1,DB2,DB3,DB4
          Replicate_Ignore_DB: information_schema,mysql
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 228681259
              Relay_Log_Space: 228681667
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

master:

mysql> SELECT COUNT(*) FROM DB3.users;
+----------+
| COUNT(*) |
+----------+
|    97786 |
+----------+
1 row in set (0.03 sec)

mysql>

slave:

mysql> SELECT COUNT(*) FROM DB3.users;
+----------+
| COUNT(*) |
+----------+
|    95678 |
+----------+
1 row in set (0.01 sec)

mysql>

at the initial time of setting up replication, SELECT COUNT(*) returned same values.


texts: Update postgres row where condition is from another table

texts:
I want to update a password field from a given value that exists from another table.

user_info:

--------------------------------------------
| user_login | password                    |
|------------|-----------------------------|
| ad         | $2a$12$oGBdoD....ia9mk25OHu |
--------------------------------------------

user_contacts

-------------------------
| user_login | email    |
|------------|----------|
| ad         | ad@...com|
-------------------------

I would like to change the password by identifying the user_login using the associated email address. So, something like:

UPDATE user_info SET password = '$2a$11$fwea...IEI' WHERE user_contacts.email = 'ad@...com';

Would anyone have an idea how to go about this? I've been trying to search for an answer online, but I've either not been able to find anything relating to this.

Cheers!


texts: Referential integrity based on data more than a table away

texts:
Notes on tables A, B, C, D, E

B.aid and C.aid reference A.aid.
D.cid references C.cid and D.did is the primary key.  D is a C.
cid and did are pre-assigned and I cannot change them (they need to remain separate despite the is-a).
E has information that depends on its primary key (bid, did) with bid referencing B.bid and did referencing D.did.

Is there a way to make sure that the bid and did of a given row in E reference rows in B and D that reference the same row in A (through C in the case of D)?

So far I've come up with

CREATE TABLE a (
    aid INTEGER PRIMARY KEY
);

CREATE TABLE b (
    bid INTEGER PRIMARY KEY,
    aid INTEGER NOT NULL REFERENCES a ( aid ),
    UNIQUE ( bid, aid )
);

CREATE TABLE c (
    cid INTEGER PRIMARY KEY,
    aid INTEGER NOT NULL REFERENCES a ( aid ),
    UNIQUE ( cid, aid )
);

CREATE TABLE d (
    did INTEGER PRIMARY KEY,
    cid INTEGER NOT NULL REFERENCES c ( cid ),
    UNIQUE ( did, cid )
);

CREATE TABLE e (
    bid INTEGER NOT NULL REFERENCES b ( bid ),
    cid INTEGER NOT NULL REFERENCES c ( cid ),
    did INTEGER NOT NULL REFERENCES d ( did ),
    dcid INTEGER NOT NULL,
    baid INTEGER NOT NULL,
    caid INTEGER NOT NULL,
    PRIMARY KEY ( bid, did ),
    FOREIGN KEY ( bid, baid ) REFERENCES b ( bid, aid ),
    FOREIGN KEY ( cid, caid ) REFERENCES c ( cid, aid ),
    FOREIGN KEY ( did, dcid ) REFERENCES d ( did, cid ),
    CHECK ( baid = caid AND cid = dcid )
);

Is there a better way to create E that doesn't seem so complicated and redundant?


texts: MySQL Workbench: I need to update values in a table based on values from a csv

texts:
Newbie Problem, but I've tried creating a new table and importing the csv to that then updating, but I have been having trouble with that, any guidance would be greatly appreciated.

I have a csv file that contains about 1500 entries, with an "ID" column and an "Unit" column. I am trying to figure out the best way to update, in my mySQL workbench table, the "unit" of only the IDs in my csv file.

CSV File

id_inv  unit
1,      aaa
2,      aaa
3,      aaa
5,      aaa
10,     aaa

DB Table

id_inv       unit     ... other columns
1            bbb
2            bbb
3            bbb
4            bbb
5            bbb
6            bbb
7            bbb
8            bbb
9            bbb
10           bbb


texts: postgresql 9.3 with Ubuntu 14.04.1 LTS throw 's ERROR: invalid page in block 105 of relation base/16385/17407

texts:
This is Ubuntu 14.04.1 LTS with postgresql 9.3 in production, Now i got this below error's in log.

# tail -f /var/log/postgresql/postgresql-9.3-main.log
2015-01-08 14:10:16 IST CONTEXT:  automatic vacuum of table "lms_production.pg_toast.pg_toast_17005"
2015-01-08 14:10:29 IST LOG:  incomplete startup packet
2015-01-08 14:11:10 IST ERROR:  invalid page in block 105 of relation base/16385/17407
2015-01-08 14:11:10 IST STATEMENT:  select count(*) from report_snapshots;
2015-01-08 14:11:16 IST ERROR:  invalid page in block 632 of relation base/16385/17010
2015-01-08 14:11:16 IST CONTEXT:  automatic vacuum of table "lms_production.pg_toast.pg_toast_17005"
2015-01-08 14:11:29 IST LOG:  incomplete startup packet
2015-01-08 14:12:16 IST ERROR:  invalid page in block 632 of relation base/16385/17010
2015-01-08 14:12:16 IST CONTEXT:  automatic vacuum of table "lms_production.pg_toast.pg_toast_17005"
2015-01-08 14:12:29 IST LOG:  incomplete startup packet
2015-01-08 14:13:16 IST ERROR:  invalid page in block 632 of relation base/16385/17010
2015-01-08 14:13:16 IST CONTEXT:  automatic vacuum of table "lms_production.pg_toast.pg_toast_17005"
2015-01-08 14:13:29 IST LOG:  incomplete startup packet

While checking for error reports in postgres it shows the count fine.

# select count(*) from error_reports;

 count
-------
  5345
(1 row)

While checking for report_snapshots it's throwing the above error.

# select count(*) from report_snapshots;

ERROR:  invalid page in block 105 of relation base/16385/17407

While googling found that someone says it's disk problem but here we use dedicated server and this database running machine is one of the virtual machine for production. Disk have enough space and nothing issue with disk, Other virtual machine running in host are working without any issue's. Any one there to fix this issue ?


texts:
I'm trying to use the AES_ENCRYPT and AES_DECRYPT functions. I've found an answer to my initial question which was how to decrypt the encrypted string, https://stackoverflow.com/questions/16556375/how-to-use-aes-encrypt-and-aes-decrypt-in-mysql. I'm not sure why the char(50) is needed though, nor what to do since my string can be longer than 50 characters. Should I use char(65535)?

This returns the encrypted string as expected:

SELECT 'test' as original,
AES_ENCRYPT('test', UNHEX(SHA2('This Is Cool',512))) as encrypted,
CAST(AES_DECRYPT(AES_ENCRYPT('test', UNHEX(SHA2('This Is Cool',512))), UNHEX(SHA2('This Is Cool',512))) as char(50)) as decrypted

this does not:

SELECT 'test' as original,
AES_ENCRYPT('test', UNHEX(SHA2('This Is Cool',512))) as encrypted,
AES_DECRYPT(AES_ENCRYPT('test', UNHEX(SHA2('This Is Cool',512))), UNHEX(SHA2('This Is Cool',512))) as decrypted


texts: Any correlation between tempdb and sysmail?

texts: Is there any correlation between [tempdb] getting full and subsequently  db_mail disabled ? [This needs sysmail_start_sp to be executed in msdb]


texts:
I am facing the following problem:

I want to have a Express (=free) version of MS SQL Server and there I want to be able to see the SQL Management Studio interface. I have found only installations where there is the SQL Server Configuration Manager which has more limited list of features and of course I want to use a feature that does not exist in it.

So as the title says:

  Is it possible to install a MS SQL Express Studio, where the studio tool also is included?


texts:
I'm using MySQL's boolean full-text search for my search engine: https://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html.

Assume I have a table with the following columns:

PRODUCTS
--------
id (PK)
color_id (FK to COLORS table)
name
description

When given a search term, I want to search the product's table's name and  description columns, as well as the product's color name. The color's name is in a separate table.

Should I create a new column in the product's table and put all the text I want indexed in there? For example, I add a products.full_text_index column, and dump a concatenation of name, description, and the color's name in that column? Then do full-text search against that column?

UPDATE: Or, instead of a column in an existing table, maybe create a new table to contain all the text that should be used for the full-text search. That table can just have a FK to the products table.


texts: update statements shows update count but not updating the table

texts:
I am facing a strange issue which I have never faced before. I have a update query like below

Update A
Set A.Col10 = E.Col10, A.Col11 = E.Col11
FROM TableA A
INNER JOIN TableB B on A.Col1 = B.Col1
INNER JOIN TableC C on C.Col1 = B.Col2
INNER JOIN TableD D on D.Col1 = C.Col2
INNER JOIN TableE E on E.Col1 = D.Col2
WHERE D.Col3 BETWEEN @StartDate AND @ENDDate
AND E.Col10 <> A.Col10

When i am running above statement it is giving message that 810 rows are updated. but when I am selecting based on the same criteria I am still getting those 810 rows.

SELECT A.Col10 , E.Col10
FROM TableA A
INNER JOIN TableB B on A.Col1 = B.Col1
INNER JOIN TableC C on C.Col1 = B.Col2
INNER JOIN TableD D on D.Col1 = C.Col2
INNER JOIN TableE E on E.Col1 = D.Col2
WHERE D.Col3 BETWEEN @StartDate AND @ENDDate
AND E.Col10 <> A.Col10

no matter how many times I update the table it is still showing the same count. now, my question is where I am going wrong and what are those records which got updated when actually no records are updated.


texts:
I want to use ORDER BY clause but still have one specific entry on the top.
I thought this might do it but Vertica is ignoring the ORDER BY clause on the second query:

(SELECT country_id, country_name
FROM country_dim
WHERE country_dim.iso_country_code LIKE 'US')
UNION
(SELECT country_id, country_name
FROM country_dim
WHERE country_dim.iso_country_code NOT LIKE 'US'
ORDER BY country_name ASC)

Resulting in

12  United States of America
10  Germany
5   Brazil
6   Canada
7   China
8   France
4   Algeria
3   Aland Islands
2   Albania
8   Denmark
11  United Arab Emirates
13  Central African Republic
-1  Unknown
14  Svalbard and Jan Mayen

DDL is

CREATE TABLE country_dim
(
    country_id int NOT NULL,
    iso_country_code char(2) NOT NULL,
    country_name varchar(512) NOT NULL,
    create_ts timestamptz,
    update_ts timestamptz
);

ALTER TABLE country_dim ADD CONSTRAINT country_dim_pk PRIMARY KEY (country_id) DISABLED;


texts: Storing objects in key/value stores - serialized or separate values?

texts:
I'm playing around with Go and BadgerDB, which is an embedded disk based (not in-memory like Redis) key/value store. There's just so little information about how to use key/values stores in general.

My main question basically is: Does it make more sense to store one entity/object/item per key? Like this:

user:0 = {"email":"johndoe@example.com","password":"some-hash"}

Or to store every field on its own? Like this:

user:0:email = "johndoe@example.com"
user:0:password = "some-hash"

Does anyone have some experience with this? Does one technique have some performance advantages over the other? Or does one have management issues I don't think about yet? Or is there something else to consider?


texts: I am somewhat new to MongoDB but I do have some experience with MySQL and PostgreSQL. Currently, I need to migrate Mongo DB from one site to another. The sites should be identical (development and production) but the production ran a bit forward in terms of content so we want to make sure they are mirroring each other. How do I do it? The mongo is run on the CentOS Linux system. Will this be as easy as doing a dump and restore? Or is there more involved?


texts: Azure - How can I check if my first full backup is available

texts:
As per Microsoft documentation Learn about automatic SQL Database backups

How often do backups happen?

  Full database backups happen weekly, differential database backups
  generally happen every few hours, and transaction log backups
  generally happen every 5 - 10 minutes. The first full backup is
  scheduled immediately after a database is created. It usually
  completes within 30 minutes, but it can take longer when the database
  is of a significant size. For example, the initial backup can take
  longer on a restored database or a database copy. After the first full
  backup, all further backups are scheduled automatically and managed
  silently in the background. The exact timing of all database backups
  is determined by the SQL Database service as it balances the overall
  system workload.

Is there a way for me to check if my first full backup is completed and ready for restore?

I understand it will be first available in the same location and later replicated to other locations.  I also know that I can check by trying to add a new database from backup and see if my database backup shows up in the list.  I am more interested if there are system tables, management views that I can query to check?


texts: Will this trigger always complete before returning results of Insert to client?

texts:
I have the following trigger for Auditing.

CREATE OR ALTER TRIGGER dbo.TR_MyTrigger ON dbo.Cust
    AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @EntityState AS SMALLINT;
    SET @EntityState = (CASE
                        WHEN EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED) THEN 3 -- Updated
                        WHEN EXISTS(SELECT * FROM INSERTED) THEN 4 -- Inserted
                        WHEN EXISTS(SELECT * FROM DELETED) THEN 2 -- Deleted
                    ELSE NULL END)
    IF @EntityState = 4
    BEGIN
        UPDATE Cust
        SET Created = GETDATE(), Updated = GETDATE()
        FROM INSERTED I
        WHERE Cust.BinId = I.BinId
    END
    ELSE IF @EntityState = 3
    BEGIN
        UPDATE Cust
        SET Updated = GETDATE()
        FROM INSERTED I
        WHERE Cust.BinId = I.BinId
    END
    IF @EntityState <> 2
    BEGIN
        INSERT INTO AuditEntries (SomeColumns..)
        SELECT SomeColumns..
        FROM INSERTED I;
    END
    ELSE
    BEGIN
        INSERT INTO AuditEntries (SomeColumns..)
        SELECT SomeColumns..
        FROM DELETED I;
    END
END;

When the client(API Server) call writing to the table Cust is complete, the next call inserts userIds to the AuditEntries table.

Extra info:

There exist no other triggers anywhere.
Client uses EntityFramework Core so time in question is after SaveChangesAsync() returns.
The PrimaryKey of the AuditEntries table is the ROWVERSION of dbo.Cust

The question is: are the AuditEntries guaranteed to exist at this time or is this a race I need to handle?

Other research:

sql-server-trigger-execution

An answer to: Are Sql Triggers synchronous or asynchronous

UPDATE: The above trigger does not enter a loop via direct recursion on any update operations due to the default T-Sql configuratoin.  See: Recursive Triggers

Found an SO this am that answers my original question: Does a trigger in a transaction only fire when the transaction is committed?


texts: Alternative to second column for tracking a FOREIGN KEY pre-deletion

texts:
Let's say I have a table A, that has one column: just an ID. This is obviously the primary key.

Next, I have a second table, which has an ID for table A, and then a second ID. The primary key is on it's own ID. Now I want to be able to delete rows from A, but not lose the foreign key in B.

There's no ON DELETE SKIP INTEGRITY or anything, so I have to use a trigger and second column to retain it:

CREATE TABLE Test1
(
    Id INT NOT NULL,
    CONSTRAINT PK_Test1 PRIMARY KEY (Id)
)

CREATE TABLE Test2
(
    Test1Id INT NULL,
    OldTest1Id INT NULL,
    Id INT NOT NULL,
    CONSTRAINT PK_Test2 PRIMARY KEY (Id),
    CONSTRAINT FK_Test2_Test1Id_Test1_Id FOREIGN KEY (Test1Id) REFERENCES Test1 (Id) ON DELETE SET NULL
)
GO

CREATE TRIGGER Test2_OldTest1Id ON Test2
AFTER INSERT, UPDATE
AS
UPDATE Test2
SET OldTest1Id = Test1Id
WHERE
    EXISTS(SELECT 1 FROM inserted WHERE inserted.Id = Test2.Id)
    AND Test1Id IS NOT NULL
GO

Are there any alternatives to this that don't require a TRIGGER or second column? Is there any way I can force referential-integrity for all but DELETE statements? I don't care about tracking what it is now vs. what it was, I just want to retain that FOREIGN KEY value forever, even if it's deleted from the referenced table.

I have a sample db<>fiddle to illustrate:

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=ba02ec43fff23231cb5715aaa265c1e5


texts: Nonclustered index storage on clustered columnstore

texts:
In SQL Server, a non-unique nonclustered index on a rowstore table incorporates the base object's bookmark (RID or clustering key) at all levels of the nonclustered index structure. The bookmark is stored as part of the nonclustered index key at all index levels.

On the other hand, if the nonclustered index is unique, the bookmark is present only at the leaf level of the index - not as part of the key (the bookmark is present as one or more included columns, in effect).

In SQL Server 2016, it is possible to build a nonclustered b-tree index on a column-oriented table (one that has a clustered columnstore index).

What is the 'bookmark' used for a nonclustered b-tree index on a clustered columnstore table?
Do the differences between unique and non-unique nonclustered indexes described above still apply?


texts: Understanding statistics, execution plans, and 'ascending key problem'

texts:
I'm trying to better understand (conceptually) the relationship between statistics, execution plans, stored procedure execution.

Am I correct in saying that statistics are only used when creating the execution plan for a stored procedure, and they are not used in the actual execution context? In other words, if this is true, once the plan is created (and assuming it's properly reused), how important are "up to date" statistics?

I was particularly motivated by an article I read (Statistics, row estimations and the ascending date column) which describes a scenario very similar to one I face daily with several of our client's databases.

We have an ascending date/time column in one of our largest tables that we query regularly using a specific stored procedure.

How do you prevent execution plans from growing stale when you have a hundred thousand rows being added a day?

If we're updating statistics frequently to combat this issue, would it make sense to use the OPTION (RECOMPILE) hint on this stored procedure's query?

Any advice or recommendations would be appreciated.

Update: I'm using SQL Server 2012 (SP1).


texts: How to know or recover my pg_dump password?

texts:
I would like to backup my Postgre db using pg_dump. However, the first question asked by the prompt is the password.
But I have no idea what it is. Indeed, it is different from my admin account password, and I don't know where to find it or recover it.


texts: Database reporting more space reserved than total size

texts:
I have a database which has four data files and a log file. The data files are each around 5 GB, the log is about 1 GB. The database reports the total space reserved as around 21 GB, and of that 18 GB is in use. This information is corroborated by:

Disk Usage Report
Database Properties dialog (in Management Studio)
sp_spaceused
sys.database_files
The data files on disk

However, the Disk Usage Report and sp_spaceused are both reporting that there is around 61 GB of data file space reserved, leaving around 43 GB as "unused" (i.e. not containing data). This should be impossible as the total space reserved is only 21 GB.

This phantom reserved space certainly isn't on the hard drive, so it must be a case of incorrect data somewhere in SQL Server. Where is it and how do I restore normality?


texts: Query gives correct answer on second run

texts:
I have the query

SELECT * FROM
(
    SELECT sort.*,
    (
        CASE concat(question_id,`type`)
        WHEN
            @curType
        THEN
            @curRow := coalesce(@curRow,0) + 1
        ELSE
            @curRow := 1 AND @curType := concat(question_id,`type`)
        END
    ) + 1 AS rank
    FROM
    (
        SELECT a.* FROM
        ul_attempt_responses a
        LEFT JOIN us_attempts b ON a.attempt_id = b.id
        WHERE b.user_id = 3
        AND response IS NOT NULL
        AND trim(response) != ''
        AND b.authenticated = 1
        ORDER BY question_id, type, id DESC
    ) sort
) final WHERE rank = 2;

On my local machine, when the query is executed twice, that is when I get the correct response. But on the Production Server, it works on the first time.

What could be the problem? Is there a problem with the query.


texts:
I am running a WSUS Server on Windows Server 2016. The WSUS Service stops because transition log is full.

The Event viewer says:

The transaction log for database 'SUSDB' is full due to 'CHECKPOINT'.

When i start SQL Management Studio and connect to the database i tried to follow the technet article for Troubleshooting a Full Transaction Log. It says that i could Back up the log.
So i select the database SUSDB and try to run a backup by pointing to Tasks and klicking on Back Up.... But as Backup Type i can't select Transition Log, because the Recovery Model is SIMPLE.

Then i tried to change the Recovery Model to Full in Database Properties >> Options. But when i try to do that, it says

Alter failed for Database 'SUSDB'.  (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The transaction log for database 'SUSDB' is full due to 'CHECKPOINT'.
The transaction log for database 'SUSDB' is full due to 'CHECKPOINT'.
ALTER DATABASE statement failed.
Could not write a checkpoint record in database SUSDB because the log is out of space.

Also in Database Properties >> Files the SUSDB_log has a Limit to 2.097.152 MB but the actual size of the log file is 220 MB on my disk. The disk free space is 230 GB.

After that i tried to run the query:

ALTER DATABASE susdb SET RECOVERY FULL;

But this also leaded to the error

The transaction log for database 'SUSDB' is full due to 'CHECKPOINT'.

I'm not able to restart the WSUS Service becaus of this error. Has anyone any suggestions what to do?


texts: which way is better to design shopping Cart Table - SQL Server

texts:
I want to create a shopping cart in an online store. I have two ways to design shopping cart, but I don't know which one is better.

Solution 1:
create a table like below :

in this way for each product for a user add new row.

note UserId and ProductId are uniqe

Solution 2:

use two tables: shopping cart, cartItem

In this way for each user just create a record in Shoopingcart Table .

What is your opinion?


texts: Can I write SELECT query which returns table name after FROM keyword?

texts:
I query a table which has another table name in one column. I must get that column value for searching that table. To do that I try something like this;

select cap from (select table_name from table2 where mi_prinx=(select mip1 from table1 where mip1=184425 and startmi1=0 group by mip1,startmi1 having count(*)>=4);

Inner Select statement returns table3 name. But this does not working. Can I write something like that or should I use Pl/Sql for that.

I am using something like this for now;

declare
table_name varchar2(100);
obj_mi_prinx number;
cap varchar2(100);
begin
select table_name,obj_mi_prinx into table_name, obj_mi_prinx from table2 where mi_prinx=(select mip1 from table1 where mip1=184425 and startmi1=0 group by mip1,startmi1 having count(*)>=4);
dbms_output.put_line('table_name: ' || table_name);
if(table_name = 'table3') THEN
select cap into cap from table3 where mi_prinx=obj_mi_prinx;
END IF;
dbms_output.put_line('mi_prinx in yb_ob_boru: ' || obj_mi_prinx);
end;

If I can do this without using IF statement, I prefer it.


texts:
New to this.

Looking at db2 schemas, which should be equal to OS system users, right ?
Doh apparently not. Except for PC,DB2ADMIN beeing on my OS system

How  is it, that this

select * from sysibm.systabauth
where grantee = 'DB2ADMIN'

produsces something and 'DB2ADMIN' is not on list of schemas ?

'DB2ADMIN' was created at db setup, so I assume it should have all privileges, but no, if I'm at 'PC' schemata 'DB2ADMIN' can't even execute simple select statement.

How to grant all privileges to 'DB2ADMIN' on 'PC schemata ?'

if this is my connection url:
jdbc:db2://127.0.0.1:50000/SAMPLE
What is SAMPLE, should be db, a little confuzig coz looking at db tree structure at sqirell client there is DB2 & schematas listed under it, and nowhere 'SAMPLE' ?


texts: Maintaining Postgres copies of fdw tables from Oracle

texts:
I have a production Oracle 11g server, from which I want to copy data regularly to PostgreSQL 10.14 database for DWH uses, so as not to overload the production with analytics queries.
I have succesfully mirrored the neccessary Oracle views with FDW wrapper, so relevant Oracle structures are accessible, and I have a couple of functions and queries I run regularly, to copy oracle_fdw view data to Postgres tables.
As Oracle is used for a business system, which is maintained by external support company, they are occasionally tweaking some of the views I copy, in order to enable new functionality in the business system. For example, they are adding columns to Oracle tables and views, and/or extending some column sizes.
When that happens, the Oracle fdw views in Postgres need to be reimported, or else they fail when trying to fetch a column, in which place there is another column now, or the column sizes don't match anymore, or there are more columns in the actual Oracle structs than in the fdw imported view.
Reimport, however, still breaks the queries and/or functions which use constructs like:
INSERT INTO my_pg_mirror_table_archive SELECT * FROM oracle_fdw_view;
because as most of Oracle structures I copy, are actually views, the new Oracle columns can and do sometimes appear in middle of others, instead of at end, like:
oracle_fdw_table: col1 new_col3 col2
which means even if I add column to my_pg_mirror_table_archive the above query will fail, as it will expect the column order col1 col2 new_col3.
I can circumvent that by manually specifying column order instead of SELECT * , however, the more tables the more possibility of mistakes and manual work.
In addition there are views on the pg_mirror_table_archive, which depend on it and need to be recreated after every such column addition/change.
Are there any tools or best practices that can automagically or semi-automatically maintain such relationship(s) and do the behind scenes work, so that my postgres tables always match the oracle ones?


texts: Migrating every table to a new linked server has too many prefixes

texts:
Unfortunately, I can't backup and restore, since the new database is on an older version of SQL.

When I run the following script, I get this error:

    Msg 117, Level 15, State 1, Line 1
    The object name 'eih-dr01.eih.ehs.org.DBA.dbo.fp_monthly' contains more than the maximum number of prefixes. The maximum is 2.

    declare @table varchar(255),
    @sql nvarchar(max)

    declare c cursor local for

    select st.name from sys.tables st

    open c

    fetch next from c into @table

    set @sql = 'select * into [eih-dr01.eih.ehs.org].DBA.dbo.' + @table + ' from ' + @table

    print @sql
    exec sp_executesql @sql

    fetch next from c into @table

    close c
    deallocate c

Question: Which prefix is superfluous?

Actual Question Why aren't my '[',']' charachters showing up in my dynamic sql statement?


texts:
I have postgresql database.
My table name is tblvoippolicy. Now, problem is by mistake i have deleted some records from table.
When I get the data file using following commands.

select * from pg_class where relname = 'tblvoippolicy' ; I get the following result

    relname    | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relacl
---------------+--------------+---------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------
 tblvoippolicy |        17234 |  407227 |      100 |     0 |      407226 |             0 |        0 |         0 |             0 |             0 | t           | f           | r       |        8 |         0 |           0 |        0 |        0 |       0 | t          | t          | f           | f              |

Now, when I look that data into data file of database using cat /var/pgsql/data/base/17230/407226. I get following result.

[root@manage upload]# cat /var/pgsql/data/base/17230/407226
ôu\"(Ô  Ÿà Ÿà¨žð8žàÔÈM ï¡test EC250
@%
ÿ10.106.1.228Q   ï–BEC200_Post_250      EC200
ú%
ú10.106.1.228?   ï”BBS_Service_Pre_250bs_service_1
@&
ú10.106.1.2286   ï’BEC200_Pre_500       EC200
@&
ô10.106.1.228+   ïBEC200_Pre_250       EC200
@&

I can not understand the described values and I want to revert back the records which I have deleted from table, how can I get back that records ?


texts: How to generate csv in oracle if it's query result return more than 32767 character per line  in linux shell script


texts:
I've just read this article:
http://dev.mysql.com/tech-resources/articles/move_from_microsoft_SQL_Server.html

There it says that MySQL on Windows is a good choice. (or is what I've understood)

In the article I don't understand if he says that MySQL on Windows is better than on Linux, or just that MySQL on Windows is good enough as Linux.

Now my doubt is:
if I can choose a server dedicated to MySQL and I have to choose between Windows Server 2012 and a free GNU/Linux server distro, which one would have better performances?

May you guys help me understand this?


texts: MySQL Insert Values Into Normalized Relational Database (multiple tables) at the same time

texts:
I have normalised my database to 4NF. How do I now insert values at the same time when records have been split into multiple tables? most of the child tables reference their parent table CUSTOMER column customer_ID as their foreign keys. The primary key in the parent table is auto-incremented.
There are many tables set up this way, but I thought the following 3 samples would sufficiently paint the picture:

CREATE TABLE customer (
  CustomerID INT(11) NOT NULL AUTO_INCREMENT,
  CusCatID INT(2) NOT NULL DEFAULT 1 COMMENT 'This is supposed to reference the customer catergpries table. But its giving a foreihn key/ index probpem?',
  `First Name` VARCHAR(50) NOT NULL,
  LastName VARCHAR(255) NOT NULL,
  EmailAddress VARCHAR(50) NOT NULL,
  GENDER BIT(1) NOT NULL,
  DOB DATE NOT NULL,
  Cus_Status_ID INT(11) NOT NULL DEFAULT 1,
  PRIMARY KEY (CustomerID)
)

ALTER TABLE customer
  ADD CONSTRAINT FK_customer_CusCatID FOREIGN KEY (CusCatID)
    REFERENCES customer_category(CusCategoryID) ON DELETE NO ACTION;

ALTER TABLE customer
  ADD CONSTRAINT FK_customer_Cus_Status_ID FOREIGN KEY (Cus_Status_ID)
    REFERENCES status(StatusID) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE TABLE customer_accounts (
  Cus_ID INT(11) DEFAULT NULL,
  AccountNo INT(11) NOT NULL
)

ALTER TABLE customer_accounts
  ADD UNIQUE INDEX AccountNo(AccountNo);

ALTER TABLE customer_accounts
  ADD CONSTRAINT FK_customer_accounts_AccountNo FOREIGN KEY (AccountNo)
    REFERENCES account(AccountNo) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE customer_accounts
  ADD CONSTRAINT FK_customer_accounts_Cus_ID FOREIGN KEY (Cus_ID)
    REFERENCES customer(CustomerID) ON DELETE CASCADE ON UPDATE CASCADE;

 CREATE TABLE customer_authentication (
  auth_id_cusId INT(11) NOT NULL,
  Username VARCHAR(255) NOT NULL,
  Password VARCHAR(255) NOT NULL,
  PIN INT(6) NOT NULL,
  PRIMARY KEY (auth_id_cusId)
)

ALTER TABLE customer_authentication
  ADD UNIQUE INDEX Username(Username);

ALTER TABLE customer_authentication
  ADD CONSTRAINT FK_customer_authentication_auth_id_cusId FOREIGN KEY (auth_id_cusId)
    REFERENCES customer(CustomerID) ON DELETE CASCADE ON UPDATE CASCADE;


texts: MYSQL DISTINCT on one column PLUS oher values

texts:
I wish to retrieve ordno and invno where ordno is unique from two different tables (archived and none archived invoices). The closest I have come is using the following using UNION DISTINCT but I also need the invno - any help is welcome?

SELECT INInvoiceHeaders.ordno
FROM INInvoiceHeaders
WHERE (MONTH(INInvoiceHeaders.invdate)=11
  AND YEAR(INInvoiceHeaders.invdate)=2016)
UNION DISTINCT
SELECT INArchivedInvoiceHeaders.ordno
FROM INArchivedInvoiceHeaders
WHERE (MONTH(INArchivedInvoiceHeaders.invdate)=11
  AND YEAR(INArchivedInvoiceHeaders.invdate)=2016)
GROUP BY ordno


texts: Error deleting FileTable - HRESULT = '0x80070490'

texts:
When trying to delete a FileTable, the following error occurs:

  Message 33405, Level 16, State 7, Line 1
  Error during operation on an object table Discard FileTable. (HRESULT = '0 x80070490 ').


texts: File / filegroup restore vs database / log restore in SQL Server

texts:
everyone

I have been doing some reading about database backup and restore.

While I have some understanding about full / differential database backup and restore + subsequent log backup and restore, I can not comprehend the usefulness of DATA /FILEGROUPS backup and restore.

I am not asking about the syntax, but rather its usefulness and situations under which regular full database backup + differential database backup + log backup + tail-log backup would not be sufficient or inappropriate.

Thanks


texts: Property DefaultLanguageName is not available Error on saving db settings

texts:
When I open DB settings and try to change any setting it throws an error on save:

  Property DefaultLanguageName is not available for Database
  '[TMS-API555]'. This property may not exist for this object, or may
  not be retrievable due to insufficient access rights.
  (Microsoft.SqlServer.Smo)

It really does show empty language setting

I tried to reset language via TSQL

EXEC sp_configure 'default language', 2 ;
GO
RECONFIGURE ;
GO

  Configuration option 'default language' changed from 3 to 2. Run the
  RECONFIGURE statement to install.

After that it still shows empty language and throws the error on save.

Tried

ALTER AUTHORIZATION ON DATABASE::dbname TO [sa];

didn't work

Facts:

I updated SQL Server 2016 to latest available version 13.0.4474.0
Windows Server 2016
Management Studio 14.0.17199.0
Logged in as sysadmin
If I create a new empty DB - there is no error and everything works


texts: How to grant SQL Agent proxy to a public role in MSSQL 2014

texts:
I want to grant SQL Agent proxy to a public role in msdb database in MSSQL 2014.

I have the following query and I want this to return me some valid output.

USE [msdb]
SELECT
 sp.name AS proxyname
FROM dbo.sysproxylogin spl
JOIN sys.database_principals dp
  ON dp.sid = spl.sid
JOIN sysproxies sp
 ON sp.proxy_id = spl.proxy_id
WHERE principal_id = USER_ID('public');


texts: Is there always a MySQL user that has all privileges on all databases?

texts:
For example, does root always have all privileges on all databases?

If I create a new database, will this user automatically have all privileges on the new database without having to run a GRANT command?


texts:
I am writing a trigger to audit the MySQL database. IF statement doesn't work. Where am i doing wrong?

Also, can you guide me on the best trigger for mysql audit.

DELIMITER $$

CREATE
    DEFINER = 'sqlsa'@'%'
TRIGGER AuthenticationLog_U
    AFTER UPDATE
    ON AuthenticationLog
    FOR EACH ROW
BEGIN
    DECLARE Type varchar(1);
    DECLARE TableId tinyint;
    DECLARE IsActive tinyint;
    SET @Type='U';
    SET @TableId = 2;
    SET @IsActive = (SELECT IsActive FROM AuditDB.ExportLogTableDefinition WHERE TableId=@TableId);
    IF (@IsActive=1) THEN
        INSERT INTO AuditDB.ExportLog (DBName,TableId,OperationType,HostName,UserName,PrimaryKey,OldData,NewData)
        VALUES (DATABASE(),@TableId,@Type,@@hostname,CURRENT_USER(),OLD.PKAuthenticationLogID,
        CONCAT_WS('',
          '[Key]=',IFNULL(OLD.Key,'NULL')
          ,'[Secret]=',IFNULL(OLD.Secret,'NULL')
          ,'[Token]=',IFNULL(OLD.Token,'NULL')
          ,'[ApplicationId]=',IFNULL(OLD.ApplicationId,'NULL')
          ,'[IpAddress]=',IFNULL(OLD.IpAddress,'NULL')
          ,'[Message]=',IFNULL(OLD.Message,'NULL')
          ,'[CreatedOn]=',IFNULL(OLD.CreatedOn,'NULL')
          ,'[IsSuccess]=',IFNULL(EXPORT_SET(OLD.IsSuccess,'1','0','',1),'NULL')
          ,'[LogType]=',IFNULL(OLD.LogType,'NULL')
          ),
            CONCAT_WS('',
          '[Key]=',IFNULL(NEW.Key,'NULL')
          ,'[Secret]=',IFNULL(NEW.Secret,'NULL')
          ,'[Token]=',IFNULL(NEW.Token,'NULL')
          ,'[ApplicationId]=',IFNULL(NEW.ApplicationId,'NULL')
          ,'[IpAddress]=',IFNULL(NEW.IpAddress,'NULL')
          ,'[Message]=',IFNULL(NEW.Message,'NULL')
          ,'[CreatedOn]=',IFNULL(NEW.CreatedOn,'NULL')
          ,'[IsSuccess]=',IFNULL(EXPORT_SET(NEW.IsSuccess,'1','0','',1),'NULL')
          ,'[LogType]=',IFNULL(NEW.LogType,'NULL'))
        );
      END IF;
END
$$

DELIMITER ;

There is no error message. But I try query, nice work.

SET @IsActive = 1;
    IF (@IsActive=1) THEN


texts: MS Access 365 When selecting Lookup Wizard, datatype is changed autmatically to Number

texts:
I want to create a relationship between my Contacts table & Ref_State table.

Contacts table has field called State/Region which I want to make Data Type = Lookup Wizard
Ref_State has fields ID (autonum), StateDescription(short text), & StateAbbreviation(short text).

When I complete the lookup wizard and have Referential Integrity, Cascade Update & Cascade delete selected, the Data Type for State/Region in the Contacts table is changed to "Number". I'm confused why it does this and need to know if this is an error or normal for all lookup data types to be changed to something other than what was intended with no way to alter the datatype. Is it displaying Number because the Primary Key in the Ref_State is ID which is an auto number, and hence the Contacts field State/Region displays a Number Datatype? But When looking at data view and adding entries to the table, the StateDescription field is being displayed, not the ID.   I'm really lost.

I get this message when I try complete the Lookup Wizard

  Some data may be lost.
  The size of one or more fields has been changed to a shorter size. If data is lost, validation rules may be violated as a result. Do you want to continue anyway?

I took a database class in college and am familiar with basic concepts, but MS Access is making this a nightmare for me to design.