“Portable” MySQL and logging – Database Administrators Stack Exchange

I’m using “portable” Mysql(not installed and should be started by batch file)
“@echo off
@color B
echo Starting the MySQL
mysql5binmysqld.exe –console –max_allowed_packet=64M”

it has inside 2db, and I want to log any queries for only one db.
But I can’t find the config(lol) It has 5 configs inside: my-huge, my-innodb-heavy-4G, my-large and so on.
What config should I edit?

LVE Manager MySQL Limits – Database Administrators Stack Exchange

Stack Exchange Network


Stack Exchange network consists of 177 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Postgresql values not inserting – Database Administrators Stack Exchange

Hi I am trying to add following values to my table, but it gives following error,

ERROR: syntax error at or near “::” LINE 1:
…tion))/nullif((s_totalnorevws),0),’FM99,999,999′))::characte…

insert into testschema.tbls values (212,to_char(((s_sumdduration))/nullif((s_totalnorevws),0),'FM99,999,999'))::character varying as ob_arht);

MySql Audit Log Configuration – Database Administrators Stack Exchange

Stack Exchange Network


Stack Exchange network consists of 177 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

mysql – Serialize MYSQL_RES – Database Administrators Stack Exchange

I have 2 servers (One for Database Connections and one or more for client connections) running in a machine. The job of the database connections server is to fetch data from MySQL database (Aurora) and give this to the other servers on request.

Right now the data transfer (Between the 2 servers) happens via Json (json_spirit)(Don’t know why I designed it this way.).

I am coming up to a stage where the data that is loaded from the MySQL DB is huge when the server starts up and every 1 minute. And 1000s of smaller queries in between.

I can see the impact Json is having since I have to Parse MYSQL_RES to Json and transmit from the DB Connection server to Client connection server and then parse the Json to a data set. I am looking to serialize my data or do something other than parsing since the overhead is slowing down the client connection server since it is waiting on the response from DB Connection Server.

What would you suggest to serialize MYSQL_RES struct? I have read about Protobuf, Flatbuffer and serialization. But simply cannot make a decision.

MySQL trigger deadlocks itself? – Database Administrators Stack Exchange

I have a MySQL 5.7.22 database using the InnoDB engine.

There’s a table respondents that has 6 associated triggers – three of them make CRUD operations on a completed_respondents table, and the other three do so on respondent_stats (shown below for their relevance).

I’ve been noticing some deadlock errors on the application logs (specifically, Deadlock found when trying to get lock; try restarting transaction in different parts of the codebase), and enabling deadlock printing on the DB yields things like the one below.

For what I understand, there are two different transactions mentioned (each of them executing a different trigger).

Transaction (1) is waiting on a lock:

(1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 325 page no 8 n bits 192 index PRIMARY of table `surveda`.`respondent_stats` trx id 22600376 lock_mode X locks rec but not gap waiting

But, as far as I understand, it doesn’t hold any lock (given the lack of a (1) HOLDS THE LOCK(S) section).

Transaction (2) does hold a lock:

(2) HOLDS THE LOCK(S):

RECORD LOCKS space id 325 page no 8 n bits 192 index PRIMARY of table `surveda`.`respondent_stats` trx id 22600374 lock_mode X locks rec but not gap

And it’s blocked waiting on a lock that, to me, seems to be exactly that very same one:

(2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 325 page no 8 n bits 192 index PRIMARY of table `surveda`.`respondent_stats` trx id 22600374 lock_mode X waiting

I’ve read answers stating that triggers run in the same transaction as the query that generated them (and that queries run in an implicit transaction by default), but I don’t understand why is the transaction trying to get the same lock again, and, as an extra, why can’t it get it (I’d expect the same transaction to be able to get the same lock of a second time) or why is this same lock been asked twice at all.


Full deadlock description

2020-06-18T21:35:34.027779Z 566 (Note) InnoDB: Transactions deadlock detected, dumping detailed information.
2020-06-18T21:35:34.027808Z 566 (Note) InnoDB:
*** (1) TRANSACTION:

TRANSACTION 22600376, ACTIVE 1 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 577, OS thread handle 140402000619264, query id 108941776 10.42.205.242 surveda updating
UPDATE respondent_stats
     SET `count` = `count` - 1
   WHERE survey_id = OLD.survey_id
     AND questionnaire_id = IFNULL(OLD.questionnaire_id, 0)
     AND state = OLD.state
     AND disposition = OLD.disposition
     AND quota_bucket_id = IFNULL(OLD.quota_bucket_id, 0)
     AND mode = IFNULL(OLD.mode, '')
2020-06-18T21:35:34.027839Z 566 (Note) InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 325 page no 8 n bits 192 index PRIMARY of table `surveda`.`respondent_stats` trx id 22600376 lock_mode X locks rec but not gap waiting
Record lock, heap no 49 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 8; hex 0000000000000047; asc        G;;
 1: len 4; hex 8000005d; asc    );;
 2: len 6; hex 616374697665; asc active;;
 3: len 9; hex 636f6e746163746564; asc contacted;;
 4: len 4; hex 80000000; asc     ;;
 5: len 7; hex 5b22697672225d; asc ("ivr");;
 6: len 6; hex 00000158dab6; asc    X  ;;
 7: len 7; hex 32000001e72445; asc 2    $E;;
 8: len 4; hex 8000030c; asc     ;;

2020-06-18T21:35:34.028070Z 566 (Note) InnoDB: *** (2) TRANSACTION:

TRANSACTION 22600374, ACTIVE 1 sec inserting
mysql tables in use 3, locked 3
7 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 566, OS thread handle 140402422155008, query id 108941778 10.42.205.242 surveda update
INSERT INTO respondent_stats(survey_id, questionnaire_id, state, disposition, quota_bucket_id, mode, `count`)
  VALUES (NEW.survey_id, IFNULL(NEW.questionnaire_id, 0), NEW.state, NEW.disposition, IFNULL(NEW.quota_bucket_id, 0), IFNULL(NEW.mode, ''), 1)
  ON DUPLICATE KEY UPDATE `count` = `count` + 1
2020-06-18T21:35:34.028092Z 566 (Note) InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 325 page no 8 n bits 192 index PRIMARY of table `surveda`.`respondent_stats` trx id 22600374 lock_mode X locks rec but not gap
Record lock, heap no 49 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 8; hex 0000000000000047; asc        G;;
 1: len 4; hex 8000005d; asc    );;
 2: len 6; hex 616374697665; asc active;;
 3: len 9; hex 636f6e746163746564; asc contacted;;
 4: len 4; hex 80000000; asc     ;;
 5: len 7; hex 5b22697672225d; asc ("ivr");;
 6: len 6; hex 00000158dab6; asc    X  ;;
 7: len 7; hex 32000001e72445; asc 2    $E;;
 8: len 4; hex 8000030c; asc     ;;

