MySQL: Updating large data set including time aggregation I am having a large MySQL (5.7) table with millions of rows (contains data for each second). Based on these values calculations should be performed, in some cases for large data ranges. Therefore, I want to perform a preprocessing where I perform the calculations beforehand and store the result in a separate table. Due to some internal reasons, aggregations are performed so, for example, I need the avaerage value of the formula for one hour. Here is an example:
CREATE TABLE `datavalues` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `ddate` DATE NOT NULL,
    `ttime` TIME NOT NULL,
    `unixtime` BIGINT(20) NOT NULL,
    `value1` DOUBLE,
    `value2` DOUBLE,
    PRIMARY KEY (`id`),
    INDEX `unixtime_idx` (`unixtime`)
);
CREATE TABLE `calculation_result` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `ddate` DATE NOT NULL,
    `ttime` TIME NOT NULL,
    `unixtime` BIGINT(20) NOT NULL,
    `result1` DOUBLE,
    PRIMARY KEY (`id`),
    INDEX `unixtime_idx` (`unixtime`)
);

As an exemplary calculation I calculate value1-value2. The initial data query for filling the calculation table with one value for each hour could look like this:
INSERT INTO calculation_result (ddate, ttime, unixtime, result1)
select max(ddate), STR_TO_DATE(TIME_FORMAT(max(ttime),'%H:00:00'),'%H:%i:%s'),
UNIX_TIMESTAMP(adddate(max(ddate), INTERVAL HOUR(max(ttime)) HOUR)), AVG(value1 - value2)
FROM datavalues GROUP BY ddate, hour(ttime);

So far so good. However, I am struggling to implement a query for updating the result1 column in the calculation_result table in case the calculation description changes, for example, from value1 - value2 to value1 + value2. How can I efficiently update the result1 column? Deleting and recreating the calculation table is not an option as it contains a lot of further calculations in different columns. Additionally, I expect the updating process to take some time. As data is newly imported on a regular basis, how can I prevent the calculation_result table to be locked? Maybe using bulk updates of data chunks?
Thanks for your help.

If your event table is huge, what takes a while is reading it. Once the data is read, computing a few extra aggregates won't hurt a lot. And with one row per second in the event table and one row per hour in the aggregate table, you'll reduce data size substantially anyway. So you should basically store everything you'll need.
For example if you need avg(a-b) and avg(a+b), knowing that avg(a-b)=avg(a)-avg(b) and avg(a+b)=avg(a)+avg(b) the simplest option would be to just store avg(a) and avg(b) and you get the sum, difference, and any linear combination thereof for free when selecting from the aggregate table. Of course, that won't work if you use aggregates that cant' be combined this way. For example max(a-b) can't be calculated from max(a) and max(b).

As data is newly imported on a regular basis, how can I prevent the calculation_result table to be locked?

Use an engine that supports this, like InnoDB, for your aggregates table. If you don't want a long running update on your aggregation table, you could write the results of the aggregation in a temporary table, this takes no locks while the query runs. Then update the main table quickly.
If you have performance problems with your amount of data you could consider trying clickhouse, or another time series database. Clickhouse is easy to use if you know SQL, but it's not a general purpose database. If you use it for what it's good at, performance is just ridiculous, like 120 million rows/s for SELECT x,avg(y) GROUP BY x. It also has an AggregatingMerge table engine which automatically stores aggregates on larger time windows automatically, which is basically what you're asking. And since it compresses the tables, data files are tiny. It uses huge amounts of RAM though.


dbatools - Copy all jobs to the other node - selected jobs only Is it possible to copy all sql server agent jobs using dbatools from one node to another, except a particular job? or perhaps just copy a set of jobs?

The beauty about dbatools is that the source code is available on github

you can use -ExcludeJob parameter

  .PARAMETER ExcludeJob
              The job(s) to exclude. This list is auto-populated from the server.


Why the query optimizer choose this bad execution plan? We have a mariadb table (stories) with above 1TB of data, periodically running a query that fetches recently added rows for indexing somewhere else.

innodb_version: 5.6.36-82.1
version       : 10.1.26-MariaDB-0+deb9u1

The query works just fine when the query optimizer decides on using the secondary index to do a range walk through (in buckets of a 1000)

explain extended     SELECT  stories.item_guid
    FROM  `stories`
    WHERE  (updated_at >= '2018-09-21 15:00:00')
      AND  (updated_at <= '2018-09-22 05:30:00')
    ORDER BY  `stories`.`id` ASC
    LIMIT  1000;

+------+-------------+---------+-------+-----------------------------+-----------------------------+---------+------+--------+----------+---------------------------------------+
| id   | select_type | table   | type  | possible_keys               | key                         | key_len | ref  | rows   | filtered | Extra                                 |
+------+-------------+---------+-------+-----------------------------+-----------------------------+---------+------+--------+----------+---------------------------------------+
|    1 | SIMPLE      | stories | range | index_stories_on_updated_at | index_stories_on_updated_at | 5       | NULL | 192912 |   100.00 | Using index condition; Using filesort |
+------+-------------+---------+-------+-----------------------------+-----------------------------+---------+------+--------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

But occasionally, even with small differences in the data set (Note the second timestamp difference with the query above, worth to mention that the whole table holds data for several years and holds several dozens millions of rows) decides to use the primary key index

explain extended     SELECT  stories.item_guid
    FROM  `stories`
    WHERE  (updated_at >= '2018-09-21 15:00:00')
      AND  (updated_at <= '2018-09-22 06:30:00')
    ORDER BY  `stories`.`id` ASC
    LIMIT  1000;

+------+-------------+---------+-------+-----------------------------+---------+---------+------+--------+----------+-------------+
| id   | select_type | table   | type  | possible_keys               | key     | key_len | ref  | rows   | filtered | Extra       |
+------+-------------+---------+-------+-----------------------------+---------+---------+------+--------+----------+-------------+
|    1 | SIMPLE      | stories | index | index_stories_on_updated_at | PRIMARY | 8       | NULL | 240259 |    83.81 | Using where |
+------+-------------+---------+-------+-----------------------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Causing it to walk through the whole primary key index (I guess sequentially) to then filter on updated_at field taking several hours to complete instead.

The query was created by the ORM ActiveRecord and is probably far from ideal, as a workaround solution we came up with a couple of manually crafted queries, that moves the ORDER BY stories.id out, and/or using use/force index to avoid the PK since we are really filtering our data set by updated_at.

What I'm interested in understanding here is how/why the query optimizer chooses that execution plan, I understand that query optimizer uses index/table statistics for taking that decision but in this case, and If my understanding is correct about how innodb works, this is pretty clear that walking through a huge PK while not using any PK id for filtering is not ideal.

I'm essentially trying to understand where that "bad" decision comes from, what statistics or unknowns variables are used not to end up with the good plan (the one that is often chosen and which is orders of magnitude faster)

feel free to correct any of my assumptions as I'm definitively not a DBA expert,

Thanks in advance!

My suggestion would be to change the ORDER BY id and use instead:

ORDER BY updated_at, id

The secondary index on (updated_at) includes the id as well (which I assume is the primary key), so with this change there is no chance that the index won't be used, as it will be a perfect match for both the WHERE and the ORDER BY clause.

Bonus point, the query's plan will get rid of the sort as well.


dangers of using SHRINKFILE in sql server 2008 I'm trying to shrink the file size of a massive log file and was wondering how I estimate the size of the log file and what the dangers in running this are

If you use the full or bulk logged recovery model,
I advise that you take a look at this post for shrinking the log file. First perform a full database backup as switching the recovery models will break the log chain. Also chose a reasonable initial size for the log file, and generally speaking I use an increment in MB rather than percents.


BULK INSERT runs fine in window, but fails inside sql job I have two server (in the same network), Server A, is the web server, with IIS and all, Server B is the second server, the DB (MS SQL Server 2012), both Windows 2012 R2.

In the Server A, files are uploaded to this folder, uploaded-bills. That I have shared with \\web-server-01\uploaded-bills\.

I have given FULL ACCESS (in the security tab) to EVERYONE in that folder!

Now, this bulk insert runs in the SQL Server in Server B

INSERT INTO BillDetails (
    ItemID
    ,Qty
    ,Amount
    )
SELECT ItemID
    ,Qty
    ,Amount
FROM OPENROWSET(BULK '\\web-server-01\uploaded-bills\itemised-bill.csv', FIRSTROW = 2, FORMATFILE = 'D:\itemised-bill-format-file.fmt') AS BulkLoadFile;

Which runs perfectly fine. But if I create a job, to ran at a particular schedule, it always fails. When it is triggered automatically (due to time cycle) or I ran the job manually. When I view the history, it shows me this error:

The job failed.  The Job was invoked by Schedule 16 (InsertItemisedBillsEveryFiveMinutes).  The last step to run was step 1 (BulkInsert).,00:00:00,0,0,,,,0
05/29/2017 15:30:00,InvoiceUploadRawInsertJob,Error,1,SERVER-B,BillsUploadRawJob,BulkInsert,,Executed as user: NT SERVICE\SQLSERVERAGENT. Cannot bulk load because the file "\\web-server-01\uploaded-bills\itemised-bill.csv" could not be opened. Operating system error code 5(Access is denied.). [SQLSTATE 42000] (Error 4861).

As I mentioned, I have given everyone the whole access, even then the sql sever agent can't seem to access it. Also, I can access this folder fine in the windows explorer without any issues, and even the query executes fine when you run this query in a new query window. But for the love of Microsoft I can't figure out why the job is failing, and what should I do to fix that. Can anyone help me figure out what am I doing wrong?

Update
Update after comment and Remus's answer.

Yes Server A and Server B are in domain, in the same domain.

The SQL Server service (MSSQLSERVER) is running under a windows account name Expo (in the log on tab in the service configuration, in services.msc) amd that Expo account is available domain, so I can log in in Server A as well as Server B using that account's credentials.

The SQL Server Agent is running under (in the log on tab in the service configuration, in services.msc) NT Service\SQLSERVERAGENT.

As for Everyone, I grant it Read\Write in the file sharing dialog, under Sharing tab, and in the Security tab, I've added everyone with Full Control checkbox checked. (But I am guessing the Everyone on Server A could be different from the Everyone on Server B, I could be wrong though).

Please let me know if I could provide any more information to help sort out this.

Update 2
I've created the credentials and proxy, but in the job step dialog, Job Step Properties, the Run as option is disabled because the Type I have selected is Transact-SQL script (T-SQL) which is actually it's type. So, how do I specify the proxy account for this?

When you run that query interactively then SQL Server impersonates you when accessing the share. Normally this would fall into double-hop Kerberos Constrained Delegation and fail unless set up appropriately. It works because probably one of the hops is short-circuited (eg. you log on from a session on Server A or Server B).

