texts: PowerBI Service Account granting duplicate execute permissions on stored procedure almost constantly

texts:
I have PowerBi Server (Jan 2019) installed on my server. I can see from the default trace that that the PowerBi Service account NT SERVICE\PowerBIReportServer is granting execute permissions on a stored procedure ExtendEditSessionLifetime to the RSExecRole (within the ReportServer database):

GRANT EXECUTE ON [dbo].[ExtendEditSessionLifetime] TO RSExecRole

This is happening frequently many times during the hour, most hours (around 50 times an hour) there doesn't seem to a pattern that I can see and I can't find any documentation online about what the stored procedure does. I can't see anything obvious in the PowerBI logfiles or the event viewer either.

What could be causing this (or is it intended behaviour?)


texts: How to return values from "upsert" query in case when UPDATE has been performed?

texts:
WITH upsert AS (UPDATE tbl SET a = 2 WHERE a = 1 RETURNING tbl.*)
INSERT INTO tbl (a)
SELECT 1 WHERE NOT EXISTS( SELECT * FROM upsert )
RETURNING *

This "upsert" statement works however I would like to retrieve either UPDATE or INSERTED values. When upsert performs INSERT (row is not yet in db) then inserted values are returned correctly returned from query.

However when UPDATE is performed then no values are returned (but row IS updated). I've tried adding RETURNING upsert.* but it yelds error missing FROM-clause entry for table \"upsert\".


texts: Fill ibdata1 With Zeros/Nulls/etc Or Change A Table's Tablespace

texts:
I have a MySQL 5.5 server running on CentOS 6. The MySQL package is a MySQL-released version (MySQL-server-5.5.28-1.el6.x86_64), not the stock CentOS one. I have moved an existing database over from a single tablespace to innodb_file_per_table. This leaves me with a 150GB ibdata1 file.

The active space isn't a big deal as the rest of the database is quite large. However, I'd like to reduce the size of it for backups. My thinking is that if I can fill the empty space in it with mostly zeros, nulls, or anything else that'll compress well, that that'll cut a lot of space out of the backups.

I've searched around for ways to fill it with zeros and for a related question, filling with random data.

The obvious solution if there isn't a built-in solution, is to use a new table with a large column and insert zeros into it until it's around size of ibdata1. However, I've been unable to find a way to change a table's tablespace when innodb_file_per_table=1.


texts: Archiving data in SQL Server with stored procedure

texts:
Database in SQL Server 2014 has one table and is consuming around 17-20 GB of disk space each day. Assuming that level of usage remains fairly consistent  and not accounting for lower usage on weekends/holidays.

That would mean I am looking at requiring around 7.3 TB of space to house 1 year of table data. I want to figure out long term strategy is going to be for this, including archiving (when/where/how). The database currently contains just a tad over 1 billion rows.

I am trying to write a stored procedure to archive data in other table but I can do it once with my code. Later if I want I have to create one more table each time.

create PROC [dbo].[ArchiveData_auditlog1]
(
    @CutOffDate datetime = NULL
)
AS
BEGIN
    SET NOCOUNT ON

    IF @CutOffDate IS NULL
    BEGIN
        SET @CutOffDate = DATEADD(mm, -8, CURRENT_TIMESTAMP)
    END
    ELSE
    BEGIN
        IF @CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
        BEGIN
            RAISERROR ('Cannot delete orders from last three months', 16, 1)
            RETURN -1
        END
    END

    BEGIN TRAN

                SELECT * into  [dbo].[archive_auditlog2]

        FROM [dbo].[AuditLog_test]
        WHERE timestamp < @CutOffDate

        IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRAN
            RAISERROR ('Error occured while copying data to dbo.archive.account', 16, 1)
            RETURN -1
        END

        DELETE [dbo].[AuditLog_test]
        WHERE timestamp < @CutOffDate

        IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRAN
            RAISERROR ('Error occured while deleting data from dbo.archive.account', 16, 1)
            RETURN -1
        END

    IF @@TRANCOUNT > 0
    BEGIN
        COMMIT TRAN
        RETURN 0
    END
end

My question is what will be best way to fix problem, table has no dependency on other tables and has primary key on Id. I want to archive data based on timestamp column.

If I have to do partitioning how can I partition this big table, how can I migrate data to archive server.


texts:
Our SQL Agent jobs execute different DTSX packages at various steps.
e.g:

\\DEVLserver\DTSX\package1.dtsx

I want to keep the agent job script in TFS but when I promote it is there any way to change or publish it as:

\\TESTserver\DTSX\package1.dtsx
\\PRODserver\DTSX\package1.dtsx

by using variables?


texts: MS Access M:N Relationship query: exclude records with more than the requested values

texts:
I have a little problem when looking up records that have m:n relationships to other tables and therefore appear multiple times in link tables. I try to show it with a minimal example here:

Let's assume we have a table called "books", one called "authors" and a link table connecting both.

Some books are written by one author, others are written by multiple authors.

As we can see, book one (book_id = 1) is written by only one person (authorID = 1) while book two (book_id = 2) is written by two people (authorID 1 and 3).
If we now try to get the book which is written by author 1 and author 3 we can go like this:

SELECT book_id, title, year_of_publication, pagecount, cover FROM (book
INNER JOIN
(SELECT * FROM link_author_book WHERE author = 1) AS author1
ON author1.book = book.book_id)
INNER JOIN
(SELECT * FROM link_author_book WHERE author = 3) AS author2
ON author2.book = book.book_id

which leads to this:

I am pretty sure that my solution to this is not the best way to do it but in this case it works... anyway, when we try to get only those books returned that are written by author 1 and nobody else, we have a problem. In code, this selection looks like this:

SELECT book_id, title, year_of_publication, pagecount, cover FROM book
INNER JOIN
link_author_book ON link_author_book.book = book.book_id
WHERE author = 1;

and leads to this recordset:

while it is technically true that authorID = 1 is the author of both of these books, what I really wanted to get was only book one (book_id = 1). So here is my question: How can I exclude records that have more than the queried values linked to them in this kind of m:n relationship? And how can this be done as a SQL statement in Access 2016? Thanks in advance


texts: What's the best way to update several (200k)rows in a database?

texts:
I've got a database that I need to update a column in some rows. I've got the data from my logs and I wrote php script that processes each log and extracts the data in to a file with an update for each record that needs it.

I broke it down in to each month but running this is taking forever. Is there a better way to do it?

The file is basically a .sql file filled with 205k rows of

update table set ended_at = '2019-01-31 23:59:30' where uuid = '304ad6ea-b949-42f5-96a0-a7a550e6126d';

Is there a better way?


texts: I have several data flow tasks in my package that depend on other data flow tasks.  For testing reasons I want to run the entire package and then roll it back after it completes.  How do I do it?


texts: What risks are there if we enable read committed snapshot in sql-server?

texts:
I have read here that some extra data will be stored per row so we might see a performance degradation but what other risks are there?

eg.
Will this affect recovery of the database?
Is there anything else we need to do to take advantage of this?

I plan to execute these commands:

ALTER DATABASE DatabaseName SET READ_COMMITTED_SNAPSHOT ON
ALTER DATABASE DatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON

I believe this will give us something closer to oracle where if one transaction is updating other transactions can still read the old data. Is this correct?

I am looking into this because I am sick of locking problems in SQL Server 2005. I am hoping this might reduce the the occasional deadlocks our users see, help overall performance of our application and encourage our developers to do more than one operation per transaction without fear.


texts: Add a primary key constraint to an existing unique column

texts:
I've seen lots of information around adding a new primary key column to a large database table, but I have a somewhat different situation.

I have a large table (~90,000,000 records), that has no primary key. However, it DOES have a "RowID" column, which is an identity column. It also has a unique index on the RowID column.

All my research showed that a table without a primary key is terrible design that should be gotten rid of if at all possible. In my particular case; it's causing issues because Entity Framework maps the table as a read only view if it can't find a primary key.

I want to add a primary key constraint to the table on the RowID column. I was hoping that because it's not adding a new column, and there's already a unique index on that column, that it would be a small operation; however, it appears from a test run that it will take a very long time.

Having this table be inaccessible for a long time will cause issues across our business. I know that another option is to create a new table with the new schema, and copy all data from the old to the new. But this will also cause us to need to lock down the table for a long time.

Is there a way to more quickly add the primary key constraint when you already have an identity column with a unique index?


texts:
We have a very large DB (6TB) that is taking a long time in recovery, after we rebooted the server.

After doing some research we understand that it is because of the large number of VLFs. Right now, it is a waiting game.

In the SQL Server log, we see that the ETA is 24 hours! This is for the first of the three phases!

We have announced everyone that DB will be available in 24 hours, not knowing there are 2 more phases.

Does anyone know, how long the other 2 phases take? Just a ballbark number would do. By any chance are the next 2 phases shorter than the first one?


texts: How to use same stored procedure for different tables and avoid using dynamic sql

texts:
I have quite a few tables which contain some common columns (same name and datatype). On these columns I need to perform the same operations and I would like to do that via a stored procedure. However the only way I can make it work at the moment is using dynamic sql, which I'd rather avoid.

Is there any way to use a stored procedure for different tables (where the table name is handed over as parameter) without using dynamic sql?


texts: Enabling write caching for TempDb data drives

texts:
Recently, we had issues with slowness for some queries where TempDb latencies on their respective drives had spikes.

We already have 8 tempdb files and trace flags 1117 and 1118 are not enabled. We are planning to enable but do not know as of now what issues might occur due to them.

However our storage team has suggested something called enabling write caching for TempDb drives. We have no clue how this will effect overall performance.

Can someone provide any insight if enabling this is really a good idea for SQL server TempDb files?


texts: Identify sequence of events based on certain rules

texts:
I need help on identifying a sequence of events in SQL Server 2008 R2 rows please:

This is the sample data with SQLFiddle:

   ID   EventTime       TypeID             EventNr
    1   06:34:51        1
    2   06:35:51        3
    3   06:36:51        40
    4   06:37:51        10
    5   06:48:51        1
    6   06:49:51        2
    7   06:50:51        4
    8   06:51:51        40
    9   06:52:51        5
    10  06:53:51        10

I need help on identifying Event Nrs please -
Rules that I must follow:

An event starts start with TypeID 1 (@StartTypeID).
An event ends with TypeID 10 (@EndTypeID).
An event has a different TypeID after @StartTypeID, and before @EndTypeID.
And there must be more than 5 minutes between EventNrs.

This is the explected result for the above example:

 ID EventTime   TypeID  Notes                                               EventNr
1   06:34:51    1       Starts with TypeID @StartID 1     (OK)                     1
2   06:35:51    3       Has different TypeID in sequence before @EndID reached(OK) 1
3   06:36:51    40                                                                 1
4   06:37:51    10      Ends with @EndID 10 + >5min before next Event(OK EventEnd) 1

