texts: Mysql Restore - ERROR 1227 (42000) at line 30908: Access denied DEFINER issue

texts:
Im trying to restore the database dump which has the DEFINER=test@%.

Backup file is 50GB.
OS - CentOS 7.5

While restoring this, I got the below error.
ERROR 1227 (42000) at line 30908: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

The line 30908 is,
CREATE DEFINER=`test`@`%` PROCEDURE `myprod`(IN id INT)

So I tried to remove the definer using perl.
perl -p -i.bak -e "s/DEFINER=\`\w.*\`@\`\d[0-3].*[0-3]\`//g" my_dump_file.sql

Its executed, but after that also, I got the same issue and its not changed in file.
Then I tried,
sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' < my_dump_file.sql > my_dump_file.new

This is also executed, and tried to restore my_dump_file.new but still same issue on the same line.
Then finally I tried to create an user on the MySQL.
create user `test`@`%` identified by 'test';
grant all on *.* to `test`@`%`;
flush privileges;

Again, Im getting the same issue.
Can anyone help me to fix this?


texts: Find MIN and MAX Database Backup duration

texts:
I have this query to find the duration of backups taken which I want to run against registered servers in CMS across the entire enterprise.

The problem though, is how to find only the MIN and MAX duration per database rather than the runtime of all of the backups.

I've messed around a lot with MIN and MAX but have yet to find the correct combination.

Am I going about this the wrong way?

DECLARE @dbname sysname

SET @dbname = NULL

