texts: How can I decrypt data on a replicated MSSQL database?

texts:
I have a database which uses a symmetric key to encrypt a credit card field. There is a trigger on the credit_card field which runs every time it is updated, to encrypt the contents, and save it to another field called credit_card_encrypted and then it wipes the contents of credit_card.

I have recently setup replication on the table, to another instance of SQL Server.
Everything works fine except, I am unable to decrypt the data on the replicated server.

I have imported the certificate and the key and I have created a symmetric key however, whenever I try to decrypt the credit cards, it just returns NULL.

I have tried to manually add a value to the credit_card field on the replication server, and then I manually encrypted it, and I was actually able to successfully decrypt it.

So why can I not decrypt the data that is replicated?

Also, I can compare the varbinary data of the encrypted card and the contents looks exactly the same on the main server &  the replication server.

e.g: 0x000CE24D0120A349PPL29D6BFE70C54E0100000064E3380AA4EC04A4B4E959535798696E81502A063617B21CFD75FAFF93866D47603543A5D6EBECDF5F8C0D23D8CCL982


texts: Is it possible to view a SQL query in the PgAdmin III Graphical Query Builder?

texts:
I often use pgadmin III's graphical query builder to design my SQL queries. I can then toggle back to the SQL Editor tab, and grab the SQL.

Is it possible to paste a SQL query in to the SQL Editor tab, then toggle to Graphical Query Builder to see the graphical representation? I'm coming from an MS Access background, and wondering if similar functionality exists.


texts:
I have created an SSIS Transfer Logins Task. I have selected the SourceConnection and DestinationConnection and both valid and active.

I have selected certain Windows group accounts to transfer from source to destination databases. IfObjectExists is set to 'Overwrite' and CopySids is 'True'.

When I run the task I get the following error:

  SSIS package "F:\SQL Server\SSDT\acQuire Migration\acQuire Migration SSIS\acQuire Migration SSIS\Package.dtsx" starting.
   Error: 0xC002F325 at Transfer Logins Task, Transfer Logins Task: Execution failed with the following error: "An exception occurred in SMO.".
   Error: 0xC002F325 at Transfer Logins Task, Transfer Logins Task: Execution failed with the following error: "Value cannot be null.
   Parameter name: server".
   Task failed: Transfer Logins Task
   Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
  SSIS package "F:\SQL Server\SSDT\acQuire Migration\acQuire Migration SSIS\acQuire Migration SSIS\Package.dtsx" finished: Failure.
  The program '[1868] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).


texts: Changing Charset to utf8 for all the fields and tables in mysql

texts:
How should I use the mysql and awk to change the charset for all the fields and tables for a specific database? I did not include username or password, so for sure it will not work.

Should I run it as it is and wait or does it need to be modified to work with authentication?

mysql --database=dbname -B -N -e "SHOW TABLES" \
| awk '{print "SET foreign_key_checks = 0; ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1; "}' \
| mysql --database=dbname &


texts: Fastest way to shred large xml data into a table

texts:
I have a nightly process that shreds XML data from a 17 million row table (120 GB) into another table, it takes about 3 hours and inserts about 1.5 million records.

I currently do a complete drop and reload. I've tested just shredding and inserting new records, but haven't seen much of an improvement (tried left outer join and not exists and except). I use xquery and nodes(), use text(), use cross apply; the source table does not have a clustered index so I cannot create an XML index (vendor table that I cannot modify). Is there a faster way to get this finished in less than 3 hours?

The query is basically this:

WITH XMLNAMESPACES(DEFAULT 'blahblahblah')
INSERT INTO mytable
SELECT
  ID,
  Value1.Field1.value('element1/text())[1]','varchar(255)') AS myfieldvalue1
  Value2.Field1.value('element2/text())[1]','varchar(255)') AS myfieldvalue2
FROM my table
CROSS APPLY mytable.data.nodes('//path1') AS Value1(Field1)
CROSS APPLY mytable.data.nodes('//path2') AS Value2(Field2)

I can't really post a sample of the XML, but it is big for some records.


texts: Copy Data from Two different databases with FK relation

texts:
So the situation is as follow:

I have production DB called DB1 which has huge data, around 250 GB.
I have Test DB called DB2 on my local machine which has the same schema as the production DB but only the first two tables are partially filled(UUT_RESULT, STEP_RESULT).

What I want:
Copy the only related data from the Production DB tables (PROP_RESULT) to my local DB table.

Very important note: I can copy the entire table, However, it's huge. That's why I only want to copy the data from PROP_RESULT table in the production DB which has relation to the already existed data in my local DB.

Note: Only UUT_RESULT and STEP_RESULT are partially filled in the Local DB, other tables are empty and I need to populate them and that's why I am posting this.

The following image will make is easier to understand


texts: A flag (indicator) that shows for each row which conditions was used to select it

texts:
Suppose I have the table foo. I want to filter the rows of this table using different conditions (cond_1, cond_2, ..., cond_n - the order is important) combined in a disjunction, i.e.:
SELECT * FROM foo WHERE cond_1 OR cond_2 OR ... OR cond_n;

Is it possible (in PostgreSQL 13) to distinguish for each row which condition cond_i was used to select it? Since I stated that the order is important the question may be reformulated as - I want to know the first cond_i that selected the row, for each row.

Now consider that I have in foo a column for this purpose, e.g. selected_by.

Is it possible to distinguish for each row which condition cond_i was used to select it and store it in selected_by? (the same as 1st question but for an UPDATE statement)

Two things are important: the order of conditions (as it was highlighted earlier); performance (i.e. table foo may have many rows, the number of conditions is small, up to 5-10).


texts:
I recently verified the IOPS in my RDS instances IOPS and I believe that it is too high. What is considered a good number/average for IOPS?

Are there any tools to check integrity and problems in a Mysql instance to help lower the IOPS, better than what is seen below?


texts: What's an alternative solution to using a null value for a foreign key field?

texts:
I have a few tables in my database which contain a Foreign Key field linking to another table. The problem is, the value for that field isn't always going to be present, and so sometimes I might have to insert a null foreign key field. To elaborate:

I have a table Sense_LanguageSource which lists individual etymologies of entries in a dictionary. This has the following fields:

LanguageSourceID (PK - Autonumber)
SenseFK (FK - Number)
LanguageCode (FK - Number)
SourceWord (Short text)
LanguageSourceType (Short text)
IsWaseieigo (Yes/No)

The LanguageCode field is the problematic case. It's used to list the language which the etymology derives from, and in this case it's a foreign key linking to a LanguageCodeValue table, which contains both the ISO-639-2B code and the full name of the language.

However, some of the etymologies I'm going to insert into the database don't have LanguageCodes, so I would have to insert a 0 into that field. Initially, Access wouldn't let me do this, so I disabled Referential Integrity. Then when I reopened the database, Access said that the Database was compromised and needed to be repaired. So I think that I need an alternative solution to this.

I would make it less normalised and simply have a text field for the LanguageCode in there, but there are two tables that refer to the LanguageCodeValue table, and it seems to make a lot of sense to have a table describing what each LanguageCode refers to. The solutions I can think of right now are:

Remove the relationship between the LanguageCode fields and the LanguageCodeValue table (so that it's purely a reference table, and not linked,) and just use SELECT queries if I need to look-up the meaning of the LanguageCode.
Keep the link between the tables without Referential Integrity to allow null foreign keys, and hope that the database doesn't break down.
Insert a ""null"" value in the LanguageCodeValue table to refer to (by which I mean an actual record with a key value of 0 or similar, and blank values for the text fields), so that instead of entering a null foreign key, I can just link to a record that doesn't mean anything.
Another solution?

As I'm new to databases and not sure about best design practices, I would like to know which solution is most appropriate in my situation.


texts:
I know this has been asked many times but, what is the proper configuration to succesfully purge / shrink .idb tables?

I have tried to use optimize table table_name but after the query the .idb tables stays the same size.

This is my configuration at [mysql] in /etc/my.cnf:

[mysqld]
max_allowed_packet=500M
innodb_buffer_pool_size = 15G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_flush_log_at_trx_commit=2
query_cache_size = 268435456
query_cache_type=1
query_cache_limit=134217728
innodb_file_per_table=1

Am i missing something?


texts: How to select rows after a certain row using auto increment temporary columns

texts:
I have a table that has ID varchar(255) as PK. I have managed to create an auto increment temporary column rowNumber to use with a SELECT, as follows:
SELECT
   (@cnt := @cnt + 1) AS rowNumber, ID
  from table
  CROSS JOIN (SELECT @cnt := 0) AS n
where
  (
    some conditions
    )
  )
  and date > {ts '2020-08-06 08:51:23.08'}
  ORDER BY ID
  LIMIT 10
;

Executing the above query gives me the following result:
rowNumber     ID
1           0000fb46-7eb1-48b4-8b47-6d99f636c92a
2           000198a5-0453-4339-b022-3050cad4e2b8
3           000ef1cc-1dcb-4162-806d-a040a29986d1
4           001de8e9-2809-4747-b12b-278b76a8bb5d
5           002080fb-21a8-437c-b468-243eb172cd1f
6           0024e99c-f576-404a-b14e-de8b4d6c6d73
7           004f2923-96ae-46ae-8d24-fd67cef40d16
8           006c8439-1ec0-486a-a372-7091d4a85eea
9           0078ce32-5858-4885-b207-c528561c2d40
10          0086618f-ab05-4c06-9c0a-7ec9fed1b82f

How can I modify the query above so that I can select the rows, after a certain rowNumber value, 4 for example. Could really use some help with this.


texts: PostgreSQL update and select in one query

texts:
I have a table (id, serialnumber, status) of numbers and their statuses, need to take one with status 'unused' and change it's state to something like 'in work'. Can do it this way:

UPDATE serials
SET status = 'in work'
WHERE serialnumber IN (
  SELECT "serialnumber"
  FROM serials
  WHERE status = 'unused'
  ORDER BY id ASC
  LIMIT 1
)
RETURNING serialnumber;

Is that the right way of selecting and updating a row in concurrent environment? It's unacceptable to take a serial when someone already took it.


texts: TRANSACTION ISOLATION LEVEL SNAPSHOT vs. TRUNCATE?

texts:
I'm hoping someone can shed some light on this behavior that I was not expecting regarding SNAPSHOT isolation vs. TRUNCATE.

Database: Allow Snapshot Isolation = True; Is Read Committed Snapshot On = False.

Procedure1 (Replaces content of table foo from a long-running complex SELECT with lots of joins):

BEGIN TRAN;
TRUNCATE TABLE foo;
INSERT INTO foo SELECT...;
COMMIT;

Procedure2 (Reads from table foo):

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SELECT * FROM foo;