5   06:48:51    1       Starts with TypeID @StartID 1                              2
6   06:49:51    2       Has different TypeID in sequence before @EndID reached     2
7   06:50:51    4                                                                  2
8   06:51:51    40                                                                 2
9   06:52:51    5       Has different TypeID in sequence before  @EndID reached    2
10  06:53:51    10      Ends with @EndID 10                                        2

SQL for creating the data sample:

declare @FirstTypeID int = 1

declare @LastTypeID int = 10

create table #T (ID int identity , EventTime datetime, TypeID int)
insert into #T(EventTime,TypeID)
values
 (DATEADD(minute,-30,getdate()),1)
 ,(DATEADD(minute,-29,getdate()),3)
 ,(DATEADD(minute,-28,getdate()),40)
 ,(DATEADD(minute,-27,getdate()),10)
 ,(DATEADD(minute,-16,getdate()),1)
 ,(DATEADD(minute,-15,getdate()),2)
 ,(DATEADD(minute,-14,getdate()),4)
 ,(DATEADD(minute,-13,getdate()),40)
 ,(DATEADD(minute,-12,getdate()),5)
 ,(DATEADD(minute,-11,getdate()),10)

select * from #T

drop table #T


texts: How can release memory usage of sql server by command. my system have 16GB RAM, but when get huge query by sql server, memory usage become full. if I restart SQL Server service memory usage become free. but I want to release memory usage of sql server after each huge query execute.


texts: Why does this plan cost even though there are no rows?

texts:
I execute a Batch-Operation. The whole execution needs about 60s. The following statement needs 75.6% of the whole Batch-Operation:

update WW_Pos set LieferantID=null where LieferantID in (select Ident from deleted)

But there are no rows in WW_Pos for this where-condition. As you can see in the execution-plan, the Update-Operation of the clustered index would take 99% of the costs of the plan:

and the Plan-Tree shows there are no Actual Rows for the Update-Operation:

LieferantID is not the Clustered index (but it is in another index WW_Pos_LieferantID). Nevertheless, it has to update the clustered index?

Why does this Update-Statement take about 40 seconds, even though there are no records to update?

I hope someone can help me understand this behavior.


texts: Student and Course Relationship - Grade and Attendance

texts:
I was reading on this DBA page, about Student and Course relationship, and the use of a junction table. Suppose I wanted to do two things:

Include the student grade for each course
Allow the professor to generate an attendance sheet of students

I have a Student table like this:

Student (id, first name, last name)

Student_Course (student_id, course_id)

Subject (id, name, professorID)

Can I place the grade in the Student_Course table? What table can I use to generate an attendance sheet with StudentID, and First/Last name? I'm guessing attendance would be a query.


texts: Uber-wide tables: Time to switch to Mongo?

texts:
As a web developer, I've typically only worked with nice, normalized tables in SQL-based RDBMS, but I'm doing some data consolidation for a researcher who will be analyzing correlations between all items of several very long questionnaires and wants a giant CSV with data for all takers and all items.  So, say, 3000 rows and 5000 columns (?).

I set up tables for each questionnaire (each has its own CSV), but we're talking tables with 1000+ columns representing item responses for each taker / pkid.  Obviously I can't join up four, five different questionnaires like that based on pkid -- I'd have 5K or more columns and I'm already at the outer limits of MySQL just on the individual tables.

So do I:
1) look into NoSQL at last,
2) just programmatically write a one-off script to consolidate this data
3) convince scientist to buy Oracle or MS SQL
4) other option I as a non-data-scientist don't know about?

Thanks for the advice!

Joy


texts:
MYSQL Workbench Column Default/Expression

What can be put in here, I see I can put in a default value for the field

Can I also put mysql statements? Or is this just for default values?

For a Column detail, if I check the "G" for generate it will create a statement

GENERATED ALWAYS AS () VIRTUAL ,

What does this mean, and where can I find documentation on how to use this?


texts: Deprecating SQL Server, need to redirect clients to new server

texts:
I am in the process of decommissioning an old SQL Server server and replacing it with a new server.

My old SQL Server is a named instance called: SQL\Development
My new SQL Server is a default instance called Sandbox

Is there a way on the server side to redirect clients from SQL\Development to Sandbox?

There are hundreds of clients and I am looking for the easiest way to accomplish this task.


texts: Modeling a database structure for contact management

texts:
I was wondering what was the best design I could make for reusability, long-term maintenance and scaling for my database design. I am currently the only programmer at my job and they need a good database for a future project. All the application will use is this database for local user login, for customer (which can be also a company) tracking etc.

The database management system I will use is MySQL.

Basically what I want to implement is a database that will keep track of all the users we have to eventually configure some ATA automatically. Furthermore, I need this database to contain customers as well as companies (which are customers also). I need this database to contain users, which are employees. I want to fusion both "databases", but I am not sure what is the proper way to do it.

Business rules

A Party can have multiple Email
A Party can have multiple Phone
An Employee is a Party
A Customer is a Party
A Company has a Party as a Contact
A Company can have multiple Address
A Customer can have multiple Address
A Customer can have multiple ATA (Not shown in my diagram)
A Company can have multiple ATA
There are exactly-three PhoneTypes: 'Home', 'Office' and 'Cellphone'
A Phone can be of exactly one PhoneType

So a Customer is essentially a Party but it has Address(es) while an Employee is a Party who has restriction. I don't know how to fit Company in there. My current design has Company attached to Customer with a one-to-many relation(one customer, multiple companies).

In short I want to create a database that contain Companies, Customers and Employees login information (no pay check involved here). How can I optimize my (application program) UML schema to fit all those criteria?

Additional information

Here is how my new design works, I am still not sure of the Company integration in my schema:

I thought that Company and Customer are quite the same so I made them both Customer. Perhaps adding a column for IS_COMPANY could do the trick?

Responses to comments

  What can be phone type? How many phone types can exist? Since there can be multiple emails, phones and addresses, why not keep them in one table called contact. Here's a small version of your problem. - KumarHash

Phone type has three possibilities: "Home", "office" or "cellphone". I made it that way so I can load my dropdown values from the relevant table. Party is the contact table you mentionned; I coukd have named it like that, but I chose "party" after I read another question on SO.


texts: Truncate database in mysql server by half?

texts: I have a Mysql server with large DB  over 100 Gigs with lots of tables; I would like to set up a test server with half of the records in DB; what could be the best query to  do it ? Any help is much appreciated.


texts: How to create a csv with SQLCMD which can be imported into another database system

texts:
I may be doing this all the wrong way so please point me in the right direction if I am missing something.

I have an Ubuntu server with SQLCMD installed and a Windows Server with a MSSQL database.

I have a working script to output data from a query out of the MSSQL database into a CSV file already BUT its not quite right. SQLCMD creates 2 excess features which I would like to remove and there is no real definitive guide on how to do it.

Sample output:

header1,header2
-----,-----
data1,data2
data3,data4

Network packet size (bytes): 4096
1 xact[s]:
Clock Time (ms.): total       445  avg   445.0 (2.2 xacts per sec.)

So the features here I do not want in a CSV are quite obvious. The --- lines and the output data at the bottom.

My script looks something like this so far:

sqlcmd -S databaseserver -U "sa" -d database -p -W -Q "set nocount on; SELECT x as data, count(*) as datacount FROM location WHERE x IS NOT NULL GROUP BY x" -s "," -o output.csv -r0

I am happy to make .sh files with actions which take place after so any native ubuntu scripting method which can "finish the job" is acceptable. I am not that confident at writing the scripts but can usually work out what they do.


texts: create difference column of time series in MySQL

texts:
I have a table with 2 columns, the date and the value for the date, e.g.

date         value
2015-09-01   15
2015-09-02   17
2015-09-03   21

and I want to create a column with the difference between the value and the value of the last day, like

date         difference
2015-09-01   NULL
2015-09-02   2
2015-09-03   4

Can this be done with MySQL?


texts: The innodb_system data file 'ibdata1' must be writable even with full permissions set

texts:
i'm trying to change the datadir in mysql, i created a new directory with the user mysql as a owner, i know am not supposed to set the permission to 777. but even with this full permission still getting the same error in the log file whenever i try to start the mysql service.
[InnoDB] The innodb_system data file 'ibdata1' must be writable
user@user:~$ ls -l /database2/mysql | grep ibdata1
-rwxrwxrwx 1 mysql mysql 12582912 Jan 15 15:24 ibdata1

any solution ?


texts: Is there a shortcut for 'SELECT * FROM table' in relational algebra?

texts:
Problem

A question I once found on a test was worded as such:

  Report the book information for the book with ISBN=43221

The relational schema for a book is depicted below:

πISBN, title, type, NumPages, edition, sequel, name(σISBN=43221(Book))

This is essentially the equivalent of

SELECT * FROM Book WHERE ISBN=43221.

I wrote out all the attributes in the Projection statement on the test, but I had the feeling there was a quicker way to do it.

Question

Is there a shortcut way of displaying all the information of one tuple in Relational Algebra, or must all attributes be written out in the Project statement?


texts: Alternatives for UPDATE statement Oracle 11g

texts:
I'm currently using Oracle 11g and let's say I have a table with the following columns (more or less)

Table1

ID varchar(64)
Status int(1)
Transaction_date date
tons of other columns

And this table has about 1 Billion rows. I would want to update the status column with a specific where clause, let's say

where transaction_date = somedatehere

What other alternatives can I use rather than just the normal UPDATE statement?

Currently what I'm trying to do is using CTAS or Insert into select to get the rows that I want to update and put on another table while using AS COLUMN_NAME so the values are already updated on the new/temporary table, which looks something like this:

INSERT INTO TABLE1_TEMPORARY (
    ID,
    STATUS,
    TRANSACTION_DATE,
    TONS_OF_OTHER_COLUMNS)
SELECT
    ID
    3 AS STATUS,
    TRANSACTION_DATE,
    TONS_OF_OTHER_COLUMNS
FROM TABLE1
WHERE
    TRANSACTION_DATE = SOMEDATE

So far everything seems to work faster than the normal update statement. The problem now is I would want to get the remaining data from the original table which I do not need to update but I do need to be included on my updated table/list.

What I tried to do at first was use DELETE on the same original table using the same where clause so that in theory, everything that should be left on that table should be all the data that i do not need to update, leaving me now with the two tables:

TABLE1 --which now contains the rows that i did not need to update
TABLE1_TEMPORARY --which contains the data I updated

But the delete statement in itself is also too slow or as slow as the orginal UPDATE statement so without the delete statement brings me to this point.

TABLE1 --which contains BOTH the data that I want to update and do not want to update
TABLE1_TEMPORARY --which contains the data I updated