2020-06-18T21:35:34.028363Z 566 (Note) InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 325 page no 8 n bits 192 index PRIMARY of table `surveda`.`respondent_stats` trx id 22600374 lock_mode X waiting
Record lock, heap no 49 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 8; hex 0000000000000047; asc        G;;
 1: len 4; hex 8000005d; asc    );;
 2: len 6; hex 616374697665; asc active;;
 3: len 9; hex 636f6e746163746564; asc contacted;;
 4: len 4; hex 80000000; asc     ;;
 5: len 7; hex 5b22697672225d; asc ("ivr");;
 6: len 6; hex 00000158dab6; asc    X  ;;
 7: len 7; hex 32000001e72445; asc 2    $E;;
 8: len 4; hex 8000030c; asc     ;;

2020-06-18T21:35:34.028593Z 566 (Note) InnoDB: *** WE ROLL BACK TRANSACTION (1)

respondent_stats-related triggers

CREATE TRIGGER respondents_ins
AFTER INSERT ON respondents
FOR EACH ROW
BEGIN
  INSERT INTO respondent_stats(survey_id, questionnaire_id, state, disposition, quota_bucket_id, mode, `count`)
  VALUES (NEW.survey_id, IFNULL(NEW.questionnaire_id, 0), NEW.state, NEW.disposition, IFNULL(NEW.quota_bucket_id, 0), IFNULL(NEW.mode, ''), 1)
  ON DUPLICATE KEY UPDATE `count` = `count` + 1;
END;

CREATE TRIGGER respondents_del
AFTER DELETE ON respondents
FOR EACH ROW
BEGIN
  UPDATE respondent_stats
      SET `count` = `count` - 1
    WHERE survey_id = OLD.survey_id
      AND questionnaire_id = IFNULL(OLD.questionnaire_id, 0)
      AND state = OLD.state
      AND disposition = OLD.disposition
      AND quota_bucket_id = IFNULL(OLD.quota_bucket_id, 0)
      AND mode = IFNULL(OLD.mode, '');
END;

CREATE TRIGGER respondents_upd
AFTER UPDATE ON respondents
FOR EACH ROW
BEGIN
  UPDATE respondent_stats
      SET `count` = `count` - 1
    WHERE survey_id = OLD.survey_id
      AND questionnaire_id = IFNULL(OLD.questionnaire_id, 0)
      AND state = OLD.state
      AND disposition = OLD.disposition
      AND quota_bucket_id = IFNULL(OLD.quota_bucket_id, 0)
      AND mode = IFNULL(OLD.mode, '');

  INSERT INTO respondent_stats(survey_id, questionnaire_id, state, disposition, quota_bucket_id, mode, `count`)
  VALUES (NEW.survey_id, IFNULL(NEW.questionnaire_id, 0), NEW.state, NEW.disposition, IFNULL(NEW.quota_bucket_id, 0), IFNULL(NEW.mode, ''), 1)
  ON DUPLICATE KEY UPDATE `count` = `count` + 1;
END;

Quality Database Security Metrics – Database Administrators Stack Exchange

Fellow DBAs and Engineers,