If Procedure1 is running while Procedure2 is executed, Procedure2 is held up with a LCK_M_SCH_S wait (according to sp_WhoIsActive) until Procedure1 finishes. And when Procedure2 does complete, it raises this exception:

  Snapshot isolation transaction failed in database 'DatabaseName'
  because the object accessed by the statement has been modified by a
  DDL statement in another concurrent transaction since the start of
  this transaction. It is disallowed because the metadata is not
  versioned. A concurrent update to metadata can lead to inconsistency
  if mixed with snapshot isolation.

However, Microsoft does not list TRUNCATE as a DDL statement not permitted under SNAPSHOT isolation: http://msdn.microsoft.com/en-us/library/bb933783.aspx

Clearly I'm not understanding something correctly, as I would have expected a best case of Procedure2 immediately returning the most recently committed data from the table before the TRUNCATE or a worst case of being held up by Procedure1 and then returning the new content of the table. Can you help?


texts: Create multiple logins with the same permissions

texts:
I have a server instance with 100 databases in it.

I need to create 20 new logins with access to 65 of the 100 databases. They all need to have the exact same permissions. The 20 logins should not even see the other 35 databases in the Object Explorer.

What is the best and most efficient way to do that?

Thank you


texts:
Today I changed my username and password in MySQL and I logged into phpmyadmin to see that all my databases were gone.

I've tried changing the datapath but then I realized I didn't see any of my databases in the data folder either!

Can I get back my databases? Are they stored somewhere else?


texts: Readonly table permissions not applying to new tables

texts:
I have a postgreSQL-11 database that get's a new table created every Monday as part of a DW load.  However, when that new table is created, the [readonly] role that's been created doesn't get SELECT permissions to it.  Doesn't seem to matter what I do.  Here are the details:

Using PostgreSQL 11.6
RedHat 4.8.5 x64
Tables are being created in the public schema
The users that have been created authenticate through LDAP.
The users are configured to inherit perms from [readonly]

Code used to create acct:

CREATE ROLE readonly;
GRANT CONNECT ON DATABASE mydatabase TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

create role user1 login;
GRANT readonly TO user1;

Output of \du

     Role name  |                 Attributes        | Member of
 ---------------|-----------------------------------|-----------
 dba1           | Superuser, Create role, Create DB | {}
 user1          |                                   | {readonly}
 user2          |                                   | {readonly}
 user3          |                                   | {readonly}
 dba2           | Superuser, Create role, Create DB | {}
 user4          |                                   | {readonly}
 user5          |                                   | {readonly}
 dba3           | Superuser, Create role, Create DB | {}
 appUser        | Create DB                         | {}
 readonly       | Cannot login                      | {}

output of \ddp

\ddp output:

  Owner    | Schema | Type  | Access privileges
-----------+--------+-------+-------------------
user1       | public | table | readonly=r/user1
DBA1        | public | table | =r/DBA1
            |        |       | DBA1=r/DBA1
            |        |       | dba2=r/DBA1
            |        |       | dba3=r/DBA1
            |        |       | user2=r/DBA1
            |        |       | user3=r/DBA1
            |        |       | user4=r/DBA1
            |        |       | user5=r/DBA1
            |        |       | user1=r/DBA1
            |        |       | readonly=r/DBA1

Articles I've used to setup and/or troubleshoot the issue:

Managing PostgreSQL users and roles
Messing with PostgreSQL users and permissions
ALTER DEFAULT PRIVILEGES

Anyone see what wrong here?  I'm at my wits end and about to give them damn RO user Superuser rights, which is clearly not ideal.


texts: oracle clob export/import across non linked instances

texts:
I'm trying to migrate table data across two instances(different instance schemas, but same table schema) that are not connected(not an option).

The problem is that I can not access host file systems and the table has large number of rows with CLOB data(with newlines of course).

TOAD exports with delimited text, insert statements, SQL loader(automatic TOAD script, not custom fitted) and MS Access has failed so far; TOAD either refuses or does't fill CLOB cells.

Is there any way to do this without buying specialized software or splitting CLOBs into mutiple rows and assembling them later?

Also I'm limited to the following:
Oracle db: 10.2.0.4.0
client: 9.2.0.1.0
TOAD: 9.6.0.27

If anybody has any ideas, it would help a lot, thanks!


texts: Purpose of Try/Catch without Transactions

texts:
I have come across the following code (SQL Server 2016, 2008-compatible database):