What other alternatives can I use in order to get the data that's the opposite of my WHERE clause (take note that the where clause in this example has been simplified so I'm not looking for an answer of NOT EXISTS/NOT IN/NOT EQUALS plus those clauses are slower too compared to positive clauses)

I have ruled out deletion by partition since the data I need to update and not update can exist in different partitions, as well as TRUNCATE since I'm not updating all of the data, just part of it.

Is there some kind of JOIN statement I use with my TABLE1 and TABLE1_TEMPORARY in order to filter out the data that does not need to be updated?

I would also like to achieve this using as less REDO/UNDO/LOGGING as possible.

Thanks in advance.


texts: Escape from Alcatraz: SqlAnywhere to MSSQL

texts:
Is there a migration tool that actually works for SAP SqlAnywhere 17 to MSSQL (any recent version)?

Searching for "sqlanywhere mssql migration" yields a variety of hits including SQL Server Migration Assistance (SSMA) but all of the tools I have tried — various free and some trials of commercial offerings — all failed to handle the following test (assume T is a real table with columns A and B):

SELECT A, B + 'X' AS B1 FROM T GROUP BY B1

SqlAnywhere permits this but on MSSQL the symbol B1 is not in scope in the GROUP BY clause, and the query must be transformed as follows:

SELECT * FROM (SELECT A, B + 'X' AS B1 FROM T) AS T1 GROUP BY B1

I wasn't even able to test SSMA. It is available for a range of platforms including Sybase ASE but is not compatible with the database formerly known as Sybase SqlAnywhere and now titled SAP SqlAnywhere. I've tried this, and it is not interested in using my SqlAnywhere ODBC drivers.

If there isn't an out of the box migration tool then is there anything that can transform syntax? In some ways this would be even more helpful than a migration tool because our applications are riddled with queries that exhibit this particular incompatibility. I seriously considered writing a transformation myself but quickly realised that SQL syntax is hideously complex and idiosyncratic.


texts: Create a PostgreSQL constraint to prevent unique combination rows

texts:
Imagine you have a simple table:

name | is_active
----------------
A    | 0
A    | 0
B    | 0
C    | 1
...  | ...

I need to create a special unique constraint which fails on following situation:
different is_active values can't co-exist for the same name value.

Example of permitted condition:

Note: simple multi-column unique index won't permit combination like this.

A    | 0
A    | 0
B    | 0

Example of permitted condition:

A    | 0
B    | 1

Example of failed condition:

A    | 0
A    | 1
-- should be prevented, because `A 0` exists
-- same name, but different `is_active`

Ideally, I need unique constraint or unique partial index. Triggers are more problematic for me.

Double A,0 allowed, but (A,0) (A,1) isn't.


texts: Use min max value from a CTE to define interval range on a next CTE

texts:
I have one table on Redshift and in a query I'm trying to have the list of ids and the list of ids from a sequence generated.

WITH

missing_rows AS (
  SELECT id, created_at
  FROM logs
  WHERE created_at > GETDATE() - INTERVAL '2 hours'
),

range AS (
  SELECT min(id) AS min_id, max(id) AS max_id
  FROM missing_rows
),

series AS (
  SELECT (row_number() over (ORDER BY 1)) + (range.min_id - 1) AS id,
  FROM logs
  FULL OUTER JOIN range ON ???? -- here is my issue
  WHERE active_connections.id
BETWEEN range.min_id -- how to use min_id from range
    AND range.max_id -- how to use max_id from range
)

SELECT *
FROM series

How to use range values inside the next query series?


texts: How to set up signed SSL Root and Leaf certificates on a PostgreSQL 11 database on Ubuntu and Windows 10 Power BI plus PostgreSQL?

texts:
I have Power BI and PostgreSQL 11 installed on Windows 10 and my primary PostgreSQL 11 database on Ubuntu 18.10 Linux. I edited the postgresql.conf and pg_hba.conf files correctly to the IP Adresses my Windows 10 computer uses with PostgreSQL 11 and Power BI to connect to my PostgreSQL 11 Ubuntu database. However, I got errors in Power BI while I could manually connect via command line.
After troubleshooting with Microsoft Support, I was told:

This indicates that the Postgresql server does not have its certificates configured and therefore cant connect to PowerBI. To allow the client to verify the identity of the server, place a root certificate on the client and a leaf certificate signed by the root certificate on the server. To allow the server to verify the identity of the client, place a root certificate on the server and a leaf certificate signed by the root certificate on the client. One or more intermediate certificates (usually stored with the leaf certificate) may be used.

However, I am confused by the tutorials at

https://www.postgresql.org/docs/current/libpq-ssl.html and
https://www.gab.lc/articles/postgresql_with_ssl/.

Error messages
The error messages I received were:

"ConnectionId":"bcfc620b-0fda-4db9-bda6-d9ac4aabcc4c","Exception":"Exception:\r\nExceptionType: System.Security.Authentication.AuthenticationException, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\r\nMessage: The remote certificate is invalid according to the validation procedure.\r\nStackTrace:\n   at System.Net.Security.SslState.StartSendAuthResetSignal(ProtocolToken message, AsyncProtocolRequest asyncRequest, Exception exception)\r\n   at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)\r\n   at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)\r\n   at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)\r\n   at System.Net.Security.SslState.CheckCompletionBef

and

Details: "An error happened while reading data from the provider: 'The remote certificate is invalid according to the validation procedure.'"

and

Details: "Microsoft SQL: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remost host.)"

Questions
I can't tell what steps need to be done on my Ubuntu PC and what steps need to be done on my Windows PC. I don't know if special steps are needed to configure Power BI with the certificates. How do I set up my bidirectional certificates?
Can someone offer a very detailed tutorial, tell me 'how' to read a tutorial, or explain the steps in an answer please?


texts: Converting MySQL 5.5 datetime binary to MySQL 5.6 (math problem)

texts:
I received a database "backup" of binary files (.frm and .ibd) that were copied from a MySQL 5.5 install.  Unfortunately, I was unable to get these files to import into a MySQL 5.5 vm that I created.  It seems that MySQL 5.6 is much happier about accepting .ibd files without a corresponding .cfg file, as it will just assume the layout of the binary files matches the existing schema.  Which would be fine except datetimes are stored differently between the two versions:

https://dev.mysql.com/doc/internals/en/date-and-time-data-type-representation.html

I went ahead and altered a table with a datetime column in the middle of it to use datetime(6).  This will force the 8-byte datetime from the binary to fit nicely within the datetime(6) column, without overflowing into the columns to the right.

So, one problem solved: the datetime columns no longer corrupt all columns after them.  Hooray!

Next problem: now, the datetimes are incorrect.  I know this date should be within the last 10 years, but I am unsure how to correct it: 0000-00-09 04:59:58.8100193

I assume there is a math solution for this, where I can take the data from the column and convert it in place.  Something like execution_datetime = execution_datetime * math and come out with the correct datetime.

Has anyone tried anything like this before?  Is there a standard code snippet for this?

Any help would be much appreciated!


texts: Postgres Greatest Query not returning correct timestamp

texts:
I think I might be writing this query incorrectly. Essentially, I want to find the object with the latest timestamp across four possible values (in this example, user_id is 1).

SELECT *, GREATEST(books.updated_at, books.deleted_at, posts.updated_at, posts.deleted_at)
FROM books, posts WHERE books.user_id = 1
OR posts.user_id = 1

This will always return a time for updated_at, but even if the deleted_at time for one of the objects is the latest, it will not return a deleted_at time. I know, because running a query for deleted_at IS NOT NULL returned a later timestamp.

What am I doing wrong here?


texts:
I am learning how to administrate MS Sql server 2012, come cross a question and its suggested solution for copying an existing database for different name.

Administer a windows azure sql database named Orders, you need to create a copy of this table and renamed as Order_Reporting

CREATE DATABASE Order_Reporting COPY OF Orders

I was wondering whether such useful query could also be used in standard ms sql server some how?


texts: What happens when you create the primary key of a table on a different filegroup?

texts:
Creating a table on a different filegroup is absolutely no problem when using Sql Server.

create table common.test
(
    id int,

    CONSTRAINT [pk_test] PRIMARY KEY CLUSTERED (id ASC) ON [common_index],
) ON [common_data]

But what happens to the data in the background? Are they really stored on two different filegroups? Because when you look at the table properties in Management Studio it show common_index as storage.

If this is true, why does it even work to define them on two seperate filegroups?

What would happen with the stored data when the primary key is dropped and recreated on another filegroup?


texts:
Are these two queries logically equivalent?

DECLARE @DateTime DATETIME = GETDATE()

Query 1

SELECT *
FROM   MyTable
WHERE  Datediff(DAY, LogInsertTime, @DateTime) > 7

Query 2

SELECT *
FROM   MyTable
WHERE  LogInsertTime < @DateTime - 7

If they are not logically equivalent, can you give me the logical equivalent of the first query so that the WHERE clause can effectively use an index (i.e. eliminate function wrapping)?


texts:
I have this:

SELECT
A
,B
,dbo.Func(C)
,dbo.Func(D)
,dbo.Func(E)
,F
FROM abcdef
WHERE
0 = dbo.Func(C) + dbo.Func(D)

I have read that this is not good practice because the function is called million of times and it has a bad impact on performance.

I tried to rewrite it with CROSS APPLY:

SELECT *
 FROM abcdef
  CROSS APPLY dbo.Func(D) as FD

but it returns this error:

Invalid object name 'dbo.Func'

Can I use CROSS APPLY only on TABLE VALUED functions ?

And is CROSS APPLY suitable for scalar functions (converted to table valued function) anyway ? Because on my small performance test CROSS APPLY is a bit slower.


texts: Does using a read committed isolation level and a NOLOCK query hint impact performance?

texts: Can having both the SET TRANSACTION ISOLATION LEVEL READ COMMITTED and WITH (NOLOCK) on all tables in SP cause conflicts and downgrade performance on the SP?  I just noticed some changes that a dev made in the SP recently.


texts:
I am in the process of reinstalling a Ubuntu server to rhel6 server. I have a pretty huge database. When I checked the mysql data directory size, it was around 354 GB. However, I dumped the entire database using the mysqldump command.

After reinstalling the machine, I started to restore the database using the mysql command. I started the restore process 3 days back. When I checked the mysql data directory in this newly installed machine, the size is just 31 GB. I am confused if actually the mysql command is restoring the database. I still see the command getting executed and the process is visibile from the linux top command. Is there something else I can do to speed up the process?


texts: How to cast (a) a date value recorded as a VARCHAR to (b) a specific DATE format for (c) use in an ORDER BY clause

texts:
How would I cast a column value (a "non-MySQL" DATE string retained as a VARCHAR) to a "MySQL date" so it can be used for sorting?