When the query is run as a job the query impersonates the SQL Server agent service, and it all depends on how the SQL Server Agent is configured to run and connect to SQL Server. Different answers apply depending on whether the connection is made using a SQL Server login (eg. sa), a domain account, a virtual account, a local built-in account (eg. LocalService) and so on and so forth.

We here cannot guess what the problem is. To document it, please provide the details of the accounts involved. Are Server A/B in a domain? In the same domain? Is SQL Server running as a domain account, as a virtual account, as a local account, as local service, or what? Is SQL Server Agent running as who?

When you granted access to Everyone, did you grant access to the share or did you grant access to the folder?

Ideally you should set up an audit policy and then simply consult the event log, which will tell you exactly which account attempted the access. See Auditing File Access on File Servers

Finally, you may be interested in reading up about SQL Server Agent proxies.


DB2 LUW V10.5 on Windows. How to Manually rotate the Diaglog using scripts In DB2 LUW V10.5 on Windows, is there a way to manually rotate the diaglog log file? Currently, it is almost 1GB and it is difficult to open it to find info.The diagsize is set to 0 and I cannot change it due to other constraints.

The easiest way to rotate the db2diag.log file is to use the db2diag utility with the -A ("archive") option.  It will rename the current db2diag.log file (appending the current timestamp to the file name).

db2diag is also quite useful for searching through huge db2diag.log files (instead of trying to open them in a text editor).  It has many options to filter the file based on time, content of messages, etc.  You can read more in the documentation.


Optimize MySQL FULLTEXT searches to use another index I'm running a price comparison service, with a database of products that aims to become quite large (in the millions or more).

The Product table looks like this:

CREATE TABLE Product (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    site_id INT(10) UNSIGNED NOT NULL,
    brand VARCHAR(100) NULL,
    name VARCHAR(500) NULL,
    variation VARCHAR(100) NOT NULL,
    PRIMARY KEY (id),
    INDEX Product_Site (site_id),
    FULLTEXT INDEX Product_Fulltext (brand, name, variation),
)

Now I need to query products belonging to a given site, using a search string, which translates into this query:

SELECT * FROM Product
WHERE site_id = 7
AND MATCH (brand, name, variation) AGAINST('something')
ORDER BY MATCH (brand, name, variation) AGAINST('something') DESC
LIMIT 10;

The query is not very fast, even though my table is still quite small (< 1M).

The reason is given by EXPLAIN:

+----+-------------+---------+------------+----------+-------------------------------+------------------+---------+-------+------+----------+-------------------------------+
| id | select_type |  table  | partitions |   type   |         possible_keys         |       key        | key_len |  ref  | rows | filtered |            Extra              |
+----+-------------+---------+------------+----------+-------------------------------+------------------+---------+-------+------+----------+-------------------------------+
|  1 | SIMPLE      | Product | NULL       | fulltext | Product_Site,Product_Fulltext | Product_Fulltext |       0 | const |    1 |    50.00 | Using where; Ft_hints: sorted |
+----+-------------+---------+------------+----------+-------------------------------+------------------+---------+-------+------+----------+-------------------------------+

It looks like MySQL is choosing to use the FULLTEXT index first, finding all rows that match the search string, then filter the rows that belong to the site I'm interested in. This is obviously highly inefficient.

And AFAIK:

MySQL still cannot use two indexes in the same query,
there is no way to have FULLTEXT act as a "hybrid" index that would be segmented by an integer id,
partitioned tables do not support FULLTEXT indexes;

but I do hope that I'm missing something.

Is there any way to speed up this query? Or should I move away from MySQL to implement these searches? All suggestions welcome.

Give this a try (and let us know if it helps):

SELECT  p.*, ft.relevance
    FROM  (
        SELECT id,
               MATCH (brand, name, variation) AGAINST('something' IN BOOLEAN MODE) AS relevance
            FROM Product
            WHERE MATCH (brand, name, variation) AGAINST('something' IN BOOLEAN MODE)
          ) AS ft
    JOIN Product AS p USING(id)
    WHERE p.side_id = 7
    ORDER BY  ft.relevance DESC
    LIMIT  10;

Also, provide EXPLAIN FOMAT=JSON SELECT ....


Database user specified as a definer I have a view in my database. problem is below

Error
SQL query:

SELECT *
FROM `lumiin_crm_prod`.`v_contact`
LIMIT 1 ;

MySQL said:

  1449 - The user specified as a definer ('lumicrm'@'%') does not exist

i Google for a solution

User is created for Host & not for Global.

How to create the User for Global ????

Long story short: You do not have the user 'lumicrm'@'%' on the DB Server.
Login to MySQL and run
SELECT user,host FROM mysql.user WHERE user='lumicrm';

Let's say you see the user 'lumicrm'@'localhost'
You have to create the user so that the view can be recognized. There are three(3) things you can try
Try This #1
Login to MySQL as lumicrm and run this command
SHOW GRANTS FOR CURRENT_USER();

Given the output of that query:

Copy the output of that query to a Text File
Change the host portion of the userhost to '%';
Copy and Paste into a MySQL Session to create the View.

Try This #2
CREATE TABLE mysql.lumicrm LIKE mysql.user;
INSERT INTO mysql.lumicrm SELECT * FROM mysql.user WHERE user='lumicrm';
UPDATE mysql.lumicrm SET host='%';
INSERT INTO mysql.user SELECT * FROM mysql.lumicrm;
FLUSH PRIVILEGES;
DROP TABLE mysql.lumicrm;

Now you have the MySQL user 'lumicrm'@'localhost' hacked in
Try This #3
Dump the Views to a Text File. Here are my posts on how to do this:

Jun 26, 2011 : Modify DEFINER on Many Views
Jan 19, 2012 : Will mysqldump command also backup all the views that are present in DB or there is a special command for it?
May 02, 2012 : Problem with View When Restoring a MySQL Backup

Edit the user for the View with the appropriate username. Copy the View Creation Code and Paste into a MySQL Session to create the View.
Give it a Try !!!


Days between two days by month SQL I have a new job where I am re-learning SQL (oh the joy) after years of not using it.

I currently have a table that looks something like this:

RoomNumber,
BlockID,
ModelEndDate,
DepartureDate,
DATEDIFF(DAY, StartDate, EndDate) AS NumDays

So essentially there is a model applied to each room, which has an enddate. If a resident leaves early we set the DepartureDate and calculate how many days left in the Model.

EDIT Some background on how this actually is calculated (as I should have probably included it). Each room is occupied at different periods throughout the year, and has associated models to show what those dates are. E.g. one room may have the following:

ModelStartDate,
ModelEndDate,
Period

As a fairly standard rule of thumb, each room usually has three periods. I currently just calculate the total number of free days by either working out the difference between the start and end dates or (if departure is mid-model) I would work out the difference between the DepartureDate and ModelEndDate. I then group by Room giving me the totalfreedays.

What I now need to do is work out how these days are spread by month per block (many rooms to a block). So, if somebody was to Depart very early in the year, they would free up a load of days in each month for that room. For financial reporting I want to be able to say how many free days, per month, per block (summing all rooms within the Block). Ideally I would like a table that looks like the following:

BlockID,
DaysFreeSept,
DaysFreeOct,
DaysFreeNov
etc

I am having a little trouble understanding how I should accomplish this, I have looked through a few answers on here but they are mighty confusing.

Cheers for any help you can give me.

This is on SQL Server 2008 R2 (10.50)

This is a situation that might benefit from use of a date table. https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

Then you can join on the date table for all dates between the DepartureDate and the ModelEndDate, something like this:

Select y.BlockID,
    DaysFreeJan = Sum(CASE WHEN d.MonthName = 'January' THEN 1 END),
    DaysFreeFeb = Sum(CASE WHEN d.MonthName = 'February'THEN 1 END),
    ...
  From dbo.YourTable As y
  Join dbo.DateTable On d
    On y.DepartureDate < d.DateKey
    And y.ModelEndDate >= d.DateKey
  Group By y.BlockID;

So each row of your table will join to multiple rows in your date table, then this query counts how many of those rows there are in each month.


How to check which database roles can see `masked` columns? In SQL Server 2016 SP1 standard edition we can use dynamic-data-masking. The masking of data is controlled using the built-in security, for example:

REVOKE UNMASK TO user_who_cannot_see_senstive_data

Of course, this is not working for the users who are members of the db_owner database role.

Is there a list with security roles or cases showing when the REVOKE UNMASK is going to work?

I have found a database engine permissions but it's too complicated and does not seem to show when data cannot be masked from a user.

How to check which database roles can see masked columns?

Normal query which checks for users permissions seems to do the trick

    select  princ.name
,       princ.type_desc
,       perm.permission_name
,       perm.state_desc
,       perm.class_desc
,       object_name(perm.major_id)
from    sys.database_principals princ
left join
        sys.database_permissions perm
on      perm.grantee_principal_id = princ.principal_id

I tested it on below sample script from msdn

CREATE TABLE Membership
  (MemberID int IDENTITY PRIMARY KEY,
   FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL,
   LastName varchar(100) NOT NULL,
   Phone# varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,
   Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL);

