Using a trigger function to constrain values according to those already in the table (postgresql) I'm just learning SQL and I can't seem to find out how to do this.

The table is (id, subject, year, grade). I'm supposed to reject any inserts or updates for a student if he has scored an F before for the same subject i.e. students are not allowed to retake a subject if they have failed in it before.

I'm supposed to use a trigger function but I'm confused about how to get started.
A nudge to the right direction would be appreciated.

With an Exclusion Constraint and a UNIQUE Partial Index

So long as you don't care about people that pass retaking and later failing you can easily do this without even using a trigger using a exclusion-constraint. It's a bit clever, but it's pretty efficient.

CREATE TABLE foo (
  id         serial    PRIMARY KEY,
  student_id int,
  subject    text,
  year       smallint,
  grade      "char"    NOT NULL
);

CREATE UNIQUE INDEX no_double_fail
  ON foo ( student_id, subject ) WHERE upper(grade)='F';

ALTER TABLE foo
  ADD CONSTRAINT fail_once_fail_always
  EXCLUDE USING gist (
    student_id WITH =,
    (CASE WHEN upper(grade) = 'F' THEN 0::smallint ELSE 1::smallint END) WITH <>
  );

Test suite.

-- fails error msg below
INSERT INTO foo (student_id,subject,year,grade) VALUES
  (1,'Art',2000,'F'),
  (1,'Art',2001,'A');

ERROR:  conflicting key value violates exclusion constraint "fail_once_fail_always"
DETAIL:  Key (student_id, (
CASE
    WHEN upper(grade::text) = 'F'::text THEN 0::smallint
    ELSE 1::smallint
END))=(1, 1) conflicts with existing key (student_id, (
CASE
    WHEN upper(grade::text) = 'F'::text THEN 0::smallint
    ELSE 1::smallint
END))=(1, 0).

-- works
INSERT INTO foo (student_id,subject,year,grade) VALUES
  (2,'Math',2000,'B'),
  (2,'Math',2001,'A');

-- fails error msg below
INSERT INTO foo (student_id,subject,year,grade) VALUES
  (3,'Science',2000,'F'),
  (3,'Science',2001,'F');

ERROR:  duplicate key value violates unique constraint "no_double_fail"
DETAIL:  Key (student_id, subject)=(3, Science) already exists.

If you care about it, to compliment the condition to protect against people that pass retaking and later failing you'll need the ability to do a non-commutative exclusion constraint. I'm not sure that's even possible. This may force you to use a trigger.