SELECT database_name AS [Database]
    , backup_start_date AS [Backup Started]
    , backup_finish_date AS [Backup Finished]
    , backup_size AS [Backup Size]
    , compressed_backup_size AS [Compressed Backup Size]
    , CAST((CAST(DATEDIFF(s, backup_start_date, backup_finish_date) AS int))/3600 AS varchar) + ' hours, '
     + CAST((CAST(DATEDIFF(s, backup_start_date, backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '
     + CAST((CAST(DATEDIFF(s, backup_start_date, backup_finish_date) AS int))%60 AS varchar)+ ' seconds' AS [Total Time]
FROM msdb.dbo.backupset
WHERE [type]!='L'
    AND database_name IN (SELECT name FROM master.dbo.sysdatabases)
GROUP BY database_name
    ,backup_start_date
    ,backup_finish_date
    ,backup_size
    ,compressed_backup_size;

Code sourced from here.


texts:
I have a view that is pulling sales from multiple companies and I need to basically 'combine' two rows so they display as one.  Here is a picture of what the data looks like:

I need to take all rows for Wine Group and add them into Other/Us Direct and then delete Wine Group or Filter it out so it doesn't show after adding it's values.   I tried writing a case statement and also just tried to aggregate the sum of values and union it to the existing view but it still displays them as separate entities.  Any ideas or tips would be much appreciated!

Here is current view query that pulls from the table that has this information:

SELECT  [LoadDate]
      ,[Company]
      ,CASE WHEN [ReportGroup] = 'Germany' THEN 'Direct Germany' ELSE ReportGroup END AS ReportGroup
      ,[ShipToCountryCode]
      ,[LLCCustName]
      ,[TodayGrossSalesUSD]
      ,[TodayGrossSalesEURO]
      ,[CurrMonthInvoiceUSD]
      ,[CurrMonthInvoiceEURO]
      ,[CurrMonthShipUSD]
      ,[CurrMonthShipEURO]
      ,[CurrMonthBudgetUSD]
      ,[CurrMonthBudgetEURO]
      ,[CurrMonthLinearUSD]
      ,[CurrMonthLinearEURO]
      ,[LastYearCurrMonthUSD]
      ,[LastYearCurrMonthEURO]
      ,[LastYearCurrMonthLinearUSD]
      ,[LastYearCurrMonthLinearEURO]
      ,[TodayOrderIntakeUSD]
      ,[TodayOrderIntakeEUR]
      ,[TodayVolume]
      ,[CurrMonthInvoiceVolume]
      ,[CurrMonthShipVolume]
      ,[CurrMonthBudgetVolume]
      ,[CurrMonthLinearVolume]
      ,[LastYearCurrMonthVolume]
      ,[LastYearCurrMonthLinearVolume]
      ,[TodayOrderIntakeVolume]
      ,[NextMonthVolume]
      ,[NextMonthGrossSalesUSD]
      ,[NextMonthGrossSalesEURO]
      ,[LastYearCurrMonthAsOfVolume]
      ,[LastYearCurrMonthAsOfUSD]
      ,[LastYearCurrMonthAsOfEURO]
      ,[CurrMonthForecastVolume]
      ,[CurrMonthForecastUSD]
      ,[CurrMonthForecastEURO]
      ,DayType
  FROM [BI_DB].[dbo].[DailySummary]

UNION

SELECT
DISTINCT
LoadDate,
Company,
CASE WHEN [ReportGroup] = 'Germany' THEN 'Direct Germany' ELSE ReportGroup END AS ReportGroup,
[ShipToCountryCode],
[LLCCustName],
NULL AS [TodayGrossSalesUSD],
NULL AS [TodayGrossSalesEURO],
NULL AS [CurrMonthInvoiceUSD],
NULL AS [CurrMonthInvoiceEURO],
NULL AS [CurrMonthShipUSD],
NULL AS [CurrMonthShipEURO],
[CurrMonthBudgetUSD],
[CurrMonthBudgetEURO],
[CurrMonthLinearUSD],
[CurrMonthLinearEURO],
NULL AS [LastYearCurrMonthUSD],
NULL AS [LastYearCurrMonthEURO],
NULL AS [LastYearCurrMonthLinearUSD],
NULL AS [LastYearCurrMonthLinearEURO],
NULL AS [TodayOrderIntakeUSD],
NULL AS [TodayOrderIntakeEUR],
NULL AS [TodayVolume],
NULL AS [CurrMonthInvoiceVolume],
NULL AS [CurrMonthShipVolume],
[CurrMonthBudgetVolume],
[CurrMonthLinearVolume],
NULL AS [LastYearCurrMonthVolume],
[LastYearCurrMonthLinearVolume],
NULL AS [TodayOrderIntakeVolume],
NULL AS [NextMonthVolume],
NULL AS [NextMonthGrossSalesUSD],
NULL AS [NextMonthGrossSalesEURO],
NULL AS [LastYearCurrMonthAsOfVolume],
NULL AS [LastYearCurrMonthAsOfUSD],
NULL AS [LastYearCurrMonthAsOfEURO],
[CurrMonthForecastVolume],
[CurrMonthForecastUSD],
[CurrMonthForecastEURO],
DayType
  FROM [BI_DB].[dbo].[DailySummaryFullMonth]
WHERE LoadDate > (SELECT MAX(LoadDate) FROM [BI_DB].[dbo].[DailySummary])


texts: Cannot start MySQL Cluster Management Service

texts:
Could not determine which nodeid to use for this node. Specify it with --ndb-nodeid= on command line.

This is what EventViewer is saying


texts: Unable to connect to AWS redshift database

texts:
I am trying to migrate to a new PostgreSQL database on Amazon Redshift and get the following error:

Connection to jdbc:postgresql://redshift-cluster-i-created:5439/dev wasn't established. ERROR: code 0

To connect I am using the AWS Schema Conversion Tool->Connect to Amazon RDS for PostgreSQL.

Server name: Cluster-name-as-created-on-redshift
Server port: 5439 as is the default AWS port
Database: left blank to connect to the default database I created
User name and password as created on AWS.

For the Server name I also tried the JDBC URL as per the cluster info on AWS but without the 'jdbc:redshift://' prefix or ':5439/dev' postfix.

Then I tried the public IP address of the AWS cluster for the Server name, then I tried the AWS cluster Endpoint as the Server name.

None worked. Any ideas?


texts: Set Parameter in SPFILE still outputs the same error

texts:
for the installation of a SAP IDM system, I need to know how to use the command alter system set "_ORACLE_SCRIPT"=true SCOPE=SPFILE; command correctly.
The installation creates a user and gives the error ORA-65096: "invalid common user or role name". During the researches I found the command alter session set "_ORACLE_SCRIPT"=true. This command allows to create users temporarly.
At the next session or in a separate terminal, this is no longer possible.
In the $ORACLE_HOME/dbs/spfile.ora the parameter *_oracle_script=true was in there, but it gives me the same error again.
Thank you in advance.
-Sruscht


texts: SQL Anywhere 17 - Database server connection limit exceeded

texts:
I set up a new SQL Anywhere 17 Database in Sybase Server. I've got a lot of clients that should be able to connect to the Database over ODBC. The ODBC works fine but as soon as the third user tries to connect i get the following message:

I've tried select @@max_connections and got 32'766.
Also when I execute the folowing statement I get the following result:
SELECT
PROPERTY('ServerEdition') as ServerEdition,
PROPERTY('IsNetworkServer') as IsNetworkServer,
PROPERTY('LicenseType') as LicenseType,
PROPERTY('LicenseCount') as LicenseCount,
PROPERTY('MaxConnections') as MaxConnections
FROM dummy

Does anyone have any idea what I can cause this problem?


texts: SSMS connections not using KERBEROS over VPN - why?

texts:
I have a very simple setup. SQL Server X (2012) and SQL Server Y (2016). X has a linked server to Y using the setting "Connections will: Be made using the login's current security context". SPNs are set up, and internal users on our domain can query the linked server. I can see their connections authenticating through KERBEROS if I run this query:

select session_id,net_transport,client_net_address,auth_scheme
from sys.dm_exec_connections WHERE net_Transport = 'TCP'

However, a few external users connect via VPN. They then shift+right click on SSMS and "run as a different user" and use their account located on our domain. They can connect to the instances separately, but hit the 'double-hop' issue like below when trying to query the linked server:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

If I run the above query, I can see their connections are using NTLM authentication, not KERBEROS, so that explains why credentials are not passed through. Why are being authenticated with NTLM? Is this a configuration issue with the VPN? Maybe a limitation to a VPN?

**** UPDATE ****

Further testing has shown that if a user connects to a machine on our network via RDP and leaves that connection open, connections from their local SSMS then seem to use KERBEROS. When the user logs off of the RDP session, new connections from SSMS then start using NTLM again. In other words, they are doing NOTHING inside the RDP session, but as long as that's open, connections from SSMS use KERBEROS. (Maybe a coincidence, but that's the best I've got now)


texts: Testing an Oracle DB connection before it is made - ERROR?

texts:
I tried to make a new oracle DB connection with user name and password. I entered the connection name, user name and password in the new connection window. When I click the "test" button or the "connect" button, I get the following error:
Status : Failure - Test Failed: ORA-01017: invalid username/password; logon denied

Why am I getting this error ? What does the Test & Connect button actually do ?


texts: INSERT INTO FROM: Concatenating Forename and Surname when Forename can be blank,

texts:
I have a staging (RAW) table that contains one field for to hold a person's first name, and another for a person's last name. I want to insert those value on a production (DBO) table, where the person's forename and surname will be concatenated:

INSERT INTO [dbo].Test
SELECT firstname + ' ' + lastname
      ,field1
      ,field2
      ,fieldn
FROM RAW.Test

Problem however is that I just found out that the firstname field can be NULL (no blanks, but NULL) in the RAW table. Considering the code I have above, this would mean that the person's name would be " Smith" (note the trailing space) instead of "Smith".

What would be the best way to check for such issue and only add the trailing space between firstname and lastname if firstname contains a value?

Note that firstname would never be blank, since the RAW table has a NULLIF conditional that replaces all blanks with NULL.

Thanks for your help and support!

P.

EDIT: Sorry for the lame edit but I made a mistake, the firsname field is the one that can be blank (which makes sense if you see the example I gave about " Smith").


texts: How to split table into two tables in one transaction or query?

texts:
I have a table "profiles" with these columns: "id", "name", "surname", "email", "avatar".

I want to create table "media" which will contain columns "id" and "path".

After that I want to create column "profiles"."avatar_id" and migrate data by this rule: each value of "profiles"."avatar" must be inserted into "media" ("media"."id" is autogenerated), returning id must be written into "profiles"."avatar_id" in the same row.

It should be executed in one query, transaction or procedure, and I want to use only postgres functionality.

I tried to rename "profiles" to "profiles_old", create new "profiles" with "avatar_id" but without "avatar". Then run something like:

INSERT INTO profiles(id, "name", email, surname, avatar_id)
SELECT id, "name", email, surname, (INSERT INTO media(origin) VALUES(profiles_old.avatar) RETURNING id) FROM profiles_old;

...but SELECT doesn't support subquery INSERT. Maybe I should use procedure?

I am using Postgres 9.4.9. I tried a transaction and temp tables. I cannot understand how to insert each row of one table into another table and update the same row of first table with returned id? My code:

BEGIN;
ALTER TABLE profiles DROP CONSTRAINT profiles_pkey;
ALTER TABLE profiles RENAME TO profiles_old;
CREATE TABLE profiles (
id uuid NOT NULL DEFAULT uuid_generate_v4(),
"name" character varying(255),
email character varying(255),
surname character varying(255),
avatar_id uuid NOT NULL,
CONSTRAINT profiles_pkey PRIMARY KEY (id) ) WITH ( OIDS=FALSE );
ALTER TABLE profiles OWNER TO app;
INSERT INTO profiles(id, "name", email, surname, gender, avatar_id) SELECT id, "name", email, surname, (INSERT INTO media(origin) VALUES(profiles_old.avatar) RETURNING id) FROM profiles_old;
DROP TABLE profiles_old;
COMMIT;


texts: How to best use connection pooling in SQLAlchemy for PgBouncer transaction-level pooling?

texts:
Using SQLAlchemy to query a PostgreSQL database behind PgBouncer, using transaction-level pooling.

What is the best pattern to use for this kind of set up?  Should I have one-engine-per-process, using a ConnectionPool, or should I create an engine per-request, and use NullPool for each one of them?  Is there a different pattern altogether that I should be using?

Thanks very much!  Let me know if more information is needed and I'll update ASAP.


texts: Postgres Number of Updates/Deletes Since Last Autovacuum

texts:
Is there a good way to monitor the number of updates/deletes to a table since the last autovacuum? I'm trying to find tables that aren't updated frequently enough to avoid autovacuum (to prevent wraparound).

I was thinking of using n_dead_tup from pg_stat_all_tables, but is this a good idea/is there a better way?

I'm using Postgres 9.6.

Thanks


texts:
Earlier,  there were non-conclusive to me debates / discussions on whether to (always) engage / avoid clustered indexes.

Well, I understood that they are to be used sometimes with proper + specific purposes and context.

SQL Azure Database Clustered Index Requirement:

  "SQL Azure does not support tables without clustered indexes. A table must have a clustered index. If a table is created without a clustered constraint, a clustered index must be created before an insert operation is allowed on the table"

does not fit in the previous conclusions, rationale and explanations.

What is the rationale, that I have missed from prev explanations, of rigidly imposing ubiquity of clustered indexes without any exceptions?


texts: How to permanently fix IntelliSense poor functioning on SSMS?

texts:
I have literally installed on my development machine three different versions of SSMS standalone: 2008, 2012, 2014, and I work with SQL Server 2008 databases. On every single one of those versions, I tried to have IntelliSense display my available columns when I type a table alias and a dot "Table." for example. But that doesn't show up over 90% of the time. This happens similarly on any SSMS version I try.

I've searched for other people having this issue and found various cases, but none of those provided solutions, such as refreshing the IntelliSense cache and disabling/re-enabling IntelliSense itself, de-selecting e re-selecting certain options, work for me.

Could this be a problem related to the instances of SQL Server that I am connecting to, rather than with SSMS itself?

Edit: I have also tried to install a third party addon to SSMS, it then starts displaying as expected, but cripples my performance (making typing somewhat slower as it loads things in the background, which I really don't like), so I would rather fix the original functionality instead of resorting to that.

Thanks


texts: Database not able to startup showing control file is inconsistent with another

texts:
Our windows server 2008 R2 is shutdown due to power failure. When I am trying to startup Oracle 11g database its shows an error.

SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> startup mount
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shut immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2180024 bytes
Variable Size            2415922248 bytes
Database Buffers          771751936 bytes
Redo Buffers               16982016 bytes
ORA-00214: control file 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL'
version 145627 inconsistent with file
'D:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL' version 145625

Can any one help me how to resolve this issue?


texts: MySQL DELETE statement doesn't use index although the same SELECT query does

texts:
I've got a table with ~30 million rows (and soon twice/triple times more) where I have to do quite regular updates. The table structure is like the following:

id,
cookie_id VARCHAR(45),
country VARCHAR(45),
category VARCHAR(45),
other_non_relevant_columns

Indexes look like this:

SHOW INDEX FROM data;
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| data  |          0 | PRIMARY                |            1 | id          | A         |    24767570 |     NULL | NULL   |      | BTREE      |         |               |
| data  |          1 | cookie_index           |            1 | cookie_id   | A         |    14440214 |     NULL | NULL   |      | BTREE      |         |               |
| data  |          1 | country_category_index |            1 | country     | A         |         498 |     NULL | NULL   |      | BTREE      |         |               |
| data  |          1 | country_category_index |            2 | category    | A         |         997 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

So there's a non-unique index on cookie_id, and non-unique index on country+category columns. Now the case is, every week I should run query to

Delete all data belonging to country='Y' AND category='X' (5 to 20 million rows)
Import fresh data (similar amount)

The problem is, deleting the data takes crazy amount of time - that's why I've set up an index on country+category columns. However, 'DELETE' statement still doesn't use the index and instead tries to scan the whole table:

mysql> EXPLAIN DELETE FROM data WHERE country='Y' and category='X';
+----+-------------+-------+------------+------+------------------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys          | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-------+------------+------+------------------------+------+---------+------+----------+----------+-------------+
|  1 | DELETE      | data  | NULL       | ALL  | country_category_index | NULL | NULL    | NULL | 24767570 |   100.00 | Using where |
+----+-------------+-------+------------+------+------------------------+------+---------+------+----------+----------+-------------+

Select works fine:

mysql> EXPLAIN SELECT id, cookie_id FROM data WHERE country='Y' and category='X';
+----+-------------+-------+------------+------+------------------------+------------------------+---------+-------------+----------+----------+-------+
| id | select_type | table | partitions | type | possible_keys          | key                    | key_len | ref         | rows     | filtered | Extra |
+----+-------------+-------+------------+------+------------------------+------------------------+---------+-------------+----------+----------+-------+
|  1 | SIMPLE      | data  | NULL       | ref  | country_category_index | country_category_index | 365     | const,const | 10130630 |   100.00 | NULL  |
+----+-------------+-------+------------+------+------------------------+------------------------+---------+-------------+----------+----------+-------+

Is there any way to optimize DELETE query?


texts: Postgres recursive query not using trivial index on massive table

texts:
I've got a simple recursive query to find a tree of all posts:

with recursive all_mentions(post_id, author_id, mentioned_id) as (
  select post_id, author_id, mention_id
  from mentions
  where author_id = 'abc123' -- <-- index gets used

  union all

  select next.post_id, next.author_id, next.mention_id
  from all_mentions as cur
  inner join mentions as next on
    next.author_id = cur.mention_id -- <-- index does not get used
  where next.post_id > cur.post_id
)
select *
from all_mentions

There is an index on mentions.author_id, which gets used in the first portion of the query (ie, for … where author_id = 'abc123'), but it does not get used in the second part of the query (ie, … where next.author_id = cur.mention_id), and instead there is a table scan + sort of the 500M line table:

Limit  (cost=3818225300.80..3818225310.80 rows=500 width=136)
  CTE all_mentions
    ->  Recursive Union  (cost=779.32..3818225300.80 rows=13002675849 width=193)
          ->  Bitmap Heap Scan on mentions  (cost=779.32..75336.89 rows=18919 width=193)
                Recheck Cond: (("author_id")::citext = '0xc5918a927c4fb83fe99e30d6f66707f4b396900e'::citext)
USES INDEX =>   ->  Bitmap Index Scan on mentions_author_id  (cost=0.00..774.59 rows=18919 width=0)
                      Index Cond: (("author_id")::citext = '0xc5918a927c4fb83fe99e30d6f66707f4b396900e'::citext)
          ->  Merge Join  (cost=282994142.90..355809644.69 rows=1300265693 width=193)
                Merge Cond: ((all_mentions_1."author_id")::citext = (next."mention_id")::citext)
                Join Filter: (next.post_id > all_mentions_1.post_id)
                ->  Sort  (cost=25542.31..26015.28 rows=189190 width=40)
                      Sort Key: all_mentions_1."author_id"
                      ->  WorkTable Scan on all_mentions all_mentions_1  (cost=0.00..3783.80 rows=189190 width=40)
                ->  Materialize  (cost=282968600.59..285568486.01 rows=519977084 width=166)
                      ->  Sort  (cost=282968600.59..284268543.30 rows=519977084 width=166)
DOES NOT USE INDEX =>       Sort Key: next."mention_id"
                            ->  Seq Scan on mentions next  (cost=0.00..37074366.84 rows=519977084 width=166)
  ->  CTE Scan on all_mentions  (cost=0.00..260053516.98 rows=13002675849 width=136)

Other relevant details:

There are many (millions) authors, and each author has a relatively small number of posts (on ~10-100; obviously there's a long tail)
I expect the query to return ~hundreds of rows

Any pointers towards why this could be?


texts: Is it possible to log mysql queries from other hosts in general log?

texts:
I'm having a task to figure out what are the hosts that are still running scripts that accessing our database directly so we can prevent that from happening(we only want our web app to access the database but nothing else). I just learnt that enabling general_log would give me a nice table of access log. My problem is that the table grows too fast and easily lost control.

For my specific use case, my question would be: is there a mysql feature to make general_log only record database access from other hosts so general_log could record less data? I know it's pretty unlikely to happen, so my last option is to periodically delete the records that are local access. But this might cause performance issue. Do you think it makes sense to do that? Is there a better way to do it? Thanks.


texts: PostgreSQL 10 logical replication only synchronizing one time and then stopping

texts: I have one database containing user information that I'm replicating to a separate application db on the same cluster. After an error I discarded the subscription, replication slot, and the replicated schema. After re-creating the tables and subscription the data replicated as expected. However, all subsequent changes after the initial load failed to synchronize. This included all major operation INSERT, UPDATE, & DELETE.


texts: Many to many relationship, select rows based on values in list

texts:
I want to select group_runs based on a list of group_runners.player and I'm having issue getting rid of results that don't have all the values on that list. In this case group_runs with id = 2 shouldn't be returned because it contains only 1,3.
Schema (MySQL v5.7)
CREATE TABLE IF NOT EXISTS `group_runs`
(
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `dungeon_id` SMALLINT NOT NULL,
    `difficulty` TINYINT NOT NULL,
    `time` MEDIUMINT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `group_runners`
(
    `run_id` INT UNSIGNED NOT NULL,
    `player` INT NOT NULL,
    FOREIGN KEY (`run_id`) REFERENCES `group_runs` (`id`) ON DELETE CASCADE
);

INSERT INTO `group_runs` VALUES (NULL, 1, 1, 160); #run 1
INSERT INTO `group_runs` VALUES (NULL, 1, 1, 150); #run 2

#Add to run 1 player (1, 2, 3)
INSERT INTO `group_runners` VALUES (1, 1);
INSERT INTO `group_runners` VALUES (1, 2);
INSERT INTO `group_runners` VALUES (1, 3);

#Add to run 2 player (1, 3)
INSERT INTO `group_runners` VALUES (2, 1);
INSERT INTO `group_runners` VALUES (2, 3);

Query #1
SELECT runs.id, runs.time, GROUP_CONCAT(runners.player) players
FROM group_runs runs
JOIN group_runners runners
ON runs.id = runners.run_id
WHERE
    runs.dungeon_id = 1 AND
    runs.difficulty = 1 AND
    runners.player IN (1, 2, 3)
GROUP BY runs.id;

id
time
players

1
160
1,2,3

2
150
1,3

View on DB Fiddle


texts: How could I improve the performance of this MySQL query?

texts:
This query takes a long time to run, is there be a way I could rewrite it to make it faster?

SELECT DISTINCT r.racedate, re.trainerid
FROM raceentry re
INNER JOIN race r ON r.raceid = re.raceid
WHERE re.trainerid IS NOT NULL
AND re.Trainer30in90Starts IS NULL
AND re.trainerid IN (
    SELECT DISTINCT trainerid
    FROM pp_entries
)
AND re.raceid IN (
    SELECT re2.raceid
    FROM raceentry re2
    INNER JOIN race r2 ON r2.raceid = re2.raceid
    WHERE re2.horsename = re.horsename
    ORDER BY r2.racedate DESC
)
ORDER BY r.racedate DESC, re.trainerid LIMIT 500

This is what it displays when I run the EXPLAIN on it:

1   SIMPLE  pp_entries  NULL    range   IX_pp_Entries_1,trainerid               trainerid   5   NULL    4835    46.66   Using where; Using index; Using temporary; Using f...
1   SIMPLE  re          NULL    ref     PRIMARY,IX_RaceEntry,ix_RaceEntry_Horse,RaceEntry2...   RaceEntry_trainerID     5   thefigures_dbo.pp_entries.trainerid     10  10.00   Using where
1   SIMPLE  r           NULL    eq_ref  PRIMARY,IX_Race     PRIMARY     17  thefigures_dbo.re.RaceID    1   100.00  NULL
1   SIMPLE  r2          NULL    eq_ref  PRIMARY,IX_Race     PRIMARY     17  thefigures_dbo.re.RaceID    1   100.00  Distinct
1   SIMPLE  re2         NULL    eq_ref  PRIMARY,IX_RaceEntry,ix_RaceEntry_Horse,RaceEntry2...   PRIMARY     49  thefigures_dbo.re.RaceID,thefigures_dbo.re.HorseNa...   1   100.00  Using index; Distinct

Thanks guys, used a combo of the suggestions and ended up with this query

SELECT DISTINCT r.racedate, re.trainerid
FROM raceentry re
INNER JOIN race r ON r.raceid = re.raceid
WHERE re.trainerid IS NOT NULL
AND re.Trainer30in90Starts IS NULL
AND re.trainerid IN (
    SELECT trainerid
    FROM pp_entries
)
LIMIT 300

The biggest improvement seemed to be removing the 2nd IN() clause suggested by Oreo


texts: find sold subscriptions from table 1 and store name in table 2, joined by store id. (oracle)

texts:
I am trying to get the number of sold subscriptions from one table and the store name from another. The tables have a common column containing a store id, however the columns do not have the same name.

Talbe1                  |   Table2

Amount     store_code   |   store_name    store_no

1          nf1ii        |   Jims kiosk    nf1ii
1          nfoi1        |   Henrys kiosk  nfoi1
...        ...          |   ...           ...

I am trying to count the number of subscriptions sold for each store name. How is this doen in Oracle? I am fairly new to SQL in general so please forgive me if the question is phrased strangely.


texts:
We have 14 GB worth of CSV's which total 138 million rows.  I imported this into a MySQL table first with InnoDB, and then tried again with MyISAM.  In both cases, a simple SELECT on primary key (which is just an autoincremented int) took 6-7 seconds, though MyISAM sometimes was a little faster at 5-6 seconds.

We only need to write the data once, and I've been using mysqlimport.  With that in mind, how can I improve the query speed?

...it's worth nothing that we have 2 gigs of RAM and everything is one table (and it has to stay that way due to the nature of the queries).  Is that the best performance I can expect given the hardware?  Or is there something else I should try, like compression?  Or really, I need a lot more RAM?


texts: Add a new Column and define its position in a table

texts:
I have Table A with 5 columns:

TableA
--
Name
Tel
Email
Address

I want to add a new column (mobile) in between Tel & Email:

TableA
--
Name
Tel
Mobile
Email
Address

If I use

ALTER TABLE TableA
ADD COLUMN Mobile INT NOT NULL

the mobile column is added to the end of the table.

Is there a way to achieve this without dropping the table and moving data to a new table?


texts: Delete Query Issue in MySQL and PHPMyAdmin


texts: How to add rows/columns to the table in runtime in SSRS 2008

texts:
Usually we design the table to have x number of rows and y number of columns in a report. But how can we create a report which adds the rows and columns dynamically at run time based on the result of the source query?

For example I want to list stdentId, StudentName and any course each student has enrolled in. As the number of courses is different from one person to the other, I should add the rows and related column for courses at run time based on the query result. How can it be done? For example:

Thanks for your help in advance.


texts: Quantifiers in Tuple Relational Calculus (Existencial and Universal)

texts:
Suppose this state for the relation schema Company

I'm stuck trying to understand how, why and when I should use Quantifiers (Existencial and Universal)

Examples:

Here I understand how the existencial is working, all the formula bounded            to him would evaluate to TRUE, if at least one of all the possible tuples that d can take evaluates to TRUE (please tell me if this reasoning is okay)

Here is where things get messy, I don't understand what's actually doing that Universal Quantifier in NOT(PROJECT(x)), and why is it using x as a tuple in PROJECT in this part NOT(x.Dnum = 5), how we know that x is in PROJECT ? Is something about the first part with the Universal Quantifier? And why the Universal Quantifier is just bounding NOT(PROJECT(x))?


texts: Extract week number from character varying column where dates are in float values as well as in datetimestamp as well

texts:
I have a table in which the closure_date which is represented as a character varying column. Some of the dates are saved as "43684.5708564815" and also as "2019-05-24 18:51:17". Now I need to extract the week number from the column. I tried the following the query :

 SELECT closure_date,
 extract('week' from timestamp '1899-12-30' + interval '1 day' * cast(closure_date as  double
 precision)) as closure_week,
 FROM <table_name>
 LIMIT 10000

This query runs fine for values like  43684.5708564815 however result into an error while trying to extract the week_number from "2019-05-24 18:51:17" stating :

 ERROR:  invalid input syntax for type double precision: "2019-05-24 18:51:17"
 SQL state: 22P02

How to handle the error? Any suggestions?


texts: What's the best way to go about searching for short names?

texts:
I have a table that has CHAR in Korean for Soccer player names. All of these are under 12 characters, and while LIKE "%name%" works, Slight mistypes or variations don't work, and I've heard that LIKE isn't the fastest solution compared to something like Fulltext Indexes. I've also heard that the NGRAM parser works best with Korean/Japanese/Chinese characters, but it requires that I put in a title/body column; I only want to search for names, nothing else.

I currently have ~30k rows, and it won't grow more than ~45k. What's the best way to go about this?

Here's what I tried (after making an word index table in ngram):

...MATCH (name) AGAINST ('foo' IN NATURAL LANGUAGE MODE);

but it's not as precise as using LIKE, since it returns any word that's remotely connected to the query word.


texts: Store Mysql Index in separate .Ibd file?

texts:
Is there any option to store table index in separate .ibd file? something like file_per_table mode? Becuase my table is growing more than 200gb. Table .ibd file have index for that table also. so If index kept in another file, then table .idb file size will be reduced.

Thanks


texts: Removing InnoDB indexes causes database table almost not accessible and slow

texts:
I have a server with 32GB RAM. I have given almost 70% InnoDB buffer pool to it. I have table with almost 40 million rows. Recently, I tried to remove one of the indexes and replace it with another. My whole system could not work any more and it was not able to see my db.

I could see a lot of this error in my application:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
No operations allowed after statement closed.

Also I saw a lot of too "many file open" errors on my application due to this process.

I had to stop the database, truncate the table, and start afresh. Is this an InnoDB limitation? In future, what can I do to avoid this? What other limitations do I need to be aware of?

Is it not advisable to remove indexes when the size reaches big as few millions like this?

Is it a good reason for my to swap to TokuDB?

Below is my table description:

CREATE TABLE IF NOT EXISTS `mainData` (
  `mainDataID` int(11) NOT NULL AUTO_INCREMENT,
  `deviceID` int(11) NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `gpdateTime` datetime NOT NULL,
  `linkID` int(5) NOT NULL DEFAULT '0',
  `eventID` int(11) NOT NULL DEFAULT '0',
  `dataInsertDateTime` datetime NOT NULL,
  PRIMARY KEY (`mainDataID`,`dataInsertDateTime`),
  KEY `gpdateTime` (`gpdateTime`),
  KEY `linkID` (`linkID`),
  KEY `eventID` (`eventID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=50425561 ;

This was on a running production system with heavy inserts going in so that is why all almost halted. When tables reach a huge sum in million how to best to say remove and replace a new index?

My version is 5.1.73. I know kind of old; as you know CentOS goes for stability. What I did was removing the gpdateTime index via PHPMyAdmin and everything started to fall apart. My OS which I am running is Centos 6.8.


texts:
I have installed new MS SQL Web Edition on C Drive and I have also another D drive on server. Now I want to change database location, like when we make new database it should be make on D drive.

Can you please give me an idea how can I Do this.


texts: Mysql -Which Column table the index should be added

texts:
I have written this query (which returns only 67 records, which is fine)

The problem is with the query time, the Query took 0.0042 seconds which is slow, I heard that indexes can help fasten the query, shall I add indexes on UserID or OfficeID?

SELECT Distinct(u.UserID),
       u.Role,
       u.FirstName,
       u.LastName,
       u.Email
FROM   Users as u,
       Hires as h,
       Contractors as c,
       ContractorCompanies as cc
where  (u.UserID = h.User_ID AND h.Offices_OfficeID = 3)
OR     (u.UserID = c.User_ID AND c.Offices_OfficeID = 3)
OR     (u.UserID = cc.User_ID AND cc.Offices_OfficeID = 3)

Updated: When I added an index on UserID the Query took 0.0066 seconds.)


texts: I have database scheme for Microsoft Access. I'm interested how I can convert the scheme into an ER diagram? Is there any tool that can do this based on scheme relations?


texts: My SQL 5.6.4 DATETIME column milliseconds is not getting stored

texts:
Beginning from MySQL 5.6.4, milliseconds support is provided for date columns like DATETIME etc.

I just created a table to check if its working:

create table testdata(startime  DATETIME(5));

However, when I save some DATE(java.lang.Date) value using iBATIS framework it is ignoring milliseconds and storing:

2012-04-16 17:54:55.00000

Any idea how to resolve this issue?

When I use an SQL query like INSERT INTO testdata VALUES ('2012-04-16 17:54:55.32345') it stores milliseconds properly.  I want it to work via iBATIS.


texts: Are AD Groups the exact same as individual windows logins with explicit permissions?

texts:
Does any application requires a windows/domain login to be explicitly added to a SQL Server instance or by replacing the login with an AD group that belongs to the server and has elevated permissions I would achieve the same thing?

To clarify, I am referring specifically to application accounts (In this case we have an account for TFS (team foundation services)) and not actual "users" accounts.  Will any application fail if they can't find an explicit windows/domain user on the server?


texts: SQLite or CouchDB for Locational Data Mining Project?

texts:
I am currently designing a Data Mining Project where I am going to harvest rather large volumes of Twitter data in order to analyse locational data (geocoded tweets) and do some machine learning with it.

What I want to do: I'll have some scripts that run 24/7 on a small Samsung Netbook (<2GHz,1GB RAM, 200GB Disk), limited in frequency only by the query limit of the Twitter API. These scripts will save various sorts of data in a database, which in turn will later be used as a base for analysing data.

I am quite experienced in RDBMS, thus I also know their limitations. I just read about CouchDB and its ability to store JSON in so called documents - this would come in handy because the responses from the Twitter API are in JSON, and some of those strings are quite nested and complex.

On the other hand, I don't really want to miss relational functionality, since I have for example a table user which saves general data about a Twitter account and a table geo which saves place-time-Tuples which always reference a particular user.

For analysis, the content of geo will be used in any possible way - I have not yet thought about geospatial analysis in depth, but there will aggregation, distance calculation, all that sort of stuff. This might be done with CouchDB's reduce-Functions in Javascript, I read? If I used a SQLite DB, I would just stick to Python and do everything there.

I don't really know what is possible in CouchDB since I am really new to that concept. I just saw that it is easy to store JSON and that the structure of the database is not fixed at all, so I could easily introduce new types of data or destroy old ones (DROP COLUMN is not possible in SQLite). Also, since I know Javascript pretty well (actually better than Python), it might be easier for me to do analysis on the data.

What do you think? Is there a striking advantage in using NoSQL for doing that sort of thing or should I stick to what I can  do best?


texts: Key-range RangeI-N lock compatibility in SQL Server

texts:
The documentation states that the RangeI-N lock (Insert range, null resource lock; used to test ranges before inserting a new key into an index) is compatible with itself (see the compatibility matrix), so even though one transaction has obtained a RangeI-N lock on particular key another transaction can also obtain such a lock.

Further below, it says that

  When inserting a value within a transaction, the range the value falls
  into does not have to be locked for the duration of the transaction
  performing the insert operation. Locking the inserted key value until
  the end of the transaction is sufficient to maintain serializability.
  For example, given this INSERT statement:

  INSERT mytable VALUES ('Dan');

  The RangeI-N mode key-range lock is placed on the index entry
  corresponding to the name David to test the range. If the lock is
  granted, Dan is inserted and an exclusive (X) lock is placed on the
  value Dan. The RangeI-N mode key-range lock is necessary only to test
  the range and is not held for the duration of the transaction
  performing the insert operation. Other transactions can insert or
  delete values before or after the inserted value Dan. However, any
  transaction attempting to read, insert, or delete the value Dan will
  be locked until the inserting transaction either commits or rolls
  back.

Quoting another source - Microsoft SQL Server 2008 Internals: Transactions and Concurrency:

  For example, the RangeIn-Null lock is acquired when SQL Server
  attempts to insert into the range between keys in a session using
  Serializable isolation. This type of lock is not often seen because it
  is typically very transient. It is held only until the correct
  location for insertion is found, and then the lock is converted into
  an X lock.

My understanding is that this type of lock is during the process of identifying the range where the newly inserted key should be placed (I'm assuming that's what 'test the range' means). After this happens the lock is released, the new key gets inserted and an X lock is placed on it.

However I do not see why two RangeI-N locks are said to be compatible with each other. If transactions A and B both place a RangeI-N lock on the same key because they both want to insert a new key into the range and transaction A performs the insert first, then the location for key insertion determined by B might be already incorrect because the ranges have changed (A inserted a new value there). Could anyone explain that?


texts: How to force drop database in SQL Server 2008

texts:
I'm trying to force drop a database, but after dropping the database, when I try to recreate the database, I'm getting the error

  cannot create file C:\Program Files.....[databasename].mdf because it already exists

Here's my query to force drop the database

Use master;
ALTER database [databasename] set offline with ROLLBACK IMMEDIATE;
DROP database [databasename];

I understood that, the above query is dropping the database, but it's not deleting the .ldf and .mdf files. How to drop the database thoroughly?

A normal query

Drop database [databasename] ; //deletes the database completely, including the ldf and mdf's.

How to force drop a database, which also deletes the .mdf and .ldf files?


texts:
With Mysql 5.6.10, I have a table like this:
CREATE TABLE `es_user_action` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(11) unsigned NOT NULL,
  `company_id` bigint(11) unsigned NOT NULL,
  `work_id` bigint(11) unsigned NOT NULL,
  `action` tinyint(2) NOT NULL COMMENT '10, 20, 30, 40',
  `action_id` bigint(11) unsigned DEFAULT NULL,
  `apply_id` bigint(11) unsigned DEFAULT '0',
  `apply_display_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `action_time` datetime NOT NULL,
  `scout_time` datetime NOT NULL,
  `register_datetime_sorting` datetime DEFAULT NULL,
  `score` tinyint(2) DEFAULT '0',
  `is_pending` tinyint(2) DEFAULT '0',
  `apply_status` tinyint(2) DEFAULT '2' COMMENT '1: paid, 2: free',
  `has_response` tinyint(2) DEFAULT '0',
  `response_time` datetime DEFAULT NULL,
  `is_shown` tinyint(2) DEFAULT '1',
  `source` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_company_work` (`user_id`,`work_id`,`company_id`,`apply_id`,`source`),
  KEY `IDX_2` (`company_id`,`is_shown`,`apply_status`,`apply_id`,`work_id`,`action_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=436896779 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The full query I want to perform on the table would be the following:
SELECT
    `id` AS `seqNo`,
    `company_id` AS `companyId`,
    `work_id` AS `workId`,
    `action` AS `userActionType`,
    `action_time` AS `userActionDatetime`,
    `is_pending`,
    `apply_status`,
    `action_id`,
    `source`,
    `user_id` AS `userId`,
    `apply_display_id`
FROM
    `es_user_action`
WHERE
    `company_id` = 449664
    AND `is_shown` = 1
    AND `apply_status` = 1
    AND `apply_id` = 0
    AND `action_time` >= '2021-01-05 15:56:14'
-- AND `work_id` IN ( 1250160 )
ORDER BY
    is_pending ASC,
    action ASC,
    score DESC,
CASE
        source
        WHEN "entenshoku" THEN
        action_time
    END DESC,
CASE

        WHEN source <> "entenshoku" THEN
        action_time
END ASC
    LIMIT 100 OFFSET 0;

The table has around 15 million rows and the following query takes around 15 seconds. Can anyone help out? Thanks in advance.
UPDATED:
This is the explain query result:

UPDATED 4/2/2021:
Handler counts result:


texts:
Quick question guys!

Will there be any issues if I use the default instance (MSSQLSERVER) for both test and production environments? They are on separate physical servers. I'm using MS SQL Server 2012 which will run on clustered Windows Server 2012?

Thanks in advance and Happy New Year.


texts:
I have sales fact table (which has quantity - price measures) and dimensions (product - customer -time)
my table schema:

I need a query to get best selling product; then show list of amount paid for buying this product in each year and month like this example

Year    Month        AmountOfMoney
====    =====        =============
-       -            14040.00
1988    -            130.00
1998    March        130.00


texts: What is the best way to design one-to-many relationship with null value?

texts:
What is the best way to design one-to-many relationship with nullable FK?

I have two tables:

TableA:
  Id PK
  Name

TableB:
  Id PK
  TableARef FK
  Name

TableB has reference to TableA but this reference can be null. What is the best way to design that? I want to avoid nulls in my database (or maybe I shouldn't). It is a good practise to add join table to one-to-many relationship? Like this:

TableA_TableB:
  Id PK
  TableARef FK
  TableBRef FK

I know that in many-to-many is a good solution but I couldn't find enough data about this solution in one-to-many relationship.


texts: Is there are a way to restore System schema to it's original state?

texts:
I've installed Oracle 11g Database Enterprise Edition and my task was to import schemas. I'm totally new so I ran command:

imp system/password file=path.dmp full=y

And import everything (10 dumps, 150+ tables) into System schema. Could I restore this Schema to original state without reinstalling database? Or maybe you could delete directory with all of this tables and reinstall database, without harming listeners and tnsnames.ora?


texts: Log Shipping -Monitor Server Down - Copying happening but not restoring on secondary

texts:
I have set up a few log shipping instances, but I am not a DBA.

This weekend something happened for the first time - the log shipping Monitor Server went down temporarily due to electrical issues outside of my control.

When I looked at the primary and secondary machines during this time- both were up and running
and
the .trn files were still being copied from the primary to the secondary - as expected.
Yet, the secondary instance did not restore the .trn files at all. I received a notification:

  The log shipping primary database _________ has backup threshold of 60
  minutes and has  not performed a backup log operation for 82 minutes.
  Check agent log and logshipping monitor information.

Is this expected? The primary and secondary were both running - shouldnt the monitor be an "optional" SQL instance which should have no effect on the log shipping? It was preventing the secondary instance from applying the backups even though I later explicitly ran the restore job.
When the monitor server came back on - everything sorted itself out.

I have a lot to learn I know, any advice on whether I can check some settings or whether this is expected would be appreciated please.


texts:
When I execute the below query in mongodb shell, it gives me the number of records updated after the mentioned timestamp.

db.getCollection('oplog.rs').find({"ts":{"$gt":Timestamp(1582823397,2)}})

I wish to execute a similar query with mongodump command. Below is how I'm trying to do it.

query.json
{ "ts" : { "$gt" : Timestamp(1582823397,2) } }

mongodump -h 127.0.0.1:27018 -d local -c oplog.rs --queryFile=query.json -o - > D:/MongoDB/Backup/1582823
397_2_oplog.bson

The above command doesn't work and throws the below mentioned error.

Failed: error parsing query as Extended JSON: invalid JSON input. Position: 19. Character: T

Which seems valid as the query.json doesn't follow the JSON rules.

If I change the query.json file as below.

query.json
{"ts":{"$gt":"Timestamp(1582823397,2)"}}

The query doesn't return any records.

All of the above is a part of a shell script that is supposed to give me incremental dumps.

Reference of the script is taken from here. Referred this question as well.

I'm open to any other suggestions of getting incremental backups of MongoDB.

Adding in response to Wernfried's comment.

{
    "ts" : Timestamp(1582823446, 1),
    "t" : NumberLong(4),
    "h" : NumberLong(0),
    "v" : 2,
    "op" : "i",
    "ns" : "uis.healthCheck",
    "ui" : UUID("60d2d4de-48a3-46ec-a948-6fcb676ba52f"),
    "wall" : ISODate("2020-02-27T17:10:46.010Z"),
    "o" : {
        "_id" : 2,
        "status" : "Success2"
    }
}


texts:
I want to create a temporary table for the user result and the user_id variable, but if several users use the database, the table and variable will be recreated. How can I isolate database usage for multiple users?
I heard about connections, but based on the fact that there are only 150 connections by default, I thought they were for other purposes. I also heard about transactions, but I have nothing to roll back, so maybe this tool is not for me.
I also do not want to add different "id" for the variable "user_id" because it is confusing
What should I use?

Short example:

def create connection(user_id):
cursor.execute('SET @user_id = %s' ,(user_id,))
cursor.execute('CREATE TEMPORARY TABLE user_resulte AS (SELECT column FROM results WHERE column = @user_id)')

p.s. I'm a novice in databases :)


texts:
I need something to automatically stop my index maintenance job on Saturday night. How do I do that? How can I check automatically if my index maintenance job is still running and if it's running, stop it every Saturday night before 12 am?

Right now I do it manually using:

USE msdb
GO
EXEC dbo.sp_stop_job
N'The name of the job'

How can I automate stopping the job at a certain time ?


texts:
I need to implement comparing 2 tables by set of keys (columns of compared tables). By this comparing i should check equal records (equal = equal by set of defined keys (columns in these input tables)) and then insert these records to report table with some calculated columns.

Also i need to get the different (by keys) records in these 2 tables and insert them to result table. This pl/sql stored procedure works correct, but completely unusable on real datasets (~ 30 million entries in each input table).

CREATE OR REPLACE procedure ASUPR_OUP."6.3.2" (
    upd_date in date
)

as

-- filtered first input table
cursor c_end is select * from mon_end inner join dep on dep.dep = mon_end.depprod and dep.gpp_op = 1 and mon_end.division != 9 and mon_end.tehind not in (7,23) and (mon_end.depprod != mon_end.depcons or (mon_end.depprod = mon_end.depcons and dep.s_s = 1)) and mon_end.cyphdir in (1,7);

-- filtered second input table
cursor c_begin is select * from mon_begin inner join dep on dep.dep = mon_begin.depprod and dep.gpp_op = 1 and mon_begin.division != 9 and mon_begin.tehind not in (7,23) and (mon_begin.depprod != mon_begin.depcons or (mon_begin.depprod = mon_begin.depcons and dep.s_s = 1)) and mon_begin.cyphdir in (1,7);

is_found boolean;

begin

    for b_row in c_begin loop
        for e_row in c_end loop
            if (b_row.depprod = e_row.depprod) and (b_row.depcons = e_row.depcons) and
            (b_row.detail = e_row.detail) and (b_row.series = e_row.series) and (b_row.stk = e_row.stk) then
            begin
                if (b_row.trs != e_row.trs) or (b_row.trn != e_row.trn) or (b_row.kolsd != e_row.kolsd) or (b_row.applic != e_row.applic) then
                begin
                    if e_row.s_s = 0 then
                        begin
                            -- insert to result table
                        end;
                    else
                        begin
                            -- insert to result table
                        end;
                    end if;
                end;
             end if;
            end;
            end if;
        end loop;
    end loop;
    for b_row in c_begin loop
        is_found := false;
        for e_row in c_end loop
            if (b_row.depprod = e_row.depprod) and (b_row.depcons = e_row.depcons) and (b_row.detail = e_row.detail)
            and (b_row.series = e_row.series) and (b_row.stk = e_row.stk) then
                begin
                    is_found := true;
                end;
            end if;
        end loop;
        if is_found = false
        then
            begin
                if b_row.s_s = 0 then
                    begin
                        -- insert to result table
                    end;
                else
                    begin
                       -- insert to result table
                    end;
                end if;
            end;
        end if;
    end loop;
    for e_row in c_end loop
        is_found := false;
        for b_row in c_begin loop
            if (b_row.depprod = e_row.depprod) and (b_row.depcons = e_row.depcons) and (b_row.detail = e_row.detail)
            and (b_row.series = e_row.series) and (b_row.stk = e_row.stk) then
                begin
                    is_found := true;
                end;
            end if;
        end loop;
        if is_found = false then
            begin
                if e_row.s_s = 0 then
                    begin
                        -- insert to result table
                    end;
                else
                   begin
                        -- insert to result table
                    end;
                end if;
            end;
        end if;
    end loop;
end;

Can anyone suggest some way to boost performance of this procedure (i am using oracle 11g2) ? All input tables have indexes on used columns.


texts:
I have a user who will be away from the office for a while, but still wants to be able to insert a load of records to our MySQL database. The user will have a laptop, but no reliable internet connection so no VPN etc.

I'm aware that MySQL has a Master/Slave Replication feature, but unless I've misunderstood, this only provides read-only synchronization for the slaves.

I'm thinking that I can take a snapshot of the master database and copy it to a local server on the user's laptop. From this point onwards the local and master database will of course be out-of-sync. However, because the user only intends to add records while away and not delete or update records I'm thinking, perhaps naively, that a re-sync should be relatively easy to do with some SQL scripting.

Is this the right way to go? Are there any hidden dangers to be aware of?


texts: Optimizing queries against a set of tables that contain partitioned data

texts:
I have a third party program that creates and populates a series of tables to hold some historical data:

data_33 -- contains data for 12/01/11 - 12/05/11
data_34 -- contains data for 12/05/11 - 12/14/11
data_35 -- contains data for 12/14/11 - 12/20/11
data_36 -- contains data for 12/20/11 - 12/22/11
data_37 -- contains data for 12/22/11 - 12/23/11
data_38 -- contains data for 12/24/11 - 12/28/11
data_39 -- contains data for 12/28/11 - 01/02/12
data_(you get the idea)

Unfortunately, while these tables are indexed, they are not created with a partitioning scheme or partition function so I'm left to manually iterate through the tables. Now I want to allow the user generation of reports, but I'm unsure of how to best write a stored procedure to query against these tables in an optimized manner.

My first idea was to just UNION ALL all the tables into one gigantic temporary table, but I'm guessing this is going to be costly as a single table has a large number of records and even doing a SELECT * FROM data_33 is taking about 10 minutes to resolve. On top of that, I'll have to maintain this (materialized) view as the software generates new tables.

My second idea was to programmatically UNION ALL everything by grabbing all the tables in the database matching the results of SELECT name FROM sys.tables WHERE name LIKE 'data_%' but without any other limit I'm afraid this would still be extremely slow.

What would be the best way to optimize presenting this mass of data in a somewhat optimized manner?


texts: Remove some auto created indices from Entity Framework?

texts: I'm using Microsoft's Entity Framework.  It automatically creates indices for every foreign key, but in many cases, I can see from the index stats that these are never used for reading - they are only ever updated (which doesn't surprise me as I doubt I am ever actually doing a join on many of them).  My gut feeling is that I should remove these indexes as they are just slowing down updates (albeit probably not that significantly) and taking up space.  I like to try to keep things clean and lean.  Is there a good reason to keep them?


texts:
I  have to design a database for buses timetables.
Entities:

Bus        (idBus*)
Stop       (idStop*,stopDescription)
Line       (idLine*,lineDescription)
Position   (lat,lon)

Some constraints are the following:

Multiple Buses may operate for one Line (therefore BUS:LINE = N:1)
One Line has many Stops , and from one Stop are passing many Lines (therefore STOP:LINE = N:N)
One  Bus passes from many Stops and vice versa (therefore BUS:STOP = N:N)
A Stop has One Position (therefore STOP:POSITION = N:N)
A Bus has multiple Positions (therefore BUS:POSITION = 1:N)

E-R DIAGRAM

An example of modelling would be a bridging table for the STOP-POSITION relationship that would look like this:
STOP_POSITION(idStop*,lat,lon) whereas idStop is the Foreign Key.

In general:
If i have an idBus i would like to be able to get the associated idLine.
If i have an idBus and an idStop i would like to have info on the itinerary of the Bus (which is the next stop , time of arrival, direction)
If i have  an idBus and an idLine i would like to get the itinerary of the Bus(all the Stops from where the Bus will pass and their order)

Questions

The problem arise when considering the BUS-STOP relationship, because when i consider to know the id of the Stop and the id of the Bus then i will know a number of attributes like Direction,ID of NextStop, TimeOfArrival..
How should i model those attributes?
For example, every Bus is passing from multiple Stops and the progression is denoted by an attribute (e.g progressiveStop). How should i model this attribute?
Does it really make sense modelling the association of LINE-STOP?
Does it really make sense storing dynamic data in the database? I am referring to the BUS-STOP relationship.


texts: Postgresql MVIEW refresh from Oracle Materialized View Log

texts:
Is it possible for me to have Oracle MVIEW log in my source database and refresh a Postgresql database MVIEW using this MVIEW log? I know I can query the MVIEW log, I have done that. But since it is not a good practice to query a log, is it even possible to use an MVIEW log from Postgresql just like an Oracle MVIEW uses a log?

If that's not possible, is a dblink from Postgresql to Oracle possible?


texts: Should I backup a database with only temporary data?

texts:
I have a database, in SQL Server 2008, with only temporary data, but it has tables with a certain structure which I need. Should I back it up?

And if I should, should I make one copy of it or should I include it in the rest of the backup routine?


texts:
What is the best way to upgrade a MySQL version in a Linux Server

I have database around 14 GB running with 8 -15 GB ram(diffrent deployments) .At present MySQL version is 5.5 and I’m planning to move to 5.6 . I did similar activity in past for 5.1 to 5.5 upgrade .

Steps I followed is
1. Resolve any syntactical issue that can be faced in upgrader version of Mysql in code base  prior to upgrade.

at the day of upgrade
2.  Shut down the MySQL with application; take two backups (one with data and without data)
3. Uninstall and remove old MySQL instance and install new MySQL instance.
4. Import backup without data first and with data second and go live again.

I experience that during above process, it took very long time for importing backup file to database as some of our production servers run with less memory and during this remotely increase the risk as well.
This is the only best way I know, is there a best approach I can fallow to upgrade MySQL version in future with very less time  . Appreciate your help . Please note that I do not have facility to get another machine to do this, so I have to do in same production server where justifiable downtime is acceptable.


texts: Select where value in row is different and update

texts:
I have a large table that includes rows like the following:

Query:

select *
from cartographic_text_vm
where fid = 'osgb5000005104723440'
order by ckpt;

Results:

-[ RECORD 1 ]---------------+---------------------------------------------------------
change_history              | 2012-09-05,New
descriptive_group           | Buildings Or Structure
descriptive_term            |
feature_code                | 10026
fid                         | osgb5000005104723440
cartographic_text_version   | 1
make                        | Manmade
physical_level              | 50
physical_presence           |
text_render_anchor_position | 4
text_render_font            | 1
text_render_height          | 2.125
text_render_orientation     | 0
text_string                 | (Secondary)
theme                       | Buildings
version_date                | 2012-09-06 00:00:00
primary_key                 | 8240079
partition_key               | 2
random_number               | 9
geom                        | 0101000020346C000085EB51B8486B1B417B14AE47E9AFFB40
ckpt                        | 1
end_ckpts                   | {7}
transact_status             |

-[ RECORD 2 ]---------------+---------------------------------------------------------
change_history              | 2012-09-05,New,2015-10-25,TextChange
descriptive_group           | Buildings Or Structure
descriptive_term            |
feature_code                | 10026
fid                         | osgb5000005104723440
cartographic_text_version   | 2
make                        | Manmade
physical_level              | 50
physical_presence           |
text_render_anchor_position | 4
text_render_font            | 1
text_render_height          | 2.125
text_render_orientation     | 0
text_string                 | (secondary)
theme                       | Buildings
version_date                | 2015-10-26 00:00:00
primary_key                 | 8240079
partition_key               | 2
random_number               | 124
geom                        | 0101000020346C000085EB51B8486B1B417B14AE47E9AFFB40
ckpt                        | 7
end_ckpts                   | {61}
transact_status             |

-[ RECORD 3 ]---------------+---------------------------------------------------------
change_history              | 2012-09-05,New,2015-10-25,TextChange,2016-04-04,Modified
descriptive_group           | Buildings Or Structure
descriptive_term            |
feature_code                | 10026
fid                         | osgb5000005104723440
cartographic_text_version   | 3
make                        | Manmade
physical_level              | 50
physical_presence           |
text_render_anchor_position | 4
text_render_font            | 1
text_render_height          | 2.125
text_render_orientation     | 0
text_string                 | (secondary)
theme                       | Buildings
version_date                | 2016-04-05 00:00:00
primary_key                 | 123465 <- needs to be changed to 8240079
partition_key               | 2
random_number               | 135
geom                        | 0101000020346C0000AE47E17A446B1B41C3F5285CE7B0FB40
ckpt                        | 61
end_ckpts                   |
transact_status             |

I want to search for rows that have the same value for fid, but different values for primary_key. The database has a versioning extension enabled, so it's important that each version of a fid has the same primary_key.

When you look at the three records above, they are all in different checkpoints as denoted by the value in the ckpt field. The lowest number is the original version of the record, all of the records that have a higher ckpt value are modifications of that original record. I want to update any records that have a different primary_key from the original record to that value. As you can see in Record 3, the primary_key is 123456 which needs to be updated to the primary_key in record 1.

Any pointers in how the SQL should look?


texts: Backing up a MySQL server with mixed character sets

texts:
This may sound like a really simple question, but I've not found a straight answer with a lot of googling!

I have a MySQL database server which has databases with various different character encodings (largely a mix of latin1 and utf8)

Are there any recommendations when using mysqldump to back up the the entire server (one db at a time), when for each individual database I will not know what the character set is that is being used?

(I have a bash script which gets all the DBS on the server, and then runs mysqldump individually on each one - copied below)

I know I can set the default encoding to use with mysqldump by specifiying that as an argument to the mysqldump command - should I just be setting that to UTF-8? Will that be OK for the latin encoded DBs?

#!/bin/bash
mkdir `date -I`;
dblist=`mysql -h myhosthere -u myuser -pmypassword -e "show databases" | sed -n '2,$ p'`
for db in $dblist; do
   mysqldump -h myhosthere -u myuser -pmypassword $db | gzip --best > home/user/`date -I`/$db.sql.gz
done;


texts: Mysql Insert taking too long. Table need optimzation

texts:
I have around 60 tables and a video table with over 600,000 rows. with 4 Btree and 2 FULLText Indexes and have around 30 Columns.

Server Specs are high, over 30G of Ram, Intel Xeon Processor.

Mysql configs are here

[mysqld]
bind-address = 10.0.0.128
tmpdir=/var/tmp/mysql
innodb_file_per_table=1
local-infile=0
max_connections = 8192
max_user_connections = 8192
key_buffer_size = 256M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 2M
table_cache = 16384
thread_cache_size = 384
wait_timeout = 15
connect_timeout = 15
tmp_table_size = 1024M
max_heap_table_size = 1024M
max_allowed_packet = 64M
net_buffer_length = 16384
max_connect_errors = 1000
thread_concurrency = 32
concurrent_insert = 2
read_rnd_buffer_size = 8M
bulk_insert_buffer_size = 8M
query_cache_limit = 64M
query_cache_size = 1024M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
max_write_lock_count = 16
innodb_buffer_pool_size=2G
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=16
innodb_flush_method=O_DIRECT

slow_query_log=0
long_query_time=5
slow_query_log_file=/var/lib/mysql/slow.log

log-bin=mysql-bin
server-id=1

expire_logs_days = 7
max_binlog_size = 512M

[mysqld_safe]
open_files_limit = 58192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 384M
sort_buffer = 384M
read_buffer = 256M
write_buffer = 256M

This query

INSERT into video (file,userid) VALUES (11223,1);

is taking around ~4 minutes .

INSER into users (username,email) VALUES ('test','test@test.tld');

Takes 0.00s

I seriusly need some expert advice on Table optimzation. Things I have done wrong from start

Didn't plan the table properly
Added few indexes after 200,000 rows crossed.

Whats the best solution?


texts:
I often see queries written with DATEADD and DATEDIFF in the WHERE clause to define a range, flatten datetime to 0 hours, or find the last day of a month or year, but I don't understand how all the parts work.

For example, this will find dates from the start of the current day, to the start of the the day 30 days ago.

SELECT *
FROM tbl
WHERE datecol >= DATEADD(DAY, DATEDIFF(DAY, 0, GETUTCDATE()), 0)
AND datecol   <  DATEADD(DAY, DATEDIFF(DAY, 0, GETUTCDATE()), -30);

What do all the different parts of this accomplish?


texts: Drop partition scheme with orphan indexes

texts:
I have a partition scheme in my SQL Server database that I am trying to drop. This scheme was used by 2 indexes, which I deleted.

I can't delete the scheme, since it appears to still be used by the indexes, even though they don't exist anymore.

Running the following query:

select object_name(i.object_id), i.name, i.index_id
FROM   sys.indexes i
JOIN   sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
WHERE  ps.name = 'CallScheme'

I get the names of the 2 tables that had the indexes, but the index names are NULL (and index id is 0).

How do I get rid of these indexes so that I can drop the partition scheme?


texts:
I am reading the description of the datetime datatype from Sybase ASE 15.7 documentation:

  datetime columns hold dates between January 1, 1753 and December 31, 9999. datetime values are accurate to 1/300 second on platforms that support this level of granularity.

I find the above very confusing. First of all, this is the only data type for which the caveat "on platforms that support this level of granularity" is added. What exactly does this mean and how do I find if my platform is one of those that "support this level of granularity"?

Moreover, it is not clear to me what being able to accurately store 1/300ths of a second effectively means. I am accessing the database using JDBC for which the only plausible type is java.sql.Timestamp. This type allows me to retrieve up to the precision of nanoseconds. But given that division by 300 requires infinite digits in the decimal system in the general case, effectively a fractional number of nanoseconds (with infinite decimal digits) are needed to hold a value expressed as 1/300 of a second. So, this means that I am not able to obtain the value stored in the server without losing some precision, however negligible.

Finally, when I execute the following query:

SELECT convert(char(32), submission_date, 139) FROM some..table

I see values like the following:

Jan  6 2014 12:36:12.420000
Sep 12 2013 13:44:57.100000
Sep 10 2014 13:47:02.240000
Sep 10 2014 13:47:07.850000
Sep 10 2014 13:47:13.346000
Sep 10 2014 13:47:19.033000
Sep 10 2014 13:47:24.533000
Sep 10 2014 13:47:30.030000
Sep 10 2014 13:47:35.636000
Sep 10 2014 13:47:41.136000
Sep 10 2014 13:47:46.750000
Sep 10 2014 13:47:52.240000
Sep 25 2014 09:01:18.426000

These values seem to indicate that only whole thousandths of a second are kept (not 1/300ths of a second - which would require a fractional number of thousandths). If it were the case that the server internally stores the values "accurately to 1/300 second" I would expect the conversion to a decimal notation to use all available decimal places (except for the edge cases of 3/300 secs, 30/300 secs, 150/300 secs and a few others which do not require an infinite amount of digits in the decimal system).


texts:
I could not find any documentation about pgbouncer log, I'm assuming it has the same meaning as the SHOW STATS ?

req/s - number of requests since last stat period
in b/s - Bytes per sec received ?
out b/s - Bytes per sec sent ?
query - Total query duration in micro secs ou avg query duration in micro secs ?

I need to better understand this query column to implement reliable log monitoring.

Another issue I'm facing is the fact that I seldom see more than 0 requests on log lines and most of the times the query value is greater than 10s.


texts:
I had a PostgreSQL database with loads of scanned documents, as a document bytea column in the table scans, with hundreds of thousands of documents that was large and inconvenient to backup. Also, there was a high rate of duplication, not major but 5-10%.

I wanted to store those documents outside of the database so they could be backed-up via incremental tar, and reduce the size of the pg_dump database backup.

I came up with a solution I want to share below, using plperlu. Any comments or further optimisation ideas will be appreciated!


texts: Cannot DROP/ALTER a VIEW because other objects depend on it

texts:
I need to ALTER a VIEW in order to add an additional field but I am unable to alter/drop it because other objects (several functions and views) depend on it. Is there any way to alter the view without having to drop and create the existing functions and views that depend on it? Do you have any suggestion?
Thanks.


texts: Display dates not in ascending for To_Char

texts:
can i know why and how i can solve the problem whereby if i use To_Char it does not sort the date in ascending. Below is my code

    select distinct To_Char (attendance_Date, 'dd/mm/yyyy')
    from DIT_2010MAR_CIT4114A_FYP1_NO
    order by 1 asc

result obtained is

DATE
----------
01/05/2016
02/05/2016
03/04/2016
04/04/2016
05/04/2016
06/04/2016


texts: Permission denied while trying to delete even when delete is granted

texts:
I recently had this issue where I created a database user and gave delete and update permission. Yet, I was getting permission denied on a delete query.
Create user: create user username password 'whatever';
Grant: grant update, delete on table to username;
Query which gave permission denied:
delete from table
where condition1 and
condition2 and
condition3


texts: Proper normalization for an Orders table--whether to split or no

texts:
Each order has a unique SalesOrderNumber. An order is made up of various PartID numbers, each with an associated OrderQuantity number.

I have the following table currently in my model:

I'm wondering if this table should be split into two tables as such:

This would leave the Order table with only one row for each SalesOrderNumber, but I'm not sure if this is necessary from a normalization point of view. It seems that either arrangement should meet 2NF. I'm still a bit shaky with 3NF and BCNF however.

Here is what a sample from the table might look like now, with only relevant columns displayed for a made up SalesOrderNumber of 12345:

SalesOrderNumber PartID OrderQuantity
12345            1a     1
12345            2b     2
12345            3c     43


texts: What is MS Access ODBC SQL syntax for a table with AutoIncrement and Memo columns?

texts:
I am migrating a full framework .NET app to core. It used ADOX to create an MS Access database and  tables. It used OLE DB to populate data. In ADOX, the data type was ADOX.DataTypeEnum.adInteger and the syntax from c# was:
fld.Properties["Autoincrement"].Value = true;
fld.Properties["Seed"].Value = 1;
fld.Properties["Increment"].Value = 1;

For memo, ADOX data type was ADOX.DataTypeEnum.adLongVarWChar.
Since we can no longer use ADOX under .NET Core, I have to use SQL DML through ODBC. When I attempt to create a table using syntax similar to
create table t (
  col1 int not null identity(1,1),
  col2 varchar(512)
)

I get a syntax error. When I try to adjust this for Access according to this and other related articles in the same documentation tree, I also get syntax error.
create table t (
  col1 counter,
  col2 text
)

What is the correct syntax for these columns that is compatible with .NET Core and MS Access ODBC driver?


texts: Alternative way to compress NVARCHAR(MAX)?

texts:
I am trying to compress some tables that have NVARCHAR(MAX) fields. Unfortunately, the row and the page compression do not have the desire impact (only ~100/200 MB saved for 20 GB table). Also, I am not able to apply column store and column store archival compressions because they do not support compression of NVARCHAR(MAX) fields.

Can anyone tell if I have any alternatives here?

I also guess the row and page compression do not have effect because the content of the NVARCHAR(MAX) columns is unique.


texts: Bulk grant execute permissions for scalar functions & stored procedures

texts:
We have a situation where we have restored a large number (50+) of MSSQL2008 databases from backup and most if not all of them require that a user is granted EXECUTE permissions on scalar functions and stored procedures in the dbo schema.

I've seen bits of documentation with T-SQL queries to grant execute permissions on single stored procedures or scalar functions, but I need to do this for multiple DBs and multiple stored procedures.

Unfortunately, my experience with MSSQL is limited so I have no idea where to begin with this.

Any help would be awesome.


texts: Match multiple components of a subquery CTE in PostgreSQL

texts:
I've got an app that grants access to users in varying different ways, and I'm attempting to write a query that determines access in all the different ways, but is readable, by breaking the access out into CTEs.

This is the basic pattern I'm talking about:

WITH user_allowed_data1 AS (
    SELECT u.id user_id, c.id content_id FROM …
),
user_allowed_data2 AS (
    SELECT u.id user_id, c.id content_id FROM …
)
SELECT u.name, c.name FROM user_table
LEFT OUTER JOIN content_table ON 1=1
WHERE u.id, c.id in (SELECT user_id, content_id FROM user_allowed_data_1)
OR u.id, c.id in (SELECT user_id, content_id FROM user_allowed_data_2)

So, two questions:

Is there a better/faster/cleaner way to think about this query?
Is there a way to match multiple fields pulled out of a subquery in a where clause like that?


texts:
My server's default collation is Latin1_General_CI_AS, as determined by this query:

SELECT SERVERPROPERTY('Collation') AS Collation;

I was surprised to discover that with this collation I can match non-digit characters in strings using the predicate LIKE '[0-9]'.

Why in the default collation does this happen? I can't think of a case where this would be useful. I know I can work around the behavior using a binary collation, but it seems like a strange way to implement the default collation.

Filtering digits produces non-digit caracters

I can demonstrate the behavior by creating a column that contains all possible single-byte character values and filtering the values with the digit-matching predicate.

The following statement creates a temporary table with 256 rows, one for each code point in the current code page:

WITH P0(_) AS (SELECT 0 UNION ALL SELECT 0),
P1(_) AS (SELECT 0 FROM P0 AS L CROSS JOIN P0 AS R),
P2(_) AS (SELECT 0 FROM P1 AS L CROSS JOIN P1 AS R),
P3(_) AS (SELECT 0 FROM P2 AS L CROSS JOIN P2 AS R),
Tally(Number) AS (
  SELECT -1 + ROW_NUMBER() OVER (ORDER BY (SELECT 0))
  FROM P3
)
SELECT Number AS CodePoint, CHAR(Number) AS Symbol
INTO #CodePage
FROM Tally
WHERE Number >= 0 AND Number <= 255;

Each row contains the integer value of the code point, and the character value of the code point. Not all of the character values are displayable - some of the code points are strictly control characters. Here is a selective sample of the output of SELECT CodePoint, Symbol FROM #CodePage:

0
1
2
...
32
33  !
34  "
35  #
...
48  0
49  1
50  2
...
65  A
66  B
67  C
...
253 ý
254 þ
255 ÿ

I would expect to be able to filter on the Symbol column to find digit characters using a LIKE predicate and specifying the range of characters '0' thru '9':

SELECT CodePoint, Symbol
FROM #CodePage
WHERE Symbol LIKE '[0-9]';

It produces a surprising output:

CodePoint   Symbol
48  0
49  1
50  2
51  3
52  4
53  5
54  6
55  7
56  8
57  9
178 ²
179 ³
185 ¹
188 ¼
189 ½
190 ¾

The set of code points 48 thru 57 are the ones I expect. What surprises me is that the symbols for superscripts and fractions are also included in the result set!

There might be a mathematical reason to think of exponents and fractions as numbers, but it seems wrong to call them digits.

Using binary collation as a workaround

I understand that to get the result I expect, I can force the corresponding binary collation Latin1_General_BIN:

SELECT CodePoint, Symbol
FROM #CodePage
WHERE Symbol LIKE '[0-9]' COLLATE Latin1_General_BIN;

The result set includes only the code points 48 thru 57:

CodePoint   Symbol
48  0
49  1
50  2
51  3
52  4
53  5
54  6
55  7
56  8
57  9


texts: Meaning of sqlstate in activity monitor table?

texts:
I'm investigating phenomena for a transaction and created an activity monitor.
db2 "create event monitor act_stmt for activities write to table manualstart"
db2 "set event monitor act_stmt state = 1"

A certain query is used to check whether the phenomena I'm looking for has appeared and indeed it does. However, when I look at the sqlstate for this query is says '02000' which I interpret as "Not found", at the same time it says ROWS_RETURNED = 1
I would have expected sqlstate to be '00000' for the query that returned a row. Am I missing something here?
db2 "select sqlstate, sqlcode, rows_returned
     from ACTIVITY_ACT_STMT x
     join ACTIVITYSTMT_ACT_STMT y
         using (appl_id, uow_id, activity_id)
     where stmt_text = 'SELECT 1 from EVENT.ADMISSION_EVENT
                        where admission_event_id = ?'
      and ROWS_RETURNED = 1"

SQLSTATE SQLCODE     ROWS_RETURNED
-------- ----------- --------------------
02000            100                    1

As far as I can tell, the only thing that distinguishes a query that returned a row from one that does not is ROWS_RETURNED. Am I overlooking something or is that the way to tell if a query returned rows or not?
I tried to simplify the scenario by asking two questions and got a similar, but the opposite result (both queries have sqlstate '00000'):
db2 "select tabname from syscat.tables where 1=0 fetch first 1 rows only"
db2 "select tabname from syscat.tables where 1=1 fetch first 1 rows only"

db2 "select sqlstate, sqlcode, rows_returned, substr(stmt_text,30,20)
     from ACTIVITY_ACT_STMT x
     join ACTIVITYSTMT_ACT_STMT y
         using (appl_id, uow_id, activity_id)
     where stmt_text like 'select tabname from syscat.tables
                           where 1=% fetch first 1 rows only'"

SQLSTATE SQLCODE     ROWS_RETURNED        4
-------- ----------- -------------------- --------------------
00000              0                    1 bles where 1=1 fetch
00000              0                    0 bles where 1=0 fetch

]$ db2level
DB21085I  This instance or install (instance name, where applicable:
"db2inst1") uses "64" bits and DB2 code release "SQL11055" with level
identifier "0606010F".
Informational tokens are "DB2 v11.5.5.0", "s2011011400",
"DYN2011011400AMD64",
and Fix Pack "0".
Product is installed at "/opt/ibm/db2/V11.5".


texts: I'm looking for steps to reset my master-master replication after an error

texts:
I'm using mysql 5.7.30 with GTID enabled on two nodes (rhel 7.8) configured for master-master replication.  In the event of an error where one or both of my slaves stop running I would like some suggestions, please, for steps to reset/repair the database(s) that could be placed in a script.  I understand that there are many possible reasons for the slaves to stop but in your suggestion consider the most common issue encountered being primary key duplication.
Perhaps someone can provide steps similar to this link, data resync.  Those directions didn't work for me as the slave didn't start as was suggested but in defense of the link, it didn't specify the version of mysql being used or any other specs.
In general the steps I would like to use would be something like:

stop all apps using the database
[ possibly use mysqldump to salvage data, if I can ]
stop both mysqld services
edit/remove files under /var/lib/mysql on both nodes.    <- Admin Magic Requested Here
start both mysqld services, both slaves are running.
restore data

Please not that I am less concerned with data integrity than I am about getting the system backup in an automated way.  There will not be a DBA on hand to run commands by hand.
-John


texts: migrate data from production to development db

texts:
Sometimes I need to copy interview bookings made on our production server to test some I have the same data in order to reproduce bugs / demonstrate features with live data.

I know the easiest way is probably to restore the a backup of the live server but its not always possible. Takes ages to restore on my crappy pc. People arent happy when I copy a 3gb file over the network.

I was wondering is way I can write a query to copy the relational data over from a readonly copy of production to my dev db.

for example table a has a relation with b. Table B may also have relations to other tables. I want to copy the data from table A and also have any related records  copied as well.

we use sql server 10.50.1600


texts: Can you get the "CREATE" statement for a domain in PostgreSQL?

texts: In PostgreSQL, for many objects like functions (among other things), there exists a way to get the CREATE command for that specific object (with functions you can use pg_get_functiondef(oid), for example). Is there any way to do this for domains? It must be possible, since pgAdmin III seems to be able to get these statements for domains, but I haven't been able to find how this is done.


texts: CPU Spiking But Top 10 Queries Are Not Resource Intensive

texts: This is a strange issue that I have been trying to trouble shoot.  For the past few months our production CPU will just spike despite the load that I am running on it.  Specifically, the spiking is usually occurring at night (8:00 PM) when there are not a lot of users in the system.  I am using Red Gate Monitor and Red Gate reports that the instance CPU is only around 33% and the host CPU is at 100%.  Thus, I am not sure if this is a SQL Server issue or a VM issue.  I looked at the top 10 queries at this time and each are running under a second.  Should I be looking at waits too?  We are using SQL 2012 on a virtual machine (VMWare).


texts: How does one select the histogram / scatterplot / etc. Data Viewers in SSIS 2012

texts:
I have a data flow task in SSIS 2012 and have created a flat file source and an OLE DB connection destination.  I can select the data flow path between them as in earlier versions of SSIS and I can right click and select the Data Flow Path Editor.

The Data Viewer option is available as in past versions, but there is no add button and I cannot determine how to add histogram/ scatterplot /etc. data viewer.

All I can seem to configure is a sample of the data that actually moved across.


texts: MariaDB Master / Slave failover / failback automation

texts:
Good day all, I'm hoping someone might be able to point me in the right direction.

I'm working on a new dual data center project, and I'm trying to come up with a plan for failover (for patching and other maintenance) and failback. The databases / data centers will act as Primary / DR. Of course DR would fall into this plan. There is no viable 3rd pop for an arbitrator, so clustering isn't in the cards. Each database will be running MariaDB (10.3 or columnstore).

The plan includes running ProxySQL on each application to handle communication with the database, and failover from that perspective. It's my understanding that it can leverage "READ-ONLY" to recognize which database is primary, which seems intuitive.

On the database side I hope to configure Master/Master replication to ease the transition between data centers, and hope to utilize the read only options to control data flow as mentioned previously. And this is where I'm stuck as I need the process to be as easy as possible so that non-DBA's can facilitate the failover without much interaction.

Is there an out of the box, community (free), utility that can perform the necessary steps to effectively "swap" masters and allow for unattended recovery? If not, can anyone point me toward a sample process/script that might achieve the same results?

Thanks in advance!


texts: Oracle does not use transitive closure to add additional predicate


texts: Oracle Exchange partition from non partitioned table

texts:
We have table name Summary with 86 GB data. This table do not have partition/sub-partition .We want to move the data from the summary table to a composite partitioned table (range partition on entry_date column and hash partition on users_codes column ) .

  oracle doesn't allow to exchange data with non partitioned table

So we are doing the following approach.kindly let me know if this is correct approach.

Create a temporary table summary_temp with range partition on entry_date column ,then exchange the data from summary table to partitioned table(summary_temp) in part_hist.
Alter the summary_temp table to add sub partition (hash partition on users_codes) column
exchange the partition and sub_partition data to the another empty destination table which is having the same partition and sub partition.


texts: MySQL server has gone away obstructing import of large dumps

texts:
I am trying to import a large sql dump (2GB) to my local mysql on my mac. I have been able to do this in the past (I was using MAMP), but now I get a
    ERROR 2006 (HY000) at line 7758: MySQL server has gone away
everytime I try to import the dump. The database contains innodb tables.

I tried copying the my-innodb-heavy-4G.cnf file to my my.cnf to see if those settings would help, but no luck.

Any ideas on what to tweak?

I am using the "Mac OS X ver. 10.6 (x86, 64-bit), DMG Archive" from here: http://dev.mysql.com/downloads/mysql/


texts: SQL SERVER - Linked Server and query performance

texts:
I have a performance problem related to SQL Server/linked servers/views.
I hope you can help me understand what is the best way to do what I want =).

I have a database K with 3 linked servers L1,L2,L3 to 3 databases X,Y,Z.
In X,Y,Z i have, respectively, 3 views called V1,V2,V3.
I want to query the union of V1,V2,V3 by database K with linked server L1,L2,L3.

After some testing, this is the situation:

In SSMS, if I run this pseudo query SELECT * FROM (L1.V1 u L2.V2 u L3.V3) WHERE some filters the performance is really great
If I create a view VK in database K, which contains the union of the three views and then i run the query SELECT * FROM VK WHERE some filters the performance is worse than case 1

Questions

Why is the performance so different?
How can I improve performance in case 2?

I'm interested in improving performance in case 2 because I need a view to map with nHbinernate in our software...
Thanks in advance, regards
UPDATE AFTER JOHN ALAN's POST
Ok, I try but with no results.
I'm not a DBA and my skills on DB configuration is really limited.
Can we proceed step by step?

On remote server (called Y) I created a new account (called linkedserver) by security->logins->new login. I select login name then sql authentication and I choose a password. for deafault database I select master. In server roles tab I select public. in User mapping tab I select the databases involved in remote queries and, for each of them, I select db_ddladmin and public role. Then, for each database involved in remote query, I checked the effective permission for linkedserver user and there are a lot of ALTER and a lot of CREATE permissions but not SHOW PLAN (then I selected this one too).

On the database server (called X) where the linked server to Y exists, I created a linked server (called L1). In security TAB, I selected local user sa and remote user linkedserver with its password.

When i run the query across L1 without a VIEW I have good result, If I put the query in a VIEW low performace, nothing have changed...
I think I did some step wrong, but I don't know where...
FOR BETTER CLARITY
This is the query I run without a view using linked server:
select * from
(
    select * from dolph.agendasdn.dbo.vistaaccettazionegrp
    union
    select * from dolph.acampanet.dbo.vistaaccettazionegrp
    union
    select * from municipio.dbnet.dbo.vistaaccettazionegrp
) a
where cognome = 'test' and nome = 'test'

into the view I put only this code
    select * from dolph.agendasdn.dbo.vistaaccettazionegrp
    union
    select * from dolph.acampanet.dbo.vistaaccettazionegrp
    union
    select * from municipio.dbnet.dbo.vistaaccettazionegrp

then I called select * from VIEW where cognome = 'test' and nome = 'test'
So..

1st case 0-1 seconds.
2nd case >15 seconds...

I think this is absurd!
EXECUTION PLAN
The execution plan with plain query, without the use of a view:

the execution plan using the view:


texts: Can't convert this type of ER diagram into SQL or relational tables

texts:
I have stumbled upon a problem during database design. Let me explain what it is all about:

Contract has some simple attributes that describe him, and may have annexes and dynamics of payment.

Therefore I have decided to make main table Contracts, and placed simple attributes as columns.

Dynamics of payment is a complex attribute of Contracts table. It can have none, one or multiple values. Searching through the Internet I have learned that this is called multivalued attribute, and have found this example that seems to illustrate my case very well ( Dynamics of payment is equivalent to the Hobbies table in the linked example ).

As for Annexes, it is a complex attribute of Contract. Contract can have many of them, one or none.

Annex has exactly the same simple attributes as contract, and can also have Dynamics of payment. Annex' relationship with Dynamics of payment is the same as the relationship between Contract and Dynamics of payment.

To sum-it-up, Annex and contract have everything the same, the only difference is that Annex is a complex attribute of Contract.

Using this as a reference, I have made the sketch of my ER diagram:

This is the first time I see this type of relationships so I ask the community to help me convert this ER diagram into SQL or relational tables.

I apologize for not providing more info, if you have further requests please leave a comment and I will update my post immediately.

Thank you for your understanding and help.

Best regards.


texts: While restoring copy of SQL Server 2008 backup file in SQL Server 2008 R2 degraded performance

texts:
I am trying to upgrade our SQL Server version from SQL Server 2008 to SQL Server 2008 R2. But when I restored a SQL Server 2008 backup file in SQL Server 2008 R2, it's significantly degraded the performance of stored procedures. I took a full back up from old SQL Server 2008 and restored it in new SQL Server 2008 R2. I checked the execution plan of several stored procedures to make sure no indexes are missing, but everything is same.

Can anyone please give me an advice about it if I need to do any further steps to get better performance on it?

Thanks,
Dony


texts:
I like backupninja because of simplicity. Unfortunately it started giving me this problem:

  Warning: mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */
  dead.letter FROM GLOBAL_STATUS': The
  'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the
  documentation for 'show_compatibility_56' (3167)

It looks like this can be fixed with set @@global.show_compatibility_56=ON. Unfortunately that doesn't work on my MariaDB instance:

  Welcome to the MariaDB monitor.  Commands end with ; or \g. Your
  MariaDB connection id is 73 Server version: 10.1.26-MariaDB-0+deb9u1
  Debian 9.1

  Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

  Type 'help;' or '\h' for help. Type '\c' to clear the current input
  statement.

  MariaDB [(none)]> set @@global.show_compatibility_56=ON;
  ERROR 1193 (HY000): Unknown system variable 'show_compatibility_56'

What else can I try to resolve this?


texts:
This is my SQL syntax and (this is only a snippet unfortunately I am stuck running dynamic SQL in this instance) - but my issue is at the very end of my statement when I do the Select @numreturned what is returned is the SQL statement NOT the actual number that is returned when I run the Exec(@numreturned)

What must I alter in order to return the NUMBER instead of the SQL Statement?

Declare @numreturned varchar(max),
@fulllocation varchar(max) = 'nagadishi.mirama.dbo.Humizan'

Set @numreturned = 'Select Count(*) FROM '+@fulllocation+' '
                   +'WHERE ([Nishan] IS NULL) '

Print @numreturned
Exec(@numreturned)

Select @numreturned


texts:
Microsoft SQL Server Web Edition (64-bit)

Version 10.50.1600.1

SQL Server 2008 R2

I have noticed that we are getting a lot of execution plan re-complilcations - first executions quite slow, subsequent calls very fast as you would expect.

However, periodically (after around 10 minutes or so), performances returns to that of the first query i.e. execution is slow again, then fast.

I have read many articles on what could cause this; I have changed auto update statistics to off as suggested.

What I have noticed is that syscacheobjects will build up nicely, with usecounts into the 1,000's - then suddenly the table will be empty, causing all the queries to recompile.

Does anyone know why the cache may be getting flushed like this?

Many thanks

Paul


texts: How mysqldump replaces special characters during dumping records?

texts:
i am trying to dump a particular row using java.

So that i retrieve the data by using record sets and i manually written sql script code by appending the dynamic records.

But my problem is i could not write the script for blob data fields.

Because there are some special characters are present inside blob type fields.

i need to know how mysqldump.exe is replacing special characters ?

If i know the detail means i can replace those characters as same as mysqldump process.


texts: Accidentally Deleted SQL Server Log Reader Service Account

texts:
Uh, I accidentally deleted the Security Login in my SQL server that allows for the reading of log files.

Now, I can no longer see any of the log files in my SQL Server Logs node of SMSS:(

What account is it so that I can add back the login?

I think it started with "BUILT IN" or something.