INSERT Membership (FirstName, LastName, Phone#, Email) VALUES
('Roberto', 'Tamburello', '555.123.4567', 'RTamburello@contoso.com'),
('Janice', 'Galvin', '555.123.4568', 'JGalvin@contoso.com.co'),
('Zheng', 'Mu', '555.123.4569', 'ZMu@contoso.net');

CREATE USER TestUser WITHOUT LOGIN;
GRANT SELECT ON Membership TO TestUser;

GRANT UNMASK TO TestUser;
EXECUTE AS USER = 'TestUser';
SELECT * FROM Membership;
REVERT;

now querying gives me below result


Broken SELECT in Sql Server Compact 3.5 SP2? I'm looking for a cause of a really strange case of SQL Server Compact db corruption. The case is that in a .NET Compact Framework app that we are running every once in a while we get into a situation where the following statement returns no rows

select * from order_item where id = '043740d1-c6d0-4fd4-ae84-fdb9218f8b81'

while

select * from order_item where id LIKE '043740d1-c6d0-4fd4-ae84-fdb9218f8b81'

returns one. Actually, when you select all rows from the table you can see that this row indeed exists in the db. The id column is uniqueidentifier, so it's not related to casing etc.

In any case, when I run the sdf file through Repair in SSMS, the first query starts returning the correct row.

I can only guess that the problem is related to some sort of index corruption - maybe it gets out of sync with the actual data store. Neither the app nor SSMS gives any sign of DB corruption - no exceptions, no messageboxes whatsoever.

Anyway, I can't really get to the root cause of this. I think it might have to do with modifying the db from different threads (at some points we may have 2 threads working on the db), but I thought this shouldn't be an issue.

Any idea what might cause this sort of issue?

UPDATE:
As @Catcall mentioned dropping the index fixed the issue, so it seems it's related to an out-of synch index. I think it's likely happening when the row is deleted - the index gets updated but the table row isn't for some reason.

I can only guess that the problem is related to some sort of index
  corruption - maybe it gets out of sync with the actual data store.

You could isolate index issues by just dropping the index. That will slow down some or all of the queries that access that table, but if the problem goes away, you know for certain that the index was the source of the problem.

But even if the index is the source of your problem, it's not the root cause of your problem. To my way of thinking, the root cause is quite likely to be in Microsoft's code. An index that doesn't match the data is one of those things the database shouldn't allow. And if it happens, you should see big, waving red flags.

You can verify and and try to repair a SQL Server CE database in code.  I'm not sure whether this exercises the same code you'd use by trying to repair through SSMS. (I'd hope so, but I know that hope doesn't scale well.)

How to: Verify and Repair a SQL Server Compact Edition Database (Programmatically)

  I think it might have to do with modifying the db from different
  threads (at some points we may have 2 threads working on the db), but
  I thought this shouldn't be an issue.

Documentation for .NET 3 SqlCEConnection class.

  Any public static (Shared in Visual Basic) members of this type are
  thread safe. Any instance members are not guaranteed to be thread
  safe.

I think you're safe if each thread uses a separate connection. I could be wrong.


How to change the AES key of a specific column in mysql? I have a test table in which some columns are encrypted using an AES key, now I think of changing the AES key of those column?

I used the following query to set an AES key:

UPDATE test_encrypt SET first_name = AES_ENCRYPT(first_name, 'g50l2');

Now, I want the change the AES key, Is there any way?

Once you have encrypted the data using an AES key the data is encrypted (the point of using AES). The only way to change the key would be to decrypt the data with the old AES key and then re-encrypt the data using a new ASE key.

Some good MySQL AES documentation.


CLR_SEMAPHORE & CXPACKET wait types I ran sp_Blitzfirst, and it threw back CXPACKET and CLR_SEMAPHORE waits as the top waits for my SQL server.

I know how to fix CXPACKET waits, but how do I go about fixing CLR_semaphore waits?

Ignore that wait type CLR_semaphore - its a benign wait type to be filtered out as per Wait statistics, or please tell me where it hurts


SQL Server secondary replica snapshot with Basic Availability Group I have the following scenario:

I created an HA group of two nodes in SQL Server 2016 Standard (which is limited to two nodes, one database per group and a non readable secondary replica).
I tried to take a snapshot of the secondary non-readable replica, but it's didn't work and I can do that only on primary replica.

On failover snapshot replication (local publication) is moving automatically on primary replica.

From what I read it's possible to have a snapshot on secondary non-readable replica.

What am I doing wrong?

That link refers to taking a Database Snapshot of the secondary database, which was possible in Database Mirroring.

Remember there are lots of unrelated things that use the word "snapshot" in SQL Server:

  Database snapshots are unrelated to snapshot backups, snapshot
  isolation of transactions, or snapshot replication.

It's not possible to use a Snapshot Replication with a secondary database:

  In an availability group a secondary database cannot be a publisher.
  Republishing is only supported when transactional replication is
  combined with Always On availability groups.

Replication, change tracking, & change data capture - Always On availability groups


"show create table" output with out "show"? What

I'd like to be able to get the SQL statement by 'show create table' as something I could do something with along the lines of

insert into mytable (myTextColumn) (show create table blah)

I've been able to find some stored procedures that do this for other brands of SQL than I use.  I realize all the required information is in information_schema and I could use that to build the create statement myself.  It just seems like this is a problem that's been solved in other flavors and there's likely already a stored proc out there to do what I want w/o reinventing it myself for mysql.

Why

I'm trying to set some dynamic schema introspection as part of documentation.  The idea is I'd like to have the doc pages illustrate a current representation of the schema in the various environment it may exist in (dev, qa, prod).

The "obvious" thing to do is just query the dbs and "show create table blah".  However doing that requires select access on the table.  I don't really care to create an account with global read access to every table in every DB.

I'm looking to either have a restricted script on a cron that runs/collects the output of show create table; or have a mysql event that constructs the output I want in an event.  Either way this output would ultimately be persisted to a documentation db w/ just the schemas stored so the documentation app can pull this information without its db account having select access to the actual tables.

I'm tempted to lean toward an internal event that populates this just so there's not an account out there with wide read access.

Why the why

I'm explaining my ultimate goal in case someone can point out a different implementation path or solution that matches my requirements (or convince me my requirements are off to begin with).

If you want something that's quick-and-dirty, may you should consider doing this:
STEP 01) Use mysqldump
mysqldump --no-data --all-databases > MySQLSchema.sql

STEP 02) Parse the text
Every CREATE TABLE would mark the next databse
Every USE dbname marks the name of the next database
Have each CREATE TABLE description in a text file
STEP 03) Do a LOAD DATA INFILE of each of the parsed files into a table of your choice (such as mydb.mytables)
STEP 04) Query for the structures
SELECT table_desc FROM mydb.mytables WHERE dbname="...' AND tbname='...';

Your your would look something like this:
CREATE TABLE mytables
(
    dbname VARCHAR(64) NOT NULL,
    tbname VARCHAR(64) NOT NULL,
    table_desc TEXT DEFAULT NULL,
    primary key (dbname,tbname)
);

Your mission is to parse the mysqldump I just mentioned and get each table description loaded into this table, along with the database anem table name.
Once you construct such a parsing program, you could do this process every hour and update your dynamic documentation.
Give it a Try !!!
UPDATE 2012-04-01 00:39 EDT
Here is modified suggestion: Given the mysqldump suggestion I made, your should load tMySQL Schema attained from the mysqldump into another DB server that contains no data. Set it up as a replication slave using replicate-do-db=mysql. That way, no data will collect in the slave. You can use this slave as the source of the mysqldumping of the DB schema. This separates the data from the schema. You can use your documentation system fetch the schema from the slave.


Multiple calculations in a select statement with CASE I have the following query:

select
CASE
    WHEN f.nature = 'box'
    THEN 1
    WHEN f.nature = 'bag'
    THEN 3
    ELSE 0
END AS Pnature,
CASE
    WHEN f.type = 'electronics'
    THEN 0
    WHEN f.type = 'cloths'
    THEN 3
    ELSE 0
END AS Ptype
from product f

Now I want somehow to sum Pnature+Ptype in the select statement and do another CASE i.e. if Pnature+Ptype<100 then 20.

Is there a way to do this?

Using a derived table, as already shown, is one way. Another is to use CROSS APPLY:

SELECT
  CASE
    WHEN x.Pnature + x.Ptype < 100 THEN 20
    ELSE ...
  END,
  ...
FROM
  dbo.product AS f
  CROSS APPLY
  (
    SELECT
      CASE
        WHEN f.nature = 'box' THEN 1
        WHEN f.nature = 'bag' THEN 3
        ELSE 0
      END,
      CASE
        WHEN f.type = 'electronics' THEN 0
        WHEN f.type = 'cloths'      THEN 3
        ELSE 0
      END
  ) AS x (Pnature, Ptype)
;

The two method are somewhat similar: in both cases you are defining the expressions in a nested SELECT. The APPLY method is different in that you are not nesting the main query, only the bits that you want to additionally reference in the main SELECT.


Why is DROP DATABASE taking so long? (MySQL) New CentOS installation.

I was running an import of a large DB (2GB sql file) and had a problem. The SSH client seemed to lose the connection and the import seemed to freeze. I used another window to login to mysql and the import appeared to be dead, stuck on a particular 3M row table.

So I tried

DROP DATABASE huge_db;

15-20 minutes later, nothing. In another window, I did:

/etc/init.d/mysqld restart

The DROP DB window messaged: SERVER SHUTDOWN.
Then I actually restarted the physical server.

Logged back into mysql, checked and the db was still there, ran

DROP DATABASE huge_db;

again, and again I'm waiting already about 5 minutes.

Once again, it's fresh installation. The huge_db is the only db (other than system dbs). I swear I've dropped db's this large before and quickly, but maybe I'm wrong.

I've successfully dropped the database. It took something like 30 minutes. Also note that I think I was mistaken when I thought the mysqldump import was dead. The terminal connection was lost, but I think the process was still running. I most-likely killed the import mid-table (the 3M row table) and probably 3/4 of the way through the whole db. It was misleading that "top" showed mysql using only 3% of memory, when it seemed like it should be using more.

Dropping the DB ended up taking 30 min, so, again, I might not have had to restart the server and possibly could have just waited for the DROP to finish, but I don't know how mysql would react to getting a DROP query for the same db that it's importing via mysqldump.

Still, the question remains, why does it take 30min+ to DROP a 2GB database when all it should have to do is delete all the db files and remove all references to the DB from information_schema? What's the big deal?

Rather than killing the process, it would be safer if you did it within MySQL:

$ mysqladmin processlist -u root -p
Enter password:
+-----+------+-----------+-------------------+---------+------+-------+------------------+
| Id  | User | Host      | db                | Command | Time | State | Info             |
+-----+------+-----------+-------------------+---------+------+-------+------------------+
| 174 | root | localhost | example           | Sleep   | 297  |       |                  |
| 407 | root | localhost |                   | Query   | 0    |       | show processlist |
+-----+------+-----------+-------------------+---------+------+-------+------------------+

The query with id 174 is the one blocking deletion of the 'example' database, so before you kill any processes first let MySQL try to terminate the query:

$ mysqladmin kill 174

Run the processlist command above again to confirm that it was killed.

If this doesn't work, then you could perhaps look at killing the errant process, but before that you might try restarting the MySQL server.

You can also run commands like 'SHOW FULL PROCESSLIST' and 'KILL 174' in the MySQL shell, for example if you only have the MySQL client installed. The main point is to avoid killing the process using 'kill' in the shell unless absolutely necessary.

Generally speaking you can use either mysql or mysqladmin. You shouldn't need to be running commands like this that often though; once you start killing queries regularly something is definitely wrong and you'd be better off fixing that problem (killing the query process is just treating the symptom).


Aggregate a JSON doing an average on values without defining keys I would like to know if there is a way or an existing function to perform an aggregation on a JSON field by doing the average of values if the key appears multiple times with PostgreSQL. The json's result keys are unknow and have to be add to it if these keys are meet during the aggregate function. They can't be define on the query.

datetime             data
2018-05-06 18:17:00  {"speed":23.3,"orientation":1.3,"o2":75.2,"pm25":12.1}
2018-05-06 19:17:00  {"speed":20.3,"pm25":13.1}
2018-05-07 15:02:00  {"speed":21.3,"orientation":1.3,"pm10":72.2}

Imagine here we want the result aggregate by day

The result wanted :