Is there an advantage to moving to 'real' date partitions on MySQL 5.5? We are using partitions per day for some our tables. We started with MySQL 5.1, so we had to partition using the to_days (end_time is a datetime column):

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!50100 PARTITION BY RANGE ( to_days(end_time))
(PARTITION p20130521 VALUES LESS THAN (735375) ENGINE = InnoDB,
....
PARTITION p20130603 VALUES LESS THAN (735388) ENGINE = InnoDB,
PARTITION p20130604 VALUES LESS THAN (735389) ENGINE = InnoDB,
PARTITION p20130605 VALUES LESS THAN (735390) ENGINE = InnoDB,
PARTITION p20130606 VALUES LESS THAN (735391) ENGINE = InnoDB,
PARTITION p99991230 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

Starting with MySQL 5.5, it is now possible to avoid the to_days and partition by real dates. Is there any (speed) advantage to doing so?

Using to_days to Partition
The to_days function yields a 6 digit number in the 700,000's (in the MEDUIMINT range).
Using DATE or DATETIME to Partition
DATE takes up 3 bytes. DATETIME takes up 8 bytes.
Conjecture
UNIX_TIMESTAMP of today's date exceeds 1.37 billion. It takes 4 bytes to store that. Range partitioning allows for range values that high. Here is an example of that from the MySQL Documentation:

It is also possible to partition a table by RANGE, based on the value
of a TIMESTAMP column, using the UNIX_TIMESTAMP() function, as shown
in this example:
CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

Partitions seem to be numerically represented as a 4-byte unsigned integer. As mentioned, DATE takes up 3 bytes, but, from what I have observed, partitions are bounded by 4-byte numbers. Given this, expressing a DATE as a RANGE parameter really does not buy you anything except cleaner syntax for defining the range.
Expressing to_days of a DATE yourself or having mysql do it for you boils down to personal choice.


does SELECT with implicit transaction prevent VersionStore cleanup? The JDBC driver usually sets the implicit transaction on in every query executed by the client.
I found many SELECT statements in suspended state with implicit transaction set to ON using Version Store for 48 hour.
These selects statements didn't take up much tempdb space, but I was wondering if this might have blocked version cleanup even though the UPDATE / DELETE / INSERT statements that generated the version store rows had already been committed. (many databases use read committed snapshot isolation level)
After I killed the sessions the version store space became free.
I found the select statement using the version store with the following query:
SELECT
       db_name(spu.database_id) as database_name,
       ast.session_id,
       ast.transaction_id,
       ast.transaction_sequence_num,
       ast.elapsed_time_seconds,
       b.program_name,
       b.open_tran,
       b.status,
       ses.row_count,
       (spu.user_objects_alloc_page_count * 8) AS user_objects_kb,
       (spu.user_objects_dealloc_page_count * 8) AS user_objects_deallocated_kb,
       (spu.internal_objects_alloc_page_count * 8) AS internal_objects_kb,
       (spu.internal_objects_dealloc_page_count * 8) AS internal_objects_deallocated_kb,
       loginame,
       last_request_start_time,
       last_request_end_time,
       cmd,
       lastwaittype
   FROM sys.dm_tran_active_snapshot_database_transactions ast
      JOIN sys.dm_exec_sessions ses ON ses.session_id = ast.session_id
      JOIN sys.dm_db_session_space_usage spu ON spu.session_id = ses.session_id
      JOIN sys.sysprocesses b on ast.session_id = b.spid
   ORDER BY elapsed_time_seconds DESC

I found the answer in this video:
Monitoring Snapshot Isolation with Perfmon in SQL Server
And yes SELECT statements with open transaction can block version store cleanup.


DSV - SSAS Project - New 2 Columns In Table Change More then I Planned I encountered a very weird thing.
I added in my DWH table 2 new columns but when I refreshed the DSV after adding this to my multidimensional project I saw that more (3) columns have been "changed".
I did not touch these 3 columns(Marked in red ) they were already in my database before and have been handled .

Someone have an idea why this happened? Can I know what "changed"?
Also you can see each "changed column appears twice.

Here Is the PrintScreen After I refresh the DSV.
Marked In blue - the columns I added
Marked In red - the Columns I did not touch and was existing in the Table.

I have no idea why the columns changed appear twice and neither does google apparently.

With regards to the changes on the columns on the other hand your best option would be to compare the data types and table definitions on the .dsv.

Compare your new dsv to an old version of your dsv and have a look at all these datasource view properties

Certainly something has changed, maybe not necessarily in the datasource, but in what has been detected. It could be minor (correcter guessing of length for example) or a real problem, so I suggest you review the changes anytime this happens.

You could have a go at diffing the .dsv xml structure instead of reviewing each property. That sometimes looks messy but you could have some luck with BIDSHelper's SmartDiff's feature.


Should we enable innodb_file_per_table for single tenant SaaS app (thousands of databases) We have a mySQL backed SaaS application where each customer has their own mySQL database. So think of it like a wordpress/magento etc. database for each client.

Each database has ~200 table, and we expect to have about 1,000 databases per server, so 200,000+ tables. We currently have innodb_file_per_table enabled.

Our question is regarding whether we should use innodb_file_per_table or not. We've seen some conflicting information on this:

Performance: With the large number of tables, is it better or worse to use innodb_file_per_table. We understand there can be issues with the number of file pointers required to have hundreds of thousands of tables open.
Stability: There is some discussion that using innodb_file_per_table is safer or improves crash recorvery
Concurrency: Some reports say that using innodb_file_per_table improves concurrency because there are individual tablespaces.
I presume nothing has come from innodb_file_per_database. Facebook talked about this back in 2009 (!) but I've not seen anything since. https://www.facebook.com/notes/mysql...ent_id=6285127

Welcome any thoughts on this.

Answering one by one:

Performance: yes, this is the main problem. Not only in resource for file descriptors, but also for the mysql open table cache and data dictionary, InnoDB checks on start and regenerating table statistics when opened. To be fair, these last ones (except InnoDB checks) will be found no matter the value of innodb_file_per_table, only because a huge number of tables.

The largest problem, however -in my opinion-, was that in the case of a DROP TABLE with innodb_file_per_table = 1 older versions of 5.5 required a full buffer pool lock, basically stalling the whole server. Some patches applied in the latest versions of MySQL 5.5 and 5.6, plus the buffer pool partitioning (available in 5.5 and enabled by default in 5.6) made that a minor issue.

On the bright side, you can in 5.6 set a manual path of every table, which may help in some cases, for example, to put the most problematic tables on a separate disk or an SSD, etc.
Stability: I wouldn't say that it is more stable, but it is true that if you have disk corruption and a single huge file, you can only pray. Having different files makes  easier to "save" all but one or a few. Not convinced that this is a decisive point -InnodB is well regarded about its disk consistency (assuming there is no physical damage).
Concurrency: This is true, but only in specific cases. Ext filesystems had a limitation that if you were using O_DIRECT mode, you couldn't write in concurrency to the same file descriptor. That is completely true, but is a very specific case: you could use a different filesystem (XFS is very recommended when handling many files and does not have that problem), you can use the filesystem cache if you have enough ram and in some cases the high concurrency is not a problem if writes go to different files at the same time (thus the innodb_file_per_table=1 or several ibdata files).
I cannot see your link, I suppose it is this? Yes, I agree 100% that one thing that it is missing in InnoDB is more control about the tablespace management (funnily enough, it is done for the disk tables in the memory-only engine NDB). I have not seen such a thing implemented. You are supposed to do all optimizations at RAID/LVM/physical level.

I think that you miss one thing that is the number 1 reason for having innodb_file_per_table = 1 - the flexibility for backups, restores, management and moving tables around inidividually  (something made even easier with the transportable tablespaces in MySQL 5.6). With it enabled, you will be able to recover disk space when tables are dropped and make defragmentation possible. When you have thousands of tables in a single file, physical backup and restore is impossible, and you have to use logical backups (mysqldump/LOAD DATA).

So what are the alternatives? I have found this problem before, and I have helped solving it on hosts with literally millions of tables in several ways:

If disk space is not a problem, you can have some tables in the common tablespace and some separately (innodb_file_per_table is a dynamic variable, although you need to rebuild the table for each one you want to move in or out). This solution is ok if your problem is having some tables that are very dynamic -being created and destroyed frequently- while other are static, but you may end up with a very large common tablespace. However, if using innodb_file_per_table = 0 works better with you, then end of discussion.
You can have several common tablespace files (ibdata1;ibdata2;ibdata3). This is ok if you want to separate them in several disks, or you do not want to deal with huge files, but it will not solve the "having many tables" problem.
Change your schema- For example, wordpress multisite installation puts many tables together, and if I remember correctly (I may be wrong), it only creates a new table per site, not a full database. If your tables are going to be very small, this is the way to go- modify the structure so that you have larger and less tables
Choose a different engine - Wordpress.com uses MyISAM at scale and they do not care about corruption because they have a good backup/recovery system. Maybe this is your case if most of your tables are read-only (or almost read-only) or you put a good caching system in-between.
What is the number 1 way to deal with this problem? What I have been doing on behalf of my clients most of the time is what Wordpress, Facebook and other do- sharding. Do not try to handle a million of users in a single powerful machine- use smaller ones and divide load (and databases and users) between several servers. You do not have to handle the sharding all by yourself, there are tools to help you do that, like the Upstream MySQL Fabric (for Java and Python) or the engine Spider.

Please note that if the bottleneck is not on CPU/disk/memory, you can even use virtual machines in order to consolidate services.

In general, innodb_file_per_table is something that you want ON, for the flexibility reasons that I told you before. If you cannot have it on (test it first, maybe you would be surprised! -avoid premature optimization), you can disable it for all or some of the tables (but the change will be more painful than anything else. But be prepared for the consequences. Try to avoid that with some of the recommendations that I told you at the end.

Update: 5.7 labs releases have announced that may finally become obsolete and we will finally have better tablespace management thanks to a CREATE TABLESPACE syntax (search for "InnoDB: General Tablespaces"). With this, you will be free to create your own "innodb_file_per_database" or any other schema you want.


Deploying UDFs/SPs across all DBs I'm an accidental DBA with no scripting knowledge/background whatsoever. We have a bunch of different DBs (each client has their own) that have the same structure, but different data. That said, we regularly create functions that everyone needs to use. This means that I have to deploy these to each and every one of our 120+ DBs. How do I do that without 'manually' deploying it to every DB?

I have asked around about this before, but nothing was really helpful. The answers I got were 'use scripting' (not viable because I don't even know how to start), 'use RedGate's tool' (don't have the money for it and the company won't buy it), and 'don't use scalar UDFs to begin with' (completely unhelpful).

I've also been told 'just put it in master, or some other DB and call out to that'. That's a good thought, but soon our permissions/logins will change and I don't think that one DB user will be able to call out to another DB (HIPPA and all that).

Can anyone provide some guidance for a 'DBA' of my level?

Thanks

UPDATE: I have found that one can use sp_MSforeachdb for DDL Statements. Clearly, this is not best practice as sp_MSforeachdb is undocumented and not 'supported' by MS. As in, use at your own risk. It worked like a charm for me.
See example 2: https://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/

For those that do not want to go that route and are in a similar boat than me, use Johnathan Fite's PS Script provided in the answers section. That's a handy script to have, especially if you have more than one server/instance.

Thanks everyone!

You can try out this as given by user Cade Roux in an answer to the Stack Overflow question Common function / stored procedures for all databases:

  Depending upon what the SP actually does, you want to create the procedure in master, name it with sp_ and mark it as a system procedure:

From Running a master db residing stored procedure in current database context by Mladen Prajdić:

USE master
go
CREATE PROC sp_TestMasterAccess
AS
SELECT    *
FROM    information_schema.tables
GO
-- SS2k5 provides a stored procedure to mark the object as system
EXEC sys.sp_MS_marksystemobject sp_TestMasterAccess
GO
EXEC sp_TestMasterAccess

go
USE AdventureWorks
EXEC sp_TestMasterAccess

go
USE master
DROP PROC sp_TestMasterAccess


Updating an indexed view with NOEXPAND I will preface this by saying that this is a cross post from an unanswered question on Stack Overflow.
I am not doing this simply to get more views on the question, I would like the DBA community to share their view on whether this might even be a bug in SQL Server. I don't think the SO community has the expertise to decide that, so I'm reposting here.

Suppose I have a table T, and I have an indexed view V on it:
CREATE TABLE dbo.T (id int PRIMARY KEY, b bit NOT NULL, txt varchar(20));
GO
CREATE VIEW dbo.V
WITH SCHEMABINDING AS
  SELECT T.Id, T.txt
  FROM dbo.T AS T
  WHERE T.b = 1;
GO
CREATE UNIQUE CLUSTERED INDEX idx_V ON dbo.V (Id);

In this trivial example it's basically just a filtered index, but it could also have joins and such like.
I would now like to select some rows in T where b = 1, the filtered view here is very useful, and I'm on Standard so have to use NOEXPAND (or it's too complex for view matching):
SELECT Id, txt
FROM V WITH (NOEXPAND);

This works nicely.
Now I want to update these rows to some value. The view qualifies as updatable so I can do this:
UPDATE V
SET txt = 'Foo';

This does not use the indexed view to find the rows to update, even though it needs them to actually update the view. What I would like it to do is use the view like a normal table index, and identify the rows to update from it, pass them to the Clustered Index Update on T, followed by an Update on the view. So I try this:
UPDATE V WITH (NOEXPAND)
SET txt = 'Foo';

This fails with "Hint 'noexpand' on object 'V' is invalid.".
I know I can get round it with a query such as this:
UPDATE T
SET txt = 'Foo'
FROM T
JOIN V WITH (NOEXPAND) ON V.Id = T.Id;

But this means an extra Seek. Not only that, it adds a Filter on the subsequent indexed view update to check the rows match the view (a joined view would require the joins to be evaluated) when clearly they must match the view.
Is there any way of getting this to work in the way I would like?
I believe there is an optimization called rowset-sharing which may make a difference here, does anyone know about this?

Update:
It does not help to put the view in a FROM clause or even in a derived table. As soon as it looks to the parser that it's being used for an update, it fails.
There is no indication that NOEXPAND should not work either in the Table Hints, Indexed View, or Updatable Views documentation. The docs for the UPDATE statement specifically mention that certain table hints are not allowed, but only NOLOCK and READUNCOMMITTED are excluded.

So, maybe this is even a bug in SQL Server, and I should file it on Azure Feedback. What do people think?

This works as you want on Enterprise/Developer Edition:
use tempdb
go

CREATE TABLE dbo.T (id int PRIMARY KEY, b bit NOT NULL, txt varchar(20));
GO
CREATE OR ALTER VIEW dbo.V
WITH SCHEMABINDING AS
  SELECT T.Id, T.txt
  FROM dbo.T AS T
  WHERE T.b = 1;

GO
CREATE UNIQUE CLUSTERED INDEX idx_V ON dbo.V (Id);
GO

UPDATE V
SET txt = 'Foo';

The update has this plan

running
Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64)
    Sep 23 2020 16:03:08
    Copyright (C) 2019 Microsoft Corporation
    Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19042: ) (Hypervisor)

You can add a feedback item here to request that NOEXPAND be supported on UPDATE queries.  I put in a PR to update the docs to clarify that NOEXPAND is not available on UPDATE.


full outer join where join key in result always has value I wish to do a full outer join between two tables A, B joining on A.ID = B.ID.
Now, it is quite easy to include A.ID and B.ID in a result set. this will include rows where:

A.ID is null, B.ID is not null
B.ID is null, A.ID is not null
A.ID and B.ID have the same value.

I want to have a third column which be equal to:

A.ID and B.ID when they are the same value
A.ID when A.ID has a value and B.ID is null
B.ID when B.ID has a value and A.ID is null

How do I do this?

Use this function:

COALESCE( a.id, b.id )


Help me convert SQL statement to MySQL I have an application called Moodle version 1.9 and was migrated from Windows platform and Microsoft SQL Server to Linux and MySQL.

Now, when I click save changes the SQL query statement from Custom SQL, it gives me "Query failed".

I would appreciate your help to advise how to fix the below SQL statement that I got from http://docs.moodle.org/22/en/ad-hoc_contributed_reports#All_Ungraded_Assignments_w.2F_Link

SELECT
    u.firstname AS "First",
    u.lastname AS "Last",
    c.fullname AS "Course",
    a.name AS "Assignment",
    '' + a.name + ''
        AS "Assignmentlink"
FROM prefix_assignment_submissions AS asb
    JOIN prefix_assignment AS a ON a.id = asb.assignment
    JOIN prefix_user AS u ON u.id = asb.userid
    JOIN prefix_course AS c ON c.id = a.course
    JOIN prefix_course_modules AS cm ON c.id = cm.course