I’m looking for few quality Database security metrics. Purpose of these metric is to demonstrate level of security provided to database schemas in the organisation. Specifically, I’m looking for security metrics which can show state of confidentiality and integrity provided to the DBs in the environment.

As an example:

  1. Percentage of DBs encrypted and Redacted.
  2. Percentage of DBs hosting PII information.

Metric request is platform agnostic but mostly pertaining to Oracle environment in hosted DB environment.

Let me know if you have any questions.

Regards

~ RS

mongodb – database administration – Database Administrators Stack Exchange

Draw ERD of the following scenario:
The setting is that you are one organiser of a poster exhibition on “Global Problems of the 21st Century”, and you must design a database to keep track of the administration of the exhibition. Three main phases are recognised for the exhibition: the submission, the selection and the presentation phase. These are further explained below. 1 Submission Phase: Graphic designers create posters for the exhibition to illustrate one of the chosen global problems. Relevant information on designers includes their name and their affiliation, i.e. the organization they work for. A poster has a title and is assigned an identification number, and it may be created by several graphic designers; although each individual designer may only be involved with one poster. Where a group of graphic designers create a poster, we distinguish between the main designer and the co-designers. In case of a single graphic designer, that person is considered to be the main designer of the poster. The main designer is always the point of contact, so should provide an email address. Selection Phase: All posters created for this exhibition are judged by members of a jury. A judge is a graphic design expert with experience in communication for raising public awareness and for public benefit. Judge information that is of relevance to the organising committee includes the judge’s name, their affiliation and email. Each poster is judged by three different judges. When judging a poster, a judge gives a decision: accept or reject. A poster is selected for the exhibition only if all three judges give an “accept” decision. Note that judges are not allowed to compete in “Global Problems of the 21st Century” themselves. Presentation Phase: All selected posters are then presented in the exhibition by their main graphic designers. The poster presentation is allocated a stand and an exhibition session. Each exhibition session takes place at a specific date, and 4 session topics have been announced: human rights, environmental pollution, poverty, and war.

Index Order Help SQLite – Database administrators stack Exchange

I have a very large sqlite table (70 million rows) on a low end machine that I am trying to index properly.

My columns are timestamp, machineid, ioid and value.

There are probably around 200 ioids (on each machine), a handful of machineids, and the timestamp is one sample per second for each ioid.

My requests are as such:

select * from AnalogInput where SampleInfo_source_timestamp>=0 and SampleInfo_source_timestamp<=1587481620000000000 and ioId in (1,9,10,11,5,6,7,8,12,13,14,15,179,203,2,3,27,28,29,31,32,33,34,35,36,37,30,38,42,48,41,49,53,59,52,60,64,70,63,79,89,82,134,144,137,71,74,75,78,16,479,490,20,26,19,90,94,100,93,101,105,111,104,133,112,115,116,122,181,182,233,177,171,504,505,173,172,174,175,211,212,217,213,214,215,216,176,234,235,236,237,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,219,220,221,222,223,237,238,239,240,241,225,226,227,230,231,232,228,229,506,507) and machineid=1111 group by ioId;

I am currently using an index of: CREATE INDEX "index1 $ AnalogInput" ON "AnalogInput" ("machineId", "ioId", "SampleInfo_source_timestamp")

Is it the most efficient command?

permissions – Access the file belonging to BUILTIN Administrators

As part of a challenge, I need to access a file that belongs to BUILTIN Administrators on a DC.
I mounted the C $ share from another workstation, with the domain account I have, unfortunately I get access denied when I try to access the file.

X:UsersAdministratorFOLDER>dir /Q

03/15/2018  06:46 AM              BUILTINAdministrators .
03/15/2018  06:46 AM              BUILTINAdministrators ..
04/15/2018  05:09 PM               126 BUILTINAdministrators FILE

On the workstation to which I have access via ps, I added my user to the local administration group of the DC:

$group = (ADSI)("WinNT:///administrators,group")
$group.add("WinNT:///,user")

Verification on rpcclient:

rpcclient $> enumalsgroups builtin
...
group:(Administrators) rid:(0x220)
...
rpcclient $> queryaliasmem builtin 0x220
    sid:(S-1-5-21-779333104-4060554207-35427279-500)
    sid:(S-1-5-21-779333104-4060554207-35427279-519)
    sid:(S-1-5-21-779333104-4060554207-35427279-512)
    sid:(S-1-5-21-779333104-4060554207-35427279-2605)

rpcclient $> lookupsids S-1-5-21-779333104-4060554207-35427279-2605
S-1-5-21-779333104-4060554207-35427279-2605  (1)

also using smbclient:

smbclient ///c$ -U 
Enter WORKGROUP's password: 
Try "help" to get a list of possible commands.
smb: > cd UsersAdministratorFOLDER
smb: UsersAdministratorFOLDER> get FILE
NT_STATUS_ACCESS_DENIED opening remote file UsersAdministratorFOLDERFILE

I have also created a local user but nothing changes.
icacls and cacls returns "Access is denied."
any suggestion?

KR
dk