CREATE PROCEDURE myProc
AS BEGIN
    DECLARE @ERRORMESSAGE As VARCHAR(1000)

    BEGIN TRY
        DELETE FROM Foo -- (statement #1)
        DELETE FROM Bar -- (statement #2)
    END TRY
    BEGIN CATCH
        SET @ERRORMESSAGE = ERROR_MESSAGE()
        RaisError(@ERRORMESSAGE,16,1)
    END CATCH
END

What is the purpose of the try/catch block?

For the sake of discussion, lets assume there are no triggers called and the client code that executes this procedure does not wrap the call in a transaction.

I had to fix a bug in code similar to this where statement #2 raised an error. Looking at the data it appeared statement #1 completed successfully and statement #2 did not apply (obviously).

One thing this does is not give a clear line number when trying to debug the crash, but is there anything legitimate this does?


texts: Oracle Standard Edition Monitoring Tools

texts:
We have 10g and 11g Oracle databases and all of them is Standard Edition.

We need monitoring tools like cloud control for standard editions. Do you have any suggestions ?


texts: SQL Server 2005: Full-text search catalog design

texts:
I'm new to full-text searches, and am curious about how to properly design the full-text catalogs.  I have an "articles" table.  I would like to do full-text search on both the "title" and the "body" at the same time.

Does this require two separate catalogs?  If not, is it better to use two separate catalogs, or just one?  If another unrelated table requires full-text indexing, should it be part of the same catalog, or a different one?


texts: write "ENGINE=INNODB DEFAULT CHARSET=UTF8MB4" once for the whole script

texts:
When exporting my database structure there is this ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 after every table creation. Do you have to write it for every table? Or is there a way to write it once for every table like (pseudo SQL)

CREATE DATABASE IF NOT EXISTS `mydb`;
USE `mydb`;
SET (ENGINE, `INNODB`);
SET (DEFAULT CHARSET, `UTF8MB4`);


texts:
I am experiencing a blocking issue in sql server 2005. An end user executes a query (e.g.  select statements) and seems to get into a never-ending blocking until i kill the process e.g. for hours on end .

the end user querys run just fine when I test the. It seems to get into a blocking issue when qanother process tries to truncate and reload a table.

how can I troubleshoot this?


texts:
I have a relationship that I am trying to represent in a database.  Imagine I have series of many to many relationships...

Say student to proffesor, and professor to classes...

A student can many professors and professors can obviously have many students.  A student also has many classes, each taught by a different or similar professor, and professors can teach many classes.

To relate those many to many relationships to each other, you obviously need a join table between each.

But, if you relate a student to a professor, you are automatically linking him to all the classes that he teaches.  Which doesn't necessarily have to be the case.  You could take one class with one professor, and have another class with another professor, or you could have two classes with one professor and another with another professor.

Obviously you could just link the student directly to the classes and then link the professors to the classes.  But Imagine, for the sake of this question, that for some reason, you can only link students to classes.  Imagine this is a legacy database for instance.

Would it be good form to instead of linking directly to the professor, link instead a link-table of the professor... So I would have a link table to a link table of say Prof ID 2, class ID 3... That would have a link id of lets say 2.  I would then have a link table with student 3 linked to Prof->Class ID 3.  So that student is now linked to professor ID 2 and class ID 3.  Think of that relationship like this...

Is that a good way to represent that relationship, or is there a better way to do this?


texts: Is it possible to limit concurrent aggregations in Mongo?

texts:
When clients makes certain requests, our service sends aggregation queries to Mongo. Some of these aggregations take longer than others.

Usually this is fine, but occasionally multiple heavy aggregations will be requested at the same time. In the worst case, this causes Mongo to become unresponsive, and other requests (such as inserting new documents) fail with a timeout.

Is there any way I can limit the number of aggregations that Mongo will perform in parallel?

Ideally if 8 aggregations are running and a new aggregation request is made, Mongo would queue the new aggregation until one of the others has completed.


texts: Create indexed view with fluctuating data behind

texts:
I´ve heard about indexed-views in MSSQL which can increase the performance.
My problem is that I want to create a view which joins round about 5 tables. The tables are constantly updated and are essential for the backend.

But the customer should have a view where he has all the data which is interesting for him. Cause the normal statement is really slowly, I thought about creating an indexed view to increase performance, when getting data from view.

My problem is that I have to create an unique clustered index which has to updated each time the data behind is changed.

I don´t know which is smarter, to create a new indexed-view or run the normal statement. I can not create more indices inside the base tables cause this will not be smart and it would not work great.

The following points are important for me:

the view query performance must be fast
the view data must be current
the base table update speed must be fast

Is there any idea or advice you can share with me?

Thanks to anyone who can help!


texts: Saving a SQL Server database to a file and opening it on another server under any database name

texts:
I am trying to find an efficient way of being able to physically save and open a SQL Server database like how it's done with Access. I would like to be able to save my entire database into a file and open it on some other server. I have already tried backup/restore but they're very tricky and it does not work all the time. Is there a way to do it other than the backup/restore procedure?

The following is what I get when I try to do the restore. I tried every possible approach that I could think of, but it simply doesn't restore.


texts:
We are using Mysql V5.6.
We have a MyISAM table. when we open the table, the records will be displayed in some order instead of primary key order. Even when selecting the records will not be retrieved in primary key order. I am not sure on what basis mysql is displaying the records.

Is there any default order in Mysql, instead of primary key order?


texts:
I am importing a database (.dmp) file using the 12c Data Pump import wizard.  I created a user for the database and remapped the schema and tablespace.  I get the following errors:

ORA-39071: Value for NAME_EXPR is badly formed.
ORA-01756: quoted string not properly terminated
ORA-39012: Client detached before the job started.

The statement begins, "dbms_datapump.metadata_filter(handle => h1, name => 'NAME_EXPR', value => 'IN(''ABSENCE_DURATION'',  ...
The statement includes a LONG list of tables and the number of characters is 32,659.

I'm thinking that this statement may have exceeded some limit.  If that is the case, how would I break this statement into several statements?


texts: Resource overlap between Synapse databases

texts:
I have an Azure Synapse dedicated pool with a single database. It is scaled to DW500c which the documentation says means it has a single compute node.
If I add a second database how does work against it affect the first DB's resources? Will the second DB reside on the same node as the first, or will the second have its own dedicated memory & compute?
Having tried it and queried sys.pdw_nodes_pdw_physical_databases from each DB I see different values for pdw_node_id between the results. This makes me think they are separate hardware (VMs, k8s containers, whatever) but I cannot find documentation to confirm or deny my hunch.


texts: Scaling out SQL Server and syncing data across multiple machines

texts:
I don't have expertise in architecting databases, and I've been teaching myself new stuff every day. I'd like to make an Internet-scale application using SQL Server as the data store. I haven't found any good information online with regards to scaling out SQL Server.

My understanding is that scaling out is great for write throughput, but it doesn't necessarily scale reads. A simple example (which is relevant in my case) is, if data is sharded by posting user id, status 1 posted by user X living in shard A will have all its likes and comments across the whole federation. So, if I need to fetch the comments on this status, I need to hit every database and merge and sort/filter results in application memory. This is bad for the databases because they are kept busy and bad for the web servers because I will be using CPU and RAM for post processing the objects. Ideally, I'd like to write to one database and read from one database for maximum scalability.

Now, what I'm thinking of doing is, instead of sharding by posting user id, shard by receiving user id. So, if user X posts status 1, user Y living in shard B can insert a comment in shard A, and I can enforce a parent-child relationship between the status and the comment. User Z living in shard C can insert a like in shard A for the comment, so the comment and the like can constitute a parent-child relationship. The benefit of this approach is I query only one database to get all the comments and likes for a specific status rather than naively querying every single shard.

However, I need to get results like "comments on status 1 by people who are male or 18+ years old". This is a crucial functionality I want to implement. I still have to hit other databases to get information about the users. In order to eliminate this, I'm thinking of creating a sync group where one database (hub) syncs all user deltas to all shards (every 5 minutes). I'm okay with eventual consistency though it has its own problems for example, if a user deletes their account, from the time the account is deleted to the time the delta is persisted to a shard, other users will not see the change potentially adding child objects to objects created by that user. This seems to me a data integrity issue.

I'm also aware of replication and caching to increase read throughput.

My question is, which approach should I pursue? If I choose the second one, will I have trouble syncing data across potentially hundreds or thousands of servers? Not to mention the hub is essentially a single point of failure.


texts: ASM filter drive not starting automatically

texts:
After migrate from asmlib to asm filter drive using the Doc ID 2172754.1 the ASM Filter drive not start automatically and I always need configure again using asmcmd afd_configure command to start it again.
What can be the possible reason for it?
Here is the current status of asm filter drive after rebooting the machine:
[root@server1 ~]# asmcmd afd_state
ASMCMD-9530: The AFD state is 'INSTALLED'
[root@server1 ~]#

I can start normally after issue the command asmcmd afd_configure
[root@server1 ~]# asmcmd afd_configure

AFD-627: AFD distribution files found.
AFD-632: Existing AFD installation detected.
AFD-634: Removing previous AFD installation.
AFD-635: Previous AFD components successfully removed.
AFD-9294: updating file /etc/sysconfig/oracledrivers.conf
AFD-636: Installing requested AFD software.
AFD-637: Loading installed AFD drivers.
AFD-9321: Creating udev for AFD.
AFD-9323: Creating module dependencies - this may take some time.
AFD-9154: Loading 'oracleafd.ko' driver.
AFD-649: Verifying AFD devices.
AFD-9156: Detecting control device '/dev/oracleafd/admin'.
AFD-638: AFD installation correctness verified.
Modifying resource dependencies - this may take some time.

[root@server1 ~]# asmcmd afd_state
ASMCMD-9526: The AFD state is 'LOADED' and filtering is 'ENABLED' on host 'server1' <<<<< LOADED AND ENABLE

[root@server1 ~]# asmcmd afd_lsdsk
--------------------------------------------------------------------------------
Label                     Filtering   Path
================================================================================
DISCO1                      ENABLED   /dev/sde1
DISCO2                      ENABLED   /dev/sdf1
DISCO3                      ENABLED   /dev/sdg1
OCR_NEW                     ENABLED   /dev/sdh1
[root@server1 ~]#


texts: Should I use a three-way associative table?

texts:
I have a scenario which I believe I have come up with a solution - however, I can't be sure it's a manageable one, or even correct. The scenario is as follows.

There are are three tables involved in this: groups, user_types, and resources.

The requirement is that a user can add a resource for a specific combination of user types and groups. For instance, a given user might upload a ‘technical manual’ (resources) that is supposed to be only accessible to ‘painters’ and ‘foremen’ (user_types) in ‘group a’ (groups). They might then assign the ‘manual’ to only ‘painters’ in ‘group b’, and so on.

My tentative solution to this is to have a many-to-many (M:N) relationship between groups and user_types using an associative table called groups_user_types. Then, that associative table has a many-to-many relationship to resources, using another associative table unfortunately called groups_user_types_resources. I've also considered having an associative table that connects the three tables, but I'm not a DBA and I don't even know if this is a valid way to do it.

Next problem is seeing if my chosen ORM supports this type of relationship, but that's a question for another Stack Exchange site...

Does this relationship make sense? Is there a solution that doesn't connect an associative table to another table?

If I have not provided enough information, please let me know and I'll explain further.


texts: Variable to select database in SQL query

texts:
If I want use a variable in the FROM clause of a  SQL query I need to use dynamic SQL. Which might be a pain if the query is long and complex.

There are few different ways of running a query from a database you are not currently connected to.  The two methods I use most often are these..

Select * from msdb..sysjobs_view

or

USE MSDB
Select * from sysjobs_view

If you start on the master database both of the above will give you the same results.

When I put these both into dynamic SQL one works and the other does not

Starting from master, this works

DECLARE @DBN SYSNAME
SET @DBN = 'msdb'
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = N'Select * from ' + @DBN + '..sysjobs_view'

EXEC (@Sql)

But this does not

DECLARE @DBN SYSNAME
SET @DBN = 'msdb'
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = N'USE ' + @DBN

EXEC (@Sql)
Select * from sysjobs_view

I get the error

  Msg 156, Level 15, State 1, Line 14
  Incorrect syntax near the keyword 'EXEC'.

My reason for this: One line of dynamic SQL in big complex query is going to be less painful to code than trying to turn the whole query into dynamic SQL.  My question here is just about why its not working with the USE clause.

Why can't I use dynamic SQL with the USE clause to change the database context?

NOTE: The below does work and would be easy to code.  But my question about why remains.

DECLARE @DBN SYSNAME
SET @DBN = 'msdb'
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = N'USE ' + @DBN  +'
Select * from sysjobs_view
'

EXEC (@Sql)


texts: Making decision between Count() & Top queries in terms of performance

texts:
We are using SQL Server and this is a performance related question.

We have a relatively big table and intend to perform an action in case of having more than 1 record with the same ID. Consider the simple queries below:

select top 2 [TenantID] from [dbo].[tblTenant] where TenantID = 1

vs

select COUNT ([TenantID]) from [dbo].[tblTenant] where TenantID = 1

I would guess in the first query, SQL Server stops scanning the entire table after finding the first possible two TenantIDs. But in the second query, operation is done over all the records in the table.

Is that a correct assumption? I mean, could we say the first query is better in terms of performance?


texts:
I did a backup of a database using pg_basebackup. I need to restore it to another server.
The database version in the source server is 9.2.23. The destination server postgresql version is 12.
Will there be any problems if I restore from the 9.2.23 to 12?


texts: Drop primary key without dropping an index

texts:
Is it possible to drop primary key constraint on a table without dropping the index? I have a situation when I want to remove the PRIMARY KEY to get rid of the NOT NULL constraints and set it to another column pair. I want to avoid dropping and recreating the index. Can just the PRIMARY KEY 'attribute' on the index be dropped? Dropping the constraint removes the index.

I am using PostgreSQL version 9.1.


texts: Failed to execute IS server package because of error 0x80131904 ... System.ComponentModel.Win32Exception: The handle is invalid

texts:
I have an SSIS package that is executed every 30 seconds by a SQL Agent Job. Every few days, an execution will fail with this message in the Job History:

  Failed to execute IS server package because of error 0x80131904.
  Server: MyServer, Package path:
  \SSISDB\Packages\MyProject\MyPackage.dtsx, Environment reference Id:
  1.  Description: A .NET Framework error occurred during execution of user-defined routine or aggregate "start_execution_internal":
  System.ComponentModel.Win32Exception: The handle is invalid
  System.ComponentModel.Win32Exception:      at
  Microsoft.SqlServer.IntegrationServices.Server.ServerApi.CreateCallerToken(IntPtr&
  callerToken, IntPtr& lpEnvironmentInfo)     at
  Microsoft.SqlServer.IntegrationServices.Server.ServerApi.StartExecutionInternal(SqlInt64
  projectId, SqlInt64 executionId, SqlInt64 versionId, SqlInt16
  use32BitRuntime)  .  Source: .Net SqlClient Data Provider

Does anyone have any ideas as to what might be going wrong and what I can do to prevent the error?


texts: Should I use SQL Server DMVs with NOLOCK

texts:
I am trying to monitor live performance data and usage, which cannot be tracked with PerformanceMonitor. What are the consequences of reading DMVs in a production live OLTP database? : Eg: sys.dm_tran_locks, sys.dm_os_waiting_tasks, sys.dm_os_performance_counters, sys.dm_exec_connections, sys.dm_io_virtual_file_stats,  sys.dm_exec_sql_text,etc

Should I just utilize WITH (NOLOCK) when querying Dmvs? Would this resolve many of the resource issues? Is there anything such as dirty read rollbacks in DMVs. I know it can exist in applications tables eg where someone submits an order then cancels, etc. Additionally, with NOLOCK, is there higher chance query will continue forever in high volume environment since I am not locking rows, pages, and things will continue be added into DMV TableView?

Thank you,


texts: How to change the SSAS Multidimensional Cube partition location on disk?

texts: I have a SSAS Multidimensional Cube. There is a measure group with multiple partitions (based on year). I need to move old partitions to a different physical (slower) disk. When I create the partition I'm able to choose the physical location of it. But when partition has already been created I cannot find the place where I can change the path to physical disk. Do you know is it possible to change this ?


texts: how do I use sa to access local sql server database with jdbc

texts:
I have a new local sql server database, running hypothetically on port 48000.  In SQL Server Management Studio under the name of the computer\MSSQLSERVER01, Security, Logins I clicked sa, properties and set the password.  I have created a database, mystuff.  sa is not listed under security under mystuff.  I have the connection string
jdbc:sqlserver://MYDESKTOP\MSSQLSERVER01:48000;databasename=mystuff;user=sa;password=123
but I get "Login failed for user 'sa'" when I attempt to connect.  What else do I need to do to setup sa?  Something under mystuff?


texts:
We are at Mongo 3.2. We deployed our first app with mongo without user authentication in db schema.
Now we want to enable authentication at db level but want to avoid application downtime doing the same.
Is it possible in Mongo that we enable this authentication at DB level to avoid anyone to log in and run commands through console.

Application should not take down time.


texts: MySQL output file location - accessing server files

texts:
I am running the MySQL Workbench 6.2 CE on Windows7. I have an output table of 200,000 rows and I need to save it. I will have to repeat this task, so I would like to iterate the following code:

SELECT * FROM mytable
INTO OUTFILE 'mytable.csv'
FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

This saves my output but I cannot access it. It seems it is saved in the server database. I have the server installed in my desktop so I should be able to access it. Do you know how could I find the filepath to my saved csv files?

Edit1: I can save it manually in the Workbench GUI but I would like to find a code-based solution as I have to iterate this process many times.

Edit2: Putting all the file path doesn't work. It gives an error: Errcode: 13 - Permission denied

PS: Sorry if it is a basic question, I am new to MySQL.


texts: PostgreSQL: How does the implicit index work when using "COPY" for loading bulk rows into a newly created table?

texts:
What I know for fast loading bulk rows into a new table is:

Create the table(without creating the index)
Use "COPY" to load data from a file into the table
Create the index

What if I added a primary key when creating the table, according to PostgreSQL: "Adding a primary key will automatically create a unique btree index on the column or group of columns used in the primary key.", will the rows be indexed during "COPY" since the table has an implicit index? if the answer is yes, the "COPY" will slow down?


texts: Error with FUNCTION ( SQL Server 2008 ) - ''Cannot find either column...'

texts:
I have this Function:

CREATE FUNCTION [dbo].[fn_xxx]
    (
    @Nome VARCHAR(100),
    @Descricao VARCHAR(500)
    )
RETURNS @tbDados TABLE
    (
        Qtd INT
    )
AS
BEGIN
    DECLARE @CMD AS NVARCHAR(MAX);
    DECLARE @Tabela VARCHAR(100);
    DECLARE @CampoDescricao VARCHAR(100);

    --- *** Identifica os campos e tabelas
    SET @Tabela = 'tbl' + @Nome;
    SET @CampoDescricao = 'Desc' + @Nome;

    SET @CMD = '
    INSERT INTO @tbDados (Qtd)
    SELECT @Qtd = COUNT(*)
    FROM ' + @Tabela + '
    WHERE ' + @CampoDescricao + ' = ''' + replace(@Descricao, '''', '`') + ''';';

    EXEC sp_executesql @CMD;

    RETURN
END

I'm trying to help developers here, but with no success.

When we execute it ( select dbo.fn_xxx ('processador','teste')) it give us this error:

  Cannot find either column "dbo" or
  the user-defined function or aggregate "dbo.fn_xxx", or the name is
  ambiguous.

I've been searching through the internet, and found some results, but it was all about the code.

can someone help me how to figure out this error in this function?

Thanks

EDIT:

If i execute it with:

SELECT * FROM [EasyTablet].[dbo].[fn_xxx] (
    'processador','teste'
  )
GO

It shows me this error:

  Only functions and some extended stored procedures can be executed
  from within a function.


texts: SQL Server logshipping: Extend the LS_Alert window

texts:
We have a logshipping setup, where the LS_Restore job on the secondary database is switched off every night for the datawarehouse to grab its data. The restore cycle is set to 15 minutes and if LS_Restore would kick in, the datawarehouse connection would get severed - the interface therefore switches off LS_Restore. When the datawarehouse interface is done, the LS_Restore job is turned back on.
However, this window can be longer than one hour (by just ten minutes or there-abouts), and this then triggers the LS_Alert job to raise errors.
How can we extend the range on LS_Alert to, say, 1,5 hours?


texts: How can I efficiently represent inheritance down a tree?

texts:
I have a database with three "levels" of objects: A, B, and C. Table A has around 100000 rows, B has 500000, and C has 2 million.

In a fit of stupidity, I designed the database to use joining tables AB and BC, even though these are 1:m and could have been represented as an ParentA attribute in B and a ParentB attribute in C. I did this because I thought at the time that these might be m:m.

Now, I also have a table D that has a m:m relationship with C. To simplify data management for users, it makes sense to allow them to relate D to A or B rather than directly to each C.

So, I have joining tables DA, DB, and DC, and a joining view DC2 that includes DC as well as the inherited DC relationships from joins of DA-AB-BC and DB-BC. I use a UNION ALL for this, and there's some business logic to prevent users from assigning the same D record at two levels.

The problem is, the performance of DC2 kinda sucks. All of the other tables have appropriate covering indexes for these joins, and the covering indexes are clustered. DC2 does not include tables A, B, C, or D, just the joining tables.

There are also tables E, F, G, and H that are similar to D in how they relate to A, B, and C.

What are some strategies I can use to improve performance of these inherited relationships?

Thought of already:

Making DC2 an indexed view is not an option since UNION is not permitted in an indexed view in MSSQL.
I could make DC2 a table and manage it with triggers, but this would be a pain, and I still have EC2, FC2, etc. to deal with. I can't make it where
Changing AB and BC into attributes of B and C might actually slow things down, since the joining tables are lighter and the main tables aren't joined to DC2 often.


texts: Never Ending OLEDB Wait for Linked Server

texts:
We created a linked server to connect SQL Server to our ERP (running UniVerse not SQL Server). It has worked fine in the past, but now any queries to the linked server just get stuck (for days) with an OLEDB wait type.

This has happened on rare occasions previously, and restarting the SQL Server has always resolved the issue. Now however, restarting the server either does not solve the issue or it fixes it for at the most an hour or two.

We are experiencing the issue in both SQL Server 2016 and 2008 R2.

We are able to query the data source using other tools on the same server and with the same driver as the SQL Server. On one server, we are running two instances of SQL Server 2008 R2. One is able to query the data source, while other gets stuck with the OLEDB wait type. The linked server on both is set up exactly the same way, and they are using the same driver.

We hare tried:

Restarting SQL Server
Restarting the server the data source is on
Different drivers
Recreating the linked server
Querying the data source using a different tool using the same driver (it was successful)
We are able to ping the data source server from the sever with the affected SQL Server
We are able to connect to the data source using Telnet from the affected server


texts:
I'm working on an e-commerce project to sell clothes, to store these products I'm using a table tb_product. These clothes will have the attributes color and size, where I also need to keep track of the stock for each produtct, size and color.

If I use a second table to control the attributes, as I already asked here, I think it's going to add unecessary complexity, because I need to keep track of the stock for the each shirt, each size and each color, all related.

One solution I've seen many stores doing is to add each color as a single product and only control the size attribute. The ony doubt I have is how to link all colors of the some product to show under an option "More colors", like this image example. If I click on any of those options on the tab "Mais cores" it will open a new product page.

This is where my doubt begins, because every other aspect of the item, such as name, price, description, etc.. Is all the same, only the color is changing.

How can I create a link between these products to show them as related colors? The simple solution I could think of is to add a new column to the table, called, for example, group, and all the products would contain within this group. For example:

id | active | group |      name     | description | price | ...
1  |    1   |   A   | T-Shirt white | This is a.. | 9.90  | ...
2  |    1   |   A   | T-Shirt Black | This is a.. | 9.90  | ...
3  |    1   |   B   | Hat yellow    | This is a.. | 9.90  | ...
4  |    1   |   B   | Hat red       | This is a.. | 9.90  | ...
5  |    1   |  null | Custom dress  | This is a.. | 9.90  | ...
6  |    1   |  null | Belt          | This is a.. | 9.90  | ...

CREATE TABLE IF NOT EXISTS `product` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `active` tinyint(1) NOT NULL DEFAULT '0',
    `group` varchar(10) DEFAULT NULL,
    `name` varchar(250) NOT NULL,
    `description` longtext NOT NULL,
    `price` decimal(10,2) NOT NULL,
    PRIMARY KEY (`id`),
);

Is there a better, or any other, approach to this solution?


texts: I have a tinyint column that represents a set of flags. Assuming I wanted to know if a specific bit was set or not, how can I do bitwise AND/OR in a stored procedure?


texts: Why Oracle SQL Developer does not need Oracle client?

texts: I installed Oracle SQL Developer from Oracle's web page. It seems to work without a Oracle client installed on my computer. How is that possible? I also installed Toad for Oracle and it seems to require Oracle client.


texts: Optimal time interval for scheduling of Transaction Log Backup

texts:
I have a database which is currently of size 50 MB. The database is bound to grow by 10 to 15 MB per hour and the data is important. So, I wanted to ask what should be the optimal time interval in which a transaction log backup should be scheduled so that in case there is any data loss then it is as minimal as possible?
Thanks in advance.


texts: How can I remove a channel from replication slave?

texts:
Today my question is about MySQL replication cleanup.

I used mysqldump with the --master-info --all-databases tag and restored it to a new host to be used as a replication slave.

After restore, I see some artifacts of slave information from the master. This is the third host in a replication chain.

I issued reset slave for channel 'xxxxx'; which returned Query OK, 0 rows affected (0.00 sec). When I later query using show slave status for channel 'xxxxx';, I still see information for this replication channel appearing.

How can I cleanup this replication channel such that it never accidentally starts, as well as cleanup the output of show slave status \G to only show the intended replication channel?


texts: What other databases have a feature similar to Oracle's Flashback Query?

texts: I am interested to know what other databases have features similar to Oracle's Flashback Query. It would seem that any database that supports MVCC could have the feature, but which do?  SO has this question for SQL Server, but it seems more appropriate here.


texts: Design database capable of searching for rows where all items in a column are in a given list

texts:
I'm designing a mobile app that will utilise some sort of database with the primary search functionality requiring every element in a list of items in the table to exist in a given list used for searching.
Given the below example table for simplicty:

ID
Owner
Pets

1
Sharon
[Dog, Cat, Rabbit]

2
James
[Cat, Turtle]

3
Natalie
[Dog, Fish, Rabbit]

and the list [Dog,Cat,Hamster,Rabbit,Turtle]

[Dog,Cat,Rabbit] all exist in [Dog,Cat,Hamster,Rabbit,Turtle].
[Cat, Turtle] all exist in [Dog,Cat,Hamster,Rabbit,Turtle].
[Dog,Fish,Rabbit] do not all exist in [Dog,Cat,Hamster,Rabbit,Turtle].

So row 1 and 2 should be returned, but not 3.
I've looked into using an inverted index, which seems useful for returning rows that contain a certain item (e.g. Dog), but I'm not sure if it's capable of checking if all items exist in the search list.
There are approximately 700 unique items (Pets in this example), and 10,000 rows of data (which will increase in time)
I'm looking for a logical design.
Any help would be appreciated.


texts:
Suppose we have a field ("Flag"). How we can shift records of this flag in another field ("ShiftField") by using SQL code?

See the example:

ID   Flag     ShiftFlag
1    0
2    1        0
3    1        1
4    1        1
5    0        1
6    0        0
7    1        0
8    1        1
9    1        1
10   0        1

My efforts already was something like below:

for i=1 to n str =
  "UPDATE HoboData Set ShiftFlag = Wet_Estimated80 WHERE ID = " & i + 1
next i
Dim cmd As OleDbCommand = New OleDbCommand(str, Connect)

but this way is very slow


texts: How much free space do I need for mongoDB? and how to reclaim lost space?

texts:
I ran out of disk space on my mongo database. I saw it and dropped some collections (120 out of 1700 i had). And ran db.compact(...) thinking it would release free space. well, that was a stupid idea.

Anyway, the database is around 800GB, and I dont have any space (~5GB) in the current disk. I symlinked a folder from another disk that has around 35GB and tried db.repairDatabase() (and mongod --repair --repairpath ...), but that failed too saying not enough space. What else can I do? short of getting another 1TB HDD?

Why is even 35GB not enough? Since I dropped the collections, I know I should get another ~50GB free after the repair. Is that going to be enough?

The partition I use for data would have around ~800GB for mongo. How much free space should I ideally have?

--EDIT

I don't know why someone would downvote. What's bad about this question? I read all the documents I can find, and it says I need working set+2GB free space. But no queries are running and mongod stopped. So I'm not sure why 35GB is not enough.


texts: how to download oracle database to a remote server

texts:
I am trying to install Oracle 11g on my client's server. In order to download it to the server, I have followed this :

Logged in to www.oracle.com with my client's username/pass from my desktop.
Accepted the Licence agreement (OTN).
clicked on the download link
When my browser started the download, I copied the download link, and
wget -c "the_download_link"

However, I get "403 Forbidden" error. So how do I download the Oracle db, directly to the server (rather than downloading to my Desktop first and then uploading it to the server) ?


texts: Mongodb does not contain shardkey for pattern

texts:
I've created a database with 3 sharded clusters using the wiredTiger engine and I've enable the storage engine option for replicas.
Now after enabling sharding for collection, importing a csv to mongo tells me :

error inserting documents:
document
{
    _id: ObjectId('55a211a3238512f1bc943318'),
    Accident_Index: "200501LX50106",
    Location_Easting_OSGR: 530140,
    Location_Northing_OSGR: 173660,
    Longitude: -0.128651, Latitude: 51.446928,
    Police_Force: 1,  Accident_Severity: 3,
    Number_of_Vehicles: 2, Number_of_Casualties: 1,
    Date: "10/02/2005", Day_of_Week: 5, Time: "10:22",
    Local_Authority_(District): 9,
    Local_Authority_(Highway): "E09000022",
    1st_Road_Class: 3, 1st_Road_Number: 205,
    Road_Type: 6, Speed_limit: 30,
    Junction_Detail: 6, Junction_Control: 2,
    2nd_Road_Class: 5, 2nd_Road_Number: 0,
    Pedestrian_Crossing-Human_Control: 0,
    Pedestrian_Crossing-Physical_Facilities: 5,
    Light_Conditions: 1, Weather_Conditions: 1,
    Road_Surface_Conditions: 2,
    Special_Conditions_at_Site: 0,
    Carriageway_Hazards: 0,
    Urban_or_Rural_Area: 1,
    Did_Police_Officer_Attend_Scene_of_Accident: 1,
    LSOA_of_Accident_Location: "E01003162"
}
does not contain shard key for pattern
{
    Accident_Index: 1.0,
    Accident_Severity: 1.0,
    Number_of_Vehicles: 1.0,
    Number_of_Casualties: 1.0,
    Date: 1.0,
    Road_Type: 1.0
}


texts:
I am building a blog website with php.

I have a table with a column SET(Y,N,X,A).

And I want to categorize each value.

for example:
A post has labels: News, World, Tropical... selected from the SET of values in the database.

And now I want to get every post from the database that has "News" as label.
I've been really struggling 2 days to find an answer!!!


texts: MySQL replication just hangs, then Database wont shutdown

texts:
I have recently set up some new MySQL Databases on a remote site. They are all 5.5.47, running on Debian Servers.

They are set up as one Master with multiple slaves. The Master is itself a slave from our main site.

But periodically replication on one of them (never the same one, and only one at a time) gets stuck. The servers at my main site which have been running for years, are fine.
The config of the servers at the new site is identical to the main site.
The servers (VM's) are the same as the main site.

I can see the relay logs continuing to increase in size, but the Exec_Master_Log_Pos value just sits there.

The error logs are of no use as I have log_warnings=0 due to our website generating hundreds of unsafe statements every second.

Everything else continues to work fine, until I execute a STOP SLAVE command which just hangs. After that all other related queries (show slave status, start slave etc. also just hang.)

I've tried killing the connections / queries, but it makes no difference with each of them sitting there saying 'killed', but not completing.

I've checked disk space, and that is always fine. I can log in to MySQL via the terminal and run basic commands (until I run STOP SLAVE).

I have compared everything I can think of to the other servers, but there is never any difference.

If I try and shutdown the database (mysqladmin . . . shutdown), it hangs at Event Scheduler: Purging the queue. 0 events

Finally I resort to killing the mysqld_safe process (sudo kill 12345), which forces it to restart, and goes into crash receovery. After that it seems to be fine.

Is there anything else I can check to try and find out what is happening.


texts: Finding the largest island size (redshift)

texts:
Current sql code will sum up all the max island sizes into one. I'm trying to only find what the max island size is.

 select deviceid, grp -- min(readdate) as mn, max(readdate) as mx
       from (  select deviceid, readdate, sum(case when prev_rd >= readdate - interval '1 hour' then 1 else 0 end) over (partition by deviceid order by readdate rows between unbounded preceding and current row) as grp
               from (
                    select deviceid, readdate, lag(readdate) over (partition by deviceid order by readdate) as prev_rd
                    from metersWithExcess
                    order by readdate)
            ) tt
            group by deviceid, readdate, grp
           -- having mx > mn + 1 * interval '1 hour'

Is there a way to return back the deviceid with it's max size? I am trying to find the longest continuous hourly readings

the data looks like this:

    deviceid    readdate
91428   2018-08-27 18:04:42
91428   2018-08-27 17:04:42
91428   2018-08-27 16:04:42
91428   2018-08-27 12:04:42
91428   2018-08-27 11:04:42
91428   2018-08-26 20:04:42
91428   2018-08-26 17:04:40
91428   2018-08-26 16:04:40
91428   2018-08-26 15:04:40
91428   2018-08-26 12:04:40
91489   2018-08-26 06:00:49
91493   2018-08-27 06:58:44
91511   2018-08-27 12:57:01
91511   2018-08-27 10:57:01
91511   2018-08-27 09:57:01
91511   2018-08-26 23:57:01
91511   2018-08-26 22:57:01
91511   2018-08-26 20:57:01
91511   2018-08-26 13:56:59
91511   2018-08-26 12:56:59
91511   2018-08-26 10:56:59
91547   2018-08-26 16:59:07
91547   2018-08-26 15:59:07
91547   2018-08-26 14:59:07
91547   2018-08-26 11:59:07
91547   2018-08-26 04:59:07
91582   2018-08-27 12:01:45
91582   2018-08-26 19:01:45

in this example: 91428  would have a max size of 3
The other devices would be similar in that they will contain the max number of continuous hours


texts: Recreated database or clear all data on import to it in MySQL

texts:
How to recreate db during import of the dump?
By recreate, db drop or deletion of its data is meant.

mysql db -u root -p < dump.sql

It's possible of course to drop and create db manually or automate it using bash, but as import is run frequently, i would like it to be done automatically via mysql command if it's possible.


texts: Permissions to see and script objects, but not alter them?

texts:
I am a software developer.

I have a production database that I need full ddl read access to, but not any ddl write or execute access.

Is there a script I can send my DBA that will grant me full ddl read access without allowing me to affect the database?


texts: Default value of serial fields changes after restore

texts:
Today I found a strange behavior after restoring a PostgreSQL database: the schema of all serial fields default values are trimmed out.

For example:

CREATE TABLE testschema.testtable
(
   id serial,
   name character varying(255),
   CONSTRAINT pk_testtable PRIMARY KEY (id)
)
WITH (
  OIDS = FALSE
)
;

SELECT a.attnum, n.nspname, c.relname, d.adsrc AS default_value
FROM pg_attribute AS a
JOIN pg_class AS c ON a.attrelid = c.oid
JOIN pg_namespace AS n ON c.relnamespace = n.oid
LEFT OUTER JOIN pg_attrdef AS d ON d.adrelid = c.oid AND d.adnum = a.attnum
WHERE a.attnum > 0
  AND n.nspname = 'testschema'
  AND c.relname = 'testtable'

The id's default_value is nextval('testschema.testtable_id_seq'::regclass).

After restore, default_value changes to nextval('testtable_id_seq'::regclass) and INSERT's start to fail as the sequence can´t be found on it's schema.

Backup
$ pg_dump -F c -Z 9 -b -h localhost -U postgres -f backup dbname

Restore
$ pg_restore -U postgres -h localhost -l backup > backup.list
$ pg_restore -U postgres -h localhost --disable-triggers -O -d dbname -S postgres -Fc -L backup.list backup

Is this some backup/restore problem? What am I doing wrong? BTW, PostgreSQL version is 9.1.3 x64 running on Windows (dev machine), but can be reproduced on Linux too.


texts: Why use foreign keys if cascade action is not need?

texts:
I'm testing some queries with MySQL and tried to create two tables were there should be a reference between IDs. Something like:

CREATE TABLE user
(
  id int(11) UNSIGNED AUTO_INCREMENT,
  name varchar(30) NOT NULL,
  PRIMARY KEY id
);

CREATE TABLE photo
(
  id int(11) UNSIGNED AUTO_INCREMENT,
  user_id int(11) UNSIGNED,
  filename varchar(50) NOT NULL,
  PRIMARY KEY id
);

A query like:

SELECT p.filename, u.name FROM user AS u
INNER JOIN photo AS p ON u.id = p.user_id

Would result in all records of photos and their user's name.

So if it is possible to create a relation on query, why would I use foreign keys and define NO ACTION, considering I want to keep a history of photos, if there is no need for that? Foreign keys helps on performance or just make data more organized?


texts: select users who do something on each date in sql server

texts: I have a table with 3 columns - user_id, building_id, datetime where each record is an access to a specific building and hence users can accesss a building many times. I need to select each user_id from each day who has accessed a specific building. It would seem like I need to cast the datetime to just a day and do a distinct across user_id, building_id and day. How would I do this in a modern version of SQL Server?


texts: Security implications of backing up to UNC path

texts:
I have a few servers which backup to a Data Domain. This is accessible via a UNC path, i.e. \\datadomain\SqlBackups\ and all that seems to be done is we give the SQL Server access to the data server via Firewall rules, and we give SQL DB Service Account full access to the SqlBackups folder.

The server is configured to map the Y:\ drive to this location so backups go to Y:\<InstanceName>\<DatabaseName>.

This works until something happens with the drive mapping. For example, sometimes on reboot the mapping is lost so backups fail.

I just want to backup to the UNC path which I've tested and it works without fault. However I'm told there are security implications for this, something about making the UNC path visible to potential hackers... which doesn't make sense to me as you can just easily find out where a mapped drive points to.

Can anybody shed any light on this from a SQL Server / Windows Server Security point of view? What's so bad about backing up the UNC path?


texts: Load data from flat file into oracle table

texts:
I created a new database, then created a table "users". Now, I'm trying to load data to this table from a flat file called 'users.tbl', that has some records  with the following format:

1|john|email@email.email|active

I'm using SQL Developer for this, i tried the following commands.

load data infile `"C:\path"
insert into table users
fields terminated by "|"
lines terminated by "\r\n";

But the data is not loaded and no errors are shown. Do you see why its not working? Or How can i load the data in the flat file to Oracle table?


texts: Copy files using script to a network share

texts:
Is there a simple script I can use to copy files in a particular folder to a network share? This is for use with SQL Server Agent so I can run it as a step after backups.

Thanks


texts:
This is an output from sp_whoisactive. Should I be worried about the sleeping spids with high cpu numbers and an open transaction? The sql_command value is the same as sql_text. CPU and reads do increase over time.

I have hundreds of these at any given time...would this impact CPU?


texts: Discard tablespace of temporary innodb table in MySQL


texts: Why does the READPAST hint cause indexed views to be ignored?

texts:
I am investigating using the READPAST hint to reduce resource locking in our application's financial subsystem.

It seemed like a good way to go because financial transaction records are only ever added, never updated or deleted. The only rows that would ever be skipped are brand new rows inserted inside of a transaction; they effectively don't exist to the outside world until the transaction is committed.

However, I noticed worse performance on queries that utilize indexed views that I had put the READPAST hint on. Comparing the query plans, it looks like with the hint, the query optimizer chooses to not use the indexed view and instead falls back to treating it like a regular view.

I'm not sure why that would be; I imagine indexed views to be like any other index in that keys can be locked during operations and adding READPAST would work similarly.

SELECT TOP 1 isa.InvoiceId
FROM Financial_InvoiceSummaryAmounts isa WITH (READPAST)
WHERE isa.TotalOwedAmount = 0.0

SELECT TOP 1 isa.InvoiceId
FROM Financial_InvoiceSummaryAmounts isa
WHERE isa.TotalOwedAmount = 0.0

Adding a NOEXPAND hint as well does seem to work, but I am interested in learning more about possibly why READPAST caused the query optimizer to make that choice in the first place (as part of a full answer).


texts:
This is the situation:

I have a table, with name TABLE_1 I would like delete TABLE_1 and then create another table with the same name and some of its columns.

TABLE_1 has records, but I need to pass it to the new table TABLE_1

What is the the most effective way to to this?

UPDATE

old TABLE_1:
    ID int(11) NOT NULL
    ACTIVE int(11) NOT NULL
    SENT_DATE DATETIME
    SENT INT(11) NOT NULL
    DESCRIPTION VARCHAR(50)

new TABLE_1:
    ID_TABLE_1 int(11) NOT NULL AUTO_INCREMENT
    STATUS TINYINT(1) NOT NULL DEFAULT(1)
    SENT_DATE DATETIME
    SENT TINYINT(1) DEFAULT(0)

In the ACTIVE and SENT columns are stores just 1 or 0. The relation between columns are (from old to new):

* ID -> ID_TABLE_1
* ACTIVE -> STATUS
* SENT_DATE -> SENT_DATE
* SENT -> SENT


texts: sp_addserver - Where does it add in which system table. if I use this in my current server configuration will it change the name of my server and how will it affect ?


texts: Is it possible to add Postgres extensions to AWS Aurora?

texts:
I am trying to futureproof a Postgres database so it can be transferred to AWS Aurora.

I am using the guide extension uuid-ossp.

Is it possible to add PostGres extensions to AWS Aurora?

In particular, I am interested in whether the extension uuid-ossp can be used in Aurora.


texts:
I have a very large table that needs to be partitioned by two columns. One column is an ID and partitioning is simple enough. The second partitioning should be done daily on the date column. I need to keep data of up to 30 days in this table and partitions older than 30 days need to be dropped. What is the best way to do the partitioning for this scenario.

Thanks


texts: What is the purpose of PGBouncer's parameter server_reset_query?

texts:
Recently I started learning about pgbouncer, but there are some things I do not understand about the parameter server_reset_query.

Question 1: What is the main purpose of server_reset_query?

Question 2: There are three modes of the pool_mode of pgbouncer: session ,transaction  and
            statement  mode. When the pool_mode is session, we usually set the values of
            server_reset_query to 'DISCARD ALL'. When the pool_mode is transaction
            we usually set the values of server_reset_query to ''，

I do not know the details about this, so can anybody explain this?


texts: LogShipping frequency for backup/copy/restore

texts:
I wish to create a LogShipping job that runs every 5 minutes. What is the best way to do this so that the backup/copy/restore does not collide?

Maybe Sql server knows how to handle this, but I was concerned if I put 5 mins into all of the scheduled times, that I would get some sort of conflict.

i.e. would this create a problem?

Primary server: starts backup at 9am
Secondary server: starts copy at 9am
Secondary server: starts restore at 9am

Would staggering them be a better idea?

Primary server: starts backup at 9:00am
Secondary server: starts copy at 9:01am
Secondary server: starts restore at 9:02am

The database is only approx 200mb in backup size and the two sql servers are running on the same local network.


texts: Creating additional indexes on a table with a columnstore clustered index

texts:
According to this article, a table with a clustered columnstore index can have one or more additional non-clustered indexes. However, when I try to add a non-clustered index on a table with a CCI, I get the error shown in the image below:

Am I overlooking something, or misinterpreting something? I'm using SQL Server 2016 with SP1.


texts: SQL Server 2017 CU3 will not add read-scale replica. Thinks there is a cluster

texts: Using SQL Server 2017, CU3. I successfully create a 2 node cluster=NONE availability group. Both replicas are VMs local to the site. When attempting to add another replica that is off-site, I get sql error 19417, which basically says my replica add failed because the operation is not supported when the AG is running under a Windows Server Failover Clustering context. However, I am not using a cluster, that is the point of the cluster=none AG. I have had trouble before with CU1, and went backwards to the RTM version. That is my next step. Posting in case someone has seen something similar.


texts: The actual cause of transaction log full under below scenario

texts:
in one of the instance, it shows

The transaction log for database 'DB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

The instance has at least 100g harddisk empty storage and the transaction log allowed 2,097,152MB (2T) to grow, growth rate at 10%.

The database recovery mode is FULL (at least it displays FULL in the SSMS)

In this case, what can cause the exception of full transaction log?

When the transaction log have enough physical storage to grow and the log size is not bigger than the upper bound


texts: innodb , mysql Indexes difference when adding index

texts:
When I am altering one of my MySQL table to add an index I am getting below error in error log.

  Table a/b contains 4 indexes inside InnoDB, which is different from the number of indexes 3 defined in the MySQL

What would be the exact root cause of this issue. Is the table not closed properly? I have read altering the table may remove the extra index which is causing the issue and we may create it later. But I want to know the exact reason why this is happening.
I am assuming inside innodb 4 indexes means those indexes definition updated in table .frm file and .ibd file along with ibdata1. But what does it mean by "inside mysql"?

Edit (add CREATE)

CREATE TABLE b (
    p datetime DEFAULT NULL,
    q datetime DEFAULT NULL,
    r bigint(20) NOT NULL,
    s varchar(25) DEFAULT NULL,
    t varchar(50) DEFAULT NULL,
    u datetime DEFAULT NULL,
    v varchar(50) DEFAULT NULL,
    w varchar(20) DEFAULT NULL,
    x longtext,
    y longtext,
    z bigint(20) DEFAULT NULL,
    k bigint(20) DEFAULT NULL,
    PRIMARY KEY (r),
    KEY e (z),
    KEY f (u),
    KEY g (s)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


texts: Server with mariadb sometimes dies under high load

texts:
I have a dedicated server i7-4770 with 32GB RAM. On server installed CentOs 7.8, MariaDB 10.5.8, PHP 7.4.14, Nginx 16.1. All tables in the database are InnoDB.
When the load on the database increases significantly and I see more than 20,000,000 requests per hour in PHPMyAdmin, the server dies unexpectedly. After I reboot the server from the hosting company's control panel - the server is working fine. But as soon as the load on the database becomes large, the server dies again.
In the logs of MariaDB 10.5.8, before the server dies, only 1 error appears:
[Warning] Detected table cache mutex contention at instance 1: 22% waits. Additional table cache instance cannot be activated: consider raising table_open_cache_instances. Number of active instances: 1.

I think the reason for the death of the server is in her. But the problem is that when I specify table_open_cache_instances = 8 and restart the MariaDB, the request
SHOW VARIABLES LIKE 'table_open_cache_instances'

gives me a value of 1. After reboot server value also remains 1. Other values such as max_connections are set correctly.
My /etc/my.cnf.d/server.cnf (=my.cnf)
slow_query_log      = 1
slow_query_log_file = /var/log/mariadb/slow_queries.log
long_query_time     = 1

max_connections = 836
key_buffer_size = 256M
read_buffer_size = 16M
join_buffer_size = 16M

query_cache_size = 0
query_cache_type = 0
query_cache_limit = 1M

tmp_table_size = 512M
max_heap_table_size = 512M

innodb_buffer_pool_size = 22G
innodb_log_file_size = 512M

innodb_flush_log_at_trx_commit = 0

thread_cache_size = 512

table_open_cache = 207000
open_files_limit = 207000

table_definition_cache     = 1400

table_open_cache_instances = 8

How can I solve this problem? How do I edit my MariaDB 10.5.8 config?


texts: Does SQL Server have a reference partitioning equivalent?

texts:
Oracle 11g has introduced a new feature that is called Reference Partition.

Using reference partitioning, a child table can inherit the partitioning characteristics from a parent table.

http://www.orafaq.com/wiki/Reference_partitioning

Is there an equivalent feature in SQL Server?


texts: Alter of stored procedure and execution performance

texts:
I would like to understand how an alter of a stored procedure without modifications can impact performance. I had a situation where a production environment had its database schema updated and a particular stored procedure (that referenced a table that had schema changes but required no modifications) that normally ran in minutes went to hours.   The procedure does consolidation work and has to query a table with about 800 million records and this table was modified as part of the schema update (table swap).  We found that when we made a copy of the procedure and executed it the normal execution time returned but the original procedure performance did not improve.  The developer executed an alter on the base procedure, with no changes, and the execution time returned back to normal and they asked why.    I'm not clear what resolved our issues but I'm more concerned how to keep it from happening again.  The procedure didn't have an explicit recompile statement so I'm not sure it was implicitly recompiled and the cache plan replaced.

Again, I am just trying to understand the mechanics of how altering the procedure resolved the issue so I can plan accordingly for any schema changes.

I appreciate your input!


texts:
Show create view viewName does not display correctly formatted text. How can I get the correctly-formatted SQL?

For example, I created a view like create view v1 as select * from mysql.uer, but it return like this:

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER
VIEW v1 AS
    select
        mysql.user.Host AS Host,
        mysql.user.User AS User,
        mysql.user.Password AS Password,
        mysql.user.Select_priv AS Select_priv,
        ... ...,
        mysql.user.Grant_priv AS Grant_priv,
        mysql.user.References_priv AS References_priv,
        mysql.user.Index_priv AS Index_priv,
        mysql.user.Alter_priv AS Alter_priv
    from mysql.user

How can I get my original definitions?


texts: CommandLine for Exporting All MS SQL Tables to CSV

texts:
On my Mac, I have a docker container running an Ubuntu version of MS SQL.  I have a DB running in it and I can access it via Azure Data Studio, and I can view each table individually and export it to CSV using the GUI, but I was hoping to find a way to export all tables to CSV en masse.  I have googled it endlessly but have yet to find the command line SQL commands to do this export, and Azure Data Studio seems to only offer one-table-at-a-time exporting.

Can anyone help me out here?


texts: Extension created for public schema not available within database

texts:
So, I create the citext extension for schema public and I can see it's there, but then it's not available within the database in that schema. The database is in public as well, because it's created from the postgres user and no schema is specified at the creation. Here's a screenshot with some more data:

So, you can see that the extension is there, but once I connect to the database, it's not available anymore and I basically have to install it within the database. Any ideas how to make it available initially for the database as well?

PgSQL v9.6


texts: What is the best way to represent the relationship between Team and Player in an ERD?

texts:
I am working on designing an entity-relationship diagram (ERD) for a football system, I stopped in the relationship between the Player and Team entity types, as we know a player plays for only one team and a team have many players, so the relationship will be:

Team 1…* Player

But, how can I represent this relation when a player can play in more than one team? In real life, a player has a contract with a team, when that contract ends, the player may continue with the same team or with other team, so the relation will be

Team *…* Player, or
Team 1…* Contract *…1 Player

Questions

Is my understanding is well?
Do you have any suggestions about the two scenarios?


texts: SQL to find nearest location in one table from locations in another

texts:
I have a table like this, that contains a few million locations (POINT):

CREATE TABLE NewLocations(
    [NewName] [varchar](20),
    [NewLocation] [geometry]
)

I have another table like this, that contains a few hundred existing locations (POINT):

CREATE TABLE SourceData(
    [OriginalName] [varchar](20),
    [originalLocation] [geometry]
)

What I want to do is construct a sql select that for every row in SourceData find an entry(ies) in NewLocations that is within a few metres, in other words I'm trying to find a NewName either in the same place or near enough the same place as each originalname in SourceData.

e.g.

Name    NewName
loc1    loc56
loc2    NULL

I'm not bothered if there are multiple rows, I can remove them later, and there need not be a match.

With a single created point I can call STDistance and find a match, but I don't quite know how to construct the SQL for this to work with the table. I guess I could also turn it into a function, but I figured there might be a way to do it wholly in sql.

thanks for any help.


texts: ORA-00911: invalid character error in insert statement

texts:
I created a table called TROUBLE in Oracle Database 10g Express Edition by this command

create table TROUBLE (
City VARCHAR2(13) NOT NULL,
SampleDate DATE NOT NULL,
Noon NUMBER(3,1),
Midnight NUMBER(3,1),
Precipitation NUMBER
);

The table got created. When I tried to insert a record, there is an error

ORA-00911: invalid character

My insert statement was

insert into TROUBLE values
 ('PUNE' , '21-MAR-15' , 39.9, -25.5, 3.6);


texts:
I tried to mark disk and got Fail!!!

[root@oradbserver3 Server]# /etc/init.d/oracleasm createdisk DISK1 /dev/sda2

Marking disk "DISK1" as an ASM disk:                       [FAILED]


texts: Oracle database creation - recording response file option not available

texts:
I try to create database with dbca assistant (v 12c) and I would like to firstly use the graphical guide in order to save the response file for later.

I checked the advanced mode during the step 2, I passed all the steps and then I went to the Summary step : I don't have the option "save response file" as you can see on my screenshot

I'm wondering if it's because this option is simply not available if we use advanced mode only ?

If you have any suggestions...


texts: Postgres Search Path between connections

texts:
I'm a noob to Postgres, and am setting up a multi-tenant database using one schema per tenant.

To target each tenant I need to set search_path accordingly.

I'm worried about accidentally executing something intended for one tenant on another tenant, if the wrong search path is picked up somehow.

I will be executing Functions from .Net code (via NpgSql). The functions will reside in the public schema. Each function will have the search path set inside it, like so:

BEGIN
 PERFORM set_config('search_path', tenant, true);
...
END

Is there any danger, via concurrency or connection pooling, that the search paths for two calls might be shared, for instance if the 'true' parameter was forgotten or set to false.

I really want to be sure that there's no danger of data from one tenant crossing over in to another.

Thanks in advance.


texts: Does PostgreSQL have some ability to "compare" a backup with the live database automatically and neatly?

texts:
I make full backups of my PG databases each day. I store these pg_dumped archives/blobs in a dir on the same computer, as well as on offline disks, up to a year before they are culled.
I'm constantly worried that my "live" data is being either deleted or modified by accident or through malice. It would be extremely useful to be able to do something like:
pg_compare.exe "path to pg_dump of my database X" "Database X" --deletes --updates

And then it would return a JSON array of any records found in the dump but not in the live database, as well as any changes in existing data found between the backup and the live database. (It would not care about new INSERTed rows, of course.)
Basically (translated to human form):
1. The row in table "invoices" with "id" 4825 has changed its numeric "amount" column cell from "49" to "499".
2. The row in table "bookkeeping" with "id" 13459 is no longer present.
3. ...

At the very least, this would help against honest mistakes and severely help against my paranoia and fear. As I'm working in pgAdmin 4, for example, I'm always worried that maybe I had selected a row out of sight or something as I press that "trash can" icon and then the "accept changes" icon. I don't want to lose data.
It's important that this can be done in an automated fashion, constantly behind the scenes (or maybe once a day if it's resource-demanding). It must not involve manual creating of databases and a bunch of complex custom SQL queries etc. And please, no extensions! I hate extensions.
Is there such an ultra-useful feature built into PG?


texts:
I'm working with some tables/columns that were renamed at some point, but the names of the primary/foreign keys are still based on the previous names.

In SSMS is there any (preferably automatic) way to reset the name of a key to what the default name would be if it were newly created?


texts: Getting ORA-00947: not enough values error while inserting date into table

texts:
I have created a table

    create table Times (
departure date not null,
arrival date not null,

)

and I need to insert date and time in both columns

    insert into Times(departure, arrival)
values (to_date ('2018-05-02 12:40', '2018-05-02 15:08'))

I get the following error

ORA-00947: not enough values

Is there a way to solve this problem?


texts: Way to run SQLCMD without storing password in text format

texts:
Is there a way to run sqlcmd from a batch file where I don't want to store the password in plain text format. I have done some research and found the system variables method(store password in system variables) but the requirement is not to store a password in text format anywhere in the system.

The user that is logging into the system should not be able to see the password for sql server and still run the batch file containing sqlcmd and restore command

Any help, pointers are appreciated.


texts: How to drop materialized view logs if underlying log table ($MLOG_...) is dropped?

texts:
In an attempt to work around another problem, I have mistakenly dropped the underlying tables for a materialized view log, and now I can neither drop that log nor the MV relating to it. Our DBA says it's because those tables were manually dropped.
Is there a way of either forcing the logs to be dropped in the absence of those tables or else recreate those tables?

Version Information:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0  Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production


texts: SQL Server : low disk space impacts CPU?

texts:
Is there any relation between disk space and CPU utilization?

We experienced the issue with very high CPU utilization on SQL Server 2008. At the same time, we were in lack of free space on the server. Once we cleaned up some space - CPU utilization was back to norm.

Is this a coincidence or there is a documented explanation for this?


texts: Approach to save many to many relationship in case select all values

texts:
I have Countries mapped to Products as many to many relationships. As a result a new mapping table is created with product_id and country_id. If a Product is saved then multiple country_ids are stored in new row against product_id, which is fine in case if there are 20 or 30 countries chosen.

But if there is select all option which contains almost 300 values, is it good to go with this approach? or I should use a flag to identify that it is for all values(Countries).


texts: Extracting two particular values from a multi valued column

texts:
I have a table SUBSCRIBER and in it the data is stored in a way that bars is a multi-valued column . Different values of bars are stored in different rows with the ID as following :

ID BARS
1 , BAR1
1 , BAR2
1 , BAR3
2 , BAR4
2 , BAR3
3 , BAR3
4 , BAR1
4 , BAR4

OUTPUT for the data set should be the following:

1,BAR1,
2,,BAR4
3,,,
4,BAR1,BAR4

So the query should be extracting 2 particular BAR values from the multi valued column(BARS) and gives the result for a report based on the conditions as following:

ID,BAR1,BAR4 : WHEN BOTH BAR1 AND BAR4 EXIST
ID,BAR1,, : WHEN BAR1 EXISTS AND BAR4 IS NOT PRESENT
ID,,BAR4 : WHEN BAR4 EXISTS AND BAR1 IS NOT PRESENT
ID,,, : WHEN BOTH BAR1 AND BAR4 ARE NOT PRESENT


texts:
I have a big Postgres server with 2 Tb of data that has been created with the wrong locale.
How can I correct it, without recreating the whole server with pg_dump and pg_restore.  It would take a longtime to do so, and would need a lot of disk space.


texts: Restore RMAN Level 0 without any archivelogs?

texts:
I have a level 0 backup, and control files, but I don't have the archivelogs. And I'm not using rman catalog.

I found ways to restore a L0 backup, if you have at least the first archivelog after the L0, or until the first missing archivelog, but yet I don't find how to restore the database as it was when the L0 was created.

Is it possible to restore just the L0?


texts: Cannot get SQLCMD to work regardless of what I try

texts:
Regardless of what I do, I cannot get SQLCMD to work. I am trying to run a script that is 200mb+ in size and cannot be run from the sql server application so I need to do this via SQLCMD, however whenever I try to login or run the script it immediately closes.

I've followed the sqlcmd utility by msdn.

I've tried to opoen it via trusted command SQLCMD -E
I get:

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Named Pipes Provider: Could not open a connection to SQL Server [2]. .
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

I've tried via explicit server, username and password declaration:

C:\Users\MyUserName\Documents>SQLCMD -S DESKTOP-1N3OD6V\SQLEXPRESS -U DESKTOP-1N3OD6V\MyUserName-P MyPassword
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed for user 'DESKTOP-1N3OD6V\MyUserName'..


texts: How to track only DDL statements executed in MySQL

texts:
Can we track only executed DDL statements in MySQL but not DML statements. I dont want to use either binlog/general log to enable. I just wanted to track only DDL statements.

I had gone through the below link in MySQL Site. But in the site, it is not mentioned how to enable/disable this log.

http://dev.mysql.com/doc/refman/5.6/en/ddl-log.html

I added below line in my.cnf and restarted MySQL service. MySQL got restarted successfully, but I could not find any DDL log in the below path.

ddl-log=/var/log/ddl.log

Is this feature released or will it be released in future.


texts: Is it possible to backup with dump or rman from Oracle to MS Sql Server?

texts: We need to move some tables and schemas from an Oracle database to an MS SQL Server database. We have only Oracle dump files. We import them into an Oracle database. Then we need to use a replication tool to move the data from Oracle to the SQL Server. Is it possible to bypass the replication tool for the moving task? Is it possible to move data from Oracle to the SQL Server using Oracle RMAN or DUMP files? Thanks...


texts: What is the most effective way to compress and store a SQL Server backup?

texts:
I've been doing some testing of different methods for compressing and storing SQL Server  backups (using SQL Server 2008 R2 Enterprise edition), and I'm wondering what the most effective compression algorithm is for long term storage of those backups, outside of SQL's internal compression algorithms.

I'm not worried about the physical storage or tape drives or anything, just trying to turn our 3TB of data and log files into the smallest single file I can.

So, for example, would a .zip or .7z? Or are there too many variables within my database to be able to accurately estimate what will be the most effective and I'll just need to do some tests? Or is SQL Server's internal compression the best I'll get?


texts: how do I determine why I cannot drop a schema

texts:
A schema was created with the name of a windows user [DOMAIN\Foo]. I receive this error message when attempting to drop the schema.

DROP SCHEMA [DOMAIN\foo]

Msg 15151, Level 16, State 1, Line 8
Cannot drop the schema 'DOMAIN\foo', because it does not exist or you do not have permission.
The statement has been terminated.

I have already made sure that the user does not have authorization on the schema, and transferred ownership to dbo

alter authorization on schema::[DOMAIN\foo] to dbo

My windows login is a member of the server role sysadmin.
My user mapped to the database is a member of the database role db_owner.


texts: Need Help in DFD Diagram for online hotel booking system

texts:
Hi friends,
          I am creating a data flow diagram for online hostel booking system. not sure if i created a correct diagram or not any help would be appreciated.

thanks in advance


texts:
I'm trying to install latest version of PostgreSQL and the install keeps running into the problem of

  Error running the Post-Install Step. Installation may not complete correctly. The data cluster initialization has failed.

I looked online and found a lot of solutions to the problem and I've tried most of them. However, I keep getting the same error, so I looked at the Install-Log and it say that Icacls doesn't exist, which seems new.

  Called IsVistaOrNewer()...

    'winmgmts' object initialized...

    Version:10.

    MajorVersion:10

  Ensuring we can write to the data directory (using icacls) to  CORPORATE\pjy002:

    Executing batch file 'radC235D.bat'...

    'icacls' is not recognized as an internal or external command,
    operable program or batch file.

Some other important things.

Downloading PostgreSQL on a virtual machine
Am an administrator of said virtual machine or at least can get adminrights
Using Windows 10
Installing PostgresSQL 10.4


texts:
At this moment we have a MySQL table that for BC reasons we continue on updating. We use it to storing tracking data. The problem is we need to do some verification prior to the insert, which consists in selecting tracking data (on the same table) of interest. There is a high insert rate and therefore high select rate as well.
In my experience such queries (high select/high insert on the same table) has caused the database to be overloaded with connections (my guess, from waiting for lock) when used for different type of data. For which the problem has been fixed by removing the select.
The database showed a similar high connection rate recently for a short 15 minutes due to that select query on the tracking data. I am confident it will happen again.
I am curious:

to know what could be the exact reason for this high connection rate. From what I saw the table is not locked by the select.
What could be a potential solution to this, my initial thought is that we aren't supposed to be selecting timeseries data in that fashion in the first place.

Edit:
During that period there were 5 calls per seconds for that query, I know not a lot, provisionned is db.r5.large, 2 vCPU 10 ECU, 16 GB Memory.
CREATE TABLE `Telemetry` (
  `TelemetryId` int(11) NOT NULL AUTO_INCREMENT,
  `DeviceId` int(11) NOT NULL,
  `UnitId` int(11) NOT NULL,
  `GnssLatitude` decimal(10,8) NOT NULL,
  `GnssLongitude` decimal(11,8) NOT NULL,
  `MatchedLatitude` decimal(10,8) NOT NULL,
  `MatchedLongitude` decimal(11,8) NOT NULL,
  `Gnss` json NOT NULL,
  `Matched` json NOT NULL,
  `SpeedLimit` json NOT NULL,
  `VehicleData` json DEFAULT NULL,
  `Comment` varchar(500) DEFAULT NULL,
  `ShadowHash` varchar(64) DEFAULT NULL,
  `RecordDate` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `CreationDate` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `ModificationDate` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `DeviceTelemetryId` varchar(10) NOT NULL,
  PRIMARY KEY (`TelemetryId`),
  UNIQUE KEY `unique_telemetry_per_device_index` (`DeviceTelemetryId`,`DeviceId`),
  KEY `Telemetry_Unit` (`UnitId`),
  KEY `Telemetry_DeviceUnit` (`DeviceId`,`UnitId`),
  CONSTRAINT `FK_Telemetry_Device` FOREIGN KEY (`DeviceId`) REFERENCES `Device` (`DeviceId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_Telemetry_Unit` FOREIGN KEY (`UnitId`) REFERENCES `Unit` (`UnitId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query is
SELECT `DeviceTelemetryId` FROM `Telemetry` WHERE `DeviceId` = ? AND `DeviceTelemetryId` IN (?)


texts: Database Design: Include Question Answer and Comment tables into Post table

texts:
I'm designing a database. This system has question, answer and comment.

Question has some type of questions. In each type, there some differences so they're have some difference fields.
Answer and comment have some slight differences too.

But both those three tables have some same things: UserID, content, DateCreated, VoteUp, VoteDown, Share ...

With those similar, should I combine question, answer and comment into one table name Post?
Has an additional field to tell which kind of this post?
Which advantages and disadvantages for this way?


texts: SELECT * WHERE VarCharColumn IN (...) Optimization

texts:
I have a list of 3000 strings, and I am passing them (twenty at a time) into a parametrized IN clause. It definitely isn't getting the results I would like to see ~ 500ms per execution.

The column is an index. Do you know a better way than this:

SELECT * FROM [ohb].[dbo].[MasterUrls] WITH (NOLOCK) WHERE Hash
IN(@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14,@p15,@p16,@p17,@p18,@p19)

A list of 3000 takes between 3 and 5minutes. I really need that down to around 30 seconds. Is this possible?

I am using MSSQL 2008 R2 on a server with 24 gigs of RAM, and dual 8core NUMA Xeons @2.4Ghz running on a 6HDD (@15k/rpm) RAID 10 ISCSI.

The table has 1.4M rows, and the index is a non-clustered index.

Execution plan shows the index scan as 90% of the total execution.


texts: How do I dump spatial types, like point, with their SRIDs in MySQL?

texts:
I am testing the point data type on Mysq 5.7 below is my java insert query.

String insertQuery1 ="INSERT INTO gtest3 SET " +
      "gDateTime='2017-07-16 01:00:00'"+
      ",vehicleID=1"+
      ",alarmTypeID=1"+
      ",latlong=Point("+floatLatitude+","+floatLongitude+")";

The values are inserted accordingly but when I got to the row it shows something like GEOMETERY -25B but when I press to edit it show me this 'POINT(5.19878 100.492)',0 . I would like is this correct with the extra 0 at the back. Can this values be used for later spatial functions.


texts: How to UPDATE MAX and MIN of a GROUP BY?

texts:
I have a SELECT query of a table with GROUP BY

id     col1     category
1      some     1
2      x        1
3      another  1
4      x        2       // should be removed
5      some     2
6      x        2       // should be removed
7      some     3
8      some     3
9      x        4       // should be removed
10     some     4
11     x        4
12     some     4

I want to SELECT or UPDATE to remove a row if the first or last (with id order) is x (a given value). In other words, I want to skip the first and last row of a category IF col1='x'.

I cannot use JOIN as it is a TEMPORARY table.


texts: How to get YAML Python Library in PostgreSQL

texts:
I would like to use YAML in some plpython code, but YAML is not included in the python3 extension for PostgreSQL.

My 'import yaml' gets an error that it cannot find yaml.

On my regular Python3 install I did 'pip3 install yaml' which worked fine.

How can I get yaml installed into PostgreSQL?

Thanks.

Some more info for clarification:

Here is the start of a function defined in PG:

-- Default audit trigger
create or replace function sys_audit() returns trigger language plpython3u AS $$
  from sys import path
  path.append('/usr/local/lib/ez-python-library/PostgreSQL/bin');

  from datetime import datetime
  from CommonRowFunctions import getPkValue, getRowValue, getRowChanges

  keyVal    = ''
  modData   = 'unknown'
...

The module 'CommonRowFunctions' tries to use YAML to configure logging. This module lives in my python library (external to PostgreSQL). This all works if I use a properties file for the log config, but using a dictionary is the preferred method and YAML makes that very easy.


texts: How monitor TEMP space allocation for a particular object type like Clob\Blob

texts:
I am new to Oracle and i wanted to know if there is a way to monitor Clob\Blob objects allocation in Oracle Temp Space.

With JDBC 4.0, we have clob.free() and blob.free() methods and i want a way to verify the benefits using these methods in my application.

Does anyone know how can i do that and/or possibly point me in the right direction?


texts: Is it possible to take the table level backup in mysql using any of the script?

texts: I am new to MySQL and I am in need of the script which is taking backup of  multiple tables from the multiple databases in single file in MySQL?