day         data
2018-05-06  {"speed":21.8,"orientation":1.3,"o2":75.2,"pm25":12.6}
2018-05-07  {"speed":21.3,"orientation":1.3,"pm10":72.2}

The first two rows has been aggregate doing an average on recurrent keys and kept all of the existing keys.

I don't know how to do this, so I'll learn myself as we go!

Test table and data:

postgres=# CREATE TABLE testtable (
postgres(#     dt date,
postgres(#     data jsonb
postgres(# );
CREATE TABLE
postgres=#
postgres=# INSERT INTO testtable
postgres-# VALUES
postgres-# ('2018-05-06 18:17:00','{"speed":23.3,"orientation":1.3,"o2":75.2,"pm25":12.1}'),
postgres-# ('2018-05-06 19:17:00','{"speed":20.3,"pm25":13.1}'),
postgres-# ('2018-05-07 15:02:00','{"speed":21.3,"orientation":1.3,"pm10":72.2}');
INSERT 0 3
postgres=#

First, we don't need the time component of the data, so we cast:

postgres=# select dt::date from testtable ;
     dt
------------
 2018-05-06
 2018-05-06
 2018-05-07
(3 rows)

postgres=#

Next, we want to decompose the JSON object to its individual elements:

postgres=# SELECT dt::date, jsonb_each_text(data)
FROM testtable;
     dt     |  jsonb_each_text
------------+-------------------
 2018-05-06 | (o2,75.2)
 2018-05-06 | (pm25,12.1)
 2018-05-06 | (speed,23.3)
 2018-05-06 | (orientation,1.3)
 2018-05-06 | (pm25,13.1)
 2018-05-06 | (speed,20.3)
 2018-05-07 | (pm10,72.2)
 2018-05-07 | (speed,21.3)
 2018-05-07 | (orientation,1.3)
(9 rows)

postgres=#

Now split out the key<>value pairs from the above:

postgres=# SELECT dt::date, jsondata.key, jsondata.value
postgres-# FROM testtable, jsonb_each_text(data) as jsondata;
     dt     |     key     | value
------------+-------------+-------
 2018-05-06 | o2          | 75.2
 2018-05-06 | pm25        | 12.1
 2018-05-06 | speed       | 23.3
 2018-05-06 | orientation | 1.3
 2018-05-06 | pm25        | 13.1
 2018-05-06 | speed       | 20.3
 2018-05-07 | pm10        | 72.2
 2018-05-07 | speed       | 21.3
 2018-05-07 | orientation | 1.3
(9 rows)

postgres=#

Aggregate the above data:

postgres=# SELECT dt::date, jsondata.key, sum(jsondata.value::float) as total
postgres-# FROM testtable, jsonb_each_text(data) as jsondata
postgres-# Group by dt::date, jsondata.key;
     dt     |     key     | total
------------+-------------+-------
 2018-05-06 | o2          |  75.2
 2018-05-06 | speed       |  43.6
 2018-05-07 | orientation |   1.3
 2018-05-07 | speed       |  21.3
 2018-05-06 | orientation |   1.3
 2018-05-07 | pm10        |  72.2
 2018-05-06 | pm25        |  25.2
(7 rows)

postgres=#

Now turn the above resultset into JSON:

postgres=# With sourcedata as (
postgres(# SELECT dt::date as dt, jsondata.key as key, sum(jsondata.value::float) as total
postgres(# FROM testtable, jsonb_each_text(data) as jsondata
postgres(# Group by dt::date, jsondata.key
postgres(# )
postgres-# Select dt, jsonb_object_agg(key, total)
postgres-# From sourcedata
postgres-# Group by dt;
     dt     |                       jsonb_object_agg
------------+---------------------------------------------------------------
 2018-05-07 | {"pm10": 72.2, "speed": 21.3, "orientation": 1.3}
 2018-05-06 | {"o2": 75.2, "pm25": 25.2, "speed": 43.6, "orientation": 1.3}
(2 rows)

postgres=#


How to get value from range datetime from date format to bigint in postgresql I'm new using postgresql and had difficult to convert datetime to bigint type. So my tables has column timecreated that datatype is bigint. I want to get value with range datetime format like dd/mm/yyyy example : select * from mytables where timecreated_at between '01/09/2021' and '10/09/2021' . How i can achieved this?

You do not convert datetime to bigint, you use timestamp with time zone. To convert a string to a timestamp with time zone, use the to_timestamp function.


single table to reduce joint queries? We are doing e-commerce shopping site in PHP.

We designed database as below image.

we are planning to delete "category_Details" table and include "category_id & category_name" columns in "products_information" table as this going reduce one table. Is this good idea or is it better to have separate columns for "categories"? We may end up with 15 rows under "category_name" column.
We are planning to merge as we have to use multiple joint queries to fetch information?

In image designer_details table contains all sellers/vendors who sell their products.

It is not a good idea to put the category_name in the product_information. This way you will 'break' the normalization. Instead of storing the category_name 15 times you store the same name multiple times. You speak about gaining on joining but this will give you, taken the limited number of rows in category_details, not much advantage. The whole category_details table will be in memory and you only need to join if you need the category_name.

You say that you have about 15 categories. I suppose that you have (many) more product. So this makes it a one-to-many relation which is the second case in the answer to MySQL: multiple tables or one table with many columns? that you mentioned in a comment. There it mentions that in the case of a one-to-many relation you must put the information in separate tables.

Also look at the application level. If somebody has to enter each time a category_name you might end up with the same name but spelled different. This will make it hard to search on it.


Alter Table for Multiple Databases (same table name) Is there a good way to Alter a table (add a column) for multiple databases (think like 50 or so). Or is it pretty much manual for all of them?

the Tables are all the same and the column added will be the same as well.

Thanks!

Here's the technique I frequently use to do things like this with dynamic SQL. This also has applications beyond just "looping" across databases, too.

Now, you didn't say which 50 databases, so I just went with everything in the instance, as that's a pretty common thing to do:

DECLARE @sql nvarchar(MAX);

SELECT
    @sql = ISNULL(@sql, N'') +
        N'ALTER TABLE ' + QUOTENAME(d.name) + N'.[dbo].[MyTable] ADD C1 int;'
    FROM sys.databases d
    WHERE
        (d.database_id > 4) AND /* No system databases */
        (d.state = 0) AND /* Online only */
        (d.is_distributor = 0) AND /* Not a distribution database */
        (d.is_read_only = 0) AND /* We can write to it */
        /* TODO: filter me more? */;

BEGIN TRANSACTION;
    EXEC(@sql);
COMMIT TRANSACTION;

You can replace the sys.databases part with a list of constants, or a query from a master database table, or whatever is appropriate for your scenario. There's also no error checking, so you may want to add that.

(Yes, I left the syntax error in the above code on purpose.)


Deny INSERT/UPDATE/DELETE to all users for objects in a specific schema I've got a payroll-related schema that I need to secure. I figure if I prevent all users from directly modifying the tables, and create a set of procedures to handle all data modifications, we should be in good shape. I can have the procedures handle all the audit logging, wrap things in transactions as needed, etc.

But to do this, I need to deny direct DML statements to everybody on this particular schema - even the sysadmin server role. Is there any simple way to do this without resorting to INSTEAD OF triggers (which wouldn't be quite as bulletproof)? I don't see any syntax like "DENY INSERT, UPDATE, DELETE ON SCHEMA::schemaname TO ALL", and denying permissions to the "public" role doesn't seem to have the desired effect.

If all data access is via stored procedures, then permissions on the table are not checked of the same user (dbo probably) owns both procedure and tables. This is called ownership chaining

This also means the no permissions are needed on the tables at all. You don't need to deny or grant anything on the tables because they won't be checked. Lack of GRANT means no rights anyway, so you don't need DENY

Now, this means nothing to folk with elevated privileges: sysadmin, db_owner etc. These will always have rights. You can only use triggers to block these: but they can disable or drop triggers of course.

I assume that your "end users" are not running as db_owner or sysadmin...


Function execute query, manipulates result and then return the same result I need a function that does something like this pseudocode:

function get_data() RETURN SET OF something... as
BEGIN
    myResultSet = select id, some_other_column from ...... limit 20000;
    update some_other_table set status = 2 where id in (myResultSet.id);
    RETURN QUERY myResultSet;
END;

In other words, I need to execute a query, use the IDs I got in other update statement and then return this same query result.

I was wondering if there is a way to store a resultset in some kind of variable, but my research didn't find any good results. Any other kind of solution is appreciated, since I don't need to execute my same query twice.

You can use a cursor.

create type my_type as (id int, other_column text);

create or replace function get_data ()
returns setof my_type
language plpgsql as $$
declare
    cur cursor for select id, other_column from my_table limit 20000;
    rec record;
begin
    for rec in cur loop
        update other_table
            set status = 2
            where id = rec.id;
        return next rec;
    end loop;
end $$;

select * from get_data();

Note however that the function will perform 20000 (maybe less) updates, not just one.
It is not obvious if performance will be better or worse than in one query, as it depends on various circumstances. For example, one query should be much faster on an idle server whereas the function might turn out to be better on a heavily loaded server. The best way to check it is to test the function in the actual environment.

You can also use a temporary table. In this case rows are updated in one query.

create or replace function get_data_2 ()
returns setof my_type
language plpgsql as $$
begin
    create temporary table temp_table of my_type on commit drop;
    insert into temp_table
        select id, other_column from my_table limit 20000;
    update other_table
        set status = 2
        where id in (select id from temp_table);
    return query select * from temp_table;
end $$;


Why won't `max_connections` update? I'm using MySQL 5.7.29 on Ubuntu 16.04.  I'd like to increase the number of connections allowed by the MySQL server.  This involves setting configuration for both MySQL and Ubuntu.

MySQL

The primary config parameter is MySQL's max_connections.  I've edited /etc/mysql/mysql.conf.d/mysqld.cnf to have

max_connections = 800

(previously it was commented out and set to 100).  I also need to make sure that table-open-cache is high enough; for our application that's about 2 x max_connections plus a little extra.  $ mysqld --verbose --help reports

table-open-cache                2000

which should be high enough.

Ubuntu

For every open connection, MySQL opens a placeholder file for it.  Thus, in addition to increasing MySQL's max_connections, I need to make sure that the OS allows the MySQL process to open a sufficient number of files.

Existing answers for doing this divide into systemd and non-systemd approaches.  This system uses systemd.

non-systemd

The non-systemd approach involves editing /etc/security/limits.conf.  I followed instructions for doing this before I learned that systemd ignores this file for this case.  The original version of this question concerned that effort; I've edited it to remove what I now know is irrelevant.  I'm happy to restore information about what I did if anyone thinks it's relevant.  Long story short is that I successfully increased the output of $ ulimit -n from 1024 to 16384.

systemd

Raising the number of files MySQL can open on systemd requires editing the MySQL service file.

The systemd MySQL service file on this system is /lib/systemd/system/mysql.service.  Rather than editing the file directly, which I understand is bad practice, I created the directory /etc/systemd/system/mysql.service.d/ and the file override.conf within it.  To override.conf, I added

[Service]
LimitNOFILE=65536
LimitMEMLOCK=infinity

I also added fs.file-max = 65536 to /etc/sysctl.conf, though I'm still not clear on what relation that file has to systemd or whether it has any effect on a systemd system.

I reloaded systemd config ($ sudo systemctl daemon-reload) and MySQL ($ sudo service mysql restart).

Checking to see if it worked, I note that $ service mysql status indicates that the new override.conf file is being recognized:

$ sudo service mysql status
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled
  Drop-In: /etc/systemd/system/mysql.service.d
           └─override.conf
...

However, MySQL continues to report a value of 151 for max_connections:

$ sudo mysqld --verbose --help | grep max-connections
  --max-connections=# The number of simultaneous clients allowed
max-connections                                              151

(I get the same result checking mysql> SHOW VARIABLES LIKE 'max_connections' from within the interactive MySQL shell).

What else do I need to do to permanently increase max_connections?

Welcome.

As per MySQL Documentation, your MySQL server does not have enough resources.

Also go through this

  The table_open_cache and max_connections system variables affect the
  maximum number of files the server keeps open. If you increase one or
  both of these values, you may run up against a limit imposed by your
  operating system on the per-process number of open file descriptors.


Where would the table data be if you had 3 filegroups, 2 with tables and 1 with all indexes? I'm studying filegroups at the moment as I haven't used them much before. A class I'm taking mentions the above scenario. I was wondering if I'm understanding it correctly:

Would the 2 filegroups with tables just be holding table objects and the 1 filegroup with indexes be holding the table data?

It depends on what you mean by an index, and what you mean by a table object, because every table is either a heap or a clustered index. Either type of table can also have non-clustered indexes as well, which are copies of the data.

If you have all the non-clustered indexes in a separate filegroup, then you're in a space that was described by Greg Linwood in chapter 33 of the first SQL MVP Deep Dives book, where the suggestion was that because it's only a copy of data you might not have to back it up. It's very complicated to recover from though, so it's not for everyone.

Typically when I've heard people suggesting that all indexes are stored separately, it's because they figure that data that has a lot of Scans run against it might suit a different type of storage to data that has a lot of Seeks (I'm capitalising as I'm referring to the type of database operation, not a general 'scan'). And they figure that indexes have Seeks, and heaps have Scans. But they're forgetting that any kind of lookup (including RID Lookups against heaps) are the same kind of operation as a Seek, and that indexes often have Scans run against them too.


Canceling SQL Server Support I need your advice.

Our company is about to cancel the SQL Server support.

I have this instinct feeling this is a very bad idea.

No more SQL Server updates, I know, but even when that is reason enough for me to keep the support, my company thinks my Data Warehouse is running perfectly. So no reason to update "non existing" bugs, right?

My question for you is: Why would you keep the SQL Server support contract running?

(By support I'm assuming that you are talking about software assurance.)

SA gets you two basic things.

Phone Support
Ability to upgrade

It also gets you some other stuff.

If you have a cluster and you have an Enterprise Agreement then you get something called license mobility which allows you to have a passive node for free.  If you aren't clustered but are running within a VM license mobility gives you the ability to move the VM from host to host without issue.  If you don't have license mobility then you can only fail over the cluster (or move the SQL VM from host to host) once every 90 days.

Given that Microsoft is moving to a short release cycle (18-24 months give or take) having SA makes sense if you plan on upgrading any time soon after the new version comes out.

Now if you don't care about that stuff and you ditch SA you can still call in and get support for SQL Server, but keep in mind that you'll be the last one in the queue to get a call back, and at the lowest priority.  Now if you never call support that's fine, but when you do having SA in place pushes you up the chain from level 1 support to starting at level 2 support.

Now given that you are on SQL 2008 R2 today if you ever plan on upgrading to SQL 2012 or SQL 2014 if you don't have software assurance you have to purchase all new licenses to upgrade using the new licensing model.  If you have software assurance you get to upgrade for free.  So if you are using SQL 2008 R2 standard edition CPU licensing today a SQL 2014 upgrade might be really expensive all of a sudden due to the core licensing change which started in SQL Server 2012.


Mysql load from infile stuck waiting on hard drive I've got a windows 7 64 bit machine that I'm using for some load testing of a mysql db. My program uses sqlalchemy to connect and run several load from infile statements on said database. These bulk loads all happen within a single transaction, all keys are disabled beforehand, and each csv file is only a few megabytes large.

The problem I've run into is that the test machine gets IO bound. It has enough ram available (12G) to hold the entire transaction in memory and do a single flush out the other end. As far as I understand the manual, the innodb tables shouldn't touch the hard drive until it flushes the dirty pages at transaction completion.

The total data to be loaded is about 1G, spread across the different tables. It ends up taking 37 minutes to load it all. Here's my current test settings for perusal. I'd also be happy to report the results from show engine innodb status or similar queries if necessary.

To recap, I need to know if 37 minutes is a fast insert speed for this data size, and what I can do to increase the insert speed.

Edit:

Whoops! I forgot some important info.

Mysql version 5.5
Server has 12G total ram
Total rows inserted ~2,597,240

Your bulk insert buffer is 4G. That's great ... FOR MyISAM !!!

InnoDB does not use the bulk insert buffer.

You may need to have sqlalchemy throttle the load data infile calls into multiple transactions.

You may also want to disable innodb_change_buffering, setting it to inserts.
 Unfortunately, you cannot do SET GLOBAL innodb_change_buffering = 'inserts';. If you dom you may need to set it in my.cnf and restart mysql.

UPDATE 2012-07-13 16:53 EDT

I just noticed that you have two values in the my.cnf for innodb_buffer_pool_size. First one is 2385M, and the last one is 14G. If MySQL for Windows accepted 14G and you only have 12G of RAM, your server must be having a good old time swapping.

You can verify what the buffer pool size is with

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

UPDATE 2012-07-13 16:58 EDT

You may also want to check how full the buffer pool is with this

SELECT FORMAT(A.num * 100.0 / B.num,2) BufferPoolFullPct FROM
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;


Can a query whose execution plan uses parallelization use the cores of a processor on a another server that's part of the same Availability Group? If I have two servers (server A and server B) that are part of the same Availability Group failover cluster, and a query running on server A has an execution plan that uses parallelization, is it possible for that query to use cores from a processor on server B?

No, execution plan parallelization refers to using multiple cores on the server executing the query.  Parallelize plans are not executed across availability group replicas.


DBIO Error: Utility 'mysqldump.exe' not found in MySQL home 'MySQL Connector/Net' I am using dbeaver 3.6.10 and I would like to export my database (MySQL).

I select the database > right mouse click on it > tools > export database > next > start

I get the following error:

  IO Error: Utility 'mysqldump.exe' not found in MySQL home 'MySQL Connector/Net'

I do know where mysqldump.exe is located on my machine.

  C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe

But I am new to dbeaver and I am not completely sure where I can set the mysqldump.exe value in dbeaver in order to be able to create db dumps.

Can you anyone please point me in the right direction?

I've figured it out. Export database is working now.

I had to copy mysqldump.exe from

C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe

to

C:\Program Files (x86)\MySQL\Connector NET 6.5.4\mysqldump.exe


SSIS package with log provider configured using expression - creates multiple log files, fails if I change original target folder I have created a small SSIS solution to illustrate my problem.  Unfortunately it looks like I can't attach it here, so I will try to explain.  I can send the solution to people individually.

SSIS SQL Server 2008.  I have a package called "Parent_Package" with a single variable: "User::Log_Path".  During design time I assigned this value to it: "C:\temp\Log_Design_Time".  The package has a log provider configured (simple text file).  The connection string of the log file has an expression: @[User::Log_Path]  + "\" +  @[System::PackageName] + [expression for timestamp] + ".txt".   As you can see, I would like to create a new log file for each package execution.

If I now run this package, I will have two log files generated!  One will have a timestamp from around the time I created the package.  This file only has the header fields and no other other information.  The second file has the correct timestamp and correct output information:

It appears that the first file gets created during validation (even though I set DelayValidation = True for the log file connection manager).  So my first question is why is this happening?  It doesn't seem like the right behavior.
Now to take this to the next step, I create a child package "Package_Child".  I add an execute package task to the parent package to run this child package.  In the child package I add a variable by the same name as above: "User::Log_Path".  Next, I add a configuration to the child package, so that the value of "User::Log_Path" is obtained from the parent package variable by the same name.  Finally I add log provider to the child package and set it up the same way as described above for the parent package.

Let's now run the parent package again.  This time I will end-up with three log files: two for parent and one for child:

For my next test, I now would like to change the location of the log files, so I modify the value of the [User::Log_Path] variable in the parent package to "C:\temp\Log_Run_Time".  If I run now, I will have four log files!  Two for the parent package in the  "Log_Run_Time" directory and two for the child package - one in "Log_Design_Time" and one in "Log_Run_Time" directory:

What's going on?
Finally, if I simply remove the original "Log_Design_Time" directory, my child package will start failing complaining that it cannot find the path specified:

Obviously it's still looking for the path specified earlier, but why?  Again, I set "Delay Validation" = True on all my connection managers and the package itself.
I appreciate any help on this.

Thank you!

P.S.  Here is a complete expression for log path: @[User::Log_Path]  + "\\" +  @[System::PackageName] + "_" +
(DT_STR, 4, 1252)DATEPART("yyyy", @[System::ContainerStartTime]) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mm", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("dd", @[System::ContainerStartTime]), 2) + "_" +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", @[System::ContainerStartTime]), 2) + ".txt"

I believe it has to do with the timing and interpretation of System Variables.

Using this expression to build a filename with _.log I had been using:

@[User::PathROOT]+"edw_logs\\"+ @[System::PackageName] + "_"+(DT_STR,4,1252) YEAR(GETDATE())+ RIGHT("00"+(DT_STR,2,1252) MONTH(GETDATE()),2)+RIGHT("00"+(DT_STR,2,1252) DAY(GETDATE()),2)+RIGHT("00"+(DT_STR,2,1252)DATEPART("Hour", @[System::StartTime]),2)  + RIGHT("00"+ (DT_STR,2,1252)DATEPART("Minute", @[System::StartTime]) ,2) + RIGHT("00"+(DT_STR,2,1252)DATEPART("Second", @[System::StartTime]),2) +".log"

and two files were created once on validation and once when it ran.

Using just User variables, they are evaulated at runtime and only one file is created.  See this:

@[User::PathROOT]+"edw_logs\\"+ @[User::MyPackageName] + "_"+(DT_STR,4,1252) YEAR(GETDATE())+ RIGHT("00"+(DT_STR,2,1252) MONTH(GETDATE()),2)+RIGHT("00"+(DT_STR,2,1252) DAY(GETDATE()),2)+RIGHT("00"+(DT_STR,2,1252)DATEPART("Hour", GETDATE()),2)  + RIGHT("00"+ (DT_STR,2,1252)DATEPART("Minute", GETDATE()) ,2) + RIGHT("00"+(DT_STR,2,1252)DATEPART("Second", GETDATE()),2) +".log"


invalid record length at 1A2/8F240298: wanted 24, got 0 Due to power failure, my server running Postgres database got crashed.

Now when I am trying to start the database, I am getting the below error.

< 2020-01-04 11:40:39.409 CET > LOG:  database system shutdown was interrupted; last known up at 2020-01-04 11:39:52 CET

< 2020-01-04 11:40:39.655 CET > LOG:  database system was not properly shut down; automatic recovery in progress

< 2020-01-04 11:40:39.657 CET > LOG:  redo starts at 1A2/8F237FF8

< 2020-01-04 11:40:39.658 CET > LOG:  invalid record length at 1A2/8F240298: wanted 24, got 0

< 2020-01-04 11:40:39.658 CET > LOG:  redo done at 1A2/8F240270

< 2020-01-04 11:40:39.658 CET > LOG:  last completed transaction was at log time 2020-01-03 07:21:31.432701+01

< 2020-01-04 11:40:39.685 CET > FATAL:  could not fsync file "pg_tblspc/16387/PG_9.6_201608131/48151/51110": Input/output error

< 2020-01-04 11:40:39.687 CET > LOG:  startup process (PID 33321) exited with exit code 1

< 2020-01-04 11:40:39.687 CET > LOG:  aborting startup due to startup process failure

< 2020-01-04 11:40:39.709 CET > LOG:  database system is shut down

is there any way to get it running.

You are looking at the wrong message.  The "invalid record length" is an ordinary way for WAL to end.  It does not necessarily indicate that there is a problem.  The  "FATAL: could not fsync" however definitely should not be happening.  You have a hardware problem, or a corrupt file system, and those need to be dealt with outside the database.

It is possible you can fix it with something like fsck, and then repeat the automatic recovery.  But you can never really be sure you don't already have permanent corruption that crash recovery can't fix.  So your best bet may be to restore from a backup, if you have one, onto different hardware.


Date conversion and integer truncation during import I am doing some data file imports using:
LOAD DATA
INFILE 'apa.txt'
INTO TABLE Test_Stock_Daily
FIELDS TERMINATED BY ','
(symbol, date, open, high, low, close, volume);

The date in the file is formatted mm/dd/yyyy. I need it to be yyyymmdd in the database.
The volume field needs to be truncated by two places so 123456 becomes 1234.
What are the most SQL-centric ways of doing this?
Table definition

I suggest that this is a bit cleaner than Vérace's answer:

LOAD DATA
    INFILE 'apa.txt'
    INTO TABLE Test_Stock_Daily
    FIELDS TERMINATED BY ','
    (symbol, @date, open, high, low, close, volume)
    SET date = STR_TO_DATE(@date, '%m/%d/%y');

(And yes, date is a poor choice for a column name.)


Merging one-to-many and one-to-one relation in the same JSON object in postgresql LINK TO FIDDLE: https://www.db-fiddle.com/f/a6FXTqJHpU9smuJTUKqwuV/0

My tables are as follows:

=> \d ops;
                                                   Table "public.ops"
          Column           |           Type           | Collation | Nullable |                      Default
---------------------------+--------------------------+-----------+----------+----------------------------------------------------
 op_id                     | integer                  |           | not null | nextval('ops_op_id_seq'::regclass)
 name                      | text                     |           |          |
Indexes:
    "ops_pkey" PRIMARY KEY, btree (op_id)

=> \d events;
                                                   Table "public.events"
         Column         |         Type         | Collation | Nullable |                        Default
------------------------+----------------------+-----------+----------+--------------------------------------------------------
 event_id               | integer              |           | not null | nextval('events_event_id_seq'::regclass)
 desc                   | text                 |           |          |
 op_id                  | integer              |           |          |
Indexes:
    "events_pkey" PRIMARY KEY, btree (event_id)

=> \d op_extra_info;
                                   Table "public.op_extra_info"
      Column       |  Type   | Collation | Nullable |                      Default
-------------------+---------+-----------+----------+----------------------------------------------------
 op_extra_info_id  | integer |           | not null | nextval('op_extra_info_op_extra_info_id_seq'::regclass)
 op_id             | integer |           |          |
 extra_info1       | text    |           |          |
 extra_info2       | text    |           |          |
Indexes:
    "op_extra_info_pkey" PRIMARY KEY, btree (op_extra_info_id)

For each op, there can be many events (each event refers to its corresponding op by means of the "op_id" field) but only a single extra info (again, using "op_id" to refer to the op it's related to). My goal is to return a JSON result as follows:

{ "name": ....,
  "extra_info": { "extra_info1": ....., "extra_info2": ..... },
  "events": [ { "desc": .... }, { "desc": .... }, ... ]
}

I've been able to get partway through with this query:

SELECT to_json(x) FROM (
  SELECT ops.name, array_agg(events.descr) as events
    FROM ops
    LEFT JOIN (select event_id, op_id, descr FROM events) events USING(op_id)
    GROUP BY ops.name) AS x;

But I can't find a way to:

get a real object for each element of the "events" array (ie, a real { "descr": .... } object instead of just the value of the "descr" column)
add the extra_info data as sub-object of the "extra_info" key at the top level.

See the composite types documentation for how to handle entire rows as a single value (which ends up as an object in JSON).

Apparently, the only way to get the correct field names for the extra info is to create a new type for that subobject:

CREATE TYPE extra_info_object AS (
  extra_info1 TEXT,
  extra_info2 TEXT
);

SELECT to_json(data)
FROM (SELECT name,
             ROW(extra_info1,
                 extra_info2)::extra_info_object AS extra_info,
             (SELECT array_agg(events)
              FROM events
              WHERE events.op_id = ops.op_id
             ) AS events
      FROM ops
      LEFT JOIN op_extra_info USING (op_id)
) AS data;


Scheduled tasks in Oracle 11g I have a scheduled task in Oracle 11g scheduled to run at 12:00 and 17:00 each day. If the first run takes more than five hours, what would happen to the second scheduled run? Would it simply be dropped or is it postponed until after the first is complete? If it is possible to do either, how can I check what is used in my DB?

I've seen the task run unscheduled a while after the first completes, but I'm not sure if there are other people starting it manually or if something else is affecting it.


Write differences between varchar and nvarchar Currently in our SQL Server 2012 database, we're using varchar, and we'd like to change that nvarchar. I've generated a script to do that.

My question is are there any differences in how SQL Server writes to varchar columns vs. nvarchar columns? We have a number of backend procedures that I'm concerned about.

Edit:
Not sure if this helps, but the columns don't have indexes, f/k, or constraints on them.

You need to be sure that you prefix Unicode string literals with an N prefix. For example these will work differently if the underlying data type is NVARCHAR:

CREATE TABLE dbo.t(c NVARCHAR(32));

INSERT dbo.t(c) SELECT 'រៀន';
INSERT dbo.t(c) SELECT 'នរៀ';
INSERT dbo.t(c) SELECT N'រៀន';

SELECT c FROM dbo.t;

SELECT c FROM dbo.t WHERE c = 'រៀន';
SELECT c FROM dbo.t WHERE c = N'រៀន';

Results:

c
----
??? -- not stored correctly
??? -- not stored correctly
រៀន -- stored correctly!

c
----
???
??? -- probably not expected, however all Unicode characters have been changed to ?

c
----
រៀន

For those on mobile devices or decrepit browsers that show box characters instead of actual Unicode characters, this is what it looks like:


That's typical query, that can be implemented with EXISTS() function:

SELECT
    *                           -- get fields
FROM
    users as u                  -- from users
WHERE
    NOT EXISTS(                 -- where there is no
        SELECT
            TRUE
        FROM
            owns as o           -- owns
        WHERE
            (o.userId = u.id)   -- with this user
            AND
            (o.itemId <> 1)     -- and such item
    );

For multiple items check you can use IN() in condition check of correlated subquery:

SELECT
    *                             -- get fields
FROM
    users as u                    -- from users
WHERE
    NOT EXISTS(                   -- where there is no
        SELECT
            TRUE
        FROM
            owns as o             -- owns
        WHERE
            (o.userId = u.id)     -- with this user
            AND
            (o.itemId IN (1, 3))  -- and items from subset
    );


Oracle: procedures+functions code base not overlapping package body code base I intend to find all code in my database containing some string.
I run a query such as:

SELECT DISTINCT name, type FROM all_source WHERE UPPER(text) LIKE UPPER(‘%string%’);

and a lot of results, let's say 100 results, contain 'PACKAGE BODY' on the TYPE column. Because
a package body is too large, I want to get only the name of the procedure/function containing that text.

So I add the following clause

AND TYPE IN ('PROCEDURE', 'FUNCTION')

now i get only a few results, let's say 10 results, and I am confused.

Since a package body can contain only functions and procedures (apart from package constants), if 100 packages contain the text 'abc', how can only 10 procedures contain the same text? Text 'abc' withing a package can only fit within a procedure or a function, so if there are 100 occurences withing packages, there must be at least 100 occurences within functions/procedures.

I would need some explanation of this phenomena and, more importantly,I would like to know how can I narrow the search of text, to get the  name of the containing procedure/function, instead of the parent package?

(a colleague suggested that some procedures are only imported, but not compiled; could this be a cause?)

Your search will have to be more complicated.  Stored code can be in the package header which is not done that often.  Most often it is in PACKAGE BODY.  If you modified your query to read

SELECT DISTINCT name, type FROM all_source WHERE UPPER(text)
LIKE UPPER(‘%string%’) and type ='PACKAGE BODY';

This gives all the code that meets your criteria in package bodies.  The line number is all you can get out of all_source.  The fields available in all_source are

OWNER VARCHAR2(30)     Owner of the object
NAME  VARCHAR2(30)     Name of the object
TYPE  VARCHAR2(12)     Type of object: FUNCTION, JAVA SOURCE, PACKAGE,PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY
LINE  NUMBER           Line number of this line of source
TEXT  VARCHAR2(4000)   Text source of the stored object

When you search for type PROCEDURE or FUNCTION you are looking at the stand alone code blocks that are not part of a package.

If the style of coding includes local procedures or functions within procedures or functions in a package body this could get complicated fast. You could look for the mandatory syntax that precedes a code block:  PROCEDURE  xxx or FUNCTION xx is and the code that ends it END ;

So your search would be:

find all procedure or function names in a package or package body and the line number
find the line number of the text you are looking for
write some logic to find the name of the code block that contains the text

Or you could use a GUI tool like TOAD or SQL Developer which will do this for you.


Grant privilege to see column names in null constraint violations when inserting in views My database has two schemas, one with the actual tables and one that is used by an application. The application schema doesn't have any privileges on the actual tables. Instead there is a view for each table that limits the rows the application schema sees (but not the columns, the view is created as SELECT * FROM ...). For all those views SELECT, INSERT, UPDATE and DELETE was granted to the application schema.

If the application user does an insert into the view and violates a not null constraint, no column name is given. The actual error message is

  ORA-01400: cannot insert NULL into (???).

I see that this makes sense from a security standpoint, but I don't care if the application user can see the name of the column of the actual table since it's always the same as the column name in the view. If it isn't possible otherwise, it would even be OK for me if the application schema sees the table name. Is there some privilege that can be granted that allows seeing the column name without allowing any actual queries on the table?

It seems that granting the privilege references allows the user to see the column names:

grant references on the_table to app_user;

The grant needs to be done for the base table not for the view.


Cleanup Error Messages in SQL Server ERRORLOG The SQL Server's ERRORLOG file has a lot of messages like the following in AG in publisher (primary) as you know that distributor is out of AG Server. Subscriber is in another AG setup.

 Logon       Login failed for user 'domain\svc-prod'. Reason: Failed to open the explicitly specified database 'DB'. [CLIENT: xx.xxx.xxx.xxx]
 Logon       Error: 18456, Severity: 14, State: 38.

This error is because the replication server thinks there are either Publication on production SQL Server and I need to  clean up these publisher/subscriber items and error messages.

These messages are related to Replicated DB that are hosted in AG, these messages are occurring continuously and I wanted to stop them from occurring. Somewhere I need to map the above mentioned user login to the DB but I am not sure how to do that to stop these messages.

I'm not sure I understand your motivation behind wanting to Cleanup Error Messages in SQL Server ERRORLOG.  You can certainly close the current file and create a new by either:

Restarting SQL Server

OR

Executing sp_cycle_errorlog

Keep in mind that the SQL Server error log is a file that is full of messages generated by SQL Server. By default this tells you when log backups occurred, other informational events, and even contains pieces and parts of stack dumps. In short, it’s a treasure trove of information. When SQL Server is in trouble, it’s nice to have this available as a source of information during troubleshooting.

SQL Server keeps up to 6 error log files around by default. You can easily change this. Open up your copy of SSMS and:

Expand the “Management” folder.
Right click on “SQL Server Logs”
Select “Configure”
Check the box “Limit the number of error log files before they are
recycled”
Pick some value to put in the “Maximum number of error log failes”
box
Click “OK”

Check out Forgotten Maintenance – Cycling the SQL Server Error Log for more information about configuring the SQL Server ERRORLOG


How to setup authentication for connections on MongoDB? How to configure authentication for MongoDB when using mongo Shell or any other sort of client?

Here is a complete solution, including creation of a "super user", capable to access any resources and perform any operation.

Notice

First: the first access to MongoDB is made without authentication ($ mongo).
Second: the configuration file of MongoDB may differ, depending on which version are you using (3.x: mongod.conf / 2.x: mongodb.conf)

Creating User

> use admin
switched to db admin
> db.createUser( {user:"mongo", pwd:"mongo", roles:["root"]} )
Successfully added user: { "user" : "mongo", "roles" : [ "root" ] }

Verifying User Creation and Authentication

> db.getUser("mongo")
{
    "_id" : "admin.mongo",
    "user" : "mongo",
    "db" : "admin",
    "roles" : [
        {
            "role" : "root",
            "db" : "admin"
        }
    ]
}

> db.auth("mongo","mongo")
1

Enabling Authentication

check which config file is using MongoDB

$ ps aux | grep mongo
mongodb  12583  0.5  3.4 2381948 281140 ?  Sl  18:34  0:06 /usr/bin/mongod --config /etc/mongod.conf

MongoDB 3.x

$ grep -A 1 security /etc/mongod.conf
security:
   authorization: enabled

MongoDB 2.x

$ grep -B 2 auth=True /etc/mongodb.conf
# Turn on/off security.  Off is currently the default
#noauth = true
auth=True

Restart MongoDB (SysV script)

$ /etc/init.d/mongodb restart

Connecting via Mongo Shell

$ mongo -u mongo -p --authenticationDatabase admin
MongoDB shell version v3.4.3
Enter password: **********
connecting to: mongodb://127.0.0.1:27017
MongoDB server version: 3.4.3
> show dbs
admin     0.078GB
android   0.078GB
blog      0.078GB
games     0.078GB
school    0.078GB

Notice: you can just hit mongo and open the Mongo Shell, but it will not let you perform any action, due to the fact that you were not previously authenticated.

$ mongo
MongoDB shell version v3.4.3
connecting to: mongodb://127.0.0.1:27017
MongoDB server version: 3.4.3
> show dbs
2017-10-03T19:04:55.953+0000 E QUERY    [thread1] Error: listDatabases failed:{
"ok" : 0,
"errmsg" : "not authorized on admin to execute command { listDatabases: 1.0 }",
"code" : 13,
"codeName" : "Unauthorized"
} :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
Mongo.prototype.getDBs@src/mongo/shell/mongo.js:62:1
shellHelper.show@src/mongo/shell/utils.js:761:19
shellHelper@src/mongo/shell/utils.js:651:15
@(shellhelp2):1:1
>

For dropping a user:

> use admin
> db.dropUser("myuser")

References:

https://docs.mongodb.com/manual/tutorial/enable-authentication/
https://docs.mongodb.com/manual/reference/configuration-options/
https://docs.mongodb.com/manual/core/security-built-in-roles/


Record MultiSubnetFailover value at connection? With AlwaysOn, there are 3 options for the keyword MultiSubnetFailover

True (yes)
False (no) (example)
Not sent/used

Not all applications support it's use in connection strings. Not all vendor contacts understand it's importance to performance at failover.

As a DBA I would like to be able to validate what property is being used by the application connection.  If I have not made the appropriate setting change to RegisterAllProvidersIP it can cause issues.  See: Follow-up: After Creating an Availability Group Listener

It does not appear to be capture in either sys.dm_exec_sessions nor sys.dm_exec_connections

How can I capture what (if any) keyword values were used to create a connection to one of my instances?

I don't believe this configuration setting is sent to the server.  It controls the client's interpretation of a DNS lookup returning multiple IP addresses, and there is no functional need for the server to be informed about it.  And the client doesn't send arbitrary name/value pairs to the server.  The fields that a client sends to the server are dictated by the TDS protocol, and it would require a protocol revision to add new ones.

Also while properly setting MultSubnetFailover at the client continues to be a best-practice, newer SQL Server client libraries have changed their behavior to mitigate the issues arising from not setting MultiSubnetFailover.  See Using Transparent Network IP Resolution.  Newer clients will have only a brief delay in connecting when the first IP returned from DNS is not the active AG Listener, instead of the old behavior of waiting 20-30 seconds before attempting to connect to the second IP.


Cleaning disabled SQL agent jobs in SQL Server 2005 and above For many of our old servers, we have found that there are lot of SQL agent jobs which were either run as an ad-hoc for instant purpose and now no more needed OR Jobs which are not functional/working and replaced by some new jobs, are just sitting idle and occupying the unwanted attention on the job activity list:

Hence, I would like to know, should we be good to clean up all those disabled jobs?
(I am not sure if they are sitting idle there, what would be current or future impact)

If yes, what should be the safest way of cleaning them in one go, for cases where there is quite a big list of disabled jobs.

Only you can answer if the jobs can be dropped completely. If they are not jobs like from the import/export wizard I would probably script them out. This can be done easily with PowerShell.

A quick way of dropping them is via Object Explorer details pane, it allows multiple selections and then just hit the delete key or right click and delete.


changing Virtual IP interface names on RAC nodes - how to reconfigure We are adding an extra interface on our nodes and we are going to bond these so the VIP interface will change from eth5 to bond3.

Can someone point on documentation on what to do to reconfigure the interface name in a RAC?

Or simply give a procedure to do so.

If the NIC has changed for the Public / private NIC:

Modify the Public NIC information like so:

$CRS_HOME/bin/oifcfg/oifcfg delif -global <if_name>[/<subnet>]
$CRS_HOME/bin/oifcfg/oifcfg setif -global <if_name>/<subnet>:public
For example:
% $CRS_HOME/bin/oifcfg delif -global eth0/10.2.156.0
% $CRS_HOME/bin/oifcfg setif -global eth0/10.2.166.0:public

Then make the change at OS layer.

It can be done one node at a time so there's no down time unless the OS requires it.

Once the public network is changed, the VIP needs to be modified like so:

View the VIP information:

$ srvctl config nodeapps -n racnode1 -a
VIP exists.: /racnode1-vip/101.17.80.184/255.255.254.0/eth1

Stop the nodeapps resources

$ srvctl stop instance -d <db_name> -i <inst_name>   (optional for 10.2.0.3+)
$ srvctl stop asm -n <node_name>                     (optional for 10.2.0.3+)
$ srvctl stop nodeapps -n <node_name>

eg,
$ srvctl stop instance -d RACDB -i RACDB1
$ srvctl stop asm -n racnode1
$ srvctl stop nodeapps -n racnode1

Verify that the VIP is offline:

$ crs_stat -t

Modify VIP like so:

# srvctl modify nodeapps -n <node> -A <new_vip_address or new_vip_hostname>/<netmask>/<[if1[if2...]]>

eg:
# srvctl modify nodeapps -n racnode1 -A racnode1-nvip/255.255.255.0/eth2

Verify the change:

$ srvctl config nodeapps -n <node> -a (10g and 11gR1)
$ srvctl config nodeapps -a (11gR2)

eg:
$ srvctl config nodeapps -n racnode1 -a
VIP exists.: /racnode1-nvip/110.11.70.11/255.255.255.0/eth2

Restart nodeapps:

$ srvctl start vip -n <node_name>
$ srvctl start listener -n <node_name>
$ srvctl start instance -d <db_name> -n <node_name>      (optional)

eg,
$ srvctl start vip -n racnode1
$ srvctl start listener -n racnode1
$ srvctl start instance -d RACDB -n racnode1

Verify the VIP is online

$ crs_stat -t (or $ crsctl stat res -t for 11gR2)


Exclusive access to edit rows I'm developing a program that allows users to view and edit data.
My question:
Is there tools (sql server tools, may be users/roles) to implement the following:
There are two users: A and B. User A opened the edit form for row, at this point the user B can not edit the same row.

I know, that i can use colunm for store UserId(user, which edit this row), but this solution is not suitable.

One approach would be to use optimistic locking.

Before displaying the update form, you retrieve all the original values from the database. Or, at a minimum, a column that is guaranteed to change on update, such as last_update_date or similar.

When updating you check that all values are still the same in your where clause. If the update fails to update any rows you know that the values have been changed in between and inform the user that this has happened.


Build a three table join with a recusive table in the middle? I have three relevant tables: Parts, PartGroup, and MarkupGroup.

Parts is simple.

PartID          artificial primary key
Part            part number
PartGroupID     Foreign key

sample data:

1   T1000           5
2   wizbang gold    17
3   flux capacitor  2

PartGroup is an Acyclic Directed Graph(tree) modeled with a self-linked parent key

PartGroupID     artificial primary key
Description     name of group
ParentID        foreign key linked to PartGroupID
MarkupGroupID   foreign key linked to MarkupGroup

Sample data would look like this-

1   system      null    null
2   component   null    1
3   software    null    2
4   abc         1       3
5   xyz         1       4
6   123         4       null
7   456         4       null
8   789         5       null
9   a1          6       null
10  b2          6       null
11  c3          7       null
12  d4          7       null
13  e5          8       null
14  f6          8       null
15  alpha       3       null
16  beta        3       null
17  gamma       3       null

MarkupGroup is to apply a markup factor to several PartGroups as a set.

MarkupGroupID   primary key
MarkupFactor    numeric attribute field

sample data-

1   15
2   20
3   25
4   22

I need to write a query that returns the appropriate markup amount for each part in the parts table. There is never a case when more then one markup can apply to a part. I need to recurse up the graph to find the markup, and I won't know at query time how many levels I need to recurse to find a non-null markup.

There is never a case when a part will encounter more than one possible join to a margin on the way up the tree, so there's no accumulation that needs to be done.

The t1000 is of type xyz, which has a foreign key to a MarginGroup, so we can join and get a value. It also has a parent, but that's irrelevant since we have the value we need.

Wizbang Gold is gamma-group software, which has no foreign key, but the parent node 'software' does and we should return that.

A flux capacitor is a component, and it has a direct foreign key to a MarginGroup. Return that value.

So the results would be:

1   T1000           22
2   wizbang gold    20
3   flux capacitor  15

I'm pretty sure I'm going to need a recursive CTE and some flavor of APPLY to get this, but my brain is not working very well at the moment. If not for the recursive nature of the middle table this would be pretty straightforward. Platform is MS-SQL.

You can do the recursion in a CTE from the top down carrying MarkupGroupID with you.

with C as
(
  select P.PartGroupID,
         P.ParentID,
         P.MarkupGroupID
  from PartGroup as P
  where P.ParentID is null
  union all
  select P.PartGroupID,
         P.ParentID,
         coalesce(P.MarkupGroupID, C.MarkupGroupID)
  from PartGroup as P
    inner join C
      on P.ParentID = C.PartGroupID
)
select P.PartID,
       P.Part,
       MG.MarkupFactor
from Parts as P
  inner join C
    on P.PartGroupID = C.PartGroupID
  inner join MarkupGroup as MG
    on C.MarkupGroupID = MG.MarkupGroupID
order by P.PartID

SQL Fiddle

The recursive CTE will create a derived table that looks like this.

PartGroupID ParentID    MarkupGroupID
----------- ----------- -------------
1           NULL        NULL
2           NULL        1
3           NULL        2
15          3           2
16          3           2
17          3           2
4           1           3
5           1           4
8           5           4
13          8           4
14          8           4
6           4           3
7           4           3
11          7           3
12          7           3
9           6           3
10          6           3

If you need parts with partgroup not connected to a markup you can use a outer join against MarkupGroup in your main query.

with C as
(
  select ...
)
select P.PartID,
       P.Part,
       MG.MarkupFactor
from Parts as P
  inner join C
    on P.PartGroupID = C.PartGroupID
  left outer join MarkupGroup as MG
    on C.MarkupGroupID = MG.MarkupGroupID
order by P.PartID


MySQL server wont' start. Reason: Internal error The very vague error message

Reason: Internal error

I've had this happen before, and it usually fixes itself after I restart my machine, but after the 4th time now it still is showing the error :(

Update: Found my.cnf file, details below:

port = 3306
socket = /tmp/mysql.sock

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 128M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
thread_concurrency = 4
innodb = on
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:512M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 256M
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
"my.cnf" [readonly] 67L, 1336C
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
default-storage-engine = innodb
datadir=/var/lib/mysql
user = mysql
symbolic-links = 0
transaction-isolation = READ-COMMITTED
max_length_for_sort_data = 8388608
max_sort_length = 8388608
group_concat_max_len = 32M

net_buffer_length = 1M
max_connections = 32768
back_log = 32768

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open-files-limit = 8192

[mysqldump]
quick
max_allowed_packet = 32M

[mysql]
#no-auto-rehash
database = whoat
#safe-updates
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Any idea what is going on? Here is the server log

I was able to start SQL from MySQL Workbench, would like to know how I breached the max open file limit however

This has happened to me before and while there's nothing in the logs to really point you in the right direction the first place to start is in the MySQL's configuration file (On a Mac, I believe it is called my.cnf and it is found in /etc).

You may have made some adjustments or tweaks that it doesn't agree with. Going a bit overboard with certain values (max_open_files for example you're requesting 33802 out of the maximum of 8192, this could be the culprit) can lead to this scenario where MySQL just doesn't want to kick off.

Take a look inside your conf file and try editing the values to be your defaults. You may be able to find example files here: /usr/local/mysql/support-files/

Once you fix it and wish to tweak, just tweak one at a time and start MySQL to confirm it can run with that desired change. It's a little more time consuming but it helps avoid scenarios like this in the future.


logged on as sysdba, how to run dml script as another user if you do not have the passwrod logged on as sysdba to an oracle database, how to run dml script as another user (Joebloggs) if you do not have the password. The scripts will run dml that updates objects in (jobbloggs) schema.

If you really need to log in as that user, use a proxy user:

SQL> conn / as sysdba
Connected.
SQL> select count(*) from dba_users where username = 'UNKNOWN_PASSWORD';

  COUNT(*)
----------
         1

SQL> conn UNKNOWN_PASSWORD/UNKNOWN_PASSWORD
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> create user proxy_user identified by proxy_password;

User created.

SQL> alter user UNKNOWN_PASSWORD grant connect through proxy_user;

User altered.

SQL> conn proxy_user[UNKNOWN_PASSWORD]/proxy_password
Connected.
SQL> show user
USER is "UNKNOWN_PASSWORD"
SQL>

Otherwise, you can run DML on objects in other schemas.


How to find function definition How can I find the definitions of below system functions in SQL Server:

has_access
permission_name

I tried with DAC connection but getting below error:

Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 54 [Batch Start Line 118]
The object 'has_access' does not exist in database 'master' or is invalid for this operation.

Please let me know if any further details are required. Thanks!

These two functions appear to be internal / built-in functions given that:

they are referenced without a schema-name
SSMS color-codes them the same as all other built-in functions

sys.server_permissions, on Line #7, has:

        p.type, permission_name(p.class, p.type) AS permission_name,

sys.server_principals, on Line # 18, has:

    WHERE has_access('LG', p.id) = 1

When the instance is in single-user mode, I can execute the following:

USE [mssqlsystemresource];
SELECT PERMISSION_NAME(100, 'COSQ');
-- CONNECT SQL

The same statement does not work outside of mssqlsystemresource, nor does it work in that DB when prefixed with sys. or dbo..

Ergo, no, there is no definition that you will be able to get.


Oracle integer to number conversion According to the Oracle documentation, creating a table with a column of ANSI type INT, Oracle will convert it to NUMBER(38).  According to the same document, NUMBER(38) is supposed to be a NUMBER with a precision of 38 and a scale of 0. But in practice, I am seeing a NUMBER with data_precision set to NULL and a data_length set to 22.

SQL> create table ltheisen.silly ( id int );

Table created.

SQL> select substr(data_type,1,10), data_length, data_scale, data_precision from
 all_tab_columns where owner='LTHEISEN' and table_name='SILLY' and column_name='
ID';

SUBSTR(DATA_TYPE,1,10)                   DATA_LENGTH DATA_SCALE DATA_PRECISION
---------------------------------------- ----------- ---------- --------------
NUMBER                                            22          0

What gives?

Also, I am trying to do a generic conversion from Oracle to ANSI.  Is there any way to determine from the metadata that the NUMBER was created as, or fits into an INT?

Lastly, I tried creating with BINARY_INTEGER which appears in the documentation, but the create statement fails:

SQL> create table ltheisen.silly ( id binary_integer );
create table ltheisen.silly ( id binary_integer )
                                 *
ERROR at line 1:
ORA-00902: invalid datatype

What am I missing?

-------------------- UPDATE -----------------------------

Reading a little deeper gives this gem:

  Specify a floating-point number using the following form:

  NUMBER

The absence of precision and scale designators specifies the maximum range and precision
for an Oracle number.

So since the datatype is NUMBER and there is no precision specified, we can assume it is maximum precision?

A DATA_LENGTH of 22 is just the largest number of bytes it can take to store the largest possible number (Oracle uses 2 digits per byte). A NULL DATA_PRECISION implies the maximum of 38.

You can verify this with DBMS_METADATA:

SQL>  create table sotmp ( a int );

Table created.

SQL> select dbms_metadata.get_ddl('TABLE','SOTMP')
  2  from dual;

DBMS_METADATA.GET_DDL('TABLE','SOTMP')
--------------------------------------------------------------------------------

  CREATE TABLE "PHIL"."SOTMP"
   (    "A" NUMBER(*,0)
   ) SEGMENT CREATION DEFER

SQL>

(The "*" in the above output denotes the maximum precision).

BINARY_INTEGER is not a data type that can be used in table columns. It can only be used as a PL/SQL variable datatype.


Access tables from multiple schemas I have two schemes with multiple tables. The queries in packages heavily reference schema names. Is it possible to remove schema names from the queries and oracle to identify the tables based on roles or privileges??

Query -

Select * from schema1.app_table1 ap1 inner join schema2.app_table2 ap2

The query would be run from oracle user app_user who has access to both the tables - app_table1 and app_table2.

It is possible to run the query without schema prefixes or synonyms just by modifying the user app_user.

Thanks for your help.

If possible. What you need to do is create public synonyms for the tables, grant GRANT SELECT to the user who performs the query.

CREATE PUBLIC SYNONYM app_table1 FOR schema1.app_table1;

CREATE PUBLIC SYNONYM app_table2 FOR schema2.app_table2;

GRANT SELECT ON app_table1 TO app_user;

GRANT SELECT ON app_table2 TO app_user;

Your query will be like this:

Select * from app_table1 ap1 inner join app_table2 ap2