I have dates in the format of 06-02-2019 (6 feb) but MySQL itself doesn't seem to recognize this by just using the default DATE() function.

What I currently have is: SELECT * FROM Services ORDER BY Date(nextdate) DESC;


texts: Is there a way to get the IP address of a client virtual machine from pg_stat_activity?

texts:
I have several C# applications where I have to ensure that there is only one instance of the application running against a given schema.

I am migrating from Oracle to PostgreSQL 10.

In Oracle, I would run the following query:

select sid, username, machine, program, logon_time
  from sys.v_$session v
 where status not in ('KILLED')
   and upper('\' || machine) not like upper('%\' || :MACHINE)
   and upper(username) = upper(:USERNAME)
   and upper(program) = upper(:PROGRAM)

I use Environment.MachineName in my applications to get the value for the MACHINE parameter.

I have rewritten the query for PostgreSQL as follows:

select pid, user, client_hostname, application_name, backend_start
  from pg_stat_activity
 where upper(state) not in ('KILLED')
   and upper('\\' || client_hostname) not like upper('%\\' || :MACHINE)
   and upper(user) = upper(:USERNAME)
   and upper(application_name) = upper(:APPNAME)

I have added the ApplicationName to my connection string and enabled the log_hostname setting on my database to populate the application_name and client_hostname columns in pg_stat_activity, respectively.

The problem that I have run into is that, when running the application on a virtual machine, the client_address column shows the IP address of the physical machine that is hosting the VM, not the IP address of the virtual machine, and therefore the client_hostname is wrong also.

Am I doing something wrong?  Is there a way to get my application to push the correct information to PostgreSQL when connecting?


texts: How to export own objects using expdp without special privileges?

texts:
As per this post on Oracle forums expdp can be used to export your own schema without any special privileges:

  It all depends on what you want that user to be able to export/import.
  If all that use is exporting/importing is their own objects, then they
  don't need any raised privs. If you want that user to be able to
  export objects owned by other schemas, then they would need the
  exp_full_database and in order to import, they would need
  imp_full_database.

However I just can't get it to work. Directory object is required to be specified otherwise expdp displays error:
ORA-39145: directory object parameter must be specified and non-null
But creating directories requires CREATE ANY DIRECTORY system privilege.

How then can I export my own schema as a normal user?


texts:
I have a script which has this command 'sqlldr' which gives me the error
sqlldr: command not found
when I run this script. I googled and found that people say that this command should be there in my $ORACLE_HOME/bin directory. But I searched and found it's not there. How can I get this command and use in my script?


texts: Use if else sql statement with two selects

texts:
Currently I have the following SQL query
DECLARE @UgpEntry VARCHAR(50)

IF (@UgpEntry = -1)
SELECT t1.ItemCode as sapitemcode
     , t1.CodeBars as Barcode
     , t1.ItemName as description
     ,LEFT(t1.ItemName,20) as short_description
     ,
           (select max(p.Price)
        from ITM1 p
        where p.ItemCode = t1.ItemCode
          and p.PriceList = 1) as [price_1]
     , CASE t1.VatGourpSa when 'V0' THEN 4 when 'V1' THEN 1 WHEN 'V2' THEN 2 WHEN 'V3' THEN 3 END as TaxCode,t1.U_GRUPOA, t1.U_GRUPOB, t1.U_GRUPOC, t1.UgpEntry

FROM OITM t1
WHERE t1.ItemCode='00004'

ELSE

SELECT t1.ItemCode as sapitemcode
     , t1.CodeBars as Barcode
     , t1.ItemName as description
     ,LEFT(t1.ItemName,20) as short_description
     , (select max(p.Price)
        from ITM9 p
        where p.ItemCode = t1.ItemCode
          and p.UomEntry = 1) as [price_1],

           (select max(p.Price)
        from ITM1 p
        where p.ItemCode = t1.ItemCode
          and p.PriceList = 1) as [preciocaja]
     , CASE t1.VatGourpSa when 'V0' THEN 4 when 'V1' THEN 1 WHEN 'V2' THEN 2 WHEN 'V3' THEN 3 END as TaxCode,t1.U_GRUPOA, t1.U_GRUPOB, t1.U_GRUPOC, t1.UgpEntry

FROM OITM t1
WHERE t1.ItemCode='00004'

What I'm trying to do:

If this column t1.UgpEntry from OITM table (already exists and have data) is equal to -1 then
run the first query.

Else run the second query.

What am I doing wrong?
example: this record have UgpEntry = -1

this record have  UgpEntry = 5 and have another columns


texts: How can I improve this normalization to 3NF/How do you self-check normalization?

texts:
this is NOT homework, I'm just trying to improve my normalization skills. The first part is the functional dependencies i've identified and the second is what I believe the appropriate 3NF normalization would look like.

Part numbers (but not Type) uniquely identify parts. Manufacturer names uniquely identify Manufacturers. The Unit Cost of an item is unique to the Manufacturer but not necessarily the part itself. The Part Costs are in reference to how much the Manufacturer sold me the part.
Any additional tips on normalization or explanations relating to this one would be really helpful. Intuitively, this seems correct to me and it makes sense to have all of the manufacturer information in one database. Thank you!


texts: SELECT query blocking writes in postgresql

texts:
We have a web application that sends emails with a queue worker. Before an email is sent we write to a table:

INSERT INTO user_email (user_id, email_key, sent_at) VALUES (
    175413579283955991,
    'reminder-2017-07-06',
    '2017-07-06T14:35:56.329185'::timestamp
) RETURNING user_email.id

The table definition looks like this:

CREATE TABLE user_email (
    id bigserial primary key,
    user_id bigint references "user"(id),
    key varchar(64),
    sent_at timestamp without time zone,
    UNIQUE (user_id, key)
);

We've started seeing writes to this table take a very long time, it appears that SELECT statements to the user table are blocking it:

 locked_item      | transactionid
 waiting_duration | 00:00:22.516621
 blocked_pid      | 7409
 blocked_query    | INSERT INTO user_email (user_id, key, sent_at) VALUES (175413579283955991, 'reminder-2017-07-06', '2017-07-06T14:35:56.329185'::timestamp) RETURNING user_email.id
 blocked_mode     | ShareLock
 blocking_pid     | 4345
 blocking_query   | SELECT "user".id AS user_id, "user".first_name AS user_first_name, "user".last_name AS user_last_name, "user".title AS user_title, ... FROM "user" WHERE "user".id = 683220705055081414
 blocking_mode    | ExclusiveLock

We're at a loss on why a SELECT statement is causing an ExclusiveLock, any insights on what we should look into?


texts:
I cannot create index with the where clause.

Refer the below query:

CREATE INDEX c_bplocation_isdefault
ON c_bpartner_location
  (ad_client_id,c_bpartner_id,isdefault)
WHERE isdefault = 'Y';


texts:
I am new to Netezza and have a quick question.

I do a simple select of next val from sequence

SELECT NEXT VALUE FOR XX.XX.RUN_SEQ FROM _V_DUAL I

  ERROR [HY000] ERROR:  Sequence 46339885 not properly initialized

I get the above error, can some one help?

DDL:

CREATE SEQUENCE XX.RUN_SEQ AS BIGINT
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    NO CYCLE;


texts: View still depends on column even after I have removed the explicit (as well as transitive) dependencies?

texts:
Assume I have the following table and view:

CREATE TABLE my_table (a int, b text, c text);
CREATE VIEW my_view AS SELECT * FROM my_table WHERE b = 'foo';

The table has some data:

INSERT INTO my_table VALUES (1, 'foo', 'first');
INSERT INTO my_table VALUES (2, 'foo', 'second');
INSERT INTO my_table VALUES (3, 'foo', 'third');

I realise that column b always has the same value 'foo'. I decide that the entire column is unnecessary, and wish to drop it.

Because I like to be explicit about my dependencies, to make sure I don't accidentally drop more than I should, I avoid using the CASCADE keyword, opting instead to redefine the dependent view manually:

CREATE OR REPLACE VIEW my_view AS SELECT * FROM my_table;

The view now no longer has any explicit dependency on column b, so the column should be safe to delete.

ALTER TABLE my_table DROP COLUMN b;

However, as I try to drop column b, I get the following error message

ERROR:  cannot drop table my_table column b because other objects depend on it
DETAIL:  view my_view depends on table my_table column b
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Why does this error message appear even after removing the dependency on the column to be dropped?


texts: Are multiple Primary Keys in MySQL allowed?

texts:
I haven't used SQL for ages and now I have to create a software with a small DB. I've decided to use MySQL with MySQL Workbench to help me design this database (by drawing a model with ER diagram).

When I've started adding relations, I found out that with each relation added to table, another PK is added to it. At first, I thought that it is how this GUI marks both Primary and Foreign Keys, but then I saw the output SQL code (query), e.g.:

CREATE TABLE IF NOT EXISTS `test`.`table` (
    `id` INT NOT NULL,
    ...
PRIMARY KEY (`id`, `ppe_id`, `type_id`, `code_id`),
...

So my question is - are those other fields/columns also Primary Keys? Up till now I thought that only a single PK is permitted per table.

Actually, those additional PKs gave me an error in the end, when I tried running SQL statements generated by Workbench on server, because I need one of those "key-fields" to allow NULL values. To solve that I removed a tick for PK checkbox on this nullable column. So additional (strongly related) question: should I remove all those additional PKs from all of my tables as well? Even if they can have NOT NULL constraint, their values won't be unique, because those are used mostly for one-to-many relations.


texts:
I'm trying to add privileges for a newly created user in my database (CLI). But i am getting an error, probably because i have a dash in my db name but how do you work around it?

Error:

    You have an error in your SQL syntax; check the manual that corresponds to
    your MySQL server version for the right syntax to use
    near '-acc.* TO 'bivv-acc'@'localhost'' at line 1

The actual command:

GRANT ALL PRIVILEGES ON bivv-acc.* TO 'bivv-acc'@'localhost';

Note: I am in my database, not in the overview of all the databases. Any clues?

Cheers,


texts: what are the best ways to sync two databases in sql server or azure sql db?

texts:
I have a question like syncing two different databases available on two different servers/instances.

lets say Source be the source server and sourcedb is the source database.
destinationdb is the destination database available in the destination server(destination).

Sourcedb will be refreshed for every 15mins so data will keep on updating.
*

  Note: the source db is huge database, might be 3tb in size

  Note: There are some 100 tables which should be in sync with Sourcedb and destinationdb

*

The process which i though

Process1:

take a full backup and restore in off business hours
take the transaction replication for those 100 tables b/w sourcedb and destinationdb

Limitations:
Full backup restoring is fine but when comes to replication it made me cry. Sourcedb act as publisher and destinationdb act as subscriber and distributer.
So whenever destinationdb restored with Sourcedb, the replication setting will be vanished.
**

  is there any other way to overcome this problem?

**

Process2:

Using Copy database command:

While using this setting will my destination database will be in
online? because both of my databases should be in online.

Process3

Old fashioned way:
Taking full backups,differential backups and restore in the destinationdb.

  while restoring the destination db will goes offline.

Please advice.


texts: How to compare xmin and txid_current() after transactions ID wraparound?

texts:
Besides its regular columns, Postgres tables also have various system columns available. One of them, xmin, stores the transaction ID used to create a row. Its data type is xid, a four byte integer that wraps around at some point (i.e. not necessarily unique). The function txid_current() in turn returns the current transaction ID, but as bigint, because it "is extended with an "epoch" counter so it will not wrap around during the life of an installation" (to quote the manual).

If transactions wraparound did not yet happen, both values seem to match:

# CREATE TABLE test (label text);
CREATE TABLE
# INSERT INTO test VALUES ('test') RETURNING txid_current();
 txid_current
--------------
   674500
(1 row)
INSERT 0 1
# SELECT xmin FROM test;
  xmin
--------
 674500
(1 row)

But I wonder: are these two values always comparable? As far as I understand, txid_current() will continue to deliver unique values after transaction ID wraparound (at most 2^32 transactions) and xmin will start from zero. This means both start to return different values at that point?

And if this is true, is there a way to extract regular xid of a txid_current() result so that it would match xmin entries in a table (e.g. casting txid_current() to integer)?

Edit: Make it clearer that I care about what happens after a transaction ID wraparound, which very likely happens long before 2^32 transactions. Thanks to Daniel Vérité for noting this in the comments.


texts: When are dimension tables still needed using clustered columnstore indexing?

texts:
I am using MS SQL Server 2016 Clustered Columnstore Indexing (let's call it CCI) in my reporting database.

In initial designs I was thinking star schema but then I started playing with CCI. Now I have discarded many dimension tables in favor of flattening the strings directly into the "fact" table.  The only place I retain the dimension table is when that dimension has attributes that change frequently AND the requirement is to have the changed attribute made applicable to all historical records.  I have done this much to the dismay of a colleague who has more DW experience but no free time to explore CCI.

It appears that flat tables stored on disk as individual columns (and the massive compression that affords) need not be narrow at all.  When does one still need dimension tables when using CCI?


texts:
I have a table with sample data set-up like below.  How can I update the missing data for the field rglname?  There will always only be ONE rglname assigned to region so I am not worried about duplicate values.  Below is DDL

Create Table #BlueBell
(
  ID int
  ,region varchar(100)
 ,rglname varchar(500)
)

Insert Into #BlueBell VALUES
(1, 'East', 'Bob')
,(2, 'East', NULL)
,(3, 'East', NULL)
,(4, 'West', 'Sam')
,(5, 'West', NULL)
,(6, 'South', NULL)
,(7, 'South', NULL)
,(8, 'South', NULL)
,(9, 'South', 'Mitch')

And desired output is:

  ID    Region  RGLName
  1   East    Bob
  2   East    Bob
  3   East    Bob
  4   West    Sam
  5   West    Sam
  6   South   Mitch
  7   South   Mitch
  8   South   Mitch
  9   South   Mitch


texts: How to enable query store on the SSMS object browser with Sql Azure?

texts:
I am following this guide: Monitoring Performance By Using the Query Store

I am using SSMS 2016 CTP3.2 and Sql Azure V12
I have enabled the query store on the database properties.
I can use the query performance insight on the azure portal.

But when I open the azure database on SMSS 2016 CTP3.2 and expand the database tree, the Query Store / Regressed Queries or any other options are available.

I want to be able to take the most IO consuming queries and analyze the execution plan.

Anything else I have to enable?

The relevant is_query_store_on column in sys.databases shows 0, but the database properties says it is enabled. I executed ALTER DATABASE <DB> SET QUERY_STORE = ON; but is_query_store_on still stays at 0.

The following query:

SELECT
    actual_state, actual_state_desc, readonly_reason,
    current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

Shows actual_state = 2;


texts:
I inner join 2 table (Termination Reasons with Users) were the two talk to the each other and Termination Reasons table contains 2 column (Id (int) , Name(varchar)) and the other one , Users Table contains 1 column (EmploymentStatusId (int)) . I can use EmploymentStatusId(int) from tbl Users  with Id (int) from another table wich is Termination Reasons beacuse , both of them have same data type (int):

SELECT
u AS Status

FROM
d AS p INNER JOIN d AS u ON u=p
INNER JOIN d AS t ON t = u
WHERE u=214 order by u;

But when i try using EmploymentStatusId (int) from tbl Users with Name (varchar) from tbl Termination Reasons , i got problem:

SELECT
t AS Status

FROM
d AS p INNER JOIN db AS u ON u.I=p
INNER JOIN b AS t ON t= u
WHERE u=214 order by u;

And then Error :
Conversion failed when converting the varchar value 'TerminatedByCompany' to data type int.

i know why ,because they have different data type  , therefore they can not talk to the each other. but i tried also to solve this with  Convert  but idont know ,why or how is not working.Can someone please help or point me in the right direction. Thanks.

The Query ,where i tried with CONVERT ,its look like this:

SELECT
CONVERT(INT, CONVERT(VARCHAR(12), t)) as Status

FROM
db AS p INNER JOIN d AS u ON u =p
INNER JOIN db AS t ON t.Name = u
WHERE u=214 order by u.FirstName;


texts: ERROR 1034 (HY000) : Wrong aligned block, while importing in mysql

texts:
I got a SQLdump from my database and now I want to import it in to an other mysql server with same version(5.5.22-0ubuntu1) in ubuntu 12.04 .but at the middle of importing, it said:

ERROR 1034 (HY000) at line 3502: Key 2 - Found wrong stored record at 232882088

I do it again and I got another error

ERROR 1034 (HY000) at line 3502: Wrong aligned block at 11847827

I use default parameter to create SQL dump
This is my my.conf:
[client]
    port            = 3306
    socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /media/HDD1/comparable/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address            = 127.0.0.1

key_buffer              = 16M
max_allowed_packet      = 11600M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10

query_cache_limit       = 1M
query_cache_size        = 16M

#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#log_slow_queries       = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name

# chroot = /var/lib/mysql/
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

[mysqldump]
quick
quote-names
max_allowed_packet      = 11600M
net_write_timeout       = 10000
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

!includedir /etc/mysql/conf.d/

start of SQL dumb:

DROP TABLE IF EXISTS `DescIdf`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `DescIdf` (
  `word` varchar(255) NOT NULL,
  `idf` float NOT NULL,
  PRIMARY KEY (`word`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

...

Can anyone tell me how can I fix it?


texts: Finding differences between two tables takes too long

texts:
I'm trying to find the diff of two tables using a left join.

My query is as follows:

CREATE TABLE my_diff (INDEX my_index (name, type))
SELECT AA.name, AA.type
FROM AA
LEFT JOIN BB
USING (name, type)
WHERE BB.type IS NULL;

Table AA is sized: ~400K records and has PRIMARY KEY on all columns as well as an INDEX on each column
Table BB is sized: ~8K records and has a composite INDEX on both columns: my_index (name, type)

EXPLAIN EXTENDED:

id  select_type     table   type    possible_keys   key         key_len     ref     rows    filtered    Extra
1   SIMPLE          AA      index   NULL            PRIMARY     383         NULL    396178  100.00      Using index
1   SIMPLE          BB      index   NULL            my_index    104         NULL    8359    100.00      Using where; Not exists; Using index; Using join buffer (Block Nested Loop)

The query takes a mind-boggling 11 min to finish.

I have tried various options of composite and single column indexes / primary keys, tried forcing the keys/indexes on the select and the join, but to no avail.

What can I do to improve this?


texts: Best way to store currency values on MySql database

texts:
I'm working on an application where we have some services that requires a monetary value to calculate budgets for services. Currently we are using columns DECIMAL(10,2).

While analysing some of the generated budgets I've noticed some of them returns values like 180.0000000024.

Reading on other questions, I've only found the same type of answers, to go with decimals. In a few cases I saw a recomendation to use INT (or similar) instead. The reason is because the calculations will be more precise.

Example: $64.58 + $348.99 would be calculated like 6458 + 34899 and stored the result 41357 and later on converted to 413.57.

So it left mw with this questions, on which is the best way to store currency values on the database. Is DECIMAL really the best way? How much of a problem those extra decimals values can be a problem? Is INT (or similar) type going to improve something?


texts: MySql autoincrement counter resetting after server restart

texts:
I need a way to keep auto-increment counter between mysql service restart.

As docs states by default after restart mysql autoincrement counter start from max(id)+1. How can I keep the last autoincrement value?

This is a big problem for me. I'm using envers to keep entities audits. I get as many errors as many "last rows" I delete.

Suppose I'm starting insert data into an empty table. Suppose to insert 10 rows. Then suppose to delete the last 8. In my table I will have as result 2 entities, with id 1 and 2 respectively. In the audit table I will have all 10 entities, with id from 1 to 10: entities with id from 3 to 10 will have 2 action: create action and delete action.

auto-increment counter is now setted to 11 in main table. Restarting mysql service auto-increment counter goes to 3. So if I insert a new entity, it will be saved with id 3. But in audit table there is already an entity with id = 3. That entity is already marked as created and deleted. It result in an assertion failure during update /delete action because envers cannot handle this inconsistent state.

Thanks


texts: Why is alert.log not located in path indicated by BACKGROUND_DUMP_DEST in oracle 12c (12.1.0)

texts:
Just do this :
startup open;

Then
show parameter background_dump

The result of the show command on my machine is :
/ora01/app/oracle/product/12.1.0/db_1/rdbms/log

So when I go to that location, there is no alert log there.

Then I went to
$ORACLE_BASE/diag/rdbms/<db_name>/<oracle_sid>/trace
I found the alert log there.

So my question is why BACKGROUND_DUMP_DEST  not containing the proper path to the alert log ?


texts: Modulus calculation for compound primary keys in MySQL Cluster (and other sharded databases)

texts:
In MySQL Cluster, and generally in sharded databases, how is a compound primary key's modulus calculated?

Say, for example, there are two tables that have primary keys like so:

CREATE TABLE Orders (order_id INT PRIMARY KEY, ...);
CREATE TABLE LineItems (order_id INT, lineitem_id INT, PRIMARY KEY (order_id, lineitem_id), ...);

I saw this example in a Quora question about sharding MySQL tables, where it is specifically mentioned as a good idea for sharding if Orders and LineItems are to be queried together.

How is the modulus calculated so that rows with the same order_id are on the same shard?


texts: When should I extract a field into a new table?

texts:
Given a table :

CAR
---
id
model_id
condition
fuel_id
release_year

Since many cars would have the same Model and same Fuel, there are 1:N tables created.

Though, many Car objects can have the same release_year.

Should I create a Year table and then a year_id field into Car?

Design-wise, it seems accurate, but it seems weird to create a Year table containing a list of years.

Still, this is actually what I do for Fuel : a table containing a list of fuels types.

When is it accurate to create a new 1:N table instead of a field ?


texts: Identify slow query without slow query logs in mysql server

texts:
I am wondering is there any other way to to check our slow queries without logging slow query. Suppose, I have a highily busy server can't afford to log much to save memory and I/Os. Then, is there any other way available to check if I have a slow query? I know, we can do profiling of the query but still not sure what exactly to do to identify which query is the one taking most of the time and memory.

Just started mysql administration and not sure how to handle this. Any guidance will be highly appreciated.


texts: How to model a three-way association that involves Product, Category and Label?

texts:
I have the following three tables:
products:
    product_id,
    product_name,
    ...

categories:
    category_id,
    category_name,
    category_parent_id,
    category_priority (for sort ordering),
    .....

labels:
    label_id,
    label_name,
    .....

The idea is that products assigned to a category will be grouped within each category by labels and listed on the website in this manner:
---label1---
product_1
product_2
product_3
---label2---
product_4
product_5
---label3---
product_6
product_7
product_8
product_9
etc.

I can’t figure out how to design an association table (or tables) that glue all this together and prevent anomalies like this:
---label1---
product_1
product_2
---label2---
product_2
product_3

At the same time, I’d like to allow a situation where there will be no labels assigned to a category when there are not enough products to justify it.
Questions
Is it possible to design a structure that will hold it together or should I ‘abandon all hope’ and go with something like this:
categories:
    category_id,
    category_name,
    category_parent_id,
    categor_is_label,
    category_priority

products:
    product_id,
    product_name,
    ...

plus the association table that follows:
categories_products:
    category_id,
    product_id,
    priority

and handle all logic and checking for anomalies in the application?
I’m assuming that users won’t have direct access to the database.
Comments and chat interactions
For those interested in an in-depth conversation about the business context at hand, you can visit this chat room.


texts: Tracking down a stored procedure's parameters after execution

texts:
I have been asked to identify a permissions issue with a stored procedure. This stored procedure behaves in two possible ways depending on what values are used for its parameters.

exec ps_my_stored_procedure @a=1, @b=2, @c=3

is handled very differently from

exec ps_my_stored_procedure @a=5, @b=7, @c=0

You could say that ps_my_stored_procedure is divided logically into two completely separate processes.

Using dm_exec_procedure_stats and dm_exec_query_stats, I can find the execution plan which shows the SQL of the stored procedure used.  I have not been able however, to recover how the parameters were defined and with what values.

Is it possible using dm_exec_procedure_stats and dm_exec_query_stats and any other management views to reconstruct the execution of the stored procedure that shows the values used for its parameters.

What I'd really like is to find in cache, is the actual execution of the stored procedure so that I can execute it as is using EXECUTE AS LOGIN = 'someone' to resolve the permissions issues


texts:
I've got an RDF table in MySQL 5.1 with the MyISAM engine.  Here's the table definition:

CREATE TABLE `node` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `graph` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
  `subject` varchar(200) NOT NULL,
  `predicate` varchar(200) NOT NULL,
  `object` mediumtext NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `nodeindex` (`graph`(20),`subject`(100),`predicate`(100),`object`(1
00)),
  KEY `ix_node_subject` (`subject`),
  KEY `ix_node_graph` (`graph`),
  KEY `ix_node_object` (`object`(255)),
  KEY `ix_node_predicate` (`predicate`),
  KEY `node_po` (`predicate`,`object`(130)),
  KEY `node_so` (`subject`,`object`(130)),
  KEY `node_sp` (`subject`,`predicate`(130)),
  FULLTEXT KEY `node_search` (`object`)
) ENGINE=MyISAM AUTO_INCREMENT=468086207 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

User-generated queries on this table construct multiple LEFT JOINs and perform full-text searches, and these queries get stuck in the Copying to tmp table state.

Now, Gods know I'd love advice on how to improve this, but my real question here is this: how big would the temporary table be? It would seem plausible that it needs to construct n temp tables for n joins, each of 80 million rows.

Update

I guess I wasn't clear: I'm wondering how you can estimate (or even better, compute) how big the temp tables would get. This is more a question about the implementation of a left outer join than anything.

More Update

Let me try to restate the question.  I've got the table above, with 80 million rows. (Actually 81 now.)  I've got a transient query, generated by a tool, that filters these 81 million rows by building a bunch of LEFT JOINs and then doing a full text search on the object field.  To do the text search, as I understand things, it creates a temporary table, and since we're hypothetically searching the whole table that will also have 81 million rows.  So that's one.

Let's say the number of rows is r.

Now, consider a left join of the table on itself; this results in a new table with r rows. So we're got one temp table because it always builds a temp table for text searches. Does that left join now create a second temp table? Do successive left joins create successive copies of the temp table, to that there are r+1 tables created in total? Or does it "add" the new columns to the existing table?

If it's done "in place" that means the length of the row in the joined table increases by the length of one source row for each join, and thus the total storage needed is growing as O(n) for n joins.  But if it constructs a table because of the text search, then a table containing (row,row), then a table containing (row,row,row) and so forth (and now you've got an earworm for the rest of the day) then the space required grows as O(n2).

In other words, I'm asking does the total size of the table grow as O(n) for n joins, or O(n2)?


texts: how to remove/deal with locks in a MariaDB Galera cluster

texts:
I have a cluster of 3 nodes running MariaDB Galera cluster, cluster works as expected, I can reboot a single node at a time and in the best case, IST will be used or SST(using rsync) to sync back the node, all good so far.

I noticed that some DELETES or DDL's as described here: http://galeracluster.com/documentation-webpages/schemaupgrades.html, can lock the full cluster, therefore when possible and having access to the 3 nodes, I started to follow this procedure:

SET wsrep_OSU_method='RSU';
Run the ALTER/DELETE statement.
SET wsrep_OSU_method='TOI';

The problem with this approach is that I need to run the procedure in all the 3 nodes and in some cases this is not possible since the full cluster is exposed using an HAproxy.

The more the database grows and if "accidentally" someone runs a DELETE or ALTER instead of trying to follow the "RSU/TOI" procedure, the whole database locks/blocks (no read/writes) and start affecting other databases within the cluster. An ugly but quick solution after "guessing" the node where the query that is locking is by running this query:

show open tables where in_use > 0;

Rebooting the node in question helps, and hopefully, the node will resync using IST like mentioned before and worst case SST. Restarting the mysqld process works but takes to much time that is better to just reboot the full VM since is faster to have the cluster unlocked and usable again.

Is not possible also to kill the query:

ERROR 1095 (HY000): You are not owner of thread 70760372

This "workaround" has become a bad habit, therefore, wondering what other techniques or procedures can be used to unlock the cluster in a graceful, clean and pragmatically way (something that could be automated) or even better what options/settings could help to prevent against the locks?

A long term solution could be to use a Master-slave setup, in where the "RSU/TOI" could be avoided, but before going that further, I would like to fine tune better the current cluster setup.


texts:
We want to setup a mysql cluster with master-master sync replication, to provide HA so that one db for write from one app, other for write from other app. It is a webapp with high write and medium read

We are considering adding mysql cluster and considering percona vs mysql cluster.

Can MySQL ndb cluster import innodb dump ?

Can ndb cluster support MySQL async and 2-way sync replication ?

Can ndb guarantee consistency since it does not write to disk? I.e. What if there is 2 node cluster and both go down and data is not flushed to disk

can ndb cluster support 4-5 table join, ? Will the tables be in same node or will there be any network latency due to distributed joins

Does ndb cluster support MySQL-fabric Python api ?

Does ndb cluster support multi-master bi-directional sync?

Does percona cluster support above items ?


texts: MySQL replication works but slave keeps reconnecting to master

texts:
I have a master-slave set-up with multiple slaves in different datacenters. One of the slaves stopped working normally today. This was easily resolved by restarting mysqld but ever since (and perhaps before) the Slave_IO_State keeps jumping from "Waiting for master to send event" to "Checking master version" to "Reconnecting after a failed master event read" (the order may be different, but these are the statuses I get from continuously running a SHOW SLAVE STATUS query).

The connection to the master is made via a SSH tunnel, and I'm able to connect to the master from the command line without a problem. Furthermore, replication seems to be working; the slave is up to date. What I don't understand is why it keeps disconnecting/reconnecting. No other errors show.

This is MySQL 5.5.54 running on CentOS 6 boxes.

Resetting the slave doesn't help. How do I get to the root of this issue?

EDIT: When it really fails, as before and it just happened again, the message is "The slave I/O thread stops because SET @master_heartbeat_period on master failed. Error: Lost connection to MySQL server during query".


texts:
New DBA, all of our databases are SQL 2005.  All of our servers run in Simple recovery mode.  I work for a school district, where we report student attendance to the state.

I run a query to fix reporting minutes on a specific grade level of students.  I have this scheduled as a job to run daily.

Our vendor is now saying that this is fixed in their product, but we believe it is fixed because we run the script daily.

Is there something that I can query to find out how many records were affected when the job is run?  I would need to do this for a date in the past (May 26, 2015).

Here is the script I run daily -

/*
The default absFTE value for students using twice daily attendance is 0.50.

This Query is used to change the absFTE values from 0.50 to 1.0 for PS students that are only
half day in order to send the full days worth of minutes.

*/

Update rev.EPC_STU_ATT_DAILY
Set ABS_FTE1 = '1.00'
Where DAILY_ATTEND_GU IN
(
Select
da.DAILY_ATTEND_GU
from rev.EPC_STU_ATT_DAILY da
join rev.EPC_STU_ENROLL en on da.ENROLLMENT_GU = en.ENROLLMENT_GU
join rev.EPC_STU_SCH_YR ssy on en.STUDENT_SCHOOL_YEAR_GU = ssy.STUDENT_SCHOOL_YEAR_GU
join rev.EPC_STU st on st.STUDENT_GU = ssy.STUDENT_GU
join rev.REV_ORGANIZATION_YEAR oyr on (oyr.ORGANIZATION_YEAR_GU = ssy.ORGANIZATION_YEAR_GU)
join rev.REV_ORGANIZATION org on (org.ORGANIZATION_GU = oyr.ORGANIZATION_GU)
join rev.EPC_SCH s on s.ORGANIZATION_GU = org.ORGANIZATION_GU
join rev.REV_YEAR y on y.YEAR_GU = oyr.YEAR_GU
join rev.SIF_22_Common_CurrentYear cy on cy.SCHOOL_YEAR = y.SCHOOL_YEAR
left join rev.REV_BOD_LOOKUP_VALUES grade on grade.VALUE_CODE=ssy.GRADE
      and grade.LOOKUP_DEF_GU=
      (
      select LOOKUP_DEF_GU
       from rev.REV_BOD_LOOKUP_DEF
       where LOOKUP_DEF_CODE='grade'
      )
Where da.ABS_FTE1 is not null and VALUE_DESCRIPTION IN ('PS')
)

Update rev.EPC_STU_ATT_DAILY
Set ABS_FTE2 = '1.00'
Where DAILY_ATTEND_GU IN
(
Select
da.DAILY_ATTEND_GU
from rev.EPC_STU_ATT_DAILY da
join rev.EPC_STU_ENROLL en on da.ENROLLMENT_GU = en.ENROLLMENT_GU
join rev.EPC_STU_SCH_YR ssy on en.STUDENT_SCHOOL_YEAR_GU = ssy.STUDENT_SCHOOL_YEAR_GU
join rev.EPC_STU st on st.STUDENT_GU = ssy.STUDENT_GU
join rev.REV_ORGANIZATION_YEAR oyr on (oyr.ORGANIZATION_YEAR_GU = ssy.ORGANIZATION_YEAR_GU)
join rev.REV_ORGANIZATION org on (org.ORGANIZATION_GU = oyr.ORGANIZATION_GU)
join rev.EPC_SCH s on s.ORGANIZATION_GU = org.ORGANIZATION_GU
join rev.REV_YEAR y on y.YEAR_GU = oyr.YEAR_GU
join rev.SIF_22_Common_CurrentYear cy on cy.SCHOOL_YEAR = y.SCHOOL_YEAR
left join rev.REV_BOD_LOOKUP_VALUES grade on grade.VALUE_CODE=ssy.GRADE
      and grade.LOOKUP_DEF_GU=
      (
      select LOOKUP_DEF_GU
       from rev.REV_BOD_LOOKUP_DEF
       where LOOKUP_DEF_CODE='grade'
      )
Where da.ABS_FTE2 is not null and VALUE_DESCRIPTION IN ('PS')
)


texts: Does enabling LPIM work even if the memory pressure is coming from SQL Server itself

texts:
I have a scenario where I have 128 GB Physical memory for the SQL Server 2016 machine.
It is a SQL Server dedicated machine.
I have 110 GB allocated to SQL Server itself.

I have performance issues when the CPU usage is high or the no of connections go high. It is normal for the CPU usage to go high as there are more request coming in with more connections.
I looked at the RING BUFFERS,plan cache etc. I see that plan cache gets cleared often when i have more usage. Also RING BUFFER indicated memory pressure. I see that for most of the cases IndicatorProcess=2 which means SQL server internally detected memory pressure. On few occasions it is IndicatorSystem = 2.

I have been struggling with this for sometime now. I have tuned the frequently executed queries and most of them runs fast when there are plans available in plan cache.
My Question here is :

Does enabling LPIM help in this scenario since i see
IndicatorProcess=2 in most of the cases.
I already have 128GB RAM.Is that the maximum i can have for SQL Server 2016 Standard.?

Additional Info

Here is what the monitoring tool has to say for the last one hour.


texts:
I had to install MySQL on Ubuntu 18.04.4 LTS (GNU/Linux 4.15.0-1065-aws x86_64)
But it produces errors:
ExecStart=/usr/sbin/mysqld --daemonize
--pid-file=/run/mysqld/mysqld.pid (code=exited, status=1/FAILURE)

journalctl -u mysql.service

-- Logs begin at Thu 2020-04-09 10:00:11 UTC, end at Wed 2020-04-15 04:19:17 UTC. --
Apr 14 07:45:57 ip-172-31-91-207 systemd[1]: Starting MySQL Community Server...
Apr 14 07:45:57 ip-172-31-91-207 systemd[1]: Started MySQL Community Server.
Apr 14 08:28:25 ip-172-31-91-207 systemd[1]: Stopping MySQL Community Server...
Apr 14 08:28:27 ip-172-31-91-207 systemd[1]: Stopped MySQL Community Server.
Apr 14 08:43:23 ip-172-31-91-207 systemd[1]: Starting MySQL Community Server...
Apr 14 08:43:23 ip-172-31-91-207 systemd[1]: Started MySQL Community Server.
Apr 14 08:46:44 ip-172-31-91-207 systemd[1]: Stopping MySQL Community Server...
Apr 14 08:46:45 ip-172-31-91-207 systemd[1]: Stopped MySQL Community Server.
Apr 14 08:48:55 ip-172-31-91-207 systemd[1]: Starting MySQL Community Server...
Apr 14 08:48:56 ip-172-31-91-207 systemd[1]: mysql.service: Control process exited, code=exited status=1
Apr 14 08:48:57 ip-172-31-91-207 systemd[1]: mysql.service: Failed with result 'exit-code'.
Apr 14 08:48:57 ip-172-31-91-207 systemd[1]: Failed to start MySQL Community Server.
Apr 14 08:48:57 ip-172-31-91-207 systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Apr 14 08:48:57 ip-172-31-91-207 systemd[1]: mysql.service: Scheduled restart job, restart counter is at 1.
Apr 14 08:48:57 ip-172-31-91-207 systemd[1]: Stopped MySQL Community Server.
Apr 14 08:48:57 ip-172-31-91-207 systemd[1]: Starting MySQL Community Server...
Apr 14 08:48:58 ip-172-31-91-207 mysqld[900]: Initialization of mysqld failed: 0
Apr 14 08:48:58 ip-172-31-91-207 systemd[1]: mysql.service: Control process exited, code=exited status=1
Apr 14 08:48:59 ip-172-31-91-207 systemd[1]: mysql.service: Failed with result 'exit-code'.
Apr 14 08:48:59 ip-172-31-91-207 systemd[1]: Failed to start MySQL Community Server.

Log error (file /var/log/mysql/error.log)
2020-04-14T21:43:30.823694Z 0 [ERROR] Can't start server: Bind on TCP/IP port: Cannot assign requested address
2020-04-14T21:43:30.823698Z 0 [ERROR] Do you already have another mysqld server running on port: 3306 ?
2020-04-14T21:43:30.823715Z 0 [ERROR] Aborting

I checked on port 3306, nothing running on it. I can't fix this error.
To use MySQL, I don't restart it and install phpMyadmin for remotely, management DB


texts:
I am trying to create a table inside a procedure but getting some issues related to quote identifier or may be something else.

BEGIN EXECUTE IMMEDIATE
'Create newTable as select * from oldTable';
END;

This's working fine however I need to create my table as

BEGIN EXECUTE IMMEDIATE
'Create newTable as select * from oldTable where col1='XXX' and col2='Y'';
END;

above statement is throwing errors.

I tried to change like this but this is also not working.

BEGIN EXECUTE IMMEDIATE
'Create newTable as select * from oldTable where col1="XXX" and col2="Y"';
END;


texts: Grant a login of Sql server authentication the same permission of a Windows group login with Windows authentication?

texts:
Update:
I am trying to make it easier for the login to have the permissions on multiple databases.

I already have a Windows group login with Windows authentication. The Windows users in the group can access multiple databases on a server.

Now I need to create an Sql server login and I want it have the same permission of the Windows group login (so it will have the permissions on mulitple database). Is it possible to do it without grant all the permissions to the SQL server separately?

I tried to create a credential for the Windows group login and add the credential to the SQL login but it doesn't work.

create credential WinGroupLogin with identity = 'MyServer\WinGroupLogin'
ALTER LOGIN [sql_login] ADD CREDENTIAL [WinGroupLogin]


texts: Grant an AD Group permission to edit all MSSQL Agent jobs

texts:
We have a team of people, we all log in MSSQL Server from SSMS using our Active Directory accounts, by Windows Authentication.
Our AD accounts are added to an AD group, which is used to grant all permissions on MSSQL. For simplicity, we don't grant any permissions directly to our accounts, and for security we don't use local MSSQL accounts.
We're struggling to setup permission so that everybody (on the AD group) is able to list, view, edit, start, stop, enable, disable, etc MSSQL Agent jobs, and list and set proxies on CmdExec steps. It shouldn't matter who had created each job, we should all have full access to all jobs.
I had googled about it, read answers here, made a question myself. I read the SQL Server Agent Fixed Database Roles doc and noticed that SQLAgentOperatorRole is the most privileged role and still doesn't have full access to all jobs.
Is there any way to have this kind of permission set for an AD group?
If not, my next path will be to create a local MSSQL account, deny it permission to R/W on all databases, and then everybody will know its password and all jobs be created and managed from it. But I really wish to not do such thing.


texts: Syncronize mysql databases between local and hosted servers automatically

texts:
We have many websites with Development, Staging and Production servers. We have many developers for many projects. We need a solution to synchronize the developer and staging databases. After that one is worksing we can move to the live database.

That one needs to be fully automatically synchronized so that developers don't need to run that tool each and every time.


texts: sp_executesql adds statements to executed dynamic script?

texts:
The Question:
As far as I can tell, sp_executesql adds statements to the beginning of submitted dynamic SQL script. But, a SQL Profiler trace does not capture the extra statements, and neither does DBCC OUTPUTBUFFER. So:

Is there any way to see the extra statements added to submitted dynamic SQL batches by sp_executesql?
Can anyone confirm definitively that my conclusions about the extra statements are correct/incorrect?

Background
I have a database where some objects (views, synonyms, SPs) are rewritten based on data in a Script table. If the database is moved to another server, a stored procedure loops through the rows of the Script table, replaces certain key values in the supplied SQL script with those defined for the new server context, and runs the script.
Everything was working fine until I made a few tweaks to add support for scripting permissions through this same mechanism. The database integrates with a vendor's product, and in each environment the vendor's database can have a different user that must be given permission to a particular view in my database for reporting purposes. So, I have to query for that user (from the vendor's database) then use that name to create the user in my database if it doesn't exist and finally grant SELECT permission. This required more lengthy scripting and doing dynamic-sql inside of dynamic-sql, so I wanted to pass in my outer script's @Debug parameter so I could see the extra script that was being generated and confirm its correctness before trying to execute it.
Other than changing what object types could be scripted and making the DROP script optional, the only material change I made to accommodate the @Debug parameter was to change this:
EXEC (@CreateSQL);

to this:
EXEC sp_executesql @CreateSQL, N'@Debug bit', @Debug;

Then I ran into a problem: the one stored procedure in my Script table could no longer be created, though the DROP just before it still worked okay. The result I got was this:

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

This was very confusing, but after wrangling with it for quite some time, I finally figured out the problem: sp_executesql binds parameters to dynamic SQL by secretly adding a DECLARE statement to the top before executing. Since CREATE PROCEDURE must be the only statement in the batch, but there is now an extra statement before the CREATE PROCEDURE line, it throws an error. It does say Line 1--which further misled me--but this is obviously tweaked by the engine so people don't get confused about the line numbers of their own script when dealing with errors.
The solution to the problem was to detect which type of object was being worked with and NOT pass in the @Debug parameter so script that must have no other statements works okay. A quick change did the job:
IF @ScriptType IN ('Procedure', 'View', 'Function') BEGIN
   EXEC sp_executesql @CreateSQL;
END
ELSE BEGIN
   EXEC sp_executesql @CreateSQL, N'@Debug bit', @Debug;
END;

I could also have nested my dynamic SQL one level deeper, to create the procedure inside dynamic sql (again, inside the script in the table) but that was a less optimal solution in my case.
I suspect that using OUTPUT variables with sp_executesql would also add one or more statements to the end of the script to enable the engine to capture them, most likely in a SELECT statement that is silently swallowed up.


texts:
How to get a list of failed job run last night? I only find the following powershell script. What's the SQL equivalence?

dir $psPath\Jobs | % { $_.EnumHistory() } | ? { $_.RunStatus -ne 1 }


texts: I have "applied" Agent Software in my Oracle Cloud Control 13c. Agent was installed on previous servers and work fine. But now when I want to install it on new configured server, OEM tell me that "Agent Software Unavailable". But when I look to Setup -> Extensibility -> Self Update, there is this Agents status is "Apply". I can't remove or do anything from Action button. Also Size column is empty. How can I redownload or reapply this agent?


texts:
I have been looking online for an answer but do not really know how to formulate correctly what I would like to achieve and whether it's possible, sorry if the question sounds dumb. I am using Postgresql.

I have price data per day.

CREATE TEMP TABLE Price (id,Day, Price) AS
VALUES
  (1, 1, 40),
  (2, 1, 20),
  (3, 1, 50),
  (4, 1, 10),
  (5, 1, 20),
  (6, 1, 60),
  (7, 2, 10),
  (8, 2, 40),
  (9, 2, 10),
  (10,2, 20),
  (11,2, 10);

I want to assign numbers (1, 2, 3...) to the price data based on the day and the sum of the prices. Every time when the sum > 60, the sum calculation starts again + every time when a new day is reached, the sum calculation starts again. So for example:

Row 1 [day 1, price 40] = 1. Then for row 2 [day 1, price 20] the price sum is 20 + 40 < 61, therefore row 2 also gets assigned to 1. Then for row 3 [day 1, price 50] the price sum is 20 + 40 + 50 > 60, therefore the counting of the sum has to restart and number 2 is assigned to row 3. The result will look like:

Does anyone know if this is achievable and how? I understand how to take the SUM(Price) OVER (PARTITION BY Day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), but do not know how to restart the sum counting when the condition is met. Thank you in advance for the help!


texts: I can't divide 2 count(*) values without returning zero

texts:
The statement I'm using is for a KPI in Maximo and I currently have this statement:

select (select count(*) from incident where incident.slanum = 'USERADMIN' and
 incident.actualfinish > incident.targetresolve and date(incident.actualfinish) >=
 current_date-(day(current_date)-1)days) / (select count(*) from incident where
 incident.slanum = 'USERADMIN' and date(incident.actualfinish) >= current_date-
 (day(current_date)-1)days) from dummy_table

This returns a zero, because I'm dividing 2 integers I have realised. But I cannot work out what the syntax needs to be to use CAST or CONVERT to be able to successfully divide one figure by the other.

Please help!

Thanks, Will


texts: oracle 10G forms connection with Oracle database 10G Enterprise Edition in windows 7

texts:
I am trying to connect Oracle Forms 10g version (9.0.4) to the Oracle 10G Enterprise Edition.

At first I tried to connect to the db without setting the "Local Net Service Name Configuration" in Oracle Net Configuration Assistant.I got the error

After setting the "Local Net Service Name Configuration" in Oracle Net Configuration Assistant, I am still getting an error

Can anyone explain me how to connect to the database with forms as I am stubbed as of long struggle for connecting the database?

# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

The following are the listener.ora and tnsnames.ora contents

# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = RahulSingh-PC)(PORT = 1521))
    )
  )


texts: How can I stop a single Progress OpenEdge server process and not the whole database?

texts:
Is there a way to stop a single _mprosrv process started by the database broker? All I can find from documentation and google searches are ways to disconnect a single user connection or shut down the entire database.

For context, the problem is, that we are running an OpenEdge 10.2 database, that is mostly used by remote 4GL clients and one singular client using SQL over ODBC to run data export queries once a week. After rebooting the entire server in our current configuration, the database started up and broker spawned _mprosrv server processes up to the maximum number of servers allowed for the broker. Therefore it can't start up a _sqlsrv2 process any more, because all the slots are occupied. Even during low usage periods where no remote 4GL clients are connected, the ODBC connection does not succeed, since the idle servers are not shut down to allow new servers to be spawned.

Hence the question - can I shut down a single 4GL server process to trigger a new server creation for SQL connections without taking the entire database and application offline?


texts:
Could you please give little clarification about SQL server backup message

For example :

  BACKUP DATABASE successfully processed 19696388 pages in 1945.648
  seconds (79.088 MB/sec).

For example we have disk with database and we have separate disk for backup , does it mean that 79 MB/sec is that one disk reads data for backup with speed of 40 mb/sec and another disk writes data with speed 40 mb and as sum it gives us speed of 79 mb/sec , is it correct ?


texts: Audit trail database design for multiple related tables

texts:
We have the database design requirement for audit trail purpose in MySQL 5.7.

There is one main table and there are few sub-tables to the main table.  For every record in the main table, there will be multiple records in the sub-tables.  If there is any change in any of the tables, we want to capture all the information in all the tables for a main table record together.

We initially thought of designing a single table for audit trail which consists of all the columns of all these tables.  Whenever there is a change, trigger will capture the pre-image in this table and sub-table records for one main-table record would be stored in one column per sub-table in JSON format.

But we may make the change of 1 million records at the same time.  Using trigger is still fine, but manipulation of all the records to put it in a JSON format would kill the performance.

The audit trail is not for reconstructing inserts or to see the entire database changes.  If something got changed in any of the tables, we want to identify who changed it and when.


texts: Is SQL Server's fill strategy the same for multi-file tempdb as multi-file (non-tempdb) filegroups?

texts:
Will tempdb I/O from a single query be split across multiple tempdb files? (assuming that tempdb is configured to use multiple files, of course!)

For non-tempdb databases, MDSN seems to say that yes, newly-added data will be spread across multiple files in a filegroup:

  Filegroups use a proportional fill strategy across all the files within each filegroup. As data is written to the filegroup, the SQL Server Database Engine writes an amount proportional to the free space in the file to each file within the filegroup, instead of writing all the data to the first file until full. It then writes to the next file. For example, if file f1 has 100 MB free and file f2 has 200 MB free, one extent is allocated from file f1, two extents from file f2, and so on. In this way, both files become full at about the same time, and simple striping is achieved.

Does this same fill strategy apply to tempdb?

And does this answer depend on the type of query, e.g. parallel vs. non-parallel?  Or is the answer different based on the kind of tempdb I/O, e.g. for temp ables I create vs. tempdb usage by the database engine for worktables or spilling?


texts: Which files in the “MySQL-5.0.27-win32” folder do i need to backup?

texts:
Ok, I am using a pretty old MYSQL "mysql-5.0.27-win32" and the file and the structure maybe different from the latest Mysql (mysql 7).

Here is the current structure of of mysql-5.0.27-win32:

In "mysql-5.0.27-win32" folder:
bin
data
Docs
Embedded
examples
include
lib
mysql-test
scripts
share
sql-bench
my-huge.ini
my-innodb-heavy-4G.ini
my-large.ini
my-medium.ini
my-small.ini

In "data" folder:
mysql
mydatabase
...some-other-database that I don't touch...
my-PC.err
my-PC.pid
ib_logfile0 ---> this is a file, not a folder
ib_logfile1 ---> this is a file, not a folder
ibdata1 ---> this is a file, not a folder

the "mydatabase" folder has file such as .frm, .MYD, .MYI for each of its table,
"mydatabase" also has "db.opt"

Ok, here is the scenarios. I finished developing my WebApp using mysql-5.0.27-win32in my Local Desktop PC. Now it is a time to deploy my app.

To deploy, I copy the whole mysql-5.0.27-win32 folder (which contains the current DB structure + some basic data) into Virtual Server.

So, there will be 2 same folders the original mysql-5.0.27-win32 in the Local PC & the copy  mysql-5.0.27-win32 in the Virtual Server. At the beginning, these 2 folders are exactly the same. They have the same DB structures (ie same number of tables & columns in the tables) & same data stored in the tables.

From now on, my website will store user data directly to the copy mysql-5.0.27-win32 folder in Virtual Server (VPS). But if the VPS got shutdown then I will lose all data.

Thus I want to back up daily the "mydatabase" from the copy mysql-5.0.27-win32.

So which file in the "MySQL-5.0.27-win32" folder do i need to backup so that when I put them into the original mysql-5.0.27-win32, then I can have exactly the same info as I have in the copy mysql-5.0.27-win32?

Supposed that we know while data file to copy then:
is there any problem for this solution “Backup mysql database by copying the data file even when MYSQL server is running”?

(pls see the subsequent question: "Backup mysql database by copying the data file even when MYSQL server is running" solution! Is there any problem for this backup solution?)


texts: Mysql query - find out 1000th row from a table for each user

texts:
Lets say I have a table where we log activities of user, I need to find out created_at of the 1000th activity of each user..

Following query works fine for one user..

select `created_at` from `user_activity` where `user_id` = 2 order by `created_at` limit 1 offset 1000

But how do i run it for all users..

I have tried

where user_id in (select DISTINCT(user_id) from user_activity)

OR

GROUP BY user_id


texts: Error on SELECT - Primary filegroup is full

texts:
A SQL Server 2008 R2 database view unions several transaction table. The query has been slow over the couple of months and the dev team is planning to recreate the query. I need to ETL the results into a flat file and purge them from the DB. When I tried to query the view (of 5M records) this error showed up:

  Msg 1105, Level 17, State 2, Line 1
  Could not allocate space for
  object 'dbo.SORT temporary run storage:  140744470495232' in database
  'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by
  deleting unneeded files, dropping objects in the filegroup, adding
  additional files to the filegroup, or setting autogrowth on for
  existing files in the filegroup.

What is the recommended file growth I should set, how is this error managed or solved?
Thanks in advance.