WHERE asb.grade < 0
    AND cm.instance = a.id
    AND cm.module = 1
ORDER BY c.fullname, a.name, u.lastname

you should use concat to join strings.

this column:

'' + a.name + ''
AS "Assignmentlink"

in mysql should write as this:

concat('' , a.name , '')
AS "Assignmentlink"


Update "NULL" string to Actual NULL value I have a table that contains NULL values but the problem is that some of the values are actually string "NULL" and not actual NULLS so when you trying something like

where date is null

it will not return the fields that have the "NULL" string.

What I am needing to do is run an update of the whole table that will convert all string "NULLS" to the actual NULL value. The "NULL" strings happen throughout all columns of the table so it is not just 1 column that needs to be updated. I am not sure how to approach this scenario. I'm thinking I might need to use a loop since i have many columns but then again there might be a simple solution without having to use a loop. What would be the best way to resolve this issue?

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + '
  ' + QUOTENAME(name) + ' = CASE
  WHEN ' + QUOTENAME(name) + ' = ''NULL'' THEN NULL ELSE '
  + QUOTENAME(name) + ' END,'
FROM sys.columns
WHERE [object_id] = OBJECT_ID('dbo.YourTableName')
AND system_type_id IN (35,99,167,175,231,239);

SELECT @sql = N'UPDATE dbo.YourTableName SET ' + LEFT(@sql, LEN(@sql)-1) + ';';

PRINT @sql;
--EXEC sp_executesql @sql;


SQL Server: Remove public role privileges from login account I've got a SQL Server (2012) and I need to create a read-only user.  At first, I was just going to create a user, and then give them the db_datareader role.  But while looking around, I saw the public role, and how it had Select, Update, Insert and Delete privileges on all the objects in the db.

Is there a way for me to say "Yes, I know you are a member of the public role, but I'm revoking privileges X,Y,Z"?

Is there a way for me to say "Yes, I know you are a member of the public role, but I'm revoking privileges X,Y,Z"?

You can use DENY :

  "Denies a permission to a principal. Prevents that principal from
  inheriting the permission through its group or role memberships. DENY
  takes precedence over all permissions, except that DENY does not apply
  to object owners or members of the sysadmin fixed server role.
  Security Note Members of the sysadmin fixed server role and object
  owners cannot be denied permissions."

So to DENY the DELETE permission to a user Fred database-wide:

DENY DELETE to Fred;

Or for just one schema

DENY DELETE ON SCHEMA::dbo to Fred;

But I would advise you to investigate why Public has all those permissions.  That's not a common or recommended security model.


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

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

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

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

Escalation, though

Lock escalation under serializable isolation level may occur the same as it does with other isolation levels.

Correct indexes can help to avoid lock escalation up to a point
Locking many indexes will increase the likelihood of lock escalation; the count is cumulative across objects for a single statement

Some quick examples using a single table with a single index. Id is the primary key and clustered index on the table.

One Row

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN

UPDATE c
SET c.Score = 2147483647
FROM dbo.Comments AS c
WHERE c.Id = 138; --One value

ROLLBACK

For a single Id value, locking is minimal.

+--------------+---------------+---------------+-------------+
| request_mode | locked_object | resource_type | total_locks |
+--------------+---------------+---------------+-------------+
| RangeX-X     | Comments      | KEY           |           1 |
| IX           | Comments      | OBJECT        |           1 |
| IX           | Comments      | PAGE          |           1 |
+--------------+---------------+---------------+-------------+

Multiple Rows

But locks will go up if we start working in ranges:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN

UPDATE c
SET c.Score = 2147483647
FROM dbo.Comments AS c
WHERE c.Id BETWEEN 1 AND 5000; -- Small range

ROLLBACK

Now we have more exclusive locks on more keys:

+--------------+---------------+---------------+-------------+
| request_mode | locked_object | resource_type | total_locks |
+--------------+---------------+---------------+-------------+
| RangeX-X     | Comments      | KEY           |        2429 |
| IX           | Comments      | OBJECT        |           1 |
| IX           | Comments      | PAGE          |          97 |
+--------------+---------------+---------------+-------------+

Way More Rows

This will carry on until we hit a tipping point:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN

UPDATE c
SET c.Score = 2147483647
FROM dbo.Comments AS c
WHERE c.Id BETWEEN 1 AND 11655; --Larger range

ROLLBACK

Lock escalation is attempted and is successful:

+--------------+---------------+---------------+-------------+
| request_mode | locked_object | resource_type | total_locks |
+--------------+---------------+---------------+-------------+
| X            | Comments      | OBJECT        |           1 |
+--------------+---------------+---------------+-------------+

Pay Attention

It's important to separate two concepts here: the isolation level will be serializable no matter what kind of locks are taken. The query chooses the isolation level, and the storage engine chooses the locks. Serializable won't always result in range locks -- the storage engine can pick whichever kind of locks still honor the isolation level.


MySQL: 'user'@'hostname' vs 'user'@'IPaddress' I needed an user to be able to log in on our MySQL server 5.6 from machine foobarhost, IP 10.11.12.13.

Therefore I created a user 'joe'@'foobarhost' but he was unable to log in.  I had to create an user 'joe'@'10.11.12.13' and this time worked fine.

My question: how does MySQL handle hostnames vs IP addresses concerning users?

MySQL sorts the user table before checking for a match, using IP addresses and hostnames as the most-specific values to match to.  However, the docs do not specify what takes precedence - hostname or IP.  Does MySQL attempt a hostname resolution and, if it fails, performs a check on the IP?

What is the recommended usage for the user table: specify only IP addresses, hostnames, or both (as I did in the example above)?

I had this problem before, and after long investigation I found out sometimes MySQL can not resolve any issue related to DNS by default, so I did the following: edited hosts file ( can be found under Linux /etc/hosts, for Windows C:\Windows\System32\drivers\etc\hosts) and added ip and hostname for the client machine,in your example its

10.11.12.13 foobarhost

then save and close ( note Windows will require reboot before taking any effect), you may need to add the following parameters --skip-name-resolve and\or --skip-host-cache .


MySQL trigger/check on INSERT based on multiple factors (location, date and time) I have a shift schedule planner for various warehouses. I'm trying to add multiple checks at DB level. If I'm using a composite key (by removing shift_id) to match staff, location,date time it's works but it doesn't solve the shift overlapping problem (e.g if I insert exact same data as shift_id 1 but instead of 09:00 start time is 09:01 will accept as valid shift).

In below example, the INSERT should fail for shift_id 2 because, the shift time is overlapped for same staff_id 1 on same date The next available shift for same staff (on same date and same location or a different one) should be available after 13:00. For shift_id 3 should be another fail because staff_id 1 is already scheduled for exact same date and same hours to another location (location_id 1).

