texts: How to allow only a certain type of foreign key in a table

texts:
I'm designing a new DB. I have employee and order table. order table has deliverer_id to indicate who delivered the order. But only employee who is "deliverer" should be allowed.

I thought of 3 methods to enforce this.

Create deliverer table in the middle of employee and order tables to make use of foreign key restrictions: But if I choose this method, I might need to make many other tables like administrator, supervisor, guest and so on for other tables. I fear if this makes things harder unnecessarily. This would be something like this:

CREATE TABLE IF NOT EXISTS temp1_employee
(
    id INT AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE IF NOT EXISTS temp1_deliverer
(
    employee_id INT NOT NULL PRIMARY KEY,
    CONSTRAINT temp_deliverer_temp_employee_id_fk FOREIGN KEY (employee_id) REFERENCES temp1_employee (id)
);
CREATE TABLE IF NOT EXISTS temp1_order
(
    id           INT AUTO_INCREMENT PRIMARY KEY,
    deliverer_id INT NULL,
    CONSTRAINT temp_order_temp_deliverer_employee_id_fk FOREIGN KEY (deliverer_id) REFERENCES temp1_deliverer (employee_id)
);

Add type column in employee table and create a trigger on order table to check if the employee.type is "deliverer": I'm worried about this method since I read that triggers should be used carefully or even avoided if possible.
Since this is a business logic(is it?), it should be enforced from applications: But I already know those applications will cause a lot of trouble if it's not enforced by DBMS.

Which method should be used? Maybe there is another?


texts: Check whether MySQL Innodb Rows are compressed

texts: After setting the various variables to enable compression on the Innodb Barracuda tables, what query will you use to check that those tables are indeed compressed?


texts:
I am new to data mart design and need to clear a few concepts.

I have read up a bit on dimension modelling where I see that fact tables store foreign key references to dimension tables.

Now suppose I have a phonenumber dimension table and a phone_extension dimension table.
(These tables have different details because of which I cannot combine them)

As I understand both these dimension tables will have integer primary keys for better performance, and the fact table will have its own integer primary key and also store foreign key references to these dimension tables.

But suppose I have a situation that not all phone numbers have a  phone_extension related to them. ( some phone numbers need not have an extension)

For phone numbers that have an extension, the fact table would have foreign key references to both the dimension tables, but how do I capture the situation where there are only phone numbers and no extension to them (and vice versa i.e extension with no phonenumbers)?

Should I capture such information with the phonenumber FK in the fact table having a value and phone_extension foreign key null?? Or are such non related objects not recorded in fact tables?

Also I need to generate report of this data mart. So do I start by querying the fact table and retrieving the dimension key values or report straight from the dimension table?

Thanks for your time in reading this!!
Appreciate any help!!


texts: What objective factors indicate it's time to implement SQL Server replication?

texts:
I am trying to strike a balance between high performance of our database and ease of maintenance.  We are considering using replication to improve performance, by replicating our SSRS reports to a physically separate database from our transactional database.  However, enabling replication has a number of drawbacks from a developer point of view:

It makes schema changes more difficult
It interferes with our automated integration/build server
It seems to make it difficult to implement SQL source control

My question is: When do you know it's time to go with replication in light of these drawbacks?  How do you decide whether the additional complexity justifies the gains?

We've used it before so setting it up is not a problem.  This is more about making the decision to, or not to, enable it.  I'm looking for some object performance metrics that others have observed with replication.

Of course the best thing would be to do some simulated load testing on our own servers and figure it out ourselves, but I am hoping there are some general guidelines out there.


texts: Sargability of queries against a view with a case expression

texts:
I have a view that looks similar to this:

CREATE VIEW Shipment.Shipment AS

    SELECT  CASE
                WHEN shipmentOld.SHIPMENT_ID = 0 OR shipmentOld.SHIPMENT_ID = -1 THEN NULL
                ELSE shipmentOld.SHIPMENT_ID
            END AS ShipmentId,

            OtherValue, SomeOtherValue, OtherStuff
    FROM    dbo.tblShipment_Old shipmentOld
GO

Then if I do a query like this:

SELECT * FROM Shipment.Shipment WHERE ShipmentId = 18140

I get a full index scan.  The reason why is that it is running the case expression for ShipmentId on all the rows in the table and comparing the result to my ShipmentId (18140).  The very definition of non-sargable.

I am using this view to normalize legacy data so I can write a new application on top of it.  I just want the case expression to run on output.

I am guessing that is not possible, but I thought I would ask before I pursue more extreme options.  So here is my question:

Is it possible to still have my ShipmentId output go through the case expression, but also have a sargable query when ShipmentId is used in the where clause?


texts: How to Archive Only MySQL Master not the Slave.?

texts:
Currently, I have a Master and Slave. Both of them have same set of data (I can say are in Sync). Replication is running fine.

What I need to accomplish is the following ...

Need to Archive the Master(Say I need Quarter(Only Last 3 months) of data in Master) but need the Full data on Slave.

How can we do it with having minimal downtime ? What strategy should we follow ?


texts: How to find the last data backup on secondary replica?

texts:
To determine when database has been backed up the last time usually is enough to look into backup tables in msdb database.

However, it doesn't work well with Availability Groups - after the failover new active replica doesn't know when the last FULL\DIFF backup was taken because local msdb database doesn't have those records.

It is a bit easier with log backups - DBCC DBINFO (@DBName) WITH TABLERESULTS gives you the last LOG backup data no matter where it was taken.

My question is - is there an easy way to say on the new active replica, right after failover, when the last data backup was taken?
There is always an option to query each replicas through OPENROWSET (or anything like this), but this introduces unwanted complexity.

The goal is to make sure that code like this works stable even after the failover:

IF dbo.lastBackupWasTakenMoreThanSevenDaysAgo(@DatabaseName) = 1
   < full backup >
ELSE
   < diff backup >

Probably I'm missing something very simple.

Thanks!


texts:
I have the following situation. An application writes logs into SQL Server 2012 Standard edition database. The incoming data are too big to leave in the operational DB because the application also reads from this DB and it is time critical. So we need to delete the log entries which are older than a week.

But in some cases we need the old data. So my idea is to create a second database from a backup of the original and restore it every week before we delete the data. An already existing external reporting tool is ready to read data from two databases so our problem would be solved.

Is this possible - restoring and attaching data to another DB with the same schema - or do I need to create a new database each week?


texts: How to convert .rda database to excel workbook

texts:
I have downloaded a database with .rda extension, unfortunately I don't have oracle program.

How can I convert .rda database to excel?


texts:
I'd like to use DBeaver with SQLite, but the network firewall disallows external connections. So DBeaver cannot download the driver automatically.

Where can I download the SQLite driver for it and how can I install it manually in DBeaver?


texts: How to select specific databases in a backup retention policy maintenance plan cleanup task?

texts:
I have full backups of three different databases. Right now all of their backups follow the same retention policy, but I need to setup the retention to a different amount of time for each database.

In the Maintenance Cleanup Task screen I see it just searches the whole backup folder for any backup files to remove. Is there any way to specify the databases I want this to run for or is the only way to do this is by setting the folder directly to the backup folder of that database? (E.g. I'd have to change the folder to Y:\SQLBackup\Full\Database1)

If so, then do I need to create a retention policy maintenance plan for each database then?


texts:
Is it possible to combine both update and delete in a single SQL statement? I would like to execute something like this:

update a
set a.value = a.value + b.value
from Table1 a
join Table2 b on a.ID = b.ID

Also, rows, which has value higher than some constant number, should be deleted.


texts: Running a speculative transaction in SQL Server

texts:
I have a long-running transaction that I know is always going to be rolled back.

Is there a way to run this transaction so that it doesn't acquire any locks in the database? The transaction inserts and updates rows, but these changes will never persist in the database and in general should not be visible to anybody outside the transaction.

Here is a more specific example.

Connection 1:

BEGIN TRAN
UPDATE foobar SET something = 1;
COMMIT

Connection 2:

BEGIN TRAN
UPDATE foobar SET something = 2;
ROLLBACK

I want to prevent connection 2 from blocking connection 1. I can add SQL statements in the transaction, but I cannot easily change the queries.


texts:
I´m trying to execute this query into a SP

 DELETE FROM table SC
      WHERE SC.evaluationMonth= 'Some month';
 COMMIT;

But for this month there are something like 10 million records. So I get the error

  ORA-01562: failed to extend rollback segment number 11.

How can I improve the SP or make something to solve this problem?
Pdta: I had increase de unto to 20G


texts: When using an ORM, what are some things to watch out for in your database design

texts:
What are some database design gotchas to watch out for when you know the database will be accessed using a Object Relational Mapper (ORM) Wikipedia? Also see Entity Framework NHibernate or LLBLGenPro.

As an example, I'll note the 2100 parameter limit on SqlServer's RPC call.  This is a problem when using LLBLgen and joining tables when compound primary keys were utilized see MSDN article for compound keys.


texts: SQL Server Linked server query problem (group by)

texts:
I have been using an old SQL Server 2000 installation for a long time that is linked to several Oracle servers, and it all works very well.

I am trying to migrate to SQL Server 2012 via SQL Server 2008.

I am encountering some problems with the linked Oracle servers on SQL Server 2008 and SQL Server 2012.

I downloaded the last Oracle 10 Instant Client since we have some older Oracle databases I need access to, and have configured tns_admin, and checked with SQL Plus; all good so far.

I created an ODBC system DSN to point to one of the Oracle databases.  I then created my linked server using that DSN.

The linked server returns the standard date query, select sysdate from dual, just fine, and will return the count of specific queries too.  However, when I try to return the actual rows from a table, I get a smaller number than I should.

Also, if I run an aggregate GROUP-BY query, I only get one row returned!

I would have tried downloading the full client from Oracle, but in their infinite wisdom, the v10 client is no longer available!

Any help or advice would be greatly appreciated.


texts:
I Made Bad Mistake And Disable All Users In SQL
Is There Any Way With cmd Or RegEdit To Enable One User?


texts: How to update values in production table with foreign keys?

texts:
I have a prod MySQL table on 5.7.32 where I want to update the values. It coutains only 4K rows. However there is another table with a foreign key to the table I want to update and this one holds millions of rows.
table a
ID | DIMENSION | VALUE

table b
ID | A_ID | ..
Key: A_ID -> a.ID cascade

Now how do I update table a? I fear to cascade to table b in case I do a truncate before import. The new table values are on a development DBMS where initially thought to do a single export and then import with prior truncate.
The updated table holds additional rows, has removed rows and edited rows.
What would be the recommended way to update the data?
Could I simply use the following settings during export with PhpMyAdmin 4.5.7 and then import on prod?


texts: Start service error code 3417 with SQL Server 2012

texts:
I cannot start the SQL Server 2012 service. The event log says the service-specific error code is 3417.

In the Event Viewer:

  The SQL Server (MSSQLSERVER) service terminated with the following service-specific error:
  WARNING: You have until SQL Server (MSSQLSERVER) to logoff. If you have not logged off at this time, your session will be disconnected, and any open files or devices you have open may lose data.

I've changed the service logon account in the service properties but this error happened again.

I think the problem is in the master database, but I do not know what to do.

EDIT:

I've try Rebuild the Resource database  but this error happened again!


texts: Moving data from PostgreSQL 32bit to 64bit

texts:
I use Synology's DS1515+ as a PostgreSQL (PG) DB server. Last week Synology upgraded their DSM (Linus based OS) to version 6.0. Doing so, they've upgraded their PG to 64bit and potentially to a newer version (9.3.6). I thus can no long start my instance of PG to access my data.

All this to say:

-I did not get a heads up that this upgrade was coming and therefore did not do a back-up to restore from.

-I can not install the 32bit version of PG to run a dump.

-I know and have access to my DB files

-I am limitted on the tools I can install and use

Is there a way for me to get to my data?


texts: MySQL on SSD - what are the disadvantages?

texts:
Recently, I heard that I should not host my MySQL database on an SSD drive because there is a big chance of disk failure and data lost.
Given that 20% of all queries in my database are INSERTs and UPDATEs, here are my questions:
QUESTIONS

What are the disadvantages of using MySQL on SSD ?
What solutions or alternatives are available for MySQL/SSD usage ?


texts:
I have converted a MySQL database with 80.000.000 rows to TokuDB.

Now when I run:

 select count(id) from xxx where active=1

it takes 90% of the time of the normal MySQL request.

What do I have to further optimize so that it runs faster?

The table definition:

CREATE TABLE `adsDelivered` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uid` varchar(40) NOT NULL,
  `_adsDelivered` bigint(20) NOT NULL DEFAULT '0',
  `_campaign` bigint(20) NOT NULL DEFAULT '0',
  `_ad` bigint(20) NOT NULL DEFAULT '0',
  `session` varchar(44) NOT NULL,
  `referer` text NOT NULL,
  `refererDomain` varchar(256) NOT NULL,
  `pageTime` int(11) NOT NULL DEFAULT '0',
  `pageVisibleTime` int(11) NOT NULL DEFAULT '0',
  `browser` varchar(256) NOT NULL,
  `ip` varchar(15) NOT NULL,
  `clicks` int(11) NOT NULL DEFAULT '0',
  `clickTimeLast` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `tag` varchar(256) NOT NULL,
  `countryShort` varchar(2) NOT NULL,
  `timeCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `timeUpdated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

  PRIMARY KEY (`id`),
  UNIQUE KEY `uid` (`uid`),
  KEY `_campaign` (`_campaign`),
  KEY `_ad` (`_ad`),
  KEY `_adsDelivered` (`_adsDelivered`),
  KEY `session` (`session`),
  KEY `tag` (`tag`),
  KEY `ip` (`ip`),
  KEY `countryShort` (`countryShort`),
  KEY `refererDomain` (`refererDomain`)
) ENGINE=TokuDB AUTO_INCREMENT=7420143 DEFAULT CHARSET=utf8;


texts:
I was reviewing the commit-fest scheduled for 7/01 for PostgreSQL and I saw that Pg is likely going to get "identity columns" sometime soon.

I found some mention in information_schema.columns but nothing much

is_identity         yes_or_no         Applies to a feature not available in PostgreSQL
identity_generation character_data    Applies to a feature not available in PostgreSQL
identity_start      character_data    Applies to a feature not available in PostgreSQL
identity_increment  character_data    Applies to a feature not available in PostgreSQL
identity_maximum    character_data    Applies to a feature not available in PostgreSQL
identity_minimum    character_data    Applies to a feature not available in PostgreSQL
identity_cycle      yes_or_no         Applies to a feature not available in PostgreSQL

The Wikipedia Page doesn't say much either

  An identity column differs from a primary key in that its values are managed by the server and usually cannot be modified. In many cases an identity column is used as a primary key; however, this is not always the case.

But, I don't see anything else on them. How do identity columns work? Do they provide any new functionality or is this just a standard method to create sequences? Any breakdown of the new feature and how it works?


texts: How to delete a datafile if the father-tablespace does not exists?

texts:
I just know Oracle basic and I would like to know about the datafiles.
I have some files ".dbf" associated with Tablespace that already were deleted. I don't know how I can drop that datafiles and have some free space in my server.

Look:
This is my dbf :

And this is the father-tablespace:
select tablespace_name from dba_data_files where file_name = '/u01/app/oracle/oradata/orcl/BGBA_GAL126344.dbf';

Result:

If I try to drop that tablespace, It says that the tablespace does not exists.

How can I drop that datafile if the father-tablespace does not exists?


texts: Why would  running ALTER TABLE  on a  column cause it to go from NOT NULL to NULL?

texts:
I am mass updating a SQL Server database. I am changing all our numeric(38,0) columns to int (yes, SQL Server scripts were created from Oracle scripts).
Using SMO and C# (I am a sw engineer), I managed to generate really nice scripts like SQL Server Management studio would. It all works very nicely except for one particular issue:

for a a handful of tables, when I call ALTER TABLE [myTable] ALTER COLUMN [columnA] INT it decides to also change the column from NOT NULL to NULL. That of course is a huge issue since I need to regenerate primary keys for most of those tables on those particular columns.

Obviously, I have plenty of options using SMO to find out which columns are primary keys and force them to be NOT NULL after or while I am updating the data type, but I am really curious as to what can be causing this.


texts: Inserting into an table with identity column from stored procedure works even though it shouldn't

texts:
We have this piece of (anonymyzed code)

create table #SomeTmpTable
(
  [RowNumber]         integer identity,
  [Value]             integer,
  primary key (RowNumber)
)

insert into #SomeTmpTable (RowNumber, Value)
execute SomeSchema.GetResultSet
    @ParamA = 9999,
    @ParamB = 'Alpha',
    @RowCount = 5

GetresultSet is a procedure that does something and most important returns a two column result set.

Issue:
Even though RowNumber is an identity column in #SomeTmpTable, the insert sometimes works.

Sometime definition:
DEV computer (SQL2016 - works) (edit: doesnt work on SQL2012)
Q&A computer - fails - SQL 2012

replacing execute with a select  - fails...

Issue in code was easy to fix per se (remove identity, makes no sense having it there), but the question here is:

Why did it work on DEV PC?

Thank you

EDIT: So it seems for some reason, on MS SQL 2016, inserting into identity column from a stored procedure's result set is OK. Trying to dig up some official info on it


texts: Improve SQL Performance with LIKE '%ABC%' QUERY

texts:
I know that using a LIKE '%ABC%' query will not use indexes and there is little that can be changed to the query to improve this, however what can do to make execution quicker?  At this stage we cannot change the query to use Full-text indexing.

Some background...

We are bringing systems up in Azure VMs (NOTE: Not Azure SQL, but SQL Server running on Windows 2012) as a 2nd location for additional resilience (offline backup) and have 'built' an SQL server using a basic server spec.  Performance of a LIKE query on our old platform took 2 seconds to run, whereas on this Azure platform its taking 10 seconds.

This is clearly server spec limitations, however what can i do to improve this?

I can see the CPU spike during the query run, so it would seem a 'faster' azure cpu will help, but know that these figures can be misleading too!

So my question is, do i need to concentrate on improving CPU, or could it be more than that?

The DB in question is only 300mb on disk, and the table being queried has about 160k rows so it isn't large by any means.

Please let me know if i am barking up the wrong tree here, or if i need to check anything else first?

The SQL server is Windows 2012 R2 with SQL Server 2014 Std, and has been built following the Azure SQL performance guidelines (i.e. data on a dedicated striped drive).

EDIT
As requested, this is the query i am testing:

SELECT Name
FROM Users
WHERE Name like '%ABC%'

Thats it.  Nothing complex here, just retrieving data from a small database!

Incidentally, This query takes 10s to run, whereas adding the clause 'AND Description like '%ABC%' reduces the time to 6s?

EDIT 2

Ok, some more information following feedback in the comments...

I have followed the information from this page: http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

I have run the query shown, and these are the results shown:

To me, it initially looked like it was Disk IO bound, but the Average wait time seems low?  I am no expert in this area so please advise...


texts: Measure time duration between events, partitioned by hours

texts:
I have the following table in PostgreSQL 9.4, which persists users log on/off events (log on is event_type 1, log off is event_type 0).

CREATE TABLE user_online_offline_events (
  id serial,
  user_id int4,
  event_type int4,
  created_at timestamp
);

Sample data:

INSERT INTO user_online_offline_events
       (id, user_id, event_type, created_at)
VALUES (1,  123,     1,         '2015-10-07 12:15:00'),
       (2,  123,     0,         '2015-10-07 12:25:00'),
       (3,  123,     1,         '2015-10-07 12:45:00'),
       (4,  123,     0,         '2015-10-07 13:10:00');

I'd like to calculate the number of minutes each user was logged-on per hour:

| id | user_id |     time | minutes logged on |
+----+---------+----------+-------------------+
|  1 |     123 | 12:00:00 |                30 |
|  2 |     123 | 13:00:00 |                10 |

This is my WIP version. It's not really elegant, it still has the hour hard coded in, ignores user_ids and assumes online-offline events are consecutive:

select time, sum(minutes) / 60 as minutes from (
  SELECT
    date_trunc('hour', time) as time,
    CASE
    WHEN event_type = 0 AND lag(event_type, 1) OVER w = 1
      THEN
        extract(EPOCH FROM time - lag(time, 1) OVER w)
    WHEN event_type = 0 AND lag(event_type, 1) OVER w ISNULL
      THEN
        extract(EPOCH FROM time - date_trunc('hour', created_at))
    WHEN event_type = 1 AND lead(event_type, 1) OVER w ISNULL
      THEN
        extract(EPOCH FROM date_trunc('hour', time) + INTERVAL '1 hour' - time)
    ELSE 0
    END AS minutes
  FROM user_online_offline_events
  WHERE date_trunc('hour', time) = '2015-10-07 12:00:00'
  WINDOW w AS ( ORDER BY time )
  ORDER BY time
) m group by time;

How to do this properly?


texts: pgAdmin 4 version 3 fails to open a second time

texts:
I just installed pgAdmin 4 version 3.0.0 for macOS Sierra, replacing the pgAdmin that came bundled with Postgres 10.3 running locally on the same Mac.

I opened the pgAdmin 4.app app icon, which caused Safari browser to come to the front with a tab showing the usual pgAdmin user-interface. Good.

I closed that tab in Safari. Now when I open the pgAdmin 4.app icon (either double-clicking or choosing File > Open), nothing happens.

Is pgAdmin supposed to launch in a browser window rather than in the app's own window?

➡ How to re-open pgAdmin again?


texts: How to GRANT/REVOKE user permissions for user on Firebird 1.5?

texts:
I need to create a user in firebird 1.5 and give him permissions to access only some specific tables.
I have managed to create the user, but apparently GRANT/REVOKE operations only work on version 2.1 or above.
SO, how to do it?

UPDATE
Actually i was mistaken, GRANT and REVOKE both work fine in version 1.5.


texts: Optimal drive configuration for SQL Server 2008R2

texts:
I have a fairly busy database server running SQL Server 2008 R2 that has the following setup:

SATA RAID 1 (2 Drives)  - OS / Programs
SAS  RAID 10 (4 Drives) - Sql Database Files (data and logs)
SAS  RAID 1 (2 Drives)  - TempDB (data and logs)

Assuming I can't add additional drives into this server, have I made the best use of the configuration I have available? Or should I consider another scheme here where logs are isolated from the data files, for example?

Update:

For those that requested further hardware details:

The SATA drives (used for the OS / Program partition) are: WD 7200 RPM 3 Gb/s 3.5 Inch SATA
The SAS drives used in the other arrays are: Seagate 15K RPM 6 Gb/s 3.5 inch SAS
The RAID controller used is an: LSI 9260-8i SAS/SATA 6 Gb 8 port

Update 2:

Based upon the feedback I've received, it looks like I have the following viable options to choose from - I will award the bounty to someone that can tell me which is likely to be the best in the environment that I've outlined:

Leave everything as is - I probably won't do much better
Move my 2 SAS RAID 1 drives into my existing RAID 10 array to have it composed
of 6 disks in total
Move my log files onto the SAS RAID 1 and/or
relocate TempDB (data or logs) back to the RAID 10


texts: Returning all rows from multiple partitions of a partitioned table

texts:
I have a table that is manually partitioned into multiple filegroups and spread out over multiple tables by month. My partition scheme is using a date column. What I would like to do is something like the following:

SELECT a
, b
, c
FROM<table>
WHERE date between '2015-08-01' AND SYSDATETIME()

I am wondering if there is a way to do it without the need to use a UNION ALL on all the tables?


texts: PostgreSQL not SELECTing by RETURNING value of INSERT

texts:
I have a query that's supposed to insert a new row, then return it. Normally a RETURNING would suffice, but there's a lot of other JOINed data must be added, so a SELECT seems to be necessary after the INSERT.
In this simplified example id is a SERIAL:
WITH new_row AS (
  INSERT INTO my_table (
    some_row,
    some_other_row
  ) VALUES (
    0,
    'whatever'
  ) RETURNING id
)

SELECT
   my_table.*,
   other_table.*
FROM my_table
LEFT JOIN other_table ON other_table.somekey = my_table.id

WHERE my_table.id = (SELECT id FROM new_row)

The result is an empty set. Apparently WHERE doesn't work.
If I just do this:
WITH new_row AS (
  INSERT INTO my_table (
    some_row,
    some_other_row
  ) VALUES (
    0,
    'whatever'
  ) RETURNING id
)

SELECT id FROM new_row;

then I get the value of id correctly.
Why doesn't it work in the WHERE condition?


texts: Creating a mysql trigger without hard-coding database name

texts:
Is there a way to create a trigger in mysql without hardcoding the database name?

Doing a typical create trigger will stop working if I dump the database and then import it into a database of a different name.


texts: Simplifying Function with Recursive CTE and/or Window Function

texts:
I'm trying to come up with a Recursive CTE and/or Window Function to create a function.

After days, I've boiled the function down to (pseudocode)
where I have N and B, and need to generate E:

  En = Bn * (1 - SUM(E1, E2, ... En-1))

Examples:

╔═══╦═════════════╦═════════════╗
║ N ║ B           ║ E           ║
╠═══╬═════════════╬═════════════╣
║ 0 ║ 0.142857143 ║ 0.142857143 ║
║ 1 ║ 0.285714286 ║ 0.244897959 ║
║ 2 ║ 0.285714286 ║ 0.174927114 ║
║ 3 ║ 0.285714286 ║ 0.124947938 ║
║ 4 ║ 0.285714286 ║ 0.089248527 ║
║ 5 ║ 0.4         ║ 0.089248527 ║
║ 6 ║ 0.666666667 ║ 0.089248527 ║
║ 7 ║ 1           ║ 0.044624264 ║
╚═══╩═════════════╩═════════════╝

  E0 = 0.143 * (1 - 0) = 0.143
  E1 = 0.286 * (1 - 0.143) = 0.245
  E2 = 0.286 * (1 - (0.143 + 0.245)) = 0.175
  E3 = 0.286 * (1 - (0.143 + 0.245 + 0.175)) = 0.125
  E4 = 0.286 * (1 - (0.143 + 0.245 + 0.175 + 0.125)) = 0.089
  E5 = 0.400 * (1 - (0.143 + 0.245 + 0.175 + 0.125 + 0.089)) = 0.089
  E6 = 0.667 * (1 - (0.143 + 0.245 + 0.175 + 0.125 + 0.089 + 0.089)) = 0.089
  E7 = 1.000 * (1 - (0.143 + 0.245 + 0.175 + 0.125 + 0.089 + 0.089 + 0.089)) = 0.044

If the table above was in Excel, C2 = B2 * (1 - 0) (base) and C3 = B3 * (1 - SUM(C$2:C2)) (recursive)

What I've tried:

Windowed Functions

Tried SUM(...) OVER(ORDER BY [N] ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), but can't reference the column recursively.

Recursive CTE

Tried several iterations of:

WITH B AS ([Num], [Best], [Effective Rate]) AS (
    SELECT *
        , [Best]
    FROM A
    WHERE [Num] = 0
    UNION ALL
    SELECT A.*
        , (1 - [Effective Rate]) * A.[Best]
    FROM B
    JOIN A ON A.[Num] = B.[Num] + 1
)

and some with an extra column in the CTE, but it only covers 1 previous row and results after 2nd row are wrong.

Recursive CTE with Windowed Function

From all that I've tried, it seems that the recursive segment of the CTE is calculated independently of the other results, and SUM(...) OVER(...) only works on the current row. (With regard to the above table, all values of E would be 0.142857143).

I assume this is because the UNION ALL happens all at once, and not incrementally.

Alternative Solutions

What I would really like to happen is to simplify the above equation, and/or transform it into an iterative function.

Bonus: If anyone cares to know the source of this information, it's used to calculate MACRS depreciation for tax purposes.


texts:
I have a MySQL instance running on AWS, with around 5000 inserts per second. Any idea what the performance impact will be if I use binlog (row) and a binlog tailer?

Check this link

From my understanding, a bin log tailer polls the MySQL binlog periodically in order to make a 'realtime' data connection possible. The binlog tailer runs in NodeJS.

The point is, I have to use MySQL and I want to use Meteor to get the data in realtime to my clients. Hence my idea of using this binlog tailer.

Because the MySQL db is gonna be filled quite intensively (5000 inserts per second) , I want to know at which point the binlog/binlog tailer gets severe performance issues.

Grts, Tom


texts: I have 2 parent tables with 1 child and I want to perform a query that involves both parents

texts:
I have one table that contains information about different types of hops for use in brewing and a second that contains a list of aromas used to describe hops.  I've made a third table that contains a primary key and 2 foreign keys (one is the primary key of the hops table and one is the primary key of the aromas table).  I set it up as two tables because any hops variety may have several aroma descriptors and any aroma may apply to several hops varieties.

The child of the two parents was created in the following way:

CREATE TABLE IF NOT EXISTS aromareference
( ID int(5) NOT NULL AUTO_INCREMENT,
  HopsID int(5) DEFAULT NULL,
  DescriptorID int(5) DEFAULT NULL,
  CONSTRAINT FOREIGN KEY (HopsID)
    REFERENCES hops (ProductID),
  CONSTRAINT FOREIGN KEY (DescriptorID)
    REFERENCES hopsdescriptors (DescriptorID),
  PRIMARY KEY(ID)
);

The hops table has a column called HumanName, as does the hopsdescriptors table.  I would like to perform a query using the HumanName from the hops table to list from the hopsdescriptors table all of the applicable HumanNames of the aromas.

As an example output, I would be looking for something like:

+------------+
| Centennial |
+------------+
| Floral     |
| Citrus     |
+------------+

The basic structure of the tables in question is as follows:

CREATE TABLE IF NOT EXISTS hops
('ProductID' int(5) NOT NULL AUTO_INCREMENT,
'HumanName' varchar(50) DEFAULT NULL,
'ShortDescript' varchar(500) DEFAULT NULL,
'PrimaryUse' int(1) DEFAULT NULL,
'AlphaAcidMin' float(5,2) DEFAULT NULL,
'AlphaAcidMax' float(5,2) DEFAULT NULL,
'CohumuloneMin' int(3) DEFAULT NULL,
'CohumuloneMax' int(3) DEFAULT NULL,
PRIMARY KEY ('ProductID')
);

CREATE TABLE IF NOT EXISTS hopsdescriptors
('DescriptorID' int(5) NOT NULL AUTO_INCREMENT,
'HumanName' varchar(20) DEFAULT NULL,
'ShortDescript' varchar(500) DEFAULT NULL,
PRIMARY KEY ('DescriptorID')
);

I imagine this is a pretty common query to perform, however I'm brand new to this stuff and I haven't even been able to think about the question clearly enough to google it.


texts: Does leaving queries open in SSMS lockup memory on the SQL Server?

texts:
We have a logging database that has been locked down from developer access.  The reason it was locked down was because they say that people would run large queries and then leave the results up in Sql Server Management Studio (SSMS).

They read somewhere that doing this leaves the connection open and holds onto memory on the SQL Server.

I had never heard this, but if it is true, then I want to make sure I start closing my queries, especially when I run large ones.

Is it true that leaving a query open in SSMS will hold on to extra memory on the server?  If so, is there a way around it?


texts: Is the mapping between PostgreSQL system tables and information_schema views documented?

texts:
If you connect pgadminIII to a PostgreSQL 9.x server, you'll find two catalogs exposed in the user interface: ANSI (information schema), and PostgreSQL (pg_catalog).

I'm looking for documentation that describes the mapping between

database objects and the system tables
the system tables and the information_schema views

For example, I'd like to be able to take a sequence name from a database, and be able to tell quickly where the sequence itself, the sequence's name, and the sequence's ACL (from GRANT and REVOKE statements) would be found in both the system tables and in the information_schema views.

Is that documented anywhere? I've been through PostgreSQL's online documentation, and I've found details of both the ANSI and PostgreSQL catalogs, but I haven't found documentation of the mappings.

Update

It looks like the information is probably in the source file information_schema.sql, but that file doesn't seem to be in the online source code. (See, for example, initdb.c at http://doxygen.postgresql.org/.)


texts: What causes pt-online-schema-change to slow down the further it gets through a table?

texts:
I'm currently running a schema migration on a table containing ~23 million rows. The rows have a normal primary index and we're running MySQL 5.5.

The migration, to add a couple of columns, started quickly. At a rapid rate, it copied the first 3 million in about 3 minutes. The next 3 took about 20 minutes. Overnight, it did another 9 million in about 8 hours.

Other than modifying the replica lag I'm willing to accept, I've not modified the defaults - so it's obeying the chunk time and aiming to get each copy in the ~0.5 second range.

As I'm running the tool with PTDEBUG on so I can see detailed output, and it would appear the operations are correctly in that range - but I don't understand why they're getting slower and slower. At peak it was around 7000 rows a second, and it's slowly dropping further and further, we're down to ~470 now and still have ~15million rows to go.

I have several assumptions, but I'm not sure which is correct, or how to diagnose it.

Are any of my assumptions correct, and how would I identify which?

It's the index on the original table - the query itself is taking too long. I'm assuming it's not this, as the SELECT is off the primary key.
It's something to do with the insertion process. The larger the table, the more "thinking" has to be done about the INSERT and so that process is taking longer.
There's a hardware limitation somewhere. We're running on RDS with 3 replicas, by the way. Monitoring doesn't suggest we're hitting a bottleneck at the moment.

Here's the CREATE TABLE statement anonymised:

CREATE TABLE `user_offer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `CreatedDate` datetime NOT NULL,
  `A` int(11) NOT NULL,
  `B` int(11) NOT NULL,
  `C` int(11) NOT NULL,
  `D` datetime DEFAULT NULL,
  `E` datetime DEFAULT NULL,
  `F` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `G` int(11) DEFAULT NULL,
  `H` int(11) DEFAULT NULL,
  `I` int(11) NOT NULL,
  `J` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `K` tinyint(1) NOT NULL,
  `L` tinyint(1) NOT NULL,
  `M` tinyint(1) NOT NULL,
  `N` tinyint(1) NOT NULL,
  `O` int(11) NOT NULL,
  `P` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Q` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `R` tinyint(1) NOT NULL,
  `S` tinyint(1) NOT NULL,
  `T` int(11) DEFAULT NULL,
  `U` tinyint(1) NOT NULL,
  `V` int(11) DEFAULT NULL,
  `W` datetime DEFAULT NULL,
  `X` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Y` tinyint(1) NOT NULL,
  `Z` tinyint(1) DEFAULT NULL,
  `AA` int(11) DEFAULT NULL,
  `AB` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_CB147C66AAB016B8` (`I`),
  KEY `IDX_CB147C6668D3EA09` (`J`),
  KEY `L_idx` (`L`),
  KEY `H_idx` (`H`),
  KEY `IDX_CB147C6632A90D98` (`T`),
  KEY `IDX_CB147C66F6CB3049` (`V`),
  KEY `createdDate_idx` (`CreatedDate`),
  KEY `consentedDate_idx` (`W`),
  CONSTRAINT `FK_CB147C6632A90D98` FOREIGN KEY (`T`) REFERENCES `table_1` (`id`),
  CONSTRAINT `FK_CB147C6668D3EA09` FOREIGN KEY (`H`) REFERENCES `table_2` (`id`),
  CONSTRAINT `FK_CB147C66AAB016B8` FOREIGN KEY (`I`) REFERENCES `table_3` (`id`),
  CONSTRAINT `FK_CB147C66F6CB3049` FOREIGN KEY (`V`) REFERENCES `table_2` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23448523 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The alter statement is: ADD AC VARCHAR(5) plus adding indexes on AC, P and Q.


texts: Why Azure site recovery may not meet RPO?

texts:
While Azure Site Recovery may meet RTO, it may not meet RPO since it
is not accounting for where data is inside SQL Server.

I am looking for an example for what it means by "is not accounting for where data is inside SQL Server".


texts: MS SQL Server create user who can access only those databases which they have created

texts: How to create and give permission to a user in such a way that they will have full access only to the database(s) they have created not other database(s). I am using SQL Server 2016 Express.


texts: Create a user with rights and privileges between root and limited (CRUD single database) user

texts:
Is it possible to create a mid-root MySQL user, who will have the ability to create other users, with scope limited to databases which mid-root user creates? This mid-root user would not have the privileges of a root user, and would not be able to perform any of the CRUD operations on any of the databases other than on those they've created themselves.
Hopefully, this illustration would do a better job of explaining.
In this scenario, the user ROOT has access to everything, and can create other users, with limited privileges.
The user called mid-root is somewhat of a demigod or middle management - they can create other users as well, but can grant them only access to the databases that the mid-root user has created. Mid-root can only access the databases they themselves have created (simple db1, simple db2, etc), while being unable to access those created by the root user (db1, db2, db3, db4).
Finally, users created by the fictional mid-root user could only perform standard CRUD operations with their given databases. By standard, I mean everything other than DROP database, or ALTER table, or TRUNCATE, etc.
Is any of this possible?
Real life use scenario follows.
There is a webapplication, and a potential client or clients (one or more of hundreds, or thousands) wishes to try out a demo for a certain period of time (a month or so). When that client registers, background logic creates a MySQL user and the appropriate DB, tables, etc, and fills them with necessary data. Since the server might be shared between different types of webapps, there's a need for a mid-root user, who can perform these tasks, and only them.


texts:
Is there a best practice or strategy for table types used in TVPs? For instance, given the following:

CREATE TABLE dbo.Colors (
    Id int identity PRIMARY KEY,
    Name nvarchar(100),
);

CREATE TABLE dbo.Shapes (
    Id int identity PRIMARY KEY,
    Name nvarchar(100),
);

CREATE TABLE dbo.Materials (
    Id int identity PRIMARY KEY,
    Name nvarchar(100),
);

CREATE TABLE dbo.Items (
    Id int identity PRIMARY KEY,
    Name nvarchar(100),
    ColorId int FOREIGN KEY REFERENCES dbo.Colors (ID),
    ShapeId int FOREIGN KEY REFERENCES dbo.Shapes (ID),
    MaterialId int FOREIGN KEY REFERENCES dbo.Materials (ID),
);

If you implemented a stored procedure for searching items that needed to support selecting multiple colors, multiple shapes, and multiple materials via TVPs (think checkbox lists in the UI), would you create three separate table types, one for every TVP, or would you create a single type for using it across all three?

In other words, this:

CREATE TYPE dbo.ColorIds AS TABLE (Id int);
CREATE TYPE dbo.ShapeIds AS TABLE (Id int);
CREATE TYPE dbo.MaterialIds AS TABLE (Id int);
GO

CREATE PROCEDURE dbo.SearchItems
    @ColorIds ColorIds READONLY,
    @ShapeIds ShapeIds READONLY,
    @MaterialIds MaterialIds READONLY
AS
BEGIN
    PRINT 'Do something here'
END
GO

Versus this:

CREATE TYPE dbo.Ids AS TABLE (Id int);
GO

CREATE PROCEDURE dbo.SearchItems
    @ColorIds Ids READONLY,
    @ShapeIds Ids READONLY,
    @MaterialIds Ids READONLY
AS
BEGIN
    PRINT 'Do something here'
END
GO

The sample is deliberately contrived; the real use case consists of a lot more tables which although have different columns, all have a ID int primary key. Because of this, I personally am much more inclined to do the latter. It's far less overhead, but I'm curious to know if there are any cons I should be aware of in doing this. This is of course for TVPs and TVPs only (I would never mix different entities in a real table, or any other structure of a more permanent nature.)

While at it, what is your naming convention for naming table types and TVPs?


texts: SQL Server Stored Procs not parameterized, leading to high compilations?

texts:
We're experiencing about 300 SQL Compilations per second, and we use very little dynamic SQL.  However, I noticed that in SQL Profiler our calls look like:

exec cid.dbo.uspCIDLog '9425','19155852234',2 -- null

And I think they should look like this if properly parameterized:

exec cid.dbo.uspCIDLog @sFrom='9425', @sTo='19155852234', @userId=2

Will this make a difference?


texts:
I install sqlserver and login with SSMS with windows auth. and server = DESKTOP-XXXXX\MSSQLSERVER01
When I tried to connect with 127.0.0.1 ot 127.0.0.1:1433 I can't
I put into TCP/IP
 but still I can't login with 127.0.0.1\MSSQLSERVER01
why is that?


texts: Mysql - How to Optimize Retrieval Time in a Table

texts:
I have query which has 200 million Records in a single table. I am using BTree indexes in my table:

mysql> select COUNT(DISTINCT id) from [tablename] where [columname] >=3;
+------------------------------+
| COUNT(DISTINCT id) |
+------------------------------+
|    8242063
+------------------------------+
1 row in set (3 min 23.53 sec)

I am not satisfy with this timing.

How can I reduce the result time less than 30sec. Kindly give me any suggessions! It will be more helpful to me!


texts: A Difficult Case of "A transport-level error has occurred when receiving results from the server" SQL Server

texts:
We are building a new BizTalk cluster, with two BizTalk Application Servers and two SQL Servers. From SSMS on BizTalk AppServer #1 (and similarly from #2), we have two scenarios:

Current - our infrastructure team just rebuilt 2012/R2 Windows. It cannot connect to one specific SQL Server, but it can connect to others. The perplexing part is that any other machine seems to be able to connect to the same SQL Server just fine, and also run queries against it.
Yesterday - (We rebuilt the box since yesterday, so can't go back... just wanted to describe the issues.) It seemed to be connecting sporadically. When it was connected, I tried a few queries, created a test database and table, and tried the following:

Code:

declare @MaxLoops int = 100
declare @LoopCounter int = 0

while (@LoopCounter < @MaxLoops)
begin
     set @LoopCounter = @LoopCounter + 1

     --select SYSDATETIME(), * from NealTest.dbo.NealTest
     waitfor delay '00:00:01'
     print @LoopCounter
     print SYSDATETIME()
end

My original intent of the script was to see if we lost connections after it got "rolling", in other words I could set it up and increase the @MaxLoops and let it run for an hour or something. (Could also add try/catch to help it keep going to see if their were sporadic connectivity problems.)

The script (with the Select statement commented out) gave a

  Transport-level error

So we started highlight one or two lines at a time. The declare worked fine, and I could print the values after the declare. When we lowered the @MaxLoops to 5 it actually ran.  We increased to 15 it failed with Transport-level error. The same script runs fine (even up to @MaxLoops = 1000) from any other client in SSMS 2008 or 2012 client running against the same SQL Server.

This lead me to think that the packet size involved might have been the issue. I put about 20 rows of data with 40 bytes per row, and was able to select the entire table, which seemed to nullify the idea that the packet size was the issue.

And further, from the BizTalk App Server #1, we can use SSMS to connect to any other SQL Server in our shop, and it works fine. So the issue seems to be specifically between these two pairs of servers.

We are stuck, trying to decide if we should call Microsoft, rebuild the SQL Server, or what.

The SQL Server @@Version is

  Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
  Dec 28 2012 20:23:12
  Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on
  Windows NT 6.2  (Build 9200: )

The original issue was that the BizTalk configure utility was having the same "Transport-level errors".  In the current scenario, BizTalk is not even installed.  If SSMS cannot connect, we don't expect BizTalk to connect.

There is no firewall between these internal servers. Could some GroupPolicy have any impact?


texts: MongoDB Stuck with Jumbo chunks while exhausting a shard

texts:
I'm running a shared MongoDB 2.4 cluster.
I'm currently in a process of exhausting a shard and I'm down to the last 15 chunks needs to be migrated out of 9660.

My issue is that these chunks are too big as the shard key wasn't the best one, and dumping/restoring the 2 collections that has these issues is not an option at this moment.

I've tried to manually split the chunks, but it keeps on failing with this error: { "cause" : { }, "ok" : 0, "errmsg" : "split failed" }

I also tried to raise the chunk size, but that didn't work out as well.
Is there another solution I might be missing?

EDIT - Added chunk information as requested - Based on AdamC function.

mongos> AllChunkInfo("db.coll1", true)
ChunkID,Shard,ChunkSize,ObjectsInChunk
db.coll1-company_name_-6727988435900812827,shard0003,148256192,248752
db.coll1-company_name_-6615369920888926287,shard0003,88895188,149153
db.coll1-company_name_-6595735839523245520,shard0003,87448696,146726
db.coll1-company_name_-6490024596613592713,shard0003,32099368,53858
db.coll1-company_name_-3125507653387230887,shard0003,89171732,149617
db.coll1-company_name_-3084465672933415339,shard0003,105551600,177100
db.coll1-company_name_-2997585070019050064,shard0003,120763904,202624
db.coll1-company_name_-2993901072357262199,shard0003,86105908,144473
db.coll1-company_name_-2543788723715016046,shard0003,123507888,207228
db.coll1-company_name_4754688535895874026,shard0003,91061648,152788
db.coll1-company_name_5062850413490905708,shard0003,118145676,198231
db.coll1-company_name_5483223634690519314,shard0003,111475244,187039
Summary Chunk Information
Total Chunks: 12
Average Chunk Size (bytes): 100206920.33333333
Empty Chunks: 0
Average Chunk Size (non-empty): 100206920.33333333

mongos> AllChunkInfo("db.coll2", true)
ChunkID,Shard,ChunkSize,ObjectsInChunk
db.coll2-company_name_-5389355967913336416,shard0003,95282040,94808
Summary Chunk Information
Total Chunks: 1
Average Chunk Size (bytes): 95282040
Empty Chunks: 0
Average Chunk Size (non-empty): 95282040

mongos> AllChunkInfo("db.coll3", true)
ChunkID,Shard,ChunkSize,ObjectsInChunk
db.coll3-company_name_-3231146661862124155,shard0003,101878644,173263
db.coll3-company_name_658930972165413978,shard0003,142545900,242425
Summary Chunk Information
Total Chunks: 2
Average Chunk Size (bytes): 122212272
Empty Chunks: 0
Average Chunk Size (non-empty): 122212272

Thanks,
Meny


texts:
Can anyone help me to work out below T-SQL code. I am facing issue while adding the second value. The first value gives the state of SQL Service and second value gives the SQL last restart time. I am not a core developer so can anyone help me .

if object_id ('tempdb..#temp1') is not null
drop table tempdb..#temp1

create table #temp1
([Current Service State] nvarchar(200),
[SQL_Server_last_RestartOn]nvarchar(200))

insert into  #temp1
EXEC master.sys.xp_servicecontrol N'querystate',N'MSSQLSERVER',
(select min(login_time) from master..sysprocesses) as SQL_Server_last_RestartOn


texts:
Is there any way to create a new schema from template a similar way to creating new database?
CREATE DATABASE music TEMPLATE template0;

Maybe something like this imaginary snippet
CREATE SCHEMA myschema TEMPLATE public;

I don't want to use pg_dump or pg_restore the only available option to me is SQL commands.
PS. I use PostgreSQL 13.1


texts:
I use sqlplus and all non-Latin characters appear as '?' when I run select queries. When I run insert or update queries with non-Latin characters I get question marks on a web page.

select * from nls_database_parameters; gives NLS_CHARACTERSET: AL32UTF8.
I've exported the NLS_LANG variable: export NLS_LANG=RUSSIAN_CIS.AL32UTF8, but this didn't make any changes.

What is the problem? Of course I use UTF-8 working in Linux terminal and I can see the script file contents executing cat script.sql.

If I execute alter session set nls_language='russian' I also face with question marks in ora-xxxxx messages.


texts: Should I divide my table into simpler ones or should I leave it the way it is?

texts:
I have a table which must store data related to an examination
The structure of the table is as follows

Table name: Exam_Routine

| Year |  Date   |Stream | Paper_Code

Since I have noticed that there will be only 4 values foryearin the entire table, I decide d to decompose my table into four different tables featuring different years as

Table name: Exam_RoutineYear_1

| Date   |Stream | Paper_Code

Table name: Exam_RoutineYear_2

| Date   |Stream | Paper_Code

Table name: Exam_RoutineYear_3

| Date   |Stream | Paper_Code

Table name: Exam_RoutineYear_4

| Date   |Stream | Paper_Code

Is this a better option? or the first one is better? which one will be more helpful.


texts:
I'm trying to configure MongoDB in OS X Mountain Lion. I've installed with Homebrew; I have followed lots of tutorials, but I have got an issue which I haven't been able to solved.

The issue is related with launchctl unload and launchctl load commands. My homebrew.mxcl.mongodb.plist is located at /usr/local/Cellar/mongodb/2.4.6/ and it contains the following code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
  <key>Label</key>
  <string>homebrew.mxcl.mongodb</string>
  <key>ProgramArguments</key>
  <array>
    <string>/usr/local/Cellar/mongodb/2.4.6/mongod</string>
    <string>run</string>
    <string>--config</string>
    <string>/usr/local/Cellar/mongodb/2.4.6/mongod.conf</string>
  </array>
  <key>RunAtLoad</key>
  <true/>
  <key>KeepAlive</key>
  <false/>
  <key>WorkingDirectory</key>
  <string>/usr/local</string>
  <key>StandardErrorPath</key>
  <string>/usr/local/Cellar/mongodb/2.4.6/data/log/mongod.log</string>
  <key>StandardOutPath</key>
  <string>/usr/local/Cellar/mongodb/2.4.6/data/log/mongod.log</string>
  <key>HardResourceLimits</key>
  <dict>
    <key>NumberOfFiles</key>
    <integer>1024</integer>
  </dict>
  <key>SoftResourceLimits</key>
  <dict>
    <key>NumberOfFiles</key>
    <integer>1024</integer>
  </dict>
</dict>
</plist>

If I executed sudo launchctl load -w /usr/local/Cellar/mongodb/2.4.6/homebrew.mxcl.mongodb.plist, in http://localhost:28017/ appears MongoDB's http diagnostic access information. But if I execute sudo launchctl unload -w /usr/local/Cellar/mongodb/2.4.6/homebrew.mxcl.mongodb.plist does not disconnected and appears the same in the http://localhost:28017/ url.

To shutdown Mongo I've need the following commands:

use admin
db.shutdownServer()

How can I do to shutdown Mongo automatically with launchctl unload command?


texts: Is it bad practice to calculate within the insert query?

texts:
I am trying to do a web application for time sheets.
I have three options to calculate the value of hours (AFAIK):

In the form
In the backend
In the query

Is it smart or bad practice to do this calculation in the SQL query?
with list as (select code_id,code_ppu from code)

insert into hr values
(default,'JD','20-0001','PM','testing',1.25,
(select code_ppu from list where code_id='PM'),
(select 1.25 * code_ppu from list where code_id='PM')),

(default,'JD','20-0001','COPY','testing',2.25,
(select code_ppu from list where code_id='COPY'),
(select 2.25 * code_ppu from list where code_id='COPY'));

db fiddle https://www.db-fiddle.com/f/f8nZn5LQfbmKxAdxX2fdej/50


texts: Column Editing is Disabled. How to Enable?

texts:
I am using MySQL Workbench, in order to design an ERD (Entity Relation Diagram).

I can make use of indexes, triggers, etc. but for some reason the columns tab, which allows me to add data or just edit the tables is disabled. I have searched online without success. Any ideas on how to enable it?

I would like to be able to add data into my tables, which I cannot now do, since the process is disabled.


texts: MySQL Error 1064 (42000). Insert failure on MySQL table

texts:
i am making chatting software in which whenever a new friend is made the details are added in current users friend table. similar insert queries are working for other purposes but when used for this it shows syntax error.
so please help me out. also do tell me while showing error it shows whatsapp_friends as table name which is my other table but not 9636354343_whatsapp_friends.

INSERT INTO 9636354343_ whatsapp_friends  (friend_no ,friend_name )
VALUES (1111111111,'gurmeet');


texts: Why does MYSQL use blocking locks instead of optimistic attempts that fail immediately?

texts:
Why isn't optimistic concurrency control more commonly supported at the database layer? It is a valid alternative and can completely do away with any blocking locks. For certain usage patterns, this will offer complete safety with better performance.
The primary argument for OCC is:
When a SELECT or UPDATE blocks on a lock, there is no guarantee that it will return successfully and failure/success has to be handled at the application layer. Instead of forcing the application to wait for the timeout period before giving the result, we provide the result immediately and let the application figure out how it wants to handle the failure.
There are use cases where this approach will primarily cause issues like starvation (Primarily with large writes, or highly contentious changes). However, in a lot of standard cases (such as building a blog/forum/maybe even a bank), contentions will be rare and seem like they'll be better dealt with by the optimistic pattern.
What am I missing?

I have attempted to rephrase my question with better terminology to clarify the question a little bit more. Below is the question as it was originally asked. Feel free to remove it since it is there in the history as well.
Essentially, I am trying to understand the reasoning for why conflicting changes are blocked in MySQL instead of failing them immediately. At first glance, blocking doesn't seem to provide much benefit over failing immediately:

The client can always retry if it is important to commit the data
The client has to wait till any conflicts are resolved in both cases

Overall, it seems to me like a system that involves no blocking locks, purely relying on atomically committing changes in order, while failing those that conflict with other committed transactions would fare better in terms of performance. The client will have to implement retries and this could be non-ideal for large transactions, but that could be a special case in which we switch to blocking mode.
Any thoughts and explanations as to what I'm missing here?


texts: SQL Server considerations when backing up / restoring a database that uses replication

texts:
In the event that I need to restore a database involved in replication, what considerations do I need to keep in mind?

Do I need to keep backups of both the publisher and the subscriber?
Can I restore only the publisher and have the subscriber automatically sync up?
If the subscriber crashes, is it possible to restore from its back up and have it automatically sync up?


texts: Restore FULL Oracle DB from ORADATA after DISK fail

texts:
my system DISK crashed and know I have to restore my oracle from ORADATA only.
CTL, DBF, REDO is okay because system was shutdown normally.
I installed new harddisks and new OS.
After that I installed Oracle 12.1 Standard Edition One with database.
Than I tried shutdown and restart with my old ORADATA (moveing DIR, like with Postgres).
It doesn't work. ;-(((
What is a good working way? ;-)
Thanks a lot.
Regards, Jan


texts: File permissions for SQL Server 2014 virtual account

texts:
I have two SQL Server 2014 instances installed on the same server - one for production, and one for development.  I have set up a SQL Agent job that identifies the most recent backup from the production instance and restores it on the development instance.  When I run the job, it gives me an error like this (names altered to protect the innocent):

  Executed as user: NT SERVICE\SQLAgent$MyAppDEV.
  Cannot open backup device 'E:...MyApp_backup_2016_02_24_092912_0136837.bak'.
  Operating system error 5(Access is denied.).
  [SQLSTATE 42000] (Error 3201)
  RESTORE DATABASE is terminating abnormally.
  [SQLSTATE 42000] (Error 3013).
  The step failed.

I read a little about the virtual accounts that SQL Agent runs under.  If I open the folder in question and give read permissions to NT Service\SQLAgent$MyAppDEV, I still get the same error.  But if I give read access to the Authenticated Users built-in account, everything works.

What gives?  The error message purports to tell me what security context the job is using, but that doesn't seem to match up with reality.


texts: How to partition existing table in postgres?

texts:
I would like to partition a table with 1M+ rows by date range. How is this commonly done without requiring much downtime or risking losing data? Here are the strategies I am considering, but open to suggestions:

The existing table is the master and children inherit from it. Over time move data from master to child, but there will be a period of time where some of the data is in the master table and some in the children.
Create a new master and children tables. Create copy of data in existing table in child tables (so data will reside in two places). Once child tables have most recent data, change all inserts going forward to point to new master table and delete existing table.


texts:
I have an issue with re-starting my MySQL 8 service on Ubuntu 19 after a force shutdown in normal mode.

Originally I was able to re-start the service in the save mode (had to do

innodb_force_recovery = 3  #in my cnf

However, switching this setting back to 0, in order to start up the db in normal mode, gives me the following error -

Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.

I tried doing

mysqlcheck -uroot -p on_every_database

on every database, and all tables came back as OK .

I'm in the process of doing auto-repair on all db's, but thats 300gb total.  Is there anything else I can try to fix the DB , without blowing everything away and restoring from mysql dumps?

p.s. stats on pc - 32gb ram total with 8gb for buffer pool size + 2tb ssd.  so more than enough ram/space.

output for systemctl status mysql.service

Mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: en
   Active: failed (Result: exit-code) since Thu 2019-10-03 19:57:34 PDT; 3min 54
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 8810 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=
  Process: 8849 ExecStart=/usr/sbin/mysqld (code=exited, status=2)
 Main PID: 8849 (code=exited, status=2)
   Status: "Server startup in progress"

Oct 03 19:57:34 bob123 systemd[1]: mysql.service: Service RestartSec=100ms expir
Oct 03 19:57:34 bob123 systemd[1]: mysql.service: Scheduled restart job, restart
Oct 03 19:57:34 bob123 systemd[1]: Stopped MySQL Community Server.
Oct 03 19:57:34 bob123 systemd[1]: mysql.service: Start request repeated too qui
Oct 03 19:57:34 bob123 systemd[1]: mysql.service: Failed with result 'exit-code'
Oct 03 19:57:34 bob123 systemd[1]: Failed to start MySQL Community Server.


texts:
I have read about mongodb role based access control and I have understood it to a large extent. I was going through this tutorial and when you run show roles on the mongo shell it returns a different set of roles for a given database.

Why does each database have its own roles? Yet we allocate roles to users. Does this mean that if you create a user in that db they can only be allocated roles present in that database?
What is importance of having an authentication database as opposed to saving all uses in a single database, for example like admin? Is there any advantage of having different authentication databases?


texts:
I read a lot of articles that PARTITIONing is not helpful in most cases. I believe it should speed up the queries in my case. I have a table with the structure of

CREATE TABLE pages
(
page_id int(11) unsigned NOT NULL AUTO_INCREMENT,
category_id smallint(5) unsigned,
title varchar(255),
created datetime,
updated datetime,
FOREIGN KEY(category_id) REFERENCES categories(category_id) ON DELETE CASCADE,
UNIQUE INDEX (category_id,title),
INDEX(title),
PRIMARY KEY(page_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci ROW_FORMAT=COMPRESSED

The table is close to 1 billion rows and 200-1000 category_id.

Almost all queries have category_id in it.

I consider PARTITIONing the table as

PARTITION BY KEY(category_id)
PARTITIONS 40; // between 20-50

Is it worthy?


texts: Writing to a DKNF database, but reading from a de-normalized table for performance?

texts:
Would the following approach be reasonable?

The Goal: queries have to be relatively fast, but without the hassles incurred by writing to a de-normalized database.

The Context: A database for handling user profiles for some CMS. User profiles sometimes change, but are not updated too regularly; so for the most part the information for each user remains the same for some time.

The Database Design:

First, we put together a highly normalized database, call it "N". User profiles are stored in N.
Being highly normalized, N is secure and elegant to update, but slow to query. So we take all the data stored in N and we create a single de-normalized table. We call this table T.
T is used for the sole purpose of retrieving user information. It is only read from, not written to, because it's prone to anomalies and would be a mess to update.

With one exception:

To table T, we add a boolean column, "hasChanged", default value "FALSE".

The CMS's algorithm for handling queries:

Suppose we want to load user 47's profile.

If hasChanged == FALSE for a row with ID == 47, we can safely retrieve the user profile from this row. (Because we know nothing has changed.)
If hasChanged == TRUE, then row 47 in T cannot be trusted (something has changed), so the query is passed on to the reliable but slow N.
When a user updates his profile, N is updated. When N is updated, hasChanged is set to TRUE on all rows in T containing information that could be affected by this change.
T is completely rewritten by the system whenever it becomes too unreliable (this is determined by how frequently queries are passed on to N).


texts:
I just noticed something peculiar. We're using AlwaysOn availability in one of our environments with a read-only secondary replica. However, checking the .mdf / .ldf files for the secondary replica, it shows a modification time of several months ago. Similarly, using sp_helpdb 'DBNAME' in the secondary replica, it claims the space is significantly less than in the primary node.

Still, the DB shows as being Synchronized in SSMS object explorer, and upon reading the data in the DB, it's clear that the latest data is being replicated properly.

This is not a problem, I would simply like to understand what's going on, as I can't seem to find this information by googling for it. Why doesn't the space usage seem to be updated properly? Is it a question of permissions, or a symptom of the read-only mode itself, or what?

Thank you!


texts: Under what circumstances can a division expression reduce the number of rows in a query's result?

texts:
I'm experiencing a problem with a query that I've written.

Given a table with the schema:

create table somevals(division varchar(100),
                      subdivision varchar(100),
                      good float,
                      bad float);

I would like to find, for each unique (division, subdivision), the sum of good values, the sum of bad values, and the ratio of good to bad values. So, I wrote the query:

select division, subdivision, g, b, g / b as ratio
from (select division, subdivision, sum(good) as g, sum(bad) as b
      from somevals
      group by division, subdivision
     ) as temp

The problem is that this returns far fewer rows than I would expect. However, if I remove the division (g / b as ratio), this query returns the correct number of rows in the result (about three times as many). None of the columns contain Null values. None of the bad values are less than or equal to zero, so sum(bad) will always be non-zero.

I am at a loss for how this could be happening. Under what circumstances can adding a division expression in the select statement reduce the number of rows in the result?


texts:
My batch file will contain some thing like this :

    set MUSER=root
    set MPASS=xprd123
    set MHOST=172.33.49.55
    set DB=DBName1
    set MBIN=C:\Program Files\MySQL\MySQL Server 5.5\bin
    set scriptpath=D:\STATS_COLLECTOR
    "%MBIN%\mysql.exe" -u"%MUSER%" -p"%MPASS%" -h "%MHOST%" "%DB%" -e "set @2:='%scriptpath%'; source DBQuery1.sql;"

Content of DBQuery1.sql will be :

SELECT 'INNODB_OS_LOG_WRITTEN','QUESTIONS'
UNION
SELECT
MAX(IF(variable_name = 'INNODB_OS_LOG_WRITTEN', VARIABLE_VALUE, NULL)) AS INNODB_OS_LOG_WRITTEN,
MAX(IF(variable_name = 'QUESTIONS', VARIABLE_VALUE, NULL)) AS QUESTIONS
FROM information_schema.GLOBAL_STATUS
INTO OUTFILE '@2/stats.csv'
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
;

I am not sure this is the way to pass path to the SELECT query inside DBQuery1.sql.

This code is not working.

I looked at other posts on passing path to batch file, but whatever I tried, I am not able to solve this issue.

When I execute the batch file, I am not sure if the path is getting passed into the sql file and if yes, why @2 is not getting the path passed.

Could anybody kindly help on this.


texts: Why does SSDT deploy inline FK Constraint as WITH NOCHECK?

texts:
Given the following Foreign Key Constraint definition inline of a Create Table definition in a SSDT project:

CREATE TABLE A
(...Columns...),
CONSTRAINT [FK_O] FOREIGN KEY ([OID]) REFERENCES [dbo].[O] ([ID]) ON DELETE CASCADE NOT FOR REPLICATION,
(...)

and the following quote from MSDN

  WITH CHECK | WITH NOCHECK
  Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.

, I wonder why the FIRST deployment result of this NEW table does look like that using a constraint WITH NOCHECK option as follows:

ALTER TABLE [dbo].[A]  WITH NOCHECK ADD CONSTRAINT [FK_O] FOREIGN KEY([OID])
REFERENCES [dbo].[O] ([ID])
ON DELETE CASCADE
NOT FOR REPLICATION
GO

ALTER TABLE [dbo].[A] CHECK CONSTRAINT [FK_O]
GO

So the question is, shouldn't the inline Constraint definition that does not allow to define "with Nocheck" paired with the rule from the MSDB page result into a constraint using the WITH CHECK option on this NEW table? Why do we get a WITH NOCHECK Constraint here?


texts: InnoDB - How to get top locked tables and rows which are locked

texts: I was searching for a tool OR query which can give me top locked tables and which particular row is locked, Is it possible to get ?


texts: Recommended Database(s) for Selecting Random Rows

texts:
I'm familiar with selecting random rows from a table in PostgreSQL. It's really slow. I've looked into some other databases and they all seem to be bad at it. Mostly because it's not a normal use case for any of them.

Is there any type of database that can select a random row quickly? The only hard requirement is that it be installable on Linux.

So far I have a table with 500 million rows and anticipate it growing to a few billion in the near future and would like to be able to select a random item. I'd also like to be able to delete the same row a short time later, so it'll need some sort of key/index for that.

Right now my psql schema has 3 columns: a text field, a date, and an integer sequence id. The date is not important and doesn't need to be in the new database.


texts: Sync SQL Server Transaction Logs to Redshift

texts:
I am researching the plausibility of syncing SQL Server logs to an AWS Redshift data warehouse.

Redshift has the COPY command to do parallel loads from S3 to Redshift already. Additionally, there are many 3rd party tools that promise near synchronous replication of the transaction logs.

Are these 3rd party tools the only solution?
Are these tools faster than an S3 COPY?
Is there an SQL Server based solution that still includes Redshift?


texts: how do you output a plsql array via ref cursor

texts:
I have a legacy procedure that returns a plsql assoc table to a C++ DLL.  Now I need to call it via C# and ODP.NET.  I have been unable to find any info on how to call a proc that returns a pl/sql assoc array (plenty on how to send data to one that takes as an input).

Thinking that if I can have the proc return a ref cursor then it should not be an issue since I have done it plenty of times

However, have not found examples of doing that and all attempts have failed.

Any help would be welcomed.

code below.  it provides an orderid as input. it calls another proc relOrders  that returns an array that is put into pl sql table.

TYPE tab_number12 IS TABLE OF NUMBER (12)
      INDEX BY BINARY_INTEGER;

PROCEDURE GetOrders (p_Orderid          IN     NUMBER,
                                    p_subOrderIds            OUT tab_number12)
   IS
      allOrder   DBMS_SQL.number_table;

   BEGIN
      allOrder (1) := p_Orderid;

      relOrders (allOrder);

      FOR i IN allOrder.FIRST .. allOrder.LAST
      LOOP
         p_subOrderIds (i) := allOrder (i);
      END LOOP;
   END;


texts:
I have a neo4j database with some data stored in Points (e.g. coordinates: point({srid:4326, x:-4.224099535439225, y:52.10621881941465})).

I want the neo4j browser to honour these points when laying out the graph visually. Is there a way to interact with its layout manager, and ask it to use these points?


texts: Should I be using this to export the data using the SQL Developer 'Data Pump Export Wizard' connected as a DBA?

texts:
I am trying to learn more about Oracle databases in my spare time.  I have Oracle Enterprise available in a Docker Container.  I am a developer rather than a DBA.  I have plenty of experience with SQL Server.
I am trying to use the data pump feature.  So far I have done this:

Created a user called dummy and created a table (Dummy.table1).
Added a few rows to table1.
Created a user called admin and given this user dba access.
Login to SQL Developer as admin and select: View/DBA.
Connect to my database and use Data Pump Export Wizard to create a dump file.
Generate a .sql file from the .dmp file.

I have tried all four options (Database; Tablespaces; Schemas and Tabeles) under: 'Types to be exported in data pump', selecting 'Data and DDL' each time, however I cannot see the INSERT statements, which should be created for Dummy.table1.  Why is this?
The only thing I can think of is that there is a menu item called: Tools\Database Export.  Should I be using this to export the data? I would of thought that the data should be exported when using the Data Pump Export Wizard; connected as a DBA?


texts: SQL Server Availability Group Design Question/RFC

texts:
We are in the early stages of planning an upgrade/redesign of our SQL environment and I am seeking advice/feedback.

The scenario:

Current SQL Server 2008R2 EE on Windows Server 2008 R2, blade servers with databases on SAN, some mirroring
Target SQL Server 2012 R2 EE (should I consider 2014?) on Windows Server 2012R2, blade server with SAN storage
SQL Server 1 to be primary Infrastructure database server (the IT databases, vCenter, AV, SCOM etc.)
SQL Server 2 to be primary user database server
SQL Server 3, secondary server in availability group configuration for Servers 1 & 2
SQL Server 4 secondary mirror of AGs in a different data center, 10GBs or more between datacenters
SQL servers 1 & 2 should not fail over to each other

Migration process will be a database at a time, the existing environment will be available until transition is complete

Can this be done?  Is there a better way to do it?  Is more information needed to determine if there is a better way, if so, what?  Where can I find some reliable documented guidance?

Thank you!


texts: Is PostgreSQL JSONB @> operator equal to ->''=?

texts: Is the jsonb_column @> '{"key":value}'::jsonb operator equal to jsonb_column->'key' = value? in terms of result, performance, and indexes that will be used?


texts:
I want to know why these queries give different results

    Select *  FROM Table1 T1
    Left Join MyTable MT
    ON MT.studentId = T1.StudentId
    AND T1.class = 7

    Select *  FROM Table1 T1
    Left Join MyTable MT
    ON MT.studentId = T1.StudentId
    WHERE T1.class = 7


texts:
How can I output my Unix timestamps to human readable GMT?

SELECT
      category
      ,fullname
      ,shortname
      ,idnumber
      ,startdate
      ,visible
      ,timecreated
      ,timemodified
  ,count( my.id ) AS mydata
FROM mdl_mytable
JOIN mycontext ct ON ( ct.my = cat.id )
LEFT JOIN myjoin ra ON ( mycontext = cat.id )
WHERE ct.mynumber =10000000
GROUP BY shortname, fullname, .category
      ,.idnumber
      ,.startdate
      ,.visible
      ,.timecreated
      ,.timemodified
ORDER BY myorder ASC

Note: The query has been changed for security reasons.


texts: Should I split my tables into different tables when all of the data is dependant on the primary key?

texts:
For my assignment I have to make a database for my licensing application. I have 12 columns and one column is for the license key. All other columns are data that is related to the license key. The license key is always different. I'm getting told that it's better to split the table into more tables to remove redundant data.
The table is as follows:

  license_code
  product_amount
  product_limit
  product_name
  start_date
  end_date
  first_registration_date
  most_recent_check_date
  registration_end_date
  hardware_id
  IP
  status

Currently I have it split into this:

Table: License

  license_code*
  product_id*
  start_date
  end_date
  first_registration_date
  most_recent_check_date
  registration_end_date
  product_amount
  product_limit
  hardware_id
  IP
  status

Table: Product

  product_id*
  product_name

I'm really thinking that it's unnecessary to split it because all the data is dependant on the license_code column anyway. Can anyone help me out? Thanks.

EDIT: explaination of how it's used

A license key/code is basically a unique string. To use a product (software program), a license key has to be used. A product can be activated with multiple license keys, person 1 can use code X to activate product Y, person 2 can use code Z to activate product Y. So a license key can activate multiple instances of a product but it can only be used for a single product.

I hope I'm clearer with this explaination.


texts: How to wait for SQL Server replication reinitialization via TSQL

texts:
We use transaction and push replication. Every night after the data import 2 replication targets are initialized with the following commands.

USE [dbImport]

-- Execute at the Publisher to reinitialize the push subscription.
EXEC sp_reinitsubscription
@subscriber = N'Target2',
@destination_db = N'dbImport',
@publication = N'dbImportPub';
GO

-- Start the Distribution Agent.
USE msdb
EXEC sp_start_job @job_name = 'Source1-dbImport-dbImport-Pub-Target2-35'
GO

The next step waits for 15 minutes because this reinitialization needs to take normally 10 minutes and we add some more time to be on the safer side.
Sometimes the reinitialization needs a little bit longer than 15 minutes, so the indexes which are created in next step on target aren't correct.

I can't check for stopping of the distibution job because in transaction replication it never stops.

How can I wait for the end of reinitialization (initial push of all scripts and data) on target2 (to afterwards add the indexes "on time")?

Stefan


texts: Does SQL Server's serializable isolation level lock entire table

texts:
Me and a colleague of mine discussed the implications of use of the serializable isolation level. He said it locked the entire table, but I disagreed to that telling him it potentially could but it tries to apply range locks and it doesn't apply true serialization as explained here: The Serializable Isolation Level.

I can't find anything in the docs either for the "locks entire table": SET TRANSACTION ISOLATION LEVEL.

The doc states a bunch of things regarding range locks, so in theory you could lock the entire table by simply having a range lock that locks the entire range of possible values in the table, but it doesn't lock the table.

Am I completely wrong here?  Does it in fact lock the entire table (or tables)?


texts: How to find the length of multiple column index in MySQL

texts:
Is there a way to find out what is the length in bytes for the unique index (NKU) key created through the following command:

CREATE TABLE test_ (
  testId bigint(20) NOT NULL,
  name varchar(75) DEFAULT NULL,
  key_ varchar(200) DEFAULT NULL,
  user varchar(255) DEFAULT NULL,
  PRIMARY KEY (testId),
  UNIQUE KEY NKU (name,key_,user)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


texts:
could you help me to figure out the solution for a MySQL replication error?
mysql replication error: (Last_Errno: 1452)

Last_SQL_Error: Error
'Cannot add or update a child row: a foreign key constraint fails


texts:
Whats better in terms of SQL server efficiency; to use sub queries or joins?

I know uncorrelated are better than correlated sub-queries. But what about joins?

The SQL becomes more readable and understandable using joins

OUTER JOIN and check for NULLS

but is it worse or better for performance of the DB?


texts:
In the system I work on there are a lot of stored procedures and SQL scripts that make use of temporary tables. After using these tables it's good practice to drop them.

Many of my colleagues (almost all of whom are much more experienced than I am) typically do this:

TRUNCATE TABLE #mytemp
DROP TABLE #mytemp

I typically use a single DROP TABLE in my scripts.

Is there any good reason for doing a TRUNCATE immediately before a DROP?


texts: is_not_trusted = 1 after Error-Free WITH CHECK CHECK CONSTRAINT

texts:
I'd like SQL Server 2005 SP4 to trust a certain foreign key constraint. Executing an ALTER TABLE statement that enables + checks the constraint returns Command(s) completed successfully yet sys.foreign_keys is_not_trusted stays set at 1.

Any ideas why this could be? Does ALTER TABLE sometimes silently swallow constraint errors?

ALTER TABLE {table} WITH CHECK CHECK CONSTRAINT {constraint_name}
-- returns: Command(s) completed successfully.

SELECT is_not_trusted, is_disabled
FROM sys.foreign_keys
WHERE name = {constraint_name}
-- returns 1, 0

Dropping and re-creating the constraint also completes successfully but the re-created constraint also isn't trusted.

ALTER TABLE {table} DROP CONSTRAINT {constraint_name};
-- returns: Command(s) completed successfully.

ALTER TABLE {table} ADD CONSTRAINT {constraint_name}
FOREIGN KEY ({column_name})
REFERENCES {referenced_table_name} ({referenced_column})
ON DELETE CASCADE
ON UPDATE NO ACTION
NOT FOR REPLICATION;
-- returns: Command(s) completed successfully.

SELECT is_not_trusted, is_disabled
FROM sys.foreign_keys
WHERE name = {constraint_name}
-- returns 1, 0

For reference, the key column is SMALLINT NOT NULL.


texts: How to know when a refresh of a materialized view has been completed

texts:
After a specific event(e.g. someone add new data into the database via a GUI), I need to refresh a materialized view that aggregates some data and only after that refresh is complete I have to query from the MW and to show in the GUI the updated results.

Now, my problem is that I need a way to know when the refresh is complete so that I can then query and be sure to obtain an up to date result.

The MV is specified in this way:

CREATE MATERIALIZED VIEW ASSAY
("TYPE","METHOD",COMPOUND_CORPORATE_ID,RESULTS_AVG,RESULTS_ST_DEV,RESULTS_N)
AS
select "TYPE","METHOD",AVG("RESULTS"), STDDEV("RESULTS") , COUNT("RESULTS")
from ASSAY_INHIB
where upper("VALIDATION")='YES'
group by "TYPE","METHOD",COMPOUND_CORPORATE_ID;
CREATE INDEX assays_inhib_parent ON ASSAY(compound_corporate_id);
ALTER MATERIALIZED VIEW ASSAY  REFRESH FORCE   ON DEMAND;

I call from the GUI a stored procedure with the command exec REFRESH_MV() and the stored procedure is like this :

CREATE  PROCEDURE REFRESH_MV () AS
BEGIN
    DBMS_MVIEW.REFRESH('ASSAY');
END REFRESH_MV;

The problem is that I don't know when I can query the MV and be sure that data are up to date. Can anybody help?

Thanks


texts: Import 76 MB xlsb file makes Import Dialog crash

texts:
I have xlsb file which is around 76 MB. I need import this data into Sql Server. I am trying with differnt options to import including,

Sql Server Native Client
Sql Server Oledb Provider
.Net Framework Provider for Sql Server

But it keeps crashing with this error inside event viewer,
Faulting application name: DTSWizard.exe, version: 15.0.1900.63
Faulting module name: MSVCR100.dll, version: 10.0.40219.325
Exception code: 0x40000015
Fault offset: 0x0008d6fd
Faulting process id: 0xef9c
Faulting application start time: 0x01d6abbe6621199c
Faulting application path: C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\CommonExtensions\Microsoft\SSIS\150\Binn\DTSWizard.exe
Faulting module path: C:\WINDOWS\SYSTEM32\MSVCR100.dll
Report Id: 1dfd6110-8990-4002-82d9-dd75f6110ef3
Faulting package full name:
Faulting package-relative application ID:

What else I can do to import this file?


texts: Any Idea how to migrate from PostgresSQL 8.4 to PostgresSQL 9.1.1 in opensuse12.1

texts:
I had a crashed opensuse 11.3 that's why I installed opensuse 12.1..
after I installed (via yast) and edited /etc/passwd

I do this:

 rcpostgresql start

and this shows on the /var/log/messeges

~ # less  /var/log/messages  | grep postgres
Dec 27 10:43:08 t1libora1 shadow[2393]: new group added - group=postgres, gid=26, by=0
Dec 27 10:43:08 t1libora1 shadow[2393]: running GROUPADD_CMD command - script=/usr/sbin/groupadd.local, account=postgres, uid=26, gid=0, home=, by=0
Dec 27 10:43:08 t1libora1 useradd[2396]: new account added - account=postgres, uid=26, gid=26, home=/var/lib/pgsql, shell=/bin/bash, by=0
Dec 27 10:43:08 t1libora1 useradd[2396]: running USERADD_CMD command - script=/usr/sbin/useradd.local, account=postgres, uid=26, gid=26, home=/var/lib/pgsql, by=0
Dec 27 11:08:45 t1libora1 su: (to postgres) root on none
Dec 27 11:08:45 t1libora1 postgresql[2998]: Starting PostgreSQL2011-12-27 13:08:45 GMT   FATAL:  los archivos de base de datos son incompatibles con el servidor
**Dec 27 11:08:45 t1libora1 postgresql[2998]: 2011-12-27 13:08:45 GMT   DETALLE:  El directorio de datos fue inicializado por PostgreSQL versión 8.4, que no es compatible con esta versión 9.1.1.**
Dec 27 11:08:50 t1libora1 postgresql[2998]: pg_ctl: no se pudo iniciar el servidor.
Dec 27 11:08:50 t1libora1 postgresql[2998]: Examine el registro del servidor.
Dec 27 11:08:50 t1libora1 postgresql[2998]: ..failed
Dec 27 11:08:50 t1libora1 systemd[1]: postgresql.service: control process exited, code=exited status=1
Dec 27 11:08:50 t1libora1 systemd[1]: Unit postgresql.service entered failed state.

how can I migrate?


texts: how to avoid duplicate record with certain conditon

texts:
Heading ##I have a table with data  like below

+----+--------+-----------+----------------+
| id |  name  | testfield |     status     |
+----+--------+-----------+----------------+
|  1 | raju   |        11 |         import |
|  1 | raju   |        11 |         ticket |
|  2 | ravi   |        22 |         import |
|  2 | ravi   |        33 |         ticket |
|  3 | ramesh |        44 |         import |
|  3 | ramesh |        44 |         ticket |
|  4 | rahul  |        55 |         import |
|  5 | rudra  |        66 |         import |
+----+--------+-----------+----------------+

now i want output like below

+----+--------+-----------+----------------+
| id |  name  | testfield |     status     |
+----+--------+-----------+----------------+
|  1 | raju   |        11 |         ticket |
|  2 | ravi   |        33 |         ticket |
|  3 | ramesh |        44 |         ticket |
|  4 | rahul  |        55 |         import |
|  5 | rudra  |        66 |         import |
+----+--------+-----------+----------------+

explanation: If I have a row of status 'ticket' for a (id,name)-pair, this row should be selected. If I have a row of status 'import' for a (id,name)-pairbut no row with status 'ticket' for such a (id,name)-pair, the row should be selected.

For example the rows id 1,2 and 3  have status 'ticket' so they should be displayed. The rows with id 4 and 5 do not have a corresponding 'ticket'-row (with same (id,name) values) so 'import' should be displayed.


texts: Best way to find out read/write distribution in MySQL?

texts:
People often say things like "application X is write heavy", 20/80 write vs reads, etc.

What metric(s) is most relevant for MySQL when checking if an application is write/read heavy?

Is checking Com_insert, etc enough, not taking into account number of rows read/modified, or is it checking iostat metrics?


texts: Rewrite trigger from PostgreSQL to MySQL

texts:
I have this trigger in PostgreSQL database:

CREATE FUNCTION connections_insert_update_connection_root_fnc() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
            BEGIN
                UPDATE connections SET connection_root = connection WHERE connection = NEW.connection AND NEW.connection_root IS NULL;
                RETURN NEW;
            END;
            $$;

How it will look in MySQL please?

The table connections in MySQL looks this:

| connections | CREATE TABLE `connections` (
  `connection` int(11) NOT NULL AUTO_INCREMENT,
  `connection_type` text,
  `connection_transport` text,
  `connection_protocol` text,
  `connection_timestamp` int(11) DEFAULT NULL,
  `connection_root` int(11) DEFAULT NULL,
  `connection_parent` int(11) DEFAULT NULL,
  `local_host` text,
  `local_port` int(11) DEFAULT NULL,
  `remote_host` text,
  `remote_hostname` text,
  `remote_port` int(11) DEFAULT NULL,
  PRIMARY KEY (`connection`),
  KEY `local_port` (`local_port`),
  KEY `connection_parent` (`connection_parent`),
  KEY `connection_root` (`connection_root`),
  KEY `connection_timestamp` (`connection_timestamp`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

I tried this. It is semantic correct please?

CREATE TRIGGER connections_INSERT_update_connection_root_trg AFTER INSERT ON connections
FOR EACH ROW
BEGIN
    IF connection = NEW.connection AND NEW.connection_root IS NULL THEN
    UPDATE connections SET connection_root = connection WHERE connection = new.connection AND new.connection_root IS NULL;
    END IF;
END$$

Thank you for your help.


texts:
We just argued that storing timestamp in sqlserver in microsoft does guarantee uniqueness, but im a little bit doubful about mysql/mariadb, because we are using parallel execution when inserting to the mysql/mariadb database.

So, Is using timestamp as primary key good idea - mysql/mariadb?

PS: There is always an PK and status but its important if we are using timestamp as the where clause in getting the actual remaining records to be archived/removed (so that it is efficient in querying the records).So its important its unique


texts: Is there any performance issue with including the "COLLATE" as part of the query as opposed to setting it for the column/table?

texts:
PostgreSQL has this "collate" concept. You can either tell a table's column to always have a "collate", or you can do it in the query.

If I do it in the query, does PG punish me somehow performance-wise? I mean, it makes sense that it can "prepare itself" if I tell the table/column to have a specific "collate", rather than it having to figure it out "on the fly", but is it the case?

Also, any general tips on whether or not I should be having the collate in the query or table/column definition?


texts:
I have a dacpac that has set the BlockOnPossibleDataLoss set to false, however the deployment is blocked when I run it with sqlpackage.exe, telling me "The column [a] is being dropped, data loss could occur."

However when I use the exact same deployment profile and publish from Visual Studio 2012 it goes through.


texts: Partition table data into dump files in a single command

texts:
I have a fairly large table of spatial data. Because it is spatial data, I have discovered that data pump uses a very large amount of space to import it. (I have seen it use approximately 40 GB of undo space to import less than 10 GB of data. This might be some kind of bug, but I'm stuck with it for now.) To this end, I would like to break my dump file up into pieces; this way I can import them one at a time to reduce undo usage.

However, writing several commands of the form

expdp DUMPFILE=expfil01.dump CONTENT=DATA_ONLY TABLE=MYSCHEMA.MYTABLE QUERY=MYSCHEMA.MYTABLE:\"WHERE       0 \< MYTABLE_ID AND MYTABLE_ID \<=  2000000\"
expdp DUMPFILE=expfil01.dump CONTENT=DATA_ONLY TABLE=MYSCHEMA.MYTABLE QUERY=MYSCHEMA.MYTABLE:\"WHERE 2000000 \< MYTABLE_ID AND MYTABLE_ID \<=  4000000\"

on up to the last ID is inconvenient, difficult to automate, and error prone. Additionally, the upper limit will change each time I run this, meaning that I have to query for it somehow and then create each command.

Partitioning by file size does not work. All dump files must be imported at the same time when partitioning by file size.

Does data pump's expdp have any way of partitioning separate dumps by some kind of GROUP BY clause or anything similar?


texts:
If i want to transfer schema ownership to another schema i know that i need to use something like:

ALTER SCHEMA destinationschema TRANSFER sourceschema.objectname;

But how is it different from:

ALTER AUTHORIZATION ON OBJECT::sourceschema.objectname TO destinationschema ;

Thanks in advance!


texts: Can subqueries produce different execution plans than CTE's?

texts:
I was talking with the bossman, and he prefers subqueries over CTE's. Personally, I loathe subqueries. He mentioned that subqueries can be faster, but I am not convinced. I ran this short test:

    with classes as
    (select top 10 Classkey from dimclass
    group by classkey
    order by count(1) desc),
    policies as (
    select CarrierKey, policykey, periodeffectivedate from dimpolicy),
    exposure as (
    select policykey, classkey  from DimExposure)

    select * from policies p
    inner join exposure x on p.PolicyKey = x.PolicyKey
    inner join classes c on x.ClassKey = c.Classkey

has an excution plan of:

https://www.brentozar.com/pastetheplan/?id=SJYJUZNHS

    select p.CarrierKey, p.PolicyKey, p.periodeffectivedate from dimpolicy p
    inner join (select policykey, classkey  from DimExposure) x on p.PolicyKey = x.PolicyKey
    inner join (select top 10 Classkey from dimclass
                group by classkey
                order by count(1) desc) c on x.ClassKey = c.Classkey

has the same execution plan:

https://www.brentozar.com/pastetheplan/?id=rJs_LbVSr

Question: Is this always the case? In the weird and wonderful world of SQL Server, the answer always seems to be it depends.


texts:
I have a mysql user account who has following Global privileges

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, PROCESS, REFERENCES, INDEX, ALTER,
SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW,
CREATE ROUTINE, ALTER ROUTINE, EVENT,
TRIGGER ON *.* TO 'maintenance'@'host' IDENTIFIED BY PASSWORD 'password';

I need to revoke insert, delete or update privileges on the certain tables  for this user. So, I tried this:

mysql> REVOKE INSERT, DELETE, UPDATE ON  Hotels.AllHotels FROM 'user'@'host';

An I am seeing the following error

  ERROR 1147 (42000): There is no such grant defined for user 'user' on host 'host' on table 'AllHotels'

I am performing the above actions as the mysql root user.

Could someone help please?


texts: Replicaton - The current transaction cannot be committed and cannot be rolled back to a savepoint

texts:
After creating a replication publication, I go ahead and create a subscription using the script below:

-----------------BEGIN: Script to be run at Publisher 'MY_PUBLISHER_SERVER'--------------
use [MY_PUBLICATION_DB]
exec sp_addsubscription
 @publication = N'MY_PUBLICATION',
 @subscriber = N'MY_SUBSCRIBER_SERVER',
 @destination_db = N'ORCA_Repl_Sub',
 @subscription_type = N'Push',
 @sync_type = N'automatic',
 @article = N'all',
 @update_mode = N'read only',
 @subscriber_type = 0

exec sp_addpushsubscription_agent
 @publication = N'MY_PUBLICATION',
 @subscriber = N'MY_SUBSCRIBER_SERVER',
 @subscriber_db = N'ORCA_Repl_Sub',
 @job_login = N'MY_COMPANY\sqlrpl',
 @job_password = N'MYP455w0rd',
 @subscriber_security_mode = 1,
 @frequency_type = 64,
 @frequency_interval = 0,
 @frequency_relative_interval = 0,
 @frequency_recurrence_factor = 0,
 @frequency_subday = 0,
 @frequency_subday_interval = 0,
 @active_start_time_of_day = 0,
 @active_end_time_of_day = 235959,
 @active_start_date = 20190423,
 @active_end_date = 99991231,
 @enabled_for_syncmgr = N'False',
 @dts_package_location = N'Distributor'
GO
--------------END: Script to be run at Publisher 'MY_PUBLISHER_SERVER'-----------------

to my surprise I get all the following errors when running the second procedure:

Msg 50000, Level 16, State 1, Procedure msdb.dbo.sp_add_jobstep_internal, Line 255 [Batch Start Line 14]
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3931, Level 16, State 1, Procedure sys.sp_MSadd_repl_job_unsafe, Line 376 [Batch Start Line 14]
The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.

Basically Replication Scripts Stopped Working..

How could be a solution for this?
where to find more information about these errors?