I was trying to add table constraint to compare shift_start and shift_end but unsuccessfully:

 ADD CONSTRAINT NoDoubleShifts CHECK(
    NOT EXISTS (
            SELECT 1
            FROM Shift S1, Shift S2
            WHERE s1.shift_date + s1.shift_start < s2.shift_date + s2shift_start
            AND s1.shift_date + s1.shift_end > s2.shift_Date + s2shift_start)

MySQl throw errors (I'm guessing that the constraint is not well formatted "A statement was found, but no delimiter betwen it near CHECK").

The second trying solution was to add a trigger BEFORE INSERT in different combinations but didn't work either. Is calculating the time but is preventing me to add any other staff (with different id) during that time slot.

begin
  if exists (select * from shift
             where shift_start <= new.shift_end
             and shift_end >= new.shift_start ) then
    signal sqlstate '45000' SET MESSAGE_TEXT = 'Shifts are overlapping';
  end if;
end;

Any help and constructive comments or anyone to pint me in right direction will be highly appreciated.

Thanks in advance..

With some help, the solution was extremely simple...
The trigger needs to validate the staff id as well. Therefore, the final trigger would look like:

begin
  if exists (select * from shift
             where shift_start <= new.shift_end
             and shift_end >= new.shift_start
             and staff_id = new.staff_id) then
    signal sqlstate '45000' SET MESSAGE_TEXT = 'Shifts are overlapping';
  end if;
end


sqlserver - Insert from xml generated with `select for xml` Is there a simple way to create an XML (file, variable, value in column, whatever) with select * from table for xml ... and later automatically insert this data into identical table in different database? Something like insert into table from xml .... All solutions I can find require providing column names explicitly.
What I want in the end is a simple thing - I have Java service connecting to the database. It is able to download new dictionary data and insert (merge) it into specific tables. Currently there are quite a few such tables and we have separate procedures for them, all specifying columns names and so on. This is getting out of hand and I'm looking for some way of simplifying it.

Here is what I ended with.
Assuming that data is read from the table using the following:
select * from someTable where st_id between 100 and 200
for xml path, root('rows')

I get this data from one server and store it as a tuple (schema, table, keys, xml), where:

schema is schema name (e.g. dbo)
table is table name (e.g. someTable)
keys is comma-separated list of "keys" (columns used for <match> in merge ... on <match>, e.g. st_id in example above, if there are more keys they should be passed,like,this)
xml is data as an xml (easy)

This data is then passed to some other server, where there is one function and one procedure defined.
Function tools.GenerateXMLMergerForTable creates body (whole declaration in fact) of procedure used for merging data read from xml into table table in schema schema using parsed string keys as <match>.
Procedure tools.createXMLMergerForTable executes aforementioned function and creates procedure that takes single parameter (of type XML) and merges data read from it into table that it was configured for.
T-SQL follows:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER FUNCTION tools.GenerateXMLMergerForTable
(
@tableschema nvarchar(max), @tablename nvarchar(max), @matchcols nvarchar(max), @procname nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
    declare @str nvarchar(max) = '
    create or alter procedure tools.sp_AutoMergerForTableXXXPROCEDURENAMEXXX @x xml
    as
    begin
        begin try
            DECLARE @handle INT, @PrepareXmlStatus INT

            EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT, @x

            merge XXXTABLENAMEXXX as tgt
            using (SELECT  * FROM OPENXML(@handle, ''/rows/row'', 2) WITH XXXTABLENAMEXXX)
                as src (XXXFULLCOLUMNLISTXXX)
            on XXXMATCHLISTXXX
            when matched
                then update set
                    XXXSETLISTXXX
            when not matched by target
                then
                    insert (XXXFULLCOLUMNLISTXXX)
                    values (XXXFULLCOLUMNLISTXXX);
            EXEC sp_xml_removedocument @handle
        end try
        begin catch
        end catch
    end
    '

    declare @collist nvarchar(max) = '', @setlist nvarchar(max) = '', @matchlist nvarchar(max) = '', @fullcollist nvarchar(max) = ''

    select @collist = @collist + quotename(COLUMN_NAME) + ','
        , @setlist = @setlist + quotename(column_name) + ' = src.' + quotename(column_name) + ', '
    from INFORMATION_SCHEMA.COLUMNS where
        TABLE_SCHEMA = @tableschema and TABLE_NAME = @tablename and COLUMN_NAME not in (select * from string_split(@matchcols, ','))
    order by ordinal_position

    select @fullcollist = @fullcollist + quotename(COLUMN_NAME) + ','
    from INFORMATION_SCHEMA.COLUMNS where
        TABLE_SCHEMA = @tableschema and TABLE_NAME = @tablename
    order by ordinal_position

    select @matchlist = @matchlist + 'tgt.' + QUOTENAME(trim(value)) + '=src.' + QUOTENAME(trim(value)) from string_split(@matchcols, ',')

    declare @result nvarchar(max) = replace(@str, 'XXXCOLUMNLISTXXX', trim(', ' from @collist))
    set @result = REPLACE(@result, 'XXXSETLISTXXX', trim(', ' from @setlist))
    set @result = REPLACE(@result, 'XXXTABLENAMEXXX', @tablename)
    set @result = REPLACE(@result, 'XXXMATCHLISTXXX', REPLACE(trim(@matchlist), ' ', ' and '))
    set @result = REPLACE(@result, 'XXXPROCEDURENAMEXXX', @procname)
    set @result = REPLACE(@result, 'XXXFULLCOLUMNLISTXXX', trim(', ' from @fullcollist))

    RETURN @result

END
GO

create or alter procedure tools.createXMLMergerForTable @schema nvarchar(max), @table nvarchar(max), @matchcols nvarchar(max), @procsuffix nvarchar(max)
as
begin
    declare @body nvarchar(max) = tools.GenerateXMLMergerForTable(@schema, @table, @matchcols, @procsuffix)
    exec sp_executesql @body
end

go

Java code simply reads tuple defined above, inserts parameters that it knows off, creates random suffix, creates merging procedure, executes it using xml from The Tuple and drops previously created procedure.
It seems to work as advertised, though no guarantees are provided.
NOTE: it is assumed that function, procedure and subsequently created procedures live in schema tools.
NOTE 2: It is possible that you will get information about string_split object not found, if so then just do:
ALTER DATABASE <your database name>
SET COMPATIBILITY_LEVEL = 130;

For reasons see documentation of string_split and how to change compatibility level.


MySQL IDB File grows huge despite having little data We have recently migrated to a new database server with mysql 8 (from 5.7 previously). We now have a table that stores an int, a datetime and a mediumtext column that grows unproportionally huge. So much so that the server's complete hard drive space was depleted.

The problem is the table only contains about 100 rows of data and the idb file after an optimize table is 7 Mb. after a week running the table grew to over 700GB.

The table does receive a lot of deletes+inserts. We had the same table on the old server for years without issue.

i tried

innodb_purge_threads = 32
innodb_max_purge_lag = 2000000

But the table continues to grow at about 20Mb per Minute without any obvious reason. Am I missing any special new mysql feature?

Or will I need to have my apps run an optimize table every couple minutes now. It seems the database should be handling this issue by itself instead of needing to rely on such hacks from applications.

Thanks in advance for any pointers!

The MySQL Server is Version 8.0.18 running on Windows 2019 with 32GB of RAM. Nothing else runs on this server and the database server is an upgrade from an older version that had a third of this specs. The table structure is identical and ran for years without issue on the old server.

Below is the table structure:

The following is the Show Create Table
CREATE TABLE `automationjobstate` (
  `AutomationJobID` int(10) unsigned NOT NULL,
  `StateTimestamp` datetime NOT NULL,
  `StateData` mediumtext,
  PRIMARY KEY (`AutomationJobID`),
  CONSTRAINT `automationjobstate_ibfk_1` FOREIGN KEY (`AutomationJobID`) REFERENCES `automationjobs` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

How do I attach the results from SHOW VARIABLES? It says its to many characters.

Some 30 hours now have past and the table in question (i.e. its idb file) has less disk space occupied than expected. The application that uses the table had been offline for a couple of hours, too, and hence, the table has been shrunk again. It still shows its growing behaviour once the application is coming back online again.

It appears now, that there is a purge system within mysql that does not quite keep up with usage of that table - despite the older 5.7 MySQL on much older hardware didn't have that issue.

I suppose that this realization is the answer to my original question and that I will have to tweak the innoDB purge parameters to find optimal settings, or maybe trigger Optimize Table from the Application every once in a while.


MySQL Semi-synchronous replication with Multi-Master Is it possible to use semi-synchronous replication with a Multi-Master setup?

I've tried to follow this guide to setup a semi-synchronous replication for a master-slave setup: https://avdeo.com/2015/02/02/semi-synchronous-replication-in-mysql/

But I'm not sure how to implement this on a Multi-Master setup.

There are two plugins: one for the master and one for the slave. Since a Multi-Master act as a Master and Slave, does that mean I have to install both plugins on all servers?

I'm using MySQL 5.7

The idea of semi-synchronous is to wait for acknowledgement from at least one  slave and since you would be using master-master so you might have to install both plugins i.e. for master & slave on both nodes. This is because when one master would write the other would behave like it's slave and same goes for the other case. Hope it helps


Deciding EC2 Instance type & Volume IOPS for Mongo DB We have 500GB data in our Mongo(v2.6.3) including the indexes.

We currently run this on AWS m3.large(8GB RAM) instance. Yes, Single
machine.

Our MongoDB Cloud Manager shows these stats:

  Opcounters - 1.5k
  Background Flush Average - 5s
  Non mapped Virtual Memory - 2GB
  Lock Percentage - 15%
  BTree - 6k
  Connections - 2k
  Queues - 100
  Record Stats - 200
  Page Faults - 1k

Our Mongo has become slower over the last few months as the Data is growing rapidly at the rate of 40GB per month.

We are sure this is the time to scale it up. We had difficult time deciding whether to scale it horizontally or vertically. What we decided then was that let's scale vertically now. And six months down the line, as we will have more bandwidth in terms of people(currently we are a very small team), we will scale it Horizontally. We would love to have your views on this decision.

And moreover, the biggest confusion that we have is over choosing hardware.The options we have are:

AWS Ec2 r3.large - 2 CPU Cores - 15GB RAM
AWS Ec2 r3.xlarge - 4 CPU Cores - 30GB RAM

And we will be taking 1.5TB Volume but still confused with the number of IOPS that we need to reserve.

Hoping to get your views & valuable suggestions.

You should be able to view your current IOPS on the monitoring page of AWS - at least you can for RDS instances.

If this is EC2 and not RDS (RDS is backed by EC2 instances) you probably need to enable enhanced monitoring for those instances.


Logging to a file in PostgreSQL windows service I need an alternative for running the following command:

C:\xxx\pgsql\bin\pg_ctl" -D "C:\xxx\pgsql\data" -l "C:\yyy\log\pgsql.log" start.

This way the server starts and logs to C:\yyy\log\pgsql.log.
When I try to register the server as a service though - no logging options are available and the server logs to Event Viewer.
From pg_ctl documentation:

  pg_ctl register [-N servicename] [-U username] [-P password] [-D datadir] [-S a[uto] | d[emand] ] [-w] [-t seconds] [-s] [-o options]

How can I force the server to log into a file?
Note: I don't want to use one-click installer, I just want to work with unzipped binaries.

Locate your postgresql.conf in the datadir

Find the section that looks like this

#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------

# - Where to Log -

#log_destination = 'stderr'             # Valid values are combinations of
                                        # stderr, csvlog, syslog and eventlog,
                                        # depending on platform.  csvlog
                                        # requires logging_collector to be on.

# This is used when logging to stderr:
#logging_collector = off                # Enable capturing of stderr and csvlog
                                        # into log files. Required to be on for
                                        # csvlogs.
                                        # (change requires restart)

# These are only used if logging_collector is on:
#log_directory = 'pg_log'               # directory where log files are written,
                                        # can be absolute or relative to PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'        # log file name pattern,
                                        # can include strftime() escapes
#log_truncate_on_rotation = off         # If on, an existing log file of the
                                        # same name as the new log file will be
                                        # truncated rather than appended to.
                                        # But such truncation only occurs on
                                        # time-driven rotation, not on restarts
                                        # or size-driven rotation.  Default is
                                        # off, meaning append to existing files
                                        # in all cases.
log_rotation_age = 7d                   # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.
#log_rotation_size = 10MB               # Automatic rotation of logfiles will
                                        # happen after that much log output.
                                        # 0 disables.

simply uncomment and use these parameters. Then, restart the postgres service,

Since you are running postgresql in Windows, it is possible that you may not be allowed to edit postgresql.conf while the service is up. If that is the case:

shutdown the postgres service
edit postgresql.conf
start the postgres service

Give it a Try !!!


does a php 7.3 cassandra driver exist? I have php 7.3 on Debian 10. I'm trying to get the Apache Cassandra driver for php 7.3.

The one provided by datastax (https://github.com/datastax/php-driver) only works up to 7.1, and the pecl one (pecl install cassandra) also only work up to PHP 7.1.x. Does one for 7.3 exist? I have searched around and can't find it.

PHP & Ruby drivers are put on hold - they will receive only critical bug fixes but not the new functionality. You should be able to compile existing code for PHP versions beyond 7.1, but DataStax doesn't provide pre-built versions.


Could not fork new process for connection: Could not allocate memory I need help to understand the cause of this error and how to fix it.

I have a server running PostgreSQL 9.3. The cluster has around 1.000 databases. I use pgBouncer for pooling connections, and I set up a pool_size of only 1 connection per database.
The total amount of active connections at any time is ~80. This is because most of databases have almost no activity at all.

My application load has been increasing slowly during the last months, and in the last couple of days I started to see the error showing up intermitently in the postgres log file. The time the error is logged corresponds with brief moments where the load is a bit higher than usual. Notice the error happens when postgres tries to fork a new process for connection and even for an autovacuum process:

2020-05-07 07:16:16 -03  LOG:  main could not fork new process for connection: Cannot allocate memory
2020-05-07 07:16:17 -03  LOG:  could not fork autovacuum worker process

These are some of my PostgreSQL settings:

max_connections = 300
shared_buffers = 2GB
effective_cache_size = 2GB
maintenance_work_mem = 1GB
work_mem = 288MB
wal_buffers = 8MB
checkpoint_segments = 16

The server has 64GB of total RAM, 16 CPU cores, and it is running CentOS 7.
My stack is: Nginx, uWSGI, Redis, pgBouncer and PostgreSQL.
It's all installed in the same server, so resources must be shared between the elements of the stack.Redis is set to use no more than 30GB of RAM. Nginx ~8GB of RAM. uWSGI uses ~10GB of RAM. PostgreSQL uses ~8GB of RAM.

I'm no an expert at PostgreSQL. I've been reading the documentation regarding memory consumption, and considering my settings, my best guess is that I need to set higher values for shared_buffers and effective_cache_size. I got to that conclusion because those settings have been there for a long time (when my server had much less resources). But in the last few years, I've been adding resources to my server, but at the same time it has become busier. So I think maybe shared_buffers should be at least of 8gb, what do you think about that?

Also, for what I've read, I think I should make effective_cache_size higher than shared_buffers (considering the amount of RAM the server has available). Am I right?

One more thing: what do you think about my work_mem setting? I'm planning to reduce it (I'm not sure why it is set to 288MB). I know that the vast majority of the queries are very simple and fast. This is because all the databases correspond to simple blog applications, where most of the queries are SELECTs and the UPDATEs involve changing some value at an specific row, nothing complex. So, what do you think? I plan to reduce work_mem, am I in the right direction?

Thank you very much in advance!
Warm regards,
Lisandro.

I think that your machine is overloaded. You don't say what your max_connection setting is, but based on what you say I would assume it to be something around 1000.

As long as the number of active sessions is around 80, your memory should not get exhausted, but I assume that there are spikes of more active sessions, and your RAM is exhausted.

I am surprised that your machine runs well with 80 active sessions, since you have only 16 cores (you didn't say anything about the storage).

1000 databases seem a lot; I assume that each of them is small.

Since you have so many databases, I recommend that you move some to other clusters on different machines. That is an easy way to distribute the load.

Using a lower value for max_connections would reduce the danger of overload and memory exhaustion.


Soft delete field causes left join not to return my "left" side data I've got a parent table of trainings and a child table of trainingattendees.
That is a training can be attended by multiple attendees.
I have a bit field in my trainingattendee table called Deleted that has a default value of (0).  That is the record is not deleted.  This value can change to 1 if the record is deleted.

I was simply doing a left join from trainings to trainingattendees to get some data but if ALL my training attendees table rows are marked as deleted (1) I don't get my training record (the left side table)

That is this works:

SELECT
    Training.TrainingID AS TrainingID
FROM
    Training
LEFT OUTER JOIN
    TrainingAttendee ta
ON
    ta.TrainingID=Training.TrainingID
WHERE
    LOWER(Training.Title) = LOWER('Courtesy Dust Mask Training')
    AND DATEADD(d, DATEDIFF(d, 0, Training.StartDate), 0) >= DATEADD(d, DATEDIFF(d, 0, GetDate()), 0)
    AND (ta.TrainingAttendeeStatusID=4)
GROUP BY
    Training.TrainingID

The issue here is this works because I am not filtering out my training attendee records for deleted ones.  I have created a record in my training attendee table but marked it as deleted and reran this same query with one additional where condition.

SELECT
    Training.TrainingID AS TrainingID
FROM
    Training
LEFT OUTER JOIN
    TrainingAttendee ta
ON
    ta.TrainingID=Training.TrainingID
WHERE
    LOWER(Training.Title) = LOWER('Courtesy Dust Mask Training')
    AND DATEADD(d, DATEDIFF(d, 0, Training.StartDate), 0) >= DATEADD(d, DATEDIFF(d, 0, GetDate()), 0)
    AND (ta.Deleted=0)
    AND (ta.TrainingAttendeeStatusID=4)
GROUP BY
    Training.TrainingID

By adding this: AND (ta.Deleted=0)

I then thought I could change this condition to this:

AND (ta.Deleted=0 OR ta.Deleted IS NULL)

But this too did not work...How can I properly do this such that I get my "left" table data ?

(Note: completely updated, when I realized your problems was a bit different than I initially thought).

As you probably realize (given the change you made), if you check for a non-NULL value on a LEFT JOIN column in your WHERE clause, it can make your LEFT JOIN act like an INNER JOIN.

There are two ways to handle this problem:

You can perform all your checks in the JOINs ON clause
You can check for the condition you're interested in within the WHERE clause, but OR that with a condition that is true when there are no rows found from the "right" table.

However, this can have two different effects.

When you check a condition in the ON clause, that becomes a part of the condition for joining the rows from the "right" table to the "left" table. So, if a row fails to meet your condition, it has no impact on the rows from the "left" table - it's as if it doesn't exist.

On the other hand, when you're checking in the WHERE clause, it is already tied to the data form the "left" table. So, if there are rows from the "right" table, but none of them match your condition, the rows from the "left" table do get excluded.

With that in mind, let's take a look at what you're trying to do. You want training classes where the trainees (if any) are in status 4, and you want to ignore any trainees marked as deleted.

So, try this instead:

SELECT
    Training.TrainingID AS TrainingID
FROM
    Training
LEFT OUTER JOIN
    TrainingAttendee ta
ON
    (ta.TrainingID=Training.TrainingID AND ta.Deleted = 0)
WHERE
    LOWER(Training.Title) = LOWER('Courtesy Dust Mask Training')
    AND DATEADD(d, DATEDIFF(d, 0, Training.StartDate), 0) >= DATEADD(d, DATEDIFF(d, 0, GetDate()), 0)
    AND (ta.TrainingAttendeeStatusID=4 OR ta.TrainingID IS NULL)
GROUP BY
    Training.TrainingID

This should treat any ta records marked as deleted as if they don't exist, but will only return Training records is they have no attendees, or if they have non-deleted attendees in the desired status.

NOTE: This does change your original query (which you said was working correctly), which was basically acting as an INNER JOIN. Try the new query with and without the OR ta.TrainingID IS NULL in the status check, to see if you really if the results are different, and which results you really wanted.

UPDATE: Further discussion with JonH revealed that he was trying to get a count of the number of seats in each class (where, logically enough, deleted attendees shouldn't be counted). He'd added the following to his query's SELECT list:

   ,ISNULL(COUNT(*),0) AS TakenSeats

However, he was still getting back 1 seat as taken, even if all attendees had been deleted. I explained that this was due to the fact that COUNT(*) was counting the number of rows returned - and even if no TrainingAttendee row was found, you'd still get one row from Training. I recommended changing this to:

   ,ISNULL(COUNT(ta.TrainingID),0) AS TakenSeats

which (since COUNT ignores NULL values) should return 0 when there are no TrainingAttendee rows found for a given Training row.


log_warnings doesn't seem to work for aborted connections in MySQL 5.1 The docs for 5.1 say that I can set log_warnings = 2 to capture Aborted_connects in the error log.  This bug report suggests that this was only merged into 5.5 and 6.0 (and maybe 5.2) but not into 5.1.

I tried out all combinations of set global log_warnings = 2; and set global log_warnings = 1; followed by login attempts with valid and invalid users and invalid and no passwords results in nothing in the error log.

Are the docs incorrect or have I made a mistake somewhere?
Do other types of aborted connections (such as a loss of network during the login or improper permissions for the database) get logged in the error log?
Do aborted clients get logged in the error log?

Some other things I have checked/done:

log_error is set to /var/log/mysql/error.log.
That file exists and the mysqld process has it open.
The last entry in it is from 2012/06/01.
skip-log-warnings is not set in my.cnf and the process was started with no arguments.
skip_log_warnings does not exist in SHOW GLOBAL VARIABLES LIKE '%skip%'
The uptime of MySQL is 50 days.
I ran FLUSH LOGS just in case.
The version string is 5.1.62-0ubuntu0.10.04.1-log.
The Aborted_connects value is increasing by one every time I try an invalid connection attempt.

This seems to be an elusive bug nobody wants to fix

http://bugs.mysql.com/bug.php?id=24761 (2006-12-01 : Status Triaged)
http://bugs.mysql.com/bug.php?id=41602 (2008-12-18 : Status Closed)
https://serverfault.com/questions/235369/how-can-i-get-mysql-5-5-to-log-warnings-to-one-of-the-log-files (2011-02-14)

According to http://bugs.mysql.com/bug.php?id=24761, the aborted connect is recorded in the general log

[29 Sep 2008 8:03] Konstantin Osipov
OK, it was fixed differently:
      /*
        Log the command before authentication checks, so that the user can
        check the log for the tried login tried and also to detect
        break-in attempts.
      */
      general_log_print(thd, command,
                        (thd->main_security_ctx.priv_user ==
                         thd->main_security_ctx.user ?
                         (char*) "%s@%s on %s" :
                         (char*) "%s@%s as anonymous on %s"),
                        thd->main_security_ctx.user,
                        thd->main_security_ctx.host_or_ip,
                        db ? db : (char*) "");

So, this is logged in the general log at least.

I was reviewing a patch that added more logging,
but I can't remember the worklog task number.

We just have to wait on MySQL (I mean Oracle) to fix it. (That was sarcasm)


migrating legacy databases from SQL Server 2000 via SQL Server 2008 to SQL Server 2012: users and logins I have some legacy SQL Server 2000 databases to migrate to SQL Server 2012.

In my dry run, I have done this:

I created empty databases of the same names in SQL Server 2008.
Then I backed up the SQL Server 2000 databases and restored them, overwriting the 2008 databases.
Then I created empty databases of the same names in SQL Server 2012.
Then I backed up the 2008 databases and restored them, overwriting the 2012 databases.

At this point, I can log in to the SQL Server 2012 databases from within Query Analyzer, as Domain Admin using Windows Authentication, and query the databases; and SPs invoked from within QA work as they should.

The SQL Server 2012 server has been set up to support Windows and SQL authentication, but the SQL Authentication-mode connection strings from client applications do not work yet.
During the restore process, the users have been orphaned, as expected.

It has been a few years since I've had to migrate databases between server versions, so I cannot remember how to recreate the SIDs for these orphaned users, though there used to be a fairly quick way to do that, IIRC.   There was a script, and it didn't require me to do any preparation on the "from" server; it was simply run on the "target" server.

Please jog my memory.

You can use the following to list orphaned sql users:

USE foo
go

EXEC sp_change_users_login 'Report';

and

USE foo
go

EXEC sp_change_users_login 'update_one', 'foouser', 'foouser';

to fix each one.


How to set datetime2 to mandate? I want to create a SQL Script to clean datetime2 field and set it up to datetime2 begin '1-1-0001 12:00:00'. How to set GETDATE() to  '1-1-0001 12:00:00' instead of using convert function ?

UPDATE XYZTable
SET ReferralDate = CONVERT(datetime2, '1/1/0001 09:00')

Well, a couple things.

GETDATE() returns a datetime value, which has a valid range of January 1, 1753, through December 31, 9999. 1753 is when the civilized (or is it civilised?) world switched to the Gregorian calendar from the Julian one. So you won't be able to have it return the date you're after; it's out of range.
You can use SYSDATETIME() to get you there, because it returns a datetime(2) value. However, it won't necessarily be any cleaner code-wise than using CONVERT on that string. You'd have to throw some weird date math at it, like SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, SYSDATETIME(), '00010101 12:00:00.0000000'), SYSDATETIME()).

If this is just going to be a canary value, you're better off setting it as the column default value.

ReferralDate DATETIME(2) DEFAULT '00010101 12:00:00.0000000'

If you need to update, it might be a little simpler to set it in a variable and then update using the variable.

CREATE TABLE #crap (Id INT IDENTITY(1,1), Dt DATETIME2(7))
GO

INSERT #crap ( Dt )
    VALUES ( NULL)
GO 1000

SELECT *
FROM #crap AS c

DECLARE @dt DATETIME2(7) = '00010101 12:00:00.0000000'

SELECT @dt

UPDATE c
SET c.Dt = @dt
FROM #crap AS c
WHERE c.Dt IS NULL

SELECT *
FROM #crap AS c


Query to find all dates between two specified dates using plsql I have a table with below structure
create table test_table
(
  customer_num      NUMBER,
  card_number       char(2),
  card_issue_date   DATE,
  card_expire_date  DATE
)

Example data is:
customer_num    |  card_number   |  card_issue_date | card_expire_date
--------------------------------------------------------------------------
  1             |  x             |  1/1/2019        | 1/4/2020
  1             |  y             |  1/1/2020        | 1/4/2020
  1             |  z             |  1/11/2020       | 1/15/2020
  1             |  b             |  1/11/2020       | 1/20/2020
  1             |  w             |  1/12/2020       | 1/17/2020
  1             |  a             |  1/18/2020       | 2/12/2020

For each customer's card, I need to have all dates between card_issue_date and card_expire_date.
So the output for the first record would be like this :
customer_num    |  card_number   |  card_issue_date
----------------------------------------------------
  1             |  x             |  1/1/2019
  1             |  x             |  1/2/2020
  1             |  x             |  1/3/2020
  1             |  x             |  1/4/2020

IS there any function or something else that can help me out with this?
Thanks in advance

This should get you started:
declare
  s date := date'2020-01-01';
  e date := date'2020-01-04';
  ndays number;
begin
  ndays := e-s+1;
  for i in 1..ndays loop
    dbms_output.put_line (s);
    s := s+1;
  end loop;
end;
/

01-JAN-2020
02-JAN-2020
03-JAN-2020
04-JAN-2020

PL/SQL procedure successfully completed.

Just use simple date arithmetic: the difference between two dates is the number of days between the two:
date'2020-01-04' - date'2020-01-01' + 1

The above returns 4 (the +1 is so as to also include the last day, since this is what you describe in your example).
Then the rest is just a loop over the number of days, incrementing the start date by one day on each loop:
s := s + 1;

The next stage is to turn that into a usable function. First we need a type:
create or replace type date_array as table of date;
/

This defines an array of dates as is used as the output of the pipelined function:
create or replace function dates_between (s date, e date)
return date_array
pipelined
is
  d date := s;
begin
  for i in 1..e-s+1 loop
    pipe row(d);
    d := d + 1;
  end loop;
end;
/
show errors

This function returns the list of dates between two dates (including the end date). Like this:
SQL> select * from table (dates_between (date'2020-02-23',date'2020-03-05'));

COLUMN_VALUE
-----------------
23-FEB-2020
24-FEB-2020
25-FEB-2020
26-FEB-2020
27-FEB-2020
28-FEB-2020
29-FEB-2020
01-MAR-2020
02-MAR-2020
03-MAR-2020
04-MAR-2020
05-MAR-2020

12 rows selected.

Let's try that with your example. First let's create and populate the test table:
drop table test_table purge;
create table test_table (
  customer_num      NUMBER,
  card_number       char(2),
  card_issue_date   DATE,
  card_expire_date  DATE
);
insert into test_table values (1,'x',to_date('1/1/2020 ','MM-DD-YYYY')  ,to_date('1/4/2020','MM-DD-YYYY'));
insert into test_table values (1,'y',to_date('1/1/2020 ','MM-DD-YYYY')  ,to_date('1/4/2020','MM-DD-YYYY'));
insert into test_table values (1,'z',to_date('1/11/2020','MM-DD-YYYY')  ,to_date('1/15/2020','MM-DD-YYYY'));
insert into test_table values (1,'b',to_date('1/11/2020','MM-DD-YYYY')  ,to_date('1/20/2020','MM-DD-YYYY'));
insert into test_table values (1,'w',to_date('1/12/2020','MM-DD-YYYY')  ,to_date('1/17/2020','MM-DD-YYYY'));
insert into test_table values (1,'a',to_date('1/18/2020','MM-DD-YYYY')  ,to_date('2/12/2020','MM-DD-YYYY'));
commit;

(Noticed I corrected the first date to match your description).
Here is the query that gives the result you describe:
select customer_num, card_number, column_value as card_date
from test_table, table (dates_between (card_issue_date,card_expire_date));

which returns:
CUSTOMER_NUM CARD_N CARD_DATE
------------ ------ -----------------
           1 x      01-JAN-2020
           1 x      02-JAN-2020
           1 x      03-JAN-2020
           1 x      04-JAN-2020
           1 y      01-JAN-2020
           1 y      02-JAN-2020
           1 y      03-JAN-2020
           1 y      04-JAN-2020
           1 z      11-JAN-2020
           1 z      12-JAN-2020
           1 z      13-JAN-2020
           1 z      14-JAN-2020
           1 z      15-JAN-2020
           1 b      11-JAN-2020
           1 b      12-JAN-2020
           1 b      13-JAN-2020
           1 b      14-JAN-2020
           1 b      15-JAN-2020
           1 b      16-JAN-2020
           1 b      17-JAN-2020
           1 b      18-JAN-2020
           1 b      19-JAN-2020
           1 b      20-JAN-2020
           1 w      12-JAN-2020
           1 w      13-JAN-2020
           1 w      14-JAN-2020
           1 w      15-JAN-2020
           1 w      16-JAN-2020
           1 w      17-JAN-2020
           1 a      18-JAN-2020
           1 a      19-JAN-2020
           1 a      20-JAN-2020
           1 a      21-JAN-2020
           1 a      22-JAN-2020
           1 a      23-JAN-2020
           1 a      24-JAN-2020
           1 a      25-JAN-2020
           1 a      26-JAN-2020
           1 a      27-JAN-2020
           1 a      28-JAN-2020
           1 a      29-JAN-2020
           1 a      30-JAN-2020
           1 a      31-JAN-2020
           1 a      01-FEB-2020
           1 a      02-FEB-2020
           1 a      03-FEB-2020
           1 a      04-FEB-2020
           1 a      05-FEB-2020
           1 a      06-FEB-2020
           1 a      07-FEB-2020
           1 a      08-FEB-2020
           1 a      09-FEB-2020
           1 a      10-FEB-2020
           1 a      11-FEB-2020
           1 a      12-FEB-2020

55 rows selected.


Many to Many Relationship Question I have a database, with one main table: Project, and about 10 other tables (Entity1, Entity2, etc.)

Each of the different entities can belong to one or many projects.

I know I can have tables like ProjectEntity1, ProjectEntity2, ProjectEntity3, to store relationships, but that gets old.

Are there any issues with creating a ProjectContents table, which would hold projectid, entityid, and entitytype? (Entitytype would be name of the source table.)

(Currently, this is in SQL Server Compact 4.0, as a standalone solution. It may never evolve past that.)

EDIT: More details -
It's yet another world builder type desktop application, where I can dream up/generate stuff - worlds, cultures, languages, religions, alien species, characters, plots - and then assign them to a full blown project if I want. So, a project can have many entities of varying types, and an entity can belong to many projects, or none at all. Right now, all CRUD/Reads are done with C# LINQ, and unless I switch to SQL Express or something else, I don't see the need for stored procedures. At least until I hit some performance bottlenecks.

One problem with your proposed design is that instead of simple JOINs you have to run dynamic SQL every time you want to see the actual value of any entities.  Furthermore, you will have complicated procedures (or triggers) for inserting data into ProjectContents.

Stick with the ProjectEntity1, ProjectEntity2, ... solution if you can have more that one EntityX coupled to a certain Project.  If this is not the case (that is, a Project can have maximum one of each Entities), you can create a table like this:

ProjectID | Entity1ID | ... | EntityNID

In either case, your JOINs will be relatively numerous (not that 10 JOINs in a query are a lot) but otherwise simple.  And, lastly, occasional maintenance (like adding new entities) will also be simple.


noexpand hint for non-enterpise edition and performance I have to use Indexed views to reach performance. As I can see from this comparison table standard edition does not support indexed views. But BOL says:

  Indexed views can be created in any
  edition of SQL Server. In SQL Server
  Enterprise, the query optimizer
  automatically considers the indexed
  view. To use an indexed view in all
  other editions, the NOEXPAND table
  hint must be used.

So will it work (I am talking about performance)

select * from dbo.OrderTotals with (noexpand, index=IXCU_OrderTotals)

on SQL Server Standard edition
as well as it works

select * from dbo.OrderTotals

on the Enterprise one?

Here is code for view:

CREATE VIEW dbo.OrderTotals
WITH SCHEMABINDING
AS
select
    OrderId     =   r.OrderId
  , TotalQty            =   SUM(r.Quantity)
  , TotalGrossConsid    =   SUM(r.Price * r.Quantity)
  , XCount      =   COUNT_BIG(*)
from dbo.Order r
group by r.OrderId

CREATE UNIQUE CLUSTERED INDEX IXCU_OrderTotals ON OrderTotals (OrderId)

The difference is that Enterprise edition without the hint may decide not to use the indexed view but the base tables instead.

My personal experience is that SQL Server is somewhat braindead in this. I almost always have to use the hint: the query is quicker with far less IO even though the plan "looks" worse with a scan on the view not index seeks on the base tables. And it runs more consistently too

YMMV of course :-)

So, to answer, it will (should?) work the same based on what I've seen. Other folk may have different experiences and I'm interested in other answers

To avoid using the hint everywhere you can wrap the indexed view in another view with the hint: hints propagate inwards to all outer queries will automatically have NOEXPAND.


What would cause a table with 80,000 records to use 145GB of space? I have a table that gets inserted and deleted from every day.  On average the record count is 80,000.  Roughly 20 fields, 10 of which are VARCHAR(4000) however most of the records have those fields empty.  1 NC Index account for 8MB.

The job that loads this table runs as expected with no performance issues.

This table has been slowly growing 1GB a day with MINIMAL record count increase(sometimes decrease).  I cant seem to pin point what would cause this.  Any help is greatly appreciated!

Create a clustered index so that your table is not a heap. When a row is deleted from a heap, the space will not be reused. Even empty pages don’t get cleared up. This problem won’t happen if your table is a clustered index.


Windows Managed account to report subscription file share Is it possible to used a Windows Managed account to access a file share in a Report subscription?

When creating a Windows file share report subscription, on Step 4 - Create a data-driven subscription, I must specify the Username and Password of the account which has access to the file share.  Can this be a Windows Managed Account and if so, what do I specify for the password to get the field to validate and allow me to move to the next screen?

Ok, I have not messed with the file share subscriptions so I had to set this up locally on my machine to mess with it. The only thing I can even come across in MSDN that talks about the username for this type of subscription is here:

  Password:  Specify the password for the Windows user name.

Out of that the only thing I can find is that it requires an actual user account with password. So it is not possible to use virtual or managed account.

The only way I could get this subscription to write to the UNC path was to enter a Windows User account. In this instance my own login. I was just running an example SSRS report against AdventureWorks2012 database.


High write latency on tempdb datafiles only I'm experiencing large write latency on our tempdb database, but on no other database.
(from sys.dm_io_virtual_file_stats) Tempdb log file latency is also listed as being below 5ms.

For comparison:
Write latency on all databases except tempdb are below 5ms, tempdb was about 500ms when using 8 datafiles. I removed 4 datafiles 2 days ago just to check if something changes and it looks like the latency doubled after doing this.

A few notes on our environment:

SQL Server 2008 R2 SP3 VM running on Hyper-V
one large RAID10 array attached to our Hyper-V hosts via fibre channel where everything is stored

I have no explanation for this and I don't know where to look for answers.
I could of course assume that doubling the data files will half the WriteLatency but I don't know if this is the way to go.

Any ideas on where to start?

I seem to have solved it or at least know where it is coming from.

To find out I setup perfmon to monitor avg. ms per write operation and it looks like this. which leads me to the conclusion that those extreme spikes only happen during nightly index rebuilding (I'm using Ola Hallengrens IndexMaintenance-solution for this), normal operation is fine.

My learning: don't rely on cumulated stats in sys.dm_io_virtual_file_stats only, but they are a good indicator to look further for the origin.


Delete array element by index Is it possible to delete a Postgres array element by index? (Using Postgres 9.3.)

I don't see anything for this in the docs (http://www.postgresql.org/docs/9.3/static/functions-array.html) but perhaps there are other functions I am missing?

There is no built-in function that I would know of. In Postgres 9.3+ you have the similar function array_remove(anyarray, anyelement) to (per documentation):

  remove all elements equal to the given value from the array (array
  must be one-dimensional)

To delete the 3rd element from a 1-dimenstional array with unique elements:

SELECT array_remove(arr, arr[3]);

If uniqueness is not guaranteed the above might delete multiple elements. You can concat two array slices instead:

Postgres 9.5 or older

SELECT arr[1:2] || arr[4:2147483647] AS arr_without_3rd_elem;

2147483647 is just the maximum possible array subscript to cover all possibilities. Works for any version since at least 8.3, probably more.
About array subscripts:

Normalize array subscripts for 1-dimensional array so they start with 1

Old SQL Fiddle for Postgres 9.3.

If you need this a lot, create a function. I suggest an IMMUTABLE polymorphic SQL function. (An implementation in C would be a bit faster.)

CREATE OR REPLACE FUNCTION f_array_remove_elem(anyarray, int)
  RETURNS anyarray LANGUAGE sql IMMUTABLE AS
'SELECT $1[1:$2-1] || $1[$2+1:2147483647]';

Call:

SELECT f_array_remove_elem('{1,2,3,4,5}'::int[], 4)    AS i
     , f_array_remove_elem('{foo,bar,baz}'::text[], 1) AS t;

Result:

i         | t
----------+-----------
{1,2,3,5} | {bar,baz}

It's more complicated ...

for multi-dimensional arrays.
You cannot just remove a single element, dimensions have to stay in sync. So you have to define which slice to remove exactly. Then I would probably unnest the array using generate_suscripts(), remove selected subscripts and array_agg() / array_agg_mult() the result.
for non-standard array-subscripts.
Normalize subscripts in the source like demonstrated in the linked question or adapt subscripts in the expression with array_lower() and array_upper() ...

Postgres 9.6

.. introduced short notation for upper and lower limit (just omit the number). I added an answer to the question linked above:

Normalize array subscripts for 1-dimensional array so they start with 1

Simpler now:

SELECT arr[:2] || arr[4:] AS arr_without_3rd_elem;

The function:

CREATE OR REPLACE FUNCTION f_array_remove_elem(anyarray, int)
  RETURNS anyarray LANGUAGE sql IMMUTABLE AS
'SELECT $1[:$2-1] || $1[$2+1:]';

Works for all 1-dimensional arrays, including non-standard array-subscripts. Just be sure to provide the correct array index of the element to remove.

dbfiddle here


How do I demonstrate Transaction Log activity? Say I have the following setup:

use tempdb
go

set nocount on
go

create table MyTest
(
    Column1 varchar(100),
    Column2 text
)
go

insert mytest (Column1, Column2)
select REPLICATE('a', 100), REPLICATE('a', 100)
from sys.syscolumns a, sys.syscolumns b

I'd like to convert each of the columns to varchar(max) like this:

-- processes every page:
alter table mytest
alter column Column1 varchar(max)

-- processes only metadata:
alter table mytest
alter column Column2 varchar(max)

How can I demonstrate that the first command processes the whole table while the second command only processes metadata. I was thinking of using SET STATISTICS IO which reports thousands of logical reads for the first command and nothing for the other. I was also thinking of using DBCC LOG or fn_dblog. But I wasn't sure how to interpret or tie the results to the queries I issued.

Pick a page containing a record in your table (use %%physloc%% to find one).
Run DBCC PAGE before the DDL, write down last_lsn of the page.
Run your DDL
Run again DBCC PAGE. Did last_lsn change?

If the last_lsn changed the DDL is a size-of-data updates-every-record kind of change. If the last_lsn did no change obviously the DDL did not update every record.

For a more elaborate way, you can track page write XEvents for a single statement (the DDL).


what is the penalty for NOT setting Disconnect users in the database when restoring backups? setting up Log shipping between 2 sql server 2016 servers
I want my databases read-only standby on the secondary server.

there is a tick box saying Disconnect users in the database when restoring backups.

what if I don't tick this box?
The restore of the log would not come trough because it could not get exclusive lock on the database?

But then, what if someone is logged in for hours? Hours without restoring the logs?

From this page in the MS documentation:

  There are two options for configuration when you place the secondary
  database in standby mode:

  You can choose to have database users disconnected when transaction
  log backups are being restored. If you choose this option, users will
  be disconnected from the database each time the log shipping restore
  job attempts to restore a transaction log to the secondary database.
  Disconnection will happen on the schedule you set for the restore job.

  You can choose not to disconnect users. In this case, the restore job
  cannot restore transaction log backups to the secondary database if
  there are users connected to that database. Transaction log backups
  will accumulate until there are no user connections to the database.

So yes, it could be hours, days, weeks, or years  without a restore if users are still connected to the secondary.


MySQL: Unable To Create Generated Column With Expression That Uses DATE_ADD() Function There might be a really obvious solution to my question, but I am stuck and did not manage find out what I am doing wrong, so you guys might tell me.

I am not able to create a nullable stored column for my expression using the DATE_ADD function in MySQL 8.

See the following example:

ADD COLUMN `test` TIMESTAMP GENERATED ALWAYS AS (DATE_ADD(col1, INTERVAL col2 HOUR)) STORED NULL;

col1 is TIMESTAMP and col2 is INT

MySQL does not complain, but just converts the expression to

(`col1` + interval `col2` hour)

And the resulting is not nullable as well.

Mysql has done, what you ask of him.
here are more infomration

DATE_ADD(col1, INTERVAL col2 HOUR) is equal to (col1 + interval col2 hour)

And is set as Default Value for the column. so when you add a NULL you get  what ever (col1 + interval col2 hour) represents.

So you can't achieve a Nullable and generated Column at the same time.

If You need A NULL there, you have to generate the test column VALUE at runtime.


SmallDateTime to DateTime Conversion Equality If I were to convert a table's smalldatetime field to a datetime field instead, what are possible side effects (with regards to the database only)? Are there equality issues that might come up in stored procedures? Could joins, with or without casting result in errors or different results?

It seems to me that converting smalldatetime to datetime is mostly harmless but I wanted to get an understanding of possible pitfalls.

Converting Smalldatetime to datetime will not cause any issue other than taking double the space for that column.  Look at the the SQL Server Data Type Conversion Chart.

Smalldatetime uses 4 bytes of space compare to datetime uses 8 bytes. Both are fixed length.

From books online:

  When the conversion is to datetime, the smalldatetime value is copied
  to the datetime value. The fractional seconds are set to 0. The
  following code shows the results of converting a smalldatetime value
  to a datetime value.

DECLARE @smalldatetime smalldatetime = '1955-12-13 12:43:10';
DECLARE @datetime datetime = @smalldatetime;

    SELECT @smalldatetime AS '@smalldatetime', @datetime AS 'datetime';

    --Result
    --@smalldatetime          datetime
    ------------------------- -----------------------
    --1955-12-13 12:43:00     1955-12-13 12:43:00.000
    --
    --(1 row(s) affected)][2]

I know in some blogs and articles it is mentioned that implicit conversion will cause a scan instead of seek or will not use the index at all. Which is true in many cases but not always.  In this case you will get a seek. See this article by Jonathan Kehayias.


How to disable MySQL error logging? My error file(/var/log/mysql/error.log) is increased rapidly（10% of disk usage per hour） with only this entry:

[ERROR] Error in accept: Bad file descriptor

and we can only shutdown the server down in the midnight to investigate further, now I need to find a way to stop the error file taking up all space of my disk.

I've tried something like set GLOBAL general_log = 0, but it's not working at all, also tried to flush error logs, a new error file(error.log.1) is created, but the process(mysqld) keeps writing the the old file(error.log).

please help, ask me if you need more information.

DEBUG INFO

Mysql Server version: 5.7.18-0ubuntu0.16.04.1

To find error log location, run the below command

mysql> show global variables like '%log_error%';

As you mentioned, set GLOBAL general_log = 0 - this command used to disable general log. Since it is a space issue, identify, whether it is due to error log or general log. If general log, disable it, if it's due to error log; flush and rename the error log.

Note: If you flush the logs using FLUSH LOGS or FLUSH ERROR LOGS commands, the MySQL will closes and reopens the error log, but it will keep on writing to same error log file.

In order to create a new file, rename the original file before performing the FLUSH operation, so that, when you run FLUSH LOGS or FLUSH ERROR LOGS the MySQL will close and open new file and start writing to new file.

mv /var/log/mysql/error.log /var/log/mysql/error_old.log
mysqladmin flush-logs

If the error log file size keep on increasing, the root cause should be identified and removed. Managing the size is not a solution, just a temporary workaround. Open the error log and start debugging and remove the errors, this will keep the error log size within limit.

If troubleshooting errors not in your hand, just make the FLUSHING operation as scheduled task and take backup of the error log. This can be done as below:

mv /var/log/mysql/error.log /var/log/mysql/error_old.log
mysqladmin flush-logs
mv /var/log/mysql/error_old.log /backupdir

I hope this answer will help you, Thanks!


Can't export table structure with expdp, parameter full=X conflicts with tablespaces=X I'm trying to export table schema with expdp,
expdp \'sys/XXXX as sysdba\' dumpfile=XXX.dmp logfile=XXXX.log content=METADATA_ONLY tablespaces=XXXX

And it fails with
ORA-31655: no data or metadata objects selected for job

Then I added full=Y and it conflicts with tablespaces parameter:
ORA-31655: no data or metadata objects selected for job

How can I export all schemas from specific tablespaces only?

What you posted works fine.
[oracle@o81 ~]$ expdp \'/ as sysdba\' tablespaces=users content=metadata_only

Export: Release 19.0.0.0.0 - Production on Sat Nov 14 12:48:17 2020
Version 19.9.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLESPACE_01":  "/******** AS SYSDBA" tablespaces=users content=metadata_only
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TABLE
Master table "SYS"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLESPACE_01 is:
  /u01/app/oracle/homes/OraDB19Home1/rdbms/log/expdat.dmp
Job "SYS"."SYS_EXPORT_TABLESPACE_01" successfully completed at Sat Nov 14 12:48:43 2020 elapsed 0 00:00:26

[oracle@o81 ~]$

You get this error when the tablespace specirfied does not exist:
[oracle@o81 ~]$ expdp \'/ as sysdba\' tablespaces=users123456 content=metadata_only

Export: Release 19.0.0.0.0 - Production on Sat Nov 14 12:50:16 2020
Version 19.9.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLESPACE_01":  "/******** AS SYSDBA" tablespaces=users123456 content=metadata_only
ORA-31655: no data or metadata objects selected for job
Job "SYS"."SYS_EXPORT_TABLESPACE_01" completed with 1 error(s) at Sat Nov 14 12:50:19 2020 elapsed 0 00:00:03

[oracle@o81 ~]$


How do I read ERD Notation (crow's feet) to convert to Natural Language? Background

I am attempting to insure I understand how to read ERD (Entity Relationship Diagram) notation so I can convert it to a natural language explanation.

I'm trying to insure I can explain a method of converting what I see in the diagram to natural language.

Let's use the following diagram:
(Please don't get too caught up on the correctness of the actual entities, they are just for our example.)

Natural Language Sentence Examples

Company to Address

Would this be a correct way of talking about the relationship from Company to Address.

A company has 1 to many addresses. (shipping, billing, etc).

This indicates that the company must have at least one address, right?

Does that match the notation I have in the diagram?

Company to Connection

Each company has zero to many Connections.

Is that correct?  Is that how I read that double-bar on the Company side?

How Are the Two Company-side Connectors Any Different?

Do the two company-side connectors indicate anything different at all?
Can you explain that?

General Help For Natural Language Translation?

Do you have a logical method with steps you could provide for examining each relationship and converting to natural language?

For example, with Company to Connection.

Where do I start reading?
How do I remember (or explain) that the crows foot on Connection indicates the number of Connections a Company has and not vice versa?

Thanks

I'd like to answer this more definitively now using the following image (from https://www.lucidchart.com/pages/ER-diagram-symbols-and-meaning)

Using the definitions of these symbols you can see that the following things are true about my originally posted diagram.

A company has 1 to many addresses. (shipping, billing, etc).
A company has 0 to many connections
A connection has one (and only one) company
An address has one company - this should probably contain the double
line connector to show that it is one and only one.

My final question was:

  Do you have a logical method with steps you could provide for
  examining each relationship and converting to natural language?

Place a finger on the entity you are starting with.  For example,
place a finger on Company.
Now say, "Company has..." and choose the related entity you want to
talk about. For example, choose connection.
Look at the relation symbol that touches the Connection entity (it is the empty circle with three lines (legs)) which indicates zero to many.
Continue saying "...zero to many connections".

The final sentence would be,

  "Company has zero to many Connections."

That is how you transform symbols on Entity Relationship Diagrams into natural language.